-- =============================================================================
-- 分账开关 & 已分账订单佣金去向 · 线上只读核验脚本
-- =============================================================================
-- 用途：
--   1) 判断当前是否走支付通道分账（微信分账 / 汇付延时分账）
--   2) 核对已分账订单：网关分账明细 vs 业务佣金/钱包入账是否一致、有无重复发放风险
--
-- 用法：
--   mysql -u用户名 -p 数据库名 < split_commission_verify.sql
--   或在客户端中分段执行（建议先跑「一、全局配置」）
--
-- 表前缀默认 fa_，若不同请全局替换 fa_
-- 本脚本仅 SELECT，不修改数据
-- =============================================================================

SET NAMES utf8mb4;

-- =============================================================================
-- 一、全局配置：是否开启分账、走哪条通道
-- =============================================================================
SELECT '=== 1. 分账总配置 shopro_split_config ===' AS section;

SELECT name, value, description
FROM fa_shopro_split_config
WHERE name IN (
  'split_enable', 'split_mode', 'split_provider', 'split_timing',
  'split_amount_source', 'split_max_rate', 'delay_acct_enable',
  'platform_receiver_account', 'platform_receiver_name',
  'platform_huifu_id', 'platform_acct_id'
)
ORDER BY name;

SELECT '=== 2. 汇付支付配置 shopro_huifu_config（关键项）===' AS section;

SELECT name, value
FROM fa_shopro_huifu_config
WHERE name IN (
  'status', 'env', 'huifu_id', 'upper_huifu_id',
  'miniprogram_channel', 'delay_acct_enable', 'onboard_required'
)
ORDER BY name;

SELECT '=== 3. 分销/区域代理结算事件（与分账时机对比）===' AS section;

SELECT 'commission' AS cfg, name, value
FROM fa_shopro_commission_config
WHERE name IN ('commission_event', 'level', 'self_buy')
UNION ALL
SELECT 'regional_agent', name, value
FROM fa_shopro_regional_agent_config
WHERE name IN ('commission_event', 'status');

-- 人工解读（见下方说明）：
-- split_enable=1 且 split_mode 非 wallet → 会尝试支付通道分账
-- miniprogram_channel=huifu + split 开启 + pay_type=huifu_wechat → 汇付延时分账
-- split_timing 默认 payed；commission_event 默认 confirm → 可能出现「先分账、后入账钱包」

SELECT '=== 4. 配置结论（自动推断，供对照）===' AS section;

SELECT
  sc_enable.value AS split_enable,
  sc_mode.value AS split_mode,
  sc_provider.value AS split_provider,
  sc_timing.value AS split_timing,
  sc_delay.value AS split_delay_acct_enable,
  hf_channel.value AS miniprogram_channel,
  hf_status.value AS huifu_plugin_status,
  CASE
    WHEN IFNULL(sc_enable.value, '0') <> '1' THEN '未走支付分账（总开关关闭）'
    WHEN sc_mode.value = 'wallet' THEN '未走支付分账（仅钱包模式）'
    WHEN hf_channel.value = 'huifu' AND IFNULL(hf_status.value, '0') = '1'
         AND sc_mode.value IN ('huifu', 'both')
      THEN '走汇付延时分账（huifu_wechat 订单）'
    WHEN hf_channel.value = 'huifu' AND IFNULL(hf_status.value, '0') = '1'
         AND IFNULL(sc_provider.value, 'auto') IN ('auto', 'huifu')
      THEN '走汇付延时分账（auto/huifu 提供方 + 小程序汇付通道）'
    WHEN sc_mode.value IN ('wechat', 'both') THEN '走微信分账（pay_type=wechat 订单）'
    ELSE '分账已开但通道需结合 pay_type 人工确认'
  END AS inferred_split_path
