<?php
/**
 * Index Builder Service
 *
 * Orchestrates the AI Store Index building process using Action Scheduler.
 * Uses sharded storage for large catalogs (10K products per shard).
 *
 * @package AIPT\Api\StoreIndex
 * @since 2.5.0
 * @updated 2.5.0 Added profile_id support for multi-profile indexing
 * @updated 2.5.0 Refactored to use sharded index only (legacy single-file removed)
 */

namespace AIPT\Api\StoreIndex;

/**
 * IndexBuilder class
 *
 * Handles batch processing of products to build the sharded AI index.
 */
class IndexBuilder {

	/**
	 * Action Scheduler group name.
	 */
	private const AS_GROUP = 'aipt-store-index';

	/**
	 * Hook name for batch processing.
	 */
	private const AS_HOOK_BUILD = 'aipt_build_store_index';

	/**
	 * Hook name for scheduled rebuilds.
	 */
	private const AS_HOOK_SCHEDULED = 'aipt_scheduled_index_rebuild';

	/**
	 * Index schema version.
	 */
	private const INDEX_VERSION = '1.0.0';

	/**
	 * Wait timeout for acquiring MySQL GET_LOCK in seconds.
	 *
	 * @since 2.5.0
	 */
	private const LOCK_WAIT_TIMEOUT = 5;

	/**
	 * Default lock validity timeout in seconds for batch processing.
	 *
	 * @since 2.5.0
	 */
	private const LOCK_TIMEOUT = 300;

	/**
	 * Get the lock timeout value.
	 *
	 * @since 2.5.0
	 * @return int Lock timeout in seconds.
	 */
	private function getLockTimeout(): int {
		return (int) apply_filters( 'aipt_batch_lock_timeout', self::LOCK_TIMEOUT );
	}

	/**
	 * Profile ID.
	 *
	 * @var int
	 */
	private $profile_id;

	/**
	 * IndexSettings instance.
	 *
	 * @var IndexSettings
	 */
	private $settings;

	/**
	 * ProductProfileBuilder instance.
	 *
	 * @var ProductProfileBuilder
	 */
	private $profile_builder;

	/**
	 * TempStorage instance for intermediate build data.
	 *
	 * @var TempStorage|null
	 */
	private $temp_storage = null;

	/**
	 * ShardManager instance for sharded index builds.
	 *
	 * @var ShardManager
	 */
	private $shard_manager;

	/**
	 * Constructor.
	 *
	 * @param int $profile_id Profile ID. If 0, uses the active profile.
	 * @throws \InvalidArgumentException If profile does not exist.
	 * @throws \RuntimeException If no valid profile can be resolved.
	 */
	public function __construct( int $profile_id = 0 ) {
		$profile_manager = new ProfileManager();

		if ( 0 === $profile_id ) {

			$active = $profile_manager->getActive();

			if ( $active ) {
				$this->profile_id = (int) $active['id'];
			} else {

				$default = $profile_manager->getDefault();

				if ( ! $default ) {
					throw new \RuntimeException(

						__( 'No valid profile found. Please create a profile first.', 'ai-product-tools' )
					);
				}

				$this->profile_id = (int) $default['id'];
			}
		} else {

			$profile = $profile_manager->getById( $profile_id );

			if ( ! $profile ) {
				throw new \InvalidArgumentException(
					sprintf(
						/* translators: %d: profile ID */
						__( 'Profile with ID %d does not exist.', 'ai-product-tools' ), 
						$profile_id 
					)
				);
			}

			$this->profile_id = $profile_id;
		}

		$this->settings        = new IndexSettings( $this->profile_id );
		$this->profile_builder = new ProductProfileBuilder();
		$this->shard_manager   = new ShardManager( $this->profile_id );
	}

	/**
	 * Get the profile ID.
	 *
	 * @return int
	 */
	public function getProfileId(): int {
		return $this->profile_id;
	}

