-- 005_accounts_and_licensing
--
-- Who can log in and what they paid for. Three separate identity tables by
-- design: `admins` (back office), `establishment_admins` (teachers), and
-- `players` (the game client). They share no columns and no auth path.
--
-- `licenses` and `orders` reference each other, so licenses is created without
-- its order_id constraint and picks it up in the guarded ALTER at the bottom.

-- Back-office operators. Nothing references this table.
CREATE TABLE IF NOT EXISTS `admins` (
  `admin_id` int NOT NULL AUTO_INCREMENT,
  `username` varchar(128) COLLATE utf8mb4_unicode_ci NOT NULL,
  `password_hash` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `display_name` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`admin_id`),
  UNIQUE KEY `username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Machine credentials for the Unity catalog sync. Only the hash is stored.
CREATE TABLE IF NOT EXISTS `api_keys` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL,
  `key_hash` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL,
  `scope` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'catalog_sync',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `last_used_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `key_hash` (`key_hash`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- A licence is the unit that grants access. `individual` is redeemed by one
-- player via `code`; `establishment` backs a school and caps its classes and
-- players. expires_at NULL means it never expires.
CREATE TABLE IF NOT EXISTS `licenses` (
  `license_id` bigint NOT NULL AUTO_INCREMENT,
  `code` varchar(32) NOT NULL,
  `type` enum('individual','establishment') NOT NULL,
  `status` enum('active','suspended','revoked','expired') NOT NULL DEFAULT 'active',
  `seats` int DEFAULT NULL,
  `max_classes` int DEFAULT NULL,
  `max_players` int DEFAULT NULL,
  `purchaser_name` varchar(128) DEFAULT NULL,
  `purchaser_email` varchar(255) DEFAULT NULL,
  `source` enum('web','app_store','play_store','steam','manual') NOT NULL DEFAULT 'web',
  `external_order_ref` varchar(128) DEFAULT NULL,
  `order_id` bigint DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `expires_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`license_id`),
  UNIQUE KEY `uq_license_code` (`code`),
  KEY `idx_license_type_status` (`type`,`status`),
  KEY `idx_license_order_ref` (`external_order_ref`),
  KEY `idx_license_order` (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- One establishment per licence, enforced by uq_establishment_license.
CREATE TABLE IF NOT EXISTS `establishments` (
  `establishment_id` bigint NOT NULL AUTO_INCREMENT,
  `license_id` bigint NOT NULL,
  `name` varchar(255) NOT NULL,
  `kind` enum('school','professional','other') NOT NULL DEFAULT 'school',
  `contact_email` varchar(255) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`establishment_id`),
  UNIQUE KEY `uq_establishment_license` (`license_id`),
  CONSTRAINT `fk_establishment_license` FOREIGN KEY (`license_id`) REFERENCES `licenses` (`license_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Teachers. Usernames are unique across all establishments, not per school.
CREATE TABLE IF NOT EXISTS `establishment_admins` (
  `ea_id` bigint NOT NULL AUTO_INCREMENT,
  `establishment_id` bigint NOT NULL,
  `username` varchar(128) NOT NULL,
  `password_hash` varchar(255) NOT NULL,
  `display_name` varchar(64) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `last_seen_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`ea_id`),
  UNIQUE KEY `uq_ea_username` (`username`),
  KEY `idx_ea_establishment` (`establishment_id`),
  CONSTRAINT `fk_ea_establishment` FOREIGN KEY (`establishment_id`) REFERENCES `establishments` (`establishment_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Stripe checkouts. Rows are written pending and flipped to paid by the
-- webhook; delivered_at is stamped once the licence codes have been mailed,
-- which is what idx_orders_undelivered is scanned for.
CREATE TABLE IF NOT EXISTS `orders` (
  `order_id` bigint NOT NULL AUTO_INCREMENT,
  `license_id` bigint DEFAULT NULL,
  `establishment_id` bigint DEFAULT NULL,
  `ea_id` bigint DEFAULT NULL,
  `buyer_email` varchar(190) DEFAULT NULL,
  `buyer_name` varchar(128) DEFAULT NULL,
  `kind` enum('class','player','renewal','individual') NOT NULL,
  `quantity` smallint UNSIGNED NOT NULL,
  `unit_amount_cents` int UNSIGNED NOT NULL,
  `currency` varchar(3) NOT NULL DEFAULT 'eur',
  `stripe_session_id` varchar(255) NOT NULL,
  `stripe_payment_intent` varchar(255) DEFAULT NULL,
  `stripe_customer_id` varchar(255) DEFAULT NULL,
  `status` enum('pending','paid','expired') NOT NULL DEFAULT 'pending',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `paid_at` timestamp NULL DEFAULT NULL,
  `delivered_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`order_id`),
  UNIQUE KEY `uniq_session` (`stripe_session_id`),
  KEY `idx_license` (`license_id`),
  KEY `idx_status` (`status`),
  KEY `idx_kind` (`kind`),
  KEY `idx_orders_buyer_email` (`buyer_email`),
  KEY `idx_orders_undelivered` (`status`,`delivered_at`),
  KEY `fk_orders_establishment` (`establishment_id`),
  CONSTRAINT `fk_orders_establishment` FOREIGN KEY (`establishment_id`) REFERENCES `establishments` (`establishment_id`) ON DELETE SET NULL,
  CONSTRAINT `fk_orders_license` FOREIGN KEY (`license_id`) REFERENCES `licenses` (`license_id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Game accounts. auth_uid is the legacy device identity; email/password_hash
-- are the current login. All three unique keys allow NULL, so a player can
-- exist with only one of them set.
CREATE TABLE IF NOT EXISTS `players` (
  `player_id` bigint NOT NULL AUTO_INCREMENT,
  `auth_uid` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `username` varchar(128) DEFAULT NULL,
  `email` varchar(190) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `password_hash` varchar(255) DEFAULT NULL,
  `display_name` varchar(64) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `last_seen_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `password_reset_sent_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`player_id`),
  UNIQUE KEY `auth_uid` (`auth_uid`),
  UNIQUE KEY `username` (`username`),
  UNIQUE KEY `uniq_players_email` (`email`),
  KEY `idx_players_email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Closes the licenses -> orders half of the cycle, now that orders exists.
-- Guarded so the file stays re-runnable against a database that already has it.
SET @fk_license_order := (
  SELECT COUNT(*) FROM information_schema.TABLE_CONSTRAINTS
  WHERE CONSTRAINT_SCHEMA = DATABASE()
    AND TABLE_NAME = 'licenses'
    AND CONSTRAINT_NAME = 'fk_license_order'
);
SET @sql := IF(@fk_license_order = 0,
  'ALTER TABLE `licenses` ADD CONSTRAINT `fk_license_order` FOREIGN KEY (`order_id`) REFERENCES `orders` (`order_id`) ON DELETE SET NULL',
  'DO 0');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
