<?php
class GoodsClientPos extends Model
{
    public function nextClientPoNo(): string
    {
        $db   = new DB();
        $year = (int) date('Y');
        $rs = $db->mySelect(
            "SELECT client_po_no FROM goods_client_pos WHERE client_po_no LIKE :p ORDER BY c0d DESC LIMIT 1",
            [':p' => 'CPO-' . $year . '-%']
        );
        $seq = (!empty($rs) && preg_match('/-(\d+)$/', $rs[0]['client_po_no'], $m)) ? (int)$m[1] + 1 : 1;
        return sprintf('CPO-%d-%04d', $year, $seq);
    }

    /**
     * Create a DRAFT client PO with N line items.
     * $d = [ 'project_id','customer_id','po_date','delivery_by','currency','vat_rate',
     *        'default_margin_pct','customer_po_ref','payment_terms','delivery_poc',
     *        'delivery_poc_phone','delivery_address','notes',
     *        'items' => [ ['description','qty','unit','unit_price_sell','unit_price_cog'], ... ] ]
     */
    public function create(array $d, int $uid, string $uname): array
    {
        $items = $d['items'] ?? [];
        if (empty($items)) return ['ok' => false, 'msg' => 'Add at least one line item.', 'id' => 0];

        $pid = (int)($d['project_id']  ?? 0);
        $cid = (int)($d['customer_id'] ?? 0);
        if ($pid <= 0 || $cid <= 0) return ['ok' => false, 'msg' => 'Project and customer are required.', 'id' => 0];

        $db  = new DB();
        // Ops gate — project must be onboarded or migrated ------------------
        $prj = $db->mySelect(
            "SELECT project_no, crm_status, migrated_flag, currency, project_customer, project_type
               FROM c_projects WHERE c0d = :id AND deleted = 0",
            [':id' => $pid]
        );
        if (empty($prj)) return ['ok' => false, 'msg' => 'Project not found.', 'id' => 0];
        $p = $prj[0];
        if ($p['crm_status'] !== 'APPROVED' && (int)$p['migrated_flag'] !== 1) {
            return ['ok' => false, 'msg' => 'Project has not passed CRM onboarding — cannot create Client PO.', 'id' => 0];
        }

        // Customer name lookup
        $cust = $db->mySelect("SELECT fname, lname FROM customers WHERE c0d = :id AND deleted = 0", [':id' => $cid]);
        $custNm = !empty($cust) ? trim(($cust[0]['fname'] ?? '') . ' ' . ($cust[0]['lname'] ?? '')) : ($p['project_customer'] ?? '');

        // Compute totals from items
        $vatRate     = (float)($d['vat_rate'] ?? 18);
        $subtotalSell = 0.0; $subtotalCog = 0.0;
        $cleanItems  = [];
        foreach ($items as $it) {
            $qty  = (float)($it['qty'] ?? 0);
            $upS  = (float)($it['unit_price_sell'] ?? 0);
            $upC  = (float)($it['unit_price_cog']  ?? 0);
            $desc = trim((string)($it['description'] ?? ''));
            if ($qty <= 0 || $desc === '' || $upS < 0 || $upC < 0) continue;
            $ltS = round($qty * $upS, 2);
            $ltC = round($qty * $upC, 2);
            $subtotalSell += $ltS;
            $subtotalCog  += $ltC;
            $cleanItems[] = [
                'description' => $desc, 'qty' => $qty,
                'unit' => (string)($it['unit'] ?? ''),
                'unit_price_sell' => $upS, 'unit_price_cog' => $upC,
                'line_total_sell' => $ltS, 'line_total_cog' => $ltC,
            ];
        }
        if (empty($cleanItems)) return ['ok' => false, 'msg' => 'No valid line items.', 'id' => 0];

        $vatAmt = round($subtotalSell * $vatRate / 100, 2);
        $total  = round($subtotalSell + $vatAmt, 2);

        $poNo = $this->nextClientPoNo();
        $r = $db->insertUpdate(
            "INSERT INTO goods_client_pos
                (client_po_no, customer_po_ref, project_id, customer_id, customer_name, source_module,
                 po_date, delivery_by, currency, vat_rate, subtotal_sell, subtotal_cog, vat_amount, total,
                 default_margin_pct, status, payment_terms, delivery_poc, delivery_poc_phone, delivery_address,
                 notes, created_by, created_by_name, edited_by)
             VALUES
                (:pn, :ref, :pid, :cid, :cnm, :src,
                 :pd, :db2, :cur, :vr, :sub, :cog, :vat, :tot,
                 :mgn, 'DRAFT', :pt, :poc, :phn, :addr,
                 :nt, :uid, :unm, :uid)",
            [
                ':pn'   => $poNo,
                ':ref'  => (string)($d['customer_po_ref'] ?? ''),
                ':pid'  => $pid,
                ':cid'  => $cid,
                ':cnm'  => $custNm,
                ':src'  => (string)($d['source_module'] ?? ($p['project_type'] ?: 'goods')),
                ':pd'   => (string)($d['po_date'] ?? date('Y-m-d')),
                ':db2'  => !empty($d['delivery_by']) ? $d['delivery_by'] : null,
                ':cur'  => (string)($d['currency'] ?? ($p['currency'] ?: 'TZS')),
                ':vr'   => $vatRate,
                ':sub'  => $subtotalSell,
                ':cog'  => $subtotalCog,
                ':vat'  => $vatAmt,
                ':tot'  => $total,
                ':mgn'  => (float)($d['default_margin_pct'] ?? 25),
                ':pt'   => (string)($d['payment_terms']      ?? ''),
                ':poc'  => (string)($d['delivery_poc']       ?? ''),
                ':phn'  => (string)($d['delivery_poc_phone'] ?? ''),
                ':addr' => (string)($d['delivery_address']   ?? ''),
                ':nt'   => (string)($d['notes']              ?? ''),
                ':uid'  => $uid,
                ':unm'  => $uname,
            ]
        );
        $poId = (int)$r['last_id'];
        if ($poId <= 0) return ['ok' => false, 'msg' => 'Save failed.', 'id' => 0];

        $n = 0;
        foreach ($cleanItems as $it) {
            $n++;
            $db->insertUpdate(
                "INSERT INTO goods_client_po_items
                    (client_po_id, item_no, description, qty, unit, unit_price_sell, unit_price_cog, line_total_sell, line_total_cog)
                 VALUES
                    (:pid, :n, :d, :q, :u, :ups, :upc, :lts, :ltc)",
                [
                    ':pid' => $poId, ':n' => $n,
                    ':d' => $it['description'], ':q' => $it['qty'], ':u' => $it['unit'],
                    ':ups' => $it['unit_price_sell'], ':upc' => $it['unit_price_cog'],
                    ':lts' => $it['line_total_sell'], ':ltc' => $it['line_total_cog'],
                ]
            );
        }
        return ['ok' => true, 'msg' => 'Client PO created (' . $poNo . ').', 'id' => $poId, 'client_po_no' => $poNo];
    }

