<?php

declare(strict_types=1);

namespace App\Core;

use PDO;
use RuntimeException;
use Throwable;

final class Database
{
    /** @var array<string, array<string, mixed>> */
    private static $connections = [];

    /**
     * Central Oracle connection factory.
     * Supports DB_DRIVER=pdo_oci, oci8, or odbc.
     * Supports Oracle connect types: service, sid, or tns.
     *
     * @return array{driver:string, conn:mixed, connect_name:string}
     */
    public static function connection(string $hospitalCode): array
    {
        $hospitalCode = strtolower($hospitalCode);
        if (isset(self::$connections[$hospitalCode])) {
            RequestTrace::add('oracle_connection_reused', 0.0);
            return self::$connections[$hospitalCode];
        }

        RequestTrace::begin('oracle_connection');
        $hospital = \config('hospitals.' . $hospitalCode);
        if (!$hospital || empty($hospital['database'])) {
            throw new RuntimeException('Hospital database config not found: ' . $hospitalCode);
        }

        $db = $hospital['database'];
        $driver = strtolower((string) \env('DB_DRIVER', 'pdo_oci'));

        foreach (['username', 'password'] as $required) {
            if (!isset($db[$required]) || trim((string)$db[$required]) === '') {
                throw new RuntimeException('Missing Oracle setting: ' . $hospitalCode . '.' . $required);
            }
        }

        if ($driver === 'odbc') {
            $result = self::connectOdbc($hospitalCode, $db);
            RequestTrace::end('oracle_connection');
            RequestTrace::context('oracle_driver', 'odbc');
            return $result;
        }

        $connectName = self::buildOracleConnectName($hospitalCode, $db);

        if ($driver === 'pdo_oci' || $driver === 'pdo') {
            if (!extension_loaded('pdo') || !in_array('oci', PDO::getAvailableDrivers(), true)) {
                throw new RuntimeException('PDO_OCI driver is not enabled. Enable php_pdo_oci.dll or set DB_DRIVER=oci8/odbc.');
            }

            $dsn = 'oci:dbname=' . $connectName . ';charset=AL32UTF8';
            $pdo = new PDO($dsn, (string)$db['username'], (string)$db['password'], [
                PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
                PDO::ATTR_CASE => PDO::CASE_LOWER,
                PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
                PDO::ATTR_EMULATE_PREPARES => false,
            ]);

            RequestTrace::end('oracle_connection');
            RequestTrace::context('oracle_driver', 'pdo_oci');
            self::$connections[$hospitalCode] = [
                'driver' => 'pdo_oci',
                'conn' => $pdo,
                'connect_name' => $connectName,
            ];
            return self::$connections[$hospitalCode];
        }

        if ($driver === 'oci8') {
            if (!extension_loaded('oci8')) {
                throw new RuntimeException('OCI8 extension is not enabled. Enable php_oci8.dll/php_oci8_19.dll or set DB_DRIVER=pdo_oci/odbc.');
            }

            $conn = @oci_pconnect((string)$db['username'], (string)$db['password'], $connectName, 'AL32UTF8');
            if (!$conn) {
                $error = oci_error();
                throw new RuntimeException('Oracle OCI8 connection failed: ' . ($error['message'] ?? 'unknown error'));
            }

            RequestTrace::end('oracle_connection');
            RequestTrace::context('oracle_driver', 'oci8');
            self::$connections[$hospitalCode] = [
                'driver' => 'oci8',
                'conn' => $conn,
                'connect_name' => $connectName,
            ];
            return self::$connections[$hospitalCode];
        }

        throw new RuntimeException('Unsupported DB_DRIVER: ' . $driver . '. Use pdo_oci, oci8, or odbc.');
    }

