<?php
// api/request_workflow_common.php
// Rockview University official request workflow helpers.
// Routing is server-controlled and position/campus aware.

require_once __DIR__ . '/../config/database.php';
require_once __DIR__ . '/session_auth.php';

function rvu_req_json($payload, int $status = 200): void {
    http_response_code($status);
    header('Content-Type: application/json; charset=UTF-8');
    echo json_encode($payload, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES | JSON_INVALID_UTF8_SUBSTITUTE);
    exit;
}

function rvu_req_input(): array {
    $raw = file_get_contents('php://input');
    if ($raw === false || trim($raw) === '') return [];
    $decoded = json_decode($raw, true);
    if (!is_array($decoded)) rvu_req_json(['detail' => 'Supply a valid JSON object.'], 400);
    return $decoded;
}

function rvu_req_user(PDO $pdo): array {
    $user = rvu_current_user($pdo);
    if (!$user) rvu_req_json(['detail' => 'Your session is missing or expired. Please sign in again.'], 401);
    return $user;
}

function rvu_req_clean($value, int $max = 0): string {
    $text = trim((string)($value ?? ''));
    if ($max > 0 && mb_strlen($text) > $max) $text = mb_substr($text, 0, $max);
    return $text;
}

function rvu_req_norm($value): string {
    $text = trim((string)($value ?? ''));
    $text = preg_replace('/\s+/', ' ', $text);
    return mb_strtoupper((string)$text, 'UTF-8');
}

function rvu_req_campus_key($value): string {
    $n = rvu_req_norm($value);
    if ($n === '') return '';
    if (strpos($n, 'MANSA') !== false) return 'MANSA';
    if (strpos($n, 'NDOLA') !== false) return 'NDOLA';
    if (strpos($n, 'CHIPATA') !== false) return 'CHIPATA';
    if (strpos($n, 'LIVINGSTONE') !== false) return 'LIVINGSTONE';
    if (strpos($n, 'E-LEARNING') !== false || strpos($n, 'ELEARNING') !== false || strpos($n, 'ONLINE') !== false) return 'E-LEARNING';
    if (strpos($n, '10 MILES') !== false || strpos($n, 'TEN MILES') !== false) return '10 MILES';

    // All supported Head Quarters labels normalize to one canonical key.
    // This includes the actual campus label used by Rockview: "HQ CAMPUS".
    if (
        $n === 'HQ' ||
        $n === 'HQ CAMPUS' ||
        strpos($n, 'HEAD QUART') !== false ||
        strpos($n, 'HEADQUART') !== false ||
        strpos($n, 'HEAD OFFICE') !== false
    ) return 'HQ';

    return $n;
}

function rvu_req_role_is_admin($role): bool {
    $role = strtolower(trim((string)($role ?? '')));
    return in_array($role, ['admin', 'administrator', 'superadmin', 'super_admin', 'super admin'], true);
}

function rvu_req_is_global_admin(array $user): bool {
    $role = strtolower(trim((string)($user['role'] ?? '')));
    return in_array($role, ['superadmin', 'super_admin', 'super admin'], true);
}

function rvu_req_table_exists(PDO $pdo, string $table): bool {
    $stmt = $pdo->prepare("SELECT 1 FROM information_schema.tables WHERE table_schema = current_schema() AND table_name = ? LIMIT 1");
    $stmt->execute([$table]);
    return (bool)$stmt->fetchColumn();
}

function rvu_req_columns(PDO $pdo, string $table): array {
    $stmt = $pdo->prepare("SELECT column_name FROM information_schema.columns WHERE table_schema = current_schema() AND table_name = ? ORDER BY ordinal_position");
    $stmt->execute([$table]);
    return $stmt->fetchAll(PDO::FETCH_COLUMN) ?: [];
}

function rvu_req_existing_admin_table(PDO $pdo): ?string {
    foreach (['admins', 'administrators', 'admin_users'] as $name) {
        if (rvu_req_table_exists($pdo, $name)) return $name;
    }
    return null;
}

function rvu_req_is_admin_account(PDO $pdo, array $user): bool {
    if (rvu_req_role_is_admin($user['role'] ?? '')) return true;
    $email = rvu_req_clean($user['email'] ?? '', 255);
    if ($email === '') return false;
    $table = rvu_req_existing_admin_table($pdo);
    if ($table === null) return false;
    $cols = rvu_req_columns($pdo, $table);
    if (!in_array('email', $cols, true)) return false;
    $stmt = $pdo->prepare('SELECT 1 FROM "' . str_replace('"', '""', $table) . '" WHERE LOWER(email) = LOWER(?) LIMIT 1');
    $stmt->execute([$email]);
    return (bool)$stmt->fetchColumn();
}

