<?php
/**
 * Query builder.
 *
 * @package FormPilot
 * @since   1.0.0
 */

declare( strict_types = 1 );

namespace FormPilot\Database;

if ( ! defined( 'ABSPATH' ) ) {
	exit;
}

/**
 * Assembles a SELECT statement and the values bound to it.
 *
 * The builder never touches the database and never escapes anything: it
 * collects clauses that carry `%s` and `%d` placeholders alongside the values
 * those placeholders stand for, and hands both to the repository, which passes
 * them through `wpdb::prepare()`. Keeping the two together is what makes it
 * impossible to add a condition and forget its value.
 *
 * Identifiers are the one thing `prepare()` cannot bind, so table names come
 * from {@see Schema} and column names are whitelisted by the caller.
 *
 * @since 1.0.0
 */
final class QueryBuilder {

	/**
	 * WHERE fragments, each carrying its own placeholders.
	 *
	 * @var array<int, string>
	 */
	private array $clauses = array();

	/**
	 * Values bound to the clauses, in the order the placeholders appear.
	 *
	 * @var array<int, mixed>
	 */
	private array $params = array();

	/**
	 * Column to sort on.
	 *
	 * @var string
	 */
	private string $orderby = '';

	/**
	 * Sort direction.
	 *
	 * @var string
	 */
	private string $order = 'DESC';

	/**
	 * Secondary sort, already rendered as "column DIRECTION".
	 *
	 * @var string
	 */
	private string $secondary = '';

	/**
	 * Rows per page, 0 for no limit.
	 *
	 * @var int
	 */
	private int $limit = 0;

	/**
	 * Rows to skip.
	 *
	 * @var int
	 */
	private int $offset = 0;

	/**
	 * Constructor.
	 *
	 * @param string $table Prefixed table name, from {@see Schema::table()}.
	 */
	public function __construct( private string $table ) {}

	/**
	 * Adds a raw clause and the values it binds.
	 *
	 * Use this for anything the typed helpers cannot express, such as a
	 * subquery. The clause must carry a placeholder for every value.
	 *
	 * @param string            $clause SQL fragment with `%s` or `%d` placeholders.
	 * @param array<int, mixed> $values Values bound to those placeholders.
	 */
	public function where( string $clause, array $values = array() ): QueryBuilder {
		if ( '' === trim( $clause ) ) {
			return $this;
		}

		$this->clauses[] = $clause;

		foreach ( $values as $value ) {
			$this->params[] = $value;
		}

		return $this;
	}

	/**
	 * Adds an equality clause, skipping values that mean "no filter".
	 *
	 * An empty string and a zero are treated as absent, which is what a form
	 * filter left on "all" submits.
	 *
	 * @param string $column Column name. Must come from a whitelist.
	 * @param mixed  $value  Value to match.
	 * @param string $format Optional. Placeholder, `%s` or `%d`. Default '%s'.
	 */
	public function where_equals( string $column, mixed $value, string $format = '%s' ): QueryBuilder {
		if ( null === $value || '' === $value || 0 === $value ) {
			return $this;
		}

		return $this->where( sprintf( '%1$s = %2$s', $column, $format ), array( $value ) );
	}

	/**
	 * Restricts the result to a set of record IDs.
	 *
	 * @param string          $column Column holding the ID.
	 * @param array<int, int> $ids    IDs to match. An empty list adds nothing.
	 */
	public function where_in( string $column, array $ids ): QueryBuilder {
		$ids = array_values( array_filter( array_map( 'intval', $ids ) ) );

		if ( array() === $ids ) {
			return $this;
		}

		$placeholders = implode( ', ', array_fill( 0, count( $ids ), '%d' ) );

		return $this->where( sprintf( '%1$s IN ( %2$s )', $column, $placeholders ), $ids );
	}

	/**
	 * Matches a term against any of several columns.
	 *
	 * The term must already be wrapped in wildcards and escaped for LIKE; the
	 * repository does that with `esc_like()`. An empty term adds nothing.
	 *
	 * @param array<int, string> $columns Columns to search. Must come from a whitelist.
	 * @param string             $term    Escaped LIKE term.
	 */
	public function search( array $columns, string $term ): QueryBuilder {
		if ( '' === $term || array() === $columns ) {
			return $this;
		}

		$fragments = array();
		$values    = array();

		foreach ( $columns as $column ) {
			$fragments[] = sprintf( '%s LIKE %%s', $column );
			$values[]    = $term;
		}

		return $this->where( '( ' . implode( ' OR ', $fragments ) . ' )', $values );
	}

