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;