function rvu_req_can_choose_campus(PDO $pdo, array $user): bool {
    return rvu_req_is_admin_account($pdo, $user);
}

function rvu_req_can_manage_office_holders(PDO $pdo, array $user): bool {
    return rvu_req_is_admin_account($pdo, $user);
}

function rvu_req_position_label_field(PDO $pdo): ?string {
    if (!rvu_req_table_exists($pdo, 'positions')) return null;
    $columns = rvu_req_columns($pdo, 'positions');
    foreach (['title', 'name', 'position_name'] as $candidate) {
        if (in_array($candidate, $columns, true)) return $candidate;
    }
    return null;
}

function rvu_req_position_key($value): string {
    $n = rvu_req_norm($value);
    $simple = preg_replace('/[^A-Z0-9]+/', ' ', $n);
    $simple = trim(preg_replace('/\s+/', ' ', (string)$simple));

    if (strpos($simple, 'REGISTRAR') !== false) return 'registrar';
    if (preg_match('/(^| )DVC ?1( |$)/', $simple)) return 'dvc1';
    if (strpos($simple, 'DEPUTY VICE CHANCELLOR') !== false && strpos($simple, ' 2') === false && strpos($simple, ' II') === false) return 'dvc1';
    if (strpos($simple, 'CHIEF FINANCIAL OFFICER') !== false || preg_match('/(^| )CFO( |$)/', $simple)) return 'cfo';
    if (strpos($simple, 'CHIEF EXECUTIVE OFFICER') !== false || preg_match('/(^| )CEO( |$)/', $simple)) return 'ceo';
    if (strpos($simple, 'ACCOUNTANT') !== false) return 'accountant';
    return '';
}

function rvu_req_stage_label(string $stage): string {
    switch ($stage) {
        case 'registrar': return 'Registrar';
        case 'dvc1': return 'DVC1';
        case 'accountant': return 'Accountant';
        case 'cfo': return 'Chief Financial Officer';
        case 'ceo': return 'Chief Executive Officer';
        default: return 'Administrative Office';
    }
}

/**
 * Returns all offices an administrator may route to from the current stage.
 * DVC1 has a professional routing choice: Accountant or direct to CFO.
 * CFO may return to Accountant for financial clarification or escalate to CEO.
 */
function rvu_req_next_stages(string $stage): array {
    switch ($stage) {
        case 'registrar': return ['dvc1'];
        case 'dvc1': return ['accountant', 'cfo'];
        case 'accountant': return ['cfo'];
        case 'cfo': return ['accountant', 'ceo'];
        case 'ceo': return [];
        default: return [];
    }
}

/** Compatibility helper for older callers that expect only one next stage. */
function rvu_req_next_stage(string $stage): ?string {
    $stages = rvu_req_next_stages($stage);
    return $stages[0] ?? null;
}


/**
 * Return the exact Head Quarters campus label stored in the database.
 * The database may use HQ CAMPUS, HQ, Head Quarters, or a similar alias;
 * rvu_req_campus_key() normalizes all of them to the canonical HQ key.
 */
function rvu_req_headquarters_campus(PDO $pdo): string {
    foreach (rvu_req_campuses($pdo) as $campus) {
        if (rvu_req_campus_key($campus) === 'HQ') {
            return rvu_req_clean($campus, 180);
        }
    }

    // Safe fallback when the campus directory has not yet been populated.
    return 'HQ CAMPUS';
}

/**
 * Official workflow campus rule:
 *   Registrar   -> request origin campus
 *   DVC1        -> Head Quarters
 *   Accountant  -> Head Quarters
 *   CFO         -> Head Quarters
 *   CEO         -> Head Quarters
 */
function rvu_req_routing_campus(PDO $pdo, string $stage, string $originCampus): string {
    $stage = strtolower(trim($stage));
    $originCampus = rvu_req_clean($originCampus, 180);

    if ($stage === 'registrar') {
        return $originCampus;
    }

    if (in_array($stage, ['dvc1', 'accountant', 'cfo', 'ceo'], true)) {
        return rvu_req_headquarters_campus($pdo);
    }

    return $originCampus;
}

function rvu_req_request_origin_campus(array $request): string {
    $origin = rvu_req_clean($request['requester_campus'] ?? '', 180);
    if ($origin !== '') return $origin;
    return rvu_req_clean($request['target_campus'] ?? '', 180);
}