	/**
	 * Start a new index build job.
	 *
	 * @param bool $force If true, cancel existing build first.
	 * @return int Action Scheduler job ID.
	 * @throws \RuntimeException If build already in progress and not forced.
	 * @throws \InvalidArgumentException If feature is disabled or WooCommerce not active.
	 */
	public function start( bool $force = false ): int {

		if ( ! class_exists( 'WooCommerce' ) ) {
			throw new \InvalidArgumentException(
				__( 'WooCommerce is required for AI Store Index.', 'ai-product-tools' )
			);
		}

		if ( ! function_exists( 'as_schedule_single_action' ) ) {
			throw new \InvalidArgumentException(
				__( 'Action Scheduler is required for AI Store Index.', 'ai-product-tools' )
			);
		}

		if ( ! $this->settings->isEnabled() ) {
			throw new \InvalidArgumentException(
				__( 'AI Store Index feature is disabled.', 'ai-product-tools' )
			);
		}

		if ( ! $this->shard_manager->isWritable() ) {
			throw new \RuntimeException(
				__( 'Uploads directory is not writable. Please check file permissions.', 'ai-product-tools' )
			);
		}

		if ( $this->isRunning() && ! $force ) {
			throw new \RuntimeException(
				__( 'Index build already in progress.', 'ai-product-tools' )
			);
		}

		if ( $force ) {
			$this->cancel();
		}

		$batch_size = $this->settings->get( 'batch_size', 100 );

		$job_id = (int) ( microtime( true ) * 1000 );

		$total_eligible_products = $this->countMatchingProducts();

		$this->settings->updateStatus(
			array(
				'status'                   => 'building',
				'build_progress'           => 0,
				'progress_percent'         => 0,
				'processed_product_count'  => 0,
				'estimated_total_products' => $total_eligible_products,
				'error'                    => null,
				'build_job_id'             => $job_id,
			)
		);

		if ( $total_eligible_products === 0 ) {

			$this->shard_manager->initBuild();
			$global_meta = $this->buildMeta( 0, 0, 0 );
			$this->shard_manager->finalizeBuild( $global_meta );

			$this->settings->updateStatus(
				array(
					'status'         => 'completed',
					'last_build'     => current_time( 'c' ),
					'product_count'  => 0,
					'category_count' => 0,
					'file_size'      => 0,
					'shard_count'    => 0,
					'build_progress' => 100,
					'error'          => null,
					'build_job_id'   => 0,
				)
			);

			/**
			 * Action fired when build completes with zero products.
			 *
			 * @param array $meta Index metadata.
			 */
			do_action( 'aipt_store_index_build_complete', $global_meta );

			return 0; 
		}

		/**
		 * Action fired when index build starts.
		 *
		 * @param int $total_products Total number of products to process.
		 */
		do_action( 'aipt_store_index_build_start', $total_eligible_products );

		if ( ! $this->shard_manager->isWritable() ) {

			throw new \RuntimeException(
				__( 'Index storage directory is not writable. Please check uploads folder permissions.', 'ai-product-tools' )
			);

		}

		$kickstart_size = min( 20, $batch_size ); 

		$this->processBatch( $job_id, 0, $kickstart_size, 0 );

		$this->settings->clearCache();
		$status_after_kickstart = $this->settings->get( 'status', 'idle' );

		if ( $status_after_kickstart === 'failed' ) {
			$current_status = $this->settings->getStatus();

			throw new \RuntimeException(
				$current_status['error'] ?? __( 'Build failed during kickstart batch.', 'ai-product-tools' )
			);

		}

		return $job_id;
	}

	/**
	 * Cancel a running index build.
	 *
	 * @param bool $force If true, reset status even if no build appears to be running.
	 * @return bool True if cancelled or force reset, false if no build running and not forced.
	 */
	public function cancel( bool $force = false ): bool {

		$this->settings->clearCache();

		$was_running = $this->isRunning();

		if ( ! $was_running && ! $force ) {
			return false;
		}

		$this->cancelPendingBuildActions();

		$current_status = $this->settings->getStatus();
		$current_job_id = (int) ( $current_status['build_job_id'] ?? 0 );
		if ( $current_job_id > 0 ) {
			$temp_storage = new TempStorage( $current_job_id );
			$temp_storage->cleanup();
		}

		$this->settings->updateStatus(
			array(
				'status'                   => 'idle',
				'build_progress'           => 0,
				'progress_percent'         => 0,
				'processed_product_count'  => 0,
				'estimated_total_products' => 0,
				'build_job_id'             => 0,
			)
		);

		if ( ! $was_running && $force ) {

			error_log(
				sprintf(
					'[AIPT] IndexBuilder: Force cancelled profile %d (status was not "building")',
					$this->profile_id
				)
			);
		}

		return true;
	}

