<?php
/**
 * TenderPricing — BOQ-style bid pricing per tender, versioned + approvable.
 *
 * Structure:
 *   Version (per tender, immutable snapshot once submitted) →
 *     Sections (grouping: Site prep / Equipment / Labour / etc) →
 *       Lines (item_ref, description, qty × uom × unit_cost × markup% → line_cost, line_price)
 *
 * Chain: TENDER_PRICING_APPROVAL — PM_REVIEW → FM_VERIFY → EXEC_APPROVE
 * Rollup: line.line_cost = qty × unit_cost, line.line_price = qty × unit_price (unit_price = unit_cost × (1 + markup%/100))
 *         section.sum_cost/sum_price = sum of active lines
 *         version.sum_cost/sum_price = sum of sections + prelims_amount
 *         version.margin_pct = (sum_price - sum_cost) / sum_price × 100
 */
class TenderPricing extends Model
{
    /* ── Version CRUD ──────────────────────────────────────────────────── */

    public function versionsFor(int $tenderId): array
    {
        $db = new DB();
        return $db->mySelect(
            "SELECT * FROM tender_pricing_versions
              WHERE tender_id = :t AND deleted = 0
              ORDER BY version_no DESC",
            [':t' => $tenderId]
        ) ?: [];
    }

    public function findVersion(int $versionId): ?array
    {
        $db = new DB();
        $r = $db->mySelect("SELECT * FROM tender_pricing_versions WHERE c0d = :id AND deleted = 0", [':id' => $versionId]);
        return $r[0] ?? null;
    }

    /** Latest APPROVED version for a tender (used for baseline snapshot to project). */
    public function latestApproved(int $tenderId): ?array
    {
        $db = new DB();
        $r = $db->mySelect(
            "SELECT * FROM tender_pricing_versions
              WHERE tender_id = :t AND deleted = 0 AND status = 'APPROVED'
              ORDER BY version_no DESC LIMIT 1",
            [':t' => $tenderId]
        );
        return $r[0] ?? null;
    }

    /** The active DRAFT (only one at a time per tender); null if none. */
    public function activeDraft(int $tenderId): ?array
    {
        $db = new DB();
        $r = $db->mySelect(
            "SELECT * FROM tender_pricing_versions
              WHERE tender_id = :t AND deleted = 0 AND status = 'DRAFT'
              ORDER BY version_no DESC LIMIT 1",
            [':t' => $tenderId]
        );
        return $r[0] ?? null;
    }