function rvu_req_position_match_score(string $stage, string $title): int {
    $n = rvu_req_norm($title);
    $key = rvu_req_position_key($title);
    if ($key !== $stage) return 9999;
    $exact = [
        'registrar' => ['THE REGISTRAR', 'REGISTRAR'],
        'dvc1' => ['DVC1', 'DVC 1', 'THE DVC1', 'THE DVC 1', 'THE DEPUTY VICE CHANCELLOR 1', 'DEPUTY VICE CHANCELLOR 1', 'THE DEPUTY VICE CHANCELLOR', 'DEPUTY VICE CHANCELLOR'],
        'accountant' => ['THE ACCOUNTANT', 'ACCOUNTANT'],
        'cfo' => ['THE CHIEF FINANCIAL OFFICER', 'CHIEF FINANCIAL OFFICER', 'THE CFO', 'CFO'],
        'ceo' => ['THE CHIEF EXECUTIVE OFFICER', 'CHIEF EXECUTIVE OFFICER', 'THE CEO', 'CEO'],
    ];
    $idx = array_search($n, $exact[$stage] ?? [], true);
    if ($idx !== false) return (int)$idx;
    return 100 + mb_strlen($n);
}

function rvu_req_all_positions(PDO $pdo): array {
    $field = rvu_req_position_label_field($pdo);
    if ($field === null) return [];
    $quoted = '"' . str_replace('"', '""', $field) . '"';
    $stmt = $pdo->query("SELECT id, $quoted AS title FROM positions ORDER BY $quoted ASC");
    $rows = $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
    foreach ($rows as &$row) $row['id'] = (int)$row['id'];
    unset($row);
    return $rows;
}

function rvu_req_position_for_stage(PDO $pdo, string $stage): ?array {
    $best = null;
    $bestScore = 10000;
    foreach (rvu_req_all_positions($pdo) as $row) {
        $score = rvu_req_position_match_score($stage, (string)($row['title'] ?? ''));
        if ($score < $bestScore) {
            $best = $row;
            $bestScore = $score;
        }
    }
    return $bestScore >= 9999 ? null : $best;
}

function rvu_req_workflow_positions(PDO $pdo): array {
    $rows = [];
    foreach (['registrar', 'dvc1', 'accountant', 'cfo', 'ceo'] as $stage) {
        $position = rvu_req_position_for_stage($pdo, $stage);
        if ($position) {
            $position['stage'] = $stage;
            $position['stage_label'] = rvu_req_stage_label($stage);
            $rows[] = $position;
        }
    }
    return $rows;
}

