<?php
/**
 * GoodsOnboarding — CRM-authored Client PO + 4-stage GOODS_PROJECT_ONBOARDING chain.
 *
 * Flow (rsflow.dspera Goods):
 *   1. CRM_CAPTURE   · CRM lists Items To Supply on the Client PO (brand, qty, unit,
 *                       source_pref [LOCAL/IMPORT/ANY], delivery_method, price). Submits.
 *   2. FM_REVIEW     · FM confirms finance side, ASSIGNS Accountant + Procurement.
 *   3. EXEC_APPROVE  · Exec gives commercial go-ahead, ASSIGNS Project Manager.
 *   4. PM_ASSIGN     · PM designates Projects Coordinator. TERMINAL — Ops gates open.
 *
 * On terminal APPROVED, onChainDecision() flips c_projects.crm_status='APPROVED',
 * stamps c_projects.{accountant_id, procurement_id, project_manager_id, pc_id} from
 * the accumulated staged assignments, and lifts the Client PO status DRAFT → CONFIRMED.
 *
 * Legacy projects (migrated_flag=1 from the backfill) never touch this model — they
 * keep working via the pre-8g PC-authored Client PO path unchanged.
 */
class GoodsOnboarding extends Model
{
    public const CHAIN_KEY   = 'GOODS_PROJECT_ONBOARDING';
    public const ENTITY_TYPE = 'goods_project_onboarding';

    /* ── Client PO helper — one DRAFT PO per goods project during CRM phase ─── */

    /**
     * Returns the existing DRAFT Client PO for this project, or creates a new one
     * (bare header, no items) tied to the project's customer. Idempotent.
     */
    public function getOrCreateDraftClientPo(int $projectId, int $userId, string $userName): array
    {
        $db  = new DB();
        $prj = $db->mySelect(
            "SELECT c0d, project_no, project_customer, project_customer_id, project_type, currency, contract_value, default_margin_pct, crm_status, migrated_flag
               FROM c_projects WHERE c0d = :p AND deleted = 0",
            [':p' => $projectId]
        );
        if (empty($prj)) return ['ok' => false, 'msg' => 'Project not found.', 'po_id' => 0];
        $p = $prj[0];
        if ($p['project_type'] !== 'goods') {
            return ['ok' => false, 'msg' => 'Only goods projects use this onboarding.', 'po_id' => 0];
        }
        if ((int)$p['migrated_flag'] === 1) {
            return ['ok' => false, 'msg' => 'Legacy project (migrated) — uses old PC-authored Client PO path.', 'po_id' => 0];
        }

        // Existing DRAFT — reuse
        $ex = $db->mySelect(
            "SELECT c0d FROM goods_client_pos WHERE project_id = :p AND status = 'DRAFT' AND deleted = 0 LIMIT 1",
            [':p' => $projectId]
        );
        if (!empty($ex)) return ['ok' => true, 'po_id' => (int)$ex[0]['c0d'], 'created' => false];

        $custId = (int)$p['project_customer_id'];
        if ($custId <= 0) return ['ok' => false, 'msg' => 'Project has no customer_id linked — set one before onboarding.', 'po_id' => 0];

        // Slice 8i · central atomic doc numbering (CLIENT_PO doc_key)
        if (!class_exists('DocNumberSettings')) require_once ROOT . '/app/models/DocNumberSettings.php';
        $poNo = (new DocNumberSettings())->allocate('CLIENT_PO');
        $fb = $db->insertUpdate(
            "INSERT INTO goods_client_pos
                (client_po_no, project_id, customer_id, customer_name, po_date,
                 currency, vat_rate, default_margin_pct, status, source_module,
                 created_by, created_on, edited_by, edited_on)
             VALUES (:no, :pj, :cid, :cnm, CURDATE(),
                     :cur, 18.00, :dm, 'DRAFT', 'goods',
                     :u, NOW(), :u, NOW())",
            [
                ':no'  => $poNo, ':pj' => $projectId,
                ':cid' => $custId, ':cnm' => (string)$p['project_customer'],
                ':cur' => (string)($p['currency'] ?: 'TZS'),
                ':dm'  => (float)($p['default_margin_pct'] ?: 25.00),
                ':u'   => $userId,
            ]
        );
        return ['ok' => true, 'po_id' => (int)($fb['last_id'] ?? 0), 'created' => true];
    }

