<?php
// api/leave_common.php
// Shared helpers for Rockview University staff leave workflow.
// QR verification + HR leave ledger + automatic annual accrual.

declare(strict_types=1);

require_once __DIR__ . '/../config/database.php';
require_once __DIR__ . '/session_auth.php';
if (is_file(__DIR__ . '/leave_public_config.php')) { require_once __DIR__ . '/leave_public_config.php'; }

function leave_json($body, int $status = 200): void {
    http_response_code($status);
    header('Content-Type: application/json; charset=UTF-8');
    header('Cache-Control: no-store, no-cache, must-revalidate');
    if ($body !== null) {
        echo json_encode($body, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES | JSON_INVALID_UTF8_SUBSTITUTE);
    }
    exit;
}

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

function leave_norm(string $value): string {
    $value = mb_strtoupper(trim($value), 'UTF-8');
    $value = preg_replace('/[^A-Z0-9]+/', ' ', $value) ?? $value;
    return trim(preg_replace('/\s+/', ' ', $value) ?? $value);
}

function leave_campus_key(string $campus): string {
    $n = leave_norm($campus);
    if ($n === '') return '';
    if (str_contains($n, 'MANSA')) return 'MANSA';
    if (str_contains($n, 'NDOLA')) return 'NDOLA';
    if (str_contains($n, 'CHIPATA')) return 'CHIPATA';
    if (str_contains($n, 'LIVINGSTONE')) return 'LIVINGSTONE';
    if (str_contains($n, 'E LEARNING') || str_contains($n, 'ELEARNING') || str_contains($n, 'ONLINE')) return 'E-LEARNING';
    if (str_contains($n, '10 MILES') || str_contains($n, 'TEN MILES')) return '10 MILES';
    if ($n === 'HQ' || $n === 'HQ CAMPUS' || str_contains($n, 'HEAD QUART') || str_contains($n, 'HEADQUART') || str_contains($n, 'HEAD OFFICE')) return 'HQ';
    return $n;
}