function rvu_req_ensure_tables(PDO $pdo): void {
    $pdo->exec("CREATE TABLE IF NOT EXISTS workflow_requests (
        id BIGSERIAL PRIMARY KEY,
        requester_user_id BIGINT NOT NULL,
        requester_name VARCHAR(255) NOT NULL,
        requester_email VARCHAR(255) NOT NULL,
        requester_position VARCHAR(180) NULL,
        requester_campus VARCHAR(180) NULL,
        recipient_position_id BIGINT NULL,
        recipient_position VARCHAR(180) NOT NULL,
        target_campus VARCHAR(180) NOT NULL,
        current_stage VARCHAR(32) NULL,
        reference VARCHAR(240) NOT NULL,
        body TEXT NOT NULL,
        category VARCHAR(80) NOT NULL DEFAULT 'General',
        priority VARCHAR(20) NOT NULL DEFAULT 'normal',
        amount NUMERIC(14,2) NULL,
        table_data JSONB NULL,
        status VARCHAR(24) NOT NULL DEFAULT 'pending',
        created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
        finalized_at TIMESTAMPTZ NULL,
        CONSTRAINT workflow_requests_status_check CHECK (status IN ('pending','approved','declined','cancelled')),
        CONSTRAINT workflow_requests_priority_check CHECK (priority IN ('normal','important','urgent'))
    )");
    $pdo->exec("ALTER TABLE workflow_requests ADD COLUMN IF NOT EXISTS current_stage VARCHAR(32) NULL");
    $pdo->exec("ALTER TABLE workflow_requests ADD COLUMN IF NOT EXISTS table_data JSONB NULL");

    $pdo->exec("CREATE TABLE IF NOT EXISTS workflow_request_routes (
        id BIGSERIAL PRIMARY KEY,
        request_id BIGINT NOT NULL REFERENCES workflow_requests(id) ON DELETE CASCADE,
        action VARCHAR(40) NOT NULL,
        actor_user_id BIGINT NOT NULL,
        actor_name VARCHAR(255) NOT NULL,
        actor_position VARCHAR(180) NULL,
        actor_campus VARCHAR(180) NULL,
        from_position VARCHAR(180) NULL,
        from_campus VARCHAR(180) NULL,
        to_position_id BIGINT NULL,
        to_position VARCHAR(180) NULL,
        to_campus VARCHAR(180) NULL,
        remarks TEXT NULL,
        created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
    )");

    $pdo->exec("CREATE TABLE IF NOT EXISTS workflow_request_notifications (
        id BIGSERIAL PRIMARY KEY,
        request_id BIGINT NOT NULL REFERENCES workflow_requests(id) ON DELETE CASCADE,
        recipient_user_id BIGINT NOT NULL,
        notification_type VARCHAR(50) NOT NULL,
        title VARCHAR(220) NOT NULL,
        body TEXT NOT NULL,
        is_read BOOLEAN NOT NULL DEFAULT FALSE,
        created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
        read_at TIMESTAMPTZ NULL
    )");

    $pdo->exec("CREATE TABLE IF NOT EXISTS workflow_office_holders (
        id BIGSERIAL PRIMARY KEY,
        position_id BIGINT NOT NULL,
        position_title VARCHAR(180) NOT NULL,
        campus VARCHAR(180) NOT NULL,
        user_id BIGINT NOT NULL,
        assigned_by_user_id BIGINT NOT NULL,
        is_active BOOLEAN NOT NULL DEFAULT TRUE,
        created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
        CONSTRAINT workflow_office_holders_unique UNIQUE(position_id, campus)
    )");

    $pdo->exec("CREATE INDEX IF NOT EXISTS idx_workflow_requests_requester ON workflow_requests(requester_user_id, created_at DESC)");
    $pdo->exec("CREATE INDEX IF NOT EXISTS idx_workflow_requests_target ON workflow_requests(target_campus, recipient_position, status)");
    $pdo->exec("CREATE INDEX IF NOT EXISTS idx_workflow_requests_stage ON workflow_requests(current_stage, target_campus, status)");
    $pdo->exec("CREATE INDEX IF NOT EXISTS idx_workflow_routes_request ON workflow_request_routes(request_id, created_at)");
    $pdo->exec("CREATE INDEX IF NOT EXISTS idx_workflow_routes_actor ON workflow_request_routes(actor_user_id, request_id)");
    $pdo->exec("CREATE INDEX IF NOT EXISTS idx_workflow_notifications_user ON workflow_request_notifications(recipient_user_id, is_read, created_at DESC)");
    $pdo->exec("CREATE INDEX IF NOT EXISTS idx_workflow_office_holders_user ON workflow_office_holders(user_id, is_active, campus)");
    $pdo->exec("CREATE INDEX IF NOT EXISTS idx_workflow_office_holders_position ON workflow_office_holders(position_id, campus, is_active)");
}

function rvu_req_normalize_table_payload($raw): ?array {
    if ($raw === null || $raw === '' || $raw === false) return null;
    if (is_string($raw)) {
        $decoded = json_decode($raw, true);
        if (!is_array($decoded)) rvu_req_json(['detail' => 'The optional request table is not valid JSON.'], 422);
        $raw = $decoded;
    }
    if (!is_array($raw)) rvu_req_json(['detail' => 'The optional request table must be an object.'], 422);

    $title = rvu_req_clean($raw['title'] ?? 'Table description', 160);
    if ($title === '') $title = 'Table description';
    $columnsRaw = $raw['columns'] ?? [];
    $rowsRaw = $raw['rows'] ?? [];
    if (!is_array($columnsRaw) || !is_array($rowsRaw)) {
        rvu_req_json(['detail' => 'The optional request table has an invalid structure.'], 422);
    }
    if (count($columnsRaw) < 1) return null;
    if (count($columnsRaw) > 12) rvu_req_json(['detail' => 'A request table can contain at most 12 columns.'], 422);
    if (count($rowsRaw) > 60) rvu_req_json(['detail' => 'A request table can contain at most 60 rows.'], 422);

    $columns = [];
    foreach ($columnsRaw as $index => $column) {
        if (is_string($column)) $column = ['label' => $column];
        if (!is_array($column)) rvu_req_json(['detail' => 'A request table column is invalid.'], 422);
        $label = rvu_req_clean($column['label'] ?? '', 80);
        if ($label === '') $label = 'COLUMN ' . ((int)$index + 1);
        $width = isset($column['width']) && is_numeric($column['width']) ? (float)$column['width'] : 140.0;
        $width = max(80.0, min(360.0, $width));
        $columns[] = ['label' => $label, 'width' => round($width, 1)];
    }

    $rows = [];
    foreach ($rowsRaw as $rowRaw) {
        if (!is_array($rowRaw)) rvu_req_json(['detail' => 'A request table row is invalid.'], 422);
        $cellsRaw = array_key_exists('cells', $rowRaw) && is_array($rowRaw['cells']) ? $rowRaw['cells'] : $rowRaw;
        $cells = [];
        for ($i = 0; $i < count($columns); $i++) {
            $value = rvu_req_clean($cellsRaw[$i] ?? '', 500);
            $cells[] = $value;
        }
        $rows[] = ['cells' => $cells];
    }

    if (!$rows) $rows[] = ['cells' => array_fill(0, count($columns), '')];
    return ['title' => $title, 'columns' => $columns, 'rows' => $rows];
}

