Subversion Repositories SmartDukaan

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

-- Cleanup: phantom PAYMENT_FAILED orders inflating "Unbilled PO" (pending indent)

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

-- selectPendingIndentWalletAmount counts ANY order with billing/refund NULL,

-- regardless of status. So old PAYMENT_FAILED(1) orders (payment never completed,

-- never a committed PO) were counted as unbilled PO and drove the finance reco

-- "gaps" (e.g. Metro Refrigeration 18L, Mobile Planet 10.3L -- all phantom).

-- All such orders are 2017-2019 (none since; the order flow changed).

--

-- DO NOT blanket-exclude PAYMENT_FAILED:

--   - PAYMENT_PENDING(0) is the legit awaiting-approval bulk-PO state -> must stay.

--   - Some PAYMENT_FAILED orders are NET-COMMITTED (wallet debited, never refunded)

--     = a REAL payable; dropping them would hide money owed to the partner.

-- So we drop ONLY the phantom: no wallet debit, OR transaction net wallet >= 0

-- (refunded by other means). Net-committed orders are KEPT (finance refund list).

--

-- Mechanism: set refund_timestamp = created_timestamp on phantom rows so they drop

-- out of pending indent (status stays PAYMENT_FAILED -- correct). Data-only, no code.

--

-- IMPORTANT - use this TEMP-TABLE form, NOT a single UPDATE with the wallet

-- subqueries inline: the broad "all PURCHASE debits" aggregate over

-- userwallethistory is a full-table scan that runs for minutes and holds locks on

-- a LIVE DB. Scope the wallet computation to just the ~1.2k candidate transactions,

-- then UPDATE by primary key (short lock). Run with `transaction` as default DB

-- (CREATE TEMPORARY TABLE needs a selected schema).

--

-- Scope: status=1, billing/refund NULL, wallet_amount>0.

-- Expected on hadb1 (2026-06-08): 1162 candidates -> 945 phantom dropped

--   (no-debit ~1.67cr + net-settled ~14.6L), 217 net-committed kept (~9.38L,

--   of which 9.04L is the internal Test Account; real retail payable ~33.6k).

-- IDEMPOTENT: guarded on refund_timestamp IS NULL.

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

-- run with:  mysql ... transaction < cleanup_phantom_payment_failed.sql

CREATE TEMPORARY TABLE cand AS

  SELECT o.id AS order_id, CAST(o.transaction_id AS CHAR) AS ref

  FROM transaction.`order` o

  WHERE o.status = 1

    AND o.billing_timestamp IS NULL

    AND o.refund_timestamp IS NULL

    AND COALESCE(o.original_wallet_amount, o.wallet_amount) > 0;

ALTER TABLE cand ADD INDEX(ref);

CREATE TEMPORARY TABLE refstat AS

  SELECT h.reference AS ref,

         SUM(h.amount) AS net,

         MAX(CASE WHEN h.reference_type = 'PURCHASE' AND h.amount < 0 THEN 1 ELSE 0 END) AS has_debit

  FROM transaction.userwallethistory h

  WHERE h.reference IN (SELECT ref FROM cand)

  GROUP BY h.reference;

ALTER TABLE refstat ADD INDEX(ref);

-- phantom = no wallet debit, OR debit fully reversed (net >= 0). Net-committed (net < 0) excluded.

CREATE TEMPORARY TABLE phantom_ids AS

  SELECT c.order_id

  FROM cand c

  LEFT JOIN refstat r ON r.ref = c.ref

  WHERE r.has_debit IS NULL OR r.has_debit = 0 OR COALESCE(r.net, 0) >= -1;

UPDATE transaction.`order` o

  JOIN phantom_ids p ON p.order_id = o.id

SET o.refund_timestamp = o.created_timestamp,

    o.refunded_by      = 'system-cleanup',

    o.refund_reason    = 'Phantom PAYMENT_FAILED order (payment never completed; not a committed PO) - excluded from unbilled PO';

-- Verify: remaining status=1 in PI = the net-committed payable only.

-- SELECT COUNT(*), ROUND(SUM(COALESCE(original_wallet_amount,wallet_amount)),0)

-- FROM transaction.`order`

-- WHERE status=1 AND billing_timestamp IS NULL AND refund_timestamp IS NULL

--   AND COALESCE(original_wallet_amount,wallet_amount) > 0;   -- expect ~217 / ~9.38L