    public function byId(int $id): array
    {
        $db = new DB();
        $rs = $db->mySelect(
            "SELECT gcp.*, p.project_no, p.project_name,
                    c.fname, c.lname, c.email, c.customer_tin, c.customer_vrn
               FROM goods_client_pos gcp
          LEFT JOIN c_projects p ON p.c0d = gcp.project_id
          LEFT JOIN customers  c ON c.c0d = gcp.customer_id
              WHERE gcp.c0d = :id AND gcp.deleted = 0 LIMIT 1",
            [':id' => $id]
        );
        return $rs[0] ?? [];
    }

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

    /**
     * Slice 8Z-g · Procurement Batch 1 — item action helpers.
     *
     * State machine:
     *   PENDING  → CONFIRMED   (PC scrub, existing)
     *   CONFIRMED → ACCEPTED   (Procurement accepts spec — locks item from PC edits)
     *   CONFIRMED/ACCEPTED → QUERIED  (Procurement or PC bounces back, note required)
     *   QUERIED → CONFIRMED    (PC re-scrubs — existing flow)
     */

    /** Procurement locks in the spec. Only from CONFIRMED. Note is optional here. */
    public function acceptItem(int $poId, int $itemId, int $userId, string $userName, string $note = ''): array
    {
        $db = new DB();
        $it = $db->mySelect("SELECT c0d, item_status, description FROM goods_client_po_items WHERE c0d = :i AND client_po_id = :p", [':i'=>$itemId, ':p'=>$poId]);
        if (empty($it))                              return ['ok' => false, 'msg' => 'Item not found on this PO.'];
        if ($it[0]['item_status'] !== 'CONFIRMED')  return ['ok' => false, 'msg' => 'Only CONFIRMED items can be Accepted (current: ' . $it[0]['item_status'] . ').'];
        $db->insertUpdate(
            "UPDATE goods_client_po_items SET
                item_status = 'ACCEPTED',
                procurement_note = :n,
                procurement_actioned_at = NOW(),
                procurement_actioned_by = :u,
                procurement_actioned_by_name = :un
              WHERE c0d = :i AND client_po_id = :p",
            [':n'=>trim($note), ':u'=>$userId, ':un'=>$userName, ':i'=>$itemId, ':p'=>$poId]
        );
        return ['ok' => true];
    }

