-- 8. CBT / ONLINE EXAMS  (term_id + session_id on every exam-scoped table)
-- ---------------------------------------------------------------------

CREATE TABLE exams (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(150) NOT NULL,
  subject_id INT UNSIGNED NOT NULL,
  class_id INT UNSIGNED NOT NULL,
  term_id INT UNSIGNED NOT NULL,
  session_id INT UNSIGNED NOT NULL,
  duration_minutes INT NOT NULL,
  total_marks INT NOT NULL,
  pass_mark INT DEFAULT NULL,
  shuffle_questions TINYINT(1) NOT NULL DEFAULT 1,
  max_malpractice_warnings INT NOT NULL DEFAULT 3,
  status ENUM('draft','scheduled','ongoing','closed') NOT NULL DEFAULT 'draft',
  scheduled_start DATETIME DEFAULT NULL,
  scheduled_end DATETIME DEFAULT NULL,
  created_by BIGINT UNSIGNED NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_exam_subject FOREIGN KEY (subject_id) REFERENCES subjects(id),
  CONSTRAINT fk_exam_class FOREIGN KEY (class_id) REFERENCES classes(id),
  CONSTRAINT fk_exam_term FOREIGN KEY (term_id) REFERENCES terms(id),
  CONSTRAINT fk_exam_session FOREIGN KEY (session_id) REFERENCES sessions(id),
  CONSTRAINT fk_exam_creator FOREIGN KEY (created_by) REFERENCES users(id),
  KEY idx_exam_term_session (term_id, session_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE exam_questions (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  exam_id BIGINT UNSIGNED NOT NULL,
  question_text TEXT NOT NULL,
  question_image VARCHAR(255) DEFAULT NULL,
  question_type ENUM('mcq','theory') NOT NULL DEFAULT 'mcq',
  option_a TEXT, option_b TEXT, option_c TEXT, option_d TEXT, option_e TEXT,
  correct_answer VARCHAR(255) DEFAULT NULL,
  marks INT NOT NULL DEFAULT 1,
  CONSTRAINT fk_eq_exam FOREIGN KEY (exam_id) REFERENCES exams(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE student_exam_sessions (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  student_id BIGINT UNSIGNED NOT NULL,
  exam_id BIGINT UNSIGNED NOT NULL,
  term_id INT UNSIGNED NOT NULL,
  session_id INT UNSIGNED NOT NULL,
  start_time DATETIME NOT NULL,
  end_time DATETIME DEFAULT NULL,
  score DECIMAL(6,2) DEFAULT NULL,
  status ENUM('in_progress','submitted','auto_submitted','disqualified') NOT NULL DEFAULT 'in_progress',
  UNIQUE KEY uq_student_exam (student_id, exam_id),
  CONSTRAINT fk_ses_student FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
  CONSTRAINT fk_ses_exam FOREIGN KEY (exam_id) REFERENCES exams(id),
  CONSTRAINT fk_ses_term FOREIGN KEY (term_id) REFERENCES terms(id),
  CONSTRAINT fk_ses_session FOREIGN KEY (session_id) REFERENCES sessions(id),
  KEY idx_ses_term_session (term_id, session_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE student_answers (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  student_exam_session_id BIGINT UNSIGNED NOT NULL,
  question_id BIGINT UNSIGNED NOT NULL,
  answer TEXT,
  is_correct TINYINT(1) DEFAULT NULL,
  answered_at DATETIME DEFAULT NULL,
  UNIQUE KEY uq_session_question (student_exam_session_id, question_id),
  CONSTRAINT fk_ans_session FOREIGN KEY (student_exam_session_id) REFERENCES student_exam_sessions(id) ON DELETE CASCADE,
  CONSTRAINT fk_ans_question FOREIGN KEY (question_id) REFERENCES exam_questions(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE malpractice_logs (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  student_exam_session_id BIGINT UNSIGNED NOT NULL,
  event_type ENUM('tab_switch','copy_paste','right_click','fullscreen_exit','multiple_faces','other') NOT NULL,
  warning_count INT NOT NULL DEFAULT 1,
  logged_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_mal_session FOREIGN KEY (student_exam_session_id) REFERENCES student_exam_sessions(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;