<?php

namespace classes;

/**
 * Ensures additive department edge gateway tables exist.
 *
 * The legacy PHP stack has no centralized migration runner, so this bootstrap
 * must be safe to call from runtime flows and tests.
 */
class edge_gateway_schema_bootstrap
{
    private static bool $initialized = false;

    public static function ensureTables(): void
    {
        if (self::$initialized) {
            return;
        }

        $pdo = self::pdo();
        $queries = [
            "CREATE TABLE IF NOT EXISTS edge_gateways (
                id INT AUTO_INCREMENT PRIMARY KEY,
                department_id INT NOT NULL,
                label VARCHAR(255) NOT NULL,
                hostname VARCHAR(255) NULL,
                agent_token_hash VARCHAR(255) NOT NULL,
                status VARCHAR(32) NOT NULL DEFAULT 'PENDING',
                transport_mode VARCHAR(32) NOT NULL DEFAULT 'gateway',
                release_channel VARCHAR(32) NOT NULL DEFAULT 'stable',
                installed_version VARCHAR(64) NULL,
                target_version VARCHAR(64) NULL,
                last_heartbeat_at DATETIME NULL,
                last_seen_ip VARCHAR(64) NULL,
                discovery_status VARCHAR(32) NOT NULL DEFAULT 'PENDING',
                is_primary TINYINT(1) NOT NULL DEFAULT 1,
                metadata_json JSON NULL,
                created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
                deleted_at TIMESTAMP NULL DEFAULT NULL,
                INDEX idx_edge_gateways_department_status (department_id, status),
                INDEX idx_edge_gateways_department_primary (department_id, is_primary),
                INDEX idx_edge_gateways_last_heartbeat (last_heartbeat_at)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",

            "CREATE TABLE IF NOT EXISTS edge_gateway_claim_tokens (
                id INT AUTO_INCREMENT PRIMARY KEY,
                department_id INT NOT NULL,
                label VARCHAR(255) NULL,
                token_hash VARCHAR(255) NOT NULL,
                created_by INT NULL,
                expires_at DATETIME NOT NULL,
                used_at DATETIME NULL,
                metadata_json JSON NULL,
                created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
                deleted_at TIMESTAMP NULL DEFAULT NULL,
                INDEX idx_edge_gateway_claim_tokens_department (department_id),
                INDEX idx_edge_gateway_claim_tokens_expires (expires_at),
                INDEX idx_edge_gateway_claim_tokens_used (used_at)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",

            "CREATE TABLE IF NOT EXISTS edge_gateway_device_inventory (
                id INT AUTO_INCREMENT PRIMARY KEY,
                gateway_id INT NOT NULL,
                device_id VARCHAR(255) NOT NULL,
                local_ip VARCHAR(64) NULL,
                model VARCHAR(255) NULL,
                channel_count INT NOT NULL DEFAULT 1,
                capabilities_json JSON NULL,
                online TINYINT(1) NOT NULL DEFAULT 0,
                last_seen_at DATETIME NULL,
                metadata_json JSON NULL,
                created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
                deleted_at TIMESTAMP NULL DEFAULT NULL,
                UNIQUE KEY uniq_edge_gateway_inventory_device (gateway_id, device_id),
                INDEX idx_edge_gateway_inventory_gateway (gateway_id),
                INDEX idx_edge_gateway_inventory_ip (local_ip)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",

            "CREATE TABLE IF NOT EXISTS edge_gateway_relay_bindings (
                id INT AUTO_INCREMENT PRIMARY KEY,
                gateway_id INT NOT NULL,
                department_id INT NOT NULL,
                relay_id VARCHAR(255) NOT NULL,
                device_id VARCHAR(255) NOT NULL,
                local_ip VARCHAR(64) NULL,
                channel INT NOT NULL DEFAULT 0,
                fallback_mode VARCHAR(32) NOT NULL DEFAULT 'PREFER_LOCAL',
                binding_source VARCHAR(64) NOT NULL DEFAULT 'MANUAL',
                approved_by INT NULL,
                approved_at DATETIME NULL,
                metadata_json JSON NULL,
                created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
                deleted_at TIMESTAMP NULL DEFAULT NULL,
                UNIQUE KEY uniq_edge_gateway_binding (gateway_id, relay_id),
                INDEX idx_edge_gateway_binding_department (department_id),
                INDEX idx_edge_gateway_binding_device (gateway_id, device_id)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",

            "CREATE TABLE IF NOT EXISTS edge_gateway_command_jobs (
                id INT AUTO_INCREMENT PRIMARY KEY,
                gateway_id INT NOT NULL,
                command_type VARCHAR(64) NOT NULL,
                status VARCHAR(32) NOT NULL DEFAULT 'PENDING',
                request_json JSON NULL,
                response_json JSON NULL,
                delivery_json JSON NULL,
                correlation_id VARCHAR(128) NOT NULL,
                requested_by INT NULL,
                requested_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
                completed_at DATETIME NULL,
                error_message TEXT NULL,
                created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
                deleted_at TIMESTAMP NULL DEFAULT NULL,
                UNIQUE KEY uniq_edge_gateway_command_correlation (correlation_id),
                INDEX idx_edge_gateway_command_gateway (gateway_id),
                INDEX idx_edge_gateway_command_status (status),
                INDEX idx_edge_gateway_command_type (command_type)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",

            "CREATE TABLE IF NOT EXISTS edge_gateway_expected_relay_states (
                id INT AUTO_INCREMENT PRIMARY KEY,
                gateway_id INT NOT NULL,
                department_id INT NOT NULL,
                lane_id INT NOT NULL,
                role VARCHAR(32) NOT NULL,
                relay_id VARCHAR(255) NOT NULL,
                device_id VARCHAR(255) NULL,
                local_ip VARCHAR(64) NULL,
                device_type VARCHAR(64) NOT NULL DEFAULT 'UNKNOWN',
                expected_state TINYINT(1) NOT NULL DEFAULT 0,
                updated_at DATETIME NOT NULL,
                completed_at DATETIME NULL,
                last_attempted_at DATETIME NULL,
                last_error TEXT NULL,
                metadata_json JSON NULL,
                created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
                row_updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
                deleted_at TIMESTAMP NULL DEFAULT NULL,
                UNIQUE KEY uniq_edge_gateway_expected_relay_state (gateway_id, lane_id, role),
                INDEX idx_edge_gateway_expected_relay_gateway (gateway_id),
                INDEX idx_edge_gateway_expected_relay_department (department_id),
                INDEX idx_edge_gateway_expected_relay_pending (gateway_id, completed_at)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",

            "CREATE TABLE IF NOT EXISTS edge_gateway_operations (
                id INT AUTO_INCREMENT PRIMARY KEY,
                gateway_id INT NOT NULL,
                type VARCHAR(32) NOT NULL,
                operation_type VARCHAR(32) NOT NULL DEFAULT 'DISCOVERY',
                status VARCHAR(32) NOT NULL DEFAULT 'PENDING',
                request_json JSON NULL,
                summary_json JSON NULL,
                result_json JSON NULL,
                error_code VARCHAR(128) NULL,
                error_message TEXT NULL,
                correlation_id VARCHAR(128) NOT NULL,
                agent_instance_id VARCHAR(128) NULL,
                lease_expires_at DATETIME NULL,
                last_progress_at DATETIME NULL,
                attempt_count INT NOT NULL DEFAULT 0,
                requested_by INT NULL,
                requested_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
                started_at DATETIME NULL,
                completed_at DATETIME NULL,
                created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
                deleted_at TIMESTAMP NULL DEFAULT NULL,
                UNIQUE KEY uniq_edge_gateway_operation_correlation (correlation_id),
                INDEX idx_edge_gateway_operations_gateway (gateway_id),
                INDEX idx_edge_gateway_operations_status (status),
                INDEX idx_edge_gateway_operations_type (type)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",

            "CREATE TABLE IF NOT EXISTS edge_gateway_operation_events (
                id INT AUTO_INCREMENT PRIMARY KEY,
                operation_id INT NOT NULL,
                gateway_id INT NOT NULL,
                stage VARCHAR(64) NOT NULL DEFAULT 'RECORDED',
                level VARCHAR(16) NOT NULL DEFAULT 'INFO',
                code VARCHAR(128) NULL,
                message TEXT NOT NULL,
                context_json JSON NULL,
                counts_json JSON NULL,
                payload_json JSON NULL,
                created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
                deleted_at TIMESTAMP NULL DEFAULT NULL,
                INDEX idx_edge_gateway_operation_events_operation (operation_id),
                INDEX idx_edge_gateway_operation_events_gateway (gateway_id),
                INDEX idx_edge_gateway_operation_events_level (level)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",

            "CREATE TABLE IF NOT EXISTS edge_gateway_audit_logs (
                id INT AUTO_INCREMENT PRIMARY KEY,
                gateway_id INT NULL,
                department_id INT NULL,
                action VARCHAR(128) NOT NULL,
                actor_user_id INT NULL,
                actor_type VARCHAR(32) NOT NULL DEFAULT 'USER',
                severity VARCHAR(16) NOT NULL DEFAULT 'INFO',
                context_json JSON NULL,
                created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
                INDEX idx_edge_gateway_audit_gateway (gateway_id),
                INDEX idx_edge_gateway_audit_department (department_id),
                INDEX idx_edge_gateway_audit_action (action)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",

            "CREATE TABLE IF NOT EXISTS edge_gateway_log_entries (
                id INT AUTO_INCREMENT PRIMARY KEY,
                gateway_id INT NOT NULL,
                department_id INT NULL,
                level VARCHAR(16) NOT NULL DEFAULT 'INFO',
                stream VARCHAR(32) NOT NULL DEFAULT 'agent',
                source VARCHAR(64) NOT NULL DEFAULT 'BROKER',
                message TEXT NOT NULL,
                context_json JSON NULL,
                created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
                INDEX idx_edge_gateway_log_entries_gateway (gateway_id),
                INDEX idx_edge_gateway_log_entries_department (department_id),
                INDEX idx_edge_gateway_log_entries_level (level),
                INDEX idx_edge_gateway_log_entries_stream (stream)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",

            "CREATE TABLE IF NOT EXISTS edge_gateway_shell_sessions (
                id INT AUTO_INCREMENT PRIMARY KEY,
                gateway_id INT NOT NULL,
                department_id INT NOT NULL,
                actor_user_id INT NULL,
                session_token_hash CHAR(64) NOT NULL,
                status VARCHAR(32) NOT NULL DEFAULT 'PENDING',
                reason VARCHAR(255) NULL,
                connection_id VARCHAR(128) NULL,
                cwd VARCHAR(255) NULL,
                shell_command VARCHAR(255) NULL,
                shell_args_json JSON NULL,
                cols INT NULL,
                terminal_rows INT NULL,
                transcript LONGTEXT NULL,
                metadata_json JSON NULL,
                expires_at DATETIME NULL,
                approved_at DATETIME NULL,
                opened_at DATETIME NULL,
                closed_at DATETIME NULL,
                created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
                deleted_at TIMESTAMP NULL DEFAULT NULL,
                UNIQUE KEY uniq_edge_gateway_shell_session_token_hash (session_token_hash),
                INDEX idx_edge_gateway_shell_sessions_gateway (gateway_id),
                INDEX idx_edge_gateway_shell_sessions_status (status),
                INDEX idx_edge_gateway_shell_sessions_expires (expires_at)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",
        ];

        foreach ($queries as $sql) {
            $pdo->exec($sql);
        }

        self::ensureColumn('edge_gateway_command_jobs', 'delivery_json', 'JSON NULL AFTER response_json');

        self::ensureColumn('edge_gateway_expected_relay_states', 'device_type', "VARCHAR(64) NOT NULL DEFAULT 'UNKNOWN' AFTER local_ip");
        self::ensureColumn('edge_gateway_expected_relay_states', 'last_attempted_at', 'DATETIME NULL AFTER completed_at');
        self::ensureColumn('edge_gateway_expected_relay_states', 'last_error', 'TEXT NULL AFTER last_attempted_at');
        self::ensureColumn('edge_gateway_expected_relay_states', 'metadata_json', 'JSON NULL AFTER last_error');
        self::ensureColumn('edge_gateway_expected_relay_states', 'row_updated_at', 'TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP AFTER created_at');
        self::ensureColumn('edge_gateway_expected_relay_states', 'deleted_at', 'TIMESTAMP NULL DEFAULT NULL AFTER row_updated_at');

        self::ensureColumn('edge_gateway_relay_bindings', 'fallback_mode', "VARCHAR(32) NOT NULL DEFAULT 'PREFER_LOCAL' AFTER channel");

        self::ensureColumn('edge_gateway_operations', 'type', "VARCHAR(32) NOT NULL DEFAULT 'DISCOVERY' AFTER gateway_id");
        self::ensureColumn('edge_gateway_operations', 'operation_type', "VARCHAR(32) NOT NULL DEFAULT 'DISCOVERY' AFTER type");
        self::ensureColumn('edge_gateway_operations', 'summary_json', 'JSON NULL AFTER request_json');
        self::ensureColumn('edge_gateway_operations', 'result_json', 'JSON NULL AFTER summary_json');
        self::ensureColumn('edge_gateway_operations', 'error_code', 'VARCHAR(128) NULL AFTER result_json');
        self::ensureColumn('edge_gateway_operations', 'error_message', 'TEXT NULL AFTER error_code');
        self::ensureColumn('edge_gateway_operations', 'correlation_id', 'VARCHAR(128) NULL AFTER error_message');
        self::ensureColumn('edge_gateway_operations', 'agent_instance_id', 'VARCHAR(128) NULL AFTER correlation_id');
        self::ensureColumn('edge_gateway_operations', 'lease_expires_at', 'DATETIME NULL AFTER agent_instance_id');
        self::ensureColumn('edge_gateway_operations', 'last_progress_at', 'DATETIME NULL AFTER lease_expires_at');
        self::ensureColumn('edge_gateway_operations', 'attempt_count', 'INT NOT NULL DEFAULT 0 AFTER last_progress_at');
        self::ensureColumn('edge_gateway_operations', 'requested_by', 'INT NULL AFTER attempt_count');
        self::ensureColumn('edge_gateway_operations', 'requested_at', 'DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP AFTER requested_by');
        self::ensureColumn('edge_gateway_operations', 'started_at', 'DATETIME NULL AFTER requested_at');
        self::ensureColumn('edge_gateway_operations', 'completed_at', 'DATETIME NULL AFTER started_at');
        self::ensureColumn('edge_gateway_operations', 'updated_at', 'TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP AFTER created_at');
        self::ensureColumn('edge_gateway_operations', 'deleted_at', 'TIMESTAMP NULL DEFAULT NULL AFTER updated_at');

        self::ensureColumn('edge_gateway_operation_events', 'stage', "VARCHAR(64) NOT NULL DEFAULT 'RECORDED' AFTER gateway_id");
        self::ensureColumn('edge_gateway_operation_events', 'code', 'VARCHAR(128) NULL AFTER level');
        self::ensureColumn('edge_gateway_operation_events', 'context_json', 'JSON NULL AFTER message');
        self::ensureColumn('edge_gateway_operation_events', 'counts_json', 'JSON NULL AFTER context_json');
        self::ensureColumn('edge_gateway_operation_events', 'payload_json', 'JSON NULL AFTER counts_json');
        self::ensureColumn('edge_gateway_operation_events', 'deleted_at', 'TIMESTAMP NULL DEFAULT NULL AFTER created_at');

        self::ensureColumn('edge_gateway_audit_logs', 'actor_type', "VARCHAR(32) NOT NULL DEFAULT 'USER' AFTER actor_user_id");
        self::ensureColumn('edge_gateway_audit_logs', 'severity', "VARCHAR(16) NOT NULL DEFAULT 'INFO' AFTER actor_type");
        self::ensureColumn('edge_gateway_audit_logs', 'context_json', 'JSON NULL AFTER severity');

        self::ensureColumn('edge_gateway_log_entries', 'department_id', 'INT NULL AFTER gateway_id');
        self::ensureColumn('edge_gateway_log_entries', 'level', "VARCHAR(16) NOT NULL DEFAULT 'INFO' AFTER department_id");
        self::ensureColumn('edge_gateway_log_entries', 'stream', "VARCHAR(32) NOT NULL DEFAULT 'agent' AFTER level");
        self::ensureColumn('edge_gateway_log_entries', 'source', "VARCHAR(64) NOT NULL DEFAULT 'BROKER' AFTER stream");
        self::ensureColumn('edge_gateway_log_entries', 'message', 'TEXT NOT NULL AFTER source');
        self::ensureColumn('edge_gateway_log_entries', 'context_json', 'JSON NULL AFTER message');

        self::ensureColumn('edge_gateway_shell_sessions', 'department_id', 'INT NOT NULL AFTER gateway_id');
        self::ensureColumn('edge_gateway_shell_sessions', 'actor_user_id', 'INT NULL AFTER department_id');
        self::ensureColumn('edge_gateway_shell_sessions', 'session_token_hash', 'CHAR(64) NOT NULL AFTER actor_user_id');
        self::ensureColumn('edge_gateway_shell_sessions', 'status', "VARCHAR(32) NOT NULL DEFAULT 'PENDING' AFTER session_token_hash");
        self::ensureColumn('edge_gateway_shell_sessions', 'reason', 'VARCHAR(255) NULL AFTER status');
        self::ensureColumn('edge_gateway_shell_sessions', 'connection_id', 'VARCHAR(128) NULL AFTER reason');
        self::ensureColumn('edge_gateway_shell_sessions', 'cwd', 'VARCHAR(255) NULL AFTER connection_id');
        self::ensureColumn('edge_gateway_shell_sessions', 'shell_command', 'VARCHAR(255) NULL AFTER cwd');
        self::ensureColumn('edge_gateway_shell_sessions', 'shell_args_json', 'JSON NULL AFTER shell_command');
        self::ensureColumn('edge_gateway_shell_sessions', 'cols', 'INT NULL AFTER shell_args_json');
        self::renameColumnIfPresent('edge_gateway_shell_sessions', 'rows', 'terminal_rows', 'INT NULL', 'cols');
        self::ensureColumn('edge_gateway_shell_sessions', 'terminal_rows', 'INT NULL AFTER cols');
        self::ensureColumn('edge_gateway_shell_sessions', 'transcript', 'LONGTEXT NULL AFTER terminal_rows');
        self::ensureColumn('edge_gateway_shell_sessions', 'metadata_json', 'JSON NULL AFTER transcript');
        self::ensureColumn('edge_gateway_shell_sessions', 'expires_at', 'DATETIME NULL AFTER metadata_json');
        self::ensureColumn('edge_gateway_shell_sessions', 'approved_at', 'DATETIME NULL AFTER expires_at');
        self::ensureColumn('edge_gateway_shell_sessions', 'opened_at', 'DATETIME NULL AFTER approved_at');
        self::ensureColumn('edge_gateway_shell_sessions', 'closed_at', 'DATETIME NULL AFTER opened_at');
        self::ensureColumn('edge_gateway_shell_sessions', 'updated_at', 'TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP AFTER created_at');
        self::ensureColumn('edge_gateway_shell_sessions', 'deleted_at', 'TIMESTAMP NULL DEFAULT NULL AFTER updated_at');

        self::syncOperationTypeColumns();

        self::$initialized = true;
    }

