<?php

declare(strict_types=1);

namespace App\Services;

use App\Core\Database;
use RuntimeException;
use Throwable;

/**
 * Resolves physical Oracle access for NJCH/JCKH/MNH without guessing a single
 * hard-coded path.
 *
 * Resolution order:
 *  1. validated cached endpoint from a previous successful discovery;
 *  2. configured direct connection;
 *  3. SERVICE_NAME <-> SID variant of the configured endpoint;
 *  4. dynamically discovered accessible Oracle DB links;
 *  5. TNS aliases/descriptors from the active Oracle client;
 *  6. legacy dashboard connection configuration found beside ddb-new;
 *  7. corroborated non-secret HMH endpoint candidates where available.
 *
 * Every candidate must pass a live Oracle identity probe before it can be used.
 * No Oracle DDL/DML is performed.
 */
final class BranchOracleAccessResolver
{
    /** @var array<int, string> */
    private const BRANCHES = ['njch', 'jckh', 'mnh'];

    /** @var array<string, array<int, string>> */
    private const ALIASES = [
        'njch' => ['njch', 'newjeddah', 'new_jeddah', 'oranjc', 'rcpmaster3'],
        'jckh' => ['jckh', 'jchk', 'jck', 'orajckh', 'orajck', 'rcpmaster_jck'],
        'mnh'  => ['mnh', 'madina', 'medina', 'oramnh', 'rcpmaster_mnh'],
    ];

    /**
     * Non-secret endpoint candidates already corroborated elsewhere in the
     * hospital application estate. They are never trusted blindly: identity
     * validation is mandatory before use.
     *
     * @var array<string, array<int, array<string, string>>>
     */
    private const CORROBORATED_ENDPOINTS = [
        'mnh' => [
            [
                'connect_type' => 'service',
                'host' => '10.20.104.20',
                'port' => '1521',
                'service' => 'oramnh',
                'source' => 'CORROBORATED_HMH_APPLICATION_CONFIG',
            ],
        ],
    ];

    /** @var array<string, array<string, mixed>> */
    private $resolved = [];

    /** @var array<string, array<int, array<string, mixed>>> */
    private $attempts = [];

    /** @var array<string, array<string, mixed>> */
    private $accessPaths = [];

    /** @var int */
    private $candidateLimit = 12;

    /**
     * @param callable(string):string $sqlFactory
     * @param array<string, mixed> $params
     * @return array<int, array<string, mixed>>
     */
    public function fetchAll(
        string $branch,
        callable $sqlFactory,
        array $params = []
    ): array {
        $branch = $this->normalizeBranch($branch);
        $resolution = $this->resolve($branch);
        $mode = (string)$resolution['mode'];

        if ($mode === 'DB_LINK') {
            $link = (string)$resolution['db_link'];
            $sql = (string)$sqlFactory('@' . $link);
            $rows = Database::fetchAll('njch', $sql, $params);
        } elseif ($mode === 'CONFIGURED_DIRECT') {
            $sql = (string)$sqlFactory('');
            $rows = Database::fetchAll($branch, $sql, $params);
        } elseif ($mode === 'DYNAMIC_DIRECT') {
            $sql = (string)$sqlFactory('');
            $db = (array)$resolution['database'];
            $rows = Database::fetchAllUsingDatabaseConfig(
                (string)$resolution['cache_key'],
                $db,
                $sql,
                $params
            );
        } else {
            throw new RuntimeException('Unsupported resolved Oracle access mode: ' . $mode);
        }

        $this->accessPaths[$branch] = $this->publicResolution($resolution);
        return $rows;
    }

    /**
     * @return array<string, array<string, mixed>>
     */
    public function accessPaths(): array
    {
        return $this->accessPaths;
    }

    /**
     * @return array<string, mixed>
     */
    public function diagnostics(): array
    {
        $resolved = [];
        foreach ($this->resolved as $branch => $resolution) {
            $resolved[$branch] = $this->publicResolution($resolution);
        }

        return [
            'schema_version' => '2.0',
            'policy' => 'DISCOVER_VALIDATE_CACHE_NO_GUESSING',
            'branches_expected' => self::BRANCHES,
            'resolved' => $resolved,
            'access_paths_used' => $this->accessPaths,
            'attempts' => $this->attempts,
            'write_operations' => 'NONE',
        ];
    }

