<?php

namespace wpsolr\core\classes\engines\postgresql;

use wpsolr\core\classes\engines\WPSOLR_AbstractSearchClient;
use wpsolr\core\classes\services\WPSOLR_Service_Container;
use wpsolr\core\classes\utilities\WPSOLR_Option;
use wpsolr\core\classes\utilities\WPSOLR_Regexp;
use wpsolr\core\classes\WpSolrSchema;

class WPSOLR_Search_PostgreSQL_Client extends WPSOLR_AbstractSearchClient {
	use WPSOLR_PostgreSQL_Client;

	const MORE_LIKE_THIS_FIELDS = [ 'text' ];

	const IS_LOG_QUERY_TIME_IMPLEMENTED = true;

	// Scripts in painless: https://www.elastic.co/guide/en/elasticsearch/reference/current/modules-scripting-painless-syntax.html
	const SCRIPT_LANGUAGE_PAINLESS = 'painless';
	const SCRIPT_PAINLESS_DISTANCE = 'doc[params.field].empty ? params.empty_value : doc[params.field].planeDistance(params.lat,params.lon)*0.001';

	const FIELD_SEARCH_AUTO_COMPLETE = 'autocomplete';
	const FIELD_SEARCH_SPELL = 'spell';
	const SUGGESTER_NAME = 'wpsolr_spellcheck';
	protected const ES_SCROLL_ID = 'scroll_id';
	protected const ES_SCROLL = 'scroll';
	protected const ES_SCROLL_TIME_DELAY = '10s';

	/* @var array $query */
	protected $query;

	/* @var array $query */
	protected $query_url_params = [];

	// https://www.elastic.co/guide/en/elasticsearch/reference/5.2/query-dsl-query-string-query.html
	/* @var array $query_string */
	protected $query_string;

	/* @var array $query_filters */
	protected array $query_filters;
	protected array $query_filters_by_field_name;

	/* @var array $query_post_filters */
	protected $query_post_filters;

	/* @var array $query_script_fields */
	protected $query_script_fields;

	/* @var array $facets_filters */
	protected $facets_filters;

	/* @var array $facets_ranges */
	protected $facets_ranges;

	/* @var array */
	protected $facets_fields;

	/* @var array $completion $facets_fields */
	protected $completion;

	/* @var bool $is_did_you_mean */
	protected $is_did_you_mean = false;

	/* @var bool $is_query_built */
	protected $is_query_built = false;

	/* @var string $boost_field_values */
	protected $boost_field_values;

	/* @var array $function_score */
	protected $function_score;

	/* @var array $stats */
	protected $stats;

	/** @var int */
	protected $random_sort_seed = 0;

	/** @var array */
	protected $highlighting_fields;

	/** @var array */
	protected $source_fields;

	/**
	 * @var array
	 */
	protected $the_query;
	protected array $facets_terms_filters;

	/**
	 *
	 * @param WPSOLR_PostgreSQL_Connection $search_engine_client
	 *
	 * @return WPSOLR_Results_PostgreSQL_Client
	 */
	public function search_engine_client_execute( $search_engine_client, $update_query ) {
		$this->search_engine_client_build_query();
		$results = $this->search_engine_client->execute_statement( $this->the_query['body'], $this->the_query['query_params'] );

		return new WPSOLR_Results_PostgreSQL_Client( $results );
	}

	public function search_engine_client_set_query_keywords( $keywords ) {
		$new_keywords = trim( WPSOLR_Regexp::extract_parenthesis( $keywords ) );

		$this->query_string['query'] = ( '*' === $new_keywords ) ? '' : $new_keywords;
	}

	protected function _log_query_as_string() {
		if ( empty( $this->the_query['body'] ) ) {
			return;
		}

		return $this->search_engine_client->_expand_query_for_log( $this->the_query['body'], $this->the_query['query_params'] );
	}

	protected function _log_results_as_string( $results ) {
		return wp_json_encode( $results->get_results(), JSON_PRETTY_PRINT );
	}

	/***************************************************************************************************
	 *
	 * TODO
	 *
	 */

	public function search_engine_client_build_query() {

		$columns = array_unique( array_merge(
			array_diff(
				$this->source_fields, [
				WpSolrSchema::_FIELD_NAME_POST_THUMBNAIL_HREF_STR,
				WpSolrSchema::_FIELD_NAME_POST_HREF_STR,
				WpSolrSchema::_FIELD_NAME_SNIPPET_S,
			] ),
			array_keys( $this->query['sort'] ?? [] ),
			array_keys( $this->facets_filters ?? [] )
		) );

		list( $last_view, $select_sql ) = $this->_generate_select_sql( $columns );
		$sql = /** @lang PostgreSQL */
			<<<SQL
			{$select_sql}
			{$this->_generate_facets_sql()}
			SELECT jsonb_build_object(
			  'results',
			    (
			    	SELECT jsonb_agg(
				        jsonb_build_object(
						    {$this->_generate_jsonb_build_object_columns( array_merge( $columns, [ 'total_count' ] ) )}
				        )
			    	)
					FROM {$last_view}
			    )
			    {$this->_generate_jsonb_build_object_facets()}
			) as response
		SQL;

		$this->the_query['body']         = $sql;
		$this->the_query['query_params'] = empty( trim( $this->query_string['query'] ) ) ? [] : [
			$this->_transform_text_before_embedding( $this->query_string['query'] )
		];

	}

	protected
	function admin_is_index_exists(
		$is_throw_error = false
	): bool {

		// this methods throws an error if index is not responding.
		try {
			// Check if DB exists
			$exists = ! empty( $this->search_engine_client
				->execute_statement_as_array(
					"SELECT 1 FROM information_schema.schemata WHERE schema_name = $1 LIMIT 1",
					[ $this->_get_schema_name() ] )
			);

		} catch ( \Exception $e ) {

			if ( $is_throw_error ) {
				throw $e;
			}

			return false;
		}

		// Index exists.
		return $exists;
	}