function rvu_req_decode_table_data($raw): ?array {
    if ($raw === null || $raw === '') return null;
    if (is_array($raw)) return $raw;
    if (is_string($raw)) {
        $decoded = json_decode($raw, true);
        return is_array($decoded) ? $decoded : null;
    }
    return null;
}

function rvu_req_position_by_id(PDO $pdo, int $id): ?array {
    foreach (rvu_req_all_positions($pdo) as $row) {
        if ((int)$row['id'] === $id) return $row;
    }
    return null;
}

function rvu_req_user_holder_assignment(PDO $pdo, int $userId, string $campus = ''): ?array {
    $stmt = $pdo->prepare("SELECT * FROM workflow_office_holders WHERE user_id = ? AND is_active = TRUE ORDER BY updated_at DESC, id DESC");
    $stmt->execute([$userId]);
    $campusKey = rvu_req_campus_key($campus);
    foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $row) {
        if ($campusKey === '' || rvu_req_campus_key($row['campus'] ?? '') === $campusKey) return $row;
    }
    return null;
}

function rvu_req_admin_record(PDO $pdo, string $email): ?array {
    $table = rvu_req_existing_admin_table($pdo);
    if ($table === null || $email === '') return null;
    $cols = rvu_req_columns($pdo, $table);
    if (!in_array('email', $cols, true)) return null;
    $select = ['email'];
    foreach (['name', 'full_name', 'role', 'position', 'position_name', 'campus'] as $field) {
        if (in_array($field, $cols, true)) $select[] = '"' . $field . '"';
    }
    $stmt = $pdo->prepare('SELECT ' . implode(', ', $select) . ' FROM "' . str_replace('"', '""', $table) . '" WHERE LOWER(email) = LOWER(?) LIMIT 1');
    $stmt->execute([$email]);
    $row = $stmt->fetch(PDO::FETCH_ASSOC);
    return $row ?: null;
}

function rvu_req_official_profile(PDO $pdo, array $user): array {
    $position = '';
    $campus = rvu_req_clean($user['campus'] ?? '', 180);
    $role = rvu_req_clean($user['role'] ?? '', 80);

    // The administrators table/users.position is authoritative for what an
    // administrator is actually allowed to do. Office-holder assignment does
    // not silently change a person's real administrator position.
    $adminRow = rvu_req_admin_record($pdo, rvu_req_clean($user['email'] ?? '', 255));
    if ($adminRow) {
        $position = rvu_req_clean($adminRow['position'] ?? $adminRow['position_name'] ?? '', 180);
        if (rvu_req_clean($adminRow['campus'] ?? '') !== '') $campus = rvu_req_clean($adminRow['campus'] ?? '', 180);
    }

    if ($position === '' && in_array('position', rvu_req_columns($pdo, 'users'), true)) {
        $stmt = $pdo->prepare('SELECT position FROM users WHERE id = ? LIMIT 1');
        $stmt->execute([(int)$user['id']]);
        $position = rvu_req_clean($stmt->fetchColumn() ?: '', 180);
    }

    // Compatibility fallback for installations that have not yet synchronized
    // administrator position into admins/users but already use the workflow
    // office-holder directory.
    if ($position === '') {
        $holder = rvu_req_user_holder_assignment($pdo, (int)$user['id'], $campus);
        if ($holder) {
            $position = rvu_req_clean($holder['position_title'] ?? '', 180);
            if ($campus === '' && rvu_req_clean($holder['campus'] ?? '') !== '') {
                $campus = rvu_req_clean($holder['campus'], 180);
            }
        }
    }

    if ($position === '') $position = $role;

    return [
        'user_id' => (int)$user['id'],
        'name' => rvu_req_clean($user['full_name'] ?? '', 255),
        'email' => rvu_req_clean($user['email'] ?? '', 255),
        'role' => $role,
        'position' => $position,
        'position_key' => rvu_req_position_key($position),
        'campus' => $campus,
        'campus_key' => rvu_req_campus_key($campus),
        'is_admin' => rvu_req_is_admin_account($pdo, $user),
        'is_global_admin' => rvu_req_is_global_admin($user),
    ];
}

