<?php

declare(strict_types=1);

namespace App\Core;

use PDO;
use PDOException;

final class Database
{
    private static ?PDO $pdo = null;

    public static function pdo(): PDO
    {
        if (self::$pdo instanceof PDO) {
            return self::$pdo;
        }

        $url = Env::get('DATABASE_URL', '');
        $host = Env::get('DB_HOST', '127.0.0.1');
        $port = Env::get('DB_PORT', '5432');
        $db = Env::get('DB_DATABASE', Env::get('DB_NAME', 'postgres'));
        $user = Env::get('DB_USERNAME', Env::get('DB_USER', 'postgres'));
        $pass = Env::get('DB_PASSWORD', Env::get('DB_PASS', ''));
        $schema = Env::get('DB_SCHEMA', 'gestao_sisprom');

        if ($url) {
            $parts = parse_url($url);
            if (is_array($parts)) {
                $host = $parts['host'] ?? $host;
                $port = (string)($parts['port'] ?? $port);
                $db = isset($parts['path']) ? ltrim($parts['path'], '/') : $db;
                $user = isset($parts['user']) ? rawurldecode((string)$parts['user']) : $user;
                $pass = isset($parts['pass']) ? rawurldecode((string)$parts['pass']) : $pass;
            }
        }

        $dsn = "pgsql:host={$host};port={$port};dbname={$db}";

        try {
            self::$pdo = new PDO($dsn, $user, $pass, [
                PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
                PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
                PDO::ATTR_EMULATE_PREPARES => false,
                PDO::ATTR_PERSISTENT => Env::bool('DB_PERSISTENT', true),
            ]);
            self::$pdo->exec('SET search_path TO "' . str_replace('"', '""', $schema) . '", public');
            return self::$pdo;
        } catch (PDOException $e) {
            throw $e;
        }
    }

    public static function fetchOne(string $sql, array $params = []): ?array
    {
        $stmt = self::pdo()->prepare($sql);
        $stmt->execute(self::normalizeParams($params));
        $row = $stmt->fetch();
        return $row ?: null;
    }

    public static function fetchAll(string $sql, array $params = []): array
    {
        $stmt = self::pdo()->prepare($sql);
        $stmt->execute(self::normalizeParams($params));
        return $stmt->fetchAll();
    }

    public static function execute(string $sql, array $params = []): int
    {
        $stmt = self::pdo()->prepare($sql);
        $stmt->execute(self::normalizeParams($params));
        return $stmt->rowCount();
    }

    public static function tableColumns(string $table): array
    {
        $table = preg_replace('/[^a-zA-Z0-9_]/', '', $table) ?? '';
        if ($table === '') {
            return [];
        }

        static $runtimeCache = null;
        if ($runtimeCache !== null) {
            return $runtimeCache[$table] ?? [];
        }

        $schema = Env::get('DB_SCHEMA', 'gestao_sisprom');
        $root = dirname(__DIR__, 2);
        $dir = $root . DIRECTORY_SEPARATOR . 'storage' . DIRECTORY_SEPARATOR . 'cache';
        $file = $dir . DIRECTORY_SEPARATOR . 'schema_columns_' . preg_replace('/[^a-zA-Z0-9_]/', '_', $schema) . '.json';
        $ttl = Env::int('SCHEMA_CACHE_TTL', 600);

        if (is_file($file) && (time() - filemtime($file)) < $ttl) {
            $json = json_decode((string)@file_get_contents($file), true);
            if (is_array($json)) {
                $runtimeCache = $json;
                return $runtimeCache[$table] ?? [];
            }
        }

        $rows = self::fetchAll(
            'SELECT table_name, column_name
               FROM information_schema.columns
              WHERE table_schema = current_schema()
              ORDER BY table_name, ordinal_position'
        );

        if (!$rows) {
            $rows = self::fetchAll(
                'SELECT table_name, column_name
                   FROM information_schema.columns
                  WHERE table_schema = :schema
                  ORDER BY table_name, ordinal_position',
                ['schema' => $schema]
            );
        }

        $cache = [];
        foreach ($rows as $row) {
            $t = (string)($row['table_name'] ?? '');
            $c = (string)($row['column_name'] ?? '');
            if ($t !== '' && $c !== '') {
                $cache[$t][] = $c;
            }
        }

        $runtimeCache = $cache;
        if (!is_dir($dir)) {
            @mkdir($dir, 0777, true);
        }
        @file_put_contents($file, json_encode($cache, JSON_UNESCAPED_UNICODE | JSON_PRETTY_PRINT));

        return $runtimeCache[$table] ?? [];
    }

    public static function tableExists(string $table): bool
    {
        return self::tableColumns($table) !== [];
    }

    private static function normalizeParams(array $params): array
    {
        foreach ($params as $key => $value) {
            if (is_bool($value)) {
                $params[$key] = $value ? 'true' : 'false';
            }
        }
        return $params;
    }

}