function leave_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 leave_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 leave_ensure_tables(PDO $pdo): void {
    $pdo->exec("CREATE TABLE IF NOT EXISTS leave_applications (
        id BIGSERIAL PRIMARY KEY,
        applicant_user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
        applicant_name VARCHAR(255) NOT NULL,
        applicant_email VARCHAR(255) NOT NULL,
        applicant_phone VARCHAR(120) NULL,
        personnel_no VARCHAR(120) NULL,
        position_title VARCHAR(180) NOT NULL,
        department VARCHAR(180) NOT NULL,
        campus VARCHAR(180) NOT NULL,
        leave_type VARCHAR(60) NOT NULL,
        other_leave_type VARCHAR(180) NULL,
        reason TEXT NOT NULL,
        start_date DATE NOT NULL,
        end_date DATE NOT NULL,
        expected_return_date DATE NOT NULL,
        requested_days NUMERIC(8,2) NOT NULL DEFAULT 0,
        accrual_rate_month NUMERIC(8,2) NULL,
        available_days NUMERIC(8,2) NULL,
        previously_taken_days NUMERIC(8,2) NULL,
        closing_balance NUMERIC(8,2) NULL,
        balance_confirmed_by_user_id BIGINT NULL REFERENCES users(id) ON DELETE SET NULL,
        balance_confirmed_at TIMESTAMPTZ NULL,
        address_while_on_leave TEXT NULL,
        emergency_contact_name VARCHAR(255) NULL,
        emergency_contact_phone VARCHAR(120) NULL,
        emergency_contact_relationship VARCHAR(120) NULL,
        emergency_contact_address TEXT NULL,
        current_stage VARCHAR(40) NOT NULL DEFAULT 'hod',
        current_recipient_user_id BIGINT NULL REFERENCES users(id) ON DELETE SET NULL,
        current_recipient_label VARCHAR(180) NULL,
        current_recipient_campus VARCHAR(180) NULL,
        status VARCHAR(30) NOT NULL DEFAULT 'pending',
        applicant_signature_mark VARCHAR(30) NOT NULL DEFAULT 'QR',
        applicant_signed_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
        submitted_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
        final_decision_at TIMESTAMPTZ NULL,
        created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
    )");

    // Upgrade older installations without removing legacy signature columns.
    $pdo->exec("ALTER TABLE leave_applications ADD COLUMN IF NOT EXISTS applicant_qr_token VARCHAR(64) NULL");
    $pdo->exec("CREATE UNIQUE INDEX IF NOT EXISTS idx_leave_applications_qr_token ON leave_applications(applicant_qr_token) WHERE applicant_qr_token IS NOT NULL");
    $pdo->exec("CREATE INDEX IF NOT EXISTS idx_leave_applications_applicant ON leave_applications(applicant_user_id, created_at DESC)");
    $pdo->exec("CREATE INDEX IF NOT EXISTS idx_leave_applications_recipient ON leave_applications(current_recipient_user_id, status, updated_at DESC)");
    $pdo->exec("CREATE INDEX IF NOT EXISTS idx_leave_applications_stage ON leave_applications(current_stage, status, campus)");

    $pdo->exec("CREATE TABLE IF NOT EXISTS leave_routes (
        id BIGSERIAL PRIMARY KEY,
        leave_id BIGINT NOT NULL REFERENCES leave_applications(id) ON DELETE CASCADE,
        action VARCHAR(50) NOT NULL,
        from_stage VARCHAR(40) NULL,
        to_stage VARCHAR(40) NULL,
        actor_user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
        actor_name VARCHAR(255) NOT NULL,
        actor_position VARCHAR(180) NULL,
        actor_campus VARCHAR(180) NULL,
        to_user_id BIGINT NULL REFERENCES users(id) ON DELETE SET NULL,
        to_label VARCHAR(180) NULL,
        to_campus VARCHAR(180) NULL,
        decision VARCHAR(40) NULL,
        remarks TEXT NULL,
        signature_mark VARCHAR(30) NOT NULL DEFAULT 'QR',
        signed_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
    )");
    $pdo->exec("ALTER TABLE leave_routes ADD COLUMN IF NOT EXISTS qr_token VARCHAR(64) NULL");
    $pdo->exec("CREATE UNIQUE INDEX IF NOT EXISTS idx_leave_routes_qr_token ON leave_routes(qr_token) WHERE qr_token IS NOT NULL");
    $pdo->exec("CREATE INDEX IF NOT EXISTS idx_leave_routes_leave ON leave_routes(leave_id, signed_at ASC)");

    $pdo->exec("CREATE TABLE IF NOT EXISTS leave_notifications (
        id BIGSERIAL PRIMARY KEY,
        leave_id BIGINT NULL REFERENCES leave_applications(id) ON DELETE CASCADE,
        user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
        kind VARCHAR(60) 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
    )");
    // Older versions made leave_id NOT NULL. HR ledger notifications are not
    // attached to a specific leave form, so make it nullable safely.
    try { $pdo->exec("ALTER TABLE leave_notifications ALTER COLUMN leave_id DROP NOT NULL"); } catch (Throwable $ignored) {}
    $pdo->exec("CREATE INDEX IF NOT EXISTS idx_leave_notifications_user ON leave_notifications(user_id, is_read, created_at DESC)");

    $pdo->exec("CREATE TABLE IF NOT EXISTS leave_balances (
        id BIGSERIAL PRIMARY KEY,
        user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
        leave_type VARCHAR(60) NOT NULL,
        leave_year INT NOT NULL,
        entitlement_days NUMERIC(8,2) NOT NULL DEFAULT 0,
        carried_forward_days NUMERIC(8,2) NOT NULL DEFAULT 0,
        used_days NUMERIC(8,2) NOT NULL DEFAULT 0,
        adjustment_days NUMERIC(8,2) NOT NULL DEFAULT 0,
        accrual_rate_month NUMERIC(8,2) NOT NULL DEFAULT 2,
        accrual_started_on DATE NULL,
        last_accrual_month DATE NULL,
        updated_by_user_id BIGINT NULL REFERENCES users(id) ON DELETE SET NULL,
        updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
        UNIQUE(user_id, leave_type, leave_year)
    )");
    $pdo->exec("ALTER TABLE leave_balances ADD COLUMN IF NOT EXISTS accrual_rate_month NUMERIC(8,2) NOT NULL DEFAULT 2");
    $pdo->exec("ALTER TABLE leave_balances ADD COLUMN IF NOT EXISTS accrual_started_on DATE NULL");
    $pdo->exec("ALTER TABLE leave_balances ADD COLUMN IF NOT EXISTS last_accrual_month DATE NULL");

    $pdo->exec("CREATE TABLE IF NOT EXISTS leave_balance_adjustments (
        id BIGSERIAL PRIMARY KEY,
        user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
        leave_year INT NOT NULL,
        amount NUMERIC(8,2) NOT NULL,
        before_balance NUMERIC(8,2) NOT NULL,
        after_balance NUMERIC(8,2) NOT NULL,
        reason TEXT NOT NULL,
        actor_user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
        actor_name VARCHAR(255) NOT NULL,
        actor_position VARCHAR(180) NULL,
        actor_campus VARCHAR(180) NULL,
        created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
    )");
    $pdo->exec("CREATE INDEX IF NOT EXISTS idx_leave_balance_adjustments_user ON leave_balance_adjustments(user_id, leave_year, created_at DESC)");

    // Account status controls are shared by the leave executive console and
    // the authentication layer. Existing installations are upgraded safely.
    $pdo->exec("ALTER TABLE users ADD COLUMN IF NOT EXISTS account_status VARCHAR(30) NOT NULL DEFAULT 'active'");
    $pdo->exec("ALTER TABLE users ADD COLUMN IF NOT EXISTS suspended_at TIMESTAMPTZ NULL");
    $pdo->exec("ALTER TABLE users ADD COLUMN IF NOT EXISTS suspended_by_user_id BIGINT NULL");
    $pdo->exec("ALTER TABLE users ADD COLUMN IF NOT EXISTS suspension_reason TEXT NULL");
    $pdo->exec("CREATE INDEX IF NOT EXISTS idx_users_account_status ON users(account_status)");

    $pdo->exec("CREATE TABLE IF NOT EXISTS leave_account_status_audit (
        id BIGSERIAL PRIMARY KEY,
        target_user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
        action VARCHAR(30) NOT NULL,
        previous_status VARCHAR(30) NOT NULL,
        new_status VARCHAR(30) NOT NULL,
        reason TEXT NOT NULL,
        actor_user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
        actor_name VARCHAR(255) NOT NULL,
        actor_position VARCHAR(180) NULL,
        actor_campus VARCHAR(180) NULL,
        created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
    )");
    $pdo->exec("CREATE INDEX IF NOT EXISTS idx_leave_account_status_audit_target ON leave_account_status_audit(target_user_id, created_at DESC)");
}

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

