<?php
declare(strict_types=1);

// Test-only repair; credentials are inherited in memory from the approved runtime.
$action = (string)(getenv('POC_SCHEMA_ACTION') ?: 'preflight');
$schema = strtoupper((string)getenv('POC_SCHEMA'));
if ($schema !== 'ZHCT_APP' || getenv('POC_DIALECT') !== 'dm') {
    throw new RuntimeException('This repair is restricted to the approved DM test schema');
}
$pdo = new PDO('dm:host=' . getenv('POC_HOST') . ';port=' . getenv('POC_PORT')
    . ';schema=' . $schema, getenv('POC_USER'), getenv('POC_PASSWORD'), [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_CASE => PDO::CASE_LOWER,
    ]);
$table = 'YDY_MEAL_ORDER';
$definitions = [
    'ACTIVITY_PRINCIPAL_PAYMENT' => 'DECIMAL(10,2) DEFAULT 0.00',
    'ACTIVITY_PRINCIPAL_REFUND_AMOUNT' => 'DECIMAL(10,2) DEFAULT 0.00',
    'FACE_DIRECT_PAYMENT' => 'SMALLINT DEFAULT 0',
    'IS_SUPPLEMENT' => 'SMALLINT DEFAULT 0',
    'ORIGIN_COST_CENTER_CODE' => "VARCHAR(64 CHAR) DEFAULT ''",
    'SETTLEMENT_ORDER_NO' => "VARCHAR(64 CHAR) DEFAULT ''",
    'STRATEGY_ACCOUNT_SNAPSHOT' => "VARCHAR(512 CHAR) DEFAULT ''",
    'SUPPLEMENT_ID' => 'BIGINT DEFAULT NULL',
    'SUPPLEMENT_NO' => 'VARCHAR(32 CHAR) DEFAULT NULL',
    'WITHDRAWABLE_CASH_PAYMENT' => 'DECIMAL(10,2) DEFAULT 0.00',
    'WITHDRAWABLE_CASH_REFUND_AMOUNT' => 'DECIMAL(10,2) DEFAULT 0.00',
];
$nullable = ['SUPPLEMENT_ID', 'SUPPLEMENT_NO'];
$columns = static function (string $name) use ($pdo): array {
    $q = $pdo->prepare('SELECT COLUMN_NAME,DATA_TYPE,DATA_LENGTH,DATA_PRECISION,'
        . 'DATA_SCALE,NULLABLE,DATA_DEFAULT FROM USER_TAB_COLUMNS'
        . ' WHERE TABLE_NAME=? ORDER BY COLUMN_ID');
    $q->execute([$name]);
    return $q->fetchAll(PDO::FETCH_ASSOC);
};
$fingerprint = static function (string $name, array $names) use ($pdo, $schema): array {
    foreach (array_merge([$name], $names) as $identifier) {
        if (!preg_match('/^[A-Z][A-Z0-9_]*$/D', $identifier)) {
            throw new RuntimeException('Invalid snapshot identifier');
        }
    }
    $sql = 'SELECT ' . implode(',', array_map(static function ($s) { return '"' . $s . '"'; }, $names))
        . ' FROM ' . $schema . '.' . $name . ' ORDER BY ID';
    $q = $pdo->query($sql);
    $hash = hash_init('sha256');
    $count = 0;
    while ($row = $q->fetch(PDO::FETCH_NUM)) {
        foreach ($row as &$value) if (is_resource($value)) $value = stream_get_contents($value);
        unset($value);
        hash_update($hash, serialize($row) . "\n");
        $count++;
    }
    return ['rows' => $count, 'sha256' => hash_final($hash)];
};
$add = static function (string $name) use ($pdo, $schema, $definitions): void {
    $parts = [];
    foreach ($definitions as $column => $definition) $parts[] = $column . ' ' . $definition;
    $pdo->exec('ALTER TABLE ' . $schema . '.' . $name . ' ADD (' . implode(',', $parts) . ')');
};
$validateDefaults = static function (string $name) use ($pdo, $schema, $definitions, $nullable): void {
    $invalid = [];
    foreach ($definitions as $column => $definition) {
        if (in_array($column, $nullable, true)) $invalid[] = $column . ' IS NOT NULL';
        elseif (strpos($definition, 'VARCHAR') === 0) $invalid[] = '(' . $column . " IS NULL OR " . $column . "<>'')";
        else $invalid[] = '(' . $column . ' IS NULL OR ' . $column . '<>0)';
    }
    $count = (int)$pdo->query('SELECT COUNT(*) FROM ' . $schema . '.' . $name
        . ' WHERE ' . implode(' OR ', $invalid))->fetchColumn();
    if ($count !== 0) throw new RuntimeException('New columns have unexpected values');
};
$setNotNull = static function (string $name) use ($pdo, $schema, $definitions, $nullable): void {
    foreach ($definitions as $column => $definition) {
        if (!in_array($column, $nullable, true)) {
            $pdo->exec('ALTER TABLE ' . $schema . '.' . $name . ' ALTER COLUMN '
                . $column . ' SET NOT NULL');
        }
    }
};
$metadata = $columns($table);
if (!$metadata) throw new RuntimeException('Approved test table not found');
$names = array_map('strtoupper', array_column($metadata, 'column_name'));
$missing = array_values(array_diff(array_keys($definitions), $names));
if ($action === 'preflight') {
    $counts = [];
    foreach (['USER_INDEXES' => 'indexes', 'USER_CONSTRAINTS' => 'constraints',
        'USER_TRIGGERS' => 'triggers'] as $view => $label) {
        $q = $pdo->prepare('SELECT COUNT(*) FROM ' . $view . ' WHERE TABLE_NAME=?');
        $q->execute([$table]); $counts[$label] = (int)$q->fetchColumn();
    }
    echo json_encode(['action' => $action, 'columns' => count($names),
        'missing' => $missing, 'objects' => $counts,
        'data' => $fingerprint($table, $names)]) . PHP_EOL;
    return;
}
if ($action === 'probe') {
    $probe = 'CDXMO_PROBE_' . strtoupper(bin2hex(random_bytes(5)));
    $created = false;
    try {
        $pdo->exec('CREATE TABLE ' . $schema . '.' . $probe
            . ' (ID INT PRIMARY KEY,PAY_PRICE DECIMAL(10,2))');
        $created = true;
        $pdo->exec('INSERT INTO ' . $schema . '.' . $probe . ' VALUES (1,0.80)');
        $pdo->exec('INSERT INTO ' . $schema . '.' . $probe . ' VALUES (2,100.00)');
        $before = $fingerprint($probe, ['ID', 'PAY_PRICE']);
        $add($probe); $validateDefaults($probe); $setNotNull($probe);
        $after = $fingerprint($probe, ['ID', 'PAY_PRICE']);
        if ($before !== $after) throw new RuntimeException('Probe original data changed');
        $probeMeta = $columns($probe);
        foreach ($probeMeta as $column) {
            $name = strtoupper($column['column_name']);
            if (!isset($definitions[$name])) continue;
            $expected = in_array($name, $nullable, true) ? 'Y' : 'N';
            if ($column['nullable'] !== $expected) throw new RuntimeException('Probe NULL contract changed');
        }
        echo json_encode(['action' => 'probe', 'status' => 'passed',
            'new_columns' => 11, 'original_data_unchanged' => true]) . PHP_EOL;
    } finally {
        if ($created) $pdo->exec('DROP TABLE ' . $schema . '.' . $probe);
    }
    return;
}
$objectNames = static function () use ($pdo, $table): array {
    $result = [];
    foreach (['USER_INDEXES' => 'INDEX_NAME', 'USER_CONSTRAINTS' => 'CONSTRAINT_NAME'] as $view => $column) {
        $q = $pdo->prepare('SELECT ' . $column . ' FROM ' . $view . ' WHERE TABLE_NAME=? ORDER BY ' . $column);
        $q->execute([$table]); $result[$view] = $q->fetchAll(PDO::FETCH_COLUMN);
    }
    return $result;
};
$runTool = static function (string $tool, array $options, string $dir, string $label): void {
    $user = (string)getenv('POC_USER'); $password = (string)getenv('POC_PASSWORD');
    $host = (string)getenv('POC_HOST'); $port = (string)getenv('POC_PORT');
    if (preg_match('/["\r\n]/', $user . $password) || !preg_match('/^[a-zA-Z0-9.]+$/D', $host)
        || !ctype_digit($port)) throw new RuntimeException('Unsupported private export parameter');
    $parameterFile = $dir . '/' . $label . '.par';
    $text = '';
    foreach ($options as $key => $value) $text .= $key . '=' . $value . "\n";
    file_put_contents($parameterFile, $text); chmod($parameterFile, 0600);
    try {
        $process = proc_open('setsid -w ' . escapeshellarg('/home/dmdba/dmdbms/bin/' . $tool)
            . ' USERID=' . escapeshellarg('"' . $user . '"@' . $host . ':' . $port)
            . ' PARFILE=' . escapeshellarg($parameterFile), [
                0 => ['pipe', 'r'],
                1 => ['file', $dir . '/' . $label . '.stdout', 'w'],
                2 => ['file', $dir . '/' . $label . '.stderr', 'w'],
            ], $pipes, $dir);
        if (!is_resource($process)) throw new RuntimeException('Cannot start private backup tool');
        fwrite($pipes[0], $password . "\n"); fclose($pipes[0]);
        $exit = proc_close($process);
        if ($exit !== 0) throw new RuntimeException('Private ' . $tool . ' failed; exit=' . $exit);
    } finally {
        unlink($parameterFile);
    }
};
if ($action === 'backup') {
    if (count($missing) !== 11) throw new RuntimeException('Backup expects the original 11-column gap');
    umask(0077);
    $dir = '/dmdata/zhct_compat/codex_backups/meal_order_' . date('Ymd_His') . '_' . bin2hex(random_bytes(4));
    if (!mkdir($dir, 0700, true)) throw new RuntimeException('Cannot create private backup directory');
    $snapshot = $fingerprint($table, $names);
    $objects = $objectNames();
    $q = $pdo->prepare('CALL SP_TABLEDEF(?, ?)'); $q->execute([$schema, $table]);
    file_put_contents($dir . '/table-before.sql', (string)$q->fetchColumn());
    file_put_contents($dir . '/columns-before.json', json_encode($metadata, JSON_PRETTY_PRINT));
    $common = ['FILE' => 'meal_order.dmp', 'DIRECTORY' => $dir,
        'TABLES' => '(' . $schema . '.' . $table . ')', 'PARALLEL' => '1',
        'TABLE_PARALLEL' => '1', 'GRANTS' => 'N', 'ROWS' => 'Y', 'DUMMY' => 'N'];
    $runTool('dexp', $common + ['LOG' => 'export.log', 'INDEXES' => 'Y',
        'CONSTRAINTS' => 'Y', 'TRIGGERS' => 'Y'], $dir, 'export');
    $dump = $dir . '/meal_order.dmp';
    if (!is_file($dump) || filesize($dump) < 1024) throw new RuntimeException('Backup dump missing or empty');
    $restoreTable = 'CDXMO_RESTORE_' . strtoupper(bin2hex(random_bytes(5)));
    $verified = false;
    try {
        $runTool('dimp', $common + ['LOG' => 'restore.log',
            'REMAP_TABLE' => $schema . '.' . $table . ':' . $restoreTable,
            'INDEXES' => 'N', 'CONSTRAINTS' => 'N', 'TRIGGERS' => 'N',
            'TABLE_EXISTS_ACTION' => 'SKIP'], $dir, 'restore');
        $restored = $fingerprint($restoreTable, $names);
        if ($restored !== $snapshot) throw new RuntimeException('Restored backup differs from original data');
        if ($fingerprint($table, $names) !== $snapshot || $objectNames() !== $objects) {
            throw new RuntimeException('Original table changed during backup; do not apply migration');
        }
        $verified = true;
    } finally {
        $q = $pdo->prepare('SELECT COUNT(*) FROM USER_TABLES WHERE TABLE_NAME=?');
        $q->execute([$restoreTable]);
        if ((int)$q->fetchColumn() === 1) $pdo->exec('DROP TABLE ' . $schema . '.' . $restoreTable);
    }
    $manifest = ['schema' => $schema, 'table' => $table, 'original_columns' => $names,
        'snapshot' => $snapshot, 'objects' => $objects, 'verified_restore' => $verified,
        'dump_sha256' => hash_file('sha256', $dump), 'dump_bytes' => filesize($dump),
        'created_at' => date(DATE_ATOM)];
    file_put_contents($dir . '/manifest.json', json_encode($manifest, JSON_PRETTY_PRINT));
    echo json_encode(['action' => 'backup', 'status' => 'passed', 'verified_restore' => $verified,
        'rows' => $snapshot['rows'], 'dump_bytes' => filesize($dump),
        'manifest' => $dir . '/manifest.json']) . PHP_EOL;
    return;
}
if ($action === 'apply') {
    $path = (string)getenv('POC_BACKUP_MANIFEST');
    if (strpos($path, '/dmdata/zhct_compat/codex_backups/meal_order_') !== 0 || !is_file($path)) {
        throw new RuntimeException('Verified private backup manifest is required');
    }
    $manifest = json_decode((string)file_get_contents($path), true);
    if (empty($manifest['verified_restore']) || $manifest['schema'] !== $schema
        || $manifest['table'] !== $table || count($missing) !== 11
        || hash_file('sha256', dirname($path) . '/meal_order.dmp') !== $manifest['dump_sha256']) {
        throw new RuntimeException('Backup manifest or current schema contract mismatch');
    }
    if ($fingerprint($table, $manifest['original_columns']) !== $manifest['snapshot']
        || $objectNames() !== $manifest['objects']) {
        throw new RuntimeException('Original table changed since verified backup');
    }
    $started = microtime(true);
    $add($table); $validateDefaults($table); $setNotNull($table);
    $after = $columns($table);
    if (count($after) !== count($manifest['original_columns']) + 11) {
        throw new RuntimeException('Unexpected column count after repair');
    }
    foreach ($after as $column) {
        $name = strtoupper($column['column_name']);
        if (!isset($definitions[$name])) continue;
        if ($column['nullable'] !== (in_array($name, $nullable, true) ? 'Y' : 'N')) {
            throw new RuntimeException('Repaired column NULL contract mismatch');
        }
        if (strpos($definitions[$name], 'DECIMAL') === 0
            && ((int)$column['data_precision'] !== 10 || (int)$column['data_scale'] !== 2)) {
            throw new RuntimeException('Repaired DECIMAL precision mismatch');
        }
    }
    $final = $fingerprint($table, $manifest['original_columns']);
    if ($final !== $manifest['snapshot'] || $objectNames() !== $manifest['objects']) {
        throw new RuntimeException('Original data or indexes changed after repair');
    }
    $result = ['action' => 'apply', 'status' => 'passed', 'columns_before' => count($names),
        'columns_after' => count($after), 'rows' => $final['rows'],
        'original_data_sha256' => $final['sha256'], 'original_objects_unchanged' => true,
        'elapsed_seconds' => round(microtime(true) - $started, 3)];
    file_put_contents(dirname($path) . '/applied.json', json_encode($result, JSON_PRETTY_PRINT));
    echo json_encode($result) . PHP_EOL;
    return;
}
throw new RuntimeException('Unknown repair stage');