<?php
/**
 * WorksCrFulfilment — turns approved CR lines into sourcing actions and tracks delivery.
 *
 * When a CR goes ACTIVE:
 *  - MATERIALS / TOOLS / SUBCONTRACTS / EQUIPMENT (rented, in_house_flag=0) lines can be
 *    LPO'd to suppliers via Lpos::createSupplierWithItems, stamped with source_cr_id +
 *    source_cr_pillar + source_phase_id so we can trace fulfilment back per pillar.
 *  - PRELIMINARIES / OVERHEADS lines that are project-cash requests are handled by the
 *    petty-cash / requisition flows (out of scope for this slice — only budget shows).
 *  - MANPOWER + in-house EQUIPMENT lines are tracked but not sourced through LPO (they
 *    consume the project's own float / attendance). Progress against them is manual.
 *
 * Physical delivery is recorded in `works_phase_items_issued` — the phase's authoritative
 * on-site register. One row per delivery event. `total` on the register drives the
 * "delivered" number that closes the loop against the CR budget.
 */
class WorksCrFulfilment extends Model
{
    /** Pillars that can be sourced via supplier LPOs. */
    public const SOURCEABLE_PILLARS = ['MATERIALS','TOOLS','SUBCONTRACTS','EQUIPMENT'];

    /* ── Read: what's on this CR that can be sourced ─────────────────── */

    /**
     * Return each sourceable pillar's lines with their budget + already-sourced totals,
     * so the UI can show remaining budget per line.
     *
     *   [
     *     'MATERIALS' => [
     *        ['line_id'=>1, 'description'=>'Diesel', 'qty'=>4000, 'unit'=>'L',
     *         'unit_cost'=>3000, 'line_total'=>12000000,
     *         'sourced_amount'=>8850000, 'remaining_amount'=>3150000],
     *        …
     *     ],
     *     'TOOLS' => [...], 'EQUIPMENT' => [...], 'SUBCONTRACTS' => [...]
     *   ]
     */
    public function sourceableLines(int $crId): array
    {
        $db = new DB();
        $out = [];
        foreach (self::SOURCEABLE_PILLARS as $pillar) {
            $out[$pillar] = $this->linesForPillar($crId, $pillar);
        }
        return $out;
    }

    private function linesForPillar(int $crId, string $pillar): array
    {
        $db = new DB();
        $rows = [];
        // Per-pillar SQL — each pillar has a different column shape.
        switch ($pillar) {
            case 'MATERIALS':
                $rows = $db->mySelect(
                    "SELECT c0d line_id, description, qty, unit, unit_cost, total AS line_total
                       FROM works_cr_materials WHERE cr_id = :c AND deleted = 0
                       ORDER BY line_no, c0d",
                    [':c' => $crId]
                ) ?: [];
                break;
            case 'TOOLS':
                $rows = $db->mySelect(
                    "SELECT c0d line_id, description, qty, unit, unit_cost, total AS line_total
                       FROM works_cr_tools WHERE cr_id = :c AND deleted = 0
                       ORDER BY line_no, c0d",
                    [':c' => $crId]
                ) ?: [];
                break;
            case 'SUBCONTRACTS':
                // Lump-sum → qty=1, unit='LS', unit_cost=lump_sum
                $rows = $db->mySelect(
                    "SELECT c0d line_id,
                            CONCAT(subcontractor_name, ' · ', scope) description,
                            1 qty, 'LS' unit, lump_sum unit_cost, lump_sum AS line_total
                       FROM works_cr_subcontracts WHERE cr_id = :c AND deleted = 0
                       ORDER BY line_no, c0d",
                    [':c' => $crId]
                ) ?: [];
                break;
            case 'EQUIPMENT':
                // Only rented (in_house_flag=0) equipment gets sourced through LPO.
                $rows = $db->mySelect(
                    "SELECT c0d line_id, description, hours AS qty, 'hr' unit, rate AS unit_cost, total AS line_total
                       FROM works_cr_equipment
                      WHERE cr_id = :c AND deleted = 0 AND in_house_flag = 0
                       ORDER BY line_no, c0d",
                    [':c' => $crId]
                ) ?: [];
                break;
        }

        // Attach sourced_amount per line (sum of LPO line totals pointing at this CR line).
        foreach ($rows as &$r) {
            $sr = $db->mySelect(
                "SELECT COALESCE(SUM(li.line_total),0) s
                   FROM lpo_items li
                   JOIN lpos l ON l.c0d = li.lpo_id AND l.deleted = 0
                  WHERE li.source_cr_line_id = :l AND li.source_cr_pillar = :p",
                [':l' => (int)$r['line_id'], ':p' => $pillar]
            );
            $sourced = (float)($sr[0]['s'] ?? 0);
            $r['sourced_amount']   = $sourced;
            $r['remaining_amount'] = max(0, (float)$r['line_total'] - $sourced);
        }
        return $rows;
    }