    public function itemsFor(int $poId): array
    {
        $db = new DB();
        return $db->mySelect(
            "SELECT * FROM goods_client_po_items WHERE client_po_id = :p ORDER BY item_no, c0d",
            [':p' => $poId]
        ) ?: [];
    }

    /* ── Add / edit / remove a single item (CRM authoring) ──────────────────── */

    public function addItem(int $poId, array $d, int $userId): array
    {
        $desc = trim((string)($d['description'] ?? ''));
        if ($desc === '')                        return ['ok' => false, 'msg' => 'Description required.'];
        $qty  = (float)($d['qty'] ?? 0);
        $upx  = (float)($d['unit_price_sell'] ?? 0);
        if ($qty <= 0)                            return ['ok' => false, 'msg' => 'Qty must be > 0.'];
        if ($upx  < 0)                            return ['ok' => false, 'msg' => 'Unit price must be >= 0.'];
        $src = strtoupper((string)($d['source_pref'] ?? 'ANY'));
        if (!in_array($src, ['LOCAL','IMPORT','ANY'], true)) $src = 'ANY';

        $db = new DB();
        $po = $db->mySelect("SELECT status FROM goods_client_pos WHERE c0d = :id AND deleted = 0", [':id' => $poId]);
        if (empty($po))                           return ['ok' => false, 'msg' => 'Client PO not found.'];
        if ($po[0]['status'] !== 'DRAFT')         return ['ok' => false, 'msg' => 'Client PO is not DRAFT (status=' . $po[0]['status'] . ') — items are locked.'];

        $nextNo = (int)($db->mySelect(
            "SELECT COALESCE(MAX(item_no),0)+1 n FROM goods_client_po_items WHERE client_po_id = :p",
            [':p' => $poId]
        )[0]['n'] ?? 1);

        $lt = round($qty * $upx, 2);
        $margin = (float)($d['margin_pct'] ?? 0);
        $cog = $margin > 0 ? round($upx * (1 - $margin/100), 4) : (float)($d['unit_price_cog'] ?? 0);
        $ltCog = round($qty * $cog, 2);

        $fb = $db->insertUpdate(
            "INSERT INTO goods_client_po_items
                (client_po_id, item_no, description, brand, source_pref, delivery_method,
                 qty, unit, unit_price_sell, unit_price_cog, margin_pct,
                 line_total_sell, line_total_cog, notes)
             VALUES (:p, :n, :d, :br, :sp, :dm,
                     :q, :u, :px, :cog, :mp,
                     :lt, :ltc, :nt)",
            [
                ':p' => $poId, ':n' => $nextNo, ':d' => $desc,
                ':br' => (string)($d['brand'] ?? ''), ':sp' => $src,
                ':dm' => (string)($d['delivery_method'] ?? ''),
                ':q' => $qty, ':u' => (string)($d['unit'] ?? ''),
                ':px' => $upx, ':cog' => $cog, ':mp' => $margin,
                ':lt' => $lt, ':ltc' => $ltCog,
                ':nt' => (string)($d['notes'] ?? ''),
            ]
        );
        $this->recomputeTotals($poId);
        return ['ok' => true, 'id' => (int)($fb['last_id'] ?? 0)];
    }

