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

echo "<h1>Complete Nationality & ID Migration</h1>\n";
echo "<p>This script will set up all nationality-related fields for your recruitment system.</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: Update existing candidates with default values
    echo "<h3>Step 4: 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 5: Add indexes for performance
    echo "<h3>Step 5: Adding database indexes</h3>\n";
    
    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;'>⚠ is_foreign_national index might already exist</div>\n";
    }
    
    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 (prevents duplicates)</div>\n";
    } catch (PDOException $e) {
        echo "<div style='color: orange;'>⚠ sa_id_number index might already exist</div>\n";
    }
    
    // Step 6: Verification and Statistics
    echo "<h3>Step 6: 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'];
    $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 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
                           FROM candidates");
        $stats = $stmt->fetch();
        
        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 "<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 "</ul>\n";
        
        echo "<h4>✅ Features Now Available:</h4>\n";
        echo "<ul>\n";
        echo "<li><strong>Registration:</strong> Candidates can select nationality and enter ID/Passport</li>\n";
        echo "<li><strong>Profile Management:</strong> View and edit nationality information</li>\n";
        echo "<li><strong>Validation:</strong> 13-digit SA ID number format validation</li>\n";
        echo "<li><strong>Data Integrity:</strong> Unique SA ID numbers (no duplicates)</li>\n";
        echo "<li><strong>Visual Indicators:</strong> Nationality badges on profiles</li>\n";
        echo "</ul>\n";
        
        echo "<h4>🔧 Test Your System:</h4>\n";
        echo "<p>\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;'>🆕 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;'>👤 Test Profile Edit</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;'>📊 View Dashboard</a>\n";
        echo "</p>\n";
        
        echo "<h4>📋 How to Use:</h4>\n";
        echo "<div style='background: #f8f9fa; padding: 15px; border-radius: 5px; margin: 15px 0;'>\n";
        echo "<p><strong>For South African Candidates:</strong></p>\n";
        echo "<ul>\n";
        echo "<li>Select 'No - South African Citizen/Permanent Resident'</li>\n";
        echo "<li>Enter their 13-digit SA ID number</li>\n";
        echo "<li>System validates format automatically</li>\n";
        echo "</ul>\n";
        echo "<p><strong>For Foreign Nationals:</strong></p>\n";
        echo "<ul>\n";
        echo "<li>Select 'Yes - Foreign National'</li>\n";
        echo "<li>Enter their passport number</li>\n";
        echo "<li>Include work permit information if relevant</li>\n";
        echo "</ul>\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;
}
h1 { color: #2c3e50; margin-bottom: 10px; }
h2 { color: #34495e; margin-top: 30px; }
h3 { color: #7f8c8d; margin-top: 25px; margin-bottom: 15px; }
p { margin: 12px 0; }
hr { margin: 25px 0; border: none; border-top: 2px solid #ecf0f1; }
ul { padding-left: 25px; }
li { margin: 8px 0; }
a { text-decoration: none; }
a:hover { opacity: 0.9; }
.success { background: #d4edda; border: 1px solid #c3e6cb; color: #155724; padding: 15px; border-radius: 5px; margin: 15px 0; }
.warning { background: #fff3cd; border: 1px solid #ffeaa7; color: #856404; padding: 15px; border-radius: 5px; margin: 15px 0; }
.error { background: #f8d7da; border: 1px solid #f5c6cb; color: #721c24; padding: 15px; border-radius: 5px; margin: 15px 0; }
</style>