<?php
/**
 * SalonPro X - Database Connection Manager
 * Singleton PDO wrapper with prepared statement support
 */

namespace core;

use PDO;
use PDOException;
use PDOStatement;

class Database
{
    private static ?Database $instance = null;
    private ?PDO $connection = null;
    private ?PDOStatement $statement = null;
    private string $table = '';
    private string $select = '*';
    private string $where = '';
    private array $bindings = [];
    private string $orderBy = '';
    private string $groupBy = '';
    private string $having = '';
    private ?int $limit = null;
    private ?int $offset = null;
    private string $join = '';
    private bool $logQueries = false;
    private array $queryLog = [];

    private static function quoteColumn(string $column): string
    {
        if (str_contains($column, '(') || str_contains($column, '*')) {
            return $column;
        }
        $parts = explode('.', $column);
        $quoted = array_map(fn($p) => "`{$p}`", $parts);
        return implode('.', $quoted);
    }

    private function __construct()
    {
        try {
            $dsn = DB_DRIVER . ':host=' . DB_HOST . ';port=' . DB_PORT . ';dbname=' . DB_NAME . ';charset=' . DB_CHARSET;
            $this->connection = new PDO($dsn, DB_USER, DB_PASS, [
                PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
                PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_OBJ,
                PDO::ATTR_EMULATE_PREPARES => false,
                PDO::ATTR_STRINGIFY_FETCHES => false,
            ]);
        } catch (PDOException $e) {
            throw new \RuntimeException('Database connection failed: ' . $e->getMessage());
        }
    }

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

    public function getConnection(): PDO
    {
        return $this->connection;
    }

    public function table(string $table): self
    {
        $this->reset();
        $this->table = $table;
        return $this;
    }

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

    public function where(string|\Closure $column, string $operator = '=', mixed $value = null): self
    {
        if ($column instanceof \Closure) {
            $subQuery = new static($this->connection);
            $subQuery->table = $this->table;
            $column($subQuery);
            $prefix = empty($this->where) ? ' WHERE' : ' AND';
            $subWhere = substr($subQuery->where, 7);
            $this->where .= "{$prefix} ({$subWhere})";
            $this->bindings = array_merge($this->bindings, $subQuery->bindings);
            return $this;
        }
        $col = self::quoteColumn($column);
        if ($value === null && $operator !== 'IS' && $operator !== 'IS NOT') {
            $value = $operator;
            $operator = '=';
        }
        if ($operator === 'IS' && $value === null) {
            $this->where .= empty($this->where) ? " WHERE {$col} IS NULL" : " AND {$col} IS NULL";
            return $this;
        }
        if ($operator === 'IS NOT' && $value === null) {
            $this->where .= empty($this->where) ? " WHERE {$col} IS NOT NULL" : " AND {$col} IS NOT NULL";
            return $this;
        }
        $key = preg_replace('/[^a-zA-Z0-9_]/', '_', $column);
        $placeholder = ':' . $key . '_' . count($this->bindings);
        $this->where .= empty($this->where) ? " WHERE {$col} {$operator} {$placeholder}" : " AND {$col} {$operator} {$placeholder}";
        $this->bindings[$placeholder] = $value;
        return $this;
    }

    public function orWhere(string $column, string $operator = '=', mixed $value = null): self
    {
        $col = self::quoteColumn($column);
        if ($value === null && $operator !== 'IS' && $operator !== 'IS NOT') {
            $value = $operator;
            $operator = '=';
        }
        if ($operator === 'IS' && $value === null) {
            $this->where .= (empty($this->where) ? ' WHERE' : '') . " OR {$col} IS NULL";
            return $this;
        }
        if ($operator === 'IS NOT' && $value === null) {
            $this->where .= (empty($this->where) ? ' WHERE' : '') . " OR {$col} IS NOT NULL";
            return $this;
        }
        $key = preg_replace('/[^a-zA-Z0-9_]/', '_', $column);
        $placeholder = ':' . $key . '_' . count($this->bindings);
        $this->where .= " OR {$col} {$operator} {$placeholder}";
        $this->bindings[$placeholder] = $value;
        return $this;
    }