function rvu_req_recipient_users(PDO $pdo, string $position, string $campus, ?int $positionId = null): array {
    $campusKey = rvu_req_campus_key($campus);
    $requiredStage = rvu_req_position_key($position);
    $found = [];

    // Explicit office-holder assignment has first priority. The assignment is
    // still validated against the real administrator account before delivery.
    if ($positionId !== null && $positionId > 0) {
        $stmt = $pdo->prepare("SELECT u.id, u.full_name, u.email, u.role, u.campus, h.position_title AS official_position, h.campus AS official_campus
                              FROM workflow_office_holders h
                              INNER JOIN users u ON u.id = h.user_id
                              WHERE h.position_id = ? AND h.is_active = TRUE");
        $stmt->execute([$positionId]);
        foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $row) {
            if ($campusKey !== '' && rvu_req_campus_key($row['official_campus'] ?? $row['campus'] ?? '') !== $campusKey) continue;
            if (!rvu_req_is_admin_account($pdo, $row)) continue;
            $profile = rvu_req_official_profile($pdo, $row);
            if ($requiredStage !== '' && ($profile['position_key'] ?? '') !== $requiredStage) continue;
            $row['official_position'] = $profile['position'];
            $row['official_campus'] = $profile['campus'];
            $found[(int)$row['id']] = $row;
        }
        if ($found) return array_values($found);
    }

    // Fall back to the existing administrator directory. Compare normalized
    // workflow stages instead of exact text, so REGISTRAR and THE REGISTRAR,
    // DVC1 and DEPUTY VICE CHANCELLOR 1, etc. resolve to the same office.
    $adminTable = rvu_req_existing_admin_table($pdo);
    if ($adminTable !== null) {
        $cols = rvu_req_columns($pdo, $adminTable);
        $positionField = in_array('position', $cols, true) ? 'position' : (in_array('position_name', $cols, true) ? 'position_name' : null);
        $campusField = in_array('campus', $cols, true) ? 'campus' : null;
        if (in_array('email', $cols, true) && $positionField) {
            $sql = 'SELECT u.id, u.full_name, u.email, u.role, u.campus, a."' . $positionField . '" AS official_position';
            if ($campusField) $sql .= ', a."' . $campusField . '" AS official_campus';
            $sql .= ' FROM "' . $adminTable . '" a INNER JOIN users u ON LOWER(u.email) = LOWER(a.email)';
            $stmt = $pdo->query($sql);
            foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $row) {
                if ($requiredStage !== '' && rvu_req_position_key($row['official_position'] ?? '') !== $requiredStage) continue;
                if ($campusKey !== '' && rvu_req_campus_key($row['official_campus'] ?? $row['campus'] ?? '') !== $campusKey) continue;
                $found[(int)$row['id']] = $row;
            }
        }
    }

    if (in_array('position', rvu_req_columns($pdo, 'users'), true)) {
        $stmt = $pdo->query("SELECT id, full_name, email, role, campus, position AS official_position FROM users WHERE position IS NOT NULL AND TRIM(position) <> ''");
        foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $row) {
            if ($requiredStage !== '' && rvu_req_position_key($row['official_position'] ?? '') !== $requiredStage) continue;
            if ($campusKey !== '' && rvu_req_campus_key($row['campus'] ?? '') !== $campusKey) continue;
            if (!rvu_req_is_admin_account($pdo, $row)) continue;
            $found[(int)$row['id']] = $row;
        }
    }

    return array_values($found);
}

function rvu_req_campuses(PDO $pdo): array {
    $values = [];
    if (rvu_req_table_exists($pdo, 'campuses')) {
        $columns = rvu_req_columns($pdo, 'campuses');
        $nameField = in_array('name', $columns, true) ? 'name' : (in_array('campus_name', $columns, true) ? 'campus_name' : null);
        if ($nameField !== null) {
            try {
                $stmt = $pdo->query('SELECT "' . $nameField . '" AS campus FROM campuses WHERE "' . $nameField . '" IS NOT NULL AND TRIM("' . $nameField . '") <> \'\' ORDER BY "' . $nameField . '"');
                foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $row) {
                    $key = rvu_req_campus_key($row['campus'] ?? '');
                    if ($key !== '') $values[$key] = $row['campus'];
                }
            } catch (Throwable $e) {}
        }
    }
    try {
        $stmt = $pdo->query("SELECT DISTINCT TRIM(campus) AS campus FROM users WHERE campus IS NOT NULL AND TRIM(campus) <> '' ORDER BY campus");
        foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $row) {
            $key = rvu_req_campus_key($row['campus'] ?? '');
            if ($key !== '') $values[$key] = $row['campus'];
        }
    } catch (Throwable $e) {}
    return array_values($values);
}

