-- =====================================================
-- Migration: Add email field to delegates table
-- Description: Adds optional email column for individual delegate communication
-- Created: 2025-10-08
-- Risk Level: LOW (Adding optional column, backward compatible)
-- Estimated Time: < 1 minute
-- =====================================================

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

-- Check if email column already exists (prevents duplicate execution)
SELECT 'Checking if email column exists...' as Info;
SELECT COUNT(*) as email_column_exists
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_SCHEMA = DATABASE()
AND TABLE_NAME = 'delegates' 
AND COLUMN_NAME = 'email';

-- Add email column to delegates table
-- Position: After designation (for logical flow)
-- Type: VARCHAR(255) - standard email length
-- Default: NULL - optional field, backward compatible
SELECT 'Adding email column to delegates table...' as Info;

ALTER TABLE delegates 
ADD COLUMN email VARCHAR(255) DEFAULT NULL 
COMMENT 'Optional email for individual delegate communication'
AFTER designation;

-- Add index for email lookups (improves search/filter performance)
SELECT 'Creating index on email column...' as Info;

CREATE INDEX idx_delegate_email ON delegates(email);

-- Verify changes
SELECT 'Verifying changes...' as Info;
DESCRIBE delegates;

-- Show sample data structure
SELECT 'Sample delegate record structure:' as Info;
SELECT 
    id,
    full_name,
    badge_name,
    designation,
    email,
    dietary_requirements,
    special_assistance,
    created_at
FROM delegates 
LIMIT 1;

-- Statistics after migration
SELECT 'Migration statistics:' as Info;
SELECT 
    COUNT(*) as total_delegates,
    SUM(CASE WHEN email IS NOT NULL AND email != '' THEN 1 ELSE 0 END) as delegates_with_email,
    SUM(CASE WHEN email IS NULL OR email = '' THEN 1 ELSE 0 END) as delegates_without_email,
    ROUND(SUM(CASE WHEN email IS NOT NULL AND email != '' THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 2) as percentage_with_email
FROM delegates;

-- =====================================================
-- Migration completed successfully!
-- 
-- Next steps:
-- 1. Verify column was added: SHOW COLUMNS FROM delegates LIKE 'email';
-- 2. Deploy updated code files (registration.js, api.php, admin pages)
-- 3. Test new registration with email field
-- 4. Test editing existing delegates
-- 
-- Rollback (if needed):
-- ALTER TABLE delegates DROP INDEX idx_delegate_email;
-- ALTER TABLE delegates DROP COLUMN email;
-- =====================================================

SELECT '✓ Migration completed successfully!' as Status;
SELECT CONCAT(
    'Email column added to delegates table. ',
    'All existing delegates have NULL for email (backward compatible). ',
    'New registrations can include optional email addresses.'
) as Summary;

