<?php

/**
 * Progress Analytics — query & aggregation library (single source of truth).
 *
 * Every analytics surface (the admin page, the teacher-portal tab, the
 * parent-portal child view, and all AJAX endpoints) builds its numbers here so
 * they can never disagree. Endpoints are thin: normalise a scope, call a
 * function below, echo JSON.
 *
 * THE SCOPE CONTRACT — one associative array describes what to look at:
 *   level          'school' | 'class' | 'student'
 *   academic_year  string   ('' = every year, e.g. for a cross-year trend)
 *   term           string   ('' = every term)
 *   classes        string[] (class names; [] = all classes in scope)
 *   student_id     int      (required when level = 'student')
 *   subjects       int[]    (subject ids; [] = all subjects)
 *   sex            string   ('Male' | 'Female' | '' = any)
 *   pass_mark      float    (total_score at/above which a result counts as a
 *                            pass; default 50 = grade E on the 'ALL' boundary)
 *   allowed_classes ?string[] role scoping. NULL = unrestricted (admin). An
 *                            array restricts EVERY query to those classes — a
 *                            tutor's own classes, or a single parent's child's
 *                            class — enforced server-side, never trusted from
 *                            the request.
 *
 * class_name is NULL for score rows written before the P0 migration in prior
 * academic years ("Class not recorded"). Those rows are excluded from
 * class-level aggregates and surfaced as an explicit bucket where relevant —
 * they are historical, not erroneous.
 */

if (!function_exists('getDBConnection')) {
    require_once dirname(__DIR__, 2) . '/config.php';
}
require_once dirname(__DIR__, 2) . '/lib/academic_period.php';

const PROGRESS_DEFAULT_PASS_MARK = 50.0;

/** Grade letters counted as a fail when tallying pass rate from stored grades. */
const PROGRESS_FAIL_GRADES = ['W', 'F', 'U'];

/**
 * Sanitise raw request input into a trusted scope array.
 *
 * $allowedClasses (from the caller's own auth context, never the request)
 * intersects the requested classes — a caller can only ever narrow within what
 * their role permits, never widen.
 */
function progressNormalizeScope(array $in, ?array $allowedClasses = null): array
{
    $level = in_array($in['level'] ?? '', ['school', 'class', 'student'], true)
        ? $in['level'] : 'school';

    $classes = [];
    if (!empty($in['classes'])) {
        $raw = is_array($in['classes']) ? $in['classes'] : explode(',', (string) $in['classes']);
        foreach ($raw as $c) {
            $c = trim((string) $c);
            if ($c !== '') { $classes[] = $c; }
        }
    }

    $subjects = [];
    if (!empty($in['subjects'])) {
        $raw = is_array($in['subjects']) ? $in['subjects'] : explode(',', (string) $in['subjects']);
        foreach ($raw as $s) {
            $s = (int) $s;
            if ($s > 0) { $subjects[] = $s; }
        }
    }

    // Role scoping: intersect requested classes with the permitted set.
    if (is_array($allowedClasses)) {
        $allowedClasses = array_values(array_filter(array_map('strval', $allowedClasses), 'strlen'));
        $classes = $classes === []
            ? $allowedClasses
            : array_values(array_intersect($classes, $allowedClasses));
    }

    $sex = in_array($in['sex'] ?? '', ['Male', 'Female'], true) ? $in['sex'] : '';

    $passMark = isset($in['pass_mark']) && is_numeric($in['pass_mark'])
        ? (float) $in['pass_mark'] : PROGRESS_DEFAULT_PASS_MARK;

    return [
        'level'           => $level,
        'academic_year'   => trim((string) ($in['academic_year'] ?? '')),
        'term'            => trim((string) ($in['term'] ?? '')),
        'classes'         => array_values(array_unique($classes)),
        'student_id'      => (int) ($in['student_id'] ?? 0),
        'subjects'        => array_values(array_unique($subjects)),
        'sex'             => $sex,
        'pass_mark'       => $passMark,
        'allowed_classes' => is_array($allowedClasses) ? $allowedClasses : null,
    ];
}

/**
 * Build the shared WHERE fragment for a scope against `scores sc`
 * (optionally joined to `students st`).
 *
 * Returns ['sql' => ' AND ...', 'types' => '...', 'params' => [...]] ready to
 * append after a "WHERE 1=1". Every value is a bound parameter — no
 * interpolation. When $requireClass is true, rows with a NULL/'' class_name are
 * excluded (class-level aggregates); school-level callers pass false.
 */
function progressScoreFilter(array $scope, bool $requireClass = false, bool $joinStudents = false): array
{
    $sql = '';
    $types = '';
    $params = [];

    // Role scoping is active (allowed_classes set) but the requested class(es)
    // fell entirely outside the permitted set — resolve to NO data rather than
    // letting an empty class list read as "all classes". This is the hard stop
    // that keeps a tutor or parent from widening past their allowed classes.
    if ($scope['allowed_classes'] !== null && $scope['classes'] === []) {
        return ['sql' => ' AND 1=0', 'types' => '', 'params' => []];
    }

    if ($scope['academic_year'] !== '') {
        $sql .= ' AND sc.academic_year = ?';
        $types .= 's';
        $params[] = $scope['academic_year'];
    }
    if ($scope['term'] !== '') {
        $sql .= ' AND sc.term = ?';
        $types .= 's';
        $params[] = $scope['term'];
    }
    if ($scope['student_id'] > 0) {
        $sql .= ' AND sc.student_id = ?';
        $types .= 'i';
        $params[] = $scope['student_id'];
    }
    if ($scope['classes'] !== []) {
        $ph = implode(',', array_fill(0, count($scope['classes']), '?'));
        $sql .= " AND sc.class_name IN ($ph)";
        $types .= str_repeat('s', count($scope['classes']));
        foreach ($scope['classes'] as $c) { $params[] = $c; }
    } elseif ($requireClass) {
        $sql .= " AND sc.class_name IS NOT NULL AND sc.class_name <> ''";
    }
    if ($scope['subjects'] !== []) {
        $ph = implode(',', array_fill(0, count($scope['subjects']), '?'));
        $sql .= " AND sc.subject_id IN ($ph)";
        $types .= str_repeat('i', count($scope['subjects']));
        foreach ($scope['subjects'] as $s) { $params[] = $s; }
    }
    if ($joinStudents && $scope['sex'] !== '') {
        $sql .= ' AND st.sex = ?';
        $types .= 's';
        $params[] = $scope['sex'];
    }

    return ['sql' => $sql, 'types' => $types, 'params' => $params];
}

