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

CREATE TABLE IF NOT EXISTS `accounts` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `username` VARCHAR(100) NOT NULL,
  `password_hash` VARCHAR(255) NOT NULL,
  `displayname` VARCHAR(255) NOT NULL,
  `role` ENUM('SUPER_ADMIN', 'ADMIN') NOT NULL DEFAULT 'ADMIN',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_accounts_username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `challengelevel` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `level` INT UNSIGNED NOT NULL,
  `text` VARCHAR(255) NOT NULL,
  `icon` VARCHAR(500) DEFAULT NULL,
  `sort_order` INT NOT NULL DEFAULT 1,
  `time_limit_seconds` INT UNSIGNED NOT NULL DEFAULT 60,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_challengelevel_level` (`level`),
  KEY `idx_challengelevel_order` (`is_active`, `sort_order`, `level`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `questions` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `type` TINYINT UNSIGNED NOT NULL,
  `question` TEXT NOT NULL,
  `hint` TEXT NULL,
  `assetUrl` TEXT NULL,
  `ChallengeLevel` INT UNSIGNED NOT NULL DEFAULT 1,
  `answers` JSON NOT NULL,
  `correct_index` TINYINT UNSIGNED NOT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_questions_type` (`type`),
  KEY `idx_questions_challenge_level` (`ChallengeLevel`),
  CONSTRAINT `chk_questions_type` CHECK (`type` IN (0, 1, 2, 3))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `playrecord` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `region` VARCHAR(255) NOT NULL,
  `result` ENUM('WIN', 'LOSS') NOT NULL,
  `correct_count` INT UNSIGNED NOT NULL,
  `required_correct_count` INT UNSIGNED NOT NULL,
  `total_questions` INT UNSIGNED NOT NULL,
  `elapsed_time_seconds` INT UNSIGNED NOT NULL DEFAULT 0,
  `answer_details` JSON NOT NULL,
  `played_at` DATETIME NOT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_playrecord_region` (`region`),
  KEY `idx_playrecord_played_at` (`played_at`),
  KEY `idx_playrecord_result` (`result`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `challengelevel` (`level`, `text`, `icon`, `sort_order`, `time_limit_seconds`, `is_active`)
VALUES
  (1, 'Beginner', '/assets/challenge-level/level-1.svg', 1, 60, 1),
  (2, 'Intermediate', '/assets/challenge-level/level-2.svg', 2, 90, 1),
  (3, 'Advanced', '/assets/challenge-level/level-3.svg', 3, 120, 1),
  (4, 'Audio Challenge', '/assets/challenge-level/level-4.svg', 4, 180, 1)
ON DUPLICATE KEY UPDATE
  `text` = VALUES(`text`),
  `icon` = VALUES(`icon`),
  `sort_order` = VALUES(`sort_order`),
  `time_limit_seconds` = VALUES(`time_limit_seconds`),
  `is_active` = VALUES(`is_active`);

-- The password hashes are generated with bcrypt. Change these credentials after first login.
INSERT INTO `accounts` (`username`, `password_hash`, `displayname`, `role`)
VALUES
  ('superadmin', '$2b$10$rJa79RMh9FPfXx33EgIJKubrVv.sxB/NCHLqt11FmyiyDrB5enA1y', 'Super Administrator', 'SUPER_ADMIN'),
  ('admin', '$2b$10$owCYTq6tBu7pyfIrbUDWI.lG81iQIByDkbLHW/UVIHCBvjYKUVE3S', 'Administrator', 'ADMIN')
ON DUPLICATE KEY UPDATE `username` = VALUES(`username`);

INSERT INTO `questions` (`type`, `question`, `hint`, `assetUrl`, `ChallengeLevel`, `answers`, `correct_index`)
SELECT 0, 'What is the average flight velocity of an unladen swallow?', 'Think about the classic answer from Monty Python.', NULL, 1, JSON_ARRAY('African or European?', '24 mph', 'I don''t know'), 1
WHERE NOT EXISTS (SELECT 1 FROM `questions` WHERE `question` = 'What is the average flight velocity of an unladen swallow?');
INSERT INTO `questions` (`type`, `question`, `hint`, `assetUrl`, `ChallengeLevel`, `answers`, `correct_index`)
SELECT 1, 'Select the image that has cars', 'Look for a vehicle with four wheels.', 'https://picsum.photos/200?question=cars', 2, JSON_ARRAY('https://picsum.photos/200', 'https://picsum.photos/200', 'https://picsum.photos/200', 'https://picsum.photos/200'), 0
WHERE NOT EXISTS (SELECT 1 FROM `questions` WHERE `question` = 'Select the image that has cars');
INSERT INTO `questions` (`type`, `question`, `hint`, `assetUrl`, `ChallengeLevel`, `answers`, `correct_index`)
SELECT 2, 'Find the word BOOK', 'Choose the Vietnamese word for a book.', NULL, 3, JSON_ARRAY('Sách', 'Vở', 'Sạch', 'Sảnh'), 0
WHERE NOT EXISTS (SELECT 1 FROM `questions` WHERE `question` = 'Find the word BOOK');
INSERT INTO `questions` (`type`, `question`, `hint`, `assetUrl`, `ChallengeLevel`, `answers`, `correct_index`)
SELECT 3, 'Nghe âm thanh và chọn đáp án đúng', 'Lắng nghe kỹ âm đầu tiên.', 'https://tatoeba.org/en/audio/download/836321', 4, JSON_ARRAY('I''m fine, thank you. And you?', 'The fault lies within you!', 'Yo, roshiku!', 'Here''s ~ Johnny!'), 0
WHERE NOT EXISTS (SELECT 1 FROM `questions` WHERE `assetUrl` = 'https://tatoeba.org/en/audio/download/836321');