<?php
/**
 * Content Indexer for Agentorie Chat.
 *
 * Indexes public pages and posts for AI-powered search functionality.
 *
 * @package    Agentorie_Chat
 * @subpackage Agentorie_Chat/includes
 * @since      1.0.0
 */

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

/**
 * Content Indexer class.
 *
 * @since 1.0.0
 */
class Agentorie_Content_Indexer {

	/**
	 * Singleton instance.
	 *
	 * @var Agentorie_Content_Indexer|null
	 */
	private static $instance = null;

	/**
	 * Products already found in the manual list during this request.
	 *
	 * @since 1.2.0
	 * @var int[] Product IDs keyed by list line.
	 */
	private $found_products = array();

	/**
	 * Maximum number of variations described for one variable product.
	 *
	 * @since 1.2.0
	 * @var int
	 */
	const MAX_PRODUCT_VARIATIONS = 50;

	/**
	 * Variable products to re-index at the end of the request.
	 *
	 * @since 1.2.0
	 * @var bool[] Keyed by product ID.
	 */
	private $pending_parents = array();

	/**
	 * Get singleton instance.
	 *
	 * @return Agentorie_Content_Indexer
	 */
	public static function get_instance() {
		if ( null === self::$instance ) {
			self::$instance = new self();
		}
		return self::$instance;
	}

	/**
	 * Private constructor to enforce singleton.
	 */
	private function __construct() {
		// Constructor is called via get_instance().
	}

	/**
	 * Check if a post should be indexed.
	 *
	 * Validates multiple criteria to ensure only meaningful content is indexed.
	 *
	 * @param WP_Post $post                The post object.
	 * @param bool    $bypass_validation   Skip content quality checks (manual mode).
	 * @return bool True if post should be indexed, false otherwise.
	 */
	private function is_indexable( $post, $bypass_validation = false ) {
		// Must be published.
		if ( 'publish' !== $post->post_status ) {
			return false;
		}

		// Must not be password protected.
		if ( ! empty( $post->post_password ) ) {
			return false;
		}

		// In manual mode, skip content quality validation (user's responsibility).
		if ( $bypass_validation ) {
			return true;
		}

		// AUTO MODE: Skip content length validation
		// Reason: Many modern WordPress sites use page builders (Elementor, Divi, Oxygen)
		// or custom fields (ACF) where post_content is empty but the page has content.
		// We trust that if a page is published, it's meant to be indexed.

		// OPTIONAL: Check for placeholder patterns only (no minimum length)
		// Generate clean content to check for placeholders.
		// Use apply_filters to render shortcodes and blocks properly.
		// phpcs:ignore WordPress.NamingConventions.PrefixAllGlobals.NonPrefixedHooknameFound -- Using WordPress core filter
		$content = apply_filters( 'the_content', $post->post_content );
		$content = wp_strip_all_tags( $content );
		$content = preg_replace( '/\s+/', ' ', $content );
		$content = trim( $content );

		// Check for common placeholder/empty page patterns (multilingual).
		$placeholder_patterns = array(
			// Generic.
			'/^lorem ipsum/i',
			// English.
			'/^coming soon\.?$/i',
			'/^under construction\.?$/i',
			// French.
			'/^en construction\.?$/i',
			'/^bientôt disponible\.?$/i',
			'/^page en cours\.?$/i',
			// Spanish.
			'/^próximamente\.?$/i',
			'/^en construcción\.?$/i',
			// German.
			'/^demnächst\.?$/i',
			'/^im aufbau\.?$/i',
			// Italian.
			'/^prossimamente\.?$/i',
			'/^in costruzione\.?$/i',
			// Portuguese.
			'/^em breve\.?$/i',
			'/^em construção\.?$/i',
			// Russian.
			'/^скоро\.?$/iu',
			'/^в разработке\.?$/iu',
			// Chinese.
			'/^即将推出\.?$/u',
			'/^建设中\.?$/u',
			// Japanese.
			'/^近日公開\.?$/u',
			'/^準備中\.?$/u',
			// Arabic.
			'/^قريبا\.?$/u',
		);

		foreach ( $placeholder_patterns as $pattern ) {
			if ( preg_match( $pattern, $content ) ) {
				return false;
			}
		}

		return true;
	}

	/**
	 * Initialize the indexer hooks.
	 *
	 * @return void
	 */
	public function init() {
		// CRON for daily indexing.
		add_action( 'agentorie_chat_daily_index', array( $this, 'index_all_content' ) );

		// Real-time hooks for content changes.
		add_action( 'save_post', array( $this, 'index_single_post' ), 10, 2 );
		add_action( 'delete_post', array( $this, 'remove_from_index' ) );
		add_action( 'wp_trash_post', array( $this, 'remove_from_index' ) );
		add_action( 'woocommerce_new_product_variation', array( $this, 'queue_variation_parent' ) );
		add_action( 'woocommerce_update_product_variation', array( $this, 'queue_variation_parent' ) );
		add_action( 'woocommerce_before_delete_product_variation', array( $this, 'queue_variation_parent' ) );
		add_action( 'woocommerce_trash_product_variation', array( $this, 'queue_variation_parent' ) );

		// Schedule CRON if not already scheduled.
		if ( ! wp_next_scheduled( 'agentorie_chat_daily_index' ) ) {
			wp_schedule_event( time(), 'daily', 'agentorie_chat_daily_index' );
		}
	}

	/**
	 * Get total count of indexed content.
	 *
	 * @return int Number of currently indexed pages.
	 */
	public function get_total_count() {
		global $wpdb;
		$table = Agentorie_Database::site_content_table();

		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table name from internal method
		$count = (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$table} WHERE is_indexed = 1 AND post_type != 'product'" );
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		return $count;
	}

