<?php

declare(strict_types=1);

namespace PpPhp\Core;

/**
 * DB API – enterprise‑grade, multi‑connection database abstraction.
 *
 * Philosophy:
 *  - Zero external dependencies – built entirely on PDO.
 *  - Static facade with uppercase method names (matching PHPRunner).
 *  - Lazy connections with a named registry; switch contexts via SetConnection().
 *  - All data goes through prepared statements – SQL injection safe by default.
 *
 * Quick start:
 *   DB::AddConnection('default', 'mysql:host=...;dbname=...', 'root', 'pass');
 *   DB::Init('my-project-slug');                     // auto‑register project DB
 *   DB::SetConnection('my-project-slug');            // switch context
 *   $rows = DB::Select('users', ['active' => 1], 'name');
 *   $id   = DB::Insert('logs', ['event' => 'login']);
 *   $zip  = DB::DBLookup("SELECT zip FROM users WHERE id=:1", $userId);
 *   DB::Exec("UPDATE orders SET status='shipped' WHERE ship_date = CURDATE()");
 */

class DB
{
    // ── Connection registry ──────────────────────────────────────────────

    /** @var array<string, array{dsn: string, user: ?string, pass: ?string, options: array, driver: string}> */
    private static array $connections = [];

    /** @var array<string, \PDO> */
    private static array $pdoInstances = [];

    /** Name of the currently active connection. */
    private static string $activeConnection = 'default';

    /** Last PDO error message. */
    private static string $lastError = '';

    /** Per-request cache of column base types: "conn::table" => [col => baseType]. */
    private static array $columnTypeCache = [];

    /** Column base types (lowercased, sizes stripped) for a table, cached. */
    private static function columnTypes(string $table): array
    {
        $key = self::$activeConnection . '::' . $table;
        if (array_key_exists($key, self::$columnTypeCache)) {
            return self::$columnTypeCache[$key];
        }
        $types = [];
        try {
            $safe = preg_replace('/[^A-Za-z0-9_]/', '', $table);
            foreach (self::FetchAll("SHOW COLUMNS FROM `{$safe}`") ?: [] as $c) {
                $t = strtolower((string) ($c['Type'] ?? ''));
                $types[(string) ($c['Field'] ?? '')] = preg_replace('/\(.*/', '', $t) ?? $t;
            }
        } catch (\Throwable) {
            // Non-MySQL driver or introspection unavailable → no coercion.
        }
        return self::$columnTypeCache[$key] = $types;
    }

    /**
     * Convert '' → NULL for columns MySQL's strict mode rejects an empty string
     * for (dates, numbers, JSON…). A blank form field posts '' but for these
     * types it means "no value" and must be stored as NULL. String columns are
     * left untouched. Safe no-op when the schema can't be read.
     */
    private static function coerceEmptyToNull(string $table, array $data): array
    {
        static $nonString = [
            'date', 'datetime', 'timestamp', 'time', 'year',
            'int', 'integer', 'bigint', 'smallint', 'tinyint', 'mediumint',
            'decimal', 'numeric', 'float', 'double', 'real', 'bit', 'json',
        ];
        $types = self::columnTypes($table);
        if ($types === []) {
            return $data;
        }
        foreach ($data as $col => $val) {
            if (is_string($val) && trim($val) === ''
                && isset($types[$col]) && in_array($types[$col], $nonString, true)) {
                $data[$col] = null;
            }
        }
        return $data;
    }

    // ── Connection management ────────────────────────────────────────────

    /**
     * Register a named database connection (lazy – PDO is created on first use).
     *
     * @param string $name   Unique alias for this connection (e.g. "default", "logs").
     * @param string $dsn    Full PDO DSN, e.g. "mysql:host=127.0.0.1;port=3306;dbname=mydb;charset=utf8mb4".
     * @param string|null $user
     * @param string|null $pass
     * @param array  $options  Extra PDO options (merged with safe defaults).
     */
    public static function AddConnection(
        string $name,
        string $dsn,
        ?string $user = null,
        ?string $pass = null,
        array $options = []
    ): void {
        if (isset(self::$connections[$name])) {
            // Idempotent: keep the existing registration. Callers like
            // Security::GetUserData() call DB::Init() defensively, which
            // must not fail when the connection is already set up.
            return;
        }
        self::$connections[$name] = [
            'dsn'     => $dsn,
            'user'    => $user,
            'pass'    => $pass,
            'options' => $options,
            'driver'  => strtolower((string)strtok($dsn, ':')),
        ];
    }

