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
-- ---------------------------------------------------------------------------