<?php
/**
 * Tenders — internal tender tracker with 8-stage lifecycle.
 *
 * Sources: NEST_PUBLIC | PRIVATE_CLIENT | INVITATION | REFERRAL | WALK_IN
 *   - NEST_PUBLIC: source_ref = OCID, buyer_name = procuring entity (no customer_id)
 *   - PRIVATE_CLIENT: customer_id required, buyer_name auto-filled from customer
 *   - INVITATION / REFERRAL / WALK_IN: customer_id optional, buyer_name free-text
 *
 * Lifecycle (one-way, monotone): PREP → SUBMISSION → FOLLOW_UP → OPENING → NEGOTIATIONS →
 *   AWARDED → CONTRACT_SIGNING → EXECUTION
 *
 * Convert to project: only allowed once AWARDED or later. Sets c_projects.origin from source_type:
 *   NEST_PUBLIC → TENDER_PUBLIC; anything else → TENDER_BDM (private tender path).
 *   Sets crm_status = PENDING so the Onboarding chain still gates Ops.
 */
class Tenders extends Model
{
    public const STAGES = ['PREP','SUBMISSION','FOLLOW_UP','OPENING','NEGOTIATIONS','AWARDED','CONTRACT_SIGNING','EXECUTION'];
    public const SOURCES = ['NEST_PUBLIC','PRIVATE_CLIENT','INVITATION','REFERRAL','WALK_IN'];

    /**
     * List tenders with filters. When $filter['page_size'] > 0, applies LIMIT/OFFSET
     * for pagination; otherwise returns every matching row (used by Excel/PDF exports).
     */
    public function listAll(array $filter = []): array
    {
        $db = new DB();
        [$where, $params] = $this->buildFilter($filter);

        $sql = "SELECT t.*, cp.project_no, cp.crm_status,
                       TRIM(CONCAT(IFNULL(cu.fname,''),' ',IFNULL(cu.lname,''))) AS customer_display
                  FROM tender_tracker t
             LEFT JOIN c_projects cp ON cp.c0d = t.linked_project_id AND cp.deleted = 0
             LEFT JOIN customers cu  ON cu.c0d = t.customer_id AND cu.deleted = 0
                 WHERE " . implode(' AND ', $where) . "
                 ORDER BY t.deadline_date IS NULL, t.deadline_date ASC, t.created_on DESC";

        $pageSize = (int)($filter['page_size'] ?? 0);
        if ($pageSize > 0) {
            $page = max(1, (int)($filter['page'] ?? 1));
            $offset = ($page - 1) * $pageSize;
            // Inline LIMIT/OFFSET: PDO chokes on binding these on some MySQL drivers, and both are int-cast here.
            $sql .= sprintf(' LIMIT %d OFFSET %d', $pageSize, $offset);
        }
        return $db->mySelect($sql, $params) ?: [];
    }

    /** Same filters as listAll — returns the total row count for pagination. */
    public function countAll(array $filter = []): int
    {
        $db = new DB();
        [$where, $params] = $this->buildFilter($filter);
        $r = $db->mySelect(
            "SELECT COUNT(*) AS n FROM tender_tracker t WHERE " . implode(' AND ', $where),
            $params
        );
        return (int)($r[0]['n'] ?? 0);
    }

    /** Shared WHERE + params for listAll / countAll. */
    private function buildFilter(array $filter): array
    {
        $where  = ['t.deleted = 0'];
        $params = [];
        if (!empty($filter['stage']))        { $where[] = 't.tender_stage = :st';  $params[':st']  = $filter['stage']; }
        if (!empty($filter['status']))       { $where[] = 't.tender_status = :s';  $params[':s']   = $filter['status']; }
        if (!empty($filter['source']))       { $where[] = 't.source_type = :src';  $params[':src'] = $filter['source']; }
        if (!empty($filter['project_type'])) { $where[] = 't.project_type = :pt';  $params[':pt']  = $filter['project_type']; }
        if (!empty($filter['customer_id']))  { $where[] = 't.customer_id = :c';    $params[':c']   = (int)$filter['customer_id']; }
        if (!empty($filter['q'])) {
            $where[] = '(t.tender_no LIKE :q OR t.tender_title LIKE :q OR t.buyer_name LIKE :q OR t.source_ref LIKE :q)';
            $params[':q'] = '%' . $filter['q'] . '%';
        }
        return [$where, $params];
    }

    public function find(int $id): ?array
    {
        $db = new DB();
        $r = $db->mySelect(
            "SELECT t.*, cp.project_no, cp.crm_status,
                    TRIM(CONCAT(IFNULL(cu.fname,''),' ',IFNULL(cu.lname,''))) AS customer_display
               FROM tender_tracker t
          LEFT JOIN c_projects cp ON cp.c0d = t.linked_project_id AND cp.deleted = 0
          LEFT JOIN customers cu  ON cu.c0d = t.customer_id AND cu.deleted = 0
              WHERE t.c0d = :id AND t.deleted = 0",
            [':id' => $id]
        );
        return $r[0] ?? null;
    }

    public function stageHistory(int $tenderId): array
    {
        $db = new DB();
        return $db->mySelect(
            "SELECT * FROM tender_tracker_stages WHERE tender_id = :id ORDER BY changed_on DESC, c0d DESC",
            [':id' => $tenderId]
        ) ?: [];
    }

    public function bidders(int $tenderId): array
    {
        $db = new DB();
        return $db->mySelect(
            "SELECT * FROM tender_tracker_bidders WHERE tender_id = :id ORDER BY is_us DESC, ranking, bid_amount",
            [':id' => $tenderId]
        ) ?: [];
    }

    /* ── Requirements (document-type checklist per tender) ──────────────── */

    /**
     * All doc types grouped by file_cat, sorted for UI display.
     * Returns ['SUBMISSION' => [rows], 'FOLLOW_UP' => [rows], ...].
     */
    public function docTypesGrouped(): array
    {
        $db = new DB();
        $rows = $db->mySelect(
            "SELECT * FROM tender_doc_types WHERE is_active = 1 AND deleted = 0 ORDER BY file_cat, sort_order, type_label",
            []
        ) ?: [];
        $out = [];
        foreach ($rows as $r) { $out[$r['file_cat']][] = $r; }
        return $out;
    }

    /** Set of doc_type_ids currently required for a tender. */
    public function requiredDocTypeIds(int $tenderId): array
    {
        $db = new DB();
        $rows = $db->mySelect(
            "SELECT doc_type_id FROM tender_required_documents WHERE tender_id = :t AND deleted = 0",
            [':t' => $tenderId]
        ) ?: [];
        return array_map(fn($r) => (int)$r['doc_type_id'], $rows);
    }

    /**
     * Replace the required-doc-type set for this tender with exactly $docTypeIds.
     * Idempotent: inserts new, deletes removed.
     */
    public function setRequirements(int $tenderId, array $docTypeIds, int $userId): int
    {
        $db = new DB();
        $desired = array_unique(array_map('intval', $docTypeIds));

        $existing = $this->requiredDocTypeIds($tenderId);
        $toAdd    = array_diff($desired, $existing);
        $toDelete = array_diff($existing, $desired);

        foreach ($toAdd as $id) {
            $db->insertUpdate(
                "INSERT IGNORE INTO tender_required_documents (tender_id, doc_type_id, created_by, created_on)
                 VALUES (:t, :d, :u, NOW())",
                [':t' => $tenderId, ':d' => (int)$id, ':u' => $userId]
            );
        }
        if (!empty($toDelete)) {
            $placeholders = implode(',', array_fill(0, count($toDelete), '?'));
            $params = array_merge([$tenderId], array_values($toDelete));
            // Direct prepared statement (mySelect's positional binding path)
            $stmt = (new DB())->dBCxn()->prepare(
                "DELETE FROM tender_required_documents WHERE tender_id = ? AND doc_type_id IN ($placeholders)"
            );
            $stmt->execute($params);
        }

        return count($desired);
    }

    /** Tick every is_statutory=1 doc type on this tender (leaves user-picked non-statutory rows untouched). */
    public function applyStatutoryDefaults(int $tenderId, int $userId): int
    {
        $db = new DB();
        $statIds = $db->mySelect(
            "SELECT c0d FROM tender_doc_types WHERE is_statutory = 1 AND is_active = 1 AND deleted = 0",
            []
        ) ?: [];
        $ids = array_map(fn($r) => (int)$r['c0d'], $statIds);
        foreach ($ids as $id) {
            $db->insertUpdate(
                "INSERT IGNORE INTO tender_required_documents (tender_id, doc_type_id, created_by, created_on)
                 VALUES (:t, :d, :u, NOW())",
                [':t' => $tenderId, ':d' => $id, ':u' => $userId]
            );
        }
        return count($ids);
    }

    public function clearRequirements(int $tenderId): void
    {
        $db = new DB();
        $stmt = $db->dBCxn()->prepare("DELETE FROM tender_required_documents WHERE tender_id = ?");
        $stmt->execute([$tenderId]);
    }

    public function result(int $tenderId): ?array
    {
        $db = new DB();
        $r = $db->mySelect(
            "SELECT * FROM tender_tracker_results WHERE tender_id = :id",
            [':id' => $tenderId]
        );
        return $r[0] ?? null;
    }