    /**
     * Initialise a database connection.
     *
     * If called with no arguments → central database (config/database.php).
     * If called with a slug → project database (projects/<user>/<slug>/config/database.php).
     *
     * @param string|null $slug  Project slug, or null for the central database.
     * @param string|null $name  Connection alias (defaults to $slug, or 'default' for central).
     */
    public static function Init(?string $slug = null, ?string $name = null): void
    {
        if ($slug === null) {
            // Central database
            $name ??= 'default';
            $config = require constant('BASE_PATH') . '/config/database.php';
            $host = ($config['host'] === 'localhost') ? '127.0.0.1' : $config['host'];
            $dsn = "mysql:host={$host};port={$config['port']};dbname={$config['dbname']};charset=utf8mb4";
            self::AddConnection($name, $dsn, $config['user'], $config['pass']);
            return;
        }

        // Project database. Resolve the project's real owner so an administrator
        // (or the platform owner) opening someone else's project finds the right
        // folder — access is authorised by the controller before we get here.
        $name ??= $slug;
        $configPath = ProjectLocator::dir($slug) . '/config/database.php';

        if (!file_exists($configPath)) {
            throw new \RuntimeException("Database config for project '{$slug}' not found at {$configPath}.");
        }
        $cfg = DatabaseConnection::normalize((array)require $configPath);
        $projectDir = dirname($configPath, 2);
        $driver = DatabaseConnection::preferredDriver($cfg['type']);
        $dsn = DatabaseConnection::buildDsn($cfg, $projectDir, $driver);

        self::AddConnection(
            $name,
            $dsn,
            $cfg['type'] === 'sqlite' ? null : $cfg['user'],
            $cfg['type'] === 'sqlite' ? null : $cfg['pass'],
            DatabaseConnection::pdoOptions($cfg, $driver)
        );
    }

    /**
     * Switch the active connection context.
     *
     * @param string $name  Connection alias. Pass empty string to switch back to "default".
     */
    public static function SetConnection(string $name = ''): void
    {
        if ($name === '') {
            $name = 'default';
        }
        if (!isset(self::$connections[$name])) {
            throw new \RuntimeException("Connection '{$name}' is not registered. Use DB::AddConnection() first.");
        }
        self::$activeConnection = $name;
    }

    /**
     * Return (and lazily create) the PDO instance for the given connection name.
     */
    private static function connection(?string $name = null): \PDO
    {
        $name ??= self::$activeConnection;

        if (!isset(self::$pdoInstances[$name])) {
            if (!isset(self::$connections[$name])) {
                throw new \RuntimeException("Connection '{$name}' is not registered.");
            }
            $c = self::$connections[$name];
            $options = $c['options'] + [
                \PDO::ATTR_ERRMODE            => \PDO::ERRMODE_EXCEPTION,
                \PDO::ATTR_DEFAULT_FETCH_MODE => \PDO::FETCH_ASSOC,
                \PDO::ATTR_EMULATE_PREPARES   => false,
            ];
            self::$pdoInstances[$name] = new \PDO($c['dsn'], $c['user'], $c['pass'], $options);
            self::$lastError = '';

            // Virtual DB (SQLite) robustness: WAL, foreign keys, busy-timeout.
            if (($c['driver'] ?? '') === 'sqlite') {
                DatabaseConnection::applySqlitePragmas(self::$pdoInstances[$name]);
            }

            // Generated apps carry a project timezone (Misc → Advanced). Align the
            // MySQL session time_zone with it so NOW()/CURRENT_TIMESTAMP and PHP
            // date() agree everywhere. Guarded by PP_TIMEZONE (defined only in a
            // built app), mysql-only, and best-effort — never breaks the request.
            if (defined('PP_TIMEZONE') && PP_TIMEZONE !== '' && ($c['driver'] ?? 'mysql') === 'mysql') {
                try {
                    $off = (new \DateTime('now', new \DateTimeZone(PP_TIMEZONE)))->format('P'); // e.g. +05:45
                    self::$pdoInstances[$name]->exec("SET time_zone = '{$off}'");
                } catch (\Throwable $e) {
                    // Keep the server default time_zone on any failure.
                }
            }
        }
        return self::$pdoInstances[$name];
    }

