<?php
/**
 * Laravel-like Fluent Query Builder
 *
 * @package ZenoChatbot\Framework\Database
 */

namespace ZenoChatbot\Framework\Database;

use ZenoChatbot\Framework\Support\Collection;
use ZenoChatbot\Framework\Database\ORM\Grammar\GrammarFactory;

// Prevent direct access
if (!defined('ABSPATH')) {
    exit;
}

/**
 * Fluent Query Builder for WordPress Database
 */
class QueryBuilder
{
    /**
     * Database connection
     *
     * @var \wpdb
     */
    protected $db;

    /**
     * Grammar instance
     *
     * @var \ZenoChatbot\Framework\Database\ORM\Grammar\Grammar
     */
    protected $grammar;

    /**
     * The table name
     *
     * @var string
     */
    protected $table;

    /**
     * The SELECT columns
     *
     * @var array
     */
    protected $columns = ['*'];

    /**
     * Whether to use DISTINCT
     *
     * @var bool
     */
    protected $distinct = false;

    /**
     * The WHERE clauses
     *
     * @var array
     */
    protected $wheres = [];

    /**
     * The ORDER BY clauses
     *
     * @var array
     */
    protected $orders = [];

    /**
     * The LIMIT clause
     *
     * @var int|null
     */
    protected $limit;

    /**
     * The OFFSET clause
     *
     * @var int|null
     */
    protected $offset;

    /**
     * The JOIN clauses
     *
     * @var array
     */
    protected $joins = [];

    /**
     * The GROUP BY clauses
     *
     * @var array
     */
    protected $groups = [];

    /**
     * The HAVING clauses
     *
     * @var array
     */
    protected $havings = [];

    /**
     * The relationships to eager load
     *
     * @var array
     */
    protected $with = [];

    /**
     * The relationships to count
     *
     * @var array
     */
    protected $withCount = [];

    /**
     * The model instance for relationship loading
     *
     * @var Model|null
     */
    protected $model;

    /**
     * Create a new query builder instance
     *
     * @param string $table
     */
    public function __construct($table = null)
    {
        global $wpdb;
        $this->db = $wpdb;

        // Initialize grammar based on database type
        $connection = new ORM\Connection();
        $this->grammar = GrammarFactory::make($connection);

        if ($table) {
            $this->table = $this->db->prefix . $table;
        }
    }

    /**
     * Set the table name
     *
     * @param string $table
     * @return $this
     */
    public function table($table)
    {
        $this->table = $this->db->prefix . $table;
        return $this;
    }

    /**
     * Set the model instance for relationship loading
     *
     * @param Model $model
     * @return $this
     */
    public function setModel($model)
    {
        $this->model = $model;
        return $this;
    }

    /**
     * Eager load relationships
     *
     * @param array|string $relations
     * @return $this
     */
    public function with($relations)
    {
        if (is_string($relations)) {
            $relations = func_get_args();
        }

        // Handle both simple strings and associative arrays with closures
        foreach ($relations as $key => $value) {
            if (is_numeric($key)) {
                // Simple relationship name: ['customer', 'messages']
                $this->with[] = $value;
            } else {
                // Relationship with closure: ['messages' => function($query) {...}]
                $this->with[$key] = $value;
            }
        }

        return $this;
    }

    /**
     * Add relationship counts to the query
     *
     * @param array|string $relations
     * @return $this
     */
    public function withCount($relations)
    {
        if (is_string($relations)) {
            $relations = func_get_args();
        }

        $this->withCount = array_merge($this->withCount, $relations);
        return $this;
    }

    /**
     * Set the columns to select
     *
     * @param array|string $columns
     * @return $this
     */
    public function select($columns = ['*'])
    {
        $this->columns = is_array($columns) ? $columns : func_get_args();
        return $this;
    }

    /**
     * Set the query to use DISTINCT
     *
     * @return $this
     */
    public function distinct()
    {
        $this->distinct = true;
        return $this;
    }