    private static function ensureColumn(string $table, string $column, string $definition): void
    {
        if (self::tableHasColumn($table, $column)) {
            return;
        }

        if (!preg_match('/^[A-Za-z0-9_]+$/', $table) || !preg_match('/^[A-Za-z0-9_]+$/', $column)) {
            throw new \RuntimeException('Invalid schema bootstrap identifier');
        }

        self::pdo()->exec(
            "ALTER TABLE `$table`
             ADD COLUMN `$column` $definition"
        );
    }

    private static function renameColumnIfPresent(
        string $table,
        string $from,
        string $to,
        string $definition,
        ?string $afterColumn = null
    ): void {
        if (!self::tableHasColumn($table, $from) || self::tableHasColumn($table, $to)) {
            return;
        }

        if (!preg_match('/^[A-Za-z0-9_]+$/', $table)
            || !preg_match('/^[A-Za-z0-9_]+$/', $from)
            || !preg_match('/^[A-Za-z0-9_]+$/', $to)
            || ($afterColumn !== null && !preg_match('/^[A-Za-z0-9_]+$/', $afterColumn))) {
            throw new \RuntimeException('Invalid schema bootstrap identifier');
        }

        $positionClause = $afterColumn === null ? '' : " AFTER `$afterColumn`";

        self::pdo()->exec(
            "ALTER TABLE `$table`
             CHANGE COLUMN `$from` `$to` $definition$positionClause"
        );
    }

