<?php

/**
 * Academic Period — shared term/year ordering and class attribution.
 *
 * Two problems this solves once, for every caller:
 *
 *   1. TERM ORDERING. `scores.term` and `scores.academic_year` are free-text
 *      varchars with no ordinal. Any trend chart needs a chronological x-axis,
 *      and sorting the raw strings does not give one across years (or across
 *      the "Term 1" / "First Term" spellings that both exist in the data).
 *      Ordering is resolved from school_terms.start_date where a matching row
 *      exists, and falls back to a fixed term ordinal otherwise.
 *
 *   2. CLASS ATTRIBUTION. `scores` now carries a class_name snapshot (see
 *      database/migrations/scores_progress_analytics.sql). Every write path
 *      must stamp it, so the lookup lives here rather than being re-implemented
 *      at each of the six sites that write scores.
 *
 * Read paths must treat a NULL/'' class_name as "class not recorded" and
 * exclude it from class aggregates — it is not an error, it is a pre-migration
 * row from a prior academic year.
 */

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

/** Canonical term labels, in academic order. */
const ACADEMIC_TERMS = ['First Term', 'Second Term', 'Third Term'];

/**
 * Map any term spelling to its ordinal (1..3), or 0 when unrecognised.
 *
 * Tolerates the spellings that occur in the wild: "First Term", "Term 1",
 * "1st Term", plain "1", "SEMESTER 2", differing case and padding.
 */
function academicTermOrdinal(?string $term): int
{
    $t = strtolower(trim((string) $term));
    if ($t === '') {
        return 0;
    }
    if (str_contains($t, 'first')  || str_contains($t, '1')) return 1;
    if (str_contains($t, 'second') || str_contains($t, '2')) return 2;
    if (str_contains($t, 'third')  || str_contains($t, '3')) return 3;
    return 0;
}

/** Normalise any term spelling to its canonical label, or '' when unrecognised. */
function academicTermLabel(?string $term): string
{
    $ord = academicTermOrdinal($term);
    return $ord > 0 ? ACADEMIC_TERMS[$ord - 1] : '';
}

/**
 * Sortable key for a period, e.g. "2026-2027#2".
 *
 * Zero-padding the ordinal keeps plain string comparison chronological, so
 * callers can sort mixed year/term rows in PHP without a custom comparator.
 */
function academicPeriodSortKey(?string $academic_year, ?string $term): string
{
    return trim((string) $academic_year) . '#' . academicTermOrdinal($term);
}

/**
 * SQL expression that orders a term column academically.
 *
 * Returns a FIELD() over the canonical labels plus the common alternates, so it
 * works directly in ORDER BY / GROUP BY without a join to school_terms. Prefer
 * academicPeriodsInOrder() when exact calendar dates matter.
 *
 * @param string $col Already-safe column reference, e.g. "sc.term".
 */
function academicTermOrderSql(string $col = 'term'): string
{
    return "CASE
                WHEN LOWER($col) LIKE '%first%'  OR $col LIKE '%1%' THEN 1
                WHEN LOWER($col) LIKE '%second%' OR $col LIKE '%2%' THEN 2
                WHEN LOWER($col) LIKE '%third%'  OR $col LIKE '%3%' THEN 3
                ELSE 9
            END";
}

/**
 * Every (academic_year, term) period that has score data, in chronological
 * order.
 *
 * school_terms.start_date is authoritative where a matching row exists — a
 * school can run terms on its own calendar. Periods with no school_terms row
 * fall back to (year string, term ordinal), which is why the sort is done in
 * PHP rather than SQL.
 *
 * @return array<int, array{academic_year:string, term:string, term_ordinal:int,
 *                          start_date:?string, label:string}>
 */
