Subversion Repositories SmartDukaan

-- ============================================================================
-- Void net-settled stranded orders (status=0 left in "Unbilled PO")
-- ============================================================================
-- selectPendingIndentWalletAmount counts any billing/refund-NULL order. Some
-- status=0 orders had their wallet PURCHASE debit REVERSED via a non-standard
-- wallet entry (e.g. OTHERS "Oder Cancelled", "order not placed") -- so the
-- transaction nets to ~0 (fully refunded) but the order row stayed status=0 and
-- kept inflating unbilled PO. Two sub-classes:
--   (a) rejected-txn (transaction_approval REJECTED) the PO_REJECTED backfill
--       missed because the refund wasn't the standard "Order canceled" PURCHASE
--       entry  -> mark PO_REJECTED(91).
--   (b) non-rejected, refunded-by-other-means -> keep status=0, just drop from PI.
--
-- SAFETY: only net-settled (transaction net wallet >= 0) orders are voided. A
-- genuinely committed / awaiting-approval PO is net-DEBITED (net < 0) and is left
-- untouched -- this is what protects awaiting-approval bulk POs and real payables.
--
-- Mechanism: refund_timestamp = created_timestamp (drops from pending indent).
-- Use the TEMP-TABLE form (scope wallet net to candidate txns) -- the broad
-- PURCHASE-debit aggregate is a full scan that locks the LIVE DB (hadb1). Run with
-- `transaction` as default schema.
--
-- Ran on hadb1 (live) 2026-06-08: Group-B rejected 55 -> status 91; net-settled
-- non-rejected 186 -> refund_timestamp. IDEMPOTENT (guard refund_timestamp NULL).
-- ============================================================================

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

CREATE TEMPORARY TABLE cand AS
  SELECT o.id AS order_id, CAST(o.transaction_id AS CHAR) AS ref,
         CASE WHEN ta.id IS NOT NULL THEN 1 ELSE 0 END AS is_rejected
  FROM transaction.`order` o
  LEFT 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
    AND COALESCE(o.original_wallet_amount, o.wallet_amount) > 0;
ALTER TABLE cand ADD INDEX(ref);

CREATE TEMPORARY TABLE refnet AS
  SELECT h.reference AS ref, SUM(h.amount) AS net
  FROM transaction.userwallethistory h
  WHERE h.reference IN (SELECT ref FROM cand)
  GROUP BY h.reference;
ALTER TABLE refnet ADD INDEX(ref);

-- net-settled only (net >= -1). Net-committed (net < -1) excluded = real payable / awaiting-approval.
CREATE TEMPORARY TABLE void_ids AS
  SELECT c.order_id, c.is_rejected
  FROM cand c
  LEFT JOIN refnet r ON r.ref = c.ref
  WHERE COALESCE(r.net, 0) >= -1;

UPDATE transaction.`order` o
  JOIN void_ids v ON v.order_id = o.id
SET o.status           = CASE WHEN v.is_rejected = 1 THEN 91 ELSE o.status END,  -- 91=PO_REJECTED for rejected txns
    o.refund_timestamp = o.created_timestamp,
    o.refunded_by      = 'system-cleanup',
    o.refund_reason    = CASE WHEN v.is_rejected = 1
                              THEN 'PO rejected (refunded via non-standard wallet entry; order row stranded) - excluded from unbilled PO'
                              ELSE 'Stranded order, wallet debit already reversed (net-settled); excluded from unbilled PO' END;

-- Verify: no net-settled stranded status=0 orders should remain (only net-committed kept).