-- =============================================================================
-- Event Participants Table - Federation Cup Integration
-- =============================================================================
-- Purpose: Add poomsae_group_id column to link participants with batch groups
-- Created: December 2025
-- Version: 1.0.0
-- =============================================================================

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

-- Add poomsae_group_id column if it doesn't exist
ALTER TABLE `event_participants` 
ADD COLUMN IF NOT EXISTS `poomsae_group_id` bigint UNSIGNED DEFAULT NULL 
COMMENT 'Links participant to Federation Cup batch group' 
AFTER `game_path`;

-- Add index for faster joins
ALTER TABLE `event_participants` 
ADD INDEX IF NOT EXISTS `idx_poomsae_group_id` (`poomsae_group_id`);

-- Add foreign key constraint (optional - comment out if referential integrity not required)
-- ALTER TABLE `event_participants` 
-- ADD CONSTRAINT `fk_event_participants_poomsae_group` 
-- FOREIGN KEY (`poomsae_group_id`) 
-- REFERENCES `event_poomsae_groups` (`id`) 
-- ON DELETE SET NULL 
-- ON UPDATE CASCADE;

-- =============================================================================
-- Notes:
-- =============================================================================
--
-- 1. Purpose of poomsae_group_id:
--    - Links each participant to their assigned batch/slot group
--    - Enables tracking of which batch a participant belongs to
--    - Used for generating batch-wise reports and exports
--    - Facilitates slot utilization tracking (e.g., 3/4 slots filled)
--
-- 2. NULL Values:
--    - NULL indicates participant not yet assigned to a batch
--    - For non-Federation Cup events, this field remains NULL
--    - For Federation Cup events, populated automatically on registration
--
-- 3. Batch Assignment:
--    - Assignment handled by FederationCupBatchService
--    - Automatically finds or creates appropriate batch group
--    - Ensures max 4 participants per batch
--
-- 4. Relationship:
--    - One-to-Many: event_poomsae_groups (1) → event_participants (Many)
--    - Each group can have 0-4 participants
--    - Each participant belongs to 0-1 group
--
-- 5. Query Examples:
--
--    a) Get all participants in a batch:
--       SELECT * FROM event_participants 
--       WHERE poomsae_group_id = 123;
--
--    b) Count participants per batch:
--       SELECT 
--         epg.batch_code,
--         COUNT(ep.id) as participant_count
--       FROM event_poomsae_groups epg
--       LEFT JOIN event_participants ep ON ep.poomsae_group_id = epg.id
--       WHERE epg.event_id = 123
--       GROUP BY epg.id, epg.batch_code
--       ORDER BY epg.batch_seq;
--
--    c) Find batches with available slots:
--       SELECT 
--         epg.id,
--         epg.batch_code,
--         (4 - COUNT(ep.id)) as slots_available
--       FROM event_poomsae_groups epg
--       LEFT JOIN event_participants ep ON ep.poomsae_group_id = epg.id
--       WHERE epg.event_id = 123
--       GROUP BY epg.id
--       HAVING slots_available > 0;
--
--    d) Get participant with batch information:
--       SELECT 
--         ep.*,
--         epg.batch_code,
--         epg.batch_seq,
--         epg.display_code
--       FROM event_participants ep
--       LEFT JOIN event_poomsae_groups epg ON ep.poomsae_group_id = epg.id
--       WHERE ep.event_id = 123;
--
-- 6. Data Integrity:
--    - Optional foreign key constraint commented out above
--    - If enabled: Deleting a group sets poomsae_group_id to NULL
--    - If disabled: Manual cleanup required when deleting groups
--    - Recommendation: Enable FK for production environments
--
-- 7. Migration Impact:
--    - Existing records: poomsae_group_id will be NULL
--    - New Federation Cup registrations: Auto-populated
--    - No data loss or breaking changes
--    - Backward compatible with existing event types
--
-- 8. Index Performance:
--    - idx_poomsae_group_id: Speeds up joins with event_poomsae_groups
--    - Recommended for large datasets (>10,000 participants)
--    - Can be dropped if database size is small
--
-- =============================================================================

-- Verify column was added successfully
SELECT 
    COLUMN_NAME, 
    COLUMN_TYPE, 
    IS_NULLABLE,
    COLUMN_DEFAULT, 
    COLUMN_COMMENT 
FROM information_schema.COLUMNS 
WHERE TABLE_SCHEMA = DATABASE() 
  AND TABLE_NAME = 'event_participants' 
  AND COLUMN_NAME = 'poomsae_group_id';

-- Verify index was created
SELECT 
    INDEX_NAME,
    COLUMN_NAME,
    SEQ_IN_INDEX,
    NON_UNIQUE
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME = 'event_participants'
  AND INDEX_NAME = 'idx_poomsae_group_id';
