sqxp/docs/sql/migration_20260408_biz_order_three_status.sql

-- 订单三状态字段（阶段A：兼容期）
-- 新增字段：order_status / delivery_status / pay_status
-- 注意：历史表 biz_order_his 同步新增同名字段

ALTER TABLE `biz_order`
  ADD COLUMN `order_status` varchar(20) NOT NULL DEFAULT 'pending' COMMENT '订单主状态: pending/completed/cancelled' AFTER `status`,
  ADD COLUMN `delivery_status` varchar(20) NOT NULL DEFAULT 'pending_delivery' COMMENT '配送状态: pending_delivery/delivering/delivered/partial_delivered' AFTER `order_status`,
  ADD COLUMN `pay_status` varchar(20) NOT NULL DEFAULT 'pending_payment' COMMENT '支付状态: pending_payment/paid/refunding/refunded' AFTER `delivery_status`;

ALTER TABLE `biz_order_his`
  ADD COLUMN `order_status` varchar(20) NOT NULL DEFAULT 'pending' COMMENT '订单主状态: pending/completed/cancelled' AFTER `status`,
  ADD COLUMN `delivery_status` varchar(20) NOT NULL DEFAULT 'pending_delivery' COMMENT '配送状态: pending_delivery/delivering/delivered/partial_delivered' AFTER `order_status`,
  ADD COLUMN `pay_status` varchar(20) NOT NULL DEFAULT 'pending_payment' COMMENT '支付状态: pending_payment/paid/refunding/refunded' AFTER `delivery_status`;

-- 可选：一次性回填（建议在执行前先确认线上/测试库的实际旧 status 分布）
-- 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` IN ('delivering') THEN 'delivering'
--     WHEN `status` IN ('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(`paid_amount`, 0) > 0 THEN 'paid'
--     ELSE 'pending_payment'
--   END;