FROM (SELECT value FROM fa_shopro_split_config WHERE name = 'split_enable' LIMIT 1) sc_enable
CROSS JOIN (SELECT IFNULL(value, 'wechat') AS value FROM fa_shopro_split_config WHERE name = 'split_mode' LIMIT 1) sc_mode
CROSS JOIN (SELECT IFNULL(value, 'auto') AS value FROM fa_shopro_split_config WHERE name = 'split_provider' LIMIT 1) sc_provider
CROSS JOIN (SELECT IFNULL(value, 'payed') AS value FROM fa_shopro_split_config WHERE name = 'split_timing' LIMIT 1) sc_timing
CROSS JOIN (SELECT IFNULL(value, '0') AS value FROM fa_shopro_split_config WHERE name = 'delay_acct_enable' LIMIT 1) sc_delay
CROSS JOIN (SELECT IFNULL(value, 'yansongda') AS value FROM fa_shopro_huifu_config WHERE name = 'miniprogram_channel' LIMIT 1) hf_channel
CROSS JOIN (SELECT IFNULL(value, '0') AS value FROM fa_shopro_huifu_config WHERE name = 'status' LIMIT 1) hf_status;

-- =============================================================================
-- 二、分账订单统计（近 90 天，可改 INTERVAL）
-- =============================================================================
SELECT '=== 5. 分账订单按状态/通道汇总（近90天）===' AS section;

SELECT
  so.channel,
  so.status,
  CASE so.status
    WHEN 0 THEN '待分账'
    WHEN 1 THEN '分账中'
    WHEN 2 THEN '成功'
    WHEN 3 THEN '部分成功'
    WHEN 4 THEN '失败'
    ELSE CONCAT('未知(', so.status, ')')
  END AS status_label,
  COUNT(*) AS cnt,
  ROUND(SUM(so.split_amount), 2) AS sum_split_amount,
  ROUND(SUM(so.total_amount), 2) AS sum_order_pay
FROM fa_shopro_split_order so
INNER JOIN fa_shopro_order o ON o.id = so.order_id
WHERE o.paytime >= UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 90 DAY))
GROUP BY so.channel, so.status
ORDER BY so.channel, so.status;

SELECT '=== 6. 已支付订单 pay_type 分布（近90天）===' AS section;

SELECT
  pay_type,
  COUNT(*) AS order_cnt,
  ROUND(SUM(pay_fee), 2) AS sum_pay_fee
FROM fa_shopro_order
WHERE status >= 1
  AND paytime >= UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 90 DAY))
GROUP BY pay_type;

-- =============================================================================
-- 三、已分账订单：佣金去了哪里（网关 vs 钱包）
-- =============================================================================
-- 网关分账：fa_shopro_split_order_receiver + receiver 表（汇付 huifu_id / 微信 openid）
-- 业务佣金：fa_shopro_commission_reward / fa_shopro_regional_agent_reward
-- 钱包入账：fa_shopro_user_wallet_log type=commission_income / regional_agent_income
-- 代码说明：SplitWalletSettlement + shouldSettle*ViaWalletHook 控制分账成功不入余额；§9 仍用于核对历史数据

SELECT '=== 7. 分账成功订单明细（最近 30 笔）===' AS section;

SELECT
  so.id AS split_order_id,
  so.order_id,
  so.order_sn,
  so.channel,
  so.status,
  so.total_amount AS order_pay_fee,
  so.split_amount AS split_to_receivers,
  ROUND(so.total_amount - so.split_amount, 2) AS implied_platform_remain,
  FROM_UNIXTIME(so.createtime) AS split_at,
  so.confirm_hf_seq_id,
  o.pay_type
FROM fa_shopro_split_order so
INNER JOIN fa_shopro_order o ON o.id = so.order_id
WHERE so.status IN (2, 3)
ORDER BY so.id DESC
LIMIT 30;

SELECT '=== 8. 上述成功分账的接收方明细（最近 30 单）===' AS section;

SELECT
  so.order_sn,
  sor.role_type,
  sor.amount,
  sor.description,
  sor.status AS line_status,
  sr.receiver_account,
  sr.receiver_type,
  sr.channel AS receiver_channel,
  sr.receiver_name