    /**
     * Create a new draft version. If $cloneFromVersionId is set, clone its sections + lines
     * (used when adjusting an APPROVED version → start a new draft based on it).
     */
    public function createVersion(int $tenderId, ?int $cloneFromVersionId, int $userId, string $userName): int
    {
        $db = new DB();
        $existing = $this->activeDraft($tenderId);
        if ($existing) throw new RuntimeException('A draft version already exists (#' . (int)$existing['version_no'] . '). Submit or delete it before creating another.');

        $maxNo = (int)($db->mySelect("SELECT COALESCE(MAX(version_no),0) AS n FROM tender_pricing_versions WHERE tender_id = :t", [':t' => $tenderId])[0]['n'] ?? 0);
        $nextNo = $maxNo + 1;

        // Slice 8R: fresh drafts inherit org-wide defaults from PricingDefaults (row edited by CRM/FM/Exec/Admin).
        // Cloned drafts still inherit from the seed version (that's the whole point of "clone from vN").
        $seed = $cloneFromVersionId ? $this->findVersion($cloneFromVersionId) : null;
        if (!class_exists('PricingDefaults')) require_once ROOT . '/app/models/PricingDefaults.php';
        $defaults = (new PricingDefaults())->get();

        // Slice 8S: procurement_mode + client_boq_visibility default from tender's project_type + org defaults.
        $tRow = $db->mySelect("SELECT project_type FROM tender_tracker WHERE c0d = :t", [':t' => $tenderId]);
        $tenderProjectType = $tRow[0]['project_type'] ?? 'goods';
        $defProcMode = ($tenderProjectType === 'goods')
            ? ($defaults['default_procurement_mode'] ?? 'LOCAL')
            : 'NA';
        $procMode = $seed['procurement_mode']       ?? $defProcMode;
        $boqVis   = $seed['client_boq_visibility']  ?? ($defaults['default_client_boq_visibility'] ?? 'ITEMS_AND_SECTIONS');

        $fb = $db->insertUpdate(
            "INSERT INTO tender_pricing_versions
                (tender_id, version_no, version_label, status, currency, vat_pct, contingency_pct, prelims_amount, default_markup_pct,
                 procurement_mode, client_boq_visibility,
                 sum_cost, sum_overhead, sum_price, sum_margin, margin_pct, notes, created_by, created_by_name, created_on)
             VALUES (:t, :vn, :vl, 'DRAFT', :cur, :vat, :cp, :pa, :dm,
                     :pmo, :cbv,
                     0, 0, 0, 0, 0, :nt, :u, :un, NOW())",
            [
                ':t'   => $tenderId,
                ':vn'  => $nextNo,
                ':vl'  => $seed ? 'v' . $nextNo . ' (from v' . $seed['version_no'] . ')' : 'v' . $nextNo,
                ':cur' => $seed['currency']           ?? $defaults['default_currency'],
                ':vat' => $seed['vat_pct']            ?? $defaults['default_vat_pct'],
                ':cp'  => $seed['contingency_pct']    ?? $defaults['default_contingency_pct'],
                ':pa'  => $seed['prelims_amount']     ?? $defaults['default_prelims_amount'],
                ':dm'  => $seed['default_markup_pct'] ?? $defaults['default_markup_pct'],
                ':pmo' => $procMode,
                ':cbv' => $boqVis,
                ':nt'  => $seed ? 'Cloned from v' . $seed['version_no'] : 'Inherited org defaults',
                ':u'   => $userId,
                ':un'  => $userName,
            ]
        );
        $newId = (int)($fb['last_id'] ?? 0);

        // Clone sections + lines
        if ($seed) {
            foreach ($this->sectionsFor((int)$seed['c0d']) as $s) {
                $sid = $this->addSection($newId, $tenderId, (string)$s['section_name'], (string)$s['notes'], $userId);
                foreach ($this->linesFor((int)$s['c0d']) as $ln) {
                    $this->addLine($sid, [
                        'item_ref'   => (string)$ln['item_ref'],
                        'description'=> (string)$ln['description'],
                        'qty'        => (float)$ln['qty'],
                        'uom'        => (string)$ln['uom'],
                        'unit_cost'  => (float)$ln['unit_cost'],
                        'markup_pct' => (float)$ln['markup_pct'],
                        'notes'      => (string)$ln['notes'],
                    ], $userId);
                }
            }
            // Slice 8S: clone overhead lines too so a v2 from v1 keeps its overhead pool.
            if (!class_exists('PricingVersionOverheads')) require_once ROOT . '/app/models/PricingVersionOverheads.php';
            $pvo = new PricingVersionOverheads();
            foreach ($pvo->listFor((int)$seed['c0d']) as $oh) {
                $pvo->add($newId, $tenderId, [
                    'category'     => (string)$oh['category'],
                    'label'        => (string)$oh['label'],
                    'amount'       => (float)$oh['amount'],
                    'display_mode' => (string)$oh['display_mode'],
                    'notes'        => (string)($oh['notes'] ?? ''),
                ], $userId, $userName);
            }
            $this->rollupVersion($newId);
        } else {
            // Slice 8S: fresh Goods draft auto-loads a quick-fill template so PC isn't staring at zero categories under time pressure.
            if ($tenderProjectType === 'goods') {
                if (!class_exists('PricingVersionOverheads')) require_once ROOT . '/app/models/PricingVersionOverheads.php';
                $tplKey = ($procMode === 'IMPORT') ? 'GOODS_IMPORT' : 'GOODS_LOCAL';
                (new PricingVersionOverheads())->loadTemplate($newId, $tenderId, $tplKey, $userId, $userName);
                $this->rollupVersion($newId);
            }
        }

        return $newId;
    }