/** Bind an array of params onto a prepared statement (no-op when empty). */
function progressBind(mysqli_stmt $stmt, string $types, array $params): void
{
    if ($types !== '') {
        $stmt->bind_param($types, ...$params);
    }
}

/**
 * Headline KPIs for the current scope.
 *
 * @return array{
 *   students:int, results:int, avg_score:?float, pass_rate:?float,
 *   pass_mark:float, top_score:?float, low_score:?float,
 *   coverage_students:int, coverage_expected:int, coverage_pct:?float,
 *   unrecorded_results:int
 * }
 */
function progressKpis(mysqli $conn, array $scope): array
{
    $f = progressScoreFilter($scope, false, true);

    $sql = "SELECT
                COUNT(*)                                        AS results,
                COUNT(DISTINCT sc.student_id)                   AS students,
                ROUND(AVG(sc.total_score), 2)                   AS avg_score,
                ROUND(MAX(sc.total_score), 2)                   AS top_score,
                ROUND(MIN(sc.total_score), 2)                   AS low_score,
                SUM(CASE WHEN sc.total_score >= ? THEN 1 ELSE 0 END) AS passes,
                SUM(CASE WHEN sc.class_name IS NULL OR sc.class_name = '' THEN 1 ELSE 0 END) AS unrecorded
            FROM scores sc
            JOIN students st ON st.id = sc.student_id
            WHERE 1=1" . $f['sql'];

    $stmt = $conn->prepare($sql);
    progressBind($stmt, 'd' . $f['types'], array_merge([$scope['pass_mark']], $f['params']));
    $stmt->execute();
    $row = $stmt->get_result()->fetch_assoc() ?: [];
    $stmt->close();

    $results = (int) ($row['results'] ?? 0);
    $passes  = (int) ($row['passes'] ?? 0);

    // Data coverage: of the active students in scope, how many actually have any
    // score in the selected period? A class average over half-entered marks is
    // misleading, so this is surfaced alongside it.
    [$expected, $withScores] = progressCoverage($conn, $scope);

    return [
        'students'           => (int) ($row['students'] ?? 0),
        'results'            => $results,
        'avg_score'          => isset($row['avg_score']) ? (float) $row['avg_score'] : null,
        'pass_rate'          => $results > 0 ? round($passes * 100 / $results, 1) : null,
        'pass_mark'          => $scope['pass_mark'],
        'top_score'          => isset($row['top_score']) ? (float) $row['top_score'] : null,
        'low_score'          => isset($row['low_score']) ? (float) $row['low_score'] : null,
        'coverage_students'  => $withScores,
        'coverage_expected'  => $expected,
        'coverage_pct'       => $expected > 0 ? round($withScores * 100 / $expected, 1) : null,
        'unrecorded_results' => (int) ($row['unrecorded'] ?? 0),
    ];
}

/**
 * Coverage: [expected active students in the class scope, students with >=1
 * score in the period]. Only meaningful when the scope names classes; for an
 * all-classes scope "expected" is every active student.
 *
 * @return array{0:int,1:int}
 */
function progressCoverage(mysqli $conn, array $scope): array
{
    // Expected = active students in the scoped classes (or all active students).
    $wsql = " WHERE status = 'active'";
    $wtypes = '';
    $wparams = [];
    // Mirror the hard stop in progressScoreFilter(): under active role scoping an
    // empty class list means "no permitted class", not "every student".
    if ($scope['allowed_classes'] !== null && $scope['classes'] === []) {
        return [0, 0];
    }
    if ($scope['classes'] !== []) {
        $ph = implode(',', array_fill(0, count($scope['classes']), '?'));
        $wsql .= " AND class IN ($ph)";
        $wtypes .= str_repeat('s', count($scope['classes']));
        foreach ($scope['classes'] as $c) { $wparams[] = $c; }
    }
    if ($scope['student_id'] > 0) {
        $wsql .= ' AND id = ?';
        $wtypes .= 'i';
        $wparams[] = $scope['student_id'];
    }
    if ($scope['sex'] !== '') {
        $wsql .= ' AND sex = ?';
        $wtypes .= 's';
        $wparams[] = $scope['sex'];
    }

    $stmt = $conn->prepare("SELECT COUNT(*) c FROM students" . $wsql);
    progressBind($stmt, $wtypes, $wparams);
    $stmt->execute();
    $expected = (int) ($stmt->get_result()->fetch_assoc()['c'] ?? 0);
    $stmt->close();

    // Students with at least one score in the period+scope.
    $f = progressScoreFilter($scope, false, false);
    $stmt = $conn->prepare(
        "SELECT COUNT(DISTINCT sc.student_id) c FROM scores sc WHERE 1=1" . $f['sql']
    );
    progressBind($stmt, $f['types'], $f['params']);
    $stmt->execute();
    $withScores = (int) ($stmt->get_result()->fetch_assoc()['c'] ?? 0);
    $stmt->close();

    return [$expected, $withScores];
}

/**
 * Average score per academic period, in chronological order — the trend line.
 *
 * Ignores the scope's own term filter (a trend needs every term) but honours
 * year/class/subject/student. When level = 'student', a second series carries
 * the class average for the same periods, so a child can be read against peers.
 *
 * $onlyPeriods (optional) restricts the trend to a whitelist of
 * [academic_year, term] pairs — used by the parent portal to plot only the
 * terms whose report has been published.
 *
 * @return array{labels:string[], series:array<int,array{name:string,data:array<int,?float>}>}
 */
