baiyue-firstleg/deploy/sql/lastleg-dispatch-transport-tasks.sql

-- MySQL 8 可重复执行增量：仓储转运到仓取货与本土卡车接入现有排车。
-- 目标数据库固定为 baiyue_lastleg；仅新增运输类型、原单关联及请求账本，不回填或删除业务数据。
-- 前置：已完成 Lastleg 现有整车排车结构升级；如部署为缺少每日流水表的旧库，也创建正式排车流水表。
USE baiyue_lastleg;

SET @dispatch_transport_column_ddl = IF(
  EXISTS (
    SELECT 1 FROM information_schema.columns
    WHERE table_schema = DATABASE() AND table_name = 'll_dispatch_order' AND column_name = 'task_type'
  ),
  'SELECT 1',
  'ALTER TABLE ll_dispatch_order ADD COLUMN task_type VARCHAR(32) NOT NULL DEFAULT ''DELIVERY'' COMMENT ''任务类型：仓库派送、到仓取货、本土卡车'' AFTER dispatch_no'
);
PREPARE dispatch_transport_column_stmt FROM @dispatch_transport_column_ddl;
EXECUTE dispatch_transport_column_stmt;
DEALLOCATE PREPARE dispatch_transport_column_stmt;

-- 旧库兼容：整车主单跨客户，原来单客户排车头字段不再要求填写。
-- 如目标库已删除旧字段则跳过；已有客户值保留，不置空、不修改。
SET @dispatch_transport_legacy_ddl = IF(
  EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'll_dispatch_order' AND column_name = 'customer_id' AND is_nullable = 'NO'),
  'ALTER TABLE ll_dispatch_order MODIFY COLUMN customer_id VARCHAR(128) NULL COMMENT ''旧客户排车归属，整车主单不使用''',
  'SELECT 1'
);
PREPARE dispatch_transport_legacy_stmt FROM @dispatch_transport_legacy_ddl;
EXECUTE dispatch_transport_legacy_stmt;
DEALLOCATE PREPARE dispatch_transport_legacy_stmt;

-- 旧本土卡车结构兼容：新单沿用已有的起终点联系人及pickup_at/delivery_at字段。
-- 旧单字段保留原值，仅放宽空值约束，避免新单被已弃用字段阻断。
SET @dispatch_transport_legacy_ddl = IF(
  EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'll_us_truck_order' AND column_name = 'contact_name' AND is_nullable = 'NO'),
  'ALTER TABLE ll_us_truck_order MODIFY COLUMN contact_name VARCHAR(128) NULL COMMENT ''旧本土卡车联系人，新单使用起终点联系人''',
  'SELECT 1'
);
PREPARE dispatch_transport_legacy_stmt FROM @dispatch_transport_legacy_ddl;
EXECUTE dispatch_transport_legacy_stmt;
DEALLOCATE PREPARE dispatch_transport_legacy_stmt;

SET @dispatch_transport_legacy_ddl = IF(
  EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'll_us_truck_order' AND column_name = 'contact_info' AND is_nullable = 'NO'),
  'ALTER TABLE ll_us_truck_order MODIFY COLUMN contact_info VARCHAR(128) NULL COMMENT ''旧本土卡车联系方式，新单使用起终点联系方式''',
  'SELECT 1'
);
PREPARE dispatch_transport_legacy_stmt FROM @dispatch_transport_legacy_ddl;
EXECUTE dispatch_transport_legacy_stmt;
DEALLOCATE PREPARE dispatch_transport_legacy_stmt;

SET @dispatch_transport_legacy_ddl = IF(
  EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'll_us_truck_order' AND column_name = 'requested_date' AND is_nullable = 'NO'),
  'ALTER TABLE ll_us_truck_order MODIFY COLUMN requested_date DATE NULL COMMENT ''旧本土卡车请求日期，新单使用取货和送货时间''',
  'SELECT 1'
);
PREPARE dispatch_transport_legacy_stmt FROM @dispatch_transport_legacy_ddl;
EXECUTE dispatch_transport_legacy_stmt;
DEALLOCATE PREPARE dispatch_transport_legacy_stmt;

CREATE TABLE IF NOT EXISTS ll_dispatch_daily_sequence (
  business_date DATE NOT NULL COMMENT '排车编号业务日期（Asia/Shanghai）',
  last_sequence INT NOT NULL COMMENT '当日已分配排车流水号，范围0001至9999',
  PRIMARY KEY (business_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='排车单号每日独立原子流水';

CREATE TABLE IF NOT EXISTS ll_dispatch_truck_task (
  dispatch_no VARCHAR(64) NOT NULL COMMENT '排车业务号',
  trip_no VARCHAR(64) NOT NULL COMMENT '本次运输资源记录号',
  customer_id VARCHAR(128) NOT NULL COMMENT '本土卡车原单客户标识',
  order_no VARCHAR(64) NOT NULL COMMENT '本土卡车原单业务号',
  active_order VARCHAR(193) NULL COMMENT '有效原单唯一键；取消置空，完成保留',
  PRIMARY KEY (dispatch_no),
  UNIQUE KEY uk_transport_trip (trip_no),
  UNIQUE KEY uk_transport_source (active_order),
  KEY idx_transport_order (customer_id, order_no)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='排车与本土卡车原单关联';

CREATE TABLE IF NOT EXISTS ll_dispatch_transport_create (
  trip_no VARCHAR(64) NOT NULL COMMENT '本次运输资源记录号',
  request_hash CHAR(64) NOT NULL COMMENT '首次安排请求摘要',
  PRIMARY KEY (trip_no)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='本土卡车排车创建请求账本';

CREATE TABLE IF NOT EXISTS ll_dispatch_transport_command (
  dispatch_no VARCHAR(64) NOT NULL COMMENT '排车业务号',
  request_key VARCHAR(128) NOT NULL COMMENT '客户端幂等请求键',
  request_hash CHAR(64) NOT NULL COMMENT '执行命令请求摘要',
  evidence_json JSON NULL COMMENT '已确认运输完成凭证标识列表',
  created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) COMMENT '业务命令提交时间',
  PRIMARY KEY (dispatch_no, request_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='排车运输执行命令与凭证账本';

-- 当前承运占用查询还依赖散板来源标识（与V33一致）；仅补结构，不创建散板业务记录。
SET @dispatch_transport_pool_ddl = IF(
  EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'll_fleet_external_booking' AND column_name = 'source_looseboard_order_id'),
  'SELECT 1',
  'ALTER TABLE ll_fleet_external_booking ADD COLUMN source_looseboard_order_id BIGINT NULL COMMENT ''客户原始散板商业单ID，取消后保留来源'''
);
PREPARE dispatch_transport_pool_stmt FROM @dispatch_transport_pool_ddl;
EXECUTE dispatch_transport_pool_stmt;
DEALLOCATE PREPARE dispatch_transport_pool_stmt;

SET @dispatch_transport_pool_ddl = IF(
  EXISTS (SELECT 1 FROM information_schema.statistics WHERE table_schema = DATABASE() AND table_name = 'll_fleet_external_booking' AND index_name = 'idx_booking_looseboard_source'),
  'SELECT 1',
  'ALTER TABLE ll_fleet_external_booking ADD KEY idx_booking_looseboard_source (customer_id, source_looseboard_order_id, status)'
);
PREPARE dispatch_transport_pool_stmt FROM @dispatch_transport_pool_ddl;
EXECUTE dispatch_transport_pool_stmt;
DEALLOCATE PREPARE dispatch_transport_pool_stmt;