<?php

namespace Velis\Db;

use ArrayObject;
use Closure;
use Velis\Db\Db;
use Velis\Db\QueryBuilder\ConflictsResolver;
use Velis\Db\QueryBuilder\Enums\JoinExpressionType;
use Velis\Db\QueryBuilder\Enums\WhereSeparator;
use Velis\Db\QueryBuilder\Exceptions\MissingTableException;
use Velis\Db\QueryBuilder\JoinExpression;
use Velis\Db\QueryBuilder\QueryBuilderMapper;
use Velis\Db\QueryBuilder\WhereExpression;

/**
 * Class used to build SQL queries.
 * @author Jakub Szczugieł <jakub.szczugiel@velistech.com>
 */
class QueryBuilder implements QueryBuilderInterface
{
    private ConflictsResolver $collisionResolver;
    private QueryBuilderMapper $mapper;
    private string $table;
    private array $select = ['*'];
    /**
     * @var JoinExpression[]
     */
    private array $joins = [];
    /**
     * @var WhereExpression[]
     */
    private array $wheres = [];
    private array $orderBy = [];
    private ?int $limit = null;
    private ?int $offset = null;
    private array $params = [];

    public function __construct(private Db $db)
    {
        $this->collisionResolver = new ConflictsResolver();
        $this->mapper = new QueryBuilderMapper(ArrayObject::class);
    }

    public function setMapperClass(string $mapperClass): self
    {
        $this->mapper = new QueryBuilderMapper($mapperClass);
        return $this;
    }

    public function from(Closure|QueryBuilderInterface|string $table, ?string $as = null): self
    {
        return match (true) {
            $table instanceof Closure => $this->fromClosure($table, $as),
            $table instanceof QueryBuilderInterface => $this->fromQueryBuilder($table, $as),
            default => $this->fromTable($table, $as),
        };
    }

    private function fromClosure(Closure $table, ?string $as): self
    {
        $queryBuilder = new self($this->db);
        $table($queryBuilder);

        $this->table = $this->addSqlWithParams($this->nameAs("({$queryBuilder->toSql()})", $as), $queryBuilder->getParams());
        return $this;
    }

    private function fromQueryBuilder(QueryBuilderInterface $table, ?string $as): self
    {
        $this->table = $this->addSqlWithParams($this->nameAs("({$table->toSql()})", $as), $table->getParams());
        return $this;
    }

    private function fromTable(string $table, ?string $as): self
    {
        $this->table = $this->nameAs($table, $as);
        return $this;
    }

    private function nameAs(string $name, ?string $as = null): string
    {
        return $as ? "{$name} AS {$as}" : $name;
    }

    public function select(?array $columns = ['*']): self
    {
        if (empty($columns)) {
            return $this;
        }

        $this->select = $columns;
        return $this;
    }

    public function join(string $table, string $first, ?string $operator = null, ?string $second = null, JoinExpressionType $type = JoinExpressionType::INNER): self
    {
        $this->joins[] = new JoinExpression($table, $first, $operator, $second, $type);

        return $this;
    }

    public function where(Closure|string|array $column, mixed $operator = null, mixed $value = null, WhereSeparator $boolean = WhereSeparator::AND): self
    {
        if (is_array($column)) {
            foreach ($column as $key => $val) {
                $this->where($key, $operator, $val);
            }

            return $this;
        }

        [$value, $operator] = $this->getValueAndOperator($value, $operator, func_num_args());

        return match (true) {
            $column instanceof Closure => $this->whereClosure($column, $boolean),
            default =>  $this->whereColumn($column, $operator, $value, $boolean),
        };
    }

    private function whereClosure(Closure $column, WhereSeparator $boolean): self
    {
        $queryBuilder = new self($this->db);
        $column($queryBuilder);
        $condition = '(' . $queryBuilder->getWhereExpression() . ')';
        $params = $queryBuilder->getParams();

        $condition = $this->addSqlWithParams($condition, $params);
        $this->wheres[] = new WhereExpression($condition, $boolean);

        return $this;
    }

    private function whereColumn(string $column, mixed $operator, mixed $value, WhereSeparator $boolean): self
    {
        if (is_array($value) && empty($value)) {
            return $this;
        }

        if (is_array($value)) {
            $params = array_combine(
                array_map(function ($item) use ($column): string {
                    $paramName = preg_replace('/\W/', '_', $column);
                    return "{$paramName}_{$item}";
                }, array_keys($value)),
                $value
            );
            $paramsSQL = implode(', ', array_map(fn ($param) => ":{$param}", array_keys($params)));
            $condition = "{$column} {$operator} ($paramsSQL)";
        } elseif ($value === null || $value instanceof NullValue) {
            $condition = "{$column} IS NULL";
            $params = [];
        } else {
            $paramName = preg_replace('/\W/', '_', $column);
            $condition = "{$column} {$operator} :{$paramName}";
            $params = [$paramName => $value];
        }

        $condition = $this->addSqlWithParams($condition, $params);
        $this->wheres[] = new WhereExpression($condition, $boolean);

        return $this;
    }

    public function whereRaw(string $sql, array $bindParams = []): self
    {
        $sql = $this->addSqlWithParams($sql, $bindParams);
        $this->wheres[] = new WhereExpression($sql, WhereSeparator::AND);

        return $this;
    }

    public function orWhere(Closure|string|array $column, mixed $operator = null, mixed $value = null): self
    {
        [$value, $operator] = $this->getValueAndOperator($value, $operator, func_num_args());
        return $this->where($column, $operator, $value, WhereSeparator::OR);
    }

    public function orderBy(string $field, string $direction = 'ASC'): self
    {
        $this->orderBy[] = "{$field} {$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 toSql(): string
    {
        if (!isset($this->table)) {
            throw new MissingTableException();
        }

        $sql = "SELECT " . implode(', ', $this->select) . " FROM {$this->table}";

        if ($this->joins) {
            $sql .= ' ' . implode(' ', array_map(fn ($join) => $join->getExpression(), $this->joins));
        }

        if ($this->wheres) {
            $sql .= ' WHERE ' . $this->getWhereExpression();
        }

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

        if ($this->limit) {
            $sql .= " LIMIT {$this->limit}";
        }

        if ($this->offset) {
            $sql .= " OFFSET {$this->offset}";
        }

        return $sql;
    }

    public function getParams(): array
    {
        return $this->params;
    }

    public function get(): array
    {
        return $this->mapper->map($this->db->getAll($this->toSql(), $this->getParams()));
    }

    private function getWhereExpression(): string
    {
        $expression = '';
        foreach ($this->wheres as $i => $condition) {
            $expression .= $i === 0 ? '' : ' ' . $condition->getSeparator() . ' ';
            $expression .= $condition->getSql();
        }

        return $expression;
    }

    private function getValueAndOperator(mixed $value, mixed $operator, int $countArgs): array
    {
        [$value, $operator] = match ($countArgs) {
            2 => [$operator, null],
            default => [$value, $operator],
        };

        $defaultOperator = is_array($value) ? 'IN' : '=';
        return [$value, $operator ?? $defaultOperator];
    }

    private function addSqlWithParams(string $sql, array $params): string
    {
        $conflicts = $this->collisionResolver->getConflicts($this->params, $params);
        $params = $this->collisionResolver->fixParams($params, $conflicts);
        $this->params = array_merge($this->params, $params);

        return $this->collisionResolver->fixSql($sql, $conflicts);
    }
}