    public function updateVersionHeader(int $versionId, array $d, int $userId): void
    {
        $v = $this->findVersion($versionId);
        if (!$v) throw new RuntimeException('Version not found.');
        if ($v['status'] !== 'DRAFT') throw new RuntimeException('Only DRAFT versions can be edited (v' . $v['version_no'] . ' is ' . $v['status'] . ').');

        // Slice 8N: pricing_mode + lumpsum fields
        $mode = strtoupper((string)($d['pricing_mode'] ?? $v['pricing_mode'] ?? 'PER_ITEM'));
        if (!in_array($mode, ['PER_ITEM','LUMPSUM'], true)) $mode = 'PER_ITEM';
        $lumpsumAmt   = (float)($d['lumpsum_amount'] ?? $v['lumpsum_amount'] ?? 0);
        $lumpsumNotes = (string)($d['lumpsum_notes'] ?? $v['lumpsum_notes'] ?? '');

        // Slice 8S: procurement_mode + client_boq_visibility (fallback = current value, not hard default)
        $procIncoming = strtoupper(trim((string)($d['procurement_mode'] ?? '')));
        $procMode = in_array($procIncoming, ['LOCAL','IMPORT','MIXED','NA'], true)
            ? $procIncoming
            : (string)($v['procurement_mode'] ?? 'NA');
        $boqIncoming = strtoupper(trim((string)($d['client_boq_visibility'] ?? '')));
        $boqVis = in_array($boqIncoming, ['LUMPSUM_ONLY','ITEMS_ONLY','ITEMS_AND_SECTIONS'], true)
            ? $boqIncoming
            : (string)($v['client_boq_visibility'] ?? 'ITEMS_AND_SECTIONS');

        $db = new DB();
        $db->insertUpdate(
            "UPDATE tender_pricing_versions SET
                version_label = :vl, vat_pct = :vat, contingency_pct = :cp,
                prelims_amount = :pa, default_markup_pct = :dm, notes = :nt,
                pricing_mode = :pm, lumpsum_amount = :la, lumpsum_notes = :ln,
                procurement_mode = :pmo, client_boq_visibility = :cbv,
                edited_by = :u, edited_on = NOW()
              WHERE c0d = :id",
            [
                ':vl'  => (string)($d['version_label'] ?? $v['version_label']),
                ':vat' => (float)($d['vat_pct'] ?? $v['vat_pct']),
                ':cp'  => (float)($d['contingency_pct'] ?? $v['contingency_pct']),
                ':pa'  => (float)($d['prelims_amount'] ?? $v['prelims_amount']),
                ':dm'  => (float)($d['default_markup_pct'] ?? $v['default_markup_pct']),
                ':nt'  => (string)($d['notes'] ?? $v['notes']),
                ':pm'  => $mode,
                ':la'  => $lumpsumAmt,
                ':ln'  => $lumpsumNotes,
                ':pmo' => $procMode,
                ':cbv' => $boqVis,
                ':u'   => $userId,
                ':id'  => $versionId,
            ]
        );
        $this->rollupVersion($versionId);
    }

    /* ── Section CRUD ──────────────────────────────────────────────────── */

    public function sectionsFor(int $versionId): array
    {
        $db = new DB();
        return $db->mySelect(
            "SELECT * FROM tender_pricing_sections WHERE version_id = :v AND deleted = 0 ORDER BY section_no",
            [':v' => $versionId]
        ) ?: [];
    }

    public function addSection(int $versionId, int $tenderId, string $name, string $notes, int $userId): int
    {
        $this->assertDraft($versionId);
        $db = new DB();
        $nextNo = (int)($db->mySelect("SELECT COALESCE(MAX(section_no),0)+1 AS n FROM tender_pricing_sections WHERE version_id = :v", [':v' => $versionId])[0]['n'] ?? 1);
        $fb = $db->insertUpdate(
            "INSERT INTO tender_pricing_sections (version_id, tender_id, section_no, section_name, notes, created_on)
             VALUES (:v, :t, :n, :nm, :nt, NOW())",
            [':v' => $versionId, ':t' => $tenderId, ':n' => $nextNo, ':nm' => $name, ':nt' => $notes]
        );
        return (int)($fb['last_id'] ?? 0);
    }

    public function updateSection(int $sectionId, array $d, int $userId): void
    {
        $sec = (new DB())->mySelect("SELECT version_id FROM tender_pricing_sections WHERE c0d = :id", [':id' => $sectionId]);
        if (empty($sec)) throw new RuntimeException('Section not found.');
        $this->assertDraft((int)$sec[0]['version_id']);
        $db = new DB();
        $db->insertUpdate(
            "UPDATE tender_pricing_sections SET section_name = :nm, notes = :nt WHERE c0d = :id",
            [':nm' => (string)($d['section_name'] ?? ''), ':nt' => (string)($d['notes'] ?? ''), ':id' => $sectionId]
        );
    }

