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

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

echo "<h2>📋 Job Filtering Demo - Create Test Scenario</h2>\n";

// Create a test external job for SANPC company to demonstrate filtering
$test_job_title = "Test External Job - SANPC Only";
$test_job_description = "This is a test external job that internal SANPC employees should NOT be able to see.";

echo "<h3>1. Creating Test External Job for SANPC</h3>\n";

// Check if test job already exists
$stmt = $db->prepare("SELECT id FROM jobs WHERE title = ?");
$stmt->execute([$test_job_title]);
$existing_job = $stmt->fetch();

if (!$existing_job) {
    $stmt = $db->prepare("
        INSERT INTO jobs (
            company_id, title, description, requirements, location, 
            employment_type, salary_min, salary_max, experience_level,
            job_visibility, status, created_at
        ) VALUES (
            8, ?, ?, 'Bachelor degree required', 'Cape Town', 
            'full-time', 50000, 80000, 'mid',
            'external', 'active', NOW()
        )
    ");
    
    $result = $stmt->execute([
        $test_job_title,
        $test_job_description
    ]);
    
    if ($result) {
        $test_job_id = $db->lastInsertId();
        echo "<p>✅ Created test job with ID: $test_job_id</p>\n";
    } else {
        echo "<p>❌ Failed to create test job</p>\n";
    }
} else {
    $test_job_id = $existing_job['id'];
    echo "<p>ℹ️ Test job already exists with ID: $test_job_id</p>\n";
}

echo "<h3>2. Current Job Distribution</h3>\n";

// Show job distribution by company and visibility
$stmt = $db->query("
    SELECT 
        c.name as company_name,
        j.job_visibility,
        COUNT(*) as count
    FROM jobs j
    LEFT JOIN companies c ON j.company_id = c.id
    WHERE j.status = 'active'
    GROUP BY c.name, j.job_visibility
    ORDER BY c.name, j.job_visibility
");
$job_stats = $stmt->fetchAll();

echo "<table border='1' cellpadding='5'>\n";
echo "<tr><th>Company</th><th>Visibility</th><th>Count</th></tr>\n";
foreach ($job_stats as $stat) {
    echo "<tr>";
    echo "<td>{$stat['company_name']}</td>";
    echo "<td>{$stat['job_visibility']}</td>";
    echo "<td>{$stat['count']}</td>";
    echo "</tr>\n";
}
echo "</table>\n";

echo "<h3>3. Testing Filtering for SANPC Internal Employees</h3>\n";

// Get SANPC internal candidates
$stmt = $db->prepare("SELECT id, first_name, last_name FROM candidates WHERE candidate_type = 'internal' AND company_id = 8 LIMIT 1");
$stmt->execute();
$sanpc_candidate = $stmt->fetch();

if ($sanpc_candidate) {
    echo "<h4>Testing for {$sanpc_candidate['first_name']} {$sanpc_candidate['last_name']} (SANPC Internal Employee)</h4>\n";
    
    // Jobs they CAN see (using corrected logic)
    $stmt = $db->prepare("
        SELECT j.id, j.title, j.job_visibility, c.name as company_name
        FROM jobs j 
        LEFT JOIN companies c ON j.company_id = c.id 
        WHERE j.status = 'active' AND c.status = 'approved' AND (
            (j.job_visibility IN ('internal', 'both') AND j.company_id = 8) OR 
            (j.job_visibility IN ('external', 'both') AND j.company_id != 8)
        )
        ORDER BY j.company_id, j.job_visibility
    ");
    $stmt->execute();
    $can_see = $stmt->fetchAll();
    
    echo "<p><strong>✅ Jobs they CAN see (" . count($can_see) . " total):</strong></p>\n";
    echo "<table border='1' cellpadding='5'>\n";
    echo "<tr><th>Title</th><th>Visibility</th><th>Company</th><th>Why Visible?</th></tr>\n";
    foreach ($can_see as $job) {
        $reason = '';
        if ($job['job_visibility'] == 'internal' && $job['company_name'] == 'SANPC') {
            $reason = '🏢 Internal job from own company';
        } elseif ($job['job_visibility'] == 'both' && $job['company_name'] == 'SANPC') {
            $reason = '🏢 Both visibility from own company';
        } elseif (in_array($job['job_visibility'], ['external', 'both']) && $job['company_name'] != 'SANPC') {
            $reason = '🌐 External/Both from other company';
        }
        
        echo "<tr>";
        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;'>$reason</td>";
        echo "</tr>\n";
    }
    echo "</table>\n";
    
    // Jobs they CANNOT see
    $stmt = $db->prepare("
        SELECT j.id, j.title, j.job_visibility, c.name as company_name
        FROM jobs j 
        LEFT JOIN companies c ON j.company_id = c.id 
        WHERE j.status = 'active' AND c.status = 'approved' 
        AND j.job_visibility = 'external' AND j.company_id = 8
    ");
    $stmt->execute();
    $cannot_see = $stmt->fetchAll();
    
    echo "<p><strong>❌ Jobs they CANNOT see (" . count($cannot_see) . " total):</strong></p>\n";
    if ($cannot_see) {
        echo "<table border='1' cellpadding='5' style='background-color: #ffebee;'>\n";
        echo "<tr><th>Title</th><th>Visibility</th><th>Company</th><th>Why Hidden?</th></tr>\n";
        foreach ($cannot_see as $job) {
            echo "<tr>";
            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; color: #d32f2f;'>🚫 External-only from own company</td>";
            echo "</tr>\n";
        }
        echo "</table>\n";
    } else {
        echo "<p>No external-only jobs from their company</p>\n";
    }
}

echo "<h3>4. Business Logic Explanation</h3>\n";
echo "<div style='background: #f0f4ff; color: #1565c0; padding: 15px; border-radius: 5px; margin: 10px 0;'>\n";
echo "<h4>🎯 Why This Filtering Makes Sense:</h4>\n";
echo "<p><strong>Scenario:</strong> SANPC has both internal employees and wants to hire external candidates for certain roles.</p>\n";
echo "<ul>\n";
echo "<li><strong>External-only jobs</strong> are meant for external candidates only (not current employees)</li>\n";
echo "<li><strong>Internal-only jobs</strong> are meant for current employees only</li>\n";
echo "<li><strong>Both jobs</strong> are open to both internal employees and external candidates</li>\n";
echo "</ul>\n";
echo "<p><strong>Internal employees should see:</strong></p>\n";
echo "<ul>\n";
echo "<li>✅ Internal jobs from their company (promotions, transfers)</li>\n";
echo "<li>✅ 'Both' jobs from their company (they can apply)</li>\n";
echo "<li>✅ All jobs from other companies (they can leave and apply elsewhere)</li>\n";
echo "</ul>\n";
echo "<p><strong>Internal employees should NOT see:</strong></p>\n";
echo "<ul>\n";
echo "<li>❌ External-only jobs from their own company (these are for external candidates only)</li>\n";
echo "</ul>\n";
echo "</div>\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>