mtsd/docs/sql/migration_20260409_order_payment_offline_online.sql

-- 支付方式统一与订单调整快照扩展

-- 1) 支付方式统一为 online/offline
UPDATE biz_order
SET payment_method = 'online'
WHERE payment_method IN ('wechat', 'wx', 'online') OR payment_method IS NULL OR payment_method = '';

UPDATE biz_order_his
SET payment_method = 'online'
WHERE payment_method IN ('wechat', 'wx', 'online') OR payment_method IS NULL OR payment_method = '';

-- 2) 线下支付订单直接可履约（历史数据回填）
UPDATE biz_order
SET
  pay_status = 'paid',
  paid_amount = COALESCE(total_amount, 0),
  status = 'pending_delivery'
WHERE payment_method = 'offline'
  AND order_status = 'pending'
  AND delivery_status = 'pending_delivery'
  AND (pay_status IS NULL OR pay_status = 'pending_payment');

-- 3) 订单调整记录补充“调整后商品快照”
ALTER TABLE biz_order_adjust
  ADD COLUMN snapshot_items_json TEXT NULL COMMENT '调整后订单商品快照(JSON)';

ALTER TABLE biz_order_adjust_his
  ADD COLUMN snapshot_items_json TEXT NULL COMMENT '调整后订单商品快照(JSON)';