mtsd/docs/sql/migration_20260413_biz_delivery_task_dual_status.sql

-- 配送任务双状态改造：
-- 1) 新增配送员状态 deliverer_status
-- 2) 新增供应商备货状态 supplier_prepare_status
-- 3) 按历史 status 回填新字段

ALTER TABLE `biz_delivery_task`
  ADD COLUMN `deliverer_status` varchar(20) NOT NULL DEFAULT 'pending_pickup' COMMENT '配送员状态: pending_pickup/delivering/delivered' AFTER `status`,
  ADD COLUMN `supplier_prepare_status` varchar(20) NOT NULL DEFAULT 'pending_prepare' COMMENT '供应商备货状态: pending_prepare/prepared/picked_up' AFTER `deliverer_status`;

ALTER TABLE `biz_delivery_task_his`
  ADD COLUMN `deliverer_status` varchar(20) NOT NULL DEFAULT 'pending_pickup' COMMENT '配送员状态: pending_pickup/delivering/delivered' AFTER `status`,
  ADD COLUMN `supplier_prepare_status` varchar(20) NOT NULL DEFAULT 'pending_prepare' COMMENT '供应商备货状态: pending_prepare/prepared/picked_up' AFTER `deliverer_status`;

-- 主表回填
UPDATE `biz_delivery_task`
SET
  `deliverer_status` = CASE
    WHEN `status` IN ('delivering') THEN 'delivering'
    WHEN `status` IN ('completed') THEN 'delivered'
    ELSE 'pending_pickup'
  END,
  `supplier_prepare_status` = CASE
    WHEN `status` IN ('supplier_ready', 'picking', 'delivering', 'completed') THEN 'prepared'
    ELSE 'pending_prepare'
  END;

-- 历史表回填
UPDATE `biz_delivery_task_his`
SET
  `deliverer_status` = CASE
    WHEN `status` IN ('delivering') THEN 'delivering'
    WHEN `status` IN ('completed') THEN 'delivered'
    ELSE 'pending_pickup'
  END,
  `supplier_prepare_status` = CASE
    WHEN `status` IN ('supplier_ready', 'picking', 'delivering', 'completed') THEN 'prepared'
    ELSE 'pending_prepare'
  END;