<?php
// api/promotion_performance.php
require_once __DIR__ . '/../config/database.php';
require_once __DIR__ . '/performance_common.php';

try {
    rvu_perf_ensure_tables($pdo);
    $user = rvu_perf_require_user($pdo);
    $method = strtoupper($_SERVER['REQUEST_METHOD'] ?? 'GET');
    $input = rvu_perf_input();
    $isAdmin = rvu_perf_role_is_admin((string)($user['role'] ?? ''));

    if ($method === 'GET') {
        $departmentId = (int)($_GET['department_id'] ?? 0);
        $campus = rvu_perf_clean_text($_GET['campus'] ?? '', 255);
        $year = rvu_perf_clean_text($_GET['academic_year'] ?? '', 20);
        $session = rvu_perf_clean_text($_GET['exam_session'] ?? '', 150);

        $where = [];
        $params = [];

        if (!$isAdmin) {
            $assignments = array_values(array_filter(
                rvu_perf_user_assignments($pdo, (int)$user['id']),
                fn(array $assignment): bool => rvu_perf_assignment_matches_user($assignment, $user)
            ));
            if (empty($assignments)) {
                rvu_perf_json(['detail' => 'Only an active Head of Department can access promotion performance records.'], 403);
            }
            $clauses = [];
            foreach ($assignments as $assignment) {
                $clauses[] = '(p.department_id = ? AND p.campus = ?)';
                $params[] = (int)$assignment['department_id'];
                $params[] = (string)$assignment['campus'];
            }
            $where[] = '(' . implode(' OR ', $clauses) . ')';
        }

        if ($departmentId > 0) {
            $where[] = 'p.department_id = ?';
            $params[] = $departmentId;
        }
        if ($campus !== '') {
            $where[] = 'p.campus = ?';
            $params[] = $campus;
        }
        if ($year !== '') {
            $where[] = 'p.academic_year = ?';
            $params[] = $year;
        }
        if ($session !== '') {
            $where[] = 'p.exam_session = ?';
            $params[] = $session;
        }

        $sql = "SELECT p.*,
                       (p.sat_count - p.passed_count) AS failed_count,
                       CASE WHEN p.sat_count > 0 THEN ROUND((p.passed_count::numeric / p.sat_count::numeric) * 100, 2) ELSE 0 END AS pass_rate
                FROM student_promotion_performance p";
        if ($where) $sql .= ' WHERE ' . implode(' AND ', $where);
        $sql .= ' ORDER BY p.academic_year DESC, p.campus, p.department_name, p.exam_session, p.level_name';

        $stmt = $pdo->prepare($sql);
        $stmt->execute($params);
        $rows = $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];

        $totalSat = 0;
        $totalPassed = 0;
        foreach ($rows as &$row) {
            $row['id'] = (int)$row['id'];
            $row['department_id'] = (int)$row['department_id'];
            $row['sat_count'] = (int)$row['sat_count'];
            $row['passed_count'] = (int)$row['passed_count'];
            $row['failed_count'] = (int)$row['failed_count'];
            $row['pass_rate'] = (float)$row['pass_rate'];
            $row['submitted_by'] = (int)$row['submitted_by'];
            $row['hod_assignment_id'] = (int)$row['hod_assignment_id'];
            $totalSat += $row['sat_count'];
            $totalPassed += $row['passed_count'];
        }
        unset($row);

        $totalFailed = $totalSat - $totalPassed;
        $passRate = $totalSat > 0 ? round(($totalPassed / $totalSat) * 100, 2) : 0.0;

        rvu_perf_json([
            'data' => $rows,
            'summary' => [
                'records' => count($rows),
                'sat' => $totalSat,
                'passed' => $totalPassed,
                'failed' => $totalFailed,
                'pass_rate' => $passRate,
            ],
            'is_admin' => $isAdmin,
        ]);
    }

    if ($method === 'POST') {
        // Even administrators cannot enter department results unless they are the assigned HOD.
        $departmentId = (int)($input['department_id'] ?? 0);
        $assignment = rvu_perf_active_assignment_for_user($pdo, (int)$user['id'], $departmentId);
        if (!$assignment || !rvu_perf_assignment_matches_user($assignment, $user)) {
            rvu_perf_json(['detail' => 'Only the currently assigned HOD who still belongs to this campus and department can submit promotion examination performance.'], 403);
        }

        $academicYear = rvu_perf_clean_text($input['academic_year'] ?? '', 20);
        $examSession = rvu_perf_clean_text($input['exam_session'] ?? '', 150);
        $levelName = rvu_perf_clean_text($input['level_name'] ?? '', 120);
        $sat = (int)($input['sat_count'] ?? -1);
        $passed = (int)($input['passed_count'] ?? -1);
        $notes = trim((string)($input['notes'] ?? ''));

        if ($academicYear === '' || $examSession === '' || $levelName === '') {
            rvu_perf_json(['detail' => 'Academic year, examination session and level are required.'], 422);
        }
        if ($sat < 0 || $passed < 0) rvu_perf_json(['detail' => 'Student counts cannot be negative.'], 422);
        if ($passed > $sat) rvu_perf_json(['detail' => 'Students passed cannot be greater than students who sat for the examination.'], 422);

        try {
            $stmt = $pdo->prepare("INSERT INTO student_promotion_performance
                (department_id, department_name, campus, school, academic_year, exam_session, level_name,
                 sat_count, passed_count, notes, hod_assignment_id, submitted_by, submitted_by_name)
                VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
                RETURNING id, created_at");
            $stmt->execute([
                (int)$assignment['department_id'],
                (string)$assignment['department_name'],
                (string)$assignment['campus'],
                (string)($assignment['school'] ?? '') ?: null,
                $academicYear,
                $examSession,
                $levelName,
                $sat,
                $passed,
                $notes === '' ? null : $notes,
                (int)$assignment['id'],
                (int)$user['id'],
                (string)($user['full_name'] ?? 'HOD'),
            ]);
            $created = $stmt->fetch(PDO::FETCH_ASSOC);
        } catch (PDOException $e) {
            if ($e->getCode() === '23505') {
                rvu_perf_json(['detail' => 'A performance record already exists for this campus, department, academic year, examination session and level. Edit the existing record instead.'], 409);
            }
            throw $e;
        }

        rvu_perf_json([
            'message' => 'Promotion examination performance recorded successfully.',
            'id' => (int)$created['id'],
            'created_at' => $created['created_at'],
        ], 201);
    }

    if ($method === 'PUT') {
        $id = (int)($_GET['id'] ?? $input['id'] ?? 0);
        if ($id <= 0) rvu_perf_json(['detail' => 'A valid performance record id is required.'], 422);

        $find = $pdo->prepare('SELECT * FROM student_promotion_performance WHERE id = ? LIMIT 1');
        $find->execute([$id]);
        $existing = $find->fetch(PDO::FETCH_ASSOC);
        if (!$existing) rvu_perf_json(['detail' => 'Performance record not found.'], 404);

        $assignment = rvu_perf_active_assignment_for_user($pdo, (int)$user['id'], (int)$existing['department_id']);
        if (!$assignment || !rvu_perf_assignment_matches_user($assignment, $user) || strcasecmp((string)$assignment['campus'], (string)$existing['campus']) !== 0) {
            rvu_perf_json(['detail' => 'Only the currently assigned HOD can edit this performance record.'], 403);
        }

        $academicYear = rvu_perf_clean_text($input['academic_year'] ?? $existing['academic_year'], 20);
        $examSession = rvu_perf_clean_text($input['exam_session'] ?? $existing['exam_session'], 150);
        $levelName = rvu_perf_clean_text($input['level_name'] ?? $existing['level_name'], 120);
        $sat = isset($input['sat_count']) ? (int)$input['sat_count'] : (int)$existing['sat_count'];
        $passed = isset($input['passed_count']) ? (int)$input['passed_count'] : (int)$existing['passed_count'];
        $notes = array_key_exists('notes', $input) ? trim((string)$input['notes']) : (string)($existing['notes'] ?? '');

        if ($sat < 0 || $passed < 0 || $passed > $sat) {
            rvu_perf_json(['detail' => 'Counts are invalid. Passed must be between 0 and the number who sat.'], 422);
        }

        try {
            $stmt = $pdo->prepare("UPDATE student_promotion_performance
                SET academic_year = ?, exam_session = ?, level_name = ?, sat_count = ?, passed_count = ?, notes = ?,
                    submitted_by = ?, submitted_by_name = ?, hod_assignment_id = ?, updated_at = CURRENT_TIMESTAMP
                WHERE id = ?");
            $stmt->execute([
                $academicYear, $examSession, $levelName, $sat, $passed,
                $notes === '' ? null : $notes,
                (int)$user['id'], (string)($user['full_name'] ?? 'HOD'), (int)$assignment['id'], $id,
            ]);
        } catch (PDOException $e) {
            if ($e->getCode() === '23505') {
                rvu_perf_json(['detail' => 'Another performance record already uses this year, session and level.'], 409);
            }
            throw $e;
        }

        rvu_perf_json(['message' => 'Performance record updated successfully.']);
    }

    if ($method === 'DELETE') {
        $id = (int)($_GET['id'] ?? $input['id'] ?? 0);
        if ($id <= 0) rvu_perf_json(['detail' => 'A valid performance record id is required.'], 422);

        $find = $pdo->prepare('SELECT department_id, campus FROM student_promotion_performance WHERE id = ? LIMIT 1');
        $find->execute([$id]);
        $existing = $find->fetch(PDO::FETCH_ASSOC);
        if (!$existing) rvu_perf_json(['detail' => 'Performance record not found.'], 404);

        $allowed = $isAdmin;
        if (!$allowed) {
            $assignment = rvu_perf_active_assignment_for_user($pdo, (int)$user['id'], (int)$existing['department_id']);
            $allowed = $assignment && strcasecmp((string)$assignment['campus'], (string)$existing['campus']) === 0;
        }
        if (!$allowed) rvu_perf_json(['detail' => 'You cannot delete this performance record.'], 403);

        $pdo->prepare('DELETE FROM student_promotion_performance WHERE id = ?')->execute([$id]);
        rvu_perf_json(['message' => 'Performance record deleted.']);
    }

    rvu_perf_json(['detail' => 'Unsupported request.'], 405);
} catch (Throwable $e) {
    rvu_perf_json(['detail' => 'Student performance server error: ' . $e->getMessage()], 500);
}