	/**
	 * Index all public content.
	 *
	 * Supports two modes:
	 * - 'auto': Index all published pages (and optionally posts), excluding specific URLs
	 * - 'manual': Index only the URLs specified by the user
	 *
	 * @return int Number of posts indexed.
	 */
	public function index_all_content() {
		// Force fresh settings from database (bypass WordPress object cache).
		// Critical when called right after an AJAX save in a separate request.
		wp_cache_delete( 'agentorie_chat_settings', 'options' );
		agentorie_chat_clear_settings_cache();

		$indexing_enabled = agentorie_chat_get_setting( 'content_indexing_enabled', false );
		$woo_enabled      = agentorie_chat_get_setting( 'woo_indexing_enabled', false );

		// Nothing to do if both are disabled.
		if ( ! $indexing_enabled && ! $woo_enabled ) {
			return 0;
		}

		$indexed_count = 0;

		// Page/Post content indexing (only if enabled).
		if ( $indexing_enabled ) {
			$indexing_mode = agentorie_chat_get_setting( 'content_indexing_mode', 'auto' );

			global $wpdb;
			$table = Agentorie_Database::site_content_table();

			// Reset is_indexed flag for non-product content only.
			// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table name from internal method
			$wpdb->query( "UPDATE {$table} SET is_indexed = 0 WHERE post_type != 'product'" );
			// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

			if ( 'manual' === $indexing_mode ) {
				$indexed_count = $this->index_manual_urls();
			} else {
				$indexed_count = $this->index_auto_content();
			}

			// Remove non-product entries that were not re-indexed.
			// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
			$wpdb->query( "DELETE FROM {$table} WHERE is_indexed = 0 AND post_type != 'product'" );
			// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		}

		// WooCommerce product indexing (separate flow, separate toggle).
		$product_count  = $this->index_woo_products();
		$indexed_count += $product_count;

		// Update last sync timestamp.
		agentorie_chat_update_setting( 'last_content_sync', current_time( 'mysql' ) );

		// Notify SaaS to increment content_version for cache-busting.
		$this->notify_content_updated();

		// Log the indexing.
		if ( class_exists( 'Agentorie_Logger' ) ) {
			Agentorie_Logger::info(
				'Content indexing completed',
				array(
					'indexed'  => $indexed_count,
					'products' => $product_count,
					'mode'     => $indexing_enabled ? agentorie_chat_get_setting( 'content_indexing_mode', 'auto' ) : 'disabled',
				)
			);
		}

		return $indexed_count;
	}

	/**
	 * Get the list of post types allowed for auto indexing.
	 *
	 * Always includes 'page'. Optionally includes 'post' and any
	 * selected custom post types from settings.
	 *
	 * @since 1.1.6
	 * @return array Array of post type slugs.
	 */
	private function get_allowed_post_types() {
		$post_types = array( 'page' );

		// Include blog posts if enabled.
		$index_posts = agentorie_chat_get_setting( 'index_posts', false );
		if ( $index_posts ) {
			$post_types[] = 'post';
		}

		// Include selected custom post types (only if toggle is enabled).
		$index_cpts_enabled = agentorie_chat_get_setting( 'index_custom_post_types', false );
		if ( $index_cpts_enabled ) {
			$custom_types = agentorie_chat_get_setting( 'content_index_post_types', array() );
			if ( ! empty( $custom_types ) && is_array( $custom_types ) ) {
				// Validate each CPT still exists and is public.
				$registered_cpts = get_post_types(
					array(
						'public'   => true,
						'_builtin' => false,
					),
					'names'
				);
				foreach ( $custom_types as $cpt ) {
					$cpt = sanitize_key( $cpt );
					if ( ! empty( $cpt ) && 'product' !== $cpt && in_array( $cpt, $registered_cpts, true ) && ! in_array( $cpt, $post_types, true ) ) {
						$post_types[] = $cpt;
					}
				}
			}
		}

		return $post_types;
	}

	/**
	 * Index content in auto mode.
	 *
	 * Indexes all published pages (and optionally posts) that meet quality criteria.
	 *
	 * @return int Number of posts indexed.
	 */
	private function index_auto_content() {
		$post_types = $this->get_allowed_post_types();

		$excluded_ids = $this->get_excluded_ids();

		$indexed_count = 0;
		$paged         = 1;
		$batch_size    = 100;

		do {
			// phpcs:disable WordPressVIPMinimum.Performance.WPQueryParams.PostNotIn_exclude -- Limited exclusion list from user settings, acceptable for this use case
			$posts = get_posts(
				array(
					'post_type'      => $post_types,
					'post_status'    => 'publish',
					'posts_per_page' => $batch_size,
					'paged'          => $paged,
					'exclude'        => $excluded_ids,
					'has_password'   => false, // Exclude password-protected posts.
					'orderby'        => 'date',
					'order'          => 'DESC', // Most recent first.
				)
			);
			// phpcs:enable WordPressVIPMinimum.Performance.WPQueryParams.PostNotIn_exclude

			if ( empty( $posts ) ) {
				break;
			}

			foreach ( $posts as $post ) {
				// Check if post meets all indexing criteria.
				if ( ! $this->is_indexable( $post ) ) {
					continue;
				}

				if ( $this->upsert_post( $post ) ) {
					++$indexed_count;
				}
			}
			++$paged;
			$posts_count = count( $posts );

		} while ( $posts_count === $batch_size );

		return $indexed_count;
	}

	/**
	 * Index content in manual mode.
	 *
	 * Indexes only the URLs specified by the user in settings.
	 *
	 * @return int Number of posts indexed.
	 */
	private function index_manual_urls() {
		$manual_urls = agentorie_chat_get_setting( 'content_manual_urls', '' );

		if ( empty( $manual_urls ) ) {
			return 0;
		}

		$urls = $this->parse_url_list( $manual_urls );

		if ( empty( $urls ) ) {
			return 0;
		}

		$indexed_count = 0;

		foreach ( $urls as $url ) {
			$post_id = url_to_postid( $url );

			if ( $post_id > 0 ) {
				$post = get_post( $post_id );
				// Skip WooCommerce products — handled separately by index_woo_products().
				if ( $post && 'product' === $post->post_type ) {
					continue;
				}
				// Bypass validation in manual mode (user's responsibility).
				if ( $post && $this->is_indexable( $post, true ) ) {
					if ( $this->upsert_post( $post ) ) {
						++$indexed_count;
					}
				}
			}
		}

		return $indexed_count;
	}

	/**
	 * Index a single post (called on save_post hook).
	 *
	 * @param int     $post_id The post ID.
	 * @param WP_Post $post    The post object.
	 * @return void
	 */
	public function index_single_post( $post_id, $post ) {
		// Skip revisions and autosaves.
		if ( wp_is_post_revision( $post_id ) || wp_is_post_autosave( $post_id ) ) {
			return;
		}

		// WooCommerce products: completely separate flow (independent toggle).
		// Must be checked BEFORE content_indexing_enabled so that editing a product
		// still triggers reindex even when Page Indexing is disabled.
		if ( 'product' === $post->post_type ) {
			$this->index_single_product( $post_id, $post );
			return;
		}

		$indexing_enabled = agentorie_chat_get_setting( 'content_indexing_enabled', false );
		if ( ! $indexing_enabled ) {
			return;
		}

		$indexing_mode     = agentorie_chat_get_setting( 'content_indexing_mode', 'auto' );
		$bypass_validation = false;

		// In manual mode, only index if this URL is in the manual list.
		if ( 'manual' === $indexing_mode ) {
			$manual_urls = agentorie_chat_get_setting( 'content_manual_urls', '' );
			$urls        = $this->parse_url_list( $manual_urls );
			$post_url    = get_permalink( $post_id );

			if ( ! in_array( $post_url, $urls, true ) ) {
				// Not in manual list, remove if was indexed.
				$this->remove_from_index( $post_id );
				return;
			}

			// Bypass validation in manual mode.
			$bypass_validation = true;
		} else {
			// Auto mode: check if it's an indexable post type.
			$allowed_types = $this->get_allowed_post_types();

			if ( ! in_array( $post->post_type, $allowed_types, true ) ) {
				return;
			}

			// Check if excluded.
			$excluded_ids = $this->get_excluded_ids();
			if ( in_array( $post_id, $excluded_ids, true ) ) {
				$this->remove_from_index( $post_id );
				return;
			}
		}

		// Only index if passes all criteria.
		if ( $this->is_indexable( $post, $bypass_validation ) ) {
			$this->upsert_post( $post );

			// Notify SaaS to increment content_version for cache-busting.
			$this->notify_content_updated();
		} else {
			$this->remove_from_index( $post_id );
		}
	}

	/**
	 * Build the price fields of a product as shown to visitors.
	 *
	 * Variable products get their price range followed by the price of each
	 * variation, since the parent product has no price of its own.
	 *
	 * @since 1.2.0
	 * @param WC_Product $product The product.
	 * @return array{price: string, sale_price: string}
	 */
	private function get_product_prices( $product ) {
		$prices = array(
			'price'      => '',
			'sale_price' => '',
		);

		if ( is_a( $product, 'WC_Product_Variable' ) ) {
			$prices['price'] = $this->get_variable_product_price( $product );
			return $prices;
		}

		$regular = $product->get_regular_price();
		if ( '' === $regular ) {
			$regular = $product->get_price();
		}
		if ( '' === $regular ) {
			return $prices;
		}

		$prices['price'] = $this->format_price( wc_get_price_to_display( $product, array( 'price' => (float) $regular ) ) );

		$sale = $product->get_sale_price();
		if ( '' !== $sale && $product->is_on_sale() ) {
			$prices['sale_price'] = $this->format_price( wc_get_price_to_display( $product, array( 'price' => (float) $sale ) ) );
		}

		return $prices;
	}

	/**
	 * Describe the prices of a variable product.
	 *
	 * @since 1.2.0
	 * @param WC_Product $product The variable product.
	 * @return string Price range, then one "variation: price" entry per variation.
	 */
	private function get_variable_product_price( $product ) {
		$lines   = array();
		$amounts = array();

		foreach ( $product->get_children() as $variation_id ) {
			if ( count( $lines ) >= self::MAX_PRODUCT_VARIATIONS ) {
				break;
			}

			$variation = wc_get_product( $variation_id );
			if ( ! $variation || ! $variation->variation_is_visible() || ! $variation->is_purchasable() ) {
				continue;
			}

			$active = $variation->get_price();
			if ( '' === $active ) {
				continue;
			}

			$active_display = (float) wc_get_price_to_display( $variation, array( 'price' => (float) $active ) );
			$amounts[]      = $active_display;

			$label = $this->to_plain_text( wc_get_formatted_variation( $variation, true, false, false ) );
			$label = trim( (string) preg_replace( '/,\s*(?=,)/', '', $label ), " ,\t" );
			if ( '' === $label ) {
				$label = $this->to_plain_text( $variation->get_name() );
			}

			$line    = $label . ': ' . $this->format_price( $active_display );
			$regular = $variation->get_regular_price();
			if ( '' !== $regular && $variation->is_on_sale() && (float) $regular > (float) $active ) {
				$line .= ' (regular price ' . $this->format_price( wc_get_price_to_display( $variation, array( 'price' => (float) $regular ) ) ) . ')';
			}
			if ( ! $variation->is_in_stock() ) {
				$line .= ' (out of stock)';
			}

			$lines[] = $line;
		}

		if ( empty( $amounts ) ) {
			return '';
		}

		$min   = min( $amounts );
		$max   = max( $amounts );
		$range = ( $min === $max )
			? $this->format_price( $min )
			: $this->format_price( $min ) . ' - ' . $this->format_price( $max );

		return $range . ' | ' . implode( ' | ', $lines );
	}

	/**
	 * Format an amount with the store's currency settings, as plain text.
	 *
	 * @since 1.2.0
	 * @param float|string $amount Amount already converted for display.
	 * @return string Price such as "$38.25" or "38,25 €".
	 */
	private function format_price( $amount ) {
		return $this->to_plain_text( wc_price( (float) $amount ) );
	}

	/**
	 * Convert WooCommerce HTML output to plain text.
	 *
	 * @since 1.2.0
	 * @param string $html HTML fragment.
	 * @return string Plain text without entities or non-breaking spaces.
	 */
	private function to_plain_text( $html ) {
		$text = html_entity_decode( wp_strip_all_tags( (string) $html ), ENT_QUOTES, 'UTF-8' );

		return trim( str_replace( "\xc2\xa0", ' ', $text ) );
	}

	/**
	 * Tell whether shop prices are displayed with or without tax.
	 *
	 * @since 1.2.0
	 * @return string 'incl.', 'excl.', or empty string when taxes are disabled.
	 */
	private function get_price_tax_mode() {
		if ( ! wc_tax_enabled() ) {
			return '';
		}

		return 'incl' === get_option( 'woocommerce_tax_display_shop' ) ? 'incl.' : 'excl.';
	}

	/**
	 * Queue the parent of a saved variation for re-indexing.
	 *
	 * Editing variations does not save the parent product, so its prices
	 * would otherwise stay outdated until the daily indexing. Only admin
	 * requests are handled: stock changes made by orders are picked up by
	 * the daily indexing, so the checkout never waits for a re-index.
	 *
	 * @since 1.2.0
	 * @param int $variation_id Variation ID.
	 * @return void
	 */
	public function queue_variation_parent( $variation_id ) {
		if ( ! is_admin() ) {
			return;
		}

		$parent_id = (int) wp_get_post_parent_id( $variation_id );
		if ( $parent_id <= 0 ) {
			return;
		}

		if ( empty( $this->pending_parents ) ) {
			add_action( 'shutdown', array( $this, 'reindex_pending_parents' ) );
		}

		$this->pending_parents[ $parent_id ] = true;
	}

	/**
	 * Re-index the queued variable products that are already in the index.
	 *
	 * @since 1.2.0
	 * @return void
	 */
	public function reindex_pending_parents() {
		$parent_ids            = array_keys( $this->pending_parents );
		$this->pending_parents = array();

		if ( ! agentorie_chat_get_setting( 'woo_indexing_enabled', false ) || ! self::is_woocommerce_active() ) {
			return;
		}

		$updated = false;
		foreach ( $parent_ids as $parent_id ) {
			if ( ! $this->is_post_indexed( $parent_id ) ) {
				continue;
			}

			$post = get_post( $parent_id );
			if ( ! $post || 'product' !== $post->post_type ) {
				continue;
			}

			if ( $this->upsert_product( $post ) ) {
				$updated = true;
			}
		}

		if ( $updated ) {
			$this->notify_content_updated();
		}
	}

	/**
	 * Index a single WooCommerce product (called via index_single_post).
	 *
	 * Checks woo_indexing_enabled and woo_indexing_limit before indexing.
	 * If the limit is already reached, the product is not indexed.
	 *
	 * @since 1.2.0
	 * @param int     $post_id Product post ID.
	 * @param WP_Post $post    The post object.
	 * @return void
	 */
	private function index_single_product( $post_id, $post ) {
		$woo_enabled = agentorie_chat_get_setting( 'woo_indexing_enabled', false );
		if ( ! $woo_enabled || ! self::is_woocommerce_active() ) {
			$this->remove_from_index( $post_id );
			return;
		}

		$woo_limit = (int) agentorie_chat_get_setting( 'woo_indexing_limit', 10 );

		// Manual selection: only the products of the list, within the quota, in list order.
		if ( 'manual' === $this->get_woo_mode() ) {
			$selection = $this->resolve_manual_products();
			$selected  = array_slice( $selection['ids'], 0, max( 0, $woo_limit ) );
			if ( ! in_array( (int) $post_id, $selected, true ) ) {
				$this->remove_from_index( $post_id );
				return;
			}
			$this->upsert_product( $post );
			$this->keep_only_products( $selected );
			$this->notify_content_updated();
			return;
		}

		if ( ! $this->is_indexable( $post ) ) {
			$this->remove_from_index( $post_id );
			return;
		}

		// Automatic selection restricted to one language.
		$woo_language = $this->get_woo_language();
		if ( '' !== $woo_language ) {
			$languages = $this->get_post_language_map( array( (int) $post_id ) );
			if ( ! isset( $languages[ (int) $post_id ] ) || $languages[ (int) $post_id ] !== $woo_language ) {
				$this->remove_from_index( $post_id );
				return;
			}
		}

		// Check if we're within the product limit.
		$product_stats = $this->get_product_stats();
		$current_count = $product_stats['total_indexed'];

		// Allow re-indexing existing products, only block new ones over limit.
		$is_existing = $this->is_post_indexed( $post_id );
		if ( ! $is_existing && $current_count >= $woo_limit ) {
			return;
		}

		$this->upsert_product( $post );
		$this->notify_content_updated();
	}

	/**
	 * Check if a post is already in the index.
	 *
	 * @since 1.2.0
	 * @param int $post_id The post ID.
	 * @return bool True if post is indexed.
	 */
	private function is_post_indexed( $post_id ) {
		global $wpdb;
		$table = Agentorie_Database::site_content_table();

		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table name from internal method
		$exists = $wpdb->get_var(
			$wpdb->prepare(
				"SELECT id FROM {$table} WHERE post_id = %d AND is_indexed = 1",
				$post_id
			)
		);
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

		return ! empty( $exists );
	}

	/**
	 * Insert or update a post in the index.
	 *
	 * @param WP_Post $post The post object.
	 * @return bool True on success, false on failure.
	 */
	private function upsert_post( $post ) {
		global $wpdb;
		$table = Agentorie_Database::site_content_table();

		$data = array(
			'post_id'    => $post->ID,
			'post_type'  => $post->post_type,
			'title'      => $post->post_title,
			'url'        => get_permalink( $post ),
			'excerpt'    => $this->generate_excerpt( $post ),
			'is_indexed' => 1,
			'last_sync'  => current_time( 'mysql' ),
		);

		// Check if entry already exists.
		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table name from internal method
		$existing = $wpdb->get_var(
			$wpdb->prepare(
				"SELECT id FROM {$table} WHERE post_id = %d",
				$post->ID
			)
		);
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

		if ( $existing ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter
			$result = $wpdb->update(
				$table,
				$data,
				array( 'post_id' => $post->ID ),
				array( '%d', '%s', '%s', '%s', '%s', '%d', '%s' ),
				array( '%d' )
			);
		} else {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, PluginCheck.Security.DirectDB.UnescapedDBParameter
			$result = $wpdb->insert(
				$table,
				$data,
				array( '%d', '%s', '%s', '%s', '%s', '%d', '%s' )
			);
		}

		return false !== $result;
	}

	/**
	 * Insert or update a WooCommerce product in the index with enriched data.
	 *
	 * Stores product metadata (price, categories, stock, attributes) as JSON
	 * in the product_data column at indexing time, avoiding expensive WooCommerce
	 * API calls at query time.
	 *
	 * @since 1.2.0
	 * @param WP_Post $post The product post object.
	 * @return bool True on success, false on failure.
	 */
	private function upsert_product( $post ) {
		if ( ! self::is_woocommerce_active() ) {
			return false;
		}

		$product = wc_get_product( $post->ID );
		if ( ! $product ) {
			return $this->upsert_post( $post );
		}

		// Build enriched product data JSON.
		$prices = $this->get_product_prices( $product );

		// Categories.
		$category_terms = wp_get_post_terms( $post->ID, 'product_cat', array( 'fields' => 'names' ) );
		$categories     = ( ! is_wp_error( $category_terms ) && ! empty( $category_terms ) )
			? implode( ', ', $category_terms )
			: '';

		// Stock status.
		$stock_map    = array(
			'instock'     => 'In stock',
			'outofstock'  => 'Out of stock',
			'onbackorder' => 'On backorder',
		);
		$stock_raw    = $product->get_stock_status();
		$stock_status = isset( $stock_map[ $stock_raw ] ) ? $stock_map[ $stock_raw ] : $stock_raw;

		// Attributes.
		$attributes      = $product->get_attributes();
		$attribute_parts = array();
		foreach ( $attributes as $attribute ) {
			if ( is_a( $attribute, 'WC_Product_Attribute' ) ) {
				$name    = wc_attribute_label( $attribute->get_name() );
				$options = $attribute->get_options();
				if ( $attribute->is_taxonomy() ) {
					$term_names = array();
					foreach ( $options as $term_id ) {
						$term = get_term( $term_id );
						if ( $term && ! is_wp_error( $term ) ) {
							$term_names[] = $term->name;
						}
					}
					$options = $term_names;
				}
				if ( ! empty( $options ) ) {
					$attribute_parts[] = $name . ': ' . implode( ', ', $options );
				}
			}
		}

		$product_data = wp_json_encode(
			array(
				'price'        => $prices['price'],
				'sale_price'   => $prices['sale_price'],
				'tax'          => $this->get_price_tax_mode(),
				'categories'   => $categories,
				'stock_status' => $stock_status,
				'attributes'   => ! empty( $attribute_parts ) ? implode( ' | ', $attribute_parts ) : '',
			),
			JSON_UNESCAPED_UNICODE
		);

		global $wpdb;
		$table = Agentorie_Database::site_content_table();

		$data = array(
			'post_id'      => $post->ID,
			'post_type'    => $post->post_type,
			'title'        => $post->post_title,
			'url'          => get_permalink( $post ),
			'excerpt'      => $this->generate_excerpt( $post ),
			'product_data' => $product_data,
			'is_indexed'   => 1,
			'last_sync'    => current_time( 'mysql' ),
		);

		// Check if entry already exists.
		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table name from internal method
		$existing = $wpdb->get_var(
			$wpdb->prepare(
				"SELECT id FROM {$table} WHERE post_id = %d",
				$post->ID
			)
		);
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

		if ( $existing ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter
			$result = $wpdb->update(
				$table,
				$data,
				array( 'post_id' => $post->ID ),
				array( '%d', '%s', '%s', '%s', '%s', '%s', '%d', '%s' ),
				array( '%d' )
			);
		} else {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, PluginCheck.Security.DirectDB.UnescapedDBParameter
			$result = $wpdb->insert(
				$table,
				$data,
				array( '%d', '%s', '%s', '%s', '%s', '%s', '%d', '%s' )
			);
		}

		return false !== $result;
	}

	/**
	 * Remove a post from the index.
	 *
	 * @param int $post_id The post ID.
	 * @return bool True on success, false on failure.
	 */
	public function remove_from_index( $post_id ) {
		global $wpdb;
		$table = Agentorie_Database::site_content_table();

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter
		$result = $wpdb->delete( $table, array( 'post_id' => $post_id ), array( '%d' ) );

		return false !== $result;
	}

	/**
	 * Generate a clean excerpt from post content.
	 *
	 * Processes content through WordPress filters, strips HTML, decodes entities,
	 * and truncates intelligently on word boundaries. Supports multilingual content
	 * including CJK (Chinese, Japanese, Korean), Arabic, Russian, etc.
	 *
	 * @since 1.0.0
	 * @param WP_Post $post   The post object.
	 * @param int     $length Maximum excerpt length in characters (default: 3000).
	 * @return string The generated excerpt.
	 */
	private function generate_excerpt( $post, $length = 3000 ) {
		// Render blocks/shortcodes like the_content() does.
		// phpcs:ignore WordPress.NamingConventions.PrefixAllGlobals.NonPrefixedHooknameFound -- Using WordPress core filter
		$content = apply_filters( 'the_content', $post->post_content );

		// Remove script/style tags BEFORE stripping (they can leave junk).
		$content = preg_replace( '/<(script|style)[^>]*>.*?<\/\1>/is', '', $content );

		// Remove HTML tags.
		$content = wp_strip_all_tags( $content );

		// Decode HTML entities (fixes &rsquo; &nbsp; &#x4F60; etc.).
		$content = html_entity_decode( $content, ENT_QUOTES | ENT_HTML5, 'UTF-8' );

		// Normalize whitespace (multiple spaces, tabs, newlines → single space).
		$content = preg_replace( '/\s+/', ' ', $content );
		$content = trim( $content );

		// Truncate intelligently if needed.
		if ( mb_strlen( $content, 'UTF-8' ) > $length ) {
			$content = mb_substr( $content, 0, $length, 'UTF-8' );

			// For Latin scripts: find last space to avoid cutting mid-word.
			// For CJK: characters are self-contained, no word boundary needed.
			$last_space = mb_strrpos( $content, ' ', 0, 'UTF-8' );
			if ( false !== $last_space && $last_space > $length - 100 ) {
				$content = mb_substr( $content, 0, $last_space, 'UTF-8' );
			}

			$content .= '...';
		}

		return $content;
	}

	/**
	 * Get excluded page IDs from settings.
	 *
	 * Supports both:
	 * - Legacy: comma-separated IDs (content_excluded_pages)
	 * - New: URL list (content_exclude_urls)
	 *
	 * @return array Array of excluded post IDs.
	 */
	private function get_excluded_ids() {
		$excluded_ids = array();

		// Legacy: IDs from content_excluded_pages (deprecated but still supported).
		$excluded_pages = agentorie_chat_get_setting( 'content_excluded_pages', '' );
		if ( ! empty( $excluded_pages ) ) {
			$ids          = array_map( 'trim', explode( ',', $excluded_pages ) );
			$ids          = array_filter( $ids, 'is_numeric' );
			$excluded_ids = array_map( 'intval', $ids );
		}

		// New: URLs from content_exclude_urls.
		$exclude_urls = agentorie_chat_get_setting( 'content_exclude_urls', '' );
		if ( ! empty( $exclude_urls ) ) {
			$urls = $this->parse_url_list( $exclude_urls );
			foreach ( $urls as $url ) {
				$post_id = url_to_postid( $url );
				if ( $post_id > 0 && ! in_array( $post_id, $excluded_ids, true ) ) {
					$excluded_ids[] = $post_id;
				}
			}
		}

		return $excluded_ids;
	}

	/**
	 * Parse a URL list (one URL per line) into an array.
	 *
	 * @param string $urls_text URLs separated by newlines.
	 * @return array Array of valid URLs.
	 */
	private function parse_url_list( $urls_text ) {
		if ( empty( $urls_text ) ) {
			return array();
		}

		$lines      = explode( "\n", $urls_text );
		$site_url   = trailingslashit( home_url() );
		$valid_urls = array();

		foreach ( $lines as $line ) {
			$url = trim( $line );

			if ( empty( $url ) ) {
				continue;
			}

			// Validate it's a proper URL.
			if ( ! filter_var( $url, FILTER_VALIDATE_URL ) ) {
				continue;
			}

			// Only accept URLs from this site.
			if ( strpos( $url, $site_url ) !== 0 ) {
				continue;
			}

			$valid_urls[] = $url;
		}

		return $valid_urls;
	}

	/**
	 * Search the indexed content.
	 *
	 * @deprecated 1.5.0 Content is now injected directly into AI prompts via get_all_indexed().
	 *                   This method is kept for backward compatibility but is no longer used.
	 *
	 * @param string $query The search query.
	 * @param int    $limit Maximum number of results.
	 * @return array Array of search results.
	 */
	public function search( $query, $limit = 5 ) {
		global $wpdb;
		$table = Agentorie_Database::site_content_table();

		// Sanitize and prepare query.
		$query = sanitize_text_field( $query );
		$limit = absint( $limit );

		if ( empty( $query ) || $limit < 1 ) {
			return array();
		}

		// Try FULLTEXT search first (on title and excerpt only, keywords removed).
		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table name from internal method
		$results = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT title, url, excerpt, post_type,
				MATCH(title, excerpt) AGAINST (%s IN NATURAL LANGUAGE MODE) as relevance
			FROM {$table}
			WHERE is_indexed = 1
			AND MATCH(title, excerpt) AGAINST (%s IN NATURAL LANGUAGE MODE)
			ORDER BY relevance DESC
			LIMIT %d",
				$query,
				$query,
				$limit
			),
			ARRAY_A
		);
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

