-- SQL Schema for Smart English Vocabulary Memorization


-- 1. Table users (Admin & Siswa)
CREATE TABLE IF NOT EXISTS `users` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `username` VARCHAR(50) NOT NULL UNIQUE,
  `password` VARCHAR(255) NOT NULL,
  `role` ENUM('admin', 'siswa') NOT NULL,
  `nama_lengkap` VARCHAR(100) NOT NULL,
  `level` INT DEFAULT 1,
  `total_kata` INT DEFAULT 0,
  `no_hp` VARCHAR(20) DEFAULT NULL,
  `foto_profil` VARCHAR(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 2. Table categories (Kategori Kosa Kata)
CREATE TABLE IF NOT EXISTS `categories` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(100) NOT NULL UNIQUE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 3. Table vocabularies (Kamus Kosa Kata hasil AI)
CREATE TABLE IF NOT EXISTS `vocabularies` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `category_id` INT NOT NULL,
  `word` VARCHAR(100) NOT NULL,
  `meaning` VARCHAR(100) NOT NULL,
  `ipa` VARCHAR(100) NOT NULL,
  `cara_baca` VARCHAR(100) NOT NULL,
  `example` TEXT NOT NULL,
  `translation` TEXT NOT NULL,
  `image_url` VARCHAR(255) DEFAULT NULL,
  FOREIGN KEY (`category_id`) REFERENCES `categories` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 4. Table packages (Sesi / Paket Belajar Siswa)
CREATE TABLE IF NOT EXISTS `packages` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `siswa_id` INT NOT NULL,
  `category_id` INT NOT NULL,
  `jumlah_kata` INT NOT NULL,
  `status` ENUM('belajar', 'pending', 'disetujui', 'ditolak') DEFAULT 'belajar',
  `catatan_guru` TEXT DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`siswa_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  FOREIGN KEY (`category_id`) REFERENCES `categories` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 5. Table package_items (Detail kosakata di dalam paket)
CREATE TABLE IF NOT EXISTS `package_items` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `package_id` INT NOT NULL,
  `vocab_id` INT NOT NULL,
  `is_memorized` BOOLEAN DEFAULT FALSE,
  FOREIGN KEY (`package_id`) REFERENCES `packages` (`id`) ON DELETE CASCADE,
  FOREIGN KEY (`vocab_id`) REFERENCES `vocabularies` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 6. Table notifications (Sistem Notifikasi)
CREATE TABLE IF NOT EXISTS `notifications` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `user_id` INT NOT NULL,
  `title` VARCHAR(100) NOT NULL,
  `message` TEXT NOT NULL,
  `is_read` BOOLEAN DEFAULT FALSE,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- --------------------------------------------------------
-- DUMMY DATA SEEDING
-- --------------------------------------------------------

-- Insert default Admin (Password: admin)
-- Insert default Siswa (Password: siswa)
INSERT INTO `users` (`id`, `username`, `password`, `role`, `nama_lengkap`, `level`, `total_kata`) VALUES
(1, 'admin', '$2y$10$FIJ6JwYgtm624SuDBhCcWuJD9jQeWM9UneKro7PLKLT9arWbbLS0i', 'admin', 'Guru / Admin', 1, 0),
(2, 'siswa', '$2y$10$tzqUwgC4vJGdgB1dLEXMbe5plQY0cIcgzd40fBSpG9DR30qz9pfwC', 'siswa', 'Andi Saputra', 1, 0)
ON DUPLICATE KEY UPDATE `id`=`id`;

-- Insert default categories
INSERT INTO `categories` (`id`, `name`) VALUES
(1, 'Animals'),
(2, 'Fruits'),
(3, 'Vegetables'),
(4, 'School'),
(5, 'Family'),
(6, 'Transportation'),
(7, 'Profession'),
(8, 'Daily Activities'),
(9, 'Food & Drink'),
(10, 'Technology')
ON DUPLICATE KEY UPDATE `id`=`id`;