<?php
/**
 * SQL for each advanced order search type.
 *
 * @package AdminPowerups
 */

namespace SmartWP\AdminPowerups\Modules\AdvancedOrderSearch;

use Automattic\WooCommerce\Utilities\OrderUtil;
use SmartWP\AdminPowerups\Support\MetaMask;
use SmartWP\AdminPowerups\Support\Orders;

defined( 'ABSPATH' ) || exit;

/**
 * Builds a prepared WHERE fragment on the HPOS orders table for one search type.
 *
 * Every fragment is either `1=0` (no match possible) or a condition on the orders table,
 * mostly `id IN (subquery)`, so it slots into WooCommerce's own search WHERE.
 *
 * @since 1.0.0
 */
class SearchQuery {

	/**
	 * Options for this search.
	 *
	 * @var array{tolerance: float, meta_keys: string[], decimal: string, masked_meta: string[]}
	 */
	private $options;

	/**
	 * Orders missing from the order–product lookup table (false = not read yet).
	 *
	 * @var int[]|null|false
	 */
	private $missing = false;

	/**
	 * Why the last search matched nothing on purpose ('' = it ran normally).
	 *
	 * @var string
	 */
	private $notice = '';

	/**
	 * Most products a SKU search passes to the orders query.
	 */
	const MAX_PRODUCTS = 5000;

	/**
	 * Most order IDs a custom metadata search resolves up front (see resolve_limit()).
	 */
	const MAX_RESOLVED = 10000;

	/**
	 * Resolved `id IN ( … )` conditions, keyed by the md5 of their prepared subquery.
	 *
	 * @var array<string, string>
	 */
	private $resolved = array();

	/**
	 * Constructor.
	 *
	 * @since 1.0.0
	 * @param array<string, mixed> $options tolerance (float), meta_keys (string[] allowed keys; empty = any),
	 *                                      decimal (separator), masked_meta (string[] wildcard key patterns
	 *                                      the current user may not search).
	 */
	public function __construct( array $options = array() ) {
		$strings       = static fn( $values ) => array_values( array_filter( (array) $values, static fn( $v ) => is_string( $v ) && '' !== $v ) );
		$this->options = array(
			'tolerance'   => (float) ( $options['tolerance'] ?? 0.0 ),
			'meta_keys'   => $strings( $options['meta_keys'] ?? array() ),
			'decimal'     => (string) ( $options['decimal'] ?? '.' ),
			'masked_meta' => $strings( $options['masked_meta'] ?? array() ),
		);
	}

	/**
	 * Why the last search was refused (e.g. a SKU too broad to check every order), or ''.
	 *
	 * @since 1.0.0
	 * @return string Plain text.
	 */
	public function notice() {
		return $this->notice;
	}

	/**
	 * Search types this class knows, in display order.
	 *
	 * @since 1.0.0
	 * @return array<string, string> Key => label.
	 */
	public static function types() {
		return array(
			'order_number'    => __( 'Order number', 'admin-powerups-for-woocommerce' ),
			'customer'        => __( 'Customer', 'admin-powerups-for-woocommerce' ),
			'billing_email'   => __( 'Billing email', 'admin-powerups-for-woocommerce' ),
			'billing_phone'   => __( 'Billing phone', 'admin-powerups-for-woocommerce' ),
			'company'         => __( 'Company', 'admin-powerups-for-woocommerce' ),
			'product'         => __( 'Product', 'admin-powerups-for-woocommerce' ),
			'sku'             => __( 'SKU', 'admin-powerups-for-woocommerce' ),
			'variation'       => __( 'Variation', 'admin-powerups-for-woocommerce' ),
			'exact_total'     => __( 'Exact total', 'admin-powerups-for-woocommerce' ),
			'total_range'     => __( 'Total range', 'admin-powerups-for-woocommerce' ),
			'coupon'          => __( 'Coupon', 'admin-powerups-for-woocommerce' ),
			'shipping_method' => __( 'Shipping method', 'admin-powerups-for-woocommerce' ),
			'payment_method'  => __( 'Payment method', 'admin-powerups-for-woocommerce' ),
			'transaction_id'  => __( 'Transaction ID', 'admin-powerups-for-woocommerce' ),
			'status'          => __( 'Order status', 'admin-powerups-for-woocommerce' ),
			'customer_id'     => __( 'Customer ID', 'admin-powerups-for-woocommerce' ),
			'meta'            => __( 'Custom metadata', 'admin-powerups-for-woocommerce' ),
			'notes'           => __( 'Order notes', 'admin-powerups-for-woocommerce' ),
		);
	}