	/**
	 * Create the index
	 *
	 * @param array $index_parameters
	 */
	protected
	function admin_create_index(
		&$index_parameters
	) {
		$admin_connection = $this->_get_maintenance_database_connection();

		/**
		 * Switch to the database
		 */
		$this->search_engine_client = $this->create_search_engine_client( $this->config );

		/*
		 * Create shema
		 */
		$schema_name = $this->_get_schema_name();
		$this->search_engine_client->execute_statement_as_array( $admin_connection->get_create_schema_sql( $schema_name ) );

		/*
        * Create vectorized keywords table with its index
        */
		$keywords_cache_table = sprintf( '"%s"."terms_vectors_cache"', $schema_name );
		$this->search_engine_client->execute_statement_as_array(
		/** @lang PostgreSQL */
			<<<SQL
				CREATE TABLE {$keywords_cache_table} (
					term TEXT PRIMARY KEY,
					embedding VECTOR({$this->_get_model_dimensions()}) /* Matches model dimensions */
				)
			SQL
		);
		$this->search_engine_client->execute_statement_as_array(
		/** @lang PostgreSQL */
			<<<SQL
				CREATE INDEX term_idx ON {$keywords_cache_table} (term);
			SQL
		);

		/*
		 * Create table
		 */
		$this->_postgresql_execute_query_array( '
			CREATE TABLE IF NOT EXISTS %1s (
    			id TEXT PRIMARY KEY
				)
    		' );

		/**
		 * Add default columns
		 */
		$this->_add_missing_columns( array_keys( WpSolrSchema::DEFAULT_FIELD_TYPES ) );

		/**
		 * Add extensions
		 */
		$this->search_engine_client->execute_statement_as_array( 'CREATE EXTENSION IF NOT EXISTS vector CASCADE' );
		$this->search_engine_client->execute_statement_as_array( 'CREATE EXTENSION IF NOT EXISTS ai CASCADE' ); # vectorizer extension from timescale
		$this->search_engine_client->execute_statement_as_array( 'CREATE EXTENSION IF NOT EXISTS pg_textsearch CASCADE' ); # bm25 extension from timescale
		$this->search_engine_client->execute_statement_as_array( 'CREATE EXTENSION IF NOT EXISTS vectorscale CASCADE' ); # performance extension from timescale

		/**
		 * Add pgai vectorizer
		 */
		try {
			$destination = $this->_create_pgai_vectorizer( $schema_name );
		} catch ( \Exception $e ) {
			// Drop the schema, else the user will not be able to retry with other parameters
			$this->admin_delete_index();

			// Show errors
			throw $e;
		}

		/*
		 * Create bm25 index
		 */
		$bm25_index_name = "{$schema_name}_bm25";
		$this->search_engine_client->execute_statement_as_array( sprintf( /** @lang PostgreSQL */ '
			DROP INDEX IF EXISTS "%s";
			', $bm25_index_name,
		) );
		$this->search_engine_client->execute_statement_as_array( sprintf( /** @lang PostgreSQL */ '
			CREATE INDEX "%s" ON %s USING bm25(%s) WITH (text_config=\'english\');
			', $bm25_index_name, $this->_postgresql_table_name(), $this->_get_concatenated_field_name()
		) );

		/*
         * Create hnsw index with cosine <=> operator
         */
		$hnws_index_name = "{$destination}_hnws";
		$this->search_engine_client->execute_statement_as_array( sprintf( /** @lang PostgreSQL */ '
			DROP INDEX IF EXISTS "%s";
			', $hnws_index_name,
		) );
		$this->search_engine_client->execute_statement_as_array( sprintf( /** @lang PostgreSQL */ '
			CREATE INDEX "%s" ON %s USING hnsw (embedding vector_cosine_ops);
			', $hnws_index_name, $this->_get_table_embedding_store_name()
		) );
	}

	/**
	 * Delete the index
	 * @throws \Exception
	 */
	public
	function admin_delete_index() {
		$admin_connection = $this->_get_maintenance_database_connection();

		// Disconnect all other sessions first
		$admin_connection->execute_statement_as_array( $admin_connection->get_disconnect_database_sql( $this->_get_schema_name() ) );

		$admin_connection->execute_statement_as_array( $admin_connection->get_drop_schema_sql( $this->_get_schema_name() ) );

		$admin_connection->execute_statement_as_array( $admin_connection->get_delete_vectorizer_sql( $this->_get_schema_name() ) );
	}

	/**
	 * Add a configuration to the index if missing.
	 */
	protected
	function admin_index_update(
		&$index_parameters
	) {

		return;

		try {

			$mapping = $this->search_engine_client->indices()->getMapping( $this->get_index() );

		} catch ( \Exception $e ) {

			// Since 5.5.1, no type mapping yet triggers an exception. We continue anyway.
		}

		if ( empty( $mapping ) || empty( $mapping[ $this->get_index_label() ] ) || empty( $mapping[ $this->get_index_label() ]['mappings'] ) ) {

			$this->search_engine_client->indices()->close( $this->get_index() );
			$this->search_engine_client->indices()->putSettings( $this->get_and_decode_configuration_file() );
			$this->search_engine_client->indices()->open( $this->get_index() );
		}

	}

	/**
	 * Create a query select.
	 *
	 * @return  array
	 */
	public
	function search_engine_client_create_query_select() {

		$this->query = [];

		$this->query_string                = [];
		$this->query_filters_by_field_name = [];

		return $this->query;
	}

	/**
	 * @inheritDoc
	 *
	 * From \Elastica\Util::escapeTerm
	 *
	 */
	public
	function search_engine_client_escape_keywords(
		$keywords
	) {

		$result = $keywords;

		// \ escaping has to be first, otherwise escaped later once again
		$escapableChars = [
			'\\',
			'+',
			'-',
			'&&',
			'||',
			'!',
			'(',
			')',
			'{',
			'}',
			'[',
			']',
			'^',
			'"',
			'~',
			'*',
			'?',
			':',
			'/'
		];

		foreach ( $escapableChars as $char ) {
			$result = str_replace( $char, '\\' . $char, $result );
		}

		// < and > cannot be escaped, so they should be removed
		// @see https://www.elastic.co/guide/en/elasticsearch/reference/current/query-dsl-query-string-query.html#_reserved_characters
		$nonEscapableChars = [ '<', '>' ];

		foreach ( $nonEscapableChars as $char ) {
			$result = str_replace( $char, '', $result );
		}

		return $result;
	}

	/**
	 * Set query's default operator.
	 *
	 * @param string $operator
	 */
	public
	function search_engine_client_set_default_operator(
		$operator = 'AND'
	) {
		$this->query_string['default_operator'] = $operator;
	}

	/**
	 * Set query's start.
	 *
	 * @param int $start
	 */
	public
	function search_engine_client_set_start(
		$start
	) {
		$this->query['from'] = $start;
	}

	/**
	 * Set query's rows.
	 *
	 * @param int $rows
	 */
	public
	function search_engine_client_set_rows(
		$rows
	) {
		$this->query['size'] = $rows;
	}

	/**
	 * @inerhitDoc
	 */
	public function search_engine_client_set_is_show_all_results( $is_show_all_results = false ) {
		if ( $is_show_all_results ) {
			// Remove the 10K results limit: https://www.elastic.co/guide/en/elasticsearch/reference/current/search-your-data.html#track-total-hits
			$this->query['track_total_hits'] = true;
		}
	}

	/**
	 * @inheritDoc
	 */
	public
	function search_engine_client_set_cursor_mark(
		$cursor_mark
	) {

		unset( $this->query[ self::ES_SCROLL_ID ] );
		unset( $this->query_url_params[ self::ES_SCROLL ] );

		if ( ! is_null( $cursor_mark ) ) {

			if ( ! empty( $cursor_mark ) ) {
				$this->query[ self::ES_SCROLL_ID ] = $cursor_mark;
			}
			$this->query_url_params[ self::ES_SCROLL ] = self::ES_SCROLL_TIME_DELAY;// Time between scrolls before automatic deletion of the cursor
		}

	}

	/**
	 * @inheritdoc
	 */
	public
	function search_engine_client_add_sort(
		$sort, $sort_by, $args = []
	) {//done
		if ( empty( $this->query['sort'] ) || empty( $this->query['sort'][ $sort ] ) ) {
			$this->query['sort'][ $sort ] = $sort_by;
		}
	}

	/**
	 * @inheritdoc
	 */
	public
	function search_engine_client_set_sort(
		$sort, $sort_by
	) {//done
		$this->query['sort'] = [];
		$this->search_engine_client_add_sort( $sort, $sort_by );
	}

	/**
	 * @inheritdoc
	 */
	public
	function search_engine_client_add_sort_random(
		$seed
	) {

		$this->random_sort_seed = (int) preg_replace( "/[^0-9]/", "", $seed ); // Remove all non-digit caracters from seed
	}

	/**
	 * Add a simple filter term.
	 *
	 * @param string $filter_name
	 * @param string $field_name
	 * @param $facet_is_or
	 * @param string $field_value
	 * @param string $filter_tag
	 */
	public
	function search_engine_client_add_filter_term(
		$filter_name, $field_name, $facet_is_or, $field_value, $filter_tag = ''
	) {//done
		$this->search_engine_client_add_filter_in_terms( $filter_name, $field_name, [ $field_value ], $filter_tag );
	}

	/**
	 * Add a negative filter on terms.
	 *
	 * @param string $filter_name
	 * @param string $field_name
	 * @param array $field_values
	 *
	 * @param string $filter_tag
	 *
	 */
	public
	function search_engine_client_add_filter_not_in_terms(
		$filter_name, $field_name, $field_values, $filter_tag = ''
	) {//done
		$this->_add_filter_query( $field_name, $this->search_engine_client_create_not(
			$this->search_engine_client_create_filter_in_terms( $field_name, $field_values )
		) );
	}

	protected
	function _add_filter_query(
		string $field_name, array $query
	) {//done
		return $this->query_filters[] = $query;
	}

	/**
	 * @inheritdoc
	 */
	public
	function search_engine_client_add_filter_not_in_terms_of_other_sites(
		$filter_name, $field_name, $field_values, $site_id
	) {

		$terms_not     = $this->search_engine_client_create_filter_not_in_terms( $field_name, $field_values );
		$terms_site_id = $this->search_engine_client_create_filter_in_terms( WpSolrSchema::_FIELD_NAME_BLOG_NAME_STR, [ $site_id ] );

		// not terms OR site_id
		$this->_add_filter_query( $field_name, $this->search_engine_client_create_or( [
			$terms_not,
			$terms_site_id
		] ) );
	}

	public
	function search_engine_client_add_filter_in_terms(
		$filter_name, $field_name, $field_values, $filter_tag = ''
	) { //done
		$this->search_engine_client_add_filter_any( $filter_name, $field_name, false, $this->search_engine_client_create_filter_in_terms( $field_name, $field_values ), $filter_tag );
	}

	/**
	 * @return array
	 */
	public
	function search_engine_client_create_filter_in_terms(
		$field_name, $field_values
	) {//done
		return [ 'ANY' => [ $field_name => $field_values ] ];
	}

	/**
	 * @inheritdoc
	 *
	 * @return array
	 */
	public
	function search_engine_client_create_filter_wildcard(
		$field_name, $field_value
	) {

		$wildcard = [
			'wildcard' => [
				$field_name => [
					'value' => $field_value,
					'boost' => 1,
				]
			]
		];

		return $wildcard;
	}

	/**
	 * @inheritdoc
	 *
	 * @return array
	 */
	public
	function search_engine_client_create_filter_wildcard_not(
		$field_name, $field_value
	) {

		return $this->search_engine_client_create_not( $this->search_engine_client_create_filter_wildcard( $field_name, $field_value ) );
	}

	/**
	 * @inheritdoc
	 *
	 */
	public
	function search_engine_client_add_filter_in_all_terms(
		$filter_name, $field_name, $field_values, $filter_tag = ''
	) {
		$this->search_engine_client_add_filter_any( $filter_name, $field_name, false, $this->search_engine_client_create_filter_in_all_terms( $field_name, $field_values ), $filter_tag );
	}

	/**
	 * @inheritdoc
	 */
	public
	function search_engine_client_create_filter_in_all_terms(
		$field_name, $field_values
	) {//done
		return [ 'ALL' => [ $field_name => $field_values ] ];
	}


	/**
	 * @inheritdoc
	 *
	 * @return array
	 */
	public
	function search_engine_client_create_filter_not_in_terms(
		$field_name, $field_values
	) {//done
		return $this->search_engine_client_create_not( $this->search_engine_client_create_filter_in_terms( $field_name, $field_values ) );
	}

	/**
	 * @inheritdoc
	 *
	 * @return array
	 */
	public
	function search_engine_client_create_filter_lt(
		$field_name, $field_values
	) {

		return $this->_create_filter_range_terms( $field_name, $field_values, 'lt' );
	}

	/**
	 * @inheritdoc
	 *
	 * @return array
	 */
	public
	function search_engine_client_create_filter_lte(
		$field_name, $field_values
	) {

		return $this->_create_filter_range_terms( $field_name, $field_values, 'lte' );
	}

	/**
	 * @inheritdoc
	 *
	 * @return array
	 */
	public
	function search_engine_client_create_filter_gt(
		$field_name, $field_values
	) {

		return $this->_create_filter_range_terms( $field_name, $field_values, 'gt' );
	}

	/**
	 * @inheritdoc
	 *
	 * @return array
	 */
	public
	function search_engine_client_create_filter_gte(
		$field_name, $field_values
	) {

		return $this->_create_filter_range_terms( $field_name, $field_values, 'gte' );
	}

	/**
	 * @inheritdoc
	 *
	 * @return array
	 */
	public
	function search_engine_client_create_filter_between(
		$field_name, $field_values
	) {

		return $this->search_engine_client_create_and( [
			[
				'range' => [
					$field_name =>
						[
							'from' => $field_values[0],
							'to'   => $field_values[1]
						]
				]
			]
		] );
	}

	/**
	 * @inheritdoc
	 *
	 * @return array
	 */
	public
	function search_engine_client_create_filter_not_between(
		$field_name, $field_values
	) {

		return $this->search_engine_client_create_not(
			$this->search_engine_client_create_filter_between( $field_name, $field_values )
		);
	}

	/**
	 *
	 * @param string $field_name
	 * @param array $field_values
	 * @param string $operator
	 *
	 * @return array
	 */
	protected
	function _create_filter_range_terms(
		$field_name, $field_values, $operator
	) {

		$results = [];

		foreach ( $field_values as $field_value ) {
			$results[] = [ 'range' => [ $field_name => [ $operator => $field_value ] ] ];
		}

		return $this->search_engine_client_create_and( $results );
	}

	/**
	 * Create a 'only numbers' filter.
	 *
	 * @param string $field_name
	 *
	 * @return array
	 */
	public
	function search_engine_client_create_filter_only_numbers(
		$field_name
	) {
		return $this->search_engine_client_create_not( [ 'regexp' => [ $field_name => '[^0-9]*' ] ] );
	}

	/**
	 * Create a 'empty or absent' filter.
	 *
	 * @param string $field_name
	 *
	 * @return array
	 */
	public
	function search_engine_client_create_filter_no_values(
		$field_name
	) {//done
		return $this->search_engine_client_create_not( [ 'IS_NULL' => $field_name ] );
	}

	/**
	 * @inheritDoc
	 *
	 * @param array $queries
	 *
	 * @return array
	 */
	public
	function search_engine_client_create_or(
		$queries
	) {//done
		return [ 'OR' => $queries ];
	}

	/**
	 * https://www.elastic.co/guide/en/elasticsearch/reference/7.9/query-dsl-nested-query.html
	 *
	 * @inheritDoc
	 */
	public
	function search_engine_client_create_nested_query(
		$path, $query
	) {

		return [
			'nested' => [
				'path'  => $path,
				'query' => [ 'bool' => $query ],
			]
		];

	}

	/**
	 * https://www.elastic.co/guide/en/elasticsearch/reference/7.9/query-dsl-nested-query.html
	 *
	 * @inheritDoc
	 */
	public
	function search_engine_client_create_nested_filter(
		$path, $query
	) {

		return [
			'nested' => [
				'path'   => $path,
				'filter' => [ 'bool' => $query ],
			]
		];

	}

	/**
	 * Add a query
	 *
	 * @throws \Exception
	 */
	public
	function search_engine_client_add_query(
		$query
	) {
		$this->query[] = $query;
	}

	/**
	 * @inheritdoc
	 *
	 * @param array $query
	 *
	 * @return array
	 */
	public
	function search_engine_client_create_not(
		$query
	) {//done
		return [ 'NOT' => $query ];
	}

	/**
	 * Add a filter
	 *
	 * @param string $filter_name
	 * @param array $filter
	 */
	public
	function search_engine_client_add_filter(
		$filter_name, $filter
	) {
		$this->_add_filter_query( $filter_name, $filter );
	}

	/**
	 * Create a 'AND' from filters.
	 *
	 * @param array $queries
	 *
	 * @return array
	 */
	public
	function search_engine_client_create_and(
		$queries
	) {//done
		return [ 'AND' => $queries ];
	}

	/**
	 * Add a filter on: empty or in terms.
	 *
	 * @param string $filter_name
	 * @param string $field_name
	 * @param array $field_values
	 * @param string $filter_tag
	 *
	 */
	public
	function search_engine_client_add_filter_empty_or_in_terms(
		$filter_name, $field_name, $field_values, $filter_tag = ''
	) {//done

		// 'IN' terms
		$in_terms = $this->search_engine_client_create_filter_in_terms( $field_name, $field_values );

		// 'empty': not exists
		$empty = $this->search_engine_client_create_not( $this->search_engine_client_create_filter_exists( $field_name ) );

		// 'empty' OR 'IN'
		$this->_add_filter_query( $field_name,
			$this->search_engine_client_create_or(
				[
					$empty,
					$in_terms
				]
			)
		);
	}

	/**
	 * @inheritdoc
	 */
	public
	function search_engine_client_add_filter_exists(
		$filter_name, $field_name
	) {

		// Add 'exists'
		$this->_add_filter_query( $field_name, $this->search_engine_client_create_filter_exists( $field_name ) );

	}

	/**
	 * @inheritdoc
	 */
	public
	function search_engine_client_create_filter_exists(
		$field_name
	) {// done
		return $this->search_engine_client_create_filter_not_in_terms( $field_name, [ static::FIELD_VALUE_UNDEFINED ] );
	}

	/**
	 * Set highlighting.
	 *
	 * @param string[] $field_names
	 * @param string $prefix
	 * @param string $postfix
	 * @param int $fragment_size
	 */
	public
	function search_engine_client_set_highlighting(
		$field_names, $prefix, $postfix, $fragment_size
	) {

		// https://www.elastic.co/guide/en/elasticsearch/reference/5.2/search-request-highlighting.html#_highlight_query

		$fields = [];

		foreach ( $field_names as $field_name ) {

			$fields[ $field_name ] = [
				'fragment_size'       => $fragment_size,
				'number_of_fragments' => 1,
			];
		}

		$this->highlighting_fields = [
			'require_field_match' => false,
			// Show highlighted fields even if they are not part of the query.
			'pre_tags'            => [ $prefix ],
			'post_tags'           => [ $postfix ],
			'fields'              => $fields,
		];
		$this->query['highlight']  = $this->highlighting_fields;
	}

	/**
	 * Get facet terms.
	 *
	 * @param string $facet_name
	 *
	 * @return array
	 */
	protected
	function &get_or_create_facets_field(
		$facet_name
	) {
		if ( ! isset( $this->facets_filters ) ) {

			$this->facets_filters = [];
		}

		if ( isset( $this->facets_filters[ $facet_name ] ) ) {
			return $this->facets_filters[ $facet_name ]['aggs'][ $facet_name ];
		}

		$facet      = [ 'terms' => [ 'field' => $facet_name ] ];
		$agg_filter = $this->_create_outer_aggs( [], [ $facet_name => $facet ] );

		$this->facets_filters[ $facet_name ] = $agg_filter;

		return $facet;
	}


	/**
	 * @inerhitDoc
	 */
	public
	function search_engine_client_set_facets_min_count(
		$facet_name, $min_count
	) {// done

		//$this->get_or_create_facets_field( $facet_name )['terms']['min_doc_count'] = $min_count;
	}

	/**
	 * @inerhitDoc
	 */
	public
	function search_engine_client_add_facet_field(
		$facet_name, $field_name
	) {

		$this->get_or_create_facets_field( $field_name );
	}

	/**
	 * Set facets limit.
	 *
	 * @param $facet_name
	 * @param int $limit
	 */
	public
	function search_engine_client_set_facets_limit(
		$facet_name, $limit
	) {
		$this->get_or_create_facets_field( $facet_name )['terms']['size'] = $limit;
	}

	/**
	 * Sort a facet field alphabetically.
	 *
	 * @param $facet_name
	 */
	public
	function search_engine_client_set_facet_sort_alphabetical(
		$facet_name
	) {

		$this->get_or_create_facets_field( $facet_name )['terms']['order']['_key'] = 'asc';
	}

	/**
	 * Set facet field excludes.
	 *
	 * @param string $facet_name
	 * @param string $exclude
	 */
	public
	function search_engine_client_set_facet_excludes(
		$facet_name, $exclude
	) {

		/**
		 * Nothing done here.
		 * Done in the facet, and in the filter.
		 *
		 * - Excluded terms are put in the post_filter, and are added as filter to each facet not excluded.
		 */

	}

	/**
	 * Set the fields to be returned by the query.
	 *
	 * @param array $fields
	 */
	public
	function search_engine_client_set_fields(
		$fields
	) {
		$this->source_fields = $fields;
	}

	/**
	 * Get suggestions from the engine.
	 *
	 * @inheritdoc
	 *
	 * @return WPSOLR_Results_PostgreSQL_Client
	 */
	public
	function search_engine_client_get_suggestions_keywords(
		$suggestion, $query, $contexts, $is_error = false
	) {

		/*
		{
			"suggest": {
				"wpsolr_spellcheck": {
					"completion": {
						"field": "autocomplete",
						"size": "10"
					},
					"text": "title"
				}
			}
		}
		*/

		$completion = [
			'field'           => self::FIELD_SEARCH_AUTO_COMPLETE,
			'size'            => $suggestion[ WPSOLR_Option::OPTION_SUGGESTION_NB ],
			'skip_duplicates' => true,
		];

		// Add the context: https://www.elastic.co/guide/en/elasticsearch/reference/7.2/suggester-context.html
		if ( ! empty( $contexts ) && WPSOLR_Service_Container::getOption()->get_option_indexes_version_suggester_has_context( $this->index_indice, ! $is_error ) ) {
			$completion['contexts'] = $contexts;
		}

		$this->completion = [
			'suggest' => [
				self::SUGGESTER_NAME => [
					'text'       => $query,
					'completion' => $completion,
				],
			]
		];

		try {

			return $this->search_engine_client_execute( $this->search_engine_client, null );

		} catch ( \Exception $e ) {

			if ( ! $is_error && $this->_add_missing_columns( $e->getMessage() ) ) {
				// Retry with the fix
				return $this->search_engine_client_get_suggestions_keywords( $suggestion, $query, $contexts, true );
			}

			// Could not fix the problem, continue
			throw $e;
		}
	}


	/**
	 * Get suggestions for did you mean.
	 *
	 * @param string $keywords
	 *
	 * @return string Did you mean keyword
	 */
	public
	function search_engine_client_get_did_you_mean_suggestions(
		$keywords
	) {

		$this->is_did_you_mean = true;

		$results = $this->search_engine_client_execute( $this->search_engine_client, null );

		$suggestions = $results->get_suggestions();

		return ! empty( $suggestions ) ? $suggestions[0]['text'] : '';
	}


	/**
	 * Add a geo distance sort.
	 * The field is already in the sorts. It will be replaced with geo sort specific syntax.
	 *
	 * @param $field_name
	 * @param $geo_latitude
	 * @param $geo_longitude
	 *
	 */
	public
	function search_engine_client_add_sort_geolocation_distance(
		$field_name, $geo_latitude, $geo_longitude
	) {

		$sorts = $this->query['sort'] ?? [];

		if ( ! empty( $sorts ) ) {

			foreach ( $sorts as $position => &$sort_item ) {

				if ( ! empty( $sort_item[ $field_name ] ) ) {

					// Replace geo sort
					$sort_item = [
						'_geo_distance' => [
							$field_name     => [ 'lat' => $geo_latitude, 'lon' => $geo_longitude ],
							'order'         => $sort_item[ $field_name ]['order'],
							'unit'          => 'km',
							'mode'          => 'min',
							'distance_type' => 'plane',
						],
					];

					// Replace sorts.
					$this->query['sort'] = $sorts;

					// sort found and replaced.
					break;
				}
			}
		}

	}

	/**
	 * Generate a distance script for a field, and name the query
	 *
	 * @param $field_prefix
	 * @param $field_name
	 * @param $geo_latitude
	 * @param $geo_longitude
	 *
	 * @return string
	 *
	 */
	public
	function get_named_geodistance_query_for_field(
		$field_prefix, $field_name, $geo_latitude, $geo_longitude
	) {

		if ( ! isset( $this->query_script_fields ) ) {
			$this->query_script_fields = [];
		}

		// Create the distance field name: field_name1_str => wpsolr_distance_field_name1
		$distance_field_name = $field_prefix . WPSOLR_Regexp::remove_string_at_the_end( $field_name, WpSolrSchema::_SOLR_DYNAMIC_TYPE_STRING );

		// Add distance field script to field scripts
		$this->query_script_fields[ $distance_field_name ] = [
			'script' => [
				'source' => self::SCRIPT_PAINLESS_DISTANCE,
				'params' => [
					'field'       => WpSolrSchema::replace_field_name_extension( $field_name ),
					// field_name1_str => field_name1_ll
					'empty_value' => 40000,
					'lat'         => floatval( $geo_latitude ),
					'lon'         => floatval( $geo_longitude ),
				],
				'lang'   => self::SCRIPT_LANGUAGE_PAINLESS,
			],
		];

		return $distance_field_name;
	}

	/**
	 * Replace default query field by query fields, with their eventual boost.
	 *
	 * @param array $query_fields
	 */
	public
	function search_engine_client_set_query_fields(
		array $query_fields
	) {
		$this->query_string['fields'] = $query_fields;
	}

	/**
	 * Set boosts field values.
	 *
	 * @param array $boost_field_values
	 */
	public
	function search_engine_client_set_boost_field_values(
		$boost_field_values
	) {
		// Store it. Will be added to the query later.

		// Add 'OR' condition, else empty results if boost value is not found.
		$this->boost_field_values = $boost_field_values;
	}

	protected
	function get_or_create_facets_range(
		$facet_name, $range_start, $range_end, $range_gap
	) {//done
		return $this->facets_filters[ $facet_name ]['aggs'][ $facet_name ]['range'] = [
			'from' => $range_start,
			'to'   => $range_end,
			'gap'  => $range_gap,
		];
	}

	/**
	 * Create a facet range regular.
	 *
	 * @param $facet_name
	 * @param $field_name
	 *
	 * @param string $range_start
	 * @param string $range_end
	 * @param string $range_gap
	 */
	public
	function search_engine_client_add_facet_range_regular(
		$facet_name, $field_name, $range_start, $range_end, $range_gap
	) {

		$this->get_or_create_facets_range( $field_name, $range_start, $range_end, $range_gap );
	}

	public
	function search_engine_client_add_facet_top_hits(
		$facet_name, $size
	) {//done
	}

	public
	function search_engine_client_add_filter_any(
		$filter_name, $field_name, $facet_is_or, $filter, $filter_tag = ''
	) { //done
		$operator              = $facet_is_or ? 'OR' : 'AND';
		$this->query_filters[] = [ $operator => $filter ];
	}

	/**
	 * @inheritdoc
	 */
	public
	function search_engine_client_add_filter_range_upper_strict(
		$filter_name, $field_name, $facet_is_or, $range_start, $range_end, $is_date, $filter_tag = ''
	) {

		if ( $range_start === $range_end ) {

			$this->search_engine_client_add_filter_range_upper_included( $filter_name, $field_name, $facet_is_or, $range_start, $range_end, $is_date, $filter_tag );

		} else {


			$range_values = [];
			if ( '*' !== $range_start ) {
				$range_values['from'] = $range_start;
			}
			if ( '*' !== $range_end ) {
				$range_values['lt'] = $range_end; // aggregation upper ranges are 'lt'
			}

			$range = [ 'range' => [ $field_name => $range_values ] ];

			$this->search_engine_client_add_filter_any( $filter_name, $field_name, $facet_is_or, $range, $filter_tag );
		}
	}

	/**
	 * @inheritdoc
	 */
	public
	function search_engine_client_add_filter_range_upper_included(
		$filter_name, $field_name, $facet_is_or, $range_start, $range_end, $is_date, $filter_tag = ''
	) {

		$range_values = [];
		if ( '*' !== $range_start ) {
			$range_values['from'] = $range_start;
		}
		if ( '*' !== $range_end ) {
			$range_values['to'] = $range_end;
		}

		$range = [ 'range' => [ $field_name => $range_values ] ];

		$this->search_engine_client_add_filter_any( $filter_name, $field_name, $facet_is_or, $range, $filter_tag );
	}

	/**
	 * Add decay functions to the search query
	 *
	 * @param array $decays
	 *
	 */
	public
	function search_engine_client_add_decay_functions(
		array $decays
	) {

		if ( empty( $decays ) ) {
			// Nothing to do
			return;
		}

		if ( is_null( $this->function_score ) ) {
			$this->function_score = [];
		}

		foreach ( $decays as $decay_def ) {

			$origin = $decay_def['origin'];
			if ( WPSOLR_Option::OPTION_SCORING_DECAY_ORIGIN_DATE_NOW === $decay_def['origin'] ) {
				$origin = 'now';
			}

			switch ( $decay_def['unit'] ) {
				case WPSOLR_Option_Scoring::DECAY_DATE_UNIT_DAY:
					$unit = 'd';
					break;

				case WPSOLR_Option_Scoring::DECAY_DATE_UNIT_KM:
					$unit = 'km';
					break;

				case WPSOLR_Option_Scoring::DECAY_DATE_UNIT_NONE:
					$unit = '';
					break;

				default:
					throw new \Exception( sprintf( 'Unit %s not recognized for field %s.',
						esc_html( $decay_def['unit'] ), esc_html( $decay_def['field'] ) ) );
					break;
			}

			$this->function_score['function_score']['functions'][] = [
				$decay_def['function'] =>
					[
						$decay_def['field'] => // displaydate_dt
							[
								'origin' => $origin, // 'now', '0', 'lat,long'
								'scale'  => sprintf( '%s%s', $decay_def['scale'], $unit ), // '10d', '10', '10km'
								'offset' => sprintf( '%s%s', $decay_def['offset'], $unit ), // '2d', '2', '2km'
								'decay'  => $decay_def['decay'], // '0.5'
							]
					]
			];

		}
	}

	/**
	 * Add a geo distance filter.
	 *
	 * @param $field_name
	 * @param $geo_latitude
	 * @param $geo_longitude
	 *
	 */
	public
	function search_engine_client_add_filter_geolocation_distance(
		$field_name, $geo_latitude, $geo_longitude, $distance
	) {

		$geo_distance_query = [
			'geo_distance' => [
				'distance'      => sprintf( '%skm', $distance ),
				'distance_type' => 'plane',
				$field_name     => [
					'lat' => $geo_latitude,
					'lon' => $geo_longitude,
				],
			]
		];

		$this->search_engine_client_add_filter_any( sprintf( '%s %s', 'max distance for', $field_name ),
			$field_name,
			false,
			$geo_distance_query,
			'post filter'
		);
	}

	/**
	 * Get facet stats.
	 *
	 * @param string $facet_name
	 *
	 * @return array
	 */
	protected
	function get_or_create_facets_stats(
		$facet_name
	) {
		if ( ! isset( $this->stats ) ) {

			$this->stats = [];
		}

		if ( isset( $this->stats[ $facet_name ] ) ) {
			return $this->stats[ $facet_name ];
		}

		// Not found. Create the stats.
		$stats = $this->_create_outer_aggs(
			[],
			[
				$facet_name => [
					'stats' => [
						'field' => $facet_name
					],
				],
			]
		);

		$this->facets_filters[ $facet_name ] = $stats;

		return $stats;
	}

	/**
	 * Create a facet stats.
	 *
	 * @param string $facet_name
	 * @param string $exclude
	 */
	public
	function search_engine_client_add_facet_stats(
		$facet_name, $exclude
	) {
		$this->get_or_create_facets_stats( $facet_name );
	}


	/**
	 * Build the outer aggs from its inner content
	 *
	 * @param array $inner_aggs
	 * @param array $inner_filter
	 *
	 * @return array
	 */
	protected function _create_outer_aggs(
		array $inner_filter, array $inner_aggs
	) {
		return [
			'filter' => empty( $inner_filter ) ?
				[
					'match_all' => new \stdClass(),
				] :
				$inner_filter,
			'aggs'   => $inner_aggs,
		];

	}

	/**
	 * @param string $facet_name
	 * @param array $agg_nested
	 *
	 * @return array
	 */
	protected
	function _create_outer_aggs_nested(
		$facet_name, array $agg_nested
	) {

		if ( $this->_is_nested( $facet_name ) ) {
			return [
				'nested' => [
					'path' => $this->_get_nested_name( $facet_name ),
				],
				'aggs'   => [ $facet_name => $agg_nested, ]
			];
		}

		return $agg_nested;
	}

	/**
	 * @param string $facet_name
	 * @param array $agg_nested
	 *
	 * @return array
	 */
	protected
	function _create_outer_filter_nested(
		$facet_name, array $filter
	) {

		if ( $this->_is_nested( $facet_name ) ) {
			return [
				'nested' => [
					'path'  => $this->_get_nested_name( $facet_name ),
					'query' => $filter
				],
			];
		}

		return $filter;
	}

	/**
	 * @href https://www.elastic.co/guide/en/elasticsearch/reference/current/sort-search-results.html#_nested_sorting_examples
	 *
	 * @param string $facet_name
	 * @param array $filter_nested
	 *
	 * @return array
	 */
	protected
	function _create_outer_sort_nested(
		$facet_name, array $filter_nested
	) {

		if ( $this->_is_nested( $facet_name ) ) {
			$filter_nested[ $facet_name ]['nested'] = [
				'path' => $this->_get_nested_name( $facet_name ),
			];
		}

		return $filter_nested;
	}

	/**
	 * @inheritDoc
	 */
	protected
	function property_exists(
		$document, $field_name
	) {
		return property_exists( $document, $field_name );
	}

	protected function _create_pgai_vectorizer( mixed $schema_name ): string {
		$destination = sprintf( '%s_embedding_store', $schema_name );
		$this->_postgresql_execute_query_array( "
			SELECT ai.create_vectorizer(
			    -- 1. Source (Positional argument)
			    '{$this->_postgresql_table_name()}'::regclass,
			
	            -- 2. DESTINATION (match the required 'config_type' pattern)
			    destination => jsonb_build_object(
			        'config_type', 'destination',
			        'implementation', 'table',
			        'table', '$destination'
			    ),
			   
			    -- 3. Embedding Config (Wrap the model details here)
			    {$this->_create_pgai_embed_table_config()},
				
			    -- 4. Chunking Config
			    -- It splits that text into smaller pieces.
			    chunking => ai.chunking_recursive_character_text_splitter(
			        chunk_size => 800,
			        chunk_overlap => 100
			    ),
			    
			    -- 5. LOADING
			    -- pgai looks at your table and pulls text from the content column.
			    -- Notice: No nested 'config' object. All keys are top-level.
			    loading => jsonb_build_object(
			        'config_type', 'loading',
			        'implementation', 'column',
			        'column_name', '{$this->_get_concatenated_field_name()}',
			        'retries', 3
			    ),

			    -- 6. Formatting
			    -- Formatting: It takes each piece (now called \$chunk) and prepares it for the AI model.
				-- Example: If we wanted to prepend the title, we could use: \"Title: \$title. Text: \$chunk\".
				-- Your Case: We just want the text, so we use \"\$chunk\".
			    formatting => ai.formatting_python_template('\$chunk')
			    
			);"
		);

		return $destination;
	}

	protected function _create_pgai_embed_table_config(): string {
		$llm_provider = trim( $this->get_config()['extra_parameters']['index_llm_provider'] );
		$url_base     = trim( $this->get_config()['extra_parameters']['index_language_code'] );
		$model_name   = trim( $this->get_config()['extra_parameters']['index_weaviate_huggingface_config_model'] );
		$dimensions   = $this->_get_model_dimensions();

		switch ( $llm_provider ) {
			case WPSOLR_PostgreSQL_Constants::AI_PROVIDER_LOCAL_OLLAMA:
				$embedding_config = "
				embedding => ai.embedding_ollama(
			        '$model_name', 
			        $dimensions, -- dimensions
			        base_url => '$url_base'
  			    )
  			    ";
				break;

			


			default:
				throw new \Exception( esc_html( "LLM provider $llm_provider is unknown." ) );
		}

		return $embedding_config;
	}

	protected function _create_pgai_embed_query_config( string $recommendation_object_id ): string {
		if ( ! empty( $recommendation_object_id ) ) {
			// Use store embedding of the recommendation object
			return /** @lang PostgreSQL */
				<<<SQL
				WITH
					QUERY_VECTOR AS (
						SELECT
							EMBEDDING AS VECTOR
						FROM
							{$this->_get_table_embedding_store_name()}
						WHERE
							id = '{$recommendation_object_id}'
						LIMIT
							1
					)
			SQL;
		}


		$llm_provider = trim( $this->get_config()['extra_parameters']['index_llm_provider'] );
		$url_base     = trim( $this->get_config()['extra_parameters']['index_language_code'] );
		$model_name   = trim( $this->get_config()['extra_parameters']['index_weaviate_huggingface_config_model'] );
		$dimensions   = $this->_get_model_dimensions();

		switch ( $llm_provider ) {
			case WPSOLR_PostgreSQL_Constants::AI_PROVIDER_LOCAL_OLLAMA:
				$embed_query_config = "
				ai.ollama_embed(
			        '$model_name', 
			        $1,
			        '$url_base'
  			    )
  			    ";
				break;

			


			default:
				throw new \Exception( esc_html( "LLM provider $llm_provider is unknown." ) );
		}

		return /** @lang PostgreSQL */
			<<<SQL
				WITH
					INSERTED AS (
						INSERT INTO
							"{$this->_get_schema_name()}"."terms_vectors_cache" (TERM, EMBEDDING)
						SELECT
							$1,
							$embed_query_config
						WHERE
							NOT EXISTS (
								SELECT
									1
								FROM
									"{$this->_get_schema_name()}"."terms_vectors_cache"
								WHERE
									TERM = $1
							)
						RETURNING
							EMBEDDING
					),
					QUERY_VECTOR AS (
						SELECT
							EMBEDDING AS VECTOR
						FROM
							INSERTED
						UNION ALL
						SELECT
							EMBEDDING
						FROM
							"{$this->_get_schema_name()}"."terms_vectors_cache"
						WHERE
							TERM = $1
						LIMIT
							1
					)
			SQL;
	}

	protected function _get_model_dimensions(): int {
		return (int) trim( $this->get_config()['extra_parameters']['index_catalog_branch'] );
	}

	protected function _get_distance_operator( string $col1, string $col2 ): array {
		switch ( $distance_type = WPSOLR_Service_Container::getOption()->get_search_distance_type() ) {
			case WPSOLR_Option::OPTION_SEARCH_DISTANCE_TYPE_COSINE:
				// Normalized cosine (unlike <#> which is not normalized and difficult to manage)
				return [ "cosine_distance({$col1}, {$col2})", 'ASC' ];

			case WPSOLR_Option::OPTION_SEARCH_DISTANCE_TYPE_INNER_PRODUCT:
				return [ "{$col1} <=> {$col2}", 'DESC' ];

			default:
				throw new \Exception( esc_html( "Distance type $distance_type is unknown." ) );
		}
	}

	protected function _get_best_chunks_by_distance_sql( string $columns, string $filters, string $distance, string $distance_sort ): string {
		$rank_in_type = '';
		if ( $this->_is_suggestion_can_be_grouped() ) {
			$rank_in_type = <<<SQL
					,
					ROW_NUMBER() OVER (
			        PARTITION BY f.type
			        ORDER BY {$distance} {$distance_sort}
			        ) AS rank_in_type
			        /*, F.TYPE			*/	
			SQL;
		}

		switch ( $this->filter ) {
			case WPSOLR_Option::OPTION_SEARCH_ITEM_FILTER_HYBRID_RRF:
				// We use rank (position in results) as distance
				$distance_name = 'rank';
				$distance      = "ROW_NUMBER() OVER (ORDER BY {$distance} {$distance_sort})";
				break;

			default:
				$distance_name = 'distance';
				break;
		}

		$REPLACE_LIMIT = '';
		$sql           = /** @lang PostgreSQL */
			<<<SQL
			filters AS (
				SELECT
					    id, type
					FROM
					    {$this->_postgresql_table_name()}
					WHERE
						{$filters}
			),
			near_chunks AS (
				SELECT
				    f.*,
				    {$distance} as {$distance_name}
				    $rank_in_type
				FROM
				    filters f
				JOIN
				    {$this->_get_table_embedding_store_name()} s ON f.id = s.id /* Join with the Embedding Store */
				CROSS JOIN
				    query_vector q
				ORDER BY
				    {$distance_name} $distance_sort /* Sort by Distance */
				__REPLACE_LIMIT__
			)
			SQL;

		if ( ( 'distance' === $distance_name ) &&
		     WPSOLR_Service_Container::getOption()->get_search_has_cut_off_type_top_k_relative_safety()
		) {
			$cut_off_top_k    = WPSOLR_Service_Container::getOption()->get_search_cut_off_top_k_value(); // 50
			$cut_off_relative = 1 + ( ( (int) WPSOLR_Service_Container::getOption()->get_search_cut_off_relative_value() ) / 100 ); // 1.25
			$cut_off_safety   = WPSOLR_Service_Container::getOption()->get_search_cut_off_safety_value(); // 0.7

			switch ( $distance_type = WPSOLR_Service_Container::getOption()->get_search_distance_type() ) {
				case WPSOLR_Option::OPTION_SEARCH_DISTANCE_TYPE_COSINE:
					$best_chunks_where = /** @lang PostgreSQL */
						<<<SQL
						r.distance <= b.best_distance * {$cut_off_relative}
						AND r.distance <= {$cut_off_safety}
						SQL;
					break;

				case WPSOLR_Option::OPTION_SEARCH_DISTANCE_TYPE_INNER_PRODUCT:
					$best_chunks_where = /** @lang PostgreSQL */
						<<<SQL
						 /* Manage postitive and negative distances */
						 (
						    (b.best_distance >= 0 AND r.distance <= b.best_distance * {$cut_off_relative})
						 	OR
						 	(b.best_distance < 0  AND r.distance <= b.best_distance / {$cut_off_relative})
						 )
						 AND
						 (r.distance BETWEEN b.best_distance - {$cut_off_safety} AND b.best_distance + {$cut_off_safety})
						SQL;
					break;

				default:
					throw new \Exception( esc_html( "Distance type $distance_type is unknown." ) );
			}

			$REPLACE_LIMIT = "LIMIT {$cut_off_top_k}  /* Top-K candidates */";
			$sql           = /** @lang PostgreSQL */
				<<<SQL
				$sql,
				best_top_n_distance AS (
				    SELECT MIN({$distance_name}) AS best_distance
				    FROM near_chunks
				),
				best_chunks AS (
				    SELECT r.*
				    FROM near_chunks r
				    CROSS JOIN best_top_n_distance b
				    WHERE
				        {$best_chunks_where}
				)
			SQL;
		} else {
			// Return near_chunks without further ranking
			$sql = str_replace( 'near_chunks', 'best_chunks', $sql );
		}

		return str_replace( '__REPLACE_LIMIT__', $REPLACE_LIMIT, $sql );
	}

	protected function _generate_reciprocal_rank_fusion_sql( string $view_filters, string $view_results ): array {
		$bm25_index_name = $this->_get_index_bm25_name();
		$last_view       = 'rff_score';

		switch ( $this->filter ) {
			case WPSOLR_Option::OPTION_SEARCH_ITEM_FILTER_HYBRID_RRF:
				// We use rank (position in results) as distance
				$distance_name = 'rank';
				break;

			default:
				$distance_name = 'distance';
				break;
		}

		$sql = /** @lang PostgreSQL */
			<<<SQL
				keyword_search AS (
					SELECT
						f.ID,
						ROW_NUMBER() OVER (ORDER BY {$this->_get_concatenated_field_name()} <@> TO_BM25QUERY ($1, '{$bm25_index_name}') ASC) AS score
					FROM
						{$view_filters} f JOIN {$this->_postgresql_table_name()} T ON F.ID = T.ID
					ORDER BY
						score ASC
				),
				{$last_view} AS (
					SELECT
						R.*,
						0.7 * COALESCE(1.0 / (60 + R.{$distance_name}), 0.0) + /* 70% weight to vectors */
						0.3 * COALESCE(1.0 / (60 + K.SCORE), 0.0) /* 30% weight to keywords */
						AS COMBINED_SCORE
					FROM
						{$this->_postgresql_table_name()} A
						LEFT JOIN keyword_search K ON A.ID = K.ID
						LEFT JOIN {$view_results} R ON A.ID = R.ID
					WHERE
						K.ID IS NOT NULL OR R.ID IS NOT NULL
					ORDER BY
						COMBINED_SCORE DESC
				)	
			SQL;

		return [ $last_view, $sql ];
	}

	protected function _generate_select_with_bm25_sql(
		string $columns_for_select, string $filters,
		string $recommendation_object_id
	): array {
		/**
		 * Order by BM25 on keywords
		 */
		list( $last_view, $sql ) = $this->_generate_select_count_for_results_sql( $columns_for_select, '', $this->_generate_select_order_by_sql() );
		$bm25_index_name = $this->_get_index_bm25_name();

		if ( $this->_is_suggestion_can_be_grouped() ) {
			$sql_distinct_docs = <<<SQL
			    distinct_docs AS (
			        SELECT
						f.ID,
						t."{$this->_get_concatenated_field_name()}" <@> TO_BM25QUERY ($1, '{$bm25_index_name}') AS distance,
						ROW_NUMBER() OVER (
				        	PARTITION BY F.type
				            ORDER BY t."{$this->_get_concatenated_field_name()}" <@> TO_BM25QUERY ($1, '{$bm25_index_name}') ASC
				        ) AS rank_in_type,
						F.TYPE
					FROM
						filters f JOIN {$this->_postgresql_table_name()} T ON F.ID = T.ID
					ORDER BY
						distance ASC
			    )
			SQL;

		} else {
			$sql_distinct_docs = <<<SQL
			    distinct_docs AS (
			        SELECT
						f.ID,
						t."{$this->_get_concatenated_field_name()}" <@> TO_BM25QUERY ($1, '{$bm25_index_name}') AS distance
					FROM
						filters f JOIN {$this->_postgresql_table_name()} T ON F.ID = T.ID
					ORDER BY
						distance ASC
			    )
			SQL;
		}

		$sql = <<<SQL
			WITH 
			    filters AS (
					SELECT
						    {$columns_for_select}, {$this->_get_concatenated_field_name()}
						FROM
						    {$this->_postgresql_table_name()} t /* Source Table */
						WHERE
							{$filters}
				),
			    {$sql_distinct_docs},
			{$sql}
		SQL;

		return [ $last_view, $sql ];
	}

	protected function _generate_facets_sql(): string {
		if ( empty( $this->facets_filters ) ||
		     $this->get_wpsolr_query()->wpsolr_get_is_suggestion() ||
		     $this->get_wpsolr_query()->wpsolr_get_is_recommendation() ) {
			return '';
		}

		$sql = [];
		foreach ( $this->facets_filters ?? [] as $field_name => $field_def ) {
			if ( isset( $field_def['aggs'][ $field_name ]['terms'] ) ) {
				$sql[ $field_name ] = $this->_generate_facet_term_sql( $field_def, $field_name );
				continue;
			}

			if ( isset( $field_def['aggs'][ $field_name ]['range'] ) ) {
				$sql[ $field_name ] = $this->_generate_facet_range_sql( $field_def, $field_name );
				continue;
			}

			if ( isset( $field_def['aggs'][ $field_name ]['stats'] ) ) {
				$sql[ $field_name ] = $this->_generate_facet_stats_sql( $field_def, $field_name );
				continue;
			}

			throw new \Exception( esc_html( "Facet type for $field_name is unknown." ) );
		}

		return ', ' . implode( ', ', array_values( $sql ) );
	}

	protected function _sprintf_sql( $facet_order, $field_name ): string {
		return sprintf( $facet_order, $field_name );
	}

	protected function _generate_jsonb_build_object_facets(): string {
		if ( empty( $this->facets_filters ) ||
		     $this->get_wpsolr_query()->wpsolr_get_is_suggestion() ||
		     $this->get_wpsolr_query()->wpsolr_get_is_recommendation() ) {
			return '';
		}

		$sql = [];
		foreach ( $this->facets_filters ?? [] as $field_name => $field_def ) {
			$sql[ $field_name ] = <<<SQL
				'{$field_name}', 
				(
				SELECT
					facet
				FROM
					{$field_name}_facet
				)
			SQL;
		}

		$facets_jsonb = implode( ', ', array_values( $sql ) );

		return <<<SQL
			,
			  'facets',
			    jsonb_build_object(
			    	{$facets_jsonb}
			    )
		SQL;
	}

	protected function _generate_jsonb_build_object_columns( array $columns ) {
		// "'col1', col1, 'col2', col2, ..."
		return implode( ', ', array_map( function ( $v ) {
			return "'$v', \"$v\"";
		}, $columns ) );
	}

	protected function _generate_facet_term_sql( array $field_def, string $field_name ): string {
		$facet_size = (int) $field_def['aggs'][ $field_name ]['terms']['size'] ?? 20;

		$facet_order = isset( $field_def['aggs'][ $field_name ]['terms']['order'] ) ?
			'ORDER BY %s ASC, count DESC' : 'ORDER BY count DESC, %s ASC';
		if ( ! ( WpSolrSchema::get_custom_field_is_numeric_type( $field_name ) ||
		         WpSolrSchema::get_custom_field_is_date_type( $field_name ) ||
		         WpSolrSchema::get_custom_field_is_range_type( $field_name ) ) ) {
			// order on text is case sensitive
			$facet_order = sprintf( $facet_order, 'LOWER(%s)' );
		}

		if ( in_array( $field_name, [ WpSolrSchema::_FIELD_NAME_TYPE, WpSolrSchema::_FIELD_NAME_AUTHOR, ] ) ) {
			// Only non array field
			return <<<SQL
				{$field_name}_facet AS (
						SELECT jsonb_agg(
					        jsonb_build_object('value', {$field_name}, 'count', count)
					        ORDER BY count DESC
					    ) AS facet
					    FROM (
					        SELECT {$field_name}, COUNT(*) AS count
					        FROM RESULTS R JOIN {$this->_postgresql_table_name()} T ON R.ID = T.ID
					        GROUP BY {$field_name}
					        {$this->_sprintf_sql( $facet_order, $field_name )}
					        LIMIT {$facet_size} 
					    ) t
				    )
			SQL;
		}

		// Array field
		return <<<SQL
			{$field_name}_facet AS (
				SELECT jsonb_agg(
			        jsonb_build_object('value', {$field_name}_flat, 'count', count)
			        ORDER BY count DESC
			    ) AS facet
			    FROM (
			        SELECT {$field_name}_flat, COUNT(*) AS count
			        FROM RESULTS R JOIN {$this->_postgresql_table_name()} T ON R.ID = T.ID
			        CROSS JOIN LATERAL unnest({$field_name}) AS {$field_name}_flat
			        GROUP BY {$field_name}_flat
			        {$this->_sprintf_sql( $facet_order, $field_name . '_flat' )}
			        LIMIT {$facet_size}
			    ) t
			)
		SQL;
	}

	protected function _generate_facet_range_sql( array $field_def, string $field_name ): string {
		$range = $field_def['aggs'][ $field_name ]['range'];

		return <<<SQL
			{$field_name}_facet_range_params AS (
			    SELECT
			        {$range['from']}::numeric  AS start,
			        {$range['to']}::numeric AS end,
			        {$range['gap']}::numeric AS gap
			),
			{$field_name}_facet_ranges AS (
			    SELECT
			        gs AS start,
			        gs + p.gap AS end
			    FROM {$field_name}_facet_range_params p,
			         generate_series(p.start, p.end - p.gap, p.gap) gs
			),
			{$field_name}_facet AS (
				SELECT jsonb_agg(
				    jsonb_build_object(
				        'from', "start",
				        'to', "end",
				        'count', "count"
				    )
				    ORDER BY start
				) AS facet
				FROM (
				    SELECT
				        ra.start,
				        ra.end,
				        COUNT(r.*) AS count
				    FROM 
						RESULTS R
					JOIN {$this->_postgresql_table_name()} T
						ON R.ID = T.ID
				    RIGHT JOIN {$field_name}_facet_ranges ra
				      ON t.{$field_name}[1] >= ra.start
				     AND t.{$field_name}[1] <  ra.end
				    GROUP BY ra.start, ra.end
				) t
			)
		SQL;
	}

	protected function _generate_facet_stats_sql( array $field_def, string $field_name ): string {

		$min_template_array = WpSolrSchema::get_custom_field_is_date_type( $field_name ) ? 'EXTRACT(EPOCH FROM MIN(%s[1])) * 1000' : 'MIN(%s[1])';
		$min_template       = $this->_is_field_type_array( $field_name ) ? $min_template_array :
			str_replace( '%s[1]', '%s', $min_template_array );
		$max_template       = str_replace( 'MIN(', 'MAX(', $min_template );
		$count_template     = $this->_is_field_type_array( $field_name ) ? 'COUNT(%s[1])' : 'COUNT(%s)';

		return <<<SQL
			{$field_name}_facet AS (
			    SELECT
			        jsonb_build_object(
			            'min', {$this->_sprintf_sql( $min_template, $field_name )},
			            'max', {$this->_sprintf_sql( $max_template, $field_name )},
			            'count', {$this->_sprintf_sql( $count_template, $field_name )}
			        ) AS facet
			    FROM RESULTS R
			    JOIN {$this->_postgresql_table_name()} T
					ON R.ID = T.ID
			    WHERE T.{$field_name} IS NOT NULL
			)
		SQL;
	}

	protected function _generate_select_sql( array $columns ): array {
		$columns_for_select = implode( ', ', array_map(
			fn( $col ) => sprintf( 't."%s"', $col ),
			$columns ) );

		$filters = $this->_postgresql_build_query( [ 'AND' => $this->query_filters ] );

		if ( ! empty( $recommendation_object_id = $this->_get_recommendation_object_id() ) ) {
			// Force recommendations with
			$this->filter = WPSOLR_Option::OPTION_SEARCH_ITEM_FILTER_NEAR_TEXT;

			return $this->_generate_select_with_keywords_sql( $columns_for_select, $filters, $recommendation_object_id );
		}

		if ( empty( trim( $this->query_string['query'] ) ) ) {
			return $this->_generate_select_without_keywords_sql( $columns_for_select, $filters );
		}

		switch ( $this->filter ?? '' ) {
			case WPSOLR_Option::OPTION_SEARCH_ITEM_FILTER_NEAR_TEXT:
			case WPSOLR_Option::OPTION_SEARCH_ITEM_FILTER_HYBRID_RRF:
				return $this->_generate_select_with_keywords_sql( $columns_for_select, $filters, $recommendation_object_id );

			case WPSOLR_Option::OPTION_SEARCH_ITEM_FILTER_BM25:
				return $this->_generate_select_with_bm25_sql( $columns_for_select, $filters, $recommendation_object_id );

			default:
				throw new \Exception( esc_html( "Unknown query type '{$this->filter}'." ) );
		}

	}

	protected function _generate_select_without_keywords_sql( string $columns_for_select, string $filters ): array {
		/**
		 * No keywords, use classic SQL instead of embeddings
		 */
		list( $last_view, $sql ) = $this->_generate_select_count_for_results_sql( $columns_for_select, '', $this->_generate_select_order_by_sql() );
		$sql = <<<SQL
			WITH 
			    filters AS (
					SELECT
						    {$columns_for_select}, {$this->_get_concatenated_field_name()}
						FROM
						    {$this->_postgresql_table_name()} t /* Source Table */
						WHERE
							{$filters}
				),
			    distinct_docs AS (
					SELECT
						    f.id
						FROM
						    filters f
						{$this->_generate_select_order_by_sql()}
				),
			{$sql}
		SQL;

		return [ $last_view, $sql ];
	}

	protected function _generate_select_order_by_sql(): string {
		if ( empty( $this->query['sort'] ) ) {
			if ( empty( rtrim( $this->query_string['query'] ) ) ) {
				return '';
			}

			switch ( $this->filter ) {
				case WPSOLR_Option::OPTION_SEARCH_ITEM_FILTER_NEAR_TEXT:
					return 'ORDER BY distance ASC';

				case WPSOLR_Option::OPTION_SEARCH_ITEM_FILTER_HYBRID_RRF:
					return 'ORDER BY rank ASC';

				case WPSOLR_Option::OPTION_SEARCH_ITEM_FILTER_BM25:
					return '';

				default:
					throw new \Exception( esc_html( "Unknown query type '{$this->filter}'." ) );
			}
		}

		$sorts = [];
		foreach ( $this->query['sort'] as $field_name => $order_by ) {
			$sorts[] = sprintf( '"%s" %s NULLS %s', $field_name, $order_by, $order_by === 'asc' ? 'FIRST' : 'LAST' ); //
			//  null values last if desc, first if asc
		}

		return sprintf( 'ORDER BY %s', implode( ', ', $sorts ) );
	}

	protected function _generate_select_with_keywords_sql(
		string $columns_for_select, string $filters,
		string $recommendation_object_id
	): array {
		/**
		 * Use keywords embeddings distance
		 */
		switch ( $this->filter ) {
			case WPSOLR_Option::OPTION_SEARCH_ITEM_FILTER_HYBRID_RRF:
				// We use rank (position in results) as distance
				$distance_name = 'rank';
				break;

			default:
				$distance_name = 'distance';
				break;
		}

		list( $distance, $distance_sort ) = $this->_get_distance_operator( 's.embedding', 'q.vector' );
		list( $last_view, $sql ) = $this->_generate_select_count_for_results_sql( $columns_for_select,
			"ORDER BY {$distance_name} ASC",
			$this->_generate_select_order_by_sql() );

		if ( $this->_is_suggestion_can_be_grouped() ) {
			$distinct_docs_sql = <<<SQL
				SELECT
				    t.id,
				    MIN({$distance_name}) AS {$distance_name}
				    , t.type, t.rank_in_type
				FROM
				    best_chunks as t
				GROUP BY /* group to deduplicate document vector chunks */
				    t.id
				    , t.type, t.rank_in_type 
			SQL;
		} else {
			$distinct_docs_sql = <<<SQL
				SELECT
				    t.id,
				    MIN({$distance_name}) AS {$distance_name}
				FROM
				    best_chunks as t
				GROUP BY /* group to deduplicate document vector chunks */
				    t.id
			SQL;
		}

		$sql = <<<SQL
			{$this->_create_pgai_embed_query_config( $recommendation_object_id )},
			{$this->_get_best_chunks_by_distance_sql( $columns_for_select, $filters, $distance, $distance_sort )},
			distinct_docs AS (
			    {$distinct_docs_sql}
			),
			{$sql}
		SQL;

		return [ $last_view, $sql ];
	}

	protected function _generate_select_count_for_results_sql( string $columns_for_select, string $order_by_distance, string $order_by_column ): array {
		$sql          = '';
		$view_results = 'results';
		$view_sql     = 'results';
		if ( ! empty( $this->filter ) && ! empty( trim( $this->query_string['query'] ?? '' ) ) ) {
			switch ( $this->filter ) {
				case WPSOLR_Option::OPTION_SEARCH_ITEM_FILTER_NEAR_TEXT:
					// Nothing to do
					break;

				case WPSOLR_Option::OPTION_SEARCH_ITEM_FILTER_HYBRID_RRF:
					list( $view_sql, $sql ) = $this->_generate_reciprocal_rank_fusion_sql( 'filters', $view_results );
					$sql .= ', ';
					break;
			}
		}

		if ( $this->_is_suggestion_can_be_grouped() ) {
			$max_nb_results = 0;
			foreach ( $this->get_wpsolr_query()->wpsolr_get_suggestion()[ WPSOLR_Option::OPTION_SUGGESTION_MODELS ] ?? [] as $model => $model_def ) {
				$max_nb_results = max( $max_nb_results, intval( $model_def[ WPSOLR_Option::OPTION_SUGGESTION_MODEL_NB ] ?? 0 ) );
			}
			$sql_results = <<<SQL
				SELECT
				    *,
				    COUNT(*) OVER () AS total_count
				FROM 
					distinct_docs
				WHERE 
				    rank_in_type <= {$max_nb_results}
				ORDER BY 
				    type, distance ASC
			SQL;

		} elseif ( ! empty( $this->_get_recommendation_object_id() ) ) {
			$sql_results = <<<SQL
				SELECT
				    *,
				    COUNT(*) OVER () AS total_count
				FROM 
					distinct_docs				
				ORDER BY 
				    distance ASC
			SQL;
		} else {
			$sql_results = <<<SQL
				SELECT
				    *,
				    COUNT(*) OVER () AS total_count
				FROM 
					distinct_docs				
			SQL;
		}

		$last_view = 'results_paginated';
		$sql       = <<<SQL
				{$view_results} AS (
					{$sql_results}
				),
				{$sql}
				{$last_view} AS (
					SELECT
						{$columns_for_select} , RF.total_count
					FROM
						{$view_sql} RF JOIN {$this->_postgresql_table_name()} T ON RF.ID = T.ID
					{$order_by_column}
					LIMIT
						{$this->query['size']} OFFSET {$this->query['from']}
				)
		SQL;

		return [ $last_view, $sql ];
	}

	protected function _transform_text_before_embedding( string $text ): string {
		/**
		 * Lowercase before embedding is a good practice
		 */

		$text = trim( $text );
		$text = mb_strtolower( $text, 'UTF-8' ); // (UTF-8 safe)
		$text = preg_replace( '/\s+/u', ' ', $text );

		return $text;
	}

	protected function _get_recommendation_object_id(): string {
		if ( ! empty( $recommendation = $this->get_recommendation() ) ) {
			switch ( $recommendation[ WPSOLR_Option::OPTION_RECOMMENDATION_TYPE ] ) {
				case WPSOLR_Option::OPTION_RECOMMENDATION_TYPE_NEAR_OBJECT:
					$object_id = $this->get_wpsolr_query()->get_wpsolr_ajax_context()['object_id'];
					if ( $object_id && get_post( $object_id ) ) {
						return $object_id;
					}
					throw new \Exception( sprintf( 'Model "%s" requires post_id.', esc_html( $recommendation[ WPSOLR_Option::OPTION_RECOMMENDATION_TYPE ] ) ) );

				default:
					throw new \Exception( sprintf( 'Model "%s" is unknown.', esc_html( $recommendation[ WPSOLR_Option::OPTION_RECOMMENDATION_TYPE ] ) ) );
			}

		}

		return '';
	}

	protected function _is_suggestion_can_be_grouped(): bool {
		return
			( WPSOLR_Option::OPTION_SEARCH_ITEM_FILTER_HYBRID_RRF !== $this->filter ) && // RFF and grouping not treated yet
			$this->get_wpsolr_query()->wpsolr_get_is_suggestion() &&
			( $this->get_wpsolr_query()->wpsolr_get_is_suggestion_type_content_grouped() ||
			  $this->get_wpsolr_query()->wpsolr_get_is_suggestion_type_content_grouped_sorted_by_position() );
	}

}
