<?php

declare(strict_types=1);

namespace System\Database;

use PDO;
use System\Contracts\DatabaseInterface;

final class QueryBuilder
{
    private PDO $pdo;

    private string $table = '';

    private array $select = ['*'];

    private array $where = [];

    private array $bindings = [];

    private array $orderBy = [];

    private ?int $limit = null;

    private ?int $offset = null;

    public function __construct(DatabaseInterface $database)
    {
        $this->pdo = $database->getConnection();
    }

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

        $this->table = $table;

        return $this;
    }

    public function select(array $columns): self
    {
        $this->select = $columns;

        return $this;
    }

    public function where(
        string $column,
        mixed $value,
        string $operator = '='
    ): self {

        $this->where[] = "{$column} {$operator} ?";

        $this->bindings[] = $value;

        return $this;
    }

    public function orderBy(
        string $column,
        string $direction = 'ASC'
    ): self {

        $direction = strtoupper($direction);

        if (!in_array($direction, ['ASC', 'DESC'], true)) {
            $direction = 'ASC';
        }

        $this->orderBy[] = "{$column} {$direction}";

        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 = sprintf(
            'SELECT %s FROM %s',
            implode(', ', $this->select),
            $this->table
        );

        if (!empty($this->where)) {
            $sql .= ' WHERE ' . implode(' AND ', $this->where);
        }

        if (!empty($this->orderBy)) {
            $sql .= ' ORDER BY ' . implode(', ', $this->orderBy);
        }

        if ($this->limit !== null) {
            $sql .= ' LIMIT ' . $this->limit;
        }

        if ($this->offset !== null) {
            $sql .= ' OFFSET ' . $this->offset;
        }

        $statement = $this->pdo->prepare($sql);

        $statement->execute($this->bindings);

        return $statement->fetchAll();
    }

public function first(): array|null
{
    $this->limit(1);

    $result = $this->get();

    return $result[0] ?? null;
}
public function find(int|string $id): array|null
{
    return $this
        ->where('id', $id)
        ->first();
}

public function count(): int
{
    $sql = "SELECT COUNT(*) total FROM {$this->table}";

    if (!empty($this->where)) {
        $sql .= ' WHERE ' . implode(' AND ', $this->where);
    }

    $statement = $this->pdo->prepare($sql);

    $statement->execute($this->bindings);

    return (int) $statement->fetchColumn();
}

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

public function insert(array $data): bool
{
    $columns = array_keys($data);

    $placeholders = array_fill(0, count($columns), '?');

    $sql = sprintf(
        "INSERT INTO %s (%s) VALUES (%s)",
        $this->table,
        implode(', ', $columns),
        implode(', ', $placeholders)
    );

    $statement = $this->pdo->prepare($sql);

    return $statement->execute(array_values($data));
}
public function update(array $data): bool
{
    if (empty($this->where)) {
        throw new \RuntimeException(
            'Update requires at least one WHERE clause.'
        );
    }

    $sets = [];

    foreach ($data as $column => $value) {
        $sets[] = "{$column} = ?";
    }

    $sql = sprintf(
        "UPDATE %s SET %s WHERE %s",
        $this->table,
        implode(', ', $sets),
        implode(' AND ', $this->where)
    );

    $statement = $this->pdo->prepare($sql);

    return $statement->execute(
        array_merge(array_values($data), $this->bindings)
    );
}

public function delete(): bool
{
    if (empty($this->where)) {
        throw new \RuntimeException(
            'Delete requires at least one WHERE clause.'
        );
    }

    $sql = sprintf(
        "DELETE FROM %s WHERE %s",
        $this->table,
        implode(' AND ', $this->where)
    );

    $statement = $this->pdo->prepare($sql);

    return $statement->execute($this->bindings);
}


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