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

// Assuming $surveyor_id comes from session ($_SESSION['userid'])
$surveyor_id = $_SESSION['userid'];

$today_start = strtotime('today midnight');
$today_end = strtotime('tomorrow midnight') - 1;
$this_week_start = strtotime('monday this week');
$this_week_end = strtotime('sunday this week 23:59:59');
$this_month_start = strtotime('first day of this month');
$this_month_end = strtotime('last day of this month 23:59:59');

// Ensure that the same filters are applied as in the admin side to get consistent results
$sql = "
    SELECT 
        aa.id, 
        cd.clientname, 
        cd.clientnumber,
        aa.appointment_date, 
        aa.status,
        COUNT(i.itemnumber) AS total_items,
        SUM(CASE WHEN i.duedate < $today_start AND i.statuslive = 1 THEN 1 ELSE 0 END) AS overdue,
        SUM(CASE WHEN i.duedate BETWEEN $today_start AND $today_end AND i.statuslive = 1 THEN 1 ELSE 0 END) AS due_today,
        SUM(CASE WHEN i.duedate BETWEEN $this_week_start AND $this_week_end AND i.statuslive = 1 THEN 1 ELSE 0 END) AS due_this_week,
        SUM(CASE WHEN i.duedate BETWEEN $this_month_start AND $this_month_end AND i.statuslive = 1 THEN 1 ELSE 0 END) AS due_this_month
    FROM 
        admin_assignments aa
    LEFT JOIN 
        clientdetails cd ON aa.client_id = cd.clientnumber
    LEFT JOIN 
        itemdetails i ON aa.client_id = i.clientnumber
    WHERE 
        aa.surveyor_id = ? 
        AND i.duedate IS NOT NULL 
        AND i.duedate > 0 -- Exclude invalid or missing due dates
        AND i.statuslive = 1 -- Only count active items
        AND aa.status IN (0, 1) 
    GROUP BY 
        aa.id
";
$stmt = $con->prepare($sql);
$stmt->bind_param('i', $surveyor_id);
$stmt->execute();
$result = $stmt->get_result();

$assignments = [];
while ($row = $result->fetch_assoc()) {
    $assignments[] = [
        'id' => $row['id'],
        'clientname' => $row['clientname'],
        'clientnumber' => $row['clientnumber'],
        'appointment_date' => $row['appointment_date'],
        'status' => $row['status'] == 0 ? 'Not Started' : ($row['status'] == 1 ? 'In Progress' : 'Completed'),
        'total_items' => $row['total_items'],  // Total items (for reference)
        'overdue' => $row['overdue'],
        'due_today' => $row['due_today'],
        'due_this_week' => $row['due_this_week'],
        'due_this_month' => $row['due_this_month']
    ];
}

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

$stmt->close();
$con->close();