-- One-off migration: creates the ent_scores table, used by the Analytics page
-- to track a student's ЕНТ (UNT) test score over time (manual entry by admin).
--
-- 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"$(grep '^DB_USER=' .env | cut -d '=' -f2-)" \
--       -p"$(grep '^DB_PASS=' .env | cut -d '=' -f2-)" \
--       "$(grep '^DB_NAME=' .env | cut -d '=' -f2-)" \
--       < scripts/create_ent_scores_table.sql

CREATE TABLE ent_scores (
    id INT AUTO_INCREMENT PRIMARY KEY,
    student_id INT NOT NULL,
    score DECIMAL(5,1) NOT NULL,
    test_date DATE NOT NULL,
    recorded_by BIGINT UNSIGNED NULL,
    note VARCHAR(255) NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (student_id) REFERENCES students(id),
    FOREIGN KEY (recorded_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
