-- =============================================================================
-- Event Payment Details - Federation Cup Integration (SIMPLIFIED)
-- =============================================================================
-- This version doesn't require information_schema access
-- Safe to run multiple times - will skip if columns already exist
-- =============================================================================

-- Add razorpay_signature column (for payment verification)
ALTER TABLE `event_payment_details` 
ADD COLUMN `razorpay_signature` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL 
COMMENT 'Razorpay signature for payment verification' 
AFTER `error_reason`;

-- Add participant_id column to link payment with event_participants
ALTER TABLE `event_payment_details` 
ADD COLUMN `participant_id` bigint UNSIGNED DEFAULT NULL 
COMMENT 'Links to event_participants table' 
AFTER `event_id`;

-- Add poomsae_group_id column to link payment with batch group
ALTER TABLE `event_payment_details` 
ADD COLUMN `poomsae_group_id` bigint UNSIGNED DEFAULT NULL 
COMMENT 'Links to event_poomsae_groups table for Federation Cup' 
AFTER `participant_id`;

-- Add sub_category_option_id (standardized column name)
ALTER TABLE `event_payment_details` 
ADD COLUMN `sub_category_option_id` int UNSIGNED DEFAULT NULL 
COMMENT 'Sub-category option ID (standardized column name)' 
AFTER `sub_category_id`;

-- Add payment_type column to distinguish between different payment scenarios
ALTER TABLE `event_payment_details` 
ADD COLUMN `payment_type` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT 'registration' 
COMMENT 'Type: registration, federation_cup, late_fee, etc.' 
AFTER `event_id`;

-- Add is_federation_cup flag for easier filtering
ALTER TABLE `event_payment_details` 
ADD COLUMN `is_federation_cup` tinyint(1) DEFAULT 0 
COMMENT 'Flag indicating Federation Cup payment' 
AFTER `payment_type`;

-- Add portal_user_id to link with portal_users table
ALTER TABLE `event_payment_details` 
ADD COLUMN `portal_user_id` bigint UNSIGNED DEFAULT NULL 
COMMENT 'Links to portal_users table' 
AFTER `reg_uid`;

-- Add state_id to track which state made the payment
ALTER TABLE `event_payment_details` 
ADD COLUMN `state_id` int UNSIGNED DEFAULT NULL 
COMMENT 'State ID of the participant' 
AFTER `portal_user_id`;

-- Add created_by column to track state admin who initiated payment
ALTER TABLE `event_payment_details` 
ADD COLUMN `created_by_state_admin` bigint UNSIGNED DEFAULT NULL 
COMMENT 'State admin ID who initiated the payment' 
AFTER `state_id`;

-- Add payment_metadata JSON column for storing additional payment info
ALTER TABLE `event_payment_details` 
ADD COLUMN `payment_metadata` json DEFAULT NULL 
COMMENT 'Additional payment metadata (batch_code, slot_info, etc.)' 
AFTER `first_step_data`;

-- Add notes column for admin remarks
ALTER TABLE `event_payment_details` 
ADD COLUMN `notes` text COLLATE utf8mb4_unicode_ci DEFAULT NULL 
COMMENT 'Admin notes or remarks about payment' 
AFTER `payment_metadata`;

-- Create indexes for better query performance
-- NOTE: If index already exists, you'll get error "Duplicate key name" - that's OK, skip it

-- Index for participant_id lookups
CREATE INDEX `idx_participant_id` ON `event_payment_details` (`participant_id`);

-- Index for poomsae_group_id lookups
CREATE INDEX `idx_poomsae_group_id` ON `event_payment_details` (`poomsae_group_id`);

-- Index for portal_user_id lookups
CREATE INDEX `idx_portal_user_id` ON `event_payment_details` (`portal_user_id`);

-- Index for state_id lookups
CREATE INDEX `idx_state_id` ON `event_payment_details` (`state_id`);

-- Composite index for event and payment type queries
CREATE INDEX `idx_event_payment_type` ON `event_payment_details` (`event_id`, `payment_type`);

-- Index for Federation Cup payment queries
CREATE INDEX `idx_federation_cup` ON `event_payment_details` (`is_federation_cup`, `status`);

-- Index for payment status and date queries
CREATE INDEX `idx_payment_status_date` ON `event_payment_details` (`status`, `payment_date`);

-- =============================================================================
-- Migration Complete!
-- =============================================================================
-- NOTE: If you see "Duplicate column" errors, that's OKAY - columns already exist
-- NOTE: If you see "Can't DROP" errors on indexes, that's OKAY - indexes don't exist yet
-- =============================================================================
