<?php

namespace SoftminalB2B\Models;

use SoftminalB2B\Support\Collection;

if (!defined('ABSPATH')) {
    exit;
}

abstract class Model
{
    protected $table;
    protected $primaryKey = 'id';
    protected $attributes = [];
    protected $fillable = [];
    protected $casts = [];
    protected $timestamps = false;
    /** Set to null on tables that lack the column (several are insert-only). */
    protected $createdAtColumn = 'created_at';
    protected $updatedAtColumn = 'updated_at';
    protected $relations = [];
    protected $with = [];

    /**
     * When true, the hasMany/hasOne/belongsTo helpers record a relation
     * descriptor and return an empty placeholder instead of running a query.
     * This lets the eager loader learn a relation's keys from the model's own
     * relation method (declared once, on the subclass) without a per-model query.
     */
    private $eagerIntrospect = false;

    /** @var array<string, mixed>|null */
    private $eagerDescriptor = null;
    protected $query = [
        'select'  => '*',
        'where'   => [],
        'orderBy' => null,
        'limit'   => null,
        'offset'  => null
    ];

    private static array $validOperators = ['=', '!=', '>', '<', '>=', '<=', 'LIKE', 'NOT LIKE', 'IN'];

    private string $cacheGroup = 'smb2b';
    private int $cacheTtl = 60;

    public static function table(): string
    {
        global $wpdb;
        $instance = new static();
        return $wpdb->prefix . SMB2B_TABLE_PREFIX . $instance->table;
    }

    public static function query()
    {
        return new static();
    }

    public function with($relations)
    {
        if (is_string($relations)) {
            $relations = [$relations];
        }

        $this->with = $relations;
        return $this;
    }

    protected function hasMany($related, $foreignKey, $localKey = null)
    {
        $localKey = $localKey ?: $this->primaryKey;

        if ($this->eagerIntrospect) {
            $this->eagerDescriptor = ['type' => 'hasMany', 'related' => $related, 'fk' => $foreignKey, 'lk' => $localKey];
            return new Collection([]);
        }

        $localValue = $this->attributes[$localKey] ?? null;

        if ($localValue === null) {
            return new Collection([]);
        }

        return $related::where($foreignKey, $localValue)->get();
    }

    protected function hasOne($related, $foreignKey, $localKey = null)
    {
        $localKey = $localKey ?: $this->primaryKey;

        if ($this->eagerIntrospect) {
            $this->eagerDescriptor = ['type' => 'hasOne', 'related' => $related, 'fk' => $foreignKey, 'lk' => $localKey];
            return null;
        }

        $localValue = $this->attributes[$localKey] ?? null;

        if ($localValue === null) {
            return null;
        }

        return $related::where($foreignKey, $localValue)->first();
    }

    protected function belongsTo($related, $foreignKey, $ownerKey = 'id')
    {
        if ($this->eagerIntrospect) {
            $this->eagerDescriptor = ['type' => 'belongsTo', 'related' => $related, 'fk' => $foreignKey, 'lk' => $ownerKey];
            return null;
        }

        $foreignValue = $this->attributes[$foreignKey] ?? null;

        if ($foreignValue === null) {
            return null;
        }

        return $related::where($ownerKey, $foreignValue)->first();
    }

    public function load($relation)
    {
        if (!$this->isRelationMethod($relation)) {
            return null;
        }

        $this->relations[$relation] = $this->$relation();
        return $this->relations[$relation];
    }

    /**
     * Whether $name is a relation that magic access is allowed to invoke.
     *
     * Magic property access (`$model->foo`) and load() must NEVER be able to
     * call an arbitrary method by name — that would let a property read trigger
     * side effects like save() or delete(). A relation is, by definition, a
     * public, non-static, zero-argument method declared on the concrete model
     * subclass — the base Model's own API (save/delete/update/load/…) is
     * declared here and is therefore excluded.
     */
    private function isRelationMethod($name): bool
    {
        if (!is_string($name) || $name === '' || !method_exists($this, $name)) {
            return false;
        }

        try {
            $method = new \ReflectionMethod($this, $name);
        } catch (\ReflectionException $e) {
            return false;
        }

        return $method->isPublic()
            && !$method->isStatic()
            && $method->getNumberOfRequiredParameters() === 0
            && $method->getDeclaringClass()->getName() !== self::class;
    }

