<?php

declare(strict_types=1);

// Read-only local-development MySQL 5.6 metadata/count probe; no customer rows fetched.
$path = realpath(__DIR__
    . '/../../../../../zhctproject/store/application/config/local/database.php');
if (!$path || strpos($path, '/zhctproject/store/application/config/local/') === false) {
    fwrite(STDERR, "Approved local config unavailable\n");
    exit(2);
}
try {
    $config = require $path;
    $schema = (string) $config['database'];
    $dsn = 'mysql:host=' . $config['hostname'] . ';port='
        . (int) ($config['hostport'] ?? 3306) . ';dbname=' . $schema
        . ';charset=utf8mb4';
    $db = 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,
        ]);
    $version = (string) $db->query('SELECT VERSION()')->fetchColumn();
    $tables = ['ydy_dishes_custom_category', 'ydy_dishes',
        'ydy_user', 'ydy_role', 'ydy_user_role', 'ydy_role_menu'];
    $result = ['version' => $version, 'tables' => []];
    foreach ($tables as $table) {
        $stmt = $db->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');
        $stmt->execute([$schema, $table]);
        $columns = $stmt->fetchAll();
        $stmt = $db->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');
        $stmt->execute([$schema, $table]);
        $indexes = $stmt->fetchAll();
        $stmt = $db->prepare('SELECT COUNT(*) FROM information_schema.TRIGGERS'
            . ' WHERE TRIGGER_SCHEMA=? AND EVENT_OBJECT_TABLE=?');
        $stmt->execute([$schema, $table]);
        $triggers = (int) $stmt->fetchColumn();
        $stmt = $db->prepare('SELECT COUNT(*) FROM information_schema.KEY_COLUMN_USAGE'
            . ' WHERE TABLE_SCHEMA=? AND TABLE_NAME=?'
            . ' AND REFERENCED_TABLE_NAME IS NOT NULL');
        $stmt->execute([$schema, $table]);
        $foreignKeys = (int) $stmt->fetchColumn();
        $entry = [
            'present' => count($columns) > 0,
            'column_count' => count($columns),
            'indexes' => $indexes,
            'trigger_count' => $triggers,
            'foreign_key_count' => $foreignKeys,
            'total_count' => null,
        ];
        if ($table === 'ydy_dishes_custom_category') {
            $entry['columns'] = $columns;
        } else {
            $entry['column_names'] = array_column($columns, 'COLUMN_NAME');
            $entry['column_metadata_sha256'] = hash('sha256', json_encode($columns));
            $wanted = $table === 'ydy_dishes'
                ? ['id','name','custom_category_id','is_delete','is_del']
                : ['id','uuid','account','name','type','status','is_delete',
                    'user_uuid','role_uuid','menu_uuid','create_time','create_by'];
            $entry['relevant_columns'] = array_values(array_filter($columns,
                static function (array $row) use ($wanted): bool {
                    return in_array((string) $row['COLUMN_NAME'], $wanted, true);
                }));
        }
        if ($columns) {
            $entry['total_count'] = (int) $db->query('SELECT COUNT(*) FROM ' . $table)
                ->fetchColumn();
            if ($table === 'ydy_dishes_custom_category') {
                $entry['cdxparcat_prefix_count'] = (int) $db->query('SELECT COUNT(*) FROM '
                    . $table . " WHERE name LIKE 'CDXPARCAT%'")->fetchColumn();
            }
        }
        $result['tables'][$table] = $entry;
    }
    $menus = [
        'list_options' => '8E1F0A4D-AE59-4D44-A75D-4F98A6714B31',
        'add' => '317E43D6-DFB2-49BA-8A74-E19F84A9F8C8',
        'update' => '75F39502-E5E5-4163-9482-4DB7DAD2D15A',
        'delete' => 'E8D43259-FE89-43DE-AE49-91FBB040B932',
        'change_status' => '27AE08D7-A263-4797-8DCF-55DDF0560167',
    ];
    $stmt = $db->prepare('SELECT COUNT(*) FROM ydy_menu'
        . ' WHERE uuid=? AND LOWER(filter) LIKE ?');
    foreach ($menus as $name => $uuid) {
        $stmt->execute([$uuid, '%p/dishcustomcategory/%']);
        $result['menu_counts'][$name] = (int) $stmt->fetchColumn();
    }
    $result['synthetic_actor_count'] = (int) $db->query('SELECT COUNT(*) FROM ydy_user'
        . " WHERE account LIKE 'CDXPARCAT%' AND type=1 AND is_delete=0")
        ->fetchColumn();
    $result['synthetic_role_link_count'] = (int) $db->query('SELECT COUNT(*)'
        . ' FROM ydy_user u JOIN ydy_user_role ur ON ur.user_uuid=u.uuid'
        . ' JOIN ydy_role_menu rm ON rm.role_uuid=ur.role_uuid'
        . " WHERE u.account LIKE 'CDXPARCAT%' AND u.type=1 AND u.is_delete=0"
        . " AND rm.menu_uuid='8E1F0A4D-AE59-4D44-A75D-4F98A6714B31'")
        ->fetchColumn();
    echo json_encode($result, JSON_UNESCAPED_SLASHES | JSON_PRETTY_PRINT) . PHP_EOL;
} catch (Throwable $error) {
    echo json_encode(['status' => 'error', 'class' => get_class($error),
        'code' => $error->getCode()]) . PHP_EOL;
    exit(1);
}