    /**
     * Slice 8Y · One-click clone every tender_items row on the source tender into this Client PO.
     * Uses the tender's APPROVED pricing lines for unit_price_sell/unit_price_cog/margin when available;
     * else falls back to the pricing_default markup and unit_cost = 0 (PC fills later).
     * Skips items whose description already exists on the PO to keep re-runs idempotent.
     * Returns ['ok'=>bool, 'inserted'=>int, 'skipped'=>int, 'msg'=>string].
     */
    public function cloneItemsFromTender(int $poId, int $tenderId, int $userId): array
    {
        $db = new DB();
        $po = $db->mySelect("SELECT c0d, status, default_margin_pct FROM goods_client_pos WHERE c0d=:id AND deleted=0", [':id'=>$poId]);
        if (empty($po))                    return ['ok'=>false, 'msg'=>'Client PO not found.'];
        if ($po[0]['status'] !== 'DRAFT') return ['ok'=>false, 'msg'=>'Client PO is not DRAFT — items are locked.'];
        $defMargin = (float)($po[0]['default_margin_pct'] ?? 25);

        $items = $db->mySelect(
            "SELECT line_no, item_ref, description, qty, uom, spec_notes
               FROM tender_items
              WHERE tender_id = :t AND deleted = 0
              ORDER BY line_no, c0d",
            [':t' => $tenderId]
        ) ?: [];
        if (empty($items)) return ['ok'=>false, 'msg'=>'No scope items on the source tender.'];

        // Approved pricing lookup: key by lowercase description → unit_cost, unit_price, markup.
        $priceByDesc = [];
        $pv = $db->mySelect(
            "SELECT c0d FROM tender_pricing_versions WHERE tender_id=:t AND deleted=0 ORDER BY (status='APPROVED') DESC, c0d DESC LIMIT 1",
            [':t' => $tenderId]
        );
        if (!empty($pv)) {
            $rows = $db->mySelect(
                "SELECT description, unit_cost, unit_price, markup_pct FROM tender_pricing_lines WHERE version_id=:v AND deleted=0",
                [':v' => (int)$pv[0]['c0d']]
            ) ?: [];
            foreach ($rows as $r) $priceByDesc[strtolower(trim((string)$r['description']))] = $r;
        }

        $existing = $db->mySelect("SELECT LOWER(TRIM(description)) AS k FROM goods_client_po_items WHERE client_po_id=:p", [':p'=>$poId]) ?: [];
        $seen = [];
        foreach ($existing as $e) {
            $k = trim(strtolower((string)($e['k'] ?? '')));
            if ($k !== '') $seen[$k] = true;
        }

        $inserted = 0; $skipped = 0;
        foreach ($items as $it) {
            $desc = trim((string)$it['description']);
            $k    = strtolower($desc);
            if (isset($seen[$k])) { $skipped++; continue; }
            $priced = $priceByDesc[$k] ?? null;

            $unitPrice = $priced ? (float)$priced['unit_price'] : 0;
            $unitCost  = $priced ? (float)$priced['unit_cost']  : 0;
            $margin    = $priced ? (float)$priced['markup_pct'] : $defMargin;

            $res = $this->addItem($poId, [
                'description'     => $desc,
                'brand'           => '',
                'source_pref'     => 'ANY',
                'delivery_method' => '',
                'qty'             => (float)$it['qty'],
                'unit'            => (string)($it['uom'] ?? ''),
                'unit_price_sell' => $unitPrice,
                'unit_price_cog'  => $unitCost,
                'margin_pct'      => $margin,
                'notes'           => trim(((string)($it['item_ref'] ?? '')) . ($it['spec_notes'] ? ' · ' . $it['spec_notes'] : '')),
            ], $userId);
            if (!empty($res['ok'])) { $inserted++; $seen[$k] = true; }
            else                    { $skipped++; }
        }
        return ['ok'=>true, 'inserted'=>$inserted, 'skipped'=>$skipped, 'msg'=>"Cloned $inserted line(s), skipped $skipped duplicate(s)."];
    }

