<?php

namespace CliqThemes\ORM\Query;

use CliqThemes\ORM\Database\Connection;
use CliqThemes\ORM\Model;

/**
 * Fluent SQL query builder tailored for {@see Model} instances.
 *
 * The builder mirrors a subset of Laravel's query builder syntax while
 * translating calls into SQL that can be executed through the shared
 * {@see Connection}. It handles compiling joins, where clauses, ordering and
 * hydration of results back into model instances.
 */
class Builder
{
    protected Connection $connection;

    /** @var class-string<Model> */
    protected string $modelClass;

    protected array $wheres = [];

    protected array $orders = [];

    protected ?int $limit = null;

    protected ?int $offset = null;

    protected array $selects = ['*'];

    /**
     * Aliases introduced by computed SELECT expressions (e.g. `distance`).
     *
     * They are not columns on any table, so a later ORDER BY or HAVING that
     * names one must be left exactly as written — see wrapColumn().
     *
     * @var string[]
     */
    protected array $selectAliases = [];

    /** @var string[] Raw SELECT expressions appended to the default columns. */
    protected array $selectsRaw = [];

    /** @var string[] Raw HAVING expressions. */
    protected array $havings = [];

    protected array $joins = [];

    protected array $groupBys = [];

    protected bool $distinct = false;

    protected bool $withTrashed = false;

    protected bool $softDeleteScopeApplied = false;

    /**
     * Create a new builder for the given model class and connection.
     *
     * @param class-string<Model> $modelClass
     */
    public function __construct(string $modelClass, Connection $connection)
    {
        $this->modelClass = $modelClass;
        $this->connection = $connection;
    }

    /**
     * Specify the columns that should be selected.
     *
     * @param string[] $columns
     */
    public function select(array $columns): self
    {
        $this->selects = $columns;

        return $this;
    }

    /**
     * Apply a DISTINCT modifier to the select query.
     */
    public function distinct(bool $state = true): self
    {
        $this->distinct = $state;

        return $this;
    }

    /**
     * Add a join clause to the query.
     */
    public function join(string $table, string $first, string $operator, string $second, string $type = 'INNER'): self
    {
        $this->joins[] = [
            'type' => strtoupper($type),
            'table' => $table,
            'first' => $first,
            'operator' => $operator,
            'second' => $second,
        ];

        return $this;
    }

    /**
     * Add a LEFT JOIN clause to the query.
     */
    public function leftJoin(string $table, string $first, string $operator, string $second): self
    {
        return $this->join($table, $first, $operator, $second, 'LEFT');
    }

    /**
     * Add a RIGHT JOIN clause to the query.
     */
    public function rightJoin(string $table, string $first, string $operator, string $second): self
    {
        return $this->join($table, $first, $operator, $second, 'RIGHT');
    }

    /**
     * Group the results by one or more columns.
     */
    public function groupBy(string|array ...$columns): self
    {
        foreach ($columns as $column) {
            if (is_array($column)) {
                foreach ($column as $nested) {
                    $this->groupBys[] = $nested;
                }
                continue;
            }

            $this->groupBys[] = $column;
        }

        return $this;
    }

    /**
     * SQL comparison operators this builder is allowed to compile. The operator
     * is the one part of a where clause that reaches the statement verbatim, so
     * anything outside this list is rejected rather than interpolated.
     *
     * @var string[]
     */
    private const OPERATORS = [
        '=', '!=', '<>', '<', '<=', '>', '>=', '<=>',
        'LIKE', 'NOT LIKE', 'IN', 'NOT IN', 'IS', 'IS NOT', 'REGEXP', 'NOT REGEXP',
    ];

    /**
     * Add a where clause to the query.
     *
     * @throws InvalidArgumentException When the operator is not supported.
     */
    public function where(string $column, string $operator, mixed $value, string $boolean = 'AND'): self
    {
        $operator = trim($operator);
        if (preg_match('/[a-z]/i', $operator) === 1) {
            $operator = strtoupper($operator);
        }
        if (! in_array($operator, self::OPERATORS, true)) {
            throw new \InvalidArgumentException(esc_html("Unsupported SQL operator: {$operator}"));
        }

        $boolean = strtoupper(trim($boolean)) === 'OR' ? 'OR' : 'AND';
        $this->wheres[] = compact('column', 'operator', 'value', 'boolean');

        return $this;
    }

    /**
     * Add an OR where clause to the query.
     */
    public function orWhere(string $column, string $operator, mixed $value): self
    {
        return $this->where($column, $operator, $value, 'OR');
    }