function leave_current_user(PDO $pdo): array {
    $user = rvu_current_user($pdo);
    if (!$user) leave_json(['detail' => 'Your session is invalid or has expired. Please sign in again.'], 401);
    $user['id'] = (int)$user['id'];
    return $user;
}

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

function leave_official_profile(PDO $pdo, array $user): array {
    $position = '';
    $campus = leave_clean($user['campus'] ?? '', 180);
    $admin = leave_admin_record($pdo, leave_clean($user['email'] ?? '', 255));
    if ($admin) {
        $position = leave_clean($admin['position'] ?? '', 180);
        $adminCampus = leave_clean($admin['campus'] ?? '', 180);
        if ($adminCampus !== '') $campus = $adminCampus;
    }
    if ($position === '' && in_array('position', leave_columns($pdo, 'users'), true)) {
        $stmt = $pdo->prepare('SELECT position FROM users WHERE id = ? LIMIT 1');
        $stmt->execute([(int)$user['id']]);
        $position = leave_clean($stmt->fetchColumn() ?: '', 180);
    }
    if ($position === '') $position = leave_clean($user['role'] ?? 'Staff', 180);
    return [
        'user_id' => (int)$user['id'],
        'name' => leave_clean($user['full_name'] ?? '', 255),
        'email' => leave_clean($user['email'] ?? '', 255),
        'phone' => leave_clean($user['phone'] ?? '', 120),
        'role' => leave_clean($user['role'] ?? '', 80),
        'position' => $position,
        'campus' => $campus,
        // Rockview currently stores the staff department name in users.program.
        'department' => leave_clean($user['program'] ?? '', 180),
        'school' => leave_clean($user['school'] ?? '', 180),
    ];
}

// Legacy helper retained for backwards-compatible database columns. The UI no
// longer displays handwritten initials; official endorsements are QR verified.
function leave_signature_mark(string $fullName): string {
    $parts = preg_split('/\s+/', trim($fullName)) ?: [];
    $parts = array_values(array_filter($parts, fn($p) => trim((string)$p) !== ''));
    if (!$parts) return 'QR';
    $first = mb_strtoupper(mb_substr($parts[0], 0, 1), 'UTF-8');
    $secondSource = count($parts) > 1 ? $parts[count($parts) - 1] : $parts[0];
    $second = mb_strtoupper(mb_substr($secondSource, 0, 1), 'UTF-8');
    return $first . $second;
}

function leave_new_qr_token(): string {
    return bin2hex(random_bytes(32));
}

function leave_public_base_url(): string {
    // For QR scanning from phones, set LEAVE_PUBLIC_BASE_URL to a URL reachable
    // by those phones, e.g. http://192.168.1.20/skill_end or your production domain.
    $constant = defined('ROCKVIEW_LEAVE_PUBLIC_BASE_URL') ? (string)ROCKVIEW_LEAVE_PUBLIC_BASE_URL : '';
    $configured = leave_clean($constant !== '' ? $constant : (getenv('LEAVE_PUBLIC_BASE_URL') ?: ''), 500);
    if ($configured !== '') return rtrim($configured, '/');

    $scheme = 'http';
    if ((!empty($_SERVER['HTTPS']) && strtolower((string)$_SERVER['HTTPS']) !== 'off') ||
        (isset($_SERVER['HTTP_X_FORWARDED_PROTO']) && strtolower((string)$_SERVER['HTTP_X_FORWARDED_PROTO']) === 'https')) {
        $scheme = 'https';
    }
    $host = leave_clean($_SERVER['HTTP_HOST'] ?? '127.0.0.1', 255);
    $script = str_replace('\\', '/', (string)($_SERVER['SCRIPT_NAME'] ?? '/skill_end/api/leave.php'));
    $apiPos = strrpos($script, '/api/');
    $root = $apiPos !== false ? substr($script, 0, $apiPos) : rtrim(dirname($script), '/');
    return $scheme . '://' . $host . rtrim($root, '/');
}

function leave_verification_url(string $token): string {
    return leave_public_base_url() . '/api/leave_verify.php?token=' . rawurlencode($token);
}

function leave_ensure_application_qr_token(PDO $pdo, int $leaveId): string {
    $stmt = $pdo->prepare('SELECT id, applicant_qr_token FROM leave_applications WHERE id = ? LIMIT 1');
    $stmt->execute([$leaveId]);
    $existingRow = $stmt->fetch(PDO::FETCH_ASSOC);
    if (!$existingRow) return '';
    $token = leave_clean($existingRow['applicant_qr_token'] ?? '', 64);
    if ($token !== '') return $token;
    for ($i = 0; $i < 5; $i++) {
        $token = leave_new_qr_token();
        try {
            $update = $pdo->prepare('UPDATE leave_applications SET applicant_qr_token = ? WHERE id = ? AND applicant_qr_token IS NULL');
            $update->execute([$token, $leaveId]);
            if ($update->rowCount() > 0) return $token;
            $stmt->execute([$leaveId]);
            $again = $stmt->fetch(PDO::FETCH_ASSOC);
            $existing = leave_clean($again['applicant_qr_token'] ?? '', 64);
            if ($existing !== '') return $existing;
        } catch (PDOException $e) {
            if ($e->getCode() !== '23505') throw $e;
        }
    }
    throw new RuntimeException('Unable to create leave verification token.');
}

