<?php

namespace wpsolr\core\classes\engines\postgresql;

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

/**
 * Some common methods of the PostgresSQL_Doctrine client.
 *
 */
trait WPSOLR_PostgreSQL_Client {
	use WPSOLR_Client;

	protected array $fields_type = [];

	protected $wpsolr_type = 'wpsolr_types';

	// Unique id to store attached decoded files.
	protected $WPSOLR_DOC_ID_ATTACHMENT = 'wpsolr_doc_id_attachment';

	/** @var WPSOLR_PostgreSQL_Connection */
	protected $search_engine_client;

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

	// Index conf files
	protected $FILE_CONF_ES_INDEX_5 = 'wpsolr_index_5.json';
	protected $FILE_CONF_ES_INDEX_6 = 'wpsolr_index_6.json';
	protected $FILE_CONF_ES_INDEX_7 = 'wpsolr_index_7.json';

	public function get_has_exists_filter(): bool {
		return false;
	}

	/**
	 * Try to fix the current index configuration before retrying
	 *
	 * @param $error_msg
	 * @param array $documents
	 * @param array $formatted_docs
	 *
	 * @return bool
	 */
	protected function _add_missing_columns( array $field_names ): bool {
		$alter_columns = [];
		foreach ( $field_names as $field_name ) {
			$field_type      = $this->_get_field_data_type( $field_name );
			$alter_columns[] = sprintf( ' ADD COLUMN IF NOT EXISTS %s %s',
				$this->_postgresql_escape_sql_quote_identifier( $field_name ), $field_type );
		}

		$this->_postgresql_execute_query_array( sprintf( 'ALTER TABLE %%1s %s', implode( ",\n", $alter_columns ) ) );

		// Fixed
		return true;
	}

	protected function _get_field_data_type( string $field_name ): string {
		$this->_get_predefined_field_data_dates();

		if ( isset( $this->fields_type[ $field_name ] ) ) {
			return $this->fields_type[ $field_name ];
		}

		if ( $field_name === WpSolrSchema::_FIELD_NAME_ID ) {
			return ( $this->fields_type[ $field_name ] = 'text' ); // Primary field cannot be an array
		}

		$field_type = WpSolrSchema::DEFAULT_FIELD_TYPES[ $field_name ] ?? WpSolrSchema::get_custom_field_dynamic_type( $field_name );

		// Default fields can be scalar or arrays, other dynamic fields only arrays (for filters)
		$field_type_to_be_array = ! isset( WpSolrSchema::DEFAULT_FIELD_TYPES[ $field_name ] ) || is_array( $field_type );

		// ['int'] => 'int'
		$field_type = is_array( $field_type ) ? $field_type[0] : $field_type;

		switch ( $field_type ) {
			case WpSolrSchema::_SOLR_DYNAMIC_TYPE_INTEGER:
			case WpSolrSchema::_SOLR_DYNAMIC_TYPE_INTEGER_LONG:
				$type = $field_type_to_be_array ? 'BIGINT[]' : 'BIGINT';
				break;

			case WpSolrSchema::_SOLR_DYNAMIC_TYPE_DATE:
				$type = $field_type_to_be_array ? 'timestamptz[]' : 'timestamptz';
				break;

			case WpSolrSchema::_SOLR_DYNAMIC_TYPE_FLOAT:
			case WpSolrSchema::_SOLR_DYNAMIC_TYPE_FLOAT_DOUBLE:
				$type = $field_type_to_be_array ? 'double precision[]' : 'double precision';
				break;

			case WpSolrSchema::_SOLR_DYNAMIC_TYPE_S:
			case WpSolrSchema::_SOLR_DYNAMIC_TYPE_STRING:
			case WpSolrSchema::_SOLR_DYNAMIC_TYPE_STRING1:
			case WpSolrSchema::_SOLR_DYNAMIC_TYPE_TEXT:
				$type = $field_type_to_be_array ? 'text[]' : 'text';
				break;

			case WpSolrSchema::_SOLR_DYNAMIC_TYPE_BASE64:
				$type = 'bytea';
				break;

			default:
				throw new \Exception( esc_html( "$field_type type not implemented for field $field_name" ) );
		}

		return ( $this->fields_type[ $field_name ] = $type );
	}