    public function create(array $d, int $userId, string $userName): int
    {
        $db = new DB();
        $sourceType = (string)($d['source_type'] ?? 'PRIVATE_CLIENT');
        if (!in_array($sourceType, self::SOURCES, true)) throw new InvalidArgumentException('Invalid source_type.');

        // Enforce PRIVATE_CLIENT requires customer_id
        $customerId = (int)($d['customer_id'] ?? 0);
        if ($sourceType === 'PRIVATE_CLIENT' && $customerId <= 0) {
            throw new InvalidArgumentException('Private-client tenders require a customer.');
        }

        // Auto-fill buyer_name from customer if not provided
        $buyerName = trim((string)($d['buyer_name'] ?? ''));
        if ($buyerName === '' && $customerId > 0) {
            $c = $db->mySelect("SELECT fname, lname FROM customers WHERE c0d = :id", [':id' => $customerId]);
            if (!empty($c)) $buyerName = trim(($c[0]['fname'] ?? '') . ' ' . ($c[0]['lname'] ?? ''));
        }
        if ($buyerName === '') throw new InvalidArgumentException('buyer_name required.');

        // Auto tender_no if not provided
        $tenderNo = trim((string)($d['tender_no'] ?? ''));
        if ($tenderNo === '') {
            $seq = ((int)$db->mySelect("SELECT COUNT(*) n FROM tender_tracker", [])[0]['n']) + 1;
            $tenderNo = 'T-' . date('Y') . '-' . str_pad((string)$seq, 4, '0', STR_PAD_LEFT);
        }

        $fb = $db->insertUpdate(
            "INSERT INTO tender_tracker
                (tender_no, tender_title, tender_description, source_type, source_ref, source_url,
                 customer_id, buyer_name, tender_industry, project_type, region, district,
                 date_received, deadline_date, deadline_time, estimated_value, currency,
                 tender_stage, tender_status, submission_method, linked_lead_id,
                 created_by, created_by_name, created_on)
             VALUES (:no, :ti, :desc, :src, :sref, :surl,
                     :cid, :bn, :ind, :pt, :rg, :ds,
                     :dr, :dd, :dt, :ev, 'TZS',
                     :stg, 'OPEN', :sm, :ll,
                     :u, :un, NOW())",
            [
                ':no'   => $tenderNo,
                ':ti'   => (string)($d['tender_title'] ?? ''),
                ':desc' => (string)($d['tender_description'] ?? ''),
                ':src'  => $sourceType,
                ':sref' => (string)($d['source_ref'] ?? ''),
                ':surl' => (string)($d['source_url'] ?? ''),
                ':cid'  => $customerId ?: null,
                ':bn'   => $buyerName,
                ':ind'  => (string)($d['tender_industry'] ?? ''),
                ':pt'   => (string)($d['project_type'] ?? 'goods'),
                ':rg'   => (string)($d['region'] ?? ''),
                ':ds'   => (string)($d['district'] ?? ''),
                ':dr'   => (string)($d['date_received'] ?? date('Y-m-d')),
                ':dd'   => !empty($d['deadline_date']) ? (string)$d['deadline_date'] : null,
                ':dt'   => !empty($d['deadline_time']) ? (string)$d['deadline_time'] : null,
                ':ev'   => (float)($d['estimated_value'] ?? 0),
                ':stg'  => (string)($d['tender_stage'] ?? 'PREP'),
                ':sm'   => (string)($d['submission_method'] ?? ''),
                ':ll'   => (int)($d['linked_lead_id'] ?? 0) ?: null,
                ':u'    => $userId,
                ':un'   => $userName,
            ]
        );
        $tenderId = (int)($fb['last_id'] ?? 0);

        // Seed stage-history with initial stage
        $db->insertUpdate(
            "INSERT INTO tender_tracker_stages (tender_id, stage_from, stage_to, changed_by, changed_by_name, notes)
             VALUES (:id, NULL, :s, :u, :un, 'Tender created')",
            [':id' => $tenderId, ':s' => (string)($d['tender_stage'] ?? 'PREP'), ':u' => $userId, ':un' => $userName]
        );

        return $tenderId;
    }

    public function update(int $id, array $d, int $userId): void
    {
        $db = new DB();
        $db->insertUpdate(
            "UPDATE tender_tracker
                SET tender_title = :ti, tender_description = :desc, tender_industry = :ind,
                    project_type = :pt, region = :rg, district = :ds,
                    deadline_date = :dd, deadline_time = :dt, estimated_value = :ev,
                    submission_method = :sm, submission_amount = :sa,
                    edited_by = :u, edited_on = NOW()
              WHERE c0d = :id",
            [
                ':ti' => (string)($d['tender_title'] ?? ''), ':desc' => (string)($d['tender_description'] ?? ''),
                ':ind' => (string)($d['tender_industry'] ?? ''), ':pt' => (string)($d['project_type'] ?? 'goods'),
                ':rg' => (string)($d['region'] ?? ''), ':ds' => (string)($d['district'] ?? ''),
                ':dd' => !empty($d['deadline_date']) ? (string)$d['deadline_date'] : null,
                ':dt' => !empty($d['deadline_time']) ? (string)$d['deadline_time'] : null,
                ':ev' => (float)($d['estimated_value'] ?? 0),
                ':sm' => (string)($d['submission_method'] ?? ''),
                ':sa' => (float)($d['submission_amount'] ?? 0),
                ':u' => $userId, ':id' => $id,
            ]
        );
    }

    /**
     * Slice 8P · Save Details-tab fields inline (partial update).
     * Only the fields shown on the Details tab are editable — tender_no,
     * project_type, customer_id, buyer_name stay locked (they're set at intake
     * and drive downstream flows that shouldn't shift under a running tender).
     */
    public function saveDetails(int $tenderId, array $d, int $userId): void
    {
        $t = $this->find($tenderId);
        if (!$t) throw new RuntimeException('Tender not found.');

        $source = (string)($d['source_type'] ?? $t['source_type']);
        if (!in_array($source, self::SOURCES, true)) $source = (string)$t['source_type'];

        $db = new DB();
        $db->insertUpdate(
            "UPDATE tender_tracker SET
                tender_title       = :tt,
                tender_description = :td,
                tender_industry    = :ti,
                region             = :rg,
                district           = :ds,
                source_type        = :src,
                source_ref         = :sr,
                source_url         = :su,
                submission_method  = :sm,
                edited_by = :u, edited_on = NOW()
             WHERE c0d = :id",
            [
                ':tt'  => (string)($d['tender_title']       ?? $t['tender_title']),
                ':td'  => (string)($d['tender_description'] ?? $t['tender_description']),
                ':ti'  => (string)($d['tender_industry']    ?? $t['tender_industry']),
                ':rg'  => (string)($d['region']             ?? $t['region']),
                ':ds'  => (string)($d['district']           ?? $t['district']),
                ':src' => $source,
                ':sr'  => (string)($d['source_ref']         ?? $t['source_ref']),
                ':su'  => (string)($d['source_url']         ?? $t['source_url']),
                ':sm'  => (string)($d['submission_method']  ?? $t['submission_method']),
                ':u'   => $userId,
                ':id'  => $tenderId,
            ]
        );
    }

    /* ── Submission details ────────────────────────────────────────────── */

    /**
     * Save submission-stage details on the tender header. All fields optional
     * (users fill progressively). If the tender is currently in PREP, this
     * call auto-advances it to SUBMISSION (matches hqdspera behavior).
     */
    public function saveSubmissionDetails(int $tenderId, array $d, int $userId, string $userName): void
    {
        $t = $this->find($tenderId);
        if (!$t) throw new RuntimeException('Tender not found.');

        // Slice 8O: when source = MANUAL, cost + margin auto-compute submission_amount so the
        // audit trail can reconstruct how the number was reached. When source = PRICING_TAB,
        // manual_cost/manual_margin_pct are ignored (pricing tab is the source of truth).
        $bps = !empty($d['bid_price_source']) ? (string)$d['bid_price_source'] : 'MANUAL';
        $manualCost   = (float)($d['manual_cost']       ?? 0);
        $manualMargin = (float)($d['manual_margin_pct'] ?? 0);
        $subAmount    = (float)($d['submission_amount'] ?? 0);
        if ($bps === 'MANUAL' && $manualCost > 0) {
            // Derive from cost + margin; do not trust a stale submission_amount posted alongside.
            $subAmount = round($manualCost * (1 + $manualMargin / 100), 2);
        }

        $db = new DB();
        $db->insertUpdate(
            "UPDATE tender_tracker SET
                submission_amount        = :sa,
                submission_method        = :sm,
                submission_date          = :sd,
                submission_time          = :st,
                submission_validity_days = :vd,
                submission_follow_up_date= :fud,
                submission_remarks       = :rm,
                submission_envelope_scheme = :es,
                submission_copies_original = :co,
                submission_copies_duplicate= :cd,
                submission_sealing_method  = :sl,
                bid_price_source           = :bps,
                bid_price_source_note      = :bpn,
                manual_cost                = :mc,
                manual_margin_pct          = :mm,
                edited_by = :u, edited_on = NOW()
             WHERE c0d = :id",
            [
                ':sa'  => $subAmount,
                ':sm'  => (string)($d['submission_method'] ?? ''),
                ':sd'  => !empty($d['submission_date']) ? (string)$d['submission_date'] : null,
                ':st'  => !empty($d['submission_time']) ? (string)$d['submission_time'] : null,
                ':vd'  => (isset($d['submission_validity_days']) && $d['submission_validity_days'] !== '') ? (int)$d['submission_validity_days'] : null,
                ':fud' => !empty($d['submission_follow_up_date']) ? (string)$d['submission_follow_up_date'] : null,
                ':rm'  => (string)($d['submission_remarks'] ?? ''),
                ':es'  => !empty($d['submission_envelope_scheme']) ? (string)$d['submission_envelope_scheme'] : null,
                ':co'  => isset($d['submission_copies_original']) && $d['submission_copies_original'] !== '' ? (int)$d['submission_copies_original'] : null,
                ':cd'  => isset($d['submission_copies_duplicate']) && $d['submission_copies_duplicate'] !== '' ? (int)$d['submission_copies_duplicate'] : null,
                ':sl'  => !empty($d['submission_sealing_method']) ? (string)$d['submission_sealing_method'] : null,
                ':bps' => $bps,
                ':bpn' => (string)($d['bid_price_source_note'] ?? ''),
                ':mc'  => $manualCost,
                ':mm'  => $manualMargin,
                ':u'   => $userId,
                ':id'  => $tenderId,
            ]
        );

        // Auto-advance PREP → SUBMISSION on first save
        if ($t['tender_stage'] === 'PREP') {
            try { $this->advanceStage($tenderId, 'SUBMISSION', 'Auto-advanced on first submission save', $userId, $userName); }
            catch (\Throwable $e) { /* ignore if already there */ }
        }
    }

    /** Advance to a later stage. Idempotent when already at target; reject only true backward moves. */
    public function advanceStage(int $tenderId, string $newStage, string $notes, int $userId, string $userName): void
    {
        if (!in_array($newStage, self::STAGES, true)) throw new InvalidArgumentException('Invalid stage.');
        $t = $this->find($tenderId);
        if (!$t) throw new RuntimeException('Tender not found.');
        $curIdx = array_search($t['tender_stage'], self::STAGES, true);
        $newIdx = array_search($newStage, self::STAGES, true);
        if ($newIdx === $curIdx) return; // already there — no-op
        if ($newIdx < $curIdx) throw new RuntimeException('Stage can only advance forward (current: ' . $t['tender_stage'] . ').');

        // Slice 8X: Execution requires a signed contract on file.
        if ($newStage === 'EXECUTION') {
            $sign = (new DB())->mySelect("SELECT c0d FROM tender_contract_signing WHERE tender_id = :t", [':t' => $tenderId]);
            if (empty($sign)) {
                throw new RuntimeException('Cannot advance to Execution — Contract Signing has not been recorded yet.');
            }
        }

        $db = new DB();
        $db->insertUpdate(
            "UPDATE tender_tracker SET tender_stage = :s, edited_by = :u, edited_on = NOW() WHERE c0d = :id",
            [':s' => $newStage, ':u' => $userId, ':id' => $tenderId]
        );
        $db->insertUpdate(
            "INSERT INTO tender_tracker_stages (tender_id, stage_from, stage_to, changed_by, changed_by_name, notes)
             VALUES (:id, :sf, :st, :u, :un, :n)",
            [':id' => $tenderId, ':sf' => $t['tender_stage'], ':st' => $newStage, ':u' => $userId, ':un' => $userName, ':n' => $notes]
        );

        // Slice 7: handoff — hitting AWARDED means Exec should expect a contract-approval chain shortly.
        if ($newStage === 'AWARDED') {
            try {
                if (!class_exists('Notifications')) require_once ROOT . '/app/models/Notifications.php';
                $ref   = (string)($t['tender_no']    ?? ('#' . $tenderId));
                $title = (string)($t['tender_title'] ?? '');
                (new Notifications())->notifyGroup(
                    'Executive', 'TENDER_AWARDED',
                    'Awarded: ' . $ref . ' — contract approval will follow',
                    $title . ' — CRM/FM will submit the contract chain for your final approval.',
                    'Tenders/view/' . $tenderId . '#tab-contract',
                    'tender_tracker', $tenderId, $userId, $userName
                );
            } catch (\Throwable $e) { /* best-effort */ }
        }
    }

