<?php

declare(strict_types=1);

// DM catalog/count-only probe; never fetches existing category or dish contents.
foreach (['POC_HOST', 'POC_PORT', 'POC_SCHEMA', 'POC_USER', 'POC_PASSWORD'] as $key) {
    if ((string) getenv($key) === '') {
        fwrite(STDERR, "Approved non-production runtime required\n");
        exit(2);
    }
}
$dsn = 'dm:host=' . getenv('POC_HOST') . ';port=' . getenv('POC_PORT')
    . ';schema=' . getenv('POC_SCHEMA');
try {
    $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,
        ]);
    $tables = ['YDY_DISHES_CUSTOM_CATEGORY', 'YDY_DISHES'];
    $output = ['tables' => []];
    foreach ($tables as $table) {
        $stmt = $pdo->prepare('SELECT COLUMN_NAME,DATA_TYPE,NULLABLE,DATA_DEFAULT'
            . ' FROM USER_TAB_COLUMNS WHERE TABLE_NAME=? ORDER BY COLUMN_ID');
        $stmt->execute([$table]);
        $columns = $stmt->fetchAll();
        $stmt = $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');
        $stmt->execute([$table]);
        $indexes = $stmt->fetchAll();
        $stmt = $pdo->prepare("SELECT COUNT(*) FROM USER_CONSTRAINTS"
            . " WHERE TABLE_NAME=? AND CONSTRAINT_TYPE='R'");
        $stmt->execute([$table]);
        $foreignKeys = (int) $stmt->fetchColumn();
        $stmt = $pdo->prepare('SELECT COUNT(*) FROM USER_TRIGGERS WHERE TABLE_NAME=?');
        $stmt->execute([$table]);
        $triggers = (int) $stmt->fetchColumn();
        $entry = [
            'present' => count($columns) > 0,
            'column_count' => count($columns),
            'indexes' => $indexes,
            'foreign_key_count' => $foreignKeys,
            'trigger_count' => $triggers,
            'total_count' => null,
        ];
        if ($table === 'YDY_DISHES_CUSTOM_CATEGORY') {
            $entry['columns'] = $columns;
        } else {
            $entry['column_names'] = array_map(static function (array $row): string {
                return (string) $row['COLUMN_NAME'];
            }, $columns);
            $entry['column_metadata_sha256'] = hash('sha256', json_encode($columns));
            $entry['relevant_columns'] = array_values(array_filter($columns,
                static function (array $row): bool {
                    return in_array((string) $row['COLUMN_NAME'],
                        ['ID', 'NAME', 'CUSTOM_CATEGORY_ID', 'IS_DELETE', 'IS_DEL'], true);
                }));
        }
        if ($columns) {
            $entry['total_count'] = (int) $pdo->query('SELECT COUNT(*) FROM ' . $table)
                ->fetchColumn();
            if ($table === 'YDY_DISHES_CUSTOM_CATEGORY') {
                $entry['cdxparcat_prefix_count'] = (int) $pdo->query('SELECT COUNT(*) FROM '
                    . $table . " WHERE NAME LIKE 'CDXPARCAT%'")->fetchColumn();
            }
        }
        $output['tables'][$table] = $entry;
    }
    $menuUuids = [
        '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',
    ];
    $output['menu_counts'] = [];
    $menu = $pdo->prepare('SELECT COUNT(*) FROM YDY_MENU WHERE UUID=? AND LOWER(FILTER) LIKE ?');
    foreach ($menuUuids as $name => $uuid) {
        $menu->execute([$uuid, '%p/dishcustomcategory/%']);
        $output['menu_counts'][$name] = (int) $menu->fetchColumn();
    }
    $output['synthetic_actor_count'] = (int) $pdo->query('SELECT COUNT(*) FROM YDY_USER'
        . " WHERE ACCOUNT LIKE 'CDXPARCAT%' AND TYPE=1 AND IS_DELETE=0")
        ->fetchColumn();
    $output['synthetic_role_link_count'] = (int) $pdo->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 IN ('"
        . implode("','", array_values($menuUuids)) . "')")
        ->fetchColumn();
    echo json_encode($output, 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);
}