<?php
declare(strict_types=1);

// Code-local development MySQL only. Never points at production.
// Backup/execute/rollback are SHA-gated; the private snapshot retains only
// dish IDs and the one column being migrated, not names or other fields.
$mode = (string)($argv[1] ?? '');
$self = (string)hash_file('sha256', __FILE__);
if (count($argv) !== 2
    || !in_array($mode, ['--preflight', '--backup', '--execute', '--audit', '--rollback'], true)
    || (in_array($mode, ['--backup', '--execute', '--rollback'], true)
        && ((string)getenv('CDX_SPICY_APPROVE') !== 'ROOT_DEV_MYSQL_SPICY_V1'
            || !hash_equals($self, (string)getenv('CDX_SPICY_SHA'))))) {
    echo json_encode(['status' => 'default_deny', 'connected' => false,
        'dml' => false, 'ddl' => false]), PHP_EOL;
    exit(78);
}
$stage = 'runtime';
$db = null;
set_exception_handler(static function (Throwable $error) use (&$stage, &$db): void {
    if ($db instanceof PDO && $db->inTransaction()) $db->rollBack();
    echo json_encode(['status' => 'redacted_stop', 'stage' => $stage,
        'error_class' => get_class($error), 'code' => (string)$error->getCode(),
        'private_snapshot_retained' => true]), PHP_EOL;
    exit(2);
});
if (PHP_VERSION_ID < 70300 || PHP_VERSION_ID >= 70400) {
    throw new RuntimeException('Approved PHP runtime drift');
}
$root = '/tmp/zhct-http-store-mysql.dQTTBP';
$cmdline = (string)@file_get_contents('/proc/10001/cmdline');
if (is_link($root) || strpos($cmdline, $root . '/public') === false
    || strpos($cmdline, '127.0.0.1:18081') === false) {
    throw new RuntimeException('B isolated Store runtime drift');
}
$runtime = require $root . '/application/database.php';
$config = $runtime;
$local = require '/workspace/zhct/zhctproject/store/application/config/local/database.php';
unset($config);
foreach (['hostname', 'hostport', 'database', 'username', 'password', 'type'] as $field) {
    if (($runtime[$field] ?? null) !== ($local[$field] ?? null)) {
        throw new RuntimeException('Not code-local development MySQL');
    }
}
if ((string)$runtime['type'] !== 'mysql' || (int)$runtime['hostport'] !== 3306) {
    throw new RuntimeException('B driver/port drift');
}
$stage = 'database_identity';
$db = new PDO('mysql:host=' . $runtime['hostname'] . ';port=3306;dbname='
    . $runtime['database'] . ';charset=' . ($runtime['charset'] ?? 'utf8'),
    (string)$runtime['username'], (string)$runtime['password'], [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_TIMEOUT => 5,
    ]);
$identity = (string)$db->query(
    "SELECT CONCAT(@@server_uuid,'|',@@hostname,'|',@@port,'|',DATABASE())")
    ->fetchColumn();
$identitySha = hash('sha256', $identity);
if ($identitySha !== '003eb5aecf1cd4595b609c1f7f20b9ee325b578309fb9fe14fedaf77584fa401') {
    throw new RuntimeException('Development MySQL server identity drift');
}
$column = static function () use ($db): array {
    $stmt = $db->query('SELECT COLUMN_TYPE,IS_NULLABLE,COLUMN_DEFAULT,EXTRA,'
        . ' COLUMN_COMMENT,ORDINAL_POSITION FROM information_schema.COLUMNS'
        . " WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='ydy_dishes'"
        . " AND COLUMN_NAME='spicy_level'");
    $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
    if (count($rows) !== 1) throw new RuntimeException('Column missing or ambiguous');
    return $rows[0];
};
$originalColumn = static function (array $row): bool {
    return strtolower((string)$row['COLUMN_TYPE']) === 'tinyint(3) unsigned'
        && strtoupper((string)$row['IS_NULLABLE']) === 'YES'
        && $row['COLUMN_DEFAULT'] === null
        && (string)$row['EXTRA'] === ''
        && (string)$row['COLUMN_COMMENT'] === '辣度：1不辣 2微辣 3中辣 4重辣';
};
$targetColumn = static function (array $row): bool {
    return strtolower((string)$row['COLUMN_TYPE']) === 'tinyint(3) unsigned'
        && strtoupper((string)$row['IS_NULLABLE']) === 'NO'
        && (string)$row['COLUMN_DEFAULT'] === '0'
        && (string)$row['EXTRA'] === ''
        && (string)$row['COLUMN_COMMENT'] === '辣度：1不辣 2微辣 3中辣 4重辣';
};
$readRows = static function () use ($db): array {
    $rows = [];
    $statement = $db->query('SELECT id,spicy_level FROM ydy_dishes ORDER BY id');
    while (($row = $statement->fetch(PDO::FETCH_NUM)) !== false) {
        $id = (int)$row[0];
        if ($id <= 0 || isset($rows[$id])) throw new RuntimeException('Dish ID drift');
        $rows[$id] = [$id, $row[1] === null ? null : (int)$row[1]];
    }
    if (count($rows) < 5000 || count($rows) > 6000) {
        throw new RuntimeException('Dish table scope drift');
    }
    return array_values($rows);
};
$fingerprint = static function (array $rows): string {
    return hash('sha256', (string)json_encode($rows, JSON_UNESCAPED_SLASHES));
};
$nullCount = static function (array $rows): int {
    return count(array_filter($rows, static function (array $r): bool {
        return $r[1] === null;
    }));
};
$expectedRows = static function (array $rows): array {
    return array_map(static function (array $r): array {
        return [$r[0], $r[1] === null ? 0 : $r[1]];
    }, $rows);
};
$triggerCount = (int)$db->query('SELECT COUNT(*) FROM information_schema.TRIGGERS'
    . " WHERE TRIGGER_SCHEMA=DATABASE() AND EVENT_OBJECT_TABLE='ydy_dishes'")
    ->fetchColumn();