    /**
     * @param array<string, mixed> $db
     */
    private static function buildOracleConnectName(string $hospitalCode, array $db): string
    {
        $connectType = strtolower(trim((string)($db['connect_type'] ?? 'service')));

        if ($connectType === 'tns') {
            $tns = trim((string)($db['tns'] ?? ''));
            if ($tns === '') {
                throw new RuntimeException('Missing Oracle TNS setting: ' . $hospitalCode . '.tns');
            }
            return $tns;
        }

        $host = trim((string)($db['host'] ?? ''));
        $port = trim((string)($db['port'] ?? '1521'));

        if ($host === '') {
            throw new RuntimeException('Missing Oracle setting: ' . $hospitalCode . '.host. Set DB_' . strtoupper($hospitalCode) . '_HOST or use DB_' . strtoupper($hospitalCode) . '_CONNECT_TYPE=tns.');
        }
        if ($port === '') {
            throw new RuntimeException('Missing Oracle setting: ' . $hospitalCode . '.port');
        }

        if ($connectType === 'sid') {
            $sid = trim((string)($db['sid'] ?? ''));
            if ($sid === '') {
                throw new RuntimeException('Missing Oracle SID setting: ' . $hospitalCode . '.sid');
            }
            return sprintf(
                '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=%s)(PORT=%s))(CONNECT_DATA=(SID=%s)))',
                $host,
                $port,
                $sid
            );
        }

        $service = trim((string)($db['service'] ?? ''));
        if ($service === '') {
            throw new RuntimeException('Missing Oracle SERVICE setting: ' . $hospitalCode . '.service');
        }

        return sprintf('//%s:%s/%s', $host, $port, $service);
    }

    /**
     * @param array<string, mixed> $db
     * @return array{driver:string, conn:mixed, connect_name:string}
     */
    private static function connectOdbc(string $hospitalCode, array $db): array
    {
        if (!extension_loaded('odbc')) {
            throw new RuntimeException('ODBC extension is not enabled. Enable extension=odbc in php.ini and restart Apache.');
        }

        $dsn = trim((string)($db['odbc_dsn'] ?? ''));
        if ($dsn === '') {
            $driverName = trim((string) \env('ODBC_ORACLE_DRIVER', 'Oracle in OraClient10g_home1'));
            $connectName = self::buildOracleConnectName($hospitalCode, $db);
            $dsn = 'Driver={' . $driverName . '};Dbq=' . $connectName . ';';
        } else {
            $connectName = $dsn;
        }

        $conn = @odbc_connect($dsn, (string)$db['username'], (string)$db['password']);
        if (!$conn) {
            throw new RuntimeException('Oracle ODBC connection failed: ' . odbc_errormsg());
        }

        self::$connections[$hospitalCode] = [
            'driver' => 'odbc',
            'conn' => $conn,
            'connect_name' => $connectName,
        ];
        return self::$connections[$hospitalCode];
    }

