#!/usr/bin/env php
<?php

declare(strict_types=1);

const DEFAULT_CONFIG = '/workspace/zhct/zhctproject/store/application/config/local/database.php';
const DEFAULT_MAX_ROWS = 50;
const HARD_MAX_ROWS = 200;
const MAX_SQL_BYTES = 20000;
const MAX_CELL_BYTES = 2000;
const DEFAULT_AUDIT_DIR = '/workspace/zhct/zhctprompt/work_wecom_customer_service/runtime/data_queries';

function fail(string $code, string $message, int $exitCode = 1): void
{
    fwrite(STDERR, json_encode([
        'ok' => false,
        'error' => $code,
        'message' => $message,
    ], JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES) . PHP_EOL);
    exit($exitCode);
}

function optionValue(array $argv, string $name, ?string $default = null): ?string
{
    $prefix = '--' . $name . '=';
    foreach ($argv as $argument) {
        if (strpos($argument, $prefix) === 0) {
            return substr($argument, strlen($prefix));
        }
    }
    return $default;
}

function validateSql(string $sql): string
{
    $sql = trim($sql);
    if ($sql === '') {
        fail('EMPTY_SQL', 'SQL 不能为空。');
    }
    if (strlen($sql) > MAX_SQL_BYTES) {
        fail('SQL_TOO_LARGE', 'SQL 超过允许长度。');
    }
    if (strpos($sql, "\0") !== false || strpos($sql, ';') !== false) {
        fail('MULTI_STATEMENT_BLOCKED', '只允许一条不带分号的 SQL。');
    }
    if (preg_match('/(?:--|#|\/\*)/', $sql)) {
        fail('COMMENT_BLOCKED', '只读查询不允许 SQL 注释。');
    }
    if (!preg_match('/^(SELECT|WITH|SHOW|DESCRIBE|DESC|EXPLAIN)\b/i', $sql)) {
        fail('WRITE_QUERY_BLOCKED', '只允许 SELECT、WITH、SHOW、DESCRIBE、DESC 或 EXPLAIN。');
    }

    $blocked = [
        'INSERT', 'UPDATE', 'DELETE', 'REPLACE', 'UPSERT', 'MERGE', 'ALTER',
        'DROP', 'CREATE', 'TRUNCATE', 'RENAME', 'GRANT', 'REVOKE', 'CALL',
        'HANDLER', 'LOAD', 'OUTFILE', 'DUMPFILE', 'INTO', 'SET', 'USE',
        'LOCK', 'UNLOCK', 'COMMIT', 'ROLLBACK', 'KILL', 'SLEEP', 'BENCHMARK',
        'GET_LOCK', 'RELEASE_LOCK', 'LOAD_FILE',
    ];
    $blockedPattern = '/\b(?:' . implode('|', $blocked) . ')\b/i';
    if (preg_match($blockedPattern, $sql) || preg_match('/\bFOR\s+UPDATE\b/i', $sql)) {
        fail('UNSAFE_SQL_BLOCKED', 'SQL 包含不允许的写入、锁定、导出或高风险操作。');
    }

    return $sql;
}

function connectionConfig(array $all, string $name): array
{
    $allowed = ['default', 'ai_zhct'];
    if (!in_array($name, $allowed, true)) {
        fail('CONNECTION_NOT_ALLOWED', '只允许 default 或 ai_zhct 连接。');
    }

    $base = [
        'hostname' => $all['hostname'] ?? '',
        'database' => $all['database'] ?? '',
        'username' => $all['username'] ?? '',
        'password' => $all['password'] ?? '',
        'port' => $all['port'] ?? 3306,
    ];
    if ($name !== 'default') {
        $base = array_merge($base, is_array($all[$name] ?? null) ? $all[$name] : []);
    }

    foreach (['hostname', 'database', 'username', 'password'] as $required) {
        if (!isset($base[$required]) || $base[$required] === '') {
            fail('CONNECTION_CONFIG_INVALID', '只读数据连接配置不完整。');
        }
    }
    return $base;
}

function maskCell(string $column, $value)
{
    if ($value === null || is_int($value) || is_float($value)) {
        return $value;
    }

    $text = (string) $value;
    if (preg_match('/password|passwd|secret|token|credential|salt/i', $column)) {
        return '[REDACTED]';
    }
    if (preg_match('/mobile|phone|tel/i', $column) && preg_match('/^(\d{3})\d+(\d{2})$/', $text, $match)) {
        return $match[1] . '****' . $match[2];
    }
    if (preg_match('/id_?card|identity|certificate/i', $column) && strlen($text) > 8) {
        return substr($text, 0, 3) . '********' . substr($text, -3);
    }
    if (strlen($text) > MAX_CELL_BYTES) {
        return substr($text, 0, MAX_CELL_BYTES) . '...[TRUNCATED]';
    }
    return $text;
}

