<?php
class Database
{
    private static ?Database $instance = null;
    private PDO $pdo;
    private array $config;
    private bool $inTransaction = false;

    private function __construct()
    {
        $this->config = require ROOT_PATH . '/config/database.php';
        $this->connect();
    }

    private function connect(): void
    {
        $dsn = sprintf(
            'mysql:host=%s;port=%s;dbname=%s;charset=%s',
            $this->config['host'],
            $this->config['port'],
            $this->config['database'],
            $this->config['charset']
        );

        $options = [
            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::MYSQL_ATTR_INIT_COMMAND => "SET NAMES {$this->config['charset']} COLLATE {$this->config['collation']}",
        ];

        $this->pdo = new PDO($dsn, $this->config['username'], $this->config['password'], $options);
    }

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

    public function getPdo(): PDO
    {
        return $this->pdo;
    }

    /**
     * Execute a query and return all rows
     */
    public function fetchAll(string $sql, array $params = []): array
    {
        $stmt = $this->executeQuery($sql, $params);
        return $stmt->fetchAll();
    }

    /**
     * Execute a query and return the first row
     */
    public function fetchOne(string $sql, array $params = []): ?array
    {
        $stmt = $this->executeQuery($sql, $params);
        $row = $stmt->fetch();
        return $row ?: null;
    }

    /**
     * Execute a query and return a single column value
     */
    public function fetchColumn(string $sql, array $params = [])
    {
        $stmt = $this->executeQuery($sql, $params);
        return $stmt->fetchColumn();
    }

    /**
     * Insert a row and return last insert ID
     */
    public function insert(string $table, array $data): int
    {
        $columns = array_keys($data);
        $placeholders = array_fill(0, count($columns), '?');

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

        $this->executeQuery($sql, array_values($data));
        return (int) $this->pdo->lastInsertId();
    }

    /**
     * Update rows and return affected count
     */
    public function update(string $table, array $data, string $where, array $whereParams = []): int
    {
        $setClauses = [];
        $values = [];
        foreach ($data as $col => $val) {
            $setClauses[] = "`{$col}` = ?";
            $values[] = $val;
        }

        $sql = sprintf(
            'UPDATE `%s` SET %s WHERE %s',
            $table,
            implode(', ', $setClauses),
            $where
        );

        $stmt = $this->executeQuery($sql, array_merge($values, $whereParams));
        return $stmt->rowCount();
    }

    /**
     * Delete rows
     */
    public function delete(string $table, string $where, array $params = []): int
    {
        $sql = sprintf('DELETE FROM `%s` WHERE %s', $table, $where);
        $stmt = $this->executeQuery($sql, $params);
        return $stmt->rowCount();
    }

    /**
     * Raw execute (for stored procedures, etc.)
     */
    public function execute(string $sql, array $params = []): PDOStatement
    {
        return $this->executeQuery($sql, $params);
    }

    /**
     * Begin transaction
     */
    public function beginTransaction(): void
    {
        if (!$this->inTransaction) {
            $this->pdo->beginTransaction();
            $this->inTransaction = true;
        }
    }

    /**
     * Commit transaction
     */
    public function commit(): void
    {
        if ($this->inTransaction) {
            $this->pdo->commit();
            $this->inTransaction = false;
        }
    }

    /**
     * Rollback transaction
     */
    public function rollBack(): void
    {
        if ($this->inTransaction) {
            $this->pdo->rollBack();
            $this->inTransaction = false;
        }
    }

    /**
     * Query builder entry point
     */
    public function table(string $table): QueryBuilder
    {
        return new QueryBuilder($this, $table);
    }

    /**
     * Core query execution with reconnect
     */
    private function executeQuery(string $sql, array $params = []): PDOStatement
    {
        try {
            $stmt = $this->pdo->prepare($sql);
            $stmt->execute($params);
            return $stmt;
        } catch (PDOException $e) {
            // Reconnect on "MySQL server has gone away"
            if (stripos($e->getMessage(), 'gone away') !== false || $e->getCode() == 'HY000') {
                $this->connect();
                $stmt = $this->pdo->prepare($sql);
                $stmt->execute($params);
                return $stmt;
            }
            Logger::error('Database query failed: ' . $e->getMessage(), [
                'sql' => $sql,
                'params' => $params,
            ]);
            throw $e;
        }
    }
}

/**
 * Simple Query Builder
 */
class QueryBuilder
{
    private Database $db;
    private string $table;
    private array $wheres = [];
    private array $whereParams = [];
    private string $orderBy = '';
    private ?int $limitVal = null;
    private ?int $offsetVal = null;
    private string $selectColumns = '*';

    public function __construct(Database $db, string $table)
    {
        $this->db = $db;
        $this->table = $table;
    }

    public function select(string $columns): self
    {
        $this->selectColumns = $columns;
        return $this;
    }

    public function where(string $column, string $operator, $value): self
    {
        $this->wheres[] = "`{$column}` {$operator} ?";
        $this->whereParams[] = $value;
        return $this;
    }

    public function whereRaw(string $condition, array $params = []): self
    {
        $this->wheres[] = $condition;
        $this->whereParams = array_merge($this->whereParams, $params);
        return $this;
    }

    public function whereIn(string $column, array $values): self
    {
        $placeholders = implode(',', array_fill(0, count($values), '?'));
        $this->wheres[] = "`{$column}` IN ({$placeholders})";
        $this->whereParams = array_merge($this->whereParams, $values);
        return $this;
    }

    public function orderBy(string $column, string $direction = 'ASC'): self
    {
        $direction = strtoupper($direction) === 'DESC' ? 'DESC' : 'ASC';
        $this->orderBy = "ORDER BY `{$column}` {$direction}";
        return $this;
    }

    public function orderByRaw(string $raw): self
    {
        $this->orderBy = "ORDER BY {$raw}";
        return $this;
    }

    public function limit(int $limit): self
    {
        $this->limitVal = $limit;
        return $this;
    }

    public function offset(int $offset): self
    {
        $this->offsetVal = $offset;
        return $this;
    }

    public function get(): array
    {
        return $this->db->fetchAll($this->buildQuery(), $this->buildParams());
    }

    public function first(): ?array
    {
        $this->limitVal = 1;
        return $this->db->fetchOne($this->buildQuery(), $this->buildParams());
    }

    public function count(): int
    {
        $sql = "SELECT COUNT(*) FROM `{$this->table}`";
        if (!empty($this->wheres)) {
            $sql .= ' WHERE ' . implode(' AND ', $this->wheres);
        }
        return (int) $this->db->fetchColumn($sql, $this->whereParams);
    }

    private function buildQuery(): string
    {
        $sql = "SELECT {$this->selectColumns} FROM `{$this->table}`";
        if (!empty($this->wheres)) {
            $sql .= ' WHERE ' . implode(' AND ', $this->wheres);
        }
        if ($this->orderBy) {
            $sql .= ' ' . $this->orderBy;
        }
        if ($this->limitVal !== null) {
            $sql .= ' LIMIT ?';
        }
        if ($this->offsetVal !== null) {
            $sql .= ' OFFSET ?';
        }
        return $sql;
    }

    private function buildParams(): array
    {
        $params = $this->whereParams;
        if ($this->limitVal !== null) {
            $params[] = $this->limitVal;
        }
        if ($this->offsetVal !== null) {
            $params[] = $this->offsetVal;
        }
        return $params;
    }
}