function rvu_req_directory(PDO $pdo, array $user, ?string $campusOverride = null): array {
    $profile = rvu_req_official_profile($pdo, $user);
    $targetCampus = rvu_req_clean($campusOverride ?? '', 180);
    if ($targetCampus === '') $targetCampus = $profile['campus'];
    if (!rvu_req_can_choose_campus($pdo, $user) && rvu_req_campus_key($targetCampus) !== rvu_req_campus_key($profile['campus'])) {
        $targetCampus = $profile['campus'];
    }

    $positions = [];
    foreach (rvu_req_workflow_positions($pdo) as $row) {
        $title = rvu_req_clean($row['title'] ?? '', 180);
        $stage = (string)($row['stage'] ?? '');

        // Only Registrar belongs to the selected/origin campus. All central
        // workflow offices are always resolved from the Head Quarters campus.
        $routingCampus = rvu_req_routing_campus($pdo, $stage, $targetCampus);
        $recipients = rvu_req_recipient_users($pdo, $title, $routingCampus, (int)$row['id']);

        $positions[] = [
            'id' => (int)$row['id'],
            'title' => $title,
            'stage' => $stage,
            'stage_label' => $row['stage_label'],
            'routing_campus' => $routingCampus,
            'recipient_count' => count($recipients),
            'recipient_names' => array_values(array_map(fn($r) => $r['full_name'] ?? '', $recipients)),
        ];
    }

    return [
        'current_user' => $profile,
        'selected_campus' => $targetCampus,
        'campuses' => rvu_req_can_choose_campus($pdo, $user) ? rvu_req_campuses($pdo) : array_values(array_filter([$profile['campus']])),
        'positions' => $positions,
        'workflow' => ['registrar', 'dvc1', 'accountant', 'cfo'],
    ];
}

function rvu_req_notify(PDO $pdo, int $requestId, array $recipientIds, string $type, string $title, string $body): void {
    if (!$recipientIds) return;
    $stmt = $pdo->prepare("INSERT INTO workflow_request_notifications (request_id, recipient_user_id, notification_type, title, body) VALUES (?, ?, ?, ?, ?)");
    foreach (array_unique(array_map('intval', $recipientIds)) as $recipientId) {
        if ($recipientId <= 0) continue;
        $stmt->execute([$requestId, $recipientId, $type, $title, $body]);
    }
}

function rvu_req_mark_action_notifications_read(PDO $pdo, int $requestId): void {
    $stmt = $pdo->prepare("UPDATE workflow_request_notifications SET is_read = TRUE, read_at = COALESCE(read_at, CURRENT_TIMESTAMP) WHERE request_id = ? AND notification_type = 'action_required' AND is_read = FALSE");
    $stmt->execute([$requestId]);
}

function rvu_req_history(PDO $pdo, int $requestId): array {
    $stmt = $pdo->prepare("SELECT * FROM workflow_request_routes WHERE request_id = ? ORDER BY created_at ASC, id ASC");
    $stmt->execute([$requestId]);
    return $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
}

function rvu_req_request_stage(array $request): string {
    $stage = strtolower(trim((string)($request['current_stage'] ?? '')));
    if (in_array($stage, ['registrar', 'dvc1', 'accountant', 'cfo', 'ceo'], true)) return $stage;
    return rvu_req_position_key($request['recipient_position'] ?? '');
}

function rvu_req_has_explicit_holder(PDO $pdo, int $positionId, string $campus): bool {
    if ($positionId <= 0) return false;
    $stmt = $pdo->prepare("SELECT campus FROM workflow_office_holders WHERE position_id = ? AND is_active = TRUE");
    $stmt->execute([$positionId]);
    $targetKey = rvu_req_campus_key($campus);
    foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $row) {
        if (rvu_req_campus_key($row['campus'] ?? '') === $targetKey) return true;
    }
    return false;
}

function rvu_req_user_is_holder(PDO $pdo, int $positionId, int $userId, string $campus): bool {
    if ($positionId <= 0 || $userId <= 0) return false;
    $stmt = $pdo->prepare("SELECT campus FROM workflow_office_holders WHERE position_id = ? AND user_id = ? AND is_active = TRUE");
    $stmt->execute([$positionId, $userId]);
    $targetKey = rvu_req_campus_key($campus);
    foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $row) {
        if (rvu_req_campus_key($row['campus'] ?? '') === $targetKey) return true;
    }
    return false;
}