    /**
     * Add a "column IS NULL" constraint.
     */
    public function whereNull(string $column, string $boolean = 'AND'): self
    {
        $this->wheres[] = ['raw' => $this->wrapColumn($column) . ' IS NULL', 'boolean' => $boolean];

        return $this;
    }

    /**
     * Add a "column IS NOT NULL" constraint.
     */
    public function whereNotNull(string $column, string $boolean = 'AND'): self
    {
        $this->wheres[] = ['raw' => $this->wrapColumn($column) . ' IS NOT NULL', 'boolean' => $boolean];

        return $this;
    }

    /**
     * Constrain a column to a set of integer values. Values are cast to int and
     * inlined (injection-safe for integers), so this is intended for id sets,
     * not arbitrary strings. An empty set compiles to a never-true clause so the
     * query returns no rows.
     *
     * @param array<int, int|string> $values
     */
    public function whereIn(string $column, array $values, string $boolean = 'AND'): self
    {
        $values = array_values(array_unique($values, SORT_REGULAR));

        if ($values === []) {
            $this->wheres[] = ['raw' => '1 = 0', 'boolean' => $boolean];

            return $this;
        }

        // Integers are written straight into the clause — that is what makes an
        // id list of any length cheap, and an int cannot carry SQL. Anything
        // else is a literal that has to be quoted, so it goes through prepare().
        $allIntegers = true;
        foreach ($values as $value) {
            if (! is_int($value) && ! (is_string($value) && preg_match('/^-?\d+$/', $value) === 1)) {
                $allIntegers = false;
                break;
            }
        }

        if ($allIntegers) {
            $list = implode(', ', array_map('intval', $values));
        } else {
            $wpdb = $this->connection->getWpdb();
            $list = implode(', ', array_map(
                static fn ($value): string => $wpdb->prepare('%s', (string) $value),
                $values
            ));
        }

        $this->wheres[] = [
            'raw' => $this->wrapColumn($column) . ' IN (' . $list . ')',
            'boolean' => $boolean,
        ];

        return $this;
    }

    /**
     * Append a raw SELECT expression (e.g. a computed distance column).
     */
    public function selectRaw(string $expression): self
    {
        $this->selectsRaw[] = $expression;

        return $this;
    }

    /**
     * Add a raw HAVING expression. Useful for filtering on computed aliases
     * such as a distance column that cannot appear in a WHERE clause.
     */
    public function havingRaw(string $expression): self
    {
        $this->havings[] = $expression;

        return $this;
    }

    /**
     * Include soft-deleted records in the results.
     */
    public function withTrashed(): self
    {
        $this->withTrashed = true;

        return $this;
    }

    /**
     * Restrict the query to only soft-deleted records.
     */
    public function onlyTrashed(): self
    {
        $this->withTrashed = true;

        /** @var Model $model */
        $model = new $this->modelClass();

        if (method_exists($model, 'usesSoftDeletes') && $model->usesSoftDeletes()) {
            $this->whereNotNull($model->getDeletedAtColumn());
        }

        return $this;
    }

    /**
     * Select a great-circle distance (Haversine) from a reference point as an
     * aliased column. Coordinate columns are expected to be DECIMAL latitude
     * and longitude. The reference values are cast to float and inlined
     * locale-safely, so they are injection-safe.
     */
    public function selectDistance(
        float $latitude,
        float $longitude,
        string $latitudeColumn = 'latitude',
        string $longitudeColumn = 'longitude',
        string $alias = 'distance',
        string $unit = 'km'
    ): self {
        $earthRadius = strtolower($unit) === 'mi' ? 3958.7613 : 6371.0088;
        $lat = $this->wrapColumn($latitudeColumn);
        $lng = $this->wrapColumn($longitudeColumn);

        $expression = sprintf(
            '(%s * ACOS(LEAST(1.0, COS(RADIANS(%s)) * COS(RADIANS(%s)) * COS(RADIANS(%s) - RADIANS(%s)) + SIN(RADIANS(%s)) * SIN(RADIANS(%s))))) AS %s',
            $this->floatLiteral($earthRadius),
            $this->floatLiteral($latitude),
            $lat,
            $lng,
            $this->floatLiteral($longitude),
            $this->floatLiteral($latitude),
            $lat,
            $this->wrapIdentifier($alias)
        );

        $this->selectAliases[] = $alias;

        return $this->selectRaw($expression);
    }

