<?php
declare(strict_types=1);

// One absent table in the user-approved Store local development MySQL only.
// No existing table is altered, and no data is inserted or deleted.
$configFile = realpath(__DIR__ . '/../../../../zhctproject/store/application/config/local/database.php');
if (!$configFile || !is_file($configFile)) {
    throw new RuntimeException('Store local config unavailable');
}
$config = require $configFile;
foreach (['hostname', 'database', 'username', 'password'] as $field) {
    if ((string)($config[$field] ?? '') === '') {
        throw new RuntimeException('Store local connection incomplete');
    }
}
$port = (int)($config['hostport'] ?? 3306);
if ($port !== 3306) {
    throw new RuntimeException('Unexpected local MySQL port');
}
$table = 'ydy_agent_feedback_log';
$ddl = <<<'SQL'
CREATE TABLE `ydy_agent_feedback_log` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增ID',
  `conversation_log_id` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT '问答日志ID',
  `feedback` varchar(32) NOT NULL DEFAULT '' COMMENT '反馈 helpful/unhelpful',
  `user_uuid` varchar(64) NOT NULL DEFAULT '' COMMENT '用户UUID',
  `business` varchar(64) NOT NULL DEFAULT '' COMMENT '商户标识',
  `create_time` datetime NOT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`),
  KEY `idx_conversation` (`conversation_log_id`),
  KEY `idx_feedback_time` (`feedback`, `create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='PC后台智能助手反馈日志'