function progressTrend(mysqli $conn, array $scope, ?array $onlyPeriods = null): array
{
    $periods = academicPeriodsInOrder($conn);
    // Respect a year filter if one is set.
    if ($scope['academic_year'] !== '') {
        $periods = array_values(array_filter(
            $periods,
            fn($p) => $p['academic_year'] === $scope['academic_year']
        ));
    }
    // Restrict to a whitelist of periods when given (parent portal: published only).
    if (is_array($onlyPeriods)) {
        $allow = [];
        foreach ($onlyPeriods as $wp) {
            $allow[($wp['academic_year'] ?? '') . '|' . ($wp['term'] ?? '')] = true;
        }
        $periods = array_values(array_filter(
            $periods,
            fn($p) => isset($allow[$p['academic_year'] . '|' . $p['term']])
        ));
    }

    $trendScope = $scope;
    $trendScope['term'] = ''; // a trend spans terms

    $labels = [];
    $primary = [];
    $classAvg = [];
    $wantClassAvg = ($scope['level'] === 'student' && $scope['student_id'] > 0);

    // Class-average comparison uses the student's class(es), not the student.
    $peerScope = $trendScope;
    if ($wantClassAvg) {
        $peerScope['student_id'] = 0;
        if ($peerScope['classes'] === []) {
            $cls = progressStudentClasses($conn, $scope['student_id']);
            $peerScope['classes'] = $cls;
        }
    }

    foreach ($periods as $p) {
        $labels[] = $p['label'];

        $ps = $trendScope;
        $ps['academic_year'] = $p['academic_year'];
        $ps['term'] = $p['term'];
        $primary[] = progressPeriodAverage($conn, $ps);

        if ($wantClassAvg) {
            $pc = $peerScope;
            $pc['academic_year'] = $p['academic_year'];
            $pc['term'] = $p['term'];
            $classAvg[] = progressPeriodAverage($conn, $pc);
        }
    }

    $series = [['name' => $wantClassAvg ? 'This student' : 'Average', 'data' => $primary]];
    if ($wantClassAvg) {
        $series[] = ['name' => 'Class average', 'data' => $classAvg];
    }

    return ['labels' => $labels, 'series' => $series];
}

/** Single-period average total_score for a fully-pinned scope, or null. */
function progressPeriodAverage(mysqli $conn, array $scope): ?float
{
    $f = progressScoreFilter($scope, false, false);
    $stmt = $conn->prepare("SELECT AVG(sc.total_score) a FROM scores sc WHERE 1=1" . $f['sql']);
    progressBind($stmt, $f['types'], $f['params']);
    $stmt->execute();
    $v = $stmt->get_result()->fetch_assoc()['a'] ?? null;
    $stmt->close();
    return $v === null ? null : round((float) $v, 2);
}

/** The distinct classes a student has scores in (fallback to students.class). */
function progressStudentClasses(mysqli $conn, int $studentId): array
{
    $classes = [];
    $stmt = $conn->prepare(
        "SELECT DISTINCT class_name FROM scores
          WHERE student_id = ? AND class_name IS NOT NULL AND class_name <> ''"
    );
    $stmt->bind_param('i', $studentId);
    $stmt->execute();
    $r = $stmt->get_result();
    while ($row = $r->fetch_assoc()) { $classes[] = $row['class_name']; }
    $stmt->close();

    if ($classes === []) {
        $c = academicStudentClass($conn, $studentId);
        if ($c !== '') { $classes[] = $c; }
    }
    return $classes;
}

/**
 * Average per subject, best→worst — the subject-strength bar chart and its
 * table. For a student scope, each subject also carries the class average.
 *
 * @return array<int,array{subject_id:int, subject:string, avg_score:?float,
 *                         class_avg:?float, results:int, pass_rate:?float}>
 */
function progressSubjectBreakdown(mysqli $conn, array $scope): array
{
    $f = progressScoreFilter($scope, false, true);

    $sql = "SELECT su.id AS subject_id, su.subject_name AS subject,
                   ROUND(AVG(sc.total_score), 2) AS avg_score,
                   COUNT(*) AS results,
                   ROUND(SUM(CASE WHEN sc.total_score >= ? THEN 1 ELSE 0 END) * 100 / COUNT(*), 1) AS pass_rate
            FROM scores sc
            JOIN subjects su ON su.id = sc.subject_id
            JOIN students st ON st.id = sc.student_id
            WHERE 1=1" . $f['sql'] . "
            GROUP BY su.id, su.subject_name
            ORDER BY avg_score DESC, su.subject_name ASC";

    $stmt = $conn->prepare($sql);
    progressBind($stmt, 'd' . $f['types'], array_merge([$scope['pass_mark']], $f['params']));
    $stmt->execute();
    $res = $stmt->get_result();
    $rows = [];
    while ($row = $res->fetch_assoc()) {
        $rows[] = [
            'subject_id' => (int) $row['subject_id'],
            'subject'    => $row['subject'],
            'avg_score'  => isset($row['avg_score']) ? (float) $row['avg_score'] : null,
            'class_avg'  => null,
            'results'    => (int) $row['results'],
            'pass_rate'  => isset($row['pass_rate']) ? (float) $row['pass_rate'] : null,
        ];
    }
    $stmt->close();

    // For a single student, overlay the peer (class) average per subject.
    if ($scope['level'] === 'student' && $scope['student_id'] > 0 && $rows !== []) {
        $peer = $scope;
        $peer['student_id'] = 0;
        if ($peer['classes'] === []) {
            $peer['classes'] = progressStudentClasses($conn, $scope['student_id']);
        }
        $pf = progressScoreFilter($peer, true, false);
        $stmt = $conn->prepare(
            "SELECT sc.subject_id, ROUND(AVG(sc.total_score),2) a
               FROM scores sc WHERE 1=1" . $pf['sql'] . " GROUP BY sc.subject_id"
        );
        progressBind($stmt, $pf['types'], $pf['params']);
        $stmt->execute();
        $res = $stmt->get_result();
        $map = [];
        while ($row = $res->fetch_assoc()) { $map[(int) $row['subject_id']] = (float) $row['a']; }
        $stmt->close();
        foreach ($rows as &$r) {
            $r['class_avg'] = $map[$r['subject_id']] ?? null;
        }
        unset($r);
    }

    return $rows;
}

/**
 * Grade distribution (A, B, … W) for the scope — the stacked/bar chart.
 * Uses the stored `grade` column, so it always agrees with the report cards.
 *
 * @return array{labels:string[], counts:int[], total:int}
 */