		// Fallback to LIKE search if FULLTEXT returns nothing.
		if ( empty( $results ) ) {
			$like = '%' . $wpdb->esc_like( $query ) . '%';
			// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table name from internal method
			$results = $wpdb->get_results(
				$wpdb->prepare(
					"SELECT title, url, excerpt, post_type
				FROM {$table}
				WHERE is_indexed = 1
				AND (title LIKE %s OR excerpt LIKE %s)
				LIMIT %d",
					$like,
					$like,
					$limit
				),
				ARRAY_A
			);
			// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		}

		return $results ? $results : array();
	}

	/**
	 * Get indexing statistics.
	 *
	 * @return array Statistics array.
	 */
	public function get_stats() {
		global $wpdb;
		$table = Agentorie_Database::site_content_table();

		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table name from internal method
		$total = (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$table} WHERE is_indexed = 1 AND post_type != 'product'" );
		$pages = (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$table} WHERE is_indexed = 1 AND post_type = 'page'" );
		$posts = (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$table} WHERE is_indexed = 1 AND post_type = 'post'" );

		// Count per custom post type (exclude product — handled separately).
		$type_counts = $wpdb->get_results(
			"SELECT post_type, COUNT(*) as count 
			FROM {$table} 
			WHERE is_indexed = 1 
			AND post_type NOT IN ('page', 'post', 'product') 
			GROUP BY post_type 
			ORDER BY post_type ASC",
			ARRAY_A
		);
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

		$custom_types = array();
		if ( ! empty( $type_counts ) ) {
			foreach ( $type_counts as $row ) {
				$custom_types[ $row['post_type'] ] = (int) $row['count'];
			}
		}

		return array(
			'total_indexed' => $total,
			'pages'         => $pages,
			'posts'         => $posts,
			'custom_types'  => $custom_types,
		);
	}

