<?php

declare(strict_types=1);

// Read-only two-table metadata/count probe. Never reads existing question/answer/name values.
$engine = (string) getenv('CDX_AGENT_SCHEMA_ENGINE');
if (!in_array($engine, ['mysql', 'dm'], true)) {
    fwrite(STDERR, "Explicit mysql or dm required\n");
    exit(2);
}
$tables = ['agent_conversation_log', 'agent_feedback_log'];
try {
    if ($engine === 'mysql') {
        $path = realpath(__DIR__
            . '/../../../../../zhctproject/store/application/config/local/database.php');
        if (!$path || strpos($path, '/zhctproject/store/application/config/local/') === false) {
            throw new RuntimeException('Local config unavailable');
        }
        $config = require $path;
        $dsn = 'mysql:host=' . $config['hostname'] . ';port='
            . (int) ($config['hostport'] ?? 3306) . ';dbname='
            . $config['database'] . ';charset=utf8mb4';
        $pdo = new PDO($dsn, (string) $config['username'],
            (string) $config['password'], [
                PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
                PDO::ATTR_EMULATE_PREPARES => false,
                PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
                PDO::ATTR_TIMEOUT => 8,
            ]);
        $schema = (string) $config['database'];
        $version = (string) $pdo->query('SELECT VERSION()')->fetchColumn();
    } else {
        foreach (['POC_HOST', 'POC_PORT', 'POC_SCHEMA', 'POC_USER', 'POC_PASSWORD'] as $key) {
            if ((string) getenv($key) === '') {
                throw new RuntimeException('Approved DM runtime unavailable');
            }
        }
        $dsn = 'dm:host=' . getenv('POC_HOST') . ';port='
            . getenv('POC_PORT') . ';schema=' . getenv('POC_SCHEMA');
        $pdo = new PDO($dsn, (string) getenv('POC_USER'),
            (string) getenv('POC_PASSWORD'), [
                PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
                PDO::ATTR_EMULATE_PREPARES => false,
                PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
            ]);
        $schema = (string) getenv('POC_SCHEMA');
        $version = null;
    }
    $result = ['engine' => $engine, 'version' => $version, 'tables' => []];
    foreach ($tables as $table) {
        $actual = $engine === 'mysql' ? 'ydy_' . $table : 'YDY_' . strtoupper($table);
        if ($engine === 'mysql') {
            $statement = $pdo->prepare('SELECT COLUMN_NAME,COLUMN_TYPE,IS_NULLABLE,'
                . 'COLUMN_DEFAULT,EXTRA FROM information_schema.COLUMNS'
                . ' WHERE TABLE_SCHEMA=? AND TABLE_NAME=? ORDER BY ORDINAL_POSITION');
            $statement->execute([$schema, $actual]);
            $columns = $statement->fetchAll();
            $statement = $pdo->prepare('SELECT INDEX_NAME,NON_UNIQUE,SEQ_IN_INDEX,COLUMN_NAME'
                . ' FROM information_schema.STATISTICS WHERE TABLE_SCHEMA=? AND TABLE_NAME=?'
                . ' ORDER BY INDEX_NAME,SEQ_IN_INDEX');
            $statement->execute([$schema, $actual]);
            $indexes = $statement->fetchAll();
            $statement = $pdo->prepare('SELECT COUNT(*) AS C FROM information_schema.TRIGGERS'
                . ' WHERE TRIGGER_SCHEMA=? AND EVENT_OBJECT_TABLE=?');
            $statement->execute([$schema, $actual]);
            $triggers = (int) $statement->fetchColumn();
            $statement = $pdo->prepare('SELECT COUNT(*) AS C FROM information_schema.KEY_COLUMN_USAGE'
                . ' WHERE TABLE_SCHEMA=? AND TABLE_NAME=? AND REFERENCED_TABLE_NAME IS NOT NULL');
            $statement->execute([$schema, $actual]);
            $foreignKeys = (int) $statement->fetchColumn();
        } else {
            $statement = $pdo->prepare('SELECT COLUMN_NAME,DATA_TYPE,NULLABLE,DATA_DEFAULT'
                . ' FROM USER_TAB_COLUMNS WHERE TABLE_NAME=? ORDER BY COLUMN_ID');
            $statement->execute([$actual]);
            $columns = $statement->fetchAll();
            $statement = $pdo->prepare('SELECT i.INDEX_NAME,i.UNIQUENESS,ic.COLUMN_NAME,'
                . 'ic.COLUMN_POSITION FROM USER_INDEXES i JOIN USER_IND_COLUMNS ic'
                . ' ON i.INDEX_NAME=ic.INDEX_NAME WHERE i.TABLE_NAME=?'
                . ' ORDER BY i.INDEX_NAME,ic.COLUMN_POSITION');
            $statement->execute([$actual]);
            $indexes = $statement->fetchAll();
            $statement = $pdo->prepare('SELECT COUNT(*) AS C FROM USER_TRIGGERS WHERE TABLE_NAME=?');
            $statement->execute([$actual]);
            $triggers = (int) $statement->fetchColumn();
            $statement = $pdo->prepare("SELECT COUNT(*) AS C FROM USER_CONSTRAINTS"
                . " WHERE TABLE_NAME=? AND CONSTRAINT_TYPE='R'");
            $statement->execute([$actual]);
            $foreignKeys = (int) $statement->fetchColumn();
        }
        $counts = null;
        if ($columns) {
            $counts = [
                'total' => (int) $pdo->query('SELECT COUNT(*) FROM ' . $actual)->fetchColumn(),
            ];
            if ($table === 'agent_conversation_log') {
                $counts['cdxparagent_prefix'] = (int) $pdo->query('SELECT COUNT(*) FROM '
                    . $actual . " WHERE QUESTION LIKE 'CDXPARAGENT%'")->fetchColumn();
            } else {
                $conversation = $engine === 'mysql'
                    ? 'ydy_agent_conversation_log' : 'YDY_AGENT_CONVERSATION_LOG';
                $counts['cdxparagent_prefix'] = (int) $pdo->query('SELECT COUNT(*) FROM '
                    . $actual . ' f WHERE EXISTS (SELECT 1 FROM ' . $conversation
                    . " c WHERE c.ID=f.CONVERSATION_LOG_ID AND c.QUESTION LIKE 'CDXPARAGENT%')")
                    ->fetchColumn();
            }
        }
        $result['tables'][$table] = [
            'present' => count($columns) > 0,
            'columns' => $columns,
            'indexes' => $indexes,
            'trigger_count' => $triggers,
            'foreign_key_count' => $foreignKeys,
            'counts' => $counts,
        ];
    }
    echo json_encode($result, JSON_UNESCAPED_SLASHES | JSON_PRETTY_PRINT) . PHP_EOL;
} catch (Throwable $error) {
    // Never emit exception text; it may include DSN/config fields.
    echo json_encode(['engine' => $engine, 'status' => 'error',
        'class' => get_class($error), 'code' => $error->getCode()]) . PHP_EOL;
    exit(1);
}