<?php

/**
 * Query Builder Class
 *
 * Provides a fluent interface for building SQL queries with proper
 * parameter binding using $wpdb->prepare() for security.
 *
 * @package     WPUltimateSecurity\UltimateSecurity\Infrastructure\Persistence
 * @since       2.0.0
 */

declare(strict_types=1);

namespace WPUltimateSecurity\UltimateSecurity\Infrastructure\Persistence;

/**
 * Fluent SQL query builder.
 *
 * Provides:
 * - SELECT, INSERT, UPDATE, DELETE operations
 * - WHERE clause variations (where, orWhere, whereIn, whereBetween, etc.)
 * - JOIN support (inner, left, right)
 * - GROUP BY and HAVING clauses
 * - ORDER BY, LIMIT, OFFSET
 * - Aggregate functions (count, sum, avg, max, min)
 * - All queries use $wpdb->prepare() for security
 *
 * @since 2.0.0
 */
class QueryBuilder
{
    /**
     * WordPress database instance.
     *
     * @since 2.0.0
     * @var \wpdb
     */
    private \wpdb $db;

    /**
     * Table name with prefix.
     *
     * @since 2.0.0
     * @var string
     */
    private string $table = '';

    /**
     * Column type casts.
     *
     * @since 2.0.0
     * @var array<string, string>
     */
    private array $casts;

    /**
     * Columns to select.
     *
     * @since 2.0.0
     * @var string[]
     */
    private array $columns = ['*'];

    /**
     * Whether to return results as arrays.
     *
     * @since 2.0.0
     * @var bool
     */
    private bool $asArray = false;

    /**
     * WHERE clauses.
     *
     * @since 2.0.0
     * @var array<int, array{column: string, operator: string, value: mixed, boolean: string}>
     */
    private array $wheres = [];

    /**
     * ORDER BY clauses.
     *
     * @since 2.0.0
     * @var array<int, array{column: string, direction: string}>
     */
    private array $orders = [];

    /**
     * GROUP BY columns.
     *
     * @since 2.0.0
     * @var string[]
     */
    private array $groups = [];

    /**
     * HAVING clauses.
     *
     * @since 2.0.0
     * @var array<int, array{column: string, operator: string, value: mixed, boolean: string}>
     */
    private array $havings = [];

    /**
     * JOIN clauses.
     *
     * @since 2.0.0
     * @var array<int, array{type: string, table: string, first: string, operator: string, second: string}>
     */
    private array $joins = [];

    /**
     * LIMIT value.
     *
     * @since 2.0.0
     * @var int|null
     */
    private ?int $limitValue = null;

    /**
     * OFFSET value.
     *
     * @since 2.0.0
     * @var int|null
     */
    private ?int $offsetValue = null;

    /**
     * Constructor.
     *
     * @since 2.0.0
     *
     * @param \wpdb|null           $db    WordPress database instance.
     * @param string               $table Table name (optional).
     * @param array<string, string> $casts Column type casts.
     */
    public function __construct(?\wpdb $db = null, string $table = '', array $casts = [])
    {
        global $wpdb;
        $this->db = $db ?? $wpdb;
        $this->table = $table;
        $this->casts = $casts;
    }

    /**
     * Set the table to query.
     *
     * @since 2.0.0
     *
     * @param string $table Table name.
     *
     * @return self
     */
    public function from(string $table): self
    {
        $this->table = $table;
        return $this;
    }

    /**
     * Alias for from() method.
     *
     * @since 2.0.0
     *
     * @param string $table Table name.
     *
     * @return self
     */
    public function table(string $table): self
    {
        return $this->from($table);
    }