    /**
     * Slice 8Y · Bulk insert items from a parsed XLSX / CSV upload.
     * Each row: description (required), qty, unit, unit_price_sell, margin_pct, brand, source_pref, notes.
     * Skips rows without description. Returns per-row report.
     */
    public function bulkInsertItems(int $poId, array $rows, int $userId): array
    {
        $db = new DB();
        $po = $db->mySelect("SELECT status FROM goods_client_pos WHERE c0d=:id AND deleted=0", [':id'=>$poId]);
        if (empty($po))                    return ['ok'=>false, 'msg'=>'Client PO not found.'];
        if ($po[0]['status'] !== 'DRAFT') return ['ok'=>false, 'msg'=>'Client PO is not DRAFT — items locked.'];
        $inserted = 0; $failed = 0; $errors = [];
        foreach ($rows as $i => $r) {
            if (empty(trim((string)($r['description'] ?? '')))) continue;
            $res = $this->addItem($poId, [
                'description'     => (string)$r['description'],
                'brand'           => (string)($r['brand']           ?? ''),
                'source_pref'     => strtoupper((string)($r['source_pref'] ?? 'ANY')),
                'delivery_method' => (string)($r['delivery_method'] ?? ''),
                'qty'             => (float) ($r['qty']             ?? 0),
                'unit'            => (string)($r['unit']            ?? ''),
                'unit_price_sell' => (float) ($r['unit_price_sell'] ?? 0),
                'margin_pct'      => (float) ($r['margin_pct']      ?? 0),
                'notes'           => (string)($r['notes']           ?? ''),
            ], $userId);
            if (!empty($res['ok'])) $inserted++;
            else { $failed++; $errors[] = 'Row ' . ($i + 1) . ': ' . ($res['msg'] ?? 'insert failed'); }
        }
        return ['ok'=>true, 'inserted'=>$inserted, 'failed'=>$failed, 'errors'=>$errors, 'msg'=>"Imported $inserted line(s)" . ($failed ? ", $failed failed." : '.')];
    }

    /**
     * Slice 8Z · PC updates the per-line scrub detail (brand, alt, priority, deliver_by/to, remarks).
     * Also handles the sourcing_ceiling override (unit_price_sell) since PC now owns that.
     * PO must be DRAFT.
     */
    public function updateItemDetails(int $poId, int $itemId, array $d): array
    {
        $db = new DB();
        $po = $db->mySelect("SELECT status FROM goods_client_pos WHERE c0d = :id AND deleted = 0", [':id' => $poId]);
        if (empty($po))                    return ['ok' => false, 'msg' => 'Client PO not found.'];
        if ($po[0]['status'] !== 'DRAFT') return ['ok' => false, 'msg' => 'Client PO is not DRAFT — items locked.'];

        $priority = strtoupper((string)($d['priority'] ?? 'MED'));
        if (!in_array($priority, ['LOW','MED','HIGH','CRITICAL'], true)) $priority = 'MED';

        $db->insertUpdate(
            "UPDATE goods_client_po_items SET
                brand         = :br,
                priority      = :pr,
                alt_ok        = :ao,
                alt_note      = :an,
                deliver_by    = :db,
                deliver_to    = :dt,
                scrub_remarks = :sr,
                unit_price_sell = COALESCE(:sc, unit_price_sell),
                line_total_sell = ROUND(qty * COALESCE(:sc2, unit_price_sell), 2)
              WHERE c0d = :id AND client_po_id = :p",
            [
                ':br' => (string)($d['brand']         ?? ''),
                ':pr' => $priority,
                ':ao' => !empty($d['alt_ok']) ? 1 : 0,
                ':an' => (string)($d['alt_note']      ?? ''),
                ':db' => !empty($d['deliver_by']) ? (string)$d['deliver_by'] : null,
                ':dt' => (string)($d['deliver_to']    ?? ''),
                ':sr' => (string)($d['scrub_remarks'] ?? ''),
                ':sc' => isset($d['sourcing_ceiling']) && $d['sourcing_ceiling'] !== '' ? (float)$d['sourcing_ceiling'] : null,
                ':sc2'=> isset($d['sourcing_ceiling']) && $d['sourcing_ceiling'] !== '' ? (float)$d['sourcing_ceiling'] : null,
                ':id' => $itemId, ':p' => $poId,
            ]
        );
        $this->recomputeTotals($poId);
        return ['ok' => true];
    }