    /**
     * Constrain rows to a lat/lng bounding box around a point. This is a cheap
     * pre-filter that lets an index on the coordinate columns prune candidates
     * before the more expensive Haversine HAVING is applied.
     */
    public function whereWithinBoundingBox(
        float $latitude,
        float $longitude,
        float $radius,
        string $unit = 'km',
        string $latitudeColumn = 'latitude',
        string $longitudeColumn = 'longitude'
    ): self {
        $radiusKm = strtolower($unit) === 'mi' ? $radius * 1.609344 : $radius;
        $latDelta = $radiusKm / 111.045;
        $lngDelta = $radiusKm / (111.045 * max(cos(deg2rad($latitude)), 0.000001));

        $this->where($latitudeColumn, '>=', $latitude - $latDelta);
        $this->where($latitudeColumn, '<=', $latitude + $latDelta);
        $this->where($longitudeColumn, '>=', $longitude - $lngDelta);
        $this->where($longitudeColumn, '<=', $longitude + $lngDelta);

        return $this;
    }

    /**
     * Convenience radius search: selects the distance, bounding-box pre-filters,
     * filters by the radius, and orders nearest-first.
     */
    public function withinDistance(
        float $latitude,
        float $longitude,
        float $radius,
        string $unit = 'km',
        string $latitudeColumn = 'latitude',
        string $longitudeColumn = 'longitude',
        string $alias = 'distance'
    ): self {
        $this->selectDistance($latitude, $longitude, $latitudeColumn, $longitudeColumn, $alias, $unit);
        $this->whereWithinBoundingBox($latitude, $longitude, $radius, $unit, $latitudeColumn, $longitudeColumn);
        $this->havingRaw(sprintf('%s <= %s', $this->wrapIdentifier($alias), $this->floatLiteral($radius)));
        $this->orderBy($alias, 'ASC');

        return $this;
    }

    /**
     * Order the results by the given column and direction.
     */
    public function orderBy(string $column, string $direction = 'ASC'): self
    {
        $this->orders[] = [$column, strtoupper($direction) === 'DESC' ? 'DESC' : 'ASC'];

        return $this;
    }

    /**
     * Limit the number of returned results and optionally offset them.
     */
    public function limit(int $limit, ?int $offset = null): self
    {
        $this->limit = $limit;
        $this->offset = $offset;

        return $this;
    }

    /**
     * Offset the result set by the given amount.
     */
    public function offset(int $offset): self
    {
        $this->offset = $offset;

        return $this;
    }

    /**
     * Execute the query and hydrate the resulting models.
     *
     * @return Model[]
     */
    public function get(): array
    {
        $this->applySoftDeleteScope();

        $sql = $this->compileSelect();
        [$sql, $bindings] = $this->applyWheres($sql);
        $sql = $this->applyGroupBys($sql);
        $sql = $this->applyHavings($sql);
        $sql = $this->applyOrders($sql);
        $sql = $this->applyLimit($sql);

        $rows = $this->connection->select($sql, $bindings);

        return $this->hydrate($rows);
    }

    /**
     * Retrieve the first result from the executed query.
     */
    public function first(): ?Model
    {
        $results = $this->limit(1)->get();

        return $results[0] ?? null;
    }

    /**
     * Shortcut to find a model by its primary key.
     */
    public function find(int|string $id): ?Model
    {
        /** @var Model $model */
        $model = new $this->modelClass();

        return $this->where($model->getPrimaryKey(), '=', $id)->first();
    }

    /**
     * Count the number of records matching the current constraints.
     */
    public function count(): int
    {
        $this->applySoftDeleteScope();

        $sql = $this->compileSelect(['COUNT(*) as aggregate']);
        [$sql, $bindings] = $this->applyWheres($sql);

        $value = $this->connection->value($sql, $bindings);

        return (int) $value;
    }

    /**
     * Create a new record for the underlying model.
     */
    public function create(array $attributes): Model
    {
        /** @var Model $model */
        $model = new $this->modelClass($attributes);
        $model->save();

        return $model;
    }

    /**
     * Execute the underlying select statement without hydrating the models.
     */
    public function raw(): array
    {
        $this->applySoftDeleteScope();

        $sql = $this->compileSelect();
        [$sql, $bindings] = $this->applyWheres($sql);
        $sql = $this->applyGroupBys($sql);
        $sql = $this->applyHavings($sql);
        $sql = $this->applyOrders($sql);
        $sql = $this->applyLimit($sql);
        return $this->connection->select($sql, $bindings);
    }