    public function addBidder(int $tenderId, string $name, bool $isUs, float $amount, ?int $ranking, string $notes, int $userId): int
    {
        $db = new DB();
        $fb = $db->insertUpdate(
            "INSERT INTO tender_tracker_bidders (tender_id, bidder_name, is_us, bid_amount, ranking, notes, created_by, created_on)
             VALUES (:t, :n, :u, :a, :r, :nt, :cb, NOW())",
            [':t' => $tenderId, ':n' => $name, ':u' => $isUs ? 1 : 0, ':a' => $amount, ':r' => $ranking, ':nt' => $notes, ':cb' => $userId]
        );
        return (int)($fb['last_id'] ?? 0);
    }

    public function recordResult(int $tenderId, string $status, string $date, string $winner, ?float $winAmount, string $reason, string $lessons, int $userId, string $userName, ?string $reasonCategory = null): void
    {
        if (!in_array($status, ['WON','LOST','CANCELLED','WITHDRAWN'], true)) throw new InvalidArgumentException('Invalid result status.');
        // Slice 8W: reason_category must match status prefix (e.g., WON_BEST_PRICE only for status WON).
        if ($reasonCategory !== null && $reasonCategory !== '') {
            $rc = strtoupper($reasonCategory);
            if (!str_starts_with($rc, $status . '_')) {
                throw new InvalidArgumentException("Reason category '{$rc}' does not match status '{$status}'.");
            }
            $reasonCategory = $rc;
        } else {
            $reasonCategory = null;
        }

        $wasWon = false;
        $tBefore = $this->find($tenderId);
        if ($tBefore && $tBefore['tender_status'] !== 'WON' && $status === 'WON') $wasWon = true;
        $db = new DB();
        // upsert result row
        $existing = $this->result($tenderId);
        if ($existing) {
            $db->insertUpdate(
                "UPDATE tender_tracker_results
                    SET result_status = :s, result_date = :d, winning_bidder = :w, winning_amount = :wa,
                        reason = :r, reason_category = :rc, lessons = :l
                  WHERE tender_id = :t",
                [':s' => $status, ':d' => $date, ':w' => $winner, ':wa' => $winAmount, ':r' => $reason, ':rc' => $reasonCategory, ':l' => $lessons, ':t' => $tenderId]
            );
        } else {
            $db->insertUpdate(
                "INSERT INTO tender_tracker_results (tender_id, result_status, result_date, winning_bidder, winning_amount, reason, reason_category, lessons, created_by, created_by_name, created_on)
                 VALUES (:t, :s, :d, :w, :wa, :r, :rc, :l, :u, :un, NOW())",
                [':t' => $tenderId, ':s' => $status, ':d' => $date, ':w' => $winner, ':wa' => $winAmount, ':r' => $reason, ':rc' => $reasonCategory, ':l' => $lessons, ':u' => $userId, ':un' => $userName]
            );
        }

        // Reflect on the tender header
        $db->insertUpdate(
            "UPDATE tender_tracker SET tender_status = :s, edited_by = :u, edited_on = NOW() WHERE c0d = :t",
            [':s' => $status, ':u' => $userId, ':t' => $tenderId]
        );

        // Slice 8W: on WON, auto-advance the tender stage to AWARDED if it isn't already at or past it.
        if ($status === 'WON' && $tBefore) {
            $stageOrder = ['PREP'=>1,'SUBMISSION'=>2,'FOLLOW_UP'=>3,'OPENING'=>4,'NEGOTIATIONS'=>5,'AWARDED'=>6,'CONTRACT_SIGNING'=>7,'EXECUTION'=>8];
            $curOrd = $stageOrder[$tBefore['tender_stage']] ?? 0;
            if ($curOrd < 6) {
                try { $this->advanceStage($tenderId, 'AWARDED', 'Auto-advance on WON result', $userId, $userName); }
                catch (\Throwable $e) { /* ignore if already advanced */ }
            }
        }

        // Slice 7: handoff — WON tender pings every FM so they know contract terms are needed.
        if ($wasWon && $tBefore) {
            try {
                if (!class_exists('Notifications')) require_once ROOT . '/app/models/Notifications.php';
                $ref = (string)($tBefore['tender_no'] ?? ('#' . $tenderId));
                $title = (string)($tBefore['tender_title'] ?? '');
                (new Notifications())->notifyGroup(
                    'Finance Manager', 'TENDER_WON',
                    'Contract terms needed for ' . $ref,
                    $title . ' — capture contract terms then submit the contract-approval chain.',
                    'Tenders/view/' . $tenderId . '#tab-contract',
                    'tender_tracker', $tenderId, $userId, $userName
                );
            } catch (\Throwable $e) { /* notifications are best-effort */ }
        }
    }

    /**
     * Convert a WON tender to a c_projects row.
     * Only allowed once stage is AWARDED or later.
     * Sets origin from source_type (NEST_PUBLIC → TENDER_PUBLIC, else TENDER_BDM).
     */
    public function convertToProject(int $tenderId, int $userId, string $userName, array $seats = []): int
    {
        $t = $this->find($tenderId);
        if (!$t) throw new RuntimeException('Tender not found.');

        $curIdx = array_search($t['tender_stage'], self::STAGES, true);
        $awardIdx = array_search('AWARDED', self::STAGES, true);
        if ($curIdx < $awardIdx) throw new RuntimeException('Convert allowed only after AWARDED (current: ' . $t['tender_stage'] . ').');
        if ($t['tender_status'] !== 'WON') throw new RuntimeException('Only WON tenders convert (current status: ' . $t['tender_status'] . ').');
        if ((int)$t['linked_project_id'] > 0) throw new RuntimeException('Tender already converted to project #' . (int)$t['linked_project_id'] . '.');

        $db = new DB();
        $seq = ((int)$db->mySelect("SELECT COUNT(*) n FROM c_projects", [])[0]['n']) + 1;
        $projectNo = 'P-' . date('Y') . '-' . str_pad((string)$seq, 4, '0', STR_PAD_LEFT);

        $origin = $t['source_type'] === 'NEST_PUBLIC' ? 'TENDER_PUBLIC' : 'TENDER_BDM';

        // Prefer the Exec-approved contract_terms.contract_value; else submission_amount; else estimated_value
        $terms = $this->contractTerms($tenderId);
        if ($terms && (float)$terms['contract_value'] > 0) {
            $contractValue = (float)$terms['contract_value'];
        } else {
            $contractValue = (float)($t['submission_amount'] > 0 ? $t['submission_amount'] : $t['estimated_value']);
        }

        // Seats staged across approval steps (Slice 5). Slot names come from fin_chain_steps.required_assignments.
        $acct  = $seats['ACCOUNTANT']       ?? [];
        $proc  = $seats['PROCUREMENT']      ?? [];
        $pmS   = $seats['PROJECT_MANAGER']  ?? [];

        $fb = $db->insertUpdate(
            "INSERT INTO c_projects
                (project_no, project_name, project_type, project_customer_id, project_customer,
                 project_tender_reference, origin, contract_value, currency, region, district,
                 project_manager_id, project_manager_name,
                 accountant_id, accountant_name,
                 procurement_id, procurement_name,
                 crm_status, project_status, project_lifecycle_stage,
                 created_by, created_on)
             VALUES (:pn, :nm, :pt, :cid, :cn,
                     :tr, :o, :cv, 'TZS', :rg, :ds,
                     :pmid, :pmnm,
                     :aid,  :anm,
                     :prid, :prnm,
                     'PENDING', 'ACTIVE', 'ONBOARDING',
                     :u, NOW())",
            [
                ':pn'  => $projectNo,
                ':nm'  => $t['tender_title'],
                ':pt'  => $t['project_type'],
                ':cid' => (int)($t['customer_id'] ?? 0),
                ':cn'  => $t['buyer_name'],
                ':tr'  => (string)($t['tender_no'] ?? ''),
                ':o'   => $origin,
                ':cv'  => $contractValue,
                ':rg'  => (string)($t['region'] ?? ''),
                ':ds'  => (string)($t['district'] ?? ''),
                ':pmid'=> (int)($pmS['user_id']   ?? 0), ':pmnm' => (string)($pmS['user_name']   ?? ''),
                ':aid' => (int)($acct['user_id']  ?? 0), ':anm'  => (string)($acct['user_name']  ?? ''),
                ':prid'=> (int)($proc['user_id']  ?? 0), ':prnm' => (string)($proc['user_name']  ?? ''),
                ':u'   => $userId,
            ]
        );
        $projectId = (int)($fb['last_id'] ?? 0);

        // Log the seat assignments as the project's first role-changes for auditability
        foreach (['PROJECT_MANAGER' => $pmS, 'ACCOUNTANT' => $acct, 'PROCUREMENT' => $proc] as $slot => $u) {
            if ((int)($u['user_id'] ?? 0) <= 0) continue;
            $db->insertUpdate(
                "INSERT INTO c_projects_role_changes
                    (project_id, role_slot, old_user_id, old_user_name, new_user_id, new_user_name,
                     changed_by, changed_by_name, reason, changed_on)
                 VALUES (:p, :s, 0, '', :nid, :nn, :cb, :cbn, :r, NOW())",
                [
                    ':p'  => $projectId, ':s' => $slot,
                    ':nid'=> (int)$u['user_id'], ':nn' => (string)($u['user_name'] ?? ''),
                    ':cb' => (int)($u['assigned_by'] ?? $userId),
                    ':cbn'=> (string)($u['assigned_by_name'] ?? $userName),
                    ':r'  => 'Assigned during TENDER_CONTRACT_APPROVAL step ' . (string)($u['step_key'] ?? ''),
                ]
            );
        }

        // Link back + auto-advance stage if we're at AWARDED
        $db->insertUpdate(
            "UPDATE tender_tracker SET linked_project_id = :pid, edited_by = :u, edited_on = NOW() WHERE c0d = :id",
            [':pid' => $projectId, ':u' => $userId, ':id' => $tenderId]
        );
        // NOTE: We deliberately do NOT auto-advance to EXECUTION here.
        // The signing event (Tenders::recordContractSigning) is the trigger for CONTRACT_SIGNING → EXECUTION.
        // Convert-to-project is a *project provisioning* step, not a lifecycle-progression step.

        // Slice 6: seed the per-project-type starter checklist for the assigned PM.
        try {
            if (!class_exists('ProjectStarter')) require_once ROOT . '/app/models/ProjectStarter.php';
            (new ProjectStarter())->seed($projectId, (string)$t['project_type'], (int)($pmS['user_id'] ?? 0), (string)($pmS['user_name'] ?? ''));
        } catch (\Throwable $e) { /* starter tasks are best-effort; project itself is provisioned */ }

        // Slice 8c-Spine: for works projects, also snapshot the approved tender pricing into a
        // contract BOQ (works_boqs + works_boq_items). Same source (tender_pricing_lines) as the
        // goods_client_pos snapshot; different consumer (measurement + IPC rather than procurement).
        if ((string)$t['project_type'] === 'works') {
            try {
                if (!class_exists('WorksBoq')) require_once ROOT . '/app/models/WorksBoq.php';
                (new WorksBoq())->snapshotFromTenderPricing($tenderId, $projectId, $userId, $userName);
            } catch (\Throwable $e) { /* BOQ snapshot is best-effort; project itself is provisioned */ }
        }

        // Slice 8S-10: for services projects, pre-populate a DRAFT service_contract from the
        // bid-time answers CRM captured on the Items tab so PC doesn't re-type the checklist.
        // Best-effort — a missing spec just means the contract stays empty for PC to fill in.
        if ((string)$t['project_type'] === 'services') {
            try {
                if (!class_exists('TenderServiceSpec')) require_once ROOT . '/app/models/TenderServiceSpec.php';
                if (!class_exists('ServiceContracts'))  require_once ROOT . '/app/models/ServiceContracts.php';
                $payload = (new TenderServiceSpec())->toContractPayload(
                    $tenderId, $projectId,
                    (int)($t['customer_id'] ?? 0),
                    (string)($t['buyer_name'] ?? '')
                );
                if (!empty($payload)) {
                    $payload['contract_value'] = $contractValue;
                    (new ServiceContracts())->saveHeader($payload, $userId, $userName);
                }
            } catch (\Throwable $e) { /* best-effort — project provisioned regardless */ }
        }

        // Slice 7: notify the CRM submitter + all three fresh seats that provisioning is complete.
        try {
            if (!class_exists('Notifications')) require_once ROOT . '/app/models/Notifications.php';
            $n = new Notifications();
            $subject = 'Project ' . $projectNo . ' provisioned from tender ' . (string)($t['tender_no'] ?? ('#' . $tenderId));
            $body    = (string)($t['tender_title'] ?? '') . ' — value ' . number_format($contractValue) . ' TZS';
            $url     = 'Projects/view/' . $projectId;
            // CRM submitter of the tender
            $subId   = (int)($t['created_by'] ?? 0);
            if ($subId > 0) $n->notify($subId, 'PROJECT_PROVISIONED', $subject, $body, $url, 'c_projects', $projectId, $userId, $userName);
            // Assigned seats (PM already sees this via ProjectStarter notifications below; still ping them explicitly for the "project is yours" signal)
            foreach ([$pmS, $acct, $proc] as $seat) {
                $sid = (int)($seat['user_id'] ?? 0);
                if ($sid > 0 && $sid !== $subId) $n->notify($sid, 'PROJECT_PROVISIONED', $subject, $body, $url, 'c_projects', $projectId, $userId, $userName);
            }
        } catch (\Throwable $e) { /* best-effort */ }

        return $projectId;
    }

