<?php
// ─── GET /api/pumps/repair-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  = ['pr.status = 1'];
$params = [];
if ($dateFrom) { $where[] = 'DATE(pr.date_time_created) >= ?'; $params[] = $dateFrom; }
if ($dateTo)   { $where[] = 'DATE(pr.date_time_created) <= ?'; $params[] = $dateTo; }
if ($teamId)   { $where[] = 'pr.drilling_team_id = ?'; $params[] = $teamId; }

$rows = $db->rows(
    "SELECT pr.record_id, pr.jobcard_no, pr.date_time_created,
            pr.address, pr.contact_number,
            pr.problem_description, pr.problem_solution,
            pr.repaired_by,
            COALESCE(pi.client_name, pr.address) AS client_name,
            COALESCE(pi.area, pr.address) AS area,
            pi.pump_depth, pi.pack_no, pi.pump_no,
            t.name AS team_name
     FROM   pump_repair pr
     LEFT JOIN teams t ON pr.drilling_team_id = t.record_id
     LEFT JOIN pump_installation pi ON pi.record_id = pr.installation_id
     WHERE  " . implode(' AND ', $where) . "
     ORDER BY pr.record_id DESC LIMIT 300",
    $params
);

foreach ($rows as &$pr) {
    $jid = $pr['jobcard_no'];
    $pr['replaced'] = $db->rows(
        "SELECT replaced_item, date_added FROM replaced_items WHERE jobcard_no = ?",
        [$jid]
    );
    $pr['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]
    );
    $pr['total_paid'] = array_sum(array_column($pr['payments'], 'amount'));
}
unset($pr);

Response::ok($rows);