function progressGradeDistribution(mysqli $conn, array $scope): array
{
    $f = progressScoreFilter($scope, false, true);
    $stmt = $conn->prepare(
        "SELECT COALESCE(NULLIF(sc.grade,''),'?') AS grade, COUNT(*) c
           FROM scores sc
           JOIN students st ON st.id = sc.student_id
          WHERE 1=1" . $f['sql'] . "
          GROUP BY grade"
    );
    progressBind($stmt, $f['types'], $f['params']);
    $stmt->execute();
    $res = $stmt->get_result();

    $map = [];
    $total = 0;
    while ($row = $res->fetch_assoc()) {
        $map[$row['grade']] = (int) $row['c'];
        $total += (int) $row['c'];
    }
    $stmt->close();

    // Stable A→F/W ordering; unknown grades appended.
    $order = ['A', 'B', 'C', 'D', 'E', 'F', 'W', 'U', '?'];
    $labels = [];
    $counts = [];
    foreach ($order as $g) {
        if (isset($map[$g])) { $labels[] = $g; $counts[] = $map[$g]; unset($map[$g]); }
    }
    foreach ($map as $g => $c) { $labels[] = $g; $counts[] = $c; }

    return ['labels' => $labels, 'counts' => $counts, 'total' => $total];
}

/**
 * The DataTable rows beneath the charts. Shape depends on the scope level:
 *   school  → one row per class
 *   class   → one row per student in the class
 *   student → one row per subject
 *
 * @return array{columns:array<int,array{key:string,label:string}>, rows:array<int,array<string,mixed>>}
 */
function progressTableRows(mysqli $conn, array $scope): array
{
    switch ($scope['level']) {
        case 'student':
            return progressStudentSubjectRows($conn, $scope);
        case 'class':
            return progressClassStudentRows($conn, $scope);
        default:
            return progressSchoolClassRows($conn, $scope);
    }
}

/** School view: per-class summary. */
function progressSchoolClassRows(mysqli $conn, array $scope): array
{
    $f = progressScoreFilter($scope, false, true);
    $sql = "SELECT
                CASE WHEN sc.class_name IS NULL OR sc.class_name = ''
                     THEN '(Class not recorded)' ELSE sc.class_name END AS class_name,
                COUNT(DISTINCT sc.student_id) AS students,
                COUNT(*)                      AS results,
                ROUND(AVG(sc.total_score),2)  AS avg_score,
                ROUND(SUM(CASE WHEN sc.total_score >= ? THEN 1 ELSE 0 END)*100/COUNT(*),1) AS pass_rate,
                ROUND(MAX(sc.total_score),1)  AS top_score
            FROM scores sc
            JOIN students st ON st.id = sc.student_id
            WHERE 1=1" . $f['sql'] . "
            GROUP BY class_name
            ORDER BY avg_score DESC";
    $stmt = $conn->prepare($sql);
    progressBind($stmt, 'd' . $f['types'], array_merge([$scope['pass_mark']], $f['params']));
    $stmt->execute();
    $res = $stmt->get_result();
    $rows = [];
    while ($row = $res->fetch_assoc()) {
        $rows[] = [
            'class_name' => $row['class_name'],
            'students'   => (int) $row['students'],
            'results'    => (int) $row['results'],
            'avg_score'  => $row['avg_score'] !== null ? (float) $row['avg_score'] : null,
            'pass_rate'  => $row['pass_rate'] !== null ? (float) $row['pass_rate'] : null,
            'top_score'  => $row['top_score'] !== null ? (float) $row['top_score'] : null,
        ];
    }
    $stmt->close();

    return [
        'columns' => [
            ['key' => 'class_name', 'label' => 'Class'],
            ['key' => 'students',   'label' => 'Students'],
            ['key' => 'results',    'label' => 'Results'],
            ['key' => 'avg_score',  'label' => 'Average'],
            ['key' => 'pass_rate',  'label' => 'Pass %'],
            ['key' => 'top_score',  'label' => 'Top'],
        ],
        'rows' => $rows,
    ];
}

/** Class view: per-student summary with in-class position. */
function progressClassStudentRows(mysqli $conn, array $scope): array
{
    $f = progressScoreFilter($scope, true, true);
    // Aggregate each student's mean and subject count within the scope.
    $sql = "SELECT sc.student_id,
                   st.student_id AS admission_no,
                   CONCAT(st.first_name,' ',st.last_name) AS name,
                   MAX(sc.class_name) AS class_name,
                   COUNT(*)                     AS subjects,
                   ROUND(AVG(sc.total_score),2) AS avg_score,
                   ROUND(SUM(sc.total_score),2) AS total_score,
                   ROUND(SUM(CASE WHEN sc.total_score >= ? THEN 1 ELSE 0 END)*100/COUNT(*),1) AS pass_rate
            FROM scores sc
            JOIN students st ON st.id = sc.student_id
            WHERE 1=1" . $f['sql'] . "
            GROUP BY sc.student_id, admission_no, name
            ORDER BY avg_score DESC";
    $stmt = $conn->prepare($sql);
    progressBind($stmt, 'd' . $f['types'], array_merge([$scope['pass_mark']], $f['params']));
    $stmt->execute();
    $res = $stmt->get_result();
    $rows = [];
    $pos = 0;
    while ($row = $res->fetch_assoc()) {
        $pos++;
        $rows[] = [
            'position'     => $pos,
            'student_id'   => (int) $row['student_id'],
            'admission_no' => $row['admission_no'],
            'name'         => $row['name'],
            'class_name'   => $row['class_name'],
            'subjects'     => (int) $row['subjects'],
            'avg_score'    => $row['avg_score'] !== null ? (float) $row['avg_score'] : null,
            'pass_rate'    => $row['pass_rate'] !== null ? (float) $row['pass_rate'] : null,
        ];
    }
    $stmt->close();

    return [
        'columns' => [
            ['key' => 'position',   'label' => 'Pos'],
            ['key' => 'name',       'label' => 'Student'],
            ['key' => 'admission_no', 'label' => 'ID'],
            ['key' => 'subjects',   'label' => 'Subjects'],
            ['key' => 'avg_score',  'label' => 'Average'],
            ['key' => 'pass_rate',  'label' => 'Pass %'],
        ],
        'rows' => $rows,
    ];
}