    /**
     * Add a WHERE clause
     *
     * @param string|array|\Closure $column
     * @param string $operator
     * @param mixed $value
     * @param string $boolean
     * @return $this
     */
    public function where($column, $operator = null, $value = null, $boolean = 'AND')
    {
        // Handle closure (nested where conditions)
        if ($column instanceof \Closure) {
            return $this->whereNested($column, $boolean);
        }

        // Handle array of conditions
        if (is_array($column)) {
            foreach ($column as $key => $val) {
                $this->where($key, '=', $val, $boolean);
            }
            return $this;
        }

        // Handle two parameters (column, value)
        if (func_num_args() === 2) {
            $value = $operator;
            $operator = '=';
        }

        $this->wheres[] = [
            'type' => 'basic',
            'column' => $column,
            'operator' => $operator,
            'value' => $value,
            'boolean' => $boolean
        ];

        return $this;
    }

    /**
     * Add a nested WHERE clause
     *
     * @param \Closure $callback
     * @param string $boolean
     * @return $this
     */
    protected function whereNested(\Closure $callback, $boolean = 'AND')
    {
        $query = new static($this->table);
        $query->setModel($this->model);

        call_user_func($callback, $query);

        if (count($query->wheres) > 0) {
            $this->wheres[] = [
                'type' => 'nested',
                'query' => $query,
                'boolean' => $boolean
            ];
        }

        return $this;
    }

    /**
     * Add an OR WHERE clause
     *
     * @param string $column
     * @param string $operator
     * @param mixed $value
     * @return $this
     */
    public function orWhere($column, $operator = null, $value = null)
    {
        return $this->where($column, $operator, $value, 'OR');
    }

    /**
     * Add a WHERE IN clause
     *
     * @param string $column
     * @param array $values
     * @param string $boolean
     * @return $this
     */
    public function whereIn($column, array $values, $boolean = 'AND')
    {
        $this->wheres[] = [
            'type' => 'in',
            'column' => $column,
            'values' => $values,
            'boolean' => $boolean
        ];

        return $this;
    }

    /**
     * Add a WHERE NOT IN clause
     *
     * @param string $column
     * @param array $values
     * @param string $boolean
     * @return $this
     */
    public function whereNotIn($column, array $values, $boolean = 'AND')
    {
        $this->wheres[] = [
            'type' => 'not_in',
            'column' => $column,
            'values' => $values,
            'boolean' => $boolean
        ];

        return $this;
    }

    /**
     * Add a WHERE NULL clause
     *
     * @param string $column
     * @param string $boolean
     * @return $this
     */
    public function whereNull($column, $boolean = 'AND')
    {
        $this->wheres[] = [
            'type' => 'null',
            'column' => $column,
            'boolean' => $boolean
        ];

        return $this;
    }

    /**
     * Add a WHERE NOT NULL clause
     *
     * @param string $column
     * @param string $boolean
     * @return $this
     */
    public function whereNotNull($column, $boolean = 'AND')
    {
        $this->wheres[] = [
            'type' => 'not_null',
            'column' => $column,
            'boolean' => $boolean
        ];

        return $this;
    }

    /**
     * Add a WHERE clause that checks for the existence of a relationship
     *
     * @param string $relation
     * @param \Closure|null $callback
     * @param string $operator
     * @param int $count
     * @param string $boolean
     * @return $this
     */
    public function whereHas($relation, ?\Closure $callback = null, $operator = '>=', $count = 1, $boolean = 'AND')
    {
        if (!$this->model) {
            throw new \InvalidArgumentException('Model must be set to use whereHas');
        }

        // Get the relationship method
        if (!method_exists($this->model, $relation)) {
            throw new \InvalidArgumentException("Relationship '{$relation}' does not exist on model");
        }

        // Get relationship details
        $relationshipQuery = $this->model->$relation();
        $relatedModel = $relationshipQuery->getModel();
        $foreignKey = $relationshipQuery->getForeignKeyName();
        $localKey = $relationshipQuery->getLocalKey();
        $relatedTable = $relatedModel->getTable();

        // Build the exists subquery
        $subQuery = new static();
        $subQuery->setModel($relatedModel);
        $subQuery->table($relatedTable);
        $subQuery->select(['1']);
        $subQuery->whereColumn($foreignKey, $this->table . '.' . $localKey);

        // Apply callback constraints if provided
        if ($callback) {
            call_user_func($callback, $subQuery);
        }

        // Add the exists condition
        $this->wheres[] = [
            'type' => 'exists',
            'query' => $subQuery,
            'boolean' => $boolean,
            'operator' => $operator,
            'count' => $count
        ];

        return $this;
    }