	protected function _get_predefined_field_data_dates(): void {
		foreach (
			WpSolrSchema::DEFAULT_DATE_FIELDS as $date_field
		) {
			$this->fields_type[ $date_field ]                                            = 'timestamptz';
			$this->fields_type[ $date_field . WpSolrSchema::_SOLR_DYNAMIC_TYPE_INTEGER ] = 'BIGINT';
			$this->fields_type[ $date_field . WpSolrSchema::_FIELD_NAME_YEAR_I ]         = 'BIGINT';
			$this->fields_type[ $date_field . WpSolrSchema::_FIELD_NAME_YEAR_MONTH_I ]   = 'BIGINT';
			$this->fields_type[ $date_field . WpSolrSchema::_FIELD_NAME_YEAR_WEEK_I ]    = 'BIGINT';
			$this->fields_type[ $date_field . WpSolrSchema::_FIELD_NAME_YEAR_DAY_I ]     = 'BIGINT';
			$this->fields_type[ $date_field . WpSolrSchema::_FIELD_NAME_MONTH_DAY_I ]    = 'BIGINT';
			$this->fields_type[ $date_field . WpSolrSchema::_FIELD_NAME_WEEK_DAY_I ]     = 'BIGINT';
			$this->fields_type[ $date_field . WpSolrSchema::_FIELD_NAME_DAY_HOUR_I ]     = 'BIGINT';
			$this->fields_type[ $date_field . WpSolrSchema::_FIELD_NAME_DAY_MINUTE_I ]   = 'BIGINT';
			$this->fields_type[ $date_field . WpSolrSchema::_FIELD_NAME_DAY_SECOND_I ]   = 'BIGINT';
		}
	}

	protected function _is_field_type_array( $field_name ): bool {
		return str_ends_with( $this->_get_field_data_type( $field_name ), '[]' );
	}


	protected function _is_field_type_text( $field_name ): bool {
		return str_starts_with( $this->_get_field_data_type( $field_name ), 'text' );
	}

	/**
	 * Fix the current index configuration with the guessed doc type
	 *
	 * @param string $index_property
	 * @param string $doc_type
	 *
	 * @return void
	 */
	protected
	function _fix_error_doc_type(
		$index_property, $doc_type
	) {

		// To be able to retry now, save it on current object index
		$this->index[ $index_property ] = $doc_type;

		$option_indexes = WPSOLR_Service_Container::getOption()->get_option_indexes();

		if ( isset( $option_indexes[ WPSOLR_Option::OPTION_INDEXES_INDEXES ][ $this->index_indice ] ) ) {
			// To prevent retry later, save it in the index options

			if ( is_null( $doc_type ) ) {
				// null value means "unset"

				unset( $option_indexes[ WPSOLR_Option::OPTION_INDEXES_INDEXES ][ $this->index_indice ][ $index_property ] );

			} else {

				$option_indexes[ WPSOLR_Option::OPTION_INDEXES_INDEXES ][ $this->index_indice ][ $index_property ] = $doc_type;
			}

			// Save it now
			update_option( WPSOLR_Option::OPTION_INDEXES, $option_indexes );
		}

	}

	/**
	 * @return array
	 */
	public
	function get_index() {

		$params = [ 'index' => $this->index_label ];

		if ( $this->index && ! empty( $this->_get_index_doc_type() ) ) {
			$params['type'] = $this->_get_index_doc_type();
		}

		return $params;
	}

	/**
	 * This index has the deprecated "type"?
	 *
	 * @return bool
	 */
	protected
	function _get_index_doc_type() {
		return $this->index['index_doc_type'] ?? $this->wpsolr_type;
	}

	/**
	 * @param string $index_label
	 */
	public
	function set_index_label(
		$index_label
	) {
		$this->index_label = $index_label;
	}

	/**
	 * @return string
	 */
	public
	function get_index_label() {
		return $this->index_label;
	}

	protected
	function create_search_engine_client(
		$config, $database_name = 'wpsolr'
	) {
		


		return new WPSOLR_PostgreSQL_Connection(
			$config['host'],
			$config['port'],
			$database_name,
			$config['username'],
			$config['password']
		);

	}

	/**
	 * Retrieve the live PostgresSQL_Doctrine version
	 *
	 * @return string
	 * @throws \Exception
	 */
	protected
	function get_version() {

		$status      = $this->search_engine_client->getStatus();
		$status_data = $status->getResponse()->getData();
		if ( ! empty( $status_data ) && ! empty( $status_data['message'] ) ) {
			throw new \Exception( esc_html( $status_data['message'] ) );
		}

		$version = $this->search_engine_client->getVersion();

		if ( version_compare( $version, '5', '<' ) ) {
			throw new \Exception( sprintf( 'WPSOLR works only with PostgresSQL_Doctrine >= 5. Your version is %s.', esc_html( $version ) ) );
		}

		return $version;
	}

	/**
	 * Transform a string in a date.
	 *
	 * @param $date_str String date to convert from.
	 *
	 * @return string
	 */
	public
	function search_engine_client_format_date(
		$date_str
	) {

		if ( is_null( $date_str ) ) {
			return null;
		}

		if ( is_int( $date_str ) ) {

			$timestamp = $date_str;

		} else {

			$timestamp = strtotime( $date_str );
		}

		$string = wp_date( 'Y-m-d\TH:i:s\Z', $timestamp );

		return $string;
	}

