huang
95fc0b0a1b
All checks were successful
构建并部署到测试环境（无 SSH） / build-and-deploy (push) Successful in 8m16s
78 lines
2.8 KiB
SQL
78 lines
2.8 KiB
SQL
-- 盘点退款误重置资产钱包的候选记录。
|
|
-- 只读脚本：不会修改任何数据。
|
|
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,
|
|
COALESCE(r.order_type, o.order_type) AS order_type,
|
|
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
|
|
),
|
|
wallets AS (
|
|
SELECT
|
|
rr.*,
|
|
active_wallet.id AS active_wallet_id,
|
|
active_wallet.balance AS active_balance,
|
|
active_wallet.frozen_balance AS active_frozen_balance,
|
|
active_wallet.created_at AS active_wallet_created_at,
|
|
deleted_wallet.id AS deleted_wallet_id,
|
|
deleted_wallet.balance AS deleted_balance,
|
|
deleted_wallet.frozen_balance AS deleted_frozen_balance,
|
|
deleted_wallet.deleted_at AS deleted_wallet_deleted_at,
|
|
COALESCE(active_tx.tx_count, 0) AS active_wallet_tx_count,
|
|
COALESCE(deleted_tx.tx_count, 0) AS deleted_wallet_tx_count
|
|
FROM refund_resources rr
|
|
LEFT 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
|
|
LEFT 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
|
|
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
|
|
LEFT JOIN LATERAL (
|
|
SELECT COUNT(*) AS tx_count
|
|
FROM tb_asset_wallet_transaction t
|
|
WHERE t.asset_wallet_id = deleted_wallet.id
|
|
AND t.deleted_at IS NULL
|
|
) deleted_tx ON TRUE
|
|
)
|
|
SELECT
|
|
w.*,
|
|
CASE
|
|
WHEN w.deleted_wallet_id IS NULL THEN 'no_deleted_wallet'
|
|
WHEN w.active_wallet_id IS NULL THEN 'no_active_wallet'
|
|
WHEN w.active_wallet_tx_count = 0 AND w.active_balance = 0 AND w.active_frozen_balance = 0 THEN 'restore_old_wallet'
|
|
ELSE 'merge_old_balance_manual_review'
|
|
END AS suggested_action
|
|
FROM wallets w
|
|
ORDER BY w.refund_id DESC;
|