<?php
require_once __DIR__ . '/../config/db.php';
require_once __DIR__ . '/../config/auth.php';
$user = requireAuth();
$db = getDB();
$uid = (int)$user['id'];
$role = (int)$user['role_id'];
$from = '2026-03-01';
$to   = '2026-03-31';

// Test the EXACT query used in events.php
$clWhere = ($role == 1 || $role == 2)
    ? "ci.due_date BETWEEN ? AND ?"
    : "ci.due_date BETWEEN ? AND ? AND (ci.user_id = ? OR ci.user_id = 0 OR ct.assigned_to = ? OR ct.assigned_to IS NULL)";
$clParams = ($role == 1 || $role == 2) ? [$from, $to] : [$from, $to, $uid, $uid];

$clStmt = $db->prepare("
    SELECT ci.id, ci.due_date, ci.status, ci.user_id, ct.name
    FROM checklist_instances ci
    JOIN checklist_templates ct ON ct.id = ci.template_id
    WHERE $clWhere
    ORDER BY ci.due_date LIMIT 5
");
$clStmt->execute($clParams);
$rows = $clStmt->fetchAll();

apiSuccess([
    'role' => $role,
    'uid'  => $uid,
    'where_used' => $clWhere,
    'params' => $clParams,
    'rows_returned' => count($rows),
    'rows' => $rows,
]);