    /**
     * Add a WHERE clause that checks for the non-existence of a relationship
     *
     * @param string $relation
     * @param \Closure|null $callback
     * @return $this
     */
    public function whereDoesntHave($relation, ?\Closure $callback = null)
    {
        return $this->whereHas($relation, $callback, '<', 1);
    }

    /**
     * Add a WHERE clause comparing two columns
     *
     * @param string $first
     * @param string $operator
     * @param string $second
     * @param string $boolean
     * @return $this
     */
    public function whereColumn($first, $operator = null, $second = null, $boolean = 'AND')
    {
        if (func_num_args() === 2) {
            $second = $operator;
            $operator = '=';
        }

        $this->wheres[] = [
            'type' => 'column',
            'first' => $first,
            'operator' => $operator,
            'second' => $second,
            'boolean' => $boolean
        ];

        return $this;
    }

    /**
     * Add an ORDER BY clause
     *
     * @param string $column
     * @param string $direction
     * @return $this
     */
    public function orderBy($column, $direction = 'ASC')
    {
        $this->orders[] = [
            'column' => $column,
            'direction' => strtoupper($direction)
        ];

        return $this;
    }

    /**
     * Add an ORDER BY DESC clause
     *
     * @param string $column
     * @return $this
     */
    public function orderByDesc($column)
    {
        return $this->orderBy($column, 'DESC');
    }

    /**
     * Order by created_at descending (latest first)
     *
     * @param string $column
     * @return $this
     */
    public function latest($column = 'created_at')
    {
        return $this->orderBy($column, 'DESC');
    }

    /**
     * Order by created_at ascending (oldest first)
     *
     * @param string $column
     * @return $this
     */
    public function oldest($column = 'created_at')
    {
        return $this->orderBy($column, 'ASC');
    }

    /**
     * Set the LIMIT clause
     *
     * @param int $limit
     * @return $this
     */
    public function limit($limit)
    {
        $this->limit = $limit;
        return $this;
    }

    /**
     * Set the OFFSET clause
     *
     * @param int $offset
     * @return $this
     */
    public function offset($offset)
    {
        $this->offset = $offset;
        return $this;
    }



    /**
     * Add a JOIN clause
     *
     * @param string $table
     * @param string $first
     * @param string $operator
     * @param string $second
     * @param string $type
     * @return $this
     */
    public function join($table, $first, $operator = null, $second = null, $type = 'INNER')
    {
        if (func_num_args() === 3) {
            $second = $operator;
            $operator = '=';
        }

        $this->joins[] = [
            'type' => $type,
            'table' => $this->db->prefix . $table,
            'first' => $first,
            'operator' => $operator,
            'second' => $second
        ];

        return $this;
    }

    /**
     * Add a LEFT JOIN clause
     *
     * @param string $table
     * @param string $first
     * @param string $operator
     * @param string $second
     * @return $this
     */
    public function leftJoin($table, $first, $operator = null, $second = null)
    {
        return $this->join($table, $first, $operator, $second, 'LEFT');
    }

    /**
     * Add a RIGHT JOIN clause
     *
     * @param string $table
     * @param string $first
     * @param string $operator
     * @param string $second
     * @return $this
     */
    public function rightJoin($table, $first, $operator = null, $second = null)
    {
        return $this->join($table, $first, $operator, $second, 'RIGHT');
    }

    /**
     * Add a GROUP BY clause
     *
     * @param string $column
     * @return $this
     */
    public function groupBy($column)
    {
        $this->groups[] = $column;
        return $this;
    }

    /**
     * Add a HAVING clause
     *
     * @param string $column
     * @param string $operator
     * @param mixed $value
     * @return $this
     */
    public function having($column, $operator = null, $value = null)
    {
        if (func_num_args() === 2) {
            $value = $operator;
            $operator = '=';
        }

        $this->havings[] = [
            'column' => $column,
            'operator' => $operator,
            'value' => $value
        ];

        return $this;
    }