    /**
     * Compile the SELECT portion of the SQL statement.
     *
     * @param string[]|null $columns
     */
    protected function compileSelect(?array $columns = null): string
    {
        /** @var Model $model */
        $model = new $this->modelClass();

        $useRawSelects = $columns === null;
        $columns = $columns ?? $this->selects;

        // A bare `*` across a join returns BOTH tables' columns, and the driver
        // hands them back in one flat array — so a duplicated name like `id`
        // silently takes the joined table's value. A model's query returns that
        // model's rows, so qualify the wildcard to its own table whenever a join
        // is present. An explicitly selected column list is left alone.
        if ($this->joins !== [] && $columns === ['*']) {
            $columns = [$model->getQualifiedTable() . '.*'];
        }

        $compiled = array_map(fn ($column) => $this->wrapColumn($column), $columns);

        if ($useRawSelects && !empty($this->selectsRaw)) {
            $compiled = array_merge($compiled, $this->selectsRaw);
        }

        $quotedColumns = implode(', ', $compiled);
        $distinct = $this->distinct ? 'DISTINCT ' : '';

        $sql = sprintf('SELECT %s%s FROM %s', $distinct, $quotedColumns, $model->getQualifiedTable());

        return $sql . $this->compileJoins();
    }

    /**
     * Append any HAVING clauses to the SQL statement.
     */
    protected function applyHavings(string $sql): string
    {
        if (empty($this->havings)) {
            return $sql;
        }

        return $sql . ' HAVING ' . implode(' AND ', $this->havings);
    }

    /**
     * Inject the soft-delete constraint (deleted_at IS NULL) once per query
     * when the underlying model opts into soft deletes and trashed records
     * have not been explicitly requested.
     */
    protected function applySoftDeleteScope(): void
    {
        if ($this->withTrashed || $this->softDeleteScopeApplied) {
            return;
        }

        /** @var Model $model */
        $model = new $this->modelClass();

        if (method_exists($model, 'usesSoftDeletes') && $model->usesSoftDeletes()) {
            $this->whereNull($model->getDeletedAtColumn());
            $this->softDeleteScopeApplied = true;
        }
    }

    /**
     * Render a float as a locale-independent SQL numeric literal.
     */
    protected function floatLiteral(float $value): string
    {
        return rtrim(rtrim(sprintf('%.10F', $value), '0'), '.') ?: '0';
    }

    /**
     * Compile the stored join clauses into SQL fragments.
     */
    protected function compileJoins(): string
    {
        if (empty($this->joins)) {
            return '';
        }

        $segments = array_map(function (array $join) {
            $table = $this->qualifyJoinTable($join['table']);
            $first = $this->wrapColumn($join['first']);
            $second = $this->wrapColumn($join['second']);

            return sprintf(' %s JOIN %s ON %s %s %s', $join['type'], $table, $first, $join['operator'], $second);
        }, $this->joins);

        return implode('', $segments);
    }

    /**
     * Build the WHERE clause for the compiled SQL and return bindings.
     */
    protected function applyWheres(string $sql): array
    {
        if (empty($this->wheres)) {
            return [$sql, []];
        }

        $clauses = [];
        $bindings = [];

        foreach ($this->wheres as $index => $where) {
            $prefix = $index === 0 ? 'WHERE' : $where['boolean'];

            if (isset($where['raw'])) {
                $clauses[] = sprintf('%s %s', $prefix, $where['raw']);
                continue;
            }

            $placeholder = $this->determinePlaceholder($where['value']);
            $column = $this->wrapColumn($where['column']);
            $clauses[] = sprintf('%s %s %s %s', $prefix, $column, $where['operator'], $placeholder);
            $bindings[] = $this->castBinding($where['value'], $placeholder);
        }

        $sql .= ' ' . implode(' ', $clauses);

        return [$sql, $bindings];
    }

    /**
     * Append any GROUP BY clauses to the SQL statement.
     */
    protected function applyGroupBys(string $sql): string
    {
        if (empty($this->groupBys)) {
            return $sql;
        }

        $columns = array_map(fn ($column) => $this->wrapColumn($column), $this->groupBys);

        return $sql . ' GROUP BY ' . implode(', ', $columns);
    }

    /**
     * Append ORDER BY clauses to the SQL statement.
     */
    protected function applyOrders(string $sql): string
    {
        if (empty($this->orders)) {
            return $sql;
        }

        $orderParts = array_map(fn ($order) => sprintf('%s %s', $this->wrapColumn($order[0]), $order[1]), $this->orders);

        return $sql . ' ORDER BY ' . implode(', ', $orderParts);
    }