    public function deleteSection(int $sectionId): void
    {
        $sec = (new DB())->mySelect("SELECT version_id FROM tender_pricing_sections WHERE c0d = :id", [':id' => $sectionId]);
        if (empty($sec)) return;
        $this->assertDraft((int)$sec[0]['version_id']);
        $db = new DB();
        $db->insertUpdate("UPDATE tender_pricing_sections SET deleted = 1 WHERE c0d = :id", [':id' => $sectionId]);
        $db->insertUpdate("UPDATE tender_pricing_lines SET deleted = 1 WHERE section_id = :id", [':id' => $sectionId]);
        $this->rollupVersion((int)$sec[0]['version_id']);
    }

    /* ── Line CRUD ─────────────────────────────────────────────────────── */

    public function linesFor(int $sectionId): array
    {
        $db = new DB();
        return $db->mySelect(
            "SELECT * FROM tender_pricing_lines WHERE section_id = :s AND deleted = 0 ORDER BY line_no",
            [':s' => $sectionId]
        ) ?: [];
    }

    public function addLine(int $sectionId, array $d, int $userId): int
    {
        $sec = (new DB())->mySelect("SELECT version_id, tender_id FROM tender_pricing_sections WHERE c0d = :id", [':id' => $sectionId]);
        if (empty($sec)) throw new RuntimeException('Section not found.');
        $versionId = (int)$sec[0]['version_id'];
        $tenderId  = (int)$sec[0]['tender_id'];
        $this->assertDraft($versionId);

        $db = new DB();
        $nextNo = (int)($db->mySelect("SELECT COALESCE(MAX(line_no),0)+1 AS n FROM tender_pricing_lines WHERE version_id = :v", [':v' => $versionId])[0]['n'] ?? 1);
        $qty       = (float)($d['qty'] ?? 1);
        $unitCost  = (float)($d['unit_cost'] ?? 0);
        $markupPct = (float)($d['markup_pct'] ?? 0);
        $unitPrice = round($unitCost * (1 + $markupPct / 100), 4);
        $lineCost  = round($qty * $unitCost, 2);
        $linePrice = round($qty * $unitPrice, 2);

        $fb = $db->insertUpdate(
            "INSERT INTO tender_pricing_lines
                (version_id, section_id, tender_id, line_no, item_ref, description, qty, uom,
                 unit_cost, markup_pct, unit_price, line_cost, line_price, notes, created_on)
             VALUES (:v, :s, :t, :n, :ir, :desc, :q, :uom, :uc, :mp, :up, :lc, :lp, :nt, NOW())",
            [
                ':v'   => $versionId, ':s' => $sectionId, ':t' => $tenderId, ':n' => $nextNo,
                ':ir'  => (string)($d['item_ref'] ?? ''),
                ':desc'=> (string)($d['description'] ?? ''),
                ':q'   => $qty, ':uom' => (string)($d['uom'] ?? ''),
                ':uc'  => $unitCost, ':mp' => $markupPct, ':up' => $unitPrice,
                ':lc'  => $lineCost, ':lp' => $linePrice,
                ':nt'  => (string)($d['notes'] ?? ''),
            ]
        );
        $this->rollupSection($sectionId);
        $this->rollupVersion($versionId);
        return (int)($fb['last_id'] ?? 0);
    }

    public function updateLine(int $lineId, array $d, int $userId): void
    {
        $db = new DB();
        $ln = $db->mySelect("SELECT version_id, section_id FROM tender_pricing_lines WHERE c0d = :id", [':id' => $lineId]);
        if (empty($ln)) throw new RuntimeException('Line not found.');
        $this->assertDraft((int)$ln[0]['version_id']);

        $qty       = (float)($d['qty'] ?? 1);
        $unitCost  = (float)($d['unit_cost'] ?? 0);
        $markupPct = (float)($d['markup_pct'] ?? 0);
        $unitPrice = round($unitCost * (1 + $markupPct / 100), 4);
        $lineCost  = round($qty * $unitCost, 2);
        $linePrice = round($qty * $unitPrice, 2);

        $db->insertUpdate(
            "UPDATE tender_pricing_lines SET
                item_ref = :ir, description = :desc, qty = :q, uom = :uom,
                unit_cost = :uc, markup_pct = :mp, unit_price = :up,
                line_cost = :lc, line_price = :lp, notes = :nt, edited_on = NOW()
              WHERE c0d = :id",
            [
                ':ir'  => (string)($d['item_ref'] ?? ''),
                ':desc'=> (string)($d['description'] ?? ''),
                ':q'   => $qty, ':uom' => (string)($d['uom'] ?? ''),
                ':uc'  => $unitCost, ':mp' => $markupPct, ':up' => $unitPrice,
                ':lc'  => $lineCost, ':lp' => $linePrice,
                ':nt'  => (string)($d['notes'] ?? ''),
                ':id'  => $lineId,
            ]
        );
        $this->rollupSection((int)$ln[0]['section_id']);
        $this->rollupVersion((int)$ln[0]['version_id']);
    }

