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

    /**
     * Create a GRN against a supplier LPO.
     * $d = [ 'lpo_id', 'received_on', 'supplier_delivery_note_ref', 'notes',
     *        'items' => [ ['lpo_item_id', 'qty_received', 'qty_rejected', 'condition_notes'] ] ]
     *
     * Cascading roll-ups:
     *   grn_items → lpo_items.qty_received (cumulative)
     *             → goods_client_po_items.qty_received (cumulative)
     *   Then recompute lpos.receipt_status from all lpo_items.
     */
    public function create(array $d, int $uid, string $uname): array
    {
        $lpoId = (int)($d['lpo_id'] ?? 0);
        $items = $d['items'] ?? [];
        if ($lpoId <= 0)   return ['ok' => false, 'msg' => 'lpo_id required.', 'id' => 0];
        if (empty($items)) return ['ok' => false, 'msg' => 'Add at least one line receipt.', 'id' => 0];

        $db = new DB();
        // Load LPO for context + validation
        $lrs = $db->mySelect(
            "SELECT c0d, lpo_no, project_id, cash_out_status, lpo_type, deleted
               FROM lpos WHERE c0d = :id LIMIT 1", [':id' => $lpoId]
        );
        $lpo = $lrs[0] ?? null;
        if (!$lpo || (int)$lpo['deleted'] === 1) return ['ok' => false, 'msg' => 'LPO not found.', 'id' => 0];
        if ($lpo['lpo_type'] !== 'SUPPLIER')     return ['ok' => false, 'msg' => 'Only SUPPLIER LPOs can have a GRN.', 'id' => 0];
        if (!in_array($lpo['cash_out_status'], ['APPROVED','ISSUED','PAID'], true)) {
            return ['ok' => false, 'msg' => 'LPO must be APPROVED (or later) before receiving. Current status: ' . $lpo['cash_out_status'], 'id' => 0];
        }

        // Load current lpo_items + validate every receipt qty ≤ (qty − qty_received)
        $lpoItems = $db->mySelect(
            "SELECT c0d, qty, qty_received, client_po_item_id, description FROM lpo_items WHERE lpo_id = :id ORDER BY item_no",
            [':id' => $lpoId]
        ) ?: [];
        $byId = [];
        foreach ($lpoItems as $li) $byId[(int)$li['c0d']] = $li;

        // Prep + validate lines
        $cleanItems = [];
        $totalReceived = 0.0;
        foreach ($items as $it) {
            $liId = (int)($it['lpo_item_id'] ?? 0);
            $qr   = round((float)($it['qty_received']  ?? 0), 4);
            $qj   = round((float)($it['qty_rejected']  ?? 0), 4);
            if ($liId <= 0 || !isset($byId[$liId])) continue;
            if ($qr <= 0 && $qj <= 0) continue; // silently skip empty lines
            $li = $byId[$liId];
            $outstanding = round((float)$li['qty'] - (float)$li['qty_received'], 4);
            if ($qr > $outstanding + 0.0001) {
                return ['ok' => false, 'msg' => 'Line "' . $li['description'] . '": receiving ' . $qr . ' exceeds outstanding ' . $outstanding . '.', 'id' => 0];
            }
            $totalReceived += $qr;
            $cleanItems[] = [
                'lpo_item_id'      => $liId,
                'qty_received'     => $qr,
                'qty_rejected'     => $qj,
                'condition_notes'  => (string)($it['condition_notes'] ?? ''),
                'client_po_item_id'=> (int)$li['client_po_item_id'],
            ];
        }
        if (empty($cleanItems) || $totalReceived <= 0) {
            return ['ok' => false, 'msg' => 'No positive-qty line receipts.', 'id' => 0];
        }

        // Insert GRN header ---------------------------------------------
        $grnNo = $this->nextGrnNo();
        $condOk = (int)!empty($d['condition_ok']);
        // default to 1 if not explicitly false and no rejections
        if (!isset($d['condition_ok'])) {
            $anyRej = 0; foreach ($cleanItems as $ci) if ($ci['qty_rejected'] > 0) { $anyRej = 1; break; }
            $condOk = $anyRej ? 0 : 1;
        }
        $r = $db->insertUpdate(
            "INSERT INTO goods_grn
                (grn_no, lpo_id, project_id, supplier_delivery_note_ref,
                 received_on, received_by, received_by_name, condition_ok, notes,
                 created_by, edited_by)
             VALUES
                (:gn, :lid, :pid, :sdn, :rd, :uid, :unm, :ck, :nt, :uid, :uid)",
            [
                ':gn'  => $grnNo,
                ':lid' => $lpoId,
                ':pid' => (int)$lpo['project_id'],
                ':sdn' => (string)($d['supplier_delivery_note_ref'] ?? ''),
                ':rd'  => (string)($d['received_on'] ?? date('Y-m-d')),
                ':uid' => $uid,
                ':unm' => $uname,
                ':ck'  => $condOk,
                ':nt'  => (string)($d['notes'] ?? ''),
            ]
        );
        $grnId = (int)$r['last_id'];
        if ($grnId <= 0) return ['ok' => false, 'msg' => 'Save failed on GRN header.', 'id' => 0];

        // Insert item rows + roll up qty_received on lpo_items + client_po_items
        foreach ($cleanItems as $ci) {
            $db->insertUpdate(
                "INSERT INTO goods_grn_items (grn_id, lpo_item_id, qty_received, qty_rejected, condition_notes)
                 VALUES (:gid, :lid, :qr, :qj, :cn)",
                [':gid' => $grnId, ':lid' => $ci['lpo_item_id'], ':qr' => $ci['qty_received'], ':qj' => $ci['qty_rejected'], ':cn' => $ci['condition_notes']]
            );
            // Roll up on the LPO item
            $db->insertUpdate(
                "UPDATE lpo_items SET qty_received = qty_received + :qr, qty_rejected = qty_rejected + :qj
                  WHERE c0d = :id",
                [':qr' => $ci['qty_received'], ':qj' => $ci['qty_rejected'], ':id' => $ci['lpo_item_id']]
            );
            // Roll up further to the client PO item
            if ($ci['client_po_item_id'] > 0) {
                $db->insertUpdate(
                    "UPDATE goods_client_po_items SET qty_received = qty_received + :qr WHERE c0d = :id",
                    [':qr' => $ci['qty_received'], ':id' => $ci['client_po_item_id']]
                );
            }
        }

        // Refresh lpos.receipt_status by comparing Σqty_received vs Σqty
        $agg = $db->mySelect(
            "SELECT SUM(qty) AS ordered, SUM(qty_received) AS received
               FROM lpo_items WHERE lpo_id = :id",
            [':id' => $lpoId]
        );
        $ordered  = (float)($agg[0]['ordered']  ?? 0);
        $received = (float)($agg[0]['received'] ?? 0);
        $newStat = 'NOT_RECEIVED';
        if ($received >= $ordered - 0.0001 && $received > 0)          $newStat = 'FULLY_RECEIVED';
        elseif ($received > $ordered + 0.0001)                        $newStat = 'OVER_RECEIVED';
        elseif ($received > 0)                                        $newStat = 'PARTIALLY_RECEIVED';
        $db->insertUpdate(
            "UPDATE lpos SET receipt_status = :s, edited_on = NOW() WHERE c0d = :id",
            [':s' => $newStat, ':id' => $lpoId]
        );

        return ['ok' => true, 'msg' => 'GRN posted (' . $grnNo . ') — LPO receipt: ' . $newStat . '.', 'id' => $grnId, 'grn_no' => $grnNo, 'lpo_receipt_status' => $newStat];
    }

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

    public function itemsFor(int $grn_id): array
    {
        $db = new DB();
        return $db->mySelect(
            "SELECT gi.*, li.description, li.qty AS lpo_qty, li.unit, li.unit_price, li.line_total
               FROM goods_grn_items gi
          LEFT JOIN lpo_items li ON li.c0d = gi.lpo_item_id
              WHERE gi.grn_id = :id ORDER BY gi.c0d ASC",
            [':id' => $grn_id]
        ) ?: [];
    }

    public function forLpo(int $lpo_id): array
    {
        $db = new DB();
        return $db->mySelect(
            "SELECT c0d, grn_no, supplier_delivery_note_ref, received_on, received_by_name, condition_ok
               FROM goods_grn WHERE lpo_id = :id AND deleted = 0 ORDER BY c0d DESC",
            [':id' => $lpo_id]
        ) ?: [];
    }

    public function list(): array
    {
        $db = new DB();
        return $db->mySelect(
            "SELECT g.c0d, g.grn_no, g.supplier_delivery_note_ref, g.received_on, g.received_by_name, g.condition_ok,
                    l.lpo_no, l.payee_name, l.currency, l.receipt_status,
                    p.project_no,
                    (SELECT COUNT(*) FROM goods_grn_items gi WHERE gi.grn_id = g.c0d) AS line_count,
                    (SELECT SUM(qty_received) FROM goods_grn_items gi WHERE gi.grn_id = g.c0d) AS qty_received_total
               FROM goods_grn g
          LEFT JOIN lpos l       ON l.c0d = g.lpo_id
          LEFT JOIN c_projects p ON p.c0d = g.project_id
              WHERE g.deleted = 0
              ORDER BY g.c0d DESC",
            []
        ) ?: [];
    }
}
