<?php
/**
 * Database operations for PDF Search plugin
 *
 * @package WebEquipe\PDFSearch
 * @since 1.0.0
 */

namespace WebEquipe\PDFSearch;

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

/**
 * Database class for managing PDF search index table
 */
class Database {
	/**
	 * Last error message from upsert_pdf (when it returns false).
	 * Used so callers can show a message even when $wpdb->last_error is empty.
	 *
	 * @since 1.0.0
	 * @var string
	 */
	private static $last_upsert_error = '';

	/**
	 * Get the last error message set by upsert_pdf.
	 *
	 * @since 1.0.0
	 * @return string
	 */
	public static function get_last_upsert_error() {
		return self::$last_upsert_error;
	}

	/**
	 * Check if a custom table exists.
	 *
	 * @since 1.0.0
	 * @param string $table_name Table name.
	 * @return bool
	 */
	public static function table_exists( $table_name ) {
		global $wpdb;

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom table existence check; no caching to avoid stale state.
		$exists = $wpdb->get_var(
			$wpdb->prepare(
				'SHOW TABLES LIKE %s',
				$table_name
			)
		);

		return ( $exists === $table_name );
	}

	/**
	 * Get the table name.
	 *
	 * @since 1.0.0
	 * @return string Table name with prefix
	 */
	public static function get_table_name() {
		global $wpdb;
		// Table name is built from trusted $wpdb->prefix and hardcoded suffix.
		// No esc_sql() needed - WordPress.org guidelines prohibit esc_sql() for SQL structure.
		return $wpdb->prefix . 'webequipe_pdf_search_index';
	}

	/**
	 * Per-PDF metadata table (v1.2.0+).
	 *
	 * @since 1.2.0
	 * @return string
	 */
	public static function get_files_table_name() {
		global $wpdb;
		return $wpdb->prefix . 'webequipe_pdf_search_files';
	}

	/**
	 * Per-page content table (v1.2.0+).
	 *
	 * @since 1.2.0
	 * @return string
	 */
	public static function get_pages_table_name() {
		global $wpdb;
		return $wpdb->prefix . 'webequipe_pdf_search_pages';
	}

	/**
	 * Index activity log table (one row per indexing attempt).
	 *
	 * @since 1.2.0
	 * @return string
	 */
	public static function get_activity_table_name() {
		global $wpdb;
		return $wpdb->prefix . 'webequipe_pdf_search_activity';
	}

	/**
	 * Post meta key linking an in-flight index job to an activity log row.
	 */
	const META_ACTIVITY_LOG_ID = '_webequipe_pdf_activity_log_id';

	/**
	 * Create the custom database table.
	 *
	 * Uses dbDelta() for CREATE TABLE (WordPress upgrade API). Table name and
	 * charset come from get_table_name() and $wpdb->get_charset_collate() only (no user input).
	 *
	 * @since 1.0.0
	 * @return void
	 */
	public static function create_table() {
		global $wpdb;

		$table_name      = self::get_table_name();
		$charset_collate = $wpdb->get_charset_collate();

		// Create table without FULLTEXT index. dbDelta() expects this format; values from WP APIs only.
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Trusted: get_table_name(), get_charset_collate().
		$sql = "CREATE TABLE IF NOT EXISTS {$table_name} (
			id bigint(20) NOT NULL AUTO_INCREMENT,
			post_id bigint(20) NOT NULL,
			pdf_title varchar(500) NOT NULL DEFAULT '',
			pdf_content longtext NOT NULL,
			pdf_url varchar(1000) NOT NULL DEFAULT '',
			pdf_filename varchar(255) NOT NULL DEFAULT '',
			pdf_filesize bigint(20) NOT NULL DEFAULT 0,
			page_count int(11) NOT NULL DEFAULT 0,
			indexed_date datetime NOT NULL,
			last_modified datetime NOT NULL,
			PRIMARY KEY  (id),
			UNIQUE KEY post_id (post_id),
			KEY indexed_date (indexed_date)
		) $charset_collate;";

		require_once ABSPATH . 'wp-admin/includes/upgrade.php';
		dbDelta( $sql );

		// Verify table was created (prepare() used for safety).
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- One-time table check during activation.
		$table_exists = $wpdb->get_var(
			$wpdb->prepare(
				'SHOW TABLES LIKE %s',
				$table_name
			)
		);

		if ( $table_exists !== $table_name ) {
			$error = 'Failed to create database table: ' . $table_name;
			if ( function_exists( __NAMESPACE__ . '\webequipe_pdf_search_log' ) ) {
				webequipe_pdf_search_log( $error, 'ERROR' );
			}
			return;
		}

		// Add FULLTEXT index via prepare() for table name (WP 6.2+ %i). Failures ignored; LIKE search still works.
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom table FULLTEXT index; one-time activation only.
		$wpdb->query(
			$wpdb->prepare(
				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.SchemaChange -- FULLTEXT index on custom table during activation.
				'ALTER TABLE %i ADD FULLTEXT KEY pdf_content_search (pdf_title, pdf_content)',
				$table_name
			)
		);

