<?php
declare(strict_types=1);

// Read-only metadata audit. No business row fetch, DML, DDL, or HTTP call.
$stage = 'preflight';
set_exception_handler(static function (Throwable $error) use (&$stage): void {
    echo json_encode(['status' => 'redacted_error', 'stage' => $stage,
        'class' => get_class($error), 'code' => (string)$error->getCode(),
        'dml' => false, 'ddl' => false], JSON_UNESCAPED_SLASHES), PHP_EOL;
    exit(2);
});
$target = (string)getenv('CDX_RECHARGE_SCHEMA_TARGET');
if (!in_array($target, ['MYSQL_LOCAL', 'DM_AI'], true)
    || getenv('CDX_RECHARGE_SCHEMA_READONLY') !== 'APPROVED_READONLY_V1'
    || !hash_equals((string)hash_file('sha256', __FILE__),
        (string)getenv('CDX_RECHARGE_SCHEMA_SHA'))) {
    echo json_encode(['status' => 'default_deny', 'database_connected' => false,
        'dml' => false, 'ddl' => false]), PHP_EOL;
    exit(78);
}
$stage = 'approved_target';
if ($target === 'MYSQL_LOCAL') {
    $path = '/Users/wiscourper/Documents/workspace/zhct/zhctproject/ai_api/.env.local';
    if (realpath($path) !== $path) throw new RuntimeException('Code local config missing');
    $ini = parse_ini_file($path, true, INI_SCANNER_RAW);
    if (!is_array($ini) || !isset($ini['DATABASE'], $ini['DATABASE_ZHCT'])
        || strtolower((string)($ini['DATABASE']['TYPE'] ?? '')) !== 'mysql') {
        throw new RuntimeException('Code local MySQL config mismatch');
    }
    $identity = [];
    foreach (['DATABASE', 'DATABASE_ZHCT'] as $section) {
        foreach (['HOSTNAME', 'HOSTPORT', 'DATABASE', 'USERNAME'] as $key) {
            $identity[] = (string)($ini[$section][$key] ?? '');
        }
    }
    if (in_array('', $identity, true)
        || !hash_equals('d908a48732b1a4874c5d8427ab60186eb8272aa4e0c5af34716eadafee482c88',
            hash('sha256', json_encode($identity, JSON_UNESCAPED_SLASHES)))) {
        throw new RuntimeException('Local development identity changed');
    }
    $config = $ini['DATABASE'];
    if (empty($config['PASSWORD'])) throw new RuntimeException('Missing local credential');
    $schema = (string)$config['DATABASE'];
    $pdo = new PDO('mysql:host=' . $config['HOSTNAME'] . ';port=' . $config['HOSTPORT']
        . ';dbname=' . $schema . ';charset=utf8', (string)$config['USERNAME'],
        (string)$config['PASSWORD'], [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_CASE => PDO::CASE_LOWER, PDO::ATTR_TIMEOUT => 5]);
    if ((string)$pdo->query('SELECT DATABASE()')->fetchColumn() !== $schema) {
        throw new RuntimeException('Connected local schema changed');
    }
    $table = 'yoshop_recharge_order';
    $base = [$schema, $table];
} else {
    if (PHP_VERSION_ID < 70400
        || !hash_equals('bcf83be7714eaec2734fb7dccddabc18c83c34b4201ee567f5044fb4bd54f526',
            hash('sha256', php_uname('n')))) {
        throw new RuntimeException('Approved DM host/PHP mismatch');
    }
    $pid = 53772;
    $cmdline = (string)@file_get_contents('/proc/' . $pid . '/cmdline');
    $raw = (string)@file_get_contents('/proc/' . $pid . '/environ');
    if (strpos($cmdline, '127.0.0.1:18102') === false
        || strpos($cmdline, '/tmp/zhct-ai-http-20260929.3XDj3w/public') === false
        || $raw === '' || strpos($raw, 'PHP_CLI_SERVER_WORKERS=') !== false) {
        throw new RuntimeException('Isolated AI HTTP process changed');
    }
    $config = [];
    foreach (explode("\0", $raw) as $entry) {
        if (strpos($entry, 'POC_') !== 0) continue;
        $equal = strpos($entry, '=');
        if ($equal !== false) {
            $config[substr($entry, 0, $equal)] = substr($entry, $equal + 1);
        }
    }
    foreach (['POC_DIALECT' => 'dm', 'POC_EXTERNAL_STUBS' => '1',
        'POC_AI_HOST' => '127.0.0.1', 'POC_AI_PORT' => '5238',
        'POC_AI_SCHEMA' => 'AIZHCT_APP', 'POC_AI_USER' => 'AIZHCT_APP'] as $key => $value) {
        if (($config[$key] ?? null) !== $value) {
            throw new RuntimeException('Nonproduction AI target changed');
        }
    }
    if (empty($config['POC_AI_PASSWORD'])) throw new RuntimeException('AI credential missing');
    $lines = [];
    $exit = 0;
    @exec('/usr/sbin/ss -ltnp 2>/dev/null', $lines, $exit);
    $listener = false;
    if ($exit === 0) foreach ($lines as $line) {
        if (preg_match('/\*:5238\s+.*users:\(\("dmserver",pid=(\d+),/', $line, $match)) {
            $exe = @readlink('/proc/' . $match[1] . '/exe');
            $listener = is_string($exe) && basename($exe) === 'dmserver';
            break;
        }
    }
    if (!$listener) throw new RuntimeException('DM5238 listener not local');
    $pdo = new PDO('dm:host=127.0.0.1;port=5238;schema=AIZHCT_APP',
        (string)$config['POC_AI_USER'], (string)$config['POC_AI_PASSWORD'],
        [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_CASE => PDO::CASE_LOWER, PDO::ATTR_TIMEOUT => 5]);
    $schema = (string)$pdo->query("SELECT SYS_CONTEXT('USERENV','CURRENT_SCHEMA') FROM DUAL")
        ->fetchColumn();
    $user = (string)$pdo->query('SELECT USER FROM DUAL')->fetchColumn();
    if (strtoupper($schema) !== 'AIZHCT_APP' || strtoupper($user) !== 'AIZHCT_APP') {
        throw new RuntimeException('Connected AI schema/user changed');
    }
    $table = 'YOSHOP_RECHARGE_ORDER';
    $base = [$table];
}
$stage = 'table_columns';
$columnsSql = $target === 'MYSQL_LOCAL'
    ? 'SELECT COLUMN_NAME,COLUMN_TYPE,DATA_TYPE,IS_NULLABLE,COLUMN_DEFAULT,EXTRA,'
        . ' COLUMN_KEY,NUMERIC_PRECISION,NUMERIC_SCALE,ORDINAL_POSITION'
        . ' FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=? AND TABLE_NAME=?'
        . ' ORDER BY ORDINAL_POSITION'
    : 'SELECT COLUMN_NAME,DATA_TYPE,DATA_LENGTH,DATA_PRECISION,DATA_SCALE,'
        . ' NULLABLE,DATA_DEFAULT,COLUMN_ID FROM USER_TAB_COLUMNS WHERE TABLE_NAME=?'
        . ' ORDER BY COLUMN_ID';
