water/server/scripts/migrate_water_promotion.php

<?php

declare(strict_types=1);

/** Additive schema only. No existing orders, balances or entitlements are changed. */
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\WaterPromotionSetting;

if (!in_array('--apply', $argv, true)) {
    echo "Plan: create three InnoDB promotion tables; insert a disabled policy only. No historical rewards.\n";
    exit;
}

$schema = (new WaterPromotionSetting())->getConnection()->getSchemaBuilder();
if (!$schema->hasTable('sa_water_promotion_setting')) {
    $schema->create('sa_water_promotion_setting', function (Blueprint $table): void {
        $table->engine = 'InnoDB';
        $table->unsignedInteger('id')->primary();
        $table->boolean('enabled')->default(false);
        $table->unsignedBigInteger('sku_id')->nullable();
        $table->unsignedInteger('quantity')->default(0);
        $table->unsignedBigInteger('min_amount_cents')->default(0);
        $table->unsignedInteger('revision')->default(1);
        $table->unsignedBigInteger('updated_by')->default(0);
        $table->dateTime('updated_at')->nullable();
    });
}
if (!$schema->hasTable('sa_water_promotion_code')) {
    $schema->create('sa_water_promotion_code', function (Blueprint $table): void {
        $table->engine = 'InnoDB';
        $table->bigIncrements('id');
        $table->unsignedBigInteger('enterprise_id')->unique();
        $table->string('code', 24)->unique();
        $table->dateTime('created_at');
    });
}
if (!$schema->hasTable('sa_water_promotion_referral')) {
    $schema->create('sa_water_promotion_referral', function (Blueprint $table): void {
        $table->engine = 'InnoDB';
        $table->bigIncrements('id');
        $table->unsignedBigInteger('inviter_id');
        $table->unsignedBigInteger('invited_id')->unique();
        $table->unsignedBigInteger('bound_by');
        $table->unsignedInteger('policy_revision');
        $table->unsignedBigInteger('sku_id');
        $table->unsignedInteger('quantity');
        $table->unsignedBigInteger('min_amount_cents');
        $table->unsignedBigInteger('order_id')->nullable()->unique();
        $table->unsignedBigInteger('checkout_id')->nullable();
        $table->unsignedBigInteger('purchase_amount_cents')->nullable();
        $table->unsignedBigInteger('delivery_floor_id')->default(0);
        $table->string('status', 20)->default('bound');
        $table->unsignedInteger('revision')->default(1);
        $table->dateTime('created_at');
        $table->dateTime('qualified_at')->nullable();
        $table->unsignedBigInteger('delivery_id')->nullable();
        $table->unsignedBigInteger('reviewed_by')->nullable();
        $table->dateTime('reviewed_at')->nullable();
        $table->string('review_note', 500)->default('');
        $table->index(['inviter_id', 'id']);
        $table->index(['status', 'id']);
    });
}
foreach (['checkout_id', 'purchase_amount_cents'] as $column) {
    if (!$schema->hasColumn('sa_water_promotion_referral', $column)) {
        $schema->table('sa_water_promotion_referral', function (Blueprint $table) use ($column): void {
            $table->unsignedBigInteger($column)->nullable();
        });
    }
}
if (!$schema->hasColumn('sa_water_promotion_referral', 'delivery_floor_id')) {
    $schema->table('sa_water_promotion_referral', function (Blueprint $table): void {
        $table->unsignedBigInteger('delivery_floor_id')->default(0);
    });
}
WaterPromotionSetting::query()->insertOrIgnore(['id' => 1]);
echo "Applied: promotion schema ready; existing policy preserved, new policy disabled.\n";