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

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

echo "<h2>🔍 Direct Visibility Test</h2>\n";

// Test with actual candidate data
$candidate_id = 13; // Sisanda
$stmt = $db->prepare("SELECT candidate_type, company_id FROM candidates WHERE id = ?");
$stmt->execute([$candidate_id]);
$candidate_data = $stmt->fetch();

echo "<p><strong>Testing with candidate ID:</strong> $candidate_id</p>\n";
echo "<p><strong>Candidate type:</strong> {$candidate_data['candidate_type']}</p>\n";
echo "<p><strong>Company ID:</strong> {$candidate_data['company_id']}</p>\n";

// Reproduce 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 = [];

if ($candidate_data && $candidate_data['candidate_type'] === 'internal') {
    $where_conditions[] = "(
        (j.job_visibility IN ('internal', 'both') AND j.company_id = ?) OR 
        (j.job_visibility IN ('external', 'both') AND j.company_id != ?)
    )";
    $params[] = $candidate_data['company_id'];
    $params[] = $candidate_data['company_id'];
}

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

echo "<h3>Exact Query from jobs.php:</h3>\n";
echo "<pre>" . htmlspecialchars($where_clause) . "</pre>\n";
echo "<p><strong>Parameters:</strong> [" . implode(', ', $params) . "]</p>\n";

// Execute the query
$query = "
    SELECT j.id, j.title, j.job_visibility, j.company_id, c.name as company_name
    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);
$results = $stmt->fetchAll();

echo "<h3>Results (" . count($results) . " jobs):</h3>\n";
echo "<table border='1' cellpadding='5'>\n";
echo "<tr><th>ID</th><th>Title</th><th>Visibility</th><th>Company ID</th><th>Company Name</th><th>Should Show?</th></tr>\n";

foreach ($results as $job) {
    $should_show = true;
    $reason = '';
    
    if ($job['company_id'] == 8 && $job['job_visibility'] == 'external') {
        $should_show = false;
        $reason = 'External-only from own company';
        $color = 'style="background-color: #ffebee;"';
    } else if ($job['company_id'] == 8 && in_array($job['job_visibility'], ['internal', 'both'])) {
        $reason = 'Internal/Both from own company';
        $color = 'style="background-color: #e3f2fd;"';
    } else {
        $reason = 'From other company';
        $color = 'style="background-color: #f3e5f5;"';
    }
    
    echo "<tr $color>";
    echo "<td>{$job['id']}</td>";
    echo "<td>" . substr($job['title'], 0, 30) . "...</td>";
    echo "<td>{$job['job_visibility']}</td>";
    echo "<td>{$job['company_id']}</td>";
    echo "<td>{$job['company_name']}</td>";
    echo "<td>" . ($should_show ? '✅ YES' : '❌ NO') . " - $reason</td>";
    echo "</tr>\n";
}
echo "</table>\n";

// Check if any external-only jobs from company 8 are showing
$external_own = array_filter($results, function($job) {
    return $job['company_id'] == 8 && $job['job_visibility'] == 'external';
});

if (!empty($external_own)) {
    echo "<div style='background: #f8d7da; color: #721c24; padding: 15px; border-radius: 5px; margin: 10px 0;'>\n";
    echo "<h4>❌ ISSUE FOUND!</h4>\n";
    echo "<p>The following external-only jobs from the candidate's own company (SANPC, ID: 8) are incorrectly showing:</p>\n";
    foreach ($external_own as $job) {
        echo "<p>• {$job['title']} (ID: {$job['id']})</p>\n";
    }
    echo "<p>These should be filtered out!</p>\n";
    echo "</div>\n";
} else {
    echo "<div style='background: #d4edda; color: #155724; padding: 15px; border-radius: 5px; margin: 10px 0;'>\n";
    echo "<h4>✅ FILTERING WORKING CORRECTLY!</h4>\n";
    echo "<p>No external-only jobs from the candidate's own company are showing.</p>\n";
    echo "</div>\n";
}

// Let's also test the logic step by step
echo "<h3>Step-by-Step Logic Test:</h3>\n";
echo "<p>Company ID: {$candidate_data['company_id']}</p>\n";
echo "<p>Condition 1: <code>(j.job_visibility IN ('internal', 'both') AND j.company_id = {$candidate_data['company_id']})</code></p>\n";
echo "<p>Condition 2: <code>(j.job_visibility IN ('external', 'both') AND j.company_id != {$candidate_data['company_id']})</code></p>\n";

// Test individual conditions
$stmt = $db->prepare("
    SELECT id, title, job_visibility, company_id, 
           CASE WHEN job_visibility IN ('internal', 'both') AND company_id = ? THEN 'Condition 1 TRUE' ELSE 'Condition 1 FALSE' END as cond1,
           CASE WHEN job_visibility IN ('external', 'both') AND company_id != ? THEN 'Condition 2 TRUE' ELSE 'Condition 2 FALSE' END as cond2
    FROM jobs 
    WHERE status = 'active' AND (deadline IS NULL OR deadline >= CURDATE())
    ORDER BY company_id, job_visibility
");
$stmt->execute([$candidate_data['company_id'], $candidate_data['company_id']]);
$logic_test = $stmt->fetchAll();

echo "<table border='1' cellpadding='5'>\n";
echo "<tr><th>Title</th><th>Visibility</th><th>Company</th><th>Condition 1</th><th>Condition 2</th><th>Should Include</th></tr>\n";

foreach ($logic_test as $job) {
    $should_include = ($job['cond1'] === 'Condition 1 TRUE' || $job['cond2'] === 'Condition 2 TRUE');
    $color = $should_include ? 'style="background-color: #e8f5e8;"' : 'style="background-color: #ffebee;"';
    
    echo "<tr $color>";
    echo "<td>" . substr($job['title'], 0, 25) . "...</td>";
    echo "<td>{$job['job_visibility']}</td>";
    echo "<td>{$job['company_id']}</td>";
    echo "<td>{$job['cond1']}</td>";
    echo "<td>{$job['cond2']}</td>";
    echo "<td>" . ($should_include ? 'YES' : 'NO') . "</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; font-size: 12px; }
th { background-color: #f2f2f2; }
h1, h2, h3, h4 { color: #333; }
pre { background: #f5f5f5; padding: 10px; border-radius: 4px; font-size: 12px; }
</style>