<?php
/**
 * File Path: includes/data/class-mbs-book-query.php
 *
 * Book Query Builder
 *
 * Abstracted query builder for [mbs_books] shortcode and archive pages.
 *
 * @package MyBookShowroom
 * @since   1.0.0
 */

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

class MBS_Book_Query { // phpcs:ignore WordPress.NamingConventions.PrefixAllGlobals.NonPrefixedClassFound -- mbs_/MBS_ prefix is correct; scanner not configured with plugin prefix

	/**
	 * Build WP_Query arguments from shortcode attributes.
	 *
	 * @return array WP_Query args.
	 */
	public static function build( array $atts ): array {
		$atts = shortcode_atts( array(
			'layout'         => mbs_get_setting( 'formatting', 'default_layout', 'hero' ),
			'columns'        => 3,
			'per_page'       => mbs_get_setting( 'formatting', 'items_per_page', 6 ), // D-6: Formatting tab is canonical
			'author'         => '',
			'series'         => '',
			'genre'          => '',
			'tag'            => '',
			'format'         => '',
			'orderby'        => 'date',
			'order'          => 'DESC',
			'ids'            => '',
			'exclude'        => '', // phpcs:ignore WordPressVIPMinimum.Performance.WPQueryParams.PostNotIn_exclude -- standard WP query pattern; VIP rule does not apply to this plugin
			'show_pagination'=> true,
			'recommended'        => false,
			'lifecycle_state'    => '',   // 'coming_soon' bypasses scheduled gate; queries mbs_coming_soon=1
			'publication_window' => 0,    // int days new-releases window (0 = disabled)
		), $atts, 'mbs_books' );

		// Explicit allowlist for orderby (best practice, eliminates silent fallback).
		// F-SORT-2 (Sprint 163-C): 'title_sort' sorts by mbs_book_meta.title_sort_key
		// via a one-shot posts_clauses filter — no postmeta JOIN.
		$valid_orderby = array( 'date', 'title', 'modified', 'rand', 'menu_order', 'title_sort' );
		$orderby       = in_array( $atts['orderby'], $valid_orderby, true )
			? $atts['orderby']
			: 'date';

		$order = in_array( strtoupper( $atts['order'] ), array( 'ASC', 'DESC' ), true )
			? strtoupper( $atts['order'] )
			: 'DESC';

		if ( 'title_sort' === $orderby ) {
			// F-SORT-2 (Sprint 163-C): sort by mbs_book_meta.title_sort_key directly —
			// no postmeta JOIN (FLAG-2 resolution). A one-shot posts_clauses filter
			// adds the LEFT JOIN and sets ORDER BY before WP_Query executes.
			// The filter removes itself after the first invocation so it affects only
			// the immediately-following WP_Query call.
			$mbs_sort_order   = $order;
			$mbs_sort_handler = null;
			$mbs_sort_handler = static function ( array $clauses, WP_Query $q ) use ( $mbs_sort_order, &$mbs_sort_handler ): array {
				remove_filter( 'posts_clauses', $mbs_sort_handler, 10 );
				global $wpdb;
				$bm               = $wpdb->prefix . 'mbs_book_meta';
				$safe_ord         = ( 'ASC' === $mbs_sort_order ) ? 'ASC' : 'DESC';
				$clauses['join'] .= " LEFT JOIN {$bm} mbs_bm_sort ON mbs_bm_sort.post_id = {$wpdb->posts}.ID";
				$clauses['orderby'] = "COALESCE(NULLIF(mbs_bm_sort.title_sort_key, ''), {$wpdb->posts}.post_title) {$safe_ord}";
				return $clauses;
			};
			add_filter( 'posts_clauses', $mbs_sort_handler, 10, 2 );

			$args = array(
				'post_type'      => MBS_POST_TYPE,
				'posts_per_page' => (int) $atts['per_page'],
				'orderby'        => 'none',
				'order'          => $order,
				'post_status'    => 'publish',
			);
		} else {
			$args = array(
				'post_type'      => MBS_POST_TYPE,
				'posts_per_page' => (int) $atts['per_page'],
				'orderby'        => sanitize_key( $orderby ),
				'order'          => $order,
				'post_status'    => 'publish',
			);
		}

		// Tax query
		$tax_query = array( 'relation' => 'AND' );
		if ( ! empty( $atts['author'] ) ) {
			$tax_query[] = array(
				'taxonomy' => MBS_TAX_AUTHOR,
				'field'    => 'slug',
				'terms'    => sanitize_text_field( $atts['author'] ),
			);
		}
		if ( ! empty( $atts['series'] ) ) {
			$tax_query[] = array(
				'taxonomy' => MBS_TAX_SERIES,
				'field'    => 'slug',
				'terms'    => sanitize_text_field( $atts['series'] ),
			);
		}
		if ( ! empty( $atts['genre'] ) ) {
			$tax_query[] = array(
				'taxonomy' => MBS_TAX_GENRE,
				'field'    => 'slug',
				'terms'    => sanitize_text_field( $atts['genre'] ),
			);
		}
		if ( ! empty( $atts['tag'] ) ) {
			$tax_query[] = array(
				'taxonomy' => MBS_TAX_TAG,
				'field'    => 'slug',
				'terms'    => sanitize_text_field( $atts['tag'] ),
			);
		}
		if ( count( $tax_query ) > 1 ) {
			$args['tax_query'] = $tax_query; // phpcs:ignore WordPress.DB.SlowDBQuery.slow_db_query_tax_query -- standard WP query pattern; VIP rule does not apply to this plugin
		}

		// Format filter — uses MBS_Format_Row_DAO (correct path per Doc 1d)
		if ( ! empty( $atts['format'] ) ) {
			$format = sanitize_key( $atts['format'] );
			$book_ids_with_format = MBS_Format_Row_DAO::get_post_ids_by_format( $format );

			if ( ! empty( $book_ids_with_format ) ) {
				$args['post__in'] = $book_ids_with_format;
			} else {
				// No books match the requested format — return empty result set
				$args['post__in'] = array( 0 );
			}
		}

		// ID inclusion/exclusion
		if ( ! empty( $atts['ids'] ) ) {
			$args['post__in'] = array_map( 'absint', explode( ',', $atts['ids'] ) );
		}
		if ( ! empty( $atts['exclude'] ) ) {
			$args['post__not_in'] = array_map( 'absint', explode( ',', $atts['exclude'] ) ); // phpcs:ignore WordPressVIPMinimum.Performance.WPQueryParams.PostNotIn_post__not_in -- standard WP query pattern; VIP rule does not apply to this plugin
		}

		// Initialize meta_query with explicit AND relation so all clauses
		// (recommended, lifecycle_state, publication_window) compose correctly.
		$args['meta_query'] = array( 'relation' => 'AND' ); // phpcs:ignore WordPress.DB.SlowDBQuery.slow_db_query_meta_query -- standard WP query pattern; VIP rule does not apply to this plugin

		// Recommended filter (PRO) — BQ-001: gate behind mbs_is_pro() before applying meta_query.
		if ( mbs_is_pro() && ! empty( $atts['recommended'] ) && $atts['recommended'] === 'true' ) {
			$args['meta_query'][] = array(
				'key'     => 'mbs_recommended',
				'value'   => '1',
				'compare' => '=',
			);
		}

		// Lifecycle state filter — 'coming_soon' queries mbs_book_meta.coming_soon via DAO index.
		if ( ! empty( $atts['lifecycle_state'] ) && 'coming_soon' === sanitize_key( $atts['lifecycle_state'] ) ) {
			$cs_ids = MBS_Book_Meta_DAO::get_coming_soon_ids();
			if ( empty( $cs_ids ) ) {
				$args['post__in'] = array( 0 ); // Force empty result set.
			} else {
				$args['post__in'] = $cs_ids;
			}
		}

		// Publication window filter — new-releases: restrict to books whose
		// mbs_pub_date falls within the last N days. Books without a
		// mbs_pub_date are excluded (no date = no known release date).
		$pub_window = absint( $atts['publication_window'] );
		if ( $pub_window > 0 ) {
			$pw_tz_string = get_option( 'mbs_setting_general_timezone', '' );
			if ( '' === $pw_tz_string ) {
				$pw_tz_string = function_exists( 'wp_timezone_string' ) ? wp_timezone_string() : 'UTC';
			}
			try {
				$pw_tz  = new DateTimeZone( $pw_tz_string );
				$pw_now = new DateTimeImmutable( 'now', $pw_tz );
			} catch ( Exception $pw_e ) {
				$pw_tz  = new DateTimeZone( 'UTC' );
				$pw_now = new DateTimeImmutable( 'now', $pw_tz );
			}
			$today_ymd  = $pw_now->format( 'Y-m-d' );
			$window_ymd = $pw_now->modify( sprintf( '-%d days', $pub_window ) )->format( 'Y-m-d' );
			$args['meta_query'][] = array(
				'key'     => 'mbs_pub_date',
				'value'   => array( $window_ymd, $today_ymd ),
				'compare' => 'BETWEEN',
				'type'    => 'DATE',
			);
		}

		// GC2-4: Scheduled release gate (PRO, front-end only).
		// When enabled, exclude books whose publication datetime is in the future.
		// Books with no mbs_pub_date are always included.
		if ( ! is_admin() && mbs_is_pro() && get_option( 'mbs_setting_general_enable_scheduled_releases' ) === '1'
			 && '' === $atts['lifecycle_state'] ) {
			$tz_string = get_option( 'mbs_setting_general_timezone', '' );
			if ( '' === $tz_string ) {
				$tz_string = function_exists( 'wp_timezone_string' ) ? wp_timezone_string() : 'UTC';
			}

			try {
				$tz  = new DateTimeZone( $tz_string );
				$now = new DateTimeImmutable( 'now', $tz );
			} catch ( Exception $e ) {
				$tz  = new DateTimeZone( 'UTC' );
				$now = new DateTimeImmutable( 'now', $tz );
			}

			$today_ymd = $now->format( 'Y-m-d' );
			$now_time  = $now->format( 'H:i' );

			global $wpdb;

			// phpcs:disable WordPress.DB.DirectDatabaseQuery
			$future_ids = $wpdb->get_col(
				$wpdb->prepare(
					"SELECT DISTINCT p.ID
					 FROM {$wpdb->posts} p
					 INNER JOIN {$wpdb->postmeta} pm_date
					     ON pm_date.post_id = p.ID
					    AND pm_date.meta_key = 'mbs_pub_date'
					    AND pm_date.meta_value != ''
					 LEFT JOIN {$wpdb->postmeta} pm_time
					     ON pm_time.post_id = p.ID
					    AND pm_time.meta_key = 'mbs_publication_time'
					 WHERE p.post_type   = %s
					   AND p.post_status = 'publish'
					   AND (
					       pm_date.meta_value > %s
					       OR (
					           pm_date.meta_value = %s
					           AND COALESCE(pm_time.meta_value, '00:00') > %s
					       )
					   )",
					MBS_POST_TYPE,
					$today_ymd,
					$today_ymd,
					$now_time
				)
			);
			// phpcs:enable WordPress.DB.DirectDatabaseQuery

			if ( ! empty( $future_ids ) ) {
				$existing_not_in      = $args['post__not_in'] ?? array();
				$args['post__not_in'] = array_unique( // phpcs:ignore WordPressVIPMinimum.Performance.WPQueryParams.PostNotIn_post__not_in -- standard WP query pattern; VIP rule does not apply to this plugin
					array_merge(
						array_map( 'absint', $existing_not_in ),
						array_map( 'absint', $future_ids )
					)
				);
			}
		}

		// BQ-003: Pass the current page number so WP_Query returns the correct
		// offset for paginated requests. Without this every request builds a
		// query for page 1 — clicking page 2 shows the same first N books.
		// get_query_var('paged') returns 0 when the query var is absent (e.g.
		// REST context, non-paginated page); max(1, 0) safely resolves to 1.
		$args['paged'] = max( 1, (int) get_query_var( 'paged' ) );

		// BQ-001 / BQ-002: Strip meta_query when no real clauses were added.
		// An array containing only the 'relation' key carries no constraints and
		// should not reach WP_Query (it adds unnecessary DB overhead and causes
		// test assertions on assertArrayNotHasKey('meta_query') to fail).
		if ( isset( $args['meta_query'] )
			&& count( $args['meta_query'] ) === 1
			&& array_key_exists( 'relation', $args['meta_query'] ) ) {
			unset( $args['meta_query'] );
		}

		return $args;
	}

