CREATE DATABASE IF NOT EXISTS ujian_anti_cheat
  CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE ujian_anti_cheat;

CREATE TABLE users (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(50) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  nama VARCHAR(150) NOT NULL,
  kelas VARCHAR(30) DEFAULT NULL,
  role ENUM('admin','operator','pengawas','peserta') NOT NULL DEFAULT 'peserta',
  aktif TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE ujian (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  kode VARCHAR(50) NOT NULL UNIQUE,
  nama VARCHAR(200) NOT NULL,
  mapel VARCHAR(100) DEFAULT NULL,
  kelas VARCHAR(30) DEFAULT NULL,
  mulai DATETIME NOT NULL,
  selesai DATETIME NOT NULL,
  durasi_menit INT UNSIGNED NOT NULL,
  status ENUM('draft','aktif','selesai') NOT NULL DEFAULT 'draft',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_jadwal (mulai, selesai, status)
) ENGINE=InnoDB;

CREATE TABLE soal (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  ujian_id BIGINT UNSIGNED NOT NULL,
  nomor INT UNSIGNED NOT NULL,
  pertanyaan TEXT NOT NULL,
  tipe ENUM('pg','essay','benar_salah') NOT NULL DEFAULT 'pg',
  bobot DECIMAL(8,2) NOT NULL DEFAULT 1,
  gambar_url VARCHAR(500) DEFAULT NULL,
  FOREIGN KEY (ujian_id) REFERENCES ujian(id) ON DELETE CASCADE,
  UNIQUE KEY uq_soal_nomor (ujian_id, nomor)
) ENGINE=InnoDB;

CREATE TABLE opsi_jawaban (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  soal_id BIGINT UNSIGNED NOT NULL,
  kode CHAR(1) NOT NULL,
  teks TEXT NOT NULL,
  benar TINYINT(1) NOT NULL DEFAULT 0,
  FOREIGN KEY (soal_id) REFERENCES soal(id) ON DELETE CASCADE,
  UNIQUE KEY uq_opsi (soal_id, kode)
) ENGINE=InnoDB;

CREATE TABLE token_ujian (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  ujian_id BIGINT UNSIGNED NOT NULL,
  token CHAR(6) NOT NULL,
  berlaku_mulai DATETIME NOT NULL,
  berlaku_sampai DATETIME NOT NULL,
  aktif TINYINT(1) NOT NULL DEFAULT 1,
  FOREIGN KEY (ujian_id) REFERENCES ujian(id) ON DELETE CASCADE,
  INDEX idx_token (ujian_id, token, aktif)
) ENGINE=InnoDB;

CREATE TABLE sesi_ujian (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  ujian_id BIGINT UNSIGNED NOT NULL,
  peserta_id BIGINT UNSIGNED NOT NULL,
  device_id VARCHAR(255) DEFAULT NULL,
  mulai DATETIME NOT NULL,
  selesai DATETIME DEFAULT NULL,
  status ENUM('belum','mengerjakan','submit','terputus','dibatalkan') NOT NULL DEFAULT 'belum',
  skor DECIMAL(8,2) DEFAULT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (ujian_id) REFERENCES ujian(id) ON DELETE CASCADE,
  FOREIGN KEY (peserta_id) REFERENCES users(id) ON DELETE CASCADE,
  UNIQUE KEY uq_sesi (ujian_id, peserta_id)
) ENGINE=InnoDB;

CREATE TABLE jawaban (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  sesi_id BIGINT UNSIGNED NOT NULL,
  soal_id BIGINT UNSIGNED NOT NULL,
  jawaban TEXT DEFAULT NULL,
  benar TINYINT(1) DEFAULT NULL,
  skor DECIMAL(8,2) DEFAULT 0,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (sesi_id) REFERENCES sesi_ujian(id) ON DELETE CASCADE,
  FOREIGN KEY (soal_id) REFERENCES soal(id) ON DELETE CASCADE,
  UNIQUE KEY uq_jawaban (sesi_id, soal_id)
) ENGINE=InnoDB;

CREATE TABLE pelanggaran (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  sesi_id BIGINT UNSIGNED NOT NULL,
  jenis VARCHAR(100) NOT NULL,
  detail VARCHAR(500) DEFAULT NULL,
  tingkat ENUM('rendah','sedang','tinggi') NOT NULL DEFAULT 'rendah',
  ip_address VARCHAR(45) DEFAULT NULL,
  device_id VARCHAR(255) DEFAULT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (sesi_id) REFERENCES sesi_ujian(id) ON DELETE CASCADE,
  INDEX idx_pelanggaran_sesi (sesi_id, created_at)
) ENGINE=InnoDB;

-- Password awal: admin123
INSERT INTO users(username,password_hash,nama,kelas,role)
VALUES ('admin', '$2y$12$FkjkoXPCtNvq7Xp6cLyrI.oJ3suGw9WkamZgNob.5hQiv5CKmWHtS', 'Administrator', NULL, 'admin');