-- =====================================================
-- Add 'complimentary' Payment Status for Free Registrations
-- Description: Distinguishes free/complimentary registrations from paid ones
-- Created: 2025-10-27
-- Risk Level: LOW (Adding new enum value, backward compatible)
-- Estimated Time: < 1 minute
-- =====================================================

-- Safety check: Display current registrations table structure
SELECT 'Current registrations table structure:' as Info;
DESCRIBE registrations;

-- Check current payment_status values distribution
SELECT 'Current payment status distribution:' as Info;
SELECT 
    payment_status,
    COUNT(*) as count,
    SUM(total_amount) as total_amount,
    COUNT(CASE WHEN total_amount = 0 THEN 1 END) as free_registrations
FROM registrations
GROUP BY payment_status;

-- Step 1: Add 'complimentary' to payment_status ENUM
SELECT 'Adding complimentary status to payment_status enum...' as Info;

ALTER TABLE `registrations` 
MODIFY COLUMN `payment_status` 
ENUM('pending','paid','failed','expired','cancelled','complimentary') 
DEFAULT 'pending'
COMMENT 'Payment status: complimentary for free/zero-cost registrations';

-- Step 2: Add helper columns for registration composition analysis
SELECT 'Adding registration composition tracking columns...' as Info;

ALTER TABLE `registrations`
ADD COLUMN `has_complimentary_items` BOOLEAN DEFAULT FALSE 
COMMENT 'Registration includes $0 products' AFTER `payment_status`,
ADD COLUMN `has_paid_items` BOOLEAN DEFAULT FALSE 
COMMENT 'Registration includes paid products' AFTER `has_complimentary_items`;

-- Step 3: Add indexes for reporting and filtering
SELECT 'Creating indexes for complimentary status queries...' as Info;

ALTER TABLE `registrations`
ADD INDEX `idx_complimentary_flags` (has_complimentary_items, has_paid_items);

-- Step 4: Update existing free registrations to complimentary status
SELECT 'Updating existing free registrations to complimentary status...' as Info;

-- First, analyze and flag registration composition
UPDATE registrations r
SET 
    has_complimentary_items = (
        SELECT COUNT(*) > 0
        FROM registration_products rp
        WHERE rp.registration_id = r.id AND rp.unit_price = 0
    ),
    has_paid_items = (
        SELECT COUNT(*) > 0
        FROM registration_products rp
        WHERE rp.registration_id = r.id AND rp.unit_price > 0
    );

-- Now update status for pure free registrations
UPDATE registrations
SET payment_status = 'complimentary'
WHERE total_amount = 0 
  AND payment_status = 'paid'
  AND has_complimentary_items = TRUE
  AND has_paid_items = FALSE;

-- Verification: Display updated statistics
SELECT 'Migration complete! New payment status distribution:' as Info;
SELECT 
    payment_status,
    COUNT(*) as count,
    SUM(total_amount) as total_amount,
    has_complimentary_items,
    has_paid_items
FROM registrations
GROUP BY payment_status, has_complimentary_items, has_paid_items
ORDER BY payment_status;

-- Display complimentary registrations for review
SELECT 'Sample complimentary registrations:' as Info;
SELECT 
    registration_number,
    contact_full_name,
    university_name,
    delegate_count,
    total_amount,
    payment_status,
    has_complimentary_items,
    has_paid_items,
    created_at
FROM registrations
WHERE payment_status = 'complimentary'
LIMIT 10;

-- Summary statistics
SELECT 'Summary by payment status:' as Info;
SELECT 
    CASE 
        WHEN payment_status = 'paid' THEN 'Paid (Revenue)'
        WHEN payment_status = 'complimentary' THEN 'Complimentary (No Revenue)'
        WHEN payment_status = 'pending' THEN 'Pending'
        WHEN payment_status = 'failed' THEN 'Failed'
        WHEN payment_status = 'expired' THEN 'Expired'
        WHEN payment_status = 'cancelled' THEN 'Cancelled'
    END as status_description,
    COUNT(*) as registration_count,
    SUM(delegate_count) as total_delegates,
    SUM(total_amount) as total_amount,
    CONCAT('$', FORMAT(SUM(total_amount), 2)) as formatted_amount
FROM registrations
GROUP BY payment_status
ORDER BY 
    CASE payment_status
        WHEN 'paid' THEN 1
        WHEN 'complimentary' THEN 2
        WHEN 'pending' THEN 3
        WHEN 'failed' THEN 4
        WHEN 'expired' THEN 5
        WHEN 'cancelled' THEN 6
    END;

SELECT 'Migration complete! ✅' as Status;
SELECT 'Remember to update api.php to use complimentary status for new free registrations.' as Note;