function rvu_req_can_process(PDO $pdo, array $user, array $request): bool {
    if (($request['status'] ?? '') !== 'pending') return false;
    if (!rvu_req_is_admin_account($pdo, $user)) return false;

    $positionId = isset($request['recipient_position_id']) ? (int)$request['recipient_position_id'] : 0;
    $targetCampus = rvu_req_clean($request['target_campus'] ?? '', 180);

    $profile = rvu_req_official_profile($pdo, $user);
    $requiredStage = rvu_req_request_stage($request);
    if ($requiredStage === '' || $profile['position_key'] !== $requiredStage) return false;

    if ($positionId > 0 && rvu_req_has_explicit_holder($pdo, $positionId, $targetCampus)) {
        return rvu_req_user_is_holder($pdo, $positionId, (int)$user['id'], $targetCampus);
    }

    return rvu_req_campus_key($targetCampus) === '' || rvu_req_campus_key($targetCampus) === rvu_req_campus_key($profile['campus']);
}

function rvu_req_can_view(PDO $pdo, array $user, array $request): bool {
    if ((int)$request['requester_user_id'] === (int)$user['id']) return true;
    if (rvu_req_is_global_admin($user)) return true;
    if (rvu_req_can_process($pdo, $user, $request)) return true;
    $stmt = $pdo->prepare('SELECT 1 FROM workflow_request_routes WHERE request_id = ? AND actor_user_id = ? LIMIT 1');
    $stmt->execute([(int)$request['id'], (int)$user['id']]);
    return (bool)$stmt->fetchColumn();
}

function rvu_req_next_routes(PDO $pdo, array $request): array {
    $stage = rvu_req_request_stage($request);
    $nextStages = rvu_req_next_stages($stage);
    if (!$nextStages) return [];

    $originCampus = rvu_req_request_origin_campus($request);
    $routes = [];

    foreach ($nextStages as $next) {
        $nextCampus = rvu_req_routing_campus($pdo, $next, $originCampus);
        $position = rvu_req_position_for_stage($pdo, $next);

        if (!$position) {
            $routes[] = [
                'stage' => $next,
                'stage_label' => rvu_req_stage_label($next),
                'position_id' => null,
                'position_title' => null,
                'campus' => $nextCampus,
                'recipient_count' => 0,
                'recipient_names' => [],
                'available' => false,
                'detail' => rvu_req_stage_label($next) . ' is not configured in the positions table.',
            ];
            continue;
        }

        $recipients = rvu_req_recipient_users(
            $pdo,
            (string)$position['title'],
            $nextCampus,
            (int)$position['id']
        );

        $routes[] = [
            'stage' => $next,
            'stage_label' => rvu_req_stage_label($next),
            'position_id' => (int)$position['id'],
            'position_title' => $position['title'],
            'campus' => $nextCampus,
            'recipient_count' => count($recipients),
            'recipient_names' => array_values(array_map(fn($r) => $r['full_name'] ?? '', $recipients)),
            'available' => count($recipients) > 0,
            'detail' => count($recipients) > 0
                ? ''
                : 'No active administrator is assigned to ' . rvu_req_stage_label($next) . ' at ' . $nextCampus . '.',
        ];
    }

    return $routes;
}

function rvu_req_public_row(PDO $pdo, array $request, array $user): array {
    $request['id'] = (int)$request['id'];
    $request['requester_user_id'] = (int)$request['requester_user_id'];
    $request['recipient_position_id'] = $request['recipient_position_id'] === null ? null : (int)$request['recipient_position_id'];
    $request['amount'] = $request['amount'] === null ? null : (float)$request['amount'];
    $request['table_data'] = rvu_req_decode_table_data($request['table_data'] ?? null);
    $request['has_table'] = is_array($request['table_data']) && !empty($request['table_data']['columns']);
    $request['current_stage'] = rvu_req_request_stage($request);
    $request['is_owner'] = (int)$request['requester_user_id'] === (int)$user['id'];
    $request['viewer_is_admin'] = rvu_req_is_admin_account($pdo, $user);
    $canProcess = rvu_req_can_process($pdo, $user, $request);
    $request['action_required'] = $canProcess;
    $request['can_approve'] = $canProcess;
    $request['can_cancel'] = $canProcess;
    $request['next_routes'] = $canProcess ? rvu_req_next_routes($pdo, $request) : [];
    $request['can_escalate'] = false;
    foreach ($request['next_routes'] as $route) {
        if (($route['available'] ?? false) === true) $request['can_escalate'] = true;
    }
    $request['history'] = rvu_req_history($pdo, (int)$request['id']);
    return $request;
}
