<?php
/**
 * Vehicle query builder.
 *
 * Centralizes the SQL that joins wp_posts with the trx_ad_vehicle_specs
 * table for faceted search and listing aggregations. Callers pass in a
 * FilterParams DTO; this class returns prepared SQL + bindings or executes.
 *
 * Per ADR-03: this is the single entry point for vehicle search SQL —
 * REST controllers and Elementor/Gutenberg renderers must route here
 * rather than building queries inline.
 *
 * @package ThemeREX\AutoDealer
 * @version 1.0.0
 */

declare(strict_types=1);

namespace ThemeREX\AutoDealer\Search;

/**
 * Builder + executor for the trx_ad_vehicle_specs faceted-search query.
 */
final class VehicleQuery {

	/**
	 * Return the fully-qualified specs table name.
	 */
	public static function table(): string {
		global $wpdb;
		return $wpdb->prefix . 'trx_ad_vehicle_specs';
	}

	/**
	 * Build the WHERE clause + values from a FilterParams.
	 *
	 * @return array{0:string,1:array<int,mixed>} [where_sql, prepare_values]
	 */
	public function buildWhere( FilterParams $params ): array {
		global $wpdb;

		$where   = array(
			"p.post_type = 'trx_ad_listing'",
			"p.post_status = 'publish'",
		);
		$prepare = array();

		// Map filter-key → taxonomy slug for inputs that come in as a
		// taxonomy term slug from the archive form but are stored
		// denormalized in the specs table as the term NAME. We accept
		// EITHER the slug or the name to match either way.
		$tax_for = array(
			'make'         => 'trx_ad_make',
			'model'        => 'trx_ad_model',
			'condition'    => 'trx_ad_condition',
			'body_style'   => 'trx_ad_body_style',
			'fuel_type'    => 'trx_ad_fuel_type',
			'transmission' => 'trx_ad_transmission',
			'drivetrain'   => 'trx_ad_drivetrain',
			'color'        => 'trx_ad_color',
			'vehicle_type' => 'trx_ad_vehicle_type',
		);
		foreach ( $params->getText() as $key => $value ) {
			$column = FilterParams::TEXT_FILTERS[ $key ];

			$candidates = array( (string) $value );
			$term_id    = 0;
			if ( isset( $tax_for[ $key ] ) ) {
				$term = get_term_by( 'slug', (string) $value, $tax_for[ $key ] );
				if ( $term instanceof \WP_Term ) {
					$candidates[] = $term->name;
					$term_id      = (int) $term->term_id;
				}
			}
			$candidates = array_values( array_unique( array_filter( $candidates, static fn( $v ) => '' !== (string) $v ) ) );

			$placeholders = implode( ',', array_fill( 0, count( $candidates ), 'LOWER(%s)' ) );
			$specs_clause = "LOWER(s.{$column}) IN ({$placeholders})";

			// Belt-and-suspenders: if the listing's specs row hasn't been
			// synced yet (e.g. created on an older plugin version, sync
			// hook silently failed) but the post HAS the taxonomy term
			// assigned, we still want to find it. EXISTS subquery against
			// the term_relationships → term_taxonomy chain catches it.
			$tax_clause = '';
			if ( $term_id > 0 && isset( $tax_for[ $key ] ) ) {
				$tax_clause = " OR EXISTS (
					SELECT 1 FROM {$wpdb->term_relationships} tr
					INNER JOIN {$wpdb->term_taxonomy} tt
						ON tt.term_taxonomy_id = tr.term_taxonomy_id
					WHERE tr.object_id = p.ID
						AND tt.taxonomy = %s
						AND tt.term_id  = %d
				)";
			}

			$where[] = '(' . $specs_clause . $tax_clause . ')';
			foreach ( $candidates as $c ) {
				$prepare[] = $c;
			}
			if ( '' !== $tax_clause ) {
				$prepare[] = $tax_for[ $key ];
				$prepare[] = $term_id;
			}
		}

		foreach ( $params->getBool() as $key => $value ) {
			$column    = FilterParams::BOOL_FILTERS[ $key ];
			$where[]   = "s.{$column} = %d";
			$prepare[] = $value;
		}

		foreach ( $params->getRange() as $key => $value ) {
			[ $column, $op ] = FilterParams::RANGE_FILTERS[ $key ];
			$where[]         = "s.{$column} {$op} %d";
			$prepare[]       = $value;
		}

		if ( null !== $params->getDealerId() ) {
			$where[]   = 's.dealer_id = %d';
			$prepare[] = $params->getDealerId();
		}

