Subversion Repositories SmartDukaan

-- ============================================================================

-- Cleanup: abandoned unpaid draft orders ("2020 draft class")

-- ============================================================================

-- These are status=0 (PAYMENT_PENDING) orders, billing/refund NULL, whose

-- transaction NEVER posted a wallet PURCHASE debit -- bulk draft batches created

-- programmatically (mostly 2019-2021), never submitted/paid, predating the

-- bulk-PO approval workflow. They have inflated pending indent (and partner

-- account-statement closing balances) ever since, with ZERO underlying money

-- movement. The problem is closed: newest is 2024-05; nothing in 2025/2026.

--

-- DISTINCT from the PO_REJECTED fix (backfill_po_rejected_orders.sql): those were

-- REJECTED bulk POs whose wallet WAS debited then refunded. This set was never

-- debited at all, so there is no wallet/rejection event to anchor to.

--

-- Mechanism:

--   status           -> PAYMENT_FAILED(1)   (existing status -> NO code/enum change,

--                                            NO deploy needed; accurate: payment never

--                                            completed. Not in openOrders; refund

--                                            reports are status-gated and exclude it.)

--   refund_timestamp -> created_timestamp   (retroactive: these were never real, so

--                                            remove them from ALL historical pending

--                                            indent. This is the lever that drops them

--                                            from PI and every refundTimestamp-IS-NULL

--                                            "active order" query.)

--

-- Safe because the wallet was never touched: no financial ledger changes, and the

-- daily Reconciliation cron only examines transactions with a PURCHASE wallet entry

-- on the reconcile date -- these have none, so it never sees them.

--

-- Scope guards (must all hold): status=0, not billed, not refunded, NOT a rejected

-- transaction (those go through PO_REJECTED), and NO PURCHASE wallet debit ever.

-- Entire set is 2+ years old, so no in-flight order can be caught.

--

-- IDEMPOTENT: guarded on status=0. Re-run matches 0 rows.

-- Expected: 1970 rows, 112 partners, ~Rs 3.14 cr pending indent released.

-- ============================================================================

UPDATE transaction.`order` o

LEFT JOIN transaction.transaction_approval ta

       ON ta.id = o.transaction_id AND ta.status = 'REJECTED'

LEFT JOIN (

        SELECT reference

        FROM transaction.userwallethistory

        WHERE reference_type = 'PURCHASE' AND amount < 0

        GROUP BY reference

) d ON d.reference = CAST(o.transaction_id AS CHAR)

SET o.status           = 1,                       -- OrderStatus.PAYMENT_FAILED

    o.refund_timestamp = o.created_timestamp,     -- retroactive void (never-real phantom draft)

    o.refunded_by      = 'system-cleanup',

    o.refund_reason    = 'Abandoned unpaid draft (status=0, wallet never debited, pre-approval-workflow)'

WHERE o.status = 0

  AND o.billing_timestamp IS NULL

  AND o.refund_timestamp IS NULL

  AND ta.id IS NULL            -- not a rejected transaction (Group A/B handled separately)

  AND d.reference IS NULL;     -- wallet NEVER debited = phantom draft

-- ---------------------------------------------------------------------------

-- Verification (after UPDATE): the draft class should now be 0.

-- SELECT COUNT(*) FROM transaction.`order` o

--   LEFT JOIN transaction.transaction_approval ta ON ta.id=o.transaction_id AND ta.status='REJECTED'

--   LEFT JOIN (SELECT reference FROM transaction.userwallethistory

--              WHERE reference_type='PURCHASE' AND amount<0 GROUP BY reference) d

--          ON d.reference=CAST(o.transaction_id AS CHAR)

--   WHERE o.status=0 AND o.billing_timestamp IS NULL AND o.refund_timestamp IS NULL

--     AND ta.id IS NULL AND d.reference IS NULL;   -- expect 0

-- ---------------------------------------------------------------------------