function leave_ensure_route_qr_token(PDO $pdo, int $routeId): string {
    $stmt = $pdo->prepare('SELECT qr_token FROM leave_routes WHERE id = ? LIMIT 1');
    $stmt->execute([$routeId]);
    $token = leave_clean($stmt->fetchColumn() ?: '', 64);
    if ($token !== '') return $token;
    for ($i = 0; $i < 5; $i++) {
        $token = leave_new_qr_token();
        try {
            $update = $pdo->prepare('UPDATE leave_routes SET qr_token = ? WHERE id = ? AND qr_token IS NULL');
            $update->execute([$token, $routeId]);
            if ($update->rowCount() > 0) return $token;
            $stmt->execute([$routeId]);
            $existing = leave_clean($stmt->fetchColumn() ?: '', 64);
            if ($existing !== '') return $existing;
        } catch (PDOException $e) {
            if ($e->getCode() !== '23505') throw $e;
        }
    }
    throw new RuntimeException('Unable to create route verification token.');
}

function leave_headquarters_campus(PDO $pdo): string {
    if (leave_table_exists($pdo, 'campuses')) {
        $cols = leave_columns($pdo, 'campuses');
        $field = in_array('name', $cols, true) ? 'name' : (in_array('campus_name', $cols, true) ? 'campus_name' : null);
        if ($field) {
            $quoted = '"' . str_replace('"', '""', $field) . '"';
            $stmt = $pdo->query("SELECT $quoted AS campus FROM campuses WHERE $quoted IS NOT NULL AND TRIM($quoted) <> ''");
            foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $row) {
                $campus = leave_clean($row['campus'] ?? '', 180);
                if (leave_campus_key($campus) === 'HQ') return $campus;
            }
        }
    }
    return 'HQ CAMPUS';
}

function leave_position_stage(string $position): string {
    $n = leave_norm($position);
    if ($n === '') return '';

    // Executive positions. Keep the numbered DVC checks before the generic
    // Deputy Vice-Chancellor alias so DVC2 is never mistaken for DVC1.
    if ($n === 'DVC2' || str_contains($n, 'DVC 2') || str_contains($n, 'DEPUTY VICE CHANCELLOR 2') || str_contains($n, 'DEPUTY VICE CHANCELLOR II')) return 'dvc2';
    if ($n === 'DVC1' || str_contains($n, 'DVC 1') || str_contains($n, 'DEPUTY VICE CHANCELLOR 1') || str_contains($n, 'DEPUTY VICE CHANCELLOR I')) return 'dvc1';
    if (str_contains($n, 'DEPUTY VICE CHANCELLOR')) return 'dvc1';
    if ($n === 'CFO' || str_contains($n, 'CHIEF FINANCIAL OFFICER')) return 'cfo';
    if ($n === 'CEO' || str_contains($n, 'CHIEF EXECUTIVE OFFICER')) return 'ceo';
    if ($n === 'CAO' || str_contains($n, 'CHIEF ACADEMIC OFFICER') || str_contains($n, 'CHIEF ADMINISTRATIVE OFFICER')) return 'cao';
    if ($n === 'COO' || str_contains($n, 'CHIEF OPERATING OFFICER') || str_contains($n, 'CHIEF OPERATIONS OFFICER')) return 'coo';

    if ((str_contains($n, 'SENIOR') && (str_contains($n, 'HR') || str_contains($n, 'HUMAN RESOURCE'))) || $n === 'SENIOR HR') return 'senior_hr';
    if ((str_contains($n, 'JUNIOR') && (str_contains($n, 'HR') || str_contains($n, 'HUMAN RESOURCE'))) || $n === 'JUNIOR HR' || $n === 'HR OFFICER' || $n === 'HUMAN RESOURCE OFFICER') return 'junior_hr';
    if (str_contains($n, 'REGISTRAR')) return 'registrar';
    if ($n === 'HOD' || str_contains($n, 'HEAD OF DEPARTMENT')) return 'hod';
    return '';
}

function leave_stage_label(string $stage): string {
    return match ($stage) {
        'hod' => 'Head of Department',
        'junior_hr' => 'Junior HR',
        'registrar' => 'Registrar',
        'senior_hr' => 'Senior HR',
        'dvc1' => 'DVC1',
        'dvc2' => 'DVC2',
        'cfo' => 'CFO',
        'ceo' => 'CEO',
        'cao' => 'CAO',
        'coo' => 'COO',
        default => ucfirst(str_replace('_', ' ', $stage)),
    };
}

function leave_routing_campus(PDO $pdo, string $stage, string $originCampus): string {
    if (in_array($stage, ['hod', 'junior_hr', 'registrar'], true)) return $originCampus;
    if (in_array($stage, ['senior_hr', 'dvc1'], true)) return leave_headquarters_campus($pdo);
    return $originCampus;
}

