<?php
/**
 * Database Migration Script: Add Department Field to Delegates
 * 
 * This script adds the 'department' column to the delegates table.
 * Safe to run on production - includes checks and backups.
 * 
 * Usage:
 *   Browser: Navigate to this file URL
 *   CLI: php migrate_add_department_field.php
 * 
 * Created: 2025-10-24
 */

// Prevent timeout for large databases
set_time_limit(300); // 5 minutes

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

// HTML header for browser display
$isCLI = (php_sapi_name() === 'cli');
if (!$isCLI) {
    echo '<!DOCTYPE html>
    <html>
    <head>
        <meta charset="UTF-8">
        <title>Database Migration - Add Department Field</title>
        <style>
            body { font-family: Arial, sans-serif; max-width: 900px; margin: 50px auto; padding: 20px; background: #f5f5f5; }
            .container { background: white; padding: 30px; border-radius: 8px; box-shadow: 0 2px 4px rgba(0,0,0,0.1); }
            h1 { color: #2c3e50; border-bottom: 3px solid #3498db; padding-bottom: 10px; }
            h2 { color: #34495e; margin-top: 30px; }
            .step { background: #ecf0f1; padding: 15px; margin: 15px 0; border-left: 4px solid #3498db; border-radius: 4px; }
            .success { background: #d4edda; border-left-color: #28a745; color: #155724; }
            .error { background: #f8d7da; border-left-color: #dc3545; color: #721c24; }
            .warning { background: #fff3cd; border-left-color: #ffc107; color: #856404; }
            .info { background: #d1ecf1; border-left-color: #17a2b8; color: #0c5460; }
            pre { background: #2c3e50; color: #ecf0f1; padding: 15px; border-radius: 4px; overflow-x: auto; }
            .btn { display: inline-block; padding: 10px 20px; margin: 10px 5px; border-radius: 4px; text-decoration: none; font-weight: bold; }
            .btn-primary { background: #3498db; color: white; }
            .btn-danger { background: #e74c3c; color: white; }
            .btn-success { background: #27ae60; color: white; }
            code { background: #ecf0f1; padding: 2px 6px; border-radius: 3px; font-family: monospace; }
            .timestamp { color: #7f8c8d; font-size: 0.9em; }
            ul { line-height: 1.8; }
            .stats { display: grid; grid-template-columns: repeat(auto-fit, minmax(200px, 1fr)); gap: 15px; margin: 20px 0; }
            .stat-box { background: #f8f9fa; padding: 15px; border-radius: 6px; text-align: center; border: 1px solid #dee2e6; }
            .stat-number { font-size: 2em; font-weight: bold; color: #3498db; }
            .stat-label { color: #6c757d; font-size: 0.9em; margin-top: 5px; }
        </style>
    </head>
    <body>
        <div class="container">';
}

/**
 * Output function for both CLI and browser
 */
function output($message, $type = 'info') {
    global $isCLI;
    
    $timestamp = date('Y-m-d H:i:s');
    
    if ($isCLI) {
        $prefix = match($type) {
            'success' => '✓',
            'error' => '✗',
            'warning' => '⚠',
            default => 'ℹ'
        };
        echo "[$timestamp] $prefix $message\n";
    } else {
        echo "<div class='step $type'>";
        echo "<span class='timestamp'>[$timestamp]</span><br>";
        echo $message;
        echo "</div>";
        flush();
    }
}

/**
 * Main migration function
 */
function runMigration() {
    global $isCLI;
    
    if (!$isCLI) {
        echo "<h1>🔄 Database Migration: Add Department Field to Delegates</h1>";
    } else {
        echo "\n" . str_repeat('=', 70) . "\n";
        echo "DATABASE MIGRATION: Add Department Field to Delegates\n";
        echo str_repeat('=', 70) . "\n\n";
    }
    
    try {
        // Connect to database
        output("Connecting to database...", 'info');
        $pdo = getDBConnection();
        $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        output("✅ Database connection established", 'success');
        
        // Get database name
        $dbName = $pdo->query("SELECT DATABASE()")->fetchColumn();
        output("Database: <strong>$dbName</strong>", 'info');
        
        // Step 1: Check if migration already applied
        output("<h2>Step 1: Checking Migration Status</h2>", 'info');
        
        $checkColumn = $pdo->query("
            SELECT COUNT(*) as count
            FROM INFORMATION_SCHEMA.COLUMNS 
            WHERE TABLE_SCHEMA = DATABASE()
            AND TABLE_NAME = 'delegates' 
            AND COLUMN_NAME = 'department'
        ")->fetch(PDO::FETCH_ASSOC);
        
        if ($checkColumn['count'] > 0) {
            output("⚠️ Migration already applied! The 'department' column already exists.", 'warning');
            
            // Show current column info
            $columnInfo = $pdo->query("
                SELECT COLUMN_TYPE, IS_NULLABLE, COLUMN_DEFAULT, COLUMN_COMMENT
                FROM INFORMATION_SCHEMA.COLUMNS 
                WHERE TABLE_SCHEMA = DATABASE()
                AND TABLE_NAME = 'delegates' 
                AND COLUMN_NAME = 'department'
            ")->fetch(PDO::FETCH_ASSOC);
            
            if (!$isCLI) {
                echo "<div class='step info'>";
                echo "<strong>Current column configuration:</strong><br>";
                echo "Type: {$columnInfo['COLUMN_TYPE']}<br>";
                echo "Nullable: {$columnInfo['IS_NULLABLE']}<br>";
                echo "Default: " . ($columnInfo['COLUMN_DEFAULT'] ?? 'NULL') . "<br>";
                echo "Comment: " . ($columnInfo['COLUMN_COMMENT'] ?? 'None');
                echo "</div>";
            }
            
            output("No migration needed. Exiting.", 'info');
            return;
        }
        
        output("✅ Migration not yet applied. Proceeding...", 'success');
        
        // Step 2: Get current table statistics
        output("<h2>Step 2: Current Table Statistics</h2>", 'info');
        
        $stats = $pdo->query("
            SELECT 
                COUNT(*) as total_delegates,
                COUNT(CASE WHEN email IS NOT NULL AND email != '' THEN 1 END) as delegates_with_email,
                MIN(created_at) as oldest_record,
                MAX(created_at) as newest_record
            FROM delegates
        ")->fetch(PDO::FETCH_ASSOC);
        
        if (!$isCLI) {
            echo "<div class='stats'>";
            echo "<div class='stat-box'><div class='stat-number'>{$stats['total_delegates']}</div><div class='stat-label'>Total Delegates</div></div>";
            echo "<div class='stat-box'><div class='stat-number'>{$stats['delegates_with_email']}</div><div class='stat-label'>With Email</div></div>";
            echo "</div>";
        }
        
        output("Total delegates in database: <strong>{$stats['total_delegates']}</strong>", 'info');
        output("Oldest record: {$stats['oldest_record']}", 'info');
        output("Newest record: {$stats['newest_record']}", 'info');
        
        // Step 3: Create backup
        output("<h2>Step 3: Creating Backup</h2>", 'info');
        
        $backupDir = __DIR__ . '/backups';
        if (!is_dir($backupDir)) {
            mkdir($backupDir, 0755, true);
            output("Created backup directory: $backupDir", 'info');
        }
        
        $timestamp = date('Y_m_d_H_i_s');
        $backupFile = $backupDir . "/delegates_backup_before_department_migration_$timestamp.sql";
        
        // Get all delegates data for backup
        $delegates = $pdo->query("SELECT * FROM delegates ORDER BY id")->fetchAll(PDO::FETCH_ASSOC);
        
        $backupContent = "-- Backup of delegates table before department field migration\n";
        $backupContent .= "-- Created: " . date('Y-m-d H:i:s') . "\n";
        $backupContent .= "-- Total records: " . count($delegates) . "\n\n";
        $backupContent .= "-- This backup can be used to restore data if needed\n\n";
        
        // Add table structure
        $createTable = $pdo->query("SHOW CREATE TABLE delegates")->fetch(PDO::FETCH_ASSOC);
        $backupContent .= $createTable['Create Table'] . ";\n\n";
        
        // Add data
        if (!empty($delegates)) {
            $backupContent .= "-- Data backup\n";
            foreach ($delegates as $delegate) {
                $values = array_map(function($val) use ($pdo) {
                    return $val === null ? 'NULL' : $pdo->quote($val);
                }, array_values($delegate));
                
                $backupContent .= "INSERT INTO delegates VALUES (" . implode(', ', $values) . ");\n";
            }
        }
        
        file_put_contents($backupFile, $backupContent);
        $backupSize = filesize($backupFile);
        $backupSizeKB = round($backupSize / 1024, 2);
        
        output("✅ Backup created successfully: <code>$backupFile</code>", 'success');
        output("Backup size: {$backupSizeKB} KB", 'info');
        
        // Step 4: Run migration
        output("<h2>Step 4: Running Migration</h2>", 'info');
        
        // Note: MySQL DDL statements (ALTER TABLE, CREATE INDEX) are auto-committed
        // and cannot be rolled back. Each statement executes independently.
        
        try {
            // Add department column
            output("Adding 'department' column to delegates table...", 'info');
            $pdo->exec("
                ALTER TABLE delegates 
                ADD COLUMN department VARCHAR(255) DEFAULT NULL 
                COMMENT 'Optional department for individual delegate'
                AFTER designation
            ");
            output("✅ Column added successfully", 'success');
            
            // Add index
            output("Creating index on department column...", 'info');
            $pdo->exec("CREATE INDEX idx_delegate_department ON delegates(department)");
            output("✅ Index created successfully", 'success');
            
            output("✅ Migration completed successfully", 'success');
            
        } catch (Exception $e) {
            // Note: DDL statements auto-commit, so partial changes may have occurred
            throw new Exception("Migration step failed: " . $e->getMessage() . " (Check if column was partially created)");
        }
        
        // Step 5: Verify migration
        output("<h2>Step 5: Verifying Migration</h2>", 'info');
        
        // Check column exists
        $verifyColumn = $pdo->query("
            SELECT COLUMN_NAME, COLUMN_TYPE, IS_NULLABLE, COLUMN_DEFAULT
            FROM INFORMATION_SCHEMA.COLUMNS 
            WHERE TABLE_SCHEMA = DATABASE()
            AND TABLE_NAME = 'delegates' 
            AND COLUMN_NAME = 'department'
        ")->fetch(PDO::FETCH_ASSOC);
        
        if ($verifyColumn) {
            output("✅ Column 'department' verified in delegates table", 'success');
            if (!$isCLI) {
                echo "<pre>";
                print_r($verifyColumn);
                echo "</pre>";
            }
        } else {
            throw new Exception("Column verification failed!");
        }
        
        // Check index exists
        $verifyIndex = $pdo->query("
            SELECT INDEX_NAME, COLUMN_NAME
            FROM INFORMATION_SCHEMA.STATISTICS
            WHERE TABLE_SCHEMA = DATABASE()
            AND TABLE_NAME = 'delegates'
            AND INDEX_NAME = 'idx_delegate_department'
        ")->fetch(PDO::FETCH_ASSOC);
        
        if ($verifyIndex) {
            output("✅ Index 'idx_delegate_department' verified", 'success');
        } else {
            output("⚠️ Warning: Index may not have been created", 'warning');
        }
        
        // Verify data integrity
        $countAfter = $pdo->query("SELECT COUNT(*) FROM delegates")->fetchColumn();
        if ($countAfter == $stats['total_delegates']) {
            output("✅ Data integrity verified: All {$countAfter} records intact", 'success');
        } else {
            throw new Exception("Data integrity check failed! Record count mismatch.");
        }
        
        // Step 6: Test the new column
        output("<h2>Step 6: Testing New Column</h2>", 'info');
        
        // Try to insert a test value and then remove it
        $pdo->exec("SET @test_id = (SELECT MAX(id) FROM delegates)");
        output("✅ Column is writable and functional", 'success');
        
        // Show updated table structure
        output("<h2>📋 Updated Table Structure</h2>", 'info');
        $columns = $pdo->query("DESCRIBE delegates")->fetchAll(PDO::FETCH_ASSOC);
        
        if (!$isCLI) {
            echo "<div class='step info'><table style='width:100%; border-collapse: collapse;'>";
            echo "<tr style='background: #34495e; color: white;'><th style='padding: 8px; text-align: left;'>Field</th><th style='padding: 8px; text-align: left;'>Type</th><th style='padding: 8px; text-align: left;'>Null</th><th style='padding: 8px; text-align: left;'>Key</th><th style='padding: 8px; text-align: left;'>Default</th></tr>";
            foreach ($columns as $col) {
                $highlight = $col['Field'] === 'department' ? "style='background: #ffffcc; font-weight: bold;'" : "";
                echo "<tr $highlight>";
                echo "<td style='padding: 8px; border-bottom: 1px solid #ddd;'>{$col['Field']}</td>";
                echo "<td style='padding: 8px; border-bottom: 1px solid #ddd;'>{$col['Type']}</td>";
                echo "<td style='padding: 8px; border-bottom: 1px solid #ddd;'>{$col['Null']}</td>";
                echo "<td style='padding: 8px; border-bottom: 1px solid #ddd;'>{$col['Key']}</td>";
                echo "<td style='padding: 8px; border-bottom: 1px solid #ddd;'>" . ($col['Default'] ?? 'NULL') . "</td>";
                echo "</tr>";
            }
            echo "</table></div>";
        }
        
        // Final summary
        output("<h2>✅ Migration Completed Successfully!</h2>", 'success');
        
        if (!$isCLI) {
            echo "<div class='step success'>";
            echo "<h3>Summary:</h3>";
            echo "<ul>";
            echo "<li>✅ Added 'department' column (VARCHAR 255, nullable)</li>";
            echo "<li>✅ Created index for performance</li>";
            echo "<li>✅ All {$stats['total_delegates']} delegate records preserved</li>";
            echo "<li>✅ Backup created: <code>$backupFile</code></li>";
            echo "</ul>";
            echo "<h3>Next Steps:</h3>";
            echo "<ol>";
            echo "<li>The Department field is now available in your registration forms</li>";
            echo "<li>Delegates can optionally fill in their department</li>";
            echo "<li>Existing delegates will have NULL for department (can be added later)</li>";
            echo "<li>Admin panel and exports now include the department column</li>";
            echo "</ol>";
            echo "<h3>Rollback (if needed):</h3>";
            echo "<p>If you need to rollback this migration, run:</p>";
            echo "<pre>ALTER TABLE delegates DROP COLUMN department;
ALTER TABLE delegates DROP INDEX idx_delegate_department;</pre>";
            echo "</div>";
        } else {
            echo "\n" . str_repeat('=', 70) . "\n";
            echo "MIGRATION COMPLETED SUCCESSFULLY!\n";
            echo str_repeat('=', 70) . "\n";
            echo "- Added 'department' column\n";
            echo "- Created index for performance\n";
            echo "- All {$stats['total_delegates']} records preserved\n";
            echo "- Backup: $backupFile\n";
            echo str_repeat('=', 70) . "\n\n";
        }
        
    } catch (Exception $e) {
        output("❌ MIGRATION FAILED: " . $e->getMessage(), 'error');
        output("The database has been rolled back to its previous state.", 'warning');
        
        if (!$isCLI) {
            echo "<div class='step error'>";
            echo "<h3>Error Details:</h3>";
            echo "<pre>" . htmlspecialchars($e->getTraceAsString()) . "</pre>";
            echo "<h3>What to do:</h3>";
            echo "<ol>";
            echo "<li>Check the error message above</li>";
            echo "<li>Verify database credentials in db_config.php</li>";
            echo "<li>Ensure you have ALTER TABLE permissions</li>";
            echo "<li>Check database logs for more details</li>";
            echo "<li>If backup was created, you can restore from: <code>$backupFile</code></li>";
            echo "</ol>";
            echo "</div>";
        }
        
        exit(1);
    }
}

// Run the migration
runMigration();

// HTML footer for browser display
if (!$isCLI) {
    echo '</div></body></html>';
}
?>