	/**
	 * Get list of indexed pages.
	 *
	 * @return array Array of indexed pages with title, url, post_type.
	 */
	public function get_indexed_pages() {
		global $wpdb;
		$table = Agentorie_Database::site_content_table();

		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table name from internal method
		$results = $wpdb->get_results(
			"SELECT title, url, post_type, last_sync 
			FROM {$table} 
			WHERE is_indexed = 1 AND post_type != 'product'
			ORDER BY post_type ASC, title ASC",
			ARRAY_A
		);
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

		return $results ? $results : array();
	}

	/**
	 * Get all indexed content for AI injection.
	 *
	 * Returns complete indexed content (title, URL, excerpt) for direct
	 * injection into AI prompts. This replaces keyword-based SEARCH_PAGES
	 * with semantic understanding by providing all content upfront.
	 *
	 * @since 1.0.0
	 * @param int|null $limit Optional. Maximum number of results to return.
	 *                        When null, returns all indexed content.
	 * @return array Array of indexed content with title, url, excerpt.
	 */
	public function get_all_indexed( $limit = null ) {
		global $wpdb;
		$table = Agentorie_Database::site_content_table();

		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $sql built with internal table name
		// Filter out empty excerpts to avoid polluting AI context with useless pages.
		// Exclude WooCommerce products (handled separately via get_all_indexed_products).
		$sql = "SELECT post_id, title, url, excerpt 
			FROM {$table} 
			WHERE is_indexed = 1 
			AND excerpt != ''
			AND post_type != 'product'
			ORDER BY post_type ASC, title ASC";

		if ( null !== $limit && $limit > 0 ) {
			$results = $wpdb->get_results(
				$wpdb->prepare( $sql . ' LIMIT %d', $limit ),
				ARRAY_A
			);
		} else {
			$results = $wpdb->get_results( $sql, ARRAY_A );
		}
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

		return $results ? $this->add_post_languages( $results ) : array();
	}

