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

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

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

// Get current auto increment value
$stmt = $db->query("SELECT AUTO_INCREMENT FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'recruitment_portal' AND TABLE_NAME = 'candidates'");
$auto_increment = $stmt->fetch()['AUTO_INCREMENT'];
echo "<p>Current AUTO_INCREMENT value: $auto_increment</p>\n";

// Get the problematic records
$stmt = $db->prepare("SELECT * FROM candidates WHERE id = 0 ORDER BY created_at ASC");
$stmt->execute();
$zero_id_records = $stmt->fetchAll();

echo "<h3>Records with ID=0:</h3>\n";
echo "<table border='1' cellpadding='5'>\n";
echo "<tr><th>Current ID</th><th>Name</th><th>Email</th><th>Created</th></tr>\n";
foreach ($zero_id_records as $record) {
    echo "<tr>";
    echo "<td>{$record['id']}</td>";
    echo "<td>{$record['first_name']} {$record['last_name']}</td>";
    echo "<td>{$record['email']}</td>";
    echo "<td>{$record['created_at']}</td>";
    echo "</tr>\n";
}
echo "</table>\n";

// Fix each record by giving it a proper ID
echo "<h3>Fixing Records:</h3>\n";

$new_id = $auto_increment;
foreach ($zero_id_records as $record) {
    echo "<h4>Fixing: {$record['first_name']} {$record['last_name']}</h4>\n";
    
    try {
        // Create a new record with proper ID
        $stmt = $db->prepare("
            INSERT INTO candidates (
                first_name, last_name, email, password, phone, date_of_birth, gender, address,
                candidate_type, company_id, employee_code, department, hr_approval_status,
                hr_approval_date, hr_approval_notes, approved_by, profile_picture,
                created_at, updated_at
            ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        ");
        
        $result = $stmt->execute([
            $record['first_name'],
            $record['last_name'],
            $record['email'],
            $record['password'],
            $record['phone'],
            $record['date_of_birth'],
            $record['gender'],
            $record['address'],
            $record['candidate_type'],
            $record['company_id'],
            $record['employee_code'],
            $record['department'],
            $record['hr_approval_status'],
            $record['hr_approval_date'],
            $record['hr_approval_notes'],
            $record['approved_by'],
            $record['profile_picture'],
            $record['created_at'],
            $record['updated_at']
        ]);
        
        if ($result) {
            $actual_new_id = $db->lastInsertId();
            echo "<p>✅ Created new record with ID: $actual_new_id</p>\n";
            
            // Delete the old record with ID=0
            $delete_stmt = $db->prepare("DELETE FROM candidates WHERE id = 0 AND email = ?");
            $delete_result = $delete_stmt->execute([$record['email']]);
            
            if ($delete_result) {
                echo "<p>✅ Deleted old record with ID=0</p>\n";
            } else {
                echo "<p>❌ Failed to delete old record</p>\n";
            }
            
            $new_id = $actual_new_id + 1;
        } else {
            echo "<p>❌ Failed to create new record</p>\n";
        }
        
    } catch (Exception $e) {
        echo "<p>❌ Error: " . $e->getMessage() . "</p>\n";
    }
    
    echo "<hr>\n";
}

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

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

if ($remaining_zeros == 0) {
    echo "<div style='background: #d4edda; color: #155724; padding: 15px; border-radius: 5px; margin: 10px 0;'>\n";
    echo "<h4>✅ SUCCESS! No more ID=0 records</h4>\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_zeros records with ID=0</h4>\n";
    echo "</div>\n";
}

// Show all Melissa and Brian records
echo "<h4>Final State - All Records:</h4>\n";
$stmt = $db->prepare("SELECT * FROM candidates WHERE email LIKE '%melissa%' OR email LIKE '%brian%' ORDER BY id");
$stmt->execute();
$final_records = $stmt->fetchAll();

echo "<table border='1' cellpadding='5'>\n";
echo "<tr><th>ID</th><th>Name</th><th>Email</th><th>Created</th></tr>\n";
foreach ($final_records as $record) {
    echo "<tr>";
    echo "<td>{$record['id']}</td>";
    echo "<td>{$record['first_name']} {$record['last_name']}</td>";
    echo "<td>{$record['email']}</td>";
    echo "<td>{$record['created_at']}</td>";
    echo "</tr>\n";
}
echo "</table>\n";

echo "<h3>🔑 Updated Login Instructions:</h3>\n";
echo "<div style='background: #f8f9fa; padding: 15px; border: 1px solid #dee2e6; border-radius: 5px;'>\n";
echo "<p>Users should now login normally. The profile data should display correctly.</p>\n";
echo "<p><strong>Melissa:</strong> melissa.makoma@petrosa.co.za</p>\n";
echo "<p><strong>Brian:</strong> brianvukile.peter@sa-npc.co.za</p>\n";
echo "<p>Both should now have unique IDs and display their own profile data.</p>\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>