	/**
	 * Cancel all pending build actions for this profile.
	 *
	 * Uses optimized batch cancellation to avoid loading all actions into memory.
	 * This prevents timeouts on sites with large Action Scheduler queues.
	 *
	 * @return int Number of actions cancelled (approximate).
	 */
	private function cancelPendingBuildActions(): int {
		$cancelled = 0;

		$batch_size = 50;
		$max_iterations = 20; 

		for ( $i = 0; $i < $max_iterations; $i++ ) {

			$pending_actions = as_get_scheduled_actions(
				array(
					'hook'     => self::AS_HOOK_BUILD,
					'group'    => self::AS_GROUP,
					'status'   => \ActionScheduler_Store::STATUS_PENDING,
					'per_page' => $batch_size,
				),
				'ids'
			);

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

			$store = \ActionScheduler::store();

			foreach ( $pending_actions as $action_id ) {
				try {
					$action = $store->fetch_action( $action_id );

					if ( ! $action || $action->is_finished() ) {
						continue;
					}

					$args = $action->get_args();

					if ( isset( $args['profile_id'] ) && (int) $args['profile_id'] === $this->profile_id ) {
						as_unschedule_action(
							self::AS_HOOK_BUILD,
							$args,
							self::AS_GROUP
						);
						++$cancelled;
					}
				} catch ( \Exception $e ) {

					error_log(
						sprintf(
							'[AIPT] IndexBuilder: Failed to cancel action %d: %s',
							$action_id,
							$e->getMessage()
						)
					);
				}
			}

			if ( count( $pending_actions ) < $batch_size ) {
				break;
			}
		}

		return $cancelled;
	}

	/**
	 * Reset the index: delete all shards, clear temp storage, reset status.
	 *
	 * @param bool $force If true, force reset even if status appears stuck.
	 * @return bool True on success.
	 */
	public function reset( bool $force = false ): bool {

		$this->settings->clearCache();

		$this->cancel( $force );

		$this->shard_manager->deleteAllShards();

		$current_status = $this->settings->getStatus();
		$current_job_id = (int) ( $current_status['build_job_id'] ?? 0 );
		if ( $current_job_id > 0 ) {
			$temp_storage = new TempStorage( $current_job_id );
			$temp_storage->cleanup();
		}

		$this->settings->updateStatus(
			array(
				'status'                   => 'idle',
				'last_build'               => null,
				'product_count'            => 0,
				'category_count'           => 0,
				'file_size'                => 0,
				'shard_count'              => 0,
				'build_progress'           => 0,
				'progress_percent'         => 0,
				'processed_product_count'  => 0,
				'estimated_total_products' => 0,
				'error'                    => null,
				'build_job_id'             => 0,
			)
		);

		/**
		 * Action fired when index is reset.
		 */
		do_action( 'aipt_store_index_reset' );

		return true;
	}

	/**
	 * Check if a build is currently running.
	 *
	 * @param bool $fresh If true, clear cache before checking.
	 * @return bool
	 */
	public function isRunning( bool $fresh = false ): bool {
		if ( $fresh ) {
			$this->settings->clearCache();
		}
		$status = $this->settings->get( 'status', 'idle' );
		return $status === 'building';
	}

	/**
	 * Get current build progress (0-100).
	 *
	 * @return int Progress percentage.
	 */
	public function getProgress(): int {
		$status = $this->settings->getStatus();
		return (int) ( $status['build_progress'] ?? 0 );
	}

