water/server/scripts/migrate_water_dispatch.php

<?php
/** 增量迁移，默认只检查；--apply 新增结构并关联已有配送员，不变更业务库存。 */
require dirname(__DIR__).'/vendor/autoload.php';
\Dotenv\Dotenv::createUnsafeMutable(dirname(__DIR__))->load();
\support\App::loadAllConfig(['route']);
\Webman\ThinkOrm\ThinkOrm::start(null);
use support\think\Db;
if (!in_array('--apply',$argv,true)) { echo "Plan: add driver, shift, route tables and nullable scheduling/location columns. Preserve all business records.\n"; exit; }
Db::execute("CREATE TABLE IF NOT EXISTS sa_water_driver (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(60) NOT NULL, phone VARCHAR(30) NOT NULL,
 region VARCHAR(80) NOT NULL DEFAULT '', vehicle VARCHAR(40) NOT NULL DEFAULT '', capacity INT UNSIGNED NOT NULL DEFAULT 100,
 status TINYINT NOT NULL DEFAULT 1, created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL,
 UNIQUE KEY driver_phone(phone)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
Db::execute("CREATE TABLE IF NOT EXISTS sa_water_driver_shift (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, driver_id BIGINT UNSIGNED NOT NULL, work_date DATE NOT NULL,
 state VARCHAR(10) NOT NULL, updated_at DATETIME NOT NULL, UNIQUE KEY driver_date(driver_id,work_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
Db::execute("CREATE TABLE IF NOT EXISTS sa_water_route (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, code VARCHAR(60) NOT NULL, driver_id BIGINT UNSIGNED NOT NULL,
 work_date DATE NOT NULL, region VARCHAR(80) NOT NULL DEFAULT '', capacity INT UNSIGNED NOT NULL,
 request_key VARCHAR(100) NOT NULL, created_by BIGINT UNSIGNED NOT NULL, created_at DATETIME NOT NULL,
 UNIQUE KEY route_code(code), UNIQUE KEY route_request(request_key), KEY route_date(work_date,driver_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
$columns = [
 'sa_water_route'=>['request_hash'=>"CHAR(64) NOT NULL DEFAULT ''"],
 'sa_water_enterprise'=>['longitude'=>'DECIMAL(10,6) NULL','latitude'=>'DECIMAL(10,6) NULL'],
 'sa_water_delivery'=>['origin_address'=>"VARCHAR(255) NOT NULL DEFAULT ''",'origin_longitude'=>'DECIMAL(10,6) NULL','origin_latitude'=>'DECIMAL(10,6) NULL',
 'driver_id'=>'BIGINT UNSIGNED NULL','route_id'=>'BIGINT UNSIGNED NULL','stop_order'=>'INT UNSIGNED NOT NULL DEFAULT 0',
 'scheduled_date'=>'DATE NULL','deadline_at'=>'DATETIME NULL','longitude'=>'DECIMAL(10,6) NULL','latitude'=>'DECIMAL(10,6) NULL'],
];
foreach ($columns as $table=>$fields) {
 foreach ($fields as $name=>$definition) {
  if (!Db::query("SHOW COLUMNS FROM `$table` LIKE '$name'")) Db::execute("ALTER TABLE `$table` ADD COLUMN `$name` $definition");
 }
}
foreach (['driver_id','route_id','scheduled_date'] as $name) {
 if (!Db::query("SHOW INDEX FROM sa_water_delivery WHERE Key_name='dispatch_$name'")) Db::execute("ALTER TABLE sa_water_delivery ADD INDEX dispatch_$name (`$name`)");
}
// 只归档既有姓名/电话，不推断排班、在线状态或新造测试业务。
Db::transaction(function () {
 $existing = Db::table('sa_water_delivery')->where('driver','<>','')->where('driver_phone','<>','')->select()->toArray();
 foreach ($existing as $task) {
  $id = Db::table('sa_water_driver')->where('phone',$task['driver_phone'])->value('id');
  if (!$id) $id = Db::table('sa_water_driver')->insertGetId(['name'=>$task['driver'],'phone'=>$task['driver_phone'],
   'capacity'=>max(100,(int)$task['quantity']),'created_at'=>date('Y-m-d H:i:s'),'updated_at'=>date('Y-m-d H:i:s')]);
  if (!$task['driver_id']) Db::table('sa_water_delivery')->where('id',$task['id'])->update(['driver_id'=>$id]);
 }
});
echo "Applied additive dispatch migration; existing stock, entitlement and task status preserved.\n";