-- =============================================================================
-- Federation Cup Entry Fees Table
-- =============================================================================
-- Purpose: Global entry fee configuration for Federation Cup events
-- Created: December 2025
-- Version: 1.0.0
-- =============================================================================

-- Drop table if exists (for clean installation)

-- Create federation_cup_entry_fees table
CREATE TABLE `federation_cup_entry_fees` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `sub_category_id` int UNSIGNED NOT NULL COMMENT '1=Kyorugi, 2=Poomsae',
  `sub_category_option_id` int UNSIGNED DEFAULT NULL COMMENT 'NULL for Kyorugi (all weights), specific option ID for Poomsae',
  `entry_fee` decimal(10,2) NOT NULL DEFAULT '0.00' COMMENT 'Fee amount in INR',
  `description` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Human-readable description',
  `is_active` tinyint(1) NOT NULL DEFAULT '1' COMMENT 'Enable/disable fee type',
  `created_by` bigint UNSIGNED DEFAULT NULL COMMENT 'Admin ID who created',
  `updated_by` bigint UNSIGNED DEFAULT NULL COMMENT 'Admin ID who last updated',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_sub_category_option` (`sub_category_id`, `sub_category_option_id`),
  KEY `is_active` (`is_active`),
  KEY `idx_sub_category_id` (`sub_category_id`),
  KEY `idx_sub_category_option_id` (`sub_category_option_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Federation Cup entry fees by sub-category option';

-- Insert default entry fees
INSERT INTO `federation_cup_entry_fees` 
(`id`, `sub_category_id`, `sub_category_option_id`, `entry_fee`, `description`, `is_active`, `created_by`, `updated_by`, `created_at`, `updated_at`) 
VALUES
-- Kyorugi (sub_category_id = 1, no specific option - applies to all weights)
(1, 1, NULL, 2500.00, 'Entry fee per individual for Kyorugi (per weight category)', 1, NULL, NULL, NOW(), NOW()),

-- Poomsae Individual Options (sub_category_id = 2, options 3 & 6)
(2, 2, 3, 2500.00, 'Entry fee for Poomsae Recognized Individual (Option 3)', 1, NULL, NULL, NOW(), NOW()),
(3, 2, 6, 2500.00, 'Entry fee for Poomsae Freestyle Individual (Option 6)', 1, NULL, NULL, NOW(), NOW()),

-- Poomsae Pair Options (sub_category_id = 2, options 4 & 7)
(4, 2, 4, 3500.00, 'Entry fee for Poomsae Recognized Pair (Option 4) - total for both athletes', 1, NULL, NULL, NOW(), NOW()),
(5, 2, 7, 3500.00, 'Entry fee for Poomsae Freestyle Pair (Option 7) - total for both athletes', 1, NULL, NULL, NOW(), NOW()),

-- Poomsae Team Options (sub_category_id = 2, options 5 & 8)
(6, 2, 5, 3500.00, 'Entry fee for Poomsae Recognized Team (Option 5) - total for all members', 1, NULL, NULL, NOW(), NOW()),
(7, 2, 8, 3500.00, 'Entry fee for Poomsae Freestyle Team (Option 8) - total for all members', 1, NULL, NULL, NOW(), NOW());

-- =============================================================================
-- Notes:
-- 1. sub_category_id values:
--    - 1 = Kyorugi (sparring/combat)
--    - 2 = Poomsae (forms)
--
-- 2. sub_category_option_id values (for Poomsae only):
--    - 3 = Recognized Individual (gender-specific batches)
--    - 4 = Recognized Pair (mixed gender)
--    - 5 = Recognized Team (gender-specific batches)
--    - 6 = Freestyle Individual (gender-specific batches)
--    - 7 = Freestyle Pair (mixed gender)
--    - 8 = Freestyle Team (mixed gender)
--    - NULL = For Kyorugi (applies to all weight categories)
--
-- 3. Fee Structure:
--    - Kyorugi: ₹2,500 per athlete (per weight category)
--    - Poomsae Individual (Options 3, 6): ₹2,500 per athlete
--    - Poomsae Pair (Options 4, 7): ₹3,500 total (not per person)
--    - Poomsae Team (Options 5, 8): ₹3,500 total (not per person)
--
-- 4. Admin Management:
--    - Admins can update fees via Settings > Federation Cup Fees
--    - Fees can be activated/deactivated using is_active flag
--    - Audit trail maintained via created_by and updated_by fields
--
-- 5. Usage in Payment Integration:
--    - During registration, fetch fee using sub_category_id and sub_category_option_id
--    - For Kyorugi: WHERE sub_category_id = 1 AND sub_category_option_id IS NULL
--    - For Poomsae: WHERE sub_category_id = 2 AND sub_category_option_id = {option_id}
--
-- 6. Unique Constraint:
--    - Combination of sub_category_id and sub_category_option_id must be unique
--    - Prevents duplicate fee entries for same option
--
-- 7. Foreign Key Relationships:
--    - sub_category_id references sub_categories table
--    - sub_category_option_id references sub_cat_options table
--    - (Add FK constraints if needed for referential integrity)
-- =============================================================================
