-- Buat database
CREATE DATABASE smansab_lms;
USE smansab_lms2;

-- Tabel lms_users
CREATE TABLE lms_users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    role ENUM('admin', 'guru', 'siswa') NOT NULL,
    nama_lengkap VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    bio TEXT,
    phone VARCHAR(25),
    foto_profil VARCHAR(255) DEFAULT NULL,
    tanggal_daftar TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    status ENUM('active', 'inactive') DEFAULT 'active'
);

-- Tabel lms_categories
CREATE TABLE lms_categories (
    id INT PRIMARY KEY AUTO_INCREMENT,
    nama_kategori VARCHAR(100) NOT NULL,
    deskripsi TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Tabel lms_courses
CREATE TABLE lms_courses (
    id INT PRIMARY KEY AUTO_INCREMENT,
    judul VARCHAR(255) NOT NULL,
    deskripsi TEXT,
    id_guru INT NOT NULL,
    id_kategori INT NOT NULL,
    harga DECIMAL(10,2) DEFAULT 0.00,
    gambar_course VARCHAR(255) DEFAULT NULL,
    durasi_jam INT DEFAULT 0,
    level ENUM('pemula', 'menengah', 'lanjutan') DEFAULT 'pemula',
    rating DECIMAL(3,2) DEFAULT 0.00,
    total_siswa INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (id_guru) REFERENCES lms_users(id),
    FOREIGN KEY (id_kategori) REFERENCES lms_categories(id)
);

-- Tabel lms_enrollments
CREATE TABLE lms_enrollments (
    id INT PRIMARY KEY AUTO_INCREMENT,
    id_siswa INT NOT NULL,
    id_course INT NOT NULL,
    progress INT DEFAULT 0,
    tanggal_enroll TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    tanggal_selesai TIMESTAMP NULL,
    status ENUM('active', 'completed', 'dropped') DEFAULT 'active',
    FOREIGN KEY (id_siswa) REFERENCES lms_users(id),
    FOREIGN KEY (id_course) REFERENCES lms_courses(id)
);

-- Tabel lms_lessons
CREATE TABLE lms_lessons (
    id INT PRIMARY KEY AUTO_INCREMENT,
    id_course INT NOT NULL,
    judul_lesson VARCHAR(255) NOT NULL,
    konten TEXT,
    video_url VARCHAR(500) DEFAULT NULL,
    tipe_lesson ENUM('teks', 'video', 'quiz', 'tugas') DEFAULT 'teks',
    urutan INT NOT NULL,
    durasi_menit INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (id_course) REFERENCES lms_courses(id)
);

-- Tabel lms_quizzes
CREATE TABLE lms_quizzes (
    id INT PRIMARY KEY AUTO_INCREMENT,
    id_lesson INT NOT NULL,
    judul_quiz VARCHAR(255) NOT NULL,
    deskripsi TEXT,
    waktu_menit INT DEFAULT 30,
    jumlah_soal INT DEFAULT 0,
    passing_grade INT DEFAULT 60,
    acak_soal TINYINT(1) DEFAULT 1,
    tampilkan_nilai TINYINT(1) DEFAULT 1,
    batas_percobaan INT DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (id_lesson) REFERENCES lms_lessons(id)
);

-- Tabel lms_questions
CREATE TABLE lms_questions (
    id INT PRIMARY KEY AUTO_INCREMENT,
    id_quiz INT NOT NULL,
    pertanyaan TEXT NOT NULL,
    tipe ENUM('pilihan_ganda', 'essay') DEFAULT 'pilihan_ganda',
    opsi_a VARCHAR(255),
    opsi_b VARCHAR(255),
    opsi_c VARCHAR(255),
    opsi_d VARCHAR(255),
    jawaban_benar CHAR(1),
    poin INT DEFAULT 1,
    FOREIGN KEY (id_quiz) REFERENCES lms_quizzes(id)
);

-- Tabel lms_quiz_attempts
CREATE TABLE lms_quiz_attempts (
    id INT PRIMARY KEY AUTO_INCREMENT,
    id_siswa INT NOT NULL,
    id_quiz INT NOT NULL,
    nilai DECIMAL(5,2),
    waktu_mulai TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    waktu_selesai TIMESTAMP NULL,
    status ENUM('in_progress', 'completed') DEFAULT 'in_progress',
    FOREIGN KEY (id_siswa) REFERENCES lms_users(id),
    FOREIGN KEY (id_quiz) REFERENCES lms_quizzes(id)
);


-- Data dummy
INSERT INTO lms_users (username, password, role, nama_lengkap, email) VALUES
('admin', '$2y$10$OVx9G2Cl8Baex4IE3r09hOhU1tSIRyrAvf7Y6AGMxE3vyKn9TlZWm', 'admin', 'Administrator', 'admin@smansab.id'),
('198503032010011018', '$2y$10$OVx9G2Cl8Baex4IE3r09hOhU1tSIRyrAvf7Y6AGMxE3vyKn9TlZWm', 'guru', 'Supriyanto, S.Pd', 'supri@smansab.id'),
('199103042022211019', '$2y$10$OVx9G2Cl8Baex4IE3r09hOhU1tSIRyrAvf7Y6AGMxE3vyKn9TlZWm', 'guru', 'Didik Purwanto, S.Pd', 'didik@smansab.id'),
('199206072022211021', '$2y$10$OVx9G2Cl8Baex4IE3r09hOhU1tSIRyrAvf7Y6AGMxE3vyKn9TlZWm', 'guru', 'Muhammad Nadir, S.Pd', 'nadir@smansab.id'),
('siswa1', '$2y$10$OVx9G2Cl8Baex4IE3r09hOhU1tSIRyrAvf7Y6AGMxE3vyKn9TlZWm', 'siswa', 'Andi Wijaya', 'andi@smansab.id'),
('siswa2', '$2y$10$OVx9G2Cl8Baex4IE3r09hOhU1tSIRyrAvf7Y6AGMxE3vyKn9TlZWm', 'siswa', 'Rina Melati', 'rina@smansab.id');