$stmt = $pdo->prepare($columnsSql);
$stmt->execute($base);
$columns = $stmt->fetchAll(PDO::FETCH_ASSOC);
if (!$columns || count($columns) > 200) throw new RuntimeException('Recharge table absent/broad');
$names = array_map(static function (array $row): string {
    return strtoupper((string)$row['column_name']);
}, $columns);
$targets = ['WITHDRAWABLE_AMOUNT', 'ACTIVITY_PRINCIPAL_AMOUNT'];
$anchors = ['ACTUAL_MONEY', 'RECHARG_BEFORE'];
$selected = [];
$anchorColumns = [];
$neighbors = [];
foreach ($columns as $index => $column) {
    $name = strtoupper((string)$column['column_name']);
    if (!in_array($name, $targets, true) && !in_array($name, $anchors, true)) continue;
    foreach ($column as $key => $value) {
        if (is_resource($value)) $column[$key] = stream_get_contents($value);
    }
    if (in_array($name, $anchors, true)) {
        $anchorColumns[$name] = $column;
        continue;
    }
    $selected[$name] = $column;
    $neighbors[$name] = ['previous' => $names[$index - 1] ?? null,
        'next' => $names[$index + 1] ?? null];
}
$stage = 'index_fk_trigger_metadata';
if ($target === 'MYSQL_LOCAL') {
    $stmt = $pdo->prepare('SELECT INDEX_NAME,COLUMN_NAME,SEQ_IN_INDEX,NON_UNIQUE'
        . ' FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA=? AND TABLE_NAME=?'
        . ' AND UPPER(COLUMN_NAME) IN (?,?) ORDER BY INDEX_NAME,SEQ_IN_INDEX');
    $stmt->execute([$schema, $table, $targets[0], $targets[1]]);
} else {
    $stmt = $pdo->prepare('SELECT ic.INDEX_NAME,ic.COLUMN_NAME,ic.COLUMN_POSITION,'
        . ' i.UNIQUENESS FROM USER_IND_COLUMNS ic JOIN USER_INDEXES i'
        . ' ON i.INDEX_NAME=ic.INDEX_NAME WHERE ic.TABLE_NAME=?'
        . ' AND ic.COLUMN_NAME IN (?,?) ORDER BY ic.INDEX_NAME,ic.COLUMN_POSITION');
    $stmt->execute([$table, $targets[0], $targets[1]]);
}
$indexes = $stmt->fetchAll(PDO::FETCH_ASSOC);
if ($target === 'MYSQL_LOCAL') {
    $stmt = $pdo->prepare('SELECT CONSTRAINT_NAME,COLUMN_NAME,REFERENCED_TABLE_NAME,'
        . ' REFERENCED_COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE'
        . ' WHERE TABLE_SCHEMA=? AND TABLE_NAME=? AND UPPER(COLUMN_NAME) IN (?,?)'
        . ' AND REFERENCED_TABLE_NAME IS NOT NULL');
    $stmt->execute([$schema, $table, $targets[0], $targets[1]]);
} else {
    $stmt = $pdo->prepare('SELECT c.CONSTRAINT_NAME,c.CONSTRAINT_TYPE,cc.COLUMN_NAME'
        . ' FROM USER_CONSTRAINTS c JOIN USER_CONS_COLUMNS cc'
        . ' ON cc.CONSTRAINT_NAME=c.CONSTRAINT_NAME WHERE c.TABLE_NAME=?'
        . ' AND cc.COLUMN_NAME IN (?,?)');
    $stmt->execute([$table, $targets[0], $targets[1]]);
}
$constraints = $stmt->fetchAll(PDO::FETCH_ASSOC);
$stmt = $pdo->prepare($target === 'MYSQL_LOCAL'
    ? 'SELECT COUNT(*) FROM INFORMATION_SCHEMA.TRIGGERS'
        . ' WHERE EVENT_OBJECT_SCHEMA=? AND EVENT_OBJECT_TABLE=?'
    : 'SELECT COUNT(*) FROM USER_TRIGGERS WHERE TABLE_NAME=?');
