<?php


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

/* Show all live clients with counts of overdue and pending items */
$sql = "
  SELECT
    cd.clientnumber,
    cd.clientname,
    COUNT(i.itemnumber) AS total_items,
    SUM(CASE WHEN i.duedate < UNIX_TIMESTAMP(NOW()) THEN 1 ELSE 0 END) AS overdue,
    SUM(CASE WHEN i.duedate >= UNIX_TIMESTAMP(NOW())
              AND i.duedate <= UNIX_TIMESTAMP(NOW() + INTERVAL 30 DAY) THEN 1 ELSE 0 END) AS pending
  FROM clientdetails cd
  LEFT JOIN itemdetails i
    ON i.clientnumber = cd.clientnumber
   AND i.statuslive = 1
   AND i.duedate IS NOT NULL
   AND i.duedate > 0
  WHERE cd.statuslive = 1
  GROUP BY cd.clientnumber, cd.clientname
";

/* If you only want clients with any expected inspections, add:
   HAVING (overdue + pending) > 0
*/

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

while ($row = $result->fetch_assoc()) {
    $expected = (int)$row['overdue'] + (int)$row['pending'];
    $clients[] = [
        'clientnumber' => (int)$row['clientnumber'],
        'clientname' => $row['clientname'],
        'expected_inspections' => $expected
    ];
}

echo json_encode(['clients' => $clients]);

$con->close();