    /* ── Contract signing (post-award) ─────────────────────────────────── */

    public function contractTerms(int $tenderId): ?array
    {
        $db = new DB();
        $r = $db->mySelect("SELECT * FROM tender_contract_terms WHERE tender_id = :t", [':t' => $tenderId]);
        return $r[0] ?? null;
    }

    /**
     * Upsert contract terms. Only allowed when tender is AWARDED / CONTRACT_SIGNING and status=WON.
     * Blocked once Exec approval is APPROVED (chain must be resubmitted to change terms).
     */
    /** Roles allowed to edit contract terms AFTER Executive approval (with mandatory reason). */
    public static function canEditLockedTerms(string $userGroup): bool
    {
        return in_array(strtoupper(trim($userGroup)), ['EXECUTIVE','ADMINISTRATOR','CEO','MANAGING DIRECTOR'], true);
    }

    /** Roles allowed to issue an unlock so FM (or others) can amend locked terms. */
    public static function canUnlockTerms(string $userGroup): bool
    {
        return in_array(strtoupper(trim($userGroup)), ['EXECUTIVE','ADMINISTRATOR','CEO','MANAGING DIRECTOR'], true);
    }

    /**
     * Executive issues an unlock so FM (or any role with saveContractTermsAj) can amend.
     * One save consumes the unlock — terms auto-re-lock afterwards. An audit row is written
     * marking the unlock event so the trail shows "Exec unlocked → FM changed X → auto-relocked".
     */
    public function unlockTerms(int $tenderId, int $execUserId, string $execName, string $execGroup, string $reason): void
    {
        if (!self::canUnlockTerms($execGroup)) throw new RuntimeException('Only Executive or Administrator may unlock terms.');
        if (trim($reason) === '')             throw new RuntimeException('A reason is required to unlock terms.');
        if ($this->contractApprovalStatus($tenderId) !== 'APPROVED') throw new RuntimeException('Terms are not locked — no need to unlock.');

        $db = new DB();
        $existing = $this->contractTerms($tenderId);
        if (!$existing) throw new RuntimeException('Contract terms row missing.');

        $db->insertUpdate(
            "UPDATE tender_contract_terms
                SET terms_unlock_active = 1,
                    terms_unlock_by = :u, terms_unlock_by_name = :un,
                    terms_unlock_reason = :r, terms_unlock_on = NOW()
              WHERE tender_id = :t",
            [':u' => $execUserId, ':un' => $execName, ':r' => $reason, ':t' => $tenderId]
        );

        // Audit synthetic row for the unlock event
        $db->insertUpdate(
            "INSERT INTO tender_contract_terms_audit
                (tender_id, changed_by, changed_by_name, changed_by_group,
                 field_name, old_value, new_value, reason, post_approval, changed_on)
             VALUES (:t, :cb, :cbn, :cbg, '_UNLOCK_ISSUED', 'LOCKED', 'UNLOCKED_FOR_EDIT', :r, 1, NOW())",
            [':t' => $tenderId, ':cb' => $execUserId, ':cbn' => $execName, ':cbg' => $execGroup, ':r' => $reason]
        );

        // Notify all FMs that they now have an editable window
        try {
            if (!class_exists('Notifications')) require_once ROOT . '/app/models/Notifications.php';
            $t = $this->find($tenderId);
            $ref = (string)($t['tender_no'] ?? ('#' . $tenderId));
            (new Notifications())->notifyGroup(
                'Finance Manager', 'TERMS_UNLOCKED',
                'Terms unlocked for ' . $ref . ' — please amend',
                'Reason: ' . $reason . ' — your next save will re-lock the terms and be audit-logged.',
                'Tenders/view/' . $tenderId . '#tab-contract',
                'tender_contract_terms', $tenderId, $execUserId, $execName
            );
        } catch (\Throwable $e) { /* best-effort */ }
    }

    /** Cancel an active unlock (Exec changed their mind, or FM never showed up). */
    public function relockTerms(int $tenderId, int $actorId, string $actorName, string $actorGroup): void
    {
        if (!self::canUnlockTerms($actorGroup)) throw new RuntimeException('Only Executive or Administrator may re-lock terms.');
        $db = new DB();
        $existing = $this->contractTerms($tenderId);
        if (!$existing) return;
        if ((int)$existing['terms_unlock_active'] !== 1) return;

        $db->insertUpdate(
            "UPDATE tender_contract_terms
                SET terms_unlock_active = 0, terms_unlock_by = 0, terms_unlock_by_name = '',
                    terms_unlock_reason = '', terms_unlock_on = NULL
              WHERE tender_id = :t",
            [':t' => $tenderId]
        );
        $db->insertUpdate(
            "INSERT INTO tender_contract_terms_audit
                (tender_id, changed_by, changed_by_name, changed_by_group,
                 field_name, old_value, new_value, reason, post_approval, changed_on)
             VALUES (:t, :cb, :cbn, :cbg, '_UNLOCK_CANCELLED', 'UNLOCKED_FOR_EDIT', 'LOCKED', 'Unlock cancelled without edit', 1, NOW())",
            [':t' => $tenderId, ':cb' => $actorId, ':cbn' => $actorName, ':cbg' => $actorGroup]
        );
    }

    /**
     * Fields the form / audit trail cares about. Kept explicit so the diff stays deterministic.
     * Order matters — used for audit display.
     */
    private const CONTRACT_TERM_FIELDS = [
        // Financial
        'contract_no','contract_date','contract_value','payment_terms','payment_basis','payment_net_days',
        // Advance + APG
        'advance_pct','apg_required','apg_bank','apg_validity_months','advance_conditions',
        // Performance guarantee
        'performance_bond_value','pg_type','performance_bond_ref','pg_bank','pg_validity_months',
        // Retention + Warranty + LD
        'retention_pct','retention_release_trigger','warranty_months','warranty_coverage',
        'ld_pct_per_day','ld_cap_pct',
        // Tax + compliance
        'vat_treatment','tax_treatment','tax_exemption_basis','wht_pct','gl_code','fx_matches_cost_base',
        // Bonds & other
        'bid_bond_value','delivery_timeline_days','insurance_ref','notes',
        // Slice 8X: seat assignments — audited like any other term
        'assigned_fm_user_id','assigned_accountant_user_id','assigned_procurement_user_id','assigned_pm_user_id',
    ];

    /** Slice 8X · which role owns which seat assignment on the contract. */
    public const SEAT_RBAC = [
        'assigned_fm_user_id'          => ['CRM','ADMINISTRATOR'],
        'assigned_accountant_user_id'  => ['FINANCE MANAGER','ADMINISTRATOR'],
        'assigned_procurement_user_id' => ['FINANCE MANAGER','ADMINISTRATOR'],
        'assigned_pm_user_id'          => ['EXECUTIVE','ADMINISTRATOR'],
    ];

    /** Slice 8X · quick lookup of active users belonging to a specific group. */
    public function usersInGroup(string $groupName): array
    {
        $db = new DB();
        return $db->mySelect(
            "SELECT c0d, full_name FROM users
              WHERE user_group_name = :g AND status = 'ACTIVE' AND deleted = 0
              ORDER BY full_name",
            [':g' => $groupName]
        ) ?: [];
    }

