<?php
/**
 * Bulk content optimization queue.
 *
 * Manages batch processing of product content optimization across
 * an entire catalog using Action Scheduler for background execution.
 *
 * @package AICommerce\Content
 */

declare(strict_types=1);

namespace AICommerce\Content;

use AICommerce\Content\Contracts\AIProviderInterface;
use AICommerce\Core\ServiceContainer;
use AICommerce\Feed\ProductDataExtractor;

/**
 * Handles bulk product content optimization via Action Scheduler.
 */
class BulkQueue {

	/**
	 * Action Scheduler hook for processing a chunk of products.
	 *
	 * @var string
	 */
	public const CHUNK_ACTION = 'aicommerce_bulk_process_chunk';

	/**
	 * Products per Action Scheduler chunk.
	 *
	 * @var int
	 */
	private const CHUNK_SIZE = 20;

	/**
	 * Seconds between chunk schedules to prevent thundering herd.
	 *
	 * @var int
	 */
	private const CHUNK_STAGGER = 5;

	/**
	 * Seconds to sleep between products within a chunk (API rate limit).
	 *
	 * @var int
	 */
	private const PRODUCT_DELAY = 2;

	/**
	 * Max rows per batch INSERT.
	 *
	 * @var int
	 */
	private const INSERT_BATCH_SIZE = 500;

	/**
	 * WordPress database abstraction.
	 *
	 * @var \wpdb
	 */
	private \wpdb $wpdb;

	/**
	 * Service container for resolving AI providers.
	 *
	 * @var ServiceContainer
	 */
	private ServiceContainer $container;

	/**
	 * Constructor.
	 *
	 * @param \wpdb            $wpdb      WordPress database instance.
	 * @param ServiceContainer $container Service container.
	 */
	public function __construct( \wpdb $wpdb, ServiceContainer $container ) {
		$this->wpdb      = $wpdb;
		$this->container = $container;
	}

	/**
	 * Enqueue a bulk optimization batch.
	 *
	 * @param array $options Batch options.
	 *
	 * @return string Batch UUID.
	 *
	 * @throws \RuntimeException If no products found or Action Scheduler unavailable.
	 */
	public function enqueue( array $options ): string {
		if ( ! function_exists( 'as_schedule_single_action' ) ) {
			throw new \RuntimeException(
				esc_html__( 'Action Scheduler is required for bulk optimization.', 'ai-discoverability-kit' )
			);
		}

		$provider_slug = $options['provider'] ?? 'openai';

		// Step 1: Resolve product IDs.
		$product_ids = $this->resolve_product_ids( $options );

		if ( empty( $product_ids ) ) {
			throw new \RuntimeException(
				esc_html__( 'No products found matching the specified criteria.', 'ai-discoverability-kit' )
			);
		}

		// Step 2: Calculate cost estimate.
		$provider = $this->resolve_provider( $provider_slug );
		$cost     = $this->calculate_cost( $provider, count( $product_ids ), $options );

		// Step 3: Create batch record.
		$batch_id = wp_generate_uuid4();
		$table    = $this->wpdb->prefix . 'aicommerce_bulk_jobs';

		// Safe: internal $wpdb->prefix table names; user values use placeholders.
		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		$this->wpdb->insert(
			$table,
			array(
				'batch_id'           => $batch_id,
				'status'             => 'pending',
				'total_products'     => count( $product_ids ),
				'processed_products' => 0,
				'failed_products'    => 0,
				'provider'           => $provider_slug,
				'options'            => wp_json_encode( $options ),
				'cost_estimate_usd'  => $cost,
			),
			array( '%s', '%s', '%d', '%d', '%d', '%s', '%s', '%f' )
		);
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

		// Step 4: Insert product statuses in batches.
		$this->insert_product_statuses( $batch_id, $product_ids );

		// Step 5: Schedule Action Scheduler chunks.
		$chunks = array_chunk( $product_ids, self::CHUNK_SIZE );
		$offset = 0;

		foreach ( $chunks as $chunk ) {
			as_schedule_single_action(
				time() + $offset,
				self::CHUNK_ACTION,
				array( $batch_id, $chunk ),
				'aicommerce'
			);
			$offset += self::CHUNK_STAGGER;
		}

		// Step 6: Update batch to running.
		// Safe: internal $wpdb->prefix table names; user values use placeholders.
		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		$this->wpdb->update(
			$table,
			array(
				'status'     => 'running',
				'started_at' => current_time( 'mysql', true ),
			),
			array( 'batch_id' => $batch_id ),
			array( '%s', '%s' ),
			array( '%s' )
		);
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

		return $batch_id;
	}

