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

echo "<h2>Adding Foreign National Fields to Candidates Table</h2>\n";

try {
    $database = new Database();
    $db = $database->getConnection();
    
    echo "<p>✓ Database connection successful</p>\n";
    
    // Check if is_foreign_national column already exists
    $stmt = $db->query("SHOW COLUMNS FROM candidates LIKE 'is_foreign_national'");
    if ($stmt->fetch()) {
        echo "<div style='color: orange; padding: 10px; background: #fff3cd; border: 1px solid #ffeaa7; border-radius: 5px;'>\n";
        echo "⚠ is_foreign_national column already exists in candidates table.\n";
        echo "</div>\n";
    } else {
        echo "<p>Adding is_foreign_national column to candidates table...</p>\n";
        
        // Add the is_foreign_national column
        $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";
    }
    
    // Check if passport_number column already exists
    $stmt = $db->query("SHOW COLUMNS FROM candidates LIKE 'passport_number'");
    if ($stmt->fetch()) {
        echo "<div style='color: orange; padding: 10px; background: #fff3cd; border: 1px solid #ffeaa7; border-radius: 5px;'>\n";
        echo "⚠ passport_number column already exists in candidates table.\n";
        echo "</div>\n";
    } else {
        echo "<p>Adding passport_number column to candidates table...</p>\n";
        
        // Add the passport_number column
        $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";
    }
    
    // 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 columns were added
    echo "<h3>Verification:</h3>\n";
    $stmt = $db->query("DESCRIBE candidates");
    $columns = $stmt->fetchAll();
    
    $foreign_national_found = false;
    $passport_found = false;
    
    foreach ($columns as $column) {
        if ($column['Field'] === 'is_foreign_national') {
            $foreign_national_found = true;
            echo "<div style='color: green;'>✓ is_foreign_national column exists</div>\n";
            echo "<p><strong>Column details:</strong> {$column['Type']}, Default: {$column['Default']}</p>\n";
        }
        if ($column['Field'] === 'passport_number') {
            $passport_found = true;
            echo "<div style='color: green;'>✓ passport_number column exists</div>\n";
            echo "<p><strong>Column details:</strong> {$column['Type']}, Null: {$column['Null']}</p>\n";
        }
    }
    
    if ($foreign_national_found && $passport_found) {
        // Test the candidates query
        echo "<h3>Testing Candidates Query:</h3>\n";
        try {
            $stmt = $db->query("SELECT COUNT(*) as count, is_foreign_national FROM candidates GROUP BY is_foreign_national");
            $results = $stmt->fetchAll();
            
            echo "<p><strong>Current foreign national distribution:</strong></p>\n";
            echo "<ul>\n";
            foreach ($results as $result) {
                $status = $result['is_foreign_national'] ?: 'no';
                echo "<li>{$status}: {$result['count']} 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 foreign national fields have been added successfully!</p>\n";
            echo "<p><strong>You can now:</strong></p>\n";
            echo "<ul>\n";
            echo "<li>Register candidates with foreign national status</li>\n";
            echo "<li>Collect passport numbers for foreign nationals</li>\n";
            echo "<li>Filter candidates by nationality status</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/dashboard.php' style='background: #007bff; color: white; padding: 10px 20px; text-decoration: none; border-radius: 4px;'>View Candidate Dashboard</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>❌ Columns 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";
}
?>