<?php

namespace SugarCalendarBookings\Support\Database;

use Exception;
use SugarCalendarBookings\Support\Helpers\DB;
use wpdb;

/**
 * QueryBuilder.
 *
 * Provides a fluent, Eloquent-inspired interface for building SELECT queries with
 * nested conditions and safe parameter binding.
 *
 * @since 1.0.0
 */
class QueryBuilder {

	/**
	 * The table to query (without prefix).
	 *
	 * @since 1.0.0
	 *
	 * @var string
	 */
	protected $table;

	/**
	 * The columns to select.
	 *
	 * @since 1.0.0
	 *
	 * @var array
	 */
	protected $columns = [ '*' ];

	/**
	 * The join clauses.
	 *
	 * @since 1.0.0
	 *
	 * @var array
	 */
	protected $joins = [];

	/**
	 * The where conditions (tree structure).
	 *
	 * @since 1.0.0
	 *
	 * @var array
	 */
	protected $wheres = [];

	/**
	 * The group by columns.
	 *
	 * @since 1.0.0
	 *
	 * @var array
	 */
	protected $groups = [];

	/**
	 * The order by clauses.
	 *
	 * @since 1.0.0
	 *
	 * @var array
	 */
	protected $orders = [];

	/**
	 * The limit value.
	 *
	 * @since 1.0.0
	 *
	 * @var int|null
	 */
	protected $limit;

	/**
	 * The offset value.
	 *
	 * @since 1.0.0
	 *
	 * @var int|null
	 */
	protected $offset;

	/**
	 * The parameter bindings for the query.
	 *
	 * @since 1.0.0
	 *
	 * @var array
	 */
	protected $bindings = [];

	/**
	 * The WordPress database object.
	 *
	 * @since 1.0.0
	 *
	 * @var wpdb
	 */
	protected $db;

	/**
	 * The table prefix.
	 *
	 * @since 1.0.0
	 *
	 * @var string
	 */
	protected $prefix;

	/**
	 * Allowed SQL operators for where/having clauses.
	 *
	 * @since 1.0.0
	 *
	 * @var array
	 */
	protected static $allowed_operators = [
		'=',
		'!=',
		'<>',
		'<',
		'>',
		'<=',
		'>=',
		'LIKE',
		'NOT LIKE',
		'IN',
		'NOT IN',
		'IS NULL',
		'IS NOT NULL',
	];

	/**
	 * Allowed SQL operators for join clauses.
	 *
	 * @since 1.0.0
	 *
	 * @var array
	 */
	protected static $allowed_join_operators = [
		'=',
		'<',
		'>',
		'<=',
		'>=',
		'<>',
		'!=',
	];

	/**
	 * Allowed SQL join types.
	 *
	 * @since 1.0.0
	 *
	 * @var array
	 */
	protected static $allowed_join_types = [
		'INNER',
		'LEFT',
		'RIGHT',
		'FULL OUTER',
		'LEFT OUTER',
		'RIGHT OUTER',
	];

	/**
	 * Allowed SQL value placeholders for bindings.
	 *
	 * @since 1.0.0
	 *
	 * @var array
	 */
	protected static $allowed_placeholders = [ '%s', '%d', '%f' ];

	/**
	 * Allowed ORDER BY directions.
	 *
	 * @since 1.0.0
	 *
	 * @var array
	 */
	protected static $allowed_order_directions = [ 'ASC', 'DESC' ];

	/**
	 * QueryBuilder constructor.
	 *
	 * @since 1.0.0
	 */
	public function __construct() {

		global $wpdb;

		$this->db     = $wpdb;
		$this->prefix = $this->db->prefix . 'scbookings_';
	}

	/**
	 * Set the table to query (without prefix).
	 *
	 * @since 1.0.0
	 *
	 * @param string $table Table name (without prefix).
	 *
	 * @return $this
	 */
	public function table( $table ) {

		$this->table = $table;

		return $this;
	}

	/**
	 * Set the columns to select.
	 *
	 * @since 1.0.0
	 *
	 * @param array $columns Columns to select.
	 *
	 * @return $this
	 */
	public function select( $columns ) {

		$this->columns = $columns;

		return $this;
	}

