<?php
class Lpos extends Model
{
    /**
     * Type registry — icon + label + chain for a UI-friendly lookup.
     * `chain` is the DEFAULT (standard-flow) chain; large-amount requests get auto-upgraded
     * to INTERNAL_CASH_LARGE_APPROVAL via chainForAmount() below.
     */
    public static function TYPES(): array
    {
        return [
            'SUPPLIER'          => ['label' => 'Supplier LPO',     'icon' => 'fas fa-truck',                'chain' => 'GOODS_LPO_APPROVAL'],
            'PETTY_CASH'        => ['label' => 'Petty Cash',       'icon' => 'fas fa-wallet',               'chain' => 'PETTY_CASH_APPROVAL'],
            'IMPREST'           => ['label' => 'Imprest Draw',     'icon' => 'fas fa-money-bill-wave',      'chain' => 'PETTY_CASH_APPROVAL'],
            'CONTINGENCY'       => ['label' => 'Contingency',      'icon' => 'fas fa-triangle-exclamation', 'chain' => 'PETTY_CASH_APPROVAL'],
            'STAFF_ADVANCE'     => ['label' => 'Staff Advance',    'icon' => 'fas fa-user-tag',             'chain' => 'PETTY_CASH_APPROVAL'],
            'DISPOSAL_REFUND'   => ['label' => 'Disposal Refund',  'icon' => 'fas fa-recycle',              'chain' => 'PETTY_CASH_APPROVAL'],
            // Slice 8Z-c · additional internal cash types
            'PRODUCT_DISCOVERY' => ['label' => 'Product Discovery','icon' => 'fas fa-binoculars',           'chain' => 'PETTY_CASH_APPROVAL'],
            'OPERATIONAL_FLOAT' => ['label' => 'Operational Float','icon' => 'fas fa-hand-holding-usd',     'chain' => 'PETTY_CASH_APPROVAL'],
            'WORKS_CONTINGENCY' => ['label' => 'Works Contingency','icon' => 'fas fa-hard-hat',             'chain' => 'PETTY_CASH_APPROVAL'],
        ];
    }

    /**
     * Threshold above which internal cash requests need Exec approval.
     * Kept as a class constant for MVP — future: move to `finance_thresholds` table for admin config.
     */
    public const LARGE_CASH_THRESHOLD = 500000;

    /**
     * DEPRECATED as of Slice 8Z-j Batch 4. Product Discovery now uses % of PO total (admin-tunable
     * via finance_settings.discovery_exec_threshold_pct). Kept as fallback if PO total not supplied.
     */
    public const DISCOVERY_LARGE_THRESHOLD = 200000;

    /** Which internal-cash LPO types skip the LARGE-chain upgrade. Supplier LPOs use their own chain. */
    public const INTERNAL_CASH_TYPES = ['PETTY_CASH','IMPREST','CONTINGENCY','STAFF_ADVANCE','DISPOSAL_REFUND','PRODUCT_DISCOVERY','OPERATIONAL_FLOAT','WORKS_CONTINGENCY'];

    /**
     * Route selector: which chain should this LPO go through based on its type + amount?
     * SUPPLIER always uses its dedicated chain. Internal-cash types below threshold use the
     * standard chain; above threshold get the LARGE (PM → Accountant → FM → Exec) chain.
     *
     * PRODUCT_DISCOVERY threshold is a % of the Client PO total (admin-tunable via
     * finance_settings). $poTotal is required to compute this properly; without it, we fall
     * back to the flat DISCOVERY_LARGE_THRESHOLD constant.
     *
     * All other internal-cash types use the flat LARGE_CASH_THRESHOLD (500K).
     */
    public static function chainForAmount(string $type, float $amount, float $poTotal = 0.0): string
    {
        $types = self::TYPES();
        $default = $types[$type]['chain'] ?? 'PETTY_CASH_APPROVAL';
        if ($type === 'SUPPLIER') return $default;

        if ($type === 'PRODUCT_DISCOVERY') {
            if ($poTotal > 0) {
                if (!class_exists('FinanceSettings')) require_once ROOT . '/app/models/FinanceSettings.php';
                $execPct = (new FinanceSettings())->getFloat('discovery_exec_threshold_pct', 3);
                $threshold = $poTotal * ($execPct / 100.0);
            } else {
                $threshold = self::DISCOVERY_LARGE_THRESHOLD; // fallback
            }
        } else {
            $threshold = self::LARGE_CASH_THRESHOLD;
        }

        if (in_array($type, self::INTERNAL_CASH_TYPES, true) && $amount > $threshold) {
            return 'INTERNAL_CASH_LARGE_APPROVAL';
        }
        return $default;
    }

    public function nextLpoNo(string $lpo_type): string
    {
        // Slice 8i · SUPPLIER LPOs use the central admin-configurable doc_key 'LPO'.
        // Petty-cash-family types keep their legacy prefixes (kept as-is until their own
        // doc_keys are seeded — the pattern is the same YYYY-#### shape).
        if ($lpo_type === 'SUPPLIER') {
            if (!class_exists('DocNumberSettings')) require_once ROOT . '/app/models/DocNumberSettings.php';
            return (new DocNumberSettings())->allocate('LPO');
        }
        $db = new DB();
        $year   = (int) date('Y');
        $prefix = match ($lpo_type) {
            'PETTY_CASH'    => 'PC',
            'IMPREST'       => 'IMP',
            'CONTINGENCY'   => 'CNT',
            'STAFF_ADVANCE' => 'ADV',
            'DISPOSAL_REFUND' => 'DIS',
            default         => 'LPO',
        };
        $rs = $db->mySelect(
            "SELECT lpo_no FROM lpos WHERE lpo_no LIKE :p ORDER BY c0d DESC LIMIT 1",
            [':p' => $prefix . '-' . $year . '-%']
        );
        if (!empty($rs) && preg_match('/-(\d+)$/', $rs[0]['lpo_no'], $m)) {
            $seq = (int) $m[1] + 1;
        } else {
            $seq = 1;
        }
        return sprintf('%s-%d-%04d', $prefix, $year, $seq);
    }