    public function loadRelations(array $relations)
    {
        foreach ($relations as $relation) {
            $this->load($relation);
        }

        return $this;
    }

    /**
     * Learn a relation's shape (type + keys) from its own method, without
     * running its query — used by the eager loader to batch.
     *
     * @return array<string, mixed>|null
     */
    private function relationDescriptor(string $relation): ?array
    {
        if (!$this->isRelationMethod($relation)) {
            return null;
        }

        $this->eagerIntrospect = true;
        $this->eagerDescriptor = null;

        try {
            $this->$relation();
        } finally {
            $this->eagerIntrospect = false;
        }

        return $this->eagerDescriptor;
    }

    /**
     * Eager-load $relations onto a whole collection with ONE query per relation
     * (a `whereIn` over the parents' keys), instead of the fake eager loading
     * that ran the relation's query once per model (1 + N). Falls back to the
     * per-model lazy load for any relation whose shape can't be introspected
     * (e.g. a hand-rolled relation method not built on hasMany/hasOne/belongsTo).
     *
     * @param Model[]  $models
     * @param string[] $relations
     */
    private function eagerLoad(array $models, array $relations): void
    {
        if (empty($models)) {
            return;
        }

        foreach ($relations as $relation) {
            $descriptor = $models[0]->relationDescriptor($relation);

            if ($descriptor === null) {
                foreach ($models as $model) {
                    $model->load($relation);
                }
                continue;
            }

            $this->eagerLoadRelation($models, $relation, $descriptor);
        }
    }

    /**
     * @param Model[]              $models
     * @param array<string, mixed> $descriptor
     */
    private function eagerLoadRelation(array $models, string $relation, array $descriptor): void
    {
        /** @var class-string<Model> $related */
        $related = $descriptor['related'];
        $type    = $descriptor['type'];

        // belongsTo: parent holds the foreign key, match against the related
        // owner key. hasMany/hasOne: parent holds the local key, match against
        // the related foreign key.
        $parentKey = $type === 'belongsTo' ? $descriptor['fk'] : $descriptor['lk'];
        $matchKey  = $type === 'belongsTo' ? $descriptor['lk'] : $descriptor['fk'];

        $keys = [];
        foreach ($models as $model) {
            $value = $model->attributes[$parentKey] ?? null;
            if ($value !== null) {
                $keys[(string) $value] = $value;
            }
        }

        $relatedModels = empty($keys)
            ? []
            : iterator_to_array($related::query()->whereIn($matchKey, array_values($keys))->get());

        // Bucket the related rows by their match key.
        $bucketed = [];
        foreach ($relatedModels as $relatedModel) {
            $bucketKey = (string) ($relatedModel->attributes[$matchKey] ?? '');
            $bucketed[$bucketKey][] = $relatedModel;
        }

        foreach ($models as $model) {
            $value  = $model->attributes[$parentKey] ?? null;
            $bucket = ($value !== null && isset($bucketed[(string) $value])) ? $bucketed[(string) $value] : [];

            if ($type === 'hasMany') {
                $model->relations[$relation] = new Collection($bucket);
            } else {
                // hasOne / belongsTo — a single related model (or null).
                $model->relations[$relation] = $bucket[0] ?? null;
            }
        }
    }

    private function getCacheVersion(): int
    {
        $key = 'model_cache_ver:' . static::table();
        $ver = wp_cache_get($key, $this->cacheGroup);
        return $ver ? (int) $ver : 1;
    }

    private static function bumpCacheVersion(): void
    {
        $instance = new static();
        $key = 'model_cache_ver:' . static::table();
        $ver = (int) wp_cache_get($key, $instance->cacheGroup);

        // An unset key reads as 0 here but as 1 in getCacheVersion(), so without
        // this floor the first write after a cold cache would re-write v1 and
        // leave every v1-keyed query result stale.
        $ver = max(1, $ver);

        wp_cache_set($key, $ver + 1, $instance->cacheGroup, DAY_IN_SECONDS);
    }

