<?php

declare(strict_types=1);

namespace PpPhp\Core;

/**
 * Driver-aware project database profiles.
 *
 * The platform database can remain MySQL while every generated project keeps
 * its own portable connection profile. Legacy project configs without a
 * `type` key are intentionally treated as MySQL.
 */
final class DatabaseConnection
{
    private const TYPE_ALIASES = [
        'postgres' => 'pgsql',
        'postgresql' => 'pgsql',
        'mssql' => 'sqlsrv',
        'sqlserver' => 'sqlsrv',
        'microsoft sql server' => 'sqlsrv',
        'oracle' => 'oci',
        'cockroach' => 'cockroachdb',
        'crdb' => 'cockroachdb',
    ];

    public static function definitions(): array
    {
        $definitions = [
            'mysql' => [
                'label' => 'MySQL',
                'family' => 'MySQL / MariaDB',
                'icon' => 'database-fill',
                'color' => '#2563eb',
                'tint' => '#eff6ff',
                'default_host' => '127.0.0.1',
                'default_port' => '3306',
                'default_charset' => 'utf8mb4',
                'pdo_drivers' => ['mysql'],
                'description' => 'The most common choice for PHP hosting and shared servers.',
            ],
            'mariadb' => [
                'label' => 'MariaDB',
                'family' => 'MySQL / MariaDB',
                'icon' => 'database-check',
                'color' => '#0f766e',
                'tint' => '#f0fdfa',
                'default_host' => '127.0.0.1',
                'default_port' => '3306',
                'default_charset' => 'utf8mb4',
                'pdo_drivers' => ['mysql'],
                'description' => 'A community-led MySQL-compatible database with familiar tooling.',
            ],
            'tidb' => [
                'label' => 'TiDB',
                'family' => 'MySQL / MariaDB',
                'icon' => 'diagram-3',
                'color' => '#db6b24',
                'tint' => '#fff7ed',
                'default_host' => '127.0.0.1',
                'default_port' => '4000',
                'default_charset' => 'utf8mb4',
                'pdo_drivers' => ['mysql'],
                'description' => 'Distributed, MySQL-compatible SQL that scales horizontally — connects over PDO_MYSQL.',
            ],
            'pgsql' => [
                'label' => 'PostgreSQL',
                'family' => 'PostgreSQL',
                'icon' => 'database-gear',
                'color' => '#336791',
                'tint' => '#eff6ff',
                'default_host' => '127.0.0.1',
                'default_port' => '5432',
                'default_charset' => 'UTF8',
                'pdo_drivers' => ['pgsql'],
                'description' => 'Powerful relational features, rich data types, and excellent standards support.',
            ],
            'cockroachdb' => [
                'label' => 'CockroachDB',
                'family' => 'PostgreSQL',
                'icon' => 'diagram-2',
                'color' => '#6933ff',
                'tint' => '#f5f3ff',
                'default_host' => '127.0.0.1',
                'default_port' => '26257',
                'default_charset' => 'UTF8',
                'pdo_drivers' => ['pgsql'],
                'description' => 'Distributed, PostgreSQL-compatible SQL built for resilience — connects over PDO_PGSQL.',
            ],
            'sqlite' => [
                'label' => 'SQLite',
                'family' => 'SQLite',
                'icon' => 'device-ssd',
                'color' => '#0891b2',
                'tint' => '#ecfeff',
                'default_host' => '',
                'default_port' => '',
                'default_charset' => 'UTF-8',
                'pdo_drivers' => ['sqlite'],
                'description' => 'A portable single-file database—ideal for prototypes and smaller deployments.',
            ],
            'sqlsrv' => [
                'label' => 'Microsoft SQL Server',
                'family' => 'SQL Server',
                'icon' => 'windows',
                'color' => '#7c3aed',
                'tint' => '#f5f3ff',
                'default_host' => '127.0.0.1',
                'default_port' => '1433',
                'default_charset' => 'UTF-8',
                'pdo_drivers' => ['sqlsrv', 'dblib'],
                'description' => 'Microsoft SQL Server through PDO_SQLSRV or the compatible PDO_DBLIB adapter.',
            ],
            'oci' => [
                'label' => 'Oracle',
                'family' => 'Oracle Database',
                'icon' => 'server',
                'color' => '#dc2626',
                'tint' => '#fef2f2',
                'default_host' => '127.0.0.1',
                'default_port' => '1521',
                'default_charset' => 'AL32UTF8',
                'pdo_drivers' => ['oci'],
                'description' => 'Enterprise Oracle connectivity when the PDO_OCI extension is installed.',
            ],
        ];

        $availableDrivers = \PDO::getAvailableDrivers();
        foreach ($definitions as $type => &$definition) {
            $driver = self::firstAvailableDriver($definition['pdo_drivers'], $availableDrivers);
            $definition['type'] = $type;
            $definition['available'] = $driver !== null;
            $definition['pdo_driver'] = $driver;
            $definition['driver_label'] = $driver !== null ? 'PDO_' . strtoupper($driver) : '';
            $definition['requirement'] = $driver !== null
                ? 'PDO ready'
                : 'Install ' . implode(' or ', array_map(static fn(string $item): string => 'pdo_' . $item, $definition['pdo_drivers']));
        }
        unset($definition);

        return $definitions;
    }