if ($triggerCount !== 0) throw new RuntimeException('Dish triggers changed');
$currentColumn = $column();
$currentRows = $readRows();
$currentHash = $fingerprint($currentRows);
$currentNulls = $nullCount($currentRows);
if ($mode === '--preflight') {
    echo json_encode(['status' => 'select_only', 'identity_sha' => $identitySha,
        'original_column' => $originalColumn($currentColumn),
        'target_column' => $targetColumn($currentColumn),
        'row_count' => count($currentRows), 'null_count' => $currentNulls,
        'column_rows_sha' => $currentHash, 'trigger_count' => 0,
        'dml' => false, 'ddl' => false]), PHP_EOL;
    exit(0);
}
$snapshotPath = (string)getenv('CDX_SPICY_SNAPSHOT');
if ($mode === '--backup') {
    $stage = 'backup';
    if (!$originalColumn($currentColumn) || count($currentRows) !== 5501
        || $currentNulls !== 5491) {
        throw new RuntimeException('Approved original column/count drift');
    }
    $directory = '/tmp/cdx-spicy-dev-20261005-'
        . strtolower(bin2hex(random_bytes(6)));
    if (file_exists($directory) || !mkdir($directory, 0700)) {
        throw new RuntimeException('Private snapshot directory unavailable');
    }
    chmod($directory, 0700);
    $snapshotPath = $directory . '/snapshot.json';
    $snapshot = [
        'phase' => 'backed_up', 'identity_sha' => $identitySha,
        'original_column' => $currentColumn,
        'row_count' => count($currentRows), 'original_null_count' => $currentNulls,
        'original_hash' => $currentHash,
        'expected_hash' => $fingerprint($expectedRows($currentRows)),
        'rows' => $currentRows,
    ];
    $serialized = json_encode($snapshot, JSON_UNESCAPED_SLASHES);
    if (!is_string($serialized)
        || file_put_contents($snapshotPath, $serialized, LOCK_EX) === false
        || !chmod($snapshotPath, 0600)) {
        throw new RuntimeException('Private snapshot write failed');
    }
    echo json_encode(['status' => 'backup_ready', 'snapshot' => $snapshotPath,
        'snapshot_sha' => hash_file('sha256', $snapshotPath),
        'row_count' => count($currentRows), 'null_count' => $currentNulls,
        'dml' => false, 'ddl' => false]), PHP_EOL;
    exit(0);
}
if (!preg_match('~^/tmp/cdx-spicy-dev-20261005-[a-f0-9]{12}/snapshot\.json$~D',
    $snapshotPath) || is_link($snapshotPath)
    || is_link(dirname($snapshotPath))
    || (fileperms(dirname($snapshotPath)) & 0777) !== 0700
    || (fileperms($snapshotPath) & 0777) !== 0600) {
    throw new RuntimeException('Private snapshot path/mode invalid');
}
$snapshot = json_decode((string)file_get_contents($snapshotPath), true);
if (!is_array($snapshot) || ($snapshot['identity_sha'] ?? null) !== $identitySha
    || !is_array($snapshot['rows'] ?? null)
    || count($snapshot['rows']) !== 5501
    || ($snapshot['row_count'] ?? null) !== 5501
    || ($snapshot['original_null_count'] ?? null) !== 5491
    || ($snapshot['original_hash'] ?? null) !== $fingerprint($snapshot['rows'])
    || ($snapshot['expected_hash'] ?? null)
        !== $fingerprint($expectedRows($snapshot['rows']))
    || !is_array($snapshot['original_column'] ?? null)
    || !$originalColumn($snapshot['original_column'])) {
    throw new RuntimeException('Private snapshot content invalid');
}
$savePhase = static function (string $phase) use (&$snapshot, $snapshotPath): void {
    $snapshot['phase'] = $phase;
    if (file_put_contents($snapshotPath,
            json_encode($snapshot, JSON_UNESCAPED_SLASHES), LOCK_EX) === false
        || !chmod($snapshotPath, 0600)) {
        throw new RuntimeException('Private phase journal write failed');
    }
};
if ($mode === '--execute') {
    $stage = 'execute_precheck';
    if (($snapshot['phase'] ?? null) !== 'backed_up'
        || !$originalColumn($currentColumn)
        || $currentHash !== $snapshot['original_hash']) {
        throw new RuntimeException('Source changed after backup');
    }
    $nullIds = [];
    foreach ($snapshot['rows'] as $row) if ($row[1] === null) $nullIds[] = (int)$row[0];
    $stage = 'update_original_null_ids';
    $db->beginTransaction();
    $affected = 0;
    foreach (array_chunk($nullIds, 400) as $ids) {
        $sql = 'UPDATE ydy_dishes SET spicy_level=0 WHERE spicy_level IS NULL'
            . ' AND id IN (' . implode(',', array_fill(0, count($ids), '?')) . ')';
        $statement = $db->prepare($sql);
        $statement->execute($ids);
        $affected += $statement->rowCount();
    }
    if ($affected !== 5491) throw new RuntimeException('Exact NULL update count drift');
    if ((int)$db->query('SELECT COUNT(*) FROM ydy_dishes'
            . ' WHERE spicy_level IS NULL')->fetchColumn() !== 0) {
        throw new RuntimeException('New NULL row appeared during update');
    }
    if (!$db->commit()) throw new RuntimeException('NULL update commit uncertain');
    $savePhase('values_updated');
    $stage = 'alter_column_not_null';
    $db->exec('ALTER TABLE ydy_dishes MODIFY COLUMN spicy_level'
        . ' TINYINT(3) UNSIGNED NOT NULL DEFAULT 0 COMMENT '
        . $db->quote((string)$currentColumn['COLUMN_COMMENT']));
    $savePhase('ddl_complete');
    echo json_encode(['status' => 'migration_pending_independent_audit',
        'affected_null_rows' => $affected, 'snapshot' => $snapshotPath,
        'snapshot_sha' => hash_file('sha256', $snapshotPath),
        'dml' => true, 'ddl' => true]), PHP_EOL;
    exit(0);
}
if ($mode === '--audit') {
    $stage = 'independent_audit';
    if (!in_array($snapshot['phase'] ?? null, ['ddl_complete', 'verified'], true)
        || !$targetColumn($currentColumn)
        || $currentHash !== $snapshot['expected_hash']
        || $currentNulls !== 0) {
        throw new RuntimeException('Post-migration source/schema mismatch');
    }
    $savePhase('verified');
    echo json_encode(['status' => 'independent_migration_pass',
        'row_count' => count($currentRows), 'null_count' => 0,
        'target_column' => true,
        'column_rows_sha' => $currentHash,
        'snapshot_retained' => $snapshotPath,
        'dml' => false, 'ddl' => false]), PHP_EOL;
    exit(0);
}
// Rollback is only safe while the exact migrated column snapshot is unchanged.
$stage = 'rollback_precheck';
if (!in_array($snapshot['phase'] ?? null,
        ['ddl_complete', 'verified', 'rollback_nullable'], true)
    || $currentHash !== $snapshot['expected_hash']
    || count($currentRows) !== 5501
    || (!$targetColumn($currentColumn) && !$originalColumn($currentColumn))) {
    throw new RuntimeException('Rollback source changed; manual review required');
}
if ($targetColumn($currentColumn)) {
    $stage = 'rollback_ddl';
    $db->exec('ALTER TABLE ydy_dishes MODIFY COLUMN spicy_level'
        . ' TINYINT(3) UNSIGNED NULL DEFAULT NULL COMMENT '
        . $db->quote((string)$snapshot['original_column']['COLUMN_COMMENT']));
    $savePhase('rollback_nullable');
}
$stage = 'rollback_original_null_ids';
$nullIds = [];
foreach ($snapshot['rows'] as $row) if ($row[1] === null) $nullIds[] = (int)$row[0];
$db->beginTransaction();
$affected = 0;
foreach (array_chunk($nullIds, 400) as $ids) {
    $statement = $db->prepare('UPDATE ydy_dishes SET spicy_level=NULL'
        . ' WHERE spicy_level=0 AND id IN ('
        . implode(',', array_fill(0, count($ids), '?')) . ')');
    $statement->execute($ids);
    $affected += $statement->rowCount();
}
if ($affected !== 5491) throw new RuntimeException('Exact rollback count drift');
if (!$db->commit()) throw new RuntimeException('Rollback commit uncertain');
if (!$originalColumn($column())
    || $fingerprint($readRows()) !== $snapshot['original_hash']) {
    throw new RuntimeException('Rollback readback mismatch');
}
$savePhase('rolled_back');
echo json_encode(['status' => 'rollback_complete',
    'restored_null_rows' => $affected, 'snapshot_retained' => $snapshotPath,
    'dml' => true, 'ddl' => true]), PHP_EOL;