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

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

$search     = post('search', '');
$linkedType = post('linked_type', '');
$isActive   = post('is_active', '');
['page' => $page, 'limit' => $limit, 'offset' => $offset] = getPagination();

// Check if migration has been run (new columns exist)
$hasMigration = false;
try {
    $check = $db->query("SHOW COLUMNS FROM checklist_templates LIKE 'linked_type'");
    $hasMigration = $check && $check->rowCount() > 0;
} catch (Exception $e) {}

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

if ($search) {
    $where[]  = '(t.name LIKE ? OR t.description LIKE ?)';
    $s        = "%$search%";
    $params[] = $s;
    $params[] = $s;
}
if ($isActive !== '') {
    $where[]  = 't.is_active = ?';
    $params[] = (int)$isActive;
}
if ($hasMigration && $linkedType !== '') {
    $where[]  = 't.linked_type = ?';
    $params[] = $linkedType;
}

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

try {
    $countStmt = $db->prepare("SELECT COUNT(*) FROM checklist_templates t WHERE $whereSQL");
    $countStmt->execute($params);
    $total = (int)$countStmt->fetchColumn();

    if ($hasMigration) {
        $sql = "
            SELECT t.*,
                   u.full_name  AS created_by_name,
                   ua.full_name AS assigned_to_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 item_count,
                   (SELECT COUNT(*) FROM checklist_instances WHERE template_id = t.id) AS instance_count,
                   (SELECT COUNT(*) FROM checklist_instances WHERE template_id = t.id AND status = 'pending') AS pending_count
            FROM checklist_templates t
            LEFT JOIN users u  ON u.id = t.created_by
            LEFT JOIN users ua ON ua.id = t.assigned_to
            LEFT JOIN fleet_vehicles v ON v.id = t.fleet_vehicle_id
            WHERE $whereSQL
            ORDER BY t.is_active DESC, t.name ASC
            LIMIT $limit OFFSET $offset
        ";
    } else {
        // Pre-migration fallback
        $sql = "
            SELECT t.*,
                   u.full_name AS created_by_name,
                   NULL AS assigned_to_name,
                   NULL AS vehicle_reg,
                   NULL AS vehicle_make,
                   NULL AS vehicle_model,
                   'general' AS linked_type,
                   (SELECT COUNT(*) FROM checklist_template_items WHERE template_id = t.id) AS item_count,
                   (SELECT COUNT(*) FROM checklist_instances WHERE template_id = t.id) AS instance_count,
                   (SELECT COUNT(*) FROM checklist_instances WHERE template_id = t.id AND status = 'pending') AS pending_count
            FROM checklist_templates t
            LEFT JOIN users u ON u.id = t.created_by
            WHERE $whereSQL
            ORDER BY t.is_active DESC, t.name ASC
            LIMIT $limit OFFSET $offset
        ";
    }

    $stmt = $db->prepare($sql);
    $stmt->execute($params);
    $templates = $stmt->fetchAll();

    apiSuccess([
        'templates'       => $templates,
        'needs_migration' => !$hasMigration,
        'pagination'      => [
            'total'       => $total,
            'page'        => $page,
            'limit'       => $limit,
            'total_pages' => ceil($total / $limit),
        ]
    ]);

} catch (Exception $e) {
    apiError('Database error: ' . $e->getMessage(), 500);
}