    public static function normalize(array $input): array
    {
        $rawType = strtolower(trim((string)($input['type'] ?? $input['db_type'] ?? 'mysql')));
        $type = self::TYPE_ALIASES[$rawType] ?? $rawType;
        $definitions = self::definitions();
        if (!isset($definitions[$type])) {
            throw new \InvalidArgumentException('Unsupported database type.');
        }

        $definition = $definitions[$type];
        $host = trim((string)($input['host'] ?? $input['db_host'] ?? $definition['default_host']));
        if ($host === 'localhost') {
            $host = '127.0.0.1';
        }

        $path = trim((string)($input['path'] ?? $input['db_path'] ?? 'database/app.sqlite'));
        $dbname = trim((string)($input['dbname'] ?? $input['db_name'] ?? ''));
        if ($type === 'sqlite' && $dbname === '') {
            $dbname = $path;
        }

        $ssl = is_array($input['ssl'] ?? null) ? $input['ssl'] : [];

        return [
            'type' => $type,
            'driver' => $definition['pdo_driver'] ?? null,
            'host' => $host,
            'port' => trim((string)($input['port'] ?? $input['db_port'] ?? $definition['default_port'])),
            'dbname' => $dbname,
            'path' => $path,
            'user' => (string)($input['user'] ?? $input['db_user'] ?? ''),
            'pass' => (string)($input['pass'] ?? $input['db_pass'] ?? ''),
            'schema' => trim((string)($input['schema'] ?? $input['db_schema'] ?? match ($type) {
                'pgsql', 'cockroachdb' => 'public',
                'sqlsrv' => 'dbo',
                default => '',
            })),
            'service' => trim((string)($input['service'] ?? $input['db_service'] ?? $dbname)),
            'charset' => trim((string)($input['charset'] ?? $input['db_charset'] ?? $definition['default_charset'])),
            'ssl' => [
                'enabled' => self::toBool($input['ssl_enabled'] ?? $input['db_ssl_enabled'] ?? $ssl['enabled'] ?? false),
                'mode' => trim((string)($input['ssl_mode'] ?? $input['db_ssl_mode'] ?? $ssl['mode'] ?? 'prefer')),
                'ca' => trim((string)($input['ssl_ca'] ?? $input['db_ssl_ca'] ?? $ssl['ca'] ?? '')),
                'encrypt' => self::toBool($input['encrypt'] ?? $input['db_encrypt'] ?? $ssl['encrypt'] ?? true),
                'trust_server_certificate' => self::toBool($input['trust_server_certificate'] ?? $input['db_trust_server_certificate'] ?? $ssl['trust_server_certificate'] ?? false),
            ],
        ];
    }

