<?php
require_once __DIR__ . '/../config/db.php';
require_once __DIR__ . '/../config/auth.php';

$user = requireAuth();
$db   = getDB();

$templateId  = post('template_id', '');
$status      = post('status', '');
$dateFrom    = post('date_from', '');
$dateTo      = post('date_to', '');
$linkedType  = post('linked_type', '');
$vehicleId   = post('vehicle_id', '');
['page' => $page, 'limit' => $limit, 'offset' => $offset] = getPagination();

$where  = ['1=1'];
$params = [];

// Technicians only see their own
if ($user['role_id'] == 4) {
    $where[]  = '(ci.user_id = ? OR ci.user_id = 0)';
    $params[] = $user['id'];
}

if ($templateId !== '') {
    $where[]  = 'ci.template_id = ?';
    $params[] = (int)$templateId;
}
if ($status !== '') {
    $where[]  = 'ci.status = ?';
    $params[] = $status;
}
if ($dateFrom) {
    $where[]  = 'ci.due_date >= ?';
    $params[] = $dateFrom;
}
if ($dateTo) {
    $where[]  = 'ci.due_date <= ?';
    $params[] = $dateTo;
}
if ($linkedType !== '') {
    $where[]  = 't.linked_type = ?';
    $params[] = $linkedType;
}
if ($vehicleId !== '') {
    $where[]  = 'ci.vehicle_id = ?';
    $params[] = (int)$vehicleId;
}

$whereSQL = implode(' AND ', $where);

$countStmt = $db->prepare("
    SELECT COUNT(*) FROM checklist_instances ci
    LEFT JOIN checklist_templates t ON t.id = ci.template_id
    WHERE $whereSQL
");
$countStmt->execute($params);
$total = (int)$countStmt->fetchColumn();

$stmt = $db->prepare("
    SELECT ci.*,
           t.name AS template_name, t.linked_type, t.frequency,
           u.full_name AS assigned_to_name,
           cb.full_name AS completed_by_name,
           v.registration AS vehicle_reg, v.make AS vehicle_make, v.model AS vehicle_model,
           (SELECT COUNT(*) FROM checklist_template_items WHERE template_id = t.id) AS total_items,
           (SELECT COUNT(*) FROM checklist_instance_items cii WHERE cii.instance_id = ci.id) AS answered_items
    FROM checklist_instances ci
    LEFT JOIN checklist_templates t  ON t.id  = ci.template_id
    LEFT JOIN users u  ON u.id  = ci.user_id
    LEFT JOIN users cb ON cb.id = ci.completed_by
    LEFT JOIN fleet_vehicles v ON v.id = ci.vehicle_id
    WHERE $whereSQL
    ORDER BY ci.due_date DESC, ci.id DESC
    LIMIT $limit OFFSET $offset
");
$stmt->execute($params);
$instances = $stmt->fetchAll();

apiSuccess([
    'instances'  => $instances,
    'pagination' => [
        'total'       => $total,
        'page'        => $page,
        'limit'       => $limit,
        'total_pages' => ceil($total / $limit),
    ]
]);