    /**
     * @param array<string, mixed> $params
     * @return array<int, array<string, mixed>>
     */
    public static function fetchAll(string $hospitalCode, string $sql, array $params = []): array
    {
        $connection = self::connection($hospitalCode);
        $driver = $connection['driver'];
        $conn = $connection['conn'];

        if ($driver === 'pdo_oci' && $conn instanceof PDO) {
            $stmt = $conn->prepare($sql);
            foreach ($params as $key => $value) {
                $key = (string) $key;
                $param = substr($key, 0, 1) === ':' ? $key : ':' . $key;
                $stmt->bindValue($param, $value, is_int($value) ? PDO::PARAM_INT : PDO::PARAM_STR);
            }
            $stmt->execute();
            return $stmt->fetchAll();
        }

        if ($driver === 'odbc') {
            $odbcSql = self::convertNamedParamsToQuestionMarks($sql, $params, $orderedValues);
            $stmt = @odbc_prepare($conn, $odbcSql);
            if (!$stmt) {
                throw new RuntimeException('ODBC prepare failed: ' . odbc_errormsg($conn));
            }
            $ok = @odbc_execute($stmt, $orderedValues);
            if (!$ok) {
                throw new RuntimeException('ODBC execute failed: ' . odbc_errormsg($conn));
            }
            $rows = [];
            while (($row = odbc_fetch_array($stmt)) !== false) {
                $rows[] = array_change_key_case($row, CASE_LOWER);
            }
            odbc_free_result($stmt);
            return $rows;
        }

        $stmt = @oci_parse($conn, $sql);
        if (!$stmt) {
            $error = oci_error($conn);
            throw new RuntimeException('Oracle parse failed: ' . ($error['message'] ?? 'unknown error'));
        }

        $bindValues = [];
        foreach ($params as $key => $value) {
            $key = (string) $key;
            $param = substr($key, 0, 1) === ':' ? $key : ':' . $key;
            $bindValues[$param] = $value;
            if (!@oci_bind_by_name($stmt, $param, $bindValues[$param])) {
                $error = oci_error($stmt);
                throw new RuntimeException('Oracle bind failed for ' . $param . ': ' . ($error['message'] ?? 'unknown error'));
            }
        }

        $ok = @oci_execute($stmt, OCI_NO_AUTO_COMMIT);
        if (!$ok) {
            $error = oci_error($stmt);
            throw new RuntimeException('Oracle execute failed: ' . ($error['message'] ?? 'unknown error'));
        }

        $rows = [];
        while (($row = oci_fetch_array($stmt, OCI_ASSOC + OCI_RETURN_NULLS)) !== false) {
            $rows[] = array_change_key_case($row, CASE_LOWER);
        }
        oci_free_statement($stmt);
        return $rows;
    }

    /**
     * @param array<string, mixed> $params
     * @param array<int, mixed> $orderedValues
     */
    private static function convertNamedParamsToQuestionMarks(string $sql, array $params, &$orderedValues): string
    {
        $orderedValues = [];
        return preg_replace_callback('/:[a-zA-Z_][a-zA-Z0-9_]*/', function ($matches) use ($params, &$orderedValues) {
            $name = $matches[0];
            $withoutColon = substr($name, 1);
            if (array_key_exists($name, $params)) {
                $orderedValues[] = $params[$name];
                return '?';
            }
            if (array_key_exists($withoutColon, $params)) {
                $orderedValues[] = $params[$withoutColon];
                return '?';
            }
            return $name;
        }, $sql);
    }

    /**
     * @param array<string, mixed> $params
     * @return array<string, mixed>|null
     */
    public static function fetchOne(string $hospitalCode, string $sql, array $params = [])
    {
        $rows = self::fetchAll($hospitalCode, $sql, $params);
        return $rows[0] ?? null;
    }

    /**
     * Execute DDL/DML statement with bind parameters.
     *
     * @param array<string, mixed> $params
     */
    public static function execute(string $hospitalCode, string $sql, array $params = []): int
    {
        $connection = self::connection($hospitalCode);
        $driver = $connection['driver'];
        $conn = $connection['conn'];

        if ($driver === 'pdo_oci' && $conn instanceof PDO) {
            return self::executePdoStatement($conn, $sql, $params);
        }

        if ($driver === 'odbc') {
            return self::executeOdbcStatement($conn, $sql, $params);
        }

        return self::executeOciStatement($conn, $sql, $params, OCI_COMMIT_ON_SUCCESS);
    }

