-- ============================================================
-- SKEMA DATABASE: CBT UJI KOMPETENSI OFFICE (Word/Excel/PowerPoint)
-- UPA TIK - Universitas Bangka Belitung
-- ============================================================

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

-- Admin / Staf TIK yang mengelola sistem
CREATE TABLE admin (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    nama VARCHAR(100) NOT NULL,
    peran ENUM('kepala_upa','staf_tik') DEFAULT 'staf_tik',
    dibuat_pada DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Kategori soal
CREATE TABLE kategori_soal (
    id INT PRIMARY KEY AUTO_INCREMENT,
    nama VARCHAR(50) NOT NULL   -- 'Word', 'Excel', 'PowerPoint'
);

INSERT INTO kategori_soal (nama) VALUES ('Word'), ('Excel'), ('PowerPoint');

-- Bank soal
CREATE TABLE bank_soal (
    id INT PRIMARY KEY AUTO_INCREMENT,
    kategori_id INT NOT NULL,
    pertanyaan TEXT NOT NULL,
    tipe_konten ENUM('text','image','file') DEFAULT 'text',
    path_lampiran VARCHAR(255) NULL,
    pilihan_a VARCHAR(255) NOT NULL,
    pilihan_b VARCHAR(255) NOT NULL,
    pilihan_c VARCHAR(255) NOT NULL,
    pilihan_d VARCHAR(255) NOT NULL,
    pilihan_e VARCHAR(255) NULL,
    kunci_jawaban CHAR(1) NOT NULL,
    bobot_nilai DECIMAL(5,2) DEFAULT 1.00,
    aktif BOOLEAN DEFAULT TRUE,
    dibuat_pada DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (kategori_id) REFERENCES kategori_soal(id)
);

-- Sesi ujian
CREATE TABLE sesi_ujian (
    id INT PRIMARY KEY AUTO_INCREMENT,
    nama_ujian VARCHAR(150) NOT NULL,
    tanggal DATE NOT NULL,
    jam_mulai TIME NOT NULL,
    durasi_menit INT NOT NULL,
    lab VARCHAR(50) NOT NULL,
    jumlah_soal_word INT DEFAULT 0,
    jumlah_soal_excel INT DEFAULT 0,
    jumlah_soal_ppt INT DEFAULT 0,
    seb_config_key VARCHAR(255) NOT NULL,
    status ENUM('draft','berjalan','selesai') DEFAULT 'draft',
    dibuat_pada DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Peserta ujian
CREATE TABLE peserta_ujian (
    id INT PRIMARY KEY AUTO_INCREMENT,
    sesi_id INT NOT NULL,
    nim VARCHAR(20) NOT NULL,
    nama VARCHAR(100) NOT NULL,
    token_akses VARCHAR(64) UNIQUE NOT NULL,
    status ENUM('belum_mulai','sedang_ujian','selesai') DEFAULT 'belum_mulai',
    waktu_mulai DATETIME NULL,
    waktu_selesai DATETIME NULL,
    FOREIGN KEY (sesi_id) REFERENCES sesi_ujian(id)
);

-- Paket soal yang sudah diacak & ditetapkan per peserta (snapshot)
CREATE TABLE paket_soal_peserta (
    id INT PRIMARY KEY AUTO_INCREMENT,
    peserta_id INT NOT NULL,
    soal_id INT NOT NULL,
    urutan_tampil INT NOT NULL,
    FOREIGN KEY (peserta_id) REFERENCES peserta_ujian(id) ON DELETE CASCADE,
    FOREIGN KEY (soal_id) REFERENCES bank_soal(id)
);

-- Jawaban peserta
CREATE TABLE jawaban (
    id INT PRIMARY KEY AUTO_INCREMENT,
    peserta_id INT NOT NULL,
    soal_id INT NOT NULL,
    jawaban_dipilih CHAR(1) NULL,
    waktu_jawab DATETIME NULL,
    UNIQUE KEY unik_jawaban (peserta_id, soal_id),
    FOREIGN KEY (peserta_id) REFERENCES peserta_ujian(id) ON DELETE CASCADE,
    FOREIGN KEY (soal_id) REFERENCES bank_soal(id)
);

-- Nilai akhir
CREATE TABLE nilai (
    id INT PRIMARY KEY AUTO_INCREMENT,
    peserta_id INT UNIQUE NOT NULL,
    skor_word DECIMAL(6,2) DEFAULT 0,
    skor_excel DECIMAL(6,2) DEFAULT 0,
    skor_ppt DECIMAL(6,2) DEFAULT 0,
    maks_word DECIMAL(6,2) DEFAULT 0,
    maks_excel DECIMAL(6,2) DEFAULT 0,
    maks_ppt DECIMAL(6,2) DEFAULT 0,
    persen_word DECIMAL(5,2) DEFAULT 0,
    persen_excel DECIMAL(5,2) DEFAULT 0,
    persen_ppt DECIMAL(5,2) DEFAULT 0,
    rata_rata_persen DECIMAL(5,2) DEFAULT 0,
    status_kelulusan ENUM('lulus','tidak_lulus') DEFAULT 'tidak_lulus',
    skor_total DECIMAL(6,2) DEFAULT 0,
    dihitung_pada DATETIME NULL,
    FOREIGN KEY (peserta_id) REFERENCES peserta_ujian(id) ON DELETE CASCADE
);

-- Log aktivitas peserta (anti-cheat: kehilangan fokus SEB, dsb)
CREATE TABLE log_aktivitas (
    id INT PRIMARY KEY AUTO_INCREMENT,
    peserta_id INT NOT NULL,
    kejadian VARCHAR(100) NOT NULL,   -- 'login','blur_window','submit_paksa','waktu_habis', dst
    waktu DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (peserta_id) REFERENCES peserta_ujian(id) ON DELETE CASCADE
);

-- Akun admin default (username: admin / password: ubahsegera123)
-- Ganti password setelah login pertama kali!
INSERT INTO admin (username, password_hash, nama, peran)
VALUES ('admin', '$2y$10$JG4VX9QIa8aKxJCoGoeGOeCNDmxSbIt9.fI1WwnlV8vj69c71P.cy', 'Administrator', 'kepala_upa');