-- Add Identifier Mapping table for Discord-FiveM linking
-- This table stores the mapping between Discord accounts and FiveM player identifiers
-- Used for first-join access control

CREATE TABLE IF NOT EXISTS `identifier_mappings` (
  `id` VARCHAR(191) NOT NULL,
  `discordId` VARCHAR(191) NOT NULL,
  
  -- FiveM Identifiers
  `license` VARCHAR(191) NULL,
  `steam` VARCHAR(191) NULL,
  `fivem` VARCHAR(191) NULL,
  `xbl` VARCHAR(191) NULL,
  `live` VARCHAR(191) NULL,
  `ip` VARCHAR(191) NULL,
  
  -- Player Reference (from game database)
  `gameIdentifier` VARCHAR(191) NULL,
  `characterId` VARCHAR(191) NULL,
  
  -- Status
  `firstJoinAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `lastSeenAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `isVerified` BOOLEAN NOT NULL DEFAULT true,
  
  `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `updatedAt` DATETIME(3) NOT NULL,

  PRIMARY KEY (`id`),
  UNIQUE INDEX `identifier_mappings_discordId_key`(`discordId`),
  INDEX `identifier_mappings_license_idx`(`license`),
  INDEX `identifier_mappings_steam_idx`(`steam`),
  INDEX `identifier_mappings_gameIdentifier_idx`(`gameIdentifier`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