	/**
	 * Clear all indexed content.
	 *
	 * @return int Number of rows deleted.
	 */
	public function clear_index() {
		global $wpdb;
		$table = Agentorie_Database::site_content_table();

		// Only clear non-product content. WooCommerce products have their own toggle.
		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table name from internal method
		$deleted = $wpdb->query( "DELETE FROM {$table} WHERE post_type != 'product'" );
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

		agentorie_chat_update_setting( 'last_content_sync', null );

		return $deleted;
	}

	/**
	 * Notify SaaS that indexed content has been updated.
	 *
	 * Triggers cache-busting by incrementing content_version on SaaS side.
	 * This ensures the next chat request will fetch fresh content from WordPress.
	 *
	 * @since 1.0.0
	 * @return bool True on success, false on failure.
	 */
	private function notify_content_updated() {
		if ( ! class_exists( 'Agentorie_API_SaaS' ) ) {
			return false;
		}

		$api    = Agentorie_API_SaaS::get_instance();
		$result = $api->notify_content_updated();

		if ( is_wp_error( $result ) ) {
			if ( class_exists( 'Agentorie_Logger' ) ) {
				Agentorie_Logger::warning(
					'Failed to notify content update',
					array(
						'error' => $result->get_error_message(),
					)
				);
			}
			return false;
		}

		return true;
	}