	/**
	 * Process a batch of products (called by Action Scheduler).
	 *
	 * Uses keyset pagination for efficient large catalog processing.
	 * Products are added to ShardManager which handles sharding automatically.
	 *
	 * @param int $job_id          Scheduler job ID.
	 * @param int $last_id         Last processed product ID (keyset pagination).
	 * @param int $batch_size      Products per batch.
	 * @param int $processed_count Total products processed so far.
	 * @return bool True if more batches remain, false if complete.
	 */
	public function processBatch( int $job_id, int $last_id, int $batch_size, int $processed_count = 0 ): bool {

		$current_status = $this->settings->getStatus();
		$current_job_id = (int) ( $current_status['build_job_id'] ?? 0 );

		if ( $current_job_id !== $job_id ) {
			return false; 
		}

		if ( ! $this->acquireBatchLock( $job_id ) ) {
			return false; 
		}

		if ( $this->temp_storage === null ) {
			$this->temp_storage = new TempStorage( $job_id );
		}

		try {

			$this->initializeShardBuildState( $last_id );

			$current_status  = $this->settings->getStatus();
			$estimated_total = $current_status['estimated_total_products'];

			$current_indexed_count = $this->shard_manager->getTotalProducts();

			if ( $current_indexed_count >= $estimated_total ) {
				return $this->finalizeShardedBuild();
			}

			$products = $this->queryProducts( $last_id, $batch_size );

			if ( empty( $products ) ) {
				return $this->finalizeShardedBuild();
			}

			$attributes      = $this->settings->get( 'attributes', array() );
			$included_fields = $this->settings->getIncludedFields();

			$profiles = $this->profile_builder->buildBatch( $products, $attributes, $included_fields );

			$profiles_added = 0;

			foreach ( $profiles as $product_id => $profile ) {

				if ( $this->shard_manager->getTotalProducts() >= $estimated_total ) {
					break;
				}

				$profile['id'] = (int) $product_id;

				$this->shard_manager->addProduct( $profile );
				++$profiles_added;
			}

			$this->saveShardBuildState();

			$new_indexed_count = $this->shard_manager->getTotalProducts();

			$progress_percent = (int) floor(
				$new_indexed_count / max( 1, $estimated_total ) * 100
			);

			if ( $new_indexed_count > 0 && $progress_percent < 1 ) {
				$progress_percent = 1;
			}
			$progress_percent = min( 99, $progress_percent );

			$new_last_id = ! empty( $products ) ? (int) end( $products )->ID : $last_id;

			$this->settings->updateStatus(
				array(
					'build_progress'          => $progress_percent,
					'progress_percent'        => $progress_percent,
					'processed_product_count' => $new_indexed_count,
					'last_processed_id'       => $new_last_id,
				)
			);

			/**
			 * Action fired after a batch is processed.
			 *
			 * @param int $last_id        Last processed product ID.
			 * @param int $profiles_added Number of products added to index in this batch.
			 */
			do_action( 'aipt_store_index_batch_complete', $last_id, $profiles_added );

			$has_more_to_scan = count( $products ) >= $batch_size;
			$within_limit     = $new_indexed_count < $estimated_total;

			if ( $has_more_to_scan && $within_limit ) {

				as_schedule_single_action(
					time(),
					self::AS_HOOK_BUILD,
					array(
						'profile_id'      => $this->profile_id,
						'job_id'          => $job_id,
						'last_id'         => $new_last_id,
						'batch_size'      => $batch_size,
						'processed_count' => $new_indexed_count,
					),
					self::AS_GROUP
				);

				$this->dispatchAsyncRunner();

				return true;
			}

			return $this->finalizeShardedBuild();

		} catch ( \Exception $e ) {
			$this->handleBuildError( $e );
			return false;
		} finally {

			$this->releaseBatchLock( $job_id );
		}
	}

	/**
	 * Initialize or restore ShardManager build state.
	 *
	 * Restores from split storage:
	 * - Minimal state from manifest
	 * - Shard data from separate temp shard files
	 *
	 * @param int $last_id Last processed product ID (0 = start fresh).
	 * @return void
	 */
	private function initializeShardBuildState( int $last_id ): void {
		if ( $last_id === 0 ) {

			$this->shard_manager->initBuild();
			return;
		}

		$manifest = $this->temp_storage->readManifest();
		if ( $manifest === null || ! isset( $manifest['shard_state'] ) ) {

			error_log(
				sprintf(
					'[AIPT] IndexBuilder: No shard state found for continuing build (last_id=%d, profile=%d). Starting fresh.',
					$last_id,
					$this->profile_id
				)
			);
			$this->shard_manager->initBuild();
			return;
		}

		$minimal_state = $manifest['shard_state'];

		$full_state = array(
			'shards'                => array(),
			'current_shard_id'      => $minimal_state['current_shard_id'] ?? 0,
			'category_distribution' => $minimal_state['category_distribution'] ?? array(),
		);

		$shard_summaries = $minimal_state['shard_summaries'] ?? array();

		$shard_ids = $this->temp_storage->getShardIds();
		foreach ( $shard_ids as $shard_id ) {
			$shard_data = $this->temp_storage->readShard( $shard_id );
			if ( $shard_data !== null ) {

				if ( ! isset( $shard_data['estimated_size'] ) && isset( $shard_summaries[ $shard_id ]['estimated_size'] ) ) {
					$shard_data['estimated_size'] = $shard_summaries[ $shard_id ]['estimated_size'];
				}
				$full_state['shards'][ $shard_id ] = $shard_data;
			} else {

				$full_state['shards'][ $shard_id ] = array(
					'products'       => array(),
					'categories'     => array(),
					'estimated_size' => $shard_summaries[ $shard_id ]['estimated_size'] ?? 0,
				);
			}
		}

		$current_id = $full_state['current_shard_id'];
		if ( ! isset( $full_state['shards'][ $current_id ] ) ) {
			$full_state['shards'][ $current_id ] = array(
				'products'       => array(),
				'categories'     => array(),
				'estimated_size' => $shard_summaries[ $current_id ]['estimated_size'] ?? 0,
			);
		}

		$this->shard_manager->restoreBuildState( $full_state );
	}