    /**
     * @return array<string, mixed>
     */
    public function resolve(string $branch): array
    {
        $branch = $this->normalizeBranch($branch);
        if (isset($this->resolved[$branch])) {
            return $this->resolved[$branch];
        }

        $this->attempts[$branch] = [];

        $cached = $this->loadCachedCandidate($branch);
        if ($cached !== null) {
            $resolved = $this->probeCandidate($branch, $cached);
            if ($resolved !== null) {
                return $this->remember($branch, $resolved);
            }
        }

        $configured = $this->configuredCandidate($branch);
        $resolved = $this->probeCandidate($branch, $configured);
        if ($resolved !== null) {
            return $this->remember($branch, $resolved);
        }

        foreach ($this->configuredTransportVariants($branch, $configured) as $candidate) {
            $resolved = $this->probeCandidate($branch, $candidate);
            if ($resolved !== null) {
                return $this->remember($branch, $resolved);
            }
        }

        foreach ($this->discoverDbLinkCandidates($branch) as $candidate) {
            $resolved = $this->probeCandidate($branch, $candidate);
            if ($resolved !== null) {
                return $this->remember($branch, $resolved);
            }
        }

        foreach ($this->discoverTnsCandidates($branch) as $candidate) {
            $resolved = $this->probeCandidate($branch, $candidate);
            if ($resolved !== null) {
                return $this->remember($branch, $resolved);
            }
        }

        foreach ($this->discoverLegacyConfigCandidates($branch) as $candidate) {
            $resolved = $this->probeCandidate($branch, $candidate);
            if ($resolved !== null) {
                return $this->remember($branch, $resolved);
            }
        }

        foreach (self::CORROBORATED_ENDPOINTS[$branch] ?? [] as $endpoint) {
            $candidate = $this->dynamicCandidateFromEndpoint(
                $branch,
                $endpoint,
                (string)($endpoint['source'] ?? 'CORROBORATED_ENDPOINT')
            );
            $resolved = $this->probeCandidate($branch, $candidate);
            if ($resolved !== null) {
                return $this->remember($branch, $resolved);
            }
        }

        $lastErrors = [];
        foreach (array_slice($this->attempts[$branch], -6) as $attempt) {
            $lastErrors[] = ($attempt['source'] ?? 'unknown')
                . ': ' . ($attempt['status'] ?? 'FAIL')
                . (isset($attempt['error_code']) ? ' [' . $attempt['error_code'] . ']' : '');
        }

        throw new RuntimeException(
            'UNRESOLVED_BRANCH_ORACLE_ACCESS branch=' . strtoupper($branch)
            . '; candidates_attempted=' . count($this->attempts[$branch])
            . '; last_attempts=' . implode(' | ', $lastErrors)
            . '. See BRANCH_ORACLE_CONNECTIVITY_DIAGNOSTICS.json for redacted evidence.'
        );
    }

    /**
     * @param array<string, mixed> $resolution
     * @return array<string, mixed>
     */
    private function remember(string $branch, array $resolution): array
    {
        $this->resolved[$branch] = $resolution;
        $this->writeCache($branch, $resolution);
        return $resolution;
    }

    /**
     * @return array<string, mixed>
     */
    private function configuredCandidate(string $branch): array
    {
        return [
            'kind' => 'CONFIGURED_DIRECT',
            'branch' => $branch,
            'source' => 'CURRENT_HOSPITAL_CONFIG',
            'database' => (array)\config('hospitals.' . $branch . '.database', []),
        ];
    }

    /**
     * @param array<string, mixed> $configured
     * @return array<int, array<string, mixed>>
     */
    private function configuredTransportVariants(string $branch, array $configured): array
    {
        $db = (array)($configured['database'] ?? []);
        $type = strtolower(trim((string)($db['connect_type'] ?? 'service')));
        $variants = [];

        if ($type === 'service' && trim((string)($db['service'] ?? '')) !== '') {
            $alt = $db;
            $alt['connect_type'] = 'sid';
            $alt['sid'] = (string)$db['service'];
            $variants[] = [
                'kind' => 'DYNAMIC_DIRECT',
                'branch' => $branch,
                'source' => 'CONFIGURED_SERVICE_AS_SID_RECOVERY',
                'database' => $alt,
                'cache_key' => 'resolver:' . $branch . ':service-as-sid',
            ];
        }

        if ($type === 'sid' && trim((string)($db['sid'] ?? '')) !== '') {
            $alt = $db;
            $alt['connect_type'] = 'service';
            $alt['service'] = (string)$db['sid'];
            $variants[] = [
                'kind' => 'DYNAMIC_DIRECT',
                'branch' => $branch,
                'source' => 'CONFIGURED_SID_AS_SERVICE_RECOVERY',
                'database' => $alt,
                'cache_key' => 'resolver:' . $branch . ':sid-as-service',
            ];
        }

        return $variants;
    }

    /**
     * @return array<int, array<string, mixed>>
     */
    private function discoverDbLinkCandidates(string $branch): array
    {
        $links = [];
        $queries = [
            'SELECT db_link, host FROM user_db_links ORDER BY db_link',
            'SELECT owner, db_link, host FROM all_db_links ORDER BY owner, db_link',
        ];

        foreach ($queries as $sql) {
            try {
                foreach (Database::fetchAll('njch', $sql) as $row) {
                    $name = trim((string)($row['db_link'] ?? ''));
                    if ($name === '' || !$this->safeDbLinkIdentifier($name)) {
                        continue;
                    }
                    $links[strtolower($name)] = $name;
                }
            } catch (Throwable $error) {
                $this->recordAttempt($branch, [
                    'source' => 'DB_LINK_CATALOG_DISCOVERY',
                    'mode' => 'DISCOVERY',
                    'status' => 'SKIP',
                    'error_code' => $this->oracleErrorCode($error),
                    'error' => $this->redactError($error->getMessage()),
                ]);
            }
        }

        $ranked = array_values($links);
        usort($ranked, function (string $a, string $b) use ($branch): int {
            return $this->aliasScore($branch, $b) <=> $this->aliasScore($branch, $a);
        });

        $out = [];
        foreach (array_slice($ranked, 0, $this->candidateLimit) as $name) {
            $out[] = [
                'kind' => 'DB_LINK',
                'branch' => $branch,
                'source' => 'DISCOVERED_ORACLE_DB_LINK_CATALOG',
                'db_link' => $name,
            ];
        }
        return $out;
    }