	// ===========================================
	// WooCommerce Product Indexing
	// ===========================================

	/**
	 * Check if WooCommerce is active.
	 *
	 * @since 1.2.0
	 * @return bool True if WooCommerce is active.
	 */
	public static function is_woocommerce_active() {
		// Primary check: WooCommerce class loaded.
		if ( class_exists( 'WooCommerce' ) ) {
			return true;
		}

		// Fallback: check if wc_get_product function exists
		// (WooCommerce may be loaded without the main class being available yet).
		return function_exists( 'wc_get_product' );
	}

	/**
	 * Index WooCommerce products.
	 *
	 * Separate indexing flow from pages/posts. Products are always indexed
	 * automatically when WooCommerce is active and content indexing is enabled.
	 * Uses woo_indexing_limit for quota enforcement.
	 *
	 * @since 1.2.0
	 * @return int Number of products indexed.
	 */
	private function index_woo_products() {
		if ( ! self::is_woocommerce_active() ) {
			return 0;
		}

		$woo_enabled = agentorie_chat_get_setting( 'woo_indexing_enabled', false );
		if ( ! $woo_enabled ) {
			return 0;
		}

		global $wpdb;
		$table = Agentorie_Database::site_content_table();

		// Reset is_indexed flag for products only.
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		$wpdb->query( "UPDATE {$table} SET is_indexed = 0 WHERE post_type = 'product'" );

		$woo_limit     = (int) agentorie_chat_get_setting( 'woo_indexing_limit', 10 );
		$indexed_count = 0;

		if ( 'manual' === $this->get_woo_mode() ) {
			$selection = $this->resolve_manual_products();

			foreach ( $selection['ids'] as $product_id ) {
				if ( $indexed_count >= $woo_limit ) {
					break;
				}
				$product_post = get_post( $product_id );
				if ( $product_post && $this->upsert_product( $product_post ) ) {
					++$indexed_count;
				}
			}

			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
			$wpdb->query( "DELETE FROM {$table} WHERE is_indexed = 0 AND post_type = 'product'" );

			return $indexed_count;
		}

		$woo_language = $this->get_woo_language();
		$paged        = 1;
		$batch_size   = 100;

		do {
			$products = get_posts(
				array(
					'post_type'      => 'product',
					'post_status'    => 'publish',
					'posts_per_page' => $batch_size,
					'paged'          => $paged,
					'has_password'   => false,
					'orderby'        => 'date',
					'order'          => 'DESC',
					'lang'           => '',
				)
			);

			if ( empty( $products ) ) {
				break;
			}

			$languages = '' !== $woo_language ? $this->get_post_language_map( wp_list_pluck( $products, 'ID' ) ) : array();

			foreach ( $products as $product_post ) {
				if ( ! $this->is_indexable( $product_post ) ) {
					continue;
				}

				if ( '' !== $woo_language && ( ! isset( $languages[ $product_post->ID ] ) || $languages[ $product_post->ID ] !== $woo_language ) ) {
					continue;
				}

				if ( $this->upsert_product( $product_post ) ) {
					++$indexed_count;
				}

				// Stop if we reached the plan limit.
				if ( $indexed_count >= $woo_limit ) {
					break 2;
				}
			}
			++$paged;
			$products_count = count( $products );

		} while ( $products_count === $batch_size );

		// Remove product entries that were not re-indexed.
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		$wpdb->query( "DELETE FROM {$table} WHERE is_indexed = 0 AND post_type = 'product'" );

		return $indexed_count;
	}