	/**
	 * Types that match text anywhere in a value (`LIKE %term%`) and so can't use indexes.
	 *
	 * @since 1.0.0
	 * @return string[]
	 */
	public static function slow_types() {
		return array( 'customer', 'billing_email', 'billing_phone', 'company', 'product', 'sku', 'variation', 'coupon', 'shipping_method', 'payment_method', 'transaction_id', 'meta', 'notes' );
	}

	/**
	 * WHERE fragment for a type and term.
	 *
	 * @since 1.0.0
	 * @param string $type Search type key (without prefix).
	 * @param string $term Raw search term.
	 * @return string SQL fragment ('' when the type is unknown).
	 */
	public function where( $type, $term ) {
		$term = trim( (string) $term );
		if ( '' === $term ) {
			return '1=0';
		}
		$method = 'where_' . $type;
		return method_exists( $this, $method ) ? $this->$method( $term ) : '';
	}

	/* ------------------------------------------------------------------ table helpers */

	/**
	 * Orders table (quoted).
	 *
	 * @return string
	 */
	private function orders() {
		return '`' . OrderUtil::get_table_for_orders() . '`';
	}

	/**
	 * Condition on an orders table column, prepared with the table as an identifier.
	 *
	 * @param string  $sql  Condition starting with "%i." for the orders table, e.g. "%i.total_amount = %f".
	 * @param mixed[] $args Values for the remaining placeholders.
	 * @return string
	 */
	private function on_orders( $sql, array $args ) {
		global $wpdb;
		return (string) $wpdb->prepare( $sql, array_merge( array( OrderUtil::get_table_for_orders() ), $args ) ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- $sql is a literal from this class with %i/%s/%d/%f placeholders only.
	}

	/**
	 * `LIKE %term%` value.
	 *
	 * @param string $term Term.
	 * @return string
	 */
	private function contains( $term ) {
		global $wpdb;
		return '%' . $wpdb->esc_like( $term ) . '%';
	}

	/**
	 * `orders.id IN ( $sql )`.
	 *
	 * @param string $sql Prepared subquery returning order IDs.
	 * @return string
	 */
	private function id_in( $sql ) {
		return $this->orders() . '.id IN ( ' . $sql . ' )';
	}

	/**
	 * Subquery: orders with an order item of $type whose name contains $term.
	 *
	 * @param string $item_type Item type (line_item, coupon, shipping).
	 * @param string $term      Term.
	 * @return string
	 */
	private function items_named( $item_type, $term ) {
		global $wpdb;
		return $wpdb->prepare(
			"SELECT order_id FROM {$wpdb->prefix}woocommerce_order_items WHERE order_item_type = %s AND order_item_name LIKE %s",
			$item_type,
			$this->contains( $term )
		);
	}

	/**
	 * `orders.id IN ( subquery )` with the subquery in a derived table, so MySQL runs it once
	 * instead of once per order (it can't merge a UNION or a multi-table join into the outer query).
	 *
	 * @param string $sql Prepared subquery returning `order_id`.
	 * @return string
	 */
	private function id_in_derived( $sql ) {
		// DISTINCT keeps MySQL/MariaDB from merging the derived table back into the outer query.
		return $this->id_in( 'SELECT order_id FROM ( SELECT DISTINCT order_id FROM ( ' . $sql . ' ) AS apwc_all ) AS apwc_ids' );
	}

	/**
	 * Subquery (order IDs): orders containing any of these products or variations.
	 *
	 * Uses WooCommerce's order–product lookup table (`wc_order_product_lookup`, indexed, filled by
	 * WooCommerce Analytics) and reads line item meta only for orders the lookup table doesn't
	 * have yet (new orders waiting for the Analytics import, or never imported). When too many
	 * orders are missing from it (Analytics off or history not imported) it reads line item meta
	 * for every order: correct, but slow on big stores.
	 *
	 * Parts are joined with UNION: wrap the result with id_in_derived() (an OR of several
	 * `IN ( subquery )` makes MySQL re-run them for every order).
	 *
	 * @param string $id_sql          Product IDs for the lookup table: a list of integers or a prepared subquery.
	 * @param string $meta_sql        The same IDs as strings for line item meta: a list of quoted strings or a prepared subquery.
	 * @param bool   $variations_only Match variation IDs only.
	 * @return string Prepared subquery returning `order_id`.
	 */
	private function products_sql( $id_sql, $meta_sql, $variations_only = false ) {
		global $wpdb;
		$keys  = $variations_only ? "'_variation_id'" : "'_product_id', '_variation_id'";
		$items = "SELECT oi.order_id FROM {$wpdb->prefix}woocommerce_order_items oi
			INNER JOIN {$wpdb->prefix}woocommerce_order_itemmeta im ON im.order_item_id = oi.order_item_id
			WHERE oi.order_item_type = 'line_item' AND im.meta_key IN ( {$keys} ) AND im.meta_value IN ( {$meta_sql} )";

		$missing = $this->orders_missing_from_lookup();
		if ( null === $missing ) {
			return $items;
		}
		$parts = array( "SELECT order_id FROM {$wpdb->prefix}wc_order_product_lookup WHERE variation_id IN ( {$id_sql} )" );
		if ( ! $variations_only ) {
			$parts[] = "SELECT order_id FROM {$wpdb->prefix}wc_order_product_lookup WHERE product_id IN ( {$id_sql} )";
		}
		if ( $missing ) {
			$parts[] = $items . ' AND oi.order_id IN ( ' . implode( ',', array_map( 'intval', $missing ) ) . ' )';
		}
		return implode( ' UNION ', $parts );
	}

	/**
	 * Same as products_sql(), for a list of IDs.
	 *
	 * @param int[] $ids             Product or variation IDs.
	 * @param bool  $variations_only Match variation IDs only.
	 * @return string Prepared subquery, or '' when there are no IDs.
	 */
	private function products_with_ids( array $ids, $variations_only = false ) {
		$ids = array_values( array_unique( array_filter( array_map( 'intval', $ids ) ) ) );
		if ( ! $ids ) {
			return '';
		}
		// Integers only; line item meta values are strings, so compare them as strings (no casts).
		return $this->products_sql( implode( ',', $ids ), "'" . implode( "','", $ids ) . "'", $variations_only );
	}

	/**
	 * Order statuses WooCommerce Analytics never imports (OrdersScheduler): orders in them never
	 * get lookup rows, so they don't count as missing.
	 */
	const NOT_IMPORTED_STATUSES = array( 'wc-auto-draft', 'auto-draft', 'trash' );

	/**
	 * Most orders missing from the order–product lookup table before product searches read every
	 * order's line items (the `apwc_order_search_lookup_fallback_limit` filter).
	 *
	 * @since 1.0.0
	 * @return int
	 */
	public static function lookup_fallback_limit() {
		/**
		 * Most orders missing from WooCommerce's order–product lookup table that product, SKU and
		 * variation ID searches still check one by one. Above this, those searches read every
		 * order's line items instead (slow on big stores).
		 *
		 * @since 1.0.0
		 * @param int $limit Order count.
		 */
		return max( 0, (int) apply_filters( 'apwc_order_search_lookup_fallback_limit', 2000 ) );
	}

	/**
	 * Orders (IDs) with no row in the order–product lookup table, or null when there are too many
	 * to list. Read once per search.
	 *
	 * Leaves out orders WooCommerce Analytics never imports (auto-drafts, trash) and orders with
	 * no product line items: neither can ever have lookup rows, and neither can match a product
	 * search through line item meta, so they must not push searches onto the slow path.
	 *
	 * @since 1.0.0
	 * @return int[]|null
	 */
	public function orders_missing_from_lookup() {
		global $wpdb;
		if ( false !== $this->missing ) {
			return $this->missing;
		}

		$limit = self::lookup_fallback_limit();

		// Anti-join first, in a derived table the LIMIT keeps from being merged; then one indexed
		// order_id lookup per missing order for a line item. An EXISTS / IN on the items table is
		// turned into a full scan of every order item: semi-join materialization on MariaDB,
		// a duplicate-weedout item scan on MySQL 8.4 (measured at ~100k orders: 167 → 93 ms on
		// MariaDB 10.11, 290 → 104 ms on MySQL 8.4). A scalar subquery on o itself would be
		// evaluated for every order before the anti-join, so it sits outside.
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery -- read once per search; lookup rows change with every order.
		$ids           = $wpdb->get_col(
			$wpdb->prepare(
				"SELECT m.id FROM (
					SELECT o.id FROM %i o LEFT JOIN %i l ON l.order_id = o.id
					WHERE o.type = %s AND o.status NOT IN ( %s, %s, %s ) AND l.order_id IS NULL
					LIMIT 18446744073709551615
				) m
				WHERE ( SELECT oi.order_item_id FROM %i oi WHERE oi.order_id = m.id AND oi.order_item_type = 'line_item' LIMIT 1 ) IS NOT NULL
				LIMIT %d",
				OrderUtil::get_table_for_orders(),
				$wpdb->prefix . 'wc_order_product_lookup',
				'shop_order',
				self::NOT_IMPORTED_STATUSES[0],
				self::NOT_IMPORTED_STATUSES[1],
				self::NOT_IMPORTED_STATUSES[2],
				$wpdb->prefix . 'woocommerce_order_items',
				$limit + 1
			)
		);
		$this->missing = count( $ids ) > $limit ? null : array_map( 'intval', $ids );
		return $this->missing;
	}

