<?php
/**
 * Migration Script: Add Email Field to Delegates Table
 * 
 * This script adds an optional email field to the delegates table.
 * Safe to run multiple times - includes duplicate execution prevention.
 * 
 * Created: 2025-01-27
 * Risk Level: LOW (Adding optional column, backward compatible)
 */

// Include database configuration
require_once 'db_config.php';

// Set content type for better output
header('Content-Type: text/html; charset=UTF-8');

echo "<!DOCTYPE html>
<html>
<head>
    <title>Email Field Migration</title>
    <style>
        body { font-family: Arial, sans-serif; margin: 20px; }
        .success { color: green; font-weight: bold; }
        .error { color: red; font-weight: bold; }
        .info { color: blue; }
        .warning { color: orange; }
        pre { background: #f5f5f5; padding: 10px; border-radius: 5px; }
        .step { margin: 10px 0; padding: 10px; border-left: 3px solid #007cba; }
    </style>
</head>
<body>";

echo "<h1>📧 Delegate Email Field Migration</h1>";
echo "<p><strong>Purpose:</strong> Add optional email field to delegates table for individual delegate communication</p>";

try {
    // Establish database connection
    $pdo = getDBConnection();
    
    // Step 1: Check if email column already exists
    echo "<div class='step'>";
    echo "<h3>Step 1: Checking if email column already exists...</h3>";
    
    $checkQuery = "SELECT COUNT(*) as email_column_exists
                   FROM INFORMATION_SCHEMA.COLUMNS 
                   WHERE TABLE_SCHEMA = DATABASE()
                   AND TABLE_NAME = 'delegates' 
                   AND COLUMN_NAME = 'email'";
    
    $result = $pdo->query($checkQuery);
    $exists = $result->fetch(PDO::FETCH_ASSOC)['email_column_exists'];
    
    if ($exists > 0) {
        echo "<p class='warning'>⚠️ Email column already exists! Migration not needed.</p>";
        echo "<p>You can safely close this page.</p>";
        echo "</div>";
        exit;
    } else {
        echo "<p class='info'>✅ Email column does not exist. Proceeding with migration...</p>";
    }
    echo "</div>";

    // Step 2: Display current table structure
    echo "<div class='step'>";
    echo "<h3>Step 2: Current delegates table structure</h3>";
    
    $describeQuery = "DESCRIBE delegates";
    $result = $pdo->query($describeQuery);
    $columns = $result->fetchAll(PDO::FETCH_ASSOC);
    
    echo "<pre>";
    foreach ($columns as $column) {
        echo sprintf("%-20s %-20s %-10s %-10s %-10s %-20s\n", 
            $column['Field'], 
            $column['Type'], 
            $column['Null'], 
            $column['Key'], 
            $column['Default'], 
            $column['Extra']
        );
    }
    echo "</pre>";
    echo "</div>";

    // Step 3: Add email column
    echo "<div class='step'>";
    echo "<h3>Step 3: Adding email column to delegates table...</h3>";
    
    $alterQuery = "ALTER TABLE delegates 
                   ADD COLUMN email VARCHAR(255) DEFAULT NULL 
                   COMMENT 'Optional email for individual delegate communication'
                   AFTER designation";
    
    $pdo->exec($alterQuery);
    echo "<p class='success'>✅ Email column added successfully!</p>";
    echo "</div>";

    // Step 4: Create index
    echo "<div class='step'>";
    echo "<h3>Step 4: Creating index on email column...</h3>";
    
    $indexQuery = "CREATE INDEX idx_delegate_email ON delegates(email)";
    
    try {
        $pdo->exec($indexQuery);
        echo "<p class='success'>✅ Index created successfully!</p>";
    } catch (PDOException $e) {
        if (strpos($e->getMessage(), 'Duplicate key name') !== false) {
            echo "<p class='warning'>⚠️ Index already exists (this is fine)</p>";
        } else {
            throw $e;
        }
    }
    echo "</div>";

    // Step 5: Verify changes
    echo "<div class='step'>";
    echo "<h3>Step 5: Verifying changes...</h3>";
    
    $describeQuery = "DESCRIBE delegates";
    $result = $pdo->query($describeQuery);
    $columns = $result->fetchAll(PDO::FETCH_ASSOC);
    
    $emailColumn = null;
    foreach ($columns as $column) {
        if ($column['Field'] === 'email') {
            $emailColumn = $column;
            break;
        }
    }
    
    if ($emailColumn) {
        echo "<p class='success'>✅ Email column verified!</p>";
        echo "<pre>";
        echo sprintf("%-20s %-20s %-10s %-10s %-10s %-20s\n", 
            $emailColumn['Field'], 
            $emailColumn['Type'], 
            $emailColumn['Null'], 
            $emailColumn['Key'], 
            $emailColumn['Default'], 
            $emailColumn['Extra']
        );
        echo "</pre>";
    } else {
        echo "<p class='error'>❌ Email column not found after migration!</p>";
    }
    echo "</div>";

    // Step 6: Show statistics
    echo "<div class='step'>";
    echo "<h3>Step 6: Migration statistics</h3>";
    
    $statsQuery = "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
        FROM delegates";
    
    $result = $pdo->query($statsQuery);
    $stats = $result->fetch(PDO::FETCH_ASSOC);
    
    echo "<ul>";
    echo "<li><strong>Total delegates:</strong> " . $stats['total_delegates'] . "</li>";
    echo "<li><strong>Delegates with email:</strong> " . $stats['delegates_with_email'] . "</li>";
    echo "<li><strong>Delegates without email:</strong> " . $stats['delegates_without_email'] . "</li>";
    echo "</ul>";
    echo "</div>";

    // Success message
    echo "<div class='step'>";
    echo "<h2 class='success'>🎉 Migration Completed Successfully!</h2>";
    echo "<p><strong>What was added:</strong></p>";
    echo "<ul>";
    echo "<li>✅ <code>email</code> column (VARCHAR(255), nullable)</li>";
    echo "<li>✅ <code>idx_delegate_email</code> index for performance</li>";
    echo "</ul>";
    
    echo "<p><strong>Next steps:</strong></p>";
    echo "<ul>";
    echo "<li>✅ Database migration complete</li>";
    echo "<li>🔧 Code files are already updated (registration.js, api.php, admin pages)</li>";
    echo "<li>🧪 Test new registration with email field</li>";
    echo "<li>✏️ Test editing existing delegates in admin panel</li>";
    echo "</ul>";
    
    echo "<p><strong>Rollback (if needed):</strong></p>";
    echo "<pre>ALTER TABLE delegates DROP INDEX idx_delegate_email;
ALTER TABLE delegates DROP COLUMN email;</pre>";
    echo "</div>";

} catch (PDOException $e) {
    echo "<div class='step'>";
    echo "<h3 class='error'>❌ Migration Failed</h3>";
    echo "<p class='error'>Error: " . htmlspecialchars($e->getMessage()) . "</p>";
    echo "<p>Please check your database connection and try again.</p>";
    echo "</div>";
} catch (Exception $e) {
    echo "<div class='step'>";
    echo "<h3 class='error'>❌ Unexpected Error</h3>";
    echo "<p class='error'>Error: " . htmlspecialchars($e->getMessage()) . "</p>";
    echo "</div>";
}

echo "</body></html>";
?>
