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

$database = new Database();
$db = $database->getConnection();

echo "🌍 Adding Nationality Column for HR Visibility\n";
echo "============================================\n\n";

try {
    // Add nationality column to candidates table
    $alter_query = "ALTER TABLE candidates ADD COLUMN nationality VARCHAR(100) DEFAULT NULL AFTER gender";
    $db->exec($alter_query);
    echo "✅ Successfully added nationality column to candidates table\n";
} catch (PDOException $e) {
    if (strpos($e->getMessage(), 'Duplicate column name') !== false) {
        echo "ℹ️ Nationality column already exists\n";
    } else {
        echo "❌ Error adding nationality column: " . $e->getMessage() . "\n";
    }
}

// Verify the column was added
try {
    $desc_stmt = $db->query("DESCRIBE candidates");
    $columns = $desc_stmt->fetchAll(PDO::FETCH_COLUMN);
    
    if (in_array('nationality', $columns)) {
        echo "✅ Nationality column confirmed in candidates table\n";
        echo "📊 Column position: " . (array_search('nationality', $columns) + 1) . " of " . count($columns) . "\n";
    } else {
        echo "❌ Nationality column not found in candidates table\n";
        return;
    }
} catch (PDOException $e) {
    echo "❌ Error checking table structure: " . $e->getMessage() . "\n";
    return;
}

// Add some sample nationality data for testing
try {
    echo "\n🎯 Adding sample nationality data for HR testing...\n";
    
    // Get some existing candidates
    $stmt = $db->query("SELECT id, first_name, last_name FROM candidates LIMIT 5");
    $candidates = $stmt->fetchAll();
    
    $sample_nationalities = [
        'American',
        'Canadian', 
        'British',
        'German',
        'Japanese'
    ];
    
    $updated_count = 0;
    foreach ($candidates as $index => $candidate) {
        if (isset($sample_nationalities[$index])) {
            $nationality = $sample_nationalities[$index];
            $update_stmt = $db->prepare("UPDATE candidates SET nationality = ? WHERE id = ?");
            $update_stmt->execute([$nationality, $candidate['id']]);
            
            echo "  ✓ Updated {$candidate['first_name']} {$candidate['last_name']} → {$nationality}\n";
            $updated_count++;
        }
    }
    
    echo "\n✅ Updated {$updated_count} candidates with sample nationality data\n";
    
} catch (PDOException $e) {
    echo "❌ Error adding sample data: " . $e->getMessage() . "\n";
}

echo "\n🎉 Nationality Column Setup Complete!\n";
echo "=====================================\n";
echo "HR teams will now see nationality information in:\n";
echo "• Candidate profile CVs\n";
echo "• Bulk download documents\n";
echo "• Personal Details section of generated CVs\n";
echo "\n📝 Next Steps:\n";
echo "1. Test bulk download functionality\n";
echo "2. Verify nationality displays in CVs\n";
echo "3. Update candidate registration to include nationality field\n";
?>