<?php
declare(strict_types=1);

namespace Celena\Core\Database;

/**
 * Лёгкий fluent query builder. Цель — безопасность (только prepared statements),
 * не полное покрытие SQL. Сложные запросы — через Connection::run().
 */
final class QueryBuilder
{
    private string $table;
    /** @var string[] */ private array $columns = ['*'];
    /** @var array<int, array{type:string, sql:string, bindings:array}> */ private array $wheres = [];
    /** @var array<int, array{type:string, table:string, on:string}> */ private array $joins = [];
    /** @var array<int, array{col:string, dir:string}> */ private array $orders = [];
    private ?int $limit = null;
    private ?int $offset = null;
    /** @var string[] */ private array $groupBy = [];

    public function __construct(private readonly Connection $conn, string $table)
    {
        $this->table = $table;
    }

    public function select(string ...$cols): self
    {
        $this->columns = $cols === [] ? ['*'] : $cols;
        return $this;
    }

    public function where(string $column, string $operator, mixed $value): self
    {
        $this->wheres[] = [
            'type' => $this->wheres === [] ? 'AND' : 'AND',
            'sql' => $this->q($column) . ' ' . $operator . ' ?',
            'bindings' => [$value],
        ];
        return $this;
    }

    public function orWhere(string $column, string $operator, mixed $value): self
    {
        $this->wheres[] = [
            'type' => 'OR',
            'sql' => $this->q($column) . ' ' . $operator . ' ?',
            'bindings' => [$value],
        ];
        return $this;
    }

    /** @param array<int, mixed> $values */
    public function whereIn(string $column, array $values): self
    {
        if ($values === []) {
            $this->wheres[] = ['type' => 'AND', 'sql' => '1=0', 'bindings' => []];
            return $this;
        }
        $placeholders = implode(',', array_fill(0, count($values), '?'));
        $this->wheres[] = [
            'type' => 'AND',
            'sql' => $this->q($column) . ' IN (' . $placeholders . ')',
            'bindings' => array_values($values),
        ];
        return $this;
    }

    public function whereNull(string $column): self
    {
        $this->wheres[] = ['type' => 'AND', 'sql' => $this->q($column) . ' IS NULL', 'bindings' => []];
        return $this;
    }

    public function whereNotNull(string $column): self
    {
        $this->wheres[] = ['type' => 'AND', 'sql' => $this->q($column) . ' IS NOT NULL', 'bindings' => []];
        return $this;
    }

    public function whereRaw(string $sql, array $bindings = []): self
    {
        $this->wheres[] = ['type' => 'AND', 'sql' => $sql, 'bindings' => $bindings];
        return $this;
    }

    public function orderBy(string $column, string $direction = 'asc'): self
    {
        $this->orders[] = ['col' => $this->q($column), 'dir' => strtoupper($direction) === 'DESC' ? 'DESC' : 'ASC'];
        return $this;
    }

    public function limit(int $n): self  { $this->limit = max(0, $n); return $this; }
    public function offset(int $n): self { $this->offset = max(0, $n); return $this; }

    public function join(string $table, string $on): self
    {
        $this->joins[] = ['type' => 'INNER', 'table' => $this->conn->table_q($table), 'on' => $on];
        return $this;
    }

    public function leftJoin(string $table, string $on): self
    {
        $this->joins[] = ['type' => 'LEFT', 'table' => $this->conn->table_q($table), 'on' => $on];
        return $this;
    }

    public function groupBy(string ...$columns): self
    {
        foreach ($columns as $c) {
            $this->groupBy[] = $this->q($c);
        }
        return $this;
    }

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

    /** @return array<string, mixed>|null */
    public function first(): ?array
    {
        $this->limit(1);
        $rows = $this->get();
        return $rows[0] ?? null;
    }

    public function value(string $column): mixed
    {
        $this->select($column);
        $row = $this->first();
        return $row[$column] ?? null;
    }

    public function count(string $column = '*'): int
    {
        $this->columns = ['COUNT(' . ($column === '*' ? '*' : $this->q($column)) . ') AS aggregate'];
        // ORDER BY при COUNT(*) без GROUP BY — ошибка в PostgreSQL
        // («column must appear in GROUP BY») и в MySQL с ONLY_FULL_GROUP_BY.
        $this->orders = [];
        $row = $this->first();
        return (int) ($row['aggregate'] ?? 0);
    }

