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

echo "<h2>Adding Country Field for Foreign Nationals</h2>\n";

try {
    $database = new Database();
    $db = $database->getConnection();
    
    echo "<p>✓ Database connection successful</p>\n";
    
    // Check if country column already exists
    $stmt = $db->query("SHOW COLUMNS FROM candidates LIKE 'country'");
    if ($stmt->fetch()) {
        echo "<div style='color: orange; padding: 10px; background: #fff3cd; border: 1px solid #ffeaa7; border-radius: 5px;'>\n";
        echo "⚠ country column already exists in candidates table.\n";
        echo "</div>\n";
    } else {
        echo "<p>Adding country column to candidates table...</p>\n";
        
        // Add the country column after sa_id_number
        $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";
        
        // Add index for better performance
        try {
            $db->exec("ALTER TABLE candidates ADD INDEX idx_country (country)");
            echo "<div style='color: green;'>✓ Added index for country</div>\n";
        } catch (PDOException $e) {
            echo "<div style='color: orange;'>⚠ Index might already exist</div>\n";
        }
    }
    
    // Verify the column was added
    echo "<h3>Verification:</h3>\n";
    $stmt = $db->query("DESCRIBE candidates");
    $columns = $stmt->fetchAll();
    
    $country_found = false;
    foreach ($columns as $column) {
        if ($column['Field'] === 'country') {
            $country_found = true;
            echo "<div style='color: green;'>✓ country column exists</div>\n";
            echo "<p><strong>Column details:</strong> {$column['Type']}, Null: {$column['Null']}</p>\n";
            break;
        }
    }
    
    if ($country_found) {
        // Test the candidates query
        echo "<h3>Testing Candidates Query:</h3>\n";
        try {
            $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 country IS NOT NULL AND country != '' THEN 1 ELSE 0 END) as with_country
                               FROM candidates");
            $results = $stmt->fetch();
            
            echo "<p><strong>Current candidate distribution:</strong></p>\n";
            echo "<ul>\n";
            echo "<li>Total Candidates: {$results['total']}</li>\n";
            echo "<li>South African Candidates: {$results['sa_candidates']}</li>\n";
            echo "<li>Foreign Nationals: {$results['foreign_candidates']}</li>\n";
            echo "<li>Candidates with Country specified: {$results['with_country']}</li>\n";
            echo "</ul>\n";
            
            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>The country field has been added successfully!</p>\n";
            echo "<p><strong>You can now:</strong></p>\n";
            echo "<ul>\n";
            echo "<li>Collect country information for foreign nationals during registration</li>\n";
            echo "<li>Display candidate's country of origin on profiles</li>\n";
            echo "<li>Filter and search candidates by country</li>\n";
            echo "<li>Generate nationality-based reports</li>\n";
            echo "</ul>\n";
            echo "<p><a href='auth/register.php?type=candidate' style='background: #28a745; color: white; padding: 10px 20px; text-decoration: none; border-radius: 4px; margin-right: 10px;'>Test Registration</a>\n";
            echo "<a href='candidate/profile.php' style='background: #007bff; color: white; padding: 10px 20px; text-decoration: none; border-radius: 4px;'>Test Profile Edit</a></p>\n";
            echo "</div>\n";
            
        } catch (PDOException $e) {
            echo "<div style='color: red;'>✗ Error testing query: " . $e->getMessage() . "</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>❌ Column still not found!</h4>\n";
        echo "<p>There may be a database permission issue. Please check your database configuration.</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>