/** Student view: per-subject scores with the student's rank in each subject. */
function progressStudentSubjectRows(mysqli $conn, array $scope): array
{
    $f = progressScoreFilter($scope, false, false);
    $sql = "SELECT su.id AS subject_id, su.subject_name AS subject,
                   ROUND(AVG(sc.raw_class_score),2)      AS class_score,
                   ROUND(AVG(sc.raw_exam_score),2)       AS exam_score,
                   ROUND(AVG(sc.total_score),2)          AS total_score,
                   MAX(sc.grade)                         AS grade,
                   MAX(sc.remark)                        AS remark
            FROM scores sc
            JOIN subjects su ON su.id = sc.subject_id
            WHERE 1=1" . $f['sql'] . "
            GROUP BY su.id, su.subject_name
            ORDER BY total_score DESC";
    $stmt = $conn->prepare($sql);
    progressBind($stmt, $f['types'], $f['params']);
    $stmt->execute();
    $res = $stmt->get_result();
    $rows = [];
    while ($row = $res->fetch_assoc()) {
        $rows[] = [
            'subject_id'  => (int) $row['subject_id'],
            'subject'     => $row['subject'],
            'class_score' => $row['class_score'] !== null ? (float) $row['class_score'] : null,
            'exam_score'  => $row['exam_score'] !== null ? (float) $row['exam_score'] : null,
            'total_score' => $row['total_score'] !== null ? (float) $row['total_score'] : null,
            'grade'       => $row['grade'],
            'remark'      => $row['remark'],
            'subject_rank' => null,
        ];
    }
    $stmt->close();

    // Rank the student within each subject against classmates for the same period.
    if ($rows !== [] && ($scope['academic_year'] !== '' || $scope['term'] !== '')) {
        $peer = $scope;
        $peer['student_id'] = 0;
        if ($peer['classes'] === []) {
            $peer['classes'] = progressStudentClasses($conn, $scope['student_id']);
        }
        foreach ($rows as &$r) {
            $r['subject_rank'] = progressSubjectRank(
                $conn, $peer, $r['subject_id'], (float) ($r['total_score'] ?? 0)
            );
        }
        unset($r);
    }

    return [
        'columns' => [
            ['key' => 'subject',      'label' => 'Subject'],
            ['key' => 'class_score',  'label' => 'Class'],
            ['key' => 'exam_score',   'label' => 'Exam'],
            ['key' => 'total_score',  'label' => 'Total'],
            ['key' => 'grade',        'label' => 'Grade'],
            ['key' => 'subject_rank', 'label' => 'Rank'],
        ],
        'rows' => $rows,
    ];
}

/** 1-based rank of $score among classmates in one subject/period ("3/28" via total). */
function progressSubjectRank(mysqli $conn, array $peerScope, int $subjectId, float $score): ?string
{
    $ps = $peerScope;
    $ps['subjects'] = [$subjectId];
    $f = progressScoreFilter($ps, true, false);
    $stmt = $conn->prepare(
        "SELECT
            SUM(CASE WHEN sc.total_score > ? THEN 1 ELSE 0 END) AS better,
            COUNT(*) AS total
         FROM scores sc WHERE 1=1" . $f['sql']
    );
    progressBind($stmt, 'd' . $f['types'], array_merge([$score], $f['params']));
    $stmt->execute();
    $row = $stmt->get_result()->fetch_assoc() ?: [];
    $stmt->close();
    $total = (int) ($row['total'] ?? 0);
    if ($total === 0) { return null; }
    return ((int) ($row['better'] ?? 0) + 1) . '/' . $total;
}

/**
 * Options for the filter bar, scoped to the caller's permissions.
 *
 * @return array{years:string[], terms:string[], classes:string[],
 *                subjects:array<int,array{id:int,name:string}>,
 *                current_year:string, current_term:string}
 */
function progressFilterOptions(mysqli $conn, ?array $allowedClasses = null): array
{
    $years = [];
    $res = $conn->query("SELECT DISTINCT academic_year FROM scores WHERE academic_year <> '' ORDER BY academic_year DESC");
    if ($res) { while ($row = $res->fetch_assoc()) { $years[] = $row['academic_year']; } $res->free(); }

    // Terms in academic order from the shared helper's period list.
    $termsSeen = [];
    foreach (academicPeriodsInOrder($conn) as $p) {
        $termsSeen[academicTermOrdinal($p['term'])] = $p['term'];
    }
    ksort($termsSeen);
    $terms = array_values($termsSeen);

    // Classes: from Class Management, then any class present in scores/students.
    $classes = [];
    $res = $conn->query("SELECT class_name FROM classes WHERE is_active = 1 ORDER BY class_name");
    if ($res) { while ($row = $res->fetch_assoc()) { $classes[$row['class_name']] = true; } $res->free(); }
    $res = $conn->query("SELECT DISTINCT class_name FROM scores WHERE class_name IS NOT NULL AND class_name <> ''");
    if ($res) { while ($row = $res->fetch_assoc()) { $classes[$row['class_name']] = true; } $res->free(); }
    $classList = array_keys($classes);
    sort($classList);

    if (is_array($allowedClasses)) {
        $classList = array_values(array_intersect($classList, $allowedClasses));
    }

    $subjects = [];
    $res = $conn->query("SELECT id, subject_name FROM subjects WHERE active = 1 OR active = 'yes' ORDER BY subject_name");
    if ($res) {
        while ($row = $res->fetch_assoc()) {
            $subjects[] = ['id' => (int) $row['id'], 'name' => $row['subject_name']];
        }
        $res->free();
    }

    return [
        'years'        => $years,
        'terms'        => $terms,
        'classes'      => $classList,
        'subjects'     => $subjects,
        'current_year' => academicCurrentYear($conn),
        'current_term' => progressCurrentTerm($conn),
    ];
}