	/**
	 * Add a where condition or a nested group via closure.
	 *
	 * @since 1.0.0
	 *
	 * @param string|callable $column      Column name or closure for nested group.
	 * @param string|null     $operator    Operator (e.g., '=', '>', etc.).
	 * @param mixed|null      $value       Value to compare.
	 * @param string          $placeholder Placeholder for value (%s, %d, %f).
	 *
	 * @return $this
	 */
	public function where( $column, $operator = null, $value = null, $placeholder = null ) {

		return $this->add_where( 'AND', $column, $operator, $value, $placeholder );
	}

	/**
	 * Add an OR where condition or a nested group via closure.
	 *
	 * @since 1.0.0
	 *
	 * @param string|callable $column      Column name or closure for nested group.
	 * @param string|null     $operator    Operator (e.g., '=', '>', etc.).
	 * @param mixed|null      $value       Value to compare.
	 * @param string          $placeholder Placeholder for value (%s, %d, %f).
	 *
	 * @return $this
	 */
	public function or_where( $column, $operator = null, $value = null, $placeholder = null ) {

		return $this->add_where( 'OR', $column, $operator, $value, $placeholder );
	}

	/**
	 * Internal method to add a where condition or group.
	 *
	 * @since 1.0.0
	 *
	 * @param string          $boolean     Boolean operator (AND or OR).
	 * @param string|callable $column      Column name or closure for nested group.
	 * @param string|null     $operator    Operator (e.g., '=', '>', etc.).
	 * @param mixed|null      $value       Value to compare.
	 * @param string          $placeholder Placeholder for value (%s, %d, %f).
	 *
	 * @return $this
	 *
	 * phpcs:ignore Squiz.Commenting.FunctionCommentThrowTag.Missing
	 */
	private function add_where( $boolean, $column, $operator = null, $value = null, $placeholder = null ) {

		if ( is_callable( $column ) ) {
			$query = new self();

			// Execute the closure to build the nested query.
			$column( $query );

			$this->wheres[] = [
				'type'    => 'nested',
				'boolean' => $boolean,
				'wheres'  => $query->wheres,
			];
		} else {
			if (
				$placeholder === null &&
				! in_array( $operator, [ 'IS NULL', 'IS NOT NULL' ], true )
			) {
				if ( defined( 'SCBOOKINGS_DEV_MODE' ) && SCBOOKINGS_DEV_MODE === true ) {
					throw new Exception(
						'Placeholder must be provided for where conditions. Use %s, %d, or %f.'
					);
				}

				$placeholder = '%s'; // Default placeholder.
			}

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

		return $this;
	}

	/**
	 * Add a join clause.
	 *
	 * @since 1.0.0
	 *
	 * @param string $table    Table to join (without prefix).
	 * @param string $first    First column.
	 * @param string $operator Operator.
	 * @param string $second   Second column.
	 * @param string $type     Join type (INNER, LEFT, etc.).
	 *
	 * @return $this
	 */
	public function join( $table, $first, $operator, $second, $type = 'INNER' ) {

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

		return $this;
	}

	/**
	 * Add a group by clause.
	 *
	 * @since 1.0.0
	 *
	 * @param array $columns Columns to group by.
	 *
	 * @return $this
	 */
	public function group_by( $columns ) {

		$this->groups = array_merge( $this->groups, $columns );

		return $this;
	}

	/**
	 * Add an order by clause.
	 *
	 * @since 1.0.0
	 *
	 * @param string $column    Column name.
	 * @param string $direction Direction (ASC or DESC).
	 *
	 * @return $this
	 */
	public function order_by( $column, $direction = 'ASC' ) {

		$this->orders[] = [
			'column'    => $column,
			'direction' => $direction,
		];

		return $this;
	}

	/**
	 * Set the limit.
	 *
	 * @since 1.0.0
	 *
	 * @param int $limit Limit value.
	 *
	 * @return $this
	 */
	public function limit( $limit ) {

		$this->limit = $limit;

		return $this;
	}

	/**
	 * Set the offset.
	 *
	 * @since 1.0.0
	 *
	 * @param int $offset Offset value.
	 *
	 * @return $this
	 */
	public function offset( $offset ) {

		$this->offset = $offset;

		return $this;
	}

	/**
	 * Execute the query and return all results as an array.
	 *
	 * @since 1.0.0
	 *
	 * @return array
	 */
	public function get() {

		if ( ! DB::table_exists( $this->table ) ) {
			return [];
		}

		$sql = $this->build_select();

		if ( $this->bindings ) {
			$sql = $this->db->prepare( $sql, $this->bindings );
		}

		$result = $this->db->get_results( $sql, ARRAY_A );

		return $result ?? [];
	}

	/**
	 * Execute the query and return the first result as an array.
	 *
	 * @since 1.0.0
	 *
	 * @return array|null
	 */
	public function first() {

		$this->limit( 1 );
		$results = $this->get();

		return $results[0] ?? null;
	}

	/**
	 * Get a flat array of values for a given column from the result set.
	 *
	 * @since 1.0.0
	 *
	 * @param string $column The column name to extract.
	 *
	 * @return array Array of values for the given column.
	 */
	public function column( $column ) {

		$results = $this->select( [ $column ] )->get();

		return array_column( $results, $column );
	}

	/**
	 * Execute the query and return the count of records.
	 *
	 * @since 1.0.0
	 *
	 * @return int
	 */
	public function count() {

		if ( ! DB::table_exists( $this->table ) ) {
			return 0;
		}

		// Store original columns.
		$original_columns = $this->columns;

		// Set count column.
		$this->columns = [ 'COUNT(*)' ];

		// Build and execute query.
		$sql = $this->build_select();

		if ( $this->bindings ) {
			$sql = $this->db->prepare( $sql, $this->bindings );
		}

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

		// Restore original columns.
		$this->columns = $original_columns;

		return (int) $result;
	}

	/**
	 * Insert multiple rows into the table.
	 *
	 * @since 1.0.0
	 *
	 * @param array $rows         Array of associative arrays (rows) to insert.
	 * @param array $placeholders Associative array of column => placeholder (e.g., '%d', '%s', '%f').
	 *
	 * @return bool True if the query was successful, false otherwise.
	 */
	public function insert( $rows, $placeholders ) {

		if ( empty( $rows ) ) {
			return true;
		}

		if ( ! DB::table_exists( $this->table ) ) {
			return false;
		}

		$sql = $this->build_insert( $rows, $placeholders );

		if ( $this->bindings ) {
			$sql = $this->db->prepare( $sql, $this->bindings );
		}

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

		return $result !== false;
	}

	/**
	 * Perform an upsert operation for multiple rows.
	 *
	 * @since 1.0.0
	 *
	 * @param array $rows           Array of associative arrays (rows) to insert.
	 * @param array $update_columns Columns to update on duplicate key.
	 * @param array $placeholders   Associative array of column => placeholder (e.g., '%d', '%s', '%f').
	 *
	 * @return bool True if the query was successful, false otherwise.
	 */
	public function upsert( $rows, $update_columns, $placeholders ) {

		if ( empty( $rows ) || empty( $update_columns ) ) {
			return true;
		}

		if ( ! DB::table_exists( $this->table ) ) {
			return false;
		}

		$sql = $this->build_upsert( $rows, $update_columns, $placeholders );

		if ( $this->bindings ) {
			$sql = $this->db->prepare( $sql, $this->bindings );
		}

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

		return $result !== false;
	}

	/**
	 * Execute a DELETE query.
	 *
	 * @since 1.0.0
	 *
	 * @return bool True if the query was successful, false otherwise.
	 */
	public function delete() {

		// Prevent accidental deletion of all rows or if table does not exist.
		if ( empty( $this->wheres ) || ! DB::table_exists( $this->table ) ) {
			return false;
		}

		$sql = $this->build_delete();

		if ( $this->bindings ) {
			$sql = $this->db->prepare( $sql, $this->bindings );
		}

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

		return $result !== false;
	}

	/**
	 * Get the SQL string.
	 *
	 * @since 1.0.0
	 *
	 * @return string
	 */
	public function to_sql() {

		return $this->build_select();
	}

	/**
	 * Get the SQL string with embedded bindings.
	 *
	 * @since 1.0.0
	 *
	 * @return string
	 */
	public function to_raw_sql() {

		$sql = $this->to_sql();

		if ( $this->bindings ) {
			$sql = $this->db->prepare( $sql, $this->bindings );
		}

		return $sql;
	}

	/**
	 * Build the SELECT SQL statement.
	 *
	 * @since 1.0.0
	 *
	 * @return string
	 */
	private function build_select() { // phpcs:ignore Generic.Metrics.CyclomaticComplexity.TooHigh, Generic.Metrics.NestingLevel.MaxExceeded

		$this->bindings = [];

		$sql  = 'SELECT ' . $this->build_columns();
		$sql .= ' FROM ' . $this->get_table_with_prefix( $this->table );

		if ( $this->joins ) {
			$sql .= $this->build_joins();
		}
		if ( $this->wheres ) {
			$sql .= ' WHERE ' . $this->build_where( $this->wheres, $this->bindings );
		}
		if ( $this->groups ) {
			$sql .= ' GROUP BY ' . implode( ', ', array_map( [ $this, 'prepare_column' ], $this->groups ) );
		}
		if ( $this->orders ) {
			$sql .= ' ORDER BY ' . $this->build_order_by();
		}
		if ( $this->limit !== null ) {
			$sql .= ' LIMIT ' . intval( $this->limit );
		}
		if ( $this->offset !== null ) {
			$sql .= ' OFFSET ' . intval( $this->offset );
		}

		return $sql . ';';
	}

	/**
	 * Build the columns part of the SELECT statement.
	 *
	 * @since 1.0.0
	 *
	 * @return string
	 */
	private function build_columns() {

		if ( $this->columns === [ '*' ] ) {
			return "{$this->get_table_with_prefix( $this->table )}.*";
		}

		$columns = array_map(
			function ( $column ) {

				if ( $column === 'COUNT(*)' ) {
					return 'COUNT(*) as count';
				} elseif ( $column === 'COUNT(DISTINCT id)' ) {
					return "COUNT(DISTINCT {$this->get_table_with_prefix( $this->table )}.`id`) as count";
				}

				return $this->prepare_column( $column );
			},
			$this->columns
		);

		return implode( ', ', $columns );
	}

	/**
	 * Build the JOIN clauses.
	 *
	 * @since 1.0.0
	 *
	 * @return string
	 */
	private function build_joins() {

		$sql = '';

		foreach ( $this->joins as $join ) {
			$sql .= sprintf(
				' %s JOIN %s ON %s %s %s',
				$this->get_allowed_join_type( $join['type'] ),
				$this->get_table_with_prefix( $join['table'] ),
				$this->prepare_column( $join['first'] ),
				$this->get_allowed_join_operator( $join['operator'] ),
				$this->prepare_column( $join['second'] )
			);
		}

		return $sql;
	}

	/**
	 * Recursively build the WHERE clause and collect bindings.
	 *
	 * @since 1.0.0
	 *
	 * @param array $wheres    The where conditions.
	 * @param array &$bindings Reference to the bindings array.
	 *
	 * @return string
	 */
	private function build_where( $wheres, &$bindings ) { // phpcs:ignore Generic.Metrics.CyclomaticComplexity.TooHigh, Generic.Metrics.NestingLevel.MaxExceeded, Generic.Metrics.CyclomaticComplexity.MaxExceeded

		$sql = '';

		foreach ( $wheres as $index => $where ) {
			if ( $index > 0 ) {
				$sql .= ' ' . $where['boolean'] . ' ';
			}

			if ( $where['type'] === 'nested' ) {
				$sql .= '(' . $this->build_where( $where['wheres'], $bindings ) . ')';
			} elseif ( $where['type'] === 'basic' ) {
				$column      = $this->prepare_column( $where['column'] );
				$operator    = $this->get_allowed_operator( $where['operator'] );
				$placeholder = $this->get_allowed_placeholder( $where['placeholder'] );

				// Handle IN and NOT IN operators with array values.
				if (
					in_array( $operator, [ 'IN', 'NOT IN' ], true ) &&
					is_array( $where['value'] )
				) {
					// Optimize single-value IN/NOT IN to =/!= for performance.
					if ( count( $where['value'] ) === 1 ) {
						$sql .= $column . ' ' . ( $operator === 'IN' ? '=' : '!=' ) . ' ' . $placeholder;
					} else {
						$placeholders = implode( ', ', array_fill( 0, count( $where['value'] ), $placeholder ) );

						$sql .= $column . ' ' . $operator . ' (' . $placeholders . ')';
					}

					foreach ( $where['value'] as $val ) {
						$bindings[] = $val;
					}
				} elseif ( in_array( $operator, [ 'LIKE', 'NOT LIKE' ], true ) ) {
					$sql .= $column . ' ' . $operator . ' ' . $placeholder;

					// Replace any % in the value with a temporary placeholder.
					$value = str_replace( '%', '--WILDCARD--', $where['value'] );
					// Escape value.
					$value = $this->db->esc_like( $value );
					// Restore the user's % wildcards.
					$value      = str_replace( '--WILDCARD--', '%', $value );
					$bindings[] = $value;
				} elseif ( in_array( $operator, [ 'IS NULL', 'IS NOT NULL' ], true ) ) {
					$sql .= $column . ' ' . $operator;
				} else {
					$sql       .= $column . ' ' . $operator . ' ' . $placeholder;
					$bindings[] = $where['value'];
				}
			}
		}

		return $sql;
	}

	/**
	 * Build the ORDER BY clause.
	 *
	 * @since 1.0.0
	 *
	 * @return string
	 */
	private function build_order_by() {

		$clauses = [];

		foreach ( $this->orders as $order ) {
			$column    = $this->prepare_column( $order['column'] );
			$direction = $this->get_allowed_order_direction( $order['direction'] );
			$clauses[] = $column . ' ' . $direction;
		}

		return implode( ', ', $clauses );
	}

	/**
	 * Build the INSERT SQL statement for multiple rows.
	 *
	 * @since 1.0.0
	 *
	 * @param array $rows         Array of associative arrays (rows) to insert.
	 * @param array $placeholders Associative array of column => placeholder (e.g., '%d', '%s', '%f').
	 *
	 * @return string
	 */
	private function build_insert( $rows, $placeholders ) {

		$this->bindings = [];

		$first_row = reset( $rows );
		$columns   = array_keys( $first_row );

		// Prepare placeholders for the columns.
		$column_placeholders = [];

		foreach ( $columns as $column ) {
			$column_placeholders[] = isset( $placeholders[ $column ] ) ? $this->get_allowed_placeholder( $placeholders[ $column ] ) : '%s';
		}

		// Build VALUES part for all rows.
		$values_sql = [];

		foreach ( $rows as $row ) {
			$values_sql[] = '(' . implode( ', ', $column_placeholders ) . ')';

			foreach ( $columns as $column ) {
				$this->bindings[] = $row[ $column ];
			}
		}

		$table   = $this->get_table_with_prefix( $this->table );
		$columns = implode( ', ', array_map( [ $this, 'prepare_column' ], $columns ) );
		$values  = implode( ', ', $values_sql );

		$sql = "INSERT INTO {$table} ({$columns}) VALUES {$values}";

		return $sql . ';';
	}

	/**
	 * Build the UPSERT SQL statement for multiple rows.
	 *
	 * @since 1.0.0
	 *
	 * @param array $rows           Array of associative arrays (rows) to insert.
	 * @param array $update_columns Columns to update on duplicate key.
	 * @param array $placeholders   Associative array of column => placeholder (e.g., '%d', '%s', '%f').
	 *
	 * @return string
	 */
	private function build_upsert( $rows, $update_columns, $placeholders ) {

		$sql = rtrim( $this->build_insert( $rows, $placeholders ), ';' );

		$update_clauses = [];

		foreach ( $update_columns as $col ) {
			$update_clauses[] = $this->prepare_column( $col ) . ' = VALUES(' . $this->prepare_column( $col ) . ')';
		}

		$sql .= ' ON DUPLICATE KEY UPDATE ' . implode( ', ', $update_clauses );

		return $sql . ';';
	}

	/**
	 * Build the DELETE SQL statement.
	 *
	 * @since 1.0.0
	 *
	 * @return string
	 */
	private function build_delete() {

		$this->bindings = [];

		$sql = 'DELETE FROM ' . $this->get_table_with_prefix( $this->table );

		if ( $this->wheres ) {
			$sql .= ' WHERE ' . $this->build_where( $this->wheres, $this->bindings );
		}

		if ( $this->limit !== null ) {
			$sql .= ' LIMIT ' . intval( $this->limit );
		}

		return $sql . ';';
	}

	/**
	 * Get the table name with WordPress prefix.
	 *
	 * @since 1.0.0
	 *
	 * @param string $table Table name (without prefix).
	 *
	 * @return string
	 */
	private function get_table_with_prefix( $table ) {

		$prefix = $this->prefix;

		if ( strpos( $table, $prefix ) === 0 ) {
			return $this->sanitize_identifier( $table );
		}

		return $this->sanitize_identifier( $prefix . $table );
	}

	/**
	 * Validate and return a safe SQL operator.
	 *
	 * @since 1.0.0
	 *
	 * @param string|null $operator Operator to validate.
	 *
	 * @return string
	 */
	private function get_allowed_operator( $operator ) {

		$operator = strtoupper( $operator );

		return in_array( $operator, self::$allowed_operators, true ) ? $operator : '=';
	}

	/**
	 * Validate and return a safe SQL operator for JOIN clauses.
	 *
	 * @since 1.0.0
	 *
	 * @param string|null $operator Operator to validate.
	 *
	 * @return string
	 */
	private function get_allowed_join_operator( $operator ) {

		return in_array( $operator, self::$allowed_join_operators, true ) ? $operator : '=';
	}

	/**
	 * Validate and return a safe SQL join type.
	 *
	 * @since 1.0.0
	 *
	 * @param string|null $type Join type to validate.
	 *
	 * @return string
	 */
	private function get_allowed_join_type( $type ) {

		$type = strtoupper( $type );

		return in_array( $type, self::$allowed_join_types, true ) ? $type : 'INNER';
	}

	/**
	 * Validate and return a safe SQL value placeholder.
	 *
	 * @since 1.0.0
	 *
	 * @param string $placeholder Placeholder to validate.
	 *
	 * @return string
	 */
	private function get_allowed_placeholder( $placeholder ) {

		return in_array( $placeholder, self::$allowed_placeholders, true ) ? $placeholder : '%s';
	}

	/**
	 * Validate and return a safe ORDER BY direction.
	 *
	 * @since 1.0.0
	 *
	 * @param string $direction Direction to validate.
	 *
	 * @return string
	 */
	private function get_allowed_order_direction( $direction ) {

		$dir = strtoupper( $direction );

		return in_array( $dir, self::$allowed_order_directions, true ) ? $dir : 'ASC';
	}

	/**
	 * Prepare a column name for use in SQL.
	 *
	 * @since 1.0.0
	 *
	 * @param string $column Column name.
	 *
	 * @return string
	 */
	private function prepare_column( $column ) {

		// Split by dot for table.column support.
		$parts = explode( '.', $column );

		// If more than two parts, only use the first two (table.column).
		if ( count( $parts ) > 2 ) {
			$parts = array_slice( $parts, 0, 2 );
		}

		$prepared_parts = [];

		foreach ( $parts as $i => $part ) {
			// If this is the table part (first part in table.column), add plugin prefix if not present.
			if ( $i === 0 && count( $parts ) === 2 && strpos( $part, $this->prefix ) !== 0 ) {
				$part = $this->prefix . $part;
			}

			$prepared_parts[] = $this->sanitize_identifier( $part );
		}

		return implode( '.', $prepared_parts );
	}

	/**
	 * Sanitize an identifier by wrapping it in backticks and removing invalid characters.
	 *
	 * @since 1.0.0
	 *
	 * @param string $identifier Identifier to sanitize.
	 *
	 * @return string
	 */
	private function sanitize_identifier( $identifier ) {

		// Remove all non-alphanumeric characters except underscores.
		$identifier = preg_replace( '/[^a-zA-Z0-9_]+/', '', $identifier );

		return "`$identifier`";
	}

	/**
	 * Return a cloned instance of this QueryBuilder.
	 *
	 * @since 1.0.0
	 *
	 * @return QueryBuilder
	 */
	public function clone() {

		return clone $this;
	}
}
