<?php
/**
 * SQL query builder for joined block queries.
 *
 * Builds a single SQL query that joins multiple WordPress tables
 * based on the block's join configuration. Meta keys are included
 * as LEFT JOINs in the query — no post-execution meta calls.
 *
 * --------------------------------------------------------------------------
 *  SAFETY MODEL — read this before reviewing the SQL construction below
 * --------------------------------------------------------------------------
 *  Every helper method in this class returns an array shaped as
 *  { sql: string, args: array } where:
 *
 *    - `sql`  contains ONLY %i / %s / %d placeholders and literal SQL
 *             keywords (FROM, LEFT JOIN, ON, AND, ORDER BY, ASC, DESC, etc.).
 *             No identifiers and no values are ever interpolated into `sql`.
 *    - `args` contains the corresponding values that prepare() will
 *             substitute for those placeholders, in declaration order.
 *
 *  In execute() these fragments are concatenated into the final query
 *  string. Because every fragment contains only placeholders, the only
 *  things `wpdb::prepare()` ever sees as variable input are the args we
 *  pass — every table name, every column name, every alias, and every
 *  value goes through a placeholder:
 *
 *    %i — wpdb::prepare's identifier placeholder (added in WP 6.2). Wraps
 *         the value in backticks and validates it as a SQL identifier.
 *         Used for table names, column names, and alias names.
 *    %s — string value placeholder (escaped and quoted by prepare()).
 *    %d — integer value placeholder.
 *
 *  The plugin requires WP 6.6+, so %i is always available.
 *
 *  See https://developer.wordpress.org/reference/classes/wpdb/prepare/
 * --------------------------------------------------------------------------
 *
 * @package Wlconduit
 */

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

/**
 * Builds and executes joined SQL queries across WordPress tables.
 *
 * @since 1.0.0
 */
class Wlconduit_Query_Builder {

	/**
	 * Format-string templates for each supported join keyword.
	 *
	 * SQL keywords (INNER JOIN / LEFT JOIN / RIGHT JOIN) cannot be
	 * placeholderised — `%i` would wrap them in backticks and break the
	 * query. To avoid interpolating the keyword string into the SQL we
	 * select a *fully literal format string* for each supported join
	 * type from this hardcoded map. The map keys are validated against
	 * the join['join_type'] config; any unknown value falls back to the
	 * 'inner' template, so this class can never emit a join clause that
	 * isn't one of these three exact templates.
	 *
	 * @since 1.0.0
	 * @var array<string, string>
	 */
	private const JOIN_KEYWORD_FORMATS = array(
		'inner' => ' INNER JOIN %i %i ON %i.%i = %i.%i',
		'left'  => ' LEFT JOIN %i %i ON %i.%i = %i.%i',
		'right' => ' RIGHT JOIN %i %i ON %i.%i = %i.%i',
	);