    /**
     * Execute multiple DML statements atomically when the selected driver supports transactions.
     *
     * This is used by the Executive Action Review workflow so the review row and its
     * audit-trail row succeed or fail together instead of saving only one side.
     *
     * @param array<int,array{sql:string,params?:array<string,mixed>}> $statements
     */
    public static function executeTransaction(string $hospitalCode, array $statements): void
    {
        if (!$statements) {
            return;
        }

        $connection = self::connection($hospitalCode);
        $driver = $connection['driver'];
        $conn = $connection['conn'];

        if ($driver === 'pdo_oci' && $conn instanceof PDO) {
            try {
                $conn->beginTransaction();
                foreach ($statements as $statement) {
                    self::executePdoStatement($conn, (string)$statement['sql'], (array)($statement['params'] ?? []));
                }
                $conn->commit();
                return;
            } catch (Throwable $e) {
                try { if ($conn->inTransaction()) { $conn->rollBack(); } } catch (Throwable $ignore) {}
                throw $e;
            }
        }

        if ($driver === 'odbc') {
            $autoCommitChanged = false;
            try {
                if (@odbc_autocommit($conn, false)) {
                    $autoCommitChanged = true;
                }
                foreach ($statements as $statement) {
                    self::executeOdbcStatement($conn, (string)$statement['sql'], (array)($statement['params'] ?? []));
                }
                if (!@odbc_commit($conn)) {
                    throw new RuntimeException('ODBC commit failed: ' . odbc_errormsg($conn));
                }
                if ($autoCommitChanged) { @odbc_autocommit($conn, true); }
                return;
            } catch (Throwable $e) {
                @odbc_rollback($conn);
                if ($autoCommitChanged) { @odbc_autocommit($conn, true); }
                throw $e;
            }
        }

        try {
            foreach ($statements as $statement) {
                self::executeOciStatement($conn, (string)$statement['sql'], (array)($statement['params'] ?? []), OCI_NO_AUTO_COMMIT);
            }
            if (!@oci_commit($conn)) {
                $error = oci_error($conn);
                throw new RuntimeException('Oracle commit failed: ' . ($error['message'] ?? 'unknown error'));
            }
        } catch (Throwable $e) {
            @oci_rollback($conn);
            throw $e;
        }
    }

    /** @param array<string,mixed> $params */
    private static function executePdoStatement(PDO $conn, string $sql, array $params): int
    {
        $stmt = $conn->prepare($sql);
        foreach ($params as $key => $value) {
            $key = (string) $key;
            $param = substr($key, 0, 1) === ':' ? $key : ':' . $key;
            $stmt->bindValue($param, $value, is_int($value) ? PDO::PARAM_INT : PDO::PARAM_STR);
        }
        $stmt->execute();
        return (int) $stmt->rowCount();
    }

    /** @param array<string,mixed> $params */
    private static function executeOdbcStatement($conn, string $sql, array $params): int
    {
        $odbcSql = self::convertNamedParamsToQuestionMarks($sql, $params, $orderedValues);
        $stmt = @odbc_prepare($conn, $odbcSql);
        if (!$stmt) {
            throw new RuntimeException('ODBC prepare failed: ' . odbc_errormsg($conn));
        }
        $ok = @odbc_execute($stmt, $orderedValues);
        if (!$ok) {
            throw new RuntimeException('ODBC execute failed: ' . odbc_errormsg($conn));
        }
        $count = @odbc_num_rows($stmt);
        odbc_free_result($stmt);
        return is_int($count) ? $count : 0;
    }

    /** @param array<string,mixed> $params */
    private static function executeOciStatement($conn, string $sql, array $params, int $mode): int
    {
        $stmt = @oci_parse($conn, $sql);
        if (!$stmt) {
            $error = oci_error($conn);
            throw new RuntimeException('Oracle parse failed: ' . ($error['message'] ?? 'unknown error'));
        }

        $bindValues = [];
        foreach ($params as $key => $value) {
            $key = (string) $key;
            $param = substr($key, 0, 1) === ':' ? $key : ':' . $key;
            $bindValues[$param] = $value;
            if (!@oci_bind_by_name($stmt, $param, $bindValues[$param])) {
                $error = oci_error($stmt);
                oci_free_statement($stmt);
                throw new RuntimeException('Oracle bind failed for ' . $param . ': ' . ($error['message'] ?? 'unknown error'));
            }
        }

        $ok = @oci_execute($stmt, $mode);
        if (!$ok) {
            $error = oci_error($stmt);
            oci_free_statement($stmt);
            throw new RuntimeException('Oracle execute failed: ' . ($error['message'] ?? 'unknown error'));
        }
        $count = oci_num_rows($stmt);
        oci_free_statement($stmt);
        return (int) $count;
    }

}
