-- 006_classes_and_membership
--
-- How players are grouped: a class belongs to an establishment, a subgroup
-- belongs to a class, and a player is attached to at most one of each.
--
-- The uniqueness rules here are load-bearing:
--   uq_cp_player  - a player is in one class, full stop
--   uq_lp_player  - a player redeems one individual licence
-- Subgroup membership is deliberately not unique: a player can be in several
-- subgroups of their class at once.

-- join_code is what a pupil types to enter the class, so it is globally unique.
CREATE TABLE IF NOT EXISTS `classes` (
  `class_id` bigint NOT NULL AUTO_INCREMENT,
  `establishment_id` bigint NOT NULL,
  `created_by` bigint DEFAULT NULL,
  `name` varchar(128) NOT NULL,
  `level` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `join_code` varchar(16) NOT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`class_id`),
  UNIQUE KEY `uq_class_join_code` (`join_code`),
  KEY `idx_class_establishment` (`establishment_id`),
  KEY `idx_class_created_by` (`created_by`),
  CONSTRAINT `fk_class_establishment` FOREIGN KEY (`establishment_id`) REFERENCES `establishments` (`establishment_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_class_created_by` FOREIGN KEY (`created_by`) REFERENCES `establishment_admins` (`ea_id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- uq_cp_player caps a player at one class across the whole system.
CREATE TABLE IF NOT EXISTS `class_players` (
  `class_id` bigint NOT NULL,
  `player_id` bigint NOT NULL,
  `joined_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`class_id`,`player_id`),
  UNIQUE KEY `uq_cp_player` (`player_id`),
  CONSTRAINT `fk_cp_class` FOREIGN KEY (`class_id`) REFERENCES `classes` (`class_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_cp_player` FOREIGN KEY (`player_id`) REFERENCES `players` (`player_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Teacher-defined groups within a class, used to hand out differentiated work.
CREATE TABLE IF NOT EXISTS `class_subgroups` (
  `subgroup_id` bigint NOT NULL AUTO_INCREMENT,
  `class_id` bigint NOT NULL,
  `name` varchar(128) NOT NULL,
  `color` varchar(16) DEFAULT NULL,
  `created_by` bigint DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`subgroup_id`),
  KEY `idx_subgroup_class` (`class_id`),
  KEY `idx_subgroup_created_by` (`created_by`),
  CONSTRAINT `fk_subgroup_class` FOREIGN KEY (`class_id`) REFERENCES `classes` (`class_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_subgroup_created_by` FOREIGN KEY (`created_by`) REFERENCES `establishment_admins` (`ea_id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- No unique key on player_id: a player may belong to several subgroups.
CREATE TABLE IF NOT EXISTS `subgroup_players` (
  `subgroup_id` bigint NOT NULL,
  `player_id` bigint NOT NULL,
  `added_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`subgroup_id`,`player_id`),
  KEY `idx_sp_player` (`player_id`),
  CONSTRAINT `fk_sp_subgroup` FOREIGN KEY (`subgroup_id`) REFERENCES `class_subgroups` (`subgroup_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_sp_player` FOREIGN KEY (`player_id`) REFERENCES `players` (`player_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Redemption of an individual licence by a player. uq_lp_player means a player
-- holds at most one, so re-redeeming replaces rather than stacks.
CREATE TABLE IF NOT EXISTS `license_players` (
  `license_id` bigint NOT NULL,
  `player_id` bigint NOT NULL,
  `redeemed_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`license_id`,`player_id`),
  UNIQUE KEY `uq_lp_player` (`player_id`),
  CONSTRAINT `fk_lp_license` FOREIGN KEY (`license_id`) REFERENCES `licenses` (`license_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_lp_player` FOREIGN KEY (`player_id`) REFERENCES `players` (`player_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