    /**
     * Upsert contract terms. Rules:
     *   - Tender must be AWARDED / CONTRACT_SIGNING and status WON.
     *   - Pre-approval: any role with saveContractTermsAj perm can edit freely.
     *   - Post-approval: only Executive/Administrator can edit, and a reason is required.
     *   - Every changed field is written as a row in tender_contract_terms_audit.
     */
    public function saveContractTerms(int $tenderId, array $d, int $userId, string $userGroup = '', string $userName = '', string $reason = ''): void
    {
        $t = $this->find($tenderId);
        if (!$t) throw new RuntimeException('Tender not found.');
        if ($t['tender_status'] !== 'WON') throw new RuntimeException('Contract terms can only be captured after the tender is Won.');
        $curIdx   = array_search($t['tender_stage'], self::STAGES, true);
        $awardIdx = array_search('AWARDED', self::STAGES, true);
        if ($curIdx < $awardIdx) throw new RuntimeException('Advance the tender to Awarded before capturing contract terms.');

        $isLocked        = ($this->contractApprovalStatus($tenderId) === 'APPROVED');
        $existingRow     = $this->contractTerms($tenderId);
        $unlockActive    = $existingRow && (int)($existingRow['terms_unlock_active'] ?? 0) === 1;
        $unlockReason    = $unlockActive ? (string)($existingRow['terms_unlock_reason'] ?? '') : '';
        $postApproval    = false;
        $consumedUnlock  = false;
        if ($isLocked) {
            // Path 1 — Exec/Admin direct override (always requires a reason).
            // Path 2 — Any role with saveContractTermsAj perm can edit if Exec issued an unlock;
            //          the unlock's original reason becomes the audit reason automatically.
            if (self::canEditLockedTerms($userGroup)) {
                if (trim($reason) === '') {
                    throw new RuntimeException('A reason is required when editing terms after Executive approval.');
                }
                $postApproval = true;
            } elseif ($unlockActive) {
                if (trim($reason) === '') $reason = 'FM amend under Exec unlock: ' . $unlockReason;
                $postApproval   = true;
                $consumedUnlock = true;
            } else {
                throw new RuntimeException('Contract already approved by Executive — ask Executive to unlock terms, or only Exec/Admin can edit directly.');
            }
        }

        $db = new DB();
        $existing = $existingRow;

        // Coerce incoming values through the same normalisation used for storage,
        // so the diff compares apples-to-apples.
        $normalised = [
            'contract_no'               => (string)($d['contract_no'] ?? ''),
            'contract_date'             => !empty($d['contract_date']) ? (string)$d['contract_date'] : null,
            'contract_value'            => (float)($d['contract_value'] ?? 0),
            'payment_terms'             => (string)($d['payment_terms'] ?? ''),
            'payment_basis'             => (string)($d['payment_basis'] ?? ''),
            'payment_net_days'          => isset($d['payment_net_days']) && $d['payment_net_days'] !== '' ? (int)$d['payment_net_days'] : null,
            'delivery_timeline_days'    => isset($d['delivery_timeline_days']) && $d['delivery_timeline_days'] !== '' ? (int)$d['delivery_timeline_days'] : null,
            'warranty_months'           => isset($d['warranty_months']) && $d['warranty_months'] !== '' ? (int)$d['warranty_months'] : null,
            'warranty_coverage'         => (string)($d['warranty_coverage'] ?? ''),
            'ld_pct_per_day'            => (float)($d['ld_pct_per_day'] ?? 0),
            'ld_cap_pct'                => (float)($d['ld_cap_pct'] ?? 0),
            'bid_bond_value'            => isset($d['bid_bond_value']) && $d['bid_bond_value'] !== '' ? (float)$d['bid_bond_value'] : null,
            'performance_bond_value'    => isset($d['performance_bond_value']) && $d['performance_bond_value'] !== '' ? (float)$d['performance_bond_value'] : null,
            'pg_type'                   => (string)($d['pg_type'] ?? ''),
            'performance_bond_ref'      => (string)($d['performance_bond_ref'] ?? ''),
            'pg_bank'                   => (string)($d['pg_bank'] ?? ''),
            'pg_validity_months'        => isset($d['pg_validity_months']) && $d['pg_validity_months'] !== '' ? (int)$d['pg_validity_months'] : null,
            'advance_pct'               => isset($d['advance_pct']) && $d['advance_pct'] !== '' ? (float)$d['advance_pct'] : null,
            'apg_required'              => !empty($d['apg_required']) ? 1 : 0,
            'apg_bank'                  => (string)($d['apg_bank'] ?? ''),
            'apg_validity_months'       => (int)($d['apg_validity_months'] ?? 0),
            'advance_conditions'        => (string)($d['advance_conditions'] ?? ''),
            'retention_pct'             => isset($d['retention_pct']) && $d['retention_pct'] !== '' ? (float)$d['retention_pct'] : null,
            'retention_release_trigger' => (string)($d['retention_release_trigger'] ?? ''),
            'wht_pct'                   => isset($d['wht_pct']) && $d['wht_pct'] !== '' ? (float)$d['wht_pct'] : null,
            'vat_treatment'             => !empty($d['vat_treatment']) ? (string)$d['vat_treatment'] : null,
            'tax_treatment'             => (string)($d['tax_treatment'] ?? ''),
            'tax_exemption_basis'       => (string)($d['tax_exemption_basis'] ?? ''),
            'gl_code'                   => (string)($d['gl_code'] ?? ''),
            'fx_matches_cost_base'      => isset($d['fx_matches_cost_base']) ? ((int)$d['fx_matches_cost_base'] ? 1 : 0) : 1,
            'insurance_ref'             => (string)($d['insurance_ref'] ?? ''),
            'notes'                     => (string)($d['notes'] ?? ''),
            // Slice 8X · seat assignments (gated below)
            'assigned_fm_user_id'          => isset($d['assigned_fm_user_id'])          && $d['assigned_fm_user_id']          !== '' ? (int)$d['assigned_fm_user_id']          : null,
            'assigned_accountant_user_id'  => isset($d['assigned_accountant_user_id'])  && $d['assigned_accountant_user_id']  !== '' ? (int)$d['assigned_accountant_user_id']  : null,
            'assigned_procurement_user_id' => isset($d['assigned_procurement_user_id']) && $d['assigned_procurement_user_id'] !== '' ? (int)$d['assigned_procurement_user_id'] : null,
            'assigned_pm_user_id'          => isset($d['assigned_pm_user_id'])          && $d['assigned_pm_user_id']          !== '' ? (int)$d['assigned_pm_user_id']          : null,
        ];

        // Slice 8X · per-role RBAC on seat assignments. If the current user's group isn't in the
        // allowed list for that seat, silently keep the existing value (mirrors CompanyProfile pattern).
        $upperGroup = strtoupper($userGroup);
        foreach (self::SEAT_RBAC as $field => $allowed) {
            if (!in_array($upperGroup, $allowed, true)) {
                $normalised[$field] = $existing[$field] ?? null;
            }
        }

        // Build audit rows against existing (skip if no existing row → treat as fresh insert)
        $auditRows = [];
        if ($existing) {
            foreach (self::CONTRACT_TERM_FIELDS as $f) {
                $before = $existing[$f] ?? null;
                $after  = $normalised[$f] ?? null;
                if ($this->normalisedEquals($before, $after)) continue;
                $auditRows[] = [
                    'field'    => $f,
                    'old'      => $before,
                    'new'      => $after,
                ];
            }
        }

        if ($existing) {
            $db->insertUpdate(
                "UPDATE tender_contract_terms SET
                    contract_no = :contract_no, contract_date = :contract_date, contract_value = :contract_value,
                    payment_terms = :payment_terms, payment_basis = :payment_basis, payment_net_days = :payment_net_days,
                    delivery_timeline_days = :delivery_timeline_days,
                    warranty_months = :warranty_months, warranty_coverage = :warranty_coverage,
                    ld_pct_per_day = :ld_pct_per_day, ld_cap_pct = :ld_cap_pct,
                    bid_bond_value = :bid_bond_value,
                    performance_bond_value = :performance_bond_value, pg_type = :pg_type,
                    performance_bond_ref = :performance_bond_ref, pg_bank = :pg_bank,
                    pg_validity_months = :pg_validity_months,
                    advance_pct = :advance_pct, apg_required = :apg_required,
                    apg_bank = :apg_bank, apg_validity_months = :apg_validity_months,
                    advance_conditions = :advance_conditions,
                    retention_pct = :retention_pct, retention_release_trigger = :retention_release_trigger,
                    wht_pct = :wht_pct, vat_treatment = :vat_treatment,
                    tax_treatment = :tax_treatment, tax_exemption_basis = :tax_exemption_basis,
                    gl_code = :gl_code, fx_matches_cost_base = :fx_matches_cost_base,
                    insurance_ref = :insurance_ref, notes = :notes,
                    assigned_fm_user_id = :assigned_fm_user_id,
                    assigned_accountant_user_id = :assigned_accountant_user_id,
                    assigned_procurement_user_id = :assigned_procurement_user_id,
                    assigned_pm_user_id = :assigned_pm_user_id,
                    edited_by = :u, edited_on = NOW()
                  WHERE tender_id = :t",
                array_merge([':t' => $tenderId, ':u' => $userId], $this->prefixKeys($normalised))
            );
        } else {
            $db->insertUpdate(
                "INSERT INTO tender_contract_terms
                    (tender_id, currency,
                     contract_no, contract_date, contract_value, payment_terms, payment_basis, payment_net_days,
                     delivery_timeline_days, warranty_months, warranty_coverage, ld_pct_per_day, ld_cap_pct,
                     bid_bond_value, performance_bond_value, pg_type, performance_bond_ref, pg_bank, pg_validity_months,
                     advance_pct, apg_required, apg_bank, apg_validity_months, advance_conditions,
                     retention_pct, retention_release_trigger,
                     wht_pct, vat_treatment, tax_treatment, tax_exemption_basis,
                     gl_code, fx_matches_cost_base,
                     insurance_ref, notes,
                     assigned_fm_user_id, assigned_accountant_user_id, assigned_procurement_user_id, assigned_pm_user_id,
                     created_by, created_on)
                 VALUES (:t, 'TZS',
                     :contract_no, :contract_date, :contract_value, :payment_terms, :payment_basis, :payment_net_days,
                     :delivery_timeline_days, :warranty_months, :warranty_coverage, :ld_pct_per_day, :ld_cap_pct,
                     :bid_bond_value, :performance_bond_value, :pg_type, :performance_bond_ref, :pg_bank, :pg_validity_months,
                     :advance_pct, :apg_required, :apg_bank, :apg_validity_months, :advance_conditions,
                     :retention_pct, :retention_release_trigger,
                     :wht_pct, :vat_treatment, :tax_treatment, :tax_exemption_basis,
                     :gl_code, :fx_matches_cost_base,
                     :insurance_ref, :notes,
                     :assigned_fm_user_id, :assigned_accountant_user_id, :assigned_procurement_user_id, :assigned_pm_user_id,
                     :u, NOW())",
                array_merge([':t' => $tenderId, ':u' => $userId], $this->prefixKeys($normalised))
            );
        }

        // Write audit rows (post-approval edits always logged with reason; pre-approval edits
        // still logged so we can show a change history but with post_approval=0)
        foreach ($auditRows as $row) {
            $db->insertUpdate(
                "INSERT INTO tender_contract_terms_audit
                    (tender_id, changed_by, changed_by_name, changed_by_group,
                     field_name, old_value, new_value, reason, post_approval, changed_on)
                 VALUES (:t, :cb, :cbn, :cbg, :f, :ov, :nv, :r, :pa, NOW())",
                [
                    ':t'   => $tenderId, ':cb' => $userId, ':cbn' => $userName, ':cbg' => $userGroup,
                    ':f'   => (string)$row['field'],
                    ':ov'  => $row['old'] === null ? null : (string)$row['old'],
                    ':nv'  => $row['new'] === null ? null : (string)$row['new'],
                    ':r'   => $reason,
                    ':pa'  => $postApproval ? 1 : 0,
                ]
            );
        }

        // Consume the Exec-issued unlock: auto-relock the terms and log the consumption event.
        if ($consumedUnlock) {
            $db->insertUpdate(
                "UPDATE tender_contract_terms
                    SET terms_unlock_active = 0, terms_unlock_by = 0, terms_unlock_by_name = '',
                        terms_unlock_reason = '', terms_unlock_on = NULL
                  WHERE tender_id = :t",
                [':t' => $tenderId]
            );
            $db->insertUpdate(
                "INSERT INTO tender_contract_terms_audit
                    (tender_id, changed_by, changed_by_name, changed_by_group,
                     field_name, old_value, new_value, reason, post_approval, changed_on)
                 VALUES (:t, :cb, :cbn, :cbg, '_UNLOCK_CONSUMED', 'UNLOCKED_FOR_EDIT', 'LOCKED',
                         'Auto-relocked after amendment save', 1, NOW())",
                [':t' => $tenderId, ':cb' => $userId, ':cbn' => $userName, ':cbg' => $userGroup]
            );
        }
    }