	/**
	 * Save ShardManager build state to TempStorage.
	 *
	 * Uses a split storage approach to prevent memory exhaustion:
	 * - Shard data (products) written to separate temp shard files
	 * - Manifest contains only minimal state (counters, distribution)
	 *
	 * This prevents 502 timeouts when saving large builds with many products.
	 *
	 * @return void
	 * @throws \RuntimeException If save fails.
	 */
	private function saveShardBuildState(): void {
		if ( $this->temp_storage === null ) {
			return;
		}

		$full_state = $this->shard_manager->getBuildState();
		if ( $full_state === null ) {
			return;
		}

		foreach ( $full_state['shards'] as $shard_id => $shard_data ) {
			if ( ! empty( $shard_data['products'] ) ) {
				$this->temp_storage->writeShard( (int) $shard_id, $shard_data );
			}
		}

		$manifest = array(
			'shard_state' => $this->shard_manager->getMinimalBuildState(),
			'stats'       => $this->shard_manager->getBuildStats(),
			'saved_at'    => current_time( 'c' ),
		);

		$success = $this->temp_storage->writeManifest( $manifest );

		if ( ! $success ) {

			throw new \RuntimeException(
				__(
					'Failed to save shard build state. Please ensure the uploads directory is writable.',
					'ai-product-tools'
				)
			);

		}
	}

	/**
	 * Finalize sharded build: write all shard files and map.
	 *
	 * @return bool False (no more batches).
	 */
	private function finalizeShardedBuild(): bool {

		$build_stats    = $this->shard_manager->getBuildStats();
		$total_products = $build_stats['total_products'] ?? 0;

		if ( $total_products === 0 ) {
			$this->settings->updateStatus(
				array(
					'status'         => 'completed',
					'last_build'     => current_time( 'c' ),
					'product_count'  => 0,
					'category_count' => 0,
					'file_size'      => 0,
					'shard_count'    => 0,
					'build_progress' => 100,
					'error'          => null,
				)
			);
			return false;
		}

		$global_meta = $this->buildMeta( 0, 0, 0 ); 

		try {

			$result = $this->shard_manager->finalizeBuild( $global_meta );

			$file_size      = $result['total_size'] ?? 0;
			$product_count  = $result['total_products'] ?? 0;
			$category_count = $result['total_categories'] ?? 0;
			$shard_count    = $result['shard_count'] ?? 0;

			$this->settings->updateStatus(
				array(
					'status'                   => 'completed',
					'last_build'               => current_time( 'c' ),
					'product_count'            => $product_count,
					'category_count'           => $category_count,
					'file_size'                => $file_size,
					'shard_count'              => $shard_count,
					'build_progress'           => 100,
					'progress_percent'         => 100,
					'processed_product_count'  => $product_count,
					'estimated_total_products' => $product_count,
					'error'                    => null,
				)
			);

			if ( $this->temp_storage !== null ) {
				$this->temp_storage->cleanup();
			}

			$action_meta = $result['map_data']['meta'] ?? array();
			if ( empty( $action_meta ) ) {
				$action_meta = $this->buildMeta( $product_count, $category_count, $file_size );
			}

			$action_meta['shard_count'] = $shard_count;

			/**
			 * Action fired when sharded build completes.
			 *
			 * @param array $action_meta Build metadata with shard information.
			 */
			do_action( 'aipt_store_index_build_complete', $action_meta );

		} catch ( \Exception $e ) {
			$this->handleBuildError( $e );
		}

		return false;
	}