	/**
	 * Process a chunk of products.
	 *
	 * Called by Action Scheduler. Checks batch status before each product.
	 *
	 * @param string $batch_id   Batch UUID.
	 * @param array  $product_ids Product IDs to process.
	 *
	 * @return void
	 */
	public function process_chunk( string $batch_id, array $product_ids ): void {
		if ( ! aicommerce_is_pro() ) {
			return;
		}

		$jobs_table    = $this->wpdb->prefix . 'aicommerce_bulk_jobs';
		$product_table = $this->wpdb->prefix . 'aicommerce_bulk_product_status';

		// Check batch status — bail if paused or cancelled.
		// Safe: internal $wpdb->prefix table names; user values use placeholders.
		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		$batch_status = $this->wpdb->get_var(
			$this->wpdb->prepare(
				"SELECT status FROM {$jobs_table} WHERE batch_id = %s",
				$batch_id
			)
		);
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

		if ( in_array( $batch_status, array( 'paused', 'cancelled' ), true ) ) {
			return;
		}

		// Resolve provider from batch options.
		// Safe: internal $wpdb->prefix table names; user values use placeholders.
		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		$batch_row = $this->wpdb->get_row(
			$this->wpdb->prepare(
				"SELECT provider, options FROM {$jobs_table} WHERE batch_id = %s",
				$batch_id
			)
		);
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

		if ( ! $batch_row ) {
			return;
		}

		$provider  = $this->resolve_provider( $batch_row->provider );
		$extractor = $this->container->get( ProductDataExtractor::class );
		$optimizer = new ContentOptimizer( $provider, $extractor );
		$options   = json_decode( $batch_row->options, true ) ?: array();
		$processed = 0;

		foreach ( $product_ids as $product_id ) {
			$product_id = (int) $product_id;

			// Re-check batch status between products.
			if ( $processed > 0 ) {
				// Safe: internal $wpdb->prefix table names; user values use placeholders.
				// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
				$current_status = $this->wpdb->get_var(
					$this->wpdb->prepare(
						"SELECT status FROM {$jobs_table} WHERE batch_id = %s",
						$batch_id
					)
				);
				// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

				if ( in_array( $current_status, array( 'paused', 'cancelled' ), true ) ) {
					return;
				}
			}

			// Mark product as processing.
			// Safe: internal $wpdb->prefix table names; user values use placeholders.
			// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
			$this->wpdb->update(
				$product_table,
				array( 'status' => 'processing' ),
				array(
					'batch_id'   => $batch_id,
					'product_id' => $product_id,
				),
				array( '%s' ),
				array( '%s', '%d' )
			);
			// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

			try {
				$result = $optimizer->optimize_product( $product_id, true );

				// Mark complete.
				// Safe: internal $wpdb->prefix table names; user values use placeholders.
				// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
				$this->wpdb->update(
					$product_table,
					array(
						'status'       => 'complete',
						'processed_at' => current_time( 'mysql', true ),
					),
					array(
						'batch_id'   => $batch_id,
						'product_id' => $product_id,
					),
					array( '%s', '%s' ),
					array( '%s', '%d' )
				);
				// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

				// Increment processed count.
				// Safe: internal $wpdb->prefix table names; user values use placeholders.
				// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
				$this->wpdb->query(
					$this->wpdb->prepare(
						"UPDATE {$jobs_table} SET processed_products = processed_products + 1 WHERE batch_id = %s",
						$batch_id
					)
				);
				// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

				// Accumulate actual cost.
				if ( isset( $result['cost_estimate_usd'] ) ) {
					// Safe: internal $wpdb->prefix table names; user values use placeholders.
					// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
					$this->wpdb->query(
						$this->wpdb->prepare(
							"UPDATE {$jobs_table} SET actual_cost_usd = actual_cost_usd + %f WHERE batch_id = %s",
							$result['cost_estimate_usd'],
							$batch_id
						)
					);
					// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
				}

				unset( $result );
			} catch ( \Exception $e ) {
				// Mark failed.
				// Safe: internal $wpdb->prefix table names; user values use placeholders.
				// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
				$this->wpdb->update(
					$product_table,
					array(
						'status'        => 'failed',
						'error_message' => mb_substr( $e->getMessage(), 0, 500 ),
						'processed_at'  => current_time( 'mysql', true ),
					),
					array(
						'batch_id'   => $batch_id,
						'product_id' => $product_id,
					),
					array( '%s', '%s', '%s' ),
					array( '%s', '%d' )
				);
				// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

				// Increment failed count + append to error log.
				$error_entry = wp_json_encode(
					array(
						'product_id' => $product_id,
						'error'      => mb_substr( $e->getMessage(), 0, 200 ),
					)
				);

				// Safe: internal $wpdb->prefix table names; user values use placeholders.
				// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
				$this->wpdb->query(
					$this->wpdb->prepare(
						"UPDATE {$jobs_table} SET failed_products = failed_products + 1,
						error_log = CONCAT(COALESCE(error_log, ''), %s, '\n')
						WHERE batch_id = %s",
						$error_entry,
						$batch_id
					)
				);
				// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
			}

			++$processed;

			// Memory management: free product data every 5 products.
			if ( 0 === $processed % 5 ) {
				gc_collect_cycles();
			}

			// Rate limit: sleep between products (skip after last one).
			if ( $processed < count( $product_ids ) ) {
				sleep( self::PRODUCT_DELAY );
			}
		}

		// Check if batch is fully complete.
		$this->maybe_complete_batch( $batch_id );
	}

