junhong_cmp_fiber/scripts/refund_wallet_reset_repair.sql

-- 修复退款误重置资产钱包的候选记录。
-- 执行前先运行 scripts/refund_wallet_reset_audit.sql 复核输出。
-- 幂等策略：
-- 1. 新钱包无余额变动时，软删新钱包并恢复旧钱包。
-- 2. 新钱包已有余额或流水时，将旧钱包余额合并到新钱包，并把旧钱包余额清零，避免重复合并。
BEGIN;

CREATE TEMP TABLE refund_wallet_reset_targets ON COMMIT DROP AS
WITH refund_resources AS (
    SELECT
        r.id AS refund_id,
        r.refund_no,
        r.order_id,
        o.order_no,
        o.generation AS order_generation,
        r.processed_at,
        CASE
            WHEN COALESCE(r.iot_card_id, o.iot_card_id) IS NOT NULL THEN 'iot_card'
            ELSE 'device'
        END AS resource_type,
        COALESCE(r.iot_card_id, o.iot_card_id, r.device_id, o.device_id) AS resource_id
    FROM tb_refund_request r
    JOIN tb_order o ON o.id = r.order_id
    WHERE r.deleted_at IS NULL
      AND r.status = 2
      AND r.asset_reset = TRUE
),
wallet_pairs AS (
    SELECT
        rr.*,
        active_wallet.id AS active_wallet_id,
        active_wallet.balance AS active_balance,
        active_wallet.frozen_balance AS active_frozen_balance,
        deleted_wallet.id AS deleted_wallet_id,
        deleted_wallet.balance AS deleted_balance,
        deleted_wallet.frozen_balance AS deleted_frozen_balance,
        COALESCE(active_tx.tx_count, 0) AS active_wallet_tx_count
    FROM refund_resources rr
    JOIN LATERAL (
        SELECT *
        FROM tb_asset_wallet w
        WHERE w.resource_type = rr.resource_type
          AND w.resource_id = rr.resource_id
          AND w.deleted_at IS NULL
        ORDER BY w.created_at DESC, w.id DESC
        LIMIT 1
    ) active_wallet ON TRUE
    JOIN LATERAL (
        SELECT *
        FROM tb_asset_wallet w
        WHERE w.resource_type = rr.resource_type
          AND w.resource_id = rr.resource_id
          AND w.deleted_at IS NOT NULL
          AND (w.balance <> 0 OR w.frozen_balance <> 0)
        ORDER BY w.deleted_at DESC, w.id DESC
        LIMIT 1
    ) deleted_wallet ON TRUE
    LEFT JOIN LATERAL (
        SELECT COUNT(*) AS tx_count
        FROM tb_asset_wallet_transaction t
        WHERE t.asset_wallet_id = active_wallet.id
          AND t.deleted_at IS NULL
    ) active_tx ON TRUE
)
SELECT
    *,
    CASE
        WHEN active_wallet_tx_count = 0 AND active_balance = 0 AND active_frozen_balance = 0 THEN 'restore_old_wallet'
        ELSE 'merge_old_balance'
    END AS repair_mode
FROM wallet_pairs;

-- 新钱包没有后续余额变动：先软删新钱包，再恢复旧钱包，避免唯一索引冲突。
UPDATE tb_asset_wallet w
SET deleted_at = NOW(),
    updated_at = NOW()
FROM refund_wallet_reset_targets t
WHERE t.repair_mode = 'restore_old_wallet'
  AND w.id = t.active_wallet_id
  AND w.deleted_at IS NULL;

UPDATE tb_asset_wallet w
SET deleted_at = NULL,
    updated_at = NOW()
FROM refund_wallet_reset_targets t
WHERE t.repair_mode = 'restore_old_wallet'
  AND w.id = t.deleted_wallet_id
  AND w.deleted_at IS NOT NULL;

-- 新钱包已有后续余额变动：合并旧钱包余额，并清零旧钱包余额，保证重复执行不会二次入账。
UPDATE tb_asset_wallet w
SET balance = w.balance + t.deleted_balance,
    frozen_balance = w.frozen_balance + t.deleted_frozen_balance,
    version = w.version + 1,
    updated_at = NOW()
FROM refund_wallet_reset_targets t
WHERE t.repair_mode = 'merge_old_balance'
  AND w.id = t.active_wallet_id
  AND w.deleted_at IS NULL;

UPDATE tb_asset_wallet w
SET balance = 0,
    frozen_balance = 0,
    updated_at = NOW()
FROM refund_wallet_reset_targets t
WHERE t.repair_mode = 'merge_old_balance'
  AND w.id = t.deleted_wallet_id
  AND w.deleted_at IS NOT NULL;

-- 安全恢复单次误增的资产世代；复杂场景保留给人工复核。
UPDATE tb_iot_card c
SET generation = t.order_generation,
    updated_at = NOW()
FROM refund_wallet_reset_targets t
WHERE t.resource_type = 'iot_card'
  AND c.id = t.resource_id
  AND c.generation = t.order_generation + 1
  AND NOT EXISTS (
      SELECT 1
      FROM tb_exchange_order e
      WHERE e.deleted_at IS NULL
        AND e.status = 4
        AND e.old_asset_type = t.resource_type
        AND e.old_asset_id = t.resource_id
        AND e.created_at >= t.processed_at
  );

UPDATE tb_device d
SET generation = t.order_generation,
    updated_at = NOW()
FROM refund_wallet_reset_targets t
WHERE t.resource_type = 'device'
  AND d.id = t.resource_id
  AND d.generation = t.order_generation + 1
  AND NOT EXISTS (
      SELECT 1
      FROM tb_exchange_order e
      WHERE e.deleted_at IS NULL
        AND e.status = 4
        AND e.old_asset_type = t.resource_type
        AND e.old_asset_id = t.resource_id
        AND e.created_at >= t.processed_at
  );

SELECT
    repair_mode,
    COUNT(*) AS affected_resources,
    SUM(deleted_balance) AS restored_balance,
    SUM(deleted_frozen_balance) AS restored_frozen_balance
FROM refund_wallet_reset_targets
GROUP BY repair_mode
ORDER BY repair_mode;

COMMIT;