<?php
// api/request_office_holders.php
// Administrator-only campus office-holder directory for the fixed request chain.

require_once __DIR__ . '/request_workflow_common.php';

function rvu_req_user_row(PDO $pdo, int $userId): ?array {
    $stmt = $pdo->prepare("SELECT id, full_name, email, role, campus, school, program, profile_picture FROM users WHERE id = ? LIMIT 1");
    $stmt->execute([$userId]);
    $row = $stmt->fetch(PDO::FETCH_ASSOC);
    return $row ?: null;
}

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

function rvu_req_directory_rows(PDO $pdo, array $user, string $campus): array {
    $profile = rvu_req_official_profile($pdo, $user);
    $targetCampus = rvu_req_clean($campus, 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 $position) {
        $stage = (string)($position['stage'] ?? '');
        $routingCampus = rvu_req_routing_campus($pdo, $stage, $targetCampus);
        $assignment = rvu_req_assignment_for($pdo, (int)$position['id'], $routingCampus);
        $holder = $assignment ? rvu_req_user_row($pdo, (int)$assignment['user_id']) : null;
        $positions[] = [
            'position_id' => (int)$position['id'],
            'position_title' => rvu_req_clean($position['title'] ?? '', 180),
            'stage' => $stage,
            'stage_label' => $position['stage_label'],
            'assignment_id' => $assignment ? (int)$assignment['id'] : null,
            'user_id' => $assignment ? (int)$assignment['user_id'] : null,
            'holder_name' => $holder['full_name'] ?? null,
            'holder_email' => $holder['email'] ?? null,
            'holder_role' => $holder['role'] ?? null,
            'holder_profile_picture' => $holder['profile_picture'] ?? null,
            'campus' => $routingCampus,
            'routing_campus' => $routingCampus,
            'assigned_at' => $assignment['updated_at'] ?? null,
        ];
    }

    $eligible = [];
    if (rvu_req_can_manage_office_holders($pdo, $user)) {
        $stmt = $pdo->query("SELECT id, full_name, email, role, campus, school, program, profile_picture FROM users ORDER BY full_name ASC");
        foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $candidate) {
            if (rvu_req_campus_key($candidate['campus'] ?? '') !== rvu_req_campus_key($targetCampus)) continue;
            if (!rvu_req_is_admin_account($pdo, $candidate)) continue;
            $candidateProfile = rvu_req_official_profile($pdo, $candidate);
            $candidate['id'] = (int)$candidate['id'];
            $candidate['position'] = $candidateProfile['position'] ?? '';
            $candidate['position_key'] = $candidateProfile['position_key'] ?? '';
            $eligible[] = $candidate;
        }
    }

    return [
        'selected_campus' => $targetCampus,
        'campuses' => rvu_req_can_choose_campus($pdo, $user) ? rvu_req_campuses($pdo) : array_values(array_filter([$profile['campus']])),
        'can_manage' => rvu_req_can_manage_office_holders($pdo, $user),
        'current_user' => $profile,
        'positions' => $positions,
        'eligible_users' => $eligible,
        'workflow_note' => 'Configure one Registrar per campus. DVC1, Accountant, CFO and CEO are central Head Quarters offices.',
    ];
}