	/**
	 * Return distinct publisher names matching a prefix string.
	 *
	 * Used by the publisher auto-suggest AJAX handler. Queries the
	 * mbs_publisher postmeta key across all non-trashed book posts.
	 *
	 * @param string $prefix  The search prefix (sanitized, without LIKE wildcard).
	 * @param int    $limit   Maximum rows to return (default 10).
	 * @return string[]       Distinct publisher names, ordered alphabetically.
	 * @since 1.1.0
	 */
	/**
	 * Return true if any published page contains an MBS shortcode.
	 *
	 * Checks for [mbs_books], [mbs_book ], and [mbs_book] in page content.
	 * Used by the onboarding widget to determine whether the plugin has been
	 * wired up on a front-end page.
	 *
	 * @since  1.2.0
	 * @return bool True if any shortcode match found.
	 */
	public static function check_shortcode_placed(): bool {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- efficient single-query shortcode check.
		$found = $wpdb->get_var( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.DirectQuery -- DAO layer -- direct DB access is intentional per MBS architecture
			"SELECT ID FROM {$wpdb->posts}
			 WHERE post_status = 'publish'
			   AND post_type   = 'page'
			   AND (
			       post_content LIKE '%[mbs_books%'
			    OR post_content LIKE '%[mbs_book %'
			    OR post_content LIKE '%[mbs_book]%'
			   )
			 LIMIT 1"
		);
		return (bool) $found;
	}

