-- ============================================================
-- Auto-generated UID system for students / teachers / staff
-- ============================================================
-- Formats:
--   Students: PMSS-STU-<2-digit class><3-digit serial>   (serial per class)
--   Teachers: PMSS-TCH-<3-digit serial>                  (global serial)
--   Staff:    PMSS-STF-<3-digit serial>                  (global serial)
--
-- Serial numbers never reuse deleted ids — they are tracked in
-- a dedicated counters table that only ever moves forward.
-- ============================================================

ALTER TABLE students     ADD COLUMN IF NOT EXISTS uid VARCHAR(32) NULL AFTER id;
ALTER TABLE teachers     ADD COLUMN IF NOT EXISTS uid VARCHAR(32) NULL AFTER id;
ALTER TABLE school_staff ADD COLUMN IF NOT EXISTS uid VARCHAR(32) NULL AFTER id;

-- Unique indexes (NULLs are allowed in MySQL unique indexes — legacy rows OK)
CREATE UNIQUE INDEX IF NOT EXISTS uniq_students_uid     ON students(uid);
CREATE UNIQUE INDEX IF NOT EXISTS uniq_teachers_uid     ON teachers(uid);
CREATE UNIQUE INDEX IF NOT EXISTS uniq_school_staff_uid ON school_staff(uid);

-- Counter table — one row per scope, last_serial is the highest
-- serial ever issued for that scope. Increments only.
CREATE TABLE IF NOT EXISTS uid_counters (
    scope        VARCHAR(32) PRIMARY KEY,
    last_serial  INT UNSIGNED NOT NULL DEFAULT 0,
    updated_at   TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----- Backfill UIDs for existing rows -----
-- Teachers (global serial by id ASC)
SET @rownum := (SELECT COALESCE(MAX(CAST(SUBSTRING(uid,10) AS UNSIGNED)),0) FROM teachers WHERE uid LIKE 'PMSS-TCH-%');
UPDATE teachers
   SET uid = CONCAT('PMSS-TCH-', LPAD((@rownum := @rownum + 1), 3, '0'))
 WHERE uid IS NULL OR uid = ''
 ORDER BY id ASC;
INSERT INTO uid_counters (scope, last_serial) VALUES ('teacher', @rownum)
  ON DUPLICATE KEY UPDATE last_serial = GREATEST(last_serial, @rownum);

-- Staff (global serial by id ASC)
SET @rownum := (SELECT COALESCE(MAX(CAST(SUBSTRING(uid,10) AS UNSIGNED)),0) FROM school_staff WHERE uid LIKE 'PMSS-STF-%');
UPDATE school_staff
   SET uid = CONCAT('PMSS-STF-', LPAD((@rownum := @rownum + 1), 3, '0'))
 WHERE uid IS NULL OR uid = ''
 ORDER BY id ASC;
INSERT INTO uid_counters (scope, last_serial) VALUES ('staff', @rownum)
  ON DUPLICATE KEY UPDATE last_serial = GREATEST(last_serial, @rownum);

-- Students (per-class serial). Loop is class-by-class — run once.
-- Done with a session-variable trick joined per class.
DROP PROCEDURE IF EXISTS backfill_student_uids;
DELIMITER $$
CREATE PROCEDURE backfill_student_uids()
BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE c INT;
    DECLARE cur CURSOR FOR SELECT DISTINCT class FROM students WHERE class IS NOT NULL ORDER BY class;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
    OPEN cur;
    read_loop: LOOP
        FETCH cur INTO c;
        IF done = 1 THEN LEAVE read_loop; END IF;
        SET @cls := LPAD(c, 2, '0');
        SET @prefix := CONCAT('PMSS-STU-', @cls);
        SET @startSerial := (SELECT COALESCE(MAX(CAST(SUBSTRING(uid, 11, 3) AS UNSIGNED)),0)
                              FROM students WHERE uid LIKE CONCAT(@prefix, '%'));
        SET @rownum := @startSerial;
        UPDATE students
           SET uid = CONCAT(@prefix, LPAD((@rownum := @rownum + 1), 3, '0'))
         WHERE class = c AND (uid IS NULL OR uid = '')
         ORDER BY id ASC;
        INSERT INTO uid_counters (scope, last_serial)
          VALUES (CONCAT('student:', @cls), @rownum)
          ON DUPLICATE KEY UPDATE last_serial = GREATEST(last_serial, @rownum);
    END LOOP;
    CLOSE cur;
END$$
DELIMITER ;

CALL backfill_student_uids();
DROP PROCEDURE backfill_student_uids;