baiyue-firstleg/scripts/db/return-relabel-truck-incremental-20261008.sql

-- 退货换标排车使用正式车次域，补齐旧本地库缺少的车队结构。
-- MySQL 8；显式 baiyue_lastleg；不写业务数据，可重复执行。
-- IAM：退货换标真实排车需要平台管理员的正式司机/车队权限；不授予其他角色。
USE baiyue_iam;
INSERT INTO iam_permission (id, tenant_id, app_code, permission_type, parent_id, permission_code, permission_name, route_path, http_method, sort_no, status, built_in, deleted, created_at, updated_at)
VALUES
 (5107300,0,'PLATFORM','MENU',0,'platform:overseas-master-data:driver:list','司机管理','/overseas-master-data/drivers','GET',930,'ACTIVE',1,0,CURRENT_TIMESTAMP(3),CURRENT_TIMESTAMP(3)),
 (5107301,0,'PLATFORM','BUTTON',5107300,'platform:overseas-master-data:driver:create','新增司机',NULL,'POST',931,'ACTIVE',1,0,CURRENT_TIMESTAMP(3),CURRENT_TIMESTAMP(3)),
 (5107302,0,'PLATFORM','BUTTON',5107300,'platform:overseas-master-data:driver:edit','编辑与重提司机档案',NULL,'PUT',932,'ACTIVE',1,0,CURRENT_TIMESTAMP(3),CURRENT_TIMESTAMP(3)),
 (5107304,0,'PLATFORM','BUTTON',5107300,'platform:overseas-master-data:driver:review','审核司机证照',NULL,'POST',934,'ACTIVE',1,0,CURRENT_TIMESTAMP(3),CURRENT_TIMESTAMP(3)),
 (5107305,0,'PLATFORM','BUTTON',5107300,'platform:overseas-master-data:driver:upload','上传司机证照',NULL,'POST',935,'ACTIVE',1,0,CURRENT_TIMESTAMP(3),CURRENT_TIMESTAMP(3)),
 (5107310,0,'PLATFORM','MENU',0,'platform:fleet:trip:list','车次管理','/overseas-master-data/trips','GET',940,'ACTIVE',1,0,CURRENT_TIMESTAMP(3),CURRENT_TIMESTAMP(3)),
 (5107311,0,'PLATFORM','BUTTON',5107310,'platform:fleet:trip:create','创建车次',NULL,'POST',941,'ACTIVE',1,0,CURRENT_TIMESTAMP(3),CURRENT_TIMESTAMP(3)),
 (5107312,0,'PLATFORM','BUTTON',5107310,'platform:fleet:trip:edit','修改车次计划',NULL,'PUT',942,'ACTIVE',1,0,CURRENT_TIMESTAMP(3),CURRENT_TIMESTAMP(3)),
 (5107313,0,'PLATFORM','BUTTON',5107310,'platform:fleet:trip:assign','分配车辆和司机',NULL,'POST',943,'ACTIVE',1,0,CURRENT_TIMESTAMP(3),CURRENT_TIMESTAMP(3)),
 (5107317,0,'PLATFORM','BUTTON',5107310,'platform:fleet:trip:dispatch-review','审核车次客户排车',NULL,'POST',947,'ACTIVE',1,0,CURRENT_TIMESTAMP(3),CURRENT_TIMESTAMP(3)),
 (5107318,0,'PLATFORM','BUTTON',5107310,'platform:fleet:trip:loading-review','审核整车理货',NULL,'POST',948,'ACTIVE',1,0,CURRENT_TIMESTAMP(3),CURRENT_TIMESTAMP(3))
ON DUPLICATE KEY UPDATE permission_type=VALUES(permission_type), parent_id=VALUES(parent_id), permission_name=VALUES(permission_name), route_path=VALUES(route_path), http_method=VALUES(http_method), sort_no=VALUES(sort_no), status='ACTIVE', built_in=1, deleted=0;
INSERT INTO iam_role_permission (tenant_id, role_id, permission_id, created_at)
SELECT 0, role.id, permission.id, CURRENT_TIMESTAMP(3)
FROM iam_role role
JOIN iam_permission permission ON permission.tenant_id=0 AND permission.app_code='PLATFORM'
 AND permission.permission_code IN (
  'platform:overseas-master-data:driver:list',
  'platform:overseas-master-data:driver:create',
  'platform:overseas-master-data:driver:edit',
  'platform:overseas-master-data:driver:upload',
  'platform:overseas-master-data:driver:review',
  'platform:fleet:trip:list',
  'platform:fleet:trip:create',
  'platform:fleet:trip:assign',
  'platform:fleet:trip:edit',
  'platform:fleet:trip:dispatch-review',
  'platform:fleet:trip:loading-review')
WHERE role.tenant_id=0 AND role.app_code='PLATFORM' AND role.role_code='PLATFORM_ADMIN'
  AND role.status='ACTIVE' AND role.deleted=0 AND permission.status='ACTIVE' AND permission.deleted=0
  AND NOT EXISTS(SELECT 1 FROM iam_role_permission existing WHERE existing.role_id=role.id AND existing.permission_id=permission.id);