    /**
     * Execute the query and get all results
     *
     * @return Collection
     */
    public function get()
    {
        $sql = $this->toSql();
        $results = $this->db->get_results($sql, ARRAY_A);
        $collection = new Collection($results ?: []);

        // Load relationships if specified
        if (!empty($this->with) && $this->model && !$collection->isEmpty()) {
            $collection = $this->loadRelationships($collection);
        }

        // Load relationship counts if specified
        if (!empty($this->withCount) && $this->model && !$collection->isEmpty()) {
            $collection = $this->loadRelationshipCounts($collection);
        }

        // Convert to model instances if we have a model and items are arrays
        if ($this->model && !$collection->isEmpty()) {
            $collection = $collection->map(function($item) {
                // Only convert if it's an array (not already a Model instance)
                if (is_array($item)) {
                    return $this->model->newFromBuilder($item);
                }
                return $item;
            });
        }

        return $collection;
    }

    /**
     * Load relationships for the collection
     *
     * @param Collection $collection
     * @return Collection
     */
    protected function loadRelationships(Collection $collection)
    {
        foreach ($this->with as $key => $value) {
            if (is_numeric($key)) {
                // Simple relationship: 'customer'
                $collection = $this->loadRelationship($collection, $value);
            } else {
                // Relationship with closure: 'messages' => function($query) {...}
                $collection = $this->loadRelationship($collection, $key, $value);
            }
        }

        return $collection;
    }

    /**
     * Load a specific relationship for the collection
     *
     * @param Collection $collection
     * @param string $relation
     * @param callable|null $closure
     * @return Collection
     */
    protected function loadRelationship(Collection $collection, $relation, $closure = null)
    {
        if (!method_exists($this->model, $relation)) {
            return $collection;
        }

        // Get the relationship query
        $relationshipQuery = $this->model->$relation();
        $relatedModel = $relationshipQuery->getModel();
        $foreignKey = $relationshipQuery->getForeignKeyName();

        // For belongsTo relationships, we need to handle the keys differently
        if ($relationshipQuery instanceof \ZenoChatbot\Framework\Database\Relations\BelongsTo) {
            $ownerKey = $relationshipQuery->getOwnerKeyName();

            // In belongsTo: local table has foreign key, related table has primary key
            // Get foreign key values from the collection
            $foreignKeys = $collection->pluck($foreignKey)->unique()->filter()->toArray();

            if (empty($foreignKeys)) {
                return $collection;
            }

            // Query related models by their primary key
            $relatedQuery = $relatedModel::whereIn($ownerKey, $foreignKeys);

            // Apply closure if provided
            if ($closure && is_callable($closure)) {
                $closure($relatedQuery);
            }

            $relatedResults = $relatedQuery->get();

            // Group related results by their primary key
            $relatedByKey = $relatedResults->groupBy($ownerKey);

            // Attach relationships using foreign key from local items
            return $collection->map(function($item) use ($relation, $foreignKey, $relatedByKey) {
                $foreignKeyValue = $item[$foreignKey] ?? null;
                $related = $relatedByKey->get($foreignKeyValue, new Collection());
                $item[$relation] = $related->first();
                return $item;
            });
        } else {
            // For hasOne/hasMany: local table has primary key, related table has foreign key
            $localKey = $relationshipQuery->getLocalKeyName();

            // Get local key values from the collection
            $localKeys = $collection->pluck($localKey)->unique()->filter()->toArray();

            if (empty($localKeys)) {
                return $collection;
            }

            // Query the related models
            $relatedQuery = $relatedModel::whereIn($foreignKey, $localKeys);

            // Check if closure contains limit - if so, we need to handle it per conversation
            $hasLimit = false;
            if ($closure && is_callable($closure)) {
                // Create a test query to check if limit is applied
                $testQuery = $relatedModel::query();
                $closure($testQuery);
                $hasLimit = !empty($testQuery->limit);
            }

            if ($hasLimit && $closure) {
                // Handle per-conversation limiting
                $relatedByKey = new Collection();
                foreach ($localKeys as $localKeyValue) {
                    $perConversationQuery = $relatedModel::where($foreignKey, $localKeyValue);
                    $closure($perConversationQuery);
                    $results = $perConversationQuery->get();
                    $relatedByKey->put($localKeyValue, $results);
                }
            } else {
                // Normal relationship loading
                if ($closure && is_callable($closure)) {
                    $closure($relatedQuery);
                }
                $relatedResults = $relatedQuery->get();
                $relatedByKey = $relatedResults->groupBy($foreignKey);
            }
        }

        // Attach relationships to each item in the collection (for hasOne/hasMany)
        return $collection->map(function($item) use ($relation, $localKey, $relatedByKey, $relationshipQuery) {
            $localKeyValue = $item[$localKey] ?? null;
            $related = $relatedByKey->get($localKeyValue, new Collection());

            // Determine if it's a single or multiple relationship
            if ($relationshipQuery instanceof \ZenoChatbot\Framework\Database\Relations\HasOne) {
                $item[$relation] = $related->first();
            } else {
                $item[$relation] = $related;
            }

            return $item;
        });
    }