    public function whereIn(string $column, array $values): self
    {
        $col = self::quoteColumn($column);
        $placeholders = [];
        foreach ($values as $i => $value) {
            $placeholder = ':' . $column . '_in_' . $i;
            $placeholders[] = $placeholder;
            $this->bindings[$placeholder] = $value;
        }
        $this->where .= (empty($this->where) ? ' WHERE' : ' AND') . " {$col} IN (" . implode(',', $placeholders) . ')';
        return $this;
    }

    public function whereNull(string $column): self
    {
        $col = self::quoteColumn($column);
        $this->where .= (empty($this->where) ? ' WHERE' : ' AND') . " {$col} IS NULL";
        return $this;
    }

    public function whereNotNull(string $column): self
    {
        $col = self::quoteColumn($column);
        $this->where .= (empty($this->where) ? ' WHERE' : ' AND') . " {$col} IS NOT NULL";
        return $this;
    }

    public function whereRaw(string $sql, array $bindings = []): self
    {
        $this->where .= (empty($this->where) ? ' WHERE' : ' AND') . " {$sql}";
        foreach ($bindings as $key => $value) {
            $this->bindings[$key] = $value;
        }
        return $this;
    }

    public function whereBetween(string $column, mixed $start, mixed $end): self
    {
        $col = self::quoteColumn($column);
        $startPlaceholder = ':' . $column . '_between_start';
        $endPlaceholder = ':' . $column . '_between_end';
        $this->where .= (empty($this->where) ? ' WHERE' : ' AND') . " {$col} BETWEEN {$startPlaceholder} AND {$endPlaceholder}";
        $this->bindings[$startPlaceholder] = $start;
        $this->bindings[$endPlaceholder] = $end;
        return $this;
    }

    public function join(string $table, string $first, string $operator = '=', string $second = '', string $type = 'INNER'): self
    {
        $this->join .= " {$type} JOIN {$table} ON {$first} {$operator} {$second}";
        return $this;
    }

    public function leftJoin(string $table, string $first, string $operator = '=', string $second = ''): self
    {
        return $this->join($table, $first, $operator, $second, 'LEFT');
    }

    public function rightJoin(string $table, string $first, string $operator = '=', string $second = ''): self
    {
        return $this->join($table, $first, $operator, $second, 'RIGHT');
    }

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

    public function groupBy(string $columns): self
    {
        $this->groupBy = " GROUP BY {$columns}";
        return $this;
    }

    public function having(string $condition): self
    {
        $this->having = " HAVING {$condition}";
        return $this;
    }

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

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

    public function get(): array
    {
        $sql = "SELECT {$this->select} FROM {$this->table}{$this->join}{$this->where}{$this->groupBy}{$this->having}{$this->orderBy}";
        if ($this->limit !== null) {
            $sql .= " LIMIT {$this->limit}";
        }
        if ($this->offset !== null) {
            $sql .= " OFFSET {$this->offset}";
        }
        return $this->query($sql, $this->bindings);
    }

    public function first(): ?object
    {
        $this->limit(1);
        $results = $this->get();
        return $results[0] ?? null;
    }

    public function find(mixed $id, string $column = 'id'): ?object
    {
        return $this->where($column, $id)->first();
    }

    public function insert(array $data): int
    {
        $cols = array_map([self::class, 'quoteColumn'], array_keys($data));
        $columns = implode(', ', $cols);
        $keys = array_keys($data);
        $placeholders = ':' . implode(', :', $keys);
        $sql = "INSERT INTO {$this->table} ({$columns}) VALUES ({$placeholders})";
        $this->execute($sql, $this->prefixBindings($data));
        return (int)$this->connection->lastInsertId();
    }

    public function update(array $data): int
    {
        $sets = [];
        $bindings = [];
        foreach ($data as $column => $value) {
            $col = self::quoteColumn($column);
            $placeholder = ':set_' . $column;
            $sets[] = "{$col} = {$placeholder}";
            $bindings[$placeholder] = $value;
        }
        $sql = "UPDATE {$this->table} SET " . implode(', ', $sets) . $this->where;
        $bindings = array_merge($bindings, $this->bindings);
        return $this->execute($sql, $bindings);
    }

    public function delete(): int
    {
        $sql = "DELETE FROM {$this->table}{$this->where}";
        return $this->execute($sql, $this->bindings);
    }

