<?php
/**
 * Database access layer.
 *
 * v1.17.0 keeps a short-lived detailed request log for diagnostics while using
 * compact daily aggregates for long-term Visibility Dash reporting. This keeps
 * high-volume crawler activity useful without allowing one row per hit to grow
 * indefinitely.
 */

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

class AI_O_Optimiser_DB {

	const AGGREGATE_STATE_OPTION = 'ai_o_optimiser_visibility_aggregate_state';
	const RAW_ROW_CAP            = 50000;
	const AGGREGATE_ROW_CAP      = 1000000;

	/** Request-local storage diagnostics snapshot; never persisted between PHP requests. */
	private static $storage_report_request_cache = null;

	/**
	 * Full raw-log table name.
	 *
	 * @return string
	 */
	public static function table() {
		global $wpdb;
		return $wpdb->prefix . AI_O_Optimiser_TABLE_NAME;
	}

	/**
	 * Full compact daily aggregate table name.
	 *
	 * @return string
	 */
	public static function aggregate_table() {
		global $wpdb;
		return $wpdb->prefix . 'ai_o_optimiser_visibility_daily';
	}

	/**
	 * Create/update raw and aggregate visibility tables.
	 */
	public static function create_table() {
		global $wpdb;

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

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

		$raw_sql = "CREATE TABLE {$table_name} (
			id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
			hit_time DATETIME NOT NULL,
			source_type VARCHAR(20) NOT NULL DEFAULT 'crawler',
			source_slug VARCHAR(80) NULL,
			source_label VARCHAR(150) NULL,
			bot_slug VARCHAR(50) NULL,
			bot_label VARCHAR(150) NULL,
			request_uri VARCHAR(500) NOT NULL,
			referrer VARCHAR(500) NULL,
			ip_hash VARCHAR(64) NULL,
			user_agent TEXT NULL,
			response_code SMALLINT UNSIGNED NULL,
			request_method VARCHAR(10) NULL,
			verified VARCHAR(12) NULL,
			PRIMARY KEY  (id),
			KEY hit_time (hit_time),
			KEY source_type_hit_time (source_type, hit_time),
			KEY source_slug_hit_time (source_slug, hit_time),
			KEY bot_slug (bot_slug),
			KEY bot_slug_hit_time (bot_slug, hit_time),
			KEY request_uri_hit_time (request_uri(191), hit_time)
		) {$charset_collate};";