    /**
     * Load relationship counts for the collection
     *
     * @param Collection $collection
     * @return Collection
     */
    protected function loadRelationshipCounts(Collection $collection)
    {
        foreach ($this->withCount as $relation) {
            $collection = $this->loadRelationshipCount($collection, $relation);
        }

        return $collection;
    }

    /**
     * Load a specific relationship count for the collection
     *
     * @param Collection $collection
     * @param string $relation
     * @return Collection
     */
    protected function loadRelationshipCount(Collection $collection, $relation)
    {
        if (!method_exists($this->model, $relation)) {
            return $collection;
        }

        // Get the relationship instance
        $relationshipQuery = $this->model->$relation();

        if (!$relationshipQuery instanceof \ZenoChatbot\Framework\Database\Relations\Relation) {
            return $collection;
        }

        // Get the related model and table
        $relatedModel = $relationshipQuery->getRelated();
        $relatedTable = $relatedModel->getTable();

        // Handle different relationship types
        if ($relationshipQuery instanceof \ZenoChatbot\Framework\Database\Relations\HasMany) {
            // For HasMany: parent key -> foreign key on related table
            $localKey = $relationshipQuery->getLocalKeyName();
            $foreignKey = $relationshipQuery->getForeignKeyName();

            // Get all local key values
            $localKeyValues = $collection->pluck($localKey)->unique()->filter()->toArray();

            if (empty($localKeyValues)) {
                return $collection;
            }

            // Build count query
            $countQuery = new static();
            $countQuery->table($relatedTable);
            $countQuery->whereIn($foreignKey, $localKeyValues);
            $countQuery->select([$foreignKey, 'COUNT(*) as count']);
            $countQuery->groupBy($foreignKey);

            // Execute count query
            $countResults = $countQuery->get();
            $countsByKey = $countResults->keyBy($foreignKey);

            // Add counts to collection items
            return $collection->map(function($item) use ($relation, $localKey, $countsByKey) {
                $localKeyValue = $item[$localKey] ?? null;
                $count = $countsByKey->get($localKeyValue)['count'] ?? 0;
                $item[$relation . '_count'] = (int) $count;
                return $item;
            });

        } elseif ($relationshipQuery instanceof \ZenoChatbot\Framework\Database\Relations\BelongsTo) {
            // For BelongsTo: foreign key -> owner key on related table
            // This doesn't make much sense for counting, but we'll support it
            $foreignKey = $relationshipQuery->getForeignKeyName();
            $ownerKey = $relationshipQuery->getOwnerKeyName();

            // Get all foreign key values
            $foreignKeyValues = $collection->pluck($foreignKey)->unique()->filter()->toArray();

            if (empty($foreignKeyValues)) {
                return $collection;
            }

            // For BelongsTo, count is always 1 or 0 (exists or not)
            $countQuery = new static();
            $countQuery->table($relatedTable);
            $countQuery->whereIn($ownerKey, $foreignKeyValues);
            $countQuery->select([$ownerKey, 'COUNT(*) as count']);
            $countQuery->groupBy($ownerKey);

            $countResults = $countQuery->get();
            $countsByKey = $countResults->keyBy($ownerKey);

            return $collection->map(function($item) use ($relation, $foreignKey, $countsByKey) {
                $foreignKeyValue = $item[$foreignKey] ?? null;
                $count = $countsByKey->get($foreignKeyValue)['count'] ?? 0;
                $item[$relation . '_count'] = (int) $count;
                return $item;
            });
        }

        // For other relationship types, just return the collection unchanged
        return $collection;
    }

