<?php
/**
 * Debug Job Approval Query
 * Temporary debug script to check job approval data
 */

// Include database connection and functions
require_once 'config/database.php';
require_once 'includes/functions.php';
require_once 'includes/auth.php';

// Check if user is logged in
if (!isLoggedIn()) {
    echo "Not logged in. Please log in first.";
    exit;
}

echo "<h2>Job Approval Debug Information</h2>";

// Check current user session
echo "<h3>Current User Session:</h3>";
echo "<pre>";
echo "User ID: " . ($_SESSION['user_id'] ?? 'Not set') . "\n";
echo "Role: " . ($_SESSION['role'] ?? 'Not set') . "\n";
echo "Society ID: " . ($_SESSION['society_id'] ?? 'Not set') . "\n";
echo "Circuit ID: " . ($_SESSION['circuit_id'] ?? 'Not set') . "\n";
echo "Diocese ID: " . ($_SESSION['diocese_id'] ?? 'Not set') . "\n";
echo "</pre>";

// Check if jobs table exists and its structure
echo "<h3>Jobs Table Structure:</h3>";
$structureQuery = "DESCRIBE jobs";
try {
    $structure = fetchRows($structureQuery);
    echo "<table border='1'>";
    echo "<tr><th>Field</th><th>Type</th><th>Null</th><th>Key</th><th>Default</th><th>Extra</th></tr>";
    foreach ($structure as $field) {
        echo "<tr>";
        echo "<td>" . htmlspecialchars($field['Field']) . "</td>";
        echo "<td>" . htmlspecialchars($field['Type']) . "</td>";
        echo "<td>" . htmlspecialchars($field['Null']) . "</td>";
        echo "<td>" . htmlspecialchars($field['Key']) . "</td>";
        echo "<td>" . htmlspecialchars($field['Default'] ?? 'NULL') . "</td>";
        echo "<td>" . htmlspecialchars($field['Extra']) . "</td>";
        echo "</tr>";
    }
    echo "</table>";
} catch (Exception $e) {
    echo "Error getting table structure: " . $e->getMessage();
}

// Check total number of jobs
echo "<h3>Total Jobs Count:</h3>";
try {
    $totalJobs = fetchRow("SELECT COUNT(*) as count FROM jobs");
    echo "Total jobs in database: " . $totalJobs['count'] . "<br>";
} catch (Exception $e) {
    echo "Error counting jobs: " . $e->getMessage() . "<br>";
}

// Check jobs by approval status
echo "<h3>Jobs by Approval Status:</h3>";
try {
    $statusCounts = fetchRows("SELECT approval_status, COUNT(*) as count FROM jobs GROUP BY approval_status");
    foreach ($statusCounts as $status) {
        echo $status['approval_status'] . ": " . $status['count'] . " jobs<br>";
    }
} catch (Exception $e) {
    echo "Error getting status counts: " . $e->getMessage() . "<br>";
}

// Check sample pending jobs
echo "<h3>Sample Pending Jobs (Raw Data):</h3>";
try {
    $sampleJobs = fetchRows("SELECT id, title, approval_status, society_id, member_id, created_at FROM jobs WHERE approval_status = 'pending' LIMIT 5");
    if (empty($sampleJobs)) {
        echo "No pending jobs found.<br>";
    } else {
        echo "<table border='1'>";
        echo "<tr><th>ID</th><th>Title</th><th>Approval Status</th><th>Society ID</th><th>Member ID</th><th>Created At</th></tr>";
        foreach ($sampleJobs as $job) {
            echo "<tr>";
            echo "<td>" . $job['id'] . "</td>";
            echo "<td>" . htmlspecialchars($job['title']) . "</td>";
            echo "<td>" . $job['approval_status'] . "</td>";
            echo "<td>" . ($job['society_id'] ?? 'NULL') . "</td>";
            echo "<td>" . ($job['member_id'] ?? 'NULL') . "</td>";
            echo "<td>" . $job['created_at'] . "</td>";
            echo "</tr>";
        }
        echo "</table>";
    }
} catch (Exception $e) {
    echo "Error getting sample jobs: " . $e->getMessage() . "<br>";
}

// Test the exact query from job-approval.php
echo "<h3>Testing Job Approval Query:</h3>";
$pendingJobsQuery = "SELECT j.*, jc.name as category_name, s.name as society_name, 
                     c.name as circuit_name, d.name as diocese_name,
                     m.first_name, m.surname, jpu.full_name as poster_name
                     FROM jobs j
                     LEFT JOIN job_categories jc ON j.category_id = jc.id
                     LEFT JOIN societies s ON j.society_id = s.id
                     LEFT JOIN circuits c ON s.circuit_id = c.id
                     LEFT JOIN dioceses d ON c.diocese_id = d.id
                     LEFT JOIN members m ON j.member_id = m.id
                     LEFT JOIN job_portal_users jpu ON j.member_id = jpu.member_id
                     WHERE j.approval_status = 'pending'";

// Filter based on user permissions (same logic as job-approval.php)
if (!hasRole(['admin', 'diocesan_secretary', 'diocesan_president'])) {
    if (isset($_SESSION['society_id']) && !empty($_SESSION['society_id'])) {
        $pendingJobsQuery .= " AND j.society_id = " . (int)$_SESSION['society_id'];
        echo "Applied society filter: society_id = " . (int)$_SESSION['society_id'] . "<br>";
    } elseif (isset($_SESSION['circuit_id']) && !empty($_SESSION['circuit_id'])) {
        $pendingJobsQuery .= " AND s.circuit_id = " . (int)$_SESSION['circuit_id'];
        echo "Applied circuit filter: circuit_id = " . (int)$_SESSION['circuit_id'] . "<br>";
    }
} else {
    echo "User has diocesan/admin role - no filtering applied<br>";
}

$pendingJobsQuery .= " ORDER BY j.created_at DESC";

echo "<strong>Final Query:</strong><br>";
echo "<pre>" . htmlspecialchars($pendingJobsQuery) . "</pre>";

try {
    $pendingJobs = fetchRows($pendingJobsQuery);
    echo "<strong>Query Result:</strong> " . count($pendingJobs) . " jobs found<br>";
    
    if (!empty($pendingJobs)) {
        echo "<table border='1'>";
        echo "<tr><th>ID</th><th>Title</th><th>Society</th><th>Approval Status</th><th>Posted By</th></tr>";
        foreach ($pendingJobs as $job) {
            echo "<tr>";
            echo "<td>" . $job['id'] . "</td>";
            echo "<td>" . htmlspecialchars($job['title']) . "</td>";
            echo "<td>" . htmlspecialchars($job['society_name'] ?? 'N/A') . "</td>";
            echo "<td>" . $job['approval_status'] . "</td>";
            echo "<td>" . htmlspecialchars($job['poster_name'] ?? ($job['first_name'] . ' ' . $job['surname'])) . "</td>";
            echo "</tr>";
        }
        echo "</table>";
    }
} catch (Exception $e) {
    echo "Error executing query: " . $e->getMessage() . "<br>";
    echo "SQL Error: " . $e->getTraceAsString() . "<br>";
}

// Check user roles
echo "<h3>User Role Check:</h3>";
$roles = ['admin', 'society_secretary', 'choirmaster', 'diocesan_secretary', 'diocesan_president', 'diocese_chairman'];
foreach ($roles as $role) {
    $hasRole = hasRole($role);
    echo "hasRole('$role'): " . ($hasRole ? 'YES' : 'NO') . "<br>";
}
?>