    /** Supplier receipt types — mirrors customer receipt types on the payables side. */
    public static function SUPPLIER_RECEIPT_TYPES(): array
    {
        return [
            'NORMAL'              => ['label' => 'Normal (no EFD)',       'has_vat' => false],
            'SUPPLIER_EFD_VAT'    => ['label' => 'Supplier EFD · VAT',    'has_vat' => true],
            'SUPPLIER_EFD_NON_VAT'=> ['label' => 'Supplier EFD · Non-VAT','has_vat' => false],
        ];
    }

    public function create(array $d, int $uid, string $uname): array
    {
        $type = (string)($d['lpo_type'] ?? '');
        if (!isset(self::TYPES()[$type])) {
            return ['ok' => false, 'msg' => 'Invalid lpo_type', 'id' => 0];
        }
        $amount = (float)($d['amount'] ?? 0);
        if ($amount <= 0) {
            return ['ok' => false, 'msg' => 'Amount must be greater than zero.', 'id' => 0];
        }
        if (empty(trim((string)($d['purpose'] ?? '')))) {
            return ['ok' => false, 'msg' => 'Purpose is required.', 'id' => 0];
        }

        // Supplier EFD / VAT capture (payables side) --------------------------
        $sTypes  = self::SUPPLIER_RECEIPT_TYPES();
        $srtype  = (string)($d['supplier_receipt_type'] ?? 'NORMAL');
        if (!isset($sTypes[$srtype])) $srtype = 'NORMAL';
        $vatIncl = (int)!empty($d['vat_inclusive_flag']);
        $vatAmt  = round((float)($d['vat_amount'] ?? 0), 2);
        // If not EFD_VAT, force VAT to zero
        if (!$sTypes[$srtype]['has_vat']) { $vatAmt = 0.0; $vatIncl = 0; }
        // Net = amount − vat (regardless of inclusive/exclusive: `amount` is what we paid)
        $netAmt  = round($amount - $vatAmt, 2);
        if ($vatAmt > 0 && $netAmt < 0) {
            return ['ok' => false, 'msg' => 'VAT (' . number_format($vatAmt) . ') exceeds total amount (' . number_format($amount) . ').', 'id' => 0];
        }
        // If EFD required, ensure supplier_efd_number present
        if ($srtype !== 'NORMAL' && trim((string)($d['supplier_efd_number'] ?? '')) === '') {
            return ['ok' => false, 'msg' => 'Supplier EFD receipt number is required for ' . $sTypes[$srtype]['label'] . '.', 'id' => 0];
        }

        $db  = new DB();
        $lpo = $this->nextLpoNo($type);
        // Slice 8Z-h · Batch 2 — Discovery LPOs carry the source client_po_id so the
        // RFQ shortlist seeder (LpoRetirements::shortlistForPo) can find their findings later.
        $r = $db->insertUpdate(
            "INSERT INTO lpos
                (lpo_no, project_id, source_module, lpo_type, payee_type, payee_id, payee_name,
                 amount, currency, purpose, budget_category_id,
                 supplier_receipt_type, supplier_efd_number, supplier_tin, supplier_efd_date,
                 net_amount, vat_amount, vat_inclusive_flag,
                 sourced_from_client_po_id,
                 requested_by, requested_by_name, requested_on,
                 approval_chain_key, cash_out_status, created_by, edited_by)
             VALUES
                (:no, :pid, :src, :typ, :pty, :pid2, :pnm,
                 :amt, :cur, :prp, :bcid,
                 :srt, :sen, :stn, :sed,
                 :net, :vat, :vif,
                 :cpo,
                 :uid, :unm, NOW(),
                 :ck, 'DRAFT', :uid, :uid)",
            [
                ':no'   => $lpo,
                ':pid'  => (int)($d['project_id'] ?? 0),
                ':src'  => (string)($d['source_module'] ?? 'central'),
                ':typ'  => $type,
                ':pty'  => (string)($d['payee_type'] ?? 'vendor'),
                ':pid2' => (int)($d['payee_id'] ?? 0),
                ':pnm'  => (string)($d['payee_name'] ?? ''),
                ':amt'  => $amount,
                ':cur'  => (string)($d['currency'] ?? 'TZS'),
                ':prp'  => trim((string)$d['purpose']),
                ':bcid' => (int)($d['budget_category_id'] ?? 0),
                ':srt'  => $srtype,
                ':sen'  => trim((string)($d['supplier_efd_number'] ?? '')),
                ':stn'  => trim((string)($d['supplier_tin'] ?? '')),
                ':sed'  => !empty($d['supplier_efd_date']) ? $d['supplier_efd_date'] : null,
                ':net'  => $netAmt,
                ':vat'  => $vatAmt,
                ':vif'  => $vatIncl,
                ':cpo'  => (int)($d['sourced_from_client_po_id'] ?? 0),
                ':uid'  => $uid,
                ':unm'  => $uname,
                ':ck'   => self::TYPES()[$type]['chain'],
            ]
        );
        return ['ok' => $r['success'] > 0, 'msg' => $r['success'] > 0 ? 'LPO created.' : 'Save failed.', 'id' => (int)$r['last_id'], 'lpo_no' => $lpo];
    }

    public function byId(int $id): array
    {
        $db = new DB();
        $rs = $db->mySelect(
            "SELECT l.*, p.project_no, p.project_name
               FROM lpos l
          LEFT JOIN c_projects p ON p.c0d = l.project_id
              WHERE l.c0d = :id AND l.deleted = 0 LIMIT 1",
            [':id' => $id]
        );
        return $rs[0] ?? [];
    }

