Subversion Repositories SmartDukaan

-- Adds a `credited` flag to transaction.hdfc_payment so the manual add-money approval

-- (WalletController.addAmountToWallet) can tell a real auto-credit apart from a merely

-- captured row (VA missing/unmatched, introduced by r36927). Default 0 => every row starts

-- uncredited; the two backfills below set the historically-credited rows to 1.

--

-- Validated on hadb1 (2026-06-26): 100,029 hdfc_payment rows; 99,967 match an AUTOMATED_ADVANCE

-- credit by reference=id; of the 62 unmatched, 47 are old (2024-03..2024-10, pre-r36927, credited

-- by construction) and 15 are the new captured-but-uncredited rows (2026-06-25 16:23:49 onward,

-- all EMPTY / nwspic07092023 VAs). A UTR-in-description match rescued 0 extra rows, so reference=id

-- + the pre-r36927 date rule together are exact: only the 15 (growing) genuine captures stay 0.

ALTER TABLE transaction.hdfc_payment

  ADD COLUMN credited TINYINT(1) NOT NULL DEFAULT 0;

-- Rule 1 ("if id is found"): a row is credited iff a wallet-history AUTOMATED_ADVANCE row

-- references it. The credit is written with reference = hdfc_payment.id

-- (HdfcPaymentController.addPayment -> walletService.addAmountToWallet(fofoId, stored.getId(), ...)

--  -> UserWalletHistory.setReference(referenceId)), so this join is exact.

UPDATE transaction.hdfc_payment hp

JOIN transaction.userwallethistory uwh

   ON uwh.reference = hp.id

  AND uwh.reference_type = 'AUTOMATED_ADVANCE'

SET hp.credited = 1;

-- Rule 2 ("older entries can be assumed true"): every row created before the r36927 deploy

-- (first capture-without-credit was 2026-06-25 16:23:49) is credited by construction -- pre-r36927

-- the hdfc_payment row was inserted ONLY after a successful credit. This covers the 47 old

-- unmatched rows (SIDBI sanctions / legacy reference schemes that left no AUTOMATED_ADVANCE match).

UPDATE transaction.hdfc_payment

SET credited = 1

WHERE create_timestamp < '2026-06-25 00:00:00';

-- After both: only post-r36927 captures with no wallet credit remain credited=0 -- i.e. the exact

-- set that is recoverable via the manual add-money (add_wallet_request) approval. Verify:

--   SELECT id, virtual_account, amount, create_timestamp

--   FROM transaction.hdfc_payment WHERE credited = 0 ORDER BY create_timestamp;