<?php
/**
 * Cross-entity query builder and executor.
 */

namespace RawTypeOE\Free;

// Prevent direct access.
if ( ! defined( 'ABSPATH' ) ) {
	exit;
}

/**
 * Executes cross-entity queries against WooCommerce analytics lookup tables.
 */
class Cross_Entity_Query {

	/** Default maximum rows returned by cross-entity queries. */
	const DEFAULT_LIMIT = 10000;

	/** @var string[] */
	const ENTITIES = array( 'orders', 'customers', 'products' );

	/** @var \wpdb|null */
	private $wpdb;

	/** @var array */
	private $last_meta = array();

	public function __construct( $wpdb = null ) {
		if ( null !== $wpdb ) {
			$this->wpdb = $wpdb;
			return;
		}

		global $wpdb;

		if ( isset( $wpdb ) ) {
			$this->wpdb = $wpdb;
		}
	}

	/**
	 * Fetch rows for export-engine chunk processing.
	 *
	 * @param array $args Query arguments.
	 * @return array
	 */
	public function fetch( $args ) {
		$filters = isset( $args['filters'] ) && is_array( $args['filters'] ) ? $args['filters'] : array();
		$logic   = isset( $args['logic'] ) ? sanitize_key( $args['logic'] ) : 'and';
		$config  = isset( $args['cross_entity'] ) && is_array( $args['cross_entity'] ) ? $args['cross_entity'] : array();
		$limit   = isset( $args['limit'] ) ? absint( $args['limit'] ) : self::DEFAULT_LIMIT;
		$paged   = isset( $args['paged'] ) ? absint( $args['paged'] ) : 1;
		$offset  = $paged > 1 ? ( $paged - 1 ) * $limit : 0;
		$columns = isset( $args['columns'] ) && is_array( $args['columns'] ) ? $args['columns'] : array();

		$result = $this->query( $columns, $filters, $logic, $config, $limit, $offset );

		if ( is_wp_error( $result ) ) {
			return array();
		}

		return isset( $result['rows'] ) && is_array( $result['rows'] ) ? $result['rows'] : array();
	}