function leave_department_id(PDO $pdo, string $department): ?int {
    if (!leave_table_exists($pdo, 'departments')) return null;
    $stmt = $pdo->prepare('SELECT id FROM departments WHERE UPPER(TRIM(name)) = UPPER(TRIM(?)) LIMIT 1');
    $stmt->execute([$department]);
    $id = $stmt->fetchColumn();
    return $id === false ? null : (int)$id;
}

function leave_hod_for(PDO $pdo, string $department, string $campus): ?array {
    if (!leave_table_exists($pdo, 'department_hods')) return null;
    $departmentId = leave_department_id($pdo, $department);
    if (!$departmentId) return null;
    $stmt = $pdo->prepare('SELECT dh.user_id, dh.campus, u.full_name, u.email, u.role FROM department_hods dh INNER JOIN users u ON u.id = dh.user_id WHERE dh.department_id = ?');
    $stmt->execute([$departmentId]);
    foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $row) {
        if (leave_campus_key((string)$row['campus']) === leave_campus_key($campus)) {
            $row['user_id'] = (int)$row['user_id'];
            return $row;
        }
    }
    return null;
}

function leave_admin_candidates(PDO $pdo, string $stage, string $campus): array {
    if (!leave_table_exists($pdo, 'admins')) return [];
    $stmt = $pdo->query("SELECT a.id AS admin_id, a.name, a.email, a.position, a.campus, u.id AS user_id, u.full_name, u.role
                        FROM admins a INNER JOIN users u ON LOWER(u.email) = LOWER(a.email)
                        ORDER BY a.id DESC");
    $matches = [];
    foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $row) {
        if (leave_position_stage((string)($row['position'] ?? '')) !== $stage) continue;
        if (leave_campus_key((string)($row['campus'] ?? '')) !== leave_campus_key($campus)) continue;
        $row['user_id'] = (int)$row['user_id'];
        $matches[] = $row;
    }
    return $matches;
}

function leave_resolve_recipient(PDO $pdo, string $stage, string $originCampus, string $department): array {
    $campus = leave_routing_campus($pdo, $stage, $originCampus);
    if ($stage === 'hod') {
        $hod = leave_hod_for($pdo, $department, $originCampus);
        if (!$hod) {
            leave_json(['detail' => 'No HOD is assigned to ' . $department . ' at ' . $originCampus . '. Configure the department HOD before submitting leave.'], 422);
        }
        return [
            'user_id' => (int)$hod['user_id'],
            'name' => leave_clean($hod['full_name'] ?? '', 255),
            'label' => 'HOD - ' . $department,
            'campus' => $originCampus,
        ];
    }

    $candidates = leave_admin_candidates($pdo, $stage, $campus);
    if (!$candidates) {
        leave_json(['detail' => 'No administrator is configured as ' . leave_stage_label($stage) . ' at ' . $campus . '. Update the Administrator Directory before routing leave.'], 422);
    }
    if (count($candidates) > 1) {
        leave_json(['detail' => 'More than one administrator is configured as ' . leave_stage_label($stage) . ' at ' . $campus . '. Keep one active office holder to avoid ambiguous leave routing.'], 409);
    }
    $row = $candidates[0];
    return [
        'user_id' => (int)$row['user_id'],
        'name' => leave_clean($row['full_name'] ?? $row['name'] ?? '', 255),
        'label' => leave_stage_label($stage),
        'campus' => $campus,
    ];
}

function leave_requested_days(string $start, string $end): float {
    try {
        $a = new DateTimeImmutable($start);
        $b = new DateTimeImmutable($end);
        if ($b < $a) return 0;
        return (float)($a->diff($b)->days + 1);
    } catch (Throwable $e) {
        return 0;
    }
}

function leave_notify(PDO $pdo, ?int $leaveId, int $userId, string $kind, string $title, string $body): void {
    if ($userId <= 0) return;
    $stmt = $pdo->prepare('INSERT INTO leave_notifications (leave_id, user_id, kind, title, body) VALUES (?, ?, ?, ?, ?)');
    $stmt->execute([$leaveId, $userId, $kind, $title, $body]);
}

function leave_route_event(PDO $pdo, array $args): array {
    $token = leave_new_qr_token();
    $stmt = $pdo->prepare("INSERT INTO leave_routes (
        leave_id, action, from_stage, to_stage, actor_user_id, actor_name, actor_position, actor_campus,
        to_user_id, to_label, to_campus, decision, remarks, signature_mark, qr_token
    ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) RETURNING id");
    $stmt->execute([
        $args['leave_id'], $args['action'], $args['from_stage'] ?? null, $args['to_stage'] ?? null,
        $args['actor_user_id'], $args['actor_name'], $args['actor_position'] ?? null, $args['actor_campus'] ?? null,
        $args['to_user_id'] ?? null, $args['to_label'] ?? null, $args['to_campus'] ?? null,
        $args['decision'] ?? null, $args['remarks'] ?? null, $args['signature_mark'] ?? 'QR', $token,
    ]);
    $id = (int)$stmt->fetchColumn();
    return ['id' => $id, 'qr_token' => $token, 'verification_url' => leave_verification_url($token)];
}