    /**
     * Unified list — filtered per role.
     * Accountant / FM / Executive / Administrator see everything.
     * Others see LPOs they raised OR that are on a queue their role can act on.
     */
    public function listForRole(string $role, int $user_id, string $status = ''): array
    {
        $db     = new DB();
        $role_u = strtoupper(trim($role));
        $params = [];
        $where  = "WHERE l.deleted = 0";
        if ($status !== '') { $where .= " AND l.cash_out_status = :st"; $params[':st'] = $status; }

        // Role-scoped visibility
        $adminRoles = ['ADMINISTRATOR','FINANCE MANAGER','ACCOUNTANT','EXECUTIVE'];
        if (!in_array($role_u, $adminRoles, true)) {
            $where .= " AND (l.requested_by = :uid OR l.next_check_by_id = :uid)";
            $params[':uid'] = $user_id;
        }

        return $db->mySelect(
            "SELECT l.c0d, l.lpo_no, l.lpo_type, l.source_module, l.payee_name, l.amount, l.currency,
                    l.purpose, l.cash_out_status, l.next_check_status, l.approval_id,
                    l.requested_by_name, l.requested_on,
                    p.project_no, p.project_name
               FROM lpos l
          LEFT JOIN c_projects p ON p.c0d = l.project_id
               $where
              ORDER BY l.c0d DESC",
            $params
        ) ?: [];
    }

    /** Update approval_id + cash_out_status after chain submission. */
    public function markSubmitted(int $lpo_id, int $approval_id): void
    {
        $db = new DB();
        $db->insertUpdate(
            "UPDATE lpos SET approval_id = :aid, cash_out_status = 'SUBMITTED', edited_on = NOW() WHERE c0d = :id",
            [':aid' => $approval_id, ':id' => $lpo_id]
        );
    }

    /**
     * Flip cash_out_status when chain reaches APPROVED / DECLINED.
     * Slice 8k · on APPROVED, allocate the canonical LPO number (was PENDING-... placeholder).
     * DECLINED keeps the PENDING ref so nothing gets an "official" LPO number without approval.
     *
     * Slice 8Z-l · for internal-cash LPOs (non-SUPPLIER), terminal APPROVED lands as BOOKED
     * — the LPO awaits Payment Schedule assembly before FM/Exec authorise funds. SUPPLIER LPOs
     * still go straight to APPROVED (existing goods flow — no schedule required).
     */
    public function markDecision(int $lpo_id, string $decision): void
    {
        $db = new DB();
        // Look up type so we know which terminal status to use.
        $row = $db->mySelect("SELECT lpo_type FROM lpos WHERE c0d = :id", [':id' => $lpo_id]);
        $type = (string)($row[0]['lpo_type'] ?? '');
        $isInternalCash = in_array($type, self::INTERNAL_CASH_TYPES, true);

        if ($decision === 'APPROVED') {
            $newStatus = $isInternalCash ? 'BOOKED' : 'APPROVED';
        } elseif ($decision === 'DECLINED') {
            $newStatus = 'DECLINED';
        } else {
            $newStatus = $decision;
        }

        // Allocate the canonical LPO number only on APPROVED (Slice 8k-LpoNoOnApprove).
        // Idempotent: skip if the row already has a non-PENDING number (re-approve safety).
        if ($newStatus === 'APPROVED') {
            $cur = $db->mySelect("SELECT lpo_no FROM lpos WHERE c0d = :id", [':id' => $lpo_id]);
            $isPending = !empty($cur) && strpos((string)$cur[0]['lpo_no'], 'PENDING-') === 0;
            if ($isPending) {
                if (!class_exists('DocNumberSettings')) require_once ROOT . '/app/models/DocNumberSettings.php';
                $realNo = (new DocNumberSettings())->allocate('LPO');
                $db->insertUpdate(
                    "UPDATE lpos SET lpo_no = :no, cash_out_status = :s, edited_on = NOW() WHERE c0d = :id",
                    [':no' => $realNo, ':s' => $newStatus, ':id' => $lpo_id]
                );
                return;
            }
        }
        $db->insertUpdate(
            "UPDATE lpos SET cash_out_status = :s, edited_on = NOW() WHERE c0d = :id",
            [':s' => $newStatus, ':id' => $lpo_id]
        );
    }

    /** Batch-flip: used by RFQ finalize when one chain covers many LPOs. Same 8k semantics. */
    public function markDecisionForApprovalBatch(int $approval_id, string $decision): int
    {
        $newStatus = ($decision === 'APPROVED') ? 'APPROVED' : (($decision === 'DECLINED') ? 'DECLINED' : $decision);
        $db = new DB();

        if ($newStatus === 'APPROVED') {
            // Fetch and allocate a real number per PENDING row in this batch
            $rows = $db->mySelect(
                "SELECT c0d, lpo_no FROM lpos WHERE approval_id = :aid AND deleted = 0",
                [':aid' => $approval_id]
            ) ?: [];
            if (!class_exists('DocNumberSettings') && !empty($rows)) require_once ROOT . '/app/models/DocNumberSettings.php';
            $dns = !empty($rows) ? new DocNumberSettings() : null;
            $count = 0;
            foreach ($rows as $r) {
                $isPending = strpos((string)$r['lpo_no'], 'PENDING-') === 0;
                if ($isPending && $dns) {
                    $realNo = $dns->allocate('LPO');
                    $db->insertUpdate(
                        "UPDATE lpos SET lpo_no = :no, cash_out_status = :s, edited_on = NOW() WHERE c0d = :id",
                        [':no' => $realNo, ':s' => $newStatus, ':id' => (int)$r['c0d']]
                    );
                } else {
                    $db->insertUpdate(
                        "UPDATE lpos SET cash_out_status = :s, edited_on = NOW() WHERE c0d = :id",
                        [':s' => $newStatus, ':id' => (int)$r['c0d']]
                    );
                }
                $count++;
            }
            return $count;
        }

        $r = $db->insertUpdate(
            "UPDATE lpos SET cash_out_status = :s, edited_on = NOW()
              WHERE approval_id = :aid AND deleted = 0",
            [':s' => $newStatus, ':aid' => $approval_id]
        );
        return (int)$r['success'];
    }

    public function itemsFor(int $lpo_id): array
    {
        $db = new DB();
        return $db->mySelect(
            "SELECT * FROM lpo_items WHERE lpo_id = :id ORDER BY item_no ASC",
            [':id' => $lpo_id]
        ) ?: [];
    }

