water/server/scripts/migrate_water_commission.php

<?php

declare(strict_types=1);

/** Create the immutable attribution snapshot for commissions on future paid water orders. */
require dirname(__DIR__) . '/vendor/autoload.php';
\Dotenv\Dotenv::createUnsafeMutable(dirname(__DIR__))->load();
\support\App::loadAllConfig(['route']);

use Illuminate\Database\Schema\Blueprint;
use plugin\saiadmin\app\model\water\WaterCommission;
use plugin\saiadmin\app\model\water\WaterCommissionSetting;

if (!in_array('--apply', $argv, true)) {
    echo "Plan: create commission snapshots and editable platform rates; existing orders are not backfilled.\n";
    exit;
}

$model = new WaterCommission();
$schema = $model->getConnection()->getSchemaBuilder();
if (!$schema->hasTable($model->getTable())) {
    $schema->create($model->getTable(), function (Blueprint $table): void {
        $table->engine = 'InnoDB';
        $table->bigIncrements('id');
        $table->unsignedBigInteger('order_id');
        $table->unsignedBigInteger('enterprise_id');
        $table->string('enterprise_name', 120);
        $table->unsignedBigInteger('agent_id');
        $table->unsignedTinyInteger('agent_level');
        $table->unsignedSmallInteger('rate_basis_points');
        $table->unsignedBigInteger('base_amount_cents');
        $table->unsignedBigInteger('amount_cents');
        $table->string('status', 20)->default('pending');
        $table->dateTime('created_at');
        $table->dateTime('settled_at')->nullable();
        $table->unique(['order_id', 'agent_id']);
        $table->index(['agent_id', 'status', 'id']);
        $table->index(['enterprise_id', 'id']);
        $table->comment('供水订单成交时的代理与比例快照；未结算不得提现');
    });
}

$connection = $model->getConnection();
$status = $connection->selectOne("SHOW TABLE STATUS LIKE 'sa_water_commission'");
if ($status && strcasecmp((string) $status->Engine, 'InnoDB') !== 0) {
    $connection->statement('ALTER TABLE sa_water_commission ENGINE=InnoDB');
}

$settings = new WaterCommissionSetting();
if (!$schema->hasTable($settings->getTable())) {
    $schema->create($settings->getTable(), function (Blueprint $table): void {
        $table->engine = 'InnoDB';
        $table->unsignedInteger('id')->primary();
        $table->unsignedSmallInteger('level1_basis_points');
        $table->unsignedSmallInteger('level2_basis_points');
        $table->unsignedSmallInteger('level3_basis_points');
        $table->unsignedInteger('revision')->default(1);
        $table->unsignedBigInteger('updated_by')->default(0);
        $table->dateTime('updated_at')->nullable();
    });
}
$settingsStatus = $connection->selectOne("SHOW TABLE STATUS LIKE 'sa_water_commission_setting'");
if ($settingsStatus && strcasecmp((string) $settingsStatus->Engine, 'InnoDB') !== 0) {
    $connection->statement('ALTER TABLE sa_water_commission_setting ENGINE=InnoDB');
}
WaterCommissionSetting::query()->insertOrIgnore([
    'id' => 1,
    'level1_basis_points' => 1000,
    'level2_basis_points' => 500,
    'level3_basis_points' => 200,
    'revision' => 1,
]);

echo "Applied: commission snapshots and editable rates are ready; existing orders were not changed.\n";