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

$invoice = $db->row("SELECT * FROM invoices WHERE record_id = ?", [$id]);
if (!$invoice) Response::error('Invoice not found.', 404);

// Line items
$items = $db->rows(
    "SELECT * FROM invoice_items WHERE invoice_no = ? ORDER BY record_id ASC",
    [$invoice['invoice_no']]
);

// Linked drilling jobcard
$jc = null;
if ($invoice['jc_record_id']) {
    $jc = $db->row(
        "SELECT j.record_id, j.jc_no, j.client_name, j.address,
                j.contact_number, j.jc_current_status, j.water_strike, j.water_flow,
                COALESCE((SELECT SUM(t.meters) FROM jobcard_timeline t
                          WHERE t.jobcard_id = j.record_id
                          AND t.type IN ('DRILLING_STOP','DRILLING_PAUSE')), 0) AS drilling_meters,
                COALESCE((SELECT SUM(t.meters) FROM jobcard_timeline t
                          WHERE t.jobcard_id = j.record_id
                          AND t.type IN ('CASING_STOP','RIEMING_STOP')), 0) AS casing_meters
         FROM   jobcards j WHERE j.record_id = ?",
        [$invoice['jc_record_id']]
    );
}

// Linked pump record (install or repair)
$pump = null;
if ($invoice['pump_record_id'] && $invoice['pump_type']) {
    if ($invoice['pump_type'] === 'install') {
        $pump = $db->row(
            "SELECT record_id, jobcard_no, client_name, area AS address,
                    contact_number, pump_depth, pump_no, pack_no,
                    jc_current_status
             FROM   pump_installation WHERE record_id = ?",
            [$invoice['pump_record_id']]
        );
    } else {
        $pump = $db->row(
            "SELECT record_id, jobcard_no, address AS client_name,
                    contact_number, address, jc_current_status,
                    problem_description, problem_solution
             FROM   pump_repair WHERE record_id = ?",
            [$invoice['pump_record_id']]
        );
    }
    if ($pump) $pump['pump_type'] = $invoice['pump_type'];
}

// Payments — from jobcard OR pump jobcard_no
$paymentSource = null;
if ($invoice['jc_record_id']) {
    $paymentSource = $invoice['jc_record_id'];
} elseif ($pump) {
    $paymentSource = $pump['jobcard_no']; // e.g. PI_1 or PR_1
}

$payments = [];
if ($paymentSource !== null) {
    $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",
        [(string) $paymentSource]
    );
}

Response::ok([
    'invoice'  => $invoice,
    'items'    => $items,
    'jobcard'  => $jc,
    'pump'     => $pump,
    'payments' => $payments,
]);