-- =============================================================================
-- Event Poomsae Groups Table
-- =============================================================================
-- Purpose: Batch/slot management for Federation Cup participants
-- Created: December 2025
-- Version: 1.0.0
-- =============================================================================

-- ==========================================
-- PART 1: Event Groups Seeding
-- ==========================================
-- Ensure event_groups table has Federation Cup entry

-- Check if event_groups table exists
SELECT COUNT(*) as table_exists 
FROM information_schema.TABLES 
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'event_groups';

-- Insert event groups if they don't exist
INSERT INTO `event_groups` (`id`, `name`, `created_at`, `updated_at`) 
VALUES 
  (1, 'Official', NOW(), NOW()),
  (2, 'Open', NOW(), NOW()),
  (3, 'Federation Cup', NOW(), NOW())
ON DUPLICATE KEY UPDATE 
  `name` = VALUES(`name`),
  `updated_at` = NOW();

-- ==========================================
-- PART 2: Event Poomsae Groups Table
-- ==========================================

-- Drop table if exists (for clean installation)

-- Create event_poomsae_groups table
CREATE TABLE `event_poomsae_groups` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `event_id` bigint UNSIGNED NOT NULL COMMENT 'Foreign key to events table',
  `game_path_prefix` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Category-SubCategory-Option path (e.g., "4-2-5")',
  `age_bar_id` bigint UNSIGNED DEFAULT NULL COMMENT 'Age bar ID for Senior category filtering',
  `gender` tinyint UNSIGNED DEFAULT NULL COMMENT '1=Male, 2=Female; NULL for mixed gender options',
  `scope_key` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Unique scope identifier: {category}-{subcat}-{option}[-{gender}][-age{agebar}]',
  `group_index` int UNSIGNED NOT NULL COMMENT 'Sequential group number within scope',
  `display_code` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Human-readable display code (e.g., "Rec Team M#2")',
  `batch_code` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Batch identifier for Federation Cup (e.g., "POM-REC-TEAM-M #2")',
  `batch_seq` int UNSIGNED DEFAULT NULL COMMENT 'Batch sequence number within scope',
  `is_empty` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'Flag indicating if group has no participants',
  `created_by` bigint UNSIGNED DEFAULT NULL COMMENT 'State admin ID who created the group',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL COMMENT 'Soft delete timestamp',
  PRIMARY KEY (`id`),
  KEY `event_id_game_path_prefix` (`event_id`,`game_path_prefix`),
  KEY `event_id_game_path_age_gender` (`event_id`,`game_path_prefix`,`age_bar_id`,`gender`),
  UNIQUE KEY `uniq_event_scope_groupindex` (`event_id`,`scope_key`,`group_index`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Federation Cup batch/slot groups';

-- =============================================================================
-- Field Descriptions:
-- =============================================================================
--
-- game_path_prefix:
--   Format: {category_id}-{sub_category_id}-{sub_category_option_id}
--   Examples:
--     - "4-2-3": Senior (4) > Poomsae (2) > Recognized Individual (3)
--     - "2-1-1": Cadet (2) > Kyorugi (1) > Standard (1)
--     - "4-2-5": Senior (4) > Poomsae (2) > Recognized Team (5)
--
-- age_bar_id:
--   - Used only for Senior category (category_id = 4)
--   - NULL for all other categories
--   - Allows separate batches per age bar within Senior category
--   - Example: Age bar 12 (35-39 years), Age bar 13 (40-44 years)
--
-- gender:
--   - 1 = Male
--   - 2 = Female
--   - NULL = Mixed gender (for options that don't separate by gender)
--   - Gender-specific options: 3 (Recognized Individual), 5 (Recognized Team), 6 (Freestyle Individual)
--   - Mixed gender options: 4 (Recognized Pair), 7 (Freestyle Pair), 8 (Freestyle Team)
--
-- scope_key:
--   Unique identifier combining all scope parameters
--   Format: {category}-{subcat}-{option}[-{gender}][-age{agebar}]
--   Examples:
--     - "4-2-3-1": Senior Recognized Individual Male (no age bar)
--     - "4-2-3-2-age12": Senior Recognized Individual Female, Age Bar 12
--     - "2-2-4": Cadet Recognized Pair (mixed gender, no gender suffix)
--     - "4-2-5-1-age13": Senior Recognized Team Male, Age Bar 13
--
-- batch_code:
--   Display-friendly batch identifier for Federation Cup
--   Format: {TYPE}-{SUBTYPE}-{OPTION}[-{GENDER}] #{SEQ}
--   Examples:
--     - "POM-REC-IND-M #1": Poomsae Recognized Individual Male, Batch 1
--     - "POM-REC-IND-F #2": Poomsae Recognized Individual Female, Batch 2
--     - "POM-REC-PAIR #1": Poomsae Recognized Pair (mixed), Batch 1
--     - "POM-REC-TEAM-M #3": Poomsae Recognized Team Male, Batch 3
--     - "POM-FREE-IND-F #1": Poomsae Freestyle Individual Female, Batch 1
--     - "KYO-54KG #2": Kyorugi 54kg weight category, Batch 2
--
-- batch_seq:
--   Sequential number for batches within the same scope
--   Increments automatically when previous batch reaches 4 participants
--   Used to generate batch_code (e.g., batch_seq=2 → "POM-REC-IND-M #2")
--
-- =============================================================================
-- Batch Code Generation Logic:
-- =============================================================================
--
-- Poomsae Options:
--   Option 3: POM-REC-IND-{M|F}   (Recognized Individual, gender-specific)
--   Option 4: POM-REC-PAIR         (Recognized Pair, mixed gender)
--   Option 5: POM-REC-TEAM-{M|F}  (Recognized Team, gender-specific)
--   Option 6: POM-FREE-IND-{M|F}  (Freestyle Individual, gender-specific)
--   Option 7: POM-FREE-PAIR        (Freestyle Pair, mixed gender)
--   Option 8: POM-FREE-TEAM        (Freestyle Team, mixed gender)
--
-- Kyorugi:
--   Format: KYO-{WEIGHT_LABEL} #{SEQ}
--   Examples:
--     - KYO-54KG #1
--     - KYO-63KG #2
--     - KYO-FLYWEIGHT #1
--
-- =============================================================================
-- Usage Examples:
-- =============================================================================
--
-- Example 1: Senior Male Recognized Individual (Option 3)
-- INSERT INTO event_poomsae_groups VALUES
-- (NULL, 123, '4-2-3', 12, 1, '4-2-3-1-age12', 1, 'Rec Ind M#1', 'POM-REC-IND-M #1', 1, 0, 5, NOW(), NOW(), NULL);
--
-- Example 2: Cadet Recognized Pair (Option 4, mixed gender)
-- INSERT INTO event_poomsae_groups VALUES
-- (NULL, 123, '2-2-4', NULL, NULL, '2-2-4', 1, 'Rec Pair#1', 'POM-REC-PAIR #1', 1, 0, 5, NOW(), NOW(), NULL);
--
-- Example 3: Senior Female Freestyle Individual (Option 6)
-- INSERT INTO event_poomsae_groups VALUES
-- (NULL, 123, '4-2-6', 13, 2, '4-2-6-2-age13', 2, 'Free Ind F#2', 'POM-FREE-IND-F #2', 2, 0, 5, NOW(), NOW(), NULL);
--
-- =============================================================================
-- Indexes:
-- =============================================================================
--
-- 1. event_id_game_path_prefix:
--    - Fast lookup of groups for a specific event and game path
--    - Used when searching for available slots
--
-- 2. event_id_game_path_age_gender:
--    - Composite index for filtering by event, path, age bar, and gender
--    - Optimizes queries for gender-specific and Senior category batches
--
-- 3. uniq_event_scope_groupindex:
--    - Ensures uniqueness of group_index within each scope
--    - Prevents duplicate group numbers for the same scope
--
-- =============================================================================
-- Business Rules:
-- =============================================================================
--
-- 1. Maximum 4 participants per group (enforced in application logic)
-- 2. New group created automatically when previous group reaches 4 participants
-- 3. Gender-specific options (3, 5, 6) MUST have gender populated
-- 4. Mixed gender options (4, 7, 8) MUST have gender = NULL
-- 5. Senior category (4) SHOULD have age_bar_id populated
-- 6. batch_seq increments within each unique scope_key
-- 7. Groups are soft-deleted (deleted_at) when empty and batch is finalized
--
-- =============================================================================
-- Related Tables:
-- =============================================================================
--
-- - events: Parent event table (event_id foreign key)
-- - event_participants: Participants linked via poomsae_group_id
-- - age_bars: Age bar definitions for Senior category
-- - weight_categories: Weight categories for Kyorugi
-- - categories: Category definitions (Sub-Junior, Cadet, Junior, Senior)
-- - sub_categories: Sub-category definitions (Kyorugi, Poomsae)
-- - sub_cat_options: Option definitions (Recognized Individual, Pair, Team, Freestyle, etc.)
--
-- =============================================================================
