2meet-data-optimizer/includes/query/class-tmdo-query-router.php

<?php
/**
 * Zone A Query Router for flat-column hot table rewrites.
 *
 * @package TMDO
 */

declare(strict_types=1);

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

/**
 * Zone A Query Router — rewrites meta_query to JOIN flat-column hot tables.
 *
 * Unlike the HPCT-inherited query interceptors (which each handle one specific
 * post type and table), the Query Router dynamically handles ANY post type
 * that has Zone A (hot) fields registered in the Schema Registry.
 *
 * Key advantage over KV tables:
 *   KV table: N meta_query conditions = N LEFT JOINs
 *   Flat table: N conditions = 1 LEFT JOIN + N WHERE clauses
 *
 * Pattern:
 *   1. pre_get_posts  — detect meta_query keys that map to hot columns;
 *                       strip them and store in wpdo_hot_clauses query var.
 *   2. posts_join     — LEFT JOIN wpdo_hot_{post_type} once per post type.
 *   3. posts_where    — append WHERE conditions against hot table columns.
 *   4. posts_groupby  — ensure GROUP BY wp_posts.ID.
 */
class TMDO_Query_Router {

	/**
	 * Query var key for storing extracted hot clauses.
	 */
	private const QUERY_VAR = 'wpdo_hot_clauses';

	/**
	 * Register WP_Query filter hooks.
	 */
	public function register_hooks(): void {
		add_action( 'pre_get_posts', array( $this, 'pre_get_posts' ), 10, 1 );
		add_filter( 'posts_join', array( $this, 'posts_join' ), 10, 2 );
		add_filter( 'posts_where', array( $this, 'posts_where' ), 10, 2 );
		add_filter( 'posts_groupby', array( $this, 'posts_groupby' ), 10, 2 );
	}

	/**
	 * Extract meta_query clauses that can be served by hot tables.
	 *
	 * Separated from the hook callback so it can be called and asserted directly
	 * in unit tests without triggering a full WP_Query lifecycle.
	 *
	 * @param array    $raw_meta_query Raw meta_query array from WP_Query.
	 * @param string[] $post_types     Post types being queried.
	 * @return array{hot_clauses: array<string, list<array>>, remaining: array} Extracted and leftover clauses.
	 */
	public static function extract_hot_clauses( array $raw_meta_query, array $post_types ): array {
		$registry    = TMDO_Schema_Registry::instance();
		$hot_clauses = array();
		$remaining   = array();

		foreach ( $raw_meta_query as $k => $clause ) {
			if ( 'relation' === $k || ! is_array( $clause ) || ! isset( $clause['key'] ) ) {
				$remaining[ $k ] = $clause;
				continue;
			}

			$matched = false;
			foreach ( $post_types as $pt ) {
				$field = $registry->get_field( $pt, $clause['key'] );
				if ( $field && 'hot' === $field['zone'] && TMDO_Routing_Predicate::should_query_from_zone( $pt, 'hot' ) ) {
					$hot_clauses[ $pt ][] = array(
						'column'  => $field['column'],
						'value'   => $clause['value'] ?? '',
						'compare' => strtoupper( trim( $clause['compare'] ?? '=' ) ),
						'type'    => strtoupper( trim( $clause['type'] ?? 'CHAR' ) ),
					);
					$matched              = true;
					break;
				}
			}

			if ( ! $matched ) {
				$remaining[ $k ] = $clause;
			}
		}

		return array(
			'hot_clauses' => $hot_clauses,
			'remaining'   => $remaining,
		);
	}

	/**
	 * Detect hot-table clauses in meta_query and stash them for later hooks.
	 *
	 * @param \WP_Query $query The WP_Query object.
	 * @return void
	 */
	public function pre_get_posts( \WP_Query $query ): void {
		if ( is_admin() && ! wp_doing_ajax() ) {
			return;
		}

		$post_types = (array) $query->get( 'post_type' );
		if ( empty( $post_types ) ) {
			return;
		}

		$raw_meta_query = (array) $query->get( 'meta_query' );
		if ( empty( $raw_meta_query ) ) {
			return;
		}

		$result = self::extract_hot_clauses( $raw_meta_query, $post_types );

		if ( empty( $result['hot_clauses'] ) ) {
			return;
		}

		$remaining = $result['remaining'];
		if ( isset( $raw_meta_query['relation'] ) && ! isset( $remaining['relation'] ) ) {
			$remaining['relation'] = $raw_meta_query['relation'];
		}

		$query->set( 'meta_query', $remaining );
		$query->set( self::QUERY_VAR, $result['hot_clauses'] );
	}

	/**
	 * LEFT JOIN hot tables for each post type with extracted clauses.
	 *
	 * @param string    $join  Current JOIN SQL.
	 * @param \WP_Query $query The WP_Query object.
	 * @return string Modified JOIN SQL.
	 */
	public function posts_join( ?string $join, \WP_Query $query ): string {
		$join        = (string) ( $join ?? '' );
		$hot_clauses = $query->get( self::QUERY_VAR );
		if ( empty( $hot_clauses ) || ! is_array( $hot_clauses ) ) {
			return $join;
		}

		global $wpdb;

		foreach ( $hot_clauses as $post_type => $clauses ) {
			$alias = $this->table_alias( $post_type );

			if ( false !== strpos( $join, "`{$alias}`" ) ) {
				continue;
			}

			$table = TMDO_Zone_Hot::table( $post_type );
			$join .= " LEFT JOIN `{$table}` AS `{$alias}`"
					. " ON (`{$wpdb->posts}`.`ID` = `{$alias}`.`post_id`)";
		}

		return $join;
	}

