<?php
// ─── GET /api/pumps/install-report.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();

$dateFrom = trim($_GET['date_from'] ?? '');
$dateTo   = trim($_GET['date_to']   ?? '');
$teamId   = (int)($_GET['team_id']  ?? 0);

$where  = ['pi.status = 1'];
$params = [];
if ($dateFrom) { $where[] = 'DATE(pi.date_time_created) >= ?'; $params[] = $dateFrom; }
if ($dateTo)   { $where[] = 'DATE(pi.date_time_created) <= ?'; $params[] = $dateTo; }
if ($teamId)   { $where[] = 'pi.drilling_team_id = ?'; $params[] = $teamId; }

$rows = $db->rows(
    "SELECT pi.record_id, pi.jobcard_no, pi.drilling_invoice, pi.date_time_created,
            pi.client_name, pi.area, pi.contact_number,
            pi.pump_depth, pi.cable_meters, pi.hdpe_meters,
            pi.borehole_meters, pi.pack_no, pi.pump_no,
            pi.installed_by, pi.date_attended,
            t.name AS team_name
     FROM   pump_installation pi
     LEFT JOIN teams t ON pi.drilling_team_id = t.record_id
     WHERE  " . implode(' AND ', $where) . "
     ORDER BY pi.record_id DESC LIMIT 300",
    $params
);

foreach ($rows as &$pi) {
    $jid = $pi['jobcard_no'];
    $pi['payments'] = $db->rows(
        "SELECT jp.amount, jp.payment_method, jp.date_time
         FROM   jobcard_payments jp
         WHERE  jp.jobcard_id = ? ORDER BY jp.date_time ASC",
        [$jid]
    );
    $pi['total_paid'] = array_sum(array_column($pi['payments'], 'amount'));
}
unset($pi);

Response::ok($rows);