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

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

echo "<h2>🔧 Simple Fix for ID=0 Conflicts</h2>\n";

// Check table structure first
echo "<h3>1. Checking Table Structure</h3>\n";
$stmt = $db->query("DESCRIBE candidates");
$columns = $stmt->fetchAll();
$column_names = array_column($columns, 'Field');

echo "<p>Available columns: " . implode(', ', $column_names) . "</p>\n";

// Get the records with ID=0
$stmt = $db->prepare("SELECT * FROM candidates WHERE id = 0");
$stmt->execute();
$records = $stmt->fetchAll();

echo "<h3>2. Current Records with ID=0</h3>\n";
foreach ($records as $record) {
    echo "<p><strong>{$record['first_name']} {$record['last_name']}</strong> ({$record['email']})</p>\n";
}

// Fix by updating one record at a time using email as unique identifier
echo "<h3>3. Fixing Records</h3>\n";

try {
    // First, let's find the highest existing ID
    $stmt = $db->query("SELECT MAX(id) as max_id FROM candidates WHERE id > 0");
    $max_id = $stmt->fetch()['max_id'];
    $next_id = $max_id ? $max_id + 1 : 14; // Start from 14 if no valid IDs found
    
    echo "<p>Starting from ID: $next_id</p>\n";
    
    // Fix Brian first (older record)
    $brian_email = 'brianvukile.peter@sa-npc.co.za';
    $stmt = $db->prepare("UPDATE candidates SET id = ? WHERE email = ? AND id = 0");
    $result = $stmt->execute([$next_id, $brian_email]);
    
    if ($result && $stmt->rowCount() > 0) {
        echo "<p>✅ Fixed Brian's ID to: $next_id</p>\n";
        $next_id++;
    } else {
        echo "<p>❌ Failed to fix Brian's ID</p>\n";
    }
    
    // Fix Melissa
    $melissa_email = 'melissa.makoma@petrosa.co.za';
    $stmt = $db->prepare("UPDATE candidates SET id = ? WHERE email = ? AND id = 0");
    $result = $stmt->execute([$next_id, $melissa_email]);
    
    if ($result && $stmt->rowCount() > 0) {
        echo "<p>✅ Fixed Melissa's ID to: $next_id</p>\n";
    } else {
        echo "<p>❌ Failed to fix Melissa's ID</p>\n";
    }
    
    // Update AUTO_INCREMENT to prevent future conflicts
    $auto_increment_value = $next_id + 1;
    $db->exec("ALTER TABLE candidates AUTO_INCREMENT = $auto_increment_value");
    echo "<p>✅ Updated AUTO_INCREMENT to: $auto_increment_value</p>\n";
    
} catch (Exception $e) {
    echo "<p>❌ Error: " . $e->getMessage() . "</p>\n";
}

// Verify the fix
echo "<h3>4. Verification</h3>\n";

$stmt = $db->prepare("SELECT id, first_name, last_name, email FROM candidates WHERE email IN (?, ?)");
$stmt->execute(['melissa.makoma@petrosa.co.za', 'brianvukile.peter@sa-npc.co.za']);
$fixed_records = $stmt->fetchAll();

echo "<table border='1' cellpadding='5'>\n";
echo "<tr><th>ID</th><th>Name</th><th>Email</th></tr>\n";
foreach ($fixed_records as $record) {
    $color = ($record['id'] == 0) ? 'style="background-color: #ffebee;"' : 'style="background-color: #e8f5e8;"';
    echo "<tr $color>";
    echo "<td>{$record['id']}</td>";
    echo "<td>{$record['first_name']} {$record['last_name']}</td>";
    echo "<td>{$record['email']}</td>";
    echo "</tr>\n";
}
echo "</table>\n";

// Check for remaining ID=0 records
$stmt = $db->prepare("SELECT COUNT(*) as count FROM candidates WHERE id = 0");
$stmt->execute();
$remaining = $stmt->fetch()['count'];

if ($remaining == 0) {
    echo "<div style='background: #d4edda; color: #155724; padding: 15px; border-radius: 5px; margin: 10px 0;'>\n";
    echo "<h4>🎉 SUCCESS! Profile data should now display correctly!</h4>\n";
    echo "<p>Both Melissa and Brian now have unique IDs. When they login:</p>\n";
    echo "<ul>\n";
    echo "<li>Menu will show their correct name</li>\n";
    echo "<li>Profile will show their own data</li>\n";
    echo "<li>No more data mixing between accounts</li>\n";
    echo "</ul>\n";
    echo "</div>\n";
} else {
    echo "<div style='background: #f8d7da; color: #721c24; padding: 15px; border-radius: 5px; margin: 10px 0;'>\n";
    echo "<h4>❌ Still have $remaining records with ID=0</h4>\n";
    echo "</div>\n";
}

?>

<style>
body { font-family: Arial, sans-serif; margin: 20px; line-height: 1.6; }
table { width: 100%; margin: 20px 0; border-collapse: collapse; }
th, td { padding: 8px; text-align: left; border: 1px solid #ddd; }
th { background-color: #f2f2f2; }
h1, h2, h3, h4 { color: #333; }
</style>