FROM fa_shopro_split_order so
INNER JOIN fa_shopro_split_order_receiver sor ON sor.split_order_id = so.id
LEFT JOIN fa_shopro_split_receiver sr ON sr.id = sor.receiver_id
WHERE so.status IN (2, 3)
  AND so.id IN (
    SELECT id FROM (
      SELECT so2.id
      FROM fa_shopro_split_order so2
      WHERE so2.status IN (2, 3)
      ORDER BY so2.id DESC
      LIMIT 30
    ) t
  )
ORDER BY so.order_sn DESC, sor.id;

SELECT '=== 9. 分账成功订单 · 业务佣金与钱包对照 ===' AS section;

SELECT
  so.order_sn,
  so.channel,
  so.split_amount AS gateway_split_sum,
  IFNULL(cr.dist_commission, 0) AS dist_reward_sum,
  IFNULL(cr.dist_accounted, 0) AS dist_wallet_settled_cnt,
  IFNULL(rr.reg_commission, 0) AS regional_reward_sum,
  IFNULL(rr.reg_accounted, 0) AS regional_wallet_settled_cnt,
  IFNULL(wl.wallet_commission, 0) AS wallet_commission_income,
  IFNULL(wl.wallet_regional, 0) AS wallet_regional_income,
  CASE
    WHEN IFNULL(wl.wallet_commission, 0) + IFNULL(wl.wallet_regional, 0) > 0
         AND so.split_amount > 0
      THEN '⚠ 网关已分账且钱包也有佣金流水（可能双重发放，需业务确认）'
    WHEN so.split_amount > 0
         AND IFNULL(wl.wallet_commission, 0) + IFNULL(wl.wallet_regional, 0) = 0
         AND IFNULL(cr.dist_accounted, 0) + IFNULL(rr.reg_accounted, 0) = 0
      THEN '✓ 佣金主要在支付通道（未入钱包或尚未结算）'
    WHEN so.split_amount = 0 THEN '分账金额为0（检查接收方/规则）'
    ELSE '请人工核对'
  END AS check_hint
FROM fa_shopro_split_order so
INNER JOIN fa_shopro_order o ON o.id = so.order_id
LEFT JOIN (
  SELECT order_id,
    SUM(commission) AS dist_commission,
    SUM(CASE WHEN status = 1 THEN 1 ELSE 0 END) AS dist_accounted
  FROM fa_shopro_commission_reward
  WHERE status >= 0
  GROUP BY order_id
) cr ON cr.order_id = so.order_id
LEFT JOIN (
  SELECT ro.order_id,
    SUM(rr.commission) AS reg_commission,
    SUM(CASE WHEN rr.status = 1 THEN 1 ELSE 0 END) AS reg_accounted
  FROM fa_shopro_regional_agent_reward rr
  INNER JOIN fa_shopro_regional_agent_order ro ON ro.id = rr.regional_order_id
  WHERE rr.status >= 0
  GROUP BY ro.order_id
) rr ON rr.order_id = so.order_id
LEFT JOIN (
  SELECT order_id,
    SUM(wallet_commission) AS wallet_commission,
    SUM(wallet_regional) AS wallet_regional
  FROM (
    SELECT cr.order_id,
      SUM(CASE WHEN wl.type = 'commission_income' THEN wl.wallet ELSE 0 END) AS wallet_commission,
      0 AS wallet_regional
    FROM fa_shopro_commission_reward cr
    INNER JOIN fa_shopro_user_wallet_log wl
      ON wl.item_id = cr.id AND wl.type = 'commission_income' AND wl.wallet_type = 'money'
    WHERE cr.status = 1
    GROUP BY cr.order_id
    UNION ALL
    SELECT ro.order_id, 0,
      SUM(CASE WHEN wl.type = 'regional_agent_income' THEN wl.wallet ELSE 0 END)
    FROM fa_shopro_regional_agent_reward rr
    INNER JOIN fa_shopro_regional_agent_order ro ON ro.id = rr.regional_order_id
    INNER JOIN fa_shopro_user_wallet_log wl
      ON wl.item_id = rr.id AND wl.type = 'regional_agent_income' AND wl.wallet_type = 'money'
    WHERE rr.status = 1
    GROUP BY ro.order_id
  ) wsum
  GROUP BY order_id
) wl ON wl.order_id = so.order_id
WHERE so.status IN (2, 3)
ORDER BY so.id DESC
LIMIT 50;