    public static function validate(array $config, ?string $basePath = null): void
    {
        $config = self::normalize($config);
        $definitions = self::definitions();
        $definition = $definitions[$config['type']];

        if (!$definition['available']) {
            throw new \RuntimeException($definition['label'] . ' is not available on this server. ' . $definition['requirement'] . '.');
        }

        if ($config['type'] === 'sqlite') {
            $path = self::resolveSqlitePath($config['path'], $basePath);
            $parent = dirname($path);
            if (!is_dir($parent) && !@mkdir($parent, 0755, true) && !is_dir($parent)) {
                throw new \RuntimeException('SQLite directory could not be created: ' . $parent);
            }
            if (!is_writable($parent)) {
                throw new \RuntimeException('SQLite directory is not writable: ' . $parent);
            }
            return;
        }

        if ($config['host'] === '') {
            throw new \InvalidArgumentException('Host or server name is required.');
        }
        if ($config['dbname'] === '' && $config['type'] !== 'oci') {
            throw new \InvalidArgumentException('Database name is required.');
        }
        if ($config['type'] === 'oci' && $config['service'] === '') {
            throw new \InvalidArgumentException('Oracle service name is required.');
        }
        if ($config['port'] !== '' && (!ctype_digit($config['port']) || (int)$config['port'] < 1 || (int)$config['port'] > 65535)) {
            throw new \InvalidArgumentException('Port must be a number between 1 and 65535.');
        }
    }

    public static function connect(array $config, ?string $basePath = null): \PDO
    {
        $config = self::normalize($config);
        self::validate($config, $basePath);
        $driver = self::preferredDriver($config['type']);
        $dsn = self::buildDsn($config, $basePath, $driver);

        $pdo = new \PDO(
            $dsn,
            $config['type'] === 'sqlite' ? null : $config['user'],
            $config['type'] === 'sqlite' ? null : $config['pass'],
            self::pdoOptions($config, $driver)
        );
        if ($config['type'] === 'sqlite') {
            self::applySqlitePragmas($pdo);
        }
        return $pdo;
    }

    /**
     * Robustness PRAGMAs applied to every SQLite (Virtual DB) connection:
     *  - WAL journalling → concurrent readers while one writer works;
     *  - foreign_keys ON → SQLite enforces FKs only when asked, per-connection;
     *  - busy_timeout → wait instead of instantly failing on a locked write;
     *  - synchronous NORMAL → safe with WAL, far faster than FULL.
     * Best-effort: a PRAGMA failure never breaks the request.
     */
    public static function applySqlitePragmas(\PDO $pdo): void
    {
        foreach ([
            // ── Concurrency & durability ────────────────────────────────────
            'PRAGMA busy_timeout=5000',        // wait (ms) on a locked DB before erroring
            'PRAGMA journal_mode=WAL',          // readers never block the writer
            'PRAGMA foreign_keys=ON',           // referential integrity enforced
            'PRAGMA synchronous=NORMAL',        // safe under WAL, far faster than FULL
            'PRAGMA wal_autocheckpoint=1000',   // fold the WAL back every ~1000 pages
            // ── Lightning-speed reads ───────────────────────────────────────
            'PRAGMA cache_size=-16000',         // 16 MB page cache per connection (neg = KiB)
            'PRAGMA temp_store=MEMORY',          // temp b-trees/sorts live in RAM
            'PRAGMA mmap_size=268435456',        // up to 256 MB memory-mapped I/O (fewer syscalls)
            // ── Robustness / hardening ──────────────────────────────────────
            'PRAGMA cell_size_check=ON',         // detect corrupt b-tree cells early
            'PRAGMA trusted_schema=OFF',         // don't run schema-defined funcs from untrusted DBs
        ] as $pragma) {
            try { $pdo->exec($pragma); } catch (\Throwable $e) { /* keep going — best effort per host */ }
        }
    }

    public static function test(array $config, ?string $basePath = null): array
    {
        try {
            $normalized = self::normalize($config);
            $pdo = self::connect($normalized, $basePath);
            $pdo->query($normalized['type'] === 'oci' ? 'SELECT 1 FROM DUAL' : 'SELECT 1');
            $driver = (string)$pdo->getAttribute(\PDO::ATTR_DRIVER_NAME);
            return [
                'success' => true,
                'message' => self::label($normalized['type']) . ' connection successful using PDO_' . strtoupper($driver) . '.',
                'driver' => $driver,
            ];
        } catch (\Throwable $e) {
            return ['success' => false, 'message' => $e->getMessage(), 'driver' => null];
        }
    }

