-- 6. RESULTS (single source of truth)
-- ---------------------------------------------------------------------

CREATE TABLE grading_systems (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  grade VARCHAR(5) NOT NULL,
  min_score DECIMAL(5,2) NOT NULL,
  max_score DECIMAL(5,2) NOT NULL,
  remark VARCHAR(50) NOT NULL,             -- Excellent, Good, Pass, Fail
  UNIQUE KEY uq_grade (grade)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE marks_allocations (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  ca1_max INT NOT NULL,
  ca2_max INT NOT NULL,
  exam_max INT NOT NULL,
  total_max INT GENERATED ALWAYS AS (ca1_max + ca2_max + exam_max) STORED,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE results (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  student_id BIGINT UNSIGNED NOT NULL,
  class_id INT UNSIGNED NOT NULL,
  subject_id INT UNSIGNED NOT NULL,
  term_id INT UNSIGNED NOT NULL,
  session_id INT UNSIGNED NOT NULL,
  marks_allocation_id INT UNSIGNED NOT NULL,
  ca1 DECIMAL(5,2) DEFAULT 0,
  ca2 DECIMAL(5,2) DEFAULT 0,
  exam DECIMAL(5,2) DEFAULT 0,
  total DECIMAL(5,2) GENERATED ALWAYS AS (ca1 + ca2 + exam) STORED,
  grade_id INT UNSIGNED DEFAULT NULL,
  entered_by BIGINT UNSIGNED NOT NULL,
  approved_by BIGINT UNSIGNED DEFAULT NULL,
  approved_at DATETIME DEFAULT NULL,
  status ENUM('draft','submitted','approved') NOT NULL DEFAULT 'draft',
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_result (student_id, subject_id, term_id, session_id),
  CONSTRAINT fk_result_student FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
  CONSTRAINT fk_result_class FOREIGN KEY (class_id) REFERENCES classes(id),
  CONSTRAINT fk_result_subject FOREIGN KEY (subject_id) REFERENCES subjects(id),
  CONSTRAINT fk_result_term FOREIGN KEY (term_id) REFERENCES terms(id),
  CONSTRAINT fk_result_session FOREIGN KEY (session_id) REFERENCES sessions(id),
  CONSTRAINT fk_result_alloc FOREIGN KEY (marks_allocation_id) REFERENCES marks_allocations(id),
  CONSTRAINT fk_result_grade FOREIGN KEY (grade_id) REFERENCES grading_systems(id),
  CONSTRAINT fk_result_enteredby FOREIGN KEY (entered_by) REFERENCES users(id),
  CONSTRAINT fk_result_approvedby FOREIGN KEY (approved_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE behavioral_assessments (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  student_id BIGINT UNSIGNED NOT NULL,
  class_id INT UNSIGNED NOT NULL,
  term_id INT UNSIGNED NOT NULL,
  session_id INT UNSIGNED NOT NULL,
  punctuality ENUM('A','B','C','D','E') DEFAULT NULL,
  neatness ENUM('A','B','C','D','E') DEFAULT NULL,
  relationship_with_others ENUM('A','B','C','D','E') DEFAULT NULL,
  regard_for_authority ENUM('A','B','C','D','E') DEFAULT NULL,
  honesty ENUM('A','B','C','D','E') DEFAULT NULL,
  class_teacher_comment TEXT,
  head_teacher_comment TEXT,
  recorded_by BIGINT UNSIGNED NOT NULL,
  UNIQUE KEY uq_behavioral (student_id, term_id, session_id),
  CONSTRAINT fk_beh_student FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
  CONSTRAINT fk_beh_class FOREIGN KEY (class_id) REFERENCES classes(id),
  CONSTRAINT fk_beh_term FOREIGN KEY (term_id) REFERENCES terms(id),
  CONSTRAINT fk_beh_session FOREIGN KEY (session_id) REFERENCES sessions(id),
  CONSTRAINT fk_beh_recorder FOREIGN KEY (recorded_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Cache tables — populated by scheduled jobs, never hand-written.
CREATE TABLE class_positions_cache (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  student_id BIGINT UNSIGNED NOT NULL,
  class_id INT UNSIGNED NOT NULL,
  term_id INT UNSIGNED NOT NULL,
  session_id INT UNSIGNED NOT NULL,
  total_score DECIMAL(7,2) NOT NULL,
  average_score DECIMAL(5,2) NOT NULL,
  class_position INT NOT NULL,
  computed_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_position (student_id, term_id, session_id),
  CONSTRAINT fk_poscache_student FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
  CONSTRAINT fk_poscache_class FOREIGN KEY (class_id) REFERENCES classes(id),
  CONSTRAINT fk_poscache_term FOREIGN KEY (term_id) REFERENCES terms(id),
  CONSTRAINT fk_poscache_session FOREIGN KEY (session_id) REFERENCES sessions(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE annual_result_cache (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  student_id BIGINT UNSIGNED NOT NULL,
  class_id INT UNSIGNED NOT NULL,
  session_id INT UNSIGNED NOT NULL,
  first_term_avg DECIMAL(5,2) DEFAULT NULL,
  second_term_avg DECIMAL(5,2) DEFAULT NULL,
  third_term_avg DECIMAL(5,2) DEFAULT NULL,
  annual_average DECIMAL(5,2) DEFAULT NULL,
  annual_position INT DEFAULT NULL,
  computed_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_annual (student_id, session_id),
  CONSTRAINT fk_annualcache_student FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
  CONSTRAINT fk_annualcache_class FOREIGN KEY (class_id) REFERENCES classes(id),
  CONSTRAINT fk_annualcache_session FOREIGN KEY (session_id) REFERENCES sessions(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE result_pins (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  pin VARCHAR(20) NOT NULL,
  serial_number VARCHAR(40) NOT NULL,
  student_id BIGINT UNSIGNED DEFAULT NULL, -- NULL until bound to a student at first use
  term_id INT UNSIGNED NOT NULL,
  session_id INT UNSIGNED NOT NULL,
  usage_limit INT NOT NULL DEFAULT 5,
  used_count INT NOT NULL DEFAULT 0,
  status ENUM('unused','active','used_up','revoked') NOT NULL DEFAULT 'unused',
  created_by BIGINT UNSIGNED NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  last_used_at DATETIME DEFAULT NULL,
  UNIQUE KEY uq_serial (serial_number),
  KEY idx_pin (pin),
  CONSTRAINT fk_pin_student FOREIGN KEY (student_id) REFERENCES students(id),
  CONSTRAINT fk_pin_term FOREIGN KEY (term_id) REFERENCES terms(id),
  CONSTRAINT fk_pin_session FOREIGN KEY (session_id) REFERENCES sessions(id),
  CONSTRAINT fk_pin_creator FOREIGN KEY (created_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;