    /**
     * Create a SUPPLIER lpo header + insert lpo_items lines.
     * Used by the RFQ finalize path — one LPO per awarded supplier.
     */
    /**
     * Slice 8a-2 · Preview what would be drafted for a project.
     * Returns an array of vendor groups: [{vendor_id, vendor_name, rfq_id, lines[], total, already_lpo_id}, ...].
     * `already_lpo_id` is set if we've already drafted a batch for this project × vendor (idempotent guard).
     */
    public function draftPreviewForProject(int $projectId): array
    {
        if ($projectId <= 0) return [];
        $db = new DB();
        // Pull every awarded quote-line under this project, joined up to vendor + client PO item.
        $rows = $db->mySelect(
            "SELECT
                r.c0d           AS rfq_id,
                r.client_po_id  AS client_po_id,
                q.c0d           AS quote_id,
                q.vendor_id, q.vendor_name, q.vendor_tin,
                qi.c0d          AS quote_item_id,
                qi.unit_price   AS awarded_unit_price,
                qi.qty_available,
                qi.lead_time_days,
                cpi.c0d         AS client_po_item_id,
                cpi.item_no, cpi.description, cpi.qty AS ordered_qty, cpi.unit
               FROM goods_rfqs r
               JOIN goods_client_pos cp        ON cp.c0d = r.client_po_id AND cp.deleted = 0
               JOIN goods_rfq_quotes q         ON q.rfq_id = r.c0d AND q.deleted = 0
               JOIN goods_rfq_quote_items qi   ON qi.rfq_quote_id = q.c0d AND qi.awarded_flag = 1
               JOIN goods_client_po_items cpi  ON cpi.c0d = qi.client_po_item_id
              WHERE cp.project_id = :p AND r.deleted = 0
              ORDER BY q.vendor_name, cpi.item_no",
            [':p' => $projectId]
        ) ?: [];

        // Group by (rfq_id, vendor_id) so each supplier LPO comes from one RFQ.
        $groups = [];
        foreach ($rows as $r) {
            $key = ((int)$r['rfq_id']) . ':' . ((int)$r['vendor_id']);
            if (!isset($groups[$key])) {
                $groups[$key] = [
                    'rfq_id'          => (int)$r['rfq_id'],
                    'client_po_id'    => (int)$r['client_po_id'],
                    'vendor_id'       => (int)$r['vendor_id'],
                    'vendor_name'     => (string)$r['vendor_name'],
                    'vendor_tin'      => (string)$r['vendor_tin'],
                    'lines'           => [],
                    'total'           => 0.0,
                    'already_lpo_id'  => 0,
                    'already_lpo_no'  => '',
                ];
            }
            $qty  = (float)($r['qty_available'] > 0 ? $r['qty_available'] : $r['ordered_qty']);
            $up   = (float)$r['awarded_unit_price'];
            $line = round($qty * $up, 2);
            $groups[$key]['lines'][] = [
                'client_po_item_id' => (int)$r['client_po_item_id'],
                'item_no'           => (int)$r['item_no'],
                'description'       => (string)$r['description'],
                'qty'               => $qty,
                'unit'              => (string)$r['unit'],
                'unit_price'        => $up,
                'line_total'        => $line,
                'lead_time_days'    => (int)$r['lead_time_days'],
            ];
            $groups[$key]['total'] += $line;
        }

        // Idempotency: has a supplier LPO already been drafted for this (project, rfq, vendor)?
        if (!empty($groups)) {
            $existing = $db->mySelect(
                "SELECT c0d, lpo_no, sourced_from_rfq_id, payee_id
                   FROM lpos
                  WHERE project_id = :p AND lpo_type = 'SUPPLIER' AND deleted = 0",
                [':p' => $projectId]
            ) ?: [];
            foreach ($existing as $e) {
                $k = ((int)$e['sourced_from_rfq_id']) . ':' . ((int)$e['payee_id']);
                if (isset($groups[$k])) {
                    $groups[$k]['already_lpo_id'] = (int)$e['c0d'];
                    $groups[$k]['already_lpo_no'] = (string)$e['lpo_no'];
                }
            }
        }

        return array_values($groups);
    }

    /**
     * Slice 8a-2 · Draft one supplier LPO per vendor group. Idempotent — skips groups that already
     * have a materialised LPO. Returns [{ok, vendor_name, lpo_id, lpo_no, msg}, ...].
     */
    public function draftBatchForProject(int $projectId, int $uid, string $uname): array
    {
        $preview = $this->draftPreviewForProject($projectId);
        if (empty($preview)) return [];

        // Look up project header for the shared context (currency + budget mapping).
        $db  = new DB();
        $prj = $db->mySelect("SELECT project_no, project_name, currency FROM c_projects WHERE c0d = :p", [':p' => $projectId]);
        $currency = (string)($prj[0]['currency'] ?? 'TZS');
        $projTag  = (string)($prj[0]['project_no'] ?? ('#' . $projectId));

        $out = [];
        foreach ($preview as $g) {
            if ($g['already_lpo_id'] > 0) {
                $out[] = ['ok' => true, 'skipped' => true, 'vendor_name' => $g['vendor_name'],
                          'lpo_id' => $g['already_lpo_id'], 'lpo_no' => $g['already_lpo_no'],
                          'msg' => 'Already drafted — kept existing.'];
                continue;
            }
            $r = $this->createSupplierWithItems(
                [
                    'project_id'                => $projectId,
                    'vendor_id'                 => $g['vendor_id'],
                    'vendor_name'               => $g['vendor_name'],
                    'currency'                  => $currency,
                    'purpose'                   => 'Supplier LPO drafted from RFQ awards · project ' . $projTag,
                    'source_module'             => 'goods',
                    'sourced_from_rfq_id'       => $g['rfq_id'],
                    'sourced_from_client_po_id' => $g['client_po_id'],
                ],
                $g['lines'],
                $uid, $uname
            );
            $out[] = array_merge(['vendor_name' => $g['vendor_name'], 'skipped' => false], $r);
        }
        return $out;
    }

