-- One-off migration: adds a "reason" dictionary for student status changes
-- and links it to student_status_history so each change can optionally
-- record why. Run once against the app database, after
-- create_student_status_history_table.sql has already been applied.
--
-- IMPORTANT: this file contains Cyrillic text — force the client to
-- utf8mb4, otherwise the mysql CLI's default charset (often latin1) will
-- double-encode it into mojibake:
--
--   docker compose exec -T db mysql --default-character-set=utf8mb4 \
--       -u"$MYSQL_USER" -p"$MYSQL_PASSWORD" "$MYSQL_DATABASE" \
--       < scripts/add_status_change_reasons.sql

CREATE TABLE IF NOT EXISTS student_status_reasons (
    id   INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    UNIQUE KEY uniq_student_status_reason_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT IGNORE INTO student_status_reasons (name) VALUES
    ('Нет результата'),
    ('Конфликт'),
    ('Не устроили условия'),
    ('Переезд'),
    ('Перестали отвечать'),
    ('Каникулы'),
    ('Выполнили цель');

-- MySQL (unlike MariaDB) doesn't support IF NOT EXISTS on ADD COLUMN or
-- CREATE INDEX, so these two are plain statements — safe to run once;
-- if you re-run this whole file, skip past these two if they already
-- applied (they'll error with "Duplicate column" / "Duplicate key name").
ALTER TABLE student_status_history ADD COLUMN reason_id INT NULL AFTER new_status_id;
CREATE INDEX idx_student_status_history_reason_id ON student_status_history (reason_id);
