-- Full schema including quiz & student accounts
CREATE DATABASE IF NOT EXISTS absensi_ma CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE absensi_ma;
CREATE TABLE IF NOT EXISTS users (id INT AUTO_INCREMENT PRIMARY KEY, username VARCHAR(100) UNIQUE, password VARCHAR(255), role ENUM('admin','guru') , nama VARCHAR(200), mapel VARCHAR(100), created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
CREATE TABLE IF NOT EXISTS mapel (id INT AUTO_INCREMENT PRIMARY KEY, nama VARCHAR(100) UNIQUE);
CREATE TABLE IF NOT EXISTS kelas (id INT AUTO_INCREMENT PRIMARY KEY, nama VARCHAR(50) UNIQUE);
CREATE TABLE IF NOT EXISTS siswa (id INT AUTO_INCREMENT PRIMARY KEY, nis VARCHAR(30) UNIQUE, nama VARCHAR(200), id_kelas INT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (id_kelas) REFERENCES kelas(id) ON DELETE CASCADE);
CREATE TABLE IF NOT EXISTS student_accounts (id INT AUTO_INCREMENT PRIMARY KEY, siswa_id INT NOT NULL UNIQUE, username VARCHAR(100) NOT NULL UNIQUE, password VARCHAR(255) NOT NULL, wali_phone VARCHAR(30) DEFAULT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (siswa_id) REFERENCES siswa(id) ON DELETE CASCADE);
CREATE TABLE IF NOT EXISTS quizzes (id INT AUTO_INCREMENT PRIMARY KEY, guru_id INT NOT NULL, judul VARCHAR(255) NOT NULL, id_mapel INT NOT NULL, id_kelas INT NOT NULL, waktu_mulai DATETIME NOT NULL, waktu_selesai DATETIME NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (guru_id) REFERENCES users(id) ON DELETE CASCADE, FOREIGN KEY (id_mapel) REFERENCES mapel(id) ON DELETE CASCADE, FOREIGN KEY (id_kelas) REFERENCES kelas(id) ON DELETE CASCADE);
CREATE TABLE IF NOT EXISTS quiz_questions (id INT AUTO_INCREMENT PRIMARY KEY, quiz_id INT NOT NULL, tipe ENUM('single','multi','essay','matching','truefalse') NOT NULL DEFAULT 'single', pertanyaan TEXT NOT NULL, opsi JSON DEFAULT NULL, kunci TEXT DEFAULT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (quiz_id) REFERENCES quizzes(id) ON DELETE CASCADE);
CREATE TABLE IF NOT EXISTS quiz_submissions (id INT AUTO_INCREMENT PRIMARY KEY, quiz_id INT NOT NULL, siswa_id INT NOT NULL, status ENUM('pending','graded') DEFAULT 'pending', submitted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, score DECIMAL(6,2) DEFAULT NULL, graded_at TIMESTAMP NULL DEFAULT NULL, grader_id INT DEFAULT NULL, FOREIGN KEY (quiz_id) REFERENCES quizzes(id) ON DELETE CASCADE, FOREIGN KEY (siswa_id) REFERENCES siswa(id) ON DELETE CASCADE, FOREIGN KEY (grader_id) REFERENCES users(id) ON DELETE SET NULL);
CREATE TABLE IF NOT EXISTS quiz_answers (id INT AUTO_INCREMENT PRIMARY KEY, submission_id INT NOT NULL, question_id INT NOT NULL, jawaban TEXT, is_correct TINYINT(1) DEFAULT NULL, answered_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (submission_id) REFERENCES quiz_submissions(id) ON DELETE CASCADE, FOREIGN KEY (question_id) REFERENCES quiz_questions(id) ON DELETE CASCADE);
INSERT IGNORE INTO mapel (nama) VALUES ('Fiqih'),('Bahasa Arab'),('Matematika'),('Bahasa Inggris'),('Sejarah'),('Informatika');
INSERT IGNORE INTO kelas (nama) VALUES ('X-1'),('X-2'),('XI-1'),('XI-2'),('XII-1'),('XII-2');
