<?php
// ─── GET /api/invoices/list.php ───────────────────────────────────────────
define('ROOT', dirname(__DIR__, 2));
require_once ROOT . '/core/DB.php';
require_once ROOT . '/core/Response.php';
require_once ROOT . '/core/Auth.php';

Auth::require();
$db = DB::get();

$status = trim($_GET['status'] ?? '');
$search = trim($_GET['search'] ?? '');

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

if ($status) { $where[] = 'i.status = ?'; $params[] = $status; }
if ($search) {
    $where[] = '(i.invoice_no LIKE ? OR i.client_name LIKE ?)';
    $params[] = "%$search%"; $params[] = "%$search%";
}

$rows = $db->rows(
    "SELECT i.*,
            COALESCE((SELECT SUM(ii.total) FROM invoice_items ii
                      WHERE ii.invoice_no = i.invoice_no), 0) AS items_subtotal,
            -- Payments: JC invoices use jc_record_id, pump invoices use pump jobcard_no
            COALESCE((
                SELECT SUM(jp.amount) FROM jobcard_payments jp
                WHERE jp.jobcard_id = CASE
                    WHEN i.jc_record_id IS NOT NULL THEN CAST(i.jc_record_id AS CHAR)
                    WHEN i.pump_type = 'install' THEN
                        (SELECT jobcard_no FROM pump_installation WHERE record_id = i.pump_record_id LIMIT 1)
                    WHEN i.pump_type = 'repair' THEN
                        (SELECT jobcard_no FROM pump_repair WHERE record_id = i.pump_record_id LIMIT 1)
                    ELSE NULL END
            ), 0) AS total_paid
     FROM   invoices i
     WHERE  " . implode(' AND ', $where) . "
     ORDER BY i.record_id DESC",
    $params
);

Response::ok($rows);