-- 010_kraken_player_runtime
--
-- The player's side of Kraken: where they are on the ladder, and the log of
-- every timed run. Split from 004 because these tables reference players,
-- which only exists from 005 onwards.

-- One row per player: the belt and kraken expedition they are currently on.
CREATE TABLE IF NOT EXISTS `kraken_progress` (
  `player_id` bigint NOT NULL,
  `current_belt_id` int NOT NULL,
  `current_kraken_expedition_id` int NOT NULL,
  PRIMARY KEY (`player_id`),
  KEY `fk_kp_belt` (`current_belt_id`),
  KEY `fk_kp_expedition` (`current_kraken_expedition_id`),
  CONSTRAINT `kraken_progress_ibfk_1` FOREIGN KEY (`player_id`) REFERENCES `players` (`player_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_kp_belt` FOREIGN KEY (`current_belt_id`) REFERENCES `kraken_belts` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_kp_expedition` FOREIGN KEY (`current_kraken_expedition_id`) REFERENCES `kraken_expeditions` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- One row per attempt. `succeeded` is set when the run is scored, so a session
-- left at 0 is either in flight or abandoned.
CREATE TABLE IF NOT EXISTS `kraken_sessions` (
  `session_id` bigint NOT NULL AUTO_INCREMENT,
  `player_id` bigint NOT NULL,
  `kraken_expedition_id` int NOT NULL,
  `difficulty_id` int NOT NULL DEFAULT '1',
  `started_at_unix` bigint NOT NULL,
  `succeeded` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`session_id`),
  KEY `idx_player_notion` (`player_id`,`kraken_expedition_id`),
  KEY `fk_ks_difficulty` (`difficulty_id`),
  KEY `fk_ks_kraken_expedition` (`kraken_expedition_id`),
  CONSTRAINT `kraken_sessions_ibfk_1` FOREIGN KEY (`player_id`) REFERENCES `players` (`player_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_ks_kraken_expedition` FOREIGN KEY (`kraken_expedition_id`) REFERENCES `kraken_expeditions` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_ks_difficulty` FOREIGN KEY (`difficulty_id`) REFERENCES `kraken_difficulties` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Kraken questions are generated at run time, not authored, so the prompt is
-- stored as text and the answers as doubles rather than pointing at `answers`.
CREATE TABLE IF NOT EXISTS `kraken_answers` (
  `answer_id` bigint NOT NULL AUTO_INCREMENT,
  `session_id` bigint NOT NULL,
  `question_text` varchar(128) NOT NULL,
  `correct_answer` double NOT NULL,
  `given_answer` double NOT NULL,
  `was_correct` tinyint(1) NOT NULL,
  `answered_at_unix` bigint NOT NULL,
  PRIMARY KEY (`answer_id`),
  KEY `idx_session` (`session_id`),
  CONSTRAINT `kraken_answers_ibfk_1` FOREIGN KEY (`session_id`) REFERENCES `kraken_sessions` (`session_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
