<?php
/**
 * Dynamic Query Block — Filter Index lifecycle.
 *
 * Owns creation, versioning and future reindex logic for the
 * {$wpdb->prefix}airowp_query_filter_index custom table.
 *
 * @package airo-wp
 * @since 2.2.0
 */

declare(strict_types=1);

namespace GoDaddy\WordPress\Plugins\AiroWp\Blocks\Common\Query;

defined( 'ABSPATH' ) || exit;
/**
 * Manages the airowp_query_filter_index database table.
 */
class FilterIndex {

	/**
	 * Current schema version.
	 *
	 * Increment when columns or indexes change and add migration logic in
	 * install() before updating the stored option.
	 *
	 * v2 (2026-04-19): adds `post_type` column so count_for_options can scope
	 * per-CPT. Upgrade TRUNCATEs the table — see install().
	 */
	const SCHEMA_VERSION = '2';

	/**
	 * Option key that stores the installed schema version.
	 */
	const OPTION_SCHEMA = 'airowp_query_filter_index_schema';

	/**
	 * Option key used to record background index status (e.g. "indexing", "ready").
	 * Reserved for future reindex tasks (Task A2+).
	 */
	const OPTION_STATUS = 'airowp_query_filter_index_status';

	/**
	 * Object-cache group for count_for_options() results.
	 *
	 * Cache entries carry the current epoch in their key so a bump invalidates
	 * every outstanding entry without needing to enumerate keys (which many
	 * object-cache backends do not support).
	 */
	const CACHE_GROUP = 'airowp_query_filter_index';

	/**
	 * Cache-epoch key. Read via wp_cache_get / bumped via wp_cache_incr so a
	 * persistent object cache (Redis/Memcached) invalidates count results
	 * atomically without hitting the options table on each write.
	 */
	const CACHE_EPOCH_KEY = 'counts_epoch';

	/**
	 * TTL (seconds) for cached count results. Counts stay correct until the
	 * next write (via the epoch), but we still TTL-cap in case the epoch
	 * counter is lost (non-persistent cache restart) — bounded staleness.
	 */
	const CACHE_TTL = 300;

	/**
	 * Per-request cache of the table existence check.
	 *
	 * Null = not yet checked; true/false = checked result.
	 *
	 * @var bool|null
	 */
	private static $table_exists = null;

	/**
	 * Database error from the last install() that failed to create the table.
	 *
	 * @var string
	 */
	private static $last_install_error = '';

	/**
	 * Returns the fully-qualified table name.
	 *
	 * @return string
	 */
	public static function table_name(): string {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		return $wpdb->prefix . 'airowp_query_filter_index';
	}

