-- =====================================================
-- Migration: Add department field to delegates table
-- Description: Adds optional department column for individual delegate information
-- Created: 2025-10-24
-- 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 department column already exists (prevents duplicate execution)
SELECT 'Checking if department column exists...' as Info;
SELECT COUNT(*) as department_column_exists
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_SCHEMA = DATABASE()
AND TABLE_NAME = 'delegates' 
AND COLUMN_NAME = 'department';

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

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

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

CREATE INDEX idx_delegate_department ON delegates(department);

-- 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,
    department,
    email,
    dietary_requirements,
    special_assistance,
    created_at
FROM delegates 
LIMIT 1;

-- Statistics after migration
SELECT 'Migration statistics:' as Info;
SELECT 
    COUNT(*) as total_delegates,
    COUNT(CASE WHEN department IS NOT NULL THEN 1 END) as delegates_with_department,
    COUNT(CASE WHEN department IS NULL THEN 1 END) as delegates_without_department
FROM delegates;

SELECT 'Migration completed successfully!' as Info;