    /**
     * @return array<int, array<string, mixed>>
     */
    private function discoverTnsCandidates(string $branch): array
    {
        $candidates = [];
        foreach ($this->tnsFiles() as $file) {
            $text = @file_get_contents($file);
            if (!is_string($text) || $text === '') {
                continue;
            }

            foreach ($this->parseTnsEntries($text) as $alias => $descriptor) {
                if ($this->aliasScore($branch, $alias . ' ' . $descriptor) <= 0) {
                    continue;
                }

                $candidate = $this->dynamicCandidateFromEndpoint(
                    $branch,
                    [
                        'connect_type' => 'tns',
                        'tns' => $this->withConnectTimeout($descriptor),
                    ],
                    'DISCOVERED_TNS:' . basename($file) . ':' . $alias
                );
                $candidates[$this->candidateSignature($candidate)] = $candidate;
            }
        }

        return array_slice(array_values($candidates), 0, $this->candidateLimit);
    }

    /**
     * @return array<int, array<string, mixed>>
     */
    private function discoverLegacyConfigCandidates(string $branch): array
    {
        $out = [];
        $count = 0;

        foreach ($this->legacyConfigFiles() as $file) {
            if ($count >= 450) {
                break;
            }
            $count++;

            $size = @filesize($file);
            if ($size === false || $size > 2 * 1024 * 1024) {
                continue;
            }

            $text = @file_get_contents($file);
            if (!is_string($text) || $text === '') {
                continue;
            }

            if ($this->aliasScore($branch, basename($file) . ' ' . $text) <= 0) {
                continue;
            }

            foreach ($this->extractEndpointCandidates(
                $branch,
                $text,
                'LEGACY_LOCAL_CONFIG:' . basename($file)
            ) as $candidate) {
                $out[$this->candidateSignature($candidate)] = $candidate;
                if (count($out) >= $this->candidateLimit) {
                    break 2;
                }
            }
        }

        return array_values($out);
    }

    /**
     * @param array<string, string> $endpoint
     * @return array<string, mixed>
     */
    private function dynamicCandidateFromEndpoint(
        string $branch,
        array $endpoint,
        string $source
    ): array {
        $base = (array)\config('hospitals.' . $branch . '.database', []);
        $db = $base;

        foreach (['connect_type', 'tns', 'host', 'port', 'service', 'sid'] as $key) {
            if (isset($endpoint[$key]) && trim((string)$endpoint[$key]) !== '') {
                $db[$key] = (string)$endpoint[$key];
            }
        }

        return [
            'kind' => 'DYNAMIC_DIRECT',
            'branch' => $branch,
            'source' => $source,
            'database' => $db,
            'cache_key' => 'resolver:' . $branch . ':' . substr(sha1(json_encode($this->safeDatabase($db))), 0, 16),
        ];
    }

    /**
     * @param array<string, mixed> $candidate
     * @return array<string, mixed>|null
     */
    private function probeCandidate(string $branch, array $candidate)
    {
        $kind = (string)($candidate['kind'] ?? '');
        $source = (string)($candidate['source'] ?? 'UNKNOWN');
        $attempt = [
            'source' => $source,
            'mode' => $kind,
            'status' => 'FAIL',
        ];

        try {
            if ($kind === 'DB_LINK') {
                $link = (string)($candidate['db_link'] ?? '');
                if (!$this->safeDbLinkIdentifier($link)) {
                    throw new RuntimeException('Unsafe DB link identifier rejected.');
                }
                $identity = $this->probeDbLinkIdentity($link);
                $resolution = [
                    'mode' => 'DB_LINK',
                    'source' => $source,
                    'db_link' => $link,
                    'identity' => $identity,
                ];
            } elseif ($kind === 'CONFIGURED_DIRECT') {
                $identity = $this->probeConfiguredIdentity($branch);
                $resolution = [
                    'mode' => 'CONFIGURED_DIRECT',
                    'source' => $source,
                    'connection' => $branch,
                    'identity' => $identity,
                ];
            } elseif ($kind === 'DYNAMIC_DIRECT') {
                $db = (array)($candidate['database'] ?? []);
                $key = (string)($candidate['cache_key'] ?? ('resolver:' . $branch . ':' . sha1($source)));
                $identity = $this->probeDynamicIdentity($key, $db);
                $resolution = [
                    'mode' => 'DYNAMIC_DIRECT',
                    'source' => $source,
                    'database' => $db,
                    'cache_key' => $key,
                    'identity' => $identity,
                ];
            } else {
                throw new RuntimeException('Unknown Oracle candidate kind: ' . $kind);
            }

            if (!$this->identityMatchesBranch($branch, (array)$resolution['identity'])) {
                $attempt['status'] = 'REJECTED_IDENTITY_MISMATCH';
                $attempt['identity'] = $this->publicIdentity((array)$resolution['identity']);
                $this->recordAttempt($branch, $attempt);
                return null;
            }

            $attempt['status'] = 'PASS';
            $attempt['identity'] = $this->publicIdentity((array)$resolution['identity']);
            $attempt['endpoint'] = $this->candidateEndpointHint($candidate);
            $this->recordAttempt($branch, $attempt);
            return $resolution;
        } catch (Throwable $error) {
            $attempt['error_code'] = $this->oracleErrorCode($error);
            $attempt['error'] = $this->redactError($error->getMessage());
            $attempt['endpoint'] = $this->candidateEndpointHint($candidate);
            $this->recordAttempt($branch, $attempt);
            return null;
        }
    }

