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