    private function cacheKey(string $suffix): string
    {
        return $suffix . ':v' . $this->getCacheVersion();
    }

    private function cacheKeyForSql(string $sql, array $params = []): string
    {
        return $this->cacheKey('sql:' . md5(static::table() . '|' . $sql . '|' . wp_json_encode($params)));
    }

    public static function all()
    {
        global $wpdb;

        $instance = new static();
        $safeTable = esc_sql(static::table());

        $cacheKey = $instance->cacheKey('all:' . md5($safeTable));
        $cached = wp_cache_get($cacheKey, $instance->cacheGroup);

        if ($cached !== false) {
            return new Collection(array_map(function ($item) {
                return static::hydrate($item);
            }, $cached ?: []));
        }

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
        $results = $wpdb->get_results(
            $wpdb->prepare("SELECT * FROM %i", static::table()),
            ARRAY_A
        );

        wp_cache_set($cacheKey, $results ?: [], $instance->cacheGroup, $instance->cacheTtl);

        return new Collection(array_map(function ($item) {
            return static::hydrate($item);
        }, $results ?: []));
    }

    public function toArray(): array
    {
        $attributes = $this->attributes;

        foreach ($attributes as $key => $value) {
            if ($value instanceof \DateTime) {
                $attributes[$key] = $value->format('Y-m-d H:i:s');
            } elseif (is_object($value) && method_exists($value, 'toArray')) {
                $attributes[$key] = $value->toArray();
            }
        }

        foreach ($this->relations as $key => $relation) {
            if ($relation instanceof Model) {
                $attributes[$key] = $relation->toArray();
            } elseif ($relation instanceof Collection) {
                $attributes[$key] = $relation->toArray();
            } elseif (is_array($relation)) {
                $attributes[$key] = array_map(function ($item) {
                    return $item instanceof Model ? $item->toArray() : $item;
                }, $relation);
            }
        }

        return $attributes;
    }

    public function toJson(): string
    {
        return wp_json_encode($this->toArray());
    }

    public static function where($column, $operator = null, $value = null)
    {
        $instance = new static();
        return $instance->andWhere($column, $operator, $value);
    }

    public function andWhere($column, $operator = null, $value = null, $boolean = 'AND')
    {
        if ($value === null) {
            $value    = $operator;
            $operator = '=';
        }

        $operator = strtoupper((string) $operator);
        if (!in_array($operator, self::$validOperators, true)) {
            throw new \InvalidArgumentException('Invalid operator: ' . esc_html($operator));
        }

        if (in_array($operator, ['LIKE', 'NOT LIKE'], true)) {
            $value = (string) $value;
            // A bare search term is auto-wrapped as a "contains" match; escape
            // its LIKE metacharacters (% and _) so they match literally rather
            // than acting as wildcards. A caller that passes its own % pattern
            // is intentionally using wildcards, so leave that untouched.
            if (strpos($value, '%') === false) {
                global $wpdb;
                $value = '%' . $wpdb->esc_like($value) . '%';
            }
        }

        $this->query['where'][] = [
            'column'   => (string) $column,
            'operator' => $operator,
            'value'    => $value,
            'boolean'  => $boolean
        ];

        return $this;
    }

    public function orWhere($column, $operator = null, $value = null)
    {
        return $this->andWhere($column, $operator, $value, 'OR');
    }

    public function whereIn($column, array $values, $boolean = 'AND')
    {
        $this->query['where'][] = [
            'column'   => (string) $column,
            'operator' => 'IN',
            'value'    => $values,
            'boolean'  => $boolean
        ];

        return $this;
    }