    /**
     * Get the first result
     *
     * @return array|Model|null
     */
    public function first()
    {
        $results = $this->limit(1)->get();
        return $results->isEmpty() ? null : $results->first();
    }

    /**
     * Find a record by ID
     *
     * @param mixed $id
     * @param string $column
     * @return array|Model|null
     */
    public function find($id, $column = 'id')
    {
        $results = $this->where($column, $id)->limit(1)->get();
        return $results->isEmpty() ? null : $results->first();
    }

    /**
     * Find a record by ID or throw an exception
     *
     * @param mixed $id
     * @param string $column
     * @return array|Model
     * @throws \Exception
     */
    public function findOrFail($id, $column = 'id')
    {
        $result = $this->find($id, $column);

        if ($result === null) {
            throw new \Exception("No query results for model with {$column} = {$id}");
        }

        return $result;
    }

    /**
     * Get the count of records
     *
     * @return int
     */
    public function count()
    {
        // Clone the query to avoid modifying the original
        $countQuery = clone $this;

        // Reset selects and orders for count query
        $countQuery->columns = ['COUNT(*)'];
        $countQuery->orders = [];
        $countQuery->limit = null;
        $countQuery->offset = null;

        $sql = $countQuery->toSql();
        $result = $this->db->get_var($sql);

        return (int) $result;
    }

    /**
     * Check if any records exist
     *
     * @return bool
     */
    public function exists()
    {
        return $this->count() > 0;
    }

    /**
     * Insert a new record using grammar
     *
     * @param array $data
     * @return int|false Insert ID or false on failure
     */
    public function insert(array $data)
    {
        $table = str_replace($this->db->prefix, '', $this->table);
        $sql = $this->grammar->compileInsert($table, $data);

        $result = $this->db->query($sql);

        if ($result === false) {
            return false;
        }

        return $this->db->insert_id;
    }

    /**
     * Insert multiple records
     *
     * @param array $data
     * @return bool
     */
    public function insertBatch(array $data)
    {
        if (empty($data)) {
            return false;
        }

        $columns = array_keys($data[0]);
        $placeholders = '(' . implode(',', array_fill(0, count($columns), '%s')) . ')';
        $values = [];

        foreach ($data as $row) {
            foreach ($columns as $column) {
                $values[] = $row[$column] ?? null;
            }
        }

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

        return $this->db->query($this->db->prepare($sql, $values)) !== false;
    }

    /**
     * Update records using grammar
     *
     * @param array $data
     * @return int|false Number of affected rows or false on failure
     */
    public function update(array $data)
    {
        if (empty($this->wheres)) {
            throw new \InvalidArgumentException('Cannot update without WHERE clause');
        }

        $table = str_replace($this->db->prefix, '', $this->table);
        $wheres = $this->buildWhereComponents();
        $sql = $this->grammar->compileUpdate($table, $data, $wheres);

        return $this->db->query($sql);
    }

    /**
     * Delete records using grammar
     *
     * @return int|false Number of affected rows or false on failure
     */
    public function delete()
    {
        if (empty($this->wheres)) {
            throw new \InvalidArgumentException('Cannot delete without WHERE clause');
        }

        $table = str_replace($this->db->prefix, '', $this->table);
        $wheres = $this->buildWhereComponents();
        $sql = $this->grammar->compileDelete($table, $wheres);

        return $this->db->query($sql);
    }

    /**
     * Build the SQL query using grammar
     *
     * @return string
     */
    public function toSql()
    {
        $components = $this->buildComponents();
        return $this->grammar->compileSelect($components);
    }