/** The school's current term from settings ('' if unset). */
function progressCurrentTerm(mysqli $conn): string
{
    $res = $conn->query("SELECT setting_value FROM settings WHERE setting_key = 'current_term' LIMIT 1");
    if ($res) {
        $row = $res->fetch_assoc();
        $res->free();
        return (string) ($row['setting_value'] ?? '');
    }
    return '';
}

/** Resolve a student's display name + class, for the student-view header. */
function progressStudentHeader(mysqli $conn, int $studentId): ?array
{
    $stmt = $conn->prepare(
        "SELECT id, student_id AS admission_no, first_name, last_name, class, sex, photo_path
           FROM students WHERE id = ? LIMIT 1"
    );
    $stmt->bind_param('i', $studentId);
    $stmt->execute();
    $row = $stmt->get_result()->fetch_assoc();
    $stmt->close();
    if (!$row) { return null; }
    return [
        'id'           => (int) $row['id'],
        'admission_no' => $row['admission_no'],
        'name'         => trim($row['first_name'] . ' ' . $row['last_name']),
        'class'        => $row['class'],
        'sex'          => $row['sex'],
        'photo_path'   => $row['photo_path'],
    ];
}

/* ══════════════════════════════════════════════════════════════════════════
 * P2 — deeper analytics: heatmap, rank movement, attendance correlation,
 * side-by-side comparison. Same scope contract and role scoping as above.
 * ════════════════════════════════════════════════════════════════════════ */

/**
 * A matrix of average scores for the heatmap — the single highest-value screen,
 * where a weak subject or a struggling row is visible at a glance.
 *
 * Row entity follows the scope level:
 *   school → one row per CLASS (classes × subjects)
 *   class  → one row per STUDENT (students × subjects), capped for legibility
 * Columns are the subjects present in the scope; cells hold the row's average
 * total_score in that subject, null where no result exists.
 *
 * @return array{
 *   mode:string, row_label:string,
 *   subjects:array<int,array{id:int,name:string}>,
 *   rows:array<int,array{key:string,name:string,avg:?float,cells:array<int,?float>}>,
 *   truncated:bool
 * }
 */
function progressHeatmap(mysqli $conn, array $scope, int $maxRows = 60): array
{
    $byClass = ($scope['level'] === 'school');

    // Subjects present in scope, in a stable order.
    $sf = progressScoreFilter($scope, false, true);
    $stmt = $conn->prepare(
        "SELECT DISTINCT su.id, su.subject_name
           FROM scores sc
           JOIN subjects su ON su.id = sc.subject_id
           JOIN students st ON st.id = sc.student_id
          WHERE 1=1" . $sf['sql'] . "
          ORDER BY su.subject_name"
    );
    progressBind($stmt, $sf['types'], $sf['params']);
    $stmt->execute();
    $res = $stmt->get_result();
    $subjects = [];
    while ($row = $res->fetch_assoc()) {
        $subjects[] = ['id' => (int) $row['id'], 'name' => $row['subject_name']];
    }
    $stmt->close();

    // Row set: classes (school) or students (class), strongest average first.
    if ($byClass) {
        // Only real classes (exclude the NULL "unrecorded" bucket) as rows.
        $cf = progressScoreFilter($scope, true, false);
        $stmt = $conn->prepare(
            "SELECT sc.class_name AS k, sc.class_name AS name, ROUND(AVG(sc.total_score),2) AS avg
               FROM scores sc
              WHERE 1=1" . $cf['sql'] . "
              GROUP BY sc.class_name
              ORDER BY avg DESC
              LIMIT " . ((int) $maxRows + 1)
        );
        progressBind($stmt, $cf['types'], $cf['params']);
    } else {
        $stmt = $conn->prepare(
            "SELECT sc.student_id AS k,
                    CONCAT(st.first_name,' ',st.last_name) AS name,
                    ROUND(AVG(sc.total_score),2) AS avg
               FROM scores sc
               JOIN students st ON st.id = sc.student_id
              WHERE 1=1" . $sf['sql'] . "
              GROUP BY sc.student_id, name
              ORDER BY avg DESC
              LIMIT " . ((int) $maxRows + 1)
        );
        progressBind($stmt, $sf['types'], $sf['params']);
    }
    $stmt->execute();
    $res = $stmt->get_result();
    $rows = [];
    while ($row = $res->fetch_assoc()) {
        $rows[(string) $row['k']] = [
            'key'   => (string) $row['k'],
            'name'  => $row['name'],
            'avg'   => $row['avg'] !== null ? (float) $row['avg'] : null,
            'cells' => [],
        ];
    }
    $stmt->close();

    $truncated = count($rows) > $maxRows;
    if ($truncated) { $rows = array_slice($rows, 0, $maxRows, true); }

    if ($rows !== [] && $subjects !== []) {
        $keys = array_keys($rows);
        $keyPh = implode(',', array_fill(0, count($keys), '?'));
        if ($byClass) {
            $cf = progressScoreFilter($scope, true, false);
            $sql = "SELECT sc.class_name AS k, sc.subject_id, ROUND(AVG(sc.total_score),2) AS a
                      FROM scores sc
                     WHERE sc.class_name IN ($keyPh)" . $cf['sql'] . "
                     GROUP BY sc.class_name, sc.subject_id";
            $types = str_repeat('s', count($keys)) . $cf['types'];
            $params = array_merge($keys, $cf['params']);
        } else {
            $cf = progressScoreFilter($scope, false, false);
            $sql = "SELECT sc.student_id AS k, sc.subject_id, ROUND(AVG(sc.total_score),2) AS a
                      FROM scores sc
                     WHERE sc.student_id IN ($keyPh)" . $cf['sql'] . "
                     GROUP BY sc.student_id, sc.subject_id";
            $types = str_repeat('i', count($keys)) . $cf['types'];
            $params = array_merge(array_map('intval', $keys), $cf['params']);
        }
        $stmt = $conn->prepare($sql);
        progressBind($stmt, $types, $params);
        $stmt->execute();
        $res = $stmt->get_result();
        while ($row = $res->fetch_assoc()) {
            $k = (string) $row['k'];
            if (isset($rows[$k])) {
                $rows[$k]['cells'][(int) $row['subject_id']] =
                    $row['a'] !== null ? (float) $row['a'] : null;
            }
        }
        $stmt->close();
    }

    return [
        'mode'      => $byClass ? 'class' : 'student',
        'row_label' => $byClass ? 'Class' : 'Student',
        'subjects'  => $subjects,
        'rows'      => array_values($rows),
        'truncated' => $truncated,
    ];
}

