<?php

declare(strict_types=1);

namespace Core;

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

/**
 * Database — singleton PDO wrapper with query helpers.
 */
final class Database
{
    private static ?self $instance = null;
    private PDO $pdo;
    private string $dsn;
    private string $user;
    private string $pass;

    private function __construct()
    {
        $this->dsn  = sprintf('mysql:host=%s;port=%s;dbname=%s;charset=%s',
            DB_HOST, DB_PORT, DB_NAME, DB_CHARSET);
        $this->user = DB_USER;
        $this->pass = DB_PASS;
        $this->connect();
    }

    private function connect(): void
    {
        try {
            $this->pdo = new PDO($this->dsn, $this->user, $this->pass, [
                PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
                PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
                PDO::ATTR_EMULATE_PREPARES   => false,
                PDO::ATTR_PERSISTENT         => false,
                PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci",
            ]);
        } catch (PDOException $e) {
            throw new RuntimeException('Database connection failed. Check configuration.');
        }
    }

    public static function getInstance(): self
    {
        if (self::$instance === null) {
            self::$instance = new self();
        }
        return self::$instance;
    }

    // ── Query Helpers ─────────────────────────────────────────────────────────

    /**
     * Execute a prepared statement, reconnecting once if MySQL dropped the connection.
     *
     * @param array<string|int, mixed> $params
     */
    public function query(string $sql, array $params = []): PDOStatement
    {
        try {
            $stmt = $this->pdo->prepare($sql);
            $stmt->execute($params);
            return $stmt;
        } catch (PDOException $e) {
            // 2006 = MySQL server has gone away, 2013 = Lost connection
            if (in_array($e->errorInfo[1] ?? 0, [2006, 2013], true)) {
                $this->connect();
                $stmt = $this->pdo->prepare($sql);
                $stmt->execute($params);
                return $stmt;
            }
            throw $e;
        }
    }

    /**
     * Fetch a single row.
     *
     * @param array<string|int, mixed> $params
     * @return array<string, mixed>|null
     */
    public function fetchOne(string $sql, array $params = []): ?array
    {
        $row = $this->query($sql, $params)->fetch();
        return $row !== false ? $row : null;
    }

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

    /**
     * Fetch a single scalar value.
     *
     * @param array<string|int, mixed> $params
     */
    public function fetchValue(string $sql, array $params = []): mixed
    {
        return $this->query($sql, $params)->fetchColumn();
    }

    /**
     * Execute INSERT and return last insert ID.
     *
     * @param array<string, mixed> $data  column => value
     */
    public function insert(string $table, array $data): int
    {
        $columns     = implode(', ', array_map(fn($c) => "`$c`", array_keys($data)));
        $placeholders = implode(', ', array_fill(0, count($data), '?'));

        $this->query(
            "INSERT INTO `{$table}` ({$columns}) VALUES ({$placeholders})",
            array_values($data)
        );

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

    /**
     * Execute UPDATE and return affected rows.
     *
     * @param array<string, mixed> $data   column => value
     * @param array<string, mixed> $where  column => value (ANDed)
     */
    public function update(string $table, array $data, array $where): int
    {
        $set   = implode(', ', array_map(fn($c) => "`{$c}` = ?", array_keys($data)));
        $cond  = implode(' AND ', array_map(fn($c) => "`{$c}` = ?", array_keys($where)));
        $params = [...array_values($data), ...array_values($where)];

        return $this->query(
            "UPDATE `{$table}` SET {$set} WHERE {$cond}",
            $params
        )->rowCount();
    }

    /**
     * Execute DELETE and return affected rows.
     *
     * @param array<string, mixed> $where  column => value (ANDed)
     */
    public function delete(string $table, array $where): int
    {
        $cond = implode(' AND ', array_map(fn($c) => "`{$c}` = ?", array_keys($where)));

        return $this->query(
            "DELETE FROM `{$table}` WHERE {$cond}",
            array_values($where)
        )->rowCount();
    }

    // ── Transactions ──────────────────────────────────────────────────────────

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

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

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

    /**
     * Run a callable inside a transaction; rolls back automatically on exception.
     */
    public function transaction(callable $callback): mixed
    {
        $this->beginTransaction();
        try {
            $result = $callback($this);
            $this->commit();
            return $result;
        } catch (\Throwable $e) {
            $this->rollback();
            throw $e;
        }
    }

    // Prevent cloning / unserialization of singleton
    private function __clone() {}
    public function __wakeup(): never
    {
        throw new RuntimeException('Database instance cannot be unserialized.');
    }
}