function academicPeriodsInOrder(mysqli $conn): array
{
    $periods = [];

    $res = $conn->query(
        "SELECT DISTINCT `academic_year`, `term` FROM `scores`
          WHERE `academic_year` <> '' AND `term` <> ''"
    );
    if ($res) {
        while ($row = $res->fetch_assoc()) {
            $key = $row['academic_year'] . '|' . $row['term'];
            $periods[$key] = [
                'academic_year' => (string) $row['academic_year'],
                'term'          => (string) $row['term'],
                'term_ordinal'  => academicTermOrdinal($row['term']),
                'start_date'    => null,
                'label'         => trim($row['term'] . ' ' . $row['academic_year']),
            ];
        }
        $res->free();
    }

    // Overlay real calendar dates where the school has defined them.
    $res = $conn->query("SELECT `academic_year`, `term`, `start_date` FROM `school_terms`");
    if ($res) {
        while ($row = $res->fetch_assoc()) {
            $key = $row['academic_year'] . '|' . $row['term'];
            if (isset($periods[$key])) {
                $periods[$key]['start_date'] = $row['start_date'];
            }
        }
        $res->free();
    }

    $ordered = array_values($periods);
    usort($ordered, static function (array $a, array $b): int {
        // Both dated: trust the calendar.
        if ($a['start_date'] && $b['start_date']) {
            return strcmp($a['start_date'], $b['start_date']);
        }
        return strcmp(
            academicPeriodSortKey($a['academic_year'], $a['term']),
            academicPeriodSortKey($b['academic_year'], $b['term'])
        );
    });

    return $ordered;
}

/** The school's current academic year, from settings. */
function academicCurrentYear(mysqli $conn): string
{
    static $cached = null;
    if ($cached !== null) {
        return $cached;
    }
    $cached = '';
    $res = $conn->query("SELECT `setting_value` FROM `settings` WHERE `setting_key` = 'academic_year' LIMIT 1");
    if ($res) {
        if ($row = $res->fetch_assoc()) {
            $cached = (string) $row['setting_value'];
        }
        $res->free();
    }
    return $cached;
}

/**
 * The class a student currently belongs to, for stamping onto a score row.
 *
 * Memoised per request: the bulk write paths call this once per student across
 * a whole class of records.
 *
 * @return string '' when the student is unknown or has no class set.
 */
function academicStudentClass(mysqli $conn, int $student_id): string
{
    static $cache = [];
    if ($student_id <= 0) {
        return '';
    }
    if (array_key_exists($student_id, $cache)) {
        return $cache[$student_id];
    }

    $class = '';
    $stmt = $conn->prepare("SELECT `class` FROM `students` WHERE `id` = ? LIMIT 1");
    if ($stmt) {
        $stmt->bind_param('i', $student_id);
        $stmt->execute();
        if ($row = $stmt->get_result()->fetch_assoc()) {
            $class = trim((string) ($row['class'] ?? ''));
        }
        $stmt->close();
    }

    return $cache[$student_id] = $class;
}

/**
 * Stamp class_name onto score rows that are missing it.
 *
 * Safety net for write paths where threading the column through the INSERT is
 * impractical — bulk importers in particular. Only fills blanks, so a value
 * already written by application code is never overwritten.
 *
 * Scope it as narrowly as the caller can: passing no arguments rewrites every
 * unstamped row in the current academic year.
 *
 * @return int rows stamped.
 */
function academicStampScoreClasses(
    mysqli $conn,
    ?int $student_id = null,
    ?string $term = null,
    ?string $academic_year = null
): int {
    $sql = "UPDATE `scores` `sc`
            JOIN `students` `st` ON `st`.`id` = `sc`.`student_id`
            SET `sc`.`class_name` = `st`.`class`
            WHERE (`sc`.`class_name` IS NULL OR `sc`.`class_name` = '')
              AND `st`.`class` IS NOT NULL AND `st`.`class` <> ''";

    $types  = '';
    $params = [];

    if ($student_id !== null && $student_id > 0) {
        $sql     .= " AND `sc`.`student_id` = ?";
        $types   .= 'i';
        $params[] = $student_id;
    }
    if ($term !== null && $term !== '') {
        $sql     .= " AND `sc`.`term` = ?";
        $types   .= 's';
        $params[] = $term;
    }

    // Never stamp a prior year — students.class is only a fact for the year in
    // progress. See the backfill policy in scores_progress_analytics.sql.
    $year = ($academic_year !== null && $academic_year !== '')
        ? $academic_year
        : academicCurrentYear($conn);
    if ($year === '') {
        return 0;
    }
    $sql     .= " AND `sc`.`academic_year` = ?";
    $types   .= 's';
    $params[] = $year;

    $stmt = $conn->prepare($sql);
    if (!$stmt) {
        return 0;
    }
    $stmt->bind_param($types, ...$params);
    $stmt->execute();
    $affected = $stmt->affected_rows;
    $stmt->close();

    return max(0, $affected);
}
