<?php
// ─── GET /api/pumps/install-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.');

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

// Linked drilling jobcard (via drilling_invoice = jc_no)
$drilling_jc = null;
if (!empty($install['drilling_invoice'])) {
    $drilling_jc = $db->row(
        "SELECT record_id, jc_no, client_name, address, contact_number,
                jc_current_status, team_assigned_id, water_strike, water_flow
         FROM   jobcards
         WHERE  jc_no = ?",
        [$install['drilling_invoice']]
    );
}

// Pump timeline
$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",
    [$install['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", [$install['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. PI_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",
    [$install['jobcard_no']]
);

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