    public function createSupplierWithItems(array $d, array $items, int $uid, string $uname): array
    {
        if (empty($items))           return ['ok' => false, 'msg' => 'No items to include on the LPO.', 'id' => 0];
        if (empty($d['vendor_id']))  return ['ok' => false, 'msg' => 'vendor_id is required.',         'id' => 0];
        if (empty($d['project_id'])) return ['ok' => false, 'msg' => 'project_id is required.',        'id' => 0];

        $total = 0.0;
        foreach ($items as $it) {
            $q = (float)($it['qty']        ?? 0);
            $u = (float)($it['unit_price'] ?? 0);
            $total += round($q * $u, 2);
        }
        if ($total <= 0) return ['ok' => false, 'msg' => 'Total amount must be > 0.', 'id' => 0];

        $db  = new DB();
        // Slice 8k-LpoNoOnApprove: DRAFT LPOs get a PENDING placeholder ref; the real
        // sequential lpo_no is only allocated at chain terminal APPROVED (markDecision).
        // Placeholder is unique-safe because it includes timestamp + random suffix and is
        // rewritten to the canonical LPO-YYYY-#### on approval.
        $lpo = 'PENDING-' . date('YmdHis') . '-' . mt_rand(1000, 9999);
        $r = $db->insertUpdate(
            "INSERT INTO lpos
                (lpo_no, project_id, source_module, lpo_type, payee_type, payee_id, payee_name,
                 amount, currency, purpose, budget_category_id,
                 sourced_from_rfq_id, sourced_from_client_po_id,
                 requested_by, requested_by_name, requested_on,
                 approval_chain_key, cash_out_status, created_by, edited_by)
             VALUES
                (:no, :pid, :src, 'SUPPLIER', 'vendor', :vid, :vnm,
                 :amt, :cur, :prp, :bcid,
                 :rfq, :cpo,
                 :uid, :unm, NOW(),
                 :ck, 'DRAFT', :uid, :uid)",
            [
                ':no'   => $lpo,
                ':pid'  => (int)$d['project_id'],
                ':src'  => (string)($d['source_module'] ?? 'goods'),
                ':vid'  => (int)$d['vendor_id'],
                ':vnm'  => (string)($d['vendor_name'] ?? ''),
                ':amt'  => round($total, 2),
                ':cur'  => (string)($d['currency'] ?? 'TZS'),
                ':prp'  => (string)($d['purpose'] ?? ''),
                ':bcid' => (int)($d['budget_category_id'] ?? 0),
                ':rfq'  => (int)($d['sourced_from_rfq_id']       ?? 0),
                ':cpo'  => (int)($d['sourced_from_client_po_id'] ?? 0),
                ':uid'  => $uid,
                ':unm'  => $uname,
                ':ck'   => self::TYPES()['SUPPLIER']['chain'],
            ]
        );
        $lpoId = (int)$r['last_id'];
        if ($lpoId <= 0) return ['ok' => false, 'msg' => 'Save failed on header.', 'id' => 0];

        $n = 0;
        foreach ($items as $it) {
            $n++;
            $q  = (float)($it['qty']        ?? 0);
            $u  = (float)($it['unit_price'] ?? 0);
            $lt = round($q * $u, 2);
            $db->insertUpdate(
                "INSERT INTO lpo_items (lpo_id, item_no, description, qty, unit, unit_price, line_total,
                                        budget_category_id, client_po_item_id, notes)
                 VALUES (:lid, :n, :d, :q, :u, :up, :lt, :bcid, :cpi, :nt)",
                [
                    ':lid'  => $lpoId, ':n' => $n,
                    ':cpi'  => (int)($it['client_po_item_id'] ?? 0),
                    ':d'    => (string)($it['description'] ?? ''),
                    ':q'    => $q,
                    ':u'    => (string)($it['unit'] ?? ''),
                    ':up'   => $u,
                    ':lt'   => $lt,
                    ':bcid' => (int)($it['budget_category_id'] ?? 0),
                    ':nt'   => (string)($it['notes'] ?? ''),
                ]
            );
        }
        return ['ok' => true, 'msg' => 'Supplier LPO created (' . $lpo . ').', 'id' => $lpoId, 'lpo_no' => $lpo];
    }

    public function setApprovalId(int $lpo_id, int $approval_id): void
    {
        $db = new DB();
        $db->insertUpdate(
            "UPDATE lpos SET approval_id = :aid, cash_out_status = 'SUBMITTED', edited_on = NOW() WHERE c0d = :id",
            [':aid' => $approval_id, ':id' => $lpo_id]
        );
    }

    /* ── Slice 8Z-k · payment method + disbursement plumbing ───────── */

    /**
     * Requester states their preferred method + destination account on request creation.
     * Non-fatal: if unspecified, Accountant picks at Prepare step.
     */
    public function setRequesterPayment(int $lpoId, ?string $method, ?int $accountId): void
    {
        if ($method !== null && !in_array($method, ['PETTY_CASH','CASH_HANDOVER','MOBILE_MONEY','BANK_TRANSFER','CHEQUE'], true)) {
            return;
        }
        $db = new DB();
        $db->insertUpdate(
            "UPDATE lpos SET preferred_method = :m, preferred_account_id = :a, edited_on = NOW() WHERE c0d = :id",
            [':m'=>$method, ':a'=>$accountId ?: null, ':id'=>$lpoId]
        );
    }

    /**
     * Accountant Prepare step — set confirmed disburse method + account + scheduled date + prep notes.
     * If method or account differs from requester's preference, $overrideReason is mandatory
     * (enforced upstream in FinChainsController::accountantPrepAjAction).
     */
    public function setAccountantPrep(int $lpoId, array $d): array
    {
        $method = (string)($d['disburse_method'] ?? '');
        if (!in_array($method, ['PETTY_CASH','CASH_HANDOVER','MOBILE_MONEY','BANK_TRANSFER','CHEQUE'], true)) {
            return ['ok' => false, 'msg' => 'Invalid disburse method.'];
        }
        $acctId = (int)($d['disburse_account_id'] ?? 0);
        $sched  = (string)($d['scheduled_disburse_date'] ?? '');
        $notes  = trim((string)($d['accountant_prep_notes'] ?? ''));
        $reason = trim((string)($d['override_reason'] ?? ''));

        $db = new DB();
        $lpo = $db->mySelect("SELECT preferred_method, preferred_account_id FROM lpos WHERE c0d = :id AND deleted = 0", [':id' => $lpoId]);
        if (empty($lpo)) return ['ok' => false, 'msg' => 'LPO not found.'];

        // Override check — only mandatory if requester expressed a preference and Accountant changed it.
        $prefMethod = (string)($lpo[0]['preferred_method'] ?? '');
        $prefAcct   = (int)($lpo[0]['preferred_account_id'] ?? 0);
        $isOverride = ($prefMethod !== '' && $prefMethod !== $method)
                   || ($prefAcct > 0 && $acctId > 0 && $prefAcct !== $acctId);
        if ($isOverride && $reason === '') {
            return ['ok' => false, 'msg' => 'Override reason required — you are changing the requester\'s preferred method or account.'];
        }

        // CASH_HANDOVER may require reason (finance_settings toggle)
        if ($method === 'CASH_HANDOVER') {
            if (!class_exists('FinanceSettings')) require_once ROOT . '/app/models/FinanceSettings.php';
            $needsReason = (int)(new FinanceSettings())->getFloat('cash_handover_requires_reason', 1) === 1;
            if ($needsReason && $reason === '' && $notes === '') {
                return ['ok' => false, 'msg' => 'Cash handover requires an Accountant reason (audit trail). Fill "Override reason" or the prep notes.'];
            }
        }

        $db->insertUpdate(
            "UPDATE lpos SET
                disburse_method         = :m,
                disburse_account_id     = :a,
                scheduled_disburse_date = :sd,
                accountant_prep_notes   = :n,
                override_reason         = :r,
                edited_on = NOW()
              WHERE c0d = :id",
            [
                ':m'=>$method,
                ':a'=>$acctId ?: null,
                ':sd'=>$sched ?: null,
                ':n'=>$notes ?: null,
                ':r'=>$reason ?: null,
                ':id'=>$lpoId,
            ]
        );
        return ['ok' => true];
    }

    /**
     * Slice 8Z-m · Apply a payment (partial or full) from a schedule disbursement.
     * Increments amount_paid; flips LPO to PAID if fully paid, otherwise back to BOOKED
     * (remainder available for another schedule). Stamps first_partial_paid_on for the
     * 30-day retirement escape hatch. Fires CASH_DISBURSED (partial or full).
     */
    public function applyPayment(int $lpoId, float $amount, string $ref, int $userId, string $userName): array
    {
        if ($amount <= 0) return ['ok' => false, 'msg' => 'Amount must be > 0.'];
        $db = new DB();
        $r = $db->mySelect("SELECT lpo_no, amount, amount_paid, currency, requested_by, disburse_method, first_partial_paid_on FROM lpos WHERE c0d = :id AND deleted = 0", [':id' => $lpoId]);
        if (empty($r)) return ['ok' => false, 'msg' => 'LPO not found.'];
        $prevPaid   = (float)$r[0]['amount_paid'];
        $total      = (float)$r[0]['amount'];
        $newPaid    = round($prevPaid + $amount, 2);
        $fullyPaid  = ($newPaid + 0.005) >= $total;
        $newStatus  = $fullyPaid ? 'PAID' : 'BOOKED';
        $firstPart  = $r[0]['first_partial_paid_on'] ?: ($prevPaid === 0.0 ? date('Y-m-d H:i:s') : null);

        $db->insertUpdate(
            "UPDATE lpos SET
                amount_paid = :ap,
                cash_out_status = :st,
                disburse_ref = COALESCE(NULLIF(:ref, ''), disburse_ref),
                disbursed_at = NOW(),
                disbursed_by = :u,
                disbursed_by_name = :un,
                first_partial_paid_on = COALESCE(first_partial_paid_on, :fp),
                edited_on = NOW()
              WHERE c0d = :id",
            [':ap'=>$newPaid, ':st'=>$newStatus, ':ref'=>trim($ref), ':u'=>$userId, ':un'=>$userName, ':fp'=>$firstPart, ':id'=>$lpoId]
        );

        // Notify requester with actual paid amount (partial or full).
        try {
            if (!class_exists('Notifications')) require_once ROOT . '/app/models/Notifications.php';
            $reqId = (int)$r[0]['requested_by'];
            if ($reqId > 0) {
                $subject = $fullyPaid
                    ? 'Cash sent (full): ' . $r[0]['lpo_no']
                    : 'Cash sent (partial): ' . $r[0]['lpo_no'];
                $body = $r[0]['currency'] . ' ' . number_format($amount) . ' via ' . strtolower(str_replace('_',' ', (string)$r[0]['disburse_method']))
                      . ($ref ? ' · ref ' . $ref : '')
                      . ($fullyPaid ? '' : ' · remaining ' . number_format($total - $newPaid));
                (new Notifications())->notify(
                    $reqId, 'CASH_DISBURSED', $subject, $body,
                    'Lpos/view/' . $lpoId, 'lpo', $lpoId, $userId, $userName
                );
            }
        } catch (\Throwable $e) { /* best-effort */ }

        return ['ok' => true, 'lpo_no' => $r[0]['lpo_no'], 'fully_paid' => $fullyPaid, 'amount_paid' => $newPaid];
    }

    /**
     * Mark disbursement done (post-Exec approval). Captures ref + timestamp + actor.
     * For PETTY_CASH, prefer PettyCashBox::disburse which also updates the tin balance.
     */
    public function markDisbursed(int $lpoId, string $ref, int $userId, string $userName): array
    {
        $db = new DB();
        $r = $db->mySelect("SELECT lpo_no, cash_out_status, disburse_method FROM lpos WHERE c0d = :id AND deleted = 0", [':id' => $lpoId]);
        if (empty($r))                                       return ['ok' => false, 'msg' => 'LPO not found.'];
        // Slice 8Z-l · APPROVED (via schedule) or BOOKED (petty cash bypass) can both disburse.
        if (!in_array($r[0]['cash_out_status'], ['APPROVED','BOOKED'], true)) return ['ok' => false, 'msg' => 'LPO is ' . $r[0]['cash_out_status'] . ' — only APPROVED or BOOKED (petty cash bypass) LPOs can be marked disbursed.'];
        $db->insertUpdate(
            "UPDATE lpos SET
                cash_out_status = 'PAID',
                disburse_ref    = :ref,
                disbursed_at    = NOW(),
                disbursed_by    = :u,
                disbursed_by_name = :un,
                edited_on = NOW()
              WHERE c0d = :id",
            [':ref'=>trim($ref) ?: null, ':u'=>$userId, ':un'=>$userName, ':id'=>$lpoId]
        );
        return ['ok' => true, 'lpo_no' => $r[0]['lpo_no']];
    }

    /**
     * Slice 8Z-d · Batch 2 — open advances for a user.
     * An internal-cash LPO that is PAID or ISSUED and has NO APPROVED retirement
     * yet counts as "open" and must be retired before the same user can raise
     * another cash request (the blocking gate on CashRequests::saveAj).
     *
     * SUPPLIER LPOs never require a retirement (goods flow closes them via GRN + invoice).
     */
    public function openAdvancesForUser(int $userId): array
    {
        $db = new DB();
        return $db->mySelect(
            "SELECT l.c0d, l.lpo_no, l.lpo_type, l.amount, l.currency, l.purpose, l.cash_out_status,
                    r.c0d AS retirement_id, r.status AS retirement_status
               FROM lpos l
          LEFT JOIN lpo_retirements r
                 ON r.lpo_id = l.c0d AND r.deleted = 0 AND r.status IN ('DRAFT','SUBMITTED','APPROVED')
              WHERE l.requested_by = :u
                AND l.deleted = 0
                AND l.lpo_type <> 'SUPPLIER'
                AND l.cash_out_status IN ('ISSUED','PAID')
                AND (r.c0d IS NULL OR r.status <> 'APPROVED')
              ORDER BY l.c0d DESC",
            [':u' => $userId]
        ) ?: [];
    }

    /* ── Slice 8Z-q · Receipt confirmation ─────────────────────────────────
     * Requester (procurement, PC, whoever raised the request) confirms that the money
     * actually arrived post-disbursement. Confirmation starts the retirement clock —
     * `retirement_due_date` = today + finance_settings.retirement_default_days (14).
     * If not confirmed within finance_settings.receipt_auto_confirm_days (5), a scheduled
     * task auto-confirms (marks receipt_confirm_auto = 1) so retirement can still be filed.
     */
    public function confirmReceipt(int $lpoId, ?string $note, int $userId, string $userName, bool $auto = false): array
    {
        $db = new DB();
        $r = $db->mySelect(
            "SELECT lpo_no, cash_out_status, receipt_confirmed_at, requested_by, currency, amount, amount_paid
               FROM lpos WHERE c0d = :id AND deleted = 0",
            [':id' => $lpoId]
        );
        if (empty($r))                                             return ['ok'=>false,'msg'=>'LPO not found.'];
        $row = $r[0];
        if (!empty($row['receipt_confirmed_at']))                  return ['ok'=>false,'msg'=>'Receipt already confirmed.'];
        if (!in_array($row['cash_out_status'], ['PAID','ISSUED'], true))
                                                                    return ['ok'=>false,'msg'=>'LPO must be PAID before receipt can be confirmed (currently ' . $row['cash_out_status'] . ').'];
        // For strict paths (non-auto), require requester or delegate; controller enforces.

        // Default days from finance_settings, fallback 14.
        $days = 14;
        try {
            $s = $db->mySelect("SELECT setting_value FROM finance_settings WHERE setting_key = 'retirement_default_days'");
            if (!empty($s) && (int)$s[0]['setting_value'] > 0) $days = (int)$s[0]['setting_value'];
        } catch (\Throwable $e) { /* keep default */ }

        $due = date('Y-m-d', strtotime('+' . $days . ' days'));

        $db->insertUpdate(
            "UPDATE lpos SET
                receipt_confirmed_at  = NOW(),
                receipt_confirmed_by  = :u,
                receipt_confirmed_by_name = :un,
                receipt_confirm_note  = :nt,
                receipt_confirm_auto  = :auto,
                retirement_due_date   = :due,
                edited_on             = NOW()
              WHERE c0d = :id",
            [
                ':u'    => $userId, ':un' => $userName,
                ':nt'   => trim((string)$note) ?: null,
                ':auto' => $auto ? 1 : 0,
                ':due'  => $due,
                ':id'   => $lpoId,
            ]
        );

        // Notify requester (unless requester is the one confirming).
        try {
            if (!class_exists('Notifications')) require_once ROOT . '/app/models/Notifications.php';
            $reqId = (int)$row['requested_by'];
            if ($reqId > 0 && ($auto || $reqId !== $userId)) {
                $subj = $auto
                    ? 'Receipt auto-confirmed: ' . $row['lpo_no']
                    : 'Receipt confirmed: ' . $row['lpo_no'];
                $body = 'Retirement due by ' . $due . ' (' . $days . ' days).'
                      . ($auto ? ' Auto-confirmed after ' . (int)($this->settingInt('receipt_auto_confirm_days', 5)) . ' days without manual confirmation.' : '');
                (new Notifications())->notify(
                    $reqId, ($auto ? 'RECEIPT_AUTO_CONFIRMED' : 'RECEIPT_CONFIRMED'),
                    $subj, $body,
                    'Lpos/view/' . $lpoId, 'lpo', $lpoId, $userId, $userName
                );
            }
        } catch (\Throwable $e) { /* best-effort */ }

        return ['ok'=>true, 'lpo_no'=>$row['lpo_no'], 'retirement_due_date'=>$due];
    }

    private function settingInt(string $key, int $default): int
    {
        try {
            $db = new DB();
            $r = $db->mySelect("SELECT setting_value FROM finance_settings WHERE setting_key = :k", [':k' => $key]);
            $v = (int)($r[0]['setting_value'] ?? 0);
            return $v > 0 ? $v : $default;
        } catch (\Throwable $e) { return $default; }
    }

    /**
     * Slice 8Z-r · Issue an APPROVED supplier LPO to the supplier.
     * Captures the dispatch method + reference + optional signed-LPO PDF.
     * Flips APPROVED → ISSUED (SUPPLIER lpo_type only) and stamps issued_at + actor.
     * No outbound email/WhatsApp sending in this slice — audit trail only.
     */
    public function issueToSupplier(int $lpoId, array $d, ?array $file, int $userId, string $userName): array
    {
        $db = new DB();
        $r  = $db->mySelect(
            "SELECT lpo_no, lpo_type, cash_out_status, payee_id, payee_name FROM lpos WHERE c0d = :id AND deleted = 0",
            [':id' => $lpoId]
        );
        if (empty($r))                                       return ['ok'=>false,'msg'=>'LPO not found.'];
        $row = $r[0];
        if ((string)$row['lpo_type'] !== 'SUPPLIER')         return ['ok'=>false,'msg'=>'Only SUPPLIER LPOs can be issued to a supplier.'];
        if ((string)$row['cash_out_status'] !== 'APPROVED')  return ['ok'=>false,'msg'=>'LPO must be APPROVED to issue (currently ' . $row['cash_out_status'] . ').'];

        $methods = ['EMAIL','PORTAL','WHATSAPP','COURIER','IN_PERSON','OTHER'];
        $method  = strtoupper(trim((string)($d['issue_method'] ?? '')));
        if (!in_array($method, $methods, true)) return ['ok'=>false,'msg'=>'Pick a valid issue method.'];
        $ref  = trim((string)($d['issue_ref']  ?? ''));
        $note = trim((string)($d['issue_note'] ?? ''));

        // Optional attachment stash — reuse the same folder pattern as retirements/receipts.
        $attRel = null; $attOrig = null; $attMime = null; $attSize = 0;
        if ($file && !empty($file['tmp_name']) && is_uploaded_file($file['tmp_name'])) {
            [$attRel, $attOrig, $attMime, $attSize] = $this->stashIssueAttachment($file, $lpoId);
        }

        $db->insertUpdate(
            "UPDATE lpos SET
                cash_out_status                = 'ISSUED',
                issue_method                   = :m,
                issue_ref                      = :r,
                issue_note                     = :nt,
                issue_attachment_path          = COALESCE(:ap, issue_attachment_path),
                issue_attachment_original_name = COALESCE(:ao, issue_attachment_original_name),
                issue_attachment_mime          = COALESCE(:am, issue_attachment_mime),
                issue_attachment_size_bytes    = COALESCE(:as, issue_attachment_size_bytes),
                issued_at                      = NOW(),
                issued_by                      = :u,
                issued_by_name                 = :un,
                edited_on                      = NOW()
              WHERE c0d = :id",
            [
                ':m'=>$method, ':r'=>$ref ?: null, ':nt'=>$note ?: null,
                ':ap'=>$attRel, ':ao'=>$attOrig, ':am'=>$attMime, ':as'=>$attSize > 0 ? $attSize : null,
                ':u'=>$userId, ':un'=>$userName, ':id'=>$lpoId,
            ]
        );

        // Notify Procurement group (internal ack that LPO is out the door) + PC + Accountant.
        try {
            if (!class_exists('Notifications')) require_once ROOT . '/app/models/Notifications.php';
            $body = 'Issued via ' . strtolower(str_replace('_',' ', $method))
                  . ($ref  !== '' ? ' · ref ' . $ref : '')
                  . (!empty($row['payee_name']) ? ' · to ' . $row['payee_name'] : '');
            (new Notifications())->notifyGroup(
                'Accountant', 'LPO_ISSUED_TO_SUPPLIER',
                'Supplier LPO issued: ' . $row['lpo_no'], $body,
                'Lpos/view/' . $lpoId, 'lpo', $lpoId, $userId, $userName
            );
        } catch (\Throwable $e) { /* best-effort */ }

        return ['ok'=>true, 'lpo_no'=>$row['lpo_no'], 'issue_method'=>$method];
    }

    private function stashIssueAttachment(array $file, int $lpoId): array
    {
        $err = (int)($file['error'] ?? 0);
        if ($err !== UPLOAD_ERR_OK)                                                 throw new RuntimeException('Upload error code ' . $err);
        $size = (int)($file['size'] ?? 0);
        if ($size <= 0 || $size > 10 * 1024 * 1024)                                 throw new RuntimeException('Attachment size invalid.');
        $mime = @mime_content_type($file['tmp_name']) ?: (string)($file['type'] ?? '');
        $ok = ['application/pdf','image/jpeg','image/png','image/webp','image/gif','application/vnd.openxmlformats-officedocument.wordprocessingml.document','application/msword'];
        if (!in_array($mime, $ok, true))                                            throw new RuntimeException('Unsupported attachment type: ' . $mime);
        $orig = (string)($file['name'] ?? 'signed-lpo');
        $safe = preg_replace('/[^A-Za-z0-9._\-]+/', '_', $orig);
        $ext  = pathinfo($safe, PATHINFO_EXTENSION);
        $base = pathinfo($safe, PATHINFO_FILENAME) . '_' . substr(bin2hex(random_bytes(4)), 0, 8) . ($ext ? '.' . $ext : '');
        $dir  = ROOT . '/attachments/lpo-issue/' . $lpoId;
        if (!is_dir($dir) && !@mkdir($dir, 0777, true) && !is_dir($dir))            throw new RuntimeException('Could not create attachment folder.');
        $abs = $dir . '/' . $base;
        if (!@move_uploaded_file($file['tmp_name'], $abs))                          throw new RuntimeException('Failed to save attachment.');
        return ['attachments/lpo-issue/' . $lpoId . '/' . $base, $orig, $mime, $size];
    }

    /**
     * Slice 8Z-q · All LPOs (supplier + internal cash) sourced from a specific client PO.
     * Used on the GoodsClientPos view "Related LPOs" card.
     */
    public function forClientPo(int $clientPoId): array
    {
        if ($clientPoId <= 0) return [];
        $db = new DB();
        return $db->mySelect(
            "SELECT c0d, lpo_no, lpo_type, payee_name, amount, amount_paid, currency, cash_out_status,
                    disbursed_at, receipt_confirmed_at, retirement_due_date, requested_by_name, requested_on
               FROM lpos
              WHERE deleted = 0 AND sourced_from_client_po_id = :p
              ORDER BY c0d DESC",
            [':p' => $clientPoId]
        ) ?: [];
    }
}