    public function deleteLine(int $lineId): void
    {
        $db = new DB();
        $ln = $db->mySelect("SELECT version_id, section_id FROM tender_pricing_lines WHERE c0d = :id", [':id' => $lineId]);
        if (empty($ln)) return;
        $this->assertDraft((int)$ln[0]['version_id']);
        $db->insertUpdate("UPDATE tender_pricing_lines SET deleted = 1 WHERE c0d = :id", [':id' => $lineId]);
        $this->rollupSection((int)$ln[0]['section_id']);
        $this->rollupVersion((int)$ln[0]['version_id']);
    }

    /**
     * Slice 8M · Seed pricing lines for a DRAFT version from the tender_items list.
     * Adds them to $sectionId (or a default "Buyer's items" section if 0/missing).
     * Default cost = 0, markup = version.default_markup_pct. Idempotent by (item_ref, description):
     * an existing line with the same ref+desc is skipped.
     */
    public function seedFromItems(int $versionId, int $sectionId, int $userId): array
    {
        $v = $this->findVersion($versionId);
        if (!$v) return ['ok' => false, 'msg' => 'Version not found.'];
        $this->assertDraft($versionId);

        $tenderId = (int)$v['tender_id'];
        $db = new DB();
        $items = $db->mySelect(
            "SELECT * FROM tender_items WHERE tender_id = :t AND deleted = 0 ORDER BY line_no",
            [':t' => $tenderId]
        ) ?: [];
        if (empty($items)) return ['ok' => false, 'msg' => 'No tender items to seed from — capture the buyer\'s list first.'];

        if ($sectionId <= 0) {
            $existing = $db->mySelect(
                "SELECT c0d FROM tender_pricing_sections WHERE version_id = :v AND deleted = 0 ORDER BY section_no LIMIT 1",
                [':v' => $versionId]
            );
            $sectionId = !empty($existing)
                ? (int)$existing[0]['c0d']
                : $this->addSection($versionId, $tenderId, "Buyer's items", 'Seeded from tender items', $userId);
        }

        // Existing lines in this section — used to skip duplicates by (item_ref, description)
        $have = [];
        foreach ($this->linesFor($sectionId) as $ln) {
            $have[strtolower((string)$ln['item_ref']) . '||' . strtolower((string)$ln['description'])] = true;
        }

        $defMarkup = (float)$v['default_markup_pct'];
        $added = 0; $skipped = 0;
        foreach ($items as $it) {
            $k = strtolower((string)$it['item_ref']) . '||' . strtolower((string)$it['description']);
            if (isset($have[$k])) { $skipped++; continue; }
            $this->addLine($sectionId, [
                'item_ref'   => (string)$it['item_ref'],
                'description'=> (string)$it['description'],
                'qty'        => (float)$it['qty'],
                'uom'        => (string)$it['uom'],
                'unit_cost'  => 0,
                'markup_pct' => $defMarkup,
                'notes'      => (string)($it['spec_notes'] ?? ''),
            ], $userId);
            $added++;
        }
        return ['ok' => true, 'added' => $added, 'skipped' => $skipped, 'section_id' => $sectionId];
    }