    /* ── Write: draft a supplier LPO from picked CR lines ──────────────── */

    /**
     * Convert a set of CR line selections into a supplier LPO (via Lpos::createSupplierWithItems).
     * Each selection = ['line_id' => X, 'qty' => Y, 'unit_price' => Z].
     * Guards over-sourcing per line (sourced + this must not exceed line_total).
     *
     * Returns the underlying Lpos::createSupplierWithItems result plus 'lpo_id'.
     */
    public function draftLpoFromCrLines(
        int $crId, string $pillar,
        array $vendor, array $selections,
        int $uid, string $uname
    ): array {
        if (empty($selections)) return ['ok' => false, 'msg' => 'No CR lines picked.', 'id' => 0];
        if (!in_array($pillar, self::SOURCEABLE_PILLARS, true)) {
            return ['ok' => false, 'msg' => "Pillar $pillar cannot be sourced via LPO.", 'id' => 0];
        }
        if (empty($vendor['vendor_id']) || (int)$vendor['vendor_id'] <= 0) {
            return ['ok' => false, 'msg' => 'Vendor is required.', 'id' => 0];
        }

        if (!class_exists('WorksCrs'))  require_once ROOT . '/app/models/WorksCrs.php';
        if (!class_exists('Lpos'))      require_once ROOT . '/app/models/Lpos.php';

        $wc = new WorksCrs();
        $cr = $wc->findById($crId);
        if (!$cr) return ['ok' => false, 'msg' => 'CR not found.', 'id' => 0];
        if ($cr['status'] !== 'ACTIVE') {
            return ['ok' => false, 'msg' => 'CR must be ACTIVE to source LPOs (current: ' . $cr['status'] . ').', 'id' => 0];
        }

        // Snapshot the sourceable lines to validate the picks + assemble descriptions.
        $lines = $this->linesForPillar($crId, $pillar);
        $byId  = [];
        foreach ($lines as $l) $byId[(int)$l['line_id']] = $l;

        $items = [];
        foreach ($selections as $sel) {
            $lid = (int)($sel['line_id'] ?? 0);
            if (!isset($byId[$lid])) return ['ok' => false, 'msg' => "CR line $lid not sourceable.", 'id' => 0];
            $qty = (float)($sel['qty']        ?? 0);
            $up  = (float)($sel['unit_price'] ?? 0);
            if ($qty <= 0) return ['ok' => false, 'msg' => "Qty must be > 0 for line $lid.", 'id' => 0];
            if ($up  <= 0) return ['ok' => false, 'msg' => "Unit price must be > 0 for line $lid.", 'id' => 0];
            $lineTotal = round($qty * $up, 2);
            // Over-source guard on line total (not qty — vendors may quote different qty→cost splits).
            if ($lineTotal > ((float)$byId[$lid]['remaining_amount'] + 0.01)) {
                return [
                    'ok' => false,
                    'msg' => "Line $lid over-sourced: this LPO adds " . number_format($lineTotal)
                           . " but only " . number_format((float)$byId[$lid]['remaining_amount']) . " remains.",
                    'id' => 0,
                ];
            }
            $items[] = [
                'description'        => (string)$byId[$lid]['description'],
                'qty'                => $qty,
                'unit'               => (string)$byId[$lid]['unit'],
                'unit_price'         => $up,
                'source_cr_pillar'   => $pillar,
                'source_cr_line_id'  => $lid,
            ];
        }

        // Create the supplier LPO via existing model
        $lp = new Lpos();
        $res = $lp->createSupplierWithItems([
            'project_id'    => (int)$cr['project_id'],
            'source_module' => 'works',
            'vendor_id'     => (int)$vendor['vendor_id'],
            'vendor_name'   => (string)($vendor['vendor_name'] ?? ''),
            'currency'      => (string)($vendor['currency'] ?? 'TZS'),
            'purpose'       => "CR " . $cr['cr_no'] . " · " . $pillar,
        ], $items, $uid, $uname);

        if (!$res['ok'] || (int)$res['id'] <= 0) return $res;

        // Stamp source_cr_* on the header + line items so we can trace back.
        // Also override the approval_chain_key to WORKS_LPO_APPROVAL (default from TYPES()
        // is GOODS_LPO_APPROVAL; Works-sourced LPOs need their own chain for correct
        // in-flight queue reporting and terminal-status semantics).
        $db = new DB();
        $db->insertUpdate(
            "UPDATE lpos
                SET source_cr_id = :c, source_cr_pillar = :p, source_phase_id = :ph,
                    approval_chain_key = 'WORKS_LPO_APPROVAL',
                    edited_on = NOW()
              WHERE c0d = :id",
            [':c' => $crId, ':p' => $pillar, ':ph' => (int)$cr['phase_id'], ':id' => (int)$res['id']]
        );
        // Fetch the newly-inserted lpo_items in order to stamp them.
        $newItems = $db->mySelect(
            "SELECT c0d, item_no FROM lpo_items WHERE lpo_id = :l ORDER BY item_no",
            [':l' => (int)$res['id']]
        ) ?: [];
        foreach ($newItems as $i => $row) {
            if (!isset($items[$i])) break;
            $db->insertUpdate(
                "UPDATE lpo_items
                    SET source_cr_pillar = :p, source_cr_line_id = :cl
                  WHERE c0d = :id",
                [':p' => $pillar, ':cl' => (int)$items[$i]['source_cr_line_id'], ':id' => (int)$row['c0d']]
            );
        }
        return $res + ['lpo_id' => (int)$res['id']];
    }