    /**
     * Append LIMIT/OFFSET clauses to the SQL statement.
     */
    protected function applyLimit(string $sql): string
    {
        if ($this->limit === null) {
            return $sql;
        }

        $sql .= ' LIMIT ' . (int) $this->limit;

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

        return $sql;
    }

    /**
     * Convert raw database rows into hydrated model instances.
     */
    /**
     * Convert raw database rows into hydrated model instances.
     *
     * @param array<int, array<string, mixed>> $rows
     *
     * @return Model[]
     */
    protected function hydrate(array $rows): array
    {
        return array_map(function (array $row) {
            /** @var Model $model */
            $model = new $this->modelClass();
            $model->forceFill($row);
            $model->syncOriginal();

            return $model;
        }, $rows);
    }

    /**
     * Determine the placeholder that should be used for a binding value.
     */
    protected function determinePlaceholder(mixed $value): string
    {
        if (is_int($value) || (is_string($value) && ctype_digit($value))) {
            return '%d';
        }

        if (is_float($value) || (is_string($value) && is_numeric($value))) {
            return '%f';
        }

        return '%s';
    }

    /**
     * Cast a binding value to match its placeholder type.
     */
    protected function castBinding(mixed $value, string $placeholder): mixed
    {
        return match ($placeholder) {
            '%d' => (int) $value,
            '%f' => (float) $value,
            default => $value,
        };
    }

    /**
     * Quote/format a column or expression for inclusion in SQL.
     */
    protected function wrapColumn(string $column): string
    {
        $column = trim($column);

        if ($column === '*') {
            return '*';
        }

        if (preg_match('/\s+as\s+/i', $column)) {
            [$expression, $alias] = preg_split('/\s+as\s+/i', $column, 2);

            return $this->wrapColumn($expression) . ' AS ' . $this->wrapIdentifier($alias);
        }

        if (preg_match('/^.+\s+[^\s]+$/', $column) && !str_contains($column, '(')) {
            [$expression, $alias] = preg_split('/\s+/', $column, 2);

            return $this->wrapColumn($expression) . ' AS ' . $this->wrapIdentifier($alias);
        }

        if (str_contains($column, '(')) {
            return $column;
        }

        if (str_contains($column, '.')) {
            $segments = array_map('trim', explode('.', $column));

            return implode('.', array_map(fn ($segment) => $this->wrapIdentifier($segment), $segments));
        }

        // With a join in play a bare column name may exist on both tables —
        // `country` and `status` live on stores AND locations — and MySQL
        // rejects the query as ambiguous. A builder belongs to one model, so an
        // unqualified name means that model's column; anything on a joined table
        // is written `alias.column` by the caller and took the branch above.
        if ($this->joins !== [] && ! in_array($column, $this->selectAliases, true)) {
            /** @var Model $model */
            $model = new $this->modelClass();

            return $this->wrapIdentifier($model->getQualifiedTable()) . '.' . $this->wrapIdentifier($column);
        }

        return $this->wrapIdentifier($column);
    }

    /**
     * Quote a table or column identifier using backticks.
     */
    protected function wrapIdentifier(string $identifier): string
    {
        $identifier = trim($identifier);

        if ($identifier === '*') {
            return '*';
        }

        // Double any embedded backtick so an identifier can never terminate its
        // own quoting and continue the statement.
        // `*` is a wildcard, never a name to quote — `table`.* is valid, but
        // `table`.`*` is a column literally called "*".
        $wrap = static fn (string $part): string => trim($part) === '*'
            ? '*'
            : '`' . str_replace('`', '``', trim($part, '`')) . '`';

        // A qualified name is quoted per part: `table`.`column`, not
        // `table.column`, which would name a column containing a dot. Without
        // this a joined table's columns cannot be referenced at all.
        if (str_contains($identifier, '.')) {
            return implode('.', array_map($wrap, explode('.', $identifier)));
        }

        return $wrap($identifier);
    }

    /**
     * Apply the WordPress table prefix and wrap any join table identifiers.
     */
    protected function qualifyJoinTable(string $table): string
    {
        $table = trim($table);
        $alias = null;

        if (stripos($table, ' as ') !== false) {
            [$table, $alias] = preg_split('/\s+as\s+/i', $table, 2);
        } elseif (str_contains($table, ' ')) {
            [$table, $alias] = preg_split('/\s+/', $table, 2);
        }

        $qualified = $this->connection->table($table);
        $wrapped = $this->wrapIdentifier($qualified);

        if ($alias) {
            return $wrapped . ' AS ' . $this->wrapIdentifier($alias);
        }

        return $wrapped;
    }
}