    /**
     * Procurement bounces an item back to PC. Mandatory note; fires ITEM_QUERIED to PC + PM.
     * Valid from CONFIRMED or ACCEPTED. Item becomes editable for PC again.
     */
    public function queryItem(int $poId, int $itemId, int $userId, string $userName, string $note): array
    {
        if (trim($note) === '') return ['ok' => false, 'msg' => 'A note is required — explain what needs changing.'];
        $db = new DB();
        $it = $db->mySelect(
            "SELECT ci.c0d, ci.item_status, ci.description, cp.client_po_no, cp.project_id, cp.scrubbed_by_id
               FROM goods_client_po_items ci
               JOIN goods_client_pos cp ON cp.c0d = ci.client_po_id
              WHERE ci.c0d = :i AND ci.client_po_id = :p",
            [':i'=>$itemId, ':p'=>$poId]
        );
        if (empty($it))                                                        return ['ok' => false, 'msg' => 'Item not found on this PO.'];
        if (!in_array($it[0]['item_status'], ['CONFIRMED','ACCEPTED'], true)) return ['ok' => false, 'msg' => 'Only CONFIRMED / ACCEPTED items can be Queried.'];
        $db->insertUpdate(
            "UPDATE goods_client_po_items SET
                item_status = 'QUERIED',
                procurement_note = :n,
                procurement_actioned_at = NOW(),
                procurement_actioned_by = :u,
                procurement_actioned_by_name = :un
              WHERE c0d = :i AND client_po_id = :p",
            [':n'=>trim($note), ':u'=>$userId, ':un'=>$userName, ':i'=>$itemId, ':p'=>$poId]
        );
        // Fire ITEM_QUERIED to PC (the scrubber) + Project Manager (via project.project_manager_id)
        try {
            if (!class_exists('Notifications')) require_once ROOT . '/app/models/Notifications.php';
            $subject = 'Item queried by Procurement: ' . $it[0]['client_po_no'];
            $body    = 'Line: ' . mb_strimwidth((string)$it[0]['description'], 0, 80, '…') . ' — “' . mb_strimwidth(trim($note), 0, 160, '…') . '”';
            $url     = 'GoodsClientPos/view/' . $poId;
            $noti = new Notifications();
            $pcId = (int)($it[0]['scrubbed_by_id'] ?? 0);
            if ($pcId > 0) $noti->notify($pcId, 'ITEM_QUERIED', $subject, $body, $url, 'goods_client_po_item', $itemId, $userId, $userName);
            $pmRow = $db->mySelect("SELECT project_manager_id FROM c_projects WHERE c0d = :p", [':p' => (int)$it[0]['project_id']]);
            $pmId  = (int)($pmRow[0]['project_manager_id'] ?? 0);
            if ($pmId > 0 && $pmId !== $pcId) $noti->notify($pmId, 'ITEM_QUERIED', $subject, $body, $url, 'goods_client_po_item', $itemId, $userId, $userName);
        } catch (\Throwable $e) { /* best-effort */ }
        return ['ok' => true];
    }