    /** True if two values would round-trip identically through the DB (handles NULL vs 0 vs ''). */
    private function normalisedEquals($a, $b): bool
    {
        if ($a === null && ($b === null || $b === '' || $b === 0 || $b === 0.0)) return true;
        if ($b === null && ($a === null || $a === '' || $a === 0 || $a === 0.0)) return true;
        if (is_numeric($a) && is_numeric($b)) return abs((float)$a - (float)$b) < 0.0001;
        return (string)$a === (string)$b;
    }

    /** Prefix keys with ':' for PDO named params. */
    private function prefixKeys(array $data): array
    {
        $out = [];
        foreach ($data as $k => $v) $out[':' . $k] = $v;
        return $out;
    }

    /**
     * Contract-terms change history for a tender, newest first.
     * View is restricted at controller-level to Exec/Admin.
     */
    public function contractTermsAudit(int $tenderId): array
    {
        $db = new DB();
        return $db->mySelect(
            "SELECT * FROM tender_contract_terms_audit
              WHERE tender_id = :t
              ORDER BY changed_on DESC, c0d DESC",
            [':t' => $tenderId]
        ) ?: [];
    }

    /**
     * Slice 8Y · Consolidated view of a project's source tender for the PM.
     *   $projectId    — c_projects.c0d
     *   Returns [] when the project has no tender origin. Otherwise a bag with:
     *     tender: tender_tracker row (essentials)
     *     terms:  tender_contract_terms row (may be null)
     *     signing:tender_contract_signing row (may be null)
     *     history:approval-log rows for TENDER_CONTRACT_APPROVAL
     *     goods_client_po_id: linked snapshotted PO id (goods only, else 0)
     */
    public function sourceTenderContextForProject(int $projectId): array
    {
        $db = new DB();
        $rs = $db->mySelect(
            "SELECT c0d, tender_no, tender_title, buyer_name, source_type,
                    estimated_value, submission_amount, submission_date, deadline_date,
                    tender_stage, tender_status, project_type, source_ref
               FROM tender_tracker
              WHERE linked_project_id = :p AND deleted = 0
              ORDER BY c0d DESC LIMIT 1",
            [':p' => $projectId]
        );
        if (empty($rs)) return [];
        $tender = $rs[0];
        $tenderId = (int)$tender['c0d'];

        $terms   = $this->contractTerms($tenderId);
        $signing = $this->contractSigning($tenderId);
        $history = $this->contractApprovalHistory($tenderId);

        // For goods, the terminal Exec-approval snapshot lands in goods_client_pos.linked_tender_id.
        $goodsPoId = 0;
        if ($tender['project_type'] === 'goods') {
            $gp = $db->mySelect(
                "SELECT c0d FROM goods_client_pos WHERE linked_tender_id = :t AND deleted = 0 ORDER BY c0d DESC LIMIT 1",
                [':t' => $tenderId]
            );
            $goodsPoId = (int)($gp[0]['c0d'] ?? 0);
        }

        // Slice 8Y Card A · buyer's RFP scope.
        $items = $db->mySelect(
            "SELECT c0d, line_no, item_ref, description, qty, uom, spec_notes, line_kind
               FROM tender_items
              WHERE tender_id = :t AND deleted = 0
              ORDER BY line_no, c0d",
            [':t' => $tenderId]
        ) ?: [];

        // Slice 8Y Card B · our priced bid — prefer APPROVED, then latest DRAFT.
        $pricing = null;
        $pricingLines = [];
        $pv = $db->mySelect(
            "SELECT c0d, version_no, version_label, status, currency, pricing_mode,
                    sum_cost, sum_overhead, sum_price, sum_margin, margin_pct
               FROM tender_pricing_versions
              WHERE tender_id = :t AND deleted = 0
              ORDER BY (status = 'APPROVED') DESC, c0d DESC
              LIMIT 1",
            [':t' => $tenderId]
        );
        $pricingSections = [];
        $pricingOverheads = [];
        if (!empty($pv)) {
            $pricing = $pv[0];
            $pricingLines = $db->mySelect(
                "SELECT c0d, section_id, description, qty, uom, unit_cost, markup_pct, unit_price, line_cost, line_price, notes
                   FROM tender_pricing_lines
                  WHERE version_id = :v AND deleted = 0
                  ORDER BY line_no, c0d",
                [':v' => (int)$pricing['c0d']]
            ) ?: [];

            // Sections give us the grouping headers used in the tender pricing tab.
            $pricingSections = $db->mySelect(
                "SELECT c0d, section_no, section_name, sum_cost, sum_price
                   FROM tender_pricing_sections
                  WHERE version_id = :v AND deleted = 0
                  ORDER BY section_no, c0d",
                [':v' => (int)$pricing['c0d']]
            ) ?: [];

            // Overhead pool — mirrors the Slice 8S overhead card on the pricing tab.
            $pricingOverheads = $db->mySelect(
                "SELECT category, label, amount, display_mode, notes
                   FROM pricing_version_overheads
                  WHERE version_id = :v AND deleted = 0
                  ORDER BY line_no, c0d",
                [':v' => (int)$pricing['c0d']]
            ) ?: [];
        }

        return [
            'tender'             => $tender,
            'terms'              => $terms,
            'signing'            => $signing,
            'history'            => $history,
            'goods_client_po_id' => $goodsPoId,
            'items'              => $items,
            'pricing'            => $pricing,
            'pricing_lines'      => $pricingLines,
            'pricing_sections'   => $pricingSections,
            'pricing_overheads'  => $pricingOverheads,
        ];
    }

    /**
     * Kick off the TENDER_CONTRACT_APPROVAL chain (FM verify → Exec approve).
     * Idempotent — if an IN_PROGRESS approval exists, returns its id without creating a new one.
     */
    public function submitContractForApproval(int $tenderId, int $userId, string $userName, string $userGroup): array
    {
        $t = $this->find($tenderId);
        if (!$t) return ['ok' => false, 'msg' => 'Tender not found.'];
        $terms = $this->contractTerms($tenderId);
        if (!$terms) return ['ok' => false, 'msg' => 'Capture the contract terms before submitting for approval.'];
        // 8Y: CRM must at least have nominated the FM before submitting — the FM will be the first
        // pair of hands on this approval and the queue depends on the assignment.
        if ((int)($terms['assigned_fm_user_id'] ?? 0) <= 0) {
            return ['ok' => false, 'msg' => 'Assign a Finance Manager before submitting for FM verification.'];
        }
        if (!class_exists('ApprovalChain')) require ROOT . '/app/models/ApprovalChain.php';
        $ac  = new ApprovalChain();
        $res = $ac->submit([
            'chain_key'    => 'TENDER_CONTRACT_APPROVAL',
            'entity_type'  => 'TENDER_CONTRACT',
            'entity_id'    => $tenderId,
            'ref_label'    => $t['tender_no'] . ' · ' . $t['tender_title'],
            'summary_json' => json_encode([
                'contract_no'    => $terms['contract_no'],
                'contract_value' => (float)$terms['contract_value'],
                'payment_terms'  => $terms['payment_terms'],
                'advance_pct'    => $terms['advance_pct'],
                'retention_pct'  => $terms['retention_pct'],
                'wht_pct'        => $terms['wht_pct'],
            ]),
            'return_url'   => PROOT . 'Tenders/view/' . $tenderId . '#tab-contract',
            'user_id'      => $userId,
            'user_name'    => $userName,
            'user_group'   => $userGroup,
        ]);
        // 8Y: fan a notification to the FM group so it lands in the bell + inbox, not just
        // buried inside FinChains/myQueue.
        if (!empty($res['ok'])) {
            try {
                if (!class_exists('Notifications')) require_once ROOT . '/app/models/Notifications.php';
                $ref = (string)($t['tender_no'] ?? ('#' . $tenderId));
                (new Notifications())->notifyGroup(
                    'Finance Manager', 'CONTRACT_PENDING_FM',
                    'Contract awaiting FM verification: ' . $ref,
                    'CRM submitted contract terms — please verify + assign Accountant/Procurement/Executive.',
                    'Tenders/view/' . $tenderId . '#tab-contract',
                    'TENDER_CONTRACT', $tenderId, $userId, $userName
                );
            } catch (\Throwable $e) { /* best-effort */ }
        }
        return $res;
    }

