
CREATE DATABASE IF NOT EXISTS db_cbt_ultimate;
USE db_cbt_ultimate;

CREATE TABLE users (
 id INT AUTO_INCREMENT PRIMARY KEY,
 username VARCHAR(50) UNIQUE,
 password VARCHAR(255),
 role ENUM('admin','siswa')
);

CREATE TABLE kelas (
 id INT AUTO_INCREMENT PRIMARY KEY,
 nama VARCHAR(20)
);

CREATE TABLE siswa (
 id INT AUTO_INCREMENT PRIMARY KEY,
 user_id INT,
 kelas_id INT,
 nama VARCHAR(100),
 nis VARCHAR(20)
);

CREATE TABLE mapel (
 id INT AUTO_INCREMENT PRIMARY KEY,
 nama VARCHAR(50)
);

CREATE TABLE soal (
 id INT AUTO_INCREMENT PRIMARY KEY,
 mapel_id INT,
 pertanyaan TEXT,
 a VARCHAR(100),
 b VARCHAR(100),
 c VARCHAR(100),
 d VARCHAR(100),
 jawaban CHAR(1),
 bobot INT
);

CREATE TABLE ujian (
 id INT AUTO_INCREMENT PRIMARY KEY,
 mapel_id INT,
 kelas_id INT,
 durasi INT,
 mulai DATETIME,
 selesai DATETIME
);

CREATE TABLE jawaban (
 id INT AUTO_INCREMENT PRIMARY KEY,
 ujian_id INT,
 soal_id INT,
 siswa_id INT,
 jawaban CHAR(1)
);

CREATE TABLE nilai (
 id INT AUTO_INCREMENT PRIMARY KEY,
 ujian_id INT,
 siswa_id INT,
 nilai INT
);

-- admin default
INSERT INTO users VALUES
(NULL,'admin','$2y$10$Q2h8Qk8X5b1Q5m2Z9V2r1u1bH4X7Z2Hq6m7Jm3n2y5cQe1m8u','admin');
