<?php
/**
 * Values index class.
 *
 * Provides helpers for retrieving filter values (label/value pairs) stored in
 * the `fibofilters_valuelist` table and utility methods for building WHERE clauses
 * based on user‑supplied logic (or/and/range/search).
 *
 * @package FiboFilters\Index
 * @since   1.10.0
 */

declare( strict_types=1 );

namespace FiboFilters\Index;

use FiboFilters\Struct\QueryContext;
use FiboFilters\Util\Helpers;
use FiboFilters\WP\WPDB;
use FiboFilters\WP\WPDBSecond;
use FiboFilters\WPDBException;

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

/**
 * Class Values
 *
 * @since 1.10.0
 */
class Values extends AbstractIndex {
	/**
	 * {@inheritdoc}
	 */
	public function get_table_type(): string {
		return 'filters_valuelist';
	}

	/**
	 * {@inheritdoc}
	 */
	public function get_cache_group(): string {
		return 'values_query';
	}

	/**
	 * Inserts a value record into the custom table.
	 *
	 * Requires 'value', 'label', 'label_url', and 'source_id' in $args. If any of
	 * these are missing, the method exits early without touching the database.
	 *
	 * @param array             $args {
	 *                 Arguments used to build the record.
	 *
	 * @type string             $value Required. Raw data, e.g. a term ID or a number for a range.
	 * @type string             $label Required. Human-readable label for the value.
	 * @type string             $label_url URL related to the label.
	 * @type int                $source_id Required. ID of the data source.
	 * @type string             $parent_value Optional. Parent value. Defaults to 0.
	 * @type int                $order Optional. Ordering position. Defaults to 0.
	 * }
	 *
	 * @param QueryContext|null $ctx Optional. Custom query context for DB access.
	 *
	 * @return int Insert‑ID of the new value.
	 * @throws WPDBException When a database error occurs (only for WPDBSecond).
	 */
	public function save_value( array $args, ?QueryContext $ctx = null ): int {
		if (
			empty( $args['value'] ) ||
			empty( $args['source_id'] )
		) {
			return 0;
		}

		$ctx ??= new QueryContext();
		$wpdb = $this->get_wpdb( $ctx );

		$data = [
			'value'        => (string) $args['value'],
			'max_value'    => isset( $args['max_value'] ) ? (string) $args['max_value'] : '',
			'label'        => (string) $args['label'],
			'label_url'    => isset( $args['label_url'] ) ? (string) $args['label_url'] : '',
			'parent_value' => isset( $args['parent_value'] ) ? (string) $args['parent_value'] : '0',
			'source_id'    => (int) $args['source_id'],
			'order'        => isset( $args['order'] ) ? (int) $args['order'] : 0,
		];

		$wpdb->insert(
			$this->get_table_name( $ctx ),
			$data,
			[ '%s', '%s', '%s', '%s', '%s', '%d', '%d' ]
		);

		return $wpdb->db->insert_id;
	}

	/**
	 * Retrieves the ID of a value from the database based on the given arguments.
	 * If the value does not exist, it creates a new entry and returns its ID.
	 *
	 * This method also uses a cache to avoid redundant database queries for repeated lookups.
	 *
	 * @param array<string, mixed> $args {
	 *     Arguments used to find or create a value.
	 *
	 * @type string                $value The unique raw value.
	 * @type string                $label The human-readable label.
	 * @type string                $label_url The URL-friendly label.
	 * @type string                $parent_value Optional. Parent value, default is '0'.
	 * @type int                   $source_id The source identifier.
	 * @type int                   $order Optional. Order priority, default is 0.
	 * }
	 *
	 * @param QueryContext|null    $ctx Optional. Query context for determining DB and language. Default null.
	 *
	 * @return int The ID of the existing or newly created value.
	 * @throws WPDBException When a database error occurs (only for WPDBSecond).
	 */
	public function get_value_id_or_create( array $args, ?QueryContext $ctx = null ): int {
		$ctx  = $ctx ?? new QueryContext();
		$wpdb = $this->get_wpdb( $ctx );

		$data = [
			'value'        => (string) $args['value'],
			'max_value'    => isset( $args['max_value'] ) ? (string) $args['max_value'] : '',
			'label'        => (string) $args['label'],
			'label_url'    => (string) $args['label_url'],
			'parent_value' => isset( $args['parent_value'] ) ? (string) $args['parent_value'] : '0',
			'source_id'    => (int) $args['source_id'],
			'order'        => isset( $args['order'] ) ? (int) $args['order'] : 0,
		];

		$cache_key  = 'value_for_source_' . $data['source_id'];
		$cache_key .= '_' . Helpers::generate_cache_hash( $data['value'], 15 ) . '_' . $data['parent_value'];
		$cache_key .= $data['parent_value'] === '0' ? '' : '_' . Helpers::generate_cache_hash( $data['parent_value'], 10 );
		$cache_key .= empty( $data['max_value'] ) ? '' : '_' . Helpers::generate_cache_hash( $data['max_value'], 10 );

		$cached = $this->get_cached_items( $cache_key, $ctx );
		if ( ! empty( $cached ) ) {
			return $cached;
		}

		$table_valuelist = $this->get_table_name( $ctx );
		// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		if ( empty( $data['max_value'] ) ) {
			$value_id = (int) $wpdb->get_var(
				$wpdb->prepare(
					"SELECT value_id
	                    FROM $table_valuelist
	                    WHERE value = %s
	                    AND parent_value = %s
						AND source_id = %d",
					$data['value'],
					$data['parent_value'],
					$data['source_id']
				)
			);
		} else {
			$value_id = (int) $wpdb->get_var(
				$wpdb->prepare(
					"SELECT value_id
	                    FROM $table_valuelist
	                    WHERE value = %s
	                    AND max_value = %s
	                    AND parent_value = %s
						AND source_id = %d",
					$data['value'],
					$data['max_value'],
					$data['parent_value'],
					$data['source_id']
				)
			);
		}
		// phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared

