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

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

echo "<h2>🔍 Check Job Visibility System</h2>\n";

// 1. Check if job_visibility column exists
echo "<h3>1. Database Structure Check</h3>\n";
try {
    $stmt = $db->query("DESCRIBE jobs");
    $columns = $stmt->fetchAll();
    $column_names = array_column($columns, 'Field');
    
    if (in_array('job_visibility', $column_names)) {
        echo "<p>✅ job_visibility column exists</p>\n";
        
        // Find the column details
        foreach ($columns as $col) {
            if ($col['Field'] === 'job_visibility') {
                echo "<p><strong>Column Type:</strong> {$col['Type']}</p>\n";
                echo "<p><strong>Default:</strong> {$col['Default']}</p>\n";
                break;
            }
        }
    } else {
        echo "<p>❌ job_visibility column does NOT exist</p>\n";
        echo "<p>Available columns: " . implode(', ', $column_names) . "</p>\n";
    }
} catch (Exception $e) {
    echo "<p>❌ Error checking table structure: " . $e->getMessage() . "</p>\n";
}

// 2. Check current job visibility values
echo "<h3>2. Current Job Visibility Values</h3>\n";
try {
    $stmt = $db->query("SELECT job_visibility, COUNT(*) as count FROM jobs GROUP BY job_visibility");
    $visibility_stats = $stmt->fetchAll();
    
    if ($visibility_stats) {
        echo "<table border='1' cellpadding='5'>\n";
        echo "<tr><th>Visibility</th><th>Count</th></tr>\n";
        foreach ($visibility_stats as $stat) {
            echo "<tr><td>{$stat['job_visibility']}</td><td>{$stat['count']}</td></tr>\n";
        }
        echo "</table>\n";
    } else {
        echo "<p>No jobs found or job_visibility column missing</p>\n";
    }
} catch (Exception $e) {
    echo "<p>❌ Error checking visibility values: " . $e->getMessage() . "</p>\n";
}

// 3. Sample jobs by company and visibility
echo "<h3>3. Sample Jobs by Company</h3>\n";
try {
    $stmt = $db->query("
        SELECT j.id, j.title, j.job_visibility, j.company_id, c.name as company_name 
        FROM jobs j 
        LEFT JOIN companies c ON j.company_id = c.id 
        ORDER BY j.company_id, j.job_visibility 
        LIMIT 10
    ");
    $sample_jobs = $stmt->fetchAll();
    
    if ($sample_jobs) {
        echo "<table border='1' cellpadding='5'>\n";
        echo "<tr><th>ID</th><th>Title</th><th>Visibility</th><th>Company</th></tr>\n";
        foreach ($sample_jobs as $job) {
            echo "<tr>";
            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 "</tr>\n";
        }
        echo "</table>\n";
    }
} catch (Exception $e) {
    echo "<p>❌ Error getting sample jobs: " . $e->getMessage() . "</p>\n";
}

// 4. Test current filtering logic
echo "<h3>4. Current Filtering Logic Analysis</h3>\n";

echo "<div style='background: #f8f9fa; padding: 15px; border-radius: 5px; margin: 10px 0;'>\n";
echo "<h4>Current Logic (from jobs.php lines 53-65):</h4>\n";
echo "<p><strong>Internal Candidates can see:</strong></p>\n";
echo "<ul>\n";
echo "<li>✅ Internal jobs from their OWN company</li>\n";
echo "<li>❌ ALL external and 'both' jobs from ANY company (This might be wrong!)</li>\n";
echo "</ul>\n";
echo "<p><strong>External Candidates can see:</strong></p>\n";
echo "<ul>\n";
echo "<li>✅ External and 'both' jobs from any company</li>\n";
echo "</ul>\n";
echo "</div>\n";

echo "<h3>5. Problem Analysis</h3>\n";
echo "<div style='background: #fff3cd; color: #856404; padding: 15px; border-radius: 5px; margin: 10px 0;'>\n";
echo "<h4>⚠️ Potential Issue Found:</h4>\n";
echo "<p>According to your requirement: <strong>\"internal candidates must not see jobs that are external only for their company\"</strong></p>\n";
echo "<p>But the current logic allows internal candidates to see ALL external jobs from ANY company.</p>\n";
echo "<p><strong>Expected Logic Should Be:</strong></p>\n";
echo "<ul>\n";
echo "<li>✅ Internal jobs from their company</li>\n";
echo "<li>✅ 'Both' visibility jobs from their company</li>\n";
echo "<li>✅ External and 'both' jobs from OTHER companies (not their own)</li>\n";
echo "<li>❌ External-only jobs from their OWN company (should be hidden)</li>\n";
echo "</ul>\n";
echo "</div>\n";

// 6. Simulate what each user type should see
echo "<h3>6. User Access Simulation</h3>\n";

// Get internal candidates
$stmt = $db->query("SELECT id, first_name, last_name, email, company_id FROM candidates WHERE candidate_type = 'internal' LIMIT 3");
$internal_candidates = $stmt->fetchAll();

foreach ($internal_candidates as $candidate) {
    echo "<h4>Internal Candidate: {$candidate['first_name']} {$candidate['last_name']} (Company ID: {$candidate['company_id']})</h4>\n";
    
    echo "<p><strong>Should SEE:</strong></p>\n";
    echo "<ul>\n";
    echo "<li>Internal jobs from company {$candidate['company_id']}</li>\n";
    echo "<li>'Both' visibility jobs from company {$candidate['company_id']}</li>\n";
    echo "<li>External and 'both' jobs from other companies (not {$candidate['company_id']})</li>\n";
    echo "</ul>\n";
    
    echo "<p><strong>Should NOT SEE:</strong></p>\n";
    echo "<ul>\n";
    echo "<li>External-only jobs from their own company {$candidate['company_id']}</li>\n";
    echo "</ul>\n";
    
    echo "<hr>\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>