	/**
	 * Returns true when the filter index table actually exists in the database.
	 *
	 * Result is cached for the lifetime of the request to avoid repeated
	 * SHOW TABLES queries. Call reset_table_cache() in tests between cases.
	 *
	 * @return bool
	 */
	public static function table_exists(): bool {
		if ( null !== self::$table_exists ) {
			return self::$table_exists;
		}
		global $wpdb;
		$table_name = self::table_name();
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		self::$table_exists = ( $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $wpdb->esc_like( $table_name ) ) ) === $table_name );
		return self::$table_exists;
	}

	/**
	 * Resets the per-request table existence cache (for tests).
	 *
	 * @return void
	 */
	public static function reset_table_cache(): void {
		self::$table_exists = null;
	}

	/**
	 * Reindexes a single object's filter values.
	 *
	 * Deletes all prior rows for this (object_type, object_id) and rewrites them
	 * based on the current FilterRegistry entries. Idempotent by design.
	 *
	 * @param string $object_type One of 'post' (A2), 'user' (v2.4+), 'term' (v2.4+).
	 * @param int    $object_id   The object's primary key.
	 */
	public static function reindex_object( string $object_type, int $object_id ): void {
		global $wpdb;
		if ( $object_id <= 0 || ! self::table_exists() ) {
			return;
		}

		// v2.2 indexes only published posts. If the post is not published,
		// remove any existing rows and bail. This covers the case where
		// taxonomy/meta hooks fire for a draft that was previously published.
		$post_type = '';
		if ( 'post' === $object_type ) {
			$post = get_post( $object_id );
			if ( ! $post || 'publish' !== $post->post_status ) {
				self::remove_object( 'post', $object_id );
				return;
			}
			$post_type = (string) $post->post_type;
		}

		$table = self::table_name();

		// Idempotency: wipe existing rows for this object before reinsert.
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		$wpdb->delete(
			$table,
			array(
				'object_id'   => $object_id,
				'object_type' => $object_type,
			),
			array( '%d', '%s' )
		);

		// Deletion alone changes filter counts when an object no longer matches
		// any registered filter. Invalidate before each early return below.
		self::bump_counts_cache();

		$filters = FilterRegistry::all();
		if ( empty( $filters ) ) {
			return;
		}

		$rows = array();
		foreach ( $filters as $filter_key => $config ) {
			$values = self::resolve_filter_values( $object_type, $object_id, $config );
			foreach ( $values as $value ) {
				$rows[] = array(
					'object_id'    => $object_id,
					'object_type'  => $object_type,
					'post_type'    => $post_type,
					'filter_key'   => $filter_key,
					'filter_value' => (string) $value,
				);
			}
		}

		if ( empty( $rows ) ) {
			return;
		}

		// Bulk insert — one query regardless of filter count.
		$placeholders = array();
		$params       = array();
		foreach ( $rows as $row ) {
			$placeholders[] = '(%d, %s, %s, %s, %s)';
			$params[]       = $row['object_id'];
			$params[]       = $row['object_type'];
			$params[]       = $row['post_type'];
			$params[]       = $row['filter_key'];
			$params[]       = $row['filter_value'];
		}

		$sql = "INSERT INTO {$table} (object_id, object_type, post_type, filter_key, filter_value) VALUES "
			. implode( ', ', $placeholders );

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		$wpdb->query( $wpdb->prepare( $sql, $params ) );

		self::bump_counts_cache();
	}

	/**
	 * Resolves filter values for a given object. Posts-only in v2.2.
	 *
	 * @param string $object_type One of 'post', 'user', 'term'.
	 * @param int    $object_id   Object primary key.
	 * @param array  $config      Filter config from FilterRegistry: { type, source, label }.
	 * @return array Flat array of string values to index.
	 */
	private static function resolve_filter_values( string $object_type, int $object_id, array $config ): array {
		if ( 'post' !== $object_type ) {
			return array(); // v2.4 will add user/term support.
		}

		$type   = $config['type'] ?? '';
		$source = $config['source'] ?? '';
		if ( '' === $source ) {
			return array();
		}

		if ( 'taxonomy' === $type ) {
			$term_ids = wp_get_post_terms( $object_id, $source, array( 'fields' => 'ids' ) );
			if ( is_wp_error( $term_ids ) || empty( $term_ids ) ) {
				return array();
			}
			return array_map( 'strval', $term_ids );
		}

		if ( 'meta' === $type ) {
			$meta = get_post_meta( $object_id, $source, false );
			if ( ! is_array( $meta ) || empty( $meta ) ) {
				return array();
			}
			// Filter out empty strings, non-scalars, and values exceeding the VARCHAR(190) column width.
			$clean = array();
			foreach ( $meta as $value ) {
				if ( ! is_scalar( $value ) ) {
					continue;
				}
				$value = (string) $value;
				if ( '' === $value ) {
					continue;
				}
				if ( mb_strlen( $value ) > 190 ) {
					continue; // Longer than the VARCHAR(190) index column; skip to avoid truncation.
				}
				$clean[] = $value;
			}
			return $clean;
		}

		return array();
	}

	/**
	 * Deletes index rows that point at a deleted term.
	 *
	 * Core's wp_delete_term() detaches posts without firing set_object_terms, so
	 * the per-post hooks never see it and the rows would keep counting a term
	 * that no longer exists.
	 *
	 * @param int    $term_id  Deleted term ID.
	 * @param string $taxonomy Taxonomy the term belonged to.
	 * @return void
	 */
	public static function remove_term_values( int $term_id, string $taxonomy ): void {
		if ( ! self::table_exists() ) {
			return;
		}
		global $wpdb;
		$removed = false;
		foreach ( FilterRegistry::all() as $filter_key => $config ) {
			if ( 'taxonomy' !== ( $config['type'] ?? '' ) || ( $config['source'] ?? '' ) !== $taxonomy ) {
				continue;
			}
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
			$wpdb->delete(
				self::table_name(),
				array(
					'filter_key'   => (string) $filter_key,
					'filter_value' => (string) $term_id,
				),
				array( '%s', '%s' )
			);
			$removed = true;
		}
		if ( $removed ) {
			self::bump_counts_cache();
		}
	}

	/**
	 * Deletes all index rows for a given object.
	 *
	 * Scoped by both object_id and object_type to avoid cross-type collisions
	 * (e.g. a post and a user that happen to share the same numeric ID).
	 *
	 * @param string $object_type The object type (e.g. 'post').
	 * @param int    $object_id   The object's primary key.
	 * @return void
	 */
	public static function remove_object( string $object_type, int $object_id ): void {
		if ( ! self::table_exists() ) {
			return;
		}
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		$wpdb->delete(
			self::table_name(),
			array(
				'object_id'   => $object_id,
				'object_type' => $object_type,
			),
			array( '%d', '%s' )
		);
		self::bump_counts_cache();
	}

	/**
	 * Returns the count of distinct objects matching each option value for a
	 * filter key, intersected with the current active-filter state.
	 *
	 * Within-group semantics: selections inside the same filter group are OR
	 * (showing "how many objects would match if you added this value").
	 * Across-group semantics: each other active-filter group is AND.
	 * The self-filter is excluded from the intersection so users can still see
	 * counts for all options of the group they are currently filtering on.
	 *
	 * @param string $filter_key     The filter key to count options for (e.g. 'category').
	 * @param array  $option_values Option values to count. Values are (string)-cast.
	 * @param array  $active_filters Active filter state: [ filter_key => [ value, ... ] ].
	 * @param string $post_type     Optional. When non-empty, counts are restricted
	 *                              to rows with a matching post_type — and so are
	 *                              the intersection subqueries for active filters,
	 *                              so a CPT-scoped query never leaks counts from
	 *                              other post types that share the same taxonomy.
	 * @return array  [ value => count ] zero-filled for options absent from the result set.
	 */
	public static function count_for_options( string $filter_key, array $option_values, array $active_filters, string $post_type = '' ): array {
		if ( empty( $option_values ) || ! self::table_exists() ) {
			return array();
		}

		global $wpdb;
		$table = self::table_name();
		$key   = sanitize_key( $filter_key );
		if ( '' === $key ) {
			return array();
		}

		// Normalise option values to strings and build a keyed default (0-filled).
		$string_values = array_values( array_unique( array_map( 'strval', $option_values ) ) );
		$counts        = array_fill_keys( $string_values, 0 );

		// Exclude self-filter from intersection — OR semantics within a group.
		unset( $active_filters[ $key ] );

		// The index stores each post's directly-assigned terms, but the query a
		// filter drives matches a term's descendants too (include_children).
		// Count and intersect the same way, or a parent term reads lower than
		// the results it returns. Resolved at read time so existing indexes
		// stay correct without a rebuild.
		$families = self::option_families( $key, $string_values );
		foreach ( $active_filters as $f_key => $f_values ) {
			$active_filters[ $f_key ] = self::expand_with_descendants( (string) $f_key, array_map( 'strval', (array) $f_values ) );
		}

		// Cache lookup. Key is (epoch, filter_key, option_values, active_filters, post_type).
		// The epoch is bumped on every index write, so cached entries remain valid
		// until the next reindex/remove/rebuild. Skipped in tests where the
		// per-request table_exists cache can race with the drop-table teardown.
		$cache_key = self::build_counts_cache_key( $key, $string_values, $active_filters, $post_type, $families );
		$cached    = wp_cache_get( $cache_key, self::CACHE_GROUP );
		if ( is_array( $cached ) ) {
			return $cached;
		}

		// Optional post_type scope — applied to the outer query AND each
		// intersection subquery so cross-filter counts stay within the CPT.
		$post_type_sql    = '';
		$subquery_pt_sql  = '';
		$post_type_params = array();
		if ( '' !== $post_type ) {
			$post_type_sql      = ' AND post_type = %s';
			$subquery_pt_sql    = ' AND post_type = %s';
			$post_type_params[] = $post_type;
		}

		// Build intersection subqueries, one per active-filter group.
		$intersect_sql    = '';
		$intersect_params = array();

		foreach ( $active_filters as $f_key => $f_values ) {
			$sanitized_f_key = sanitize_key( (string) $f_key );
			if ( '' === $sanitized_f_key || empty( $f_values ) ) {
				continue;
			}
			$f_strings = array_values( array_unique( array_map( 'strval', (array) $f_values ) ) );
			if ( empty( $f_strings ) ) {
				continue;
			}
			$f_placeholders     = implode( ',', array_fill( 0, count( $f_strings ), '%s' ) );
			$intersect_sql     .= " AND object_id IN (
            SELECT object_id FROM {$table}
            WHERE filter_key = %s AND filter_value IN ({$f_placeholders}){$subquery_pt_sql}
        )";
			$intersect_params[] = $sanitized_f_key;
			foreach ( $f_strings as $v ) {
				$intersect_params[] = $v;
			}
			if ( '' !== $post_type ) {
				$intersect_params[] = $post_type;
			}
		}

		// Options with no descendants share one grouped query; an option whose
		// family spans several terms needs its own DISTINCT count so a post in
		// two child terms still counts once.
		$simple = array();
		$nested = array();
		foreach ( $string_values as $value ) {
			if ( count( $families[ $value ] ) > 1 ) {
				$nested[ $value ] = $families[ $value ];
			} else {
				$simple[] = $value;
			}
		}

		if ( ! empty( $simple ) ) {
			$value_placeholders = implode( ',', array_fill( 0, count( $simple ), '%s' ) );
			$sql                = "SELECT filter_value, COUNT(DISTINCT object_id) AS cnt
            FROM {$table}
            WHERE filter_key = %s AND filter_value IN ({$value_placeholders}){$post_type_sql}
            {$intersect_sql}
            GROUP BY filter_value";

			$params = array_merge( array( $key ), $simple, $post_type_params, $intersect_params );

			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
			$rows = $wpdb->get_results( $wpdb->prepare( $sql, $params ) );

			if ( is_array( $rows ) ) {
				foreach ( $rows as $row ) {
					if ( array_key_exists( $row->filter_value, $counts ) ) {
						$counts[ $row->filter_value ] = (int) $row->cnt;
					}
				}
			}
		}

		foreach ( $nested as $value => $family ) {
			$value_placeholders = implode( ',', array_fill( 0, count( $family ), '%s' ) );
			$sql                = "SELECT COUNT(DISTINCT object_id)
            FROM {$table}
            WHERE filter_key = %s AND filter_value IN ({$value_placeholders}){$post_type_sql}
            {$intersect_sql}";

			$params = array_merge( array( $key ), $family, $post_type_params, $intersect_params );

			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
			$counts[ (string) $value ] = (int) $wpdb->get_var( $wpdb->prepare( $sql, $params ) );
		}

		wp_cache_set( $cache_key, $counts, self::CACHE_GROUP, self::CACHE_TTL ); // phpcs:ignore WordPressVIPMinimum.Performance.LowExpiryCacheTime.CacheTimeUndetermined -- CACHE_TTL = 300 seconds (VIP minimum)

		return $counts;
	}

	/**
	 * The taxonomy a taxonomy-type filter indexes, when it is hierarchical.
	 *
	 * @param string $filter_key Filter key.
	 * @return string Taxonomy name, or '' for meta filters and flat taxonomies.
	 */
	private static function hierarchical_taxonomy( string $filter_key ): string {
		$config = FilterRegistry::get( $filter_key );
		if ( ! $config || 'taxonomy' !== ( $config['type'] ?? '' ) || empty( $config['source'] ) ) {
			return '';
		}
		$taxonomy = (string) $config['source'];

		return is_taxonomy_hierarchical( $taxonomy ) ? $taxonomy : '';
	}

	/**
	 * Map each option value to the term IDs it counts: itself plus descendants.
	 *
	 * @param string   $filter_key Filter key.
	 * @param string[] $values     Option values (term IDs for taxonomy filters).
	 * @return array<string, string[]> Value => IDs; always contains the value itself.
	 */
	private static function option_families( string $filter_key, array $values ): array {
		$taxonomy = self::hierarchical_taxonomy( $filter_key );
		$families = array();
		foreach ( $values as $value ) {
			$family = array( (string) $value );
			if ( '' !== $taxonomy && ctype_digit( (string) $value ) ) {
				$children = get_term_children( (int) $value, $taxonomy );
				if ( is_array( $children ) ) {
					$family = array_merge( $family, array_map( 'strval', $children ) );
				}
			}
			$families[ (string) $value ] = array_values( array_unique( $family ) );
		}

		return $families;
	}

	/**
	 * Add descendants to a list of selected values (an active filter group).
	 *
	 * @param string   $filter_key Filter key.
	 * @param string[] $values     Selected values.
	 * @return string[] Values plus their descendants.
	 */
	private static function expand_with_descendants( string $filter_key, array $values ): array {
		$expanded = array();
		foreach ( self::option_families( sanitize_key( $filter_key ), $values ) as $family ) {
			$expanded = array_merge( $expanded, $family );
		}

		return array_values( array_unique( $expanded ) );
	}

	/**
	 * Builds a cache key for count_for_options() that scopes the cached value to
	 * the current cache epoch + the full argument shape. Any index write bumps
	 * the epoch, so stale entries become unreachable without explicit deletion.
	 *
	 * @param string $filter_key     Sanitized filter key.
	 * @param array  $string_values  Sorted, unique option values.
	 * @param array  $active_filters Active filter state (minus self-filter).
	 * @param string $post_type      Post-type scope or empty string for all.
	 * @param array  $families       Option value => term IDs it counts (itself and descendants).
	 * @return string
	 */
	private static function build_counts_cache_key( string $filter_key, array $string_values, array $active_filters, string $post_type = '', array $families = array() ): string {
		$epoch = self::get_counts_epoch();
		// Sort values and active filters for stable key regardless of input order.
		sort( $string_values );
		ksort( $active_filters );
		foreach ( $active_filters as $k => $vs ) {
			if ( is_array( $vs ) ) {
				$vs = array_values( array_unique( array_map( 'strval', $vs ) ) );
				sort( $vs );
				$active_filters[ $k ] = $vs;
			}
		}
		return 'cfo:' . $epoch . ':' . md5(
			$filter_key . '|' . wp_json_encode( $string_values ) . '|' . wp_json_encode( $active_filters ) . '|pt=' . $post_type . '|fam=' . wp_json_encode( $families )
		);
	}

	/**
	 * Returns the current counts cache epoch — an incrementing integer used as
	 * part of the cache key so bumping the epoch invalidates every outstanding
	 * cached result without key enumeration.
	 *
	 * @return int
	 */
	private static function get_counts_epoch(): int {
		$epoch = wp_cache_get( self::CACHE_EPOCH_KEY, self::CACHE_GROUP );
		if ( false === $epoch ) {
			$epoch = 1;
			wp_cache_set( self::CACHE_EPOCH_KEY, $epoch, self::CACHE_GROUP );
		}
		return (int) $epoch;
	}

	/**
	 * Bumps the counts cache epoch, invalidating all previously-cached
	 * count_for_options() results. Called from reindex_object / remove_object
	 * and by the rebuilder on completion. Object-cache-only (no DB write) so
	 * bumping inside tight loops is safe; with a persistent cache backend the
	 * bump is atomic via wp_cache_incr.
	 *
	 * @return void
	 */
	public static function bump_counts_cache(): void {
		$incremented = wp_cache_incr( self::CACHE_EPOCH_KEY, 1, self::CACHE_GROUP );
		if ( false === $incremented ) {
			// Non-persistent caches (or no cache yet) — seed then bump.
			$current = (int) wp_cache_get( self::CACHE_EPOCH_KEY, self::CACHE_GROUP );
			wp_cache_set( self::CACHE_EPOCH_KEY, $current + 1, self::CACHE_GROUP );
		}
	}

	/**
	 * Returns true if the given filter key is registered in FilterRegistry.
	 *
	 * @param string $filter_key The filter key to check (e.g. 'category').
	 * @return bool
	 */
	public static function is_available( string $filter_key ): bool {
		return null !== FilterRegistry::get( $filter_key );
	}

	/**
	 * Returns the CREATE TABLE statement for the filter index table.
	 *
	 * Public so tests can create the table under a different storage engine
	 * (see filter-index-test.php) without going through dbDelta.
	 *
	 * @return string
	 */
	public static function schema_sql(): string {
		global $wpdb;

		$table = self::table_name();
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		$charset = $wpdb->get_charset_collate();

		// Note: PRIMARY KEY requires two spaces before the column name — dbDelta quirk.
		// post_type is VARCHAR(40) to match WP's wp_posts.post_type column width.
		// Empty string for non-post object_types (users/terms in v2.4+).
		//
		// The composite keys use 80-character prefixes on filter_key /
		// filter_value. Under utf8mb4 (4 bytes per character) the unprefixed
		// keys were 1520 and 1680 bytes, over MyISAM's 1000-byte limit, so
		// hosts whose default storage engine is MyISAM rejected the whole
		// CREATE TABLE with error 1071 (#551). Prefixed: 644 and 805 bytes.
		// Lookups are equality / IN matches, which prefix indexes serve.
		// dbDelta compares existing indexes ignoring prefixes, so tables
		// that installed before this change are left as they are.
		return "CREATE TABLE {$table} (
	id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
	object_id BIGINT UNSIGNED NOT NULL,
	object_type VARCHAR(20) NOT NULL,
	post_type VARCHAR(40) NOT NULL DEFAULT '',
	filter_key VARCHAR(190) NOT NULL,
	filter_value VARCHAR(190) NOT NULL,
	indexed_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
	PRIMARY KEY  (id),
	KEY filter_key_value (filter_key(80), filter_value(80)),
	KEY filter_scope (post_type, filter_key(80), filter_value(80)),
	KEY object_lookup (object_type, object_id)
) {$charset};";
	}

	/**
	 * Returns the database error from the last failed install(), or ''.
	 *
	 * @return string
	 */
	public static function last_install_error(): string {
		return self::$last_install_error;
	}

	/**
	 * Creates or upgrades the filter index table via dbDelta.
	 *
	 * Safe to call multiple times — dbDelta is idempotent.
	 *
	 * @return bool True when the table exists after the call.
	 */
	public static function install(): bool {
		global $wpdb;

		require_once ABSPATH . 'wp-admin/includes/upgrade.php';

		$table          = self::table_name();
		$stored_schema  = (string) get_option( self::OPTION_SCHEMA, '0' );
		$needs_truncate = self::table_exists() && version_compare( $stored_schema, '2', '<' );

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		$wpdb->last_error = '';
		dbDelta( self::schema_sql() );
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		// Snapshot now: the SHOW TABLES below flushes $wpdb->last_error.
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		$error = (string) $wpdb->last_error;

		// Reset the per-request cache so subsequent calls see the new table.
		self::$table_exists = null;

		if ( ! self::table_exists() ) {
			// dbDelta swallows CREATE TABLE errors (it only logs them via
			// $wpdb), so a server that rejects the schema leaves no table
			// behind. Don't record the schema version for a table that
			// doesn't exist.
			self::$last_install_error = $error;
			return false;
		}

		self::$last_install_error = '';

		// On a v1 → v2 upgrade, pre-existing rows have post_type='' (dbDelta's
		// DEFAULT fills new columns). A per-CPT count query would miss them,
		// so TRUNCATE and rely on the admin dashboard / WP-CLI rebuild to
		// reindex with correct post_type values. v2.2 has not shipped yet so
		// no production data is lost.
		if ( $needs_truncate ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
			$wpdb->query( $wpdb->prepare( 'TRUNCATE TABLE %i', $table ) );
			self::bump_counts_cache();
		}

		update_option( self::OPTION_SCHEMA, self::SCHEMA_VERSION, false );

		return true;
	}
}