    /**
     * Slice 8Z · Flip item_status → CONFIRMED (PC review complete).
     * Records who + when. Idempotent — re-calling refreshes the timestamp.
     */
    public function confirmItem(int $poId, int $itemId, int $userId, string $userName): array
    {
        $db = new DB();
        $po = $db->mySelect("SELECT status FROM goods_client_pos WHERE c0d = :id AND deleted = 0", [':id' => $poId]);
        if (empty($po))                    return ['ok' => false, 'msg' => 'Client PO not found.'];
        if ($po[0]['status'] !== 'DRAFT') return ['ok' => false, 'msg' => 'Client PO is not DRAFT.'];
        $db->insertUpdate(
            "UPDATE goods_client_po_items SET
                item_status = 'CONFIRMED', confirmed_at = NOW(), confirmed_by = :u, confirmed_by_name = :un
              WHERE c0d = :id AND client_po_id = :p",
            [':u' => $userId, ':un' => $userName, ':id' => $itemId, ':p' => $poId]
        );
        return ['ok' => true];
    }

    public function unconfirmItem(int $poId, int $itemId): array
    {
        $db = new DB();
        $po = $db->mySelect("SELECT status FROM goods_client_pos WHERE c0d = :id AND deleted = 0", [':id' => $poId]);
        if (empty($po))                    return ['ok' => false, 'msg' => 'Client PO not found.'];
        if ($po[0]['status'] !== 'DRAFT') return ['ok' => false, 'msg' => 'Client PO is not DRAFT.'];
        $db->insertUpdate(
            "UPDATE goods_client_po_items SET item_status = 'PENDING', confirmed_at = NULL, confirmed_by = NULL, confirmed_by_name = NULL
              WHERE c0d = :id AND client_po_id = :p",
            [':id' => $itemId, ':p' => $poId]
        );
        return ['ok' => true];
    }

    /**
     * Slice 8Z-a · PC saves the PO's delivery block (mode, primary address, contact, incoterm, deadline, instructions).
     */
    public function savePoDelivery(int $poId, array $d): array
    {
        $db = new DB();
        $po = $db->mySelect("SELECT status FROM goods_client_pos WHERE c0d = :id AND deleted = 0", [':id' => $poId]);
        if (empty($po))                    return ['ok' => false, 'msg' => 'Client PO not found.'];
        if ($po[0]['status'] !== 'DRAFT') return ['ok' => false, 'msg' => 'PO is not DRAFT — delivery block is locked.'];

        $mode = strtoupper((string)($d['delivery_mode'] ?? 'SINGLE_LOCATION'));
        if (!in_array($mode, ['SINGLE_LOCATION','MULTI_LOCATION'], true)) $mode = 'SINGLE_LOCATION';
        $inco = strtoupper((string)($d['incoterm'] ?? ''));
        if ($inco !== '' && !in_array($inco, ['EXW','FCA','CPT','CIP','DAP','DPU','DDP'], true)) $inco = '';

        $db->insertUpdate(
            "UPDATE goods_client_pos SET
                delivery_mode                = :m,
                primary_delivery_location    = :loc,
                delivery_contact_name        = :cn,
                delivery_contact_phone       = :cp,
                incoterm                     = :inc,
                delivery_deadline            = :dd,
                delivery_special_instructions = :si,
                edited_on                    = NOW()
              WHERE c0d = :id",
            [
                ':m'   => $mode,
                ':loc' => (string)($d['primary_delivery_location'] ?? ''),
                ':cn'  => (string)($d['delivery_contact_name']     ?? ''),
                ':cp'  => (string)($d['delivery_contact_phone']    ?? ''),
                ':inc' => $inco === '' ? null : $inco,
                ':dd'  => !empty($d['delivery_deadline']) ? (string)$d['delivery_deadline'] : null,
                ':si'  => (string)($d['delivery_special_instructions'] ?? ''),
                ':id'  => $poId,
            ]
        );
        return ['ok' => true];
    }

