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

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

echo "🆔 Adding ID Number Column for Document Management\n";
echo "================================================\n\n";

try {
    // Add id_number column to candidates table
    $alter_query = "ALTER TABLE candidates ADD COLUMN id_number VARCHAR(50) DEFAULT NULL AFTER nationality";
    $db->exec($alter_query);
    echo "✅ Successfully added id_number column to candidates table\n";
} catch (PDOException $e) {
    if (strpos($e->getMessage(), 'Duplicate column name') !== false) {
        echo "ℹ️ ID number column already exists\n";
    } else {
        echo "❌ Error adding id_number 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('id_number', $columns)) {
        echo "✅ ID number column confirmed in candidates table\n";
        echo "📊 Column position: " . (array_search('id_number', $columns) + 1) . " of " . count($columns) . "\n";
    } else {
        echo "❌ ID number column not found in candidates table\n";
        return;
    }
} catch (PDOException $e) {
    echo "❌ Error checking table structure: " . $e->getMessage() . "\n";
    return;
}

// Now add sample SA ID numbers and passport numbers
try {
    echo "\n🇿🇦 Adding South African candidates with SA ID numbers...\n";
    
    // Update some candidates to be South African with proper SA ID numbers
    $sa_updates = [
        ['first_name' => 'masande', 'nationality' => 'South African', 'id_number' => '8506291234567'],
        ['first_name' => 'John', 'nationality' => 'British', 'passport_number' => 'GB123456789'],
        ['first_name' => 'Sarah', 'nationality' => 'Canadian', 'passport_number' => 'CA987654321'],
        ['first_name' => 'Michael', 'nationality' => 'South African', 'id_number' => '7803151234568'],
        ['first_name' => 'Emma', 'nationality' => 'Australian', 'passport_number' => 'AU456789123']
    ];
    
    foreach ($sa_updates as $update) {
        // Find candidate by first name
        $stmt = $db->prepare("SELECT id, first_name, last_name FROM candidates WHERE LOWER(first_name) = LOWER(?) LIMIT 1");
        $stmt->execute([$update['first_name']]);
        $candidate = $stmt->fetch();
        
        if ($candidate) {
            $update_sql = "UPDATE candidates SET nationality = ?";
            $params = [$update['nationality']];
            
            if (isset($update['id_number'])) {
                $update_sql .= ", id_number = ?";
                $params[] = $update['id_number'];
            }
            
            if (isset($update['passport_number'])) {
                $update_sql .= ", passport_number = ?";
                $params[] = $update['passport_number'];
            }
            
            $update_sql .= " WHERE id = ?";
            $params[] = $candidate['id'];
            
            $update_stmt = $db->prepare($update_sql);
            $update_stmt->execute($params);
            
            $full_name = $candidate['first_name'] . ' ' . $candidate['last_name'];
            echo "  ✓ Updated {$full_name} → {$update['nationality']}\n";
            
            if (isset($update['id_number'])) {
                echo "    📋 SA ID: {$update['id_number']}\n";
            }
            if (isset($update['passport_number'])) {
                echo "    🛂 Passport: {$update['passport_number']}\n";
            }
        } else {
            echo "  ⚠️ Candidate with first name '{$update['first_name']}' not found\n";
        }
    }
    
    echo "\n📊 Current Document Setup:\n";
    echo "=========================\n";
    
    // Show candidates with their document types
    $stmt = $db->query("
        SELECT first_name, last_name, nationality, id_number, passport_number 
        FROM candidates 
        WHERE nationality IS NOT NULL 
        ORDER BY nationality, first_name
    ");
    $candidates = $stmt->fetchAll();
    
    foreach ($candidates as $candidate) {
        $name = $candidate['first_name'] . ' ' . $candidate['last_name'];
        $nationality = $candidate['nationality'];
        $id_num = $candidate['id_number'];
        $passport = $candidate['passport_number'];
        
        $flag = (strpos(strtolower($nationality), 'south african') !== false) ? '🇿🇦' : '🌍';
        
        echo "{$flag} {$name} ({$nationality})\n";
        if ($id_num) echo "    📋 ID: {$id_num}\n";
        if ($passport) echo "    🛂 Passport: {$passport}\n";
    }
    
} catch (PDOException $e) {
    echo "❌ Error updating candidates: " . $e->getMessage() . "\n";
}

echo "\n🎉 Document Column Setup Complete!\n";
echo "==================================\n";
echo "HR teams will now see:\n";
echo "• 🇿🇦 South African ID for SA candidates\n";
echo "• 🛂 Passport Number for foreign nationals\n";
echo "• Smart document priority in CV downloads\n";
?>