    /* ── LPOs already sourced from this CR ─────────────────────────────── */

    /** All LPOs stamped with this CR, with vendor + status + amount. */
    public function lposFor(int $crId): array
    {
        $db = new DB();
        return $db->mySelect(
            "SELECT c0d, lpo_no, source_cr_pillar, payee_id, payee_name,
                    amount, currency, cash_out_status, receipt_status,
                    approval_id, requested_on
               FROM lpos WHERE source_cr_id = :c AND deleted = 0
               ORDER BY source_cr_pillar, c0d",
            [':c' => $crId]
        ) ?: [];
    }

    /* ── Physical delivery register ────────────────────────────────────── */

    /**
     * Record an on-site delivery against a specific LPO line (which is stamped with
     * source_cr_line_id + source_cr_pillar).
     */
    public function recordIssue(array $d, int $uid, string $uname): int
    {
        $lpoItemId = (int)($d['lpo_item_id'] ?? 0);
        $qty       = (float)($d['qty'] ?? 0);
        if ($lpoItemId <= 0) throw new RuntimeException('lpo_item_id required.');
        if ($qty      <= 0)  throw new RuntimeException('qty must be > 0.');
        $issuedOn = (string)($d['issued_on'] ?? date('Y-m-d'));

        $db = new DB();
        $li = $db->mySelect(
            "SELECT li.*, l.project_id, l.source_cr_id, l.source_phase_id
               FROM lpo_items li JOIN lpos l ON l.c0d = li.lpo_id
              WHERE li.c0d = :id",
            [':id' => $lpoItemId]
        );
        if (empty($li)) throw new RuntimeException('LPO line not found.');
        $row = $li[0];
        if ((int)$row['source_cr_id'] === 0) throw new RuntimeException('This LPO is not sourced from a CR.');

        // Guard: cumulative issued qty must not exceed lpo line qty
        $already = (float)($db->mySelect(
            "SELECT COALESCE(SUM(qty),0) n FROM works_phase_items_issued WHERE lpo_item_id = :i AND deleted = 0",
            [':i' => $lpoItemId]
        )[0]['n'] ?? 0);
        if ($already + $qty > (float)$row['qty'] + 0.0001) {
            throw new RuntimeException(
                'Over-issue: LPO line qty=' . rtrim(rtrim((string)$row['qty'], '0'), '.')
                . ', already issued=' . rtrim(rtrim((string)$already, '0'), '.')
                . ', this=' . rtrim(rtrim((string)$qty, '0'), '.')
            );
        }

        $total = round($qty * (float)$row['unit_price'], 2);
        $fb = $db->insertUpdate(
            "INSERT INTO works_phase_items_issued
                (phase_id, cr_id, cr_pillar, cr_line_id, lpo_id, lpo_item_id,
                 description, qty, unit, unit_cost, total,
                 issued_by, issued_by_name, issued_on, notes, created_on)
             VALUES (:ph, :cr, :pil, :cl, :lp, :li,
                     :d, :q, :u, :uc, :t,
                     :ub, :ubn, :dt, :nt, NOW())",
            [
                ':ph'  => (int)$row['source_phase_id'],
                ':cr'  => (int)$row['source_cr_id'],
                ':pil' => (string)$row['source_cr_pillar'],
                ':cl'  => (int)$row['source_cr_line_id'],
                ':lp'  => (int)$row['lpo_id'],
                ':li'  => $lpoItemId,
                ':d'   => (string)$row['description'],
                ':q'   => $qty,
                ':u'   => (string)$row['unit'],
                ':uc'  => (float)$row['unit_price'],
                ':t'   => $total,
                ':ub'  => $uid, ':ubn' => $uname,
                ':dt'  => $issuedOn,
                ':nt'  => (string)($d['notes'] ?? ''),
            ]
        );
        // Also bump lpo_items.qty_received so the LPO's own receiving view stays truthful.
        $db->insertUpdate(
            "UPDATE lpo_items SET qty_received = qty_received + :q WHERE c0d = :id",
            [':q' => $qty, ':id' => $lpoItemId]
        );
        return (int)($fb['last_id'] ?? 0);
    }

