<?php
require_once('../../../'.$_REQUEST['tenant'].'/config.php');

// Define date ranges for overdue, today, this week, and this month
$currentDate = time();
$todayStart = strtotime('today midnight');
$todayEnd = strtotime('tomorrow midnight') - 1;
$weekStart = strtotime('monday this week');
$weekEnd = strtotime('sunday this week 23:59:59');
$monthStart = strtotime('first day of this month');
$monthEnd = strtotime('last day of this month 23:59:59');

$sql = "
     SELECT 
        aa.id, 
        aa.appointment_date,
        cd.clientname, 
         cd.clientnumber, 
        s.id AS surveyor_id,  -- Select the surveyor ID here
        CONCAT(s.forename, ' ', s.surname) AS surveyor, 
        COUNT(i.itemnumber) AS total_inspections,
        SUM(CASE WHEN i.duedate < $todayStart THEN 1 ELSE 0 END) AS overdue,
        SUM(CASE WHEN i.duedate BETWEEN $todayStart AND $todayEnd THEN 1 ELSE 0 END) AS due_today,
        SUM(CASE WHEN i.duedate BETWEEN $weekStart AND $weekEnd THEN 1 ELSE 0 END) AS due_this_week,
        SUM(CASE WHEN i.duedate BETWEEN $monthStart AND $monthEnd THEN 1 ELSE 0 END) AS due_this_month,
        CASE aa.status 
            WHEN 0 THEN 'Not Started' 
            WHEN 1 THEN 'In Progress' 
            WHEN 2 THEN 'Completed' 
        END AS status
    FROM admin_assignments aa
    LEFT JOIN clientdetails cd ON aa.client_id = cd.clientnumber
    LEFT JOIN surveyor s ON aa.surveyor_id = s.id
    LEFT JOIN itemdetails i ON i.clientnumber = cd.clientnumber
    WHERE i.statuslive = 1
    GROUP BY aa.id
";

$result = $con->query($sql);
$jobs = [];

while ($row = $result->fetch_assoc()) {
    $jobs[] = $row;
}

echo json_encode(['jobs' => $jobs]);
?>