	/**
	 * Build index metadata.
	 *
	 * @param int $product_count  Number of products.
	 * @param int $category_count Number of categories.
	 * @param int $file_size      File size in bytes.
	 * @return array Meta structure.
	 */
	private function buildMeta( int $product_count, int $category_count, int $file_size ): array {
		return array(
			'version'         => self::INDEX_VERSION,
			'built_at'        => current_time( 'c' ),
			'product_count'   => $product_count,
			'category_count'  => $category_count,
			'file_size_bytes' => $file_size,
			'profile_id'      => $this->profile_id,
			'settings'        => array(
				'max_products'     => $this->settings->get( 'max_products' ),
				'categories'       => $this->settings->get( 'categories', array() ),
				'stock_filter'     => $this->settings->get( 'stock_filter' ),
				'date_filter_days' => $this->settings->get( 'date_filter_days' ),
				'attributes'       => $this->settings->get( 'attributes', array() ),
			),
		);
	}

	/**
	 * Handle build error.
	 *
	 * @param \Exception $e Exception that occurred.
	 */
	private function handleBuildError( \Exception $e ): void {

		error_log(
			sprintf(
				'[AIPT] IndexBuilder::handleBuildError() - Profile: %d, Error: %s, Trace: %s',
				$this->profile_id,
				$e->getMessage(),
				$e->getTraceAsString()
			)
		);

		$this->settings->updateStatus(
			array(
				'status' => 'failed',
				'error'  => $e->getMessage(),
			)
		);

		/**
		 * Action fired when build fails.
		 *
		 * @param string $error Error message.
		 */
		do_action( 'aipt_store_index_build_failed', $e->getMessage() );

		if ( $this->temp_storage !== null ) {
			$this->temp_storage->cleanup();
		}
	}

	/**
	 * Dispatch Action Scheduler's async runner.
	 *
	 * Attempts to trigger async processing. This is a best-effort operation -
	 * if it fails, the frontend will handle batch processing via the /process endpoint.
	 *
	 * @return void
	 */
	private function dispatchAsyncRunner(): void {

		if ( class_exists( 'ActionScheduler_AsyncRequest_QueueRunner' ) ) {
			try {
				$async_request = new \ActionScheduler_AsyncRequest_QueueRunner(
					\ActionScheduler::store()
				);
				$async_request->maybe_dispatch();
			} catch ( \Exception $e ) {

				error_log(
					sprintf(
						'[AIPT] IndexBuilder::dispatchAsyncRunner() - Async dispatch failed (frontend fallback active): %s',
						$e->getMessage()
					)
				);
			}
		}

		if ( ! defined( 'DISABLE_WP_CRON' ) || ! DISABLE_WP_CRON ) {
			spawn_cron();
		}
	}

	/**
	 * Query products for indexing using keyset pagination.
	 *
	 * @param int $last_id Last processed product ID (0 for first batch).
	 * @param int $limit   Number of products to fetch.
	 * @return array Array of product row objects.
	 */
	private function queryProducts( int $last_id, int $limit ): array {
		global $wpdb;

		$settings = $this->settings->getAll();

		$where = array(
			"p.post_type = 'product'",
			"p.post_status = 'publish'",
			$wpdb->prepare( 'p.ID > %d', $last_id ),
		);

		$filter_mode = $settings['filter_mode'] ?? 'all';

		$taxonomy_conditions = array();

		if ( ! empty( $settings['categories'] ) ) {
			$taxonomy_conditions[] = $this->buildCategoryCondition( $settings['categories'] );
		}

		if ( ! empty( $settings['tags'] ) ) {
			$taxonomy_conditions[] = $this->buildTagCondition( $settings['tags'] );
		}

		if ( ! empty( $taxonomy_conditions ) ) {
			$operator = ( $filter_mode === 'any' ) ? ' OR ' : ' AND ';
			$where[]  = '(' . implode( $operator, $taxonomy_conditions ) . ')';
		}

		if ( $settings['stock_filter'] !== 'all' ) {
			$where[] = $wpdb->prepare(
				"p.ID IN (
                    SELECT pm.post_id FROM {$wpdb->postmeta} pm
                    WHERE pm.meta_key = '_stock_status' AND pm.meta_value = %s
                )",
				$settings['stock_filter']
			);
		}