function leave_public_row(PDO $pdo, array $row, array $currentUser): array {
    $row['id'] = (int)$row['id'];
    $row['applicant_user_id'] = (int)$row['applicant_user_id'];
    $row['current_recipient_user_id'] = $row['current_recipient_user_id'] !== null ? (int)$row['current_recipient_user_id'] : null;
    foreach (['requested_days','accrual_rate_month','available_days','previously_taken_days','closing_balance'] as $n) {
        if (array_key_exists($n, $row) && $row[$n] !== null) $row[$n] = (float)$row[$n];
    }
    $row['is_owner'] = (int)$row['applicant_user_id'] === (int)$currentUser['id'];
    $row['action_required'] = $row['status'] === 'pending' && (int)($row['current_recipient_user_id'] ?? 0) === (int)$currentUser['id'];
    if (!empty($row['applicant_qr_token'])) {
        $row['applicant_verification_url'] = leave_verification_url((string)$row['applicant_qr_token']);
    }
    return $row;
}

function leave_full_row(PDO $pdo, int $id, array $currentUser): ?array {
    $token = leave_ensure_application_qr_token($pdo, $id);
    $stmt = $pdo->prepare('SELECT * FROM leave_applications WHERE id = ? LIMIT 1');
    $stmt->execute([$id]);
    $row = $stmt->fetch(PDO::FETCH_ASSOC);
    if (!$row) return null;
    $row['applicant_qr_token'] = $token;
    $public = leave_public_row($pdo, $row, $currentUser);
    $route = $pdo->prepare('SELECT * FROM leave_routes WHERE leave_id = ? ORDER BY signed_at ASC, id ASC');
    $route->execute([$id]);
    $history = $route->fetchAll(PDO::FETCH_ASSOC);
    foreach ($history as &$event) {
        $event['id'] = (int)$event['id'];
        $event['actor_user_id'] = (int)$event['actor_user_id'];
        $event['to_user_id'] = $event['to_user_id'] !== null ? (int)$event['to_user_id'] : null;
        $routeToken = leave_clean($event['qr_token'] ?? '', 64);
        if ($routeToken === '') $routeToken = leave_ensure_route_qr_token($pdo, (int)$event['id']);
        $event['qr_token'] = $routeToken;
        $event['verification_url'] = leave_verification_url($routeToken);
    }
    $public['history'] = $history;
    return $public;
}

function leave_stage_actions(string $stage): array {
    return match ($stage) {
        'hod' => ['recommend', 'return', 'reject'],
        'junior_hr' => ['verify', 'return', 'reject'],
        'registrar' => ['recommend', 'return', 'reject'],
        'senior_hr' => ['recommend', 'return', 'reject'],
        'dvc1' => ['approve', 'return', 'reject'],
        default => [],
    };
}

function leave_next_stage(string $stage): ?string {
    return match ($stage) {
        'hod' => 'junior_hr',
        'junior_hr' => 'registrar',
        'registrar' => 'senior_hr',
        'senior_hr' => 'dvc1',
        default => null,
    };
}

function leave_return_stage(string $stage): ?string {
    return match ($stage) {
        'registrar' => 'junior_hr',
        'senior_hr' => 'registrar',
        'dvc1' => 'senior_hr',
        default => null,
    };
}

function leave_is_employee_role(string $role): bool {
    $n = leave_norm($role);
    if ($n === '') return false;
    if (str_contains($n, 'STUDENT') || str_contains($n, 'LEARNER')) return false;
    foreach (['STAFF', 'ADMIN', 'MODERATOR', 'EDITOR', 'EMPLOYEE', 'LECTURER'] as $key) {
        if (str_contains($n, $key)) return true;
    }
    return false;
}

function leave_hr_access(PDO $pdo, array $user): array {
    $profile = leave_official_profile($pdo, $user);
    $stage = leave_position_stage($profile['position']);
    $institutionWide = ['senior_hr', 'dvc1', 'dvc2', 'cfo', 'ceo', 'cao', 'coo'];
    $enabled = $stage === 'junior_hr' || in_array($stage, $institutionWide, true);
    $labels = [
        'junior_hr' => 'Junior HR',
        'senior_hr' => 'Senior HR',
        'dvc1' => 'DVC1',
        'dvc2' => 'DVC2',
        'cfo' => 'CFO',
        'ceo' => 'CEO',
        'cao' => 'CAO',
        'coo' => 'COO',
    ];
    $allCampuses = in_array($stage, $institutionWide, true);
    return [
        'enabled' => $enabled,
        'stage' => $stage,
        'label' => $labels[$stage] ?? '',
        'scope' => $allCampuses ? 'all_campuses' : ($stage === 'junior_hr' ? 'campus' : 'none'),
        'campus' => $profile['campus'],
        'can_adjust' => $enabled,
        'can_suspend' => $stage === 'dvc1',
        'can_restore' => $stage === 'dvc1',
        'executive_access' => $allCampuses,
    ];
}

function leave_employee_start_date(PDO $pdo, int $userId, int $year): DateTimeImmutable {
    $jan = new DateTimeImmutable(sprintf('%04d-01-01', $year));
    if (!in_array('created_at', leave_columns($pdo, 'users'), true)) return $jan;
    $stmt = $pdo->prepare('SELECT created_at FROM users WHERE id = ? LIMIT 1');
    $stmt->execute([$userId]);
    $raw = $stmt->fetchColumn();
    if (!$raw) return $jan;
    try {
        $created = new DateTimeImmutable((string)$raw);
        if ((int)$created->format('Y') > $year) return new DateTimeImmutable(sprintf('%04d-12-01', $year));
        if ((int)$created->format('Y') < $year) return $jan;
        return new DateTimeImmutable($created->format('Y-m-01'));
    } catch (Throwable $e) {
        return $jan;
    }
}

