<?php

namespace Booktics\Abstracts;

use wpdb;

/**
 * Query builder for Booktics models.
 *
 * @property string $table
 */
class DB_Query_Builder {
    /** @var wpdb */
    private $db;
    /** @var string */
    private $table;
    /** @var array */
    private $conditions = array();
    /** @var array */
    private $values = array();
    /** @var array */
    private $columns = array( '*' );
    /** @var int|null */
    private $limit = null;
    /** @var int|null */
    private $offset = null;

    private $order_by = array();

    /**
     * Initialize the query builder with a database connection
     *
     * @param wpdb $db WordPress database instance
     */
    public function __construct( wpdb $db ) {
        $this->db = $db;
    }

    /**
     * Set the table name for the query
     *
     * @param string $table Table name without prefix
     *
     * @return self
     */
    public function table( string $table ): self {
        $this->table = booktics_get_table_name( $table );

        return $this;
    }

    /**
     * Select specific columns for the query
     *
     * @param array $columns
     *
     * @return self
     */
    public function select( array $columns ): self {
        $this->columns = $columns;

        return $this;
    }

    /**
     * Add a WHERE condition to the query
     *
     * @param string $column
     * @param string $operator
     * @param mixed $value
     *
     * @return self
     */
    public function where( string $column, string $operator, $value ): self {
        $this->conditions[] = "$column $operator %s";
        $this->values[]     = $value;

        return $this;
    }

    /**
     * Add a WHERE IN condition to the query
     *
     * @param string $column
     * @param array $values
     *
     * @return self
     */
    public function where_in( string $column, array $values ): self {
        if ( empty( $values ) ) {
            return $this;
        }
        $placeholders       = implode( ',', array_fill( 0, count( $values ), '%s' ) );
        $this->conditions[] = "$column IN ($placeholders)";
        $this->values       = array_merge( $this->values, $values );

        return $this;
    }

    /**
     * Add an OR WHERE condition to the query
     *
     * @param string $column
     * @param string $operator
     * @param mixed $value
     *
     * @return self
     */
    public function or_where( string $column, string $operator, $value ): self {
        $last               = array_pop( $this->conditions );
        $this->conditions[] = "($last OR $column $operator %s)";
        $this->values[]     = $value;

        return $this;
    }

    /**
     * Add a group of AND conditions
     *
     * @param callable $callback
     *
     * @return self
     */
    public function where_group( callable $callback ): self {
        $query             = clone $this;
        $query->conditions = array();
        $query->values     = array();
        $callback( $query );
        if ( ! empty( $query->conditions ) ) {
            $group              = implode( ' AND ', $query->conditions );
            $this->conditions[] = "($group)";
            $this->values       = array_merge( $this->values, $query->values );
        }

        return $this;
    }

    /**
     * Add a group of OR conditions
     *
     * @param callable $callback
     *
     * @return self
     */
    public function or_where_group( callable $callback ): self {
        $query             = clone $this;
        $query->conditions = array();
        $query->values     = array();
        $callback( $query );
        if ( ! empty( $query->conditions ) ) {
            $group              = implode( ' AND ', $query->conditions );
            $last               = array_pop( $this->conditions );
            $this->conditions[] = "($last OR ($group))";
            $this->values       = array_merge( $this->values, $query->values );
        }

        return $this;
    }

    /**
     * Set the maximum number of records to retrieve
     *
     * @param int $limit
     *
     * @return self
     */
    public function limit( int $limit ): self {
        $this->limit = $limit;

        return $this;
    }

    /**
     * Set the starting position of records
     *
     * @param int $offset
     *
     * @return self
     */
    public function offset( int $offset ): self {
        $this->offset = $offset;

        return $this;
    }

    /**
     * Set the ordering condition
     *
     * @param array $order_by
     *
     * @return self
     */
    public function order_by( array $order_by ): self {
        $this->order_by = $order_by;

        return $this;
    }

    /**
     * Get total count of records
     *
     * @return int
     */
    public function count(): int {
        $sql = "SELECT COUNT(*) as count FROM {$this->table}";
        if ( $this->conditions ) {
            $sql .= ' WHERE ' . implode( ' AND ', $this->conditions );
        }
        $prepared = $this->db->prepare( $sql, ...$this->values );
        $result   = $this->db->get_row( $prepared );
        if ( $result === null ) {
            throw new \RuntimeException( sprintf( 'Query failed: %s', esc_html( $this->db->last_error ) ) );
        }

        return (int) $result->count;
    }

    /**
     * Calculate sum of a column
     *
     * @param string $column
     *
     * @return float
     */
    public function sum( string $column ): float {
        $sql = "SELECT SUM($column) as sum FROM {$this->table}";
        if ( $this->conditions ) {
            $sql .= ' WHERE ' . implode( ' AND ', $this->conditions );
        }
        $prepared = $this->db->prepare( $sql, ...$this->values );
        $result   = $this->db->get_row( $prepared );
        if ( $result === null ) {
            throw new \RuntimeException( sprintf( 'Query failed: %s', esc_html( $this->db->last_error ) ) );
        }

        return (float) $result->sum;
    }