/**
 * Rank / average movement between two consecutive periods — most improved and
 * most declined students. Answers "who changed, and by how much?" which a raw
 * average never shows.
 *
 * The two periods default to the last two the scope has data for. Each student's
 * mean across subjects is compared; only students present in BOTH periods count
 * (a delta needs two endpoints).
 *
 * @return array{
 *   from:?array{academic_year:string,term:string,label:string},
 *   to:?array{academic_year:string,term:string,label:string},
 *   improved:array<int,array>, declined:array<int,array>
 * }
 */
function progressRankMovement(mysqli $conn, array $scope, int $limit = 8): array
{
    $periods = academicPeriodsInOrder($conn);
    if ($scope['academic_year'] !== '') {
        $periods = array_values(array_filter(
            $periods, fn($p) => $p['academic_year'] === $scope['academic_year']
        ));
    }
    if (count($periods) < 2) {
        return ['from' => null, 'to' => null, 'improved' => [], 'declined' => []];
    }

    // If a term is pinned, end on it; otherwise use the two most recent.
    $toIdx = count($periods) - 1;
    if ($scope['term'] !== '') {
        foreach ($periods as $i => $p) {
            if (academicTermOrdinal($p['term']) === academicTermOrdinal($scope['term'])
                && ($scope['academic_year'] === '' || $p['academic_year'] === $scope['academic_year'])) {
                $toIdx = $i;
            }
        }
    }
    if ($toIdx < 1) {
        return ['from' => null, 'to' => null, 'improved' => [], 'declined' => []];
    }
    $from = $periods[$toIdx - 1];
    $to   = $periods[$toIdx];

    $avgFrom = progressStudentAverages($conn, $scope, $from['academic_year'], $from['term']);
    $avgTo   = progressStudentAverages($conn, $scope, $to['academic_year'], $to['term']);

    $deltas = [];
    foreach ($avgTo as $sid => $info) {
        if (!isset($avgFrom[$sid])) { continue; }
        $deltas[] = [
            'student_id' => $sid,
            'name'       => $info['name'],
            'class_name' => $info['class_name'],
            'from'       => round($avgFrom[$sid]['avg'], 2),
            'to'         => round($info['avg'], 2),
            'delta'      => round($info['avg'] - $avgFrom[$sid]['avg'], 2),
        ];
    }

    usort($deltas, fn($a, $b) => $b['delta'] <=> $a['delta']);
    $improved = array_slice(array_filter($deltas, fn($d) => $d['delta'] > 0), 0, $limit);
    $declinedAll = array_filter($deltas, fn($d) => $d['delta'] < 0);
    usort($declinedAll, fn($a, $b) => $a['delta'] <=> $b['delta']);
    $declined = array_slice($declinedAll, 0, $limit);

    $meta = fn($p) => ['academic_year' => $p['academic_year'], 'term' => $p['term'], 'label' => $p['label']];
    return [
        'from'     => $meta($from),
        'to'       => $meta($to),
        'improved' => array_values($improved),
        'declined' => array_values($declined),
    ];
}

/** Per-student mean across subjects for one period within a scope. */
function progressStudentAverages(mysqli $conn, array $scope, string $year, string $term): array
{
    $ps = $scope;
    $ps['academic_year'] = $year;
    $ps['term'] = $term;
    $ps['student_id'] = 0; // movement always spans the whole class(es) in scope
    $f = progressScoreFilter($ps, false, false);
    $stmt = $conn->prepare(
        "SELECT sc.student_id,
                CONCAT(st.first_name,' ',st.last_name) AS name,
                MAX(sc.class_name) AS class_name,
                AVG(sc.total_score) AS avg
           FROM scores sc
           JOIN students st ON st.id = sc.student_id
          WHERE 1=1" . $f['sql'] . "
          GROUP BY sc.student_id, name"
    );
    progressBind($stmt, $f['types'], $f['params']);
    $stmt->execute();
    $res = $stmt->get_result();
    $out = [];
    while ($row = $res->fetch_assoc()) {
        $out[(int) $row['student_id']] = [
            'name'       => $row['name'],
            'class_name' => $row['class_name'],
            'avg'        => (float) $row['avg'],
        ];
    }
    $stmt->close();
    return $out;
}

/**
 * The [start,end] date window for a term, from school_terms — used to align
 * attendance (date-based) with a scored period (term-labelled).
 *
 * @return array{0:?string,1:?string} null dates when the term isn't defined.
 */
function progressPeriodWindow(mysqli $conn, string $year, string $term): array
{
    if ($year === '' || $term === '') { return [null, null]; }
    $stmt = $conn->prepare(
        "SELECT start_date, end_date FROM school_terms
          WHERE academic_year = ? AND term = ? LIMIT 1"
    );
    $stmt->bind_param('ss', $year, $term);
    $stmt->execute();
    $row = $stmt->get_result()->fetch_assoc();
    $stmt->close();
    if (!$row) { return [null, null]; }
    return [$row['start_date'], $row['end_date']];
}

/**
 * Attendance rate vs average score, one point per student — the scatter that
 * exposes whether attendance and achievement move together.
 *
 * Attendance is date-based, scores are term-labelled, so the two are aligned via
 * the term's calendar window (school_terms). Attendance counts present + late as
 * "attended". When the term has no attendance rows (e.g. a past term) the point
 * list is empty and the UI shows an explanatory note rather than an empty axis.
 *
 * @return array{points:array<int,array{student_id:int,name:string,attendance:float,score:float}>,
 *                correlation:?float, has_window:bool}
 */
