-- 目标数据库：baiyue_lastleg；仅增加结构，不修改既有业务数据，可重复执行。
USE baiyue_lastleg;
SET @pickup_column_sql = IF((SELECT COUNT(*) FROM information_schema.columns WHERE table_schema=DATABASE() AND table_name='ll_fleet_trip' AND column_name='trip_purpose')=0,
  'ALTER TABLE ll_fleet_trip ADD COLUMN trip_purpose VARCHAR(16) NOT NULL DEFAULT ''DELIVERY'' COMMENT ''DELIVERY出库派送 PICKUP上门取货入仓''',
  'SELECT 1');
PREPARE pickup_column_stmt FROM @pickup_column_sql;
EXECUTE pickup_column_stmt;
DEALLOCATE PREPARE pickup_column_stmt;

CREATE TABLE IF NOT EXISTS ll_fleet_pickup_plan (
  trip_no VARCHAR(64) NOT NULL COMMENT '现有车辆车次号',
  request_hash VARCHAR(64) NOT NULL COMMENT '创建来源与计划摘要',
  PRIMARY KEY(trip_no)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='仓储转运取货计划';

CREATE TABLE IF NOT EXISTS ll_fleet_pickup_task (
  transfer_order_id BIGINT NOT NULL COMMENT '原仓储转运单ID，一票唯一',
  customer_id VARCHAR(64) NOT NULL COMMENT '客户隔离键',
  transfer_no VARCHAR(64) NOT NULL COMMENT '原仓储转运单号',
  trip_no VARCHAR(64) NULL COMMENT '当前取货车次，解除后为空',
  status VARCHAR(32) NOT NULL COMMENT 'WAITING待安排 ASSIGNED已安排 PICKED已取货 ARRIVED已送仓',
  actual_packages INT NULL COMMENT '司机实提箱数，非仓库实收',
  picked_at DATETIME NULL COMMENT '实际确认取货UTC时间',
  arrived_at DATETIME NULL COMMENT '实际确认送仓UTC时间',
  evidence_json LONGTEXT NULL COMMENT '本票已确认凭证元数据',
  remark VARCHAR(1000) NULL COMMENT '取货备注',
  updated_at DATETIME NOT NULL COMMENT '最近修改UTC时间',
  PRIMARY KEY(transfer_order_id),
  UNIQUE KEY uk_pickup_source(customer_id,transfer_no),
  KEY idx_pickup_trip(trip_no,status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='仓储转运逐票取货执行';

CREATE TABLE IF NOT EXISTS ll_fleet_pickup_command (
  trip_no VARCHAR(64) NOT NULL COMMENT '取货车次号',
  request_key VARCHAR(128) NOT NULL COMMENT '成功命令幂等键',
  request_hash VARCHAR(64) NOT NULL COMMENT '命令动作与内容摘要',
  PRIMARY KEY(trip_no,request_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='取货命令幂等记录';

CREATE TABLE IF NOT EXISTS ll_fleet_pickup_upload (
  transfer_order_id BIGINT NOT NULL COMMENT '凭证所属原单',
  trip_no VARCHAR(64) NOT NULL COMMENT '凭证所属车次',
  file_id BIGINT NOT NULL COMMENT '平台归属文件ID',
  PRIMARY KEY(file_id),
  KEY idx_pickup_upload_source(transfer_order_id,trip_no)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='取货上传凭证精确单据归属';

CREATE TABLE IF NOT EXISTS ll_fleet_pickup_history (
  id BIGINT NOT NULL AUTO_INCREMENT COMMENT '操作流水',
  customer_id VARCHAR(64) NOT NULL COMMENT '客户隔离键',
  transfer_no VARCHAR(64) NOT NULL COMMENT '原转运单号',
  trip_no VARCHAR(64) NOT NULL COMMENT '当时取货车次号',
  action VARCHAR(32) NOT NULL COMMENT '安排解除取货到仓动作',
  operator_id VARCHAR(64) NOT NULL COMMENT '操作人',
  occurred_at DATETIME NOT NULL COMMENT '操作UTC时间',
  detail VARCHAR(2000) NULL COMMENT '操作说明',
  PRIMARY KEY(id),
  KEY idx_pickup_history_source(customer_id,transfer_no,id),
  KEY idx_pickup_history_trip(trip_no,id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='取货不可变操作历史';