-- Jalankan SATU KALI setelah backup database EduJurnal Pro.
-- Membuat tahun pelajaran, lalu menautkan seluruh data lama ke tahun pelajaran aktif awal.
CREATE TABLE IF NOT EXISTS academic_years (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(20) NOT NULL UNIQUE,
  start_date DATE NOT NULL,
  end_date DATE NOT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_academic_year_dates(start_date,end_date)
) ENGINE=InnoDB;

INSERT IGNORE INTO academic_years(name,start_date,end_date,is_active) VALUES('2026/2027','2026-07-01','2027-06-30',1);
UPDATE academic_years SET is_active=0;
UPDATE academic_years SET is_active=1 WHERE name='2026/2027';

ALTER TABLE students ADD COLUMN academic_year_id BIGINT UNSIGNED NULL;
ALTER TABLE attendance ADD COLUMN academic_year_id BIGINT UNSIGNED NULL;
ALTER TABLE journals ADD COLUMN academic_year_id BIGINT UNSIGNED NULL;

UPDATE students SET academic_year_id=(SELECT id FROM academic_years WHERE is_active=1 LIMIT 1) WHERE academic_year_id IS NULL;
UPDATE attendance SET academic_year_id=(SELECT id FROM academic_years WHERE is_active=1 LIMIT 1) WHERE academic_year_id IS NULL;
UPDATE journals SET academic_year_id=(SELECT id FROM academic_years WHERE is_active=1 LIMIT 1) WHERE academic_year_id IS NULL;

ALTER TABLE students DROP INDEX nis;
ALTER TABLE students ADD UNIQUE KEY uq_student_nis_year(nis,academic_year_id);
ALTER TABLE students ADD INDEX idx_student_year_class(academic_year_id,class_name);
ALTER TABLE attendance ADD INDEX idx_att_year_date(academic_year_id,attendance_date,class_name);
ALTER TABLE journals ADD INDEX idx_journal_year_date(academic_year_id,journal_date);

ALTER TABLE students MODIFY academic_year_id BIGINT UNSIGNED NOT NULL;
ALTER TABLE attendance MODIFY academic_year_id BIGINT UNSIGNED NOT NULL;
ALTER TABLE journals MODIFY academic_year_id BIGINT UNSIGNED NOT NULL;
ALTER TABLE students ADD CONSTRAINT fk_student_academic_year FOREIGN KEY(academic_year_id) REFERENCES academic_years(id);
ALTER TABLE attendance ADD CONSTRAINT fk_att_academic_year FOREIGN KEY(academic_year_id) REFERENCES academic_years(id);
ALTER TABLE journals ADD CONSTRAINT fk_journal_academic_year FOREIGN KEY(academic_year_id) REFERENCES academic_years(id);