water/server/scripts/migrate_water_mini_api.php

<?php

/** Additive API schema. No sample users, grants, balances or paid orders are created. */
require dirname(__DIR__) . '/vendor/autoload.php';
\Dotenv\Dotenv::createUnsafeMutable(dirname(__DIR__))->load();
\support\App::loadAllConfig(['route']);
if (!in_array('--apply', $argv, true)) {
    echo "Plan: add eight mini API tables, preserving existing water data.\n";
    exit;
}
$db = (new \app\miniapi\model\MiniUser())->getConnection();
$definitions = [
    'user' => "id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, appid VARCHAR(64) NOT NULL, openid VARCHAR(128) NOT NULL,
        nickname VARCHAR(60) NOT NULL DEFAULT '', status TINYINT NOT NULL DEFAULT 1, created_at DATETIME NOT NULL,
        updated_at DATETIME NOT NULL, UNIQUE KEY identity_key(appid,openid)",
    'session' => "id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id BIGINT UNSIGNED NOT NULL,
        token_hash CHAR(64) NOT NULL, expires_at DATETIME NOT NULL, revoked_at DATETIME NULL, created_at DATETIME NOT NULL,
        UNIQUE KEY token_key(token_hash), KEY user_sessions(user_id,expires_at)",
    'grant' => "id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id BIGINT UNSIGNED NOT NULL,
        role VARCHAR(30) NOT NULL, resource_id BIGINT UNSIGNED NOT NULL, status TINYINT NOT NULL DEFAULT 1,
        created_by BIGINT UNSIGNED NOT NULL, created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL,
        UNIQUE KEY user_role_resource(user_id,role,resource_id)",
    'command' => "id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id BIGINT UNSIGNED NOT NULL,
        request_key VARCHAR(80) NOT NULL, action VARCHAR(100) NOT NULL, payload_hash CHAR(64) NOT NULL,
        result LONGTEXT NOT NULL, created_at DATETIME NOT NULL, UNIQUE KEY command_key(user_id,request_key)",
    'audit' => "id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, actor_type VARCHAR(20) NOT NULL,
        actor_id BIGINT UNSIGNED NOT NULL, action VARCHAR(100) NOT NULL, resource_id BIGINT UNSIGNED NOT NULL DEFAULT 0,
        data LONGTEXT NOT NULL, created_at DATETIME NOT NULL, KEY actor_events(actor_type,actor_id,id)",
    'checkout' => "id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id BIGINT UNSIGNED NOT NULL,
        enterprise_id BIGINT UNSIGNED NOT NULL, code VARCHAR(60) NOT NULL, status VARCHAR(20) NOT NULL DEFAULT 'pending',
        items LONGTEXT NOT NULL, address_snapshot LONGTEXT NOT NULL, invoice LONGTEXT NULL,
        amount_cents BIGINT UNSIGNED NOT NULL, created_at DATETIME NOT NULL, expires_at DATETIME NOT NULL,
        UNIQUE KEY checkout_code(code), KEY enterprise_orders(enterprise_id,id)",
    'enterprise_balance' => "id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, enterprise_id BIGINT UNSIGNED NOT NULL,
        balance_cents BIGINT UNSIGNED NOT NULL DEFAULT 0, created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL,
        UNIQUE KEY enterprise_balance_key(enterprise_id)",
    'balance_ledger' => "id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, enterprise_id BIGINT UNSIGNED NOT NULL,
        checkout_id BIGINT UNSIGNED NOT NULL, user_id BIGINT UNSIGNED NOT NULL, request_key VARCHAR(80) NOT NULL,
        direction TINYINT NOT NULL, amount_cents BIGINT UNSIGNED NOT NULL, before_balance BIGINT UNSIGNED NOT NULL,
        after_balance BIGINT UNSIGNED NOT NULL, source VARCHAR(30) NOT NULL, created_at DATETIME NOT NULL,
        UNIQUE KEY balance_payment_key(enterprise_id,user_id,request_key), KEY balance_checkout(checkout_id,id)",
    'task_record' => "id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, delivery_id BIGINT UNSIGNED NOT NULL,
        user_id BIGINT UNSIGNED NOT NULL, kind VARCHAR(30) NOT NULL, data LONGTEXT NOT NULL, created_at DATETIME NOT NULL,
        KEY delivery_records(delivery_id,id)",
    'photo' => "id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, delivery_id BIGINT UNSIGNED NOT NULL,
        user_id BIGINT UNSIGNED NOT NULL, mime VARCHAR(30) NOT NULL, image_data MEDIUMBLOB NOT NULL,
        latitude DECIMAL(10,6) NULL, longitude DECIMAL(10,6) NULL, created_at DATETIME NOT NULL,
        KEY delivery_photos(delivery_id,id)",
];
foreach ($definitions as $name => $definition) {
    $table = 'sa_water_mini_' . $name;
    $db->statement("CREATE TABLE IF NOT EXISTS `$table` ($definition) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
    $engine = $db->selectOne('SELECT ENGINE FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME=?', [$table]);
    if (strtolower($engine->ENGINE ?? '') !== 'innodb') {
        throw new RuntimeException($table . ' must use InnoDB');
    }
}
$checkoutTable = 'sa_water_mini_checkout';
$checkoutColumns = $db->select("SELECT COLUMN_NAME FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME=?", [$checkoutTable]);
$checkoutColumnNames = array_map(static fn ($row) => (string) ($row->COLUMN_NAME ?? ''), $checkoutColumns);
if (!in_array('payment_method', $checkoutColumnNames, true)) {
    $db->statement("ALTER TABLE `$checkoutTable` ADD COLUMN payment_method VARCHAR(20) NULL AFTER expires_at");
}
if (!in_array('paid_at', $checkoutColumnNames, true)) {
    $db->statement("ALTER TABLE `$checkoutTable` ADD COLUMN paid_at DATETIME NULL AFTER payment_method");
}
$balanceLedgerTable = 'sa_water_mini_balance_ledger';
$paymentIndexColumns = array_map(
    static fn ($row) => (string) ($row->Column_name ?? ''),
    $db->select("SHOW INDEX FROM `$balanceLedgerTable` WHERE Key_name='balance_payment_key'")
);
if ($paymentIndexColumns !== ['enterprise_id', 'user_id', 'request_key']) {
    if ($paymentIndexColumns) {
        $db->statement("ALTER TABLE `$balanceLedgerTable` DROP INDEX balance_payment_key");
    }
    $db->statement("ALTER TABLE `$balanceLedgerTable` ADD UNIQUE KEY balance_payment_key(enterprise_id,user_id,request_key)");
}
echo "Applied mini API tables and payment columns; existing business records and authorizations unchanged.\n";