    /**
     * Slice 8Z + 8Z-a · Hand the Client PO over to Procurement.
     * Gates (in order):
     *   - PO must be DRAFT
     *   - At least one item exists
     *   - Every item is CONFIRMED
     *   - Delivery block complete: mode + incoterm + (primary location when SINGLE)
     *   - At least one ACTIVE overhead on the project (PC has reviewed the carry-over pool)
     * Effects:
     *   - status DRAFT → SOURCING
     *   - handover_at / by / notes / to_user_id stamped
     *   - Notification body carries incoterm + location + item count + overhead count
     *   - Bell notification to assigned Procurement user (or group); FYI to PM + FM
     */
    public function handoverToProcurement(int $poId, string $notes, int $userId, string $userName): array
    {
        $db = new DB();
        $po = $db->mySelect(
            "SELECT gcp.c0d, gcp.status, gcp.client_po_no, gcp.project_id,
                    gcp.delivery_mode, gcp.primary_delivery_location, gcp.incoterm,
                    p.procurement_id, p.procurement_name, p.project_manager_id, p.project_no, p.project_name
               FROM goods_client_pos gcp
          LEFT JOIN c_projects p ON p.c0d = gcp.project_id
              WHERE gcp.c0d = :id AND gcp.deleted = 0",
            [':id' => $poId]
        );
        if (empty($po))                    return ['ok' => false, 'msg' => 'Client PO not found.'];
        $po = $po[0];
        if ($po['status'] !== 'DRAFT')    return ['ok' => false, 'msg' => 'PO is not DRAFT — cannot hand over (current status: ' . $po['status'] . ').'];

        $counts = $db->mySelect(
            "SELECT COUNT(*) n, SUM(item_status = 'CONFIRMED') c FROM goods_client_po_items WHERE client_po_id = :p",
            [':p' => $poId]
        );
        $total = (int)($counts[0]['n'] ?? 0);
        $conf  = (int)($counts[0]['c'] ?? 0);
        if ($total === 0)                  return ['ok' => false, 'msg' => 'No items on the PO — add + confirm items first.'];
        if ($conf < $total)                return ['ok' => false, 'msg' => "Only $conf of $total items confirmed. Confirm all items before handover."];

        // 8Z-a delivery-block gate
        if (empty($po['incoterm']))        return ['ok' => false, 'msg' => 'Delivery: pick an Incoterm before handover.'];
        if ($po['delivery_mode'] === 'SINGLE_LOCATION' && trim((string)($po['primary_delivery_location'] ?? '')) === '') {
            return ['ok' => false, 'msg' => 'Delivery: primary location required (or switch to MULTI_LOCATION).'];
        }

        // 8Z-a overhead-review gate: at least one active overhead on the project
        $oh = $db->mySelect(
            "SELECT COUNT(*) n FROM c_project_overheads WHERE project_id = :p AND deleted = 0 AND active = 1",
            [':p' => (int)$po['project_id']]
        );
        $ohCount = (int)($oh[0]['n'] ?? 0);
        if ($ohCount === 0) {
            return ['ok' => false, 'msg' => 'Overheads: review the pool + confirm at least one (or seed from tender) before handover.'];
        }

        $procId = (int)$po['procurement_id'];
        $db->insertUpdate(
            "UPDATE goods_client_pos SET
                status              = 'SOURCING',
                handover_at         = NOW(),
                handover_by         = :u,
                handover_by_name    = :un,
                handover_notes      = :n,
                handover_to_user_id = :toid,
                edited_on           = NOW()
              WHERE c0d = :id",
            [':u' => $userId, ':un' => $userName, ':n' => trim($notes), ':toid' => $procId ?: null, ':id' => $poId]
        );

        try {
            if (!class_exists('Notifications')) require_once ROOT . '/app/models/Notifications.php';
            $N = new Notifications();
            $ref     = (string)($po['project_no'] ?? '') . ' · ' . (string)$po['client_po_no'];
            $subject = 'Client PO ready for sourcing: ' . $ref;
            $bodyBits = [
                $total . ' item(s)',
                'Incoterm ' . (string)$po['incoterm'],
                $ohCount . ' overhead(s)',
            ];
            if ($po['delivery_mode'] === 'SINGLE_LOCATION' && !empty($po['primary_delivery_location'])) {
                $bodyBits[] = 'Deliver to: ' . (string)$po['primary_delivery_location'];
            } elseif ($po['delivery_mode'] === 'MULTI_LOCATION') {
                $bodyBits[] = 'Multi-site delivery (see per-line)';
            }
            $body = implode(' · ', $bodyBits);
            if (trim($notes) !== '') $body .= "\nPC note: " . trim($notes);
            $url  = 'GoodsClientPos/view/' . $poId;

            if ($procId > 0) {
                $N->notify($procId, 'PO_READY_FOR_SOURCING', $subject, $body, $url, 'goods_client_pos', $poId, $userId, $userName);
            } else {
                $N->notifyGroup('Procurement', 'PO_READY_FOR_SOURCING', $subject, $body, $url, 'goods_client_pos', $poId, $userId, $userName);
            }
            if (!empty($po['project_manager_id'])) {
                $N->notify((int)$po['project_manager_id'], 'PO_HANDOVER_FYI', 'Handover done: ' . $ref, 'PC handed to Procurement.', $url, 'goods_client_pos', $poId, $userId, $userName);
            }
            $N->notifyGroup('Finance Manager', 'PO_HANDOVER_FYI', 'Handover done: ' . $ref, 'PC handed the Client PO to Procurement.', $url, 'goods_client_pos', $poId, $userId, $userName);
        } catch (\Throwable $e) { /* best-effort */ }

        return ['ok' => true, 'msg' => 'Handed over to ' . ($procId > 0 ? (string)$po['procurement_name'] : 'the Procurement group') . '.'];
    }