		$search = $params->getSearch();
		if ( '' !== $search ) {
			$like      = '%' . $wpdb->esc_like( $search ) . '%';
			$where[]   = '(p.post_title LIKE %s OR p.post_content LIKE %s OR s.vin LIKE %s OR s.stock_number LIKE %s)';
			$prepare[] = $like;
			$prepare[] = $like;
			$prepare[] = $like;
			$prepare[] = $like;
		}

		/**
		 * Filter the WHERE clauses + prepare values before they're imploded.
		 *
		 * Pro extensions hook here to add geo-radius (Haversine), saved-search
		 * "since" timestamps, etc. without forking VehicleQuery.
		 *
		 * @param array{0:string[],1:array<int,mixed>} $tuple [where_clauses, prepare_values]
		 * @param FilterParams                          $params The current filter set.
		 */
		$tuple = (array) apply_filters( 'trx_ad_vehicle_query_where', array( $where, $prepare ), $params );
		if ( isset( $tuple[0], $tuple[1] ) && is_array( $tuple[0] ) && is_array( $tuple[1] ) ) {
			$where   = $tuple[0];
			$prepare = $tuple[1];
		}

		return array( implode( ' AND ', $where ), $prepare );
	}

	/**
	 * Count rows matching the filters.
	 */
	public function count( FilterParams $params ): int {
		global $wpdb;

		[ $whereSql, $prepare ] = $this->buildWhere( $params );

		$specs = self::table();
		$sql   = "SELECT COUNT(*) FROM {$wpdb->posts} AS p
			LEFT JOIN {$specs} AS s ON p.ID = s.post_id
			WHERE {$whereSql}";

		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
		$prepared = empty( $prepare ) ? $sql : $wpdb->prepare( $sql, ...$prepare );

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
		return (int) $wpdb->get_var( $prepared );
	}

	/**
	 * Fetch joined post + specs rows for the current filter page.
	 *
	 * @return array<int,array<string,mixed>>
	 */
	public function fetch( FilterParams $params ): array {
		global $wpdb;

		[ $whereSql, $prepare ] = $this->buildWhere( $params );

		$orderbySql = FilterParams::ORDERBY_MAP[ $params->getOrderby() ] ?? 'p.post_date';
		$orderSql   = $params->getOrder();
		$specs      = self::table();

		// LEFT JOIN so listings without a specs row still show up (their
		// taxonomy-based filter clauses still apply). Without this, any
		// listing whose VehicleSpecsSync didn't fire is invisible to the
		// archive — which is exactly the "I see 3 cars but the filter
		// returns 0" symptom users hit.
		$sql = "SELECT p.ID, p.post_title, p.post_name, p.post_date, s.*
			FROM {$wpdb->posts} AS p
			LEFT JOIN {$specs} AS s ON p.ID = s.post_id
			WHERE {$whereSql}
			ORDER BY {$orderbySql} {$orderSql}
			LIMIT %d OFFSET %d";

		$values = array_merge( $prepare, array( $params->getPerPage(), $params->getOffset() ) );

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
		$rows = $wpdb->get_results( $wpdb->prepare( $sql, ...$values ), ARRAY_A );

		return is_array( $rows ) ? $rows : array();
	}

	/**
	 * Fetch every matching post ID in sort order, ignoring pagination.
	 *
	 * The archive (ArchiveFilters) feeds these into WP_Query's `post__in`
	 * and lets WP_Query own the pagination/LIMIT. Returning only the current
	 * page here (as fetch() does) would double-paginate: WP_Query would then
	 * re-apply its own offset on top of a single page's worth of IDs, so any
	 * page beyond the first sliced into an empty set (the "pagination doesn't
	 * work past page 1" bug).
	 *
	 * @return array<int,int>
	 */
	public function fetchIds( FilterParams $params ): array {
		global $wpdb;

		[ $whereSql, $prepare ] = $this->buildWhere( $params );

		$orderbySql = FilterParams::ORDERBY_MAP[ $params->getOrderby() ] ?? 'p.post_date';
		$orderSql   = $params->getOrder();
		$specs      = self::table();

		$sql = "SELECT p.ID
			FROM {$wpdb->posts} AS p
			LEFT JOIN {$specs} AS s ON p.ID = s.post_id
			WHERE {$whereSql}
			ORDER BY {$orderbySql} {$orderSql}";

		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
		$prepared = empty( $prepare ) ? $sql : $wpdb->prepare( $sql, ...$prepare );

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
		$ids = $wpdb->get_col( $prepared );

		return is_array( $ids ) ? array_map( 'intval', $ids ) : array();
	}

	/**
	 * MIN/MAX of a numeric column across published listings, ignoring NULLs and zeros.
	 *
	 * @param string $column Whitelisted column (price | year | mileage).
	 * @return array{min:int|float|null,max:int|float|null}
	 */
	public function range( string $column ): array {
		global $wpdb;

		$allowed = array( 'price', 'year', 'mileage' );
		if ( ! in_array( $column, $allowed, true ) ) {
			return array(
				'min' => null,
				'max' => null,
			);
		}

		$specs = self::table();

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$row = $wpdb->get_row(
			"SELECT MIN(s.{$column}) AS min_val, MAX(s.{$column}) AS max_val
			FROM {$specs} AS s
			INNER JOIN {$wpdb->posts} AS p ON p.ID = s.post_id
			WHERE p.post_status = 'publish'
			AND p.post_type = 'trx_ad_listing'
			AND s.{$column} IS NOT NULL
			AND s.{$column} > 0",
			ARRAY_A
		);

		if ( ! is_array( $row ) ) {
			return array(
				'min' => null,
				'max' => null,
			);
		}

		$asInt = in_array( $column, array( 'year', 'mileage' ), true );
		return array(
			'min' => null !== $row['min_val'] ? ( $asInt ? (int) $row['min_val'] : (float) $row['min_val'] ) : null,
			'max' => null !== $row['max_val'] ? ( $asInt ? (int) $row['max_val'] : (float) $row['max_val'] ) : null,
		);
	}

	/**
	 * Same as range() but scoped to a given list of post IDs. Used by
	 * /search-options narrowing so the year/price/mileage range reflects
	 * only the listings matching the user's current filter selection.
	 *
	 * @param string          $column    Whitelisted column.
	 * @param array<int, int> $post_ids  Listings to scope to (already deduped).
	 * @return array{min:int|float|null,max:int|float|null}
	 */
	public function rangeForPosts( string $column, array $post_ids ): array {
		global $wpdb;

		$allowed = array( 'price', 'year', 'mileage' );
		if ( ! in_array( $column, $allowed, true ) || empty( $post_ids ) ) {
			return array(
				'min' => null,
				'max' => null,
			);
		}

		$specs        = self::table();
		$placeholders = implode( ',', array_fill( 0, count( $post_ids ), '%d' ) );

		// $column is whitelisted on line 263; $specs comes from self::table()
		// (wpdb prefix + hardcoded suffix); $placeholders is %d-only. PHPCS
		// can't follow the interpolation through this many indirections.
		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare
		$row = $wpdb->get_row(
			$wpdb->prepare(
				"SELECT MIN(s.{$column}) AS min_val, MAX(s.{$column}) AS max_val
				FROM {$specs} AS s
				WHERE s.post_id IN ({$placeholders})
				AND s.{$column} IS NOT NULL
				AND s.{$column} > 0",
				...$post_ids
			),
			ARRAY_A
		);
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare

		if ( ! is_array( $row ) ) {
			return array(
				'min' => null,
				'max' => null,
			);
		}

		$asInt = in_array( $column, array( 'year', 'mileage' ), true );
		return array(
			'min' => null !== $row['min_val'] ? ( $asInt ? (int) $row['min_val'] : (float) $row['min_val'] ) : null,
			'max' => null !== $row['max_val'] ? ( $asInt ? (int) $row['max_val'] : (float) $row['max_val'] ) : null,
		);
	}

	/**
	 * Distinct values + counts for a column across published listings.
	 *
	 * Returns rows ordered by value ascending. Used by /search-options
	 * to populate dropdown facets when taxonomy term counts are stale.
	 *
	 * @param string $column Whitelisted column.
	 * @return array<int,array{value:string,count:int}>
	 */
	public function distinct( string $column ): array {
		global $wpdb;

		$allowed = array(
			'make',
			'model',
			'body_style',
			'fuel_type',
			'transmission',
			'drivetrain',
			'color_ext',
			'vehicle_type',
			'condition_type',
		);
		if ( ! in_array( $column, $allowed, true ) ) {
			return array();
		}

		$specs = self::table();

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$rows = $wpdb->get_results(
			"SELECT s.{$column} AS value, COUNT(*) AS cnt
			FROM {$specs} AS s
			INNER JOIN {$wpdb->posts} AS p ON p.ID = s.post_id
			WHERE p.post_status = 'publish'
			AND p.post_type = 'trx_ad_listing'
			AND s.{$column} IS NOT NULL
			AND s.{$column} <> ''
			GROUP BY s.{$column}
			ORDER BY s.{$column} ASC",
			ARRAY_A
		);

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

		return array_map(
			static fn( $r ) => array(
				'value' => (string) $r['value'],
				'count' => (int) $r['cnt'],
			),
			$rows
		);
	}
}
