<?php
namespace App\Core;

use PDO;
use PDOException;
use RuntimeException;
use Throwable;

class Database
{
    private static ?PDO $pdo = null;
    private static bool $failed = false;
    private static string $lastError = '';
    private static array $tableCache = [];
    private static array $columnCache = [];

    public static function configured(): bool
    {
        return DB_NAME !== '' && DB_USER !== '';
    }

    public static function pdo(): ?PDO
    {
        if (self::$pdo) {
            return self::$pdo;
        }
        if (self::$failed) {
            return null;
        }
        if (!self::configured()) {
            self::$failed = true;
            self::$lastError = 'Database credentials are not configured in app/config.local.php.';
            return null;
        }

        $dsn = sprintf(
            'mysql:host=%s;port=%d;dbname=%s;charset=%s',
            DB_HOST,
            DB_PORT,
            DB_NAME,
            DB_CHARSET
        );

        try {
            self::$pdo = new PDO($dsn, DB_USER, DB_PASS, [
                PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
                PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
                PDO::ATTR_EMULATE_PREPARES => false,
                PDO::ATTR_STRINGIFY_FETCHES => false,
                PDO::ATTR_TIMEOUT => 8,
                PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci, time_zone = '+06:00'",
            ]);
            return self::$pdo;
        } catch (PDOException $exception) {
            self::$failed = true;
            self::$lastError = $exception->getMessage();
            error_log('[' . (defined('REQUEST_ID') ? REQUEST_ID : '-') . '] Database connection error: ' . $exception->getMessage());
            return null;
        }
    }

    public static function isConnected(): bool
    {
        return self::pdo() instanceof PDO;
    }

    public static function lastError(): string
    {
        self::pdo();
        return self::$lastError;
    }

    public static function query(string $sql, array $params = [])
    {
        $pdo = self::pdo();
        if (!$pdo) {
            return false;
        }
        try {
            $statement = $pdo->prepare($sql);
            $statement->execute($params);
            return $statement;
        } catch (PDOException $exception) {
            self::$lastError = $exception->getMessage();
            $normalizedSql = preg_replace('/\s+/', ' ', trim($sql));
            error_log('[' . (defined('REQUEST_ID') ? REQUEST_ID : '-') . '] Database query error: ' . $exception->getMessage() . ' | SQL: ' . substr((string) $normalizedSql, 0, 1200));
            throw $exception;
        }
    }

    public static function safeQuery(string $sql, array $params = [])
    {
        try {
            return self::query($sql, $params);
        } catch (Throwable) {
            return false;
        }
    }

    public static function fetchAll(string $sql, array $params = []): array
    {
        $statement = self::query($sql, $params);
        return $statement ? $statement->fetchAll() : [];
    }

    public static function safeFetchAll(string $sql, array $params = []): array
    {
        $statement = self::safeQuery($sql, $params);
        return $statement ? $statement->fetchAll() : [];
    }

    public static function fetchOne(string $sql, array $params = []): ?array
    {
        $statement = self::query($sql, $params);
        $row = $statement ? $statement->fetch() : false;
        return $row ?: null;
    }

    public static function safeFetchOne(string $sql, array $params = []): ?array
    {
        $statement = self::safeQuery($sql, $params);
        $row = $statement ? $statement->fetch() : false;
        return $row ?: null;
    }

    public static function execute(string $sql, array $params = []): bool
    {
        return (bool) self::query($sql, $params);
    }

    public static function safeExecute(string $sql, array $params = []): bool
    {
        return (bool) self::safeQuery($sql, $params);
    }

    public static function lastInsertId(): int
    {
        $pdo = self::pdo();
        return $pdo ? (int) $pdo->lastInsertId() : 0;
    }

    public static function tableExists(string $table): bool
    {
        if (!preg_match('/^[a-zA-Z0-9_]+$/', $table) || !self::isConnected()) {
            return false;
        }
        if (array_key_exists($table, self::$tableCache)) {
            return self::$tableCache[$table];
        }
        $row = self::safeFetchOne(
            'SELECT 1 AS found FROM information_schema.tables WHERE table_schema = DATABASE() AND table_name = ? LIMIT 1',
            [$table]
        );
        return self::$tableCache[$table] = (bool) $row;
    }

    public static function columns(string $table): array
    {
        if (!self::tableExists($table)) {
            return [];
        }
        if (isset(self::$columnCache[$table])) {
            return self::$columnCache[$table];
        }
        $rows = self::safeFetchAll("SHOW COLUMNS FROM `{$table}`");
        return self::$columnCache[$table] = array_values(array_filter(array_map(
            static fn(array $row): string => (string) ($row['Field'] ?? ''),
            $rows
        )));
    }

    public static function transaction(callable $callback)
    {
        $pdo = self::pdo();
        if (!$pdo) {
            throw new RuntimeException('Database connection is unavailable. Check app/config.local.php.');
        }
        try {
            $pdo->beginTransaction();
            $result = $callback($pdo);
            $pdo->commit();
            return $result;
        } catch (Throwable $exception) {
            if ($pdo->inTransaction()) {
                $pdo->rollBack();
            }
            throw $exception;
        }
    }
}