    /**
     * @return array<string, mixed>
     */
    private function probeConfiguredIdentity(string $branch): array
    {
        $context = Database::fetchAll(
            $branch,
            "SELECT
                SYS_CONTEXT('USERENV','DB_NAME') db_name,
                SYS_CONTEXT('USERENV','SERVICE_NAME') service_name,
                SYS_CONTEXT('USERENV','INSTANCE_NAME') instance_name
             FROM dual"
        );
        $global = Database::fetchAll($branch, 'SELECT global_name FROM global_name');
        $hospital = Database::fetchAll(
            $branch,
            'SELECT hosp_id, hosp_en_name, hosp_short_name_en
             FROM hospital_main_data
             WHERE ROWNUM <= 20'
        );

        return $this->composeIdentity($context, $global, $hospital);
    }

    /**
     * @param array<string, mixed> $db
     * @return array<string, mixed>
     */
    private function probeDynamicIdentity(string $cacheKey, array $db): array
    {
        $context = Database::fetchAllUsingDatabaseConfig(
            $cacheKey,
            $db,
            "SELECT
                SYS_CONTEXT('USERENV','DB_NAME') db_name,
                SYS_CONTEXT('USERENV','SERVICE_NAME') service_name,
                SYS_CONTEXT('USERENV','INSTANCE_NAME') instance_name
             FROM dual"
        );
        $global = Database::fetchAllUsingDatabaseConfig(
            $cacheKey,
            $db,
            'SELECT global_name FROM global_name'
        );
        $hospital = Database::fetchAllUsingDatabaseConfig(
            $cacheKey,
            $db,
            'SELECT hosp_id, hosp_en_name, hosp_short_name_en
             FROM hospital_main_data
             WHERE ROWNUM <= 20'
        );

        return $this->composeIdentity($context, $global, $hospital);
    }

    /**
     * @return array<string, mixed>
     */
    private function probeDbLinkIdentity(string $link): array
    {
        $suffix = '@' . $link;
        $context = Database::fetchAll(
            'njch',
            "SELECT
                SYS_CONTEXT('USERENV','DB_NAME') db_name,
                SYS_CONTEXT('USERENV','SERVICE_NAME') service_name,
                SYS_CONTEXT('USERENV','INSTANCE_NAME') instance_name
             FROM dual{$suffix}"
        );
        $global = Database::fetchAll(
            'njch',
            "SELECT global_name FROM global_name{$suffix}"
        );
        $hospital = Database::fetchAll(
            'njch',
            "SELECT hosp_id, hosp_en_name, hosp_short_name_en
             FROM hospital_main_data{$suffix}
             WHERE ROWNUM <= 20"
        );

        return $this->composeIdentity($context, $global, $hospital);
    }

    /**
     * @param array<int, array<string, mixed>> $context
     * @param array<int, array<string, mixed>> $global
     * @param array<int, array<string, mixed>> $hospital
     * @return array<string, mixed>
     */
    private function composeIdentity(array $context, array $global, array $hospital): array
    {
        $ctx = $context[0] ?? [];
        $globalName = trim((string)(($global[0] ?? [])['global_name'] ?? ''));
        $names = [];

        foreach ($hospital as $row) {
            foreach (['hosp_en_name', 'hosp_short_name_en'] as $key) {
                $value = trim((string)($row[$key] ?? ''));
                if ($value !== '') {
                    $names[$value] = true;
                }
            }
        }

        $identity = [
            'global_name' => $globalName,
            'db_name' => trim((string)($ctx['db_name'] ?? '')),
            'service_name' => trim((string)($ctx['service_name'] ?? '')),
            'instance_name' => trim((string)($ctx['instance_name'] ?? '')),
            'hospital_names' => array_keys($names),
        ];

        $identity['fingerprint'] = sha1(strtolower(json_encode($identity)));
        return $identity;
    }

    /**
     * @param array<string, mixed> $identity
     */
    private function identityMatchesBranch(string $branch, array $identity): bool
    {
        $global = strtolower((string)($identity['global_name'] ?? ''));
        $db = strtolower((string)($identity['db_name'] ?? ''));
        $service = strtolower((string)($identity['service_name'] ?? ''));
        $instance = strtolower((string)($identity['instance_name'] ?? ''));
        $names = strtolower(implode(' ', (array)($identity['hospital_names'] ?? [])));
        $technical = $global . ' ' . $db . ' ' . $service . ' ' . $instance;

        if ($branch === 'njch') {
            return strpos($technical, 'oranjc') !== false
                || strpos($technical, 'njch') !== false
                || strpos($names, 'new jeddah') !== false;
        }

        if ($branch === 'jckh') {
            if (strpos($technical, 'orajck') !== false
                || strpos($technical, 'jckh') !== false
                || strpos($technical, 'jchk') !== false) {
                return true;
            }

            return strpos($names, 'jeddah') !== false
                && strpos($names, 'new jeddah') === false;
        }

        if ($branch === 'mnh') {
            return strpos($technical, 'oramnh') !== false
                || strpos($technical, 'mnh') !== false
                || strpos($names, 'madina') !== false
                || strpos($names, 'medina') !== false;
        }

        return false;
    }

