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

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

echo "<h2>🐛 Debug Brian's Live Issue - Why Can He See SANPC External Jobs?</h2>\n";

// Get Brian's actual data
$stmt = $db->prepare("SELECT * FROM candidates WHERE email = 'brianvukile.peter@sa-npc.co.za'");
$stmt->execute();
$brian = $stmt->fetch();

if (!$brian) {
    echo "<p>❌ Brian not found!</p>\n";
    exit;
}

echo "<h3>1. Brian's Account Details</h3>\n";
echo "<p><strong>ID:</strong> {$brian['id']}</p>\n";
echo "<p><strong>Name:</strong> {$brian['first_name']} {$brian['last_name']}</p>\n";
echo "<p><strong>Email:</strong> {$brian['email']}</p>\n";
echo "<p><strong>Type:</strong> {$brian['candidate_type']}</p>\n";
echo "<p><strong>Company ID:</strong> {$brian['company_id']}</p>\n";

// Check what SANPC external-only jobs exist
echo "<h3>2. SANPC External-Only Jobs (Brian should NOT see these)</h3>\n";
$stmt = $db->prepare("
    SELECT j.id, j.title, j.job_visibility, j.company_id, c.name as company_name, j.status, j.deadline
    FROM jobs j 
    LEFT JOIN companies c ON j.company_id = c.id 
    WHERE j.company_id = 8 AND j.job_visibility = 'external'
    ORDER BY j.title
");
$stmt->execute();
$sanpc_external_jobs = $stmt->fetchAll();

if ($sanpc_external_jobs) {
    echo "<table border='1' cellpadding='5'>\n";
    echo "<tr><th>ID</th><th>Title</th><th>Status</th><th>Deadline</th><th>Active?</th></tr>\n";
    foreach ($sanpc_external_jobs as $job) {
        $is_active = ($job['status'] === 'active' && (!$job['deadline'] || $job['deadline'] >= date('Y-m-d')));
        $color = $is_active ? 'style="background-color: #ffebee;"' : 'style="background-color: #f5f5f5;"';
        
        echo "<tr $color>";
        echo "<td>{$job['id']}</td>";
        echo "<td>" . substr($job['title'], 0, 50) . "...</td>";
        echo "<td>{$job['status']}</td>";
        echo "<td>" . ($job['deadline'] ?: 'No deadline') . "</td>";
        echo "<td>" . ($is_active ? '❌ YES - Should be hidden' : '✅ NO - Inactive') . "</td>";
        echo "</tr>\n";
    }
    echo "</table>\n";
} else {
    echo "<p>No external-only jobs found for SANPC</p>\n";
}

// Test the jobs.php filtering logic with Brian's actual data
echo "<h3>3. Testing jobs.php Filtering Logic with Brian's Data</h3>\n";

// Simulate the exact logic from jobs.php
$where_conditions = ["j.status = 'active'", "j.status != 'closed'", "c.status = 'approved'"];
$where_conditions[] = "(j.deadline IS NULL OR j.deadline >= CURDATE())";
$params = [];

// Check if Brian is identified as internal candidate
if ($brian && $brian['candidate_type'] === 'internal') {
    echo "<p>✅ Brian identified as internal candidate</p>\n";
    echo "<p>✅ Brian's company ID: {$brian['company_id']}</p>\n";
    
    $where_conditions[] = "(
        (j.job_visibility IN ('internal', 'both') AND j.company_id = ?) OR 
        (j.job_visibility IN ('external', 'both') AND j.company_id != ?)
    )";
    $params[] = $brian['company_id'];
    $params[] = $brian['company_id'];
    
    echo "<p>✅ Internal candidate filtering applied</p>\n";
} else {
    echo "<p>❌ Brian NOT identified as internal candidate - this is the problem!</p>\n";
    $where_conditions[] = "j.job_visibility IN ('external', 'both')";
    echo "<p>❌ External candidate filtering applied instead</p>\n";
}

$where_clause = "WHERE " . implode(" AND ", $where_conditions);

echo "<h4>Generated SQL WHERE Clause:</h4>\n";
echo "<pre>" . htmlspecialchars($where_clause) . "</pre>\n";
echo "<p><strong>Parameters:</strong> [" . implode(', ', $params) . "]</p>\n";

// Execute the query to see what Brian would see
$query = "
    SELECT j.id, j.title, j.job_visibility, j.company_id, c.name as company_name,
           CASE 
               WHEN j.company_id = 8 AND j.job_visibility = 'external' THEN '❌ PROBLEM - Should be hidden'
               WHEN j.company_id = 8 AND j.job_visibility IN ('internal', 'both') THEN '✅ OK - Own company internal/both'
               WHEN j.company_id != 8 THEN '✅ OK - Other company job'
               ELSE 'OTHER'
           END as should_see
    FROM jobs j 
    JOIN companies c ON j.company_id = c.id 
    $where_clause
    ORDER BY j.company_id, j.job_visibility