    /**
     * Return the raw PDO instance for advanced use.
     */
    public static function Pdo(): \PDO
    {
        return self::connection();
    }

    /**
     * Return the last PDO error message.
     */
    public static function LastError(): string
    {
        return self::$lastError;
    }

    /**
     * Driver-aware column introspection, normalised to the MySQL SHOW COLUMNS
     * row shape (Field / Type / Null / Key / Default / Extra) so every call
     * site written against SHOW COLUMNS works unchanged on the Virtual DB
     * (SQLite). Unknown drivers return [] — the exact silent behaviour the raw
     * SHOW COLUMNS gave them before this helper existed.
     *
     * @return array<int,array{Field:string,Type:string,Null:string,Key:string,Default:mixed,Extra:string}>
     */
    public static function Columns(string $table): array
    {
        $safe = preg_replace('/[^a-zA-Z0-9_]/', '', $table);
        if ($safe === '' || $safe === null) return [];
        $driver = self::CurrentDriver();

        if ($driver === 'mysql') {
            return self::FetchAll("SHOW COLUMNS FROM `{$safe}`") ?: [];
        }

        if ($driver === 'sqlite') {
            $rows = self::FetchAll('PRAGMA table_info("' . $safe . '")') ?: [];
            $out = [];
            foreach ($rows as $r) {
                $type = (string) ($r['type'] ?? '');
                $isPk = (int) ($r['pk'] ?? 0) > 0;
                // SQLite's rowid alias — INTEGER PRIMARY KEY — auto-increments.
                $auto = $isPk && stripos($type, 'INT') !== false;
                $out[] = [
                    'Field'   => (string) ($r['name'] ?? ''),
                    'Type'    => $type !== '' ? strtolower($type) : 'text',
                    'Null'    => ((int) ($r['notnull'] ?? 0) === 1 || $isPk) ? 'NO' : 'YES',
                    'Key'     => $isPk ? 'PRI' : '',
                    'Default' => $r['dflt_value'] ?? null,
                    'Extra'   => $auto ? 'auto_increment' : '',
                ];
            }
            return $out;
        }

        return [];
    }

    /** Return the PDO driver of the active connection. */
    public static function CurrentDriver(): string
    {
        return self::$connections[self::$activeConnection]['driver'] ?? 'mysql';
    }

    // ── Utility connection methods (external DBs) ──────────────────────────

    /**
     * Test a database connection without registering it.
     *
     * @param string $host   e.g. "127.0.0.1"
     * @param string $port   e.g. "3306"
     * @param string $dbName Database name.
     * @param string $user
     * @param string $pass
     * @return bool True if connection successful.
     */
    public static function TestConnection(
        string $host,
        string $port,
        string $dbName,
        string $user,
        string $pass
    ): bool {
        return self::TestConnectionConfig([
            'type' => 'mysql',
            'host' => $host,
            'port' => $port,
            'dbname' => $dbName,
            'user' => $user,
            'pass' => $pass,
        ]);
    }

    /** Test any supported project database profile. */
    public static function TestConnectionConfig(array $config, ?string $basePath = null): bool
    {
        $result = DatabaseConnection::test($config, $basePath);
        if (!$result['success']) {
            self::$lastError = (string)$result['message'];
            return false;
        }
        self::$lastError = '';
        return true;
    }

    /**
     * Drop a database on an external server.
     *
     * @param string $host
     * @param string $port
     * @param string $dbName
     * @param string $user
     * @param string $pass
     * @throws \PDOException on failure.
     */
    public static function DropDatabase(
        string $host,
        string $port,
        string $dbName,
        string $user,
        string $pass
    ): void {
        self::DropDatabaseConfig([
            'type' => 'mysql',
            'host' => $host,
            'port' => $port,
            'dbname' => $dbName,
            'user' => $user,
            'pass' => $pass,
        ]);
    }

