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

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

echo "<h2>🔧 Fix Brian's Record with ID=0</h2>\n";

// Check current state
$stmt = $db->prepare("SELECT * FROM candidates WHERE email = 'brianvukile.peter@sa-npc.co.za'");
$stmt->execute();
$brian_records = $stmt->fetchAll();

echo "<p>Current Brian records: " . count($brian_records) . "</p>\n";

if (count($brian_records) > 0) {
    foreach ($brian_records as $record) {
        echo "<p>Record ID: {$record['id']}, Created: {$record['created_at']}</p>\n";
    }
}

// Delete the problematic record with ID=0
echo "<h3>Step 1: Delete record with ID=0</h3>\n";
$stmt = $db->prepare("DELETE FROM candidates WHERE email = 'brianvukile.peter@sa-npc.co.za' AND id = 0");
$result = $stmt->execute();

if ($result) {
    echo "<p>✅ Deleted records with ID=0 for Brian. Rows affected: " . $stmt->rowCount() . "</p>\n";
} else {
    echo "<p>❌ Failed to delete records with ID=0</p>\n";
}

// Recreate Brian's record properly
echo "<h3>Step 2: Create new proper record for Brian</h3>\n";

$hashed_password = password_hash('Brian123', PASSWORD_DEFAULT);

$stmt = $db->prepare("
    INSERT INTO candidates (
        first_name, last_name, email, password, 
        candidate_type, company_id, hr_approval_status, 
        employee_code, created_at, updated_at
    ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, NOW(), NOW())
");

$result = $stmt->execute([
    'Brian vukile',
    'Peter', 
    'brianvukile.peter@sa-npc.co.za',
    $hashed_password,
    'internal',
    8, // SANPC company ID
    'approved',
    'BVP001' // Generate an employee code
]);

if ($result) {
    $new_id = $db->lastInsertId();
    echo "<p>✅ Created new record for Brian with ID: $new_id</p>\n";
} else {
    echo "<p>❌ Failed to create new record for Brian</p>\n";
}

// Verify final state
echo "<h3>Step 3: Verification</h3>\n";

$stmt = $db->prepare("SELECT * FROM candidates WHERE email = 'brianvukile.peter@sa-npc.co.za'");
$stmt->execute();
$final_records = $stmt->fetchAll();

if (count($final_records) == 1) {
    $record = $final_records[0];
    echo "<div style='background: #d4edda; padding: 10px; margin: 10px 0; border-radius: 5px;'>\n";
    echo "<h4>✅ Brian's Record Fixed!</h4>\n";
    echo "<p><strong>ID:</strong> {$record['id']}</p>\n";
    echo "<p><strong>Name:</strong> {$record['first_name']} {$record['last_name']}</p>\n";
    echo "<p><strong>Email:</strong> {$record['email']}</p>\n";
    echo "<p><strong>Type:</strong> {$record['candidate_type']}</p>\n";
    echo "<p><strong>Status:</strong> {$record['hr_approval_status']}</p>\n";
    echo "<p><strong>Company ID:</strong> {$record['company_id']}</p>\n";
    echo "<p><strong>Employee Code:</strong> {$record['employee_code']}</p>\n";
    echo "</div>\n";
} else {
    echo "<p>❌ Still have " . count($final_records) . " records for Brian</p>\n";
}

?>

<style>
body { font-family: Arial, sans-serif; margin: 20px; line-height: 1.6; }
h1, h2, h3 { color: #333; }
</style>