	/**
	 * Count rows for a cross-entity query.
	 *
	 * @param array $args Query arguments.
	 * @return int
	 */
	public function count( $args ) {
		$filters = isset( $args['filters'] ) && is_array( $args['filters'] ) ? $args['filters'] : array();
		$logic   = isset( $args['logic'] ) ? sanitize_key( $args['logic'] ) : 'and';
		$config  = isset( $args['cross_entity'] ) && is_array( $args['cross_entity'] ) ? $args['cross_entity'] : array();
		$columns = isset( $args['columns'] ) && is_array( $args['columns'] ) ? $args['columns'] : array();

		if ( ! $this->is_db_ready() ) {
			return 0;
		}

		$primary = isset( $config['primary_entity'] ) ? sanitize_key( $config['primary_entity'] ) : 'orders';
		$related = isset( $config['related_entities'] ) && is_array( $config['related_entities'] ) ? $config['related_entities'] : array();

		$validation = $this->validate_combination( $primary, $related );
		if ( is_wp_error( $validation ) ) {
			return 0;
		}

		$selected_entities = $this->build_selected_entities( $primary, $related );
		$column_keys       = $this->resolve_column_keys( $columns, $selected_entities, $primary );
		$row_entities      = $this->build_row_entities( $column_keys, $selected_entities, $primary );
		$logic             = in_array( $logic, array( 'and', 'or' ), true ) ? $logic : 'and';

		$query_parts = $this->build_query_parts( $column_keys, $selected_entities, $filters, $logic, $row_entities, $primary );

		$count_sql = "SELECT COUNT( DISTINCT {$query_parts['row_key']} ) {$query_parts['from']} {$query_parts['joins']} {$query_parts['where']}";
		$count_sql = trim( preg_replace( '/\s+/', ' ', $count_sql ) );
		$count_sql = $this->prepare_sql( $count_sql, $query_parts['values'] );

		$total = (int) $this->wpdb->get_var( $count_sql ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Prepared via prepare_sql().

		return max( 0, $total );
	}

	/**
	 * Normalize rows to selected columns.
	 *
	 * @param array  $items   Query result rows.
	 * @param array  $columns Requested columns.
	 * @param string $mode    Unused, kept for interface compatibility.
	 * @return array
	 */
	public function normalize( $items, $columns, $mode = '' ) { // phpcs:ignore Generic.CodeAnalysis.UnusedFunctionParameter.FoundAfterLastUsed
		if ( ! is_array( $items ) ) {
			return array();
		}

		$column_keys = $this->extract_column_keys( $columns );
		if ( empty( $column_keys ) ) {
			return $items;
		}

		$normalized = array();

		foreach ( $items as $row ) {
			if ( is_object( $row ) ) {
				$row = (array) $row;
			}

			if ( ! is_array( $row ) ) {
				continue;
			}

			$normalized_row = array();
			foreach ( $column_keys as $column_key ) {
				$normalized_row[ $column_key ] = isset( $row[ $column_key ] ) ? $row[ $column_key ] : '';
			}

			$normalized[] = $normalized_row;
		}

		return $normalized;
	}

	/**
	 * Execute cross-entity query.
	 *
	 * @param array  $columns Requested columns (array of keys or column objects).
	 * @param array  $filters Query builder filters.
	 * @param string $logic   Filter logic (and/or).
	 * @param array  $config  Cross-entity config.
	 * @param int    $limit   Requested row limit.
	 * @param int    $offset  Requested offset.
	 * @return array|\WP_Error
	 */
	public function query( $columns = array(), $filters = array(), $logic = 'and', $config = array(), $limit = self::DEFAULT_LIMIT, $offset = 0 ) {
		if ( ! $this->is_db_ready() ) {
			return new \WP_Error(
				'cross_entity_db_unavailable',
				__( 'Database connection is not available for cross-entity query.', 'rawtype-order-exporter-woocommerce' )
			);
		}

		$primary = isset( $config['primary_entity'] ) ? sanitize_key( $config['primary_entity'] ) : 'orders';
		$related = isset( $config['related_entities'] ) && is_array( $config['related_entities'] ) ? $config['related_entities'] : array();

		$validation = $this->validate_combination( $primary, $related );
		if ( is_wp_error( $validation ) ) {
			return $validation;
		}

		$selected_entities = $this->build_selected_entities( $primary, $related );
		$column_keys       = $this->resolve_column_keys( $columns, $selected_entities, $primary );
		$row_entities      = $this->build_row_entities( $column_keys, $selected_entities, $primary );
		$logic             = in_array( $logic, array( 'and', 'or' ), true ) ? $logic : 'and';
		$offset            = max( 0, absint( $offset ) );

		$max_limit = self::DEFAULT_LIMIT;

		$requested_limit = absint( $limit );
		if ( $requested_limit < 1 ) {
			$requested_limit = $max_limit;
		}

		$effective_limit = min( $requested_limit, $max_limit );

		$query_parts = $this->build_query_parts( $column_keys, $selected_entities, $filters, $logic, $row_entities, $primary );

		$count_sql = "SELECT COUNT( DISTINCT {$query_parts['row_key']} ) {$query_parts['from']} {$query_parts['joins']} {$query_parts['where']}";
		$count_sql = trim( preg_replace( '/\s+/', ' ', $count_sql ) );
		$count_sql = $this->prepare_sql( $count_sql, $query_parts['values'] );

		$total = (int) $this->wpdb->get_var( $count_sql ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Prepared via prepare_sql().
		if ( $total < 0 ) {
			$total = 0;
		}

		$rows_sql = "SELECT DISTINCT {$query_parts['row_key']} AS `__roe_row_key`, {$query_parts['select']} {$query_parts['from']} {$query_parts['joins']} {$query_parts['where']} ORDER BY {$query_parts['order_by']} LIMIT %d OFFSET %d";
		$rows_sql = trim( preg_replace( '/\s+/', ' ', $rows_sql ) );
		$rows_sql = $this->prepare_sql(
			$rows_sql,
			array_merge( $query_parts['values'], array( $effective_limit, $offset ) )
		);

		$result_type = defined( 'ARRAY_A' ) ? ARRAY_A : 'ARRAY_A';
		$rows        = $this->wpdb->get_results( $rows_sql, $result_type ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Prepared via prepare_sql().
		if ( ! is_array( $rows ) ) {
			$rows = array();
		}

		$rows = array_map( array( $this, 'sanitize_row' ), $rows );
		$rows = array_map( array( $this, 'strip_internal_columns' ), $rows );

		$limited = $total > ( $offset + $effective_limit );

		$this->last_meta = array(
			'primary_entity'    => $primary,
			'related_entities'  => $related,
			'selected_entities' => $selected_entities,
			'row_entities'      => $row_entities,
			'total'             => $total,
			'limit'             => $effective_limit,
			'limited'           => $limited,
		);

		return array(
			'rows'    => $rows,
			'total'   => $total,
			'limit'   => $effective_limit,
			'limited' => $limited,
		);
	}

	/**
	 * Get metadata for the last executed query.
	 *
	 * @return array
	 */
	public function get_last_meta() {
		return $this->last_meta;
	}

	/**
	 * Validate allowed entity combinations.
	 *
	 * @param string $primary Primary entity.
	 * @param array  $related Related entities.
	 * @return true|\WP_Error
	 */
	public function validate_combination( $primary, $related ) {
		$primary = sanitize_key( $primary );

		if ( ! in_array( $primary, self::ENTITIES, true ) ) {
			return new \WP_Error(
				'cross_entity_invalid_primary',
				__( 'Primary entity is invalid for cross-entity query.', 'rawtype-order-exporter-woocommerce' )
			);
		}

		$selected = $this->build_selected_entities( $primary, $related );

		$signature = $this->build_selection_signature( $selected );
		$allowed   = array(
			'customers|orders',
			'orders|products',
			'customers|orders|products',
		);

		if ( ! in_array( $signature, $allowed, true ) ) {
			return new \WP_Error(
				'cross_entity_invalid_combination',
				__( 'Invalid entity combination for cross-entity query. Orders must be part of the selection.', 'rawtype-order-exporter-woocommerce' )
			);
		}

		return true;
	}

	/**
	 * Build selected entities list.
	 *
	 * @param string $primary Primary entity.
	 * @param array  $related Related entities.
	 * @return array
	 */
	private function build_selected_entities( $primary, $related ) {
		$primary  = sanitize_key( $primary );
		$selected = array();

		if ( in_array( $primary, self::ENTITIES, true ) ) {
			$selected[] = $primary;
		}

		foreach ( (array) $related as $entity ) {
			$entity = sanitize_key( $entity );
			if ( in_array( $entity, self::ENTITIES, true ) && $entity !== $primary ) {
				$selected[] = $entity;
			}
		}

		$selected = array_values( array_unique( $selected ) );

		if ( count( $selected ) > 1 ) {
			$primary_entity = array_shift( $selected );
			sort( $selected );
			array_unshift( $selected, $primary_entity );
		}

		return $selected;
	}

	/**
	 * Build a normalized signature for validating entity combinations.
	 *
	 * @param array $selected_entities Selected entities.
	 * @return string
	 */
	private function build_selection_signature( $selected_entities ) {
		$signature_entities = array_values( array_unique( array_filter( (array) $selected_entities ) ) );
		sort( $signature_entities );

		return implode( '|', $signature_entities );
	}

	/**
	 * Resolve columns based on selection.
	 *
	 * @param array $columns           Requested columns.
	 * @param array $selected_entities Selected entities.
	 * @return string[]
	 */
	private function resolve_column_keys( $columns, $selected_entities, $primary = 'orders' ) {
		$column_map = $this->get_column_map();
		$keys       = $this->extract_column_keys( $columns );

		if ( empty( $keys ) ) {
			$keys = $this->get_default_columns( $selected_entities, $primary );
		}

		$resolved = array();
		foreach ( $keys as $column_key ) {
			if ( ! isset( $column_map[ $column_key ] ) ) {
				continue;
			}
			if ( ! $this->column_is_allowed_for_selection( $column_key, $selected_entities ) ) {
				continue;
			}
			$resolved[] = $column_key;
		}

		if ( empty( $resolved ) ) {
			$resolved = $this->get_default_columns( $selected_entities, $primary );
		}

		return array_values( array_unique( $resolved ) );
	}

	/**
	 * Resolve the entities that define a unique export row.
	 *
	 * The primary entity always defines the base grain. Related entities only
	 * become part of the row key when the visible column selection actually
	 * references them.
	 *
	 * @param array  $column_keys       Selected column keys.
	 * @param array  $selected_entities Selected entities.
	 * @param string $primary           Primary entity.
	 * @return array
	 */
	private function build_row_entities( $column_keys, $selected_entities, $primary ) {
		$row_entities = array();
		$primary      = sanitize_key( $primary );

		if ( in_array( $primary, $selected_entities, true ) ) {
			$row_entities[] = $primary;
		}

		foreach ( $column_keys as $column_key ) {
			$entity = $this->get_entity_for_column_key( $column_key );
			if ( empty( $entity ) ) {
				continue;
			}

			if ( ! in_array( $entity, $selected_entities, true ) || in_array( $entity, $row_entities, true ) ) {
				continue;
			}

			$row_entities[] = $entity;
		}

		if ( empty( $row_entities ) ) {
			$row_entities[] = 'orders';
		}

		return $row_entities;
	}

	/**
	 * Build SQL fragments for the query.
	 *
	 * @param array  $column_keys       Selected column keys.
	 * @param array  $selected_entities Selected entities.
	 * @param array  $filters           Applied filters.
	 * @param string $logic             Filter logic.
	 * @return array
	 */
	private function build_query_parts( $column_keys, $selected_entities, $filters, $logic, $row_entities, $primary ) {
		$column_map = $this->get_column_map();
		$select     = array();

		foreach ( $column_keys as $column_key ) {
			if ( ! isset( $column_map[ $column_key ] ) ) {
				continue;
			}
			$alias    = sanitize_key( $column_key );
			$select[] = $column_map[ $column_key ] . ' AS `' . $alias . '`';
		}

		if ( empty( $select ) ) {
			$select[] = 'os.order_id AS `order_id`';
		}

		$needs_customer         = in_array( 'customers', $selected_entities, true ) || $this->filters_require_customer_join( $filters );
		$needs_customer_metrics = $this->columns_require_customer_metrics( $column_keys ) || $this->filters_require_customer_metrics( $filters );
		$needs_product          = in_array( 'products', $selected_entities, true ) || $this->filters_require_product_join( $filters ) || $this->columns_require_product_join( $column_keys );

		$tables = $this->get_tables();
		$from   = 'FROM ' . $tables['order_stats'] . ' os';
		$joins  = array();

		if ( $needs_customer || $this->columns_require_customer_join( $column_keys ) ) {
			$joins[] = 'LEFT JOIN ' . $tables['customer_lookup'] . ' cl ON cl.customer_id = os.customer_id';
		}

		if ( $needs_customer_metrics ) {
			$joins[] = 'LEFT JOIN ( ' . $this->get_customer_metrics_subquery( $tables ) . ' ) cm ON cm.customer_id = os.customer_id';
		}

		if ( $needs_product ) {
			$joins[] = 'LEFT JOIN ' . $tables['order_product_lookup'] . ' opl ON opl.order_id = os.order_id';
			$joins[] = 'LEFT JOIN ' . $tables['posts'] . ' p ON p.ID = opl.product_id';
			$joins[] = "LEFT JOIN {$tables['postmeta']} sku_meta ON sku_meta.post_id = opl.product_id AND sku_meta.meta_key = '_sku'";
			$joins[] = "LEFT JOIN {$tables['postmeta']} price_meta ON price_meta.post_id = opl.product_id AND price_meta.meta_key = '_price'";
			$joins[] = 'LEFT JOIN ( ' . $this->get_product_category_subquery( $tables ) . ' ) pc ON pc.product_id = opl.product_id';
		}

		$where_data = $this->build_where_clause( $filters, $logic );

		if ( in_array( 'products', $selected_entities, true ) ) {
			$where_data['clauses'][] = 'opl.product_id IS NOT NULL';
		}

		$where = '';
		if ( ! empty( $where_data['clauses'] ) ) {
			$where = 'WHERE ' . implode( ' AND ', $where_data['clauses'] );
		}

		return array(
			'select'   => implode( ', ', $select ),
			'from'     => $from,
			'joins'    => implode( ' ', $joins ),
			'where'    => $where,
			'values'   => $where_data['values'],
			'row_key'  => $this->build_row_key_expression( $row_entities ),
			'order_by' => $this->build_order_by_clause( $primary ),
		);
	}

	/**
	 * Build WHERE clause and values.
	 *
	 * @param array  $filters Filters from query builder.
	 * @param string $logic   AND/OR logic.
	 * @return array
	 */
	private function build_where_clause( $filters, $logic ) {
		$filter_clauses = array();
		$values         = array();

		foreach ( (array) $filters as $filter ) {
			if ( ! is_array( $filter ) || empty( $filter['type'] ) ) {
				continue;
			}

			$type = sanitize_key( $filter['type'] );

			switch ( $type ) {
				case 'date':
					$data = $this->build_date_filter_clause( $filter );
					break;
				case 'status':
					$data = $this->build_status_filter_clause( $filter );
					break;
				case 'category':
					$data = $this->build_category_filter_clause( $filter );
					break;
				case 'order_total':
					$data = $this->build_numeric_filter_clause( 'os.total_sales', $filter );
					break;
				case 'customer_total_spend':
					$data = $this->build_numeric_filter_clause( 'COALESCE(cm.total_spend, 0)', $filter );
					break;
				case 'customer_orders_count':
					$data = $this->build_numeric_filter_clause( 'COALESCE(cm.orders_count, 0)', $filter );
					break;
				case 'product_price':
					$data = $this->build_numeric_filter_clause( 'CAST(price_meta.meta_value AS DECIMAL(20,6))', $filter );
					break;
				default:
					$data = array(
						'clause' => '',
						'values' => array(),
					);
			}

			if ( ! empty( $data['clause'] ) ) {
				$filter_clauses[] = $data['clause'];
				$values           = array_merge( $values, $data['values'] );
			}
		}

		$clauses = array();
		if ( ! empty( $filter_clauses ) ) {
			$relation  = 'or' === $logic ? ' OR ' : ' AND ';
			$clauses[] = '( ' . implode( $relation, $filter_clauses ) . ' )';
		}

		return array(
			'clauses' => $clauses,
			'values'  => $values,
		);
	}

	/**
	 * Build date filter clause.
	 *
	 * @param array $filter Filter object.
	 * @return array
	 */
	private function build_date_filter_clause( $filter ) {
		$operator = isset( $filter['operator'] ) ? sanitize_key( $filter['operator'] ) : 'between';
		$value    = isset( $filter['value'] ) ? $filter['value'] : '';

		switch ( $operator ) {
			case 'is':
				$date = is_string( $value ) ? sanitize_text_field( $value ) : '';
				if ( ! $this->is_valid_date_string( $date ) ) {
					return array(
						'clause' => '',
						'values' => array(),
					);
				}
				return array(
					'clause' => 'DATE(os.date_created) = %s',
					'values' => array( $date ),
				);
			case 'after':
				$date = is_string( $value ) ? sanitize_text_field( $value ) : '';
				if ( ! $this->is_valid_date_string( $date ) ) {
					return array(
						'clause' => '',
						'values' => array(),
					);
				}
				return array(
					'clause' => 'DATE(os.date_created) > %s',
					'values' => array( $date ),
				);
			case 'before':
				$date = is_string( $value ) ? sanitize_text_field( $value ) : '';
				if ( ! $this->is_valid_date_string( $date ) ) {
					return array(
						'clause' => '',
						'values' => array(),
					);
				}
				return array(
					'clause' => 'DATE(os.date_created) < %s',
					'values' => array( $date ),
				);
			case 'between':
			default:
				if ( ! is_array( $value ) || 2 !== count( $value ) ) {
					return array(
						'clause' => '',
						'values' => array(),
					);
				}
				$start = sanitize_text_field( $value[0] );
				$end   = sanitize_text_field( $value[1] );
				if ( ! $this->is_valid_date_string( $start ) || ! $this->is_valid_date_string( $end ) ) {
					return array(
						'clause' => '',
						'values' => array(),
					);
				}
				return array(
					'clause' => 'DATE(os.date_created) BETWEEN %s AND %s',
					'values' => array( $start, $end ),
				);
		}
	}

	/**
	 * Build status filter clause.
	 *
	 * @param array $filter Filter object.
	 * @return array
	 */
	private function build_status_filter_clause( $filter ) {
		$statuses = isset( $filter['value'] ) && is_array( $filter['value'] ) ? $filter['value'] : array();
		$statuses = $this->normalize_lookup_statuses( $statuses );

		if ( empty( $statuses ) ) {
			return array(
				'clause' => '',
				'values' => array(),
			);
		}

		$placeholders = implode( ', ', array_fill( 0, count( $statuses ), '%s' ) );

		return array(
			'clause' => 'os.status IN (' . $placeholders . ')',
			'values' => $statuses,
		);
	}

	/**
	 * Build category filter clause.
	 *
	 * @param array $filter Filter object.
	 * @return array
	 */
	private function build_category_filter_clause( $filter ) {
		$terms = isset( $filter['value'] ) && is_array( $filter['value'] ) ? $filter['value'] : array();
		$terms = array_values( array_filter( array_map( 'absint', $terms ) ) );

		if ( empty( $terms ) ) {
			return array(
				'clause' => '',
				'values' => array(),
			);
		}

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

		return array(
			'clause' => 'EXISTS ( SELECT 1 FROM ' . $this->get_tables()['term_relationships'] . ' tr_filter INNER JOIN ' . $this->get_tables()['term_taxonomy'] . " tt_filter ON tt_filter.term_taxonomy_id = tr_filter.term_taxonomy_id AND tt_filter.taxonomy = 'product_cat' WHERE tr_filter.object_id = opl.product_id AND tt_filter.term_id IN (" . $placeholders . ') )',
			'values' => $terms,
		);
	}

	/**
	 * Build a numeric filter clause.
	 *
	 * @param string $column SQL column expression.
	 * @param array  $filter Filter object.
	 * @return array
	 */
	private function build_numeric_filter_clause( $column, $filter ) {
		$operator = isset( $filter['operator'] ) ? sanitize_text_field( $filter['operator'] ) : '=';
		$value    = isset( $filter['value'] ) ? $filter['value'] : '';

		$allowed = array( '>', '<', '=', '>=', '<=', 'between' );
		if ( ! in_array( $operator, $allowed, true ) ) {
			$operator = '=';
		}

		if ( 'between' === $operator ) {
			if ( ! is_array( $value ) || 2 !== count( $value ) ) {
				return array(
					'clause' => '',
					'values' => array(),
				);
			}

			$start = floatval( $value[0] );
			$end   = floatval( $value[1] );

			if ( $start > $end ) {
				$tmp   = $start;
				$start = $end;
				$end   = $tmp;
			}

			return array(
				'clause' => $column . ' BETWEEN %f AND %f',
				'values' => array( $start, $end ),
			);
		}

		if ( '' === $value && 0 !== $value && '0' !== $value ) {
			return array(
				'clause' => '',
				'values' => array(),
			);
		}

		return array(
			'clause' => $column . ' ' . $operator . ' %f',
			'values' => array( floatval( $value ) ),
		);
	}

	/**
	 * Prepare SQL safely.
	 *
	 * @param string $sql  SQL template.
	 * @param array  $args Placeholder values.
	 * @return string
	 */
	private function prepare_sql( $sql, $args ) {
		if ( empty( $args ) || ! method_exists( $this->wpdb, 'prepare' ) ) {
			return $sql;
		}

		$prepared = call_user_func_array( array( $this->wpdb, 'prepare' ), array_merge( array( $sql ), $args ) );

		return is_string( $prepared ) ? $prepared : $sql;
	}

	/**
	 * Build the SQL expression used as the canonical row key.
	 *
	 * @param array $row_entities Entities that define a unique row.
	 * @return string
	 */
	private function build_row_key_expression( $row_entities ) {
		$expression_map = array(
			'customers' => 'COALESCE(cl.customer_id, 0)',
			'orders'    => 'os.order_id',
			'products'  => 'COALESCE(opl.product_id, 0)',
		);
		$parts          = array();

		foreach ( (array) $row_entities as $entity ) {
			if ( isset( $expression_map[ $entity ] ) ) {
				$parts[] = $expression_map[ $entity ];
			}
		}

		if ( empty( $parts ) ) {
			return 'os.order_id';
		}

		if ( 1 === count( $parts ) ) {
			return $parts[0];
		}

		return 'CONCAT_WS(\':\', ' . implode( ', ', $parts ) . ')';
	}

	/**
	 * Build a SQL-safe ORDER BY clause for DISTINCT row queries.
	 *
	 * The internal row key already reflects the primary entity and visible
	 * grain, so ordering by it avoids MySQL DISTINCT restrictions while keeping
	 * result sets deterministic.
	 *
	 * @param string $primary Primary entity.
	 * @return string
	 */
	private function build_order_by_clause( $primary ) { // phpcs:ignore Generic.CodeAnalysis.UnusedFunctionParameter.FoundAfterLastUsed
		return '__roe_row_key ASC';
	}

	/**
	 * Check whether a column key is valid for selected entities.
	 *
	 * @param string $column_key       Column key.
	 * @param array  $selected_entities Selected entities.
	 * @return bool
	 */
	private function column_is_allowed_for_selection( $column_key, $selected_entities ) {
		if ( 0 === strpos( $column_key, 'customer_' ) ) {
			return in_array( 'customers', $selected_entities, true );
		}

		if ( 0 === strpos( $column_key, 'product_' ) ) {
			return in_array( 'products', $selected_entities, true );
		}

		return true;
	}

	/**
	 * Resolve which entity owns a cross-entity column.
	 *
	 * @param string $column_key Column key.
	 * @return string
	 */
	private function get_entity_for_column_key( $column_key ) {
		$column_key = sanitize_key( $column_key );

		if ( 0 === strpos( $column_key, 'customer_' ) ) {
			return 'customers';
		}

		if ( 0 === strpos( $column_key, 'order_' ) ) {
			return 'orders';
		}

		if ( 0 === strpos( $column_key, 'product_' ) ) {
			return 'products';
		}

		return '';
	}

	/**
	 * Determine if selected columns require customer join.
	 *
	 * @param array $column_keys Column keys.
	 * @return bool
	 */
	private function columns_require_customer_join( $column_keys ) {
		foreach ( $column_keys as $column_key ) {
			if ( 0 === strpos( $column_key, 'customer_' ) ) {
				return true;
			}
		}

		return false;
	}

	/**
	 * Determine if selected columns require customer metrics.
	 *
	 * @param array $column_keys Column keys.
	 * @return bool
	 */
	private function columns_require_customer_metrics( $column_keys ) {
		foreach ( $column_keys as $column_key ) {
			if ( in_array( $column_key, array( 'customer_total_spent', 'customer_orders_count' ), true ) ) {
				return true;
			}
		}

		return false;
	}

	/**
	 * Determine if selected columns require product join.
	 *
	 * @param array $column_keys Column keys.
	 * @return bool
	 */
	private function columns_require_product_join( $column_keys ) {
		foreach ( $column_keys as $column_key ) {
			if ( 0 === strpos( $column_key, 'product_' ) ) {
				return true;
			}
		}

		return false;
	}

	/**
	 * Determine if filters need customer join.
	 *
	 * @param array $filters Filters.
	 * @return bool
	 */
	private function filters_require_customer_join( $filters ) {
		foreach ( (array) $filters as $filter ) {
			if ( ! is_array( $filter ) || empty( $filter['type'] ) ) {
				continue;
			}

			$type = sanitize_key( $filter['type'] );
			if ( in_array( $type, array( 'customer_total_spend', 'customer_orders_count' ), true ) ) {
				return true;
			}
		}

		return false;
	}

	/**
	 * Determine if filters need customer metrics aggregation.
	 *
	 * @param array $filters Filters.
	 * @return bool
	 */
	private function filters_require_customer_metrics( $filters ) {
		foreach ( (array) $filters as $filter ) {
			if ( ! is_array( $filter ) || empty( $filter['type'] ) ) {
				continue;
			}

			$type = sanitize_key( $filter['type'] );
			if ( in_array( $type, array( 'customer_total_spend', 'customer_orders_count' ), true ) ) {
				return true;
			}
		}

		return false;
	}

	/**
	 * Determine if filters need product join.
	 *
	 * @param array $filters Filters.
	 * @return bool
	 */
	private function filters_require_product_join( $filters ) {
		foreach ( (array) $filters as $filter ) {
			if ( ! is_array( $filter ) || empty( $filter['type'] ) ) {
				continue;
			}

			$type = sanitize_key( $filter['type'] );
			if ( in_array( $type, array( 'category', 'product_price' ), true ) ) {
				return true;
			}
		}

		return false;
	}

	/**
	 * Get table names.
	 *
	 * @return array
	 */
	private function get_tables() {
		$prefix = isset( $this->wpdb->prefix ) ? $this->wpdb->prefix : '';

		return array(
			'customer_lookup'      => $prefix . 'wc_customer_lookup',
			'order_stats'          => $prefix . 'wc_order_stats',
			'order_product_lookup' => $prefix . 'wc_order_product_lookup',
			'posts'                => isset( $this->wpdb->posts ) ? $this->wpdb->posts : $prefix . 'posts',
			'postmeta'             => isset( $this->wpdb->postmeta ) ? $this->wpdb->postmeta : $prefix . 'postmeta',
			'term_relationships'   => isset( $this->wpdb->term_relationships ) ? $this->wpdb->term_relationships : $prefix . 'term_relationships',
			'term_taxonomy'        => isset( $this->wpdb->term_taxonomy ) ? $this->wpdb->term_taxonomy : $prefix . 'term_taxonomy',
			'terms'                => isset( $this->wpdb->terms ) ? $this->wpdb->terms : $prefix . 'terms',
		);
	}

	/**
	 * Build an aggregate subquery for customer order metrics.
	 *
	 * WooCommerce no longer stores these rollups in wc_customer_lookup, so the
	 * cross-entity query resolves them from order stats directly.
	 *
	 * @param array $tables Table map.
	 * @return string
	 */
	private function get_customer_metrics_subquery( $tables ) {
		$paid_statuses      = $this->build_sql_string_list( Query_Builder::get_customer_metric_statuses( 'total_spent' ) );
		$countable_statuses = $this->build_sql_string_list( Query_Builder::get_customer_metric_statuses( 'order_count' ) );

		return sprintf(
			'SELECT customer_id, SUM(CASE WHEN status IN %s THEN total_sales ELSE 0 END) AS total_spend, COUNT(DISTINCT CASE WHEN status IN %s THEN order_id ELSE NULL END) AS orders_count FROM %s WHERE customer_id > 0 GROUP BY customer_id',
			$paid_statuses,
			$countable_statuses,
			$tables['order_stats']
		);
	}

	/**
	 * Build a grouped product category subquery to avoid row multiplication.
	 *
	 * @param array $tables Table map.
	 * @return string
	 */
	private function get_product_category_subquery( $tables ) {
		return sprintf(
			"SELECT tr.object_id AS product_id, GROUP_CONCAT(DISTINCT terms.name ORDER BY terms.name SEPARATOR ', ') AS category_names FROM %s tr INNER JOIN %s tt ON tt.term_taxonomy_id = tr.term_taxonomy_id AND tt.taxonomy = 'product_cat' INNER JOIN %s terms ON terms.term_id = tt.term_id GROUP BY tr.object_id",
			$tables['term_relationships'],
			$tables['term_taxonomy'],
			$tables['terms']
		);
	}

	/**
	 * Build a quoted SQL list from trusted status values.
	 *
	 * @param array $values Trusted SQL literal values.
	 * @return string
	 */
	private function build_sql_string_list( $values ) {
		$values = array_values(
			array_filter(
				array_map( 'sanitize_key', (array) $values )
			)
		);

		if ( empty( $values ) ) {
			$values = array( 'wc-processing', 'wc-completed' );
		}

		$values = array_map( 'esc_sql', $values );

		return "( '" . implode( "','", $values ) . "' )";
	}

	/**
	 * Get allowed column map.
	 *
	 * @return array
	 */
	private function get_column_map() {
		return array(
			'customer_id'           => 'cl.customer_id',
			'customer_name'         => "COALESCE(NULLIF(CONCAT(cl.first_name, ' ', cl.last_name), ' '), cl.username)",
			'customer_email'        => 'cl.email',
			'customer_total_spent'  => 'COALESCE(cm.total_spend, 0)',
			'customer_orders_count' => 'COALESCE(cm.orders_count, 0)',
			'order_id'              => 'os.order_id',
			'order_status'          => 'os.status',
			'order_total'           => 'os.total_sales',
			'order_date'            => 'os.date_created',
			'product_id'            => 'opl.product_id',
			'product_name'          => 'p.post_title',
			'product_sku'           => 'sku_meta.meta_value',
			'product_price'         => 'price_meta.meta_value',
			'product_category'      => 'COALESCE(pc.category_names, \'\')',
		);
	}

	/**
	 * Get default columns for a combination.
	 *
	 * @param array $selected_entities Selected entities.
	 * @return string[]
	 */
	private function get_default_columns( $selected_entities, $primary = 'orders' ) {
		$default_map = array(
			'customers' => array( 'customer_name', 'customer_email' ),
			'orders'    => array( 'order_id', 'order_status', 'order_total', 'order_date' ),
			'products'  => array( 'product_name', 'product_sku', 'product_category' ),
		);
		$defaults    = array();

		foreach ( $this->order_entities_by_primary( $selected_entities, $primary ) as $entity ) {
			if ( isset( $default_map[ $entity ] ) ) {
				$defaults = array_merge( $defaults, $default_map[ $entity ] );
			}
		}

		return array_values( array_unique( $defaults ) );
	}

	/**
	 * Order selected entities so the primary entity drives defaults and row keys.
	 *
	 * @param array  $selected_entities Selected entities.
	 * @param string $primary           Primary entity.
	 * @return array
	 */
	private function order_entities_by_primary( $selected_entities, $primary ) {
		$selected = array_values( array_unique( array_filter( (array) $selected_entities ) ) );
		$primary  = sanitize_key( $primary );
		$ordered  = array();

		if ( in_array( $primary, $selected, true ) ) {
			$ordered[] = $primary;
		}

		foreach ( $selected as $entity ) {
			if ( $entity !== $primary ) {
				$ordered[] = $entity;
			}
		}

		return $ordered;
	}

	/**
	 * Extract column keys from requested columns.
	 *
	 * @param array $columns Column list.
	 * @return string[]
	 */
	private function extract_column_keys( $columns ) {
		$keys = array();

		foreach ( (array) $columns as $column ) {
			if ( is_string( $column ) ) {
				$keys[] = sanitize_key( $column );
				continue;
			}

			if ( is_array( $column ) && isset( $column['key'] ) ) {
				$keys[] = sanitize_key( $column['key'] );
			}
		}

		return array_values( array_unique( array_filter( $keys ) ) );
	}

	/**
	 * Verify DB object has required methods.
	 *
	 * @return bool
	 */
	private function is_db_ready() {
		return is_object( $this->wpdb )
			&& method_exists( $this->wpdb, 'prepare' )
			&& method_exists( $this->wpdb, 'get_var' )
			&& method_exists( $this->wpdb, 'get_results' );
	}

	/**
	 * Validate YYYY-MM-DD date strings.
	 *
	 * @param string $value Date string.
	 * @return bool
	 */
	private function is_valid_date_string( $value ) {
		if ( ! is_string( $value ) || '' === $value ) {
			return false;
		}

		return (bool) preg_match( '/^\d{4}-\d{2}-\d{2}$/', $value );
	}

	/**
	 * Sanitize a result row.
	 *
	 * @param array|object $row Result row.
	 * @return array
	 */
	private function sanitize_row( $row ) {
		if ( is_object( $row ) ) {
			$row = (array) $row;
		}

		if ( ! is_array( $row ) ) {
			return array();
		}

		$sanitized = array();
		foreach ( $row as $key => $value ) {
			$sanitized_key = sanitize_key( $key );
			if ( is_scalar( $value ) || null === $value ) {
				$sanitized[ $sanitized_key ] = $value;
			} else {
				$sanitized[ $sanitized_key ] = wp_json_encode( $value );
			}
		}

		return $sanitized;
	}

	/**
	 * Remove internal columns used to stabilize SQL row uniqueness.
	 *
	 * @param array $row Sanitized row.
	 * @return array
	 */
	private function strip_internal_columns( $row ) {
		if ( isset( $row['__roe_row_key'] ) ) {
			unset( $row['__roe_row_key'] );
		}

		return $row;
	}

	/**
	 * Normalize order statuses for wc_order_stats lookups.
	 *
	 * @param array $statuses Raw status values.
	 * @return array
	 */
	private function normalize_lookup_statuses( $statuses ) {
		$normalized = array();

		foreach ( (array) $statuses as $status ) {
			$status = sanitize_key( $status );
			if ( '' === $status ) {
				continue;
			}

			if ( 0 !== strpos( $status, 'wc-' ) ) {
				$status = 'wc-' . $status;
			}

			$normalized[] = $status;
		}

		return array_values( array_unique( $normalized ) );
	}
}
