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