Subversion Repositories SmartDukaan

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

-- Backfill: PO-rejected orders stranded in status=0 (PAYMENT_PENDING)

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

-- Root cause: OrderController.updateApprovalStatus (reject branch) refunded the

-- wallet ("Order canceled") but never updated the order rows, so rejected bulk-PO

-- orders stayed status=0 with billing/refund NULL and were counted by every

-- pending-indent / "active order" query forever (e.g. account statement float).

--

-- This backfill only covers GROUP A: rejected transactions whose wallet WAS

-- refunded (a "+PURCHASE Order canceled" entry exists). Group B (rejected but no

-- wallet refund, ~55 rows) is intentionally EXCLUDED -- cancelling those would

-- drop pending indent with no offsetting wallet credit and wrongly debit the

-- partner; they need separate review.

--

-- refund_timestamp is set to the actual "Order canceled" wallet timestamp so the

-- order leaves pending indent exactly when the money was returned (historically

-- accurate per period). status is set to PO_REJECTED(91).

--

-- PRECONDITIONS

--   1. Deploy the PO_REJECTED(91) enum + reject-path code to EVERY service that

--      reads this DB BEFORE running this. OrderStatus.findByValue(91) returns null

--      on old code -> NPE on order.getStatus(). DEPLOY FIRST, BACKFILL SECOND.

--   2. Run on every environment the apps connect to (prod 192.168.142.141 AND any

--      reporting replica). Verify counts match the dry-run (485 rows / 287 txns).

--

-- IDEMPOTENT: guarded on status=0 AND refund_timestamp IS NULL. Safe to re-run

-- (a second run matches 0 rows because status is no longer 0).

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

UPDATE transaction.`order` o

JOIN transaction.transaction_approval ta

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

JOIN (

        SELECT reference, MIN(timestamp) AS cancel_ts

        FROM transaction.userwallethistory

        WHERE reference_type = 'PURCHASE'

          AND amount > 0

          AND description LIKE 'Order cancel%'

        GROUP BY reference

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

SET o.status           = 91,                       -- OrderStatus.PO_REJECTED

    o.refund_timestamp = r.cancel_ts,              -- actual wallet "Order canceled" time

    o.refunded_by      = 'system-backfill',

    o.refund_reason    = 'PO rejected (backfill: wallet already refunded, order stranded in status=0)'

WHERE o.status = 0

  AND o.billing_timestamp IS NULL

  AND o.refund_timestamp IS NULL;

-- Expected: 485 rows affected, 287 transactions, ~Rs 10.84 cr pending indent released.

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

-- Verification (run after the UPDATE):

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

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

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

--   -- Group A should now be 0 (only the ~55 Group-B no-wallet-refund rows may remain).

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