    /**
     * Set columns to select.
     *
     * @since 2.0.0
     *
     * @param string|array<string> $columns Columns to select.
     *
     * @return self
     */
    public function select(string|array $columns = ['*']): self
    {
        if (is_string($columns)) {
            $columns = func_get_args();
        }

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

    /**
     * Add columns to the select clause.
     *
     * @since 2.0.0
     *
     * @param string|array<string> $columns Additional columns to select.
     *
     * @return self
     */
    public function addSelect(string|array $columns): self
    {
        if (is_string($columns)) {
            $columns = func_get_args();
        }

        if ($this->columns === ['*']) {
            $this->columns = [];
        }

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

    /**
     * Return results as arrays instead of objects.
     *
     * @since 2.0.0
     *
     * @param bool $asArray Whether to return arrays.
     *
     * @return self
     */
    public function asArray(bool $asArray = true): self
    {
        $this->asArray = $asArray;
        return $this;
    }

    /**
     * Add a WHERE clause.
     *
     * @since 2.0.0
     *
     * @param string $column   Column name.
     * @param mixed  $operator Operator or value if no operator specified.
     * @param mixed  $value    Value (optional if operator is the value).
     *
     * @return self
     */
    public function where(string $column, mixed $operator, mixed $value = null): self
    {
        if ($value === null) {
            $value = $operator;
            $operator = '=';
        }

        $this->wheres[] = [
            'column' => $column,
            'operator' => strtoupper($operator),
            'value' => $value,
            'boolean' => 'AND',
        ];

        return $this;
    }

    /**
     * Add an OR WHERE clause.
     *
     * @since 2.0.0
     *
     * @param string $column   Column name.
     * @param mixed  $operator Operator or value.
     * @param mixed  $value    Value (optional).
     *
     * @return self
     */
    public function orWhere(string $column, mixed $operator, mixed $value = null): self
    {
        if ($value === null) {
            $value = $operator;
            $operator = '=';
        }

        $this->wheres[] = [
            'column' => $column,
            'operator' => strtoupper($operator),
            'value' => $value,
            'boolean' => 'OR',
        ];

        return $this;
    }

    /**
     * Add a WHERE IN clause.
     *
     * @since 2.0.0
     *
     * @param string       $column Column name.
     * @param array<mixed> $values Values to match.
     *
     * @return self
     */
    public function whereIn(string $column, array $values): self
    {
        $this->wheres[] = [
            'column' => $column,
            'operator' => 'IN',
            'value' => $values,
            'boolean' => 'AND',
        ];

        return $this;
    }

    /**
     * Add a WHERE NOT IN clause.
     *
     * @since 2.0.0
     *
     * @param string       $column Column name.
     * @param array<mixed> $values Values to exclude.
     *
     * @return self
     */
    public function whereNotIn(string $column, array $values): self
    {
        $this->wheres[] = [
            'column' => $column,
            'operator' => 'NOT IN',
            'value' => $values,
            'boolean' => 'AND',
        ];

        return $this;
    }

    /**
     * Add a WHERE BETWEEN clause.
     *
     * @since 2.0.0
     *
     * @param string $column Column name.
     * @param mixed  $start  Start value.
     * @param mixed  $end    End value.
     *
     * @return self
     */
    public function whereBetween(string $column, mixed $start, mixed $end): self
    {
        $this->wheres[] = [
            'column' => $column,
            'operator' => 'BETWEEN',
            'value' => [$start, $end],
            'boolean' => 'AND',
        ];

        return $this;
    }

    /**
     * Add a WHERE NOT BETWEEN clause.
     *
     * @since 2.0.0
     *
     * @param string $column Column name.
     * @param mixed  $start  Start value.
     * @param mixed  $end    End value.
     *
     * @return self
     */
    public function whereNotBetween(string $column, mixed $start, mixed $end): self
    {
        $this->wheres[] = [
            'column' => $column,
            'operator' => 'NOT BETWEEN',
            'value' => [$start, $end],
            'boolean' => 'AND',
        ];

        return $this;
    }

    /**
     * Add a WHERE LIKE clause.
     *
     * @since 2.0.0
     *
     * @param string $column  Column name.
     * @param string $pattern LIKE pattern (e.g., '%search%').
     *
     * @return self
     */
    public function whereLike(string $column, string $pattern): self
    {
        $this->wheres[] = [
            'column' => $column,
            'operator' => 'LIKE',
            'value' => $pattern,
            'boolean' => 'AND',
        ];

        return $this;
    }

    /**
     * Add a WHERE NOT LIKE clause.
     *
     * @since 2.0.0
     *
     * @param string $column  Column name.
     * @param string $pattern LIKE pattern.
     *
     * @return self
     */
    public function whereNotLike(string $column, string $pattern): self
    {
        $this->wheres[] = [
            'column' => $column,
            'operator' => 'NOT LIKE',
            'value' => $pattern,
            'boolean' => 'AND',
        ];

        return $this;
    }

    /**
     * Add a WHERE IS NULL clause.
     *
     * @since 2.0.0
     *
     * @param string $column Column name.
     *
     * @return self
     */
    public function whereNull(string $column): self
    {
        $this->wheres[] = [
            'column' => $column,
            'operator' => 'IS NULL',
            'value' => null,
            'boolean' => 'AND',
        ];

        return $this;
    }

    /**
     * Add a WHERE IS NOT NULL clause.
     *
     * @since 2.0.0
     *
     * @param string $column Column name.
     *
     * @return self
     */
    public function whereNotNull(string $column): self
    {
        $this->wheres[] = [
            'column' => $column,
            'operator' => 'IS NOT NULL',
            'value' => null,
            'boolean' => 'AND',
        ];

        return $this;
    }

    /**
     * Add a raw WHERE clause.
     *
     * WARNING: This method bypasses automatic escaping. The caller is responsible
     * for ensuring the raw SQL is safe. Always use bindings for user input.
     *
     * @since 2.0.0
     *
     * @param string       $sql      Raw SQL for WHERE clause.
     * @param array<mixed> $bindings Values to bind.
     *
     * @return self
     */
    public function whereRaw(string $sql, array $bindings = []): self
    {
        $this->wheres[] = [
            'column' => '',
            'operator' => 'RAW',
            'value' => ['sql' => $sql, 'bindings' => $bindings],
            'boolean' => 'AND',
        ];

        return $this;
    }

    /**
     * Add an INNER JOIN clause.
     *
     * @since 2.0.0
     *
     * @param string $table    Table to join.
     * @param string $first    First column.
     * @param string $operator Operator.
     * @param string $second   Second column.
     *
     * @return self
     */
    public function join(string $table, string $first, string $operator, string $second): self
    {
        $this->joins[] = [
            'type' => 'INNER',
            'table' => $table,
            'first' => $first,
            'operator' => $operator,
            'second' => $second,
        ];

        return $this;
    }

    /**
     * Add a LEFT JOIN clause.
     *
     * @since 2.0.0
     *
     * @param string $table    Table to join.
     * @param string $first    First column.
     * @param string $operator Operator.
     * @param string $second   Second column.
     *
     * @return self
     */
    public function leftJoin(string $table, string $first, string $operator, string $second): self
    {
        $this->joins[] = [
            'type' => 'LEFT',
            'table' => $table,
            'first' => $first,
            'operator' => $operator,
            'second' => $second,
        ];

        return $this;
    }

    /**
     * Add a RIGHT JOIN clause.
     *
     * @since 2.0.0
     *
     * @param string $table    Table to join.
     * @param string $first    First column.
     * @param string $operator Operator.
     * @param string $second   Second column.
     *
     * @return self
     */
    public function rightJoin(string $table, string $first, string $operator, string $second): self
    {
        $this->joins[] = [
            'type' => 'RIGHT',
            'table' => $table,
            'first' => $first,
            'operator' => $operator,
            'second' => $second,
        ];

        return $this;
    }

    /**
     * Add an ORDER BY clause.
     *
     * @since 2.0.0
     *
     * @param string $column    Column to order by.
     * @param string $direction Order direction (ASC or DESC).
     *
     * @return self
     */
    public function orderBy(string $column, string $direction = 'ASC'): self
    {
        $this->orders[] = [
            'column' => $column,
            'direction' => strtoupper($direction) === 'DESC' ? 'DESC' : 'ASC',
        ];

        return $this;
    }

    /**
     * Add a descending ORDER BY clause.
     *
     * @since 2.0.0
     *
     * @param string $column Column to order by.
     *
     * @return self
     */
    public function orderByDesc(string $column): self
    {
        return $this->orderBy($column, 'DESC');
    }

    /**
     * Order by most recent (created_at DESC).
     *
     * @since 2.0.0
     *
     * @param string $column Column name (default: created_at).
     *
     * @return self
     */
    public function latest(string $column = 'created_at'): self
    {
        return $this->orderBy($column, 'DESC');
    }

    /**
     * Order by oldest (created_at ASC).
     *
     * @since 2.0.0
     *
     * @param string $column Column name (default: created_at).
     *
     * @return self
     */
    public function oldest(string $column = 'created_at'): self
    {
        return $this->orderBy($column, 'ASC');
    }

    /**
     * Add a GROUP BY clause.
     *
     * @since 2.0.0
     *
     * @param string ...$columns Columns to group by.
     *
     * @return self
     */
    public function groupBy(string ...$columns): self
    {
        $this->groups = array_merge($this->groups, $columns);
        return $this;
    }

    /**
     * Add a HAVING clause.
     *
     * @since 2.0.0
     *
     * @param string $column   Column or aggregate expression.
     * @param mixed  $operator Operator or value.
     * @param mixed  $value    Value (optional).
     *
     * @return self
     */
    public function having(string $column, mixed $operator, mixed $value = null): self
    {
        if ($value === null) {
            $value = $operator;
            $operator = '=';
        }

        $this->havings[] = [
            'column' => $column,
            'operator' => strtoupper($operator),
            'value' => $value,
            'boolean' => 'AND',
        ];

        return $this;
    }

    /**
     * Add an OR HAVING clause.
     *
     * @since 2.0.0
     *
     * @param string $column   Column or aggregate expression.
     * @param mixed  $operator Operator or value.
     * @param mixed  $value    Value (optional).
     *
     * @return self
     */
    public function orHaving(string $column, mixed $operator, mixed $value = null): self
    {
        if ($value === null) {
            $value = $operator;
            $operator = '=';
        }

        $this->havings[] = [
            'column' => $column,
            'operator' => strtoupper($operator),
            'value' => $value,
            'boolean' => 'OR',
        ];

        return $this;
    }

    /**
     * Set the LIMIT value.
     *
     * @since 2.0.0
     *
     * @param int $limit Number of records to limit.
     *
     * @return self
     *
     * @throws \InvalidArgumentException If limit is less than 1.
     */
    public function limit(int $limit): self
    {
        if ($limit < 1) {
            throw new \InvalidArgumentException('Limit must be greater than 0');
        }
        $this->limitValue = $limit;
        return $this;
    }

    /**
     * Alias for limit().
     *
     * @since 2.0.0
     *
     * @param int $limit Number of records to take.
     *
     * @return self
     */
    public function take(int $limit): self
    {
        return $this->limit($limit);
    }

    /**
     * Set the OFFSET value.
     *
     * @since 2.0.0
     *
     * @param int $offset Number of records to skip.
     *
     * @return self
     *
     * @throws \InvalidArgumentException If offset is negative.
     */
    public function offset(int $offset): self
    {
        if ($offset < 0) {
            throw new \InvalidArgumentException('Offset cannot be negative');
        }
        $this->offsetValue = $offset;
        return $this;
    }

    /**
     * Alias for offset().
     *
     * @since 2.0.0
     *
     * @param int $offset Number of records to skip.
     *
     * @return self
     */
    public function skip(int $offset): self
    {
        return $this->offset($offset);
    }

    /**
     * Execute the query and get all results.
     *
     * @since 2.0.0
     *
     * @return array<int, object|array>
     */
    public function get(): array
    {
        $sql = $this->buildSelectSql();
        $outputType = $this->asArray ? ARRAY_A : OBJECT;
        $results = $this->db->get_results($sql, $outputType);

        if (!$results) {
            return [];
        }

        if (!$this->asArray) {
            return array_map([$this, 'castAttributes'], $results);
        }

        return $results;
    }

    /**
     * Get the first result.
     *
     * @since 2.0.0
     *
     * @return object|array|null
     */
    public function first(): object|array|null
    {
        $this->limitValue = 1;
        $results = $this->get();

        return $results[0] ?? null;
    }

    /**
     * Get a single column value from the first result.
     *
     * @since 2.0.0
     *
     * @param string $column Column name.
     *
     * @return mixed
     */
    public function value(string $column): mixed
    {
        $this->columns = [$column];
        $this->limitValue = 1;

        $sql = $this->buildSelectSql();
        return $this->db->get_var($sql);
    }

    /**
     * Get an array of values from a single column.
     *
     * @since 2.0.0
     *
     * @param string $column Column name.
     *
     * @return array<mixed>
     */
    public function pluck(string $column): array
    {
        $this->columns = [$column];

        $sql = $this->buildSelectSql();
        $results = $this->db->get_col($sql);

        return $results ?: [];
    }

    /**
     * Get the count of matching records.
     *
     * @since 2.0.0
     *
     * @param string $column Column to count (default: *).
     *
     * @return int
     */
    public function count(string $column = '*'): int
    {
        $originalColumns = $this->columns;
        $this->columns = ["COUNT({$column}) as aggregate"];

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

        $this->columns = $originalColumns;

        return (int) ($result ?? 0);
    }

    /**
     * Get the sum of a column.
     *
     * @since 2.0.0
     *
     * @param string $column Column to sum.
     *
     * @return float
     */
    public function sum(string $column): float
    {
        $safeColumn = $this->sanitizeColumn($column);
        $originalColumns = $this->columns;
        $this->columns = ["SUM({$safeColumn}) as aggregate"];

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

        $this->columns = $originalColumns;

        return (float) ($result ?? 0);
    }

    /**
     * Get the average of a column.
     *
     * @since 2.0.0
     *
     * @param string $column Column to average.
     *
     * @return float
     */
    public function avg(string $column): float
    {
        $safeColumn = $this->sanitizeColumn($column);
        $originalColumns = $this->columns;
        $this->columns = ["AVG({$safeColumn}) as aggregate"];

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

        $this->columns = $originalColumns;

        return (float) ($result ?? 0);
    }

    /**
     * Get the maximum value of a column.
     *
     * @since 2.0.0
     *
     * @param string $column Column to get max from.
     *
     * @return mixed
     */
    public function max(string $column): mixed
    {
        $safeColumn = $this->sanitizeColumn($column);
        $originalColumns = $this->columns;
        $this->columns = ["MAX({$safeColumn}) as aggregate"];

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

        $this->columns = $originalColumns;

        return $result;
    }

    /**
     * Get the minimum value of a column.
     *
     * @since 2.0.0
     *
     * @param string $column Column to get min from.
     *
     * @return mixed
     */
    public function min(string $column): mixed
    {
        $safeColumn = $this->sanitizeColumn($column);
        $originalColumns = $this->columns;
        $this->columns = ["MIN({$safeColumn}) as aggregate"];

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

        $this->columns = $originalColumns;

        return $result;
    }

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

    /**
     * Check if no records exist.
     *
     * @since 2.0.0
     *
     * @return bool
     */
    public function doesntExist(): bool
    {
        return !$this->exists();
    }

    /**
     * Get paginated results.
     *
     * @since 2.0.0
     *
     * @param int $perPage Items per page.
     * @param int $page    Current page (1-indexed).
     *
     * @return array{items: array<object|array>, total: int, per_page: int, current_page: int, last_page: int}
     */
    public function paginate(int $perPage = 15, int $page = 1): array
    {
        $total = $this->count();

        $this->limitValue = $perPage;
        $this->offsetValue = ($page - 1) * $perPage;

        $items = $this->get();

        return [
            'items' => $items,
            'total' => $total,
            'per_page' => $perPage,
            'current_page' => $page,
            'last_page' => (int) ceil($total / $perPage),
        ];
    }

    /**
     * Insert a new record.
     *
     * @since 2.0.0
     *
     * @param array<string, mixed> $data Data to insert.
     *
     * @return int|false Insert ID or false on failure.
     */
    public function insert(array $data): int|false
    {
        $columns = [];
        $values = [];
        $formats = [];

        foreach ($data as $column => $value) {
            $columns[] = $this->sanitizeColumn($column);
            $values[] = $value;
            $formats[] = $this->getFormat($value);
        }

        $columnList = implode(', ', $columns);
        $placeholders = implode(', ', $formats);

        $sql = "INSERT INTO {$this->table} ({$columnList}) VALUES ({$placeholders})";
        $prepared = $this->db->prepare($sql, ...$values);

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

        return $result !== false ? (int) $this->db->insert_id : false;
    }

    /**
     * Insert multiple records.
     *
     * @since 2.0.0
     *
     * @param array<int, array<string, mixed>> $records Records to insert.
     *
     * @return int Number of rows inserted.
     */
    public function insertMany(array $records): int
    {
        if (empty($records)) {
            return 0;
        }

        $columns = array_keys($records[0]);
        $columnList = implode(', ', array_map([$this, 'sanitizeColumn'], $columns));

        $valueGroups = [];
        $allValues = [];

        foreach ($records as $record) {
            $placeholders = [];
            foreach ($columns as $column) {
                $value = $record[$column] ?? null;
                $placeholders[] = $this->getFormat($value);
                $allValues[] = $value;
            }
            $valueGroups[] = '(' . implode(', ', $placeholders) . ')';
        }

        $sql = "INSERT INTO {$this->table} ({$columnList}) VALUES " . implode(', ', $valueGroups);
        $prepared = $this->db->prepare($sql, ...$allValues);

        $this->db->query($prepared);

        return (int) $this->db->rows_affected;
    }

    /**
     * Update matching records.
     *
     * @since 2.0.0
     *
     * @param array<string, mixed> $data Data to update.
     *
     * @return int Number of rows affected.
     */
    public function update(array $data): int
    {
        $sets = [];
        $values = [];

        foreach ($data as $column => $value) {
            $safeColumn = $this->sanitizeColumn($column);
            $format = $this->getFormat($value);
            $sets[] = "{$safeColumn} = {$format}";
            $values[] = $value;
        }

        $sql = "UPDATE {$this->table} SET " . implode(', ', $sets);

        if (!empty($this->wheres)) {
            $whereClause = $this->buildWhereClause($values);
            $sql .= ' WHERE ' . $whereClause;
        }

        $prepared = $this->db->prepare($sql, ...$values);
        $this->db->query($prepared);

        return (int) $this->db->rows_affected;
    }

    /**
     * Update or insert a record.
     *
     * @since 2.0.0
     *
     * @param array<string, mixed> $attributes Attributes to search by.
     * @param array<string, mixed> $values     Values to update/insert.
     *
     * @return int Number of affected rows (1 for insert, rows affected for update).
     */
    public function updateOrInsert(array $attributes, array $values = []): int
    {
        foreach ($attributes as $column => $value) {
            $this->where($column, $value);
        }

        if ($this->exists()) {
            return $this->update($values);
        }

        $insertId = $this->insert(array_merge($attributes, $values));
        return $insertId !== false ? 1 : 0;
    }

    /**
     * Increment a column value.
     *
     * @since 2.0.0
     *
     * @param string $column Column to increment.
     * @param int    $amount Amount to increment by.
     *
     * @return int Number of rows affected.
     */
    public function increment(string $column, int $amount = 1): int
    {
        $safeColumn = $this->sanitizeColumn($column);
        $values = [$amount];

        $sql = "UPDATE {$this->table} SET {$safeColumn} = {$safeColumn} + %d";

        if (!empty($this->wheres)) {
            $whereClause = $this->buildWhereClause($values);
            $sql .= ' WHERE ' . $whereClause;
        }

        $prepared = $this->db->prepare($sql, ...$values);
        $this->db->query($prepared);

        return (int) $this->db->rows_affected;
    }

    /**
     * Decrement a column value.
     *
     * @since 2.0.0
     *
     * @param string $column Column to decrement.
     * @param int    $amount Amount to decrement by.
     *
     * @return int Number of rows affected.
     */
    public function decrement(string $column, int $amount = 1): int
    {
        $safeColumn = $this->sanitizeColumn($column);
        $values = [$amount];

        $sql = "UPDATE {$this->table} SET {$safeColumn} = {$safeColumn} - %d";

        if (!empty($this->wheres)) {
            $whereClause = $this->buildWhereClause($values);
            $sql .= ' WHERE ' . $whereClause;
        }

        $prepared = $this->db->prepare($sql, ...$values);
        $this->db->query($prepared);

        return (int) $this->db->rows_affected;
    }

    /**
     * Delete matching records.
     *
     * @since 2.0.0
     *
     * @return int Number of rows deleted.
     */
    public function delete(): int
    {
        $sql = "DELETE FROM {$this->table}";
        $values = [];

        if (!empty($this->wheres)) {
            $whereClause = $this->buildWhereClause($values);
            $sql .= ' WHERE ' . $whereClause;
        }

        if (!empty($values)) {
            $sql = $this->db->prepare($sql, ...$values);
        }

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

        return (int) $this->db->rows_affected;
    }

    /**
     * Truncate the table.
     *
     * @since 2.0.0
     *
     * @return bool True on success.
     */
    public function truncate(): bool
    {
        return (bool) $this->db->query("TRUNCATE TABLE {$this->table}");
    }

    /**
     * Clone the builder for reuse.
     *
     * @since 2.0.0
     *
     * @return self
     */
    public function clone(): self
    {
        return clone $this;
    }

    /**
     * Reset the builder state.
     *
     * @since 2.0.0
     *
     * @return self
     */
    public function reset(): self
    {
        $this->columns = ['*'];
        $this->wheres = [];
        $this->orders = [];
        $this->groups = [];
        $this->havings = [];
        $this->joins = [];
        $this->limitValue = null;
        $this->offsetValue = null;
        $this->asArray = false;

        return $this;
    }

    /**
     * Get the raw SQL for debugging.
     *
     * @since 2.0.0
     *
     * @return string
     */
    public function toSql(): string
    {
        return $this->buildSelectSql();
    }

    /**
     * Dump the SQL and die.
     *
     * @since 2.0.0
     *
     * @return never
     */
    public function dd(): never
    {
        // phpcs:ignore WordPress.PHP.DevelopmentFunctions.error_log_var_dump
        var_dump($this->toSql());
        die();
    }

    /**
     * Build the SELECT SQL query.
     *
     * @since 2.0.0
     *
     * @return string
     */
    private function buildSelectSql(): string
    {
        $columns = $this->columns === ['*']
            ? '*'
            : implode(', ', array_map([$this, 'sanitizeColumn'], $this->columns));

        $sql = "SELECT {$columns} FROM {$this->table}";

        // JOIN clauses
        foreach ($this->joins as $join) {
            $sql .= " {$join['type']} JOIN {$join['table']} ON {$join['first']} {$join['operator']} {$join['second']}";
        }

        // WHERE clause
        $values = [];
        if (!empty($this->wheres)) {
            $whereClause = $this->buildWhereClause($values);
            $sql .= ' WHERE ' . $whereClause;
        }

        // GROUP BY clause
        if (!empty($this->groups)) {
            $sql .= ' GROUP BY ' . implode(', ', array_map([$this, 'sanitizeColumn'], $this->groups));
        }

        // HAVING clause
        if (!empty($this->havings)) {
            $havingClause = $this->buildHavingClause($values);
            $sql .= ' HAVING ' . $havingClause;
        }

        // ORDER BY clause
        if (!empty($this->orders)) {
            $orderParts = [];
            foreach ($this->orders as $order) {
                $orderParts[] = $this->sanitizeColumn($order['column']) . ' ' . $order['direction'];
            }
            $sql .= ' ORDER BY ' . implode(', ', $orderParts);
        }

        // LIMIT clause
        if ($this->limitValue !== null) {
            $values[] = $this->limitValue;
            $sql .= ' LIMIT %d';
        }

        // OFFSET clause
        if ($this->offsetValue !== null) {
            $values[] = $this->offsetValue;
            $sql .= ' OFFSET %d';
        }

        // Prepare the final SQL with all values
        if (!empty($values)) {
            $sql = $this->db->prepare($sql, ...$values);
        }

        return $sql;
    }

    /**
     * Build the WHERE clause.
     *
     * @since 2.0.0
     *
     * @param array<mixed> $values Reference to values array for binding.
     *
     * @return string
     */
    private function buildWhereClause(array &$values): string
    {
        $parts = [];

        foreach ($this->wheres as $i => $where) {
            $clause = '';

            if ($i > 0) {
                $clause .= ' ' . $where['boolean'] . ' ';
            }

            // Handle raw SQL
            if ($where['operator'] === 'RAW') {
                $rawData = $where['value'];
                $clause .= $rawData['sql'];
                foreach ($rawData['bindings'] as $binding) {
                    $values[] = $binding;
                }
                $parts[] = $clause;
                continue;
            }

            $safeColumn = $this->sanitizeColumn($where['column']);

            switch ($where['operator']) {
                case 'IN':
                case 'NOT IN':
                    $placeholders = [];
                    foreach ($where['value'] as $v) {
                        $placeholders[] = $this->getFormat($v);
                        $values[] = $v;
                    }
                    $clause .= "{$safeColumn} {$where['operator']} (" . implode(', ', $placeholders) . ")";
                    break;

                case 'BETWEEN':
                case 'NOT BETWEEN':
                    $clause .= "{$safeColumn} {$where['operator']} ";
                    $clause .= $this->getFormat($where['value'][0]) . ' AND ' . $this->getFormat($where['value'][1]);
                    $values[] = $where['value'][0];
                    $values[] = $where['value'][1];
                    break;

                case 'IS NULL':
                case 'IS NOT NULL':
                    $clause .= "{$safeColumn} {$where['operator']}";
                    break;

                case 'LIKE':
                case 'NOT LIKE':
                    $clause .= "{$safeColumn} {$where['operator']} %s";
                    $values[] = $where['value'];
                    break;

                default:
                    $format = $this->getFormat($where['value']);
                    $clause .= "{$safeColumn} {$where['operator']} {$format}";
                    $values[] = $where['value'];
            }

            $parts[] = $clause;
        }

        return implode('', $parts);
    }

    /**
     * Build the HAVING clause.
     *
     * @since 2.0.0
     *
     * @param array<mixed> $values Reference to values array for binding.
     *
     * @return string
     */
    private function buildHavingClause(array &$values): string
    {
        $parts = [];

        foreach ($this->havings as $i => $having) {
            $clause = '';

            if ($i > 0) {
                $clause .= ' ' . $having['boolean'] . ' ';
            }

            $safeColumn = $this->sanitizeColumn($having['column']);
            $format = $this->getFormat($having['value']);

            $clause .= "{$safeColumn} {$having['operator']} {$format}";
            $values[] = $having['value'];

            $parts[] = $clause;
        }

        return implode('', $parts);
    }

    /**
     * Sanitize a column name to prevent SQL injection.
     *
     * @since 2.0.0
     *
     * @param string $column Column name.
     *
     * @return string Sanitized column name.
     *
     * @throws \InvalidArgumentException If column name is invalid.
     */
    private function sanitizeColumn(string $column): string
    {
        // Allow asterisk for select all
        if ($column === '*') {
            return $column;
        }

        // Allow aggregate functions with complete syntax validation
        if (preg_match('/^(COUNT|SUM|AVG|MIN|MAX)\s*\([^)]*\)$/i', $column)) {
            return $column;
        }

        // Allow aliased columns (column as alias)
        if (preg_match('/^[\w.]+\s+as\s+[\w]+$/i', $column)) {
            return $column;
        }

        // Allow table.column format
        if (preg_match('/^[a-zA-Z_][a-zA-Z0-9_]*\.[a-zA-Z_][a-zA-Z0-9_]*$/', $column)) {
            $parts = explode('.', $column);
            return '`' . $parts[0] . '`.`' . $parts[1] . '`';
        }

        // Basic column name validation
        if (!preg_match('/^[a-zA-Z_][a-zA-Z0-9_]*$/', $column)) {
            throw new \InvalidArgumentException("Invalid column name: {esc_html($column)}");
        }

        return "`{$column}`";
    }

    /**
     * Get the format string for a value.
     *
     * @since 2.0.0
     *
     * @param mixed $value Value to format.
     *
     * @return string Format string (%d, %f, or %s).
     */
    private function getFormat(mixed $value): string
    {
        if (is_int($value)) {
            return '%d';
        }

        if (is_float($value)) {
            return '%f';
        }

        return '%s';
    }

    /**
     * Cast attributes to their proper types.
     *
     * @since 2.0.0
     *
     * @param object $row Database row.
     *
     * @return object
     */
    private function castAttributes(object $row): object
    {
        if (empty($this->casts)) {
            return $row;
        }

        foreach ($this->casts as $key => $type) {
            if (!isset($row->$key)) {
                continue;
            }

            $row->$key = match ($type) {
                'int', 'integer' => (int) $row->$key,
                'float', 'double' => (float) $row->$key,
                'bool', 'boolean' => (bool) $row->$key,
                'string' => (string) $row->$key,
                'array', 'json' => is_string($row->$key)
                    ? json_decode($row->$key, true)
                    : $row->$key,
                default => $row->$key,
            };
        }

        return $row;
    }

    /**
     * Get the last database error.
     *
     * @since 2.0.0
     *
     * @return string
     */
    public function getLastError(): string
    {
        return $this->db->last_error;
    }
}