    public static function buildDsn(array $config, ?string $basePath = null, ?string $driver = null): string
    {
        $config = self::normalize($config);
        $driver ??= self::preferredDriver($config['type']);
        $host = $config['host'];
        $port = $config['port'];

        return match ($config['type']) {
            'mysql', 'mariadb', 'tidb' => 'mysql:host=' . $host . ';port=' . $port . ';dbname=' . $config['dbname'] . ';charset=' . ($config['charset'] ?: 'utf8mb4'),
            'pgsql', 'cockroachdb' => 'pgsql:host=' . $host . ';port=' . $port . ';dbname=' . $config['dbname']
                . ';sslmode=' . self::postgresSslMode($config),
            'sqlite' => 'sqlite:' . self::resolveSqlitePath($config['path'], $basePath),
            'sqlsrv' => $driver === 'sqlsrv'
                ? 'sqlsrv:Server=' . $host . ($port !== '' ? ',' . $port : '') . ';Database=' . $config['dbname']
                    . ';Encrypt=' . (!empty($config['ssl']['encrypt']) ? 'yes' : 'no')
                    . ';TrustServerCertificate=' . (!empty($config['ssl']['trust_server_certificate']) ? 'yes' : 'no')
                : 'dblib:host=' . $host . ($port !== '' ? ':' . $port : '') . ';dbname=' . $config['dbname'] . ';charset=UTF-8',
            'oci' => 'oci:dbname=//' . $host . ':' . $port . '/' . $config['service'] . ';charset=' . ($config['charset'] ?: 'AL32UTF8'),
            default => throw new \InvalidArgumentException('Unsupported database type.'),
        };
    }

    public static function pdoOptions(array $config, ?string $driver = null): array
    {
        $config = self::normalize($config);
        $driver ??= self::preferredDriver($config['type']);
        $options = [
            \PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION,
            \PDO::ATTR_DEFAULT_FETCH_MODE => \PDO::FETCH_ASSOC,
            \PDO::ATTR_EMULATE_PREPARES => false,
        ];

        if (in_array($config['type'], ['mysql', 'mariadb', 'tidb'], true) && !empty($config['ssl']['enabled'])) {
            $ca = (string)($config['ssl']['ca'] ?? '');
            if ($ca !== '' && defined('PDO::MYSQL_ATTR_SSL_CA')) {
                $options[\PDO::MYSQL_ATTR_SSL_CA] = $ca;
            }
        }

        return $options;
    }

    public static function dropDatabase(array $config, ?string $basePath = null): void
    {
        $config = self::normalize($config);
        self::validate($config, $basePath);

        if ($config['type'] === 'sqlite') {
            $path = self::resolveSqlitePath($config['path'], $basePath);
            if (is_file($path) && !unlink($path)) {
                throw new \RuntimeException('Could not delete the SQLite database file.');
            }
            return;
        }

        if ($config['type'] === 'oci') {
            throw new \RuntimeException('Oracle databases must be removed with Oracle administration tools.');
        }

        if (in_array($config['type'], ['mysql', 'mariadb', 'tidb'], true)) {
            $serverConfig = $config;
            $serverConfig['dbname'] = 'information_schema';
            $pdo = self::connect($serverConfig, $basePath);
            $pdo->exec('DROP DATABASE ' . self::quoteDatabaseName($config['dbname'], 'mysql'));
            return;
        }

        if (in_array($config['type'], ['pgsql', 'cockroachdb'], true)) {
            $serverConfig = $config;
            $serverConfig['dbname'] = 'postgres';
            $pdo = self::connect($serverConfig, $basePath);
            // pg_terminate_backend exists on PostgreSQL but not CockroachDB
            // (which cancels sessions differently) — only PostgreSQL needs it.
            if ($config['type'] === 'pgsql') {
                $pdo->prepare('SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = ? AND pid <> pg_backend_pid()')
                    ->execute([$config['dbname']]);
            }
            $pdo->exec('DROP DATABASE ' . self::quoteDatabaseName($config['dbname'], 'pgsql'));
            return;
        }

        $serverConfig = $config;
        $serverConfig['dbname'] = 'master';
        $pdo = self::connect($serverConfig, $basePath);
        $quotedName = self::quoteDatabaseName($config['dbname'], 'sqlsrv');
        $literal = $pdo->quote($config['dbname']);
        $pdo->exec("IF DB_ID({$literal}) IS NOT NULL BEGIN ALTER DATABASE {$quotedName} SET SINGLE_USER WITH ROLLBACK IMMEDIATE; DROP DATABASE {$quotedName}; END");
    }

