<?php
declare(strict_types=1);

// Nonproduction only. User authorized missing-source-column alignment and waived backup/restore.
if (getenv('POC_AI_HOST') !== '127.0.0.1' || getenv('POC_AI_PORT') !== '5238'
    || getenv('POC_AI_SCHEMA') !== 'AIZHCT_APP' || getenv('POC_AI_USER') !== 'AIZHCT_APP') {
    throw new RuntimeException('Approved AI target gate');
}
$pdo = new PDO('dm:host=127.0.0.1;port=5238;schema=AIZHCT_APP', getenv('POC_AI_USER'),
    getenv('POC_AI_PASSWORD'), [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_CASE => PDO::CASE_LOWER]);
if ($pdo->query("SELECT SYS_CONTEXT('USERENV','CURRENT_SCHEMA') FROM DUAL")->fetchColumn() !== 'AIZHCT_APP') {
    throw new RuntimeException('Effective schema gate');
}
$tables = [
    'YOSHOP_COUPON' => [
        'old_count' => 21,
        'fields' => [
            'WRITE_OFF_LIMIT_AMOUNT' => ['sql' => 'DECIMAL(10,2) DEFAULT 0.00 NOT NULL', 'type' => 'DECIMAL', 'nullable' => 'N', 'comment' => '核销上限金额/账单阈值'],
            'UNAVAILABLE_DATES' => ['sql' => 'CLOB DEFAULT NULL', 'type' => 'CLOB', 'nullable' => 'Y', 'comment' => '不可用日期(JSON数组, 例如 [2026-02-01])'],
            'UNAVAILABLE_TIME_RANGES' => ['sql' => 'CLOB DEFAULT NULL', 'type' => 'CLOB', 'nullable' => 'Y', 'comment' => '不可用时段(JSON数组, 例如 [12:00-13:00])'],
        ],
        'checks' => ['CDX_CPN_WRITE_OFF_U' => 'WRITE_OFF_LIMIT_AMOUNT >= 0'],
    ],
    'YOSHOP_USER_COUPON' => [
        'old_count' => 19,
        'fields' => [
            'EMPLOYEE_ID' => ['sql' => 'BIGINT DEFAULT 0 NOT NULL', 'type' => 'BIGINT', 'nullable' => 'N', 'comment' => '员工ID'],
            'ISSUE_RULE_ID' => ['sql' => 'BIGINT DEFAULT 0 NOT NULL', 'type' => 'BIGINT', 'nullable' => 'N', 'comment' => '发放规则ID'],
            'ISSUE_RECORD_ID' => ['sql' => 'BIGINT DEFAULT 0 NOT NULL', 'type' => 'BIGINT', 'nullable' => 'N', 'comment' => '发放记录ID'],
        ],
        'checks' => [
            'CDX_UCP_EMPLOYEE_U' => 'EMPLOYEE_ID BETWEEN 0 AND 4294967295',
            'CDX_UCP_ISSUE_RULE_U' => 'ISSUE_RULE_ID BETWEEN 0 AND 4294967295',
            'CDX_UCP_ISSUE_RECORD_U' => 'ISSUE_RECORD_ID BETWEEN 0 AND 4294967295',
        ],
    ],
];
$columns = static function (string $table) use ($pdo): array {
    $s = $pdo->prepare('SELECT COLUMN_NAME,DATA_TYPE,DATA_PRECISION,DATA_SCALE,NULLABLE,DATA_DEFAULT FROM USER_TAB_COLUMNS WHERE TABLE_NAME=? ORDER BY COLUMN_ID');
    $s->execute([$table]); return $s->fetchAll(PDO::FETCH_ASSOC);
};
$objects = static function (string $table) use ($pdo): array {
    $out = [];
    foreach (['USER_INDEXES' => 'INDEX_NAME', 'USER_CONSTRAINTS' => 'CONSTRAINT_NAME', 'USER_TRIGGERS' => 'TRIGGER_NAME'] as $view => $column) {
        $s = $pdo->prepare('SELECT '.$column.' FROM '.$view.' WHERE TABLE_NAME=? ORDER BY '.$column);
        $s->execute([$table]); $out[$view] = $s->fetchAll(PDO::FETCH_COLUMN);
    }
    return $out;
};
$before = [];
foreach ($tables as $table => $contract) {
    $cols = $columns($table); $names = array_column($cols, 'column_name');
    if (count($cols) !== $contract['old_count'] || array_intersect($names, array_keys($contract['fields']))) {
        throw new RuntimeException('Already applied or conflicting shape; inspect before resuming');
    }
    $rows = (int)$pdo->query('SELECT COUNT(*) FROM AIZHCT_APP.'.$table)->fetchColumn();
    $objs = $objects($table);
    if ($rows !== 0 || $objs['USER_TRIGGERS']) throw new RuntimeException('Expected empty unchanged coupon tables');
    foreach ($contract['checks'] as $name => $condition) {
        $s = $pdo->prepare('SELECT COUNT(*) FROM USER_CONSTRAINTS WHERE CONSTRAINT_NAME=?'); $s->execute([$name]);
        if ((int)$s->fetchColumn() !== 0) throw new RuntimeException('Constraint name collision');
    }
    $before[$table] = ['columns' => $cols, 'objects' => $objs, 'rows' => $rows];
}
$phases = []; $started = microtime(true);
foreach ($tables as $table => $contract) {
    $defs = [];
    foreach ($contract['fields'] as $name => $field) $defs[] = $name.' '.$field['sql'];
    $sql = 'ALTER TABLE AIZHCT_APP.'.$table.' ADD ('.implode(',', $defs).')';
    $pdo->exec($sql); $phases[] = ['table' => $table, 'phase' => 'add-three-columns', 'sql_sha256' => hash('sha256', $sql)];
    foreach ($contract['checks'] as $name => $condition) {
        $sql = 'ALTER TABLE AIZHCT_APP.'.$table.' ADD CONSTRAINT '.$name.' CHECK ('.$condition.')';
        $pdo->exec($sql); $phases[] = ['table' => $table, 'phase' => 'preserve-unsigned-domain', 'name' => $name, 'sql_sha256' => hash('sha256', $sql)];
    }
    foreach ($contract['fields'] as $name => $field) {
        $sql = 'COMMENT ON COLUMN AIZHCT_APP.'.$table.'.'.$name." IS '".str_replace("'", "''", $field['comment'])."'";
        $pdo->exec($sql); $phases[] = ['table' => $table, 'phase' => 'source-comment', 'column' => $name, 'sql_sha256' => hash('sha256', $sql)];
    }
    $after = $columns($table); $names = array_column($after, 'column_name');
    if (count($after) !== $contract['old_count'] + 3 || array_slice($after, 0, $contract['old_count']) !== $before[$table]['columns']) {
        throw new RuntimeException('Original column metadata changed');
    }
    foreach ($contract['fields'] as $name => $field) {
        $c = $after[array_search($name, $names, true)];
        if (strtoupper($c['data_type']) !== $field['type'] || $c['nullable'] !== $field['nullable']) throw new RuntimeException('Target type/null mismatch');
        if ($field['type'] === 'DECIMAL' && ((int)$c['data_precision'] !== 10 || (int)$c['data_scale'] !== 2)) throw new RuntimeException('Decimal shape mismatch');
        if ($field['nullable'] === 'N' && (!is_numeric($c['data_default']) || (float)$c['data_default'] !== 0.0)) throw new RuntimeException('Zero default mismatch');
        if ($field['nullable'] === 'Y' && $c['data_default'] !== null && strtoupper(trim((string)$c['data_default'])) !== 'NULL') throw new RuntimeException('NULL default mismatch');
    }
    $afterObjects = $objects($table); $oldObjects = $before[$table]['objects'];
    if ($afterObjects['USER_INDEXES'] !== $oldObjects['USER_INDEXES'] || $afterObjects['USER_TRIGGERS'] !== $oldObjects['USER_TRIGGERS']) throw new RuntimeException('Original index/trigger changed');
    $expectedConstraints = array_merge($oldObjects['USER_CONSTRAINTS'], array_keys($contract['checks'])); sort($expectedConstraints);
    if ($afterObjects['USER_CONSTRAINTS'] !== $expectedConstraints) throw new RuntimeException('Unexpected constraint set');
    if ((int)$pdo->query('SELECT COUNT(*) FROM AIZHCT_APP.'.$table)->fetchColumn() !== 0) throw new RuntimeException('Concurrent data appeared; inspect');
    echo json_encode(['table' => $table, 'state' => 'SQL_VERIFIED', 'columns_before' => $contract['old_count'], 'columns_after' => count($after),
        'original_metadata_indexes_triggers_preserved' => true, 'expected_unsigned_checks_added' => array_keys($contract['checks']),
        'rows_before' => 0, 'rows_after' => 0, 'new_fields' => array_keys($contract['fields'])]), PHP_EOL;
}
echo json_encode(['state' => 'SQL_VERIFIED', 'backup_restore_waived_by_user' => true, 'ddl_statements' => count($phases),
    'business_dml' => 0, 'phases' => $phases, 'elapsed_seconds' => round(microtime(true) - $started, 3)]), PHP_EOL;