    /**
     * Compare one column against another (`a > b`), not against a bound value —
     * the one thing andWhere() cannot express, since it always binds the
     * right-hand side. Both sides are sanitised as column identifiers; the
     * operator is limited to the comparison set. Used for read-cursor tests
     * like `last_message_id > shop_last_read_id`.
     */
    public function whereColumn($column, $operator, $otherColumn, $boolean = 'AND')
    {
        $operator = strtoupper((string) $operator);

        if (!in_array($operator, ['=', '!=', '>', '<', '>=', '<='], true)) {
            throw new \InvalidArgumentException('Invalid comparison operator: ' . esc_html($operator));
        }

        $this->query['where'][] = [
            'column'      => (string) $column,
            'operator'    => $operator,
            'rightColumn' => (string) $otherColumn,
            'value'       => null,
            'boolean'     => $boolean
        ];

        return $this;
    }

    public function whereNull($column, $boolean = 'AND')
    {
        $this->query['where'][] = [
            'column'   => (string) $column,
            'operator' => 'IS NULL',
            'value'    => null,
            'boolean'  => $boolean
        ];

        return $this;
    }

    public function whereNotNull($column, $boolean = 'AND')
    {
        $this->query['where'][] = [
            'column'   => (string) $column,
            'operator' => 'IS NOT NULL',
            'value'    => null,
            'boolean'  => $boolean
        ];

        return $this;
    }

    public function whereBetween($column, array $values, $boolean = 'AND')
    {
        $this->query['where'][] = [
            'column'   => (string) $column,
            'operator' => 'BETWEEN',
            'value'    => $values,
            'boolean'  => $boolean
        ];

        return $this;
    }

    public function select($columns)
    {
        $this->query['select'] = is_array($columns) ? $columns : [$columns];
        return $this;
    }

    public function orderBy($column, $direction = 'ASC')
    {
        $this->query['orderBy'] = [
            'column'    => (string) $column,
            'direction' => strtoupper((string) $direction)
        ];
        return $this;
    }

    public function limit($limit)
    {
        $this->query['limit'] = (int) $limit;
        return $this;
    }

    public function offset($offset)
    {
        $this->query['offset'] = (int) $offset;
        return $this;
    }

    public function paginate($perPage = 15, $page = 1)
    {
        global $wpdb;

        $this->query['limit']  = (int) $perPage;
        $this->query['offset'] = ((int) $page - 1) * (int) $perPage;

        $safeTable = esc_sql(static::table());
        $params = [];

        $whereSql = '';
        if (!empty($this->query['where'])) {
            [$whereSql, $whereParams] = $this->buildWhereClause();
            $params = $whereParams;
        }

        $cacheKey = $this->cacheKeyForSql("COUNT:{$safeTable}{$whereSql}", $params);
        $cachedTotal = wp_cache_get($cacheKey, $this->cacheGroup);

        if ($cachedTotal === false) {
            $countSql = "SELECT COUNT(*) FROM %i" . $whereSql;
            $countParams = array_merge([static::table()], $params);
            // Table name is prepared as an identifier (%i); values are bound.
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, PluginCheck.Security.DirectDB.UnescapedDBParameter
            $total = (int) $wpdb->get_var($wpdb->prepare($countSql, $countParams));
            wp_cache_set($cacheKey, $total, $this->cacheGroup, $this->cacheTtl);
        } else {
            $total = (int) $cachedTotal;
        }

        $results = $this->get();

        return [
            'data' => $results,
            'meta' => [
                'current_page' => (int) $page,
                'per_page'     => (int) $perPage,
                'total'        => $total,
                'last_page'    => (int) ceil($total / max(1, (int) $perPage)),
                'has_more'     => ((int) $page * (int) $perPage) < $total
            ]
        ];
    }

