-- 003_expeditions_and_questions
--
-- Authored content: an expedition holds ordered question sets, each set holds
-- questions (with answers), an optional NPC intro, and story beats. All the
-- player-facing text sits in *_translations tables keyed by language_code.

-- notion_id points at notion_subs, not notions, despite the column name.
CREATE TABLE IF NOT EXISTS `expeditions` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `notion_id` int NOT NULL,
  `archipelago_id` int NOT NULL,
  `image_url` text NOT NULL,
  `unlock_code` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT '0',
  `activated_at` timestamp NULL DEFAULT NULL,
  `is_official` tinyint(1) NOT NULL DEFAULT '1',
  `npc_id` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `island_id` (`archipelago_id`),
  KEY `expeditions_ibfk_2` (`notion_id`),
  KEY `idx_expedition_npc` (`npc_id`),
  CONSTRAINT `expeditions_ibfk_1` FOREIGN KEY (`archipelago_id`) REFERENCES `archipelagos` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `expeditions_ibfk_2` FOREIGN KEY (`notion_id`) REFERENCES `notion_subs` (`id`) ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT `fk_expedition_npc` FOREIGN KEY (`npc_id`) REFERENCES `npcs` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- `reward` is the reward granted on completing the set.
CREATE TABLE IF NOT EXISTS `question_sets` (
  `id` int NOT NULL AUTO_INCREMENT,
  `expedition_id` int NOT NULL,
  `question_set_number` int NOT NULL,
  `reward` int DEFAULT '1',
  `is_active` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `expedition_id` (`expedition_id`),
  KEY `reward` (`reward`),
  CONSTRAINT `question_sets_ibfk_1` FOREIGN KEY (`expedition_id`) REFERENCES `expeditions` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `question_sets_ibfk_2` FOREIGN KEY (`reward`) REFERENCES `rewards` (`reward_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- `method` selects the Unity answer template; `randomize` shuffles the choices.
CREATE TABLE IF NOT EXISTS `questions` (
  `id` int NOT NULL AUTO_INCREMENT,
  `question_set_id` int NOT NULL,
  `order_in_set` int NOT NULL,
  `method` enum('template_4','template_6','template_nb','') NOT NULL,
  `multiple_answers` tinyint NOT NULL,
  `randomize` tinyint DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `question_set_id` (`question_set_id`),
  CONSTRAINT `questions_ibfk_1` FOREIGN KEY (`question_set_id`) REFERENCES `question_sets` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE IF NOT EXISTS `question_translations` (
  `id` int NOT NULL AUTO_INCREMENT,
  `question_id` int NOT NULL,
  `language_code` varchar(10) NOT NULL DEFAULT 'FR',
  `question_title` text NOT NULL,
  `question_text` text NOT NULL,
  `question_simplified` text,
  `question_img_url` text NOT NULL,
  `remediation_text` text NOT NULL,
  `remediation_img_url` text NOT NULL,
  `assist_text` text NOT NULL,
  `assist_title` varchar(255) NOT NULL DEFAULT '',
  `assist_img_url` text NOT NULL DEFAULT (_utf8mb4''),
  PRIMARY KEY (`id`),
  KEY `question_id` (`question_id`),
  CONSTRAINT `question_translations_ibfk_1` FOREIGN KEY (`question_id`) REFERENCES `questions` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- The answer row carries only correctness; the label is in the translation.
CREATE TABLE IF NOT EXISTS `answers` (
  `id` int NOT NULL AUTO_INCREMENT,
  `question_id` int NOT NULL,
  `correct` tinyint(1) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `question_id` (`question_id`),
  CONSTRAINT `answers_ibfk_1` FOREIGN KEY (`question_id`) REFERENCES `questions` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE IF NOT EXISTS `answer_translations` (
  `id` int NOT NULL AUTO_INCREMENT,
  `answer_id` int NOT NULL,
  `language_code` varchar(10) NOT NULL,
  `answer_text` varchar(200) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `answer_id` (`answer_id`),
  CONSTRAINT `answer_translations_ibfk_1` FOREIGN KEY (`answer_id`) REFERENCES `answers` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- The NPC line shown before a set starts.
CREATE TABLE IF NOT EXISTS `question_set_intros` (
  `id` int NOT NULL AUTO_INCREMENT,
  `question_set_id` int NOT NULL,
  `npc_id` int NOT NULL,
  PRIMARY KEY (`id`),
  KEY `question_set_id` (`question_set_id`),
  KEY `npc_id` (`npc_id`),
  CONSTRAINT `question_set_intros_ibfk_1` FOREIGN KEY (`question_set_id`) REFERENCES `question_sets` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `question_set_intros_ibfk_2` FOREIGN KEY (`npc_id`) REFERENCES `npcs` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- No FK on question_set_intro_id in the live schema; kept as-is.
CREATE TABLE IF NOT EXISTS `question_set_intro_translations` (
  `id` int NOT NULL AUTO_INCREMENT,
  `question_set_intro_id` int NOT NULL,
  `language_code` varchar(5) NOT NULL,
  `intro_text` text NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Narrative beats interleaved with the questions of a set.
CREATE TABLE IF NOT EXISTS `story_points` (
  `id` int NOT NULL AUTO_INCREMENT,
  `question_set_id` int NOT NULL,
  `order_in_set` int NOT NULL,
  PRIMARY KEY (`id`),
  KEY `question_set_id` (`question_set_id`),
  CONSTRAINT `story_points_ibfk_1` FOREIGN KEY (`question_set_id`) REFERENCES `question_sets` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE IF NOT EXISTS `story_point_translations` (
  `id` int NOT NULL AUTO_INCREMENT,
  `story_point_id` int NOT NULL,
  `language_code` varchar(10) NOT NULL DEFAULT 'FR',
  `story_title` varchar(255) NOT NULL DEFAULT '',
  `story_text` text NOT NULL,
  `story_img_url` text NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_story_lang` (`story_point_id`,`language_code`),
  CONSTRAINT `story_point_translations_ibfk_1` FOREIGN KEY (`story_point_id`) REFERENCES `story_points` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Word problems, classified by Vergnaud structure. Standalone from expeditions.
CREATE TABLE IF NOT EXISTS `problems` (
  `id` int NOT NULL AUTO_INCREMENT,
  `vergnaud` varchar(10) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `vergnaud` (`vergnaud`),
  CONSTRAINT `problems_ibfk_1` FOREIGN KEY (`vergnaud`) REFERENCES `vergnaud_classifications` (`code`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE IF NOT EXISTS `problem_translations` (
  `id` int NOT NULL AUTO_INCREMENT,
  `problem_id` int NOT NULL,
  `language_code` varchar(10) NOT NULL DEFAULT 'FR',
  `problem_title` text NOT NULL,
  `problem_text` text NOT NULL,
  `problem_simplified` text,
  `answer` int NOT NULL,
  `problem_img_url` text NOT NULL,
  `assist_text` text NOT NULL,
  PRIMARY KEY (`id`),
  KEY `problem_id` (`problem_id`),
  CONSTRAINT `problem_translations_ibfk_1` FOREIGN KEY (`problem_id`) REFERENCES `problems` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
