USE `jiznksyv_vuscrocodile2026`;

-- Run once against an existing questions table.
ALTER TABLE `questions`
  ADD COLUMN `hint` TEXT NULL AFTER `question`,
  ADD COLUMN `assetUrl` TEXT NULL AFTER `hint`,
  ADD COLUMN `ChallengeLevel` INT UNSIGNED NOT NULL DEFAULT 1 AFTER `assetUrl`,
  ADD KEY `idx_questions_challenge_level` (`ChallengeLevel`);

-- Existing type 3 records that stored the audio URL in question are migrated
-- to the new contract: question is text and assetUrl stores the audio URL.
UPDATE `questions`
SET
  `assetUrl` = `question`,
  `question` = 'Nghe âm thanh và chọn đáp án đúng'
WHERE `type` = 3
  AND (`assetUrl` IS NULL OR `assetUrl` = '')
  AND `question` REGEXP '^https?://';