SET NAMES utf8mb4;
SET time_zone = '+07:00';

CREATE TABLE IF NOT EXISTS settings (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  setting_key VARCHAR(100) NOT NULL UNIQUE,
  setting_value TEXT NULL,
  updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3)
) ENGINE=InnoDB;

INSERT INTO settings(setting_key,setting_value) VALUES
('site_name','CAT LCTP Pramuka'),('site_logo',''),('organization_name','Lomba Cerdas Tangkas Pramuka')
ON DUPLICATE KEY UPDATE setting_value=VALUES(setting_value);

CREATE TABLE IF NOT EXISTS users (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(80) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  role ENUM('admin','proctor','author','participant') NOT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  last_login_at DATETIME(3) NULL,
  created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  INDEX idx_users_role(role), INDEX idx_users_active(is_active)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS participants (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NOT NULL UNIQUE,
  participant_no VARCHAR(40) NOT NULL UNIQUE,
  name VARCHAR(150) NOT NULL,
  gender ENUM('putra','putri') NOT NULL,
  birth_place VARCHAR(100) NOT NULL,
  birth_date DATE NOT NULL,
  school VARCHAR(180) NOT NULL,
  district ENUM('Banyumanik','Candisari','Gajahmungkur','Gayamsari','Genuk','Gunungpati','Mijen','Ngaliyan','Pedurungan','Semarang Barat','Semarang Selatan','Semarang Tengah','Semarang Timur','Semarang Utara','Tembalang','Tugu') NOT NULL,
  division ENUM('Siaga','Penggalang SD/MI','Penggalang SMP/MTs','Penegak','Perti') NOT NULL,
  created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  CONSTRAINT fk_participant_user FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_participant_gender(gender), INDEX idx_participant_division(division), INDEX idx_participant_district(district)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS competitions (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(180) NOT NULL,
  slug VARCHAR(180) NOT NULL UNIQUE,
  competition_type ENUM('competition','practice') NOT NULL DEFAULT 'competition',
  description TEXT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 0,
  created_by BIGINT UNSIGNED NOT NULL,
  created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  CONSTRAINT fk_competition_creator FOREIGN KEY(created_by) REFERENCES users(id),
  INDEX idx_competition_active(is_active)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS exams (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  competition_id BIGINT UNSIGNED NOT NULL,
  title VARCHAR(180) NOT NULL,
  duration_seconds INT UNSIGNED NOT NULL DEFAULT 3600,
  open_at DATETIME(3) NULL,
  close_at DATETIME(3) NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 0,
  show_score_after_submit TINYINT(1) NOT NULL DEFAULT 1,
  randomize_questions TINYINT(1) NOT NULL DEFAULT 0,
  randomize_options TINYINT(1) NOT NULL DEFAULT 0,
  max_attempts TINYINT UNSIGNED NOT NULL DEFAULT 1,
  timer_warning_seconds INT UNSIGNED NOT NULL DEFAULT 300,
  eligible_gender ENUM('all','putra','putri') NOT NULL DEFAULT 'all',
  eligible_division ENUM('Siaga','Penggalang SD/MI','Penggalang SMP/MTs','Penegak','Perti') NULL,
  created_by BIGINT UNSIGNED NOT NULL,
  created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  CONSTRAINT fk_exam_competition FOREIGN KEY(competition_id) REFERENCES competitions(id) ON DELETE CASCADE,
  CONSTRAINT fk_exam_creator FOREIGN KEY(created_by) REFERENCES users(id),
  INDEX idx_exam_active(is_active), INDEX idx_exam_competition(competition_id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS questions (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  author_id BIGINT UNSIGNED NOT NULL,
  question_type ENUM('single_choice','multiple_choice','matching','true_false') NOT NULL,
  question_text MEDIUMTEXT NOT NULL,
  points DECIMAL(10,2) NOT NULL DEFAULT 1.00,
  scoring_mode ENUM('exact','partial') NOT NULL DEFAULT 'exact',
  media_type ENUM('none','image','audio','video') NOT NULL DEFAULT 'none',
  media_path VARCHAR(255) NULL,
  explanation TEXT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  CONSTRAINT fk_question_author FOREIGN KEY(author_id) REFERENCES users(id),
  INDEX idx_question_type(question_type), INDEX idx_question_active(is_active)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS question_options (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  question_id BIGINT UNSIGNED NOT NULL,
  option_key VARCHAR(20) NOT NULL,
  option_text TEXT NOT NULL,
  is_correct TINYINT(1) NOT NULL DEFAULT 0,
  sort_order INT NOT NULL DEFAULT 0,
  CONSTRAINT fk_option_question FOREIGN KEY(question_id) REFERENCES questions(id) ON DELETE CASCADE,
  UNIQUE KEY uq_question_option(question_id,option_key), INDEX idx_option_question(question_id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS matching_items (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  question_id BIGINT UNSIGNED NOT NULL,
  left_key VARCHAR(20) NOT NULL,
  left_text TEXT NOT NULL,
  right_key VARCHAR(20) NOT NULL,
  right_text TEXT NOT NULL,
  sort_order INT NOT NULL DEFAULT 0,
  CONSTRAINT fk_matching_question FOREIGN KEY(question_id) REFERENCES questions(id) ON DELETE CASCADE,
  UNIQUE KEY uq_matching_left(question_id,left_key), INDEX idx_matching_question(question_id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS exam_questions (
  exam_id BIGINT UNSIGNED NOT NULL,
  question_id BIGINT UNSIGNED NOT NULL,
  sort_order INT NOT NULL DEFAULT 0,
  points_override DECIMAL(10,2) NULL,
  PRIMARY KEY(exam_id,question_id),
  CONSTRAINT fk_eq_exam FOREIGN KEY(exam_id) REFERENCES exams(id) ON DELETE CASCADE,
  CONSTRAINT fk_eq_question FOREIGN KEY(question_id) REFERENCES questions(id) ON DELETE CASCADE,
  INDEX idx_eq_order(exam_id,sort_order)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS attempts (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  exam_id BIGINT UNSIGNED NOT NULL,
  participant_id BIGINT UNSIGNED NOT NULL,
  attempt_no TINYINT UNSIGNED NOT NULL DEFAULT 1,
  status ENUM('active','submitted','expired','terminated','reset') NOT NULL DEFAULT 'active',
  started_at DATETIME(3) NOT NULL,
  expires_at DATETIME(3) NOT NULL,
  finished_at DATETIME(3) NULL,
  elapsed_ms BIGINT UNSIGNED NULL,
  score DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  correct_count INT UNSIGNED NOT NULL DEFAULT 0,
  answered_count INT UNSIGNED NOT NULL DEFAULT 0,
  last_question_id BIGINT UNSIGNED NULL,
  last_seen_at DATETIME(3) NULL,
  ip_address VARCHAR(64) NULL,
  user_agent VARCHAR(500) NULL,
  created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  CONSTRAINT fk_attempt_exam FOREIGN KEY(exam_id) REFERENCES exams(id) ON DELETE CASCADE,
  CONSTRAINT fk_attempt_participant FOREIGN KEY(participant_id) REFERENCES participants(id) ON DELETE CASCADE,
  UNIQUE KEY uq_attempt(exam_id,participant_id,attempt_no),
  INDEX idx_attempt_exam_status(exam_id,status), INDEX idx_attempt_rank(exam_id,score,elapsed_ms,correct_count,started_at)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS attempt_question_order (
  attempt_id BIGINT UNSIGNED NOT NULL,
  question_id BIGINT UNSIGNED NOT NULL,
  display_order INT NOT NULL,
  PRIMARY KEY(attempt_id,question_id),
  CONSTRAINT fk_aqo_attempt FOREIGN KEY(attempt_id) REFERENCES attempts(id) ON DELETE CASCADE,
  CONSTRAINT fk_aqo_question FOREIGN KEY(question_id) REFERENCES questions(id) ON DELETE CASCADE,
  UNIQUE KEY uq_aqo_order(attempt_id,display_order)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS answers (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  attempt_id BIGINT UNSIGNED NOT NULL,
  question_id BIGINT UNSIGNED NOT NULL,
  answer_json JSON NULL,
  is_skipped TINYINT(1) NOT NULL DEFAULT 0,
  points_awarded DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  is_correct TINYINT(1) NOT NULL DEFAULT 0,
  answered_at DATETIME(3) NULL,
  updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  CONSTRAINT fk_answer_attempt FOREIGN KEY(attempt_id) REFERENCES attempts(id) ON DELETE CASCADE,
  CONSTRAINT fk_answer_question FOREIGN KEY(question_id) REFERENCES questions(id) ON DELETE CASCADE,
  UNIQUE KEY uq_answer(attempt_id,question_id), INDEX idx_answer_attempt(attempt_id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS violations (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  attempt_id BIGINT UNSIGNED NOT NULL,
  event_type ENUM('visibility_hidden','window_blur','page_leave','fullscreen_exit','copy_attempt','contextmenu','other') NOT NULL,
  details VARCHAR(500) NULL,
  client_ts_ms BIGINT NULL,
  occurred_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  CONSTRAINT fk_violation_attempt FOREIGN KEY(attempt_id) REFERENCES attempts(id) ON DELETE CASCADE,
  INDEX idx_violation_attempt(attempt_id), INDEX idx_violation_time(occurred_at)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS audit_logs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NULL,
  action VARCHAR(120) NOT NULL,
  entity_type VARCHAR(80) NULL,
  entity_id BIGINT UNSIGNED NULL,
  details_json JSON NULL,
  created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  INDEX idx_audit_user(user_id), INDEX idx_audit_action(action), INDEX idx_audit_created(created_at)
) ENGINE=InnoDB;