sqxp/docs/sql/migration_20260414_biz_order_delivered_time.sql

-- 订单：记录配送完成时间，用于「送达后 N 天自动确认收货」起算
ALTER TABLE biz_order
 ADD COLUMN delivered_time DATETIME NULL COMMENT '配送完成时间(待客户确认收货起算点)' AFTER delivery_assign_time;

ALTER TABLE biz_order_his
  ADD COLUMN delivered_time DATETIME NULL COMMENT '配送完成时间(待客户确认收货起算点)' AFTER delivery_assign_time;

-- 历史数据：待确认且已送达的订单，用已完成配送任务的更新时间近似送达时刻（无任务则回退订单 update_at）
UPDATE biz_order o
SET o.delivered_time = COALESCE(
  (
    SELECT MAX(t.update_at)
    FROM biz_delivery_task t
    WHERE t.order_id = o.id
      AND t.is_del = 0
      AND t.status = 'completed'
  ),
  o.update_at
)
WHERE o.is_del = 0
  AND o.order_status = 'pending_confirm'
  AND o.delivery_status = 'delivered';