USE baiyue_lastleg;
USE baiyue_lastleg;
SET @return_fleet_ddl = IF((SELECT COUNT(*) FROM information_schema.columns WHERE table_schema=DATABASE() AND table_name='ll_vehicle' AND column_name='max_pallet_spaces')=0, 'ALTER TABLE ll_vehicle ADD COLUMN max_pallet_spaces INT NULL COMMENT ''最大标准托位，未知时为空''', 'SELECT 1');
PREPARE return_fleet_stmt FROM @return_fleet_ddl;
EXECUTE return_fleet_stmt;
DEALLOCATE PREPARE return_fleet_stmt;
SET @return_fleet_ddl = IF((SELECT COUNT(*) FROM information_schema.columns WHERE table_schema=DATABASE() AND table_name='ll_vehicle' AND column_name='max_load_kg')=0, 'ALTER TABLE ll_vehicle ADD COLUMN max_load_kg DECIMAL(12,3) NULL COMMENT ''最大载重，千克''', 'SELECT 1');
PREPARE return_fleet_stmt FROM @return_fleet_ddl;
EXECUTE return_fleet_stmt;
DEALLOCATE PREPARE return_fleet_stmt;
SET @return_fleet_ddl = IF((SELECT COUNT(*) FROM information_schema.columns WHERE table_schema=DATABASE() AND table_name='ll_vehicle' AND column_name='max_volume_cbm')=0, 'ALTER TABLE ll_vehicle ADD COLUMN max_volume_cbm DECIMAL(12,3) NULL COMMENT ''可用容积，立方米''', 'SELECT 1');
PREPARE return_fleet_stmt FROM @return_fleet_ddl;
EXECUTE return_fleet_stmt;
DEALLOCATE PREPARE return_fleet_stmt;
CREATE TABLE IF NOT EXISTS ll_fleet_driver (
  id BIGINT NOT NULL COMMENT '内部主键',
  driver_no VARCHAR(32) NOT NULL COMMENT '司机业务编号',
  warehouse_id BIGINT NULL COMMENT '所属公共仓库引用',
  warehouse_code VARCHAR(64) NULL COMMENT '所属仓库编码快照',
  warehouse_name VARCHAR(256) NULL COMMENT '所属仓库名称快照',
  vehicle_models VARCHAR(128) NULL COMMENT '可驾驶车型描述',
  languages_and_area VARCHAR(256) NULL COMMENT '语言及服务区域',
  medical_expires_on DATE NULL COMMENT '体检到期日，当前不作为必需资质',
  training_expires_on DATE NULL COMMENT '培训安全到期日，当前不作为必需资质',
  insurance_expires_on DATE NULL COMMENT '保险到期日，当前不作为必需资质',
  name VARCHAR(128) NOT NULL COMMENT '姓名',
  phone VARCHAR(64) NOT NULL COMMENT '联系电话',
  ownership VARCHAR(16) NOT NULL COMMENT '归属 OWNED自有 DISPATCHED外派',
  region VARCHAR(16) NOT NULL COMMENT '区域 DOMESTIC国内 OVERSEAS海外',
  active TINYINT(1) NOT NULL DEFAULT 1 COMMENT '是否启用',
  review_status VARCHAR(16) NOT NULL DEFAULT 'PENDING' COMMENT '证照审核 PENDING待审 APPROVED通过 REJECTED打回',
  license_number VARCHAR(128) NULL COMMENT '驾驶证编号',
  license_type VARCHAR(64) NULL COMMENT '驾驶证类型',
  license_issued_on DATE NULL COMMENT '驾驶证签发日期',
  license_expires_on DATE NULL COMMENT '驾驶证有效截止日',
  license_zone VARCHAR(64) NOT NULL COMMENT '证照有效日期的IANA时区',
  license_file_id BIGINT NULL COMMENT '平台驾驶证文件引用',
  remark VARCHAR(1000) NULL COMMENT '备注',
  record_version BIGINT NOT NULL DEFAULT 0 COMMENT '并发版本',
  created_by VARCHAR(64) NOT NULL COMMENT '创建人',
  created_at DATETIME(3) NOT NULL COMMENT '创建时间UTC',
  updated_by VARCHAR(64) NOT NULL COMMENT '修改人',
  updated_at DATETIME(3) NOT NULL COMMENT '修改时间UTC',
  PRIMARY KEY (id),
  UNIQUE KEY uk_fleet_driver_no (driver_no),
  KEY idx_fleet_driver_page (active,review_status,updated_at,id),
  KEY idx_fleet_driver_expiry (license_expires_on),
  CONSTRAINT chk_fleet_driver_ownership CHECK (ownership IN ('OWNED','DISPATCHED')),
  CONSTRAINT chk_fleet_driver_region CHECK (region IN ('DOMESTIC','OVERSEAS')),
  CONSTRAINT chk_fleet_driver_review CHECK (review_status IN ('PENDING','APPROVED','REJECTED')),
  CONSTRAINT chk_fleet_driver_dates CHECK (license_issued_on IS NULL OR license_expires_on IS NULL OR license_issued_on <= license_expires_on)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='平台司机档案';

CREATE TABLE IF NOT EXISTS ll_fleet_driver_history (
  id BIGINT NOT NULL COMMENT '内部主键',
  driver_id BIGINT NOT NULL COMMENT '司机内部引用',
  record_version BIGINT NOT NULL COMMENT '操作后的档案版本',
  action VARCHAR(32) NOT NULL COMMENT '创建编辑启停证照上传及审核动作',
  reason VARCHAR(1000) NULL COMMENT '操作原因或审核意见',
  license_file_id BIGINT NULL COMMENT '当时驾驶证文件引用',
  snapshot_json JSON NOT NULL COMMENT '操作后不可变档案快照',
  operator_id VARCHAR(64) NOT NULL COMMENT '操作人',
  occurred_at DATETIME(3) NOT NULL COMMENT '操作时间UTC',
  PRIMARY KEY (id),
  UNIQUE KEY uk_fleet_driver_history_version (driver_id,record_version),
  KEY idx_fleet_driver_history_file (driver_id,license_file_id),
  CONSTRAINT fk_fleet_driver_history_driver FOREIGN KEY (driver_id) REFERENCES ll_fleet_driver(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='司机档案及审核历史';

CREATE TABLE IF NOT EXISTS ll_fleet_vehicle_record (
  id BIGINT NOT NULL COMMENT '记录内部主键',
  record_no VARCHAR(32) NOT NULL COMMENT '车辆运维业务编号',
  vehicle_id BIGINT NOT NULL COMMENT '所属车辆主键',
  vehicle_plate VARCHAR(64) NOT NULL COMMENT '建档时车牌快照',
  record_type VARCHAR(32) NOT NULL COMMENT '运维类型：保养、加油、维修、故障、救援、异常',
  occurred_at DATETIME(3) NOT NULL COMMENT '记录发生时间（UTC）',
  details_json JSON NOT NULL COMMENT '带明确单位的业务字段快照',
  files_json JSON NOT NULL COMMENT '所属记录照片与上传幂等摘要',
  idempotency_key VARCHAR(128) NOT NULL COMMENT '创建请求幂等键',
  request_hash CHAR(64) NOT NULL COMMENT '原创建业务内容摘要',
  record_version BIGINT NOT NULL DEFAULT 0 COMMENT '乐观锁版本',
  created_by VARCHAR(64) NOT NULL COMMENT '创建操作人',
  updated_by VARCHAR(64) NOT NULL COMMENT '最后操作人',
  created_at DATETIME(3) NOT NULL COMMENT '创建时间（UTC）',
  updated_at DATETIME(3) NOT NULL COMMENT '更新时间（UTC）',
  PRIMARY KEY (id),
  UNIQUE KEY uk_vehicle_record_no (record_no),
  UNIQUE KEY uk_vehicle_record_creation (vehicle_id, created_by, idempotency_key),
  KEY idx_vehicle_record_page (vehicle_id, record_type, occurred_at, id),
  CONSTRAINT fk_vehicle_record_vehicle FOREIGN KEY (vehicle_id) REFERENCES ll_vehicle(id),
  CONSTRAINT ck_vehicle_record_type CHECK (record_type IN ('MAINTENANCE','FUEL','REPAIR','BREAKDOWN','RESCUE','EXCEPTION'))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='车辆运维记录';

CREATE TABLE IF NOT EXISTS ll_fleet_vehicle_record_history (
  id BIGINT NOT NULL COMMENT '历史内部主键',
  record_id BIGINT NOT NULL COMMENT '运维记录主键',
  record_version BIGINT NOT NULL COMMENT '对应业务版本',
  action VARCHAR(32) NOT NULL COMMENT '操作类型',
  reason VARCHAR(1000) DEFAULT NULL COMMENT '更正原因或附件名称',
  operator_id VARCHAR(64) NOT NULL COMMENT '操作人',
  occurred_at DATETIME(3) NOT NULL COMMENT '操作时间（UTC）',
  snapshot_json JSON NOT NULL COMMENT '当时完整业务快照',
  PRIMARY KEY (id),
  UNIQUE KEY uk_vehicle_record_revision (record_id, record_version),
  CONSTRAINT fk_vehicle_record_history FOREIGN KEY (record_id) REFERENCES ll_fleet_vehicle_record(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='车辆运维记录不可变历史';

CREATE TABLE IF NOT EXISTS ll_fleet_trip (
  id BIGINT NOT NULL COMMENT '内部主键',
  trip_no VARCHAR(32) NOT NULL COMMENT '车次业务编号',
  idempotency_key VARCHAR(128) NOT NULL COMMENT '创建请求幂等键',
  request_hash VARCHAR(64) NOT NULL COMMENT '创建请求SHA256',
  status VARCHAR(16) NOT NULL COMMENT '车次状态',
  warehouse_id BIGINT NOT NULL COMMENT '起运公共仓库引用',
  warehouse_code VARCHAR(64) NOT NULL COMMENT '起运仓编码快照',
  warehouse_name VARCHAR(256) NOT NULL COMMENT '起运仓名称快照',
  vehicle_id BIGINT NULL COMMENT '车辆档案引用',
  vehicle_plate VARCHAR(64) NULL COMMENT '分配时车牌快照',
  driver_id BIGINT NULL COMMENT '司机档案引用',
  driver_name VARCHAR(128) NULL COMMENT '分配时司机姓名',
  driver_phone VARCHAR(64) NULL COMMENT '分配时司机电话',
  max_pallet_spaces INT NULL COMMENT '分配时托位容量快照',
  max_load_kg DECIMAL(12,3) NULL COMMENT '分配时载重千克',
  max_volume_cbm DECIMAL(12,3) NULL COMMENT '分配时可用容积立方米',
  planned_departure_at DATETIME(3) NOT NULL COMMENT '计划发车UTC',
  expected_driver_end_at DATETIME(3) NOT NULL COMMENT '司机预计任务结束UTC，含承担的返程转场',
  departed_at DATETIME(3) NULL COMMENT '实际发车UTC',
  completed_at DATETIME(3) NULL COMMENT '实际运输完成UTC',
  stops_json JSON NOT NULL COMMENT '按顺序保存的路线站点',
  planned_load_json JSON NULL COMMENT '拣货前自有货计划载荷与逐段剩余容量',
  load_json JSON NULL COMMENT '已确认实物装载及客户货物来源快照',
  loading_review_json LONGTEXT NULL COMMENT '整车理货审核快照，空值为待审核',
  departure_key VARCHAR(128) NULL COMMENT '整车发车幂等标识',
  departure_hash CHAR(64) NULL COMMENT '整车发车请求摘要',
  departure_result_json JSON NULL COMMENT '整车发车结果及各客户出库结果',
  completion_json JSON NULL COMMENT '货物结案来源及幂等结果快照',
  vehicle_release_json JSON NULL COMMENT '车辆任务结束地点状态与幂等记录',
  driver_release_json JSON NULL COMMENT '司机任务结束地点与幂等记录',
  remark VARCHAR(1000) NULL COMMENT '备注',
  record_version BIGINT NOT NULL DEFAULT 0 COMMENT '并发版本',
  created_by VARCHAR(64) NOT NULL COMMENT '创建人',
  created_at DATETIME(3) NOT NULL COMMENT '创建UTC时间',
  updated_by VARCHAR(64) NOT NULL COMMENT '更新人',
  updated_at DATETIME(3) NOT NULL COMMENT '更新UTC时间',
  PRIMARY KEY (id),
  UNIQUE KEY uk_fleet_trip_no (trip_no),
  UNIQUE KEY uk_fleet_trip_create (created_by,idempotency_key),
  KEY idx_fleet_trip_page (warehouse_id,status,updated_at,id),
  CONSTRAINT fk_fleet_trip_vehicle FOREIGN KEY (vehicle_id) REFERENCES ll_vehicle(id),
  CONSTRAINT fk_fleet_trip_driver FOREIGN KEY (driver_id) REFERENCES ll_fleet_driver(id),
  CONSTRAINT chk_fleet_trip_status CHECK (status IN ('PLANNED','DEPARTING','IN_TRANSIT','COMPLETED','CANCELLED')),
  CONSTRAINT chk_fleet_trip_time CHECK (expected_driver_end_at > planned_departure_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='平台运输车次';

CREATE TABLE IF NOT EXISTS ll_fleet_capacity_offer (
  id BIGINT NOT NULL COMMENT '内部主键',
  offer_no VARCHAR(64) NOT NULL COMMENT '拼车运力发布业务编号',
  trip_id BIGINT NOT NULL COMMENT '唯一所属车次',
  trip_no VARCHAR(64) NOT NULL COMMENT '车次业务编号',
  status VARCHAR(16) NOT NULL COMMENT '发布状态：草稿、开放、暂停、关闭',
  cutoff_at DATETIME(3) NOT NULL COMMENT '最晚交货及新询价截止UTC时间',
  contact_name VARCHAR(128) NULL COMMENT '对内部销售公开的调度联系人',
  contact_phone VARCHAR(128) NULL COMMENT '对内部销售公开的调度联系电话',
  remark VARCHAR(1000) NULL COMMENT '公开承运说明',
  published_fingerprint CHAR(64) NULL COMMENT '已核对路线车辆及容量计划事实摘要',
  record_version BIGINT NOT NULL DEFAULT 0 COMMENT '乐观锁版本',
  created_by VARCHAR(64) NOT NULL COMMENT '创建人',
  created_at DATETIME(3) NOT NULL COMMENT '创建UTC时间',
  updated_by VARCHAR(64) NOT NULL COMMENT '修改人',
  updated_at DATETIME(3) NOT NULL COMMENT '修改UTC时间',
  PRIMARY KEY(id),
  UNIQUE KEY uk_fleet_capacity_offer_no(offer_no),
  UNIQUE KEY uk_fleet_capacity_offer_trip(trip_id),
  KEY idx_fleet_capacity_offer_status(status,cutoff_at,id),
  CONSTRAINT fk_fleet_capacity_offer_trip FOREIGN KEY(trip_id) REFERENCES ll_fleet_trip(id),
  CONSTRAINT chk_fleet_capacity_offer_status CHECK(status IN ('DRAFT','OPEN','PAUSED','CLOSED'))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='内部拼车车次余位发布；容量实时计算不在此另存';

CREATE TABLE IF NOT EXISTS ll_fleet_capacity_offer_command (
  id BIGINT NOT NULL COMMENT '内部主键',
  trip_id BIGINT NOT NULL COMMENT '所属车次锁作用域',
  operator_id VARCHAR(64) NOT NULL COMMENT '原请求操作者',
  action VARCHAR(32) NOT NULL COMMENT '业务命令类型',
  scope_no VARCHAR(64) NOT NULL COMMENT '发布或预订业务编号作用域',
  idempotency_key VARCHAR(128) NOT NULL COMMENT '原请求幂等键',
  request_hash CHAR(64) NOT NULL COMMENT '原请求业务内容摘要',
  result_json JSON NOT NULL COMMENT '成功命令原始结果用于精确重放',
  created_at DATETIME(3) NOT NULL COMMENT '命令完成UTC时间',
  PRIMARY KEY(id),
  UNIQUE KEY uk_fleet_capacity_offer_command(trip_id,operator_id,action,scope_no,idempotency_key),
  CONSTRAINT fk_fleet_capacity_command_trip FOREIGN KEY(trip_id) REFERENCES ll_fleet_trip(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='拼车发布及销售意向持久幂等结果';

CREATE TABLE IF NOT EXISTS ll_fleet_external_booking (
  id BIGINT NOT NULL COMMENT '内部主键',
  booking_no VARCHAR(32) NOT NULL COMMENT '外接预订业务编号',
  trip_id BIGINT NOT NULL COMMENT '所属车次',
  trip_no VARCHAR(32) NOT NULL COMMENT '车次业务编号',
  customer_id VARCHAR(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '委托客户及付款归属',
  customer_code VARCHAR(64) NOT NULL COMMENT '客户编号快照',
  customer_name VARCHAR(255) NOT NULL COMMENT '客户名称快照',
  status VARCHAR(16) NOT NULL COMMENT 'DRAFT询价/CONFIRMED占位/RECEIVED确认实收/BOUND关联发运/CANCELLED取消',
  cargo_json JSON NOT NULL COMMENT '货物、计划载荷、联系人及约定报价快照',
  quote_source_json JSON NULL COMMENT '选用销售报价的可信版本快照，与实际成交金额分开保存',
  destination_key VARCHAR(160) NOT NULL COMMENT '已有路线卸货站匹配键',
  destination_label VARCHAR(255) NOT NULL COMMENT '约定卸货站名称',
  agreed_amount DECIMAL(16,2) NULL COMMENT '约定金额，非已入账应收或实收',
  idempotency_key VARCHAR(128) NOT NULL COMMENT '登记请求键',
  request_hash CHAR(64) NOT NULL COMMENT '登记内容摘要',
  confirm_key VARCHAR(128) NULL COMMENT '承运确认请求键',
  confirm_hash CHAR(64) NULL COMMENT '承运确认摘要',
  cancel_key VARCHAR(128) NULL COMMENT '取消请求键',
  cancel_hash CHAR(64) NULL COMMENT '取消摘要',
  incoming_no VARCHAR(64) NULL COMMENT '正式来货单业务编号',
  dispatch_no VARCHAR(64) NULL COMMENT '已关联客户排车业务编号',
  receipt_json JSON NULL COMMENT '实测来源及运营确认的托位和运费依据',
  incoming_key VARCHAR(128) NULL COMMENT '来货登记请求键',
  incoming_hash CHAR(64) NULL COMMENT '来货登记摘要',
  receipt_key VARCHAR(128) NULL COMMENT '实收确认请求键',
  receipt_hash CHAR(64) NULL COMMENT '实收确认摘要',
  binding_key VARCHAR(128) NULL COMMENT '关联发运请求键',
  binding_hash CHAR(64) NULL COMMENT '关联发运摘要',
  offer_id BIGINT NULL COMMENT '来源运力发布，普通车次预订为空',
  sales_owner_user_id VARCHAR(64) NULL COMMENT '登录身份冻结的销售负责人',
  business_type VARCHAR(32) NULL COMMENT '本土商业类型LOCAL_LOOSEBOARD或US_TRUCK',
  delivery_mode VARCHAR(32) NULL COMMENT '客户送仓或人工安排提货回仓',
  estimated_arrival_at DATETIME(3) NULL COMMENT '预计实际到我仓UTC时间',
  expected_pallet_count INT NULL COMMENT '预计实托数，与托位分开且未知为空',
  record_version BIGINT NOT NULL DEFAULT 0 COMMENT '记录版本',
  created_by VARCHAR(64) NOT NULL COMMENT '登记人',
  created_at DATETIME(3) NOT NULL COMMENT '登记UTC时间',
  updated_by VARCHAR(64) NOT NULL COMMENT '修改人',
  updated_at DATETIME(3) NOT NULL COMMENT '修改UTC时间',
  KEY idx_fleet_booking_offer(offer_id,status,id),
  KEY idx_fleet_booking_sales(sales_owner_user_id,offer_id,id),
  CONSTRAINT fk_fleet_booking_offer FOREIGN KEY(offer_id) REFERENCES ll_fleet_capacity_offer(id),
  CONSTRAINT chk_fleet_booking_expected_pallet CHECK(expected_pallet_count IS NULL OR expected_pallet_count > 0),
  PRIMARY KEY(id),
  UNIQUE KEY uk_fleet_booking_no(booking_no),
  UNIQUE KEY uk_fleet_booking_create(trip_id,created_by,idempotency_key),
  KEY idx_fleet_booking_trip(trip_id,status,id),
  UNIQUE KEY uk_fleet_booking_incoming(incoming_no),
  KEY idx_fleet_booking_dispatch(dispatch_no),
  CONSTRAINT fk_fleet_booking_trip FOREIGN KEY(trip_id) REFERENCES ll_fleet_trip(id),
  CONSTRAINT chk_fleet_booking_status CHECK(status IN ('DRAFT','CONFIRMED','RECEIVED','BOUND','CANCELLED'))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='外接询价及确认承运占位；货物收发和费用入账分别引用正式事实';

CREATE TABLE IF NOT EXISTS ll_fleet_pool_order_link (
  id BIGINT NOT NULL COMMENT '内部主键',
  booking_id BIGINT NOT NULL COMMENT '唯一外接预订',
  booking_no VARCHAR(64) NOT NULL COMMENT '预订业务编号',
  customer_id VARCHAR(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '商业客户隔离范围',
  business_type VARCHAR(32) NOT NULL COMMENT '本土散板或本土卡车商业类型',
  business_order_id BIGINT NOT NULL COMMENT '对应商业订单内部关联',
  business_order_no VARCHAR(64) NOT NULL COMMENT '本土商业订单业务编号',
  warehouse_id VARCHAR(128) NOT NULL COMMENT '真实我仓上车仓库',
  destination_key VARCHAR(160) NOT NULL COMMENT '冻结卸货站匹配键',
  delivery_mode VARCHAR(32) NOT NULL COMMENT '客户送仓或人工提货回仓',
  delivery_instructions VARCHAR(1000) NULL COMMENT '实际到仓交货说明',
  incoming_no VARCHAR(64) NULL COMMENT '真实来货业务编号，未登记为空',
  lastleg_order_id BIGINT NULL COMMENT '唯一真实尾程来源，未发生为空',
  dispatch_no VARCHAR(64) NULL COMMENT '已关联客户排车编号',
  fulfillment_stage VARCHAR(32) NOT NULL COMMENT '实际履约阶段，不能手工冒充完成',
  stage_occurred_at DATETIME(3) NOT NULL COMMENT '实际阶段发生UTC时间',
  actual_pallet_count INT NULL COMMENT '实际去重托盘数，未知为空',
  actual_package_count INT NULL COMMENT '实际箱数，未知为空',
  actual_pallet_spaces INT NULL COMMENT '实际核准占用托位，未知为空',
  record_version BIGINT NOT NULL DEFAULT 0 COMMENT '桥记录版本',
  creation_request_hash CHAR(64) NOT NULL COMMENT '商业单创建身份和预报事实摘要',
  created_by VARCHAR(64) NOT NULL COMMENT '确认承运操作者',
  created_at DATETIME(3) NOT NULL COMMENT '创建UTC时间',
  updated_at DATETIME(3) NOT NULL COMMENT '修改UTC时间',
  PRIMARY KEY(id),
  UNIQUE KEY uk_fleet_pool_order_booking(booking_id),
  UNIQUE KEY uk_fleet_pool_order_booking_no(booking_no),
  UNIQUE KEY uk_fleet_pool_order_business(business_type,business_order_id),
  UNIQUE KEY uk_fleet_pool_order_incoming(incoming_no),
  UNIQUE KEY uk_fleet_pool_order_lastleg(lastleg_order_id),
  KEY idx_fleet_pool_order_customer(customer_id,business_type,business_order_no),
  KEY idx_fleet_pool_order_dispatch(customer_id,dispatch_no),
  CONSTRAINT fk_fleet_pool_order_booking FOREIGN KEY(booking_id) REFERENCES ll_fleet_external_booking(id),
  CONSTRAINT chk_fleet_pool_order_type CHECK(business_type IN ('LOCAL_LOOSEBOARD','US_TRUCK')),
  CONSTRAINT chk_fleet_pool_order_actual CHECK((actual_pallet_count IS NULL OR actual_pallet_count >= 0) AND (actual_package_count IS NULL OR actual_package_count >= 0) AND (actual_pallet_spaces IS NULL OR actual_pallet_spaces >= 0))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='拼车预订与本土商业单唯一真实履约关联，不重复记运费';

CREATE TABLE IF NOT EXISTS ll_fleet_resource_assignment (
  resource_kind VARCHAR(16) NOT NULL COMMENT '资源种类 VEHICLE或DRIVER',
  resource_id BIGINT NOT NULL COMMENT '对应车辆或司机档案引用',
  trip_id BIGINT NOT NULL COMMENT '占用车次',
  trip_no VARCHAR(32) NOT NULL COMMENT '占用车次业务编号',
  PRIMARY KEY (resource_kind,resource_id),
  KEY idx_fleet_resource_trip (trip_id),
  CONSTRAINT fk_fleet_resource_trip FOREIGN KEY (trip_id) REFERENCES ll_fleet_trip(id),
  CONSTRAINT chk_fleet_resource_kind CHECK (resource_kind IN ('VEHICLE','DRIVER'))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='车辆及司机当前车次占用，释放后删除锁行，历史保留在车次';

CREATE TABLE IF NOT EXISTS ll_fleet_trip_dispatch (
  dispatch_id BIGINT NOT NULL COMMENT '客户排车单内部引用，唯一有效车次关联',
  dispatch_no VARCHAR(64) NOT NULL COMMENT '客户排车业务单号',
  customer_id VARCHAR(128) NOT NULL COMMENT '排车单自身客户归属',
  trip_id BIGINT NOT NULL COMMENT '共享运输车次内部引用',
  trip_no VARCHAR(32) NOT NULL COMMENT '共享运输车次业务编号',
  package_count INT NOT NULL COMMENT '关联时待发运箱数快照',
  bound_by VARCHAR(64) NOT NULL COMMENT '关联操作人',
  bound_at DATETIME(3) NOT NULL COMMENT '关联UTC时间',
  review_json LONGTEXT NULL COMMENT '客户排车调度审核快照，空值为待审核',
  PRIMARY KEY (dispatch_id),
  UNIQUE KEY uk_fleet_trip_dispatch_no (dispatch_no),
  KEY idx_fleet_trip_dispatch_trip (trip_id,dispatch_id),
  CONSTRAINT fk_fleet_trip_dispatch_trip FOREIGN KEY (trip_id) REFERENCES ll_fleet_trip(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='车次与各客户排车单的有效关联，解除前快照保留在车次历史';

CREATE TABLE IF NOT EXISTS ll_fleet_trip_history (
  id BIGINT NOT NULL COMMENT '内部主键',
  trip_id BIGINT NOT NULL COMMENT '车次内部引用',
  record_version BIGINT NOT NULL COMMENT '操作后版本',
  action VARCHAR(32) NOT NULL COMMENT '车次操作',
  reason VARCHAR(1000) NULL COMMENT '操作原因',
  operator_id VARCHAR(64) NOT NULL COMMENT '操作人',
  occurred_at DATETIME(3) NOT NULL COMMENT '操作UTC时间',
  snapshot_json JSON NOT NULL COMMENT '操作后不可变车次快照',
  PRIMARY KEY (id),
  UNIQUE KEY uk_fleet_trip_history_version (trip_id,record_version),
  CONSTRAINT fk_fleet_trip_history FOREIGN KEY (trip_id) REFERENCES ll_fleet_trip(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='运输车次操作历史';

CREATE TABLE IF NOT EXISTS ll_fleet_trip_operations_record (
  id BIGINT NOT NULL COMMENT '出车实测历史主键',
  trip_no VARCHAR(64) NOT NULL COMMENT '车次业务编号',
  record_version BIGINT NOT NULL COMMENT '实测记录版本从一递增',
  details_json JSON NOT NULL COMMENT '实际里程英里油耗升与备注快照',
  reason VARCHAR(1000) NOT NULL COMMENT '实测更正说明',
  operator_id VARCHAR(64) NOT NULL COMMENT '实测操作人标识',
  request_key VARCHAR(128) NOT NULL COMMENT '实测保存幂等键',
  request_hash CHAR(64) NOT NULL COMMENT '实测请求内容摘要',
  occurred_at DATETIME(3) NOT NULL COMMENT '实测保存时间UTC',
  PRIMARY KEY (id),
  UNIQUE KEY uk_fleet_operations_version (trip_no,record_version),
  UNIQUE KEY uk_fleet_operations_request (trip_no,operator_id,request_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='车次里程油耗不可变更正记录';

CREATE TABLE IF NOT EXISTS ll_fleet_trip_handover (
  id BIGINT NOT NULL COMMENT '内部主键',
  trip_id BIGINT NOT NULL COMMENT '所属车次',
  trip_version BIGINT NOT NULL COMMENT '交接对应车次历史版本',
  trip_no VARCHAR(32) NOT NULL COMMENT '车次业务编号',
  handover_no VARCHAR(32) NOT NULL COMMENT '交接业务编号',
  request_key VARCHAR(128) NOT NULL COMMENT '交接请求幂等键',
  request_hash CHAR(64) NOT NULL COMMENT '交接请求摘要',
  record_version BIGINT NOT NULL DEFAULT 0 COMMENT '资源结束并发版本',
  handed_over_at DATETIME(3) NOT NULL COMMENT '实际交接UTC时间',
  snapshot_json JSON NOT NULL COMMENT '不可变资源及剩余货物交接快照',
  vehicle_release_json JSON NULL COMMENT '原车辆任务结束及幂等结果',
  driver_release_json JSON NULL COMMENT '原司机任务结束及幂等结果',
  created_by VARCHAR(64) NOT NULL COMMENT '记录操作人',
  created_at DATETIME(3) NOT NULL COMMENT '记录UTC时间',
  PRIMARY KEY (id),
  UNIQUE KEY uk_fleet_handover_no (handover_no),
  UNIQUE KEY uk_fleet_handover_request (trip_id, request_key),
  UNIQUE KEY uk_fleet_handover_version (trip_id, trip_version),
  KEY idx_fleet_handover_page (trip_id, id),
  CONSTRAINT fk_fleet_handover_trip FOREIGN KEY (trip_id) REFERENCES ll_fleet_trip(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='在途车辆司机交接';

CREATE TABLE IF NOT EXISTS ll_fleet_finance_source (
  id BIGINT UNSIGNED NOT NULL COMMENT '来源提交主键',
  record_id BIGINT UNSIGNED NOT NULL COMMENT '车辆运维记录主键',
  source_version BIGINT UNSIGNED NOT NULL COMMENT '独立财务来源版本，从1递增',
  record_version BIGINT UNSIGNED NOT NULL COMMENT '提交时运维记录版本',
  operator_id VARCHAR(64) NOT NULL COMMENT '提交操作人标识',
  request_key VARCHAR(128) NOT NULL COMMENT '原请求幂等键',
  request_hash VARCHAR(71) NOT NULL COMMENT '规范请求摘要，含sha256前缀',
  event_id VARCHAR(128) NOT NULL COMMENT '关联公共Outbox事件ID',
  payload_json JSON NOT NULL COMMENT '不可变公司成本来源快照',
  created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) COMMENT '提交时间UTC',
  PRIMARY KEY (id),
  UNIQUE KEY uk_fleet_finance_source_version (record_id,source_version),
  UNIQUE KEY uk_fleet_finance_source_request (record_id,operator_id,request_key),
  UNIQUE KEY uk_fleet_finance_source_event (event_id),
  CONSTRAINT chk_fleet_finance_source_version CHECK (source_version>0)
) ENGINE=InnoDB COMMENT='维修救援公司成本提交来源，不保存付款推定';

CREATE TABLE IF NOT EXISTS ll_fleet_quote (
  id BIGINT PRIMARY KEY COMMENT '报价记录主键',
  quote_no VARCHAR(64) NOT NULL COMMENT '报价业务编号',
  kind VARCHAR(16) NOT NULL COMMENT '报价用途采购或销售',
  party_id BIGINT NOT NULL COMMENT '供应商或客户主档标识',
  party_name VARCHAR(160) NOT NULL COMMENT '往来单位名称快照',
  origin_city VARCHAR(128) NOT NULL COMMENT '发货城市',
  destination_code VARCHAR(64) NOT NULL COMMENT '目的仓业务编码',
  valid_from DATE NOT NULL COMMENT '报价生效日期',
  valid_to DATE NOT NULL COMMENT '报价失效日期含当天',
  status VARCHAR(16) NOT NULL COMMENT '报价启停或删除状态',
  natural_key CHAR(64) NOT NULL COMMENT '报价组合去重摘要',
  details_json JSON NOT NULL COMMENT '完整报价明细快照',
  record_version BIGINT NOT NULL COMMENT '报价记录版本',
  create_key VARCHAR(128) NOT NULL COMMENT '创建幂等键',
  create_hash CHAR(64) NOT NULL COMMENT '创建请求含导入批次内容摘要',
  created_by VARCHAR(64) NOT NULL COMMENT '创建操作人标识',
  created_at DATETIME(3) NOT NULL COMMENT '创建时间UTC',
  updated_at DATETIME(3) NOT NULL COMMENT '更新时间UTC',
  UNIQUE KEY uk_fleet_quote_no (quote_no),
  UNIQUE KEY uk_fleet_quote_natural (natural_key),
  UNIQUE KEY uk_fleet_quote_create (created_by,create_key),
  KEY idx_fleet_quote_search (kind,status,valid_from,valid_to)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='车辆采购与外接销售报价';

CREATE TABLE IF NOT EXISTS ll_fleet_quote_history (
  id BIGINT PRIMARY KEY COMMENT '报价历史主键',
  quote_id BIGINT NOT NULL COMMENT '报价记录标识',
  record_version BIGINT NOT NULL COMMENT '报价记录版本',
  action VARCHAR(24) NOT NULL COMMENT '报价操作类型',
  operator_id VARCHAR(64) NOT NULL COMMENT '操作人标识',
  occurred_at DATETIME(3) NOT NULL COMMENT '操作时间UTC',
  snapshot_json JSON NOT NULL COMMENT '报价完整版本快照',
  UNIQUE KEY uk_fleet_quote_history_version (quote_id,record_version)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='车辆报价不可变版本历史';

CREATE TABLE IF NOT EXISTS ll_fleet_trip_cost (
  id BIGINT NOT NULL COMMENT '内部主键',
  trip_id BIGINT NOT NULL COMMENT '车次内部引用',
  cost_no VARCHAR(32) NOT NULL COMMENT '车次成本业务编号',
  category VARCHAR(16) NOT NULL COMMENT '直接成本类别FUEL/DRIVER/TOLL/PARKING/OTHER',
  currency VARCHAR(3) NOT NULL COMMENT '原始成本币种CNY/USD，不混币汇总',
  amount DECIMAL(14,2) NOT NULL COMMENT '运营登记金额，不代表付款',
  incurred_at DATETIME(3) NOT NULL COMMENT '实际费用发生UTC时间',
  remark VARCHAR(1000) NULL COMMENT '费用备注',
  record_version BIGINT NOT NULL COMMENT '成本记录版本',
  request_key VARCHAR(128) NOT NULL COMMENT '创建幂等键',
  request_hash CHAR(64) NOT NULL COMMENT '创建请求摘要',
  created_by VARCHAR(64) NOT NULL COMMENT '登记人',
  updated_by VARCHAR(64) NOT NULL COMMENT '最近更正人',
  created_at DATETIME(3) NOT NULL COMMENT '登记UTC时间',
  updated_at DATETIME(3) NOT NULL COMMENT '更正UTC时间',
  PRIMARY KEY(id),
  UNIQUE KEY uk_fleet_trip_cost_no(cost_no),
  UNIQUE KEY uk_fleet_trip_cost_request(trip_id,created_by,request_key),
  KEY idx_fleet_trip_cost_page(trip_id,incurred_at,id),
  CONSTRAINT fk_fleet_trip_cost_trip FOREIGN KEY(trip_id) REFERENCES ll_fleet_trip(id),
  CONSTRAINT ck_fleet_trip_cost_amount CHECK(amount>=0),
  CONSTRAINT ck_fleet_trip_cost_currency CHECK(currency IN ('CNY','USD')),
  CONSTRAINT ck_fleet_trip_cost_category CHECK(category IN ('FUEL','DRIVER','TOLL','PARKING','OTHER'))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='车次直接运营成本，不生成客户账单或付款';

CREATE TABLE IF NOT EXISTS ll_fleet_trip_cost_history (
  id BIGINT NOT NULL COMMENT '内部主键',
  cost_id BIGINT NOT NULL COMMENT '成本记录内部引用',
  record_version BIGINT NOT NULL COMMENT '成本更正后版本',
  action VARCHAR(16) NOT NULL COMMENT '成本操作CREATE/UPDATE',
  reason VARCHAR(1000) NULL COMMENT '登记或更正说明',
  operator_id VARCHAR(64) NOT NULL COMMENT '操作人',
  occurred_at DATETIME(3) NOT NULL COMMENT '操作UTC时间',
  snapshot_json JSON NOT NULL COMMENT '成本与当时整趟汇总及分摊快照',
  PRIMARY KEY(id),
  UNIQUE KEY uk_fleet_trip_cost_history(cost_id,record_version),
  CONSTRAINT fk_fleet_trip_cost_history FOREIGN KEY(cost_id) REFERENCES ll_fleet_trip_cost(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='车次成本不可变更正历史';