    private static function tableHasColumn(string $table, string $column): bool
    {
        if (!preg_match('/^[A-Za-z0-9_]+$/', $table) || !preg_match('/^[A-Za-z0-9_]+$/', $column)) {
            throw new \RuntimeException('Invalid schema bootstrap identifier');
        }

        $statement = self::pdo()->prepare(
            "SELECT COUNT(*) AS c
             FROM information_schema.COLUMNS
             WHERE TABLE_SCHEMA = DATABASE()
               AND TABLE_NAME = :table
               AND COLUMN_NAME = :column"
        );
        $statement->execute([
            ':table' => $table,
            ':column' => $column,
        ]);

        $row = $statement->fetch();
        return ((int)($row['c'] ?? 0)) > 0;
    }

    private static function syncOperationTypeColumns(): void
    {
        if (!self::tableHasColumn('edge_gateway_operations', 'type')
            || !self::tableHasColumn('edge_gateway_operations', 'operation_type')) {
            return;
        }

        $pdo = self::pdo();
        $pdo->exec(
            "UPDATE edge_gateway_operations
             SET type = operation_type
             WHERE operation_type IS NOT NULL
               AND operation_type <> ''
               AND (type IS NULL OR type = '' OR type <> operation_type)"
        );

        $pdo->exec(
            "UPDATE edge_gateway_operations
             SET operation_type = type
             WHERE type IS NOT NULL
               AND type <> ''
               AND (operation_type IS NULL OR operation_type = '')"
        );
    }