    /**
     * Slice 8M · Full snapshot of a version for Vue-root hydration:
     * version header, sections, and lines-by-section, all in one payload.
     */
    public function snapshot(int $versionId): ?array
    {
        $v = $this->findVersion($versionId);
        if (!$v) return null;
        $secs = $this->sectionsFor($versionId);
        $lines = [];
        foreach ($secs as $s) $lines[(int)$s['c0d']] = $this->linesFor((int)$s['c0d']);
        if (!class_exists('PricingVersionOverheads')) require_once ROOT . '/app/models/PricingVersionOverheads.php';
        $overheads = (new PricingVersionOverheads())->listFor($versionId);
        return [
            'version'          => $v,
            'sections'         => $secs,
            'lines_by_section' => $lines,
            'overheads'        => $overheads,
        ];
    }

    /* ── Rollup ────────────────────────────────────────────────────────── */

    public function rollupSection(int $sectionId): void
    {
        $db = new DB();
        $db->insertUpdate(
            "UPDATE tender_pricing_sections s
                SET sum_cost  = IFNULL((SELECT SUM(line_cost)  FROM tender_pricing_lines WHERE section_id = s.c0d AND deleted = 0), 0),
                    sum_price = IFNULL((SELECT SUM(line_price) FROM tender_pricing_lines WHERE section_id = s.c0d AND deleted = 0), 0)
              WHERE s.c0d = :id",
            [':id' => $sectionId]
        );
    }

    public function rollupVersion(int $versionId): void
    {
        $db = new DB();
        // Slice 8S: sums now include the overhead pool.
        //   sum_overhead = total overhead across all display_modes
        //   sum_cost     = direct_cost (SUM sections) + sum_overhead  (INTERNAL TRUE COST)
        //   sum_price PER_ITEM  = SUM(section.sum_price) + prelims + DISTRIBUTED_oh + PRELIMS_LINE_oh
        //   sum_price LUMPSUM   = v.lumpsum_amount   (client sees one fixed number)
        //   INTERNAL_ONLY overheads never surface to client — they only bump sum_cost so true margin reflects reality.
        if (!class_exists('PricingVersionOverheads')) require_once ROOT . '/app/models/PricingVersionOverheads.php';
        $sums = (new PricingVersionOverheads())->sumsByMode($versionId);
        $distributedOh = (float)$sums['DISTRIBUTED'];
        $prelimsLineOh = (float)$sums['PRELIMS_LINE'];
        $totalOh       = $distributedOh + $prelimsLineOh + (float)$sums['INTERNAL_ONLY'];

        $db->insertUpdate(
            "UPDATE tender_pricing_versions v
                SET sum_cost     = IFNULL((SELECT SUM(sum_cost)  FROM tender_pricing_sections WHERE version_id = v.c0d AND deleted = 0), 0) + :toh,
                    sum_overhead = :toh2,
                    sum_price    = CASE
                                    WHEN v.pricing_mode = 'LUMPSUM' THEN v.lumpsum_amount
                                    ELSE IFNULL((SELECT SUM(sum_price) FROM tender_pricing_sections WHERE version_id = v.c0d AND deleted = 0), 0)
                                         + v.prelims_amount + :doh + :poh
                                   END,
                    sum_margin = 0, margin_pct = 0
              WHERE v.c0d = :id",
            [':id' => $versionId, ':toh' => $totalOh, ':toh2' => $totalOh, ':doh' => $distributedOh, ':poh' => $prelimsLineOh]
        );
        // Second pass to compute derived margin from just-updated sums.
        $db->insertUpdate(
            "UPDATE tender_pricing_versions v
                SET sum_margin = v.sum_price - v.sum_cost,
                    margin_pct = CASE WHEN v.sum_price > 0 THEN ROUND(((v.sum_price - v.sum_cost) / v.sum_price) * 100, 3) ELSE 0 END
              WHERE v.c0d = :id",
            [':id' => $versionId]
        );
    }

    /* ── Approval chain ─────────────────────────────────────────────────── */

