water/server/scripts/migrate_water_mini_sms.php

<?php

/** Additive SMS login schema. Creates no codes, binds no phone and touches no grant. */
require dirname(__DIR__) . '/vendor/autoload.php';
\Dotenv\Dotenv::createUnsafeMutable(dirname(__DIR__))->load();
\support\App::loadAllConfig(['route']);

if (!in_array('--apply', $argv, true)) {
    echo "Plan: create sa_water_mini_sms_code and add sa_water_mini_user.phone.\n";
    exit;
}

$db = (new \app\miniapi\model\MiniUser())->getConnection();
$codeTable = 'sa_water_mini_sms_code';
$db->statement("CREATE TABLE IF NOT EXISTS `$codeTable` (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    phone VARCHAR(20) NOT NULL,
    purpose VARCHAR(20) NOT NULL DEFAULT 'login',
    code_hash CHAR(64) NOT NULL,
    attempts TINYINT UNSIGNED NOT NULL DEFAULT 0,
    expires_at DATETIME NOT NULL,
    consumed_at DATETIME NULL,
    request_ip VARCHAR(45) NOT NULL DEFAULT '',
    created_at DATETIME NOT NULL,
    KEY phone_recent(phone, created_at),
    KEY expiry(expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

$userTable = 'sa_water_mini_user';
$columns = array_map(
    static fn ($row) => (string) ($row->COLUMN_NAME ?? ''),
    $db->select("SELECT COLUMN_NAME FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME=?", [$userTable])
);
if (!in_array('phone', $columns, true)) {
    $db->statement("ALTER TABLE `$userTable` ADD COLUMN phone VARCHAR(20) NULL AFTER nickname");
}
// NULLs are exempt from a unique index, so existing WeChat-only accounts stay valid.
$indexes = array_map(
    static fn ($row) => (string) ($row->Key_name ?? ''),
    $db->select("SHOW INDEX FROM `$userTable` WHERE Column_name='phone'")
);
if (!in_array('phone_key', $indexes, true)) {
    $db->statement("ALTER TABLE `$userTable` ADD UNIQUE KEY phone_key(phone)");
}

echo "Applied SMS login schema; no code, phone binding or grant was created.\n";