<?php
require_once 'config/config.php';

echo "<h1>🌍 Complete Nationality & Country Migration</h1>\n";
echo "<p>This script will set up all nationality-related fields including country information for foreign nationals.</p>\n";

try {
    $database = new Database();
    $db = $database->getConnection();
    
    echo "<p>✓ Database connection successful</p>\n";
    echo "<hr>\n";
    
    // Step 1: Add is_foreign_national column if missing
    echo "<h3>Step 1: Adding is_foreign_national column</h3>\n";
    $stmt = $db->query("SHOW COLUMNS FROM candidates LIKE 'is_foreign_national'");
    if (!$stmt->fetch()) {
        echo "<p>Adding is_foreign_national column...</p>\n";
        $db->exec("ALTER TABLE candidates ADD COLUMN is_foreign_national ENUM('yes', 'no') DEFAULT 'no' AFTER hr_approval_status");
        echo "<div style='color: green;'>✓ Added is_foreign_national column</div>\n";
    } else {
        echo "<div style='color: orange;'>⚠ is_foreign_national column already exists</div>\n";
    }
    
    // Step 2: Add passport_number column if missing
    echo "<h3>Step 2: Adding passport_number column</h3>\n";
    $stmt = $db->query("SHOW COLUMNS FROM candidates LIKE 'passport_number'");
    if (!$stmt->fetch()) {
        echo "<p>Adding passport_number column...</p>\n";
        $db->exec("ALTER TABLE candidates ADD COLUMN passport_number VARCHAR(50) NULL AFTER is_foreign_national");
        echo "<div style='color: green;'>✓ Added passport_number column</div>\n";
    } else {
        echo "<div style='color: orange;'>⚠ passport_number column already exists</div>\n";
    }
    
    // Step 3: Add sa_id_number column if missing
    echo "<h3>Step 3: Adding sa_id_number column</h3>\n";
    $stmt = $db->query("SHOW COLUMNS FROM candidates LIKE 'sa_id_number'");
    if (!$stmt->fetch()) {
        echo "<p>Adding sa_id_number column...</p>\n";
        $db->exec("ALTER TABLE candidates ADD COLUMN sa_id_number VARCHAR(13) NULL AFTER passport_number");
        echo "<div style='color: green;'>✓ Added sa_id_number column</div>\n";
    } else {
        echo "<div style='color: orange;'>⚠ sa_id_number column already exists</div>\n";
    }
    
    // Step 4: Add country column if missing  
    echo "<h3>Step 4: Adding country column</h3>\n";
    $stmt = $db->query("SHOW COLUMNS FROM candidates LIKE 'country'");
    if (!$stmt->fetch()) {
        echo "<p>Adding country column...</p>\n";
        $db->exec("ALTER TABLE candidates ADD COLUMN country VARCHAR(100) NULL AFTER sa_id_number");
        echo "<div style='color: green;'>✓ Added country column</div>\n";
    } else {
        echo "<div style='color: orange;'>⚠ country column already exists</div>\n";
    }
    
    // Step 5: Update existing candidates with default values
    echo "<h3>Step 5: Updating existing candidates</h3>\n";
    $updated = $db->exec("UPDATE candidates SET is_foreign_national = 'no' WHERE is_foreign_national IS NULL");
    echo "<div style='color: green;'>✓ Updated $updated candidates to set default nationality status</div>\n";
    
    // Step 6: Add indexes for performance
    echo "<h3>Step 6: Adding database indexes</h3>\n";
    
    $indexes = [
        'idx_is_foreign_national' => 'ALTER TABLE candidates ADD INDEX idx_is_foreign_national (is_foreign_national)',
        'idx_sa_id_number' => 'ALTER TABLE candidates ADD UNIQUE INDEX idx_sa_id_number (sa_id_number)',
        'idx_country' => 'ALTER TABLE candidates ADD INDEX idx_country (country)'
    ];
    
    foreach ($indexes as $index_name => $sql) {
        try {
            $db->exec($sql);
            echo "<div style='color: green;'>✓ Added {$index_name} index</div>\n";
        } catch (PDOException $e) {
            echo "<div style='color: orange;'>⚠ {$index_name} index might already exist</div>\n";
        }
    }
    
    // Step 7: Verification and Statistics
    echo "<h3>Step 7: Verification & Statistics</h3>\n";
    
    // Verify all columns exist
    $stmt = $db->query("DESCRIBE candidates");
    $columns = $stmt->fetchAll();
    $column_names = array_column($columns, 'Field');
    
    $required_columns = ['is_foreign_national', 'passport_number', 'sa_id_number', 'country'];
    $missing_columns = [];
    
    foreach ($required_columns as $col) {
        if (in_array($col, $column_names)) {
            echo "<div style='color: green;'>✓ {$col} column exists</div>\n";
        } else {
            echo "<div style='color: red;'>✗ {$col} column missing</div>\n";
            $missing_columns[] = $col;
        }
    }
    
    if (empty($missing_columns)) {
        // Get comprehensive statistics
        $stmt = $db->query("SELECT COUNT(*) as total, 
                                  SUM(CASE WHEN is_foreign_national = 'no' THEN 1 ELSE 0 END) as sa_candidates,
                                  SUM(CASE WHEN is_foreign_national = 'yes' THEN 1 ELSE 0 END) as foreign_candidates,
                                  SUM(CASE WHEN sa_id_number IS NOT NULL AND sa_id_number != '' THEN 1 ELSE 0 END) as with_sa_id,
                                  SUM(CASE WHEN passport_number IS NOT NULL AND passport_number != '' THEN 1 ELSE 0 END) as with_passport,
                                  SUM(CASE WHEN country IS NOT NULL AND country != '' THEN 1 ELSE 0 END) as with_country
                           FROM candidates");
        $stats = $stmt->fetch();
        
        // Get top countries
        $stmt = $db->query("SELECT country, COUNT(*) as count 
                           FROM candidates 
                           WHERE country IS NOT NULL AND country != '' 
                           GROUP BY country 
                           ORDER BY count DESC 
                           LIMIT 10");
        $top_countries = $stmt->fetchAll();
        
        echo "<div style='color: green; padding: 20px; background: #d4edda; border: 1px solid #c3e6cb; margin: 20px 0; border-radius: 5px;'>\n";
        echo "<h2>🎉 MIGRATION COMPLETED SUCCESSFULLY!</h2>\n";
        
        echo "<div style='display: flex; gap: 30px; margin: 20px 0;'>\n";
        echo "<div style='flex: 1;'>\n";
        echo "<h4>📊 Database Statistics:</h4>\n";
        echo "<ul>\n";
        echo "<li><strong>Total Candidates:</strong> {$stats['total']}</li>\n";
        echo "<li><strong>South African Candidates:</strong> {$stats['sa_candidates']}</li>\n";
        echo "<li><strong>Foreign Nationals:</strong> {$stats['foreign_candidates']}</li>\n";
        echo "<li><strong>Candidates with SA ID Numbers:</strong> {$stats['with_sa_id']}</li>\n";
        echo "<li><strong>Candidates with Passport Numbers:</strong> {$stats['with_passport']}</li>\n";
        echo "<li><strong>Candidates with Country Specified:</strong> {$stats['with_country']}</li>\n";
        echo "</ul>\n";
        echo "</div>\n";
        
        if (!empty($top_countries)) {
            echo "<div style='flex: 1;'>\n";
            echo "<h4>🌍 Top Countries:</h4>\n";
            echo "<ul>\n";
            foreach ($top_countries as $country_stat) {
                echo "<li><strong>{$country_stat['country']}:</strong> {$country_stat['count']} candidates</li>\n";
            }
            echo "</ul>\n";
            echo "</div>\n";
        }
        echo "</div>\n";
        
        echo "<h4>✅ Features Now Available:</h4>\n";
        echo "<div style='display: grid; grid-template-columns: 1fr 1fr; gap: 15px; margin: 15px 0;'>\n";
        echo "<div>\n";
        echo "<h5>🇿🇦 For South African Candidates:</h5>\n";
        echo "<ul>\n";
        echo "<li>13-digit SA ID number collection</li>\n";
        echo "<li>Format validation and duplicate prevention</li>\n";
        echo "<li>SA Citizen/PR badge display</li>\n";
        echo "</ul>\n";
        echo "</div>\n";
        echo "<div>\n";
        echo "<h5>🌍 For Foreign Nationals:</h5>\n";
        echo "<ul>\n";
        echo "<li>Passport number collection</li>\n";
        echo "<li>Country of origin selection</li>\n";
        echo "<li>Work permit information prompts</li>\n";
        echo "<li>Foreign National badge with country</li>\n";
        echo "</ul>\n";
        echo "</div>\n";
        echo "</div>\n";
        
        echo "<h4>🎯 Registration Features:</h4>\n";
        echo "<ul style='columns: 2; column-gap: 30px;'>\n";
        echo "<li>Smart field switching based on nationality</li>\n";
        echo "<li>Popular countries dropdown with full country list</li>\n";
        echo "<li>Real-time SA ID format validation</li>\n";
        echo "<li>Required field validation</li>\n";
        echo "<li>Data persistence on form errors</li>\n";
        echo "<li>Professional country grouping (Popular + All)</li>\n";
        echo "</ul>\n";
        
        echo "<h4>🔧 Test Your System:</h4>\n";
        echo "<p style='margin: 20px 0;'>\n";
        echo "<a href='auth/register.php?type=candidate' style='background: #28a745; color: white; padding: 12px 24px; text-decoration: none; border-radius: 5px; margin: 5px; display: inline-block; font-weight: bold;'>🆕 Test Registration</a>\n";
        echo "<a href='candidate/profile.php' style='background: #007bff; color: white; padding: 12px 24px; text-decoration: none; border-radius: 5px; margin: 5px; display: inline-block; font-weight: bold;'>👤 Edit Profile</a>\n";
        echo "<a href='candidate/dashboard.php' style='background: #6f42c1; color: white; padding: 12px 24px; text-decoration: none; border-radius: 5px; margin: 5px; display: inline-block; font-weight: bold;'>📊 View Dashboard</a>\n";
        echo "</p>\n";
        
        echo "<h4>📋 User Experience:</h4>\n";
        echo "<div style='background: #f8f9fa; padding: 15px; border-radius: 5px; margin: 15px 0;'>\n";
        echo "<div style='display: grid; grid-template-columns: 1fr 1fr; gap: 20px;'>\n";
        echo "<div>\n";
        echo "<p><strong>🇿🇦 SA Citizens Flow:</strong></p>\n";
        echo "<ol>\n";
        echo "<li>Select 'No - South African Citizen/Permanent Resident'</li>\n";
        echo "<li>Enter 13-digit SA ID number</li>\n";
        echo "<li>Real-time validation with visual feedback</li>\n";
        echo "<li>Profile shows green 'SA Citizen/PR' badge</li>\n";
        echo "</ol>\n";
        echo "</div>\n";
        echo "<div>\n";
        echo "<p><strong>🌍 Foreign Nationals Flow:</strong></p>\n";
        echo "<ol>\n";
        echo "<li>Select 'Yes - Foreign National'</li>\n";
        echo "<li>Enter passport number</li>\n";
        echo "<li>Select country from grouped dropdown</li>\n";
        echo "<li>Profile shows blue badge with country name</li>\n";
        echo "</ol>\n";
        echo "</div>\n";
        echo "</div>\n";
        echo "</div>\n";
        
        echo "</div>\n";
        
    } else {
        echo "<div style='color: red; padding: 15px; background: #f8d7da; border: 1px solid #f5c6cb; margin: 20px 0; border-radius: 5px;'>\n";
        echo "<h4>❌ Migration Incomplete!</h4>\n";
        echo "<p>The following columns are missing: " . implode(', ', $missing_columns) . "</p>\n";
        echo "<p>Please check your database permissions and try again.</p>\n";
        echo "</div>\n";
    }
    
} catch (Exception $e) {
    echo "<div style='color: red; padding: 15px; background: #f8d7da; border: 1px solid #f5c6cb; margin: 20px 0; border-radius: 5px;'>\n";
    echo "<h4>💥 Migration Error!</h4>\n";
    echo "<p><strong>Error:</strong> " . htmlspecialchars($e->getMessage()) . "</p>\n";
    echo "<p><strong>File:</strong> " . htmlspecialchars($e->getFile()) . "</p>\n";
    echo "<p><strong>Line:</strong> " . $e->getLine() . "</p>\n";
    echo "<p>Please check your database configuration and try again.</p>\n";
    echo "</div>\n";
}
?>

<style>
body { 
    font-family: -apple-system, BlinkMacSystemFont, 'Segoe UI', Roboto, sans-serif; 
    margin: 30px; 
    line-height: 1.6;
    color: #333;
    background-color: #f8f9fa;
}
h1 { color: #2c3e50; margin-bottom: 10px; text-align: center; }
h2 { color: #34495e; margin-top: 30px; }
h3 { color: #7f8c8d; margin-top: 25px; margin-bottom: 15px; border-bottom: 2px solid #ecf0f1; padding-bottom: 5px; }
h4 { color: #5d6d7e; margin-top: 20px; }
h5 { color: #85929e; }
p { margin: 12px 0; }
hr { margin: 25px 0; border: none; border-top: 2px solid #ecf0f1; }
ul, ol { padding-left: 25px; }
li { margin: 8px 0; }
a { text-decoration: none; transition: opacity 0.3s; }
a:hover { opacity: 0.8; }
.container { max-width: 1200px; margin: 0 auto; background: white; padding: 30px; border-radius: 10px; box-shadow: 0 4px 6px rgba(0,0,0,0.1); }
</style>