-- =============================================================================
-- 四、异常检测（应排查项）
-- =============================================================================
SELECT '=== 10. 有佣金记录但分账失败/缺失（汇付单，近90天）===' AS section;

SELECT
  o.id AS order_id,
  o.order_sn,
  o.pay_type,
  o.pay_fee,
  IFNULL(so.status, -99) AS split_status,
  LEFT(so.wx_result, 120) AS split_fail_msg,
  IFNULL(cr.reward_cnt, 0) + IFNULL(rr.reward_cnt, 0) AS reward_cnt,
  IFNULL(cr.reward_sum, 0) + IFNULL(rr.reward_sum, 0) AS reward_sum
FROM fa_shopro_order o
LEFT JOIN (
  SELECT order_id, COUNT(*) AS reward_cnt, SUM(commission) AS reward_sum
  FROM fa_shopro_commission_reward WHERE status >= 0 GROUP BY order_id
) cr ON cr.order_id = o.id
LEFT JOIN (
  SELECT ro.order_id, COUNT(*) AS reward_cnt, SUM(rr.commission) AS reward_sum
  FROM fa_shopro_regional_agent_reward rr
  INNER JOIN fa_shopro_regional_agent_order ro ON ro.id = rr.regional_order_id
  WHERE rr.status >= 0
  GROUP BY ro.order_id
) rr ON rr.order_id = o.id
LEFT JOIN fa_shopro_split_order so ON so.order_id = o.id AND so.channel = 'huifu'
WHERE o.pay_type = 'huifu_wechat'
  AND o.paytime >= UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 90 DAY))
  AND (IFNULL(cr.reward_cnt, 0) + IFNULL(rr.reward_cnt, 0)) > 0
  AND (so.id IS NULL OR so.status IN (0, 4))
ORDER BY o.id DESC
LIMIT 50;

SELECT '=== 11. 分账失败记录（status=4，最近 20 条）===' AS section;

SELECT
  so.order_id,
  so.order_sn,
  so.channel,
  so.split_amount,
  so.retry_count,
  FROM_UNIXTIME(so.next_retry_time) AS next_retry,
  LEFT(so.wx_result, 200) AS fail_reason
FROM fa_shopro_split_order so
WHERE so.status = 4
ORDER BY so.id DESC
LIMIT 20;

SELECT '=== 12. 有推广/区域佣金但缺少汇付接收方（导致无法分账）===' AS section;

-- 推广：r.agent_id 即分销商 user_id（非 commission_agent 表主键）
SELECT DISTINCT
  r.order_id,
  o.order_sn,
  'commission' AS reward_source,
  r.agent_id AS beneficiary_user_id,
  u.mobile,
  ho.huifu_id AS onboard_huifu_id,
  sr.id AS split_receiver_id
FROM fa_shopro_commission_reward r
INNER JOIN fa_shopro_order o ON o.id = r.order_id
INNER JOIN fa_user u ON u.id = r.agent_id
LEFT JOIN fa_shopro_huifu_onboard ho ON ho.user_id = r.agent_id AND ho.onboard_status = 'approved'
LEFT JOIN fa_shopro_split_receiver sr ON sr.role_type = 'promoter'
  AND sr.role_id = r.agent_id AND sr.channel = 'huifu' AND sr.status = 'normal'
WHERE r.status >= 0
  AND o.pay_type = 'huifu_wechat'
  AND o.paytime >= UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 90 DAY))
  AND sr.id IS NULL
LIMIT 30;

SELECT DISTINCT
  rr.order_id,
  o.order_sn,
  'regional_agent' AS reward_source,
  rr.agent_user_id AS beneficiary_user_id,
  u.mobile,
  ho.huifu_id AS onboard_huifu_id,
  sr.id AS split_receiver_id
