USE `jiznksyv_vuscrocodile2026`;

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

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

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