	/**
	 * Full-text search of published books by title, teaser, or publisher.
	 *
	 * Returns post IDs of matching books. Used by the [mbs_search] shortcode.
	 * The query uses LIKE matching against post_title and selected meta keys;
	 * it is intentionally kept as a single optimized SQL call.
	 *
	 * @since  1.2.0
	 * @param  string $query Sanitized search string.
	 * @param  int    $limit Maximum number of results.
	 * @return int[]         Post IDs of matching published books.
	 */
	public static function search_by_text( string $query, int $limit ): array {
		global $wpdb;
		$like = '%' . $wpdb->esc_like( $query ) . '%';
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- custom full-text search; no ORM covers this.
		$post_ids = $wpdb->get_col( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.DirectQuery -- DAO layer -- direct DB access is intentional per MBS architecture
			$wpdb->prepare(
				"SELECT DISTINCT p.ID
				 FROM {$wpdb->posts} p
				 LEFT JOIN {$wpdb->postmeta} pm
				        ON pm.post_id = p.ID
				       AND pm.meta_key IN ('mbs_teaser', 'mbs_publisher')
				 WHERE p.post_type   = %s
				   AND p.post_status = 'publish'
				   AND (
				       p.post_title  LIKE %s
				    OR pm.meta_value LIKE %s
				   )
				 ORDER BY p.post_title ASC
				 LIMIT %d",
				MBS_POST_TYPE,
				$like,
				$like,
				$limit
			)
		);
		return array_map( 'absint', $post_ids ?: array() );
	}

	public static function suggest_publishers( string $prefix, int $limit = 10 ): array {
		global $wpdb;
		$like = $wpdb->esc_like( $prefix ) . '%';
		return $wpdb->get_col( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- DAO layer -- direct DB access is intentional per MBS architecture
			$wpdb->prepare(
				// phpcs:ignore WordPress.DB.PreparedSQLPlaceholders.UnquotedComplexPlaceholder
				"SELECT DISTINCT pm.meta_value
				 FROM {$wpdb->postmeta} pm
				 INNER JOIN {$wpdb->posts} p ON p.ID = pm.post_id
				 WHERE pm.meta_key   = 'mbs_publisher'
				   AND pm.meta_value LIKE %s
				   AND pm.meta_value != ''
				   AND p.post_type   = %s
				   AND p.post_status != 'trash'
				 ORDER BY pm.meta_value ASC
				 LIMIT %d",
				$like,
				MBS_POST_TYPE,
				$limit
			)
		) ?: array();
	}
}