    public function get($columns = ['*'])
    {
        global $wpdb;

        if ($columns === ['*'] && !empty($this->query['select'])) {
            $columns = $this->query['select'];
        }

        $selectClause = $this->normalizeSelect($columns);
        $safeTable = esc_sql(static::table());
        $params = [];

        $whereSql = '';
        if (!empty($this->query['where'])) {
            [$whereSql, $whereParams] = $this->buildWhereClause();
            $params = array_merge($params, $whereParams);
        }

        $orderSql = '';
        if ($this->query['orderBy']) {
            $orderCol = esc_sql(sanitize_key($this->query['orderBy']['column']));
            $dir = strtoupper($this->query['orderBy']['direction']) === 'DESC' ? 'DESC' : 'ASC';
            $orderSql = " ORDER BY `{$orderCol}` {$dir}";
        }

        $limitSql = '';
        if ($this->query['limit']) {
            $limitSql .= ' LIMIT %d';
            $params[] = (int) $this->query['limit'];

            if ($this->query['offset']) {
                $limitSql .= ' OFFSET %d';
                $params[] = (int) $this->query['offset'];
            }
        }

        $baseSql = "SELECT {$selectClause} FROM %i{$whereSql}{$orderSql}{$limitSql}";
        array_unshift($params, static::table());

        $cacheKey = $this->cacheKeyForSql($baseSql, $params);
        $cached = wp_cache_get($cacheKey, $this->cacheGroup);

        if ($cached !== false) {
            $models = array_map(function ($item) {
                return static::hydrate($item);
            }, $cached ?: []);

            if (!empty($this->with)) {
                $this->eagerLoad($models, $this->with);
            }

            return new Collection($models);
        }

        // Table name is prepared as an identifier (%i); values are bound.
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, PluginCheck.Security.DirectDB.UnescapedDBParameter
        $results = $wpdb->get_results($wpdb->prepare($baseSql, $params), ARRAY_A);

        wp_cache_set($cacheKey, $results ?: [], $this->cacheGroup, $this->cacheTtl);

        $models = array_map(function ($item) {
            return static::hydrate($item);
        }, $results ?: []);

        if (!empty($this->with)) {
            $this->eagerLoad($models, $this->with);
        }

        return new Collection($models);
    }

    protected static function hydrate($attributes)
    {
        $model = new static();
        $model->attributes = $attributes;

        if (isset($model->casts)) {
            foreach ($model->casts as $key => $type) {
                if (array_key_exists($key, $attributes)) {
                    switch ($type) {
                        case 'json':
                            if (is_string($attributes[$key])) {
                                $model->attributes[$key] = json_decode($attributes[$key], true);
                            }
                            break;
                        case 'datetime':
                            if (is_string($attributes[$key])) {
                                $model->attributes[$key] = strtotime($attributes[$key]);
                            }
                            break;
                        case 'boolean':
                            $model->attributes[$key] = (bool) $attributes[$key];
                            break;
                    }
                }
            }
        }

        return $model;
    }

    public static function find($id)
    {
        global $wpdb;

        $instance = new static();
        $safeTable = esc_sql(static::table());

        $cacheKey = $instance->cacheKey('find:' . md5($safeTable . '|' . (int) $id));
        $cachedRow = wp_cache_get($cacheKey, $instance->cacheGroup);

        if ($cachedRow !== false) {
            return !empty($cachedRow) ? static::hydrate($cachedRow) : null;
        }

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
        $result = $wpdb->get_row(
            $wpdb->prepare("SELECT * FROM %i WHERE id = %d", static::table(), (int) $id),
            ARRAY_A
        );

        wp_cache_set($cacheKey, $result ?: [], $instance->cacheGroup, $instance->cacheTtl);

        if (!$result) {
            return null;
        }

        return static::hydrate($result);
    }

    public function save()
    {
        global $wpdb;
        $safeTable = esc_sql(static::table());

        $attributes = [];
        foreach ($this->fillable as $field) {
            if (array_key_exists($field, $this->attributes)) {
                $attributes[$field] = $this->attributes[$field];
            }
        }

        if (isset($this->attributes['id'])) {
            $attributes['id'] = $this->attributes['id'];
        }

        foreach ($this->casts as $key => $type) {
            if (isset($attributes[$key])) {
                switch ($type) {
                    case 'json':
                        if (is_array($attributes[$key]) || is_object($attributes[$key])) {
                            $attributes[$key] = wp_json_encode($attributes[$key]);
                        }
                        break;
                    case 'datetime':
                        if ($attributes[$key] instanceof \DateTime) {
                            $attributes[$key] = $attributes[$key]->format('Y-m-d H:i:s');
                        }
                        break;
                    case 'boolean':
                        $attributes[$key] = (int) (bool) $attributes[$key];
                        break;
                }
            }
        }

        if ($this->timestamps) {
            $now = current_time('mysql');

            if ($this->updatedAtColumn !== null) {
                $attributes[$this->updatedAtColumn] = $now;
            }

            if (!isset($attributes['id']) && $this->createdAtColumn !== null) {
                $attributes[$this->createdAtColumn] = $now;
            }
        }

        if (isset($attributes['id'])) {
            $id = (int) $attributes['id'];
            unset($attributes['id']);

            $format = array_fill(0, count($attributes), '%s');
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
            $updated = $wpdb->update(
                $safeTable,
                $attributes,
                ['id' => $id],
                $format,
                ['%d']
            );

            if ($updated !== false) {
                static::bumpCacheVersion();
                return true;
            }

            return false;
        }

        $format = array_fill(0, count($attributes), '%s');
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
        $inserted = $wpdb->insert($safeTable, $attributes, $format);
        if ($inserted) {
            $this->attributes['id'] = (int) $wpdb->insert_id;
            static::bumpCacheVersion();
            return true;
        }

        return false;
    }

