-- ═══════════════════════════════════════════════════════════════════════════
-- rsflow.dspera · Goods Receiving (GRN)
-- Stores/Procurement records goods received from a supplier against an
-- APPROVED supplier LPO. Each GRN captures qty per line + supplier delivery
-- note ref. Roll-ups cascade:
--   goods_grn_items.qty_received → lpo_items.qty_received
--                                → goods_client_po_items.qty_received
--   lpos.receipt_status = NOT_RECEIVED | PARTIALLY_RECEIVED | FULLY_RECEIVED
-- Payment (cash_out_status) advances independently of receipt.
-- ═══════════════════════════════════════════════════════════════════════════

DROP TABLE IF EXISTS goods_grn_items;
DROP TABLE IF EXISTS goods_grn;

CREATE TABLE goods_grn (
  c0d                       INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  grn_no                    VARCHAR(30) NOT NULL,
  lpo_id                    INT NOT NULL,
  project_id                INT NOT NULL DEFAULT 0,
  supplier_delivery_note_ref VARCHAR(60) NOT NULL DEFAULT '',
  received_on               DATE NOT NULL,
  received_by               INT NOT NULL DEFAULT 0,
  received_by_name          VARCHAR(120) NOT NULL DEFAULT '',
  condition_ok              TINYINT(1) NOT NULL DEFAULT 1,     -- 0 = damaged/rejected/short
  notes                     TEXT,
  status                    VARCHAR(20) NOT NULL DEFAULT 'POSTED',   -- POSTED|REVERSED
  created_by                INT NOT NULL DEFAULT 0,
  created_on                TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  edited_by                 INT NOT NULL DEFAULT 0,
  edited_on                 TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted                   TINYINT(1) NOT NULL DEFAULT 0,
  UNIQUE KEY uq_grn_no (grn_no),
  KEY ix_lpo (lpo_id),
  KEY ix_project (project_id),
  KEY ix_received_on (received_on)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE goods_grn_items (
  c0d            INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  grn_id         INT NOT NULL,
  lpo_item_id    INT NOT NULL,
  qty_received   DECIMAL(20,4) NOT NULL DEFAULT 0.0000,
  qty_rejected   DECIMAL(20,4) NOT NULL DEFAULT 0.0000,
  condition_notes TEXT,
  created_on     TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY ix_grn (grn_id),
  KEY ix_lpo_item (lpo_item_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ── lpo_items: proper FK to client_po_item + qty_received tracking ──────
ALTER TABLE lpo_items
    ADD COLUMN client_po_item_id INT NOT NULL DEFAULT 0 AFTER budget_category_id,
    ADD COLUMN qty_received      DECIMAL(20,4) NOT NULL DEFAULT 0.0000 AFTER client_po_item_id,
    ADD COLUMN qty_rejected      DECIMAL(20,4) NOT NULL DEFAULT 0.0000 AFTER qty_received,
    ADD KEY ix_client_po_item (client_po_item_id);

-- Backfill client_po_item_id for existing lpo_items whose notes carry the id
UPDATE lpo_items
   SET client_po_item_id = CAST(SUBSTRING_INDEX(notes, '#', -1) AS UNSIGNED)
 WHERE notes LIKE 'Client PO item #%' AND client_po_item_id = 0;

-- ── lpos: track physical receipt separately from cash-out lifecycle ─────
ALTER TABLE lpos
    ADD COLUMN receipt_status VARCHAR(30) NOT NULL DEFAULT 'NOT_RECEIVED' AFTER cash_out_status;
-- Values: NOT_RECEIVED | PARTIALLY_RECEIVED | FULLY_RECEIVED | OVER_RECEIVED
-- (Independent of cash_out_status — you can PAY before / after / regardless)

-- ── goods_client_po_items: track supplier-side receipts separately from
--    the customer-facing qty_delivered (which will be populated by the
--    Customer Delivery slice)
ALTER TABLE goods_client_po_items
    ADD COLUMN qty_received DECIMAL(20,4) NOT NULL DEFAULT 0.0000 AFTER qty_delivered;