    /**
     * Slice 8Y: the fields FM must have populated before the contract can be handed to Exec.
     * Returns [] when ready; otherwise an array of missing field labels.
     */
    public function contractCompletenessMissing(int $tenderId): array
    {
        $terms = $this->contractTerms($tenderId);
        if (!$terms) return ['Contract terms row'];
        $required = [
            'contract_no'                  => 'Contract no',
            'contract_date'                => 'Contract date',
            'contract_value'               => 'Contract value',
            'payment_terms'                => 'Payment terms',
            'payment_basis'                => 'Payment basis',
            'delivery_timeline_days'       => 'Delivery timeline (days)',
            'advance_pct'                  => 'Advance %',
            'retention_pct'                => 'Retention %',
            'wht_pct'                      => 'WHT %',
            'assigned_fm_user_id'          => 'Finance Manager seat',
            'assigned_accountant_user_id'  => 'Accountant seat',
            'assigned_procurement_user_id' => 'Procurement seat',
        ];
        $missing = [];
        foreach ($required as $field => $label) {
            $v = $terms[$field] ?? null;
            if ($v === null || $v === '' || $v === '0.00' || (is_numeric($v) && (float)$v <= 0 && !in_array($field, ['wht_pct','advance_pct','retention_pct'], true))) {
                // Percent fields can legitimately be 0 (contracts sometimes have no advance/retention/WHT),
                // so accept 0 for those — just require the field to be present (not null / not '').
                if (in_array($field, ['wht_pct','advance_pct','retention_pct'], true) && $v !== null && $v !== '') continue;
                $missing[] = $label;
            }
        }
        // Slice 8Z-q · FM's mandatory decision memos must exist before Approve. This is the
        // hard guardrail against approving a Tanzania contract without the paper trail
        // (cover memo · cash-flow projection · profitability model).
        try {
            if (!class_exists('TenderFmVerificationAttachments')) require_once ROOT . '/app/models/TenderFmVerificationAttachments.php';
            $chk = (new TenderFmVerificationAttachments())->mandatoryCheck($tenderId);
            if (empty($chk['ok'])) {
                foreach ($chk['labels'] as $lbl) $missing[] = 'FM attachment: ' . $lbl;
            }
        } catch (\Throwable $e) { /* best-effort — falls through if table missing */ }
        // Slice 8Z-t · TRA VAT exemption certificate is mandatory whenever the contract's
        // tax_treatment ≠ TAXABLE (EXEMPT, ZERO_RATED, MIXED). Audit-critical in Tanzania.
        $taxTreat = strtoupper((string)($terms['tax_treatment'] ?? ''));
        if ($terms && in_array($taxTreat, ['EXEMPT','ZERO_RATED','MIXED'], true)) {
            try {
                if (!class_exists('TenderContractBonds')) require_once ROOT . '/app/models/TenderContractBonds.php';
                $bonds = (new TenderContractBonds())->listFor($tenderId);
                $hasCert = false;
                foreach ($bonds as $b) {
                    if ((string)$b['bond_type'] === 'VAT_EXEMPTION_CERT' && (string)$b['status'] === 'RECEIVED') { $hasCert = true; break; }
                }
                if (!$hasCert) $missing[] = 'VAT exemption certificate (tax treatment is ' . strtolower(str_replace('_',' ',$taxTreat)) . ')';
            } catch (\Throwable $e) { /* best-effort */ }
        }
        return $missing;
    }

    /** Slice 8Z-r · Which section on the Contract-signing tab owns each required field. */
    private const COMPLETENESS_SECTION_MAP = [
        'contract_no'                  => 'financial',
        'contract_date'                => 'financial',
        'contract_value'               => 'financial',
        'payment_terms'                => 'financial',
        'payment_basis'                => 'financial',
        'delivery_timeline_days'       => 'financial',
        'advance_pct'                  => 'advance',
        'retention_pct'                => 'retention_warranty',
        'wht_pct'                      => 'tax',
        'assigned_fm_user_id'          => 'team',
        'assigned_accountant_user_id'  => 'team',
        'assigned_procurement_user_id' => 'team',
    ];

    /**
     * Slice 8Z-r · Full completeness checklist — every required item annotated with its
     * current status + which UI section owns it. Used by the "Before handing off, fill" UI
     * so completed items stay visible (tick + green) instead of disappearing when they're
     * done, and by the per-section status pills on the Contract-signing tab cards.
     * Returns [ ['label' => …, 'ok' => bool, 'section_key' => …], ... ] in required[] order.
     */
    public function contractCompletenessChecklist(int $tenderId): array
    {
        $terms = $this->contractTerms($tenderId);
        $required = [
            'contract_no'                  => 'Contract no',
            'contract_date'                => 'Contract date',
            'contract_value'               => 'Contract value',
            'payment_terms'                => 'Payment terms',
            'payment_basis'                => 'Payment basis',
            'delivery_timeline_days'       => 'Delivery timeline (days)',
            'advance_pct'                  => 'Advance %',
            'retention_pct'                => 'Retention %',
            'wht_pct'                      => 'WHT %',
            'assigned_fm_user_id'          => 'Finance Manager seat',
            'assigned_accountant_user_id'  => 'Accountant seat',
            'assigned_procurement_user_id' => 'Procurement seat',
        ];
        $items = [];
        if (!$terms) {
            // No terms row at all — every item is missing.
            foreach ($required as $field => $label) {
                $items[] = ['label' => $label, 'ok' => false, 'section_key' => self::COMPLETENESS_SECTION_MAP[$field] ?? 'other'];
            }
        } else {
            foreach ($required as $field => $label) {
                $v = $terms[$field] ?? null;
                $ok = true;
                if ($v === null || $v === '' || $v === '0.00'
                    || (is_numeric($v) && (float)$v <= 0 && !in_array($field, ['wht_pct','advance_pct','retention_pct'], true))) {
                    // Percent fields accept 0 as long as they're set (not null / not '')
                    if (in_array($field, ['wht_pct','advance_pct','retention_pct'], true) && $v !== null && $v !== '') {
                        $ok = true;
                    } else {
                        $ok = false;
                    }
                }
                $items[] = ['label' => $label, 'ok' => $ok, 'section_key' => self::COMPLETENESS_SECTION_MAP[$field] ?? 'other'];
            }
        }
        // FM mandatory attachments — each subtype gets its own line so FM sees which ones are done.
        try {
            if (!class_exists('TenderFmVerificationAttachments')) require_once ROOT . '/app/models/TenderFmVerificationAttachments.php';
            $rows = (new TenderFmVerificationAttachments())->listFor($tenderId);
            $present = [];
            foreach ($rows as $r) $present[(string)$r['subtype']] = true;
            foreach (TenderFmVerificationAttachments::SUBTYPES as $key => $meta) {
                if (empty($meta['mandatory'])) continue;
                $items[] = ['label' => 'FM attachment: ' . $meta['label'], 'ok' => !empty($present[$key]), 'section_key' => 'fm_attach'];
            }
        } catch (\Throwable $e) { /* best-effort */ }
        // Slice 8Z-t · Conditional VAT exemption certificate — required whenever
        // tax_treatment ≠ TAXABLE. Belongs to the Tax section for the pill roll-up.
        $taxTreat = strtoupper((string)($terms['tax_treatment'] ?? ''));
        if ($terms && in_array($taxTreat, ['EXEMPT','ZERO_RATED','MIXED'], true)) {
            try {
                if (!class_exists('TenderContractBonds')) require_once ROOT . '/app/models/TenderContractBonds.php';
                $bonds = (new TenderContractBonds())->listFor($tenderId);
                $hasCert = false;
                foreach ($bonds as $b) {
                    if ((string)$b['bond_type'] === 'VAT_EXEMPTION_CERT' && (string)$b['status'] === 'RECEIVED') { $hasCert = true; break; }
                }
                $items[] = ['label' => 'VAT exemption certificate (tax treatment is ' . strtolower(str_replace('_',' ',$taxTreat)) . ')', 'ok' => $hasCert, 'section_key' => 'tax'];
            } catch (\Throwable $e) { /* best-effort */ }
        }
        return $items;
    }

    /**
     * Slice 8Z-r · Roll the checklist up into a per-section done/total tally so the
     * Contract-signing card headers can each render a status pill. Sections with no
     * mandatory items still appear (done=0, total=0) so the view can render an empty
     * chip if desired.
     */
    public function contractSectionCompleteness(int $tenderId): array
    {
        $checklist = $this->contractCompletenessChecklist($tenderId);
        $sections  = [
            'team'               => ['done' => 0, 'total' => 0],
            'financial'          => ['done' => 0, 'total' => 0],
            'advance'            => ['done' => 0, 'total' => 0],
            'pg'                 => ['done' => 0, 'total' => 0],
            'retention_warranty' => ['done' => 0, 'total' => 0],
            'tax'                => ['done' => 0, 'total' => 0],
            'fm_attach'          => ['done' => 0, 'total' => 0],
        ];
        foreach ($checklist as $ci) {
            $k = (string)($ci['section_key'] ?? 'other');
            if (!isset($sections[$k])) $sections[$k] = ['done' => 0, 'total' => 0];
            $sections[$k]['total']++;
            if (!empty($ci['ok'])) $sections[$k]['done']++;
        }
        foreach ($sections as &$s) $s['all_ok'] = ($s['total'] > 0 && $s['done'] === $s['total']);
        unset($s);
        return $sections;
    }