    /** Drop the database represented by a supported project profile. */
    public static function DropDatabaseConfig(array $config, ?string $basePath = null): void
    {
        DatabaseConnection::dropDatabase($config, $basePath);
    }

    // ── Core query methods ───────────────────────────────────────────────

    /**
     * Execute a raw SQL statement (INSERT, UPDATE, DELETE, DDL).
     * Returns the number of affected rows.
     */
    public static function Exec(string $sql): int
    {
        try {
            $affected = self::connection()->exec($sql);
            self::$lastError = '';
            return $affected;
        } catch (\PDOException $e) {
            self::$lastError = $e->getMessage();
            return 0;
        }
    }

    /**
     * Execute a SELECT query (or any statement that returns a result set)
     * and return a QueryResult wrapper.
     *
     * @param string $sql    SQL with named (:name) or positional (?) placeholders.
     * @param array  $params Parameters to bind.
     * @return \PpPhp\Core\QueryResult|false
     */
    public static function Query(string $sql, array $params = []): QueryResult|false
    {
        try {
            $stmt = self::connection()->prepare($sql);
            $stmt->execute($params);
            self::$lastError = '';
            return new QueryResult($stmt);
        } catch (\PDOException $e) {
            self::$lastError = $e->getMessage();
            return false;
        }
    }

    /**
     * Prepare an SQL string for safe execution, replacing :1, :2, etc. with
     * properly quoted values (or real bound parameters for array arguments).
     *
     * @param string $sql   Template SQL, e.g. "SELECT * FROM t WHERE id=:1".
     * @param mixed  ...$args  Values to substitute (variadic).
     * @return string  Safe SQL string ready for DB::Exec() or DB::Query().
     */
    public static function PrepareSQL(string $sql, mixed ...$args): string
    {
        // Expand PHPRunner-style SQL variables first (:session.x, :user.field,
        // :userid, :get.x …) into safe literals — so they work anywhere PrepareSQL
        // is used (events, field code, Custom Buttons). Positional :1/:2 below.
        if (class_exists(SqlVars::class)) {
            $sql = SqlVars::expand($sql);
        }
        if (empty($args)) {
            return $sql;
        }
        // Replace :1, :2, … placeholders with quoted values.
        $i = 1;
        foreach ($args as $val) {
            $search = ":{$i}";
            if (str_contains($sql, $search)) {
                $sql = substr_replace($sql, self::quote($val), strpos($sql, $search), strlen($search));
            }
            $i++;
        }
        return $sql;
    }

    /**
     * Execute a scalar lookup query and return the first value of the first row.
     *
     * @param string $sql    e.g. "SELECT zip FROM users WHERE id=:1".
     * @param mixed  ...$args  Values for PrepareSQL placeholders.
     * @return mixed|null
     */
    public static function DBLookup(string $sql, mixed ...$args): mixed
    {
        $safeSql = self::PrepareSQL($sql, ...$args);
        $result = self::Query($safeSql);
        return ($result !== false) ? $result->value() : null;
    }

    // ── CRUD shortcuts ───────────────────────────────────────────────────

    /**
     * SELECT * FROM $table [WHERE $where] [ORDER BY $order].
     *
     * @param string       $table
     * @param string|array $where  "id=5" OR ['id' => 5, 'active' => 1]
     * @param string|array $sort   "name" OR ['name', 'created DESC']
     * @return array|false
     */
    public static function Select(string $table, string|array $where = [], string|array $sort = []): array|false
    {
        $sql = 'SELECT * FROM ' . self::QuoteIdentifier($table);
        $params = [];

        if (!empty($where)) {
            $clauses = [];
            if (is_array($where)) {
                foreach ($where as $col => $val) {
                    $clauses[] = self::QuoteIdentifier((string)$col) . ' = ?';
                    $params[] = $val;
                }
            } else {
                $clauses[] = $where;
            }
            $sql .= ' WHERE ' . implode(' AND ', $clauses);
        }

        if (!empty($sort)) {
            $orderBy = is_array($sort) ? implode(', ', $sort) : $sort;
            $sql .= ' ORDER BY ' . $orderBy;
        }

        $result = self::Query($sql, $params);
        return ($result !== false) ? $result->fetchAll() : false;
    }