    public function submitForApproval(int $versionId, int $userId, string $userName, string $userGroup): array
    {
        $v = $this->findVersion($versionId);
        if (!$v) return ['ok' => false, 'msg' => 'Version not found.'];
        if ($v['status'] !== 'DRAFT') return ['ok' => false, 'msg' => 'Only DRAFT versions can be submitted.'];
        if ((float)$v['sum_price'] <= 0) return ['ok' => false, 'msg' => 'Add at least one priced line before submitting.'];

        if (!class_exists('ApprovalChain')) require ROOT . '/app/models/ApprovalChain.php';
        $ac  = new ApprovalChain();
        $res = $ac->submit([
            'chain_key'    => 'TENDER_PRICING_APPROVAL',
            'entity_type'  => 'TENDER_PRICING',
            'entity_id'    => $versionId,
            'ref_label'    => 'Pricing v' . $v['version_no'] . ' · TZS ' . number_format((float)$v['sum_price']) . ' · margin ' . number_format((float)$v['margin_pct'], 1) . '%',
            'summary_json' => json_encode([
                'tender_id'  => (int)$v['tender_id'],
                'sum_cost'   => (float)$v['sum_cost'],
                'sum_price'  => (float)$v['sum_price'],
                'margin_pct' => (float)$v['margin_pct'],
            ]),
            'return_url'   => PROOT . 'Tenders/view/' . (int)$v['tender_id'] . '#tab-pricing',
            'user_id'      => $userId,
            'user_name'    => $userName,
            'user_group'   => $userGroup,
        ]);
        if (!empty($res['ok'])) {
            $db = new DB();
            $db->insertUpdate(
                "UPDATE tender_pricing_versions SET status = 'SUBMITTED', submitted_on = NOW(), approval_id = :aid WHERE c0d = :id",
                [':aid' => (int)$res['approval_id'], ':id' => $versionId]
            );
        }
        return $res;
    }

    /**
     * Hook: called from FinChainsController when TENDER_PRICING approval terminates.
     * On APPROVED: mark version APPROVED, retire prior APPROVED versions to SUPERSEDED.
     * On DECLINED: mark version DECLINED (user can then start a new draft).
     */
    public function onPricingDecision(int $versionId, string $finalStatus): void
    {
        $v = $this->findVersion($versionId);
        if (!$v) return;
        $db = new DB();
        if ($finalStatus === 'APPROVED') {
            // Retire any prior APPROVED versions for this tender
            $db->insertUpdate(
                "UPDATE tender_pricing_versions SET status = 'SUPERSEDED'
                  WHERE tender_id = :t AND status = 'APPROVED' AND c0d <> :id",
                [':t' => (int)$v['tender_id'], ':id' => $versionId]
            );
            $db->insertUpdate(
                "UPDATE tender_pricing_versions SET status = 'APPROVED', approved_on = NOW() WHERE c0d = :id",
                [':id' => $versionId]
            );
        } elseif ($finalStatus === 'DECLINED') {
            $db->insertUpdate(
                "UPDATE tender_pricing_versions SET status = 'DECLINED' WHERE c0d = :id",
                [':id' => $versionId]
            );
        }
    }

    /**
     * Apply an APPROVED version's sum_price to the tender header (submission_amount).
     * Also flips bid_price_source = 'PRICING_TAB'.
     */
    public function applyToTender(int $versionId): void
    {
        $v = $this->findVersion($versionId);
        if (!$v) throw new RuntimeException('Version not found.');
        if ($v['status'] !== 'APPROVED') throw new RuntimeException('Only APPROVED versions can be applied to the bid amount.');
        $db = new DB();
        $db->insertUpdate(
            "UPDATE tender_tracker SET submission_amount = :a, bid_price_source = 'PRICING_TAB',
                bid_price_source_note = CONCAT('From approved pricing v', :vn, ' on ', DATE_FORMAT(NOW(),'%Y-%m-%d'))
              WHERE c0d = :t",
            [':a' => (float)$v['sum_price'], ':vn' => (int)$v['version_no'], ':t' => (int)$v['tender_id']]
        );
    }