SQL;
$start = microtime(true);
try {
    $pdo = new PDO('mysql:host=' . $config['hostname'] . ';port=' . $port
        . ';dbname=' . $config['database'] . ';charset=utf8mb4', $config['username'],
        $config['password'], [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_TIMEOUT => 5, PDO::ATTR_EMULATE_PREPARES => false]);
    $version = (string)$pdo->query('SELECT VERSION()')->fetchColumn();
    $selected = (string)$pdo->query('SELECT DATABASE()')->fetchColumn();
    if (strpos($version, '5.6.') !== 0 || $selected !== (string)$config['database']) {
        throw new RuntimeException('Not the configured local MySQL 5.6 schema');
    }
    $check = $pdo->prepare('SELECT TABLE_TYPE FROM information_schema.TABLES'
        . ' WHERE TABLE_SCHEMA=? AND TABLE_NAME=?');
    $check->execute([$selected, $table]);
    $existingType = $check->fetchColumn();
    $verifyOnly = getenv('POC_VERIFY_DEV_FEEDBACK_DDL') === '1';
    $apply = getenv('POC_APPLY_DEV_FEEDBACK_DDL') === '1';
    if ($verifyOnly && $apply) {
        throw new RuntimeException('Apply and verify-only modes are mutually exclusive');
    }
    if ($verifyOnly) {
        if ($existingType !== 'BASE TABLE') {
            throw new RuntimeException('Expected new table is unavailable for verification');
        }
    } else {
        if ($existingType !== false) {
            throw new RuntimeException('Target object already exists; no DDL applied');
        }
        $before = $pdo->prepare('SELECT COUNT(*) FROM information_schema.COLUMNS'
            . ' WHERE TABLE_SCHEMA=? AND TABLE_NAME=?');
        $before->execute([$selected, $table]);
        if ((int)$before->fetchColumn() !== 0) {
            throw new RuntimeException('Target metadata is not empty');
        }
    }
    $source = $pdo->prepare('SELECT COUNT(*) FROM information_schema.TABLES'
        . ' WHERE TABLE_SCHEMA=? AND TABLE_NAME=? AND TABLE_TYPE=?');
    $source->execute([$selected, 'ydy_agent_conversation_log', 'BASE TABLE']);
    if ((int)$source->fetchColumn() !== 1) {
        throw new RuntimeException('Expected local conversation table absent');
    }
    $manifest = ['environment' => 'store-local-development-mysql56',
        'target' => $table, 'preexisting_target_objects' => $existingType === false ? 0 : 1,
        'ddl_sha256' => hash('sha256', $ddl), 'apply' => $apply,
        'verify_only' => $verifyOnly];
    if (!$apply && !$verifyOnly) {
        $manifest['status'] = 'preflight-pass';
        echo json_encode($manifest, JSON_UNESCAPED_SLASHES), PHP_EOL;
        exit(0);
    }

    if ($apply) {
        $pdo->exec($ddl);
    }
    $meta = $pdo->prepare('SELECT ENGINE,TABLE_COLLATION FROM information_schema.TABLES'
        . ' WHERE TABLE_SCHEMA=? AND TABLE_NAME=? AND TABLE_TYPE=?');
    $meta->execute([$selected, $table, 'BASE TABLE']);
    $actual = $meta->fetch(PDO::FETCH_ASSOC);
    $columns = $pdo->prepare('SELECT COLUMN_NAME FROM information_schema.COLUMNS'
        . ' WHERE TABLE_SCHEMA=? AND TABLE_NAME=? ORDER BY ORDINAL_POSITION');
    $columns->execute([$selected, $table]);
    $names = $columns->fetchAll(PDO::FETCH_COLUMN);
    $indexes = $pdo->prepare('SELECT INDEX_NAME,COLUMN_NAME,SEQ_IN_INDEX,NON_UNIQUE'
        . ' FROM information_schema.STATISTICS WHERE TABLE_SCHEMA=? AND TABLE_NAME=?'
        . ' ORDER BY INDEX_NAME,SEQ_IN_INDEX');
    $indexes->execute([$selected, $table]);
    $actualIndexes = $indexes->fetchAll(PDO::FETCH_ASSOC);
    $expectedIndexes = [
        ['PRIMARY', 'id', 1, 0],
        ['idx_conversation', 'conversation_log_id', 1, 1],
        ['idx_feedback_time', 'feedback', 1, 1],
        ['idx_feedback_time', 'create_time', 2, 1],
    ];
    $indexShape = array_map(static function (array $row): array {
        return [$row['INDEX_NAME'], $row['COLUMN_NAME'], (int)$row['SEQ_IN_INDEX'],
            (int)$row['NON_UNIQUE']];
    }, $actualIndexes);
    $sortIndexes = static function (array $a, array $b): int {
        return strcmp($a[0], $b[0]) ?: ($a[2] <=> $b[2]);
    };
    usort($indexShape, $sortIndexes);
    usort($expectedIndexes, $sortIndexes);
    $rows = (int)$pdo->query('SELECT COUNT(*) FROM `ydy_agent_feedback_log`')->fetchColumn();
    if (!$actual || $actual['ENGINE'] !== 'InnoDB'
        || $actual['TABLE_COLLATION'] !== 'utf8mb4_general_ci'
        || $names !== ['id', 'conversation_log_id', 'feedback', 'user_uuid',
            'business', 'create_time'] || $indexShape !== $expectedIndexes || $rows !== 0) {
        throw new RuntimeException('Created table differs from the reviewed definition');
    }
    $manifest['status'] = $verifyOnly ? 'ddl-readonly-verified' : 'ddl-created-and-verified';
    $manifest['columns'] = count($names);
    $manifest['index_columns'] = count($indexShape);
    $manifest['rows'] = $rows;
    $manifest['elapsed_ms'] = (int)round((microtime(true) - $start) * 1000);
    echo json_encode($manifest, JSON_UNESCAPED_SLASHES), PHP_EOL;
} catch (Throwable $error) {
    // PDO messages can include connection values; keep them out of task output.
    echo json_encode(['status' => 'stopped', 'class' => get_class($error),
        'code' => (string)$error->getCode(),
        'elapsed_ms' => (int)round((microtime(true) - $start) * 1000)]), PHP_EOL;
    exit(1);
}