-- ============================================================================
-- scores — Progress Analytics foundation (P0)
--
-- The `scores` table shipped with NO primary key, NO auto-increment on `id`,
-- NO unique constraint and NO indexes at all. That has three consequences this
-- migration fixes, in this order:
--
--   1. CLASS ATTRIBUTION. A score row records only (student, subject, term,
--      year) — the class is knowable only via students.class, which holds the
--      student's CURRENT class. Once a student is promoted, every one of their
--      historical rows silently re-attributes to the new class, so "Basic 5,
--      2025-2026" can never be reconstructed. `attendance` already snapshots
--      class_name per row; this brings `scores` in line with that precedent.
--
--   2. SILENT DUPLICATES. lib/sba_engine.php and teacher-portal/ajax/tp_marks.php
--      both write with ON DUPLICATE KEY UPDATE, but with no unique key that
--      clause can never fire — every save appends another row instead of
--      updating. Worse, with no AUTO_INCREMENT a non-strict server writes id = 0
--      for each one. Analytics averaging over that data would double-count.
--      The unique key below is what makes those two existing code paths behave
--      the way they were written to behave.
--
--   3. PERFORMANCE. Every analytics query groups on
--      (class_name | student_id, academic_year, term, subject_id). With no index
--      each one is a full scan.
--
-- BACKFILL POLICY (deliberate): class_name is filled ONLY for the current
-- academic year, read from settings. Within the current year no promotion has
-- happened yet, so students.class is still a fact — copying it records history
-- rather than inventing it. Prior years are left NULL and surface in the UI as
-- an explicit "Class not recorded" bucket, excluded from class aggregates.
-- Historical years can be seeded separately later.
--
-- Idempotent: safe to run repeatedly. Guarded DDL is used for keys because
-- MariaDB has no ADD KEY IF NOT EXISTS.
-- ============================================================================

-- ── 1. Class snapshot column ────────────────────────────────────────────────
ALTER TABLE `scores`
  ADD COLUMN IF NOT EXISTS `class_name` VARCHAR(50) NULL DEFAULT NULL AFTER `subject_id`;


-- ── 2. Give every row a unique id ───────────────────────────────────────────
-- Rows written by the live code paths land with id = 0 (NOT NULL, no default,
-- no AUTO_INCREMENT). They must be made distinct before `id` can become a
-- primary key, and before the de-duplication step can use it as a tie-break.
SET @next_id := (SELECT COALESCE(MAX(`id`), 0) FROM `scores`);
UPDATE `scores` SET `id` = (@next_id := @next_id + 1) WHERE `id` = 0;


-- ── 3. De-duplicate before the unique key can be applied ────────────────────
-- The self-join below matches on (student, subject, term, year). Without an
-- index on those columns it is an O(n²) nested-loop full scan — minutes on only
-- a few thousand rows. Add the covering index FIRST so the join is a keyed
-- lookup; it is dropped in step 5 once the UNIQUE key (same columns) supersedes
-- it.
SET @has_dedup_ix := (
  SELECT COUNT(*) FROM information_schema.STATISTICS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'scores' AND INDEX_NAME = 'ix_scores_dedup'
);
SET @sql := IF(@has_dedup_ix = 0,
  'ALTER TABLE `scores` ADD KEY `ix_scores_dedup` (`student_id`, `subject_id`, `term`, `academic_year`)',
  'DO 0');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- Keeps the freshest row per (student, subject, term, year): latest updated_at,
-- tie-broken by highest id. Self-join DELETE — a subquery over the target table
-- is not allowed here.
DELETE `s` FROM `scores` `s`
JOIN `scores` `keeper`
  ON  `keeper`.`student_id`    = `s`.`student_id`
  AND `keeper`.`subject_id`    = `s`.`subject_id`
  AND `keeper`.`term`          = `s`.`term`
  AND `keeper`.`academic_year` = `s`.`academic_year`
WHERE `keeper`.`updated_at` > `s`.`updated_at`
   OR (`keeper`.`updated_at` = `s`.`updated_at` AND `keeper`.`id` > `s`.`id`);


-- ── 4. Primary key + AUTO_INCREMENT ─────────────────────────────────────────
SET @has_pk := (
  SELECT COUNT(*) FROM information_schema.STATISTICS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'scores' AND INDEX_NAME = 'PRIMARY'
);
SET @sql := IF(@has_pk = 0,
  'ALTER TABLE `scores` ADD PRIMARY KEY (`id`), MODIFY `id` INT(11) NOT NULL AUTO_INCREMENT',
  'DO 0');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;


-- ── 5. Unique key — one score per student/subject/term/year ─────────────────
-- This is what activates the ON DUPLICATE KEY UPDATE clauses already present in
-- sba_engine.php and tp_marks.php. It also serves as the index for per-student
-- lookups (leftmost prefix student_id, subject_id).
SET @has_uq := (
  SELECT COUNT(*) FROM information_schema.STATISTICS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'scores' AND INDEX_NAME = 'uq_scores_entry'
);
SET @sql := IF(@has_uq = 0,
  'ALTER TABLE `scores` ADD UNIQUE KEY `uq_scores_entry` (`student_id`, `subject_id`, `term`, `academic_year`)',
  'DO 0');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- Drop the temporary dedup index — uq_scores_entry now covers the same columns.
SET @has_dedup_ix := (
  SELECT COUNT(*) FROM information_schema.STATISTICS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'scores' AND INDEX_NAME = 'ix_scores_dedup'
);
SET @sql := IF(@has_dedup_ix > 0,
  'ALTER TABLE `scores` DROP KEY `ix_scores_dedup`',
  'DO 0');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;


-- ── 6. Analytics indexes ────────────────────────────────────────────────────
-- Class-scoped period queries (class overview, class-vs-class comparison).
SET @has_ix := (
  SELECT COUNT(*) FROM information_schema.STATISTICS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'scores' AND INDEX_NAME = 'idx_scores_class_period'
);
SET @sql := IF(@has_ix = 0,
  'ALTER TABLE `scores` ADD KEY `idx_scores_class_period` (`class_name`, `academic_year`, `term`)',
  'DO 0');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- Whole-school and per-subject period queries (subject rankings, distributions).
SET @has_ix := (
  SELECT COUNT(*) FROM information_schema.STATISTICS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'scores' AND INDEX_NAME = 'idx_scores_period_subject'
);
SET @sql := IF(@has_ix = 0,
  'ALTER TABLE `scores` ADD KEY `idx_scores_period_subject` (`academic_year`, `term`, `subject_id`)',
  'DO 0');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;


-- ── 7. Backfill class_name for the CURRENT academic year only ───────────────
-- See BACKFILL POLICY above. Only untouched rows are filled, so a class_name
-- already stamped by application code is never overwritten.
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` <> ''
  AND `sc`.`academic_year` = (
        SELECT `setting_value` FROM `settings`
        WHERE `setting_key` = 'academic_year' LIMIT 1
      );