";

$stmt = $db->prepare($query);
$stmt->execute($params);
$brian_visible_jobs = $stmt->fetchAll();

echo "<h4>Jobs Brian Can Currently See (" . count($brian_visible_jobs) . " jobs):</h4>\n";
echo "<table border='1' cellpadding='5'>\n";
echo "<tr><th>ID</th><th>Title</th><th>Visibility</th><th>Company</th><th>Analysis</th></tr>\n";

$problems_found = [];

foreach ($brian_visible_jobs as $job) {
    $color = '';
    if (strpos($job['should_see'], 'PROBLEM') !== false) {
        $color = 'style="background-color: #ffebee; color: #d32f2f;"';
        $problems_found[] = $job;
    } elseif (strpos($job['should_see'], 'OK') !== false) {
        $color = 'style="background-color: #e8f5e8;"';
    }
    
    echo "<tr $color>";
    echo "<td>{$job['id']}</td>";
    echo "<td>" . substr($job['title'], 0, 40) . "...</td>";
    echo "<td>{$job['job_visibility']}</td>";
    echo "<td>{$job['company_name']}</td>";
    echo "<td style='font-size: 12px;'>{$job['should_see']}</td>";
    echo "</tr>\n";
}
echo "</table>\n";

// Analysis
if (!empty($problems_found)) {
    echo "<div style='background: #f8d7da; color: #721c24; padding: 15px; border-radius: 5px; margin: 10px 0;'>\n";
    echo "<h4>🚨 PROBLEMS FOUND!</h4>\n";
    echo "<p>Brian can see " . count($problems_found) . " external-only jobs from SANPC that he shouldn't see:</p>\n";
    foreach ($problems_found as $job) {
        echo "<p>• <strong>{$job['title']}</strong> (ID: {$job['id']})</p>\n";
    }
    echo "</div>\n";
} else {
    echo "<div style='background: #d4edda; color: #155724; padding: 15px; border-radius: 5px; margin: 10px 0;'>\n";
    echo "<h4>✅ NO PROBLEMS FOUND</h4>\n";
    echo "<p>Brian's filtering appears to be working correctly in this test.</p>\n";
    echo "</div>\n";
}

// Check if there are any other factors
echo "<h3>4. Additional Debugging</h3>\n";

// Check if the issue is with session vs database discrepancy
echo "<h4>Possible Issues:</h4>\n";
echo "<ul>\n";
echo "<li><strong>Session Issue:</strong> Maybe Brian's session has wrong user_id or company_id</li>\n";
echo "<li><strong>Database Issue:</strong> Maybe Brian's candidate_type or company_id is wrong</li>\n";
echo "<li><strong>Logic Issue:</strong> Maybe the filtering logic isn't being applied correctly</li>\n";
echo "<li><strong>Caching Issue:</strong> Maybe old query results are cached</li>\n";
echo "</ul>\n";

// Show what would happen with different scenarios
echo "<h4>Scenario Testing:</h4>\n";

echo "<p><strong>Scenario 1:</strong> If Brian's candidate_type was 'external':</p>\n";
$external_query = "
    SELECT COUNT(*) as count
    FROM jobs j 
    JOIN companies c ON j.company_id = c.id 
    WHERE j.status = 'active' AND c.status = 'approved' 
    AND (j.deadline IS NULL OR j.deadline >= CURDATE())
    AND j.job_visibility IN ('external', 'both')
";
$stmt = $db->query($external_query);
$external_count = $stmt->fetch()['count'];
echo "<p>→ He would see $external_count jobs (including SANPC external jobs)</p>\n";

echo "<p><strong>Scenario 2:</strong> If Brian's company_id was wrong (not 8):</p>\n";
echo "<p>→ He would see SANPC external jobs as 'other company' jobs</p>\n";

echo "<p><strong>Scenario 3:</strong> If the filtering logic isn't applied:</p>\n";
$all_active_query = "
    SELECT COUNT(*) as count
    FROM jobs j 
    JOIN companies c ON j.company_id = c.id 
    WHERE j.status = 'active' AND c.status = 'approved' 
    AND (j.deadline IS NULL OR j.deadline >= CURDATE())
";
$stmt = $db->query($all_active_query);
$all_count = $stmt->fetch()['count'];
echo "<p>→ He would see $all_count jobs (all active jobs)</p>\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; }
pre { background: #f5f5f5; padding: 10px; border-radius: 4px; }
</style>