	/**
	 * Append WHERE conditions for hot table columns.
	 *
	 * @param string    $where Current WHERE SQL.
	 * @param \WP_Query $query The WP_Query object.
	 * @return string Modified WHERE SQL.
	 */
	public function posts_where( ?string $where, \WP_Query $query ): string {
		$where       = (string) ( $where ?? '' );
		$hot_clauses = $query->get( self::QUERY_VAR );
		if ( empty( $hot_clauses ) || ! is_array( $hot_clauses ) ) {
			return $where;
		}

		foreach ( $hot_clauses as $post_type => $clauses ) {
			$alias = $this->table_alias( $post_type );

			foreach ( $clauses as $clause ) {
				$col     = '`' . $alias . '`.`' . esc_sql( $clause['column'] ) . '`';
				$compare = $this->sanitize_compare( $clause['compare'] );
				$type    = $clause['type'];
				$value   = $clause['value'];

				$where .= $this->build_condition( $col, $compare, $type, $value );
			}
		}

		return $where;
	}

	/**
	 * Ensure GROUP BY to prevent duplicate rows from JOINs.
	 *
	 * @param string    $groupby Current GROUP BY SQL.
	 * @param \WP_Query $query   The WP_Query object.
	 * @return string Modified GROUP BY SQL.
	 */
	public function posts_groupby( ?string $groupby, \WP_Query $query ): string {
		$groupby     = (string) ( $groupby ?? '' );
		$hot_clauses = $query->get( self::QUERY_VAR );
		if ( empty( $hot_clauses ) || ! is_array( $hot_clauses ) ) {
			return $groupby;
		}

		global $wpdb;
		if ( '' === trim( $groupby ) ) {
			$groupby = "`{$wpdb->posts}`.`ID`";
		}

		return $groupby;
	}

	// ── Private helpers ───────────────────────────────────────────────────

	/**
	 * Generate a unique alias for the hot table JOIN.
	 *
	 * @param string $post_type Post type slug.
	 * @return string SQL table alias.
	 */
	private function table_alias( string $post_type ): string {
		return 'wpdo_hot_' . sanitize_key( $post_type );
	}

	/**
	 * Build a single WHERE condition with prepared values.
	 *
	 * @param string $col     Column reference (escaped).
	 * @param string $compare Comparison operator.
	 * @param string $type    Meta type for placeholder selection.
	 * @param mixed  $value   Value(s) to compare against.
	 * @return string SQL WHERE condition fragment.
	 */
	private function build_condition( string $col, string $compare, string $type, $value ): string {
		global $wpdb;

		$ph = $this->placeholder( $type );

		switch ( $compare ) {
			case 'IN':
			case 'NOT IN':
				$vals = array_values( (array) $value );
				if ( empty( $vals ) ) {
					return ' AND 1=0';
				}
				$phs = implode( ',', array_fill( 0, count( $vals ), $ph ) );
				// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare
				return $wpdb->prepare( " AND {$col} {$compare} ({$phs})", ...$vals );

			case 'BETWEEN':
			case 'NOT BETWEEN':
				$vals = array_values( (array) $value );
				// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare -- $col/$compare/$ph are sanitized; $ph is a literal placeholder string.
				return $wpdb->prepare( " AND {$col} {$compare} {$ph} AND {$ph}", $vals[0], $vals[1] ?? $vals[0] );

			case 'EXISTS':
				return " AND {$col} IS NOT NULL";

			case 'NOT EXISTS':
				return " AND {$col} IS NULL";

			default:
				// =, !=, >, >=, <, <=, LIKE, NOT LIKE
				// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare -- $col/$compare/$ph are sanitized; $ph is a literal placeholder string.
				return $wpdb->prepare( " AND {$col} {$compare} {$ph}", $value );
		}
	}

	/**
	 * Returns the SQL placeholder string for a given meta type.
	 *
	 * @param string $type Meta type (NUMERIC, SIGNED, etc. or CHAR).
	 * @return string SQL placeholder (%d or %s).
	 */
	private function placeholder( string $type ): string {
		$int_types = array( 'NUMERIC', 'SIGNED', 'UNSIGNED', 'INTEGER' );
		return in_array( $type, $int_types, true ) ? '%d' : '%s';
	}

	/**
	 * Sanitizes a comparison operator against an allowed list.
	 *
	 * @param string $compare Comparison operator string.
	 * @return string Sanitized comparison operator, defaults to '='.
	 */
	private function sanitize_compare( string $compare ): string {
		$allowed = array(
			'=',
			'!=',
			'>',
			'>=',
			'<',
			'<=',
			'LIKE',
			'NOT LIKE',
			'IN',
			'NOT IN',
			'BETWEEN',
			'NOT BETWEEN',
			'EXISTS',
			'NOT EXISTS',
		);
		return in_array( $compare, $allowed, true ) ? $compare : '=';
	}
}