    /**
     * PC-only Reopen: flips an ACCEPTED item back to QUERIED so specs can be edited mid-sourcing.
     * Mandatory note; fires ITEM_REOPENED to Procurement (the person who accepted, or the group).
     */
    public function reopenItem(int $poId, int $itemId, int $userId, string $userName, string $note): array
    {
        if (trim($note) === '') return ['ok' => false, 'msg' => 'A note is required — explain what needs changing.'];
        $db = new DB();
        $it = $db->mySelect(
            "SELECT ci.c0d, ci.item_status, ci.description, ci.procurement_actioned_by,
                    cp.client_po_no, cp.handover_to_user_id
               FROM goods_client_po_items ci
               JOIN goods_client_pos cp ON cp.c0d = ci.client_po_id
              WHERE ci.c0d = :i AND ci.client_po_id = :p",
            [':i'=>$itemId, ':p'=>$poId]
        );
        if (empty($it))                              return ['ok' => false, 'msg' => 'Item not found on this PO.'];
        if ($it[0]['item_status'] !== 'ACCEPTED')   return ['ok' => false, 'msg' => 'Only ACCEPTED items can be Reopened (current: ' . $it[0]['item_status'] . ').'];
        $db->insertUpdate(
            "UPDATE goods_client_po_items SET
                item_status = 'QUERIED',
                procurement_note = :n,
                procurement_actioned_at = NOW(),
                procurement_actioned_by = :u,
                procurement_actioned_by_name = :un
              WHERE c0d = :i AND client_po_id = :p",
            [':n'=>trim($note), ':u'=>$userId, ':un'=>$userName, ':i'=>$itemId, ':p'=>$poId]
        );
        try {
            if (!class_exists('Notifications')) require_once ROOT . '/app/models/Notifications.php';
            $subject = 'Item reopened by PC: ' . $it[0]['client_po_no'];
            $body    = 'Line: ' . mb_strimwidth((string)$it[0]['description'], 0, 80, '…') . ' — “' . mb_strimwidth(trim($note), 0, 160, '…') . '”';
            $url     = 'GoodsClientPos/view/' . $poId;
            $noti = new Notifications();
            // Prefer the Procurement user who Accepted, fall back to the PO's assigned Procurement user, else group fan-out.
            $target = (int)($it[0]['procurement_actioned_by'] ?? 0);
            if ($target <= 0) $target = (int)($it[0]['handover_to_user_id'] ?? 0);
            if ($target > 0) $noti->notify($target, 'ITEM_REOPENED', $subject, $body, $url, 'goods_client_po_item', $itemId, $userId, $userName);
            else             $noti->notifyGroup('Procurement', 'ITEM_REOPENED', $subject, $body, $url, 'goods_client_po_item', $itemId, $userId, $userName);
        } catch (\Throwable $e) { /* best-effort */ }
        return ['ok' => true];
    }

    /** Overheads scoped to this PO (either explicitly po_id-tagged or project-wide with NULL po_id). */
    public function overheadsForPo(int $poId, int $projectId): array
    {
        $db = new DB();
        return $db->mySelect(
            "SELECT * FROM c_project_overheads
              WHERE deleted = 0 AND active = 1
                AND project_id = :p
                AND (po_id = :po OR po_id IS NULL)
              ORDER BY category, c0d",
            [':p' => $projectId, ':po' => $poId]
        ) ?: [];
    }

    /* ── Slice 8Z-h · Sourcing path picker (Batch 2) ────────────────── */

    /**
     * Procurement picks the sourcing route for this PO:
     *   DISCOVERY   → Product Discovery first (cash advance + findings capture)
     *   SKIP_TO_RFQ → straight to RFQ (Procurement already knows the suppliers)
     *
     * Slice 8Z-j · Batch 4 — the path is now a HINT, not a one-way lock. Procurement can
     * flip between paths freely. If they switch AWAY from DISCOVERY while a Discovery LPO
     * is in-flight (draft / approved / paid), we return an info warning but still allow
     * the switch — the advance can complete and findings will still seed a future RFQ.
     */
    public function setSourcingPath(int $poId, string $path, int $userId, string $userName): array
    {
        if (!in_array($path, ['DISCOVERY','SKIP_TO_RFQ'], true)) return ['ok' => false, 'msg' => 'Invalid path.'];
        $db = new DB();
        $po = $db->mySelect("SELECT status, sourcing_path FROM goods_client_pos WHERE c0d = :id AND deleted = 0", [':id' => $poId]);
        if (empty($po))                                          return ['ok' => false, 'msg' => 'Client PO not found.'];
        if (in_array($po[0]['status'], ['CLOSED','CANCELLED'], true)) return ['ok' => false, 'msg' => 'PO is ' . $po[0]['status'] . ' — cannot change sourcing path.'];
        if ($po[0]['sourcing_path'] === $path)                   return ['ok' => true, 'msg' => 'Already ' . $path . ' — no change.'];

        // Warning if switching AWAY from DISCOVERY while a Discovery engagement is live.
        $warning = '';
        if ($po[0]['sourcing_path'] === 'DISCOVERY' && $path === 'SKIP_TO_RFQ') {
            $eng = $this->discoveryEngagementForPo($poId);
            if (!empty($eng) && !in_array($eng['cash_out_status'], ['DECLINED','CANCELLED'], true)) {
                $warning = 'Discovery LPO ' . $eng['lpo_no'] . ' is in-flight (' . strtolower($eng['cash_out_status'])
                         . '). It will complete on its own — findings can still seed the RFQ once retirement is APPROVED. '
                         . 'If you want to cancel the advance, do that on the LPO detail page.';
            }
        }

        $db->insertUpdate(
            "UPDATE goods_client_pos SET
                sourcing_path = :p,
                sourcing_path_set_at = NOW(),
                sourcing_path_set_by = :u,
                sourcing_path_set_by_name = :un,
                edited_by = :u, edited_on = NOW()
              WHERE c0d = :id",
            [':p' => $path, ':u' => $userId, ':un' => $userName, ':id' => $poId]
        );
        return ['ok' => true, 'warning' => $warning];
    }