FROM fa_shopro_regional_agent_reward rr
INNER JOIN fa_shopro_order o ON o.id = rr.order_id
INNER JOIN fa_user u ON u.id = rr.agent_user_id
LEFT JOIN fa_shopro_huifu_onboard ho ON ho.user_id = rr.agent_user_id AND ho.onboard_status = 'approved'
LEFT JOIN fa_shopro_split_receiver sr ON sr.role_type = 'regional_agent'
  AND sr.role_id = rr.agent_user_id AND sr.channel = 'huifu' AND sr.status = 'normal'
WHERE rr.status >= 0
  AND o.pay_type = 'huifu_wechat'
  AND o.paytime >= UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 90 DAY))
  AND sr.id IS NULL
LIMIT 30;

SELECT '=== 13. 延时分账标记：支付扩展 delay_acct（近90天汇付单抽样）===' AS section;

SELECT
  pe.order_id,
  o.order_sn,
  pe.delay_acct_flag,
  pe.confirm_status,
  pe.confirm_hf_seq_id,
  so.status AS split_order_status
FROM fa_shopro_huifu_pay_ext pe
INNER JOIN fa_shopro_order o ON o.id = pe.order_id
LEFT JOIN fa_shopro_split_order so ON so.order_id = pe.order_id AND so.channel = 'huifu'
WHERE o.pay_type = 'huifu_wechat'
  AND o.paytime >= UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 90 DAY))
ORDER BY pe.order_id DESC
LIMIT 30;

SELECT '=== 14. 分账接收方台账（汇付/微信）===' AS section;

SELECT channel, role_type, COUNT(*) AS cnt,
  SUM(CASE WHEN status = 'normal' THEN 1 ELSE 0 END) AS normal_cnt
FROM fa_shopro_split_receiver
GROUP BY channel, role_type;

-- =============================================================================
-- 五、单笔订单深度核验
-- 用法：只改下面一行 @order_id 或 @order_sn，然后整段执行
-- 预设：77 = 598单 202605495163454091004900；74 = 分账成功样例 202609215157744091003800
-- =============================================================================

SET @order_id = 77;
-- SET @order_sn = '202605495163454091004900';
-- SET @order_id = (SELECT id FROM fa_shopro_order WHERE order_sn = @order_sn LIMIT 1);

SELECT '=== 5.1 订单主表 ===' AS section;
-- 确认/完成时间在 ext JSON（confirm_time / finish_time），非独立字段
SELECT id, order_sn, status, pay_type, pay_fee, total_fee,
  FROM_UNIXTIME(paytime) AS pay_time,
  FROM_UNIXTIME(
    NULLIF(CAST(JSON_UNQUOTE(JSON_EXTRACT(ext, '$.confirm_time')) AS UNSIGNED), 0)
  ) AS confirm_time,
  FROM_UNIXTIME(
    NULLIF(CAST(JSON_UNQUOTE(JSON_EXTRACT(ext, '$.finish_time')) AS UNSIGNED), 0)
  ) AS finish_time,
  FROM_UNIXTIME(updatetime) AS order_updated,
  ext
FROM fa_shopro_order WHERE id = @order_id;

SELECT '=== 5.2 订单商品行 ===' AS section;
SELECT id AS order_item_id, goods_id, goods_title, goods_price, goods_num,
  pay_price, discount_fee, ext
FROM fa_shopro_order_item WHERE order_id = @order_id;

SELECT '=== 5.3 汇付支付扩展 delay_acct ===' AS section;
SELECT order_id, delay_acct_flag, confirm_status, confirm_hf_seq_id, hf_seq_id, trans_stat,
  LEFT(extra, 200) AS extra_preview
FROM fa_shopro_huifu_pay_ext WHERE order_id = @order_id;