    public function removeItem(int $poId, int $itemId): array
    {
        $db = new DB();
        $po = $db->mySelect("SELECT status FROM goods_client_pos WHERE c0d = :id AND deleted = 0", [':id' => $poId]);
        if (empty($po))                    return ['ok' => false, 'msg' => 'Client PO not found.'];
        if ($po[0]['status'] !== 'DRAFT') return ['ok' => false, 'msg' => 'Client PO is not DRAFT — items locked.'];
        $db->insertUpdate(
            "DELETE FROM goods_client_po_items WHERE c0d = :id AND client_po_id = :p",
            [':id' => $itemId, ':p' => $poId]
        );
        $this->recomputeTotals($poId);
        return ['ok' => true];
    }

    public function recomputeTotals(int $poId): void
    {
        $db = new DB();
        $r = $db->mySelect(
            "SELECT COALESCE(SUM(line_total_sell),0) s, COALESCE(SUM(line_total_cog),0) c
               FROM goods_client_po_items WHERE client_po_id = :p",
            [':p' => $poId]
        );
        $sub = (float)($r[0]['s'] ?? 0);
        $cog = (float)($r[0]['c'] ?? 0);
        $po  = $db->mySelect("SELECT vat_rate FROM goods_client_pos WHERE c0d = :id", [':id' => $poId]);
        $vatRate = (float)($po[0]['vat_rate'] ?? 18);
        $vat = round($sub * $vatRate / 100, 2);
        $tot = round($sub + $vat, 2);
        $db->insertUpdate(
            "UPDATE goods_client_pos
                SET subtotal_sell = :s, subtotal_cog = :c, vat_amount = :v, total = :t, edited_on = NOW()
              WHERE c0d = :id",
            [':s' => $sub, ':c' => $cog, ':v' => $vat, ':t' => $tot, ':id' => $poId]
        );
    }

    /* ── Submit + terminal hook ─────────────────────────────────────────────── */