    public function __get($key)
    {
        if (array_key_exists($key, $this->relations)) {
            return $this->relations[$key];
        }

        if (array_key_exists($key, $this->attributes)) {
            return $this->attributes[$key];
        }

        if ($this->isRelationMethod($key)) {
            $this->relations[$key] = $this->$key();
            return $this->relations[$key];
        }

        return null;
    }

    /**
     * empty() and isset() on magic properties consult __isset(), NOT __get().
     * Without this, empty($model->column) is always true regardless of the
     * value — a silent correctness trap for every caller.
     */
    public function __isset($key)
    {
        if (array_key_exists($key, $this->relations) || array_key_exists($key, $this->attributes)) {
            return true;
        }

        return $this->isRelationMethod($key);
    }

    public function __set($key, $value)
    {
        if (in_array($key, $this->fillable, true)) {
            $this->attributes[$key] = $value;
        }
    }

    public static function create(array $attributes)
    {
        $model = new static();

        foreach ($attributes as $key => $value) {
            if (in_array($key, $model->fillable, true)) {
                $model->attributes[$key] = $value;
            }
        }

        $model->save();
        return $model;
    }

    public static function firstOrCreate(array $search, array $additional = [])
    {
        $query = new static();
        foreach ($search as $column => $value) {
            $query = $query->andWhere($column, '=', $value);
        }

        $existing = $query->first();
        if ($existing) {
            return $existing;
        }

        return static::create(array_merge($search, $additional));
    }

    public static function updateOrCreate(array $search, array $attributes = [])
    {
        $query = new static();
        foreach ($search as $column => $value) {
            $query = $query->andWhere($column, '=', $value);
        }

        $existing = $query->first();
        if ($existing) {
            $id = $existing->attributes['id'] ?? null;
            if ($id) {
                static::update($id, $attributes);
                return static::find($id);
            }
        }

        return static::create(array_merge($search, $attributes));
    }

    public function delete()
    {
        global $wpdb;

        if (!isset($this->attributes[$this->primaryKey])) {
            return false;
        }

        $id = (int) $this->attributes[$this->primaryKey];
        $safeTable = esc_sql(static::table());

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $result = $wpdb->delete(
            $safeTable,
            [$this->primaryKey => $id],
            ['%d']
        );

        if ($result !== false) {
            static::bumpCacheVersion();
        }

        return $result !== false;
    }

    public static function findByIdAndDelete($id)
    {
        global $wpdb;
        $safeTable = esc_sql(static::table());

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $result = $wpdb->delete(
            $safeTable,
            ['id' => (int) $id],
            ['%d']
        );

        if ($result !== false) {
            static::bumpCacheVersion();
        }

        return $result !== false;
    }

    public static function update($id, array $attributes)
    {
        $model = static::find($id);
        if (!$model) {
            return false;
        }

        foreach ($attributes as $key => $value) {
            if (in_array($key, $model->fillable, true)) {
                $model->attributes[$key] = $value;
            }
        }

        $saved = $model->save();
        if ($saved) {
            static::bumpCacheVersion();
        }

        return $saved;
    }

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

