Subversion Repositories SmartDukaan

-- 2026-09-24 - Mark debit notes settled by the pre-workflow returns flow as APPROVED.
--
-- APPLIED on hadb1 2026-09-24. Recorded here for the audit trail; do not re-run blindly.
--
-- Why: returns settled before the receive workflow (first purchase_return_order 2026-03-16 12:52) wrote the
-- warehouse SALE_RET scan, a returnorderinfo row and a wallet refund, but never touched the debit note. So
-- 5,557 fully settled notes still read status='CREATED' with no purchase_return_order, and every receive
-- screen decides on exactly those two things - the note looked like it was still awaiting receipt, Receive
-- button included. Re-receiving one (DN UPBLY975/4, IMEI 864973083197734) handed a partner back a phone that
-- had already been returned, refunded and resold, and a duplicate debit note followed.
--
-- Evidence rule (mirrors PurchaseReturnServiceImpl.applyReceipt, which refuses to return a unit twice):
-- a note qualifies only when EVERY unit on it is
--   (a) returned  - a SALE_RET / DOA_IN / SALE_RET_UNUSABLE scan for that unit against the order its
--                   invoice was billed on, and
--   (b) refunded  - the order's refunded returnorderinfo quantity covers its returned units.
-- Serialized units match by IMEI through line_item_imei (holds even where the SALE scan was deleted);
-- non-serialized ones qualify only when the invoice has no returnable quantity left for that item.
-- End state written is the one the new flow writes at refund: debit_note APPROVED, items RETURNED with
-- return_timestamp = the return scan and refund_timestamp = the refund. No purchase_return_order is
-- invented - these were never received through the workflow, and a PRO would falsify who received what.
--
-- Result: 4,154 debit notes and 6,639 return items. Open notes 5,557 -> 1,404.
-- Backups: fofo._bak_dn_status_20260924, fofo._bak_dn_pri_status_20260924.
-- Per-unit evidence and bucket per unit: fofo._dn_backfill_20260922 (+ _dn_backfill_dn_20260922 per note).
--
-- NOT backfilled, left at CREATED for review (see the bucket column):
--   C_REFUNDED_NOT_RETURNED      1,011 notes - refunded with no return scan (refund-leak / re-bill pattern)
--   E_NOT_RETURNED_NOT_REFUNDED    293 notes - raised and abandoned, oldest 2018
--   B_RETURNED_NOT_REFUNDED         82 notes - goods back, no refund recorded: partners may be owed money
--   D_NO_SALE_SCAN                  24 notes - sale scan deleted or IMEI mismatch
--   X_ORDER_NOT_FOUND                2 notes
-- Only 5 of the remainder belong to internal stores (fofo_store.internal = 1).

-- Guards matter: the tables below are a snapshot, and the live flow keeps settling notes. Both statements
-- re-check status and the absence of a purchase_return_order at execution time (DN 7228 was received
-- normally between the snapshot and the run, and correctly dropped out).

UPDATE fofo.debit_note dn
  JOIN fofo._dn_backfill_dn_20260922 b ON b.dn = dn.id AND b.units = b.ok_units
  SET dn.status = 'APPROVED'
  WHERE dn.status = 'CREATED'
    AND NOT EXISTS (SELECT 1 FROM fofo.purchase_return_order pro WHERE pro.debit_note_id = dn.id);

UPDATE fofo.purchase_return_item pri
  JOIN fofo._dn_backfill_20260922 u ON u.pri = pri.id
  JOIN fofo.debit_note dn ON dn.id = pri.debit_note_id AND dn.status = 'APPROVED'
  JOIN fofo._dn_backfill_dn_20260922 b ON b.dn = dn.id AND b.units = b.ok_units
  SET pri.status = 'RETURNED', pri.return_timestamp = u.returned_at, pri.refund_timestamp = u.refunded_at
  WHERE pri.status = 'DEBIT_NOTE_CREATED' AND u.bucket = 'A_RETURNED_REFUNDED'
    AND NOT EXISTS (SELECT 1 FROM fofo.purchase_return_order pro WHERE pro.debit_note_id = dn.id);

-- Verify
-- SELECT status, COUNT(*) FROM fofo.debit_note GROUP BY status;  -- APPROVED 4539, CANCELLED 6, CREATED 1404
-- SELECT COUNT(*) FROM fofo.debit_note dn WHERE dn.status='CREATED'
--   AND NOT EXISTS (SELECT 1 FROM fofo.purchase_return_order pro WHERE pro.debit_note_id=dn.id);  -- 1404