-- =============================================================================
-- XCHANGE 2025: MULTIPLE DELEGATE TYPES MIGRATION
-- =============================================================================
-- This migration converts the single DELEGATE_PASS product into multiple
-- delegate type products, enabling users to select multiple delegate types
-- in a single registration.
--
-- BACKUP RECOMMENDATION: Create database backup before running this migration
-- =============================================================================

-- Start transaction for rollback capability
START TRANSACTION;

-- =============================================================================
-- STEP 1: CREATE NEW DELEGATE TYPE PRODUCTS
-- =============================================================================

-- Insert new delegate type products into the products table
INSERT INTO products (
    category_id, 
    product_code, 
    name, 
    description, 
    base_price, 
    currency, 
    total_stock,
    is_active, 
    requires_approval,
    max_per_registration, 
    display_order, 
    metadata,
    created_at,
    updated_at
) VALUES 
-- Early Bird Delegate (Discounted early registration)
(3, 'DELEGATE_EARLY_BIRD', 'Early Bird Delegate', 
 'Discounted delegate pass for early registrations. Access to all sessions, exhibits, and networking.', 
 450.00, 'USD', NULL, 1, 0, 50, 1, 
 JSON_OBJECT('type', 'early_bird', 'eligibility', JSON_ARRAY('general'), 'approval_required', false, 'description_short', 'Early registration discount'),
 NOW(), NOW()),

-- Standard Delegate (Regular price)
(3, 'DELEGATE_STANDARD', 'Standard Delegate', 
 'Regular delegate pass. Access to all sessions, exhibits, and networking.', 
 550.00, 'USD', NULL, 1, 0, 50, 2, 
 JSON_OBJECT('type', 'standard', 'eligibility', JSON_ARRAY('general'), 'approval_required', false, 'description_short', 'Regular registration'),
 NOW(), NOW()),

-- Student Delegate (Discounted for students)
(3, 'DELEGATE_STUDENT', 'Student Delegate', 
 'Discounted pass for students. Access to all sessions, exhibits, and networking. Student verification may be required.', 
 300.00, 'USD', NULL, 1, 0, 50, 3, 
 JSON_OBJECT('type', 'student', 'eligibility', JSON_ARRAY('students'), 'approval_required', false, 'description_short', 'Student discount', 'verification_note', 'Student ID may be required at event'),
 NOW(), NOW()),

-- Speaker (Complimentary pass)
(3, 'DELEGATE_SPEAKER', 'Speaker', 
 'Complimentary pass for event speakers. Full access to all sessions, exhibits, and networking.', 
 0.00, 'USD', 50, 1, 0, 20, 4, 
 JSON_OBJECT('type', 'speaker', 'eligibility', JSON_ARRAY('speakers'), 'approval_required', false, 'complimentary', true, 'description_short', 'Complimentary for speakers'),
 NOW(), NOW()),

-- VIP Delegate (Premium experience)
(3, 'DELEGATE_VIP', 'VIP Delegate', 
 'Premium delegate experience with exclusive access to VIP areas, priority seating, and enhanced networking opportunities.', 
 800.00, 'USD', 30, 1, 0, 20, 5, 
 JSON_OBJECT('type', 'vip', 'eligibility', JSON_ARRAY('general'), 'approval_required', false, 'premium', true, 'description_short', 'Premium experience with exclusive access'),
 NOW(), NOW()),

-- Event Organizer (Complimentary pass)
(3, 'DELEGATE_ORGANIZER', 'Event Organizer', 
 'Complimentary pass for event organizers and staff. Full access to all sessions, exhibits, and networking.', 
 0.00, 'USD', 20, 1, 0, 10, 6, 
 JSON_OBJECT('type', 'organizer', 'eligibility', JSON_ARRAY('organizers'), 'approval_required', false, 'complimentary', true, 'description_short', 'Complimentary for organizers'),
 NOW(), NOW());

-- =============================================================================
-- STEP 2: HANDLE EXISTING REGISTRATIONS DATA MIGRATION
-- =============================================================================