	/**
	 * Cancel a running batch.
	 *
	 * @param string $batch_id Batch UUID.
	 *
	 * @return bool True if cancelled.
	 */
	public function cancel_batch( string $batch_id ): bool {
		$table = $this->wpdb->prefix . 'aicommerce_bulk_jobs';

		// Safe: internal $wpdb->prefix table names; user values use placeholders.
		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		$updated = $this->wpdb->update(
			$table,
			array(
				'status'       => 'cancelled',
				'completed_at' => current_time( 'mysql', true ),
			),
			array( 'batch_id' => $batch_id ),
			array( '%s', '%s' ),
			array( '%s' )
		);
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

		// Unschedule pending AS actions for this batch.
		if ( function_exists( 'as_unschedule_all_actions' ) ) {
			as_unschedule_all_actions( self::CHUNK_ACTION, null, 'aicommerce' );
		}

		return false !== $updated && $updated > 0;
	}

	/**
	 * Pause a running batch.
	 *
	 * Running chunks will check status and stop at the next product.
	 *
	 * @param string $batch_id Batch UUID.
	 *
	 * @return bool True if paused.
	 */
	public function pause_batch( string $batch_id ): bool {
		$table = $this->wpdb->prefix . 'aicommerce_bulk_jobs';

		// Safe: internal $wpdb->prefix table names; user values use placeholders.
		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		$updated = $this->wpdb->update(
			$table,
			array( 'status' => 'paused' ),
			array( 'batch_id' => $batch_id ),
			array( '%s' ),
			array( '%s' )
		);
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

		return false !== $updated && $updated > 0;
	}

	/**
	 * Resume a paused batch.
	 *
	 * Re-schedules AS jobs for remaining pending products.
	 *
	 * @param string $batch_id Batch UUID.
	 *
	 * @return bool True if resumed.
	 */
	public function resume_batch( string $batch_id ): bool {
		if ( ! function_exists( 'as_schedule_single_action' ) ) {
			return false;
		}

		$jobs_table    = $this->wpdb->prefix . 'aicommerce_bulk_jobs';
		$product_table = $this->wpdb->prefix . 'aicommerce_bulk_product_status';

		// Get remaining pending product IDs.
		// Safe: internal $wpdb->prefix table names; user values use placeholders.
		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		$pending_ids = $this->wpdb->get_col(
			$this->wpdb->prepare(
				"SELECT product_id FROM {$product_table} WHERE batch_id = %s AND status = 'pending'",
				$batch_id
			)
		);
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

		if ( empty( $pending_ids ) ) {
			$this->maybe_complete_batch( $batch_id );
			return false;
		}

		// Re-schedule chunks.
		$chunks = array_chunk( array_map( 'intval', $pending_ids ), self::CHUNK_SIZE );
		$offset = 0;

		foreach ( $chunks as $chunk ) {
			as_schedule_single_action(
				time() + $offset,
				self::CHUNK_ACTION,
				array( $batch_id, $chunk ),
				'aicommerce'
			);
			$offset += self::CHUNK_STAGGER;
		}

		// Update status to running.
		// Safe: internal $wpdb->prefix table names; user values use placeholders.
		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		$this->wpdb->update(
			$jobs_table,
			array( 'status' => 'running' ),
			array( 'batch_id' => $batch_id ),
			array( '%s' ),
			array( '%s' )
		);
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

		return true;
	}

