<?php
/**
 * Option lookups for mapped filters.
 *
 * @package Mapsemble
 */

namespace Mapsemble\Feed;

use Mapsemble\MapConfig;

/**
 * Answers "what can this filter be?" for the widget's option pickers:
 * the top values by occurrence among published posts of the map's type,
 * as `{value: label}` where `value` is what the widget will echo back in
 * `filters` — a term slug for taxonomies, the raw value for meta.
 *
 * That value convention is a locked wire contract shared with
 * FeedQuery::filter_clauses(); changing it would break every filter an
 * account has saved on Mapsemble against this site.
 */
class FilterOptions {

	/**
	 * How many options one lookup returns, mirroring the Drupal plugin.
	 */
	private const LIMIT = 10;

	/**
	 * The options for one mapped filter.
	 *
	 * @param MapConfig $config The map.
	 * @param string    $slug   Remote filter slug.
	 * @param string    $search Optional search to narrow by label.
	 * @return array<string, string> value => label, best first.
	 */
	public function options_for( MapConfig $config, string $slug, string $search = '' ): array {
		$mapping = $config->filter_mappings[ $slug ] ?? null;
		if ( ! is_array( $mapping ) ) {
			return array();
		}
		$ref = FieldRef::parse( $mapping['field'] ?? null );
		if ( null === $ref ) {
			return array();
		}

		return FieldRef::KIND_TAX === $ref['kind']
			? $this->taxonomy_options( $config->post_type, $ref['name'], $search )
			: $this->meta_options( $config->post_type, $ref['name'], $search );
	}

	/**
	 * Term options: slug => name, by how many published posts carry the term.
	 *
	 * @param string $post_type Post type.
	 * @param string $taxonomy  Taxonomy name.
	 * @param string $search    Optional label search.
	 * @return array<string, string>
	 */
	private function taxonomy_options( string $post_type, string $taxonomy, string $search ): array {
		global $wpdb;

		$sql    = "SELECT t.slug, t.name, COUNT(*) AS uses
			FROM {$wpdb->term_relationships} tr
			INNER JOIN {$wpdb->term_taxonomy} tt ON tt.term_taxonomy_id = tr.term_taxonomy_id
			INNER JOIN {$wpdb->terms} t ON t.term_id = tt.term_id
			INNER JOIN {$wpdb->posts} p ON p.ID = tr.object_id
			WHERE tt.taxonomy = %s
				AND p.post_type = %s
				AND p.post_status = 'publish'";
		$params = array( $taxonomy, $post_type );
		if ( '' !== $search ) {
			$sql     .= ' AND t.name LIKE %s';
			$params[] = '%' . $wpdb->esc_like( $search ) . '%';
		}
		$sql     .= ' GROUP BY t.term_id ORDER BY uses DESC, t.name ASC LIMIT %d';
		$params[] = self::LIMIT;

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $sql is literals and placeholders only, prepared just above with the separate params; core has no by-occurrence term API.
		$rows = $wpdb->get_results( $wpdb->prepare( $sql, $params ) );

		$options = array();
		foreach ( $rows as $row ) {
			$options[ (string) $row->slug ] = (string) $row->name;
		}
		return $options;
	}

	/**
	 * Meta options: distinct values by occurrence among published posts.
	 *
	 * @param string $post_type Post type.
	 * @param string $meta_key  Meta key.
	 * @param string $search    Optional value search.
	 * @return array<string, string>
	 */
	private function meta_options( string $post_type, string $meta_key, string $search ): array {
		global $wpdb;

		$sql    = "SELECT pm.meta_value AS value, COUNT(*) AS uses
			FROM {$wpdb->postmeta} pm
			INNER JOIN {$wpdb->posts} p ON p.ID = pm.post_id
			WHERE pm.meta_key = %s
				AND p.post_type = %s
				AND p.post_status = 'publish'
				AND pm.meta_value <> ''";
		$params = array( $meta_key, $post_type );
		if ( '' !== $search ) {
			$sql     .= ' AND pm.meta_value LIKE %s';
			$params[] = '%' . $wpdb->esc_like( $search ) . '%';
		}
		$sql     .= ' GROUP BY pm.meta_value ORDER BY uses DESC, pm.meta_value ASC LIMIT %d';
		$params[] = self::LIMIT;

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $sql is literals and placeholders only, prepared just above with the separate params; a capped GROUP BY, limits documented in readme.
		$rows = $wpdb->get_results( $wpdb->prepare( $sql, $params ) );

		$options = array();
		foreach ( $rows as $row ) {
			$options[ (string) $row->value ] = (string) $row->value;
		}
		return $options;
	}
}