-- Get the product IDs for the new delegate types (we'll need these for migration)
SET @early_bird_id = (SELECT id FROM products WHERE product_code = 'DELEGATE_EARLY_BIRD');
SET @standard_id = (SELECT id FROM products WHERE product_code = 'DELEGATE_STANDARD');
SET @old_delegate_id = (SELECT id FROM products WHERE product_code = 'DELEGATE_PASS');

-- Update existing registration_products entries that reference the old DELEGATE_PASS
-- Map based on the delegate_type field in registrations table
UPDATE registration_products rp
JOIN registrations r ON rp.registration_id = r.id
SET rp.product_id = CASE 
    WHEN r.delegate_type = 'earlyBird' THEN @early_bird_id
    WHEN r.delegate_type = 'standard' THEN @standard_id
    ELSE @standard_id  -- Default fallback
END
WHERE rp.product_id = @old_delegate_id;

-- =============================================================================
-- STEP 3: CREATE SAMPLE PRICING TIERS FOR DELEGATE TYPES
-- =============================================================================

-- Add pricing tiers for Early Bird Delegate (super early bird tier)
INSERT INTO product_pricing (
    product_id, 
    pricing_tier, 
    price, 
    valid_from, 
    valid_until, 
    is_active,
    created_at
) VALUES 
(@early_bird_id, 'Super Early Bird', 400.00, '2025-06-01 00:00:00', '2025-07-31 23:59:59', 1, NOW()),
(@early_bird_id, 'Early Bird Extended', 475.00, '2025-08-01 00:00:00', '2025-09-30 23:59:59', 1, NOW());

-- Add pricing tiers for Standard Delegate
INSERT INTO product_pricing (
    product_id, 
    pricing_tier, 
    price, 
    valid_from, 
    valid_until, 
    is_active,
    created_at
) VALUES 
(@standard_id, 'Late Registration', 600.00, '2025-10-01 00:00:00', '2025-11-25 23:59:59', 1, NOW());

-- Add pricing tiers for VIP Delegate
SET @vip_id = (SELECT id FROM products WHERE product_code = 'DELEGATE_VIP');
INSERT INTO product_pricing (
    product_id, 
    pricing_tier, 
    price, 
    valid_from, 
    valid_until, 
    is_active,
    created_at
) VALUES 
(@vip_id, 'VIP Early Bird', 750.00, '2025-06-01 00:00:00', '2025-08-31 23:59:59', 1, NOW());

-- =============================================================================
-- STEP 4: UPDATE ADMIN ACTIVITY LOG
-- =============================================================================

-- Log this migration for audit purposes
INSERT INTO admin_activity_log (
    admin_user_id, 
    action, 
    target_type, 
    details, 
    ip_address, 
    created_at
) VALUES (
    1, 
    'SYSTEM_MIGRATION', 
    'products', 
    'Multiple Delegate Types Migration: Created 6 new delegate type products (EARLY_BIRD, STANDARD, STUDENT, SPEAKER, VIP, ORGANIZER) and migrated existing registration data', 
    '127.0.0.1', 
    NOW()
);

-- =============================================================================
-- STEP 5: DEACTIVATE OLD DELEGATE_PASS PRODUCT
-- =============================================================================

-- Deactivate the old generic DELEGATE_PASS product (don't delete for data integrity)
UPDATE products 
SET is_active = 0, 
    updated_at = NOW(),
    name = 'Delegates (Legacy - Migrated)'
WHERE product_code = 'DELEGATE_PASS';

-- =============================================================================
-- STEP 6: UPDATE DISPLAY CONFIGURATIONS (if exists)
-- =============================================================================

-- Update frontend_config table to ensure delegate passes section shows all new products
-- This ensures all new delegate types appear in the frontend
INSERT IGNORE INTO frontend_config (page_section, category_id, display_mode, created_at, updated_at)
VALUES ('delegate_passes', 3, 'category', NOW(), NOW())
ON DUPLICATE KEY UPDATE 
    display_mode = 'category',
    updated_at = NOW();

-- =============================================================================
-- STEP 7: CREATE VERIFICATION VIEWS FOR MIGRATION SUCCESS
-- =============================================================================

-- Create a temporary view to verify migration success
CREATE OR REPLACE VIEW migration_verification AS
SELECT 
    'New Delegate Products' as check_type,
    COUNT(*) as count,
    'Should be 6' as expected
FROM products 
WHERE product_code IN ('DELEGATE_EARLY_BIRD', 'DELEGATE_STANDARD', 'DELEGATE_STUDENT', 'DELEGATE_SPEAKER', 'DELEGATE_VIP', 'DELEGATE_ORGANIZER')
    AND is_active = 1

UNION ALL

SELECT 
    'Migrated Registration Products' as check_type,
    COUNT(*) as count,
    'Should match existing registrations with delegates' as expected
FROM registration_products rp
JOIN products p ON rp.product_id = p.id
WHERE p.product_code IN ('DELEGATE_EARLY_BIRD', 'DELEGATE_STANDARD', 'DELEGATE_STUDENT', 'DELEGATE_SPEAKER', 'DELEGATE_VIP', 'DELEGATE_ORGANIZER')

UNION ALL

SELECT 
    'Legacy Product Deactivated' as check_type,
    COUNT(*) as count,
    'Should be 1' as expected
FROM products 
WHERE product_code = 'DELEGATE_PASS' AND is_active = 0

UNION ALL

SELECT 
    'Pricing Tiers Created' as check_type,
    COUNT(*) as count,
    'Should be > 0' as expected
FROM product_pricing pp
JOIN products p ON pp.product_id = p.id
WHERE p.product_code IN ('DELEGATE_EARLY_BIRD', 'DELEGATE_STANDARD', 'DELEGATE_VIP');

-- =============================================================================
-- MIGRATION COMPLETE - COMMIT TRANSACTION
-- =============================================================================

-- If we reach here without errors, commit the transaction
COMMIT;

-- =============================================================================
-- POST-MIGRATION VERIFICATION QUERY
-- =============================================================================

-- Run this query after migration to verify everything worked correctly
SELECT * FROM migration_verification;

-- =============================================================================
-- ROLLBACK INSTRUCTIONS (if needed)
-- =============================================================================
/*
If you need to rollback this migration, run the following:

START TRANSACTION;

-- Reactivate old DELEGATE_PASS product
UPDATE products SET is_active = 1, name = 'Delegates' WHERE product_code = 'DELEGATE_PASS';

-- Restore old registration_products mappings
UPDATE registration_products rp
JOIN registrations r ON rp.registration_id = r.id
SET rp.product_id = (SELECT id FROM products WHERE product_code = 'DELEGATE_PASS')
WHERE rp.product_id IN (
    SELECT id FROM products WHERE product_code IN 
    ('DELEGATE_EARLY_BIRD', 'DELEGATE_STANDARD', 'DELEGATE_STUDENT', 'DELEGATE_SPEAKER', 'DELEGATE_VIP', 'DELEGATE_ORGANIZER')
);

-- Delete new products and their pricing tiers
DELETE FROM product_pricing WHERE product_id IN (
    SELECT id FROM products WHERE product_code IN 
    ('DELEGATE_EARLY_BIRD', 'DELEGATE_STANDARD', 'DELEGATE_STUDENT', 'DELEGATE_SPEAKER', 'DELEGATE_VIP', 'DELEGATE_ORGANIZER')
);

DELETE FROM products WHERE product_code IN 
('DELEGATE_EARLY_BIRD', 'DELEGATE_STANDARD', 'DELEGATE_STUDENT', 'DELEGATE_SPEAKER', 'DELEGATE_VIP', 'DELEGATE_ORGANIZER');

-- Drop verification view
DROP VIEW IF EXISTS migration_verification;

COMMIT;
*/