function progressAttendanceScatter(mysqli $conn, array $scope): array
{
    [$start, $end] = progressPeriodWindow($conn, $scope['academic_year'], $scope['term']);

    // Average score per student in scope.
    $f = progressScoreFilter($scope, false, false);
    $stmt = $conn->prepare(
        "SELECT sc.student_id,
                CONCAT(st.first_name,' ',st.last_name) AS name,
                AVG(sc.total_score) AS score
           FROM scores sc
           JOIN students st ON st.id = sc.student_id
          WHERE 1=1" . $f['sql'] . "
          GROUP BY sc.student_id, name"
    );
    progressBind($stmt, $f['types'], $f['params']);
    $stmt->execute();
    $res = $stmt->get_result();
    $scoreBy = [];
    while ($row = $res->fetch_assoc()) {
        $scoreBy[(int) $row['student_id']] = ['name' => $row['name'], 'score' => (float) $row['score']];
    }
    $stmt->close();

    if ($scoreBy === []) {
        return ['points' => [], 'correlation' => null, 'has_window' => ($start !== null)];
    }

    // Attendance rate per student, bounded to the term window when known.
    $ids = array_keys($scoreBy);
    $idPh = implode(',', array_fill(0, count($ids), '?'));
    $asql = "SELECT student_id,
                    SUM(CASE WHEN status IN ('present','late') THEN 1 ELSE 0 END) AS attended,
                    COUNT(*) AS total
               FROM attendance
              WHERE student_id IN ($idPh)";
    $atypes = str_repeat('i', count($ids));
    $aparams = $ids;
    if ($start !== null && $end !== null) {
        $asql .= " AND attendance_date BETWEEN ? AND ?";
        $atypes .= 'ss';
        $aparams[] = $start;
        $aparams[] = $end;
    }
    $asql .= " GROUP BY student_id";
    $stmt = $conn->prepare($asql);
    progressBind($stmt, $atypes, $aparams);
    $stmt->execute();
    $res = $stmt->get_result();
    $attBy = [];
    while ($row = $res->fetch_assoc()) {
        $tot = (int) $row['total'];
        if ($tot > 0) {
            $attBy[(int) $row['student_id']] = round((int) $row['attended'] * 100 / $tot, 1);
        }
    }
    $stmt->close();

    $points = [];
    foreach ($scoreBy as $sid => $info) {
        if (!isset($attBy[$sid])) { continue; }
        $points[] = [
            'student_id' => $sid,
            'name'       => $info['name'],
            'attendance' => $attBy[$sid],
            'score'      => round($info['score'], 1),
        ];
    }

    return [
        'points'      => $points,
        'correlation' => progressPearson($points),
        'has_window'  => ($start !== null),
    ];
}

/** Pearson correlation of attendance vs score over the point list, or null. */
function progressPearson(array $points): ?float
{
    $n = count($points);
    if ($n < 3) { return null; }
    $sx = $sy = $sxx = $syy = $sxy = 0.0;
    foreach ($points as $p) {
        $x = $p['attendance']; $y = $p['score'];
        $sx += $x; $sy += $y; $sxx += $x * $x; $syy += $y * $y; $sxy += $x * $y;
    }
    $den = sqrt(($n * $sxx - $sx * $sx) * ($n * $syy - $sy * $sy));
    if ($den == 0.0) { return null; }
    return round(($n * $sxy - $sx * $sy) / $den, 3);
}

/**
 * Two scopes side by side — KPIs + subject averages for each — for the compare
 * mode (class vs class, or term vs term). The caller builds the two scopes; this
 * just runs the shared aggregates over each so the comparison is apples-to-apples.
 *
 * @return array{a:array, b:array}
 */
function progressCompare(mysqli $conn, array $scopeA, array $scopeB): array
{
    $one = function (array $scope) use ($conn): array {
        return [
            'kpis'     => progressKpis($conn, $scope),
            'subjects' => progressSubjectBreakdown($conn, $scope),
            'grades'   => progressGradeDistribution($conn, $scope),
        ];
    };
    return ['a' => $one($scopeA), 'b' => $one($scopeB)];
}

/* ══════════════════════════════════════════════════════════════════════════
 * Parent-portal helpers — a child's own view is restricted to PUBLISHED terms
 * so marks are never visible before the school releases the report.
 * ════════════════════════════════════════════════════════════════════════ */

/**
 * The (academic_year, term) periods whose terminal report is published for a
 * student, newest first. Empty when nothing is published yet.
 *
 * @return array<int,array{academic_year:string, term:string}>
 */
function progressPublishedPeriods(mysqli $conn, int $studentId): array
{
    $stmt = $conn->prepare(
        "SELECT DISTINCT academic_year, term
           FROM report_details
          WHERE student_id = ? AND is_published = 1"
    );
    $stmt->bind_param('i', $studentId);
    $stmt->execute();
    $res = $stmt->get_result();
    $rows = [];
    while ($row = $res->fetch_assoc()) {
        $rows[] = ['academic_year' => (string) $row['academic_year'], 'term' => (string) $row['term']];
    }
    $stmt->close();

    // Newest first, using the shared chronological ordering.
    usort($rows, fn($a, $b) => strcmp(
        academicPeriodSortKey($b['academic_year'], $b['term']),
        academicPeriodSortKey($a['academic_year'], $a['term'])
    ));
    return $rows;
}

/**
 * A single student's attendance rate (present+late / total) for a scope's term
 * window, or null when no attendance is recorded. Used as a KPI in the parent
 * and student views (the class scatter is not meaningful for one child).
 */
function progressStudentAttendanceRate(mysqli $conn, int $studentId, string $year, string $term): ?float
{
    [$start, $end] = progressPeriodWindow($conn, $year, $term);
    $sql = "SELECT SUM(CASE WHEN status IN ('present','late') THEN 1 ELSE 0 END) attended, COUNT(*) total
              FROM attendance WHERE student_id = ?";
    $types = 'i';
    $params = [$studentId];
    if ($start !== null && $end !== null) {
        $sql .= " AND attendance_date BETWEEN ? AND ?";
        $types .= 'ss';
        $params[] = $start;
        $params[] = $end;
    }
    $stmt = $conn->prepare($sql);
    progressBind($stmt, $types, $params);
    $stmt->execute();
    $row = $stmt->get_result()->fetch_assoc() ?: [];
    $stmt->close();
    $total = (int) ($row['total'] ?? 0);
    if ($total === 0) { return null; }
    return round((int) ($row['attended'] ?? 0) * 100 / $total, 1);
}
