-- 004_kraken_definitions
--
-- Kraken is the timed mental-arithmetic mode. This file is the authored side:
-- belts (progression ladder), difficulties, and the kraken expeditions that
-- pair a notion_sub with a set of belts and per-difficulty timings.
-- Player sessions and progress live in 010, after players exists.

-- Ordered progression ladder shared across kraken expeditions.
CREATE TABLE IF NOT EXISTS `kraken_belts` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `color_hex` char(7) DEFAULT NULL,
  `sort_order` int NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- reward_id is the payout for clearing at this difficulty.
CREATE TABLE IF NOT EXISTS `kraken_difficulties` (
  `id` int NOT NULL AUTO_INCREMENT,
  `code` varchar(32) NOT NULL,
  `name` varchar(255) NOT NULL,
  `sort_order` int NOT NULL DEFAULT '0',
  `reward_id` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_kd_code` (`code`),
  KEY `idx_kd_reward` (`reward_id`),
  CONSTRAINT `fk_kd_reward` FOREIGN KEY (`reward_id`) REFERENCES `rewards` (`reward_id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE IF NOT EXISTS `kraken_expeditions` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `simple_name` varchar(255) DEFAULT NULL,
  `notion_sub_id` int NOT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `is_official` tinyint(1) NOT NULL DEFAULT '1',
  `activated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_ke_notion_sub` (`notion_sub_id`),
  CONSTRAINT `fk_ke_notion_sub` FOREIGN KEY (`notion_sub_id`) REFERENCES `notion_subs` (`id`) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Places a kraken expedition at a position within a belt. The primary key is
-- kraken_expedition_id alone, so an expedition sits on exactly one belt, and
-- uq_keb_belt_position keeps two expeditions off the same slot of a belt.
CREATE TABLE IF NOT EXISTS `kraken_expedition_belts` (
  `kraken_expedition_id` int NOT NULL,
  `belt_id` int NOT NULL,
  `position` int NOT NULL DEFAULT '0',
  PRIMARY KEY (`kraken_expedition_id`),
  UNIQUE KEY `uq_keb_belt_position` (`belt_id`,`position`),
  KEY `idx_keb_belt` (`belt_id`),
  CONSTRAINT `fk_keb_belt` FOREIGN KEY (`belt_id`) REFERENCES `kraken_belts` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_keb_expedition` FOREIGN KEY (`kraken_expedition_id`) REFERENCES `kraken_expeditions` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Per-difficulty tuning: how long the run lasts and how many questions it asks.
CREATE TABLE IF NOT EXISTS `kraken_expedition_difficulties` (
  `kraken_expedition_id` int NOT NULL,
  `difficulty_id` int NOT NULL,
  `duration_seconds` int NOT NULL DEFAULT '60',
  `question_count` int NOT NULL DEFAULT '10',
  PRIMARY KEY (`kraken_expedition_id`,`difficulty_id`),
  KEY `idx_ked_difficulty` (`difficulty_id`),
  CONSTRAINT `fk_ked_difficulty` FOREIGN KEY (`difficulty_id`) REFERENCES `kraken_difficulties` (`id`) ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT `fk_ked_expedition` FOREIGN KEY (`kraken_expedition_id`) REFERENCES `kraken_expeditions` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