	/**
	 * Build and execute a joined query with flat row pagination.
	 *
	 * @since 1.0.0
	 *
	 * @param array $block_config The block entry with block_type and config.
	 * @param array $arguments    The LLM-provided input arguments.
	 * @return array Paginated flat result set with { data, _pagination }.
	 */
	public function execute( array $block_config, array $arguments ): array {
		global $wpdb;

		$empty_result = array(
			'data'        => array(),
			'_pagination' => array(
				'page'        => 1,
				'per_page'    => 0,
				'total'       => 0,
				'total_pages' => 0,
			),
		);

		$primary_block = Wlconduit_Block_Registry::get( $block_config['block_type'] ?? '' );

		if ( ! $primary_block ) {
			return $empty_result;
		}

		$primary_joinable  = $primary_block->get_joinable_columns();
		$primary_field_map = $primary_block->get_field_column_map();
		$config            = $block_config['config'] ?? array();
		$joins             = $config['joins'] ?? array();
		$join_targets      = $this->resolve_join_targets( $joins );
		$join_field_maps   = $this->resolve_join_field_maps( $joins );

		// Each helper returns { sql, args }. The `sql` strings are placeholder-only;
		// the `args` arrays carry the actual identifiers/values that prepare()
		// will substitute. See class-level safety comment for details.
		$select_result  = $this->build_select( $primary_joinable, $primary_field_map, $config, $joins, $join_targets, $join_field_maps );
		$from_result    = $this->build_from_and_joins( $primary_joinable, $config, $joins, $join_targets );
		$where_result   = $this->build_where( $primary_joinable, $primary_field_map, $config, $arguments );
		$orderby_result = $this->build_orderby( $primary_field_map, $arguments );

		// $direction is one of the two literal strings 'DESC' or 'ASC', selected
		// by a hardcoded ternary against strtoupper of the input. It cannot
		// receive any other value, so interpolating it into the format string
		// below is provably safe — it is the only non-placeholder dynamic piece.
		$direction = 'DESC' === strtoupper( $arguments['order'] ?? 'DESC' ) ? 'DESC' : 'ASC';

		$max_per_page = $config['filters']['per_page']['max'] ?? 50;
		$per_page     = max( 1, min( absint( $arguments['per_page'] ?? 10 ), $max_per_page ) );
		$page         = max( 1, absint( $arguments['page'] ?? 1 ) );
		$offset       = ( $page - 1 ) * $per_page;

		// $select_sql / $from_sql / $where_sql / $orderby_sql each contain
		// ONLY %i / %s / %d placeholders and literal SQL keywords. Concatenating
		// them with literal "SELECT", "FROM", "WHERE", "ORDER BY" produces a
		// format string where every variable piece is a placeholder.
		$select_sql  = $select_result['sql'];
		$from_sql    = $from_result['sql'];
		$where_sql   = $where_result['sql'];
		$orderby_sql = $orderby_result['sql'];

		// COUNT query. Args order matches placeholder order in the format string:
		// FROM fragment first (its %i / %s placeholders are leftmost in the SQL),
		// then WHERE fragment (its placeholders come after).
		$count_args = array_merge( $from_result['args'], $where_result['args'] );

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Dynamic joined query; $from_sql/$where_sql contain only %i/%s/%d placeholders and SQL keywords (built by helpers above), every value flows through prepare() args.
		$total = (int) $wpdb->get_var(
			$wpdb->prepare(
				// $from_sql and $where_sql are placeholder-only — no values or identifiers are interpolated.
				// PHPCS only sees the literal portion of the format string and can't count placeholders inside the interpolated fragments, so UnfinishedPrepare and ReplacementsWrongNumber are false positives here.
				// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber -- $from_sql/$where_sql are placeholder-only fragments built by helpers; final SQL is fully parameterised.
				"SELECT COUNT(*) FROM {$from_sql} WHERE {$where_sql}",
				...$count_args
			)
		);

		$total_pages = $per_page > 0 ? (int) ceil( $total / $per_page ) : 0;

		// Main query args order: SELECT first (its %i placeholders are leftmost),
		// then FROM, then WHERE, then ORDER BY column, then LIMIT/OFFSET integers.
		$main_args = array_merge(
			$select_result['args'],
			$from_result['args'],
			$where_result['args'],
			$orderby_result['args'],
			array( $per_page, $offset )
		);

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Dynamic joined query; $select_sql/$from_sql/$where_sql/$orderby_sql contain only %i/%s/%d placeholders and SQL keywords (built by helpers above), every value flows through prepare() args.
		$rows = $wpdb->get_results(
			$wpdb->prepare(
				// All four interpolated fragments are placeholder-only. $direction
				// is provably one of two literal strings ('DESC' / 'ASC').
				// PHPCS only sees the literal portion of the format string and can't count placeholders inside the interpolated fragments, so UnfinishedPrepare and ReplacementsWrongNumber are false positives here.
				// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber -- $select_sql/$from_sql/$where_sql/$orderby_sql are placeholder-only fragments; $direction is a hardcoded literal ('DESC'|'ASC').
				"SELECT {$select_sql} FROM {$from_sql} WHERE {$where_sql} ORDER BY {$orderby_sql} {$direction} LIMIT %d OFFSET %d",
				...$main_args
			)
		);

		$data = array();
		foreach ( $rows as $row ) {
			$data[] = (array) $row;
		}

		return array(
			'data'        => $data,
			'_pagination' => array(
				'page'        => $page,
				'per_page'    => $per_page,
				'total'       => $total,
				'total_pages' => $total_pages,
			),
		);
	}

	/**
	 * Resolve join targets — get joinable column info for each join.
	 *
	 * @since 1.0.0
	 *
	 * @param array $joins The joins configuration array.
	 * @return array Array of joinable column info per join index.
	 */
	private function resolve_join_targets( array $joins ): array {
		$targets = array();

		foreach ( $joins as $join ) {
			$target_block = Wlconduit_Block_Registry::get( $join['target_type'] ?? '' );
			$targets[]    = $target_block ? $target_block->get_joinable_columns() : array();
		}

		return $targets;
	}

	/**
	 * Resolve field-to-column maps for each join's target block.
	 *
	 * @since 1.0.0
	 *
	 * @param array $joins The joins configuration array.
	 * @return array Array of field column maps per join index.
	 */
	private function resolve_join_field_maps( array $joins ): array {
		$maps = array();

		foreach ( $joins as $join ) {
			$target_block = Wlconduit_Block_Registry::get( $join['target_type'] ?? '' );
			$maps[]       = $target_block ? $target_block->get_field_column_map() : array();
		}

		return $maps;
	}

