<?php
// api/performance_common.php
require_once __DIR__ . '/session_auth.php';

function rvu_perf_json(array $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);
    exit;
}

function rvu_perf_input(): array {
    $raw = file_get_contents('php://input');
    if ($raw === false || trim($raw) === '') return [];
    $decoded = json_decode($raw, true);
    return is_array($decoded) ? $decoded : [];
}

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

function rvu_perf_require_user(PDO $pdo): array {
    $user = rvu_current_user($pdo);
    if (!$user) rvu_perf_json(['detail' => 'Authentication required.'], 401);
    return $user;
}

function rvu_perf_require_admin(array $user): void {
    if (!rvu_perf_role_is_admin((string)($user['role'] ?? ''))) {
        rvu_perf_json(['detail' => 'Administrator access is required.'], 403);
    }
}

function rvu_perf_ensure_tables(PDO $pdo): void {
    $pdo->exec("CREATE TABLE IF NOT EXISTS student_performance_hod_assignments (
        id BIGSERIAL PRIMARY KEY,
        department_id BIGINT NOT NULL,
        department_name VARCHAR(255) NOT NULL,
        campus VARCHAR(255) NOT NULL,
        school VARCHAR(255) NULL,
        staff_user_id BIGINT NOT NULL,
        assigned_by BIGINT NOT NULL,
        is_active BOOLEAN NOT NULL DEFAULT TRUE,
        assigned_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
        ended_at TIMESTAMPTZ NULL
    )");

    $pdo->exec("CREATE UNIQUE INDEX IF NOT EXISTS uq_active_hod_department_campus
        ON student_performance_hod_assignments (campus, department_id)
        WHERE is_active = TRUE");
    $pdo->exec("CREATE INDEX IF NOT EXISTS idx_hod_staff_user
        ON student_performance_hod_assignments (staff_user_id)");

    $pdo->exec("CREATE TABLE IF NOT EXISTS student_promotion_performance (
        id BIGSERIAL PRIMARY KEY,
        department_id BIGINT NOT NULL,
        department_name VARCHAR(255) NOT NULL,
        campus VARCHAR(255) NOT NULL,
        school VARCHAR(255) NULL,
        academic_year VARCHAR(20) NOT NULL,
        exam_session VARCHAR(150) NOT NULL,
        level_name VARCHAR(120) NOT NULL,
        sat_count INTEGER NOT NULL CHECK (sat_count >= 0),
        passed_count INTEGER NOT NULL CHECK (passed_count >= 0),
        notes TEXT NULL,
        hod_assignment_id BIGINT NOT NULL,
        submitted_by BIGINT NOT NULL,
        submitted_by_name VARCHAR(255) NOT NULL,
        created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
        CHECK (passed_count <= sat_count)
    )");

    $pdo->exec("CREATE UNIQUE INDEX IF NOT EXISTS uq_promotion_performance_record
        ON student_promotion_performance
        (campus, department_id, academic_year, exam_session, level_name)");
    $pdo->exec("CREATE INDEX IF NOT EXISTS idx_promotion_perf_department
        ON student_promotion_performance (department_id, campus)");
    $pdo->exec("CREATE INDEX IF NOT EXISTS idx_promotion_perf_year
        ON student_promotion_performance (academic_year)");
}

function rvu_perf_department(PDO $pdo, int $departmentId): ?array {
    if ($departmentId <= 0) return null;
    $stmt = $pdo->prepare('SELECT id, name FROM departments WHERE id = ? LIMIT 1');
    $stmt->execute([$departmentId]);
    $row = $stmt->fetch(PDO::FETCH_ASSOC);
    return $row ?: null;
}

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

function rvu_perf_active_assignment_for_user(PDO $pdo, int $userId, ?int $departmentId = null): ?array {
    $sql = "SELECT a.*
            FROM student_performance_hod_assignments a
            WHERE a.staff_user_id = ? AND a.is_active = TRUE";
    $params = [$userId];
    if ($departmentId !== null) {
        $sql .= ' AND a.department_id = ?';
        $params[] = $departmentId;
    }
    $sql .= ' ORDER BY a.assigned_at DESC LIMIT 1';
    $stmt = $pdo->prepare($sql);
    $stmt->execute($params);
    $row = $stmt->fetch(PDO::FETCH_ASSOC);
    return $row ?: null;
}

function rvu_perf_user_assignments(PDO $pdo, int $userId): array {
    $stmt = $pdo->prepare("SELECT a.*
                           FROM student_performance_hod_assignments a
                           WHERE a.staff_user_id = ? AND a.is_active = TRUE
                           ORDER BY a.campus, a.department_name");
    $stmt->execute([$userId]);
    return $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
}


function rvu_perf_assignment_matches_user(array $assignment, array $user): bool {
    $userCampus = trim((string)($user['campus'] ?? ''));
    $userDepartment = trim((string)($user['program'] ?? ''));
    return $userCampus !== ''
        && $userDepartment !== ''
        && strcasecmp($userCampus, (string)$assignment['campus']) === 0
        && strcasecmp($userDepartment, (string)$assignment['department_name']) === 0;
}

function rvu_perf_assignment_public(array $row): array {
    return [
        'id' => (int)$row['id'],
        'department_id' => (int)$row['department_id'],
        'department_name' => (string)$row['department_name'],
        'campus' => (string)$row['campus'],
        'school' => (string)($row['school'] ?? ''),
        'staff_user_id' => (int)$row['staff_user_id'],
        'staff_name' => (string)($row['staff_name'] ?? ''),
        'staff_email' => (string)($row['staff_email'] ?? ''),
        'assigned_by' => (int)$row['assigned_by'],
        'assigned_by_name' => (string)($row['assigned_by_name'] ?? ''),
        'is_active' => (bool)$row['is_active'],
        'assigned_at' => (string)$row['assigned_at'],
        'ended_at' => $row['ended_at'] ?? null,
    ];
}

function rvu_perf_clean_text(mixed $value, int $max = 255): string {
    $text = trim((string)$value);
    if ($text === '') return '';
    if (strlen($text) > $max) $text = substr($text, 0, $max);
    return $text;
}
