-- 008_player_progression
--
-- What a player has done. The save pointer is the resume position; the
-- progress tables are the durable record the teacher dashboard reads.
--
-- Timestamps here are bigint unix seconds, not TIMESTAMP columns: they come
-- from the Unity client, which owns the clock for gameplay events.

-- One row per player. The pointer_* trio is where the client resumes.
CREATE TABLE IF NOT EXISTS `player_saves` (
  `player_id` bigint NOT NULL,
  `schema_version` int NOT NULL DEFAULT '2',
  `app_version` varchar(32) DEFAULT NULL,
  `last_updated_unix` bigint DEFAULT NULL,
  `pointer_expedition_id` int NOT NULL DEFAULT '0',
  `pointer_set_number` int NOT NULL DEFAULT '0',
  `pointer_question_index` int NOT NULL DEFAULT '0',
  PRIMARY KEY (`player_id`),
  CONSTRAINT `player_saves_ibfk_1` FOREIGN KEY (`player_id`) REFERENCES `players` (`player_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- current_playthrough increments each time the player replays the expedition,
-- which is how a replay is scored at the reduced replay_*_pct rate.
CREATE TABLE IF NOT EXISTS `player_expedition_progress` (
  `player_id` bigint NOT NULL,
  `expedition_id` int NOT NULL,
  `current_playthrough` int NOT NULL DEFAULT '1',
  `completed_sets` int NOT NULL DEFAULT '0',
  PRIMARY KEY (`player_id`,`expedition_id`),
  KEY `expedition_id` (`expedition_id`),
  CONSTRAINT `player_expedition_progress_ibfk_1` FOREIGN KEY (`player_id`) REFERENCES `players` (`player_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `player_expedition_progress_ibfk_2` FOREIGN KEY (`expedition_id`) REFERENCES `expeditions` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Position within the current playthrough of one set. The composite FK to
-- player_expedition_progress means the parent row must exist first.
CREATE TABLE IF NOT EXISTS `player_set_progress` (
  `player_id` bigint NOT NULL,
  `expedition_id` int NOT NULL,
  `set_number` int NOT NULL,
  `last_question_index` int NOT NULL DEFAULT '0',
  `set_completed` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`player_id`,`expedition_id`,`set_number`),
  CONSTRAINT `player_set_progress_ibfk_1` FOREIGN KEY (`player_id`,`expedition_id`) REFERENCES `player_expedition_progress` (`player_id`, `expedition_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- First-clear ledger, kept separate from player_set_progress because that one
-- is reset by a replay while this one must survive it: it is what decides
-- whether a reward pays full or replay rate.
CREATE TABLE IF NOT EXISTS `player_completed_sets` (
  `player_id` bigint NOT NULL,
  `expedition_id` int NOT NULL,
  `set_number` int NOT NULL,
  `first_completed_at_unix` int NOT NULL,
  PRIMARY KEY (`player_id`,`expedition_id`,`set_number`),
  KEY `player_completed_sets_ibfk_2` (`expedition_id`),
  CONSTRAINT `player_completed_sets_ibfk_1` FOREIGN KEY (`player_id`) REFERENCES `players` (`player_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `player_completed_sets_ibfk_2` FOREIGN KEY (`expedition_id`) REFERENCES `expeditions` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Append-only. One row per answered question, tagged with the playthrough so
-- the dashboard can report on a single run rather than a lifetime average.
CREATE TABLE IF NOT EXISTS `player_question_attempts` (
  `attempt_id` bigint NOT NULL AUTO_INCREMENT,
  `player_id` bigint NOT NULL,
  `expedition_id` int NOT NULL,
  `set_number` int NOT NULL,
  `playthrough` int NOT NULL DEFAULT '1',
  `question_id` int NOT NULL,
  `selected_answer_ids` json NOT NULL,
  `was_correct` tinyint(1) NOT NULL,
  `attempted_at_unix` bigint NOT NULL,
  PRIMARY KEY (`attempt_id`),
  KEY `idx_player_expedition` (`player_id`,`expedition_id`),
  KEY `idx_question` (`question_id`),
  KEY `player_question_attempts_ibfk_2` (`expedition_id`),
  KEY `idx_player_exp_playthrough` (`player_id`,`expedition_id`,`playthrough`),
  CONSTRAINT `player_question_attempts_ibfk_1` FOREIGN KEY (`player_id`) REFERENCES `players` (`player_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `player_question_attempts_ibfk_2` FOREIGN KEY (`expedition_id`) REFERENCES `expeditions` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `player_question_attempts_ibfk_3` FOREIGN KEY (`question_id`) REFERENCES `questions` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