SELECT '=== 5.4 分账单（全部状态）===' AS section;
SELECT id, channel, status,
  CASE status WHEN 0 THEN '待分账' WHEN 1 THEN '分账中' WHEN 2 THEN '成功'
    WHEN 3 THEN '部分成功' WHEN 4 THEN '失败' ELSE status END AS status_label,
  total_amount, split_amount, confirm_hf_seq_id,
  FROM_UNIXTIME(createtime) AS created_at,
  LEFT(wx_result, 180) AS result_msg
FROM fa_shopro_split_order WHERE order_id = @order_id
ORDER BY id DESC;

SELECT '=== 5.5 分账接收方明细 ===' AS section;
SELECT so.order_sn, sor.role_type, sor.amount, sor.description, sor.status AS line_status,
  sr.receiver_account, sr.receiver_type, sr.channel, sr.receiver_name, sr.role_id AS user_id
FROM fa_shopro_split_order so
INNER JOIN fa_shopro_split_order_receiver sor ON sor.split_order_id = so.id
LEFT JOIN fa_shopro_split_receiver sr ON sr.id = sor.receiver_id
WHERE so.order_id = @order_id
ORDER BY so.id DESC, sor.id;

SELECT '=== 5.6 分销单 + 推广佣金 ===' AS section;
SELECT co.id AS commission_order_id, co.order_item_id, co.amount AS dist_base,
  co.commission_price_type, co.commission_event,
  co.commission_reward_status, FROM_UNIXTIME(co.createtime) AS co_created,
  FROM_UNIXTIME(co.commission_time) AS co_settled,
  LEFT(co.commission_rules, 120) AS rules_preview
FROM fa_shopro_commission_order co
WHERE co.order_id = @order_id;

SELECT r.id AS reward_id, r.agent_id AS user_id, u.nickname, u.mobile,
  r.commission_level, r.commission, r.status,
  CASE r.status WHEN 0 THEN '待入账' WHEN 1 THEN '已入账' WHEN -1 THEN '已退回' ELSE r.status END AS status_label,
  r.commission_rules, FROM_UNIXTIME(r.createtime) AS reward_created
FROM fa_shopro_commission_reward r
LEFT JOIN fa_user u ON u.id = r.agent_id
WHERE r.order_id = @order_id
ORDER BY r.id;

SELECT '=== 5.7 区域代理单 + 区域佣金 ===' AS section;
SELECT ro.id AS regional_order_id, ro.amount AS regional_base, ro.commission_event,
  ro.commission_reward_status, FROM_UNIXTIME(ro.createtime) AS ro_created
FROM fa_shopro_regional_agent_order ro
WHERE ro.order_id = @order_id;

SELECT rr.id AS reward_id, rr.agent_user_id AS user_id, u.nickname, u.mobile,
  rr.commission, rr.status,
  CASE rr.status WHEN 0 THEN '待入账' WHEN 1 THEN '已入账' WHEN -1 THEN '已退回' ELSE rr.status END AS status_label,
  FROM_UNIXTIME(rr.createtime) AS reward_created
FROM fa_shopro_regional_agent_reward rr
LEFT JOIN fa_user u ON u.id = rr.agent_user_id
WHERE rr.order_id = @order_id
ORDER BY rr.id;

SELECT '=== 5.8 钱包流水（佣金/区域）===' AS section;
SELECT wl.id, wl.user_id, wl.type, wl.wallet, wl.item_id,
  FROM_UNIXTIME(wl.createtime) AS log_time, LEFT(wl.memo, 80) AS memo
FROM fa_shopro_user_wallet_log wl
WHERE wl.wallet_type = 'money'
  AND wl.type IN ('commission_income', 'regional_agent_income', 'commission_back', 'regional_agent_back')
  AND (
    wl.item_id IN (SELECT id FROM fa_shopro_commission_reward WHERE order_id = @order_id)
    OR wl.item_id IN (SELECT id FROM fa_shopro_regional_agent_reward WHERE order_id = @order_id)
  )
ORDER BY wl.id;

