<?php
// ─── GET /api/pumps/repair-get.php?id=X ─────────────────────────────────
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();

$id = (int) ($_GET['id'] ?? 0);
if (!$id) Response::error('id is required.');

$repair = $db->row(
    "SELECT pr.*, t.name AS team_name
     FROM   pump_repair pr
     LEFT JOIN teams t ON t.record_id = pr.drilling_team_id
     WHERE  pr.record_id = ?",
    [$id]
);
if (!$repair) Response::error('Record not found.', 404);

// Linked installation (existing client)
$installation = null;
if (!empty($repair['installation_id'])) {
    $installation = $db->row(
        "SELECT record_id, jobcard_no, client_name, area,
                contact_number, pump_depth, pump_no, pack_no
         FROM   pump_installation
         WHERE  record_id = ?",
        [$repair['installation_id']]
    );
}

// Linked drilling jobcard (via installation or direct)
$drilling_jc = null;
if ($installation && !empty($installation['jobcard_no'])) {
    // installation jobcard_no is PI_X, get drilling_invoice from it
    $inst_full = $db->row(
        "SELECT drilling_invoice FROM pump_installation WHERE record_id = ?",
        [$repair['installation_id']]
    );
    if ($inst_full && $inst_full['drilling_invoice']) {
        $drilling_jc = $db->row(
            "SELECT record_id, jc_no, client_name, address, contact_number,
                    jc_current_status, water_strike, water_flow
             FROM   jobcards WHERE jc_no = ?",
            [$inst_full['drilling_invoice']]
        );
    }
}

// Pump timeline for this repair
$timeline = $db->rows(
    "SELECT pt.*, u.username
     FROM   pump_timeline pt
     LEFT JOIN users u ON u.record_id = pt.user_id
     WHERE  pt.jobcard_no = ?
     ORDER BY pt.record_id ASC",
    [$repair['jobcard_no']]
);

// Replaced items
$replaced = $db->rows(
    "SELECT * FROM replaced_items WHERE jobcard_no = ? ORDER BY record_id ASC",
    [$repair['jobcard_no']]
);

// Notes — query by jobcard_no (new) and numeric id (legacy)
// Fetch notes by jobcard_no (new format) and by numeric record_id (legacy), deduplicated
$notesByJcNo  = $db->rows("SELECT * FROM notes WHERE jobcard_id = ? ORDER BY record_id DESC", [$repair['jobcard_no']]);
$notesByNumId = $db->rows("SELECT * FROM notes WHERE jobcard_id = ? ORDER BY record_id DESC", [(string)$id]);
$seen = []; $notes = [];
foreach (array_merge($notesByJcNo, $notesByNumId) as $n) {
    if (!isset($seen[$n['record_id']])) { $seen[$n['record_id']] = true; $notes[] = $n; }
}
usort($notes, fn($a,$b) => $b['record_id'] <=> $a['record_id']);

// Payments — keyed by pump jobcard_no (e.g. PR_1)
$payments = $db->rows(
    "SELECT jp.*, u.username
     FROM   jobcard_payments jp
     LEFT JOIN users u ON u.record_id = jp.user_id
     WHERE  jp.jobcard_id = ?
     ORDER BY jp.date_time ASC",
    [$repair['jobcard_no']]
);

Response::ok([
    'repair'       => $repair,
    'installation' => $installation,
    'drilling_jc'  => $drilling_jc,
    'timeline'     => $timeline,
    'replaced'     => $replaced,
    'notes'        => $notes,
    'payments'     => $payments,
]);