	/* ------------------------------------------------------------------ search types */

	/**
	 * Order number: the ID, or a custom number stored by sequential-number plugins.
	 *
	 * @param string $term Term.
	 * @return string
	 */
	private function where_order_number( $term ) {
		global $wpdb;
		$term  = ltrim( $term, '#' );
		$keys  = Orders::number_meta_keys();
		$parts = array();

		if ( ctype_digit( $term ) ) {
			$parts[] = $this->on_orders( '%i.id = %d', array( (int) $term ) );
		}
		if ( $keys ) {
			$parts[] = $this->id_in(
				$wpdb->prepare(
					'SELECT order_id FROM %i WHERE meta_key IN ( ' . implode( ',', array_fill( 0, count( $keys ), '%s' ) ) . ' ) AND meta_value = %s',
					array_merge( array( OrderUtil::get_table_for_order_meta() ), $keys, array( $term ) )
				)
			);
		}
		return $parts ? '( ' . implode( ' OR ', $parts ) . ' )' : '1=0';
	}

	/**
	 * Customer: billing/shipping name, or the account's name, username or email.
	 *
	 * @param string $term Term.
	 * @return string
	 */
	private function where_customer( $term ) {
		global $wpdb;
		$like = $this->contains( $term );
		$addr = $wpdb->prepare(
			"SELECT order_id FROM {$wpdb->prefix}wc_order_addresses WHERE CONCAT_WS( ' ', first_name, last_name ) LIKE %s",
			$like
		);
		$user = $wpdb->prepare(
			"SELECT ID FROM {$wpdb->users} WHERE display_name LIKE %s OR user_login LIKE %s OR user_email LIKE %s",
			$like,
			$like,
			$like
		);
		return '( ' . $this->id_in( $addr ) . ' OR ' . $this->orders() . '.customer_id IN ( ' . $user . ' ) )';
	}

