-- One-off migration: adds the lesson_backfills table, used to hold
-- backdated-attendance requests. A row is created when a teacher fills in
-- attendance for their OWN scheduled lesson on a date before today (e.g. via
-- the "Незаполненные уроки" list, or by picking an old date on group_att.php);
-- the submitted data sits here as JSON until an admin approves it (at which
-- point the real `lessons` / `student_lessons` rows are created or an
-- existing attendance:auto-mark-missed placeholder is converted in place) or
-- rejects it (nothing is ever created/changed). Same shape/workflow as
-- lesson_substitutions, kept as a separate table since that one's
-- original_teacher_id/substitute_teacher_id columns and its consumers
-- (AnalyticsController's substitution stats, the journal "Замена за..."
-- badge) all assume every approved row is a genuine substitution for a
-- different teacher — a self-backfill row would corrupt both. Run once
-- against the app database:
--
--   mysql -h <host> -u <user> -p <database> < scripts/create_lesson_backfills_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_backfills (
    id           INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    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_backfills_status (status),
    INDEX idx_lesson_backfills_slot (group_id, lesson_date, begin_time)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