function leave_months_inclusive(DateTimeImmutable $startMonth, DateTimeImmutable $endMonth): int {
    $startIndex = ((int)$startMonth->format('Y')) * 12 + (int)$startMonth->format('n');
    $endIndex = ((int)$endMonth->format('Y')) * 12 + (int)$endMonth->format('n');
    return max(0, $endIndex - $startIndex + 1);
}

function leave_sync_annual_balance(PDO $pdo, int $userId, ?int $year = null): array {
    $year = $year ?: (int)date('Y');
    $rate = 2.0;
    $currentYear = (int)date('Y');
    $isFutureYear = $year > $currentYear;
    $nowMonth = new DateTimeImmutable(date('Y-m-01'));
    if ($year < $currentYear) {
        $nowMonth = new DateTimeImmutable(sprintf('%04d-12-01', $year));
    } elseif ($isFutureYear) {
        // Do not pre-credit future months. A future leave year starts at zero
        // and will accrue 2 days per month when that calendar year arrives.
        $nowMonth = new DateTimeImmutable(sprintf('%04d-01-01', $year));
    }
    $startMonth = leave_employee_start_date($pdo, $userId, $year);
    if ((int)$startMonth->format('Y') !== $year) $startMonth = new DateTimeImmutable(sprintf('%04d-01-01', $year));
    if ($startMonth > $nowMonth) $startMonth = $nowMonth;

    $stmt = $pdo->prepare("SELECT * FROM leave_balances WHERE user_id = ? AND leave_type = 'annual' AND leave_year = ? LIMIT 1");
    $stmt->execute([$userId, $year]);
    $row = $stmt->fetch(PDO::FETCH_ASSOC);

    if (!$row) {
        $months = $isFutureYear ? 0 : leave_months_inclusive($startMonth, $nowMonth);
        $entitlement = $months * $rate;
        $insert = $pdo->prepare("INSERT INTO leave_balances (
            user_id, leave_type, leave_year, entitlement_days, carried_forward_days, used_days,
            adjustment_days, accrual_rate_month, accrual_started_on, last_accrual_month, updated_at
        ) VALUES (?, 'annual', ?, ?, 0, 0, 0, ?, ?, ?, CURRENT_TIMESTAMP) RETURNING *");
        $lastAccrual = $isFutureYear ? null : $nowMonth->format('Y-m-d');
        $insert->execute([$userId, $year, $entitlement, $rate, $startMonth->format('Y-m-d'), $lastAccrual]);
        $row = $insert->fetch(PDO::FETCH_ASSOC);
    } else {
        $lastRaw = leave_clean($row['last_accrual_month'] ?? '', 20);
        $currentRate = isset($row['accrual_rate_month']) ? (float)$row['accrual_rate_month'] : $rate;
        if ($isFutureYear) {
            // Existing future-year rows may contain manual carry-forward or HR
            // adjustments, but entitlement must not accrue before that year.
            $currentRate = $currentRate > 0 ? $currentRate : $rate;
        }
        if ($currentRate <= 0) $currentRate = $rate;
        if (!$isFutureYear && $lastRaw === '') {
            $expected = leave_months_inclusive($startMonth, $nowMonth) * $currentRate;
            $existingEntitlement = (float)$row['entitlement_days'];
            $newEntitlement = max($existingEntitlement, $expected);
            $update = $pdo->prepare("UPDATE leave_balances SET entitlement_days = ?, accrual_rate_month = ?, accrual_started_on = COALESCE(accrual_started_on, ?), last_accrual_month = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ? RETURNING *");
            $update->execute([$newEntitlement, $currentRate, $startMonth->format('Y-m-d'), $nowMonth->format('Y-m-d'), (int)$row['id']]);
            $row = $update->fetch(PDO::FETCH_ASSOC);
        } elseif (!$isFutureYear) {
            try { $last = new DateTimeImmutable(substr($lastRaw, 0, 10)); } catch (Throwable $e) { $last = $nowMonth; }
            $last = new DateTimeImmutable($last->format('Y-m-01'));
            $diff = leave_months_inclusive($last->modify('+1 month'), $nowMonth);
            if ($diff > 0 && $last < $nowMonth) {
                $add = $diff * $currentRate;
                $update = $pdo->prepare("UPDATE leave_balances SET entitlement_days = entitlement_days + ?, last_accrual_month = ?, accrual_rate_month = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ? RETURNING *");
                $update->execute([$add, $nowMonth->format('Y-m-d'), $currentRate, (int)$row['id']]);
                $row = $update->fetch(PDO::FETCH_ASSOC);
            }
        }
    }

    $entitlement = (float)($row['entitlement_days'] ?? 0);
    $carry = (float)($row['carried_forward_days'] ?? 0);
    $used = (float)($row['used_days'] ?? 0);
    $adjustment = (float)($row['adjustment_days'] ?? 0);
    $available = $entitlement + $carry + $adjustment - $used;
    return [
        'id' => (int)$row['id'],
        'user_id' => $userId,
        'leave_type' => 'annual',
        'leave_year' => $year,
        'entitlement_days' => $entitlement,
        'carried_forward_days' => $carry,
        'used_days' => $used,
        'adjustment_days' => $adjustment,
        'accrual_rate_month' => (float)($row['accrual_rate_month'] ?? 2),
        'accrual_started_on' => $row['accrual_started_on'] ?? $startMonth->format('Y-m-d'),
        'last_accrual_month' => $row['last_accrual_month'] ?? null,
        'available_days' => $available,
        'updated_at' => $row['updated_at'] ?? null,
    ];
}