	/**
	 * Build the SELECT clause including meta keys as columns.
	 *
	 * Returns { sql, args } where every column reference uses %i placeholders
	 * for both the table alias and the column name, plus an explicit AS alias.
	 * No identifiers or values are interpolated into the returned `sql` —
	 * everything dynamic is in the args array, in placeholder order.
	 *
	 * @since 1.0.0
	 *
	 * @param array $primary_joinable  Primary block's joinable column info.
	 * @param array $primary_field_map Primary block's field-to-column map.
	 * @param array $config            Primary block's config.
	 * @param array $joins             Joins configuration.
	 * @param array $join_targets      Resolved join target info.
	 * @param array $join_field_maps   Resolved join field-to-column maps.
	 * @return array { sql: string, args: array } SELECT fragment with prepare args.
	 */
	private function build_select( array $primary_joinable, array $primary_field_map, array $config, array $joins, array $join_targets, array $join_field_maps ): array {
		$parts = array();
		$args  = array();

		// Primary ID (always selected for grouping): "p.<id_col> AS p_<id_col>"
		// expressed as %i.%i AS %i — three identifier placeholders, three args.
		$primary_id_col = $primary_joinable['id_column'];
		$parts[]        = '%i.%i AS %i';
		$args[]         = 'p';
		$args[]         = $primary_id_col;
		$args[]         = 'p_' . $primary_id_col;

		// Primary native fields. $col is mapped via the block's field_column_map,
		// which is a closed allowlist defined by the block class — never user input.
		foreach ( $config['fields'] ?? array() as $field ) {
			$col = $primary_field_map[ $field ] ?? null;
			if ( $col && $col !== $primary_id_col ) {
				$parts[] = '%i.%i AS %i';
				$args[]  = 'p';
				$args[]  = $col;
				$args[]  = 'p_' . $col;
			}
		}

		// Primary meta keys: "<pm_alias>.meta_value AS p_meta_<key>"
		// $safe_alias is built from a literal prefix + sanitize_key(meta_key);
		// %i then validates and backtick-wraps it for SQL.
		foreach ( $config['meta_keys'] ?? array() as $meta_key => $meta_config ) {
			if ( ! empty( $meta_config['as_field'] ) ) {
				$safe_alias = 'pm_' . sanitize_key( $meta_key );
				$parts[]    = '%i.meta_value AS %i';
				$args[]     = $safe_alias;
				$args[]     = 'p_meta_' . $meta_key;
			}
		}

		// Each join's target fields and meta.
		foreach ( $joins as $i => $join ) {
			if ( empty( $join_targets[ $i ] ) ) {
				continue;
			}

			$alias         = 'j' . $i;
			$target_id_col = $join_targets[ $i ]['id_column'] ?? 'ID';
			$field_map     = $join_field_maps[ $i ] ?? array();

			// Target ID for grouping: "<alias>.<id_col> AS <alias>_<id_col>"
			$parts[] = '%i.%i AS %i';
			$args[]  = $alias;
			$args[]  = $target_id_col;
			$args[]  = $alias . '_' . $target_id_col;

			foreach ( $join['target_fields'] ?? array() as $field ) {
				$col = $field_map[ $field ] ?? null;
				if ( $col && $col !== $target_id_col ) {
					$parts[] = '%i.%i AS %i';
					$args[]  = $alias;
					$args[]  = $col;
					$args[]  = $alias . '_' . $col;
				}
			}

			// Target meta keys: "<jXm_alias>.meta_value AS jX_meta_<key>"
			foreach ( $join['target_meta_keys'] ?? array() as $meta_key => $meta_config ) {
				if ( ! empty( $meta_config['as_field'] ) ) {
					$safe_alias = "j{$i}m_" . sanitize_key( $meta_key );
					$parts[]    = '%i.meta_value AS %i';
					$args[]     = $safe_alias;
					$args[]     = "j{$i}_meta_" . $meta_key;
				}
			}
		}

		// `, ` is a literal separator. The result string contains only
		// placeholders and literal commas/spaces — no values, no identifiers.
		return array(
			'sql'  => implode( ', ', $parts ),
			'args' => $args,
		);
	}