	/**
	 * Billing email (partial match).
	 *
	 * @param string $term Term.
	 * @return string
	 */
	private function where_billing_email( $term ) {
		return $this->on_orders( '%i.billing_email LIKE %s', array( $this->contains( $term ) ) );
	}

	/**
	 * Billing phone: digits compared without spaces, dashes, dots or brackets.
	 *
	 * @param string $term Term.
	 * @return string
	 */
	private function where_billing_phone( $term ) {
		global $wpdb;
		$digits = preg_replace( '/\D/', '', $term );
		if ( strlen( $digits ) < 3 ) {
			return '1=0';
		}
		return $this->id_in(
			$wpdb->prepare(
				"SELECT order_id FROM {$wpdb->prefix}wc_order_addresses WHERE address_type = 'billing' AND REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(phone,' ',''),'-',''),'(',''),')',''),'.',''),'+','') LIKE %s",
				'%' . $wpdb->esc_like( $digits ) . '%'
			)
		);
	}

	/**
	 * Company (billing or shipping).
	 *
	 * @param string $term Term.
	 * @return string
	 */
	private function where_company( $term ) {
		global $wpdb;
		return $this->id_in(
			$wpdb->prepare( "SELECT order_id FROM {$wpdb->prefix}wc_order_addresses WHERE company LIKE %s", $this->contains( $term ) )
		);
	}