    /** All delivery rows for a CR, grouped by pillar for the fulfilment table. */
    public function issuedFor(int $crId): array
    {
        $db = new DB();
        return $db->mySelect(
            "SELECT c0d, cr_pillar, cr_line_id, lpo_id, lpo_item_id,
                    description, qty, unit, unit_cost, total,
                    issued_by_name, issued_on, notes
               FROM works_phase_items_issued
              WHERE cr_id = :c AND deleted = 0
              ORDER BY cr_pillar, issued_on DESC, c0d DESC",
            [':c' => $crId]
        ) ?: [];
    }

    /* ── Payments — the fifth measure closing the money loop ─────────── */

    /**
     * Payments allocated against LPOs sourced from this CR, joined to the LPO's PV.
     * Used by the CR fulfil dashboard to render "paid" per LPO.
     */
    public function paymentsFor(int $crId): array
    {
        $db = new DB();
        return $db->mySelect(
            "SELECT pa.c0d alloc_id, pa.payment_id, pa.amount alloc_amount, pa.allocation_type,
                    p.payment_no, p.payment_date, p.payment_method, p.reference_no,
                    pv.c0d pv_id, pv.pv_no, pv.status pv_status,
                    l.c0d lpo_id, l.lpo_no, l.source_cr_pillar, l.payee_name
               FROM payment_allocations pa
               JOIN payments        p  ON p.c0d  = pa.payment_id AND p.deleted  = 0
               LEFT JOIN payment_vouchers pv ON pv.c0d = p.payment_voucher_id
               JOIN lpos            l  ON l.c0d  = pa.lpo_id
              WHERE l.source_cr_id = :c AND l.deleted = 0
              ORDER BY p.payment_date DESC, pa.c0d DESC",
            [':c' => $crId]
        ) ?: [];
    }