    /**
     * Build query components for grammar
     *
     * @return array
     */
    protected function buildComponents()
    {
        $components = [];

        // Table
        if ($this->table) {
            // Remove prefix for grammar (it will add it back)
            $table = str_replace($this->db->prefix, '', $this->table);
            $components['from'] = $table;
        }

        // Columns
        $components['columns'] = $this->columns;

        // Distinct
        if ($this->distinct) {
            $components['distinct'] = true;
        }

        // Joins
        if (!empty($this->joins)) {
            $components['joins'] = $this->buildJoinComponents();
        }

        // Where clauses
        if (!empty($this->wheres)) {
            $components['wheres'] = $this->buildWhereComponents();
        }

        // Group by
        if (!empty($this->groups)) {
            $components['groups'] = $this->groups;
        }

        // Having
        if (!empty($this->havings)) {
            $components['havings'] = $this->buildHavingComponents();
        }

        // Order by
        if (!empty($this->orders)) {
            $components['orders'] = $this->orders;
        }

        // Limit
        if ($this->limit !== null) {
            $components['limit'] = $this->limit;
        }

        // Offset
        if ($this->offset !== null) {
            $components['offset'] = $this->offset;
        }

        return $components;
    }

    /**
     * Build join components for grammar
     *
     * @return array
     */
    protected function buildJoinComponents()
    {
        $joins = [];

        foreach ($this->joins as $join) {
            $joins[] = [
                'type' => strtoupper($join['type']),
                'table' => str_replace($this->db->prefix, '', $join['table']),
                'clauses' => [
                    [
                        'type' => 'basic',
                        'column' => $join['first'],
                        'operator' => $join['operator'],
                        'value' => $join['second'],
                        'boolean' => 'AND'
                    ]
                ]
            ];
        }

        return $joins;
    }

    /**
     * Build where components for grammar
     *
     * @return array
     */
    protected function buildWhereComponents()
    {
        return $this->wheres;
    }

    /**
     * Build having components for grammar
     *
     * @return array
     */
    protected function buildHavingComponents()
    {
        $havings = [];

        foreach ($this->havings as $having) {
            $havings[] = [
                'type' => 'basic',
                'column' => $having['column'],
                'operator' => $having['operator'],
                'value' => $having['value'],
                'boolean' => 'AND'
            ];
        }

        return $havings;
    }

    public function sum($column)
    {
        $sumQuery = clone $this;

        // Reset selects/orders/limits for aggregation
        $sumQuery->columns = ["SUM({$column}) as aggregate"];
        $sumQuery->orders = [];
        $sumQuery->limit = null;
        $sumQuery->offset = null;

        // If grouping exists, wrap grouped query and sum the aggregates
        if (!empty($sumQuery->groups)) {
            // The inner query will return grouped aggregates; wrap and sum them
            $innerSql = $sumQuery->toSql();
            $sql = "SELECT SUM(aggregate) as aggregate FROM ({$innerSql}) as aggregate_table";
            $result = $this->db->get_var($sql);
        } else {
            $sql = $sumQuery->toSql();
            $result = $this->db->get_var($sql);
        }

        if ($result === null) {
            return 0;
        }

        // Return int when possible, otherwise float
        if (is_numeric($result) && (string) (int) $result === (string) $result) {
            return (int) $result;
        }

        return (float) $result;
    }

    /**
     * Build WHERE clause
     *
     * @param array &$values
     * @return string
     */
    protected function buildWhereClause(&$values)
    {
        if (empty($this->wheres)) {
            return '';
        }

        $whereClauses = [];
        $isFirst = true;

        foreach ($this->wheres as $where) {
            $boolean = $isFirst ? '' : " {$where['boolean']} ";
            $isFirst = false;

            switch ($where['type']) {
                case 'basic':
                    $whereClauses[] = $boolean . "{$where['column']} {$where['operator']} %s";
                    $values[] = $where['value'];
                    break;

                case 'in':
                    $placeholders = implode(',', array_fill(0, count($where['values']), '%s'));
                    $whereClauses[] = $boolean . "{$where['column']} IN ({$placeholders})";
                    $values = array_merge($values, $where['values']);
                    break;

                case 'not_in':
                    $placeholders = implode(',', array_fill(0, count($where['values']), '%s'));
                    $whereClauses[] = $boolean . "{$where['column']} NOT IN ({$placeholders})";
                    $values = array_merge($values, $where['values']);
                    break;

                case 'null':
                    $whereClauses[] = $boolean . "{$where['column']} IS NULL";
                    break;

                case 'not_null':
                    $whereClauses[] = $boolean . "{$where['column']} IS NOT NULL";
                    break;
            }
        }

        return ' WHERE ' . implode('', $whereClauses);
    }

