-- Auto-assigned Student IDs. Previously the admin typed a Student ID by
-- hand every time (e.g. "HK-028"), which is exactly how two different
-- students can accidentally end up sharing one. Going forward the number
-- portion is always assigned by the server — sequential per school,
-- never re-used — and only the PREFIX is admin-configurable.
--
-- student_number is also what real numeric sorting is based on: the
-- existing "ID" column in Student Management was displaying s.id (an
-- internal database key that reflects insertion order across the whole
-- table, not a per-school sequence), which is why the list didn't look
-- sorted by the Student ID a school actually cares about.

ALTER TABLE schools ADD COLUMN IF NOT EXISTS student_id_prefix TEXT NOT NULL DEFAULT 'STU';
ALTER TABLE students ADD COLUMN IF NOT EXISTS student_number INTEGER;

-- Backfill existing students: try to reuse the numeric part of their
-- current student_code where it parses cleanly (e.g. "HK-028" -> 28),
-- so their number doesn't change under them. Where it doesn't parse,
-- fall back to assigning sequentially by creation order — still unique
-- and sortable, just not tied to whatever text was there before.
DO $$
DECLARE
  s RECORD;
  rec RECORD;
  next_num INTEGER;
BEGIN
  FOR s IN SELECT id FROM schools LOOP
    PERFORM set_config('app.current_school_id', s.id::text, true);

    -- Parseable codes first, keeping their own number.
    UPDATE students
      SET student_number = NULLIF(regexp_replace(student_code, '\D', '', 'g'), '')::INTEGER
      WHERE school_id = s.id
        AND student_number IS NULL
        AND student_code ~ '\d';

    -- Anything left (no digits at all, or a collision with another
    -- student's number) gets assigned sequentially by creation order,
    -- continuing on from the current highest number for that school.
    SELECT COALESCE(MAX(student_number), 0) INTO next_num FROM students WHERE school_id = s.id;
    FOR rec IN
      SELECT id FROM students
      WHERE school_id = s.id
        AND (student_number IS NULL
             OR student_number IN (SELECT student_number FROM students WHERE school_id = s.id GROUP BY student_number HAVING COUNT(*) > 1))
      ORDER BY created_at
    LOOP
      next_num := next_num + 1;
      UPDATE students SET student_number = next_num WHERE id = rec.id;
    END LOOP;
  END LOOP;
END $$;

CREATE UNIQUE INDEX IF NOT EXISTS idx_students_school_number ON students (school_id, student_number);