	protected
	function get_index_stats() {
		return $this->search_engine_client->indices()->stats( $this->get_index() );
	}

	/**
	 * Create a match_all query
	 *
	 * @return array
	 */
	protected
	function _create_match_all_query() {

		$params         = $this->get_index();
		$params['body'] = [ 'query' => [ 'match_all' => new \stdClass() ] ];

		return $params;
	}

	/**
	 * Create a bool query
	 *
	 * @param array $bool_query
	 *
	 * @return string
	 */
	protected
	function _create_bool_query(
		$bool_queries
	) {
		return empty( $bool_queries ) ? ' ( 1 = 1) ' : sprintf( ' %s ', implode( ' AND ', $bool_queries ) );
	}

	/**
	 * Create a query
	 *
	 * @param array $query
	 *
	 * @return array
	 */
	protected
	function _create_query(
		$query
	) {

		$params         = $this->get_index();
		$params['body'] = $query;

		return $params;
	}

	/**
	 * @return string
	 */
	protected function _get_configuration_file_from_version(): string {
		try {

			$version = $this->search_engine_client->info()['version']['number'];

			$file = $this->FILE_CONF_ES_INDEX_7;
			if ( version_compare( $version, '6', '<' ) ) {

				$file = $this->FILE_CONF_ES_INDEX_5;

			} elseif ( version_compare( $version, '7', '<' ) ) {

				$file = $this->FILE_CONF_ES_INDEX_6;
			}

		} catch ( \Exception $e ) {
			// Elasticpress does not give access to cluster infos

			$file = $this->FILE_CONF_ES_INDEX_6;
		}

		return $file;
	}

	/**
	 * @return ClientBuilder
	 */
	protected function _get_client_builder() {
		return ClientBuilder::create();
	}

	protected function _get_maintenance_database_connection(): WPSOLR_PostgreSQL_Connection {
		$config_clone                = $this->config;
		$config_clone['index_label'] = $this->config['path'] ?: 'postgres'; // default maintenance database

		return $this->create_search_engine_client( $config_clone );
	}

	/**
	 * Get the analysers available
	 * @return array
	 */
	static public function get_analysers() {
		return [];
	}

	/*************************************************************************************************
	 *
	 * PostgreSQL utilities
	 *
	 ************************************************************************************************/
	protected function _postgresql_execute_query_array( string $sql_query, array $params = [] ): array {
		return $this->search_engine_client->execute_statement_as_array( sprintf( $sql_query, $this->_postgresql_table_name() ), $params );
	}

	protected function _postgresql_execute_query( string $sql_query, array $params = [] ): \PgSql\Result {
		return $this->search_engine_client->execute_statement( sprintf( $sql_query, $this->_postgresql_table_name() ), $params );
	}

	protected function _postgresql_build_query( array $query_array ): string {
		return implode( ' ', $this->_postgresql_build_query_array( $query_array ) );
	}

