-- 007_content_access
--
-- Who may play what. Access is granted at three widths - class, subgroup, and
-- individual player - and the resolver in ContentAccess unions them.
--
-- Note the asymmetry, which is intentional: the class and subgroup tables only
-- grant, while the player tables carry a `mode` of grant or block, so a teacher
-- can open an expedition to a whole class and still close it for one pupil.
--
-- granted_by is ON DELETE SET NULL everywhere: a teacher leaving must not
-- revoke the access they handed out.

CREATE TABLE IF NOT EXISTS `class_expedition_access` (
  `class_id` bigint NOT NULL,
  `expedition_id` int NOT NULL,
  `granted_by` bigint DEFAULT NULL,
  `granted_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`class_id`,`expedition_id`),
  KEY `idx_cea_expedition` (`expedition_id`),
  KEY `idx_cea_granted_by` (`granted_by`),
  CONSTRAINT `fk_cea_class` FOREIGN KEY (`class_id`) REFERENCES `classes` (`class_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_cea_expedition` FOREIGN KEY (`expedition_id`) REFERENCES `expeditions` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_cea_granted_by` FOREIGN KEY (`granted_by`) REFERENCES `establishment_admins` (`ea_id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE IF NOT EXISTS `class_kraken_access` (
  `class_id` bigint NOT NULL,
  `kraken_expedition_id` int NOT NULL,
  `granted_by` bigint DEFAULT NULL,
  `granted_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`class_id`,`kraken_expedition_id`),
  KEY `idx_cka_granted_by` (`granted_by`),
  KEY `idx_cka_kraken_expedition` (`kraken_expedition_id`),
  CONSTRAINT `fk_cka_class` FOREIGN KEY (`class_id`) REFERENCES `classes` (`class_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_cka_kraken_expedition` FOREIGN KEY (`kraken_expedition_id`) REFERENCES `kraken_expeditions` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_cka_granted_by` FOREIGN KEY (`granted_by`) REFERENCES `establishment_admins` (`ea_id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE IF NOT EXISTS `subgroup_expedition_access` (
  `subgroup_id` bigint NOT NULL,
  `expedition_id` int NOT NULL,
  `granted_by` bigint DEFAULT NULL,
  `granted_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`subgroup_id`,`expedition_id`),
  KEY `idx_sea_expedition` (`expedition_id`),
  KEY `idx_sea_granted_by` (`granted_by`),
  CONSTRAINT `fk_sea_subgroup` FOREIGN KEY (`subgroup_id`) REFERENCES `class_subgroups` (`subgroup_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_sea_expedition` FOREIGN KEY (`expedition_id`) REFERENCES `expeditions` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_sea_granted_by` FOREIGN KEY (`granted_by`) REFERENCES `establishment_admins` (`ea_id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE IF NOT EXISTS `subgroup_kraken_access` (
  `subgroup_id` bigint NOT NULL,
  `kraken_expedition_id` int NOT NULL,
  `granted_by` bigint DEFAULT NULL,
  `granted_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`subgroup_id`,`kraken_expedition_id`),
  KEY `idx_ska_kraken_expedition` (`kraken_expedition_id`),
  KEY `idx_ska_granted_by` (`granted_by`),
  CONSTRAINT `fk_ska_subgroup` FOREIGN KEY (`subgroup_id`) REFERENCES `class_subgroups` (`subgroup_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_ska_kraken_expedition` FOREIGN KEY (`kraken_expedition_id`) REFERENCES `kraken_expeditions` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_ska_granted_by` FOREIGN KEY (`granted_by`) REFERENCES `establishment_admins` (`ea_id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- mode = 'block' overrides any class or subgroup grant for this one player.
CREATE TABLE IF NOT EXISTS `player_expedition_access` (
  `player_id` bigint NOT NULL,
  `expedition_id` int NOT NULL,
  `mode` enum('grant','block') NOT NULL DEFAULT 'grant',
  `granted_by` bigint DEFAULT NULL,
  `granted_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`player_id`,`expedition_id`),
  KEY `idx_pea_expedition` (`expedition_id`),
  KEY `idx_pea_granted_by` (`granted_by`),
  CONSTRAINT `fk_pea_player` FOREIGN KEY (`player_id`) REFERENCES `players` (`player_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_pea_expedition` FOREIGN KEY (`expedition_id`) REFERENCES `expeditions` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_pea_granted_by` FOREIGN KEY (`granted_by`) REFERENCES `establishment_admins` (`ea_id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE IF NOT EXISTS `player_kraken_access` (
  `player_id` bigint NOT NULL,
  `kraken_expedition_id` int NOT NULL,
  `mode` enum('grant','block') NOT NULL DEFAULT 'grant',
  `granted_by` bigint DEFAULT NULL,
  `granted_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`player_id`,`kraken_expedition_id`),
  KEY `idx_pka_granted_by` (`granted_by`),
  KEY `idx_pka_kraken_expedition` (`kraken_expedition_id`),
  CONSTRAINT `fk_pka_player` FOREIGN KEY (`player_id`) REFERENCES `players` (`player_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_pka_kraken_expedition` FOREIGN KEY (`kraken_expedition_id`) REFERENCES `kraken_expeditions` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_pka_granted_by` FOREIGN KEY (`granted_by`) REFERENCES `establishment_admins` (`ea_id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Read-state for the teacher dashboard: which new expeditions a teacher has
-- already looked at. Not an access grant, but it keys off the same pair.
CREATE TABLE IF NOT EXISTS `admin_seen_expeditions` (
  `ea_id` bigint NOT NULL,
  `expedition_id` int NOT NULL,
  `seen_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`ea_id`,`expedition_id`),
  KEY `idx_ase_expedition` (`expedition_id`),
  CONSTRAINT `fk_ase_admin` FOREIGN KEY (`ea_id`) REFERENCES `establishment_admins` (`ea_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_ase_expedition` FOREIGN KEY (`expedition_id`) REFERENCES `expeditions` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
