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

echo "<h2>Adding South African ID Number Field to Candidates Table</h2>\n";

try {
    $database = new Database();
    $db = $database->getConnection();
    
    echo "<p>✓ Database connection successful</p>\n";
    
    // Check if sa_id_number column already exists
    $stmt = $db->query("SHOW COLUMNS FROM candidates LIKE 'sa_id_number'");
    if ($stmt->fetch()) {
        echo "<div style='color: orange; padding: 10px; background: #fff3cd; border: 1px solid #ffeaa7; border-radius: 5px;'>\n";
        echo "⚠ sa_id_number column already exists in candidates table.\n";
        echo "</div>\n";
    } else {
        echo "<p>Adding sa_id_number column to candidates table...</p>\n";
        
        // Add the sa_id_number column after passport_number
        $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";
        
        // Add unique index for SA ID number to prevent duplicates
        try {
            $db->exec("ALTER TABLE candidates ADD UNIQUE INDEX idx_sa_id_number (sa_id_number)");
            echo "<div style='color: green;'>✓ Added unique index for sa_id_number</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();
    
    $sa_id_found = false;
    foreach ($columns as $column) {
        if ($column['Field'] === 'sa_id_number') {
            $sa_id_found = true;
            echo "<div style='color: green;'>✓ sa_id_number column exists</div>\n";
            echo "<p><strong>Column details:</strong> {$column['Type']}, Null: {$column['Null']}, Key: {$column['Key']}</p>\n";
            break;
        }
    }
    
    if ($sa_id_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
                               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 (will need ID numbers): {$results['sa_candidates']}</li>\n";
            echo "<li>Foreign Nationals (use passport numbers): {$results['foreign_candidates']}</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 sa_id_number field has been added successfully!</p>\n";
            echo "<p><strong>You can now:</strong></p>\n";
            echo "<ul>\n";
            echo "<li>Collect SA ID numbers during registration</li>\n";
            echo "<li>Display ID numbers for South African candidates</li>\n";
            echo "<li>Validate SA ID number format (13 digits)</li>\n";
            echo "<li>Prevent duplicate ID numbers with unique constraint</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>