water/server/scripts/migrate_water_fulfillment.php

<?php
/** Additive fulfillment migration. Existing quantities, receipts and task states are preserved. */
require dirname(__DIR__).'/vendor/autoload.php';
\Dotenv\Dotenv::createUnsafeMutable(dirname(__DIR__))->load();
\support\App::loadAllConfig(['route']);
use plugin\saiadmin\app\model\water\WaterSku;
use plugin\saiadmin\app\model\water\{WaterModel,WaterDelivery,WaterDriver,WaterRoute,WaterDeliveryEvent};
$db=(new WaterSku())->getConnection();
if (!in_array('--apply',$argv,true)) { echo "Plan: add fulfillment counters, route settlement and immutable delivery events.\n"; exit; }
$columns=[
 'sa_water_delivery'=>[
  'delivered_qty'=>'INT UNSIGNED NOT NULL DEFAULT 0','return_good_qty'=>'INT UNSIGNED NOT NULL DEFAULT 0',
  'return_damaged_qty'=>'INT UNSIGNED NOT NULL DEFAULT 0','empty_in_vehicle'=>'INT UNSIGNED NOT NULL DEFAULT 0',
  'receiver'=>"VARCHAR(60) NOT NULL DEFAULT ''",'exception_reason'=>"VARCHAR(255) NOT NULL DEFAULT ''",
  'received_at'=>'DATETIME NULL','returned_at'=>'DATETIME NULL','redelivery_of'=>'BIGINT UNSIGNED NULL',
 ],
 'sa_water_route'=>['settled_at'=>'DATETIME NULL','settled_by'=>'BIGINT UNSIGNED NULL','settlement_note'=>"VARCHAR(255) NOT NULL DEFAULT ''"],
 'sa_water_sku'=>['damaged_qty'=>'INT UNSIGNED NOT NULL DEFAULT 0','empty_lost_qty'=>'INT UNSIGNED NOT NULL DEFAULT 0'],
];
foreach ($columns as $table=>$fields) foreach ($fields as $name=>$definition) {
 if (!$db->select("SHOW COLUMNS FROM `$table` LIKE '$name'")) $db->statement("ALTER TABLE `$table` ADD COLUMN `$name` $definition");
}
if (!$db->select("SHOW INDEX FROM sa_water_delivery WHERE Key_name='delivery_redelivery'")) $db->statement('ALTER TABLE sa_water_delivery ADD UNIQUE KEY delivery_redelivery (redelivery_of)');
$db->statement("CREATE TABLE IF NOT EXISTS sa_water_delivery_event (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, delivery_id BIGINT UNSIGNED NULL, route_id BIGINT UNSIGNED NULL,
 event_key VARCHAR(100) NOT NULL, action VARCHAR(30) NOT NULL, payload_hash CHAR(64) NOT NULL,
 payload LONGTEXT NOT NULL, snapshot LONGTEXT NOT NULL, actor_id BIGINT UNSIGNED NOT NULL, created_at DATETIME NOT NULL,
 UNIQUE KEY fulfillment_event(event_key), KEY delivery_events(delivery_id,id), KEY route_events(route_id,id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
$linked=WaterModel::transaction(function () {
 $ids=WaterDelivery::query()->whereNull('route_id')->whereIn('status',['assigned','transit'])->orderBy('id')->pluck('id')->all();
 $count=0;
 foreach ($ids as $id) {
  $preview=WaterDelivery::query()->find($id);
  WaterSku::query()->where('id',$preview->sku_id)->lockForUpdate()->first();
  $driver=WaterDriver::query()->where('id',$preview->driver_id)->lockForUpdate()->first();
  $task=WaterDelivery::query()->where('id',$id)->lockForUpdate()->first();
  if ($task->route_id || !in_array($task->status,['assigned','transit'],true)) continue;
  if (!$driver) throw new RuntimeException('Legacy delivery #'.$id.' requires a registered driver before migration');
  $routeId=WaterRoute::query()->insertGetId(['code'=>'LEGACY-FULFILL-'.$id,'driver_id'=>$driver->id,
   'work_date'=>$task->scheduled_date?:substr($task->created_at,0,10),'region'=>$task->region,'capacity'=>max((int)$driver->capacity,(int)$task->quantity),
   'request_key'=>'fulfillment-legacy-'.$id,'request_hash'=>hash('sha256','fulfillment-legacy-'.$id),'created_by'=>$task->created_by,'created_at'=>date('Y-m-d H:i:s')]);
  WaterDelivery::query()->where('id',$id)->update(['route_id'=>$routeId,'stop_order'=>1]);
  WaterDeliveryEvent::query()->insert(['delivery_id'=>$id,'route_id'=>$routeId,'event_key'=>'legacy_route:'.$id,'action'=>'legacy_route',
   'payload_hash'=>hash('sha256','{}'),'payload'=>'{}','snapshot'=>json_encode(['before'=>$task->toArray(),'route_id'=>$routeId]),'actor_id'=>0,'created_at'=>date('Y-m-d H:i:s')]);
  $count++;
 }
 return $count;
});
echo "Applied additive fulfillment migration; linked $linked active legacy tasks to routes; historical balances and statuses preserved.\n";