<?php

declare(strict_types=1);

namespace BotGuard\Core;

use PDO;
use PDOException;
use PDOStatement;
use RuntimeException;

/**
 * Thin PDO wrapper enforcing prepared statements everywhere.
 *
 * All queries flow through prepare()/execute() with bound parameters, so the
 * application never interpolates user input into SQL — the primary defence
 * against SQL injection. The PDO connection is created lazily on first use.
 */
final class Database
{
    private ?PDO $pdo = null;

    /** @param array<string, mixed> $config */
    public function __construct(private array $config)
    {
    }

    /**
     * Build a Database from the `database` config file.
     */
    public static function fromConfig(): self
    {
        return new self([
            'driver'   => config('database.driver', 'mysql'),
            'host'     => config('database.host', '127.0.0.1'),
            'port'     => (int) config('database.port', 3306),
            'database' => config('database.database', 'botguard'),
            'username' => config('database.username', 'root'),
            'password' => config('database.password', ''),
            'charset'  => config('database.charset', 'utf8mb4'),
            'options'  => config('database.options', []),
        ]);
    }

    /**
     * Lazily establish (and memoize) the PDO connection.
     */
    public function pdo(): PDO
    {
        if ($this->pdo instanceof PDO) {
            return $this->pdo;
        }

        $dsn = sprintf(
            '%s:host=%s;port=%d;dbname=%s;charset=%s',
            $this->config['driver'],
            $this->config['host'],
            $this->config['port'],
            $this->config['database'],
            $this->config['charset'],
        );

        $options = ($this->config['options'] ?? []) + [
            PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
            PDO::ATTR_EMULATE_PREPARES   => false, // real prepared statements
            PDO::ATTR_STRINGIFY_FETCHES  => false,
        ];

        try {
            $this->pdo = new PDO($dsn, $this->config['username'], $this->config['password'], $options);
        } catch (PDOException $e) {
            throw new RuntimeException('Database connection failed: ' . $e->getMessage(), (int) $e->getCode(), $e);
        }

        return $this->pdo;
    }

    /**
     * Prepare and execute a statement with bound parameters.
     *
     * @param array<string|int, mixed> $bindings
     */
    public function run(string $sql, array $bindings = []): PDOStatement
    {
        $statement = $this->pdo()->prepare($sql);
        $statement->execute($bindings);

        return $statement;
    }

    /**
     * Fetch a single row or null.
     *
     * @param array<string|int, mixed> $bindings
     * @return array<string, mixed>|null
     */
    public function selectOne(string $sql, array $bindings = []): ?array
    {
        $row = $this->run($sql, $bindings)->fetch();

        return $row === false ? null : $row;
    }

    /**
     * Fetch all rows.
     *
     * @param array<string|int, mixed> $bindings
     * @return array<int, array<string, mixed>>
     */
    public function select(string $sql, array $bindings = []): array
    {
        return $this->run($sql, $bindings)->fetchAll();
    }

    /**
     * Fetch a single scalar column value from the first row.
     *
     * @param array<string|int, mixed> $bindings
     */
    public function scalar(string $sql, array $bindings = []): mixed
    {
        $value = $this->run($sql, $bindings)->fetchColumn();

        return $value === false ? null : $value;
    }

    /**
     * Insert a row and return the new auto-increment id.
     *
     * @param array<string, mixed> $data
     */
    public function insert(string $table, array $data): int
    {
        $columns = array_keys($data);
        $placeholders = array_map(static fn (string $c): string => ':' . $c, $columns);

        $sql = sprintf(
            'INSERT INTO `%s` (%s) VALUES (%s)',
            $table,
            '`' . implode('`, `', $columns) . '`',
            implode(', ', $placeholders),
        );

        $this->run($sql, $this->prefixKeys($data));

        return (int) $this->pdo()->lastInsertId();
    }

    /**
     * Update rows matching a simple equality WHERE map. Returns affected rows.
     *
     * @param array<string, mixed> $data
     * @param array<string, mixed> $where
     */
    public function update(string $table, array $data, array $where): int
    {
        $set = implode(', ', array_map(static fn (string $c): string => "`{$c}` = :set_{$c}", array_keys($data)));
        $conditions = implode(' AND ', array_map(static fn (string $c): string => "`{$c}` = :where_{$c}", array_keys($where)));

        $bindings = [];
        foreach ($data as $key => $value) {
            $bindings["set_{$key}"] = $value;
        }
        foreach ($where as $key => $value) {
            $bindings["where_{$key}"] = $value;
        }

        $sql = "UPDATE `{$table}` SET {$set} WHERE {$conditions}";

        return $this->run($sql, $bindings)->rowCount();
    }

    /**
     * @param array<string, mixed> $bindings
     */
    public function affectingStatement(string $sql, array $bindings = []): int
    {
        return $this->run($sql, $bindings)->rowCount();
    }

    public function beginTransaction(): void
    {
        $this->pdo()->beginTransaction();
    }

    public function commit(): void
    {
        $this->pdo()->commit();
    }

    public function rollBack(): void
    {
        if ($this->pdo()->inTransaction()) {
            $this->pdo()->rollBack();
        }
    }

    /**
     * Prefix associative keys with ":" for named-parameter binding.
     *
     * @param array<string, mixed> $data
     * @return array<string, mixed>
     */
    private function prefixKeys(array $data): array
    {
        $bindings = [];
        foreach ($data as $key => $value) {
            $bindings[':' . $key] = $value;
        }

        return $bindings;
    }
}