	/**
	 * Build the FROM and JOIN clauses including meta LEFT JOINs.
	 *
	 * Returns { sql, args }. The returned `sql` contains ONLY %i / %s
	 * placeholders and literal SQL keywords. Every table name, column
	 * name, alias, and meta_key value flows through the args array.
	 *
	 * @since 1.0.0
	 *
	 * @param array $primary_joinable Primary block's joinable column info.
	 * @param array $config           Primary block's config.
	 * @param array $joins            Joins configuration.
	 * @param array $join_targets     Resolved join target info.
	 * @return array { sql: string, args: array } FROM + JOIN clauses with prepare args.
	 */
	private function build_from_and_joins( array $primary_joinable, array $config, array $joins, array $join_targets ): array {
		// "FROM <primary_table> p" — table name flows through %i; "p" is a
		// hardcoded literal alias used everywhere else for the primary table.
		$sql  = '%i p';
		$args = array( $primary_joinable['table'] );

		// Primary block's meta LEFT JOINs. Each meta key produces one LEFT JOIN
		// clause of the shape:
		//   LEFT JOIN <meta_table> <alias> ON <alias>.<fk> = p.<id_col>
		//                                   AND <alias>.meta_key = <key>
		// Every identifier (meta_table, alias, fk, id_col) goes through %i; the
		// only value is the meta_key string, which goes through %s.
		$primary_meta_table = $primary_joinable['meta_table'];
		$primary_meta_fk    = $primary_joinable['meta_fk_column'];
		$primary_id_col     = $primary_joinable['id_column'];

		foreach ( $config['meta_keys'] ?? array() as $meta_key => $meta_config ) {
			if ( ! empty( $meta_config['as_field'] ) && $primary_meta_table ) {
				$safe_alias = 'pm_' . sanitize_key( $meta_key );
				$sql       .= ' LEFT JOIN %i %i ON %i.%i = %i.%i AND %i.meta_key = %s';
				$args[]     = $primary_meta_table;
				$args[]     = $safe_alias;
				$args[]     = $safe_alias;
				$args[]     = $primary_meta_fk;
				$args[]     = 'p';
				$args[]     = $primary_id_col;
				$args[]     = $safe_alias;
				$args[]     = $meta_key;
			}
		}

		// Table-to-table joins.
		foreach ( $joins as $i => $join ) {
			if ( empty( $join_targets[ $i ] ) ) {
				continue;
			}

			$alias        = 'j' . $i;
			$target_table = $join_targets[ $i ]['table'];
			$join_type    = $join['join_type'] ?? 'inner';

			// Select a fully-literal join clause template by join type. If the
			// admin saved a value not in our map, fall back to 'inner' — the
			// emitted SQL is one of three predetermined templates either way.
			$keyword_format = self::JOIN_KEYWORD_FORMATS[ $join_type ] ?? self::JOIN_KEYWORD_FORMATS['inner'];

			// $local_alias is either the literal "p" or "j" + an integer index.
			// absint() guarantees the index is a non-negative integer, so the
			// resulting alias is always either "p" or "j<digits>".
			$local_alias = 'p';
			if ( isset( $join['local_field_from'] ) && 'self' !== $join['local_field_from'] ) {
				$local_alias = 'j' . absint( $join['local_field_from'] );
			}

			// $local_field / $target_field originate from admin-configured join
			// config. sanitize_key() strips them down to lowercase letters,
			// digits, dash and underscore. %i then performs canonical SQL
			// identifier escaping (backtick-wrapping + validation).
			$local_field  = sanitize_key( $join['local_field'] ?? '' );
			$target_field = sanitize_key( $join['target_field'] ?? '' );

			// Append the join clause. $keyword_format is one of the three
			// templates from JOIN_KEYWORD_FORMATS — a class constant — so
			// concatenating it into $sql does not introduce any dynamic value.
			$sql   .= $keyword_format;
			$args[] = $target_table;
			$args[] = $alias;
			$args[] = $alias;
			$args[] = $target_field;
			$args[] = $local_alias;
			$args[] = $local_field;

			// Optional post-type filter when joining to a Posts target.
			if ( 'posts' === ( $join['target_type'] ?? '' ) && ! empty( $join['target_post_type'] ) ) {
				$sql   .= ' AND %i.post_type = %s';
				$args[] = $alias;
				$args[] = $join['target_post_type'];
			}

			// Target meta LEFT JOINs.
			$target_meta_table = $join_targets[ $i ]['meta_table'] ?? '';
			$target_id_col     = $join_targets[ $i ]['id_column'] ?? 'ID';
			$target_fk         = $join_targets[ $i ]['meta_fk_column'] ?? '';

			foreach ( $join['target_meta_keys'] ?? array() as $meta_key => $meta_config ) {
				if ( ! empty( $meta_config['as_field'] ) && $target_meta_table ) {
					$safe_alias = "j{$i}m_" . sanitize_key( $meta_key );
					$sql       .= ' LEFT JOIN %i %i ON %i.%i = %i.%i AND %i.meta_key = %s';
					$args[]     = $target_meta_table;
					$args[]     = $safe_alias;
					$args[]     = $safe_alias;
					$args[]     = $target_fk;
					$args[]     = $alias;
					$args[]     = $target_id_col;
					$args[]     = $safe_alias;
					$args[]     = $meta_key;
				}
			}
		}

		return array(
			'sql'  => $sql,
			'args' => $args,
		);
	}