try {
    rvu_req_ensure_tables($pdo);
    $user = rvu_req_user($pdo);
    $profile = rvu_req_official_profile($pdo, $user);
    $method = $_SERVER['REQUEST_METHOD'] ?? 'GET';

    if ($method === 'GET') {
        if (!rvu_req_can_manage_office_holders($pdo, $user)) {
            rvu_req_json(['detail' => 'Only administrator accounts can open the routing directory.'], 403);
        }
        $campus = rvu_req_clean($_GET['campus'] ?? $profile['campus'], 180);
        rvu_req_json(rvu_req_directory_rows($pdo, $user, $campus));
    }

    if ($method === 'POST' || $method === 'PUT') {
        if (!rvu_req_can_manage_office_holders($pdo, $user)) {
            rvu_req_json(['detail' => 'Only administrator accounts can assign request-routing office holders.'], 403);
        }
        $input = rvu_req_input();
        $positionId = (int)($input['position_id'] ?? 0);
        $userId = (int)($input['user_id'] ?? 0);
        $campus = rvu_req_clean($input['campus'] ?? '', 180);
        if ($positionId <= 0 || $userId <= 0 || $campus === '') {
            rvu_req_json(['detail' => 'Position, campus, and administrator are required.'], 422);
        }

        $position = rvu_req_position_by_id($pdo, $positionId);
        if (!$position || rvu_req_position_key($position['title'] ?? '') === '') {
            rvu_req_json(['detail' => 'Only Registrar, DVC1, Accountant, CFO and CEO positions can be assigned in this workflow.'], 422);
        }
        $targetUser = rvu_req_user_row($pdo, $userId);
        if (!$targetUser) rvu_req_json(['detail' => 'The selected account no longer exists.'], 404);
        if (!rvu_req_is_admin_account($pdo, $targetUser)) {
            rvu_req_json(['detail' => 'The selected account is not an administrator account. Only administrators can hold approval positions.'], 422);
        }
        $title = rvu_req_clean($position['title'] ?? '', 180);
        $requiredStage = rvu_req_position_key($title);

        // Registrar is campus-specific. DVC1, Accountant, CFO and CEO are
        // institutional Head Quarters offices regardless of the selected branch.
        $campus = rvu_req_routing_campus($pdo, $requiredStage, $campus);

        if (rvu_req_campus_key($targetUser['campus'] ?? '') !== rvu_req_campus_key($campus)) {
            rvu_req_json([
                'detail' => 'The selected administrator must belong to ' . $campus . ' for the ' . $title . ' routing office.'
            ], 422);
        }

        $targetProfile = rvu_req_official_profile($pdo, $targetUser);
        if (($targetProfile['position_key'] ?? '') !== $requiredStage) {
            rvu_req_json([
                'detail' => $targetUser['full_name'] . ' is registered as "' . ($targetProfile['position'] ?: $targetUser['role']) . '". '
                    . 'Only an administrator whose database position matches ' . $title . ' can be assigned to this routing office.'
            ], 422);
        }
        $pdo->beginTransaction();

        // Deactivate alias-duplicate assignments for the same logical campus/position.
        $all = $pdo->prepare("SELECT id, campus FROM workflow_office_holders WHERE position_id = ? AND is_active = TRUE FOR UPDATE");
        $all->execute([$positionId]);
        foreach ($all->fetchAll(PDO::FETCH_ASSOC) as $old) {
            if (rvu_req_campus_key($old['campus'] ?? '') === rvu_req_campus_key($campus) && rvu_req_norm($old['campus'] ?? '') !== rvu_req_norm($campus)) {
                $off = $pdo->prepare("UPDATE workflow_office_holders SET is_active = FALSE, updated_at = CURRENT_TIMESTAMP WHERE id = ?");
                $off->execute([(int)$old['id']]);
            }
        }

        $sql = "INSERT INTO workflow_office_holders
                    (position_id, position_title, campus, user_id, assigned_by_user_id, is_active, updated_at)
                VALUES (?, ?, ?, ?, ?, TRUE, CURRENT_TIMESTAMP)
                ON CONFLICT (position_id, campus)
                DO UPDATE SET
                    position_title = EXCLUDED.position_title,
                    user_id = EXCLUDED.user_id,
                    assigned_by_user_id = EXCLUDED.assigned_by_user_id,
                    is_active = TRUE,
                    updated_at = CURRENT_TIMESTAMP
                RETURNING id";
        $stmt = $pdo->prepare($sql);
        $stmt->execute([$positionId, $title, $campus, $userId, (int)$user['id']]);
        $assignmentId = (int)$stmt->fetchColumn();

        $pending = $pdo->prepare("SELECT id, reference, target_campus FROM workflow_requests WHERE status = 'pending' AND recipient_position_id = ? ORDER BY updated_at DESC");
        $pending->execute([$positionId]);
        $pendingCount = 0;
        foreach ($pending->fetchAll(PDO::FETCH_ASSOC) as $requestRow) {
            if (rvu_req_campus_key($requestRow['target_campus'] ?? '') !== rvu_req_campus_key($campus)) continue;
            rvu_req_mark_action_notifications_read($pdo, (int)$requestRow['id']);
            rvu_req_notify(
                $pdo,
                (int)$requestRow['id'],
                [$userId],
                'action_required',
                'Pending request requires your action',
                '"' . ($requestRow['reference'] ?? 'Official request') . '" is assigned to ' . $title . ' at ' . $campus . '.'
            );
            $pendingCount++;
        }

        $pdo->commit();
        rvu_req_json([
            'message' => $targetUser['full_name'] . ' is now the routing holder for ' . $title . ' at ' . $campus . '.',
            'assignment_id' => $assignmentId,
            'position_id' => $positionId,
            'position_title' => $title,
            'user_id' => $userId,
            'holder_name' => $targetUser['full_name'],
            'campus' => $campus,
            'pending_requests_notified' => $pendingCount,
        ], $method === 'POST' ? 201 : 200);
    }

    if ($method === 'DELETE') {
        if (!rvu_req_can_manage_office_holders($pdo, $user)) {
            rvu_req_json(['detail' => 'Only administrator accounts can remove request-routing office holders.'], 403);
        }
        $assignmentId = isset($_GET['id']) && ctype_digit((string)$_GET['id']) ? (int)$_GET['id'] : 0;
        if ($assignmentId <= 0) rvu_req_json(['detail' => 'A valid assignment ID is required.'], 400);
        $stmt = $pdo->prepare("UPDATE workflow_office_holders SET is_active = FALSE, updated_at = CURRENT_TIMESTAMP WHERE id = ?");
        $stmt->execute([$assignmentId]);
        if ($stmt->rowCount() === 0) rvu_req_json(['detail' => 'Office-holder assignment not found.'], 404);
        rvu_req_json(['message' => 'Office-holder assignment removed.']);
    }

    rvu_req_json(['detail' => 'Method not allowed.'], 405);
} catch (Throwable $e) {
    if (isset($pdo) && $pdo instanceof PDO && $pdo->inTransaction()) $pdo->rollBack();
    error_log('Request office-holder error: ' . $e->getMessage());
    rvu_req_json(['detail' => 'The request office-holder service encountered an unexpected server error.'], 500);
}