		if ( empty( $value_id ) ) {
			$value_id = $this->save_value( $data, $ctx );
		}

		if ( ! empty( $value_id ) ) {
			$this->set_cached_items( $cache_key, $value_id, $ctx );
		}

		return $value_id;
	}

	/**
	 * Generic values getter.
	 *
	 * @param array<string,mixed> $args {
	 *     Query arguments.
	 *
	 * @type bool                 $cache Whether to use object cache. Default false.
	 * @type string               $cache_key Cache key. Default 'values'.
	 * @type int                  $source_id Restrict to a specific source ID. Default 0.
	 * @type array                $user_query List of user terms / ranges. Default empty.
	 * @type string               $logic 'or', 'and', 'range', or 'search'. Default 'or'.
	 * @type string               $select SELECT clause. Default '*'.
	 * @type string               $output 'get_results' (default) or 'get_col'.
	 * }
	 *
	 * @param QueryContext|null   $ctx Query context.
	 *
	 * @return array<int,array>|int[] Array of associative rows or list of IDs.
	 *
	 * @throws WPDBException When a database error occurred (only for WPDBSecond).
	 */
	public function get_values( array $args, ?QueryContext $ctx = null ): array {
		$ctx ??= new QueryContext();
		$wpdb = $this->get_wpdb( $ctx );

		$defaults = [
			'cache'      => true,
			'cache_key'  => 'values_' . Helpers::generate_cache_hash( $args ),
			'source_id'  => 0,
			'user_query' => [],
			'logic'      => 'or',
			'select'     => '*',
			'output'     => 'get_results',
		];

		$args = wp_parse_args( $args, $defaults );

		$cached = $this->get_cached_items( $args['cache_key'], $ctx );
		if ( $args['cache'] && is_array( $cached ) ) {
			return $cached;
		}

		$where = '';

		// Logic.
		if ( ! empty( $args['user_query'] ) && is_array( $args['user_query'] ) ) {
			$where .= $this->get_where_clause_for_logic( $args['logic'], $args['user_query'], $wpdb );
		}

		// Source ID.
		$source_id = absint( $args['source_id'] );
		if ( $source_id > 0 ) {
			$where .= $wpdb->prepare( ' AND source_id = %d', $source_id );
		}

		$select = $args['select'];

		$table_valuelist = $this->get_table_name( $ctx );

		// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$sql = "SELECT $select
			FROM $table_valuelist
			WHERE 1 = 1
               $where";
		// phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared

		if ( $args['output'] === 'get_col' ) {
			$values = $wpdb->get_col( $sql ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
		} else {
			$values = $wpdb->get_results( $sql, ARRAY_A ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
		}

		if ( empty( $values ) || ! is_array( $values ) ) {
			return [];
		}

		if ( $args['cache'] ) {
			$this->set_cached_items( $args['cache_key'], $values, $ctx );
		}

		return $values;
	}

	/**
	 * Return all values assigned to a specific source.
	 *
	 * @param int               $source_id Source ID.
	 * @param QueryContext|null $ctx Query context.
	 *
	 * @return array<int,array> Array of values.
	 *
	 * @throws WPDBException When a database error occurred (only for WPDBSecond).
	 */
	public function get_values_by_source_id( int $source_id, ?QueryContext $ctx = null ): array {
		$values = [];

		if ( empty( $source_id ) ) {
			return $values;
		}

		return $this->get_values(
			[
				'cache_key' => 'values_of_source_id_' . $source_id,
				'source_id' => $source_id,
				'select'    => 'value_id, value, max_value, label, label_url, parent_value, `order`',
			],
			$ctx
		);
	}

	/**
	 * Retrieve a single value row based on its label URL.
	 *
	 * @param string            $label_url Label slug.
	 * @param int               $source_id Source ID.
	 * @param QueryContext|null $ctx Query context.
	 *
	 * @return array<int,array>|array[] Empty array when not found.
	 *
	 * @throws WPDBException When a database error occurred (only for WPDBSecond).
	 */
	public function get_value_by_label_url( string $label_url, int $source_id, ?QueryContext $ctx = null ): array {
		$ctx ??= new QueryContext();
		$wpdb = $this->get_wpdb( $ctx );

		$value = [];

		$cache_key = 'values_by_label_url_' . Helpers::generate_cache_hash( $label_url . $source_id );

		$cached = $this->get_cached_items( $cache_key, $ctx );
		if ( is_array( $cached ) ) {
			return $cached;
		}

		$table_valuelist = $this->get_table_name( $ctx );

		// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$sql = $wpdb->prepare(
			"
			SELECT value_id,
			       value,
			       label,
			       label_url,
			       parent_value,
			       `order`
			FROM $table_valuelist
			WHERE label_url = %s AND source_id = %d
		",
			$label_url,
			absint( $source_id )
		);
		// phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared

		$res = $wpdb->get_results( $sql, ARRAY_A ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
		if ( ! empty( $res ) ) {
			$value = $res;
		}

		$this->set_cached_items( $cache_key, $value, $ctx );

		return $value;
	}

	/**
	 * Return value IDs matching a user query.
	 *
	 * @param array<int|string> $user_query User‑supplied query terms.
	 * @param int               $source_id Source ID.
	 * @param string            $logic Logic: 'or', 'and', 'range', 'search'.
	 * @param QueryContext|null $ctx Query context.
	 *
	 * @return int[] List of value IDs.
	 *
	 * @throws WPDBException When a database error occurred (only for WPDBSecond).
	 */
	public function get_values_ids_by_user_query( array $user_query, int $source_id, string $logic = 'or', ?QueryContext $ctx = null ): array {
		$ids = [];

		$args = [
			'user_query' => $user_query,
			'source_id'  => $source_id,
			'logic'      => $logic,
			'select'     => 'value_id',
			'output'     => 'get_col',
		];
		$res  = $this->get_values( $args, $ctx );

		if ( ! empty( $res ) ) {
			$ids = array_map( 'intval', $res );
		}

		return $ids;
	}

	/**
	 * Deletes all records from the value list table whose value_id
	 * is not present in the provided list of valid IDs.
	 *
	 * @param int[]             $correct_value_ids Array of valid value IDs.
	 * @param QueryContext|null $ctx               Optional query context.
	 *
	 * @return bool True if the deletion query ran successfully; false on error or if the list is empty.
	 * @throws WPDBException When a database error occurred (only for WPDBSecond).
	 */
	public function delete_orphaned_values( array $correct_value_ids, ?QueryContext $ctx = null ): bool {
		$ctx           ??= new QueryContext();
		$wpdb            = $this->get_wpdb( $ctx );
		$table_valuelist = $this->get_table_name( $ctx );
		$success         = false;

		if ( ! empty( $correct_value_ids ) ) {
			$placeholders = array_fill( 0, count( $correct_value_ids ), '%d' );
			$format       = implode( ', ', $placeholders );

			// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare
			$results = $wpdb->query(
				$wpdb->prepare(
					"DELETE FROM $table_valuelist WHERE value_id NOT IN ($format)",
					$correct_value_ids
				)
			);
			// phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare

			$success = false !== $results;
		}

		return $success;
	}

	/**
	 * Build WHERE clause fragment according to logic.
	 *
	 * @param string               $logic Logic: 'or', 'and', 'range', 'search', 'range-continuous', 'range-discrete', 'search-fibosearch-pro'.
	 * @param array                $user_query User query values.
	 * @param WPDB|WPDBSecond|null $wpdb DB instance for escaping.
	 *
	 * @return string WHERE fragment (prefixed with leading space).
	 */
	private function get_where_clause_for_logic( string $logic = 'or', array $user_query = [], $wpdb = null ): string {
		$where = '';

		if ( $logic === 'range' || $logic === 'range-discrete' || $logic === 'range-continuous' ) {
			$from = $user_query[0] ?? null;
			$to   = $user_query[1] ?? null;

			// Break early.
			if ( ! is_numeric( $from ) || ! is_numeric( $to ) ) {
				return ' AND 1=0';
			}

			$where .= ' AND (';
			if ( $from >= 0 ) {
				if ( $logic === 'range-continuous' ) {
					$where .= $wpdb->prepare( 'max_value >= %f', $from );
				} else {
					$where .= $wpdb->prepare( 'value >= %f', $from );
				}
			}
			if ( $to >= 0 ) {
				$where .= $from >= 0 ? ' AND ' : '';
				$where .= $wpdb->prepare( 'value <= %f', $to );
			}
			if ( $from < 0 && $to < 0 ) {
				$where .= '1=1';
			}
			$where .= ')';

		} elseif ( $logic === 'search' ) {
			if ( $user_query[0] !== '' ) {
				$phrase_escaped = '%' . $wpdb->db->esc_like( $user_query[0] ) . '%';
				$where         .= $wpdb->prepare( ' AND value LIKE %s', $phrase_escaped );
			}
		} elseif ( $logic === 'search-fibosearch-pro' ) {
			// Return all values, searching is done by FiboSearch Pro.
			$where .= ' AND 1 = 1';
		} else {
			$placeholders = array_fill( 0, count( $user_query ), '%s' );
			$format       = implode( ', ', $placeholders );
			$where       .= $wpdb->prepare( " AND label_url IN ($format)", $user_query ); // phpcs:ignore WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		}

		return $where;
	}
}