    /** CRM submits the onboarding into the GOODS_PROJECT_ONBOARDING chain. */
    public function submitOnboarding(int $projectId, int $userId, string $userName, string $userGroup): array
    {
        $db = new DB();
        $prj = $db->mySelect(
            "SELECT c0d, project_no, project_type, crm_status, migrated_flag
               FROM c_projects WHERE c0d = :p AND deleted = 0",
            [':p' => $projectId]
        );
        if (empty($prj))                                       return ['ok' => false, 'msg' => 'Project not found.'];
        $p = $prj[0];
        if ($p['project_type'] !== 'goods')                    return ['ok' => false, 'msg' => 'Only goods projects use this chain.'];
        if ((int)$p['migrated_flag'] === 1)                    return ['ok' => false, 'msg' => 'Legacy project — cannot submit new chain.'];
        if ($p['crm_status'] === 'APPROVED')                   return ['ok' => false, 'msg' => 'Already onboarded.'];
        if ($p['crm_status'] === 'SUBMITTED')                  return ['ok' => false, 'msg' => 'Already in approval chain.'];

        $po = $db->mySelect(
            "SELECT c0d, client_po_no, subtotal_sell FROM goods_client_pos WHERE project_id = :p AND status = 'DRAFT' AND deleted = 0 LIMIT 1",
            [':p' => $projectId]
        );
        if (empty($po))                                        return ['ok' => false, 'msg' => 'No DRAFT Client PO — add items first.'];
        $items = $db->mySelect("SELECT COUNT(*) n FROM goods_client_po_items WHERE client_po_id = :id", [':id' => (int)$po[0]['c0d']]);
        if ((int)$items[0]['n'] === 0)                         return ['ok' => false, 'msg' => 'Client PO has no items — add at least one.'];

        if (!class_exists('ApprovalChain')) require_once ROOT . '/app/models/ApprovalChain.php';
        $ac = new ApprovalChain();
        $res = $ac->submit([
            'chain_key'   => self::CHAIN_KEY,
            'entity_type' => self::ENTITY_TYPE,
            'entity_id'   => $projectId,
            'ref_label'   => $p['project_no'] . ' · ' . $po[0]['client_po_no'] . ' · ' . number_format((float)$po[0]['subtotal_sell']),
            'return_url'  => 'Projects/view/' . $projectId,
            'user_id'     => $userId,
            'user_name'   => $userName,
            'user_group'  => $userGroup,
            'comment'     => 'CRM submitted onboarding.',
        ]);
        if (!$res['ok']) return $res;
        $db->insertUpdate(
            "UPDATE c_projects SET crm_status = 'SUBMITTED', edited_by = :u, edited_on = NOW() WHERE c0d = :p",
            [':u' => $userId, ':p' => $projectId]
        );
        return $res;
    }

    /**
     * FinChainsController::postDecisionHook calls this on terminal APPROVED/DECLINED.
     * Reads the accumulated payload to stamp assignments on c_projects; on APPROVED
     * flips crm_status and confirms the Client PO.
     */
    public function onChainDecision(int $projectId, string $status, array $stagedPayload): array
    {
        $db = new DB();
        if ($status === 'DECLINED') {
            $db->insertUpdate(
                "UPDATE c_projects SET crm_status = 'DECLINED', edited_on = NOW() WHERE c0d = :p",
                [':p' => $projectId]
            );
            return ['ok' => true, 'msg' => 'Project onboarding declined.'];
        }
        if ($status !== 'APPROVED') return ['ok' => true, 'msg' => 'No terminal action for status ' . $status];

        $assign = $stagedPayload['assignments'] ?? [];
        $get = fn(string $slot) => (int)($assign[$slot]['user_id'] ?? 0);

        $updates = [
            ':p'   => $projectId,
            ':acc' => $get('ACCOUNTANT'),
            ':pro' => $get('PROCUREMENT'),
            ':pm'  => $get('PROJECT_MANAGER'),
            ':pc'  => $get('PROJECTS_COORDINATOR'),
        ];
        $db->insertUpdate(
            "UPDATE c_projects
                SET crm_status         = 'APPROVED',
                    accountant_id      = COALESCE(NULLIF(:acc,0), accountant_id),
                    procurement_id     = COALESCE(NULLIF(:pro,0), procurement_id),
                    project_manager_id = COALESCE(NULLIF(:pm ,0), project_manager_id),
                    pc_id              = COALESCE(NULLIF(:pc ,0), pc_id),
                    edited_on          = NOW()
              WHERE c0d = :p",
            $updates
        );

        // Lift Client PO from DRAFT → CONFIRMED so downstream sourcing can begin.
        $db->insertUpdate(
            "UPDATE goods_client_pos SET status = 'CONFIRMED', edited_on = NOW()
              WHERE project_id = :p AND status = 'DRAFT' AND deleted = 0",
            [':p' => $projectId]
        );

        return ['ok' => true, 'msg' => 'Project onboarded · assignments stamped · Client PO confirmed.'];
    }
}