    public function count(): int
    {
        $sql = "SELECT COUNT(*) as count FROM {$this->table}{$this->join}{$this->where}";
        $result = $this->query($sql, $this->bindings);
        return (int)($result[0]->count ?? 0);
    }

    public function increment(string $column, int $amount = 1): int
    {
        $sql = "UPDATE {$this->table} SET {$column} = {$column} + {$amount}{$this->where}";
        return $this->execute($sql, $this->bindings);
    }

    public function decrement(string $column, int $amount = 1): int
    {
        return $this->increment($column, -$amount);
    }

    public function sum(string $column): float
    {
        $sql = "SELECT COALESCE(SUM({$column}), 0) as total FROM {$this->table}{$this->join}{$this->where}";
        $result = $this->query($sql, $this->bindings);
        return (float)($result[0]->total ?? 0);
    }

    public function exists(): bool
    {
        return $this->count() > 0;
    }

    public function paginate(int $perPage = PAGINATION_LIMIT, int $page = 1): object
    {
        $total = $this->count();
        $totalPages = (int)ceil($total / $perPage);
        $page = max(1, min($page, $totalPages));
        $offset = ($page - 1) * $perPage;
        
        $this->limit($perPage)->offset($offset);
        $data = $this->get();
        
        return (object)[
            'data' => $data,
            'total' => $total,
            'perPage' => $perPage,
            'currentPage' => $page,
            'totalPages' => $totalPages,
            'hasPrev' => $page > 1,
            'hasNext' => $page < $totalPages,
            'prevPage' => $page - 1,
            'nextPage' => $page + 1,
        ];
    }

    public function query(string $sql, array $bindings = []): array
    {
        $this->logQuery($sql, $bindings);
        $this->statement = $this->connection->prepare($sql);
        $this->statement->execute($this->castBindings($bindings));
        return $this->statement->fetchAll();
    }

    public function execute(string $sql, array $bindings = []): int
    {
        $this->logQuery($sql, $bindings);
        $this->statement = $this->connection->prepare($sql);
        $this->statement->execute($this->castBindings($bindings));
        return $this->statement->rowCount();
    }

    public function raw(string $sql): array
    {
        $this->logQuery($sql);
        $this->statement = $this->connection->prepare($sql);
        $this->statement->execute();
        return $this->statement->fetchAll();
    }

    public function beginTransaction(): bool
    {
        return $this->connection->beginTransaction();
    }

    public function commit(): bool
    {
        return $this->connection->commit();
    }

    public function rollback(): bool
    {
        return $this->connection->rollBack();
    }

    public function lastInsertId(): string
    {
        return $this->connection->lastInsertId();
    }

    public function enableQueryLog(): void
    {
        $this->logQueries = true;
    }

    public function getQueryLog(): array
    {
        return $this->queryLog;
    }

    private function logQuery(string $sql, array $bindings = []): void
    {
        if ($this->logQueries || defined('APP_DEBUG') && APP_DEBUG) {
            $this->queryLog[] = ['sql' => $sql, 'bindings' => $bindings];
            $logEntry = date('Y-m-d H:i:s') . ' | ' . $sql . ' | ' . json_encode($bindings) . PHP_EOL;
            file_put_contents(PATH_LOGS . DS . 'sql.log', $logEntry, FILE_APPEND);
        }
    }

    private function prefixBindings(array $data): array
    {
        $prefixed = [];
        foreach ($data as $key => $value) {
            $prefixed[':' . $key] = $value;
        }
        return $prefixed;
    }

    private function castBindings(array $bindings): array
    {
        $casted = [];
        foreach ($bindings as $key => $value) {
            if (is_array($value)) {
                $casted[$key] = implode(',', $value);
            } elseif (is_bool($value)) {
                $casted[$key] = $value ? 1 : 0;
            } else {
                $casted[$key] = $value;
            }
        }
        return $casted;
    }

    private function reset(): void
    {
        $this->select = '*';
        $this->where = '';
        $this->bindings = [];
        $this->orderBy = '';
        $this->groupBy = '';
        $this->having = '';
        $this->limit = null;
        $this->offset = null;
        $this->join = '';
    }
}