    /**
     * Slice 8Y: entity-specific notification hook for TENDER_CONTRACT chain transitions.
     * Fired from FinChainsController after each engine transition (submit is handled inline
     * in submitContractForApproval; approve/sendBack/decline/returnToOriginator arrive here).
     * $a is the approval row *after* the transition (so $a['status'] and the joined step reflect
     * the new state). $decision is the engine verb.
     */
    public function notifyContractHandoff(int $tenderId, string $decision, array $a): void
    {
        try {
            if (!class_exists('Notifications')) require_once ROOT . '/app/models/Notifications.php';
            $t = $this->find($tenderId);
            if (!$t) return;
            $ref   = (string)($t['tender_no']    ?? ('#' . $tenderId));
            $url   = 'Tenders/view/' . $tenderId . '#tab-contract';
            $N     = new Notifications();
            $status  = (string)($a['status'] ?? '');
            $stepRole = strtoupper((string)($a['role_required'] ?? $a['current_step_role'] ?? ''));

            if ($decision === 'APPROVE' && $status === 'APPROVED') {
                // Terminal Exec approval. Fan out five notifications:
                //   1. CRM (informational — signing / auto-execution next)
                //   2. FM (informational — chain complete, project provisioned)
                //   3. PM (assigned to the new project — starter checklist ready)
                //   4. Accountant (seated on the new project)
                //   5. Procurement (seated on the new project)
                // Seats read from tender_contract_terms which is the single source of truth after
                // FM + Exec saved via the Team card / inline pickers.
                $terms = $this->contractTerms($tenderId);
                $tRow  = $this->find($tenderId);
                $projectId  = (int)($tRow['linked_project_id'] ?? 0);
                $projectUrl = $projectId > 0 ? ('Projects/view/' . $projectId) : $url;

                $N->notifyGroup('CRM', 'CONTRACT_APPROVED',
                    'Contract approved: ' . $ref,
                    'Executive approved contract terms. Record the signing to advance to Execution.',
                    $url, 'TENDER_CONTRACT', $tenderId);
                $N->notifyGroup('Finance Manager', 'CONTRACT_APPROVED_FM',
                    'Contract approved by Executive: ' . $ref,
                    'Chain complete. Project provisioned. FYI — payments will begin once signing is recorded.',
                    $projectUrl, 'TENDER_CONTRACT', $tenderId);

                // PM is stored on c_projects.project_manager_id (set by convertToProject from the
                // chain step_payload_json). Acc + Proc live on tender_contract_terms since FM saved
                // them via saveContractTerms + they're copied to c_projects on convert.
                $pmId = 0;
                if ($projectId > 0) {
                    $db = new DB();
                    $pr = $db->mySelect("SELECT project_manager_id FROM c_projects WHERE c0d=:id", [':id'=>$projectId]);
                    $pmId = (int)($pr[0]['project_manager_id'] ?? 0);
                }
                $accId  = (int)($terms['assigned_accountant_user_id']   ?? 0);
                $procId = (int)($terms['assigned_procurement_user_id'] ?? 0);
                if ($pmId > 0) {
                    $N->notify($pmId, 'PROJECT_ASSIGNED_PM',
                        'You are the PM on ' . $ref,
                        'Executive approved the contract. Open the project to start onboarding.',
                        $projectUrl, 'c_projects', $projectId);
                }
                if ($accId > 0) {
                    $N->notify($accId, 'PROJECT_ASSIGNED_ACC',
                        'You are the Accountant on ' . $ref,
                        'Seat active. Payables + revenue postings will land in your queue.',
                        $projectUrl, 'c_projects', $projectId);
                }
                if ($procId > 0) {
                    $N->notify($procId, 'PROJECT_ASSIGNED_PROC',
                        'You are the Procurement lead on ' . $ref,
                        'Seat active. Sourcing + supplier LPOs on this project route to you.',
                        $projectUrl, 'c_projects', $projectId);
                }
                return;
            }
            if ($decision === 'APPROVE' && $status === 'IN_PROGRESS' && $stepRole === 'EXECUTIVE') {
                // FM verified → Exec now needs to approve.
                $N->notifyGroup('Executive', 'CONTRACT_PENDING_EXEC',
                    'Contract awaiting Executive approval: ' . $ref,
                    'Finance Manager verified terms + assigned Accountant/Procurement. Please approve + assign PM.',
                    $url, 'TENDER_CONTRACT', $tenderId);
                return;
            }
            if ($decision === 'SEND_BACK' && $status === 'IN_PROGRESS') {
                // Sent back within the chain — notify whoever now holds it.
                $N->notifyGroup(ucwords(strtolower(str_replace('_',' ',$stepRole))), 'CONTRACT_SENT_BACK',
                    'Contract sent back: ' . $ref,
                    'A later reviewer returned the contract for corrections.',
                    $url, 'TENDER_CONTRACT', $tenderId);
                return;
            }
            if ($decision === 'RETURN_TO_ORIGINATOR') {
                // Bounced all the way back to CRM (approval is CANCELLED, CRM re-submits).
                $N->notifyGroup('CRM', 'CONTRACT_RETURNED',
                    'Contract returned for edits: ' . $ref,
                    'FM/Exec returned the contract. Edit terms and re-submit for FM verification.',
                    $url, 'TENDER_CONTRACT', $tenderId);
                return;
            }
            if ($decision === 'DECLINE') {
                $N->notifyGroup('CRM', 'CONTRACT_DECLINED',
                    'Contract declined: ' . $ref,
                    'Contract chain was declined. Review comments and start a fresh submission.',
                    $url, 'TENDER_CONTRACT', $tenderId);
                $N->notifyGroup('Finance Manager', 'CONTRACT_DECLINED',
                    'Contract declined: ' . $ref, 'Contract chain was declined.',
                    $url, 'TENDER_CONTRACT', $tenderId);
                return;
            }
        } catch (\Throwable $e) { /* best-effort */ }
    }

    /** Current approval state for the tender's contract (null if never submitted). */
    public function contractApproval(int $tenderId): ?array
    {
        $db = new DB();
        $r = $db->mySelect(
            "SELECT fa.*, cs.step_key AS current_step_key, cs.step_label AS current_step_label, cs.role_required
               FROM fin_approvals fa
          LEFT JOIN fin_chain_steps cs ON cs.c0d = fa.current_step_id
              WHERE fa.entity_type = 'TENDER_CONTRACT' AND fa.entity_id = :t AND fa.deleted = 0
              ORDER BY fa.c0d DESC LIMIT 1",
            [':t' => $tenderId]
        );
        return $r[0] ?? null;
    }

    public function contractApprovalStatus(int $tenderId): ?string
    {
        $a = $this->contractApproval($tenderId);
        return $a['status'] ?? null;
    }

    public function contractApprovalHistory(int $tenderId): array
    {
        $a = $this->contractApproval($tenderId);
        if (!$a) return [];
        $db = new DB();
        return $db->mySelect(
            "SELECT log.c0d, log.approval_id, log.decision AS action, log.decided_by_name AS actor_name,
                    log.decided_by AS actor_user_id, log.decided_on AS created_on, log.comment,
                    cs.step_label
               FROM fin_approval_log log
          LEFT JOIN fin_chain_steps cs ON cs.c0d = log.to_step_id
              WHERE log.approval_id = :aid
              ORDER BY log.c0d",
            [':aid' => (int)$a['c0d']]
        ) ?: [];
    }

    /**
     * Hook to run when the chain reaches its terminal step (Exec approved).
     *  1. Auto-advances the tender stage: Awarded → Contract signing.
     *  2. Auto-creates the c_projects row (Exec's approval IS the project-creation event).
     * Called from FinChainsController::postDecisionHook when
     * entity_type='TENDER_CONTRACT' and status='APPROVED'.
     */
    public function onContractApproved(int $tenderId, int $userId, string $userName, array $seats = []): void
    {
        $t = $this->find($tenderId);
        if (!$t) return;

        // 1. Stage advance
        if ($t['tender_stage'] === 'AWARDED') {
            try { $this->advanceStage($tenderId, 'CONTRACT_SIGNING', 'Auto-advance on Exec approval of contract', $userId, $userName); }
            catch (\Throwable $e) { /* already advanced */ }
        }

        // 2. Auto-create the project if not already linked (seats from Slice 5 approval payload)
        if (empty($t['linked_project_id'])) {
            try { $this->convertToProject($tenderId, $userId, $userName, $seats); }
            catch (\Throwable $e) { /* project may already exist or gate refuses; swallow */ }
        }
    }

    /* ── Signing event ─────────────────────────────────────────────────── */

    public function contractSigning(int $tenderId): ?array
    {
        $db = new DB();
        $r = $db->mySelect("SELECT * FROM tender_contract_signing WHERE tender_id = :t", [':t' => $tenderId]);
        return $r[0] ?? null;
    }

    /**
     * Record the actual signing event. Only after Exec approved AND stage is Contract signing or later.
     * Auto-advances Contract signing → Execution.
     */
    public function recordContractSigning(int $tenderId, array $d, int $userId, string $userName): void
    {
        $t = $this->find($tenderId);
        if (!$t) throw new RuntimeException('Tender not found.');
        $curIdx = array_search($t['tender_stage'], self::STAGES, true);
        $csIdx  = array_search('CONTRACT_SIGNING', self::STAGES, true);
        if ($curIdx < $csIdx) throw new RuntimeException('Tender must be at Contract signing stage or later.');
        if ($this->contractApprovalStatus($tenderId) !== 'APPROVED') throw new RuntimeException('Executive must approve contract terms before recording the signing.');

        $db = new DB();
        $existing = $this->contractSigning($tenderId);
        $params = [
            ':t'  => $tenderId,
            ':sd' => (string)($d['signed_date'] ?? date('Y-m-d')),
            ':sv' => (string)($d['signed_venue'] ?? ''),
            ':os' => (string)($d['our_signatories'] ?? ''),
            ':ts' => (string)($d['their_signatories'] ?? ''),
            ':nt' => (string)($d['notes'] ?? ''),
            ':u'  => $userId,
            ':un' => $userName,
        ];
        if ($existing) {
            $db->insertUpdate(
                "UPDATE tender_contract_signing SET signed_date=:sd, signed_venue=:sv, our_signatories=:os, their_signatories=:ts, notes=:nt WHERE tender_id=:t",
                $params
            );
        } else {
            $db->insertUpdate(
                "INSERT INTO tender_contract_signing (tender_id, signed_date, signed_venue, our_signatories, their_signatories, notes, created_by, created_by_name, created_on)
                 VALUES (:t, :sd, :sv, :os, :ts, :nt, :u, :un, NOW())",
                $params
            );
        }

        if ($t['tender_stage'] === 'CONTRACT_SIGNING') {
            try { $this->advanceStage($tenderId, 'EXECUTION', 'Auto-advance on contract signing recorded', $userId, $userName); }
            catch (\Throwable $e) { /* already there */ }
        }
    }

    /* ── Payment receipts (log of tranches received against the contract) ─ */

    public function contractReceipts(int $tenderId): array
    {
        $db = new DB();
        return $db->mySelect(
            "SELECT * FROM tender_contract_receipts WHERE tender_id = :t ORDER BY receipt_date, c0d",
            [':t' => $tenderId]
        ) ?: [];
    }

    public function addContractReceipt(int $tenderId, array $d, int $userId, string $userName): int
    {
        $db = new DB();
        $fb = $db->insertUpdate(
            "INSERT INTO tender_contract_receipts
                (tender_id, receipt_no, receipt_date, amount, receipt_type, reference, notes, created_by, created_by_name, created_on)
             VALUES (:t, :rn, :rd, :a, :rt, :ref, :nt, :u, :un, NOW())",
            [
                ':t'   => $tenderId,
                ':rn'  => (string)($d['receipt_no'] ?? ''),
                ':rd'  => (string)($d['receipt_date'] ?? date('Y-m-d')),
                ':a'   => (float)($d['amount'] ?? 0),
                ':rt'  => (string)($d['receipt_type'] ?? 'MILESTONE'),
                ':ref' => (string)($d['reference'] ?? ''),
                ':nt'  => (string)($d['notes'] ?? ''),
                ':u'   => $userId,
                ':un'  => $userName,
            ]
        );
        return (int)($fb['last_id'] ?? 0);
    }

    public function deleteContractReceipt(int $receiptId): void
    {
        $db = new DB();
        $db->dBCxn()->prepare("DELETE FROM tender_contract_receipts WHERE c0d = ?")->execute([$receiptId]);
    }
}
