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

echo "<h2>Fixing Existing Candidates for Foreign National Fields</h2>\n";

try {
    $database = new Database();
    $db = $database->getConnection();
    
    echo "<p>✓ Database connection successful</p>\n";
    
    // First, run the migration to add columns if they don't exist
    echo "<h3>Step 1: Adding missing columns</h3>\n";
    
    // Check if is_foreign_national column exists
    $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";
    }
    
    // Check if passport_number column exists
    $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 2: Update existing candidates
    echo "<h3>Step 2: Updating existing candidates</h3>\n";
    
    // Set default values for existing candidates where is_foreign_national is NULL
    $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";
    
    // Add index for better performance
    try {
        $db->exec("ALTER TABLE candidates ADD INDEX idx_is_foreign_national (is_foreign_national)");
        echo "<div style='color: green;'>✓ Added index for is_foreign_national</div>\n";
    } catch (PDOException $e) {
        echo "<div style='color: orange;'>⚠ Index might already exist</div>\n";
    }
    
    // Verify the fix
    echo "<h3>Step 3: Verification</h3>\n";
    $stmt = $db->query("SELECT COUNT(*) as total_candidates, 
                              SUM(CASE WHEN is_foreign_national = 'yes' THEN 1 ELSE 0 END) as foreign_nationals,
                              SUM(CASE WHEN is_foreign_national = 'no' THEN 1 ELSE 0 END) as local_candidates
                       FROM candidates");
    $stats = $stmt->fetch();
    
    echo "<div style='color: green; padding: 15px; background: #d4edda; border: 1px solid #c3e6cb; margin: 20px 0; border-radius: 5px;'>\n";
    echo "<h4>🎉 SUCCESS!</h4>\n";
    echo "<p><strong>Candidate Statistics:</strong></p>\n";
    echo "<ul>\n";
    echo "<li>Total Candidates: {$stats['total_candidates']}</li>\n";
    echo "<li>Foreign Nationals: {$stats['foreign_nationals']}</li>\n";
    echo "<li>Local Candidates: {$stats['local_candidates']}</li>\n";
    echo "</ul>\n";
    echo "<p><strong>All existing candidates have been updated with default nationality status!</strong></p>\n";
    echo "<p><a href='candidate/profile.php' style='background: #28a745; color: white; padding: 10px 20px; text-decoration: none; border-radius: 4px; margin-right: 10px;'>Test Profile Page</a>\n";
    echo "<a href='auth/register.php?type=candidate' style='background: #007bff; color: white; padding: 10px 20px; text-decoration: none; border-radius: 4px;'>Test Registration</a></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 "<strong>Error:</strong> " . $e->getMessage() . "<br>\n";
    echo "<strong>File:</strong> " . $e->getFile() . "<br>\n";
    echo "<strong>Line:</strong> " . $e->getLine() . "\n";
    echo "</div>\n";
}
?>

<style>
body { font-family: Arial, sans-serif; margin: 20px; }
h2 { color: #333; }
h3 { color: #666; margin-top: 25px; }
p { margin: 10px 0; }
</style>