		$aggregate_sql = "CREATE TABLE {$aggregate_table} (
			id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
			site_day DATE NOT NULL,
			source_type VARCHAR(20) NOT NULL DEFAULT 'crawler',
			source_slug VARCHAR(80) NOT NULL DEFAULT '',
			source_label VARCHAR(150) NOT NULL DEFAULT '',
			bot_slug VARCHAR(50) NOT NULL DEFAULT '',
			bot_label VARCHAR(150) NOT NULL DEFAULT '',
			request_hash CHAR(32) NOT NULL,
			request_uri VARCHAR(500) NOT NULL,
			total BIGINT UNSIGNED NOT NULL DEFAULT 0,
			ok_total BIGINT UNSIGNED NOT NULL DEFAULT 0,
			verified_total BIGINT UNSIGNED NOT NULL DEFAULT 0,
			last_seen DATETIME NOT NULL,
			PRIMARY KEY  (id),
			UNIQUE KEY bucket (site_day, source_type, source_slug, bot_slug, request_hash),
			KEY site_day (site_day),
			KEY source_type_day (source_type, site_day),
			KEY source_slug_day (source_slug, site_day),
			KEY bot_slug_day (bot_slug, site_day),
			KEY request_day (request_hash, site_day)
		) {$charset_collate};";

		dbDelta( $raw_sql );
		dbDelta( $aggregate_sql );

		// Backfill rows created by earlier versions so historic data appears as crawler activity.
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Intentional mutation of plugin-owned persistence state; caching the write would be incorrect.
		$wpdb->query( $wpdb->prepare( "UPDATE %i SET source_type = 'crawler' WHERE source_type IS NULL OR source_type = ''", $table_name ) );
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Intentional mutation of plugin-owned persistence state; caching the write would be incorrect.
		$wpdb->query( $wpdb->prepare( "UPDATE %i SET source_slug = bot_slug WHERE (source_slug IS NULL OR source_slug = '') AND bot_slug IS NOT NULL AND bot_slug != ''", $table_name ) );
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Intentional mutation of plugin-owned persistence state; caching the write would be incorrect.
		$wpdb->query( $wpdb->prepare( "UPDATE %i SET source_label = bot_label WHERE (source_label IS NULL OR source_label = '') AND bot_label IS NOT NULL AND bot_label != ''", $table_name ) );

		self::prepare_aggregate_backfill();
	}

	/**
	 * Initialise one bounded migration snapshot for pre-v1.17 raw rows.
	 */
	private static function prepare_aggregate_backfill() {
		global $wpdb;

		$state = get_option( self::AGGREGATE_STATE_OPTION, null );
		if ( is_array( $state ) && array_key_exists( 'complete', $state ) ) {
			return;
		}

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Bounded read inside the owning persistence/workflow boundary; mutable operational state is intentionally not object-cached.
		$max_id = (int) $wpdb->get_var( $wpdb->prepare( 'SELECT COALESCE(MAX(id),0) FROM %i', self::table() ) );
		update_option(
			self::AGGREGATE_STATE_OPTION,
			array(
				'complete'   => 0 === $max_id,
				'max_id'     => $max_id,
				'last_id'    => 0,
				'started_at' => current_time( 'mysql', true ),
				'updated_at' => current_time( 'mysql', true ),
			),
			false
		);
	}

	/**
	 * Ensure the background aggregate migration is scheduled when required.
	 */
	public static function ensure_aggregate_backfill_scheduled() {
		$state = self::aggregate_state();
		if ( ! empty( $state['complete'] ) ) {
			return;
		}
		if ( ! wp_next_scheduled( 'ai_o_optimiser_visibility_aggregate_backfill' ) ) {
			wp_schedule_single_event( time() + 30, 'ai_o_optimiser_visibility_aggregate_backfill' );
		}
	}

	/** Acquire the aggregate migration lock, recovering stale runs. */
	private static function acquire_aggregate_backfill_lock() {
		$key = 'ai_o_optimiser_visibility_aggregate_backfill_lock';
		$now = time();
		if ( add_option( $key, $now, '', false ) ) {
			return true;
		}
		$locked_at = (int) get_option( $key, 0 );
		if ( $locked_at > 0 && $locked_at < ( $now - ( 5 * MINUTE_IN_SECONDS ) ) ) {
			delete_option( $key );
			return add_option( $key, $now, '', false );
		}
		return false;
	}

	/** Release the aggregate migration lock. */
	private static function release_aggregate_backfill_lock() {
		delete_option( 'ai_o_optimiser_visibility_aggregate_backfill_lock' );
	}

	/**
	 * Backfill a limited snapshot batch. New requests are aggregated at insert
	 * time, so this migration only needs to process rows that existed when the
	 * aggregate table was first introduced.
	 *
	 * @param int $batch_size Raw rows to inspect per pass.
	 */
	public static function process_aggregate_backfill( $batch_size = 5000 ) {
		if ( ! self::acquire_aggregate_backfill_lock() ) {
			self::ensure_aggregate_backfill_scheduled();
			return;
		}
		try {
			self::process_aggregate_backfill_batch( $batch_size );
		} finally {
			self::release_aggregate_backfill_lock();
		}
	}

	/** Process one bounded aggregate backfill batch after the migration lock is held. */
	private static function process_aggregate_backfill_batch( $batch_size = 5000 ) {
		global $wpdb;

		$state = self::aggregate_state();
		if ( ! empty( $state['complete'] ) ) {
			return;
		}

		$max_id  = isset( $state['max_id'] ) ? (int) $state['max_id'] : 0;
		$last_id = isset( $state['last_id'] ) ? (int) $state['last_id'] : 0;
		if ( $max_id <= 0 || $last_id >= $max_id ) {
			$state['complete']   = true;
			$state['updated_at'] = current_time( 'mysql', true );
			update_option( self::AGGREGATE_STATE_OPTION, $state, false );
			self::cleanup_old_rows();
			return;
		}

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Bounded read inside the owning persistence/workflow boundary; mutable operational state is intentionally not object-cached.
		$rows = $wpdb->get_results(
			$wpdb->prepare(
				'SELECT id, hit_time, source_type, source_slug, source_label, bot_slug, bot_label, request_uri, response_code, verified FROM %i WHERE id > %d AND id <= %d ORDER BY id ASC LIMIT %d',
				self::table(),
				$last_id,
				$max_id,
				max( 100, min( 10000, (int) $batch_size ) )
			),
			ARRAY_A
		);

		if ( empty( $rows ) ) {
			$state['last_id']    = $max_id;
			$state['complete']   = true;
			$state['updated_at'] = current_time( 'mysql', true );
			update_option( self::AGGREGATE_STATE_OPTION, $state, false );
			self::cleanup_old_rows();
			return;
		}

		$buckets = array();
		$new_last_id = $last_id;
		foreach ( $rows as $row ) {
			$new_last_id = max( $new_last_id, (int) $row['id'] );
			$bucket = self::normalise_aggregate_row( $row );
			$key    = implode( '|', array( $bucket['site_day'], $bucket['source_type'], $bucket['source_slug'], $bucket['bot_slug'], $bucket['request_hash'] ) );
			if ( ! isset( $buckets[ $key ] ) ) {
				$bucket['total']              = 0;
				$bucket['ok_increment']       = 0;
				$bucket['verified_increment'] = 0;
				$buckets[ $key ]              = $bucket;
			}
			$buckets[ $key ]['total']++;
			if ( self::is_successful_response( isset( $row['response_code'] ) ? $row['response_code'] : null ) ) {
				$buckets[ $key ]['ok_increment']++;
			}
			if ( 'verified' === self::normalise_verdict( isset( $row['verified'] ) ? $row['verified'] : '' ) ) {
				$buckets[ $key ]['verified_increment']++;
			}
			if ( $bucket['last_seen'] > $buckets[ $key ]['last_seen'] ) {
				$buckets[ $key ]['last_seen'] = $bucket['last_seen'];
			}
		}

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Intentional mutation of plugin-owned persistence state; caching the write would be incorrect.
		$wpdb->query( 'START TRANSACTION' );
		try {
			foreach ( $buckets as $bucket ) {
				self::upsert_aggregate_bucket( $bucket, (int) $bucket['total'] );
			}
			$state['last_id']    = $new_last_id;
			$state['complete']   = $new_last_id >= $max_id;
			$state['updated_at'] = current_time( 'mysql', true );
			update_option( self::AGGREGATE_STATE_OPTION, $state, false );
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Intentional mutation of plugin-owned persistence state; caching the write would be incorrect.
			$wpdb->query( 'COMMIT' );
		} catch ( Throwable $e ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Intentional mutation of plugin-owned persistence state; caching the write would be incorrect.
			$wpdb->query( 'ROLLBACK' );
			if ( defined( 'WP_DEBUG' ) && WP_DEBUG ) {
				error_log( 'AI-O Optimiser aggregate migration: ' . $e->getMessage() ); // phpcs:ignore WordPress.PHP.DevelopmentFunctions.error_log_error_log,Squiz.PHP.DiscouragedFunctions.Discouraged -- WP_DEBUG-guarded diagnostic for a migration that rolled back; there is no admin screen at this point in the request.
			}
		}

		if ( empty( $state['complete'] ) ) {
			wp_schedule_single_event( time() + 30, 'ai_o_optimiser_visibility_aggregate_backfill' );
		} else {
			self::cleanup_old_rows();
		}
	}

	/**
	 * Current aggregate migration state.
	 *
	 * @return array
	 */
	public static function aggregate_state() {
		$state = get_option( self::AGGREGATE_STATE_OPTION, array() );
		return is_array( $state ) ? wp_parse_args( $state, array( 'complete' => false, 'max_id' => 0, 'last_id' => 0, 'started_at' => '', 'updated_at' => '' ) ) : array( 'complete' => false, 'max_id' => 0, 'last_id' => 0, 'started_at' => '', 'updated_at' => '' );
	}

	/**
	 * Whether long-term dashboard queries can use compact aggregate data.
	 *
	 * @return bool
	 */
	public static function aggregates_ready() {
		$state = self::aggregate_state();
		return ! empty( $state['complete'] ) && self::table_exists( self::aggregate_table() );
	}

	/**
	 * Insert one detailed hit and increment its compact daily aggregate.
	 *
	 * @param array $row Associative array of column => value.
	 * @return int|false Inserted raw row ID, or false on failure.
	 */
	public static function insert( $row ) {
		global $wpdb;

		$source_type  = isset( $row['source_type'] ) ? sanitize_key( $row['source_type'] ) : 'crawler';
		$source_slug  = isset( $row['source_slug'] ) ? sanitize_key( $row['source_slug'] ) : ( isset( $row['bot_slug'] ) ? sanitize_key( $row['bot_slug'] ) : '' );
		$source_label = isset( $row['source_label'] ) ? sanitize_text_field( $row['source_label'] ) : ( isset( $row['bot_label'] ) ? sanitize_text_field( $row['bot_label'] ) : '' );

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- Intentional mutation of plugin-owned persistence state; caching the write would be incorrect.
		$inserted = $wpdb->insert(
			self::table(),
			array(
				'hit_time'     => $row['hit_time'],
				'source_type'  => $source_type,
				'source_slug'  => $source_slug,
				'source_label' => $source_label,
				'bot_slug'     => isset( $row['bot_slug'] ) ? $row['bot_slug'] : '',
				'bot_label'    => isset( $row['bot_label'] ) ? $row['bot_label'] : '',
				'request_uri'  => $row['request_uri'],
				'referrer'     => isset( $row['referrer'] ) ? $row['referrer'] : '',
				'ip_hash'      => isset( $row['ip_hash'] ) ? $row['ip_hash'] : '',
				'user_agent'   => isset( $row['user_agent'] ) ? $row['user_agent'] : '',
				'response_code'  => self::normalise_response_code( isset( $row['response_code'] ) ? $row['response_code'] : null ),
				'request_method' => self::normalise_request_method( isset( $row['request_method'] ) ? $row['request_method'] : '' ),
				'verified'       => self::normalise_verdict( isset( $row['verified'] ) ? $row['verified'] : '' ),
			),
			array( '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%d', '%s', '%s' )
		);

		if ( ! $inserted ) {
			return false;
		}

		$raw_insert_id = (int) $wpdb->insert_id;
		$aggregate_row = $row;
		$aggregate_row['source_type']  = $source_type;
		$aggregate_row['source_slug']  = $source_slug;
		$aggregate_row['source_label'] = $source_label;
		self::upsert_aggregate_bucket( self::normalise_aggregate_row( $aggregate_row ), 1 );

		return $raw_insert_id;
	}

	/**
	 * Clamp a captured HTTP status to a storable value.
	 *
	 * PHP reports 0 when no status has been set, and a plugin or theme can set
	 * anything at all, so a value outside the HTTP range is stored as unknown
	 * rather than as a status nobody sent.
	 *
	 * @param mixed $code Captured status.
	 * @return int|null
	 */
	public static function normalise_response_code( $code ) {
		$code = (int) $code;
		return ( $code >= 100 && $code <= 599 ) ? $code : null;
	}

	/**
	 * Normalise a captured request method, or store nothing.
	 *
	 * Stripping the characters that do not belong would splice the remainder of
	 * a crafted header onto the front of it -- "GET\r\nX-Injected: 1" becoming
	 * "GETXINJECT", a method nobody sent, recorded as though they had. HTTP
	 * methods are plain alphabetic tokens, so anything that is not one is stored
	 * as unknown instead of being repaired into something plausible.
	 *
	 * @param mixed $method Captured request method.
	 * @return string
	 */
	public static function normalise_request_method( $method ) {
		$method = strtoupper( trim( (string) $method ) );
		return preg_match( '/^[A-Z]{1,10}$/', $method ) ? $method : '';
	}

	/**
	 * Clamp a verification verdict to one the column recognises.
	 *
	 * @param mixed $verdict Verdict.
	 * @return string
	 */
	public static function normalise_verdict( $verdict ) {
		$verdict = sanitize_key( (string) $verdict );
		return in_array( $verdict, array( 'verified', 'unverified', 'unverifiable' ), true ) ? $verdict : '';
	}

	/**
	 * Whether a captured status means the crawler actually received the page.
	 *
	 * A crawler probing URLs that do not exist produces a row for every miss. In
	 * a count of "pages an AI system read" those rows are noise, so 2xx is
	 * tracked separately rather than being the only thing counted -- the misses
	 * are worth seeing, just not worth conflating with reads.
	 *
	 * An unknown status counts as successful: rows written before this column
	 * existed have none, and re-labelling that history as failed would be a
	 * worse error than treating it as it was already reported.
	 *
	 * @param int|null $code Response code.
	 * @return bool
	 */
	public static function is_successful_response( $code ) {
		$code = self::normalise_response_code( $code );
		if ( null === $code ) {
			return true;
		}
		return $code >= 200 && $code < 300;
	}

	/**
	 * Normalise a hit into one daily aggregate bucket.
	 *
	 * @param array $row Raw hit-like row.
	 * @return array
	 */
	private static function normalise_aggregate_row( $row ) {
		$hit_time = isset( $row['hit_time'] ) ? (string) $row['hit_time'] : current_time( 'mysql', true );
		$path     = isset( $row['request_uri'] ) ? substr( (string) $row['request_uri'], 0, 500 ) : '/';
		$site_day = get_date_from_gmt( $hit_time, 'Y-m-d' );
		if ( ! $site_day ) {
			$site_day = current_time( 'Y-m-d' );
		}
		return array(
			'site_day'     => $site_day,
			'source_type'  => sanitize_key( isset( $row['source_type'] ) ? $row['source_type'] : 'crawler' ),
			'source_slug'  => sanitize_key( isset( $row['source_slug'] ) ? $row['source_slug'] : '' ),
			'source_label' => sanitize_text_field( isset( $row['source_label'] ) ? $row['source_label'] : '' ),
			'bot_slug'     => sanitize_key( isset( $row['bot_slug'] ) ? $row['bot_slug'] : '' ),
			'bot_label'    => sanitize_text_field( isset( $row['bot_label'] ) ? $row['bot_label'] : '' ),
			'request_hash' => md5( $path ),
			'request_uri'  => $path,
			'last_seen'    => $hit_time,
			/*
			 * Carried alongside the count rather than inside the bucket key: a
			 * status in the key would multiply every bucket by the number of
			 * statuses seen that day, for a segment the dashboard only ever
			 * shows as a proportion.
			 */
			'ok_increment' => self::is_successful_response( isset( $row['response_code'] ) ? $row['response_code'] : null ) ? 1 : 0,
			/*
			 * Only a proved identity counts. Both "the check failed" and "there
			 * was nothing to check against" are absences of proof, and a
			 * dashboard that added them to the verified figure would report the
			 * opposite of what it means.
			 */
			'verified_increment' => 'verified' === self::normalise_verdict( isset( $row['verified'] ) ? $row['verified'] : '' ) ? 1 : 0,
		);
	}

	/**
	 * Increment one aggregate bucket.
	 *
	 * @param array $bucket Normalised bucket.
	 * @param int   $increment Count to add.
	 */
	private static function upsert_aggregate_bucket( $bucket, $increment ) {
		global $wpdb;
		$table = self::aggregate_table();
		$increment = max( 1, (int) $increment );
		/*
		 * One hit contributes at most one successful hit. A backfilled batch
		 * carries its own count of successes, and never more than the batch.
		 */
		$ok_increment = array_key_exists( 'ok_increment', $bucket ) ? (int) $bucket['ok_increment'] : $increment;
		$ok_increment = max( 0, min( $increment, $ok_increment ) );
		// Unlike delivered hits, an unknown verdict is not a verified one, so a
		// bucket with no verdict information contributes nothing here.
		$verified_increment = array_key_exists( 'verified_increment', $bucket ) ? (int) $bucket['verified_increment'] : 0;
		$verified_increment = max( 0, min( $increment, $verified_increment ) );
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Intentional upsert of the plugin-owned daily aggregate; caching the write would be incorrect.
		$wpdb->query(
			$wpdb->prepare(
				"INSERT INTO %i (site_day, source_type, source_slug, source_label, bot_slug, bot_label, request_hash, request_uri, total, ok_total, verified_total, last_seen)
				VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%d,%d,%d,%s)
				ON DUPLICATE KEY UPDATE total = total + VALUES(total), ok_total = ok_total + VALUES(ok_total), verified_total = verified_total + VALUES(verified_total), last_seen = GREATEST(last_seen, VALUES(last_seen)), source_label = VALUES(source_label), bot_label = VALUES(bot_label), request_uri = VALUES(request_uri)",
				$table,
				$bucket['site_day'],
				$bucket['source_type'],
				$bucket['source_slug'],
				$bucket['source_label'],
				$bucket['bot_slug'],
				$bucket['bot_label'],
				$bucket['request_hash'],
				$bucket['request_uri'],
				$increment,
				$ok_increment,
				$verified_increment,
				$bucket['last_seen']
			)
		);
	}

	/**
	 * Earliest local day this site has stored visibility data for.
	 *
	 * Used to tell a period with no visits apart from a period before tracking
	 * (or before retention cleanup) when comparing against earlier dates.
	 *
	 * @return string Y-m-d, or '' when nothing is stored.
	 */
	public static function earliest_day() {
		global $wpdb;
		if ( self::aggregates_ready() ) {
			// phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter,WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Fixed query with a prepared table identifier. Bounded read inside the owning persistence boundary; mutable operational state is intentionally not object-cached.
			$day = $wpdb->get_var( $wpdb->prepare( 'SELECT MIN(site_day) FROM %i', self::aggregate_table() ) );
			return $day ? (string) $day : '';
		}
		// phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter,WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Fixed query with a prepared table identifier. Bounded read inside the owning persistence boundary; mutable operational state is intentionally not object-cached.
		$time = $wpdb->get_var( $wpdb->prepare( 'SELECT MIN(hit_time) FROM %i', self::table() ) );
		return $time ? (string) get_date_from_gmt( $time, 'Y-m-d' ) : '';
	}

	/**
	 * Total hit count.
	 *
	 * @param array $args Filter args.
	 * @return int
	 */
	public static function count( $args = array() ) {
		global $wpdb;
		if ( empty( $args['raw'] ) && self::aggregates_ready() ) {
			list( $where, $params ) = self::build_aggregate_where( $args );
			$sql = "SELECT COALESCE(SUM(total),0) FROM %i {$where}";
			// phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter,WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- SQL structure is assembled only from fixed internal fragments and WordPress placeholders; all values and identifiers are prepared. Bounded read inside the owning persistence/workflow boundary; mutable operational state is intentionally not object-cached.
			return (int) $wpdb->get_var( $wpdb->prepare( $sql, array_merge( array( self::aggregate_table() ), $params ) ) );
		}

		list( $where, $params ) = self::build_where( $args );
		$sql = "SELECT COUNT(*) FROM %i {$where}";
		// phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter,WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- SQL structure is assembled only from fixed internal fragments and WordPress placeholders; all values and identifiers are prepared. Bounded read inside the owning persistence/workflow boundary; mutable operational state is intentionally not object-cached.
		return (int) $wpdb->get_var( $wpdb->prepare( $sql, array_merge( array( self::table() ), $params ) ) );
	}

	/**
	 * How many of those hits actually received the page.
	 *
	 * Reported next to the total rather than instead of it. A crawler that
	 * requested 400 URLs and was answered 404 on 380 of them did visit 400
	 * times, and hiding that would misreport the crawl; presenting it as 400
	 * pages read would misreport the reading. Both numbers are shown.
	 *
	 * @param array $args Filter args.
	 * @return int
	 */
	public static function count_successful( $args = array() ) {
		global $wpdb;
		if ( empty( $args['raw'] ) && self::aggregates_ready() ) {
			list( $where, $params ) = self::build_aggregate_where( $args );
			$sql = "SELECT COALESCE(SUM(ok_total),0) FROM %i {$where}";
			// phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter,WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- SQL structure is assembled only from fixed internal fragments and WordPress placeholders; all values and identifiers are prepared. Bounded read inside the owning persistence/workflow boundary; mutable operational state is intentionally not object-cached.
			return (int) $wpdb->get_var( $wpdb->prepare( $sql, array_merge( array( self::aggregate_table() ), $params ) ) );
		}

		list( $where, $params ) = self::build_where( $args );
		// A row written before this column existed has no status. It is counted
		// as delivered, matching how it was already being reported.
		$delivered = '(response_code IS NULL OR (response_code >= 200 AND response_code < 300))';
		// build_where() returns an empty string when nothing was filtered, so the
		// clause has to open the WHERE itself rather than assume one is there.
		$where = '' === $where ? 'WHERE ' . $delivered : $where . ' AND ' . $delivered;
		$sql   = "SELECT COUNT(*) FROM %i {$where}";
		// phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter,WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- SQL structure is assembled only from fixed internal fragments and WordPress placeholders; all values and identifiers are prepared. Bounded read inside the owning persistence/workflow boundary; mutable operational state is intentionally not object-cached.
		return (int) $wpdb->get_var( $wpdb->prepare( $sql, array_merge( array( self::table() ), $params ) ) );
	}

	/**
	 * How many of those hits proved they came from the vendor they claimed.
	 *
	 * Counts only a positive proof. A request the check could not judge is not
	 * evidence of a spoof and is not evidence of authenticity either, so it
	 * belongs in neither the verified figure nor a failure figure derived from
	 * subtracting it.
	 *
	 * @param array $args Filter args.
	 * @return int
	 */
	public static function count_verified( $args = array() ) {
		global $wpdb;
		if ( empty( $args['raw'] ) && self::aggregates_ready() ) {
			list( $where, $params ) = self::build_aggregate_where( $args );
			$sql = "SELECT COALESCE(SUM(verified_total),0) FROM %i {$where}";
			// phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter,WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- SQL structure is assembled only from fixed internal fragments and WordPress placeholders; all values and identifiers are prepared. Bounded read inside the owning persistence/workflow boundary; mutable operational state is intentionally not object-cached.
			return (int) $wpdb->get_var( $wpdb->prepare( $sql, array_merge( array( self::aggregate_table() ), $params ) ) );
		}

		list( $where, $params ) = self::build_where( $args );
		$clause = "verified = 'verified'";
		$where  = '' === $where ? 'WHERE ' . $clause : $where . ' AND ' . $clause;
		$sql    = "SELECT COUNT(*) FROM %i {$where}";
		// phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter,WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- SQL structure is assembled only from fixed internal fragments and WordPress placeholders; all values and identifiers are prepared. Bounded read inside the owning persistence/workflow boundary; mutable operational state is intentionally not object-cached.
		return (int) $wpdb->get_var( $wpdb->prepare( $sql, array_merge( array( self::table() ), $params ) ) );
	}

	/** Hits grouped by bot. */
	public static function totals_by_bot( $args = array() ) {
		global $wpdb;
		$args['source_type'] = 'crawler';
		if ( self::aggregates_ready() ) {
			list( $where, $params ) = self::build_aggregate_where( $args );
			$sql = "SELECT bot_slug, MAX(bot_label) AS bot_label, SUM(total) AS total, MAX(last_seen) AS last_seen FROM %i {$where} GROUP BY bot_slug ORDER BY total DESC";
			// phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter,WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- SQL structure is assembled only from fixed internal fragments and WordPress placeholders; all values and identifiers are prepared. Bounded read inside the owning persistence/workflow boundary; mutable operational state is intentionally not object-cached.
			return $wpdb->get_results( $wpdb->prepare( $sql, array_merge( array( self::aggregate_table() ), $params ) ) );
		}
		list( $where, $params ) = self::build_where( $args );
		$sql = "SELECT bot_slug, bot_label, COUNT(*) AS total, MAX(hit_time) AS last_seen FROM %i {$where} GROUP BY bot_slug, bot_label ORDER BY total DESC";
		// phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter,WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- SQL structure is assembled only from fixed internal fragments and WordPress placeholders; all values and identifiers are prepared. Bounded read inside the owning persistence/workflow boundary; mutable operational state is intentionally not object-cached.
		return $wpdb->get_results( $wpdb->prepare( $sql, array_merge( array( self::table() ), $params ) ) );
	}

	/** Hits grouped by AI referral source. */
	public static function totals_by_referrer_source( $args = array() ) {
		global $wpdb;
		$args['source_type'] = 'referral';
		if ( self::aggregates_ready() ) {
			list( $where, $params ) = self::build_aggregate_where( $args );
			$sql = "SELECT source_slug, MAX(source_label) AS source_label, SUM(total) AS total, MAX(last_seen) AS last_seen FROM %i {$where} GROUP BY source_slug ORDER BY total DESC";
			// phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter,WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- SQL structure is assembled only from fixed internal fragments and WordPress placeholders; all values and identifiers are prepared. Bounded read inside the owning persistence/workflow boundary; mutable operational state is intentionally not object-cached.
			return $wpdb->get_results( $wpdb->prepare( $sql, array_merge( array( self::aggregate_table() ), $params ) ) );
		}
		list( $where, $params ) = self::build_where( $args );
		$sql = "SELECT source_slug, source_label, COUNT(*) AS total, MAX(hit_time) AS last_seen FROM %i {$where} GROUP BY source_slug, source_label ORDER BY total DESC";
		// phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter,WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- SQL structure is assembled only from fixed internal fragments and WordPress placeholders; all values and identifiers are prepared. Bounded read inside the owning persistence/workflow boundary; mutable operational state is intentionally not object-cached.
		return $wpdb->get_results( $wpdb->prepare( $sql, array_merge( array( self::table() ), $params ) ) );
	}

	/** Hits grouped by AI guidance file. */
	public static function totals_by_guidance_file( $args = array() ) {
		global $wpdb;
		$args['source_type'] = 'guidance';
		if ( self::aggregates_ready() ) {
			list( $where, $params ) = self::build_aggregate_where( $args );
			$sql = "SELECT source_slug, MAX(source_label) AS source_label, SUM(total) AS total, MAX(last_seen) AS last_seen FROM %i {$where} GROUP BY source_slug ORDER BY total DESC";
			// phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter,WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- SQL structure is assembled only from fixed internal fragments and WordPress placeholders; all values and identifiers are prepared. Bounded read inside the owning persistence/workflow boundary; mutable operational state is intentionally not object-cached.
			return $wpdb->get_results( $wpdb->prepare( $sql, array_merge( array( self::aggregate_table() ), $params ) ) );
		}
		list( $where, $params ) = self::build_where( $args );
		$sql = "SELECT source_slug, source_label, COUNT(*) AS total, MAX(hit_time) AS last_seen FROM %i {$where} GROUP BY source_slug, source_label ORDER BY total DESC";
		// phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter,WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- SQL structure is assembled only from fixed internal fragments and WordPress placeholders; all values and identifiers are prepared. Bounded read inside the owning persistence/workflow boundary; mutable operational state is intentionally not object-cached.
		return $wpdb->get_results( $wpdb->prepare( $sql, array_merge( array( self::table() ), $params ) ) );
	}

	/** Most-visited pages. */
	public static function top_pages( $args = array() ) {
		global $wpdb;
		$limit = isset( $args['limit'] ) ? max( 1, (int) $args['limit'] ) : 10;
		if ( self::aggregates_ready() ) {
			list( $where, $params ) = self::build_aggregate_where( $args );
			$sql = "SELECT request_uri, SUM(total) AS total FROM %i {$where} GROUP BY request_hash, request_uri ORDER BY total DESC LIMIT %d";
			$params[] = $limit;
			// phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter,WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- SQL structure is assembled only from fixed internal fragments and WordPress placeholders; all values and identifiers are prepared. Bounded read inside the owning persistence/workflow boundary; mutable operational state is intentionally not object-cached.
			return $wpdb->get_results( $wpdb->prepare( $sql, array_merge( array( self::aggregate_table() ), $params ) ) );
		}
		list( $where, $params ) = self::build_where( $args );
		$sql = "SELECT request_uri, COUNT(*) AS total FROM %i {$where} GROUP BY request_uri ORDER BY total DESC LIMIT %d";
		$params[] = $limit;
		// phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter,WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- SQL structure is assembled only from fixed internal fragments and WordPress placeholders; all values and identifiers are prepared. Bounded read inside the owning persistence/workflow boundary; mutable operational state is intentionally not object-cached.
		return $wpdb->get_results( $wpdb->prepare( $sql, array_merge( array( self::table() ), $params ) ) );
	}

	/**
	 * Daily hit counts for a local WordPress date range, including zero-hit days.
	 */
	public static function daily_counts_for_range( $since_utc, $until_utc, $local_start, $local_end, $args = array() ) {
		global $wpdb;

		if ( empty( $since_utc ) || empty( $until_utc ) ) {
			$timezone    = wp_timezone();
			$end         = new DateTimeImmutable( current_time( 'Y-m-d' ), $timezone );
			$end         = $end->modify( '-1 day' );
			$start       = $end->modify( '-29 days' );
			$local_start = $start->format( 'Y-m-d' );
			$local_end   = $end->format( 'Y-m-d' );
			$since_utc   = $start->setTime( 0, 0, 0 )->setTimezone( new DateTimeZone( 'UTC' ) )->format( 'Y-m-d H:i:s' );
			$until_utc   = $end->setTime( 23, 59, 59 )->setTimezone( new DateTimeZone( 'UTC' ) )->format( 'Y-m-d H:i:s' );
		}

		$result   = array();
		$timezone = wp_timezone();
		$start    = new DateTimeImmutable( $local_start . ' 00:00:00', $timezone );
		$end      = new DateTimeImmutable( $local_end . ' 00:00:00', $timezone );
		for ( $day = $start; $day <= $end; $day = $day->modify( '+1 day' ) ) {
			$result[ $day->format( 'Y-m-d' ) ] = 0;
		}

		if ( self::aggregates_ready() ) {
			$args['since'] = $since_utc;
			$args['until'] = $until_utc;
			list( $where, $params ) = self::build_aggregate_where( $args );
			$sql = "SELECT site_day, SUM(total) AS total FROM %i {$where} GROUP BY site_day ORDER BY site_day ASC";
			// phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter,WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- SQL structure is assembled only from fixed internal fragments and WordPress placeholders; all values and identifiers are prepared. Bounded read inside the owning persistence/workflow boundary; mutable operational state is intentionally not object-cached.
			$rows = $wpdb->get_results( $wpdb->prepare( $sql, array_merge( array( self::aggregate_table() ), $params ) ) );
			foreach ( $rows as $row ) {
				if ( isset( $result[ $row->site_day ] ) ) {
					$result[ $row->site_day ] = (int) $row->total;
				}
			}
			return $result;
		}

		$args['since'] = $since_utc;
		$args['until'] = $until_utc;
		list( $where, $params ) = self::build_where( $args );
		$sql = "SELECT hit_time FROM %i {$where} ORDER BY hit_time ASC";
		// phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter,WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- SQL structure is assembled only from fixed internal fragments and WordPress placeholders; all values and identifiers are prepared. Bounded read inside the owning persistence/workflow boundary; mutable operational state is intentionally not object-cached.
		$hit_times = $wpdb->get_col( $wpdb->prepare( $sql, array_merge( array( self::table() ), $params ) ) );
		foreach ( $hit_times as $hit_time ) {
			$day = ( new DateTimeImmutable( $hit_time, new DateTimeZone( 'UTC' ) ) )->setTimezone( $timezone )->format( 'Y-m-d' );
			if ( isset( $result[ $day ] ) ) {
				$result[ $day ]++;
			}
		}
		return $result;
	}

	/** Paginated detailed log rows for the Diagnostics screen. */
	public static function get_rows( $args = array() ) {
		global $wpdb;
		$page     = isset( $args['page'] ) ? max( 1, (int) $args['page'] ) : 1;
		$per_page = isset( $args['per_page'] ) ? max( 1, (int) $args['per_page'] ) : 50;
		$offset   = ( $page - 1 ) * $per_page;
		list( $where, $params ) = self::build_where( $args );
		$sql = "SELECT * FROM %i {$where} ORDER BY hit_time DESC LIMIT %d OFFSET %d";
		$params[] = $per_page;
		$params[] = $offset;
		// phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter,WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- SQL structure is assembled only from fixed internal fragments and WordPress placeholders; all values and identifiers are prepared. Bounded read inside the owning persistence/workflow boundary; mutable operational state is intentionally not object-cached.
		return $wpdb->get_results( $wpdb->prepare( $sql, array_merge( array( self::table() ), $params ) ) );
	}

	/** All detailed rows matching filters — used for CSV export. */
	public static function get_all_rows( $args = array() ) {
		global $wpdb;
		list( $where, $params ) = self::build_where( $args );
		$sql = "SELECT * FROM %i {$where} ORDER BY hit_time DESC";
		// phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter,WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- SQL structure is assembled only from fixed internal fragments and WordPress placeholders; all values and identifiers are prepared. Bounded read inside the owning persistence/workflow boundary; mutable operational state is intentionally not object-cached.
		return $wpdb->get_results( $wpdb->prepare( $sql, array_merge( array( self::table() ), $params ) ) );
	}

	/** Shared WHERE builder for detailed rows. */
	private static function build_where( $args ) {
		$clauses = array();
		$params  = array();
		self::append_common_filters( $args, $clauses, $params, false );
		if ( ! empty( $args['since'] ) ) {
			$clauses[] = 'hit_time >= %s';
			$params[]  = false === strpos( $args['since'], ' ' ) ? $args['since'] . ' 00:00:00' : $args['since'];
		}
		if ( ! empty( $args['until'] ) ) {
			$clauses[] = 'hit_time <= %s';
			$params[]  = false === strpos( $args['until'], ' ' ) ? $args['until'] . ' 23:59:59' : $args['until'];
		}
		return array( $clauses ? 'WHERE ' . implode( ' AND ', $clauses ) : '', $params );
	}

	/** Shared WHERE builder for daily aggregate rows. */
	private static function build_aggregate_where( $args ) {
		$clauses = array();
		$params  = array();
		self::append_common_filters( $args, $clauses, $params, true );
		if ( ! empty( $args['since'] ) ) {
			$since = false === strpos( $args['since'], ' ' ) ? $args['since'] . ' 00:00:00' : $args['since'];
			$clauses[] = 'site_day >= %s';
			$params[]  = get_date_from_gmt( $since, 'Y-m-d' );
		}
		if ( ! empty( $args['until'] ) ) {
			$until = false === strpos( $args['until'], ' ' ) ? $args['until'] . ' 23:59:59' : $args['until'];
			$clauses[] = 'site_day <= %s';
			$params[]  = get_date_from_gmt( $until, 'Y-m-d' );
		}
		return array( $clauses ? 'WHERE ' . implode( ' AND ', $clauses ) : '', $params );
	}

	/** Add source/bot filters shared by raw and aggregate queries. */
	private static function append_common_filters( $args, &$clauses, &$params, $aggregate ) {
		if ( ! empty( $args['source_type'] ) ) {
			$source_type = sanitize_key( $args['source_type'] );
			if ( in_array( $source_type, array( 'crawler', 'referral', 'guidance' ), true ) ) {
				$clauses[] = 'source_type = %s';
				$params[]  = $source_type;
			}
		}
		if ( ! empty( $args['source_slug'] ) ) {
			$clauses[] = 'source_slug = %s';
			$params[]  = sanitize_key( $args['source_slug'] );
		}
		if ( ! empty( $args['bot_slug'] ) ) {
			$clauses[] = 'bot_slug = %s';
			$params[]  = sanitize_key( $args['bot_slug'] );
		} elseif ( array_key_exists( 'bot_slugs', $args ) && is_array( $args['bot_slugs'] ) ) {
			$bot_slugs = array_filter( array_map( 'sanitize_key', $args['bot_slugs'] ) );
			if ( ! empty( $bot_slugs ) ) {
				$placeholders = implode( ', ', array_fill( 0, count( $bot_slugs ), '%s' ) );
				$clauses[]    = "bot_slug IN ({$placeholders})";
				$params       = array_merge( $params, $bot_slugs );
			} else {
				$clauses[] = '1 = 0';
			}
		}
	}

	/**
	 * Enforce short raw diagnostics plus long compact aggregate retention.
	 */
	public static function cleanup_old_rows() {
		global $wpdb;

		// Do not remove source rows until the one-time aggregate backfill completes.
		if ( self::aggregates_ready() ) {
			$settings = wp_parse_args( get_option( 'ai_o_optimiser_settings', array() ), AI_O_Optimiser_Settings::defaults() );
			$raw_days = isset( $settings['raw_retention_days'] ) ? max( 1, min( 30, (int) $settings['raw_retention_days'] ) ) : 14;
			$raw_cutoff = gmdate( 'Y-m-d H:i:s', time() - ( $raw_days * DAY_IN_SECONDS ) );
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Intentional mutation of plugin-owned persistence state; caching the write would be incorrect.
			$wpdb->query( $wpdb->prepare( 'DELETE FROM %i WHERE hit_time < %s', self::table(), $raw_cutoff ) );
			self::trim_oldest_rows( self::table(), self::RAW_ROW_CAP );

			$aggregate_days = isset( $settings['aggregate_retention_days'] ) ? max( 90, min( 1095, (int) $settings['aggregate_retention_days'] ) ) : 730;
			$aggregate_cutoff = wp_date( 'Y-m-d', time() - ( $aggregate_days * DAY_IN_SECONDS ), wp_timezone() );
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Intentional mutation of plugin-owned persistence state; caching the write would be incorrect.
			$wpdb->query( $wpdb->prepare( 'DELETE FROM %i WHERE site_day < %s', self::aggregate_table(), $aggregate_cutoff ) );
			self::trim_oldest_rows( self::aggregate_table(), self::AGGREGATE_ROW_CAP );
		}

		self::cleanup_metadata_queue_history();
	}

	/** Keep newest rows in an append-style table. */
	private static function trim_oldest_rows( $table, $cap ) {
		global $wpdb;
		if ( ! self::table_exists( $table ) ) {
			return;
		}
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Bounded read inside the owning persistence/workflow boundary; mutable operational state is intentionally not object-cached.
		$count  = (int) $wpdb->get_var( $wpdb->prepare( 'SELECT COUNT(*) FROM %i', $table ) );
		$excess = max( 0, $count - max( 1, (int) $cap ) );
		if ( $excess <= 0 ) {
			return;
		}
		if ( $table === self::aggregate_table() ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Intentional mutation of plugin-owned persistence state; caching the write would be incorrect.
			$wpdb->query( $wpdb->prepare( 'DELETE FROM %i ORDER BY site_day ASC, id ASC LIMIT %d', $table, $excess ) );
		} else {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Intentional mutation of plugin-owned persistence state; caching the write would be incorrect.
			$wpdb->query( $wpdb->prepare( 'DELETE FROM %i ORDER BY id ASC LIMIT %d', $table, $excess ) );
		}
	}

	/** Bound old terminal Metadata Queue history without touching active work. */
	private static function cleanup_metadata_queue_history() {
		global $wpdb;
		$table = $wpdb->prefix . 'ai_o_optimiser_metadata_queue';
		if ( ! self::table_exists( $table ) ) {
			return;
		}
		$error_cutoff = gmdate( 'Y-m-d H:i:s', time() - ( 90 * DAY_IN_SECONDS ) );
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Intentional mutation of plugin-owned persistence state; caching the write would be incorrect.
		$wpdb->query( $wpdb->prepare( "DELETE FROM %i WHERE status = 'error' AND updated_at < %s", $table, $error_cutoff ) );
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Bounded read inside the owning persistence/workflow boundary; mutable operational state is intentionally not object-cached.
		$terminal_count = (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM %i WHERE status IN ('applied','error')", $table ) );
		$excess = max( 0, $terminal_count - 5000 );
		if ( $excess > 0 ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Intentional mutation of plugin-owned persistence state; caching the write would be incorrect.
			$wpdb->query( $wpdb->prepare( "DELETE FROM %i WHERE status IN ('applied','error') ORDER BY id ASC LIMIT %d", $table, $excess ) );
		}
	}

	/**
	 * Return bounded storage diagnostics for plugin-owned/legacy-compatible data.
	 *
	 * The Settings screen can ask for the same storage snapshot from more than
	 * one component. Cache only for the lifetime of this PHP request so those
	 * components share the expensive table-status/count work without creating
	 * stale cross-request state. Pass true only when the current request has
	 * deliberately changed storage and needs a fresh snapshot.
	 *
	 * @param bool $refresh Recompute even when this request already has a snapshot.
	 * @return array
	 */
	public static function storage_report( $refresh = false ) {
		if ( ! $refresh && is_array( self::$storage_report_request_cache ) ) {
			return self::$storage_report_request_cache;
		}

		global $wpdb;
		$tables = array(
			'ai_o_optimiser_visibility_daily'       => self::aggregate_table(),
			'ai_o_optimiser_crawler_log'            => self::table(),
			'ai_o_optimiser_gsc_rows'               => $wpdb->prefix . 'ai_o_optimiser_gsc_rows',
			'ai_o_optimiser_opportunity_workflow'   => $wpdb->prefix . 'ai_o_optimiser_opportunity_workflow',
			'ai_o_optimiser_metadata_queue'           => $wpdb->prefix . 'ai_o_optimiser_metadata_queue',
			'ai_o_optimiser_metadata_search_rows'     => $wpdb->prefix . 'ai_o_optimiser_metadata_search_rows',
			'ai_o_optimiser_metadata_opportunities'   => $wpdb->prefix . 'ai_o_optimiser_metadata_opportunities',
		);
		$report = array( 'tables' => array(), 'table_bytes' => 0, 'schema_option_bytes' => 0, 'total_bytes' => 0, 'aggregate_state' => self::aggregate_state() );
		foreach ( $tables as $key => $table ) {
			if ( ! self::table_exists( $table ) ) {
				continue;
			}
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Bounded read inside the owning persistence/workflow boundary; mutable operational state is intentionally not object-cached.
			$status = $wpdb->get_row( $wpdb->prepare( 'SHOW TABLE STATUS LIKE %s', $wpdb->esc_like( $table ) ), ARRAY_A );
			$bytes  = is_array( $status ) ? (int) ( ( $status['Data_length'] ?? 0 ) + ( $status['Index_length'] ?? 0 ) ) : 0;
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Bounded read inside the owning persistence/workflow boundary; mutable operational state is intentionally not object-cached.
			$rows   = (int) $wpdb->get_var( $wpdb->prepare( 'SELECT COUNT(*) FROM %i', $table ) );
			$report['tables'][ $key ] = array( 'table' => $table, 'rows' => $rows, 'bytes' => $bytes );
			$report['table_bytes'] += $bytes;
		}

		$schema_options = array( 'ai_o_optimiser_schema_review_cache', 'ai_o_optimiser_schema_review_baseline', 'ai_o_optimiser_schema_review_history', 'ai_o_optimiser_schema_review_scope', 'ai_o_optimiser_schema_review_scope_presets', 'ai_o_optimiser_schema_fallbacks', 'ai_o_optimiser_schema_fallback_payloads' );
		$placeholders   = implode( ',', array_fill( 0, count( $schema_options ), '%s' ) );
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- The interpolated fragment is a placeholder list generated from a fixed option-name array; every value is prepared. Bounded read inside the owning persistence/workflow boundary; mutable operational state is intentionally not object-cached.
		$report['schema_option_bytes'] = (int) $wpdb->get_var( $wpdb->prepare( "SELECT COALESCE(SUM(LENGTH(option_value)),0) FROM %i WHERE option_name IN ({$placeholders})", array_merge( array( $wpdb->options ), $schema_options ) ) );
		$report['total_bytes'] = $report['table_bytes'] + $report['schema_option_bytes'];
		self::$storage_report_request_cache = $report;
		return $report;
	}

	/** Test for an internal table without assuming database-level metadata access. */
	private static function table_exists( $table ) {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Bounded read inside the owning persistence/workflow boundary; mutable operational state is intentionally not object-cached.
		return $table === $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $wpdb->esc_like( $table ) ) );
	}
}