CREATE DATABASE IF NOT EXISTS `arabic_learning` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE `arabic_learning`;

-- 1. TABEL SETTINGS
CREATE TABLE IF NOT EXISTS `settings` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `school_name` VARCHAR(150) NOT NULL,
  `year` VARCHAR(20) NOT NULL,
  `semester` VARCHAR(20) NOT NULL,
  `teacher_name` VARCHAR(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `settings` (`id`, `school_name`, `year`, `semester`, `teacher_name`) VALUES
(1, 'SMP Muhammadiyah Subaim', '2026/2027', 'Ganjil', 'Ustadz Ahmad Dahlan, S.Pd.I.');

-- 2. TABEL USERS
CREATE TABLE IF NOT EXISTS `users` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(100) NOT NULL,
  `username` VARCHAR(50) UNIQUE NOT NULL,
  `password` VARCHAR(255) NOT NULL,
  `role` ENUM('admin', 'guru', 'siswa') DEFAULT 'siswa',
  `class_id` VARCHAR(20) DEFAULT '-',
  `gender` ENUM('L', 'P') DEFAULT 'L',
  `avatar` VARCHAR(255) DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `users` (`id`, `name`, `username`, `password`, `role`, `class_id`, `gender`, `avatar`) VALUES
(1, 'Ustadz Admin', 'admin', 'admin123', 'admin', '-', 'L', 'https://placehold.co/100x100/008751/FFFFFF?text=Guru'),
(2, 'Ahmad Fauzi', 'siswa', 'siswa123', 'siswa', 'VII-A', 'L', 'https://placehold.co/100x100/d4af37/000000?text=Siswa'),
(3, 'Siti Aminah', 'siti', 'siswa123', 'siswa', 'VIII-B', 'P', 'https://placehold.co/100x100/008751/FFFFFF?text=Siti');

-- 3. TABEL CLASSES
CREATE TABLE IF NOT EXISTS `classes` (
  `id` VARCHAR(20) PRIMARY KEY,
  `name` VARCHAR(50) NOT NULL,
  `teacher` VARCHAR(100) NOT NULL,
  `count` INT DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `classes` (`id`, `name`, `teacher`, `count`) VALUES
('VII-A', 'Kelas VII-A', 'Ustadz Ahmad', 28),
('VIII-B', 'Kelas VIII-B', 'Ustadzah Fatimah', 30),
('IX-C', 'Kelas IX-C', 'Ustadz Dahlan', 25);

-- 4. TABEL MATERIALS
CREATE TABLE IF NOT EXISTS `materials` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `title` VARCHAR(150) NOT NULL,
  `category` VARCHAR(50) NOT NULL,
  `class_level` VARCHAR(10) NOT NULL,
  `semester` VARCHAR(10) NOT NULL,
  `arabic` TEXT NOT NULL,
  `transliteration` TEXT,
  `translation` TEXT NOT NULL,
  `description` TEXT,
  `image` TEXT,
  `audio` TEXT,
  `status` VARCHAR(20) DEFAULT 'published'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `materials` (`id`, `title`, `category`, `class_level`, `semester`, `arabic`, `transliteration`, `translation`, `description`, `image`, `audio`, `status`) VALUES
(1, 'التعارف (Perkenalan)', 'Hiwar (Percakapan)', 'VII', '1', 'السَّلاَمُ عَلَيْكُمْ وَرَحْمَةُ اللهِ وَبَرَكَاتُهُ', 'Assalamu\'alaikum Warahmatullahi Wabarakatuh', 'Semoga keselamatan, rahmat Allah, dan berkah-Nya tercurah kepadamu.', 'Materi dasar mengenai tata cara berkenalan dalam bahasa Arab untuk siswa kelas VII.', 'https://placehold.co/600x300/008751/FFFFFF?text=At-Ta\'aruf+(Perkenalan)', 'https://www.soundhelix.com/examples/mp3/SoundHelix-Song-1.mp3', 'published'),
(2, 'الأَدَوَاتُ المَدْرَسِيَّةُ (Peralatan Sekolah)', 'Mufradat (Kosakata)', 'VII', '1', 'هَذَا كِتَابٌ وَهَذِهِ كُرَّاسَةٌ', 'Hadza Kitabun wa Hadzihi Kurrasatun', 'Ini adalah buku pelajaran dan ini adalah buku tulis.', 'Mengenal berbagai macam benda dan peralatan yang ada di dalam kelas.', 'https://placehold.co/600x300/d4af37/000000?text=Peralatan+Sekolah', '', 'published');

-- 5. TABEL QUESTIONS
CREATE TABLE IF NOT EXISTS `questions` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `type` VARCHAR(20) NOT NULL,
  `text` TEXT NOT NULL,
  `options_json` TEXT NOT NULL,
  `answer` VARCHAR(255) NOT NULL,
  `score_weight` INT DEFAULT 20,
  `class_level` VARCHAR(10) DEFAULT 'VII'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `questions` (`id`, `type`, `text`, `options_json`, `answer`, `score_weight`, `class_level`) VALUES
(1, 'pg', 'Apa arti dari kata \'كِتَابٌ\' (Kitabun)?', '["Pena","Buku","Meja","Papan Tulis"]', 'Buku', 20, 'VII'),
(2, 'bs', 'Kalimat \'السَّلاَمُ عَلَيْكُمْ\' digunakan untuk menyapa seseorang.', '["Benar","Salah"]', 'Benar', 20, 'VII');

-- 6. TABEL QUIZZES
CREATE TABLE IF NOT EXISTS `quizzes` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `title` VARCHAR(150) NOT NULL,
  `class_level` VARCHAR(10) NOT NULL,
  `duration` INT NOT NULL,
  `question_ids_json` TEXT NOT NULL,
  `active` TINYINT(1) DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `quizzes` (`id`, `title`, `class_level`, `duration`, `question_ids_json`, `active`) VALUES
(1, 'Latihan Harian Perkenalan (At-Ta\'aruf)', 'VII', 15, '[1,2]', 1);

-- 7. TABEL QUIZ RESULTS
CREATE TABLE IF NOT EXISTS `quiz_results` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `quiz_id` INT NOT NULL,
  `student_id` INT NOT NULL,
  `student_name` VARCHAR(100) NOT NULL,
  `class_name` VARCHAR(20) NOT NULL,
  `quiz_title` VARCHAR(150) NOT NULL,
  `score` INT NOT NULL,
  `correct` INT NOT NULL,
  `wrong` INT NOT NULL,
  `date` DATE NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `quiz_results` (`id`, `quiz_id`, `student_id`, `student_name`, `class_name`, `quiz_title`, `score`, `correct`, `wrong`, `date`) VALUES
(1, 1, 2, 'Ahmad Fauzi', 'VII-A', 'Latihan Harian Perkenalan', 100, 2, 0, '2026-10-28');

-- 8. TABEL VOCABULARY
CREATE TABLE IF NOT EXISTS `vocabulary` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `arabic` VARCHAR(100) NOT NULL,
  `latin` VARCHAR(100) NOT NULL,
  `translation` VARCHAR(100) NOT NULL,
  `category` VARCHAR(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `vocabulary` (`id`, `arabic`, `latin`, `translation`, `category`) VALUES
(1, 'مَدْرَسَةٌ', 'Madrasatun', 'Sekolah', 'Kata Benda'),
(2, 'قَلَمٌ', 'Qalamun', 'Pena', 'Kata Benda'),
(3, 'يَقْرَأُ', 'Yaqra\'u', 'Membaca', 'Kata Kerja'),
(4, 'جَمِيْلٌ', 'Jamilun', 'Indah/Tampan', 'Kata Sifat');

-- 9. TABEL ANNOUNCEMENTS
CREATE TABLE IF NOT EXISTS `announcements` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `title` VARCHAR(150) NOT NULL,
  `date` DATE NOT NULL,
  `content` TEXT NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `announcements` (`id`, `title`, `date`, `content`) VALUES
(1, 'Persiapan Penilaian Tengah Semester (PTS)', '2026-10-25', 'Diharapkan seluruh siswa kelas VII, VIII, dan IX mempelajari materi kosakata Bab 1-3.');

-- 10. TABEL ASSIGNMENTS
CREATE TABLE IF NOT EXISTS `assignments` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `title` VARCHAR(150) NOT NULL,
  `class_level` VARCHAR(20) NOT NULL,
  `deadline` DATE NOT NULL,
  `status` VARCHAR(20) DEFAULT 'Aktif'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `assignments` (`id`, `title`, `class_level`, `deadline`, `status`) VALUES
(1, 'Tugas Membaca Teks Al-Hiwar', 'VII-A', '2026-11-05', 'Aktif');