    /**
     * Snapshot the latest APPROVED pricing version's lines into `goods_client_po_items`
     * as a starting baseline for the newly-created project. Called from the auto-project-creation hook.
     * Silently skips if no APPROVED version exists (tender was priced elsewhere).
     * Returns count of lines inserted; also creates the parent `goods_client_pos` row if not present.
     */
    public function snapshotToProject(int $tenderId, int $projectId, int $userId, string $userName): int
    {
        $v = $this->latestApproved($tenderId);
        if (!$v) return 0;

        $db = new DB();
        // Look up project + tender headers (need customer_id, po numbering etc.)
        $projectRow = $db->mySelect("SELECT project_customer_id, project_customer, currency FROM c_projects WHERE c0d = :p AND deleted = 0", [':p' => $projectId]);
        if (empty($projectRow)) return 0;
        $custId    = (int)($projectRow[0]['project_customer_id'] ?? 0);
        $custName  = (string)($projectRow[0]['project_customer'] ?? '');
        $currency  = (string)($projectRow[0]['currency'] ?? 'TZS');

        // Find or create the client PO row for the project
        $poRow = $db->mySelect(
            "SELECT c0d FROM goods_client_pos WHERE project_id = :p AND linked_tender_id = :t AND deleted = 0 ORDER BY c0d LIMIT 1",
            [':p' => $projectId, ':t' => $tenderId]
        );
        $poId = (int)($poRow[0]['c0d'] ?? 0);

        if ($poId <= 0) {
            $seq = ((int)$db->mySelect("SELECT COUNT(*) n FROM goods_client_pos", [])[0]['n']) + 1;
            $poNo = 'PO-' . date('Y') . '-' . str_pad((string)$seq, 4, '0', STR_PAD_LEFT);
            $vatPct = (float)($v['vat_pct'] ?? 18);
            $sumSell = (float)$v['sum_price'];
            $sumCog  = (float)$v['sum_cost'];
            $vatAmt  = round($sumSell * $vatPct / 100, 2);
            $fb = $db->insertUpdate(
                "INSERT INTO goods_client_pos
                    (client_po_no, project_id, customer_id, customer_name, source_module, po_date,
                     currency, vat_rate, subtotal_sell, subtotal_cog, vat_amount, total,
                     status, notes, linked_tender_id, created_by, created_by_name, created_on)
                 VALUES (:pn, :p, :cid, :cn, 'goods', CURDATE(),
                         :cur, :vp, :ss, :sc, :va, :tot,
                         'DRAFT', :nt, :t, :u, :un, NOW())",
                [
                    ':pn' => $poNo, ':p' => $projectId, ':cid' => $custId, ':cn' => $custName,
                    ':cur' => $currency, ':vp' => $vatPct, ':ss' => $sumSell, ':sc' => $sumCog,
                    ':va' => $vatAmt, ':tot' => $sumSell + $vatAmt,
                    ':nt' => 'Baseline snapshot from approved pricing v' . $v['version_no'] . ' (tender #' . $tenderId . ')',
                    ':t' => $tenderId, ':u' => $userId, ':un' => $userName,
                ]
            );
            $poId = (int)($fb['last_id'] ?? 0);
            if ($poId <= 0) return 0;
        }

        // Copy the priced lines
        $rows = $db->mySelect(
            "SELECT l.*, s.section_name FROM tender_pricing_lines l
              JOIN tender_pricing_sections s ON s.c0d = l.section_id
             WHERE l.version_id = :v AND l.deleted = 0
             ORDER BY s.section_no, l.line_no",
            [':v' => (int)$v['c0d']]
        ) ?: [];

        $itemNo = 0; $inserted = 0;
        foreach ($rows as $ln) {
            $itemNo++;
            $db->insertUpdate(
                "INSERT INTO goods_client_po_items
                    (client_po_id, item_no, description, qty, unit, unit_price_sell, unit_price_cog,
                     line_total_sell, line_total_cog, notes, created_on)
                 VALUES (:po, :n, :desc, :q, :u, :ups, :upc, :lts, :ltc, :nt, NOW())",
                [
                    ':po'   => $poId,
                    ':n'    => $itemNo,
                    ':desc' => (string)($ln['item_ref'] ? '[' . $ln['item_ref'] . '] ' : '') . (string)$ln['description'],
                    ':q'    => (float)$ln['qty'],
                    ':u'    => (string)$ln['uom'],
                    ':ups'  => (float)$ln['unit_price'],
                    ':upc'  => (float)$ln['unit_cost'],
                    ':lts'  => (float)$ln['line_price'],
                    ':ltc'  => (float)$ln['line_cost'],
                    ':nt'   => 'From pricing v' . $v['version_no'] . ' section [' . $ln['section_name'] . '] line ' . $ln['line_no'],
                ]
            );
            $inserted++;
        }
        return $inserted;
    }

    /* ── Guards ────────────────────────────────────────────────────────── */

    private function assertDraft(int $versionId): void
    {
        $v = $this->findVersion($versionId);
        if (!$v) throw new RuntimeException('Version not found.');
        if ($v['status'] !== 'DRAFT') throw new RuntimeException('Only DRAFT versions can be edited (v' . $v['version_no'] . ' is ' . $v['status'] . ').');
    }
}
