-- ========================================
-- STEP 1: Check current state
-- ========================================
SELECT '===== CURRENT CHANNELS =====' as info;
SELECT id, name, code, channel_type, is_tamair, admin_fee 
FROM booking_channels 
ORDER BY channel_type;

SELECT '===== CURRENT GDS BOOKINGS WITH COMMISSION =====' as info;
SELECT 
    COUNT(*) as gds_bookings,
    SUM(commission_amount) as total_commission_to_remove,
    SUM(total_amount) as total_revenue
FROM bookings b
JOIN booking_channels bc ON b.booking_channel_id = bc.id
WHERE bc.channel_type = 'gds'
AND b.commission_amount > 0;

-- ========================================
-- STEP 2: Make commission_amount nullable
-- ========================================
SELECT '===== MAKING COMMISSION NULLABLE =====' as info;
ALTER TABLE bookings 
MODIFY COLUMN commission_amount DECIMAL(10,2) NULL DEFAULT NULL;

-- Verify the change
SELECT 
    COLUMN_NAME,
    DATA_TYPE,
    IS_NULLABLE,
    COLUMN_DEFAULT,
    COLUMN_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'tamair_flynas_crm'
AND TABLE_NAME = 'bookings'
AND COLUMN_NAME = 'commission_amount';

-- ========================================
-- STEP 3: Remove commission from GDS bookings
-- ========================================
SELECT '===== REMOVING GDS COMMISSION =====' as info;

-- Set GDS bookings commission to NULL (or 0)
UPDATE bookings b
JOIN booking_channels bc ON b.booking_channel_id = bc.id
SET b.commission_amount = 0
WHERE bc.channel_type = 'gds';

-- ========================================
-- STEP 4: Verify the changes
-- ========================================
SELECT '===== VERIFICATION - GDS BOOKINGS AFTER FIX =====' as info;
SELECT 
    bc.name as channel,
    bc.channel_type,
    COUNT(b.id) as total_bookings,
    SUM(b.passenger_count) as total_passengers,
    SUM(b.total_amount) as total_revenue,
    SUM(COALESCE(b.commission_amount, 0)) as total_commission,
    MAX(COALESCE(b.commission_amount, 0)) as max_commission
FROM booking_channels bc
LEFT JOIN bookings b ON b.booking_channel_id = bc.id
WHERE bc.channel_type = 'gds'
GROUP BY bc.id, bc.name, bc.channel_type;

SELECT '===== ALL CHANNELS SUMMARY =====' as info;
SELECT 
    bc.name,
    bc.channel_type,
    bc.is_tamair,
    COUNT(b.id) as bookings,
    SUM(b.total_amount) as revenue,
    SUM(COALESCE(b.commission_amount, 0)) as commission,
    SUM(COALESCE(b.admin_fee, 0)) as admin_fees
FROM booking_channels bc
LEFT JOIN bookings b ON b.booking_channel_id = bc.id
GROUP BY bc.id, bc.name, bc.channel_type, bc.is_tamair
ORDER BY 
    CASE bc.channel_type
        WHEN 'bsp' THEN 1
        WHEN 'gds' THEN 2
        WHEN 'agency' THEN 3
    END,
    bc.name;

SELECT '===== FIX COMPLETED SUCCESSFULLY =====' as info;
