<?php
class TripRequestCargoItems extends Model
{
    private function s($v): string { return trim((string)$v); }
    private function f($v): ?float { $v = str_replace(',', '', (string)$v); return $v === '' ? null : (float)$v; }

    public function forRequest(int $requestId): array
    {
        return (new DB())->mySelect(
            "SELECT c0d, seq_no, item_name, qty, uom, weight_kg, volume_m3,
                    packaging, handling_notes, unit_value, currency
               FROM trip_request_cargo_items
              WHERE trip_request_id = :id AND deleted = 0
           ORDER BY seq_no ASC, c0d ASC",
            [':id' => $requestId]
        ) ?: [];
    }

    public function sumsForRequest(int $requestId): array
    {
        $rs = (new DB())->mySelect(
            "SELECT COALESCE(SUM(weight_kg),0) AS total_weight,
                    COALESCE(SUM(volume_m3),0) AS total_volume,
                    COUNT(*) AS n
               FROM trip_request_cargo_items
              WHERE trip_request_id = :id AND deleted = 0",
            [':id' => $requestId]
        );
        return $rs[0] ?? ['total_weight'=>0,'total_volume'=>0,'n'=>0];
    }

    public function create(int $requestId, array $d, int $uid): array
    {
        $name = $this->s($d['item_name'] ?? '');
        if ($name === '') return ['ok' => false, 'msg' => 'Item name is required.', 'id' => 0];
        $qty  = (float)str_replace(',', '', (string)($d['qty'] ?? 1)) ?: 1;

        // Next seq_no for this request
        $rs = (new DB())->mySelect(
            "SELECT COALESCE(MAX(seq_no),0) + 1 AS n FROM trip_request_cargo_items WHERE trip_request_id = :id",
            [':id' => $requestId]
        );
        $seq = (int)($rs[0]['n'] ?? 1);

        $r = (new DB())->insertUpdate(
            "INSERT INTO trip_request_cargo_items
                (trip_request_id, seq_no, item_name, qty, uom, weight_kg, volume_m3,
                 packaging, handling_notes, unit_value, currency, created_by, created_on)
             VALUES
                (:req, :sq, :nm, :qty, :uom, :wt, :vol, :pkg, :hn, :uv, :cur, :uid, NOW())",
            [
                ':req' => $requestId,
                ':sq'  => $seq,
                ':nm'  => $name,
                ':qty' => round($qty, 3),
                ':uom' => $this->s(($d['uom'] ?? '') ?: 'units'),
                ':wt'  => $this->f($d['weight_kg'] ?? ''),
                ':vol' => $this->f($d['volume_m3'] ?? ''),
                ':pkg' => $this->s($d['packaging']       ?? ''),
                ':hn'  => $this->s($d['handling_notes']  ?? ''),
                ':uv'  => $this->f($d['unit_value']      ?? ''),
                ':cur' => $this->s(($d['currency'] ?? '') ?: 'TZS'),
                ':uid' => $uid,
            ]
        );
        return ['ok' => $r['success'] > 0, 'msg' => $r['success'] > 0 ? 'Cargo item added.' : 'Save failed.', 'id' => (int)$r['last_id']];
    }

    public function softDelete(int $id, int $uid): array
    {
        (new DB())->insertUpdate(
            "UPDATE trip_request_cargo_items SET deleted = 1, edited_by = :uid, edited_on = NOW() WHERE c0d = :id",
            [':uid' => $uid, ':id' => $id]
        );
        return ['ok' => true, 'msg' => 'Cargo item removed.'];
    }

    public function byId(int $id): array
    {
        $rs = (new DB())->mySelect(
            "SELECT * FROM trip_request_cargo_items WHERE c0d = :id AND deleted = 0 LIMIT 1",
            [':id' => $id]
        );
        return $rs[0] ?? [];
    }
}