	/**
	 * Get batch status with progress details.
	 *
	 * @param string $batch_id Batch UUID.
	 *
	 * @return array|null Batch status data, or null if not found.
	 */
	public function get_batch_status( string $batch_id ): ?array {
		$table = $this->wpdb->prefix . 'aicommerce_bulk_jobs';

		// Safe: internal $wpdb->prefix table names; user values use placeholders.
		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		$row = $this->wpdb->get_row(
			$this->wpdb->prepare(
				"SELECT * FROM {$table} WHERE batch_id = %s",
				$batch_id
			),
			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 ( ! $row ) {
			return null;
		}

		$total     = (int) $row['total_products'];
		$processed = (int) $row['processed_products'];
		$failed    = (int) $row['failed_products'];
		$done      = $processed + $failed;

		// Progress percentage.
		$progress = $total > 0 ? (int) round( ( $done / $total ) * 100 ) : 0;

		// Elapsed time.
		$started_at = $row['started_at'] ? strtotime( $row['started_at'] ) : 0;
		$elapsed    = $started_at > 0 ? time() - $started_at : 0;

		// ETA estimate based on current rate.
		$remaining = $total - $done;
		$eta       = 0;
		if ( $done > 0 && $remaining > 0 && $elapsed > 0 ) {
			$rate = $done / $elapsed;
			$eta  = (int) round( $remaining / $rate );
		}

		// Recent errors (last 5).
		$recent_errors = array();
		if ( ! empty( $row['error_log'] ) ) {
			$lines = array_filter( explode( "\n", $row['error_log'] ) );
			$lines = array_slice( $lines, -5 );
			foreach ( $lines as $line ) {
				$decoded = json_decode( $line, true );
				if ( is_array( $decoded ) ) {
					$recent_errors[] = $decoded;
				}
			}
		}

		return array(
			'batch_id'           => $row['batch_id'],
			'status'             => $row['status'],
			'total_products'     => $total,
			'processed_products' => $processed,
			'failed_products'    => $failed,
			'progress_percent'   => $progress,
			'elapsed_seconds'    => $elapsed,
			'eta_seconds'        => $eta,
			'cost_estimate_usd'  => (float) $row['cost_estimate_usd'],
			'actual_cost_usd'    => (float) $row['actual_cost_usd'],
			'provider'           => $row['provider'],
			'recent_errors'      => $recent_errors,
			'started_at'         => $row['started_at'],
			'completed_at'       => $row['completed_at'],
		);
	}

	/**
	 * Get cost estimate without creating a batch.
	 *
	 * @param array $options Batch options.
	 *
	 * @return array Cost estimate data.
	 */
	public function get_cost_estimate( array $options ): array {
		$provider_slug = $options['provider'] ?? 'openai';
		$product_ids   = $this->resolve_product_ids( $options );
		$count         = count( $product_ids );

		$provider = $this->resolve_provider( $provider_slug );
		$cost     = $this->calculate_cost( $provider, $count, $options );

		// Estimated time: 2 seconds per product.
		$estimated_minutes = (int) ceil( ( $count * self::PRODUCT_DELAY ) / 60 );

		return array(
			'product_count'     => $count,
			'cost_estimate_usd' => round( $cost, 4 ),
			'estimated_minutes' => $estimated_minutes,
		);
	}

	/**
	 * Get batch history (most recent 10 batches).
	 *
	 * @return array List of batch summaries.
	 */
	public function get_batch_history(): array {
		$table = $this->wpdb->prefix . 'aicommerce_bulk_jobs';

		// Safe: internal $wpdb->prefix table names; user values use placeholders.
		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		$rows = $this->wpdb->get_results(
			"SELECT batch_id, status, total_products, processed_products, failed_products,
					provider, cost_estimate_usd, actual_cost_usd, started_at, completed_at, created_at
			 FROM {$table}
			 ORDER BY created_at DESC
			 LIMIT 10",
			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 is_array( $rows ) ? $rows : array();
	}

	/**
	 * Resolve product IDs from batch options.
	 *
	 * @param array $options Batch options.
	 *
	 * @return int[] Product IDs.
	 */
	private function resolve_product_ids( array $options ): array {
		$include_all  = ! empty( $options['include_all'] );
		$category_ids = $options['category_ids'] ?? array();
		$product_ids  = $options['product_ids'] ?? array();
		$overwrite    = ! empty( $options['overwrite_existing'] );

		if ( $include_all || ! empty( $category_ids ) ) {
			$args = array(
				'status'  => 'publish',
				'limit'   => -1,
				'return'  => 'ids',
				'orderby' => 'ID',
				'order'   => 'ASC',
			);

			if ( ! empty( $category_ids ) ) {
				$args['category'] = array_map( 'intval', $category_ids );
			}

			$query       = new \WC_Product_Query( $args );
			$product_ids = $query->get_products();
		}

		$product_ids = array_map( 'intval', $product_ids );
		$product_ids = array_unique( array_filter( $product_ids ) );

		// Filter out recently optimized products if not overwriting.
		if ( ! $overwrite && ! empty( $product_ids ) ) {
			$cutoff = time() - ( 7 * DAY_IN_SECONDS );

			$product_ids = array_filter(
				$product_ids,
				static function ( int $id ) use ( $cutoff ): bool {
					$last_date = (int) get_post_meta( $id, ContentOptimizer::META_DATE, true );
					return 0 === $last_date || $last_date < $cutoff;
				}
			);

			$product_ids = array_values( $product_ids );
		}

		return $product_ids;
	}

	/**
	 * Insert product status rows in batches.
	 *
	 * @param string $batch_id    Batch UUID.
	 * @param int[]  $product_ids Product IDs.
	 *
	 * @return void
	 */
	private function insert_product_statuses( string $batch_id, array $product_ids ): void {
		$table  = $this->wpdb->prefix . 'aicommerce_bulk_product_status';
		$chunks = array_chunk( $product_ids, self::INSERT_BATCH_SIZE );

		foreach ( $chunks as $chunk ) {
			$values       = array();
			$placeholders = array();

			foreach ( $chunk as $product_id ) {
				$placeholders[] = '(%s, %d, %s)';
				$values[]       = $batch_id;
				$values[]       = $product_id;
				$values[]       = 'pending';
			}

			$placeholder_str = implode( ', ', $placeholders );

			// Safe: internal $wpdb->prefix table name; the VALUES placeholders are
			// generated as (%d,%s,%s) groups and filled via $wpdb->prepare().
			// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber
			$this->wpdb->query(
				$this->wpdb->prepare(
					"INSERT INTO {$table} (batch_id, product_id, status) VALUES {$placeholder_str}",
					...$values
				)
			);
			// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber
		}
	}

	/**
	 * Check if a batch is fully complete and update status.
	 *
	 * @param string $batch_id Batch UUID.
	 *
	 * @return void
	 */
	private function maybe_complete_batch( string $batch_id ): void {
		$jobs_table    = $this->wpdb->prefix . 'aicommerce_bulk_jobs';
		$product_table = $this->wpdb->prefix . 'aicommerce_bulk_product_status';

		// Count remaining pending/processing products.
		// Safe: internal $wpdb->prefix table names; user values use placeholders.
		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		$remaining = (int) $this->wpdb->get_var(
			$this->wpdb->prepare(
				"SELECT COUNT(*) FROM {$product_table} WHERE batch_id = %s AND status IN ('pending', 'processing')",
				$batch_id
			)
		);
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

		if ( 0 === $remaining ) {
			// Safe: internal $wpdb->prefix table names; user values use placeholders.
			// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
			$this->wpdb->update(
				$jobs_table,
				array(
					'status'       => 'complete',
					'completed_at' => current_time( 'mysql', true ),
				),
				array( 'batch_id' => $batch_id ),
				array( '%s', '%s' ),
				array( '%s' )
			);
			// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		}
	}

	/**
	 * Resolve an AI provider instance by slug.
	 *
	 * @param string $slug Provider slug.
	 *
	 * @return AIProviderInterface
	 *
	 * @throws \InvalidArgumentException If provider is unknown.
	 */
	private function resolve_provider( string $slug ): AIProviderInterface {
		$map = array(
			'openai'    => OpenAIProvider::class,
			'anthropic' => AnthropicProvider::class,
			'gemini'    => GeminiProvider::class,
		);

		if ( ! isset( $map[ $slug ] ) ) {
			throw new \InvalidArgumentException(
				esc_html(
					sprintf(
						/* translators: %s: provider slug */
						__( 'Unknown AI provider: %s', 'ai-discoverability-kit' ),
						$slug
					)
				)
			);
		}

		return $this->container->get( $map[ $slug ] );
	}

	/**
	 * Calculate estimated cost for a batch.
	 *
	 * @param AIProviderInterface $provider      AI provider instance.
	 * @param int                 $product_count Number of products.
	 * @param array               $options       Batch options.
	 *
	 * @return float Estimated cost in USD.
	 */
	private function calculate_cost( AIProviderInterface $provider, int $product_count, array $options ): float {
		// Rough token estimate per product: 400 input + 300 output.
		$cost_per_product = $provider->estimate_cost_usd( 400, 300 );

		// Alt text adds ~$0.002 per product (Vision API).
		if ( ! empty( $options['generate_alt_text'] ) ) {
			$cost_per_product += 0.002;
		}

		return $cost_per_product * $product_count;
	}
}