-- One-off migration: adds the lesson_substitutions table, used to hold
-- teacher-replacement requests. A row is created when a substitute teacher
-- fills in attendance for another teacher's scheduled lesson; the
-- submitted data sits here as JSON until an admin approves it (at which
-- point the real `lessons` / `student_lessons` rows are created) or
-- rejects it (nothing is ever created). Run once against the app database:
--
--   mysql -h <host> -u <user> -p <database> < scripts/create_lesson_substitutions_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 lesson_substitutions (
    id                     INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    original_teacher_id    INT NOT NULL,
    substitute_teacher_id  INT NOT NULL,
    group_id               INT NOT NULL,
    lesson_date            DATE NOT NULL,
    begin_time             TIME NOT NULL,
    end_time               TIME NOT NULL,
    payload                LONGTEXT NOT NULL,
    status                 ENUM('pending', 'approved', 'rejected') NOT NULL DEFAULT 'pending',
    lesson_id              INT NULL,
    reviewed_by            INT NULL,
    reviewed_at            DATETIME NULL,
    review_note            VARCHAR(500) NULL,
    created_at             DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_lesson_substitutions_status (status),
    INDEX idx_lesson_substitutions_slot (group_id, lesson_date, begin_time)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
