haha/scripts/repair_order_amount.sql

-- ============================================================
-- 修复订单金额 SQL 脚本
-- 场景：ORC_RESULT 回调创建了订单和商品明细，但 ORDER 回调
--       因签名验证失败被拒绝，导致 total_amount / paid_amount 为空
-- 逻辑：根据 order_goods 表汇总(price × product_num) 补齐金额
-- 日期：2026-07-20
-- ============================================================

-- 第一步：预览将要修复的订单（确认无误后再执行 UPDATE）
SELECT
    o.id,
    o.order_no,
    o.total_amount        AS 当前totalAmount,
    o.paid_amount         AS 当前paidAmount,
    o.pay_status,
    ROUND(SUM(og.price * og.product_num), 2) AS 计算金额,
    COUNT(og.id)          AS 商品条目数
FROM t_order o
INNER JOIN t_order_goods og ON og.order_id = o.id
WHERE (o.total_amount IS NULL OR o.total_amount <= 0)
  AND o.pay_status = 'UNPAID'
  AND o.pay_channel = 'wechat_payscore'
GROUP BY o.id, o.order_no, o.total_amount, o.paid_amount, o.pay_status
ORDER BY o.create_time DESC;

-- ============================================================
-- 第二步：确认预览结果无误后，取消注释以下语句执行更新
-- ============================================================

-- UPDATE t_order o
-- INNER JOIN (
--     SELECT
--         og.order_id,
--         ROUND(SUM(og.price * og.product_num), 2) AS calc_amount
--     FROM t_order_goods og
--     INNER JOIN t_order ord ON ord.id = og.order_id
--     WHERE (ord.total_amount IS NULL OR ord.total_amount <= 0)
--       AND ord.pay_status = 'UNPAID'
--       AND ord.pay_channel = 'wechat_payscore'
--     GROUP BY og.order_id
-- ) t ON o.id = t.order_id
-- SET
--     o.total_amount = t.calc_amount,
--     o.paid_amount  = t.calc_amount,
--     o.discount_amount = 0.00;

-- 验证更新结果：
-- SELECT id, order_no, total_amount, paid_amount, discount_amount, pay_status
-- FROM t_order
-- WHERE pay_channel = 'wechat_payscore' AND pay_status = 'UNPAID'
-- ORDER BY create_time DESC;