    private static function pdo(): \PDO
    {
        $envConfig = self::databaseConfigFromEnvironment();
        if ($envConfig !== null) {
            return self::connectPdo($envConfig);
        }

        if (!class_exists(db::class, false)) {
            $dbClassPath = __DIR__ . '/../../../classes/db.php';
            if (is_file($dbClassPath)) {
                require_once $dbClassPath;
            }
        }

        if (!class_exists(db::class)) {
            throw new \RuntimeException('Database connection helper is not available.');
        }

        return db::getPDO();
    }

    /**
     * @return array{host:string,user:string,password:string,database:string,port:int}|null
     */
    private static function databaseConfigFromEnvironment(): ?array
    {
        $target = strtolower(self::envString('CONFIG_DB_TARGET') ?: 'live');
        if ($target !== 'debug') {
            $target = 'live';
        }

        $host = self::databaseEnvValue('HOST', $target);
        $user = self::databaseEnvValue('USER', $target);
        $database = self::databaseEnvValue('DATABASE', $target);
        if ($host === '' || $user === '' || $database === '') {
            return null;
        }

        return [
            'host' => $host,
            'user' => $user,
            'password' => self::databaseEnvValue('PASSWORD', $target),
            'database' => $database,
            'port' => (int)(self::databaseEnvValue('PORT', $target) ?: 3306),
        ];
    }

    private static function databaseEnvValue(string $key, string $target): string
    {
        $liveValue = self::envString('CONFIG_DB_' . $key);
        $debugValue = self::envString('CONFIG_DB_DEBUG_' . $key);

        if ($target === 'debug' && $debugValue !== '') {
            return $debugValue;
        }

        return $liveValue;
    }

    private static function envString(string $key): string
    {
        $value = getenv($key);
        if ($value === false || $value === null) {
            $value = $_ENV[$key] ?? $_SERVER[$key] ?? '';
        }

        return trim((string)$value);
    }

    /**
     * @param array{host:string,user:string,password:string,database:string,port:int} $config
     */
    private static function connectPdo(array $config): \PDO
    {
        $port = $config['port'] > 0 ? $config['port'] : 3306;
        $dsn = "mysql:host={$config['host']};port={$port};dbname={$config['database']};charset=utf8mb4";

        return new \PDO($dsn, $config['user'], $config['password'], [
            \PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION,
            \PDO::ATTR_DEFAULT_FETCH_MODE => \PDO::FETCH_ASSOC,
            \PDO::ATTR_EMULATE_PREPARES => false,
        ]);
    }
}