    public static function label(string $type): string
    {
        $type = self::TYPE_ALIASES[strtolower($type)] ?? strtolower($type);
        return self::definitions()[$type]['label'] ?? ucfirst($type);
    }

    public static function preferredDriver(string $type): string
    {
        $type = self::TYPE_ALIASES[strtolower($type)] ?? strtolower($type);
        $definition = self::definitions()[$type] ?? null;
        if ($definition === null) {
            throw new \InvalidArgumentException('Unsupported database type.');
        }
        if (empty($definition['pdo_driver'])) {
            throw new \RuntimeException($definition['label'] . ' is not available. ' . $definition['requirement'] . '.');
        }
        return (string)$definition['pdo_driver'];
    }

    public static function resolveSqlitePath(string $path, ?string $basePath = null): string
    {
        $path = trim($path);
        if ($path === '') {
            $path = 'database/app.sqlite';
        }
        if ($path === ':memory:') {
            return $path;
        }
        // Portable central-pool reference: `vdb://<name>` resolves to THIS host's
        // pool path at connect time, so a replicated/migrated install (different
        // BASE_PATH) still opens the file. Legacy projects that stored the absolute
        // pool path keep working via the absolute-path branch below (unchanged).
        if (str_starts_with($path, 'vdb://')) {
            return \PpPhp\Core\Databases\VirtualDb::poolPath(substr($path, 6));
        }
        if (self::isAbsolutePath($path)) {
            return $path;
        }
        $parts = preg_split('~[\\\\/]+~', $path) ?: [];
        if (in_array('..', $parts, true)) {
            throw new \InvalidArgumentException('Relative SQLite paths cannot leave the project directory.');
        }
        $basePath ??= defined('BASE_PATH') ? constant('BASE_PATH') : getcwd();
        return rtrim((string)$basePath, DIRECTORY_SEPARATOR) . DIRECTORY_SEPARATOR . implode(DIRECTORY_SEPARATOR, array_filter($parts, static fn(string $part): bool => $part !== '' && $part !== '.'));
    }

    private static function postgresSslMode(array $config): string
    {
        $allowed = ['disable', 'allow', 'prefer', 'require', 'verify-ca', 'verify-full'];
        $mode = strtolower((string)($config['ssl']['mode'] ?? 'prefer'));
        if (!in_array($mode, $allowed, true)) {
            $mode = !empty($config['ssl']['enabled']) ? 'require' : 'prefer';
        }
        if (!empty($config['ssl']['enabled']) && in_array($mode, ['disable', 'allow', 'prefer'], true)) {
            $mode = 'require';
        }
        return $mode;
    }

    private static function quoteDatabaseName(string $name, string $driver): string
    {
        return match ($driver) {
            'mysql' => '`' . str_replace('`', '``', $name) . '`',
            'sqlsrv' => '[' . str_replace(']', ']]', $name) . ']',
            default => '"' . str_replace('"', '""', $name) . '"',
        };
    }

    private static function firstAvailableDriver(array $candidates, array $available): ?string
    {
        foreach ($candidates as $candidate) {
            if (in_array($candidate, $available, true)) {
                return $candidate;
            }
        }
        return null;
    }

    private static function isAbsolutePath(string $path): bool
    {
        return str_starts_with($path, '/') || str_starts_with($path, '\\') || (bool)preg_match('/^[A-Za-z]:[\\\\\/]/', $path);
    }

    private static function toBool(mixed $value): bool
    {
        if (is_bool($value)) {
            return $value;
        }
        return in_array(strtolower(trim((string)$value)), ['1', 'true', 'yes', 'on'], true);
    }
}