    /**
     * Get query result
     *
     * @return array
     */
    public function get(): array {
        $sql = 'SELECT ' . implode( ', ', $this->columns ) . " FROM {$this->table}";
        if ( $this->conditions ) {
            $sql .= ' WHERE ' . implode( ' AND ', $this->conditions );
        }
        if ( ! empty( $this->order_by ) ) {
            $sql .= ' ORDER BY ' . ( $this->order_by[0] ?? 'id' ) . ' ' . ( $this->order_by[1] ?? 'ASC' );
        }
        if ( $this->limit !== null ) {
            $sql .= " LIMIT {$this->limit}";
        }
        if ( $this->offset !== null ) {
            $sql .= " OFFSET {$this->offset}";
        }
        $prepared = $this->db->prepare( $sql, ...$this->values );
        $results  = $this->db->get_results( $prepared );
        if ( $results === null ) {
            throw new \RuntimeException( esc_html( "Query failed: {$this->db->last_error}" ) );
        }

        return $results;
    }

    /**
     * Get the first record matching the query
     *
     * @return object|null
     */
    public function first(): ?object {
        $sql = 'SELECT ' . implode( ', ', $this->columns ) . " FROM {$this->table}";
        if ( $this->conditions ) {
            $sql .= ' WHERE ' . implode( ' AND ', $this->conditions );
        }
        $sql      .= ' LIMIT 1';
        $prepared = $this->db->prepare( $sql, ...$this->values );
        $result   = $this->db->get_row( $prepared );
        if ( $this->db->last_error ) {
            if ( function_exists( 'is_wp_error' ) ) {
                return new \WP_Error( 'db_query_failed', sprintf( 'Query failed: %s', esc_html( $this->db->last_error ) ) );
            }
        }

        return $result;
    }

    /**
     * Convert query result to array
     *
     * @return array
     */
    public function to_array(): array {
        $results = $this->get();

        return array_map(
            function ( $row ) {
                return (array) $row;
            }, $results
        );
    }

    /**
     * Insert data into the table
     *
     * @param array $data
     *
     * @return int|false
     */
    public function insert( array $data ) {
        $sql          = $this->prepare_insert( $data );
        $query_result = $this->db->query( $sql );

        return $query_result ? $this->db->insert_id : false;
    }

    /**
     * Prepare SQL INSERT statement
     *
     * @param array $data
     *
     * @return string
     */
    private function prepare_insert( array $data ): string {
        $columns = array_keys( $data );
        $values  = array_map(
            function ( $value ) {
                if ( is_null( $value ) ) {
                        return 'NULL';
                }
                if ( is_numeric( $value ) ) {
                    return $value;
                }

                return "'" . esc_sql( $value ) . "'";
            }, array_values( $data )
        );

        return sprintf(
            'INSERT INTO %s (%s) VALUES (%s)',
            $this->table,
            implode( ', ', $columns ),
            implode( ', ', $values )
        );
    }

    /**
     * Update records in the table
     *
     * @param array $data
     *
     * @return bool
     */
    public function update( array $data ): bool {
        if ( empty( $this->conditions ) ) {
            throw new \RuntimeException( 'Update operation requires WHERE conditions' );
        }
        $set = array();
        foreach ( $data as $column => $value ) {
            if ( is_null( $value ) ) {
                $set[] = "$column = NULL";
            } elseif ( is_numeric( $value ) ) {
                $set[] = "$column = $value";
            } else {
                $set[] = "$column = '" . esc_sql( $value ) . "'";
            }
        }
        $sql      = sprintf(
            'UPDATE %s SET %s WHERE %s',
            $this->table,
            implode( ', ', $set ),
            implode( ' AND ', $this->conditions )
        );
        $prepared = $this->db->prepare( $sql, ...$this->values );
        $result   = $this->db->query( $prepared );
        if ( $result === false ) {
            throw new \RuntimeException( sprintf( 'Update failed: %s', esc_html( $this->db->last_error ) ) );
        }

        return true;
    }

    /**
     * Delete records from the table
     *
     * @return bool
     */
    public function delete(): bool {
        if ( empty( $this->conditions ) ) {
            throw new \RuntimeException( 'Delete operation requires WHERE conditions' );
        }
        $sql      = sprintf(
            'DELETE FROM %s WHERE %s',
            $this->table,
            implode( ' AND ', $this->conditions )
        );
        $prepared = $this->db->prepare( $sql, ...$this->values );
        $result   = $this->db->query( $prepared );
        if ( $result === false ) {
            throw new \RuntimeException( sprintf( 'Delete failed: %s', esc_html( $this->db->last_error ) ) );
        }

        return true;
    }

    /**
     * Paginate the query results
     *
     * @param int $page
     * @param int $per_page
     *
     * @return array
     */
    public function paginate( int $page = 1, int $per_page = 10 ): array {
        $total  = $this->count();
        $offset = ( $page - 1 ) * $per_page;
        $items  = $this->limit( $per_page )->offset( $offset )->get();

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

    /**
     * Reset the query builder state (for reuse)
     *
     * @return self
     */
    public function reset(): self {
        $this->conditions = array();
        $this->values     = array();
        $this->columns    = array( '*' );
        $this->limit      = null;
        $this->offset     = null;
        $this->order_by   = array();

        return $this;
    }

    /**
     * Clone handler for safe query grouping
     */
    public function __clone() {
        $this->conditions = array_merge( array(), $this->conditions );
        $this->values     = array_merge( array(), $this->values );
        $this->columns    = array_merge( array(), $this->columns );
        $this->order_by   = array_merge( array(), $this->order_by );
    }
}