	/**
	 * Sets the sort column and direction.
	 *
	 * The column is checked against the whitelist because it cannot be bound as
	 * a placeholder; anything unrecognised falls back rather than reaching SQL.
	 *
	 * @param string             $column    Requested column.
	 * @param string             $direction Requested direction.
	 * @param array<int, string> $allowed   Columns that may be sorted on.
	 * @param string             $fallback  Column used when the request is not allowed.
	 */
	public function order_by( string $column, string $direction, array $allowed, string $fallback ): QueryBuilder {
		$this->orderby = in_array( $column, $allowed, true ) ? $column : $fallback;
		$this->order   = 'ASC' === strtoupper( $direction ) ? 'ASC' : 'DESC';

		return $this;
	}

	/**
	 * Adds a tie breaker to the sort.
	 *
	 * Needed wherever the primary sort is not unique: ordering fields by
	 * `menu_order` alone leaves rows that share a position in an order the
	 * database is free to change between queries.
	 *
	 * @param string             $column    Requested column.
	 * @param string             $direction Requested direction.
	 * @param array<int, string> $allowed   Columns that may be sorted on.
	 */
	public function then_by( string $column, string $direction, array $allowed ): QueryBuilder {
		if ( '' === $this->orderby || ! in_array( $column, $allowed, true ) ) {
			return $this;
		}

		$this->secondary = sprintf(
			'%1$s %2$s',
			$column,
			'ASC' === strtoupper( $direction ) ? 'ASC' : 'DESC'
		);

		return $this;
	}

	/**
	 * Limits the result to one page.
	 *
	 * @param int $per_page Rows per page.
	 * @param int $page     Page number, one based.
	 */
	public function paginate( int $per_page, int $page ): QueryBuilder {
		$this->limit  = max( 1, $per_page );
		$this->offset = max( 0, ( max( 1, $page ) - 1 ) * $this->limit );

		return $this;
	}

	/**
	 * Builds the SELECT statement.
	 *
	 * @param string $columns Optional. Columns to select. Default '*'.
	 */
	public function select_sql( string $columns = '*' ): string {
		$sql = sprintf( 'SELECT %1$s FROM %2$s%3$s', $columns, $this->table, $this->where_sql() );

		if ( '' !== $this->orderby ) {
			$sql .= sprintf( ' ORDER BY %1$s %2$s', $this->orderby, $this->order );

			if ( '' !== $this->secondary ) {
				$sql .= ', ' . $this->secondary;
			}
		}

		if ( $this->limit > 0 ) {
			$sql .= ' LIMIT %d OFFSET %d';
		}

		return $sql;
	}

	/**
	 * Builds the matching COUNT statement.
	 *
	 * Sorting and pagination are left off: they cannot change how many rows
	 * match, and a LIMIT would make the count wrong.
	 */
	public function count_sql(): string {
		return sprintf( 'SELECT COUNT(*) FROM %1$s%2$s', $this->table, $this->where_sql() );
	}

	/**
	 * Returns the values bound to a SELECT, pagination included.
	 *
	 * @return array<int, mixed> The values, in placeholder order.
	 */
	public function select_params(): array {
		$params = $this->params;

		if ( $this->limit > 0 ) {
			$params[] = $this->limit;
			$params[] = $this->offset;
		}

		return $params;
	}

	/**
	 * Returns the values bound to a COUNT.
	 *
	 * @return array<int, mixed> The values, in placeholder order.
	 */
	public function count_params(): array {
		return $this->params;
	}

	/**
	 * Returns an aggregate SELECT over the same filters.
	 *
	 * Lets a caller ask "how many of each" without rebuilding the WHERE clause by
	 * hand, which is the one way a listing and its counts drift apart.
	 *
	 * Both arguments are SQL fragments and are inserted as given, so they must be
	 * literals written in the calling code. Neither may carry a request value:
	 * values belong in the WHERE clause, where they are bound.
	 *
	 * @param string $columns  Columns and aggregates to select.
	 * @param string $group_by Optional. Columns to group by. Default none.
	 */
	public function aggregate_sql( string $columns, string $group_by = '' ): string {
		$sql = sprintf( 'SELECT %1$s FROM %2$s%3$s', $columns, $this->table, $this->where_sql() );

		return '' === $group_by ? $sql : $sql . ' GROUP BY ' . $group_by;
	}

	/**
	 * Builds the WHERE clause.
	 *
	 * @return string The clause, with a leading space, or an empty string.
	 */
	private function where_sql(): string {
		return array() === $this->clauses ? '' : ' WHERE ' . implode( ' AND ', $this->clauses );
	}
}