	/**
	 * Build the WHERE clause from the primary block's config and arguments.
	 *
	 * Returns { sql, args } where every column reference uses %i and every
	 * value uses %s or %d. The returned `sql` is a chain of placeholder-only
	 * conditions joined by literal " AND ".
	 *
	 * @since 1.0.0
	 *
	 * @param array $primary_joinable  Primary block's joinable column info.
	 * @param array $primary_field_map Primary block's field-to-column map.
	 * @param array $config            Primary block's config.
	 * @param array $arguments         The LLM-provided input arguments.
	 * @return array { sql: string, args: array } WHERE clause with prepare args.
	 */
	private function build_where( array $primary_joinable, array $primary_field_map, array $config, array $arguments ): array {
		$conditions = array( '1=1' );
		$args       = array();

		// Post-type filter — value is parameterised; "publish" status is a literal.
		if ( ! empty( $config['post_type'] ) ) {
			$conditions[] = 'p.post_type = %s';
			$args[]       = $config['post_type'];
			$conditions[] = "p.post_status = 'publish'";
		}

		$id_col = $primary_joinable['id_column'];

		// "p.<id_col> = <id>" — column name via %i, value via %d.
		if ( ! empty( $arguments['id'] ) ) {
			$conditions[] = 'p.%i = %d';
			$args[]       = $id_col;
			$args[]       = $arguments['id'];
		}

		// "p.<title_col> = <title>" — column name via %i, value via %s.
		$title_col = $primary_field_map['title'] ?? '';
		if ( ! empty( $arguments['title'] ) && $title_col ) {
			$conditions[] = 'p.%i = %s';
			$args[]       = $title_col;
			$args[]       = $arguments['title'];
		}

		// "p.<author_col> = <author>" — column name via %i, value via %d.
		$author_col = $primary_field_map['author'] ?? '';
		if ( ! empty( $arguments['author'] ) && $author_col ) {
			$conditions[] = 'p.%i = %d';
			$args[]       = $author_col;
			$args[]       = $arguments['author'];
		}

		// Date filters validate format upfront, then pass the value through %s.
		if ( ! empty( $arguments['date_after'] ) && preg_match( '/^\d{4}-\d{2}-\d{2}$/', $arguments['date_after'] ) ) {
			$conditions[] = 'p.post_date >= %s';
			$args[]       = $arguments['date_after'];
		}

		if ( ! empty( $arguments['date_before'] ) && preg_match( '/^\d{4}-\d{2}-\d{2}$/', $arguments['date_before'] ) ) {
			$conditions[] = 'p.post_date <= %s';
			$args[]       = $arguments['date_before'];
		}

		return array(
			'sql'  => implode( ' AND ', $conditions ),
			'args' => $args,
		);
	}

	/**
	 * Build ORDER BY clause using the primary block's field-to-column map.
	 *
	 * Returns { sql, args }. The chosen column is validated against the
	 * block's allowed columns list before being passed through %i — even
	 * if validation were bypassed, %i would still backtick-wrap it.
	 *
	 * @since 1.0.0
	 *
	 * @param array $primary_field_map Primary block's field-to-column map.
	 * @param array $arguments         The LLM-provided input arguments.
	 * @return array { sql: string, args: array } ORDER BY column fragment with prepare args.
	 */
	private function build_orderby( array $primary_field_map, array $arguments ): array {
		$field           = $arguments['orderby'] ?? 'date';
		$allowed_columns = array_values( $primary_field_map );

		// Map friendly field name to column via the block's map.
		$column = $primary_field_map[ $field ] ?? $field;

		// Allowlist enforcement: if $column isn't a value defined by the block's
		// field_column_map, fall back to the first allowed column (or 'ID').
		if ( ! in_array( $column, $allowed_columns, true ) ) {
			$first  = reset( $allowed_columns );
			$column = $first ? $first : 'ID';
		}

		// "p.<column>" — column name via %i.
		return array(
			'sql'  => 'p.%i',
			'args' => array( $column ),
		);
	}
}