    /* ── The money shot: per-pillar progress ──────────────────────────── */

    /**
     * Return an assoc keyed by pillar with:
     *   budget           — CR pillar total (from works_crs.total_*)
     *   sourced          — sum of LPO amounts (works LPOs) source_cr_pillar match
     *   delivered        — sum of works_phase_items_issued.total for pillar
     *   paid             — sum of payment_allocations.amount for that pillar's LPOs
     *   remaining        — budget − paid (unpaid budget still to spend)
     *   sourced_pct / delivered_pct / paid_pct
     */
    public function progressPerPillar(int $crId): array
    {
        if (!class_exists('WorksCrs')) require_once ROOT . '/app/models/WorksCrs.php';
        $wc = new WorksCrs();
        $cr = $wc->findById($crId);
        if (!$cr) return [];

        $db = new DB();
        $sourcedByPillar = $db->mySelect(
            "SELECT source_cr_pillar pillar, COALESCE(SUM(amount),0) s
               FROM lpos
              WHERE source_cr_id = :c AND deleted = 0
              GROUP BY source_cr_pillar",
            [':c' => $crId]
        ) ?: [];
        $sourceMap = [];
        foreach ($sourcedByPillar as $r) $sourceMap[$r['pillar']] = (float)$r['s'];

        $deliveredByPillar = $db->mySelect(
            "SELECT cr_pillar pillar, COALESCE(SUM(total),0) s
               FROM works_phase_items_issued
              WHERE cr_id = :c AND deleted = 0
              GROUP BY cr_pillar",
            [':c' => $crId]
        ) ?: [];
        $delMap = [];
        foreach ($deliveredByPillar as $r) $delMap[$r['pillar']] = (float)$r['s'];

        $paidByPillar = $db->mySelect(
            "SELECT l.source_cr_pillar pillar, COALESCE(SUM(pa.amount),0) s
               FROM payment_allocations pa
               JOIN payments p ON p.c0d = pa.payment_id AND p.deleted = 0
               JOIN lpos     l ON l.c0d = pa.lpo_id     AND l.deleted = 0
              WHERE l.source_cr_id = :c
              GROUP BY l.source_cr_pillar",
            [':c' => $crId]
        ) ?: [];
        $paidMap = [];
        foreach ($paidByPillar as $r) $paidMap[$r['pillar']] = (float)$r['s'];

        $out = [];
        foreach (WorksCrs::PILLARS as $pillar => $meta) {
            $budget    = (float)($cr[$meta['total_col']] ?? 0);
            $sourced   = (float)($sourceMap[$pillar] ?? 0);
            $delivered = (float)($delMap[$pillar]    ?? 0);
            $paid      = (float)($paidMap[$pillar]   ?? 0);
            $out[$pillar] = [
                'budget'        => $budget,
                'sourced'       => $sourced,
                'delivered'     => $delivered,
                'paid'          => $paid,
                'remaining'     => max(0, $budget - $paid),
                'sourced_pct'   => $budget > 0 ? round($sourced   / $budget * 100, 1) : 0,
                'delivered_pct' => $budget > 0 ? round($delivered / $budget * 100, 1) : 0,
                'paid_pct'      => $budget > 0 ? round($paid      / $budget * 100, 1) : 0,
            ];
        }
        return $out;
    }
}
