<?php

declare(strict_types=1);

namespace App\Services;

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

/**
 * Deterministic Oracle branch resolver for the authoritative HMH SID endpoints.
 *
 * The exact endpoints were confirmed by successful SQL Developer tests.
 * No discovery scans, DB-link guessing, TNS crawling, or legacy-folder searches
 * are performed. Each branch must:
 * - use the configured SID transport;
 * - connect successfully;
 * - prove its Oracle identity;
 * - remain distinct from the other branches.
 *
 * Oracle writes: NONE.
 */
final class BranchOracleAccessResolver
{
    /** @var array<int, string> */
    private const BRANCHES = ['njch', 'jckh', 'mnh'];

    /** @var array<string, array<string, string>> */
    private const EXPECTED = [
        'njch' => [
            'connect_type' => 'sid',
            'host' => '10.10.12.10',
            'port' => '1521',
            'sid' => 'ORANJC',
        ],
        'jckh' => [
            'connect_type' => 'sid',
            'host' => '10.11.42.10',
            'port' => '1521',
            'sid' => 'orajck',
        ],
        'mnh' => [
            'connect_type' => 'sid',
            'host' => '10.20.104.20',
            'port' => '1521',
            'sid' => 'oramnh',
        ],
    ];

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

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

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

    /**
     * @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);

        $rows = Database::fetchAll(
            $branch,
            (string)$sqlFactory(''),
            $params
        );

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

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

    /**
     * @return array<string, mixed>
     */
    public function diagnostics(): array
    {
        return [
            'schema_version' => '3.0',
            'policy' => 'STRICT_CONFIGURED_SID_DIRECT',
            'branches_expected' => self::BRANCHES,
            'resolved' => $this->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 (!BranchReadinessPolicy::isBlockingQa($branch)) {
            throw new RuntimeException(
                'DEFERRED_BRANCH_NOT_READY branch=' . strtoupper($branch)
                . '; state=' . BranchReadinessPolicy::state($branch)
                . '; blocking_qa=false'
            );
        }
        if (isset($this->resolved[$branch])) {
            return $this->resolved[$branch];
        }

        $this->attempts[$branch] = [];
        $expected = self::EXPECTED[$branch];
        $database = (array)\config('hospitals.' . $branch . '.database', []);

        $configured = [
            'connect_type' => strtolower(trim((string)($database['connect_type'] ?? ''))),
            'host' => trim((string)($database['host'] ?? '')),
            'port' => trim((string)($database['port'] ?? '')),
            'sid' => trim((string)($database['sid'] ?? '')),
            'username_present' => trim((string)($database['username'] ?? '')) !== '',
            'password_present' => trim((string)($database['password'] ?? '')) !== '',
        ];

        $configOk = (
            $configured['connect_type'] === 'sid'
            && $configured['host'] === $expected['host']
            && $configured['port'] === $expected['port']
            && strcasecmp($configured['sid'], $expected['sid']) === 0
            && $configured['username_present']
            && $configured['password_present']
        );

        $this->attempts[$branch][] = [
            'source' => 'AUTHORITATIVE_ENV_SID_CONTRACT',
            'status' => $configOk ? 'PASS' : 'FAIL',
            'configured' => [
                'connect_type' => $configured['connect_type'],
                'host' => $this->maskHost($configured['host']),
                'port' => $configured['port'],
                'sid' => $configured['sid'],
                'username_present' => $configured['username_present'],
                'password_present' => $configured['password_present'],
            ],
            'expected' => [
                'connect_type' => $expected['connect_type'],
                'host' => $this->maskHost($expected['host']),
                'port' => $expected['port'],
                'sid' => $expected['sid'],
            ],
        ];

        if (!$configOk) {
            throw new RuntimeException(
                'BRANCH_SID_CONFIG_MISMATCH branch=' . strtoupper($branch)
                . '; expected=' . $expected['host'] . ':' . $expected['port']
                . '/SID=' . $expected['sid']
                . '; configured_type=' . $configured['connect_type']
                . '; configured_host=' . $configured['host']
                . '; configured_port=' . $configured['port']
                . '; configured_sid=' . $configured['sid']
            );
        }

        try {
            $identity = $this->probeIdentity($branch);
        } catch (Throwable $error) {
            $this->attempts[$branch][] = [
                'source' => 'DIRECT_SID_ORACLE_PROBE',
                'status' => 'FAIL',
                'error_code' => $this->oracleErrorCode($error),
                'error' => $this->redactError($error->getMessage()),
            ];

            throw new RuntimeException(
                'BRANCH_SID_DIRECT_CONNECT_FAILED branch=' . strtoupper($branch)
                . '; endpoint=' . $expected['host'] . ':' . $expected['port']
                . '/SID=' . $expected['sid']
                . '; oracle_error=' . $this->oracleErrorCode($error)
                . '; message=' . $this->redactError($error->getMessage()),
                0,
                $error
            );
        }

        if (!$this->identityMatchesBranch($branch, $identity)) {
            $this->attempts[$branch][] = [
                'source' => 'DIRECT_SID_ORACLE_IDENTITY',
                'status' => 'REJECTED_IDENTITY_MISMATCH',
                'identity' => $this->publicIdentity($identity),
            ];

            throw new RuntimeException(
                'BRANCH_ORACLE_IDENTITY_MISMATCH branch=' . strtoupper($branch)
                . '; global_name=' . (string)($identity['global_name'] ?? '')
                . '; db_name=' . (string)($identity['db_name'] ?? '')
                . '; service_name=' . (string)($identity['service_name'] ?? '')
                . '; instance_name=' . (string)($identity['instance_name'] ?? '')
            );
        }

        $resolution = [
            'mode' => 'CONFIGURED_SID_DIRECT',
            'source' => 'USER_CONFIRMED_SQL_DEVELOPER_SUCCESS_SCREENSHOTS',
            'endpoint' => [
                'host' => $this->maskHost($expected['host']),
                'port' => $expected['port'],
                'sid' => $expected['sid'],
            ],
            'identity' => $this->publicIdentity($identity),
        ];

        $this->attempts[$branch][] = [
            'source' => 'DIRECT_SID_ORACLE_PROBE',
            'status' => 'PASS',
            'identity' => $this->publicIdentity($identity),
        ];

        $this->resolved[$branch] = $resolution;
        return $resolution;
    }

    /**
     * @return array<string, mixed>
     */
    private function probeIdentity(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 = [];
        try {
            $hospital = Database::fetchAll(
                $branch,
                'SELECT hosp_id, hosp_en_name, hosp_short_name_en
                 FROM hospital_main_data
                 WHERE ROWNUM <= 20'
            );
        } catch (Throwable $ignored) {
            $hospital = [];
        }

        $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((string)json_encode($identity)));
        return $identity;
    }

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

        $names = strtolower(implode(' ', (array)($identity['hospital_names'] ?? [])));

        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, 'jck') !== 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;
    }

    /**
     * @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 normalizeBranch(string $branch): string
    {
        $branch = strtolower(trim($branch));
        if (!in_array($branch, self::BRANCHES, true)) {
            throw new RuntimeException('Unsupported hospital branch: ' . $branch);
        }
        return $branch;
    }

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

    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);
    }
}