	protected function _postgresql_build_query_array( array $query_array ): array {
		$results = [];
		foreach ( $query_array as $query_operator => $query ) {
			if ( is_int( $query_operator ) ) {
				$query_operator = key( $query );
				$query          = $query[ key( $query ) ];
			}

			if ( is_array( $query ) ) {
				$field_name  = key( $query );
				$field_value = $query[ key( $query ) ];
			} else {
				$field_name  = $query;
				$field_value = '';
			}

			if ( in_array( $query_operator, [ 'ALL', 'ANY' ] ) && ! $this->_is_field_type_array( $field_name ) ) {
				// type field is not an array (only exception)
				$query_operator = 'IN';
			}

			switch ( $query_operator ) {
				case 'ALL':
					$results[] = sprintf( '%s @> ARRAY[%s]',
						$field_name,
						implode( ', ',
							array_map( function ( $value ) use ( $field_name, $field_value ) {
								return $this->_postgresql_escape_sql_double_quote_value_if_type_string( $field_name, $value );
							}, $field_value ) )
					);
					break;

				case 'ANY':
				case 'IN':
					/**
					 * Works for:
					 *  - Scalar value → matches if in the list
					 *  - Array value → matches if any array element is in the list
					 *  - NULL → safely ignored
					 */
					$results[] = sprintf( ( 'IN' === $query_operator ) ? ' (%1$s IN (%2$s)) ' : ' (COALESCE(%1$s, \'{}\') && ARRAY[%2$s]) ',
						$field_name,
						implode( ', ',
							array_map( function ( $value ) use ( $field_name ) {
								return $this->_postgresql_escape_sql_double_quote_value_if_type_string( $field_name, $value );
							}, $field_value ) )
					);
					break;

				case 'EQUAL':
					$results[] = sprintf( ' (%s = %s) ', $field_name,
						$this->_postgresql_escape_sql_quote_value_if_type_string( $field_name, $field_value )
					);
					break;

				case 'NOT_EQUAL':
					$results[] = sprintf( ' (%s != %s) ', $field_name,
						$this->_postgresql_escape_sql_quote_value_if_type_string( $field_name, $field_value )
					);
					break;

				case 'AND':
				case 'OR':
					$results[] = sprintf( '(%s)', implode( " {$query_operator} ", $this->_postgresql_build_query_array( $query ) ) );
					break;

				case 'NOT':
					$results[] = sprintf( ' (NOT (%s)) ', implode( ' AND', $this->_postgresql_build_query_array( $query ) ) );
					break;

				case 'IS_NULL':
					$results[] = sprintf( ' (%s IS NULL) ', $field_name );
					break;

				case 'range':
					if ( $this->_is_field_type_array( $field_name ) ) {
						$field_name = "ANY({$field_name})";
					}
					$val_from = $field_value['gt'] ?? $field_value['gte'] ?? $field_value['from'] ?? '';
					$op_from  = '<=';
					$val_to   = $field_value['lt'] ?? $field_value['lte'] ?? $field_value['to'] ?? '';
					$op_to    = '>=';
					if ( ! empty( $field_value['gt'] ) ) {
						$op_from = '<';
					}
					if ( ! empty( $field_value['lt'] ) ) {
						$op_to = '>';
					}

					if ( ! empty( $val_from ) && ! empty( $val_to ) ) {
						$results[] = sprintf( ' (%s %s %s AND %s %s %s) ', $val_from, $op_from, $field_name, $val_to, $op_to, $field_name );
					} else if ( ! empty( $val_from ) ) {
						$results[] = sprintf( ' (%s %s %s) ', $val_from, $op_from, $field_name );
					} else {
						$results[] = sprintf( ' (%s %s %s) ', $val_to, $op_to, $field_name );
					}
					break;

				default:
					//$results = $this->_postgresql_build_query_array( $query );
					throw new \Exception( sprintf( 'Unknown query operator "%s"', esc_html( $query_operator ) ) );
			}
		}

		return $results ?: [ ' (1=1) ' ];
	}

	protected function _postgresql_table_name(): string {
		$table_name = $this->_postgresql_escape_sql_quote_identifier( $this->_get_schema_name() );

		return "{$table_name}.{$table_name}"; // shema.table
	}

	protected function _get_schema_name(): string {
		return $this->config['index_label'];
	}

	protected function _get_table_embedding_store_name(): string {
		return sprintf( '%s.%s_embedding_store', $this->_get_schema_name(), $this->_get_schema_name() );
	}

	protected function _get_index_bm25_name(): string {
		return sprintf( '%s.%s_bm25', $this->_get_schema_name(), $this->_get_schema_name() );
	}

	protected function _postgresql_table_vector_name(): string {
		$table_name        = $this->_postgresql_escape_sql_quote_identifier( $this->_get_schema_name() );
		$table_vector_name = $this->_postgresql_escape_sql_quote_identifier( sprintf( '%s_vector', $this->_get_schema_name() ) );

		return "$table_name.$table_vector_name"; // shema.table
	}

	protected function _postgresql_escape_sql_quote_value_if_type_string( string $field_name, string $value ): string {
		return $this->_is_field_type_text( $field_name ) ? $this->search_engine_client->escape_literal( $value ) : $value;
	}

	protected function _postgresql_escape_sql_double_quote_value_if_type_string( string $field_name, string $value ): string {
		return $this->_is_field_type_text( $field_name ) ?
			sprintf( "'%s'", $this->search_engine_client->escape_string( $value ) ) :
			$value;
	}

	function _to_postgresql_array( string $field_type, mixed $value ) {
		if ( ! str_ends_with( $field_type, '[]' ) ) {
			// single value
			return $value;
		}

		if ( ! is_array( $value ) ) {
			$value = [ $value ];
		}

		// Convert arrays to PostgreSQL array literals
		if ( ! str_starts_with( $field_type, 'text' ) ) {
			// single value
			return '{' . implode( ',', array_map( fn( $v ) => $v, $value ) ) . '}';
		}

		return '{' . implode( ',', array_map( fn( $v ) => '"' . addslashes( $v ) . '"', $value ) ) . '}';
	}

	protected function _postgresql_escape_sql_quote_identifier( string $value ): string {
		return $this->search_engine_client->escape_identifier( $value );
	}

	protected function _postgresql_count( array $where ): int {

		$results = $this->_postgresql_execute_query_array( sprintf( '
			SELECT count(0) as count FROM %%1s WHERE %s
			LIMIT 1;
			', $this->_postgresql_build_query( $where ) ) );

		return $results[0]['count'] ?? 0;
	}

	protected function _get_concatenated_field_name(): string {
		return WpSolrSchema::_FIELD_NAME_TEXT_CONCATENATED_FIELDS;
	}

}
