-- Buat database
CREATE DATABASE kehadiran_ujian;
USE kehadiran_ujian;

-- Tabel siswa
CREATE TABLE ujn_siswa (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nis VARCHAR(20) UNIQUE NOT NULL,
    nama VARCHAR(100) NOT NULL,
    kelas VARCHAR(10) NOT NULL
);

-- Tabel mata_pelajaran
CREATE TABLE ujn_mata_pelajaran (
    id INT AUTO_INCREMENT PRIMARY KEY,
    kode_mapel VARCHAR(10) UNIQUE NOT NULL,
    nama_mapel VARCHAR(100) NOT NULL
);

-- Tabel kehadiran
CREATE TABLE ujn_kehadiran (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nis VARCHAR(20) NOT NULL,
    kode_mapel VARCHAR(10) NOT NULL,
    hari VARCHAR(10) NOT NULL,
    tanggal DATE NOT NULL,
    jam TIME NOT NULL,
    FOREIGN KEY (nis) REFERENCES ujn_siswa(nis),
    FOREIGN KEY (kode_mapel) REFERENCES ujn_mata_pelajaran(kode_mapel)
);

-- Tabel users
CREATE TABLE ujn_users (
    id_user INT PRIMARY KEY AUTO_INCREMENT,
    nama_lengkap VARCHAR(100) NOT NULL,
    username VARCHAR(50) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    role ENUM('admin','guru') NOT NULL
);

-- Data contoh
INSERT INTO ujn_siswa (nis, nama, kelas) VALUES
('2024001', 'Ahmad Rizki', 'XII IPA 1'),
('2024002', 'Siti Nurhaliza', 'XII IPA 1'),
('2024003', 'Budi Santoso', 'XII IPA 2');

INSERT INTO ujn_mata_pelajaran (kode_mapel, nama_mapel) VALUES
('MAT', 'Matematika'),
('BIO', 'Biologi'),
('FIS', 'Fisika'),
('KIM', 'Kimia');

-- Insert admin default (password: 123)
INSERT INTO ujn_users (nama_lengkap, username, password, role) VALUES
('Administrator', 'admin', '$2y$10$tjD3f.sh8sbsy.Sn./hyS.Tqu6PBM3XkfVCL/sbxHVsSPUo0.JVX2','admin'),
('Pengwas Ujian', 'guru', '$2y$10$tjD3f.sh8sbsy.Sn./hyS.Tqu6PBM3XkfVCL/sbxHVsSPUo0.JVX2','guru');