		if ( $settings['date_filter_days'] > 0 ) {

			$date_cutoff = date( 'Y-m-d H:i:s', strtotime( "-{$settings['date_filter_days']} days", current_time( 'timestamp' ) ) );
			$where[]     = $wpdb->prepare( 'p.post_date >= %s', $date_cutoff );
		}

		$where_clause = implode( ' AND ', $where );

		$query = $wpdb->prepare(
			"SELECT
                p.ID,
                p.post_title,
                p.post_excerpt,
                p.post_content,
                pm_price.meta_value as _price,
                pm_stock.meta_value as _stock_status
            FROM {$wpdb->posts} p
            LEFT JOIN {$wpdb->postmeta} pm_price
                ON p.ID = pm_price.post_id AND pm_price.meta_key = '_price'
            LEFT JOIN {$wpdb->postmeta} pm_stock
                ON p.ID = pm_stock.post_id AND pm_stock.meta_key = '_stock_status'
            WHERE {$where_clause}
            GROUP BY p.ID
            ORDER BY p.ID ASC
            LIMIT %d",
			$limit
		);

		return $wpdb->get_results( $query );

	}

	/**
	 * Count total products matching current filters.
	 *
	 * @return int Product count.
	 */
	private function countMatchingProducts(): int {
		global $wpdb;

		$settings    = $this->settings->getAll();
		$filter_mode = $settings['filter_mode'] ?? 'all';

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

		$taxonomy_conditions = array();

		if ( ! empty( $settings['categories'] ) ) {
			$taxonomy_conditions[] = $this->buildCategoryCondition( $settings['categories'] );
		}

		if ( ! empty( $settings['tags'] ) ) {
			$taxonomy_conditions[] = $this->buildTagCondition( $settings['tags'] );
		}

		if ( ! empty( $taxonomy_conditions ) ) {
			$operator = ( $filter_mode === 'any' ) ? ' OR ' : ' AND ';
			$where[]  = '(' . implode( $operator, $taxonomy_conditions ) . ')';
		}

		if ( $settings['stock_filter'] !== 'all' ) {
			$where[] = $wpdb->prepare(
				"p.ID IN (
                    SELECT pm.post_id FROM {$wpdb->postmeta} pm
                    WHERE pm.meta_key = '_stock_status' AND pm.meta_value = %s
                )",
				$settings['stock_filter']
			);
		}

		if ( $settings['date_filter_days'] > 0 ) {

			$date_cutoff = date( 'Y-m-d H:i:s', strtotime( "-{$settings['date_filter_days']} days", current_time( 'timestamp' ) ) );
			$where[]     = $wpdb->prepare( 'p.post_date >= %s', $date_cutoff );
		}

		$where_clause = implode( ' AND ', $where );

		$count_func = ( 'any' === $filter_mode ) ? 'COUNT(DISTINCT p.ID)' : 'COUNT(*)';

		$count = $wpdb->get_var( "SELECT {$count_func} FROM {$wpdb->posts} p WHERE {$where_clause}" );

		$max_products = $settings['max_products'];

		return min( (int) $count, $max_products );
	}

	/**
	 * Build category subquery condition for SQL WHERE clause.
	 *
	 * @param array $categories Category term IDs.
	 * @return string SQL condition.
	 */
	private function buildCategoryCondition( array $categories ): string {
		global $wpdb;

		$cat_ids          = array_map( 'intval', $categories );
		$cat_placeholders = implode( ',', array_fill( 0, count( $cat_ids ), '%d' ) );

		return $wpdb->prepare(
			"p.ID IN (
                SELECT tr.object_id FROM {$wpdb->term_relationships} tr
                INNER JOIN {$wpdb->term_taxonomy} tt ON tr.term_taxonomy_id = tt.term_taxonomy_id
                WHERE tt.taxonomy = 'product_cat' AND tt.term_id IN ({$cat_placeholders})
            )",
			...$cat_ids
		);

	}

	/**
	 * Build tag subquery condition for SQL WHERE clause.
	 *
	 * @param array $tags Tag term IDs.
	 * @return string SQL condition.
	 */
	private function buildTagCondition( array $tags ): string {
		global $wpdb;

		$tag_ids          = array_map( 'intval', $tags );
		$tag_placeholders = implode( ',', array_fill( 0, count( $tag_ids ), '%d' ) );

		return $wpdb->prepare(
			"p.ID IN (
                SELECT tr.object_id FROM {$wpdb->term_relationships} tr
                INNER JOIN {$wpdb->term_taxonomy} tt ON tr.term_taxonomy_id = tt.term_taxonomy_id
                WHERE tt.taxonomy = 'product_tag' AND tt.term_id IN ({$tag_placeholders})
            )",
			...$tag_ids
		);

	}

	/**
	 * Get the Action Scheduler hook name for builds.
	 *
	 * @return string
	 */
	public static function getBuildHook(): string {
		return self::AS_HOOK_BUILD;
	}

	/**
	 * Get the Action Scheduler hook name for scheduled rebuilds.
	 *
	 * @return string
	 */
	public static function getScheduledHook(): string {
		return self::AS_HOOK_SCHEDULED;
	}

	/**
	 * Get the Action Scheduler group name.
	 *
	 * @return string
	 */
	public static function getGroup(): string {
		return self::AS_GROUP;
	}

	/**
	 * Acquire a batch processing lock.
	 *
	 * @since 2.5.0
	 * @param int $job_id Build job ID.
	 * @return bool True if lock acquired, false if already held.
	 */
	public function acquireBatchLock( int $job_id ): bool {
		global $wpdb;

		$lock_key = 'aipt_batch_lock_' . $job_id;

		$result = $wpdb->get_var( $wpdb->prepare( 'SELECT GET_LOCK(%s, %d)', $lock_key, self::LOCK_WAIT_TIMEOUT ) );

		if ( $result === '1' ) {
			return true;
		}

		if ( $result === '0' ) {

			return false;
		}

		$transient_key = 'aipt_batch_lock_' . $job_id;
		$existing      = get_transient( $transient_key );

		if ( $existing !== false ) {

			$lock_time    = (int) $existing;
			$lock_timeout = $this->getLockTimeout();
			if ( time() - $lock_time < $lock_timeout ) {

				return false;
			}

		}

		set_transient( $transient_key, time(), $this->getLockTimeout() );
		return true;
	}

	/**
	 * Release a batch processing lock.
	 *
	 * @since 2.5.0
	 * @param int $job_id Build job ID.
	 * @return bool True if at least one lock was released successfully.
	 */
	public function releaseBatchLock( int $job_id ): bool {
		global $wpdb;

		$lock_key          = 'aipt_batch_lock_' . $job_id;
		$mysql_released    = false;
		$transient_deleted = false;

		$result = $wpdb->get_var( $wpdb->prepare( 'SELECT RELEASE_LOCK(%s)', $lock_key ) );

		if ( $result === '1' ) {
			$mysql_released = true;
		} elseif ( $result === '0' ) {

			error_log(
				sprintf(
					'[AIPT] IndexBuilder: MySQL lock not held by this connection for job %d (may be normal if using transient fallback)',
					$job_id
				)
			);
		} elseif ( $result === null ) {

			$mysql_released = true;
		}

		$transient_deleted = delete_transient( 'aipt_batch_lock_' . $job_id );

		return $mysql_released || $transient_deleted;
	}

	/**
	 * Check if a batch lock is currently held.
	 *
	 * @since 2.5.0
	 * @param int $job_id Build job ID.
	 * @return bool True if lock is held.
	 */
	public function isBatchLockHeld( int $job_id ): bool {
		global $wpdb;

		$lock_key = 'aipt_batch_lock_' . $job_id;

		$result = $wpdb->get_var( $wpdb->prepare( 'SELECT IS_USED_LOCK(%s)', $lock_key ) );

		if ( $result !== null ) {
			return true;
		}

		$existing = get_transient( 'aipt_batch_lock_' . $job_id );
		if ( $existing !== false ) {
			$lock_time = (int) $existing;
			if ( time() - $lock_time < $this->getLockTimeout() ) {
				return true;
			}
		}

		return false;
	}
}
