Subversion Repositories SmartDukaan

-- Record the vendor on stock received against orders raised before lines carried an origin (2026-09-18)
--
-- WHY
--   r37694 stamps a receipt with the origin held on its purchase order line, and lines only carry one from that
--   revision onward. Goods arriving on older open orders therefore landed with a cost but no vendor - 282 rows on the
--   first day alone - even where the order itself names an outside vendor we bought from.
--   The code now falls back to the order's own supplier; this repairs what was received before that fix.
--
-- Facts only: an outside vendor named on the unit's own purchase order. Internal movements are untouched, and stock
-- whose origin was never knowable keeps NULL.

USE warehouse;

CREATE TABLE warehouse._bak_receipt_origin_20260918 AS
SELECT ii.id, ii.origin_vendor_id, ii.receipt_unit_cost
  FROM 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
 WHERE ii.origin_vendor_id IS NULL;

SELECT COUNT(*) AS rows_to_repair FROM warehouse._bak_receipt_origin_20260918;

START TRANSACTION;

UPDATE warehouse.inventoryItem ii
  JOIN warehouse._bak_receipt_origin_20260918 b ON b.id = ii.id
  JOIN warehouse.purchase p       ON p.id = ii.purchaseId
  JOIN warehouse.purchaseorder po ON po.id = p.purchaseOrder_id
  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 = COALESCE(ii.receipt_unit_cost, li.unitPrice);

COMMIT;

-- Verify: repaired = 0 left, and no internal supplier ever recorded as an origin
SELECT (SELECT COUNT(*) FROM 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
         WHERE ii.origin_vendor_id IS NULL) AS external_receipts_still_blank,
       (SELECT COUNT(*) FROM warehouse.inventoryItem ii
          JOIN warehouse.supplier s ON s.id = ii.origin_vendor_id AND s.internal = 1) AS internal_wrongly_recorded,
       (SELECT COUNT(*) FROM warehouse.inventoryItem WHERE currentQuantity > 0 AND origin_vendor_id IS NULL) AS in_stock_unknown;

-- Rollback:
-- UPDATE warehouse.inventoryItem ii JOIN warehouse._bak_receipt_origin_20260918 b ON b.id = ii.id
--    SET ii.origin_vendor_id = b.origin_vendor_id, ii.receipt_unit_cost = b.receipt_unit_cost;