	/**
	 * Get all indexed WooCommerce products with enriched data.
	 *
	 * Returns products with pre-stored enriched data (price, stock, categories,
	 * attributes) from the product_data JSON column. No WooCommerce API calls
	 * are made at query time.
	 *
	 * @since 1.2.0
	 * @param int|null $limit Optional. Maximum number of products to return.
	 * @return array Array of enriched product data.
	 */
	public function get_all_indexed_products( $limit = null ) {
		global $wpdb;
		$table = Agentorie_Database::site_content_table();

		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		$sql = "SELECT post_id, title, url, excerpt, product_data 
			FROM {$table} 
			WHERE is_indexed = 1 
			AND post_type = 'product'
			ORDER BY title ASC";

		if ( null !== $limit && $limit > 0 ) {
			$results = $wpdb->get_results(
				$wpdb->prepare( $sql . ' LIMIT %d', $limit ),
				ARRAY_A
			);
		} else {
			$results = $wpdb->get_results( $sql, ARRAY_A );
		}
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

		if ( empty( $results ) ) {
			return array();
		}

		$results = $this->add_post_languages( $results );

		$products = array();
		foreach ( $results as $row ) {
			$data = array(
				'title'        => $row['title'],
				'url'          => $row['url'],
				'excerpt'      => $row['excerpt'],
				'price'        => '',
				'sale_price'   => '',
				'categories'   => '',
				'stock_status' => '',
				'attributes'   => '',
			);

			// Merge stored product data.
			if ( ! empty( $row['product_data'] ) ) {
				$product_json = json_decode( $row['product_data'], true );
				if ( is_array( $product_json ) ) {
					$data = array_merge( $data, $product_json );
				}
			}

			if ( isset( $row['lang'] ) ) {
				$data['lang'] = $row['lang'];
			}

			$products[] = $data;
		}

		return $products;
	}

	/**
	 * Get the product selection mode.
	 *
	 * @since 1.2.0
	 * @return string 'auto' (most recent products) or 'manual' (the store owner's list).
	 */
	private function get_woo_mode() {
		return 'manual' === agentorie_chat_get_setting( 'woo_indexing_mode', 'auto' ) ? 'manual' : 'auto';
	}

	/**
	 * Get the language that automatic product selection is restricted to.
	 *
	 * @since 1.2.0
	 * @return string Base language code (e.g. "fr"), or empty string for all languages.
	 */
	private function get_woo_language() {
		$language = (string) agentorie_chat_get_setting( 'woo_indexing_language', '' );
		if ( '' === $language ) {
			return '';
		}

		return array_key_exists( $language, self::get_site_languages() ) ? $language : '';
	}

	/**
	 * Get the languages of the site from Polylang or WPML.
	 *
	 * @since 1.2.0
	 * @return array Language names keyed by base language code (e.g. 'fr' => 'Français').
	 *               Empty when no multilingual plugin is active.
	 */
	public static function get_site_languages() {
		$languages = array();

		if ( function_exists( 'pll_languages_list' ) ) {
			$list = pll_languages_list( array( 'fields' => '' ) );
			foreach ( (array) $list as $language ) {
				if ( is_object( $language ) && isset( $language->locale, $language->name ) ) {
					$code = self::language_from_locale( (string) $language->locale );
					if ( '' !== $code && ! isset( $languages[ $code ] ) ) {
						$languages[ $code ] = (string) $language->name;
					}
				}
			}
			return $languages;
		}

		if ( defined( 'ICL_SITEPRESS_VERSION' ) ) {
			// phpcs:ignore WordPress.NamingConventions.PrefixAllGlobals.NonPrefixedHooknameFound -- WPML public filter.
			$list = apply_filters( 'wpml_active_languages', null, array( 'skip_missing' => 0 ) );
			foreach ( (array) $list as $language ) {
				if ( is_array( $language ) && isset( $language['code'] ) ) {
					$source = ! empty( $language['default_locale'] ) ? $language['default_locale'] : $language['code'];
					$code   = self::language_from_locale( (string) $source );
					$name   = ! empty( $language['native_name'] ) ? $language['native_name'] : $language['code'];
					if ( '' !== $code && ! isset( $languages[ $code ] ) ) {
						$languages[ $code ] = (string) $name;
					}
				}
			}
		}

		return $languages;
	}

	/**
	 * Resolve the manual product list.
	 *
	 * Each line is a product ID, a product URL or a SKU. A variation resolves to
	 * its parent product. Duplicates are kept once, in their first position.
	 *
	 * @since 1.2.0
	 * @param string|null $text List text, or null to read the saved setting.
	 * @return array {
	 *     @type int[]    $ids     Published product IDs, in list order.
	 *     @type string[] $ignored Lines that do not match a published product.
	 * }
	 */
	public function resolve_manual_products( $text = null ) {
		if ( null === $text ) {
			$text = (string) agentorie_chat_get_setting( 'woo_manual_products', '' );
		}

		$ids     = array();
		$ignored = array();

		foreach ( preg_split( '/\r\n|\r|\n/', $text ) as $line ) {
			$line = trim( $line );
			if ( '' === $line ) {
				continue;
			}

			$product_id = $this->find_product_id( $line );
			$post       = $product_id ? get_post( $product_id ) : null;

			if ( ! $post || 'product' !== $post->post_type || ! $this->is_indexable( $post, true ) ) {
				$ignored[] = $line;
				continue;
			}

			if ( ! in_array( $product_id, $ids, true ) ) {
				$ids[] = $product_id;
			}
		}

		return array(
			'ids'     => $ids,
			'ignored' => $ignored,
		);
	}

	/**
	 * Remove from the index the products that are not in the given list.
	 *
	 * @since 1.2.0
	 * @param int[] $product_ids Product IDs to keep (not empty).
	 * @return void
	 */
	private function keep_only_products( array $product_ids ) {
		global $wpdb;
		$table = Agentorie_Database::site_content_table();

		$product_ids  = array_map( 'absint', $product_ids );
		$placeholders = implode( ', ', array_fill( 0, count( $product_ids ), '%d' ) );

		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table name from internal method, one placeholder per ID.
		$wpdb->query(
			$wpdb->prepare(
				"DELETE FROM {$table} WHERE post_type = 'product' AND post_id NOT IN ( {$placeholders} )",
				$product_ids
			)
		);
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, PluginCheck.Security.DirectDB.UnescapedDBParameter
	}

