-- Create missing registrations_with_delegate_products view for live server
-- This view is required for the admin dashboard and registration management
-- Note: Using delegate_type enum since delegate_product_id column doesn't exist in live

-- Drop existing view if it exists
DROP VIEW IF EXISTS registrations_with_delegate_products;

-- Create the corrected view
CREATE VIEW registrations_with_delegate_products AS
SELECT 
    r.*,
    CASE 
        WHEN r.delegate_type COLLATE utf8mb4_general_ci = 'earlyBird' THEN 'DELEGATE_EARLY_BIRD'
        WHEN r.delegate_type COLLATE utf8mb4_general_ci = 'standard' THEN 'DELEGATE_STANDARD'
        ELSE 'DELEGATE_STANDARD'
    END as delegate_product_code,
    CASE 
        WHEN r.delegate_type COLLATE utf8mb4_general_ci = 'earlyBird' THEN 'Early Bird Delegate Pass'
        WHEN r.delegate_type COLLATE utf8mb4_general_ci = 'standard' THEN 'Standard Delegate Pass'
        ELSE 'Standard Delegate Pass'
    END as delegate_product_name,
    CASE 
        WHEN r.delegate_type COLLATE utf8mb4_general_ci = 'earlyBird' THEN 'Discounted delegate pass for early registrations'
        WHEN r.delegate_type COLLATE utf8mb4_general_ci = 'standard' THEN 'Regular delegate pass'
        ELSE 'Regular delegate pass'
    END as delegate_product_description,
    CASE 
        WHEN r.delegate_type COLLATE utf8mb4_general_ci = 'earlyBird' THEN 500.00
        WHEN r.delegate_type COLLATE utf8mb4_general_ci = 'standard' THEN 550.00
        ELSE 550.00
    END as delegate_base_price,
    CASE 
        WHEN r.delegate_type COLLATE utf8mb4_general_ci = 'earlyBird' THEN 'Early Bird'
        WHEN r.delegate_type COLLATE utf8mb4_general_ci = 'standard' THEN 'Standard'
        ELSE 'Standard'
    END as current_pricing_tier,
    CASE 
        WHEN r.delegate_type COLLATE utf8mb4_general_ci = 'earlyBird' THEN 500.00
        WHEN r.delegate_type COLLATE utf8mb4_general_ci = 'standard' THEN 550.00
        ELSE 550.00
    END as current_tier_price
FROM registrations r;

-- Verify the view was created successfully
SELECT COUNT(*) as total_registrations FROM registrations_with_delegate_products;
