-- One-off migration: adds the student_status_history table, which logs
-- every student status change with the exact date/time it happened and
-- who made it. Run once against the app database:
--
--   mysql -h <host> -u <user> -p <database> < scripts/create_student_status_history_table.sql
--
-- (or paste the statement below into phpMyAdmin / Adminer / your MySQL
-- client of choice). No existing tables are modified.

CREATE TABLE IF NOT EXISTS student_status_history (
    id             INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    student_id     INT NOT NULL,
    old_status_id  INT NULL,
    new_status_id  INT NULL,
    changed_by     INT NULL,
    created_at     DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_student_status_history_student_id (student_id),
    INDEX idx_student_status_history_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