	/**
	 * Product: line item name, or a product ID.
	 *
	 * @param string $term Term.
	 * @return string
	 */
	private function where_product( $term ) {
		$named    = $this->items_named( 'line_item', $term );
		$products = ctype_digit( $term ) ? $this->products_with_ids( array( (int) $term ) ) : '';
		return '' === $products ? $this->id_in( $named ) : $this->id_in_derived( $named . ' UNION ' . $products );
	}

	/**
	 * SKU (current product SKUs, via the product lookup table).
	 *
	 * @param string $term Term.
	 * @return string
	 */
	private function where_sku( $term ) {
		global $wpdb;
		/**
		 * Most products a SKU search passes to the orders query as a list. Above it, a search that
		 * must read every order's line items (lookup table not filled) is refused.
		 *
		 * @since 1.0.0
		 * @param int $max Product count.
		 */
		$max  = max( 1, (int) apply_filters( 'apwc_order_search_sku_limit', self::MAX_PRODUCTS ) );
		$like = $this->contains( $term );
		// Products first (small, indexed table), then orders with those products.
		$ids = $wpdb->get_col( $wpdb->prepare( "SELECT product_id FROM {$wpdb->prefix}wc_product_meta_lookup WHERE sku LIKE %s LIMIT %d", $like, $max + 1 ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- one search, not worth caching.
		if ( ! $ids ) {
			return '1=0';
		}
		if ( count( $ids ) <= $max ) {
			// A literal ID list (never a subquery): bounded even when every order's items are read.
			$products = $this->products_with_ids( array_map( 'intval', $ids ) );
			return '' === $products ? '1=0' : $this->id_in_derived( $products );
		}
		if ( null === $this->orders_missing_from_lookup() ) {
			// Without the lookup table this would compare every line item with thousands of
			// products (minutes on big stores, holding a database connection): refuse instead.
			$this->notice = sprintf(
				/* translators: %s: number of products. */
				__( 'More than %s products match this SKU, too many to check every order. Type more of the SKU, or import historical data in WooCommerce → Analytics → Settings.', 'admin-powerups-for-woocommerce' ),
				number_format_i18n( $max )
			);
			return '1=0';
		}
		// Very broad term with the lookup table: MySQL resolves the products (indexed).
		return $this->id_in_derived(
			$this->products_sql(
				$wpdb->prepare( "SELECT product_id FROM {$wpdb->prefix}wc_product_meta_lookup WHERE sku LIKE %s", $like ),
				$wpdb->prepare( "SELECT CAST( product_id AS CHAR ) FROM {$wpdb->prefix}wc_product_meta_lookup WHERE sku LIKE %s", $like )
			)
		);
	}

	/**
	 * Variation: an attribute value on a line item (e.g. "Blue"), or a variation ID.
	 *
	 * @param string $term Term.
	 * @return string
	 */
	private function where_variation( $term ) {
		global $wpdb;
		if ( ctype_digit( $term ) ) {
			$products = $this->products_with_ids( array( (int) $term ), true );
			return '' === $products ? '1=0' : $this->id_in_derived( $products );
		}
		// Attribute values live only in line item meta (no index on values): this one stays a scan.
		// Masked keys (e.g. "license_token") are skipped so their values can't be guessed.
		list( $masked_sql, $masked_args ) = MetaMask::sql_exclusions( 'im.meta_key', $this->options['masked_meta'] );
		$sql                              = "SELECT oi.order_id FROM %i oi
				INNER JOIN %i im ON im.order_item_id = oi.order_item_id
				INNER JOIN %i iv ON iv.order_item_id = oi.order_item_id AND iv.meta_key = '_variation_id' AND iv.meta_value > 0
				WHERE oi.order_item_type = 'line_item' AND im.meta_key NOT LIKE %s AND im.meta_value LIKE %s" . $masked_sql;
		$args                             = array_merge(
			array(
				$wpdb->prefix . 'woocommerce_order_items',
				$wpdb->prefix . 'woocommerce_order_itemmeta',
				$wpdb->prefix . 'woocommerce_order_itemmeta',
				$wpdb->esc_like( '_' ) . '%',
				$this->contains( $term ),
			),
			$masked_args
		);
		return $this->id_in_derived( (string) $wpdb->prepare( $sql, $args ) ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- $sql is built from literals with %i/%s placeholders only.
	}

	/**
	 * Exact total, within the configured tolerance.
	 *
	 * @param string $term Term.
	 * @return string
	 */
	private function where_exact_total( $term ) {
		return $this->total_between( TotalQuery::parse( $term, (float) $this->options['tolerance'], $this->options['decimal'] ), $term );
	}

	/**
	 * Total range: "50-100", ">200", "<=20". A single amount matches exactly.
	 *
	 * @param string $term Term.
	 * @return string
	 */
	private function where_total_range( $term ) {
		return $this->total_between( TotalQuery::parse( $term, 0.0, $this->options['decimal'] ), $term );
	}

	/**
	 * BETWEEN condition for a parsed range.
	 *
	 * @param mixed  $range [min, max] floats, or null.
	 * @param string $term  Raw term (for the filter).
	 * @return string
	 */
	private function total_between( $range, $term ) {
		/**
		 * Filter the parsed total range for order search.
		 *
		 * @since 1.0.0
		 * @param array|null $range [min, max], or null when the term is not an amount.
		 * @param string     $term  Raw search term.
		 */
		$range = apply_filters( 'apwc_order_total_search_range', $range, $term );
		if ( ! is_array( $range ) || ! isset( $range[0], $range[1] ) || ! is_numeric( $range[0] ) || ! is_numeric( $range[1] ) || ! is_finite( (float) $range[0] ) || ! is_finite( (float) $range[1] ) ) {
			return '1=0'; // INF/NAN (e.g. from the filter) would print as a bare word in %f.
		}
		return $this->on_orders( '%i.total_amount BETWEEN %f AND %f', array( (float) $range[0], (float) $range[1] ) );
	}

	/**
	 * Coupon code.
	 *
	 * @param string $term Term.
	 * @return string
	 */
	private function where_coupon( $term ) {
		return $this->id_in( $this->items_named( 'coupon', $term ) );
	}

	/**
	 * Shipping method: name ("Flat rate") or method ID ("flat_rate").
	 *
	 * @param string $term Term.
	 * @return string
	 */
	private function where_shipping_method( $term ) {
		global $wpdb;
		$by_id = $wpdb->prepare(
			"SELECT oi.order_id FROM {$wpdb->prefix}woocommerce_order_items oi
			INNER JOIN {$wpdb->prefix}woocommerce_order_itemmeta im ON im.order_item_id = oi.order_item_id
			WHERE oi.order_item_type = 'shipping' AND im.meta_key = 'method_id' AND im.meta_value LIKE %s",
			$this->contains( $term )
		);
		return $this->id_in_derived( $this->items_named( 'shipping', $term ) . ' UNION ' . $by_id );
	}

	/**
	 * Payment method: gateway ID ("stripe") or title ("Credit card").
	 *
	 * @param string $term Term.
	 * @return string
	 */
	private function where_payment_method( $term ) {
		$like  = $this->contains( $term );
		$table = OrderUtil::get_table_for_orders();
		return $this->on_orders( '( %i.payment_method LIKE %s OR %i.payment_method_title LIKE %s )', array( $like, $table, $like ) );
	}

	/**
	 * Transaction ID (partial match).
	 *
	 * @param string $term Term.
	 * @return string
	 */
	private function where_transaction_id( $term ) {
		return $this->on_orders( '%i.transaction_id LIKE %s', array( $this->contains( $term ) ) );
	}

	/**
	 * Order status by label ("Processing") or slug ("processing", "wc-processing").
	 *
	 * @param string $term Term.
	 * @return string
	 */
	private function where_status( $term ) {
		global $wpdb;
		$needle  = strtolower( $term );
		$matches = array();
		foreach ( wc_get_order_statuses() as $slug => $label ) {
			if ( false !== strpos( strtolower( $label ), $needle ) || false !== strpos( $slug, $needle ) ) {
				$matches[] = $slug;
			}
		}
		if ( ! $matches ) {
			return '1=0';
		}
		return $wpdb->prepare(
			'%i.status IN ( ' . implode( ',', array_fill( 0, count( $matches ), '%s' ) ) . ' )',
			array_merge( array( OrderUtil::get_table_for_orders() ), $matches )
		);
	}

	/**
	 * Customer (user) ID.
	 *
	 * @param string $term Term.
	 * @return string
	 */
	private function where_customer_id( $term ) {
		$term = ltrim( $term, '#' );
		if ( ! ctype_digit( $term ) ) {
			return '1=0';
		}
		return $this->on_orders( '%i.customer_id = %d', array( (int) $term ) );
	}

	/**
	 * Custom metadata: "key=value", "key=" (key exists) or "value" (any allowed key).
	 *
	 * Keys matching `masked_meta` (sensitive keys the current user can't reveal in the Order meta
	 * inspector) are never searched, so their values can't be guessed a letter at a time. The
	 * key is checked in PHP (early exit) and the stored key again in SQL.
	 *
	 * @param string $term Term.
	 * @return string
	 */
	private function where_meta( $term ) {
		global $wpdb;
		$masked  = $this->options['masked_meta'];
		$allowed = $this->options['meta_keys'];
		$key     = '';
		$value   = $term;

		if ( false !== strpos( $term, '=' ) ) {
			list( $key, $value ) = array_map( 'trim', explode( '=', $term, 2 ) );
			if ( '' === $key || ( $allowed && ! in_array( $key, $allowed, true ) ) || MetaMask::matches( $key, $masked ) ) {
				return '1=0';
			}
		}
		if ( $allowed && $masked ) {
			$allowed = array_values( array_filter( $allowed, static fn( $k ) => ! MetaMask::matches( $k, $masked ) ) );
			if ( ! $allowed ) {
				return '1=0';
			}
		}

		$sql  = 'SELECT order_id FROM %i WHERE 1=1';
		$args = array( OrderUtil::get_table_for_order_meta() );
		if ( '' !== $key ) {
			$sql   .= ' AND meta_key = %s';
			$args[] = $key;
		} elseif ( $allowed ) {
			$sql .= ' AND meta_key IN ( ' . implode( ',', array_fill( 0, count( $allowed ), '%s' ) ) . ' )';
			$args = array_merge( $args, $allowed );
		}
		if ( '' !== $value ) {
			$sql   .= ' AND meta_value LIKE %s';
			$args[] = $this->contains( $value );
		}
		// Always also in SQL, on the stored key: the collation may fold a typed key onto a masked
		// one in ways the PHP check above doesn't know. See MetaMask. Last, so MySQL filters on
		// the cheap value LIKE before the REPLACE()-heavy key exclusions.
		list( $masked_sql, $masked_args ) = MetaMask::sql_exclusions( 'meta_key', $masked, $masked ? MetaMask::checked_keys( $masked ) : array() );
		$sql                             .= $masked_sql;
		$args                             = array_merge( $args, $masked_args );
		return $this->id_in_resolved( (string) $wpdb->prepare( $sql, $args ) ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- $sql is built above from literals with %i/%s placeholders only.
	}

	/**
	 * Most order IDs a custom metadata search resolves up front (the
	 * `apwc_order_search_meta_id_limit` filter; 0 = never resolve).
	 *
	 * @since 1.0.0
	 * @return int
	 */
	public static function resolve_limit() {
		/**
		 * Most matching orders a custom metadata search reads once and passes to the orders query
		 * as an ID list. WooCommerce runs the search twice per page (the page and its count), and
		 * the meta subquery can't use an index, so resolving it once halves the cost. Above this
		 * many matches the search keeps the subquery. 0 always keeps the subquery. Capped at
		 * 100,000 so the ID list stays a bounded size.
		 *
		 * @since 1.0.0
		 * @param int $limit Order count (default 10,000).
		 */
		return min( 100000, max( 0, (int) apply_filters( 'apwc_order_search_meta_id_limit', self::MAX_RESOLVED ) ) );
	}

	/**
	 * `orders.id IN ( … )` for a prepared subquery, with the matching order IDs read once (per
	 * exact subquery, for this object) and written as a literal list, or the subquery itself when
	 * there are more matches than resolve_limit() or the read failed.
	 *
	 * @param string $sql Prepared subquery returning `order_id`.
	 * @return string
	 */
	private function id_in_resolved( $sql ) {
		global $wpdb;
		$key = md5( $sql );
		if ( isset( $this->resolved[ $key ] ) ) {
			return $this->resolved[ $key ];
		}
		$limit = self::resolve_limit();
		$out   = $this->id_in( $sql );
		if ( $limit > 0 ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $sql is the subquery where_meta() prepared (literals with %i/%s placeholders); read once per search.
			$ids = $wpdb->get_col( $wpdb->prepare( 'SELECT DISTINCT order_id FROM ( ' . $sql . ' ) AS apwc_meta LIMIT %d', $limit + 1 ) );
			if ( '' === $wpdb->last_error ) { // Otherwise keep the subquery (it fails the same way, visibly).
				$ids = array_values( array_filter( array_map( 'intval', (array) $ids ) ) );
				if ( ! $ids ) {
					$out = '1=0';
				} elseif ( count( $ids ) <= $limit ) {
					$out = $this->orders() . '.id IN ( ' . implode( ',', $ids ) . ' )';
				}
			}
		}
		$this->resolved[ $key ] = $out;
		return $out;
	}

	/**
	 * Order notes (private and customer notes).
	 *
	 * @param string $term Term.
	 * @return string
	 */
	private function where_notes( $term ) {
		global $wpdb;
		return $this->id_in(
			$wpdb->prepare(
				"SELECT comment_post_ID FROM {$wpdb->comments} WHERE comment_type = 'order_note' AND comment_content LIKE %s",
				$this->contains( $term )
			)
		);
	}
}