	/**
	 * Find a product from an ID, a URL of this site or a SKU.
	 *
	 * @since 1.2.0
	 * @param string $line One line of the manual list.
	 * @return int Product ID, 0 if not found.
	 */
	private function find_product_id( $line ) {
		if ( isset( $this->found_products[ $line ] ) ) {
			return $this->found_products[ $line ];
		}

		$key        = $line;
		$product_id = 0;

		if ( 0 === strpos( $line, '/' ) && 0 !== strpos( $line, '//' ) ) {
			$line = home_url( $line );
		}

		if ( ctype_digit( $line ) ) {
			$product_id = absint( $line );
		} elseif ( filter_var( $line, FILTER_VALIDATE_URL ) ) {
			$product_id = url_to_postid( $line );
			if ( ! $product_id ) {
				// Plain permalinks (?product=slug) are not resolved by url_to_postid().
				$query_args = array();
				wp_parse_str( (string) wp_parse_url( $line, PHP_URL_QUERY ), $query_args );
				if ( ! empty( $query_args['product'] ) && is_string( $query_args['product'] ) && 0 === strpos( $line, home_url() ) ) {
					$found      = get_page_by_path( sanitize_title( $query_args['product'] ), OBJECT, 'product' );
					$product_id = $found ? (int) $found->ID : 0;
				}
			}
		} elseif ( function_exists( 'wc_get_product_id_by_sku' ) ) {
			$product_id = (int) wc_get_product_id_by_sku( $line );
		}

		if ( $product_id && 'product_variation' === get_post_type( $product_id ) ) {
			$product_id = (int) wp_get_post_parent_id( $product_id );
		}

		$product_id = (int) $product_id;
		if ( $product_id ) {
			$this->found_products[ $key ] = $product_id;
		}

		return $product_id;
	}

	/**
	 * Get the language of several posts from Polylang or WPML.
	 *
	 * @since 1.2.0
	 * @param int[] $post_ids Post IDs.
	 * @return array Base language codes keyed by post ID (posts without a language are absent).
	 */
	private function get_post_language_map( array $post_ids ) {
		$post_ids = array_values( array_filter( array_map( 'absint', $post_ids ) ) );
		if ( empty( $post_ids ) ) {
			return array();
		}

		if ( function_exists( 'pll_get_post_language' ) ) {
			return $this->get_polylang_languages( $post_ids );
		}

		if ( defined( 'ICL_SITEPRESS_VERSION' ) ) {
			return $this->get_wpml_languages( $post_ids );
		}

		return array();
	}

	/**
	 * Add the language of each indexed post, then drop the internal post ID.
	 *
	 * On a Polylang or WPML site every language is indexed. The language code
	 * lets the SaaS keep only the pages written in the chatbot's languages.
	 * Without a multilingual plugin, rows are returned without a language.
	 *
	 * @since 1.2.0
	 * @param array $rows Rows with a post_id key.
	 * @return array Rows without post_id, with a lang key when known (e.g. "fr").
	 */
	private function add_post_languages( array $rows ) {
		$post_ids = array();
		foreach ( $rows as $row ) {
			if ( ! empty( $row['post_id'] ) ) {
				$post_ids[] = absint( $row['post_id'] );
			}
		}

		$languages = $this->get_post_language_map( $post_ids );

		foreach ( $rows as $index => $row ) {
			$post_id = isset( $row['post_id'] ) ? absint( $row['post_id'] ) : 0;
			unset( $rows[ $index ]['post_id'] );
			if ( $post_id && ! empty( $languages[ $post_id ] ) ) {
				$rows[ $index ]['lang'] = $languages[ $post_id ];
			}
		}

		return $rows;
	}

	/**
	 * Get post languages from Polylang.
	 *
	 * The term cache is primed in one query so that pll_get_post_language()
	 * does not query the database once per post.
	 *
	 * @since 1.2.0
	 * @param int[] $post_ids Post IDs.
	 * @return array Language codes keyed by post ID.
	 */
	private function get_polylang_languages( array $post_ids ) {
		update_object_term_cache( $post_ids, array_values( get_post_types( array( 'public' => true ) ) ) );

		$languages = array();
		foreach ( $post_ids as $post_id ) {
			$locale = pll_get_post_language( $post_id, 'locale' );
			$code   = is_string( $locale ) ? self::language_from_locale( $locale ) : '';
			if ( '' !== $code ) {
				$languages[ $post_id ] = $code;
			}
		}

		return $languages;
	}

	/**
	 * Get post languages from WPML, in a single query.
	 *
	 * @since 1.2.0
	 * @param int[] $post_ids Post IDs.
	 * @return array Language codes keyed by post ID.
	 */
	private function get_wpml_languages( array $post_ids ) {
		global $wpdb;
		$table = $wpdb->prefix . 'icl_translations';

		$languages = array();
		foreach ( array_chunk( $post_ids, 500 ) as $chunk ) {
			$placeholders = implode( ', ', array_fill( 0, count( $chunk ), '%d' ) );
			$params       = array_merge( array( $wpdb->esc_like( 'post_' ) . '%' ), $chunk );

			// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, PluginCheck.Security.DirectDB.UnescapedDBParameter -- WPML table name built from $wpdb->prefix, one placeholder per ID
			$results = $wpdb->get_results(
				$wpdb->prepare(
					"SELECT element_id, language_code FROM {$table} WHERE element_type LIKE %s AND element_id IN ({$placeholders})",
					$params
				),
				ARRAY_A
			);
			// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, PluginCheck.Security.DirectDB.UnescapedDBParameter

			foreach ( (array) $results as $result ) {
				$code = self::language_from_locale( (string) $result['language_code'] );
				if ( '' !== $code ) {
					$languages[ (int) $result['element_id'] ] = $code;
				}
			}
		}

		return $languages;
	}

	/**
	 * Reduce a locale or language code to its base language.
	 *
	 * Examples: "fr_FR" and "fr" give "fr", "zh-hans" gives "zh",
	 * "de_DE_formal" gives "de".
	 *
	 * @since 1.2.0
	 * @param string $locale Locale or language code.
	 * @return string Lowercase base language code, or empty string.
	 */
	private static function language_from_locale( $locale ) {
		$parts = preg_split( '/[-_]/', strtolower( trim( $locale ) ) );
		$code  = isset( $parts[0] ) ? $parts[0] : '';

		return preg_match( '/^[a-z]{2,3}$/', $code ) ? $code : '';
	}

	/**
	 * Get WooCommerce product indexing statistics.
	 *
	 * @since 1.2.0
	 * @return array Statistics array with total_indexed count.
	 */
	public function get_product_stats() {
		if ( ! self::is_woocommerce_active() ) {
			return array( 'total_indexed' => 0 );
		}

		global $wpdb;
		$table = Agentorie_Database::site_content_table();

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		$total = (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$table} WHERE is_indexed = 1 AND post_type = 'product'" );

		return array( 'total_indexed' => $total );
	}

	/**
	 * Get list of indexed WooCommerce products for admin display.
	 *
	 * @since 1.2.0
	 * @return array Array of indexed products with title, url, post_type.
	 */
	public function get_indexed_products_list() {
		if ( ! self::is_woocommerce_active() ) {
			return array();
		}

		global $wpdb;
		$table = Agentorie_Database::site_content_table();

		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table name from internal method
		$results = $wpdb->get_results(
			"SELECT title, url, post_type, last_sync 
			FROM {$table} 
			WHERE is_indexed = 1 AND post_type = 'product'
			ORDER BY title ASC",
			ARRAY_A
		);
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

		return $results ? $results : array();
	}
}