sqxp/docs/sql/migration_20260408_biz_order_status_backfill.sql

-- 历史订单状态回填脚本
-- 适用场景：
-- 1) 已执行以下脚本，新增了三状态与取消审核字段
--    - migration_20260408_biz_order_three_status.sql
--    - migration_20260408_biz_order_cancel_status.sql
-- 2) 需要把历史旧 status 映射回新字段
--
-- 说明：
-- - 本脚本会回填 biz_order 与 biz_order_his
-- - 回填规则以旧 status 为主，金额字段为辅
-- - 可重复执行（幂等）

START TRANSACTION;

-- =========================
-- 1) 回填主表 biz_order
-- =========================
UPDATE `biz_order`
SET
  -- 订单主状态
  `order_status` = CASE
    WHEN `status` = 'cancelled' THEN 'cancelled'
    WHEN `status` = 'completed' THEN 'completed'
    ELSE 'pending'
  END,

  -- 配送状态
  `delivery_status` = CASE
    WHEN `status` IN ('pending_payment', 'pending_delivery', 'pending_review_cancel') THEN 'pending_delivery'
    WHEN `status` = 'delivering' THEN 'delivering'
    WHEN `status` = 'partial_delivered' THEN 'partial_delivered'
    WHEN `status` IN ('pending_confirm', 'completed') THEN 'delivered'
    WHEN `status` = 'cancelled' THEN
      CASE
        WHEN IFNULL(`delivery_assign_time`, NULL) IS NOT NULL THEN 'pending_delivery'
        ELSE 'pending_delivery'
      END
    ELSE 'pending_delivery'
  END,

  -- 支付状态
  `pay_status` = CASE
    WHEN `status` = 'refunding' THEN 'refunding'
    WHEN IFNULL(`refund_amount`, 0) > 0 AND IFNULL(`paid_amount`, 0) <= 0 THEN 'refunded'
    WHEN IFNULL(`paid_amount`, 0) > 0 THEN 'paid'
    ELSE 'pending_payment'
  END,

  -- 取消审核状态
  `cancel_status` = CASE
    WHEN `status` = 'pending_review_cancel' THEN 'pending_review'
    WHEN `status` = 'cancelled' AND `cancel_review_time` IS NOT NULL THEN 'approved'
    WHEN `status` IN ('pending_delivery', 'delivering', 'partial_delivered') AND `cancel_review_time` IS NOT NULL THEN 'rejected'
    ELSE 'none'
  END;


-- =========================
-- 2) 回填历史表 biz_order_his
-- =========================
UPDATE `biz_order_his`
SET
  `order_status` = CASE
    WHEN `status` = 'cancelled' THEN 'cancelled'
    WHEN `status` = 'completed' THEN 'completed'
    ELSE 'pending'
  END,

  `delivery_status` = CASE
    WHEN `status` IN ('pending_payment', 'pending_delivery', 'pending_review_cancel') THEN 'pending_delivery'
    WHEN `status` = 'delivering' THEN 'delivering'
    WHEN `status` = 'partial_delivered' THEN 'partial_delivered'
    WHEN `status` IN ('pending_confirm', 'completed') THEN 'delivered'
    ELSE 'pending_delivery'
  END,

  `pay_status` = CASE
    WHEN `status` = 'refunding' THEN 'refunding'
    WHEN IFNULL(`refund_amount`, 0) > 0 AND IFNULL(`paid_amount`, 0) <= 0 THEN 'refunded'
    WHEN IFNULL(`paid_amount`, 0) > 0 THEN 'paid'
    ELSE 'pending_payment'
  END,

  `cancel_status` = CASE
    WHEN `status` = 'pending_review_cancel' THEN 'pending_review'
    WHEN `status` = 'cancelled' AND `cancel_review_time` IS NOT NULL THEN 'approved'
    WHEN `status` IN ('pending_delivery', 'delivering', 'partial_delivered') AND `cancel_review_time` IS NOT NULL THEN 'rejected'
    ELSE 'none'
  END;

COMMIT;