Subversion Repositories SmartDukaan

-- Record where stock came from and what it cost, on the stock itself (2026-09-17)
--
-- WHY
--   Stock carried no record of its source, so every internal movement re-derived the origin by tracing serials
--   (impossible for non-serialised stock that had already moved) and then priced the move from that vendor's CURRENT
--   catalog TP. Standard practice is a receipt layer: each row of stock knows which outside vendor it came from and
--   what it cost when it arrived, and an internal movement moves it at that cost.
--
--   inventoryItem.origin_vendor_id   - outside vendor the unit came from. NULL = genuinely unknown; never a guess
--                                      and never an internal supplier.
--   inventoryItem.receipt_unit_cost  - unit price the stock was received at (warehouse.lineitem.unitPrice of the PO
--                                      line it arrived on). What an internal movement moves it at.
--   lineitem.origin_vendor_id        - origin the PO line was priced from, so a receiving GRN can copy it onward.
--
-- ORDERING: run before deploying the code that writes these columns. Both ALTERs are ADD COLUMN, which MySQL 5.7 does
-- INPLACE and allows reads/writes throughout; inventoryItem is ~1.5M rows so expect a couple of minutes.

ALTER TABLE warehouse.inventoryItem
  ADD COLUMN origin_vendor_id INT NULL
    COMMENT 'outside vendor this stock came from; NULL = unknown, never an internal supplier',
  ADD COLUMN receipt_unit_cost FLOAT NULL
    COMMENT 'unit price this stock was received at; what an internal movement moves it at';

ALTER TABLE warehouse.lineitem
  ADD COLUMN origin_vendor_id INT NULL
    COMMENT 'origin vendor this PO line was priced from; copied onto stock at GRN';

-- Backfill, facts only --------------------------------------------------------------------------------------------
-- 1. Stock received directly on an outside vendor's PO: that vendor, and the price on its PO line.
UPDATE warehouse.inventoryItem ii
  JOIN warehouse.purchase p        ON p.id = ii.purchaseId
  JOIN warehouse.purchaseorder po  ON po.id = p.purchaseOrder_id
  JOIN warehouse.supplier s        ON s.id = po.supplierId AND s.internal = 0
  LEFT JOIN warehouse.lineitem li  ON li.purchaseOrder_id = po.id AND li.itemId = ii.itemId
   SET ii.origin_vendor_id = po.supplierId,
       ii.receipt_unit_cost = li.unitPrice
 WHERE ii.origin_vendor_id IS NULL;

-- 2. Serialised stock that moved internally: the vendor of the most recent external purchase of that serial, and the
--    cost recorded against that purchase. Same rule billing uses to price a sale.
UPDATE warehouse.inventoryItem ii
  JOIN (SELECT o.id,
               (SELECT po2.supplierId FROM warehouse.inventoryItem o2
                  JOIN warehouse.purchase p2 ON p2.id = o2.purchaseId
                  JOIN warehouse.purchaseorder po2 ON po2.id = p2.purchaseOrder_id
                  JOIN warehouse.supplier s2 ON s2.id = po2.supplierId AND s2.internal = 0
                 WHERE o2.serialNumber = o.serialNumber AND o2.itemId = o.itemId
                 ORDER BY o2.created DESC, o2.id DESC LIMIT 1) AS origin_vendor_id,
               (SELECT o3.receipt_unit_cost FROM warehouse.inventoryItem o3
                  JOIN warehouse.purchase p3 ON p3.id = o3.purchaseId
                  JOIN warehouse.purchaseorder po3 ON po3.id = p3.purchaseOrder_id
                  JOIN warehouse.supplier s3 ON s3.id = po3.supplierId AND s3.internal = 0
                 WHERE o3.serialNumber = o.serialNumber AND o3.itemId = o.itemId
                 ORDER BY o3.created DESC, o3.id DESC LIMIT 1) AS receipt_unit_cost
          FROM warehouse.inventoryItem o
         WHERE o.origin_vendor_id IS NULL AND o.serialNumber IS NOT NULL AND LENGTH(o.serialNumber) > 0
           AND o.currentQuantity > 0) traced ON traced.id = ii.id
   SET ii.origin_vendor_id  = traced.origin_vendor_id,
       ii.receipt_unit_cost = traced.receipt_unit_cost
 WHERE traced.origin_vendor_id IS NOT NULL;

-- 3. Everything else keeps NULL: non-serialised stock that has already moved internally has no recoverable origin,
--    and a guess must never be written as a fact. Those units are priced from the latest externally approved catalog
--    price at movement time, as before.

-- What the backfill reached, for stock currently held.
SELECT COUNT(*) AS in_stock_rows,
       SUM(origin_vendor_id IS NOT NULL) AS with_origin,
       SUM(receipt_unit_cost IS NOT NULL) AS with_cost,
       SUM(origin_vendor_id IS NULL) AS unknown_origin
  FROM warehouse.inventoryItem WHERE currentQuantity > 0;