    /**
     * Load the PRODUCT_DISCOVERY LPO (if any) attached to this PO plus its retirement status.
     * Used by the view to render the Sourcing card correctly:
     *   - if none yet → show the "Request Discovery" button
     *   - if one exists → show its status chip (DRAFT / SUBMITTED / APPROVED / PAID / RETIRED)
     *
     * Returns [] if no Discovery LPO exists, or an associative row with:
     *   lpo_id, lpo_no, amount, currency, cash_out_status, retirement_status
     */
    public function discoveryEngagementForPo(int $poId): array
    {
        $db = new DB();
        $rs = $db->mySelect(
            "SELECT l.c0d AS lpo_id, l.lpo_no, l.amount, l.currency, l.cash_out_status, l.purpose,
                    l.requested_by_name, l.requested_on,
                    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
              WHERE l.lpo_type = 'PRODUCT_DISCOVERY'
                AND l.sourced_from_client_po_id = :p
                AND l.deleted = 0
              ORDER BY l.c0d DESC LIMIT 1",
            [':p' => $poId]
        );
        return $rs[0] ?? [];
    }

    public function list(string $status = '', int $projectId = 0, int $assignedProcUser = 0, int $limit = 0, int $offset = 0, string $search = ''): array
    {
        [$where, $params] = $this->listWhere($status, $projectId, $assignedProcUser, $search);
        $limitSql = ($limit > 0) ? (' LIMIT ' . (int)$limit . ' OFFSET ' . max(0, (int)$offset)) : '';
        $db = new DB();
        return $db->mySelect(
            "SELECT gcp.c0d, gcp.client_po_no, gcp.customer_po_ref, gcp.customer_name, gcp.status,
                    gcp.subtotal_sell, gcp.subtotal_cog, gcp.total, gcp.currency, gcp.po_date,
                    gcp.default_margin_pct, gcp.source_module,
                    p.project_no, p.project_name,
                    (SELECT COUNT(*) FROM sales s WHERE s.client_po_id = gcp.c0d AND s.deleted = 0) AS invoice_count,
                    (SELECT IFNULL(SUM(s.total),0) FROM sales s WHERE s.client_po_id = gcp.c0d AND s.deleted = 0 AND s.invoice_workflow_status = 'ISSUED') AS invoiced_total
               FROM goods_client_pos gcp
          LEFT JOIN c_projects p ON p.c0d = gcp.project_id
               $where
              ORDER BY gcp.c0d DESC" . $limitSql,
            $params
        ) ?: [];
    }

    /** Row count matching the same filters — used for pagination. */
    public function countList(string $status = '', int $projectId = 0, int $assignedProcUser = 0, string $search = ''): int
    {
        [$where, $params] = $this->listWhere($status, $projectId, $assignedProcUser, $search);
        $db = new DB();
        $r  = $db->mySelect(
            "SELECT COUNT(*) n FROM goods_client_pos gcp
          LEFT JOIN c_projects p ON p.c0d = gcp.project_id
               $where",
            $params
        );
        return (int)($r[0]['n'] ?? 0);
    }

    private function listWhere(string $status, int $projectId, int $assignedProcUser, string $search): array
    {
        $params = [];
        $where  = "WHERE gcp.deleted = 0";
        if ($status !== '')        { $where .= " AND gcp.status = :s";     $params[':s']  = $status; }
        if ($projectId > 0)        { $where .= " AND gcp.project_id = :p"; $params[':p']  = $projectId; }
        if ($assignedProcUser > 0) { $where .= " AND p.procurement_id = :apu"; $params[':apu'] = $assignedProcUser; }
        if ($search !== '') {
            $where .= " AND (gcp.client_po_no LIKE :q OR gcp.customer_po_ref LIKE :q OR gcp.customer_name LIKE :q OR p.project_no LIKE :q OR p.project_name LIKE :q)";
            $params[':q'] = '%' . $search . '%';
        }
        return [$where, $params];
    }

    /** Invoices drafted from a specific PO — used on the PO view. */
    public function invoicesFor(int $client_po_id): array
    {
        $db = new DB();
        return $db->mySelect(
            "SELECT c0d, sale_no, invoice_ref, invoice_date, total, invoice_workflow_status, journal_entry_id
               FROM sales WHERE client_po_id = :id AND deleted = 0 ORDER BY c0d DESC",
            [':id' => $client_po_id]
        ) ?: [];
    }
}