		self::create_files_table();
		self::create_pages_table();
		self::create_activity_table();
	}

	/**
	 * Create the PDF files metadata table.
	 *
	 * @since 1.2.0
	 * @return void
	 */
	public static function create_files_table() {
		global $wpdb;

		$table_name      = self::get_files_table_name();
		$charset_collate = $wpdb->get_charset_collate();

		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Trusted table name and charset.
		$sql = "CREATE TABLE IF NOT EXISTS {$table_name} (
			id bigint(20) NOT NULL AUTO_INCREMENT,
			post_id bigint(20) NOT NULL,
			pdf_title varchar(500) NOT NULL DEFAULT '',
			pdf_url varchar(1000) NOT NULL DEFAULT '',
			pdf_filename varchar(255) NOT NULL DEFAULT '',
			pdf_filesize bigint(20) NOT NULL DEFAULT 0,
			page_count int(11) NOT NULL DEFAULT 0,
			pages_indexed int(11) NOT NULL DEFAULT 0,
			index_status varchar(20) NOT NULL DEFAULT 'pending',
			error_message varchar(500) NOT NULL DEFAULT '',
			indexed_date datetime NOT NULL,
			last_modified datetime NOT NULL,
			PRIMARY KEY  (id),
			UNIQUE KEY post_id (post_id),
			KEY index_status (index_status),
			KEY indexed_date (indexed_date)
		) $charset_collate;";

		require_once ABSPATH . 'wp-admin/includes/upgrade.php';
		dbDelta( $sql );
	}

	/**
	 * Create the PDF pages content table.
	 *
	 * @since 1.2.0
	 * @return void
	 */
	public static function create_pages_table() {
		global $wpdb;

		$table_name      = self::get_pages_table_name();
		$charset_collate = $wpdb->get_charset_collate();

		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Trusted table name and charset.
		$sql = "CREATE TABLE IF NOT EXISTS {$table_name} (
			id bigint(20) NOT NULL AUTO_INCREMENT,
			post_id bigint(20) NOT NULL,
			page_number int(11) NOT NULL,
			page_content longtext NOT NULL,
			content_length int(11) NOT NULL DEFAULT 0,
			last_modified datetime NOT NULL,
			PRIMARY KEY  (id),
			UNIQUE KEY post_page (post_id, page_number),
			KEY post_id (post_id)
		) $charset_collate;";

		require_once ABSPATH . 'wp-admin/includes/upgrade.php';
		dbDelta( $sql );

		if ( ! self::table_exists( $table_name ) ) {
			return;
		}

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.DirectDatabaseQuery.NoCaching -- FULLTEXT on pages table.
		$wpdb->query(
			$wpdb->prepare(
				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.SchemaChange -- FULLTEXT index on custom pages table.
				'ALTER TABLE %i ADD FULLTEXT KEY page_content_search (page_content)',
				$table_name
			)
		);
	}

	/**
	 * Create the index activity log table.
	 *
	 * @since 1.2.0
	 * @return void
	 */
	public static function create_activity_table() {
		global $wpdb;

		$table_name      = self::get_activity_table_name();
		$charset_collate = $wpdb->get_charset_collate();

		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Trusted table name and charset.
		$sql = "CREATE TABLE IF NOT EXISTS {$table_name} (
			id bigint(20) NOT NULL AUTO_INCREMENT,
			post_id bigint(20) NOT NULL,
			pdf_title varchar(500) NOT NULL DEFAULT '',
			pdf_filename varchar(255) NOT NULL DEFAULT '',
			pdf_filesize bigint(20) NOT NULL DEFAULT 0,
			page_count int(11) NOT NULL DEFAULT 0,
			pages_indexed int(11) NOT NULL DEFAULT 0,
			index_status varchar(20) NOT NULL DEFAULT 'processing',
			error_message varchar(500) NOT NULL DEFAULT '',
			started_at datetime NOT NULL,
			completed_at datetime DEFAULT NULL,
			PRIMARY KEY  (id),
			KEY post_id (post_id),
			KEY index_status (index_status),
			KEY started_at (started_at)
		) $charset_collate;";

		require_once ABSPATH . 'wp-admin/includes/upgrade.php';
		dbDelta( $sql );
	}

	/**
	 * Whether the site still relies on the legacy single-table index for any PDFs.
	 *
	 * @since 1.2.0
	 * @return bool
	 */
	public static function uses_legacy_index() {
		$legacy_table = self::get_table_name();
		$files_table  = self::get_files_table_name();

		if ( ! self::table_exists( $legacy_table ) ) {
			return false;
		}

		global $wpdb;

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Legacy row count check.
		$legacy_count = (int) $wpdb->get_var(
			$wpdb->prepare(
				'SELECT COUNT(*) FROM %i',
				$legacy_table
			)
		);

		if ( $legacy_count < 1 ) {
			return false;
		}

		if ( ! self::table_exists( $files_table ) ) {
			return true;
		}

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Completed per-page index count.
		$completed_count = (int) $wpdb->get_var(
			$wpdb->prepare(
				"SELECT COUNT(*) FROM %i WHERE index_status = %s",
				$files_table,
				'completed'
			)
		);

		return $completed_count < 1;
	}

	/**
	 * Whether a PDF uses the per-page index (files table).
	 *
	 * @since 1.2.0
	 * @param int $post_id Attachment post ID.
	 * @return bool
	 */
	public static function post_uses_per_page_index( $post_id ) {
		$file = self::get_indexed_file( $post_id );
		return $file && 'completed' === $file->index_status;
	}

	/**
	 * Set default plugin options
	 *
	 * @since 1.0.0
	 * @return void
	 */
	public static function set_default_options() {
		$defaults = array(
			'enable_indexing'     => true,
			'enable_search'       => true,
			'max_file_size'       => 52428800, // 50MB in bytes
			'excerpt_length'      => 200,
			'show_pdf_icon'       => true,
			'show_file_size'      => true,
			'show_page_count'     => true,
			'show_date'           => false,
			'show_modified_date'  => false,
			'show_author'         => false,
			'show_preview'        => false,
			'show_summary'        => true,
			'enable_debug'        => false,
			'memory_limit'        => '256M',
			'processing_timeout'  => 60,
			'enable_background'   => true,
			'delete_on_uninstall' => false,
			'pdf_icon_type'       => 'svg',
			'css_class_prefix'    => 'webequipe-pdf',
			'batch_size'              => 10,
			'cron_schedule'           => 'hourly',
			'max_page_content_length' => 0,
			'pages_per_batch'         => 5,
			'page_index_threshold'    => 50,
		);

		add_option( 'webequipe_pdf_search_settings', $defaults );
		add_option( 'webequipe_pdf_search_version', WEBEQUIPE_PDF_SEARCH_VERSION );
		add_option( 'webequipe_pdf_search_error_count', 0 );
	}

	/**
	 * Get plugin settings
	 *
	 * @since 1.0.0
	 * @return array Plugin settings
	 */
	public static function get_settings() {
		$defaults = array(
			'enable_indexing'     => true,
			'enable_search'       => true,
			'max_file_size'       => 52428800,
			'excerpt_length'      => 200,
			'show_pdf_icon'       => true,
			'show_file_size'      => true,
			'show_page_count'     => true,
			'show_date'           => false,
			'show_modified_date'  => false,
			'show_author'         => false,
			'show_preview'        => false,
			'show_summary'        => true,
			'enable_debug'        => false,
			'memory_limit'        => '256M',
			'processing_timeout'  => 60,
			'enable_background'   => true,
			'delete_on_uninstall' => false,
			'pdf_icon_type'       => 'svg',
			'css_class_prefix'    => 'webequipe-pdf',
			'batch_size'              => 10,
			'cron_schedule'           => 'hourly',
			'max_page_content_length' => 0,
			'pages_per_batch'         => 5,
			'page_index_threshold'    => 50,
		);

		$settings = wp_parse_args( get_option( 'webequipe_pdf_search_settings', array() ), $defaults );

		// Migrate old 'emoji' icon type to 'svg' for better default display.
		if ( isset( $settings['pdf_icon_type'] ) && 'emoji' === $settings['pdf_icon_type'] ) {
			$settings['pdf_icon_type'] = 'svg';
			update_option( 'webequipe_pdf_search_settings', $settings );
		}

		// Normalize boolean-like settings to real booleans so checks like if ( $settings['show_pdf_icon'] ) work reliably.
		$boolean_fields = array(
			'enable_indexing',
			'enable_search',
			'show_pdf_icon',
			'show_file_size',
			'show_page_count',
			'show_date',
			'show_modified_date',
			'show_author',
			'show_preview',
			'show_summary',
			'enable_debug',
			'enable_background',
			'delete_on_uninstall',
		);

		foreach ( $boolean_fields as $field ) {
			if ( isset( $settings[ $field ] ) ) {
				$settings[ $field ] = (bool) $settings[ $field ];
			}
		}

		return $settings;
	}

	/**
	 * Update plugin settings
	 *
	 * @since 1.0.0
	 * @param array $settings Settings to update.
	 * @return bool True on success, false on failure
	 */
	public static function update_settings( $settings ) {
		return update_option( 'webequipe_pdf_search_settings', $settings );
	}

	/**
	 * Sanitize extracted text for index storage.
	 *
	 * @since 1.2.0
	 * @param string $text       Raw extracted text.
	 * @param int    $max_length Maximum characters (0 = use setting; negative = unlimited).
	 * @return string
	 */
	public static function sanitize_index_text( $text, $max_length = 0 ) {
		$text = wp_strip_all_tags( (string) $text );
		$text = str_replace( "\0", '', $text );

		if ( function_exists( 'mb_convert_encoding' ) ) {
			$text = mb_convert_encoding( $text, 'UTF-8', 'UTF-8' );
		}

		$text = preg_replace( '/[\x{10000}-\x{10FFFF}]/u', '', $text );
		$text = preg_replace( '/\s+/', ' ', $text );
		$text = preg_replace( '/[\x00-\x08\x0B\x0C\x0E-\x1F\x7F]/', '', $text );
		$text = trim( $text );

		if ( function_exists( 'mb_check_encoding' ) && ! mb_check_encoding( $text, 'UTF-8' ) ) {
			$text = mb_convert_encoding( $text, 'UTF-8', 'UTF-8' );
		}

		if ( function_exists( 'iconv' ) ) {
			$text = iconv( 'UTF-8', 'UTF-8//IGNORE//TRANSLIT', $text );
		}

		global $wpdb;
		$charset = $wpdb->get_charset_collate();
		if ( strpos( $charset, 'utf8mb4' ) === false ) {
			$text = preg_replace( '/[\x{10000}-\x{10FFFF}]/u', '', $text );
		}

		if ( $max_length < 0 ) {
			return $text;
		}

		if ( 0 === $max_length ) {
			$settings    = self::get_settings();
			$max_length  = isset( $settings['max_page_content_length'] ) ? (int) $settings['max_page_content_length'] : 0;
		}

		if ( $max_length > 0 && strlen( $text ) > $max_length ) {
			$text = mb_substr( $text, 0, $max_length, 'UTF-8' );
		}

		return $text;
	}

	/**
	 * Get indexed file metadata (per-page index).
	 *
	 * @since 1.2.0
	 * @param int $post_id Attachment post ID.
	 * @return object|null
	 */
	public static function get_indexed_file( $post_id ) {
		global $wpdb;

		$post_id = absint( $post_id );
		if ( ! $post_id ) {
			return null;
		}

		$table_name = self::get_files_table_name();
		if ( ! self::table_exists( $table_name ) ) {
			return null;
		}

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom files table lookup.
		return $wpdb->get_row(
			$wpdb->prepare(
				'SELECT * FROM %i WHERE post_id = %d',
				$table_name,
				$post_id
			),
			OBJECT
		);
	}

	/**
	 * Insert or update file metadata row.
	 *
	 * @since 1.2.0
	 * @param array $data File data.
	 * @return bool
	 */
	public static function upsert_file( $data ) {
		global $wpdb;

		$table_name = self::get_files_table_name();
		if ( ! self::table_exists( $table_name ) ) {
			self::create_files_table();
		}

		$defaults = array(
			'post_id'        => 0,
			'pdf_title'      => '',
			'pdf_url'        => '',
			'pdf_filename'   => '',
			'pdf_filesize'   => 0,
			'page_count'     => 0,
			'pages_indexed'  => 0,
			'index_status'   => 'pending',
			'error_message'  => '',
			'indexed_date'   => current_time( 'mysql' ),
			'last_modified'  => current_time( 'mysql' ),
		);

		if ( empty( $data['post_id'] ) ) {
			return false;
		}

		$post_id  = absint( $data['post_id'] );
		$existing = self::get_indexed_file( $post_id );
		if ( $existing ) {
			$defaults['post_id']       = $post_id;
			$defaults['pdf_title']     = $existing->pdf_title;
			$defaults['pdf_url']       = $existing->pdf_url;
			$defaults['pdf_filename']  = $existing->pdf_filename;
			$defaults['pdf_filesize']  = (int) $existing->pdf_filesize;
			$defaults['page_count']    = (int) $existing->page_count;
			$defaults['pages_indexed'] = (int) $existing->pages_indexed;
			$defaults['index_status']  = $existing->index_status;
			$defaults['error_message'] = $existing->error_message;
			$defaults['indexed_date']  = $existing->indexed_date;
		}

		$data = wp_parse_args( $data, $defaults );

		$data['post_id']       = $post_id;
		$data['pdf_title']     = sanitize_text_field( $data['pdf_title'] );
		$data['pdf_url']       = esc_url_raw( $data['pdf_url'] );
		$data['pdf_filename']  = sanitize_file_name( $data['pdf_filename'] );
		$data['pdf_filesize']  = absint( $data['pdf_filesize'] );
		$data['page_count']    = absint( $data['page_count'] );
		$data['pages_indexed'] = absint( $data['pages_indexed'] );
		$data['index_status']  = sanitize_key( $data['index_status'] );
		$data['error_message'] = sanitize_text_field( $data['error_message'] );

		if ( strlen( $data['pdf_title'] ) > 500 ) {
			$data['pdf_title'] = substr( $data['pdf_title'], 0, 500 );
		}
		if ( strlen( $data['pdf_url'] ) > 1000 ) {
			$data['pdf_url'] = substr( $data['pdf_url'], 0, 1000 );
		}
		if ( strlen( $data['pdf_filename'] ) > 255 ) {
			$data['pdf_filename'] = substr( $data['pdf_filename'], 0, 255 );
		}
		if ( strlen( $data['error_message'] ) > 500 ) {
			$data['error_message'] = substr( $data['error_message'], 0, 500 );
		}

		$allowed_statuses = array( 'pending', 'processing', 'completed', 'failed' );
		if ( ! in_array( $data['index_status'], $allowed_statuses, true ) ) {
			$data['index_status'] = 'pending';
		}

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom files table upsert lookup.
		$existing = (int) $wpdb->get_var(
			$wpdb->prepare(
				'SELECT id FROM %i WHERE post_id = %d',
				$table_name,
				$data['post_id']
			)
		);

		$row = array(
			'pdf_title'     => $data['pdf_title'],
			'pdf_url'       => $data['pdf_url'],
			'pdf_filename'  => $data['pdf_filename'],
			'pdf_filesize'  => $data['pdf_filesize'],
			'page_count'    => $data['page_count'],
			'pages_indexed' => $data['pages_indexed'],
			'index_status'  => $data['index_status'],
			'error_message' => $data['error_message'],
			'last_modified' => $data['last_modified'],
		);

		if ( $existing ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom files table update.
			return false !== $wpdb->update(
				$table_name,
				$row,
				array( 'post_id' => $data['post_id'] ),
				array( '%s', '%s', '%s', '%d', '%d', '%d', '%s', '%s', '%s' ),
				array( '%d' )
			);
		}

		$row['post_id']      = $data['post_id'];
		$row['indexed_date'] = $data['indexed_date'];

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- Custom files table insert.
		return false !== $wpdb->insert(
			$table_name,
			$row,
			array( '%d', '%s', '%s', '%s', '%d', '%d', '%d', '%s', '%s', '%s', '%s' )
		);
	}

	/**
	 * Insert or update a single page row.
	 *
	 * @since 1.2.0
	 * @param int    $post_id     Attachment post ID.
	 * @param int    $page_number 1-based page number.
	 * @param string $content     Page text content.
	 * @return bool
	 */
	public static function upsert_page( $post_id, $page_number, $content ) {
		global $wpdb;

		$post_id     = absint( $post_id );
		$page_number = absint( $page_number );
		if ( ! $post_id || ! $page_number ) {
			return false;
		}

		$table_name = self::get_pages_table_name();
		if ( ! self::table_exists( $table_name ) ) {
			self::create_pages_table();
		}

		$content = self::sanitize_index_text( $content );
		$length  = strlen( $content );
		$now     = current_time( 'mysql' );

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom pages table upsert lookup.
		$existing = (int) $wpdb->get_var(
			$wpdb->prepare(
				'SELECT id FROM %i WHERE post_id = %d AND page_number = %d',
				$table_name,
				$post_id,
				$page_number
			)
		);

		if ( $existing ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom pages table update.
			return false !== $wpdb->update(
				$table_name,
				array(
					'page_content'   => $content,
					'content_length' => $length,
					'last_modified'  => $now,
				),
				array(
					'post_id'     => $post_id,
					'page_number' => $page_number,
				),
				array( '%s', '%d', '%s' ),
				array( '%d', '%d' )
			);
		}

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- Custom pages table insert.
		return false !== $wpdb->insert(
			$table_name,
			array(
				'post_id'        => $post_id,
				'page_number'    => $page_number,
				'page_content'   => $content,
				'content_length' => $length,
				'last_modified'  => $now,
			),
			array( '%d', '%d', '%s', '%d', '%s' )
		);
	}

	/**
	 * Delete all page rows for a PDF.
	 *
	 * @since 1.2.0
	 * @param int $post_id Attachment post ID.
	 * @return void
	 */
	public static function delete_pages_for_post( $post_id ) {
		global $wpdb;

		$post_id = absint( $post_id );
		if ( ! $post_id ) {
			return;
		}

		$table_name = self::get_pages_table_name();
		if ( ! self::table_exists( $table_name ) ) {
			return;
		}

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom pages table delete.
		$wpdb->delete(
			$table_name,
			array( 'post_id' => $post_id ),
			array( '%d' )
		);
	}

	/**
	 * Delete legacy single-table row only (keep per-page index).
	 *
	 * @since 1.2.0
	 * @param int $post_id Attachment post ID.
	 * @return void
	 */
	public static function delete_legacy_pdf( $post_id ) {
		global $wpdb;

		$post_id = absint( $post_id );
		if ( ! $post_id ) {
			return;
		}

		$table_name = self::get_table_name();
		if ( ! self::table_exists( $table_name ) ) {
			return;
		}

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Legacy index row delete on re-index.
		$wpdb->delete(
			$table_name,
			array( 'post_id' => $post_id ),
			array( '%d' )
		);
	}

	/**
	 * Delete file metadata and pages for a PDF.
	 *
	 * @since 1.2.0
	 * @param int $post_id Attachment post ID.
	 * @return void
	 */
	public static function delete_file_index( $post_id ) {
		global $wpdb;

		$post_id = absint( $post_id );
		if ( ! $post_id ) {
			return;
		}

		self::delete_pages_for_post( $post_id );

		$table_name = self::get_files_table_name();
		if ( self::table_exists( $table_name ) ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom files table delete.
			$wpdb->delete(
				$table_name,
				array( 'post_id' => $post_id ),
				array( '%d' )
			);
		}
	}

	/**
	 * Build a legacy-shaped index object from per-page storage.
	 *
	 * @since 1.2.0
	 * @param object $file File row from files table.
	 * @return object|null
	 */
	public static function file_row_to_index_object( $file ) {
		if ( ! $file ) {
			return null;
		}

		$content = self::get_concatenated_page_content( (int) $file->post_id, 50000 );

		$obj = (object) array(
			'id'            => isset( $file->id ) ? (int) $file->id : 0,
			'post_id'       => (int) $file->post_id,
			'pdf_title'     => $file->pdf_title,
			'pdf_content'   => $content,
			'pdf_url'       => $file->pdf_url,
			'pdf_filename'  => $file->pdf_filename,
			'pdf_filesize'  => (int) $file->pdf_filesize,
			'page_count'    => (int) $file->page_count,
			'indexed_date'  => $file->indexed_date,
			'last_modified' => $file->last_modified,
			'pages_indexed' => isset( $file->pages_indexed ) ? (int) $file->pages_indexed : 0,
			'index_status'  => isset( $file->index_status ) ? $file->index_status : '',
		);

		return $obj;
	}

	/**
	 * Concatenate indexed page text for a PDF (preview / legacy compat).
	 *
	 * @since 1.2.0
	 * @param int $post_id    Attachment post ID.
	 * @param int $max_length Max characters (0 = unlimited).
	 * @return string
	 */
	public static function get_concatenated_page_content( $post_id, $max_length = 0 ) {
		global $wpdb;

		$post_id = absint( $post_id );
		if ( ! $post_id ) {
			return '';
		}

		$table_name = self::get_pages_table_name();
		if ( ! self::table_exists( $table_name ) ) {
			return '';
		}

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom pages table read.
		$pages = $wpdb->get_col(
			$wpdb->prepare(
				'SELECT page_content FROM %i WHERE post_id = %d ORDER BY page_number ASC',
				$table_name,
				$post_id
			)
		);

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

		$content = implode( ' ', array_map( 'trim', $pages ) );
		if ( $max_length > 0 && strlen( $content ) > $max_length ) {
			$content = mb_substr( $content, 0, $max_length, 'UTF-8' );
		}

		return $content;
	}

	/**
	 * Whether any indexed page has non-whitespace text.
	 *
	 * @since 1.2.0
	 * @param int $post_id Attachment post ID.
	 * @return bool
	 */
	public static function has_searchable_content( $post_id ) {
		global $wpdb;

		$post_id = absint( $post_id );
		if ( ! $post_id ) {
			return false;
		}

		$table_name = self::get_pages_table_name();
		if ( ! self::table_exists( $table_name ) ) {
			return false;
		}

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom pages table read.
		$pages = $wpdb->get_col(
			$wpdb->prepare(
				'SELECT page_content FROM %i WHERE post_id = %d',
				$table_name,
				$post_id
			)
		);

		if ( empty( $pages ) ) {
			return false;
		}

		foreach ( $pages as $page_content ) {
			if ( '' !== trim( (string) $page_content ) ) {
				return true;
			}
		}

		return false;
	}

	/**
	 * Default error message when no text can be extracted (image-only PDF).
	 *
	 * @since 1.2.0
	 * @return string
	 */
	public static function get_image_only_error_message() {
		return __(
			'This PDF could not be indexed because it does not contain extractable text. Please upload a text-based PDF. Image-based PDFs cannot be parsed for search.',
			'webequipe-pdf-search'
		);
	}

	/**
	 * Untranslated (English source) image-only error message.
	 *
	 * Stored in the database as the canonical value so it can be re-translated to
	 * the active locale on display. Must match the literal in
	 * {@see self::get_image_only_error_message()} so it resolves through gettext.
	 *
	 * @since 2.0.1
	 * @return string
	 */
	public static function get_image_only_error_message_source() {
		return 'This PDF could not be indexed because it does not contain extractable text. Please upload a text-based PDF. Image-based PDFs cannot be parsed for search.';
	}

	/**
	 * Replace legacy image-only error copy (pre-1.2.0 OCR wording) with current message.
	 *
	 * @since 1.2.0
	 * @param string $message Stored error message.
	 * @return string
	 */
	/**
	 * Whether a stored error string is the image-only failure (any wording/locale).
	 *
	 * Detects the current English source, the active-locale translation, and the
	 * legacy pre-1.2.0 OCR wording so the message can be re-translated on display
	 * instead of returning the language-frozen copy persisted at index time.
	 *
	 * @since 2.0.1
	 * @param string $message Stored error message.
	 * @return bool
	 */
	private static function is_image_only_error_text( $message ) {
		$lower = strtolower( trim( (string) $message ) );
		if ( '' === $lower ) {
			return false;
		}

		// Current copy in the active locale (handles same-locale stored text).
		if ( $lower === strtolower( self::get_image_only_error_message() ) ) {
			return true;
		}

		// English source string, regardless of the active locale.
		if ( $lower === strtolower( self::get_image_only_error_message_source() ) ) {
			return true;
		}

		// Distinctive substrings: current + legacy English wording and the
		// bundled translations (fr_FR, de_DE, nl_NL, sv_SE) so copies frozen in
		// a non-English locale are still recognised and re-translated on display.
		$markers = array(
			'does not contain extractable text',
			'image-based pdfs cannot be parsed',
			'no searchable text could be extracted',
			'appears to be image-only',
			'image-only',
			'use ocr',
			'ne contient pas de texte extractible',
			'keinen extrahierbaren text enthält',
			'geen extraheerbare tekst bevat',
			'innehåller extraherbar text',
		);
		foreach ( $markers as $marker ) {
			if ( false !== strpos( $lower, $marker ) ) {
				return true;
			}
		}

		return false;
	}

	/**
	 * Re-translate a stored error message into the active locale for display.
	 *
	 * Stored error text may have been frozen in a different locale at index time.
	 * Known canonical messages (image-only) are re-rendered live; other messages
	 * are returned unchanged.
	 *
	 * @since 2.0.1
	 * @param string $message Stored error message.
	 * @return string
	 */
	public static function localize_error_message( $message ) {
		$message = (string) $message;
		if ( '' === $message ) {
			return '';
		}

		if ( self::is_image_only_error_text( $message ) ) {
			return self::get_image_only_error_message();
		}

		return $message;
	}

	private static function normalize_legacy_image_only_error( $message ) {
		$message = trim( (string) $message );
		if ( '' === $message ) {
			return '';
		}

		$lower    = strtolower( $message );
		$markers  = array(
			'no searchable text could be extracted',
			'appears to be image-only',
			'image-only',
			'use ocr',
		);
		$canonical = strtolower( self::get_image_only_error_message() );

		if ( $lower === $canonical ) {
			return $message;
		}

		foreach ( $markers as $marker ) {
			if ( false !== strpos( $lower, $marker ) ) {
				return self::get_image_only_error_message();
			}
		}

		return $message;
	}

	/**
	 * Persist normalized error text to post meta and files table when legacy copy is upgraded.
	 *
	 * @since 1.2.0
	 * @param int    $post_id Attachment post ID.
	 * @param string $message Normalized error message.
	 * @return void
	 */
	private static function sync_index_error_message( $post_id, $message ) {
		$post_id = absint( $post_id );
		$message = sanitize_text_field( $message );
		if ( ! $post_id || '' === $message ) {
			return;
		}

		$stored_meta = get_post_meta( $post_id, '_webequipe_pdf_index_error', true );
		if ( $stored_meta !== $message ) {
			self::set_index_error( $post_id, $message );
		}

		$file = self::get_indexed_file( $post_id );
		if ( $file && 'failed' === $file->index_status && $file->error_message !== $message ) {
			self::upsert_file(
				array(
					'post_id'       => $post_id,
					'index_status'  => 'failed',
					'error_message' => $message,
				)
			);
		}
	}

	/**
	 * Per-page text extraction stats for an indexed PDF.
	 *
	 * @since 1.2.0
	 * @param int $post_id Attachment post ID.
	 * @return array{total_pages:int,pages_with_text:int,pages_without_text:int}
	 */
	public static function get_page_text_stats( $post_id ) {
		global $wpdb;

		$post_id = absint( $post_id );
		$stats   = array(
			'total_pages'        => 0,
			'pages_with_text'    => 0,
			'pages_without_text' => 0,
		);

		if ( ! $post_id ) {
			return $stats;
		}

		$table_name = self::get_pages_table_name();
		if ( ! self::table_exists( $table_name ) ) {
			return $stats;
		}

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom pages table read.
		$pages = $wpdb->get_col(
			$wpdb->prepare(
				'SELECT page_content FROM %i WHERE post_id = %d ORDER BY page_number ASC',
				$table_name,
				$post_id
			)
		);

		if ( empty( $pages ) ) {
			return $stats;
		}

		$stats['total_pages'] = count( $pages );
		foreach ( $pages as $page_content ) {
			if ( '' !== trim( (string) $page_content ) ) {
				++$stats['pages_with_text'];
			} else {
				++$stats['pages_without_text'];
			}
		}

		return $stats;
	}

	/**
	 * Repair stale rows: completed status with no searchable content.
	 *
	 * @since 1.2.0
	 * @param int $post_id Attachment post ID.
	 * @return void
	 */
	public static function repair_stale_index_state( $post_id ) {
		$post_id = absint( $post_id );
		if ( ! $post_id ) {
			return;
		}

		$file = self::get_indexed_file( $post_id );
		if ( ! $file || 'completed' !== $file->index_status ) {
			return;
		}

		if ( self::has_searchable_content( $post_id ) ) {
			return;
		}

		$message = self::get_image_only_error_message_source();
		self::delete_pages_for_post( $post_id );
		self::set_index_error( $post_id, $message );
		self::upsert_file(
			array(
				'post_id'       => $post_id,
				'index_status'  => 'failed',
				'error_message' => $message,
				'last_modified' => current_time( 'mysql' ),
			)
		);
		update_post_meta( $post_id, '_webequipe_pdf_indexing_status', 'failed' );
	}

	/**
	 * Resolved index error message for admin display.
	 *
	 * @since 1.2.0
	 * @param int  $post_id       Attachment post ID.
	 * @param bool $repair_stale  Whether to repair stale completed rows first.
	 * @return string Empty when no error.
	 */
	public static function get_index_error_message( $post_id, $repair_stale = true ) {
		$post_id = absint( $post_id );
		if ( ! $post_id ) {
			return '';
		}

		if ( $repair_stale ) {
			self::repair_stale_index_state( $post_id );
		}

		$meta_error = get_post_meta( $post_id, '_webequipe_pdf_index_error', true );
		if ( is_string( $meta_error ) && '' !== $meta_error ) {
			// Render the image-only failure live in the current locale rather than the
			// language-frozen copy stored at index time (keeps the DB value canonical).
			if ( self::is_image_only_error_text( $meta_error ) ) {
				return self::get_image_only_error_message();
			}
			$normalized = self::normalize_legacy_image_only_error( $meta_error );
			if ( $normalized !== $meta_error ) {
				self::sync_index_error_message( $post_id, $normalized );
			}
			return $normalized;
		}

		$file = self::get_indexed_file( $post_id );
		if ( $file && 'failed' === $file->index_status && ! empty( $file->error_message ) ) {
			$file_error = (string) $file->error_message;
			if ( self::is_image_only_error_text( $file_error ) ) {
				return self::get_image_only_error_message();
			}
			$normalized   = self::normalize_legacy_image_only_error( $file_error );
			self::sync_index_error_message( $post_id, $normalized );
			return $normalized;
		}

		if ( $file && 'completed' === $file->index_status && ! self::has_searchable_content( $post_id ) ) {
			return self::get_image_only_error_message();
		}

		if ( 'failed' === get_post_meta( $post_id, '_webequipe_pdf_indexing_status', true ) ) {
			return __( 'Indexing failed. Try re-indexing or check the error log.', 'webequipe-pdf-search' );
		}

		return '';
	}

	/**
	 * Partial-index warning message (mixed text + image pages).
	 *
	 * @since 1.2.0
	 * @param int $post_id Attachment post ID.
	 * @return string Empty when no warning.
	 */
	public static function get_index_warning_message( $post_id ) {
		$post_id = absint( $post_id );
		if ( ! $post_id ) {
			return '';
		}

		$warning = get_post_meta( $post_id, '_webequipe_pdf_index_warning', true );
		if ( ! is_string( $warning ) || '' === $warning ) {
			return '';
		}

		$lower = strtolower( $warning );
		if ( false !== strpos( $lower, 'use ocr' ) || false !== strpos( $lower, 'had no extractable text (image-only)' ) ) {
			$stats = self::get_page_text_stats( $post_id );
			if ( $stats['pages_without_text'] > 0 && $stats['total_pages'] > 0 ) {
				$warning = sprintf(
					/* translators: 1: number of pages without text, 2: total page count */
					__( 'Indexed with partial content: %1$d of %2$d pages could not be parsed. Image-based pages are not searchable. Search covers text-based pages only.', 'webequipe-pdf-search' ),
					$stats['pages_without_text'],
					$stats['total_pages']
				);
				update_post_meta( $post_id, '_webequipe_pdf_index_warning', sanitize_text_field( $warning ) );
			}
		}

		return $warning;
	}

	/**
	 * Store partial-index warning message.
	 *
	 * @since 1.2.0
	 * @param int    $post_id Attachment post ID.
	 * @param string $message Warning message.
	 * @return void
	 */
	public static function set_index_warning( $post_id, $message ) {
		$post_id = absint( $post_id );
		if ( ! $post_id || '' === $message ) {
			return;
		}

		update_post_meta( $post_id, '_webequipe_pdf_index_warning', sanitize_text_field( $message ) );
	}

	/**
	 * Remove partial-index warning message.
	 *
	 * @since 1.2.0
	 * @param int $post_id Attachment post ID.
	 * @return void
	 */
	public static function clear_index_warning( $post_id ) {
		$post_id = absint( $post_id );
		if ( ! $post_id ) {
			return;
		}

		delete_post_meta( $post_id, '_webequipe_pdf_index_warning' );
	}

	/**
	 * Admin display status for a PDF attachment.
	 *
	 * @since 1.2.0
	 * @param int $post_id Attachment post ID.
	 * @return string error|excluded|indexed|processing|scheduled|not_indexed
	 */
	public static function get_pdf_display_status( $post_id ) {
		$post_id = absint( $post_id );
		if ( ! $post_id ) {
			return 'not_indexed';
		}

		if ( get_post_meta( $post_id, '_webequipe_pdf_search_excluded', true ) ) {
			return 'excluded';
		}

		$file       = self::get_indexed_file( $post_id );
		$job_status = get_post_meta( $post_id, '_webequipe_pdf_indexing_status', true );

		$in_flight = in_array( $job_status, array( 'processing', 'scheduled' ), true )
			|| ( $file && in_array( $file->index_status, array( 'pending', 'processing' ), true ) );

		if ( $in_flight ) {
			if ( self::is_indexing_job_stalled( $post_id ) ) {
				return 'stalled';
			}
			return 'scheduled' === $job_status ? 'scheduled' : 'processing';
		}

		if ( '' !== self::get_index_error_message( $post_id ) ) {
			return 'error';
		}

		if ( self::get_indexed_pdf( $post_id ) ) {
			return 'indexed';
		}

		return 'not_indexed';
	}

	/**
	 * Whether an attachment has an indexing job that is not finished.
	 *
	 * @since 1.2.2
	 * @param int $post_id Attachment post ID.
	 * @return bool
	 */
	public static function indexing_job_in_flight( $post_id ) {
		$post_id = absint( $post_id );
		if ( ! $post_id ) {
			return false;
		}

		$file       = self::get_indexed_file( $post_id );
		$job_status = get_post_meta( $post_id, '_webequipe_pdf_indexing_status', true );

		return in_array( $job_status, array( 'processing', 'scheduled' ), true )
			|| ( $file && in_array( $file->index_status, array( 'pending', 'processing' ), true ) );
	}

	/**
	 * True when indexing is in progress but no WP-Cron event is queued to continue.
	 *
	 * @since 1.2.2
	 * @param int $post_id Attachment post ID.
	 * @return bool
	 */
	public static function is_indexing_job_stalled( $post_id ) {
		if ( ! self::indexing_job_in_flight( $post_id ) ) {
			return false;
		}

		return ! wp_next_scheduled( 'webequipe_pdf_search_index_pages', array( absint( $post_id ) ) );
	}

	/**
	 * Attachment IDs with in-flight indexing and no scheduled cron continuation.
	 *
	 * @since 1.2.2
	 * @param int $limit Max IDs to return.
	 * @return int[]
	 */
	public static function get_stalled_indexing_post_ids( $limit = 10 ) {
		global $wpdb;

		$limit = max( 1, min( 50, (int) $limit ) );

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Find in-flight indexing meta.
		$post_ids = $wpdb->get_col(
			$wpdb->prepare(
				"SELECT post_id FROM {$wpdb->postmeta} WHERE meta_key = %s AND meta_value IN ('processing', 'scheduled') LIMIT %d",
				'_webequipe_pdf_indexing_status',
				100
			)
		);

		$stalled = array();
		foreach ( (array) $post_ids as $post_id ) {
			$post_id = absint( $post_id );
			if ( ! $post_id || ! self::is_indexing_job_stalled( $post_id ) ) {
				continue;
			}
			$stalled[] = $post_id;
			if ( count( $stalled ) >= $limit ) {
				break;
			}
		}

		return $stalled;
	}

	/**
	 * All PDF attachment IDs in the media library (ascending ID).
	 *
	 * @since 1.2.2
	 * @return int[]
	 */
	public static function get_pdf_attachment_ids() {
		$attachments = get_posts(
			array(
				'post_type'              => 'attachment',
				'post_mime_type'         => 'application/pdf',
				'post_status'            => 'inherit',
				'fields'                 => 'ids',
				'posts_per_page'         => -1,
				'orderby'                => 'ID',
				'order'                  => 'ASC',
				'no_found_rows'          => true,
				'update_post_meta_cache' => false,
				'update_post_term_cache' => false,
			)
		);

		return is_array( $attachments ) ? array_map( 'absint', $attachments ) : array();
	}

	/**
	 * PDF attachment IDs that Re-index All should process (not excluded).
	 *
	 * @since 1.2.2
	 * @return int[]
	 */
	public static function get_reindexable_pdf_attachment_ids( $pending_only = false ) {
		$ids = array();

		foreach ( self::get_pdf_attachment_ids() as $post_id ) {
			if ( ! $post_id || get_post_meta( $post_id, '_webequipe_pdf_search_excluded', true ) ) {
				continue;
			}

			if ( $pending_only ) {
				// Only PDFs never started — do not restart ones already queued (Scheduled/Stalled).
				if ( 'not_indexed' !== self::get_pdf_display_status( $post_id ) ) {
					continue;
				}
			}

			$ids[] = $post_id;
		}

		return $ids;
	}

	/**
	 * Count reindexable PDFs that are not fully indexed yet (for Re-index All progress).
	 *
	 * @since 1.2.2
	 * @return int
	 */
	public static function count_reindex_pending_pdfs() {
		$pending = 0;

		foreach ( self::get_reindexable_pdf_attachment_ids() as $post_id ) {
			$status = self::get_pdf_display_status( $post_id );
			if ( in_array( $status, array( 'not_indexed', 'processing', 'scheduled', 'stalled' ), true ) ) {
				++$pending;
			}
		}

		return $pending;
	}

	/**
	 * Count PDFs whose live status is processing, scheduled, or stalled (matches Manage PDFs).
	 *
	 * @since 1.2.2
	 * @return int
	 */
	public static function count_in_flight_indexing_jobs() {
		global $wpdb;

		$candidate_ids = array();

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Candidates for in-flight indexing.
		$from_meta = $wpdb->get_col(
			$wpdb->prepare(
				"SELECT DISTINCT post_id FROM {$wpdb->postmeta} WHERE meta_key = %s AND meta_value IN ('processing', 'scheduled')",
				'_webequipe_pdf_indexing_status'
			)
		);

		$files_table = self::get_files_table_name();
		if ( self::table_exists( $files_table ) ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Files table in-flight rows.
			$from_files = $wpdb->get_col(
				$wpdb->prepare(
					'SELECT DISTINCT post_id FROM %i WHERE index_status IN (%s, %s)',
					$files_table,
					'pending',
					'processing'
				)
			);
			$candidate_ids = array_merge( (array) $from_meta, (array) $from_files );
		} else {
			$candidate_ids = (array) $from_meta;
		}

		$candidate_ids = array_unique( array_map( 'absint', $candidate_ids ) );
		$count         = 0;

		foreach ( $candidate_ids as $post_id ) {
			if ( ! $post_id ) {
				continue;
			}
			$live = self::get_pdf_display_status( $post_id );
			if ( in_array( $live, array( 'processing', 'scheduled', 'stalled' ), true ) ) {
				++$count;
			}
		}

		return $count;
	}

	/**
	 * Close open activity log rows when the document is no longer indexing.
	 *
	 * @since 1.2.2
	 * @param int $post_id Attachment post ID.
	 * @return void
	 */
	public static function sync_open_activity_log_with_live_state( $post_id ) {
		$post_id = absint( $post_id );
		if ( ! $post_id ) {
			return;
		}

		$log_id = (int) get_post_meta( $post_id, self::META_ACTIVITY_LOG_ID, true );
		if ( ! $log_id ) {
			return;
		}

		$live = self::get_pdf_display_status( $post_id );
		if ( in_array( $live, array( 'processing', 'scheduled', 'stalled' ), true ) ) {
			return;
		}

		if ( 'indexed' === $live ) {
			$file = self::get_indexed_file( $post_id );
			self::log_activity_complete( $post_id, $file ? (int) $file->page_count : 0 );
			return;
		}

		if ( 'error' === $live ) {
			self::log_activity_fail( $post_id, self::get_index_error_message( $post_id ) );
			return;
		}

		self::update_activity_terminal_status( $post_id, 'cancelled', '', 0 );
	}

	/**
	 * Status label for Index Activity rows — matches Manage PDFs for the current run.
	 *
	 * @since 1.2.2
	 * @param int    $post_id       Attachment post ID.
	 * @param int    $activity_id   Activity log row ID.
	 * @param string $log_status    Raw status stored on the activity row.
	 * @return string processing|scheduled|stalled|completed|failed|cancelled|pending
	 */
	public static function get_activity_row_display_status( $post_id, $activity_id, $log_status ) {
		$post_id     = absint( $post_id );
		$activity_id = absint( $activity_id );
		$log_status  = sanitize_key( $log_status );
		$active_id   = (int) get_post_meta( $post_id, self::META_ACTIVITY_LOG_ID, true );

		if ( $activity_id && $activity_id === $active_id ) {
			$live = self::get_pdf_display_status( $post_id );

			if ( 'indexed' === $live ) {
				return 'completed';
			}
			if ( 'error' === $live ) {
				return 'failed';
			}
			if ( in_array( $live, array( 'not_indexed', 'excluded' ), true ) ) {
				return 'cancelled';
			}
			if ( in_array( $live, array( 'processing', 'scheduled', 'stalled' ), true ) ) {
				return $live;
			}
		}

		if ( 'pending' === $log_status ) {
			return 'processing';
		}

		return $log_status;
	}

	/**
	 * Whether an activity display status counts as in progress (Manage PDFs + Activity).
	 *
	 * @since 1.2.2
	 * @param string $display_status Display status slug.
	 * @return bool
	 */
	public static function is_in_progress_display_status( $display_status ) {
		return in_array(
			sanitize_key( $display_status ),
			array( 'processing', 'scheduled', 'stalled', 'pending' ),
			true
		);
	}

	/**
	 * Sync stale open activity rows before building the activity list.
	 *
	 * @since 1.2.2
	 * @return void
	 */
	public static function sync_stale_open_activity_logs() {
		global $wpdb;

		$table_name = self::get_activity_table_name();
		if ( ! self::table_exists( $table_name ) ) {
			return;
		}

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Posts with open activity rows.
		$post_ids = $wpdb->get_col(
			$wpdb->prepare(
				"SELECT DISTINCT post_id FROM %i WHERE index_status IN ('processing', 'pending')",
				$table_name
			)
		);

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Posts with active log pointer meta.
		$meta_ids = $wpdb->get_col(
			$wpdb->prepare(
				"SELECT DISTINCT post_id FROM {$wpdb->postmeta} WHERE meta_key = %s",
				self::META_ACTIVITY_LOG_ID
			)
		);

		$post_ids = array_unique( array_map( 'absint', array_merge( (array) $post_ids, (array) $meta_ids ) ) );

		foreach ( $post_ids as $post_id ) {
			if ( $post_id ) {
				self::sync_open_activity_log_with_live_state( $post_id );
			}
		}
	}

	/**
	 * Cancel an in-flight per-page indexing job for an attachment.
	 *
	 * @since 1.2.2
	 * @param int $post_id Attachment post ID.
	 * @return bool True when a job was cancelled, false if nothing was in progress.
	 */
	public static function cancel_indexing_job( $post_id ) {
		global $wpdb;

		$post_id = absint( $post_id );
		if ( ! $post_id ) {
			return false;
		}

		$file       = self::get_indexed_file( $post_id );
		$job_status = get_post_meta( $post_id, '_webequipe_pdf_indexing_status', true );

		$in_flight = in_array( $job_status, array( 'processing', 'scheduled' ), true )
			|| ( $file && in_array( $file->index_status, array( 'pending', 'processing' ), true ) );

		if ( ! $in_flight ) {
			return false;
		}

		wp_clear_scheduled_hook( 'webequipe_pdf_search_index_pages', array( $post_id ) );

		$table_name = self::get_activity_table_name();
		if ( self::table_exists( $table_name ) ) {
			$now = current_time( 'mysql' );
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Cancel in-flight activity rows.
			$wpdb->query(
				$wpdb->prepare(
					"UPDATE %i SET index_status = %s, completed_at = %s WHERE post_id = %d AND index_status IN ('processing', 'pending')",
					$table_name,
					'cancelled',
					$now,
					$post_id
				)
			);
		}

		delete_post_meta( $post_id, '_webequipe_pdf_indexing_status' );
		delete_post_meta( $post_id, self::META_ACTIVITY_LOG_ID );
		delete_post_meta( $post_id, '_webequipe_pdf_index_cursor' );
		delete_post_meta( $post_id, '_webequipe_pdf_index_page_count' );

		self::delete_file_index( $post_id );
		self::delete_legacy_pdf( $post_id );

		return true;
	}

	/**
	 * Get indexed PDF data by post ID
	 *
	 * @since 1.0.0
	 * @param int $post_id Attachment post ID.
	 * @return object|null PDF data object or null if not found
	 */
	public static function get_indexed_pdf( $post_id ) {
		global $wpdb;

		$post_id = absint( $post_id );
		if ( empty( $post_id ) ) {
			return null;
		}

		$file = self::get_indexed_file( $post_id );
		if ( $file && 'completed' === $file->index_status ) {
			if ( self::has_searchable_content( $post_id ) ) {
				return self::file_row_to_index_object( $file );
			}
			return null;
		}

		if ( $file && in_array( $file->index_status, array( 'pending', 'processing' ), true ) ) {
			return self::file_row_to_index_object( $file );
		}

		$table_name = self::get_table_name();

		if ( ! self::table_exists( $table_name ) ) {
			return null;
		}

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Legacy index table lookup.
		$result = $wpdb->get_row(
			$wpdb->prepare(
				'SELECT * FROM %i WHERE post_id = %d',
				$table_name,
				$post_id
			),
			OBJECT
		);

		if ( ! empty( $wpdb->last_error ) && function_exists( __NAMESPACE__ . '\webequipe_pdf_search_log' ) ) {
			webequipe_pdf_search_log( 'Database error in get_indexed_pdf: ' . $wpdb->last_error . ' | Post ID: ' . $post_id, 'ERROR' );
		}

		return $result;
	}

	/**
	 * Insert or update PDF index entry
	 *
	 * @since 1.0.0
	 * @param array $data PDF data to store.
	 * @return bool True on success, false on failure
	 */
	public static function upsert_pdf( $data ) {
		global $wpdb;

		$table_name = self::get_table_name();

		// Create table if it does not exist (e.g. activation was skipped or failed).
		if ( ! self::table_exists( $table_name ) ) {
			self::create_table();
			if ( ! self::table_exists( $table_name ) ) {
				self::$last_upsert_error = 'Database table does not exist: ' . $table_name . '. Please deactivate and reactivate the plugin.';
				if ( function_exists( __NAMESPACE__ . '\webequipe_pdf_search_log' ) ) {
					webequipe_pdf_search_log( self::$last_upsert_error, 'ERROR' );
				}
				return false;
			}
		}

		$defaults = array(
			'post_id'       => 0,
			'pdf_title'     => '',
			'pdf_content'   => '',
			'pdf_url'       => '',
			'pdf_filename'  => '',
			'pdf_filesize'  => 0,
			'page_count'    => 0,
			'indexed_date'  => current_time( 'mysql' ),
			'last_modified' => current_time( 'mysql' ),
		);

		$data = wp_parse_args( $data, $defaults );

		// Validate post_id.
		if ( empty( $data['post_id'] ) || $data['post_id'] <= 0 ) {
			self::$last_upsert_error = 'Invalid post_id.';
			if ( function_exists( __NAMESPACE__ . '\webequipe_pdf_search_log' ) ) {
				webequipe_pdf_search_log( 'Invalid post_id: ' . $data['post_id'], 'ERROR' );
			}
			return false;
		}

		// Sanitize data.
		$data['post_id']   = absint( $data['post_id'] );
		$data['pdf_title'] = sanitize_text_field( $data['pdf_title'] );

		$data['pdf_content'] = self::sanitize_index_text( $data['pdf_content'], 50000 );

		$data['pdf_url']      = esc_url_raw( $data['pdf_url'] );
		$data['pdf_filename'] = sanitize_file_name( $data['pdf_filename'] );
		$data['pdf_filesize'] = absint( $data['pdf_filesize'] );
		$data['page_count']   = absint( $data['page_count'] );

		// Limit field lengths to match database schema.
		if ( strlen( $data['pdf_title'] ) > 500 ) {
			$data['pdf_title'] = substr( $data['pdf_title'], 0, 500 );
		}
		if ( strlen( $data['pdf_url'] ) > 1000 ) {
			$data['pdf_url'] = substr( $data['pdf_url'], 0, 1000 );
		}
		if ( strlen( $data['pdf_filename'] ) > 255 ) {
			$data['pdf_filename'] = substr( $data['pdf_filename'], 0, 255 );
		}

		// Check if entry exists.
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom index table; no caching to ensure correct upsert.
		$existing = $wpdb->get_var(
			$wpdb->prepare(
				'SELECT id FROM %i WHERE post_id = %d',
				$table_name,
				$data['post_id']
			)
		);
		$existing = (int) $existing;

		// Clear any previous database errors.
		$wpdb->last_error = '';
		$wpdb->last_query = '';

		if ( $existing ) {
			// Update existing entry.
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom index table update; no caching to ensure fresh index.
			$result = $wpdb->update(
				$table_name,
				array(
					'pdf_title'     => $data['pdf_title'],
					'pdf_content'   => $data['pdf_content'],
					'pdf_url'       => $data['pdf_url'],
					'pdf_filename'  => $data['pdf_filename'],
					'pdf_filesize'  => $data['pdf_filesize'],
					'page_count'    => $data['page_count'],
					'last_modified' => $data['last_modified'],
				),
				array( 'post_id' => $data['post_id'] ),
				array( '%s', '%s', '%s', '%s', '%d', '%d', '%s' ),
				array( '%d' )
			);

			// Update returns number of rows affected (0 if no change, false on error).
			if ( false === $result ) {
				self::$last_upsert_error = ! empty( $wpdb->last_error ) ? $wpdb->last_error : 'Update operation failed';
				if ( function_exists( __NAMESPACE__ . '\webequipe_pdf_search_log' ) ) {
					webequipe_pdf_search_log( 'Database update failed for post_id ' . $data['post_id'] . ': ' . self::$last_upsert_error . ' | Query: ' . $wpdb->last_query, 'ERROR' );
				}
				return false;
			}
		} else {
			// Insert new entry - ensure only valid columns are included.
			$insert_data = array(
				'post_id'       => $data['post_id'],
				'pdf_title'     => $data['pdf_title'],
				'pdf_content'   => $data['pdf_content'],
				'pdf_url'       => $data['pdf_url'],
				'pdf_filename'  => $data['pdf_filename'],
				'pdf_filesize'  => $data['pdf_filesize'],
				'page_count'    => $data['page_count'],
				'indexed_date'  => $data['indexed_date'],
				'last_modified' => $data['last_modified'],
			);

			// Validate data before insert.
			if ( empty( $insert_data['indexed_date'] ) || empty( $insert_data['last_modified'] ) ) {
				self::$last_upsert_error = 'Invalid date values for index entry.';
				if ( function_exists( __NAMESPACE__ . '\webequipe_pdf_search_log' ) ) {
					webequipe_pdf_search_log( 'Invalid date values for post_id: ' . $data['post_id'], 'ERROR' );
				}
				return false;
			}

			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- Custom index table insert required.
			$result = $wpdb->insert(
				$table_name,
				$insert_data,
				array( '%d', '%s', '%s', '%s', '%s', '%d', '%d', '%s', '%s' )
			);

			if ( false === $result ) {
				self::$last_upsert_error = ! empty( $wpdb->last_error ) ? $wpdb->last_error : 'Insert operation failed';

				if ( function_exists( __NAMESPACE__ . '\webequipe_pdf_search_log' ) ) {
					$debug_info = 'Post ID: ' . $data['post_id'] .
						' | Title length: ' . strlen( $data['pdf_title'] ) .
						' | URL length: ' . strlen( $data['pdf_url'] ) .
						' | Filename length: ' . strlen( $data['pdf_filename'] ) .
						' | Content length: ' . strlen( $data['pdf_content'] ) .
						' | Query: ' . $wpdb->last_query;
					webequipe_pdf_search_log( 'Database insert failed: ' . self::$last_upsert_error . ' | ' . $debug_info, 'ERROR' );
				}
				return false;
			}
		}

		// Success - clear last error.
		self::$last_upsert_error = '';
		return true;
	}

	/**
	 * Delete PDF from index
	 *
	 * @since 1.0.0
	 * @param int $post_id Attachment post ID.
	 * @return bool True on success, false on failure
	 */
	public static function delete_pdf( $post_id ) {
		global $wpdb;

		$post_id = absint( $post_id );
		if ( ! $post_id ) {
			return false;
		}

		self::delete_file_index( $post_id );

		$table_name = self::get_table_name();
		if ( ! self::table_exists( $table_name ) ) {
			return true;
		}

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Legacy index table delete.
		$result = $wpdb->delete(
			$table_name,
			array( 'post_id' => $post_id ),
			array( '%d' )
		);

		return false !== $result;
	}

	/**
	 * Remove all indexed PDF data (legacy table, per-page files/pages, and index post meta).
	 *
	 * Does not remove exclusion flags or activity log entries.
	 *
	 * @since 1.2.2
	 * @return bool True on success, false if a table operation failed.
	 */
	public static function clear_entire_index() {
		global $wpdb;

		wp_clear_scheduled_hook( 'webequipe_pdf_search_index_pages' );

		$tables = array(
			self::get_pages_table_name(),
			self::get_files_table_name(),
			self::get_table_name(),
		);

		foreach ( $tables as $table_name ) {
			if ( ! self::table_exists( $table_name ) ) {
				continue;
			}

			// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Trusted table name from get_*_table_name().
			$wpdb->query( $wpdb->prepare( 'TRUNCATE TABLE %i', $table_name ) );

			if ( ! empty( $wpdb->last_error ) ) {
				if ( function_exists( __NAMESPACE__ . '\webequipe_pdf_search_log' ) ) {
					webequipe_pdf_search_log( 'clear_entire_index failed on ' . $table_name . ': ' . $wpdb->last_error, 'ERROR' );
				}
				return false;
			}
		}

		update_option( 'webequipe_pdf_search_error_count', 0 );

		$pdf_ids = get_posts(
			array(
				'post_type'              => 'attachment',
				'post_mime_type'         => 'application/pdf',
				'post_status'            => 'inherit',
				'fields'                 => 'ids',
				'posts_per_page'         => -1,
				'no_found_rows'          => true,
				'update_post_meta_cache' => false,
				'update_post_term_cache' => false,
			)
		);

		if ( is_array( $pdf_ids ) ) {
			$meta_keys = array(
				'_webequipe_pdf_indexing_status',
				'_webequipe_pdf_index_error',
				'_webequipe_pdf_index_warning',
				self::META_ACTIVITY_LOG_ID,
				'_webequipe_pdf_index_cursor',
				'_webequipe_pdf_index_page_count',
			);

			foreach ( $pdf_ids as $post_id ) {
				$post_id = absint( $post_id );
				if ( ! $post_id ) {
					continue;
				}
				foreach ( $meta_keys as $meta_key ) {
					delete_post_meta( $post_id, $meta_key );
				}
			}
		}

		return true;
	}

	/**
	 * Get indexing statistics
	 *
	 * @since 1.0.0
	 * @return array Statistics array
	 */
	public static function get_statistics() {
		global $wpdb;

		$table_name = self::get_table_name();

		$stats = array(
			'total_indexed'    => 0,
			'total_in_library' => 0,
			'percentage'       => 0,
			'last_index_date'  => null,
			'failed_count'     => 0,
		);

		$files_table = self::get_files_table_name();
		if ( self::table_exists( $files_table ) ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Per-page files table count.
			$stats['total_indexed'] = (int) $wpdb->get_var(
				$wpdb->prepare(
					"SELECT COUNT(*) FROM %i WHERE index_status = %s",
					$files_table,
					'completed'
				)
			);
		} else {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Legacy index table count.
			$stats['total_indexed'] = (int) $wpdb->get_var(
				$wpdb->prepare(
					'SELECT COUNT(*) FROM %i',
					$table_name
				)
			);
		}

		// Get total PDFs in media library.
		$stats['total_in_library'] = 0;
		$counts                    = wp_count_attachments( 'application/pdf' );
		if ( $counts ) {
			foreach ( (array) $counts as $count ) {
				$stats['total_in_library'] += (int) $count;
			}
		}

		// Calculate percentage.
		if ( $stats['total_in_library'] > 0 ) {
			$stats['percentage'] = round( ( $stats['total_indexed'] / $stats['total_in_library'] ) * 100, 1 );
		}

		// Get last index date from per-page files, legacy index, and activity log (latest wins).
		$last_index_candidates = array();

		if ( self::table_exists( $files_table ) ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Per-page files table last index date.
			$files_last_index = $wpdb->get_var(
				$wpdb->prepare(
					"SELECT MAX(indexed_date) FROM %i WHERE index_status = %s",
					$files_table,
					'completed'
				)
			);
			if ( ! empty( $files_last_index ) ) {
				$last_index_candidates[] = $files_last_index;
			}
		}

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Legacy index table last index date.
		$legacy_last_index = $wpdb->get_var(
			$wpdb->prepare(
				'SELECT MAX(indexed_date) FROM %i',
				$table_name
			)
		);
		if ( ! empty( $legacy_last_index ) ) {
			$last_index_candidates[] = $legacy_last_index;
		}

		$activity_table = self::get_activity_table_name();
		if ( self::table_exists( $activity_table ) ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Activity log last completed index date.
			$activity_last_index = $wpdb->get_var(
				$wpdb->prepare(
					"SELECT MAX(completed_at) FROM %i WHERE index_status = %s AND completed_at IS NOT NULL",
					$activity_table,
					'completed'
				)
			);
			if ( ! empty( $activity_last_index ) ) {
				$last_index_candidates[] = $activity_last_index;
			}
		}

		if ( ! empty( $last_index_candidates ) ) {
			$stats['last_index_date'] = max( $last_index_candidates );
		}

		// Get failed count from stored counter to avoid slow meta queries.
		$stats['failed_count'] = self::get_error_count();

		return $stats;
	}

	/**
	 * Aggregate data for the admin dashboard view.
	 *
	 * @since 1.2.0
	 * @return array
	 */
	public static function get_dashboard_data() {
		self::ensure_activity_log_ready();

		$settings       = self::get_settings();
		$stats          = self::get_statistics();
		$activity_stats = self::get_activity_statistics();
		$recent         = self::get_activity_list(
			array(
				'offset'        => 0,
				'limit'         => 5,
				'search'        => '',
				'status_filter' => 'all',
			)
		);

		$indexing_enabled   = ! empty( $settings['enable_indexing'] );
		$search_enabled     = ! empty( $settings['enable_search'] );
		$background_enabled = ! empty( $settings['enable_background'] );
		$processing_count   = (int) $activity_stats['processing'];
		$failed_count       = (int) $stats['failed_count'];
		$needs_attention    = $failed_count + $processing_count;

		$cron_pending = $processing_count > 0;
		if ( ! $cron_pending && function_exists( 'wp_get_scheduled_event' ) ) {
			$cron_pending = (bool) wp_get_scheduled_event( 'webequipe_pdf_search_index_pages' );
		}

		$status_tone    = 'active';
		$status_message = __( 'Search and indexing are active', 'webequipe-pdf-search' );

		if ( ! $indexing_enabled && ! $search_enabled ) {
			$status_tone    = 'paused';
			$status_message = __( 'Search and indexing are paused', 'webequipe-pdf-search' );
		} elseif ( ! $indexing_enabled ) {
			$status_tone    = 'paused';
			$status_message = __( 'PDF indexing is paused', 'webequipe-pdf-search' );
		} elseif ( ! $search_enabled ) {
			$status_tone    = 'warning';
			$status_message = __( 'PDF search in WordPress search is disabled', 'webequipe-pdf-search' );
		} elseif ( $failed_count > 0 ) {
			$status_tone    = 'warning';
			$status_message = __( 'Indexing is active with documents needing attention', 'webequipe-pdf-search' );
		}

		$last_failed_message = '';
		$activity_records    = self::get_activity_records();
		foreach ( $activity_records as $record ) {
			if ( 'failed' === $record['index_status'] && ! empty( $record['error_message'] ) ) {
				$last_failed_message = self::localize_error_message( $record['error_message'] );
				break;
			}
		}

		$health_percent = 0;
		if ( $stats['total_in_library'] > 0 ) {
			$attention       = min( $stats['total_in_library'], $needs_attention );
			$health_percent  = round( ( $attention / $stats['total_in_library'] ) * 100, 1 );
		}

		return array(
			'settings'            => $settings,
			'stats'               => $stats,
			'activity_stats'      => $activity_stats,
			'recent_activity'     => $recent,
			'indexing_enabled'    => $indexing_enabled,
			'search_enabled'      => $search_enabled,
			'background_enabled'  => $background_enabled,
			'cron_pending'        => $cron_pending,
			'processing_count'    => $processing_count,
			'failed_count'        => $failed_count,
			'needs_attention'     => $needs_attention,
			'health_percent'      => $health_percent,
			'status_tone'         => $status_tone,
			'status_message'      => $status_message,
			'last_failed_message' => $last_failed_message,
		);
	}

	/**
	 * Log the start of an indexing attempt.
	 *
	 * @since 1.2.0
	 * @param int   $post_id Attachment post ID.
	 * @param array $data    pdf_title, pdf_filename, pdf_filesize, page_count.
	 * @return int|false Activity log row ID.
	 */
	public static function log_activity_start( $post_id, $data = array() ) {
		global $wpdb;

		$post_id = absint( $post_id );
		if ( ! $post_id ) {
			return false;
		}

		self::create_activity_table();
		$table_name = self::get_activity_table_name();
		$now        = current_time( 'mysql' );

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Cancel superseded in-flight jobs.
		$wpdb->query(
			$wpdb->prepare(
				"UPDATE %i SET index_status = %s, completed_at = %s WHERE post_id = %d AND index_status IN ('processing', 'pending')",
				$table_name,
				'cancelled',
				$now,
				$post_id
			)
		);

		$row = array(
			'post_id'       => $post_id,
			'pdf_title'     => isset( $data['pdf_title'] ) ? sanitize_text_field( $data['pdf_title'] ) : '',
			'pdf_filename'  => isset( $data['pdf_filename'] ) ? sanitize_file_name( $data['pdf_filename'] ) : '',
			'pdf_filesize'  => isset( $data['pdf_filesize'] ) ? absint( $data['pdf_filesize'] ) : 0,
			'page_count'    => isset( $data['page_count'] ) ? absint( $data['page_count'] ) : 0,
			'pages_indexed' => 0,
			'index_status'  => 'processing',
			'error_message' => '',
			'started_at'    => $now,
		);

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Append activity log row.
		$inserted = $wpdb->insert(
			$table_name,
			$row,
			array( '%d', '%s', '%s', '%d', '%d', '%d', '%s', '%s', '%s' )
		);

		if ( ! $inserted ) {
			return false;
		}

		$log_id = (int) $wpdb->insert_id;
		update_post_meta( $post_id, self::META_ACTIVITY_LOG_ID, $log_id );

		return $log_id;
	}

	/**
	 * Update in-flight activity log progress.
	 *
	 * @since 1.2.0
	 * @param int $post_id       Attachment post ID.
	 * @param int $pages_indexed Pages completed so far.
	 * @return void
	 */
	public static function log_activity_progress( $post_id, $pages_indexed ) {
		global $wpdb;

		$log_id = (int) get_post_meta( absint( $post_id ), self::META_ACTIVITY_LOG_ID, true );
		if ( ! $log_id ) {
			return;
		}

		$table_name = self::get_activity_table_name();
		if ( ! self::table_exists( $table_name ) ) {
			return;
		}

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Update activity progress.
		$wpdb->update(
			$table_name,
			array( 'pages_indexed' => absint( $pages_indexed ) ),
			array( 'id' => $log_id ),
			array( '%d' ),
			array( '%d' )
		);
	}

	/**
	 * Mark an indexing attempt as completed.
	 *
	 * @since 1.2.0
	 * @param int $post_id    Attachment post ID.
	 * @param int $page_count Total pages indexed.
	 * @return void
	 */
	public static function log_activity_complete( $post_id, $page_count = 0 ) {
		self::update_activity_terminal_status( absint( $post_id ), 'completed', '', $page_count );
	}

	/**
	 * Mark an indexing attempt as failed.
	 *
	 * @since 1.2.0
	 * @param int    $post_id Attachment post ID.
	 * @param string $error   Error message.
	 * @return void
	 */
	public static function log_activity_fail( $post_id, $error ) {
		self::update_activity_terminal_status( absint( $post_id ), 'failed', sanitize_text_field( $error ), 0 );
	}

	/**
	 * Update activity log row to a terminal status.
	 *
	 * @since 1.2.0
	 * @param int    $post_id    Attachment post ID.
	 * @param string $status     completed|failed|cancelled.
	 * @param string $error      Error message when failed.
	 * @param int    $page_count Final page count.
	 * @return void
	 */
	private static function update_activity_terminal_status( $post_id, $status, $error, $page_count ) {
		global $wpdb;

		if ( ! $post_id ) {
			return;
		}

		$log_id = (int) get_post_meta( $post_id, self::META_ACTIVITY_LOG_ID, true );
		if ( ! $log_id ) {
			return;
		}

		$table_name = self::get_activity_table_name();
		if ( ! self::table_exists( $table_name ) ) {
			delete_post_meta( $post_id, self::META_ACTIVITY_LOG_ID );
			return;
		}

		$update = array(
			'index_status'  => $status,
			'error_message' => $error,
			'completed_at'  => current_time( 'mysql' ),
		);

		if ( 'completed' === $status ) {
			$update['page_count']    = absint( $page_count );
			$update['pages_indexed'] = absint( $page_count );
		}

		$formats = array( '%s', '%s', '%s' );
		if ( 'completed' === $status ) {
			$formats[] = '%d';
			$formats[] = '%d';
		}

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Finalize activity log row.
		$wpdb->update(
			$table_name,
			$update,
			array( 'id' => $log_id ),
			$formats,
			array( '%d' )
		);

		delete_post_meta( $post_id, self::META_ACTIVITY_LOG_ID );
	}

	/**
	 * Ensure the activity log table exists and is populated from existing indexes when empty.
	 *
	 * @since 1.2.0
	 * @return void
	 */
	public static function ensure_activity_log_ready() {
		self::create_activity_table();

		$table_name = self::get_activity_table_name();
		if ( ! self::table_exists( $table_name ) ) {
			return;
		}

		global $wpdb;

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Activity table row count.
		$existing = (int) $wpdb->get_var(
			$wpdb->prepare( 'SELECT COUNT(*) FROM %i', $table_name )
		);

		if ( $existing > 0 ) {
			return;
		}

		// Table is empty — allow backfill even if a previous attempt set the flag without inserting rows.
		delete_option( 'webequipe_pdf_search_activity_backfilled' );
		self::backfill_activity_from_files();
	}

	/**
	 * Backfill activity log from existing file and legacy index rows (one-time).
	 *
	 * @since 1.2.0
	 * @return void
	 */
	public static function backfill_activity_from_files() {
		if ( get_option( 'webequipe_pdf_search_activity_backfilled', false ) ) {
			return;
		}

		self::create_activity_table();
		$table_name = self::get_activity_table_name();

		if ( ! self::table_exists( $table_name ) ) {
			return;
		}

		global $wpdb;

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- One-time backfill check.
		$existing = (int) $wpdb->get_var(
			$wpdb->prepare( 'SELECT COUNT(*) FROM %i', $table_name )
		);

		if ( $existing > 0 ) {
			update_option( 'webequipe_pdf_search_activity_backfilled', true, false );
			return;
		}

		$records  = array();
		$file_ids = array();
		$files_tbl = self::get_files_table_name();

		if ( self::table_exists( $files_tbl ) ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Backfill from files table.
			$file_rows = $wpdb->get_results(
				$wpdb->prepare( 'SELECT * FROM %i', $files_tbl ),
				ARRAY_A
			);

			if ( $file_rows ) {
				foreach ( $file_rows as $row ) {
					$post_id = absint( $row['post_id'] );
					if ( ! $post_id ) {
						continue;
					}

					$file_ids[] = $post_id;
					$status     = isset( $row['index_status'] ) ? (string) $row['index_status'] : 'completed';
					if ( ! in_array( $status, array( 'completed', 'failed', 'processing', 'pending', 'cancelled' ), true ) ) {
						$status = 'completed';
					}

					$started   = ! empty( $row['indexed_date'] ) ? $row['indexed_date'] : $row['last_modified'];
					$completed = in_array( $status, array( 'completed', 'failed', 'cancelled' ), true ) ? $row['last_modified'] : null;

					$record = array(
						'post_id'       => $post_id,
						'pdf_title'     => isset( $row['pdf_title'] ) ? (string) $row['pdf_title'] : '',
						'pdf_filename'  => isset( $row['pdf_filename'] ) ? (string) $row['pdf_filename'] : '',
						'pdf_filesize'  => isset( $row['pdf_filesize'] ) ? (int) $row['pdf_filesize'] : 0,
						'page_count'    => isset( $row['page_count'] ) ? (int) $row['page_count'] : 0,
						'pages_indexed' => isset( $row['pages_indexed'] ) ? (int) $row['pages_indexed'] : 0,
						'index_status'  => $status,
						'error_message' => isset( $row['error_message'] ) ? (string) $row['error_message'] : '',
						'started_at'    => $started,
					);
					if ( null !== $completed ) {
						$record['completed_at'] = $completed;
					}
					$records[] = $record;
				}
			}
		}

		$legacy_tbl = self::get_table_name();
		if ( self::table_exists( $legacy_tbl ) ) {
			if ( ! empty( $file_ids ) ) {
				$not_in_placeholders = implode( ',', array_fill( 0, count( $file_ids ), '%d' ) );
				// phpcs:disable WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber, PluginCheck.Security.DirectDB.UnescapedDBParameter
				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Backfill: post IDs are absint; NOT IN uses %d placeholders only.
				$legacy_rows = $wpdb->get_results(
					$wpdb->prepare(
						'SELECT * FROM %i WHERE post_id NOT IN (' . $not_in_placeholders . ')',
						array_merge( array( $legacy_tbl ), $file_ids )
					),
					ARRAY_A
				);
				// phpcs:enable WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber, PluginCheck.Security.DirectDB.UnescapedDBParameter
			} else {
				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Backfill legacy-only rows.
				$legacy_rows = $wpdb->get_results(
					$wpdb->prepare( 'SELECT * FROM %i', $legacy_tbl ),
					ARRAY_A
				);
			}

			if ( ! empty( $legacy_rows ) ) {
				foreach ( $legacy_rows as $row ) {
					$post_id = absint( $row['post_id'] );
					if ( ! $post_id ) {
						continue;
					}

					$page_count = isset( $row['page_count'] ) ? (int) $row['page_count'] : 0;
					$started    = ! empty( $row['indexed_date'] ) ? $row['indexed_date'] : $row['last_modified'];

					$records[] = array(
						'post_id'       => $post_id,
						'pdf_title'     => isset( $row['pdf_title'] ) ? (string) $row['pdf_title'] : '',
						'pdf_filename'  => isset( $row['pdf_filename'] ) ? (string) $row['pdf_filename'] : '',
						'pdf_filesize'  => isset( $row['pdf_filesize'] ) ? (int) $row['pdf_filesize'] : 0,
						'page_count'    => $page_count,
						'pages_indexed' => $page_count,
						'index_status'  => 'completed',
						'error_message' => '',
						'started_at'    => $started,
						'completed_at'  => $row['last_modified'],
					);
				}
			}
		}

		$inserted = 0;
		foreach ( $records as $record ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- One-time historical insert.
			if ( false !== $wpdb->insert( $table_name, $record ) ) {
				++$inserted;
			}
		}

		// Mark backfill complete when rows were inserted or there was nothing to migrate.
		if ( $inserted > 0 || empty( $records ) ) {
			update_option( 'webequipe_pdf_search_activity_backfilled', true, false );
		}
	}

	/**
	 * Get activity dashboard statistics (Index Activity admin page).
	 *
	 * @since 1.2.0
	 * @return array
	 */
	public static function get_activity_statistics() {
		self::sync_stale_open_activity_logs();

		$records = self::get_activity_records();

		$stats = array(
			'total_documents' => count( $records ),
			'total_pages'     => 0,
			'successful'      => 0,
			'processing'      => self::count_in_flight_indexing_jobs(),
			'failed'          => 0,
		);

		foreach ( $records as $record ) {
			if ( 'completed' === $record['index_status'] ) {
				++$stats['successful'];
				$stats['total_pages'] += (int) $record['page_count'];
			} elseif ( 'failed' === $record['index_status'] ) {
				++$stats['failed'];
			}
		}

		return $stats;
	}

	/**
	 * Get paginated index activity list for the admin activity page.
	 *
	 * @since 1.2.0
	 * @param array $args Query arguments (offset, limit, search, status_filter).
	 * @return array
	 */
	public static function get_activity_list( $args = array() ) {
		self::sync_stale_open_activity_logs();

		$defaults = array(
			'offset'        => 0,
			'limit'         => 20,
			'search'        => '',
			'status_filter' => 'all',
		);

		$args = wp_parse_args( $args, $defaults );

		$offset        = max( 0, absint( $args['offset'] ) );
		$limit         = max( 1, min( 100, absint( $args['limit'] ) ) );
		$search        = sanitize_text_field( $args['search'] );
		$status_filter = sanitize_key( $args['status_filter'] );

		$allowed_filters = array( 'all', 'completed', 'processing', 'scheduled', 'stalled', 'pending', 'failed', 'cancelled' );
		if ( ! in_array( $status_filter, $allowed_filters, true ) ) {
			$status_filter = 'all';
		}

		$records = self::get_activity_records();

		if ( '' !== $search ) {
			$records = array_values(
				array_filter(
					$records,
					function ( $record ) use ( $search ) {
						if ( is_numeric( $search ) && (int) $search === (int) $record['post_id'] ) {
							return true;
						}

						$haystack = strtolower(
							$record['pdf_title'] . ' ' . $record['pdf_filename']
						);

						return false !== strpos( $haystack, strtolower( $search ) );
					}
				)
			);
		}

		if ( 'all' !== $status_filter ) {
			$records = array_values(
				array_filter(
					$records,
					function ( $record ) use ( $status_filter ) {
						$display_status = self::get_activity_row_display_status(
							(int) $record['post_id'],
							(int) $record['activity_id'],
							(string) $record['index_status']
						);

						if ( 'processing' === $status_filter ) {
							return self::is_in_progress_display_status( $display_status );
						}

						return $status_filter === $display_status;
					}
				)
			);
		}

		usort(
			$records,
			function ( $a, $b ) {
				return strcmp( $b['started_at'], $a['started_at'] );
			}
		);

		$total   = count( $records );
		$records = array_slice( $records, $offset, $limit );

		$formatted = array();
		foreach ( $records as $index => $record ) {
			$post_id   = (int) $record['post_id'];
			$edit_link = admin_url(
				add_query_arg(
					array(
						'post'   => $post_id,
						'action' => 'edit',
					),
					'post.php'
				)
			);

			$title = $record['pdf_title'];
			if ( '' === $title ) {
				$attachment = get_post( $post_id );
				if ( $attachment ) {
					$title = ! empty( $attachment->post_title ) ? $attachment->post_title : $attachment->post_name;
				}
			}

			$display_date = ! empty( $record['completed_at'] ) ? $record['completed_at'] : $record['started_at'];

			$activity_id    = (int) $record['activity_id'];
			$display_status = self::get_activity_row_display_status(
				$post_id,
				$activity_id,
				(string) $record['index_status']
			);

			$page_count    = (int) $record['page_count'];
			$pages_indexed = (int) $record['pages_indexed'];
			$active_id     = (int) get_post_meta( $post_id, self::META_ACTIVITY_LOG_ID, true );

			if ( $activity_id && $activity_id === $active_id && self::is_in_progress_display_status( $display_status ) ) {
				$file = self::get_indexed_file( $post_id );
				if ( $file ) {
					$page_count    = (int) $file->page_count;
					$pages_indexed = (int) $file->pages_indexed;
				}
			}

			$formatted[] = array(
				'activity_id'     => $activity_id,
				'serial'          => $offset + $index + 1,
				'post_id'         => $post_id,
				'title'           => sanitize_text_field( $title ),
				'filename'        => sanitize_file_name( $record['pdf_filename'] ),
				'page_count'      => $page_count,
				'pages_indexed'   => $pages_indexed,
				'index_status'    => sanitize_key( $record['index_status'] ),
				'display_status'  => sanitize_key( $display_status ),
				'is_current_run'  => $activity_id && $activity_id === $active_id,
				'error_message'   => sanitize_text_field( self::localize_error_message( $record['error_message'] ) ),
				'started_at'      => sanitize_text_field( $record['started_at'] ),
				'completed_at'    => ! empty( $record['completed_at'] ) ? sanitize_text_field( $record['completed_at'] ) : '',
				'display_date'    => sanitize_text_field( $display_date ),
				'edit_link'       => esc_url_raw( $edit_link ),
				'manage_pdf_link' => esc_url_raw( admin_url( 'admin.php?page=webequipe-pdf-search-manage' ) ),
			);
		}

		return array(
			'records' => $formatted,
			'total'   => $total,
			'offset'  => $offset,
			'limit'   => $limit,
		);
	}

	/**
	 * Fetch all rows from the activity log table.
	 *
	 * @since 1.2.0
	 * @return array<int, array<string, mixed>>
	 */
	private static function get_activity_records() {
		global $wpdb;

		self::ensure_activity_log_ready();

		$table_name = self::get_activity_table_name();
		if ( ! self::table_exists( $table_name ) ) {
			return array();
		}

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Activity log listing.
		$rows = $wpdb->get_results(
			$wpdb->prepare( 'SELECT * FROM %i ORDER BY started_at DESC', $table_name ),
			ARRAY_A
		);

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

		$records = array();
		foreach ( $rows as $row ) {
			$post_id = absint( $row['post_id'] );
			if ( ! $post_id ) {
				continue;
			}

			$records[] = array(
				'activity_id'   => isset( $row['id'] ) ? (int) $row['id'] : 0,
				'post_id'       => $post_id,
				'pdf_title'     => isset( $row['pdf_title'] ) ? (string) $row['pdf_title'] : '',
				'pdf_filename'  => isset( $row['pdf_filename'] ) ? (string) $row['pdf_filename'] : '',
				'page_count'    => isset( $row['page_count'] ) ? (int) $row['page_count'] : 0,
				'pages_indexed' => isset( $row['pages_indexed'] ) ? (int) $row['pages_indexed'] : 0,
				'index_status'  => isset( $row['index_status'] ) ? (string) $row['index_status'] : 'processing',
				'error_message' => isset( $row['error_message'] ) ? (string) $row['error_message'] : '',
				'started_at'    => isset( $row['started_at'] ) ? (string) $row['started_at'] : '',
				'completed_at'  => isset( $row['completed_at'] ) ? (string) $row['completed_at'] : '',
			);
		}

		return $records;
	}

	/**
	 * Set index error for a PDF and maintain the error counter.
	 *
	 * @since 1.0.0
	 * @param int    $post_id Attachment post ID.
	 * @param string $message Error message.
	 * @return void
	 */
	public static function set_index_error( $post_id, $message ) {
		$post_id = absint( $post_id );
		if ( ! $post_id ) {
			return;
		}

		$had_error = (bool) get_post_meta( $post_id, '_webequipe_pdf_index_error', true );
		$message   = sanitize_text_field( $message );
		update_post_meta( $post_id, '_webequipe_pdf_index_error', $message );

		if ( ! $had_error ) {
			self::adjust_error_count( 1 );
		}
	}

	/**
	 * Clear index error for a PDF and maintain the error counter.
	 *
	 * @since 1.0.0
	 * @param int $post_id Attachment post ID.
	 * @return void
	 */
	public static function clear_index_error( $post_id ) {
		$post_id = absint( $post_id );
		if ( ! $post_id ) {
			return;
		}

		$had_error = (bool) get_post_meta( $post_id, '_webequipe_pdf_index_error', true );
		delete_post_meta( $post_id, '_webequipe_pdf_index_error' );

		if ( $had_error ) {
			self::adjust_error_count( -1 );
		}
	}

	/**
	 * Get the current error count.
	 *
	 * @since 1.0.0
	 * @return int
	 */
	public static function get_error_count() {
		return (int) get_option( 'webequipe_pdf_search_error_count', 0 );
	}

	/**
	 * Adjust the stored error count.
	 *
	 * @since 1.0.0
	 * @param int $delta Amount to adjust by.
	 * @return void
	 */
	private static function adjust_error_count( $delta ) {
		$count = (int) get_option( 'webequipe_pdf_search_error_count', 0 );
		$count = max( 0, $count + (int) $delta );
		update_option( 'webequipe_pdf_search_error_count', $count );
	}

	/**
	 * Whether the PDF index table has the FULLTEXT key used for MATCH ... AGAINST.
	 *
	 * @since 1.2.0
	 * @return bool
	 */
	public static function has_fulltext_index() {
		static $has_index = null;

		if ( null !== $has_index ) {
			return $has_index;
		}

		global $wpdb;

		$table_name = self::get_table_name();
		if ( ! self::table_exists( $table_name ) ) {
			$has_index = false;
			return $has_index;
		}

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Index metadata check for search mode selection.
		$indexes = $wpdb->get_results(
			$wpdb->prepare(
				'SHOW INDEX FROM %i',
				$table_name
			)
		);

		$has_index = false;
		if ( is_array( $indexes ) ) {
			foreach ( $indexes as $index ) {
				if ( isset( $index->Key_name ) && 'pdf_content_search' === $index->Key_name ) {
					$has_index = true;
					break;
				}
			}
		}
		return $has_index;
	}

	/**
	 * Minimum word length for MySQL FULLTEXT (ft_min_word_len).
	 *
	 * @since 1.2.0
	 * @return int
	 */
	private static function get_ft_min_word_len() {
		static $min_len = null;

		if ( null !== $min_len ) {
			return $min_len;
		}

		global $wpdb;

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Server variable for FULLTEXT eligibility.
		$min_len = (int) $wpdb->get_var( 'SELECT @@ft_min_word_len' );
		if ( $min_len < 1 ) {
			$min_len = 4;
		}

		return $min_len;
	}

	/**
	 * Split a search string into non-empty terms.
	 *
	 * @since 1.2.0
	 * @param string $search_term Raw search query.
	 * @return string[]
	 */
	private static function tokenize_search_term( $search_term ) {
		$terms = preg_split( '/\s+/u', trim( $search_term ), -1, PREG_SPLIT_NO_EMPTY );
		return is_array( $terms ) ? $terms : array();
	}

	/**
	 * Whether FULLTEXT search can be used for this query on the current host.
	 *
	 * @since 1.2.0
	 * @param string $search_term Sanitized search query.
	 * @return bool
	 */
	private static function can_use_fulltext_search( $search_term ) {
		if ( ! apply_filters( 'webequipe_pdf_search_use_fulltext', true, $search_term ) ) {
			return false;
		}

		if ( ! self::has_fulltext_index() ) {
			return false;
		}

		$min_len = self::get_ft_min_word_len();
		$terms   = self::tokenize_search_term( $search_term );
		if ( empty( $terms ) ) {
			return false;
		}

		foreach ( $terms as $term ) {
			if ( function_exists( 'mb_strlen' ) ) {
				$length = mb_strlen( $term, 'UTF-8' );
			} else {
				$length = strlen( $term );
			}
			if ( $length < $min_len ) {
				return false;
			}
		}

		return true;
	}

	/**
	 * Build a BOOLEAN MODE FULLTEXT search string (required terms: +word).
	 *
	 * @since 1.2.0
	 * @param string $search_term Sanitized search query.
	 * @return string
	 */
	private static function prepare_fulltext_boolean_term( $search_term ) {
		$terms = self::tokenize_search_term( $search_term );
		$parts = array();

		foreach ( $terms as $term ) {
			$term = preg_replace( '/[+\-><\(\)~*\"@]+/u', ' ', $term );
			$term = trim( $term );
			if ( '' === $term ) {
				continue;
			}
			$parts[] = '+' . $term;
		}

		return implode( ' ', $parts );
	}

	/**
	 * Search the PDF index (FULLTEXT when available, otherwise LIKE with per-word AND).
	 *
	 * @since 1.2.0
	 * @param string $search_term Search query.
	 * @param array  $args {
	 *     @type int  $limit      Max rows (0 = no limit).
	 *     @type int  $offset     Row offset.
	 *     @type bool $count_only Return total match count only.
	 * }
	 * @return int|object[] Total count if count_only; otherwise result rows.
	 */
	public static function search_index( $search_term, $args = array() ) {
		$args = wp_parse_args(
			$args,
			array(
				'limit'      => 0,
				'offset'     => 0,
				'count_only' => false,
			)
		);

		$search_term = wp_strip_all_tags( $search_term );
		$search_term = trim( $search_term );

		if ( strlen( $search_term ) < 2 ) {
			return $args['count_only'] ? 0 : array();
		}

		$pages_table = self::get_pages_table_name();
		$use_pages   = self::table_exists( $pages_table );

		if ( $use_pages ) {
			$page_results   = self::search_index_pages( $search_term, $args );
			$legacy_results = self::search_legacy_index( $search_term, $args, true );

			if ( $args['count_only'] ) {
				return (int) $page_results + (int) $legacy_results;
			}

			return self::merge_search_results( $page_results, $legacy_results, $args );
		}

		return self::search_legacy_index( $search_term, $args, false );
	}

	/**
	 * Search per-page index (one best row per PDF).
	 *
	 * @since 1.2.0
	 * @param string $search_term Search query.
	 * @param array  $args        Search arguments.
	 * @return int|object[]
	 */
	private static function search_index_pages( $search_term, $args ) {
		if ( self::can_use_pages_fulltext_search( $search_term ) ) {
			$boolean_term = self::prepare_fulltext_boolean_term( $search_term );
			if ( '' !== $boolean_term ) {
				$fulltext_result = self::run_pages_fulltext_search( $search_term, $boolean_term, $args );
				if ( $args['count_only'] ) {
					if ( $fulltext_result > 0 ) {
						return $fulltext_result;
					}
				} elseif ( ! empty( $fulltext_result ) ) {
					return $fulltext_result;
				}
			}
		}

		return self::run_pages_like_search( $search_term, $args );
	}

	/**
	 * Search legacy single-table index, optionally excluding migrated PDFs.
	 *
	 * @since 1.2.0
	 * @param string $search_term       Search query.
	 * @param array  $args              Search arguments.
	 * @param bool   $exclude_migrated  Skip PDFs with completed per-page index.
	 * @return int|object[]
	 */
	private static function search_legacy_index( $search_term, $args, $exclude_migrated ) {
		$legacy_args = $args;
		if ( $exclude_migrated ) {
			$legacy_args['exclude_post_ids'] = self::get_completed_per_page_post_ids();
		}

		if ( self::can_use_fulltext_search( $search_term ) ) {
			$boolean_term = self::prepare_fulltext_boolean_term( $search_term );
			if ( '' !== $boolean_term ) {
				$fulltext_result = self::run_fulltext_search( $search_term, $boolean_term, $legacy_args );
				if ( $args['count_only'] ) {
					if ( $fulltext_result > 0 ) {
						return $fulltext_result;
					}
				} elseif ( ! empty( $fulltext_result ) ) {
					return $fulltext_result;
				}
			}
		}

		return self::run_like_search( $search_term, $legacy_args );
	}

	/**
	 * Post IDs that have a completed per-page index.
	 *
	 * @since 1.2.0
	 * @return int[]
	 */
	private static function get_completed_per_page_post_ids() {
		global $wpdb;

		$files_table = self::get_files_table_name();
		if ( ! self::table_exists( $files_table ) ) {
			return array();
		}

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Completed file index IDs.
		$ids = $wpdb->get_col(
			$wpdb->prepare(
				"SELECT post_id FROM %i WHERE index_status = %s",
				$files_table,
				'completed'
			)
		);

		return array_map( 'absint', (array) $ids );
	}

	/**
	 * Merge page and legacy search hits (one row per post_id).
	 *
	 * @since 1.2.0
	 * @param object[] $page_results   Results from per-page search.
	 * @param object[] $legacy_results Results from legacy search.
	 * @param array    $args           Search arguments.
	 * @return object[]
	 */
	private static function merge_search_results( $page_results, $legacy_results, $args ) {
		$merged = array_merge( (array) $page_results, (array) $legacy_results );
		$merged = self::dedupe_search_results_by_post( $merged );

		if ( $args['limit'] > 0 ) {
			$offset = max( 0, (int) $args['offset'] );
			$merged = array_slice( $merged, $offset, (int) $args['limit'] );
		}

		return $merged;
	}

	/**
	 * Keep highest relevance_score row per post_id.
	 *
	 * @since 1.2.0
	 * @param object[] $rows Search rows.
	 * @return object[]
	 */
	private static function dedupe_search_results_by_post( $rows ) {
		$by_post = array();

		foreach ( $rows as $row ) {
			if ( ! isset( $row->post_id ) ) {
				continue;
			}
			$post_id = (int) $row->post_id;
			$score   = isset( $row->relevance_score ) ? (float) $row->relevance_score : 0;

			if ( ! isset( $by_post[ $post_id ] ) || $score > (float) $by_post[ $post_id ]->relevance_score ) {
				$by_post[ $post_id ] = $row;
			}
		}

		$unique = array_values( $by_post );
		usort(
			$unique,
			function ( $a, $b ) {
				$score_a = isset( $a->relevance_score ) ? (float) $a->relevance_score : 0;
				$score_b = isset( $b->relevance_score ) ? (float) $b->relevance_score : 0;
				return $score_b <=> $score_a;
			}
		);

		return $unique;
	}

	/**
	 * Whether pages table has FULLTEXT index.
	 *
	 * @since 1.2.0
	 * @return bool
	 */
	public static function has_pages_fulltext_index() {
		static $has_index = null;

		if ( null !== $has_index ) {
			return $has_index;
		}

		global $wpdb;

		$table_name = self::get_pages_table_name();
		if ( ! self::table_exists( $table_name ) ) {
			$has_index = false;
			return $has_index;
		}

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Pages table index metadata.
		$indexes = $wpdb->get_results(
			$wpdb->prepare(
				'SHOW INDEX FROM %i',
				$table_name
			)
		);

		$has_index = false;
		if ( is_array( $indexes ) ) {
			foreach ( $indexes as $index ) {
				if ( isset( $index->Key_name ) && 'page_content_search' === $index->Key_name ) {
					$has_index = true;
					break;
				}
			}
		}

		return $has_index;
	}

	/**
	 * Whether FULLTEXT can be used on the pages table.
	 *
	 * @since 1.2.0
	 * @param string $search_term Sanitized search query.
	 * @return bool
	 */
	private static function can_use_pages_fulltext_search( $search_term ) {
		if ( ! apply_filters( 'webequipe_pdf_search_use_fulltext', true, $search_term ) ) {
			return false;
		}

		if ( ! self::has_pages_fulltext_index() ) {
			return false;
		}

		$min_len = self::get_ft_min_word_len();
		$terms   = self::tokenize_search_term( $search_term );
		if ( empty( $terms ) ) {
			return false;
		}

		foreach ( $terms as $term ) {
			$length = function_exists( 'mb_strlen' ) ? mb_strlen( $term, 'UTF-8' ) : strlen( $term );
			if ( $length < $min_len ) {
				return false;
			}
		}

		return true;
	}

	/**
	 * Build SQL fragment and values for excluding post IDs from legacy search.
	 *
	 * @since 1.2.0
	 * @param int[] $exclude_post_ids Post IDs to exclude.
	 * @return array{sql: string, values: int[]}
	 */
	private static function build_exclude_post_ids_sql( $exclude_post_ids ) {
		$exclude_post_ids = array_filter( array_map( 'absint', (array) $exclude_post_ids ) );
		if ( empty( $exclude_post_ids ) ) {
			return array(
				'sql'    => '',
				'values' => array(),
			);
		}

		$placeholders = implode( ',', array_fill( 0, count( $exclude_post_ids ), '%d' ) );

		return array(
			'sql'    => ' AND p.post_id NOT IN (' . $placeholders . ')',
			'values' => $exclude_post_ids,
		);
	}

	/**
	 * Run MATCH ... AGAINST on pages joined to files.
	 *
	 * @since 1.2.0
	 * @param string $search_term  Original query.
	 * @param string $boolean_term BOOLEAN MODE string.
	 * @param array  $args         Search arguments.
	 * @return int|object[]
	 */
	private static function run_pages_fulltext_search( $search_term, $boolean_term, $args ) {
		global $wpdb;

		$files_table = self::get_files_table_name();
		$pages_table = self::get_pages_table_name();
		$posts_table = $wpdb->posts;
		$title_like  = '%' . $wpdb->esc_like( $search_term ) . '%';

		if ( $args['count_only'] ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			return (int) $wpdb->get_var(
				$wpdb->prepare(
					'SELECT COUNT(DISTINCT f.post_id) FROM %i f
					INNER JOIN %i pg ON pg.post_id = f.post_id
					WHERE (MATCH(pg.page_content) AGAINST (%s IN BOOLEAN MODE) OR f.pdf_title LIKE %s)
					AND EXISTS (SELECT 1 FROM %i WHERE ID = f.post_id AND post_status = %s)
					AND f.index_status = \'completed\'',
					$files_table,
					$pages_table,
					$boolean_term,
					$title_like,
					$posts_table,
					'inherit'
				)
			);
		}

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$rows = $wpdb->get_results(
			$wpdb->prepare(
				'SELECT f.*, pg.page_number AS matched_page_number, pg.page_content AS matched_page_content,
				pg.page_content AS pdf_content,
				(GREATEST(
					COALESCE(MATCH(pg.page_content) AGAINST (%s IN BOOLEAN MODE), 0),
					(CASE WHEN f.pdf_title LIKE %s THEN 100 ELSE 0 END),
					(CASE WHEN pg.page_content LIKE %s THEN 50 ELSE 0 END)
				)) AS relevance_score
				FROM %i f
				INNER JOIN %i pg ON pg.post_id = f.post_id
				WHERE (MATCH(pg.page_content) AGAINST (%s IN BOOLEAN MODE) OR f.pdf_title LIKE %s OR pg.page_content LIKE %s)
				AND EXISTS (SELECT 1 FROM %i WHERE ID = f.post_id AND post_status = %s)
				AND f.index_status = \'completed\'
				ORDER BY relevance_score DESC',
				$boolean_term,
				$title_like,
				$title_like,
				$files_table,
				$pages_table,
				$boolean_term,
				$title_like,
				$title_like,
				$posts_table,
				'inherit'
			),
			OBJECT
		);

		$rows = self::dedupe_search_results_by_post( $rows );

		if ( $args['limit'] > 0 ) {
			$offset = max( 0, (int) $args['offset'] );
			$rows   = array_slice( $rows, $offset, (int) $args['limit'] );
		}

		return $rows;
	}

	/**
	 * Run LIKE search on pages joined to files.
	 *
	 * @since 1.2.0
	 * @param string $search_term Sanitized search query.
	 * @param array  $args        Search arguments.
	 * @return int|object[]
	 */
	private static function run_pages_like_search( $search_term, $args ) {
		global $wpdb;

		$files_table = self::get_files_table_name();
		$pages_table = self::get_pages_table_name();
		$posts_table = $wpdb->posts;
		$terms       = self::tokenize_search_term( $search_term );

		if ( empty( $terms ) ) {
			$terms = array( $search_term );
		}

		$page_clauses   = array();
		$prepare_values = array();

		foreach ( $terms as $term ) {
			$like             = '%' . $wpdb->esc_like( $term ) . '%';
			$page_clauses[]   = '(f.pdf_title LIKE %s OR pg.page_content LIKE %s)';
			$prepare_values[] = $like;
			$prepare_values[] = $like;
		}

		$phrase_like = '%' . $wpdb->esc_like( $search_term ) . '%';
		$match_where = '(' . implode( ' AND ', $page_clauses ) . ')';

		if ( $args['count_only'] ) {
			$count_args = array_merge(
				array( $files_table, $pages_table ),
				$prepare_values,
				array( $posts_table, 'inherit' )
			);
			// phpcs:disable WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber, PluginCheck.Security.DirectDB.UnescapedDBParameter
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- LIKE clauses use fixed %s templates; values are escaped.
			$count = (int) $wpdb->get_var(
				$wpdb->prepare(
					'SELECT COUNT(DISTINCT f.post_id) FROM %i f
					INNER JOIN %i pg ON pg.post_id = f.post_id
					WHERE ' . $match_where . ' AND EXISTS (SELECT 1 FROM %i WHERE ID = f.post_id AND post_status = %s) AND f.index_status = \'completed\'',
					...$count_args
				)
			);
			// phpcs:enable WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber, PluginCheck.Security.DirectDB.UnescapedDBParameter
			return $count;
		}

		$prepare_args = array_merge(
			array( $phrase_like, $phrase_like, $files_table, $pages_table ),
			$prepare_values,
			array( $posts_table, 'inherit' )
		);

		// phpcs:disable WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber, PluginCheck.Security.DirectDB.UnescapedDBParameter
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- LIKE clauses use fixed %s templates; values are escaped.
		$rows = $wpdb->get_results(
			$wpdb->prepare(
				'SELECT f.*, pg.page_number AS matched_page_number, pg.page_content AS matched_page_content,
				pg.page_content AS pdf_content,
				(CASE
					WHEN f.pdf_title LIKE %s THEN 100
					WHEN pg.page_content LIKE %s THEN 50
					ELSE 10
				END) AS relevance_score
				FROM %i f
				INNER JOIN %i pg ON pg.post_id = f.post_id
				WHERE ' . $match_where . '
				AND EXISTS (SELECT 1 FROM %i WHERE ID = f.post_id AND post_status = %s)
				AND f.index_status = \'completed\'
				ORDER BY relevance_score DESC',
				...$prepare_args
			),
			OBJECT
		);
		// phpcs:enable WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber, PluginCheck.Security.DirectDB.UnescapedDBParameter

		$rows = self::dedupe_search_results_by_post( $rows );

		if ( $args['limit'] > 0 ) {
			$offset = max( 0, (int) $args['offset'] );
			$rows   = array_slice( $rows, $offset, (int) $args['limit'] );
		}

		return $rows;
	}

	/**
	 * Run MATCH ... AGAINST search on the PDF index.
	 *
	 * @since 1.2.0
	 * @param string $search_term  Original query (for relevance fallback).
	 * @param string $boolean_term BOOLEAN MODE search string.
	 * @param array  $args         Search arguments.
	 * @return int|object[]
	 */
	private static function run_fulltext_search( $search_term, $boolean_term, $args ) {
		global $wpdb;

		$table_name  = self::get_table_name();
		$posts_table = $wpdb->posts;
		$like        = '%' . $wpdb->esc_like( $search_term ) . '%';
		$exclude     = self::build_exclude_post_ids_sql( isset( $args['exclude_post_ids'] ) ? $args['exclude_post_ids'] : array() );

		if ( $args['count_only'] ) {
			$count_args = array_merge(
				array( $table_name, $boolean_term, $posts_table, 'inherit' ),
				$exclude['values']
			);
			// phpcs:disable WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber, PluginCheck.Security.DirectDB.UnescapedDBParameter
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Exclude IDs are absint; NOT IN uses %d placeholders only.
			$count = (int) $wpdb->get_var(
				$wpdb->prepare(
					'SELECT COUNT(*) FROM %i p
					WHERE MATCH(p.pdf_title, p.pdf_content) AGAINST (%s IN BOOLEAN MODE)
					AND EXISTS (SELECT 1 FROM %i WHERE ID = p.post_id AND post_status = %s)' . $exclude['sql'],
					...$count_args
				)
			);
			// phpcs:enable WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber, PluginCheck.Security.DirectDB.UnescapedDBParameter
			return $count;
		}

		$prepare_args = array_merge(
			array(
				$boolean_term,
				$like,
				$like,
				$table_name,
				$boolean_term,
				$posts_table,
				'inherit',
			),
			$exclude['values']
		);

		$select_sql = 'SELECT p.*,
			(GREATEST(
				COALESCE(MATCH(p.pdf_title, p.pdf_content) AGAINST (%s IN BOOLEAN MODE), 0),
				(CASE WHEN p.pdf_title LIKE %s THEN 100 WHEN p.pdf_content LIKE %s THEN 50 ELSE 0 END)
			)) AS relevance_score
			FROM %i p
			WHERE MATCH(p.pdf_title, p.pdf_content) AGAINST (%s IN BOOLEAN MODE)
			AND EXISTS (SELECT 1 FROM %i WHERE ID = p.post_id AND post_status = %s)' . $exclude['sql'] . '
			ORDER BY relevance_score DESC';

		if ( $args['limit'] > 0 ) {
			$select_sql    .= ' LIMIT %d OFFSET %d';
			$prepare_args[] = (int) $args['limit'];
			$prepare_args[] = max( 0, (int) $args['offset'] );
		}

		// phpcs:disable WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber, PluginCheck.Security.DirectDB.UnescapedDBParameter
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Exclude IDs are absint; NOT IN uses %d placeholders only.
		$rows = $wpdb->get_results(
			$wpdb->prepare( $select_sql, ...$prepare_args ),
			OBJECT
		);
		// phpcs:enable WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber, PluginCheck.Security.DirectDB.UnescapedDBParameter

		return $rows;
	}

	/**
	 * Run LIKE search with per-word AND (each word must appear in title or content).
	 *
	 * @since 1.2.0
	 * @param string $search_term Sanitized search query.
	 * @param array  $args        Search arguments.
	 * @return int|object[]
	 */
	private static function run_like_search( $search_term, $args ) {
		global $wpdb;

		$table_name  = self::get_table_name();
		$posts_table = $wpdb->posts;
		$terms       = self::tokenize_search_term( $search_term );

		if ( empty( $terms ) ) {
			$terms = array( $search_term );
		}

		$word_clauses   = array();
		$prepare_values = array();

		foreach ( $terms as $term ) {
			$like             = '%' . $wpdb->esc_like( $term ) . '%';
			$word_clauses[]   = '(p.pdf_title LIKE %s OR p.pdf_content LIKE %s)';
			$prepare_values[] = $like;
			$prepare_values[] = $like;
		}

		$phrase_like  = '%' . $wpdb->esc_like( $search_term ) . '%';
		$match_where  = '(' . implode( ' AND ', $word_clauses ) . ')';
		$exclude      = self::build_exclude_post_ids_sql( isset( $args['exclude_post_ids'] ) ? $args['exclude_post_ids'] : array() );

		if ( $args['count_only'] ) {
			$count_args = array_merge( array( $table_name ), $prepare_values, array( $posts_table, 'inherit' ), $exclude['values'] );
			// phpcs:disable WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber, PluginCheck.Security.DirectDB.UnescapedDBParameter
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- LIKE clauses use fixed %s templates; exclude IDs are absint.
			$count = (int) $wpdb->get_var(
				$wpdb->prepare(
					'SELECT COUNT(*) FROM %i p WHERE ' . $match_where . ' AND EXISTS (SELECT 1 FROM %i WHERE ID = p.post_id AND post_status = %s)' . $exclude['sql'],
					...$count_args
				)
			);
			// phpcs:enable WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber, PluginCheck.Security.DirectDB.UnescapedDBParameter
			return $count;
		}

		$prepare_args = array_merge(
			array( $phrase_like, $phrase_like, $table_name ),
			$prepare_values,
			array( $posts_table, 'inherit' ),
			$exclude['values']
		);

		$select_sql = 'SELECT p.*,
			(CASE
				WHEN p.pdf_title LIKE %s THEN 100
				WHEN p.pdf_content LIKE %s THEN 50
				ELSE 10
			END) AS relevance_score
			FROM %i p
			WHERE ' . $match_where . '
			AND EXISTS (SELECT 1 FROM %i WHERE ID = p.post_id AND post_status = %s)' . $exclude['sql'] . '
			ORDER BY relevance_score DESC';

		if ( $args['limit'] > 0 ) {
			$select_sql    .= ' LIMIT %d OFFSET %d';
			$prepare_args[] = (int) $args['limit'];
			$prepare_args[] = max( 0, (int) $args['offset'] );
		}

		// phpcs:disable WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber, PluginCheck.Security.DirectDB.UnescapedDBParameter
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- LIKE clauses use fixed %s templates; exclude IDs are absint.
		$rows = $wpdb->get_results(
			$wpdb->prepare( $select_sql, ...$prepare_args ),
			OBJECT
		);
		// phpcs:enable WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber, PluginCheck.Security.DirectDB.UnescapedDBParameter

		return $rows;
	}
}