    /**
     * Clone the query builder
     *
     * @return static
     */
    public function clone()
    {
        return clone $this;
    }

    /**
     * Paginate the given query
     *
     * @param int|null $perPage
     * @param int|null $page
     * @param array $options
     * @return LengthAwarePaginator
     */
    public function paginate($perPage = null, $page = null, array $options = [])
    {
        // Simple pagination - check request for per_page and page
        $perPage = $perPage ?: $this->getPerPageFromRequest();
        $page = $page ?: $this->getPageFromRequest();

        // Get total count
        $total = $this->getCountForPagination();

        // Get the items for the current page
        $results = $this->forPage($page, $perPage)->get();

        return new LengthAwarePaginator(
            $results,
            $total,
            $perPage,
            $page,
            array_merge($options, [
                'path' => $this->getCurrentPath(),
            ])
        );
    }

    /**
     * Get per_page from request
     *
     * @return int
     */
    protected function getPerPageFromRequest(): int
    {
        $perPage = $_GET['per_page'] ?? 15;
        return max(1, min(100, (int) $perPage)); // Min 1, max 100
    }

    /**
     * Get page from request
     *
     * @return int
     */
    protected function getPageFromRequest(): int
    {
        $page = $_GET['page'] ?? 1;
        return max(1, (int) $page);
    }

    /**
     * Get current path
     *
     * @return string
     */
    protected function getCurrentPath(): string
    {
        return strtok($_SERVER['REQUEST_URI'] ?? '/', '?');
    }



    /**
     * Set the limit and offset for a given page
     *
     * @param int $page
     * @param int $perPage
     * @return $this
     */
    public function forPage($page, $perPage = 15)
    {
        return $this->offset(($page - 1) * $perPage)->limit($perPage);
    }

    /**
     * Get the count of the total records for pagination
     *
     * @return int
     */
    protected function getCountForPagination()
    {
        // Clone the query to avoid modifying the original
        $query = clone $this;

        // Remove ordering, limit, and offset for count query
        $query->orders = [];
        $query->limit = null;
        $query->offset = null;

        // If we have group by, we need to count the groups
        if (!empty($query->groups)) {
            return $this->getCountForPaginationWithGroups($query);
        }

        // Simple count
        $query->columns = ['COUNT(*) as aggregate'];
        $result = $this->db->get_var($query->toSql());

        return (int) $result;
    }

    /**
     * Get the count for pagination when using GROUP BY
     *
     * @param QueryBuilder $query
     * @return int
     */
    protected function getCountForPaginationWithGroups(QueryBuilder $query)
    {
        // Wrap the grouped query in a subquery and count the results
        $sql = "SELECT COUNT(*) as aggregate FROM ({$query->toSql()}) as aggregate_table";
        $result = $this->db->get_var($sql);

        return (int) $result;
    }

    /**
     * Get paginated results with custom per page
     *
     * @param int $perPage
     * @param int|null $page
     * @return LengthAwarePaginator
     */
    public function paginateCustom($perPage = 15, $page = null)
    {
        return $this->paginate($perPage, $page);
    }

    /**
     * Get the where clauses
     *
     * @return array
     */
    public function getWheres()
    {
        return $this->wheres;
    }

    /**
     * Get the table name
     *
     * @return string
     */
    public function getTable()
    {
        return $this->table;
    }

    /**
     * Handle dynamic method calls for scopes
     *
     * @param string $method
     * @param array $parameters
     * @return mixed
     */
    public function __call($method, $parameters)
    {
        // Check if this is a scope method call
        if ($this->model && method_exists($this->model, 'scope' . ucfirst($method))) {
            $scopeMethod = 'scope' . ucfirst($method);
            return $this->model->$scopeMethod($this, ...$parameters);
        }

        throw new \BadMethodCallException("Method {$method} does not exist on QueryBuilder");
    }


}
