-- Jalankan setelah schema.sql pada instalasi PHP/MySQL lama.
-- Migrasi ini tidak menghapus kolom lama agar data raport terdahulu tetap terbaca.

ALTER TABLE subjects
    ADD COLUMN sumatif_count TINYINT UNSIGNED NOT NULL DEFAULT 4 AFTER code;

ALTER TABLE assessment_sets
    ADD COLUMN report_sumatif_order TINYINT UNSIGNED NULL AFTER is_report_sumatif_1;

ALTER TABLE report_settings
    ADD COLUMN sumatif_count TINYINT UNSIGNED NOT NULL DEFAULT 4 AFTER teacher_id;

ALTER TABLE report_scores
    ADD COLUMN sumatif_scores JSON NULL AFTER student_id,
    ADD COLUMN psat DECIMAL(5,2) NULL AFTER psts;

UPDATE assessment_sets SET report_sumatif_order = 1 WHERE is_report_sumatif_1 = 1 AND report_sumatif_order IS NULL;
UPDATE report_settings rs JOIN subjects s ON s.id = rs.subject_id SET rs.sumatif_count = s.sumatif_count;
UPDATE report_scores SET psat = psas_test WHERE psat IS NULL AND psas_test IS NOT NULL;