    /**
     * @return array<int, string>
     */
    private function tnsFiles(): array
    {
        $files = [];

        $tnsAdmin = trim((string)getenv('TNS_ADMIN'));
        if ($tnsAdmin !== '') {
            $files[] = rtrim($tnsAdmin, '\\/') . DIRECTORY_SEPARATOR . 'tnsnames.ora';
        }

        $oracleHome = trim((string)getenv('ORACLE_HOME'));
        if ($oracleHome !== '') {
            $files[] = rtrim($oracleHome, '\\/') . DIRECTORY_SEPARATOR . 'network'
                . DIRECTORY_SEPARATOR . 'admin' . DIRECTORY_SEPARATOR . 'tnsnames.ora';
        }

        $path = (string)getenv('PATH');
        foreach (explode(PATH_SEPARATOR, $path) as $dir) {
            $dir = trim($dir, " \t\n\r\0\x0B\"'");
            if ($dir === '') {
                continue;
            }
            $lower = strtolower($dir);
            if (strpos($lower, 'oracle') === false && strpos($lower, 'instantclient') === false) {
                continue;
            }
            $files[] = rtrim($dir, '\\/') . DIRECTORY_SEPARATOR . 'network'
                . DIRECTORY_SEPARATOR . 'admin' . DIRECTORY_SEPARATOR . 'tnsnames.ora';
            $files[] = dirname($dir) . DIRECTORY_SEPARATOR . 'network'
                . DIRECTORY_SEPARATOR . 'admin' . DIRECTORY_SEPARATOR . 'tnsnames.ora';
        }

        foreach (['C:\\oracle', 'C:\\app', 'D:\\oracle', 'D:\\app'] as $root) {
            if (!is_dir($root)) {
                continue;
            }
            foreach ([
                $root . '\\*\\network\\admin\\tnsnames.ora',
                $root . '\\*\\*\\network\\admin\\tnsnames.ora',
                $root . '\\*\\*\\*\\network\\admin\\tnsnames.ora',
            ] as $pattern) {
                foreach ((array)glob($pattern) as $file) {
                    $files[] = $file;
                }
            }
        }

        $unique = [];
        foreach ($files as $file) {
            if (is_file($file)) {
                $unique[strtolower((string)realpath($file))] = $file;
            }
        }
        return array_values($unique);
    }

    /**
     * @return array<string, string>
     */
    private function parseTnsEntries(string $text): array
    {
        $entries = [];
        if (!preg_match_all(
            '/^[ \t]*([A-Za-z0-9_.-]+)[ \t]*=/m',
            $text,
            $matches,
            PREG_OFFSET_CAPTURE
        )) {
            return $entries;
        }

        foreach ($matches[1] as $index => $aliasMatch) {
            $alias = (string)$aliasMatch[0];
            $offset = (int)$matches[0][$index][1] + strlen((string)$matches[0][$index][0]);
            $start = stripos($text, '(DESCRIPTION', $offset);
            if ($start === false) {
                continue;
            }
            $nextAliasOffset = isset($matches[0][$index + 1])
                ? (int)$matches[0][$index + 1][1]
                : strlen($text);
            if ($start >= $nextAliasOffset) {
                continue;
            }

            $descriptor = $this->balancedParentheses($text, $start);
            if ($descriptor !== '') {
                $entries[$alias] = $descriptor;
            }
        }

        return $entries;
    }

    private function balancedParentheses(string $text, int $start): string
    {
        $depth = 0;
        $length = strlen($text);

        for ($i = $start; $i < $length; $i++) {
            $char = $text[$i];
            if ($char === '(') {
                $depth++;
            } elseif ($char === ')') {
                $depth--;
                if ($depth === 0) {
                    return substr($text, $start, $i - $start + 1);
                }
            }
        }

        return '';
    }

    private function withConnectTimeout(string $descriptor): string
    {
        if (stripos($descriptor, 'CONNECT_TIMEOUT') !== false) {
            return $descriptor;
        }

        return preg_replace(
            '/^\(DESCRIPTION\s*=/i',
            '(DESCRIPTION=(CONNECT_TIMEOUT=3)(RETRY_COUNT=0)',
            $descriptor,
            1
        ) ?: $descriptor;
    }

