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

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

echo "<h2>🔍 Debug Melissa Session Issue</h2>\n";

// Find Melissa's account
echo "<h3>1. Finding Melissa's Account</h3>\n";
$stmt = $db->prepare("SELECT * FROM candidates WHERE LOWER(first_name) LIKE '%melissa%' OR LOWER(email) LIKE '%melissa%'");
$stmt->execute();
$melissa_accounts = $stmt->fetchAll();

if ($melissa_accounts) {
    echo "<table border='1' cellpadding='5'>\n";
    echo "<tr><th>ID</th><th>Name</th><th>Email</th><th>Type</th><th>Status</th><th>Company</th></tr>\n";
    foreach ($melissa_accounts as $account) {
        echo "<tr>";
        echo "<td>{$account['id']}</td>";
        echo "<td>{$account['first_name']} {$account['last_name']}</td>";
        echo "<td>{$account['email']}</td>";
        echo "<td>{$account['candidate_type']}</td>";
        echo "<td>{$account['hr_approval_status']}</td>";
        echo "<td>{$account['company_id']}</td>";
        echo "</tr>\n";
    }
    echo "</table>\n";
} else {
    echo "<p>❌ No Melissa accounts found</p>\n";
}

// Find Brian's account
echo "<h3>2. Finding Brian's Account</h3>\n";
$stmt = $db->prepare("SELECT * FROM candidates WHERE LOWER(first_name) LIKE '%brian%' OR LOWER(email) LIKE '%brian%'");
$stmt->execute();
$brian_accounts = $stmt->fetchAll();

if ($brian_accounts) {
    echo "<table border='1' cellpadding='5'>\n";
    echo "<tr><th>ID</th><th>Name</th><th>Email</th><th>Type</th><th>Status</th><th>Company</th></tr>\n";
    foreach ($brian_accounts as $account) {
        echo "<tr>";
        echo "<td>{$account['id']}</td>";
        echo "<td>{$account['first_name']} {$account['last_name']}</td>";
        echo "<td>{$account['email']}</td>";
        echo "<td>{$account['candidate_type']}</td>";
        echo "<td>{$account['hr_approval_status']}</td>";
        echo "<td>{$account['company_id']}</td>";
        echo "</tr>\n";
    }
    echo "</table>\n";
} else {
    echo "<p>❌ No Brian accounts found</p>\n";
}

// Check for ID conflicts
echo "<h3>3. Checking for ID Conflicts</h3>\n";
$stmt = $db->prepare("SELECT id, COUNT(*) as count FROM candidates GROUP BY id HAVING count > 1 OR id = 0");
$stmt->execute();
$id_conflicts = $stmt->fetchAll();

if ($id_conflicts) {
    echo "<p>⚠️ Found ID conflicts:</p>\n";
    echo "<table border='1' cellpadding='5'>\n";
    echo "<tr><th>ID</th><th>Count</th></tr>\n";
    foreach ($id_conflicts as $conflict) {
        echo "<tr><td>{$conflict['id']}</td><td>{$conflict['count']}</td></tr>\n";
    }
    echo "</table>\n";
    
    // Show all records with problematic IDs
    foreach ($id_conflicts as $conflict) {
        $id = $conflict['id'];
        echo "<h4>All records with ID = $id:</h4>\n";
        $stmt = $db->prepare("SELECT * FROM candidates WHERE id = ?");
        $stmt->execute([$id]);
        $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 ($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";
    }
} else {
    echo "<p>✅ No ID conflicts found</p>\n";
}

// Test login simulation for Melissa
echo "<h3>4. Testing Login Simulation</h3>\n";

if ($melissa_accounts) {
    $melissa = $melissa_accounts[0];
    echo "<h4>Melissa's Login Test:</h4>\n";
    echo "<p><strong>Email:</strong> {$melissa['email']}</p>\n";
    
    // Simulate the exact login query from auth/login.php
    $stmt = $db->prepare("SELECT * FROM candidates WHERE email = ?");
    $stmt->execute([$melissa['email']]);
    $login_result = $stmt->fetch();
    
    if ($login_result) {
        echo "<div style='background: #f8f9fa; padding: 15px; border-radius: 5px; margin: 10px 0;'>\n";
        echo "<h5>Login Query Result:</h5>\n";
        echo "<p><strong>ID:</strong> {$login_result['id']}</p>\n";
        echo "<p><strong>Name:</strong> {$login_result['first_name']} {$login_result['last_name']}</p>\n";
        echo "<p><strong>Email:</strong> {$login_result['email']}</p>\n";
        echo "<p><strong>Type:</strong> {$login_result['candidate_type']}</p>\n";
        echo "<p><strong>Status:</strong> {$login_result['hr_approval_status']}</p>\n";
        echo "</div>\n";
        
        // This is what would be stored in session
        echo "<h5>Session Data That Would Be Set:</h5>\n";
        echo "<ul>\n";
        echo "<li>user_id: {$login_result['id']}</li>\n";
        echo "<li>first_name: {$login_result['first_name']}</li>\n";
        echo "<li>last_name: {$login_result['last_name']}</li>\n";
        echo "<li>user_email: {$login_result['email']}</li>\n";
        echo "<li>candidate_type: {$login_result['candidate_type']}</li>\n";
        echo "</ul>\n";
        
    } else {
        echo "<p>❌ Login simulation failed - user not found</p>\n";
    }
}

// Check current database state
echo "<h3>5. Current Database State</h3>\n";
$stmt = $db->prepare("SELECT * FROM candidates WHERE id IN (0, 13) OR email LIKE '%melissa%' OR email LIKE '%brian%' ORDER BY id, email");
$stmt->execute();
$all_relevant = $stmt->fetchAll();

echo "<table border='1' cellpadding='5'>\n";
echo "<tr><th>ID</th><th>Name</th><th>Email</th><th>Created</th><th>Updated</th></tr>\n";
foreach ($all_relevant as $record) {
    $highlight = ($record['id'] == 0) ? 'style="background-color: #ffebee;"' : '';
    echo "<tr $highlight>";
    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 "<td>{$record['updated_at']}</td>";
    echo "</tr>\n";
}
echo "</table>\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, h5 { color: #333; }
</style>