    public function count()
    {
        global $wpdb;

        $params = [];
        $whereSql = '';
        if (!empty($this->query['where'])) {
            [$whereSql, $whereParams] = $this->buildWhereClause();
            $params = $whereParams;
        }

        $countSql = "SELECT COUNT(*) FROM %i" . $whereSql;
        $countParams = array_merge([static::table()], $params);

        // Table name is prepared as an identifier (%i); values are bound.
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, PluginCheck.Security.DirectDB.UnescapedDBParameter
        return (int) $wpdb->get_var($wpdb->prepare($countSql, $countParams));
    }

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

    private function buildWhereClause(): array
    {
        $sqlParts = [];
        $booleans = [];
        $params   = [];

        foreach ($this->query['where'] as $where) {
            $column   = esc_sql(sanitize_key((string) ($where['column'] ?? '')));
            $operator = strtoupper((string) ($where['operator'] ?? '='));
            $value    = $where['value'] ?? null;
            $boolean  = strtoupper((string) ($where['boolean'] ?? 'AND'));

            if ($column === '') {
                continue;
            }

            if (!in_array($boolean, ['AND', 'OR'], true)) {
                $boolean = 'AND';
            }

            $booleans[] = $boolean;

            // Column-to-column comparison (whereColumn): both sides are
            // identifiers, nothing is bound. Operator was validated on the way
            // in; re-guard here so a hand-built where entry can't inject.
            if (isset($where['rightColumn'])) {
                $right = esc_sql(sanitize_key((string) $where['rightColumn']));
                $op    = in_array($operator, ['=', '!=', '>', '<', '>=', '<='], true) ? $operator : '=';

                if ($right !== '') {
                    $sqlParts[] = "`{$column}` {$op} `{$right}`";
                } else {
                    // Nothing to compare against — drop the clause but keep the
                    // boolean list aligned with sqlParts.
                    array_pop($booleans);
                }

                continue;
            }

            if ($operator === 'IS NULL') {
                $sqlParts[] = "`{$column}` IS NULL";
                continue;
            }

            if ($operator === 'IS NOT NULL') {
                $sqlParts[] = "`{$column}` IS NOT NULL";
                continue;
            }

            if ($operator === 'BETWEEN') {
                $betweenValues = is_array($value) ? $value : [];
                $sqlParts[] = "`{$column}` BETWEEN %s AND %s";
                $params[] = (string) ($betweenValues[0] ?? '');
                $params[] = (string) ($betweenValues[1] ?? '');
                continue;
            }

            if ($operator === 'IN') {
                $values = is_array($value) ? $value : [$value];
                $values = array_values(array_filter($values, function ($v) {
                    return $v !== null && $v !== '';
                }));

                if (empty($values)) {
                    $sqlParts[] = '1=0';
                    continue;
                }

                $placeholders = implode(', ', array_fill(0, count($values), '%s'));
                $sqlParts[] = "`{$column}` IN ({$placeholders})";

                foreach ($values as $v) {
                    $params[] = (string) $v;
                }

                continue;
            }

            $sqlParts[] = "`{$column}` {$operator} %s";
            $params[]   = (string) $value;
        }

        if (empty($sqlParts)) {
            return ['', []];
        }

        $whereSql = ' WHERE ';
        foreach ($sqlParts as $i => $part) {
            if ($i > 0) {
                $whereSql .= ' ' . $booleans[$i] . ' ';
            }
            $whereSql .= $part;
        }

        return [$whereSql, $params];
    }

    private function normalizeSelect($columns): string
    {
        if (is_string($columns)) {
            $columns = trim($columns);
            return ($columns === '*') ? '*' : '`' . esc_sql(sanitize_key($columns)) . '`';
        }

        if (!is_array($columns) || empty($columns)) {
            return '*';
        }

        if (count($columns) === 1 && $columns[0] === '*') {
            return '*';
        }

        $safe = [];
        foreach ($columns as $col) {
            $col = (string) $col;
            $key = esc_sql(sanitize_key($col));
            if ($key !== '') {
                $safe[] = "`{$key}`";
            }
        }

        return !empty($safe) ? implode(', ', $safe) : '*';
    }
}
