<?php

namespace Booktics\Abstracts;

use wpdb;

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 Returns the current instance for method chaining
     */
    public function table( string $table ): self {
        $this->table = booktics_get_table_name( $table );

        return $this;
    }

    /**
     * Select specific columns for the query
     *
     * @param array $columns Array of column names to select
     *
     * @return self Returns the current instance for method chaining
     */
    public function select( array $columns ): self {
        $this->columns = $columns;

        return $this;
    }

    /**
     * Add a WHERE condition to the query
     *
     * @param string $column Column name
     * @param string $operator Comparison operator (=, >, <, etc.)
     * @param mixed $value Value to compare against
     *
     * @return self Returns the current instance for method chaining
     */
    public function where( string $column, string $operator, $value ): self {
        $this->conditions[] = "$column $operator %s";
        $this->values[]     = $value;

        return $this;
    }

    /**
     * Add an OR WHERE condition to the query
     *
     * @param string $column Column name
     * @param string $operator Comparison operator (=, >, <, etc.)
     * @param mixed $value Value to compare against
     *
     * @return self Returns the current instance for method chaining
     */
    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 and where with a group of conditions
     *
     * @param callable $callback
     *
     * @return $this
     */
    public function where_group( callable $callback ): self {
        $query = new self( $this->db );
        $callback( $query );
        $group              = implode( ' AND ', $query->conditions );
        $this->conditions[] = "($group)";
        $this->values       = array_merge( $this->values, $query->values );

        return $this;
    }

    /**
     * add or where with a group of conditions
     *
     * @param callable $callback
     *
     * @return $this
     */
    public function or_where_group( callable $callback ): self {
        $query = new self( $this->db );
        $callback( $query );
        $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 Column name to sum
     *
     * @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 ( $result === null && $this->db->last_error ) {
            throw new \RuntimeException( 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 Data to update
     *
     * @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;
    }
}