    /**
     * @return array<int, string>
     */
    private function legacyConfigFiles(): array
    {
        $base = \base_path();
        $webRoot = dirname($base);
        $roots = [];

        foreach ((array)glob($webRoot . DIRECTORY_SEPARATOR . '*') as $dir) {
            if (!is_dir($dir) || realpath($dir) === realpath($base)) {
                continue;
            }
            $name = strtolower(basename($dir));
            if (strpos($name, 'ddb') !== false
                || strpos($name, 'dashboard') !== false
                || strpos($name, 'hospital') !== false) {
                $roots[] = $dir;
            }
        }

        foreach ([
            $webRoot . DIRECTORY_SEPARATOR . 'ddb',
            $webRoot . DIRECTORY_SEPARATOR . 'DDB',
        ] as $dir) {
            if (is_dir($dir)) {
                $roots[] = $dir;
            }
        }

        $files = [];
        $seen = [];
        foreach (array_unique($roots) as $root) {
            try {
                $iterator = new \RecursiveIteratorIterator(
                    new \RecursiveDirectoryIterator(
                        $root,
                        \FilesystemIterator::SKIP_DOTS
                    )
                );
                foreach ($iterator as $info) {
                    if (count($files) >= 450) {
                        break 2;
                    }
                    if (!$info->isFile()) {
                        continue;
                    }
                    $path = $info->getPathname();
                    $ext = strtolower(pathinfo($path, PATHINFO_EXTENSION));
                    $baseName = strtolower(basename($path));

                    if (!in_array($ext, ['php', 'ini', 'env', 'conf', 'config', 'txt'], true)
                        && strpos($baseName, '.env') !== 0) {
                        continue;
                    }

                    $relativeDepth = substr_count(
                        str_replace($root, '', dirname($path)),
                        DIRECTORY_SEPARATOR
                    );
                    if ($relativeDepth > 5) {
                        continue;
                    }

                    $key = strtolower((string)realpath($path));
                    if (!isset($seen[$key])) {
                        $seen[$key] = true;
                        $files[] = $path;
                    }
                }
            } catch (Throwable $ignore) {
                continue;
            }
        }

        usort($files, static function (string $a, string $b): int {
            $priority = static function (string $path): int {
                $name = strtolower(basename($path));
                if ($name === 'sidebar_configuration_njch.php') {
                    return 100;
                }
                if (strpos($name, 'database') !== false || strpos($name, 'connection') !== false) {
                    return 80;
                }
                if (strpos($name, 'config') !== false || strpos($name, '.env') === 0) {
                    return 60;
                }
                return 0;
            };
            return $priority($b) <=> $priority($a);
        });

        return $files;
    }

    /**
     * @return array<int, array<string, mixed>>
     */
    private function extractEndpointCandidates(
        string $branch,
        string $text,
        string $source
    ): array {
        $out = [];
        $prefix = strtoupper($branch);

        $dotenv = [];
        foreach ([
            'HOST' => 'host',
            'PORT' => 'port',
            'SERVICE' => 'service',
            'SID' => 'sid',
            'TNS' => 'tns',
            'CONNECT_TYPE' => 'connect_type',
        ] as $suffix => $target) {
            if (preg_match(
                '/^[ \t]*DB_' . preg_quote($prefix, '/') . '_' . $suffix
                . '[ \t]*=[ \t]*["\']?([^"\'\r\n]+)["\']?[ \t]*$/mi',
                $text,
                $match
            )) {
                $dotenv[$target] = trim((string)$match[1]);
            }
        }
        if ($dotenv) {
            $out[] = $this->dynamicCandidateFromEndpoint($branch, $dotenv, $source . ':DOTENV');
        }

        $windows = [];
        foreach (self::ALIASES[$branch] as $alias) {
            $offset = 0;
            while (($pos = stripos($text, $alias, $offset)) !== false) {
                $windows[] = substr($text, max(0, $pos - 2500), 5000);
                $offset = $pos + strlen($alias);
                if (count($windows) >= 30) {
                    break 2;
                }
            }
        }

        foreach ($windows as $window) {
            $descOffset = 0;
            while (($pos = stripos($window, '(DESCRIPTION', $descOffset)) !== false) {
                $descriptor = $this->balancedParentheses($window, $pos);
                if ($descriptor !== '') {
                    $out[] = $this->dynamicCandidateFromEndpoint(
                        $branch,
                        [
                            'connect_type' => 'tns',
                            'tns' => $this->withConnectTimeout($descriptor),
                        ],
                        $source . ':DESCRIPTOR'
                    );
                }
                $descOffset = $pos + 12;
            }

            if (preg_match_all(
                '#//([A-Za-z0-9_.-]+):([0-9]{2,5})/([A-Za-z0-9_.-]+)#',
                $window,
                $matches,
                PREG_SET_ORDER
            )) {
                foreach ($matches as $match) {
                    $out[] = $this->dynamicCandidateFromEndpoint(
                        $branch,
                        [
                            'connect_type' => 'service',
                            'host' => $match[1],
                            'port' => $match[2],
                            'service' => $match[3],
                        ],
                        $source . ':EZCONNECT'
                    );
                }
            }

            $hosts = [];
            if (preg_match_all(
                '/(?:db_?host|hostname|host|server|ip)[A-Za-z0-9_$>\-\[\]"\']*'
                . '\s*(?:=|=>|:)\s*["\']([A-Za-z0-9_.-]+)["\']/i',
                $window,
                $matches
            )) {
                foreach ($matches[1] as $host) {
                    $hosts[$host] = true;
                }
            }
            if (preg_match_all('/\b(?:\d{1,3}\.){3}\d{1,3}\b/', $window, $matches)) {
                foreach ($matches[0] as $host) {
                    $hosts[$host] = true;
                }
            }

            $services = [];
            if (preg_match_all(
                '/(?:service(?:_name)?|sid|dbname|database)[A-Za-z0-9_$>\-\[\]"\']*'
                . '\s*(?:=|=>|:)\s*["\']([A-Za-z0-9_.-]+)["\']/i',
                $window,
                $matches
            )) {
                foreach ($matches[1] as $service) {
                    $services[$service] = true;
                }
            }
            if (preg_match_all('/\bORA[A-Z0-9_]{2,20}\b/i', $window, $matches)) {
                foreach ($matches[0] as $service) {
                    $services[$service] = true;
                }
            }

            foreach (array_slice(array_keys($hosts), 0, 4) as $host) {
                foreach (array_slice(array_keys($services), 0, 4) as $service) {
                    $out[] = $this->dynamicCandidateFromEndpoint(
                        $branch,
                        [
                            'connect_type' => 'service',
                            'host' => $host,
                            'port' => '1521',
                            'service' => $service,
                        ],
                        $source . ':HOST_SERVICE'
                    );
                    $out[] = $this->dynamicCandidateFromEndpoint(
                        $branch,
                        [
                            'connect_type' => 'sid',
                            'host' => $host,
                            'port' => '1521',
                            'sid' => $service,
                        ],
                        $source . ':HOST_SID'
                    );
                }
            }
        }

        $dedup = [];
        foreach ($out as $candidate) {
            $dedup[$this->candidateSignature($candidate)] = $candidate;
        }
        return array_slice(array_values($dedup), 0, $this->candidateLimit);
    }