    /**
     * INSERT INTO $table (...) VALUES (...).
     *
     * @return int|false  Last insert ID, or false on failure.
     */
    public static function Insert(string $table, array $data): int|false
    {
        $data = self::coerceEmptyToNull($table, $data);
        $cols = array_keys($data);
        $placeholders = array_fill(0, count($cols), '?');
        $quotedCols = array_map(static fn(string $col): string => self::QuoteIdentifier($col), $cols);
        $sql = 'INSERT INTO ' . self::QuoteIdentifier($table) . ' (' . implode(', ', $quotedCols) . ') VALUES (' . implode(', ', $placeholders) . ')';

        $result = self::Query($sql, array_values($data));
        if ($result === false) {
            return false;
        }
        return (int) self::connection()->lastInsertId();
    }

    /**
     * UPDATE $table SET ... WHERE ...
     *
     * @return int|false  Affected rows, or false on failure.
     */
    public static function Update(string $table, array $data, string|array $where): int|false
    {
        $data = self::coerceEmptyToNull($table, $data);
        $setClauses = [];
        $params = [];
        foreach ($data as $col => $val) {
            $setClauses[] = self::QuoteIdentifier((string)$col) . ' = ?';
            $params[] = $val;
        }
        $sql = 'UPDATE ' . self::QuoteIdentifier($table) . ' SET ' . implode(', ', $setClauses);

        if (is_array($where)) {
            $whereClauses = [];
            foreach ($where as $col => $val) {
                $whereClauses[] = self::QuoteIdentifier((string)$col) . ' = ?';
                $params[] = $val;
            }
            $sql .= ' WHERE ' . implode(' AND ', $whereClauses);
        } elseif (!empty($where)) {
            $sql .= ' WHERE ' . $where;
        }

        $result = self::Query($sql, $params);
        return ($result !== false) ? $result->rowCount() : false;
    }

    /**
     * DELETE FROM $table WHERE ...
     *
     * @return int|false  Affected rows, or false on failure.
     */
    public static function Delete(string $table, string|array $where): int|false
    {
        $params = [];
        if (is_array($where)) {
            $clauses = [];
            foreach ($where as $col => $val) {
                $clauses[] = self::QuoteIdentifier((string)$col) . ' = ?';
                $params[] = $val;
            }
            $whereClause = implode(' AND ', $clauses);
        } else {
            $whereClause = $where;
        }

        $sql = 'DELETE FROM ' . self::QuoteIdentifier($table) . ' WHERE ' . $whereClause;
        $result = self::Query($sql, $params);
        return ($result !== false) ? $result->rowCount() : false;
    }

    /**
     * Return the first column of the first row.
     *
     * @return mixed|null
     */
    public static function FetchOne(string $sql, array $params = []): mixed
    {
        $result = self::Query($sql, $params);
        return ($result !== false) ? $result->value() : null;
    }

    /**
     * Return all rows as an associative array.
     *
     * @return array|false
     */
    public static function FetchAll(string $sql, array $params = []): array|false
    {
        $result = self::Query($sql, $params);
        return ($result !== false) ? $result->fetchAll() : false;
    }

    /**
     * Return the last auto‑increment ID from the current connection.
     */
    public static function LastInsertId(): int
    {
        return (int) self::connection()->lastInsertId();
    }

    /** Quote an SQL identifier for the active PDO driver. */
    public static function QuoteIdentifier(string $identifier): string
    {
        $driver = self::CurrentDriver();
        if ($driver === 'mysql') {
            return '`' . str_replace('`', '``', $identifier) . '`';
        }
        if (in_array($driver, ['sqlsrv', 'dblib'], true)) {
            return '[' . str_replace(']', ']]', $identifier) . ']';
        }
        return '"' . str_replace('"', '""', $identifier) . '"';
    }

    // ── Internal helper ──────────────────────────────────────────────────

    /**
     * Quote a value for safe embedding into a raw SQL string (used by PrepareSQL).
     */
    private static function quote(mixed $value): string
    {
        if ($value === null) {
            return 'NULL';
        }
        if (is_int($value) || is_float($value)) {
            return (string) $value;
        }
        if (is_bool($value)) {
            return $value ? '1' : '0';
        }
        return self::connection()->quote((string) $value);
    }
}
