

-- 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;

-- 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`;