    /**
     * @param array<string, mixed> $candidate
     */
    private function candidateSignature(array $candidate): string
    {
        if (($candidate['kind'] ?? '') === 'DB_LINK') {
            return 'link:' . strtolower((string)($candidate['db_link'] ?? ''));
        }
        return sha1(json_encode($this->safeDatabase((array)($candidate['database'] ?? []))));
    }

    /**
     * @param array<string, mixed> $db
     * @return array<string, mixed>
     */
    private function safeDatabase(array $db): array
    {
        $safe = [];
        foreach (['connect_type', 'tns', 'host', 'port', 'service', 'sid', 'odbc_dsn'] as $key) {
            if (isset($db[$key]) && trim((string)$db[$key]) !== '') {
                $safe[$key] = (string)$db[$key];
            }
        }
        return $safe;
    }

    /**
     * @param array<string, mixed> $candidate
     */
    private function candidateEndpointHint(array $candidate): string
    {
        if (($candidate['kind'] ?? '') === 'DB_LINK') {
            return 'DB_LINK:' . (string)($candidate['db_link'] ?? '');
        }

        $db = $this->safeDatabase((array)($candidate['database'] ?? []));
        if (isset($db['host'])) {
            return strtoupper((string)($db['connect_type'] ?? 'service'))
                . ':' . $this->maskHost((string)$db['host'])
                . ':' . (string)($db['port'] ?? '1521')
                . '/' . (string)($db['service'] ?? $db['sid'] ?? '');
        }
        if (isset($db['tns'])) {
            return 'TNS:' . substr(sha1((string)$db['tns']), 0, 12);
        }
        return 'CONFIGURED';
    }

    private function maskHost(string $host): string
    {
        if (preg_match('/^(\d+)\.(\d+)\.(\d+)\.(\d+)$/', $host, $m)) {
            return $m[1] . '.' . $m[2] . '.x.x';
        }
        $parts = explode('.', $host);
        return $parts[0] . (count($parts) > 1 ? '.***' : '');
    }

    /**
     * @param array<string, mixed> $resolution
     * @return array<string, mixed>
     */
    private function publicResolution(array $resolution): array
    {
        $identity = $this->publicIdentity((array)($resolution['identity'] ?? []));
        $out = [
            'mode' => (string)($resolution['mode'] ?? ''),
            'source' => (string)($resolution['source'] ?? ''),
            'identity' => $identity,
        ];

        if (($resolution['mode'] ?? '') === 'DB_LINK') {
            $out['database_link'] = (string)($resolution['db_link'] ?? '');
        } elseif (($resolution['mode'] ?? '') === 'CONFIGURED_DIRECT') {
            $out['connection'] = (string)($resolution['connection'] ?? '');
            $out['endpoint'] = 'CURRENT_HOSPITAL_CONFIG';
        } else {
            $out['endpoint'] = $this->candidateEndpointHint([
                'kind' => 'DYNAMIC_DIRECT',
                'database' => (array)($resolution['database'] ?? []),
            ]);
        }

        return $out;
    }

    /**
     * @param array<string, mixed> $identity
     * @return array<string, mixed>
     */
    private function publicIdentity(array $identity): array
    {
        return [
            'global_name' => (string)($identity['global_name'] ?? ''),
            'db_name' => (string)($identity['db_name'] ?? ''),
            'service_name' => (string)($identity['service_name'] ?? ''),
            'instance_name' => (string)($identity['instance_name'] ?? ''),
            'hospital_names' => (array)($identity['hospital_names'] ?? []),
            'fingerprint' => (string)($identity['fingerprint'] ?? ''),
        ];
    }