    /**
     * @param array<string, mixed> $data
     * @return string ID вставленной записи (как строка для совместимости с PG/MySQL)
     */
    public function insert(array $data): string
    {
        $cols = array_keys($data);
        $placeholders = implode(',', array_fill(0, count($cols), '?'));
        $sql = 'INSERT INTO ' . $this->conn->table_q($this->table)
            . ' (' . implode(',', array_map(fn ($c) => $this->q($c), $cols)) . ')'
            . ' VALUES (' . $placeholders . ')';

        $driver = $this->conn->driver();
        if ($driver->supportsReturning()) {
            $sql .= ' RETURNING id';
            $row = $this->conn->run($sql, array_values($data))->fetch();
            return (string) ($row['id'] ?? '');
        }
        $this->conn->run($sql, array_values($data));
        return $this->conn->lastInsertId($this->table);
    }

    /** @param array<string, mixed> $data */
    public function update(array $data): int
    {
        if ($data === []) {
            return 0;
        }
        $sets = [];
        $bindings = [];
        foreach ($data as $col => $val) {
            $sets[] = $this->q($col) . ' = ?';
            $bindings[] = $val;
        }
        $sql = 'UPDATE ' . $this->conn->table_q($this->table) . ' SET ' . implode(', ', $sets);
        [$where, $whereBindings] = $this->compileWhere();
        $sql .= $where;
        $stmt = $this->conn->run($sql, array_merge($bindings, $whereBindings));
        return $stmt->rowCount();
    }

    public function delete(): int
    {
        $sql = 'DELETE FROM ' . $this->conn->table_q($this->table);
        [$where, $bindings] = $this->compileWhere();
        $sql .= $where;
        return $this->conn->run($sql, $bindings)->rowCount();
    }

    /** @return array{0:string, 1:array} */
    private function compileSelect(): array
    {
        $cols = [];
        foreach ($this->columns as $c) {
            $cols[] = $c === '*' ? '*' : (str_contains($c, '(') || str_contains($c, ' AS ') || str_contains($c, ' as ') ? $c : $this->q($c));
        }
        $sql = 'SELECT ' . implode(', ', $cols) . ' FROM ' . $this->conn->table_q($this->table);

        foreach ($this->joins as $j) {
            $sql .= ' ' . $j['type'] . ' JOIN ' . $j['table'] . ' ON ' . $j['on'];
        }

        [$where, $bindings] = $this->compileWhere();
        $sql .= $where;

        if ($this->groupBy !== []) {
            $sql .= ' GROUP BY ' . implode(', ', $this->groupBy);
        }
        if ($this->orders !== []) {
            $sql .= ' ORDER BY ' . implode(', ', array_map(fn ($o) => $o['col'] . ' ' . $o['dir'], $this->orders));
        }
        if ($this->limit !== null) {
            $sql .= ' LIMIT ' . $this->limit;
        } elseif ($this->offset !== null && $this->conn->driver()->name() === 'mysql') {
            // MySQL не понимает OFFSET без LIMIT (в PostgreSQL это валидно).
            $sql .= ' LIMIT 18446744073709551615';
        }
        if ($this->offset !== null) {
            $sql .= ' OFFSET ' . $this->offset;
        }
        return [$sql, $bindings];
    }

    /** @return array{0:string, 1:array} */
    private function compileWhere(): array
    {
        if ($this->wheres === []) {
            return ['', []];
        }
        $sql = ' WHERE ';
        $bindings = [];
        foreach ($this->wheres as $i => $w) {
            if ($i > 0) {
                $sql .= ' ' . $w['type'] . ' ';
            }
            $sql .= $w['sql'];
            $bindings = array_merge($bindings, $w['bindings']);
        }
        return [$sql, $bindings];
    }

    private function q(string $column): string
    {
        // Если уже квалифицирован (table.column) — обработаем оба сегмента.
        return $this->conn->driver()->quoteId($column);
    }
}