function leave_employee_user(PDO $pdo, int $userId): ?array {
    $columns = leave_columns($pdo, 'users');
    $select = ['id', 'full_name', 'email', 'role', 'campus', 'school', 'program'];
    foreach (['phone', 'nrc', 'created_at', 'profile_picture', 'account_status', 'suspended_at', 'suspended_by_user_id', 'suspension_reason'] as $field) {
        if (in_array($field, $columns, true)) $select[] = '"' . $field . '"';
    }
    $stmt = $pdo->prepare('SELECT ' . implode(', ', $select) . ' FROM users WHERE id = ? LIMIT 1');
    $stmt->execute([$userId]);
    $row = $stmt->fetch(PDO::FETCH_ASSOC);
    if (!$row || !leave_is_employee_role((string)($row['role'] ?? ''))) return null;
    $row['id'] = (int)$row['id'];
    return $row;
}

function leave_assert_hr_employee_scope(PDO $pdo, array $hrUser, int $targetUserId): array {
    $access = leave_hr_access($pdo, $hrUser);
    if (!$access['enabled']) leave_json(['detail' => 'Only authorised HR or executive officers can manage employee leave balances.'], 403);
    $employee = leave_employee_user($pdo, $targetUserId);
    if (!$employee) leave_json(['detail' => 'Employee account not found.'], 404);
    if ($access['stage'] === 'junior_hr' && leave_campus_key((string)$employee['campus']) !== leave_campus_key((string)$access['campus'])) {
        leave_json(['detail' => 'Junior HR may manage leave balances only for employees at the same campus.'], 403);
    }
    return $employee;
}

function leave_employee_rows(PDO $pdo, array $hrUser): array {
    $access = leave_hr_access($pdo, $hrUser);
    if (!$access['enabled']) leave_json(['detail' => 'Only authorised HR or executive officers can open the HR leave ledger.'], 403);

    $columns = leave_columns($pdo, 'users');
    $select = ['id', 'full_name', 'email', 'role', 'campus', 'school', 'program'];
    foreach (['phone', 'nrc', 'created_at', 'profile_picture', 'account_status', 'suspended_at', 'suspended_by_user_id', 'suspension_reason'] as $field) {
        if (in_array($field, $columns, true)) $select[] = '"' . $field . '"';
    }
    $stmt = $pdo->query('SELECT ' . implode(', ', $select) . ' FROM users ORDER BY full_name ASC, id ASC');
    $rows = [];
    $year = (int)date('Y');
    foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $employee) {
        if (!leave_is_employee_role((string)($employee['role'] ?? ''))) continue;
        if ($access['stage'] === 'junior_hr' && leave_campus_key((string)($employee['campus'] ?? '')) !== leave_campus_key((string)$access['campus'])) continue;
        $userId = (int)$employee['id'];
        $balance = leave_sync_annual_balance($pdo, $userId, $year);

        $pendingStmt = $pdo->prepare("SELECT COUNT(*) FROM leave_applications WHERE applicant_user_id = ? AND status IN ('pending','returned')");
        $pendingStmt->execute([$userId]);
        $approvedStmt = $pdo->prepare("SELECT COUNT(*) FROM leave_applications WHERE applicant_user_id = ? AND status = 'approved' AND EXTRACT(YEAR FROM start_date) = ?");
        $approvedStmt->execute([$userId, $year]);

        $rows[] = [
            'user_id' => $userId,
            'full_name' => leave_clean($employee['full_name'] ?? '', 255),
            'email' => leave_clean($employee['email'] ?? '', 255),
            'phone' => leave_clean($employee['phone'] ?? '', 120),
            'nrc' => leave_clean($employee['nrc'] ?? '', 120),
            'role' => leave_clean($employee['role'] ?? '', 80),
            'campus' => leave_clean($employee['campus'] ?? '', 180),
            'school' => leave_clean($employee['school'] ?? '', 180),
            'department' => leave_clean($employee['program'] ?? '', 180),
            'profile_picture' => leave_clean($employee['profile_picture'] ?? '', 500),
            'account_status' => leave_clean($employee['account_status'] ?? 'active', 30) ?: 'active',
            'suspended_at' => $employee['suspended_at'] ?? null,
            'suspended_by_user_id' => isset($employee['suspended_by_user_id']) && $employee['suspended_by_user_id'] !== null ? (int)$employee['suspended_by_user_id'] : null,
            'suspension_reason' => leave_clean($employee['suspension_reason'] ?? '', 3000),
            'pending_leave_count' => (int)$pendingStmt->fetchColumn(),
            'approved_leave_count_year' => (int)$approvedStmt->fetchColumn(),
            'balance' => $balance,
        ];
    }
    return $rows;
}