    private function aliasScore(string $branch, string $text): int
    {
        $haystack = strtolower($text);
        $score = 0;
        foreach (self::ALIASES[$branch] as $alias) {
            if (strpos($haystack, strtolower($alias)) !== false) {
                $score += strlen($alias) >= 5 ? 5 : 2;
            }
        }
        return $score;
    }

    private function safeDbLinkIdentifier(string $value): bool
    {
        return preg_match('/^[A-Za-z0-9_$#.]+$/', $value) === 1;
    }

    private function normalizeBranch(string $branch): string
    {
        $branch = strtolower(trim($branch));
        if (!in_array($branch, self::BRANCHES, true)) {
            throw new RuntimeException('Unsupported hospital branch: ' . $branch);
        }
        return $branch;
    }

    /**
     * @param array<string, mixed> $attempt
     */
    private function recordAttempt(string $branch, array $attempt): void
    {
        if (!isset($this->attempts[$branch])) {
            $this->attempts[$branch] = [];
        }
        $this->attempts[$branch][] = $attempt;
    }

    private function oracleErrorCode(Throwable $error): string
    {
        if (preg_match('/ORA-\d{5}/i', $error->getMessage(), $match)) {
            return strtoupper($match[0]);
        }
        return 'NON_ORACLE_OR_UNKNOWN';
    }

    private function redactError(string $message): string
    {
        $message = preg_replace(
            '/(password|passwd|pwd)\s*[:=]\s*[^;\s]+/i',
            '$1=<redacted>',
            $message
        ) ?: $message;
        $message = preg_replace(
            '/DB_[A-Z0-9_]*PASS\s*=\s*[^\s]+/i',
            'DB_PASS=<redacted>',
            $message
        ) ?: $message;

        return substr($message, 0, 1800);
    }

    private function cacheFile(): string
    {
        return \storage_path('cache/branch-oracle-resolution-v2.json');
    }

    /**
     * @return array<string, mixed>|null
     */
    private function loadCachedCandidate(string $branch)
    {
        $file = $this->cacheFile();
        if (!is_file($file)) {
            return null;
        }

        $raw = @file_get_contents($file);
        $data = is_string($raw) ? json_decode($raw, true) : null;
        if (!is_array($data) || (string)($data['schema_version'] ?? '') !== '2.0') {
            return null;
        }

        $entry = $data['branches'][$branch] ?? null;
        if (!is_array($entry)) {
            return null;
        }

        $savedAt = (int)($entry['saved_at'] ?? 0);
        if ($savedAt <= 0 || time() - $savedAt > 86400) {
            return null;
        }

        $candidate = $entry['candidate'] ?? null;
        if (!is_array($candidate)) {
            return null;
        }

        if (($candidate['kind'] ?? '') === 'DYNAMIC_DIRECT') {
            $base = (array)\config('hospitals.' . $branch . '.database', []);
            $candidate['database'] = array_merge(
                $base,
                (array)($candidate['database'] ?? [])
            );
        }

        return $candidate;
    }

    /**
     * @param array<string, mixed> $resolution
     */
    private function writeCache(string $branch, array $resolution): void
    {
        $candidate = null;

        if (($resolution['mode'] ?? '') === 'DB_LINK') {
            $candidate = [
                'kind' => 'DB_LINK',
                'branch' => $branch,
                'source' => 'VALIDATED_RESOLUTION_CACHE',
                'db_link' => (string)($resolution['db_link'] ?? ''),
            ];
        } elseif (($resolution['mode'] ?? '') === 'CONFIGURED_DIRECT') {
            $candidate = [
                'kind' => 'CONFIGURED_DIRECT',
                'branch' => $branch,
                'source' => 'VALIDATED_RESOLUTION_CACHE',
                'database' => (array)\config('hospitals.' . $branch . '.database', []),
            ];
        } elseif (($resolution['mode'] ?? '') === 'DYNAMIC_DIRECT') {
            $candidate = [
                'kind' => 'DYNAMIC_DIRECT',
                'branch' => $branch,
                'source' => 'VALIDATED_RESOLUTION_CACHE',
                'database' => $this->safeDatabase((array)($resolution['database'] ?? [])),
                'cache_key' => 'resolver:' . $branch . ':persisted',
            ];
        }

        if ($candidate === null) {
            return;
        }

        /*
         * Reattach credentials only in memory when a cached dynamic endpoint is
         * loaded. Never persist username/password in the cache.
         */
        if (($candidate['kind'] ?? '') === 'CONFIGURED_DIRECT') {
            unset($candidate['database']);
        }

        $file = $this->cacheFile();
        $dir = dirname($file);
        if (!is_dir($dir)) {
            @mkdir($dir, 0775, true);
        }

        $data = ['schema_version' => '2.0', 'branches' => []];
        if (is_file($file)) {
            $decoded = json_decode((string)@file_get_contents($file), true);
            if (is_array($decoded) && (string)($decoded['schema_version'] ?? '') === '2.0') {
                $data = $decoded;
            }
        }

        $data['branches'][$branch] = [
            'saved_at' => time(),
            'candidate' => $candidate,
        ];

        @file_put_contents(
            $file,
            json_encode($data, JSON_PRETTY_PRINT | JSON_UNESCAPED_SLASHES),
            LOCK_EX
        );
    }
}