function appendAudit(array $event): void
{
    $directory = getenv('ZHCT_READONLY_QUERY_AUDIT_DIR') ?: DEFAULT_AUDIT_DIR;
    if (!is_dir($directory) && !@mkdir($directory, 0770, true) && !is_dir($directory)) {
        return;
    }
    $event['ts'] = date('Y-m-d H:i:s');
    @file_put_contents(
        $directory . '/' . date('Ymd') . '.jsonl',
        json_encode($event, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES) . PHP_EOL,
        FILE_APPEND | LOCK_EX
    );
}

$connection = optionValue($argv, 'connection', 'default');
$maxRows = (int) optionValue($argv, 'max-rows', (string) DEFAULT_MAX_ROWS);
$maxRows = max(1, min(HARD_MAX_ROWS, $maxRows));
$sql = validateSql((string) stream_get_contents(STDIN));
$queryType = strtoupper((string) strtok(ltrim($sql), " \t\r\n"));
$auditBase = [
    'case_id' => getenv('WECOM_AIBOT_CASE_ID') ?: '',
    'connection' => $connection,
    'query_type' => $queryType,
    'sql_sha256' => hash('sha256', $sql),
];

if (in_array('--check-only', $argv, true)) {
    echo json_encode(['ok' => true, 'validated' => true], JSON_UNESCAPED_UNICODE) . PHP_EOL;
    exit(0);
}

$configPath = getenv('ZHCT_READONLY_DB_CONFIG') ?: DEFAULT_CONFIG;
if (!is_file($configPath)) {
    fail('CONNECTION_CONFIG_MISSING', '未找到本机数据库连接配置。');
}

$allConfig = include $configPath;
if (!is_array($allConfig)) {
    fail('CONNECTION_CONFIG_INVALID', '数据库连接配置格式无效。');
}
$config = connectionConfig($allConfig, (string) $connection);

$stage = 'connect';
$startedAt = microtime(true);
try {
    $dsn = sprintf(
        'mysql:host=%s;port=%d;dbname=%s;charset=utf8mb4',
        $config['hostname'],
        (int) $config['port'],
        $config['database']
    );
    $pdo = new PDO($dsn, $config['username'], $config['password'], [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        PDO::ATTR_EMULATE_PREPARES => false,
        PDO::MYSQL_ATTR_MULTI_STATEMENTS => false,
        PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => false,
        PDO::ATTR_TIMEOUT => 8,
    ]);
    try {
        $pdo->exec('SET SESSION MAX_EXECUTION_TIME=10000');
    } catch (PDOException $ignored) {
        // Older MySQL-compatible servers do not expose MAX_EXECUTION_TIME.
    }
    $stage = 'set_read_only';
    $pdo->exec('SET TRANSACTION READ ONLY');
    $stage = 'begin_transaction';
    $pdo->beginTransaction();
    $stage = 'execute_query';
    $statement = $pdo->query($sql);

    $rows = [];
    $truncated = false;
    while (($row = $statement->fetch()) !== false) {
        if (count($rows) >= $maxRows) {
            $truncated = true;
            break;
        }
        $masked = [];
        foreach ($row as $column => $value) {
            $masked[$column] = maskCell((string) $column, $value);
        }
        $rows[] = $masked;
    }
    $pdo->rollBack();
    appendAudit(array_merge($auditBase, [
        'ok' => true,
        'row_count' => count($rows),
        'truncated' => $truncated,
        'duration_ms' => (int) round((microtime(true) - $startedAt) * 1000),
    ]));

    echo json_encode([
        'ok' => true,
        'connection' => $connection,
        'database' => $config['database'],
        'row_count' => count($rows),
        'max_rows' => $maxRows,
        'truncated' => $truncated,
        'rows' => $rows,
    ], JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES) . PHP_EOL;
} catch (PDOException $error) {
    appendAudit(array_merge($auditBase, [
        'ok' => false,
        'stage' => $stage,
        'sqlstate' => (string) $error->getCode(),
        'duration_ms' => (int) round((microtime(true) - $startedAt) * 1000),
    ]));
    fail('READONLY_QUERY_FAILED', '只读查询执行失败，stage=' . $stage . '，SQLSTATE=' . $error->getCode());
}