-- 4. ACADEMIC STRUCTURE
-- ---------------------------------------------------------------------

CREATE TABLE sessions (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(20) NOT NULL,              -- e.g. 2025/2026
  start_date DATE NOT NULL,
  end_date DATE NOT NULL,
  is_current TINYINT(1) NOT NULL DEFAULT 0,
  UNIQUE KEY uq_session_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE terms (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  session_id INT UNSIGNED NOT NULL,
  name ENUM('First Term','Second Term','Third Term') NOT NULL,
  start_date DATE NOT NULL,
  end_date DATE NOT NULL,
  next_term_begins DATE DEFAULT NULL,
  is_current TINYINT(1) NOT NULL DEFAULT 0,
  UNIQUE KEY uq_session_term (session_id, name),
  CONSTRAINT fk_term_session FOREIGN KEY (session_id) REFERENCES sessions(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE class_levels (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(30) NOT NULL,              -- JSS2, SSS1, Primary 4...
  category ENUM('creche','nursery','primary','jss','sss') NOT NULL,
  sequence_order INT NOT NULL,            -- for promotion ordering
  adm_no_initial VARCHAR(20) DEFAULT NULL,
  UNIQUE KEY uq_level_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE classes (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  class_level_id INT UNSIGNED NOT NULL,
  arm VARCHAR(30) DEFAULT NULL,           -- "Gold", "A", "Diamond"
  class_teacher_id BIGINT UNSIGNED DEFAULT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_class_level FOREIGN KEY (class_level_id) REFERENCES class_levels(id),
  CONSTRAINT fk_class_teacher FOREIGN KEY (class_teacher_id) REFERENCES staff_profiles(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE subjects (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  code VARCHAR(20) DEFAULT NULL,
  UNIQUE KEY uq_subject_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE class_subjects (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  class_id INT UNSIGNED NOT NULL,
  subject_id INT UNSIGNED NOT NULL,
  teacher_id BIGINT UNSIGNED DEFAULT NULL,
  session_id INT UNSIGNED NOT NULL,
  UNIQUE KEY uq_class_subject_session (class_id, subject_id, session_id),
  CONSTRAINT fk_cs_class FOREIGN KEY (class_id) REFERENCES classes(id),
  CONSTRAINT fk_cs_subject FOREIGN KEY (subject_id) REFERENCES subjects(id),
  CONSTRAINT fk_cs_teacher FOREIGN KEY (teacher_id) REFERENCES staff_profiles(id),
  CONSTRAINT fk_cs_session FOREIGN KEY (session_id) REFERENCES sessions(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;