$stmt->execute($base);
$triggerCount = (int)$stmt->fetchColumn();
$stage = 'table_estimate';
if ($target === 'MYSQL_LOCAL') {
    $stmt = $pdo->prepare('SELECT TABLE_ROWS,DATA_LENGTH,INDEX_LENGTH'
        . ' FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA=? AND TABLE_NAME=?');
    $stmt->execute($base);
    $estimate = $stmt->fetch(PDO::FETCH_ASSOC) ?: [];
} else {
    $stmt = $pdo->prepare('SELECT NUM_ROWS FROM USER_TABLES WHERE TABLE_NAME=?');
    $stmt->execute($base);
    $estimate = ['num_rows_statistics' => $stmt->fetchColumn()];
    try {
        $stmt = $pdo->prepare('SELECT SUM(BYTES) FROM USER_SEGMENTS'
            . ' WHERE SEGMENT_NAME=? OR SEGMENT_NAME IN'
            . ' (SELECT INDEX_NAME FROM USER_INDEXES WHERE TABLE_NAME=?)');
        $stmt->execute([$table, $table]);
        $estimate['table_and_index_segment_bytes'] = $stmt->fetchColumn();
    } catch (Throwable $error) {
    $estimate['table_and_index_segment_bytes'] = 'unavailable';
    }
}
$stage = 'aggregate_counts_only';
if ($target === 'MYSQL_LOCAL') {
    $counts = $pdo->query('SELECT COUNT(*) AS ROW_COUNT,'
        . ' SUM(CASE WHEN WITHDRAWABLE_AMOUNT<>0 THEN 1 ELSE 0 END) AS WITHDRAWABLE_NONZERO,'
        . ' SUM(CASE WHEN ACTIVITY_PRINCIPAL_AMOUNT<>0 THEN 1 ELSE 0 END) AS ACTIVITY_NONZERO'
        . ' FROM yoshop_recharge_order')->fetch(PDO::FETCH_ASSOC) ?: [];
} else {
    $counts = ['row_count' => $pdo->query(
        'SELECT COUNT(*) FROM YOSHOP_RECHARGE_ORDER')->fetchColumn()];
}
echo json_encode(['status' => 'pass', 'target' => $target,
    'code_local_or_isolated_nonproduction' => true,
    'table_column_count' => count($columns),
    'column_name_sha256' => hash('sha256', implode(',', $names)),
    'column_names' => $names,
    'selected_columns' => $selected, 'selected_neighbors' => $neighbors,
    'anchor_columns' => $anchorColumns,
    'indexes_on_selected' => $indexes, 'constraints_on_selected' => $constraints,
    'table_trigger_count' => $triggerCount, 'table_estimate' => $estimate,
    'aggregate_counts_only' => $counts,
    'business_rows_fetched' => 0, 'dml' => false, 'ddl' => false],
    JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES) . PHP_EOL;