SELECT '=== 5.9 已入账佣金但无钱包流水（应为通道分账/仅记 total_income）===' AS section;
SELECT 'commission' AS src, r.id AS reward_id, r.agent_id AS user_id, r.commission,
  IF(wl.id IS NULL, '无 wallet_log（符合 gateway）', '有 wallet_log（可能双发）') AS wallet_check
FROM fa_shopro_commission_reward r
LEFT JOIN fa_shopro_user_wallet_log wl ON wl.wallet_type = 'money'
  AND wl.type = 'commission_income' AND wl.item_id = r.id
WHERE r.order_id = @order_id AND r.status = 1
UNION ALL
SELECT 'regional', rr.id, rr.agent_user_id, rr.commission,
  IF(wl.id IS NULL, '无 wallet_log（符合 gateway）', '有 wallet_log（可能双发）')
FROM fa_shopro_regional_agent_reward rr
LEFT JOIN fa_shopro_user_wallet_log wl ON wl.wallet_type = 'money'
  AND wl.type = 'regional_agent_income' AND wl.item_id = rr.id
WHERE rr.order_id = @order_id AND rr.status = 1;

SELECT '=== 5.10 本单结论（与 §9 同逻辑）===' AS section;
SELECT
  o.order_sn,
  o.pay_fee,
  IFNULL(so.split_amount, 0) AS gateway_split_sum,
  IFNULL(cr.dist_sum, 0) AS dist_reward_sum,
  IFNULL(rr.reg_sum, 0) AS regional_reward_sum,
  IFNULL(wl.w_comm, 0) AS wallet_commission,
  IFNULL(wl.w_reg, 0) AS wallet_regional,
  CASE
    WHEN IFNULL(wl.w_comm, 0) + IFNULL(wl.w_reg, 0) > 0 AND IFNULL(so.split_amount, 0) > 0
      THEN '⚠ 网关已分账且钱包也有流水'
    WHEN IFNULL(so.split_amount, 0) > 0
         AND IFNULL(wl.w_comm, 0) + IFNULL(wl.w_reg, 0) = 0
      THEN '✓ 分账成功且未进钱包（或仅记收益）'
    WHEN IFNULL(so.id, 0) = 0 OR so.status NOT IN (2, 3)
      THEN '未分账成功：佣金可能在钱包或仍待结算'
    ELSE '请人工核对'
  END AS check_hint
FROM fa_shopro_order o
LEFT JOIN (
  SELECT order_id, MAX(id) AS id, MAX(split_amount) AS split_amount, MAX(status) AS status
  FROM fa_shopro_split_order WHERE channel = 'huifu' GROUP BY order_id
) so ON so.order_id = o.id
LEFT JOIN (
  SELECT order_id, SUM(commission) AS dist_sum FROM fa_shopro_commission_reward WHERE status >= 0 GROUP BY order_id
) cr ON cr.order_id = o.id
LEFT JOIN (
  SELECT order_id, SUM(commission) AS reg_sum FROM fa_shopro_regional_agent_reward WHERE status >= 0 GROUP BY order_id
) rr ON rr.order_id = o.id
LEFT JOIN (
  SELECT order_id,
    SUM(CASE WHEN wl.type = 'commission_income' THEN wl.wallet ELSE 0 END) AS w_comm,
    SUM(CASE WHEN wl.type = 'regional_agent_income' THEN wl.wallet ELSE 0 END) AS w_reg
  FROM (
    SELECT cr.order_id, wl.type, wl.wallet
    FROM fa_shopro_commission_reward cr
    INNER JOIN fa_shopro_user_wallet_log wl ON wl.item_id = cr.id AND wl.type = 'commission_income' AND wl.wallet_type = 'money'
    UNION ALL
    SELECT rr.order_id, wl.type, wl.wallet
    FROM fa_shopro_regional_agent_reward rr
    INNER JOIN fa_shopro_user_wallet_log wl ON wl.item_id = rr.id AND wl.type = 'regional_agent_income' AND wl.wallet_type = 'money'
  ) wl GROUP BY order_id
) wl ON wl.order_id = o.id
WHERE o.id = @order_id;