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

    public function byClientPoId(int $cpo_id): array
    {
        $db = new DB();
        $rs = $db->mySelect("SELECT * FROM goods_rfqs WHERE client_po_id = :id AND deleted = 0 ORDER BY c0d DESC LIMIT 1", [':id' => $cpo_id]);
        return $rs[0] ?? [];
    }

    /** Ensure an RFQ exists for a client PO — creates if missing. */
    public function ensureForClientPo(int $cpo_id, int $uid, string $uname): array
    {
        $existing = $this->byClientPoId($cpo_id);
        if (!empty($existing)) {
            // Slice 8Z-i · Batch 3 · idempotent re-seed if Discovery findings landed after the RFQ was created.
            $seeded = $this->seedFromDiscoveryShortlist((int)$existing['c0d'], $cpo_id, $uid);
            return ['ok' => true, 'id' => (int)$existing['c0d'], 'created' => false, 'seeded' => $seeded];
        }

        $db = new DB();
        $cpo = $db->mySelect("SELECT c0d, project_id, currency, sourcing_path FROM goods_client_pos WHERE c0d = :id AND deleted = 0", [':id' => $cpo_id]);
        if (empty($cpo)) return ['ok' => false, 'msg' => 'Client PO not found.', 'id' => 0];

        // Slice 8Z-i · Batch 3 · Path A gate — if Procurement chose Discovery, they must retire it first.
        if ($cpo[0]['sourcing_path'] === 'DISCOVERY') {
            if (!class_exists('LpoRetirements')) require_once ROOT . '/app/models/LpoRetirements.php';
            $shortlist = (new LpoRetirements())->shortlistForPo($cpo_id);
            if (empty($shortlist)) {
                return ['ok' => false, 'msg' => 'Sourcing path is Product Discovery but no APPROVED retirement with SHORTLISTED suppliers yet. Retire the Discovery advance first.', 'id' => 0];
            }
        }

        $rfqNo = $this->nextRfqNo();
        $r = $db->insertUpdate(
            "INSERT INTO goods_rfqs (rfq_no, client_po_id, project_id, currency, status, created_by, created_by_name, edited_by)
             VALUES (:rn, :cid, :pid, :cur, 'OPEN', :uid, :unm, :uid)",
            [
                ':rn' => $rfqNo, ':cid' => $cpo_id,
                ':pid' => (int)$cpo[0]['project_id'], ':cur' => (string)$cpo[0]['currency'],
                ':uid' => $uid, ':unm' => $uname,
            ]
        );
        $rfqId = (int)$r['last_id'];
        if ($rfqId <= 0) return ['ok' => false, 'msg' => 'RFQ create failed.', 'id' => 0];
        // Auto-seed invited suppliers from Discovery shortlist (if any).
        $seeded = $this->seedFromDiscoveryShortlist($rfqId, $cpo_id, $uid);
        return ['ok' => true, 'id' => $rfqId, 'created' => true, 'rfq_no' => $rfqNo, 'seeded' => $seeded];
    }

    /**
     * Slice 8Z-i · Batch 3 · seed invited suppliers from Discovery findings.
     * For each SHORTLIST finding under an APPROVED PRODUCT_DISCOVERY retirement on this PO:
     *   - Match to an existing vendor by TIN (if TIN provided) → real vendor_id
     *   - Otherwise insert an ad-hoc invitation with vendor_id = 0 + finding's contact data
     *     (Procurement can promote to a real vendor later via the vendors module)
     *
     * Idempotent: `discovery_finding_id` uniquely tags each seeded row so re-running skips them.
     * Returns the count of newly-seeded invitations (existing ones don't re-count).
     */
    public function seedFromDiscoveryShortlist(int $rfqId, int $cpoId, int $uid): int
    {
        if (!class_exists('LpoRetirements')) require_once ROOT . '/app/models/LpoRetirements.php';
        $findings = (new LpoRetirements())->shortlistForPo($cpoId);
        if (empty($findings)) return 0;

        $db = new DB();
        $seeded = 0;
        foreach ($findings as $f) {
            // Skip if we've already seeded this finding onto this RFQ
            $existing = $db->mySelect(
                "SELECT c0d FROM goods_rfq_quotes WHERE rfq_id = :r AND discovery_finding_id = :f AND deleted = 0",
                [':r' => $rfqId, ':f' => (int)$f['c0d']]
            );
            if (!empty($existing)) continue;

            // Try to match to an existing vendor by TIN (only if TIN is meaningful).
            $vendorId = 0; $vName = (string)$f['supplier_name']; $vTin = (string)($f['tin'] ?? '');
            if ($vTin !== '') {
                $vm = $db->mySelect("SELECT c0d, vendor_name FROM vendors WHERE vendor_tin = :t AND deleted = 0 LIMIT 1", [':t' => $vTin]);
                if (!empty($vm)) { $vendorId = (int)$vm[0]['c0d']; $vName = (string)$vm[0]['vendor_name']; }
            }

            $db->insertUpdate(
                "INSERT INTO goods_rfq_quotes
                    (rfq_id, vendor_id, vendor_name, vendor_tin, vendor_contact_phone, vendor_contact_email,
                     status, discovery_finding_id, created_by, edited_by)
                 VALUES (:rid, :vid, :vn, :vt, :vp, :ve, 'INVITED', :df, :uid, :uid)",
                [
                    ':rid' => $rfqId, ':vid' => $vendorId,
                    ':vn'  => $vName, ':vt' => $vTin,
                    ':vp'  => (string)($f['phone'] ?? ''), ':ve' => (string)($f['email'] ?? ''),
                    ':df'  => (int)$f['c0d'], ':uid' => $uid,
                ]
            );
            $seeded++;
        }
        return $seeded;
    }

    public function byId(int $id): array
    {
        $db = new DB();
        $rs = $db->mySelect(
            "SELECT r.*, cpo.client_po_no, cpo.customer_po_ref, cpo.customer_name, cpo.total AS cpo_total,
                    cpo.vat_rate, p.project_no, p.project_name
               FROM goods_rfqs r
          LEFT JOIN goods_client_pos cpo ON cpo.c0d = r.client_po_id
          LEFT JOIN c_projects p          ON p.c0d = r.project_id
              WHERE r.c0d = :id AND r.deleted = 0 LIMIT 1",
            [':id' => $id]
        );
        return $rs[0] ?? [];
    }

    public function list(string $status = '', int $projectId = 0, int $assignedProcUser = 0): array
    {
        $db     = new DB();
        $where  = "WHERE r.deleted = 0";
        $params = [];
        if ($status !== '')        { $where .= " AND r.status = :s";        $params[':s']   = $status; }
        if ($projectId > 0)        { $where .= " AND r.project_id = :p";    $params[':p']   = $projectId; }
        if ($assignedProcUser > 0) { $where .= " AND p.procurement_id = :apu"; $params[':apu'] = $assignedProcUser; }
        return $db->mySelect(
            "SELECT r.c0d, r.rfq_no, r.status, r.currency, r.created_on,
                    cpo.client_po_no, cpo.customer_name, cpo.total AS cpo_total,
                    (SELECT COUNT(*) FROM goods_rfq_quotes gq WHERE gq.rfq_id = r.c0d AND gq.deleted = 0) AS quote_count,
                    (SELECT COUNT(*) FROM goods_rfq_quote_items gqi
                       JOIN goods_rfq_quotes gq ON gq.c0d = gqi.rfq_quote_id
                      WHERE gq.rfq_id = r.c0d AND gqi.awarded_flag = 1 AND gq.deleted = 0) AS awarded_count,
                    p.project_no, p.project_name
               FROM goods_rfqs r
          LEFT JOIN goods_client_pos cpo ON cpo.c0d = r.client_po_id
          LEFT JOIN c_projects p          ON p.c0d = r.project_id
               $where
              ORDER BY r.c0d DESC",
            $params
        ) ?: [];
    }

    /** Supplier quotes (headers) invited on this RFQ. */
    public function quotesForRfq(int $rfq_id): array
    {
        $db = new DB();
        return $db->mySelect(
            "SELECT gq.*, v.vendor_name AS vend_master_name, v.vendor_tin AS vend_tin_master
               FROM goods_rfq_quotes gq
          LEFT JOIN vendors v ON v.c0d = gq.vendor_id
              WHERE gq.rfq_id = :id AND gq.deleted = 0
              ORDER BY gq.c0d ASC",
            [':id' => $rfq_id]
        ) ?: [];
    }

    /** Flat matrix of quote items keyed for JS: [client_po_item_id][rfq_quote_id] = row */
    public function quoteItemsForRfq(int $rfq_id): array
    {
        $db = new DB();
        return $db->mySelect(
            "SELECT qi.*, gq.vendor_id, gq.vendor_name
               FROM goods_rfq_quote_items qi
               JOIN goods_rfq_quotes gq ON gq.c0d = qi.rfq_quote_id
              WHERE gq.rfq_id = :id AND gq.deleted = 0",
            [':id' => $rfq_id]
        ) ?: [];
    }

    /** Invite a supplier onto this RFQ (creates a goods_rfq_quotes row). */
    public function inviteSupplier(int $rfq_id, int $vendor_id, int $uid): array
    {
        $db = new DB();
        $v = $db->mySelect("SELECT vendor_name, vendor_tin, vendor_phone_1, vendor_email FROM vendors WHERE c0d = :id AND deleted = 0", [':id' => $vendor_id]);
        if (empty($v)) return ['ok' => false, 'msg' => 'Vendor not found.', 'id' => 0];
        $vm = $v[0];
        $r = $db->insertUpdate(
            "INSERT INTO goods_rfq_quotes
                (rfq_id, vendor_id, vendor_name, vendor_tin, vendor_contact_phone, vendor_contact_email, status, created_by, edited_by)
             VALUES (:rid, :vid, :vn, :vt, :vp, :ve, 'INVITED', :uid, :uid)
             ON DUPLICATE KEY UPDATE edited_on = NOW()",
            [
                ':rid' => $rfq_id, ':vid' => $vendor_id,
                ':vn'  => (string)$vm['vendor_name'], ':vt' => (string)$vm['vendor_tin'],
                ':vp'  => (string)$vm['vendor_phone_1'], ':ve' => (string)$vm['vendor_email'],
                ':uid' => $uid,
            ]
        );
        // Fetch the row (could be an existing one, so use rfq+vendor lookup)
        $rs = $db->mySelect("SELECT c0d FROM goods_rfq_quotes WHERE rfq_id = :rid AND vendor_id = :vid", [':rid' => $rfq_id, ':vid' => $vendor_id]);
        return ['ok' => true, 'msg' => 'Supplier invited.', 'id' => (int)($rs[0]['c0d'] ?? 0), 'vendor_name' => $vm['vendor_name']];
    }

    /** Save a per-line quote (upsert). */
    public function saveQuoteItem(int $rfq_quote_id, int $client_po_item_id, float $unit_price, float $qty_available, int $lead_time_days): array
    {
        $db = new DB();
        $db->insertUpdate(
            "INSERT INTO goods_rfq_quote_items (rfq_quote_id, client_po_item_id, unit_price, qty_available, lead_time_days)
             VALUES (:qid, :cid, :up, :qa, :lt)
             ON DUPLICATE KEY UPDATE unit_price = VALUES(unit_price), qty_available = VALUES(qty_available),
                                     lead_time_days = VALUES(lead_time_days), edited_on = NOW()",
            [':qid' => $rfq_quote_id, ':cid' => $client_po_item_id, ':up' => $unit_price, ':qa' => $qty_available, ':lt' => $lead_time_days]
        );
        // Mark the quote header as QUOTED once ≥1 line has price
        $db->insertUpdate(
            "UPDATE goods_rfq_quotes SET status = 'QUOTED', quoted_on = IFNULL(quoted_on, CURDATE()), edited_on = NOW()
              WHERE c0d = :id AND status = 'INVITED'",
            [':id' => $rfq_quote_id]
        );
        return ['ok' => true, 'msg' => 'Saved.'];
    }

    /** Award a single line to a supplier — clears award on other suppliers for same client PO item. */
    public function awardItem(int $quote_item_id, int $uid): array
    {
        $db = new DB();
        $rs = $db->mySelect(
            "SELECT qi.client_po_item_id, gq.rfq_id FROM goods_rfq_quote_items qi
               JOIN goods_rfq_quotes gq ON gq.c0d = qi.rfq_quote_id
              WHERE qi.c0d = :id",
            [':id' => $quote_item_id]
        );
        if (empty($rs)) return ['ok' => false, 'msg' => 'Quote item not found.'];
        $cpoItem = (int)$rs[0]['client_po_item_id'];
        $rfqId   = (int)$rs[0]['rfq_id'];

        // Clear any prior award on this client PO item within this RFQ
        $db->insertUpdate(
            "UPDATE goods_rfq_quote_items qi
               JOIN goods_rfq_quotes gq ON gq.c0d = qi.rfq_quote_id
                SET qi.awarded_flag = 0, qi.awarded_on = NULL, qi.awarded_by = 0
              WHERE gq.rfq_id = :rid AND qi.client_po_item_id = :cid",
            [':rid' => $rfqId, ':cid' => $cpoItem]
        );
        // Set new award
        $db->insertUpdate(
            "UPDATE goods_rfq_quote_items SET awarded_flag = 1, awarded_on = NOW(), awarded_by = :uid WHERE c0d = :id",
            [':uid' => $uid, ':id' => $quote_item_id]
        );
        return ['ok' => true, 'msg' => 'Awarded.'];
    }

    /** Bulk award — every line this supplier quoted gets awarded (subject to per-line uniqueness). */
    public function awardAllFromSupplier(int $rfq_quote_id, int $uid): array
    {
        $db = new DB();
        $items = $db->mySelect(
            "SELECT c0d FROM goods_rfq_quote_items WHERE rfq_quote_id = :id AND unit_price > 0 ORDER BY c0d ASC",
            [':id' => $rfq_quote_id]
        ) ?: [];
        $awarded = 0;
        foreach ($items as $it) {
            $r = $this->awardItem((int)$it['c0d'], $uid);
            if ($r['ok']) $awarded++;
        }
        return ['ok' => true, 'msg' => "Awarded $awarded line(s)."];
    }

    /**
     * Finalize: for each supplier with ≥1 awarded line, auto-create a SUPPLIER lpo
     * with the awarded lines; then submit ONE GOODS_LPO_APPROVAL chain covering
     * the whole batch; then set every LPO's approval_id to the same fin_approvals row.
     */
    public function finalize(int $rfq_id, int $uid, string $uname, string $ugroup): array
    {
        $rfq = $this->byId($rfq_id);
        if (empty($rfq))                       return ['ok' => false, 'msg' => 'RFQ not found.'];
        if ($rfq['status'] === 'AWARDED')      return ['ok' => false, 'msg' => 'RFQ already finalized.'];
        if ($rfq['status'] === 'CANCELLED')    return ['ok' => false, 'msg' => 'RFQ was cancelled.'];

        $db = new DB();

        // Group awarded items per supplier
        $rows = $db->mySelect(
            "SELECT qi.c0d AS quote_item_id, qi.rfq_quote_id, qi.client_po_item_id,
                    qi.unit_price, qi.qty_available,
                    gq.vendor_id, gq.vendor_name,
                    cpi.description, cpi.qty AS ordered_qty, cpi.unit
               FROM goods_rfq_quote_items qi
               JOIN goods_rfq_quotes gq         ON gq.c0d = qi.rfq_quote_id
               JOIN goods_client_po_items cpi   ON cpi.c0d = qi.client_po_item_id
              WHERE gq.rfq_id = :rid AND qi.awarded_flag = 1 AND gq.deleted = 0
              ORDER BY gq.c0d ASC, cpi.item_no ASC",
            [':rid' => $rfq_id]
        ) ?: [];
        if (empty($rows)) return ['ok' => false, 'msg' => 'No awarded lines — nothing to finalize.'];

        $bySupplier = [];
        foreach ($rows as $r) {
            $key = (int)$r['rfq_quote_id'];
            if (!isset($bySupplier[$key])) {
                $bySupplier[$key] = [
                    'rfq_quote_id' => $key,
                    'vendor_id'    => (int)$r['vendor_id'],
                    'vendor_name'  => (string)$r['vendor_name'],
                    'items'        => [],
                ];
            }
            // Use qty_available if > 0, else fall back to PO ordered qty
            $qty = ((float)$r['qty_available'] > 0) ? (float)$r['qty_available'] : (float)$r['ordered_qty'];
            $bySupplier[$key]['items'][] = [
                'description'       => (string)$r['description'],
                'qty'               => $qty,
                'unit'              => (string)$r['unit'],
                'unit_price'        => (float)$r['unit_price'],
                'client_po_item_id' => (int)$r['client_po_item_id'],
                'notes'             => '',
            ];
        }

        // Load client PO for header context
        $cpo = $db->mySelect(
            "SELECT client_po_no, customer_po_ref, project_id, currency, source_module FROM goods_client_pos WHERE c0d = :id",
            [':id' => (int)$rfq['client_po_id']]
        );
        $cpoRow = $cpo[0] ?? [];

        // Create LPOs -----------------------------------------------------
        $lm     = new Lpos();
        $lpoIds = [];
        $lpoRefs = [];
        $totalAmount = 0.0;
        foreach ($bySupplier as $key => $bundle) {
            $r = $lm->createSupplierWithItems(
                [
                    'project_id'                => (int)$cpoRow['project_id'],
                    'vendor_id'                 => $bundle['vendor_id'],
                    'vendor_name'               => $bundle['vendor_name'],
                    'currency'                  => (string)$cpoRow['currency'],
                    'purpose'                   => 'Sourced against ' . $cpoRow['client_po_no'] . ' via ' . $rfq['rfq_no'],
                    'source_module'             => (string)$cpoRow['source_module'],
                    'sourced_from_rfq_id'       => $rfq_id,
                    'sourced_from_client_po_id' => (int)$rfq['client_po_id'],
                ],
                $bundle['items'],
                $uid, $uname
            );
            if (!$r['ok']) {
                return ['ok' => false, 'msg' => 'Failed to create LPO for ' . $bundle['vendor_name'] . ': ' . $r['msg']];
            }
            $lpoIds[$key]  = (int)$r['id'];
            $lpoRefs[]     = $r['lpo_no'];
            // Compute batch total
            foreach ($bundle['items'] as $it) $totalAmount += round((float)$it['qty'] * (float)$it['unit_price'], 2);
            // Mark the quote as AWARDED + record LPO link
            $db->insertUpdate(
                "UPDATE goods_rfq_quotes SET status = 'AWARDED', awarded_lpo_id = :lid, edited_on = NOW() WHERE c0d = :id",
                [':lid' => (int)$r['id'], ':id' => $key]
            );
        }

        // Submit ONE GOODS_LPO_APPROVAL chain covering the batch ---------
        $ac  = new ApprovalChain();
        $ref = $rfq['rfq_no'] . ' · ' . $rfq['client_po_no'] . ' · ' . count($lpoIds) . ' LPO(s) · ' . $cpoRow['currency'] . ' ' . number_format($totalAmount);
        $sub = $ac->submit([
            'chain_key'    => 'GOODS_LPO_APPROVAL',
            'entity_type'  => 'goods_rfq',
            'entity_id'    => $rfq_id,
            'ref_label'    => $ref,
            'return_url'   => 'GoodsRfq/view/' . $rfq_id,
            'user_id'      => $uid,
            'user_name'    => $uname,
            'user_group'   => $ugroup,
            'comment'      => 'LPO batch submitted for approval. ' . count($lpoIds) . ' supplier LPO(s): ' . implode(', ', $lpoRefs),
        ]);
        if (!$sub['ok']) return ['ok' => false, 'msg' => 'Chain submit failed: ' . $sub['msg']];
        $approvalId = (int)$sub['approval_id'];

        // Link every LPO to the batch approval + flip to SUBMITTED
        foreach ($lpoIds as $lid) {
            $lm->setApprovalId($lid, $approvalId);
        }

        // Update RFQ
        $db->insertUpdate(
            "UPDATE goods_rfqs SET status = 'EVALUATING', approval_id = :aid, edited_by = :uid, edited_on = NOW() WHERE c0d = :id",
            [':aid' => $approvalId, ':uid' => $uid, ':id' => $rfq_id]
        );

        return [
            'ok'          => true,
            'msg'         => count($lpoIds) . ' supplier LPO(s) drafted (' . implode(', ', $lpoRefs) . ') and submitted to GOODS_LPO_APPROVAL chain.',
            'approval_id' => $approvalId,
            'lpo_ids'     => array_values($lpoIds),
            'lpo_refs'    => $lpoRefs,
        ];
    }
}
