<?php
/**
 * Scan results storage. Custom table holding the LATEST result per audited
 * page - one row per ref ('post:{ID}' for posts, 'url:{sha1}' for synthesized
 * archive pages; history is a possible future feature) - plus cheap summary
 * aggregates in an option for dashboard and future Hub reads.
 *
 * Archive rows store post_id 0, the archive kind in post_type ('home' | 'tax'
 * | 'date'), 'publish' in post_status, and the display label in post_title
 * (SSAA snapshots titles at scan time anyway, so no separate label column is
 * needed - unlike LIHA, which reads post titles live).
 *
 * See docs/DECISIONS.md 2026-06-10 "Scan results in a custom DB table" and
 * docs/SPEC-archive-pages.md (DB v9).
 *
 * @package search-social-appearance-audit
 */

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

class SSAA_Results {

	/** Bump when the schema changes (or a one-time data migration is needed);
	 * install() runs dbDelta + the upgrade backfills on mismatch. v6 is a
	 * data-only migration (no schema change): purge private + password-
	 * protected rows now that those are excluded from the audit. v9 keys rows
	 * by ref instead of post_id so archive pages (post_id 0) can be stored.
	 * v10 adds the ai_rank per-area sort key (the 'ai' issue area, 1.2.0) -
	 * no backfill needed: no stored pre-v10 row can carry an 'ai'-area issue,
	 * v12 adds structure_rank for the 'structure' issue area (heading findings,
 * 1.5.0). Like v10 it is a NEW area, so every existing row correctly has 0 and
 * no backfill is needed - rows gain a real rank on their next scan. All FOUR
 * upsert edit points were touched together (column list, placeholder, ON
 * DUPLICATE clause, bound value) plus the reflag format array; see the
 * upsert-column-add lesson that DB v11 had to repair.
 *
 * so every existing ai_rank is correctly 0. v11 is a data-only migration:
	 * the save() upsert omitted ai_rank from its ON DUPLICATE KEY UPDATE
	 * clause, so re-scanned rows kept whatever ai_rank their first insert
	 * wrote (0 for rows predating v10); reflag rows whose stored ai_rank may
	 * disagree with the (always-current) issues JSON. */
	const DB_VERSION = '12';

	const DB_VERSION_OPTION = 'ssaa_db_version';
	const SUMMARY_OPTION    = 'ssaa_summary';

	// This class is the data layer for the plugin's OWN custom results table
	// ({$wpdb->prefix}ssaa_results). Every query below targets only that table;
	// the table name comes from $wpdb->prefix (never user input); all dynamic
	// values are bound through $wpdb->prepare()/->delete()/->update() with
	// placeholders, and ORDER BY identifiers are whitelisted. Object caching is
	// deliberately not used - these are admin-only audit rows that change on
	// every scan, so a cache would add invalidation cost with no real benefit.
	// The DB sniffs are therefore disabled for the class (same pattern as DOIG,
	// which passed wp.org review); other sniffs still apply.
	// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

	public static function init() {
		// Self-heal: if the plugin was updated (not re-activated) and the
		// schema moved on, upgrade on the next admin request.
		if ( is_admin() && get_option( self::DB_VERSION_OPTION ) !== self::DB_VERSION ) {
			self::install();
		}

		// When a content type is switched off in settings, its stored rows
		// are removed - the tables reflect what is being audited (same
		// semantics as Exclude).
		add_action( 'update_option_' . SSAA_OPTION_KEY, array( __CLASS__, 'prune_disabled_types' ), 10, 2 );
	}

	/**
	 * Delete rows for post types that were just disabled.
	 *
	 * @param mixed $old Old option value.
	 * @param mixed $new New option value.
	 */
	public static function prune_disabled_types( $old, $new ) {
		$old_types = isset( $old['scan_post_types'] ) ? (array) $old['scan_post_types'] : array( 'post', 'page' );
		$new_types = isset( $new['scan_post_types'] ) ? (array) $new['scan_post_types'] : array( 'post', 'page' );

		/**
		 * Extension point: add-on files append their enabled type lists so
		 * disabling one of theirs prunes as well.
		 *
		 * @param string[] $types Enabled types.
		 * @param array    $settings The (old or new) settings array.
		 */
		$old_types = (array) apply_filters( 'ssaa_enabled_types_from_settings', $old_types, (array) $old );
		$new_types = (array) apply_filters( 'ssaa_enabled_types_from_settings', $new_types, (array) $new );

		$disabled = array_diff( $old_types, $new_types );

		// Archive pages behave like a content type here: switching the
		// setting off removes the stored archive rows (same semantics as
		// disabling a post type).
		$old_archives = ! isset( $old['scan_archives'] ) || ! empty( $old['scan_archives'] );
		$new_archives = ! isset( $new['scan_archives'] ) || ! empty( $new['scan_archives'] );
		$archives_off = $old_archives && ! $new_archives;

		if ( empty( $disabled ) && ! $archives_off ) {
			return;
		}

		foreach ( $disabled as $type ) {
			self::delete_by_type( $type );
		}
		if ( $archives_off ) {
			self::delete_archives();
		}
		self::refresh_summary();
		SSAA_Logger::info( 'types_pruned', array(
			'types'    => array_values( $disabled ),
			'archives' => $archives_off,
		) );
	}

	/**
	 * Remove rows last scanned before a given GMT datetime - called when a
	 * full scan completes so leftover rows from items no longer covered
	 * (cap window, status changes) cannot skew the counts.
	 *
	 * @param string $gmt_datetime Y-m-d H:i:s (GMT).
	 */
	public static function prune_scanned_before( $gmt_datetime ) {
		global $wpdb;

		$table = self::table();
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- plugin's own table; value prepared.
		$removed = $wpdb->query( $wpdb->prepare( "DELETE FROM {$table} WHERE scanned_at < %s", $gmt_datetime ) );

		if ( $removed > 0 ) {
			SSAA_Logger::info( 'stale_rows_pruned', array( 'rows' => (int) $removed ) );
		}
	}

	/**
	 * Remove all rows of one post type.
	 *
	 * @param string $type Post type.
	 */
	public static function delete_by_type( $type ) {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- custom table delete.
		$wpdb->delete( self::table(), array( 'post_type' => sanitize_key( $type ) ), array( '%s' ) );
	}

	/**
	 * Row counts per post type (optionally only rows with issues/errors).
	 * Archive rows (post_id 0; post_type holds the archive kind) are bucketed
	 * together under the reserved '__archive__' key - the type dropdown offers
	 * one "Archives" choice, not one per kind (LIHA pattern).
	 *
	 * @param bool $with_issues Restrict to rows with issues or scan errors.
	 * @return array type => count.
	 */
	public static function type_counts( $with_issues = false ) {
		global $wpdb;

		$table = self::table();
		// post_type='site' rides along so the counts match the Findings view,
		// which always lists the Site fact row (see query()).
		$where = $with_issues ? " WHERE issue_count > 0 OR scan_status = 'error' OR post_type = 'site'" : '';

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- aggregate on indexed custom table; no user input.
		$rows = (array) $wpdb->get_results( "SELECT CASE WHEN post_id = 0 THEN '__archive__' ELSE post_type END AS bucket, COUNT(*) AS n FROM {$table}{$where} GROUP BY bucket", ARRAY_A );

		$counts = array();
		foreach ( $rows as $row ) {
			$counts[ (string) $row['bucket'] ] = (int) $row['n'];
		}
		return $counts;
	}

	/**
	 * Distinct-row counts per post_status, for the status views (the WP-standard
	 * get_views() subsubsub tabs). SSAA stores post_status on each row and keeps
	 * one row per post (the post_id UNIQUE key), so a plain COUNT(*) grouped by
	 * the stored column is the distinct-page count - no wp_posts join is needed
	 * (unlike LIHA, whose occurrences table does not carry post_status). Private
	 * never appears: it is excluded from the audit, so it is never stored.
	 *
	 * @param bool $with_issues Restrict to rows with issues or scan errors.
	 * @return array status => count.
	 */
	public static function status_counts( $with_issues = false ) {
		global $wpdb;

		$table = self::table();
		// Matches query()'s Findings clause (the Site fact row always lists).
		$where = $with_issues ? " WHERE issue_count > 0 OR scan_status = 'error' OR post_type = 'site'" : '';

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- aggregate on the plugin's own table; no user input.
		$rows = (array) $wpdb->get_results( "SELECT post_status, COUNT(*) AS n FROM {$table}{$where} GROUP BY post_status", ARRAY_A );

		$counts = array();
		foreach ( $rows as $row ) {
			$counts[ (string) $row['post_status'] ] = (int) $row['n'];
		}
		return $counts;
	}

	/**
	 * Fully-prefixed table name.
	 *
	 * @return string
	 */
	public static function table() {
		global $wpdb;
		return $wpdb->prefix . 'ssaa_results';
	}

	/**
	 * Create or upgrade the table. Safe to call repeatedly (dbDelta).
	 */
	public static function install() {
		global $wpdb;

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

		$sql = "CREATE TABLE {$table} (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			post_id bigint(20) unsigned NOT NULL,
			ref varchar(64) NOT NULL DEFAULT '',
			post_type varchar(20) NOT NULL DEFAULT '',
			post_status varchar(20) NOT NULL DEFAULT '',
			post_title varchar(255) NOT NULL DEFAULT '',
			wp_author varchar(255) NOT NULL DEFAULT '',
			exposed_author varchar(255) NOT NULL DEFAULT '',
			permalink text NOT NULL,
			scan_mode varchar(10) NOT NULL DEFAULT 'standard',
			scan_status varchar(20) NOT NULL DEFAULT 'ok',
			http_status smallint(5) unsigned NOT NULL DEFAULT 0,
			error_code varchar(64) NOT NULL DEFAULT '',
			error_message text NOT NULL,
			seo_title text NOT NULL,
			meta_description text NOT NULL,
			canonical_url text NOT NULL,
			robots varchar(255) NOT NULL DEFAULT '',
			og_title text NOT NULL,
			og_description text NOT NULL,
			og_image_url text NOT NULL,
			twitter_card varchar(32) NOT NULL DEFAULT '',
			issue_count smallint(5) unsigned NOT NULL DEFAULT 0,
			severity_rank int(10) unsigned NOT NULL DEFAULT 0,
			search_rank int(10) unsigned NOT NULL DEFAULT 0,
			social_rank int(10) unsigned NOT NULL DEFAULT 0,
			ai_rank int(10) unsigned NOT NULL DEFAULT 0,
			structure_rank int(10) unsigned NOT NULL DEFAULT 0,
			issue_codes text NOT NULL,
			issue_severities varchar(40) NOT NULL DEFAULT '',
			issues longtext NOT NULL,
			extras longtext NOT NULL,
			scanned_at datetime NOT NULL,
			PRIMARY KEY  (id),
			UNIQUE KEY ref (ref),
			KEY post_id (post_id),
			KEY post_type (post_type),
			KEY issue_count (issue_count),
			KEY severity_rank (severity_rank),
			KEY search_rank (search_rank),
			KEY social_rank (social_rank),
			KEY ai_rank (ai_rank),
			KEY structure_rank (structure_rank),
			KEY scanned_at (scanned_at)
		) {$charset_collate};";

		// DB v9 pre-step (upgrade path only): the ref column must exist and be
		// backfilled BEFORE dbDelta sees the schema above, because dbDelta
		// would otherwise try to build UNIQUE KEY ref while every existing row
		// still holds the '' default - a duplicate-key collision that aborts
		// the index. Fresh installs skip this (no table yet); re-runs skip it
		// (column exists).
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- schema introspection during upgrade.
		$table_exists = (bool) $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $table ) );
		if ( $table_exists ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- schema introspection; table name not user input.
			$has_ref = (bool) $wpdb->get_var( "SHOW COLUMNS FROM {$table} LIKE 'ref'" );
			if ( ! $has_ref ) {
				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- one-time guarded v9 upgrade step on the plugin's OWN table: the column must exist and be backfilled before dbDelta declares UNIQUE KEY ref (dbDelta cannot sequence this); runs only when SHOW COLUMNS says the column is missing.
				$wpdb->query( "ALTER TABLE {$table} ADD COLUMN ref varchar(64) NOT NULL DEFAULT '' AFTER post_id" );
			}
			// Backfill idempotently - pre-v9 rows are all posts (save() used to
			// reject post_id 0), so 'post:{id}' refs are unique by construction.
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- one-time v9 backfill on the plugin's own table.
			$wpdb->query( "UPDATE {$table} SET ref = CONCAT('post:', post_id) WHERE ref = '' AND post_id > 0" );
		}

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

		// DB v9: move the uniqueness constraint from post_id to ref. The old
		// UNIQUE KEY post_id would cap archives (all post_id 0) at one shared
		// row. dbDelta never drops or alters an existing index, so swap it
		// explicitly: drop the unique index, keep a plain one for joins and
		// filters. Guarded by SHOW INDEX so re-runs are no-ops.
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- schema introspection; table name not user input.
		$unique_post_id = (array) $wpdb->get_results( "SHOW INDEX FROM {$table} WHERE Key_name = 'post_id' AND Non_unique = 0" );
		if ( $unique_post_id ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- one-time guarded v9 index swap on the plugin's OWN table: dbDelta never drops or alters an existing index, and the old UNIQUE KEY post_id would cap archive rows (all post_id 0) at one; runs only while SHOW INDEX reports the unique index.
			$wpdb->query( "ALTER TABLE {$table} DROP INDEX post_id, ADD INDEX post_id (post_id)" );
		}

		// Backfill: rows written before DB v2 have an empty post_title, which
		// made the alphabetical default sort look random and the Title sort
		// toggle appear dead (every value identical) until a full re-scan.
		// One UPDATE joining wp_posts fixes existing rows immediately.
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- one-time upgrade backfill on the plugin's own table; table names not user input.
		$wpdb->query(
			"UPDATE {$table} r
			INNER JOIN {$wpdb->posts} p ON p.ID = r.post_id
			SET r.post_title = p.post_title
			WHERE r.post_title = ''"
		);

		// Backfill the DB v5 issue index columns by reflagging every row
		// with issues (bounded by the scan cap; runs once per upgrade).
		// Ref-based since v9 (archive rows share post_id 0).
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- one-time upgrade backfill on the plugin's own table.
		$refs = (array) $wpdb->get_col( "SELECT ref FROM {$table} WHERE issue_codes = '' AND issues != '[]' AND issues != ''" );
		foreach ( $refs as $ref ) {
			self::reflag_by_ref( (string) $ref );
		}

		// DB v6 data migration: private and password-protected content is no
		// longer audited (it cannot appear in search or social), so purge any
		// rows left from before the exclusion landed. Private is removed by its
		// stored status (robust even if the post was since deleted); password-
		// protected needs the live post, so it joins wp_posts. Idempotent.
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- one-time upgrade cleanup on the plugin's own table; no user input.
		$purged_private = (int) $wpdb->query( "DELETE FROM {$table} WHERE post_status = 'private'" );
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- one-time upgrade cleanup on the plugin's own table; no user input.
		$purged_pw = (int) $wpdb->query(
			"DELETE r FROM {$table} r
			INNER JOIN {$wpdb->posts} p ON p.ID = r.post_id
			WHERE p.post_password <> ''"
		);
		if ( $purged_private > 0 || $purged_pw > 0 ) {
			self::refresh_summary();
			SSAA_Logger::info( 'unauditable_rows_purged', array(
				'private'           => $purged_private,
				'password_protected' => $purged_pw,
			) );
		}

		// DB v7: the new severity_rank sort key (critical, then warning, then
		// notice) starts at 0 for existing rows. Reflag every row that has
		// issues so the rank is populated from the stored issue data - the
		// severity-ordered default sort is then correct immediately, without
		// waiting for a re-scan. Reflag also re-applies ignore flags and
		// rebuilds the code/severity indexes, so it is safe to run here.
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- one-time upgrade backfill on the plugin's own table; no user input.
		$rank_refs = (array) $wpdb->get_col( "SELECT ref FROM {$table} WHERE issues != '[]' AND issues != '' AND severity_rank = 0" );
		foreach ( $rank_refs as $ref ) {
			self::reflag_by_ref( (string) $ref );
		}
		// Repopulate the stored summary so the new per-severity totals
		// (critical/warning/notice) are correct on the dashboard right away.
		self::refresh_summary();

		// DB v8: the per-area sort keys (search_rank, social_rank) let the
		// Search and Social tabs sort by their OWN area's severity instead of
		// the whole-row rank. New rows default to 0; reflag every row that has
		// issues so the per-area order is correct without a re-scan.
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- one-time upgrade backfill on the plugin's own table; no user input.
		$area_refs = (array) $wpdb->get_col( "SELECT ref FROM {$table} WHERE issues != '[]' AND issues != '' AND search_rank = 0 AND social_rank = 0" );
		foreach ( $area_refs as $ref ) {
			self::reflag_by_ref( (string) $ref );
		}

		// DB v11: the save() upsert never updated ai_rank on re-scan, so the
		// stored key can disagree with the issues JSON in both directions - a
		// row that gained an ai issue kept 0, a row whose ai issue was fixed
		// kept its old rank. Reflag every row either side could apply to; the
		// JSON is canonical, so this rebinds the sort key without a re-scan.
		$ai_like = '%' . $wpdb->esc_like( '"area":"ai"' ) . '%';
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- one-time upgrade backfill on the plugin's own table; value prepared.
		$ai_refs = (array) $wpdb->get_col( $wpdb->prepare( "SELECT ref FROM {$table} WHERE ai_rank > 0 OR issues LIKE %s", $ai_like ) );
		foreach ( $ai_refs as $ref ) {
			self::reflag_by_ref( (string) $ref );
		}

		update_option( self::DB_VERSION_OPTION, self::DB_VERSION );
	}

	/**
	 * Upsert the latest result for an audited page.
	 *
	 * @param array $result Keys matching the table columns. ref required
	 *                      ('post:{ID}' / 'url:{sha1}'); derived from post_id
	 *                      when absent (back-compat). post_id is 0 for archive
	 *                      rows. issues/extras as arrays (JSON-encoded here).
	 * @return bool
	 */
	public static function save( array $result ) {
		global $wpdb;

		// Back-compat: pre-v9 callers pass post_id only.
		if ( empty( $result['ref'] ) && ! empty( $result['post_id'] ) ) {
			$result['ref'] = 'post:' . absint( $result['post_id'] );
		}
		if ( empty( $result['ref'] ) ) {
			return false;
		}

		$defaults = array(
			'post_id'          => 0,
			'post_type'        => '',
			'post_status'      => '',
			'post_title'       => '',
			'wp_author'        => '',
			'exposed_author'   => '',
			'permalink'        => '',
			'scan_mode'        => 'standard',
			'scan_status'      => 'ok',
			'http_status'      => 0,
			'error_code'       => '',
			'error_message'    => '',
			'seo_title'        => '',
			'meta_description' => '',
			'canonical_url'    => '',
			'robots'           => '',
			'og_title'         => '',
			'og_description'   => '',
			'og_image_url'     => '',
			'twitter_card'     => '',
			'issue_count'      => 0,
			'issue_codes'      => '',
			'issue_severities' => '',
			'issues'           => array(),
			'extras'           => array(),
			'scanned_at'       => current_time( 'mysql', true ),
		);
		$row = wp_parse_args( $result, $defaults );

		$row['post_id'] = absint( $row['post_id'] );
		$row['ref']     = mb_substr( (string) $row['ref'], 0, 64 );

		// Apply ignore flags at save time, so rescans persist ignores.
		// issue_count counts only non-ignored issues - everything that hangs
		// off it (Issues tab filter, sorting, summaries, reports) follows.
		$row['issues']        = self::apply_ignore_flags( (array) $row['issues'], $row['post_id'] );
		$row['issue_count']   = self::count_active_issues( $row['issues'] );
		$row['severity_rank'] = self::severity_rank( $row['issues'] );
		$row['search_rank']   = self::severity_rank( $row['issues'], 'search' );
		$row['social_rank']   = self::severity_rank( $row['issues'], 'social' );
		$row['ai_rank']       = self::severity_rank( $row['issues'], 'ai' );
		$row['structure_rank'] = self::severity_rank( $row['issues'], 'structure' );

		// Queryable index of the non-ignored issues (codes + severities) -
		// keeps filtering correct in SQL so pagination and counts hold.
		$index                    = self::issue_index( $row['issues'] );
		$row['issue_codes']       = $index['codes'];
		$row['issue_severities']  = $index['severities'];

		$row['issues'] = wp_json_encode( $row['issues'] );
		$row['extras'] = wp_json_encode( (array) $row['extras'] );

		$table = self::table();

		// Upsert keyed on the ref UNIQUE index. Column list is fixed and
		// every value is passed through $wpdb->prepare placeholders.
		$sql = $wpdb->prepare(
			// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table name from self::table(), not user input.
			"INSERT INTO {$table}
				(post_id, ref, post_type, post_status, post_title, wp_author, exposed_author, permalink, scan_mode, scan_status, http_status,
				 error_code, error_message, seo_title, meta_description, canonical_url, robots,
				 og_title, og_description, og_image_url, twitter_card, issue_count, severity_rank, search_rank, social_rank, ai_rank, structure_rank, issue_codes, issue_severities, issues, extras, scanned_at)
			VALUES (%d, %s, %s, %s, %s, %s, %s, %s, %s, %s, %d, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %d, %d, %d, %d, %d, %d, %s, %s, %s, %s, %s)
			ON DUPLICATE KEY UPDATE
				post_id = VALUES(post_id),
				post_type = VALUES(post_type), post_status = VALUES(post_status),
				post_title = VALUES(post_title),
				wp_author = VALUES(wp_author), exposed_author = VALUES(exposed_author),
				permalink = VALUES(permalink), scan_mode = VALUES(scan_mode),
				scan_status = VALUES(scan_status), http_status = VALUES(http_status),
				error_code = VALUES(error_code), error_message = VALUES(error_message),
				seo_title = VALUES(seo_title), meta_description = VALUES(meta_description),
				canonical_url = VALUES(canonical_url), robots = VALUES(robots),
				og_title = VALUES(og_title), og_description = VALUES(og_description),
				og_image_url = VALUES(og_image_url), twitter_card = VALUES(twitter_card),
				issue_count = VALUES(issue_count), severity_rank = VALUES(severity_rank),
				search_rank = VALUES(search_rank), social_rank = VALUES(social_rank),
				ai_rank = VALUES(ai_rank), structure_rank = VALUES(structure_rank),
				issue_codes = VALUES(issue_codes),
				issue_severities = VALUES(issue_severities), issues = VALUES(issues),
				extras = VALUES(extras), scanned_at = VALUES(scanned_at)",
			$row['post_id'],
			$row['ref'],
			$row['post_type'],
			$row['post_status'],
			mb_substr( (string) $row['post_title'], 0, 255 ),
			mb_substr( (string) $row['wp_author'], 0, 255 ),
			mb_substr( (string) $row['exposed_author'], 0, 255 ),
			$row['permalink'],
			$row['scan_mode'],
			$row['scan_status'],
			absint( $row['http_status'] ),
			$row['error_code'],
			$row['error_message'],
			$row['seo_title'],
			$row['meta_description'],
			$row['canonical_url'],
			$row['robots'],
			$row['og_title'],
			$row['og_description'],
			$row['og_image_url'],
			$row['twitter_card'],
			$row['issue_count'],
			$row['severity_rank'],
			$row['search_rank'],
			$row['social_rank'],
			$row['ai_rank'],
			$row['structure_rank'],
			$row['issue_codes'],
			$row['issue_severities'],
			$row['issues'],
			$row['extras'],
			$row['scanned_at']
		);

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.PreparedSQL.NotPrepared -- custom table upsert, prepared above; no WP API for custom tables.
		$saved = false !== $wpdb->query( $sql );

		if ( ! $saved ) {
			SSAA_Logger::error( 'result_save_failed', array(
				'ref'      => $row['ref'],
				'post_id'  => $row['post_id'],
				'db_error' => $wpdb->last_error,
			) );
		}

		return $saved;
	}

	/**
	 * Record a FAILED scan attempt without touching the stored audit data.
	 *
	 * save() is an upsert over the full column list: called with only the error
	 * fields it writes the defaults into every audit column, so one bad minute
	 * (a maintenance window, a 503) blanked the seo_title / og_* / issues of
	 * every page in place - the row survived but its content was gone
	 * (the 2026-07-28 maintenance-mode incident). A render failure must never
	 * destroy a page's last known good audit: the page still appears in search
	 * and social exactly as it did before, we simply could not read it this
	 * time.
	 *
	 * So an existing row gets a targeted UPDATE of the status/error columns
	 * plus the identity columns discovery just re-read (title, status, author,
	 * permalink) - never the audit values, the issue index or extras.
	 * `scanned_at` IS refreshed: it records the last scan ATTEMPT, and the
	 * full-scan prune ("delete rows this scan did not attempt") is bound to it;
	 * the row's scan_status / error_code say the attempt failed, so the stale
	 * values are never presented as fresh.
	 *
	 * With no existing row there is nothing to preserve, so this falls back to
	 * save() and the empty audit columns are the honest state.
	 *
	 * @param array $base  Identity fields from SSAA_Scan::base_from_descriptor().
	 * @param array $error { scan_status, http_status, error_code, error_message }.
	 * @return bool
	 */
	public static function save_scan_error( array $base, array $error ) {
		global $wpdb;

		$ref = isset( $base['ref'] ) ? (string) $base['ref'] : '';
		if ( '' === $ref && ! empty( $base['post_id'] ) ) {
			$ref = 'post:' . absint( $base['post_id'] );
		}
		if ( '' === $ref ) {
			return false;
		}
		$ref = mb_substr( $ref, 0, 64 );

		$table = self::table();
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- custom table read; table name is a trusted constant, value prepared.
		$exists = (int) $wpdb->get_var( $wpdb->prepare( "SELECT id FROM {$table} WHERE ref = %s", $ref ) );

		if ( ! $exists ) {
			// No stored audit to protect - record the failure as a new row.
			return self::save( array_merge( $base, $error ) );
		}

		$data = array(
			'scan_status'   => isset( $error['scan_status'] ) ? (string) $error['scan_status'] : 'error',
			'http_status'   => isset( $error['http_status'] ) ? absint( $error['http_status'] ) : 0,
			'error_code'    => isset( $error['error_code'] ) ? (string) $error['error_code'] : '',
			'error_message' => isset( $error['error_message'] ) ? (string) $error['error_message'] : '',
			'scanned_at'    => isset( $base['scanned_at'] ) ? (string) $base['scanned_at'] : current_time( 'mysql', true ),
			// Identity columns: refreshing them keeps the row's labelling
			// current (a renamed or re-published page still lists correctly).
			// None of them is audit output.
			'post_id'       => isset( $base['post_id'] ) ? absint( $base['post_id'] ) : 0,
			'post_type'     => isset( $base['post_type'] ) ? (string) $base['post_type'] : '',
			'post_status'   => isset( $base['post_status'] ) ? (string) $base['post_status'] : '',
			'post_title'    => mb_substr( isset( $base['post_title'] ) ? (string) $base['post_title'] : '', 0, 255 ),
			'wp_author'     => mb_substr( isset( $base['wp_author'] ) ? (string) $base['wp_author'] : '', 0, 255 ),
			'permalink'     => isset( $base['permalink'] ) ? (string) $base['permalink'] : '',
			'scan_mode'     => isset( $base['scan_mode'] ) ? (string) $base['scan_mode'] : 'standard',
		);

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- custom table update through the wpdb API; values escaped by the format list.
		$updated = $wpdb->update(
			$table,
			$data,
			array( 'ref' => $ref ),
			array( '%s', '%d', '%s', '%s', '%s', '%d', '%s', '%s', '%s', '%s', '%s', '%s' ),
			array( '%s' )
		);

		if ( false === $updated ) {
			SSAA_Logger::error( 'result_error_save_failed', array(
				'ref'      => $ref,
				'db_error' => $wpdb->last_error,
			) );
			return false;
		}

		return true;
	}

	/**
	 * Latest result row for a post (issues/extras decoded), or null.
	 * Posts only - post_id 0 (archives) never matches; use get_by_ref().
	 *
	 * @param int $post_id Post ID.
	 * @return array|null
	 */
	public static function get( $post_id ) {
		$post_id = absint( $post_id );
		return $post_id > 0 ? self::get_by_ref( 'post:' . $post_id ) : null;
	}

	/**
	 * Latest result row by ref ('post:{ID}' / 'url:{sha1}'), or null.
	 *
	 * @param string $ref Row ref.
	 * @return array|null
	 */
	public static function get_by_ref( $ref ) {
		global $wpdb;

		$ref = (string) $ref;
		if ( '' === $ref ) {
			return null;
		}

		$table = self::table();
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- custom table read, table name not user input.
		$row = $wpdb->get_row( $wpdb->prepare( "SELECT * FROM {$table} WHERE ref = %s", $ref ), ARRAY_A );

		return $row ? self::decode_row( $row ) : null;
	}

	/**
	 * Query result rows for the list tables.
	 *
	 * @param array $args {
	 *     @type string $post_type   Restrict to one post type ('' = all).
	 *     @type string $status      Restrict to one post_status
	 *                               (publish|draft|future|pending; '' = all).
	 *     @type bool   $unpublished Legacy: any non-published status ('' status).
	 *     @type bool   $with_issues Only rows with issues or errors.
	 *     @type string $orderby     Whitelisted column (default scanned_at).
	 *     @type string $order       ASC|DESC.
	 *     @type int    $per_page    Page size (default 20).
	 *     @type int    $paged       1-based page number.
	 * }
	 * @return array{rows: array, total: int}
	 */
	public static function query( array $args = array() ) {
		global $wpdb;

		$defaults = array(
			'post_type'     => '',
			'exclude_types' => array(),
			'status'        => '',
			'unpublished'   => false,
			'with_issues'   => false,
			'issue_code'    => '',
			'severity'      => '',
			'orderby'       => 'scanned_at',
			'order'         => 'DESC',
			'per_page'      => 20,
			'paged'         => 1,
		);
		$args = wp_parse_args( $args, $defaults );

		$orderby_whitelist = array( 'scanned_at', 'issue_count', 'severity_rank', 'search_rank', 'social_rank', 'ai_rank', 'structure_rank', 'post_type', 'post_id', 'seo_title', 'post_title', 'wp_author' );
		$orderby           = in_array( $args['orderby'], $orderby_whitelist, true ) ? $args['orderby'] : 'scanned_at';
		$order             = 'ASC' === strtoupper( $args['order'] ) ? 'ASC' : 'DESC';
		$per_page          = max( 1, absint( $args['per_page'] ) );
		$offset            = ( max( 1, absint( $args['paged'] ) ) - 1 ) * $per_page;

		$where  = ' WHERE 1=1';
		$params = array();

		// Row-type exclusion (e.g. the Site row is kept off the Search/Social
		// preview tabs - it has no page to preview).
		foreach ( (array) $args['exclude_types'] as $excluded_type ) {
			$where   .= ' AND post_type != %s';
			$params[] = sanitize_key( $excluded_type );
		}

		if ( '__archive__' === $args['post_type'] ) {
			// Reserved bucket: all archive rows regardless of kind.
			$where .= ' AND post_id = 0';
		} elseif ( '' !== $args['post_type'] ) {
			// A real post type never matches archive rows (their post_type
			// holds the archive kind), so no post_id guard is needed here.
			$where   .= ' AND post_type = %s';
			$params[] = $args['post_type'];
		}
		// Per-status filter (publish / draft / future / pending). SSAA stores
		// post_status on the row, so the status views read the stored column
		// directly - no wp_posts join. Private is never stored (excluded from the
		// audit), so it can never match. The legacy `unpublished` boolean is still
		// honoured as a fallback for any bookmarked URL / external caller.
		$status = in_array( $args['status'], array( 'publish', 'draft', 'future', 'pending' ), true ) ? $args['status'] : '';
		if ( '' !== $status ) {
			$where   .= ' AND post_status = %s';
			$params[] = $status;
		} elseif ( $args['unpublished'] ) {
			$where .= " AND post_status != 'publish'";
		}
		if ( $args['with_issues'] ) {
			// The Site row (type 'site') is a FACT row - its free AI access
			// panel must be reachable even when the site is perfectly healthy
			// (zero findings), so it always lists on the Findings view. Found
			// by the 1.2.x docs pass: a clean Site row appeared NOWHERE (it is
			// excluded from the preview tabs by design), which made the free
			// panel unreachable exactly when robots.txt was fine.
			$where .= " AND ( issue_count > 0 OR scan_status = 'error' OR post_type = 'site' )";
		}
		if ( '' !== $args['issue_code'] ) {
			global $wpdb;
			$where   .= ' AND issue_codes LIKE %s';
			$params[] = '%' . $wpdb->esc_like( ',' . sanitize_key( $args['issue_code'] ) . ',' ) . '%';
		}
		if ( '' !== $args['severity'] ) {
			global $wpdb;
			$where   .= ' AND issue_severities LIKE %s';
			$params[] = '%' . $wpdb->esc_like( ',' . sanitize_key( $args['severity'] ) . ',' ) . '%';
		}

		$table = self::table();

		// Stable secondary sort: title A-Z within equal primary values.
		$secondary = ( 'post_title' === $orderby ) ? '' : ', post_title ASC';

		$count_sql = "SELECT COUNT(*) FROM {$table}{$where}";
		$rows_sql  = "SELECT * FROM {$table}{$where} ORDER BY {$orderby} {$order}{$secondary} LIMIT %d OFFSET %d";

		$rows_params   = $params;
		$rows_params[] = $per_page;
		$rows_params[] = $offset;

		if ( $params ) {
			// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- placeholders built above, values bound here.
			$count_sql = $wpdb->prepare( $count_sql, $params );
		}
		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- placeholders built above, values bound here; orderby/order whitelisted.
		$rows_sql = $wpdb->prepare( $rows_sql, $rows_params );

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- custom table read.
		$total = (int) $wpdb->get_var( $count_sql );
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- custom table read.
		$rows = (array) $wpdb->get_results( $rows_sql, ARRAY_A );

		return array(
			'rows'  => array_map( array( __CLASS__, 'decode_row' ), $rows ),
			'total' => $total,
		);
	}

	/**
	 * Count of rows not yet published (optionally only those with
	 * issues/errors) - drives the unpublished filter link.
	 *
	 * @param bool $with_issues Restrict to rows with issues or scan errors.
	 * @return int
	 */
	public static function unpublished_count( $with_issues = false ) {
		global $wpdb;

		$table = self::table();
		$where = " WHERE post_status != 'publish'";
		if ( $with_issues ) {
			$where .= " AND ( issue_count > 0 OR scan_status = 'error' )";
		}

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- aggregate on the plugin's own table; no user input.
		return (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$table}{$where}" );
	}

	/**
	 * Recompute and store the summary aggregates (dashboard + future Hub).
	 *
	 * @return array The fresh summary.
	 */
	public static function refresh_summary() {
		global $wpdb;

		$table = self::table();
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- aggregate read on indexed custom table.
		// Severity totals are unpacked from the composite severity_rank
		// (critical x 1,000,000 + warning x 1,000 + notice), so they need no
		// extra columns and exclude ignored issues exactly as the rank does.
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- aggregate read on indexed custom table.
		$row = $wpdb->get_row(
			"SELECT COUNT(*) AS total,
				SUM( CASE WHEN issue_count > 0 THEN 1 ELSE 0 END ) AS with_issues,
				SUM( CASE WHEN scan_status = 'error' THEN 1 ELSE 0 END ) AS errors,
				SUM( CASE WHEN scan_status = 'error' AND error_code LIKE 'engine\\_render%' THEN 1 ELSE 0 END ) AS degraded_pages,
				SUM( issue_count ) AS total_issues,
				SUM( FLOOR( severity_rank / 1000000 ) ) AS critical_issues,
				SUM( MOD( FLOOR( severity_rank / 1000 ), 1000 ) ) AS warning_issues,
				SUM( MOD( severity_rank, 1000 ) ) AS notice_issues,
				MAX( scanned_at ) AS last_scan
			FROM {$table}",
			ARRAY_A
		);

		$summary = array(
			'total'           => isset( $row['total'] ) ? (int) $row['total'] : 0,
			'with_issues'     => isset( $row['with_issues'] ) ? (int) $row['with_issues'] : 0,
			'errors'          => isset( $row['errors'] ) ? (int) $row['errors'] : 0,
			'total_issues'    => isset( $row['total_issues'] ) ? (int) $row['total_issues'] : 0,
			'critical_issues' => isset( $row['critical_issues'] ) ? (int) $row['critical_issues'] : 0,
			'warning_issues'  => isset( $row['warning_issues'] ) ? (int) $row['warning_issues'] : 0,
			'notice_issues'   => isset( $row['notice_issues'] ) ? (int) $row['notice_issues'] : 0,
			'last_scan'       => isset( $row['last_scan'] ) ? (string) $row['last_scan'] : '',
			// Scan health (engine 0.4.0 / the 2026-07-28 maintenance incident):
			// pages whose LAST scan attempt failed to render. Their stored
			// audit values are carried over from an earlier scan, so this is
			// not a clean baseline - the premium change tracking must not
			// advance onto it and the email report says so. Derived from the
			// canonical per-row state (scan_status + the engine_render_* error
			// class), so it stays true for exactly as long as it is true and
			// clears itself the moment a page renders again. The Site row's
			// own check errors (ai_robots_unreadable) are deliberately NOT
			// degradation: nothing was carried over there.
			'degraded_pages'  => isset( $row['degraded_pages'] ) ? (int) $row['degraded_pages'] : 0,
		);
		$summary['degraded'] = ( $summary['degraded_pages'] > 0 );

		update_option( self::SUMMARY_OPTION, $summary, false );

		return $summary;
	}

	/**
	 * Stored summary aggregates.
	 *
	 * @return array
	 */
	public static function summary() {
		return wp_parse_args( (array) get_option( self::SUMMARY_OPTION, array() ), array(
			'total'           => 0,
			'with_issues'     => 0,
			'errors'          => 0,
			'total_issues'    => 0,
			'critical_issues' => 0,
			'warning_issues'  => 0,
			'notice_issues'   => 0,
			'last_scan'       => '',
			'degraded_pages'  => 0,
			'degraded'        => false,
		) );
	}

	/**
	 * Remove a post's row (post deleted, or excluded from scans).
	 * Posts only - post_id 0 is a no-op (archives delete by ref, and a raw
	 * delete on post_id 0 would remove EVERY archive row).
	 *
	 * @param int $post_id Post ID.
	 */
	public static function delete( $post_id ) {
		$post_id = absint( $post_id );
		if ( $post_id > 0 ) {
			self::delete_by_ref( 'post:' . $post_id );
		}
	}

	/**
	 * Remove one row by ref ('post:{ID}' / 'url:{sha1}').
	 *
	 * @param string $ref Row ref.
	 */
	public static function delete_by_ref( $ref ) {
		global $wpdb;

		$ref = (string) $ref;
		if ( '' === $ref ) {
			return;
		}
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- custom table delete.
		$wpdb->delete( self::table(), array( 'ref' => $ref ), array( '%s' ) );
	}

	/**
	 * Remove all archive (non-post) rows - called when the archive-pages
	 * setting is switched off, mirroring prune_disabled_types() semantics.
	 */
	public static function delete_archives() {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- custom table delete.
		$wpdb->delete( self::table(), array( 'post_id' => 0 ), array( '%d' ) );
	}

	/**
	 * Set or clear the 'ignored' scope marker on each issue according to
	 * the current ignore lists.
	 *
	 * @param array $issues  Issue list.
	 * @param int   $post_id Post ID.
	 * @return array
	 */
	public static function apply_ignore_flags( array $issues, $post_id ) {
		foreach ( $issues as &$issue ) {
			$code  = isset( $issue['code'] ) ? (string) $issue['code'] : '';
			$scope = SSAA_Ignores::scope_for( $code, $post_id );
			if ( '' !== $scope ) {
				$issue['ignored'] = $scope; // 'site' or 'page'
			} else {
				unset( $issue['ignored'] );
			}
		}
		unset( $issue );
		return $issues;
	}

	/**
	 * Queryable index of the non-ignored issues: comma-wrapped code and
	 * severity lists (",a,b," shape so LIKE ',x,' matches exactly).
	 *
	 * @param array $issues Issue list.
	 * @return array { codes: string, severities: string }
	 */
	public static function issue_index( array $issues ) {
		$codes      = array();
		$severities = array();

		foreach ( $issues as $issue ) {
			if ( ! empty( $issue['ignored'] ) ) {
				continue;
			}
			if ( ! empty( $issue['code'] ) ) {
				$codes[] = sanitize_key( $issue['code'] );
			}
			if ( ! empty( $issue['severity'] ) ) {
				$severities[] = sanitize_key( $issue['severity'] );
			}
		}

		$codes      = array_values( array_unique( $codes ) );
		$severities = array_values( array_unique( $severities ) );

		return array(
			'codes'      => $codes ? ',' . implode( ',', $codes ) . ',' : '',
			'severities' => $severities ? ',' . implode( ',', $severities ) . ',' : '',
		);
	}

	/**
	 * Count of non-ignored issues.
	 *
	 * @param array $issues Issue list.
	 * @return int
	 */
	public static function count_active_issues( array $issues ) {
		$n = 0;
		foreach ( $issues as $issue ) {
			if ( empty( $issue['ignored'] ) ) {
				$n++;
			}
		}
		return $n;
	}

	/**
	 * Composite severity sort key for a row, from its non-ignored issues:
	 * critical x 1,000,000 + warning x 1,000 + notice. Ordering by this DESC
	 * sorts pages by true severity - all critical pages first (more criticals
	 * higher), then warnings break ties, then notices - so advisory-only pages
	 * never outrank a real problem. The multipliers assume well under 1,000 of
	 * any one severity per page, which a single page never approaches.
	 *
	 * @param array       $issues Issue list (may include ignored entries).
	 * @param string|null $area   Restrict to one area ('search'|'social'), or
	 *                            null for the whole row. Per-area ranks let the
	 *                            Search and Social tabs sort by their own area.
	 * @return int
	 */
	public static function severity_rank( array $issues, $area = null ) {
		$critical = 0;
		$warning  = 0;
		$notice   = 0;
		foreach ( $issues as $issue ) {
			if ( ! empty( $issue['ignored'] ) ) {
				continue;
			}
			if ( null !== $area && ( ! isset( $issue['area'] ) || $issue['area'] !== $area ) ) {
				continue;
			}
			switch ( isset( $issue['severity'] ) ? $issue['severity'] : '' ) {
				case 'critical':
					$critical++;
					break;
				case 'warning':
					$warning++;
					break;
				case 'notice':
					$notice++;
					break;
			}
		}
		return ( $critical * 1000000 ) + ( $warning * 1000 ) + $notice;
	}

	/**
	 * Findings that look TEMPLATE-WIDE rather than per-page (1.5.0).
	 *
	 * Heading faults cluster by template: measured on a real customer site, 61
	 * of 63 "no h1" findings came from three template families and 44 of 56
	 * skipped levels were "every single page of this type". Reported per page
	 * that is 119 rows for about six actual problems, which buries the user in
	 * exactly the way 1.4.0 set out to stop.
	 *
	 * Computed at READ time from the indexed issue_codes column - never stored.
	 * A stored count would drift the moment anything is re-scanned, and a
	 * derived artifact that can disagree with its source is the bug class this
	 * plugin keeps having to repair.
	 *
	 * The 60% threshold is set from evidence, not plausibility: on that site the
	 * genuine template faults ran 100/100/100/100/100/74 per cent of their type
	 * while the per-page ones sat at 50 and 8, so any cut between 55 and 70
	 * separates them. Three rows minimum so a two-page type cannot trip it.
	 *
	 * @param string[] $codes Issue codes to cluster.
	 * @return array[] [ { code, post_type, n, total } ], widest first.
	 */
	public static function code_clusters( array $codes ) {
		global $wpdb;

		$codes = array_filter( array_map( 'sanitize_key', $codes ) );
		if ( ! $codes ) {
			return array();
		}

		$table = self::table();

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- own table, admin read path.
		$totals_raw = (array) $wpdb->get_results( "SELECT post_type, COUNT(*) AS n FROM {$table} GROUP BY post_type", ARRAY_A );
		$totals     = array();
		foreach ( $totals_raw as $r ) {
			$totals[ $r['post_type'] ] = (int) $r['n'];
		}

		$out = array();
		foreach ( $codes as $code ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared -- prepared immediately below.
			$rows = (array) $wpdb->get_results(
				$wpdb->prepare(
					// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table name from self::table().
					"SELECT post_type, COUNT(*) AS n FROM {$table} WHERE issue_codes LIKE %s GROUP BY post_type",
					'%' . $wpdb->esc_like( ',' . $code . ',' ) . '%'
				),
				ARRAY_A
			);
			foreach ( $rows as $r ) {
				$n     = (int) $r['n'];
				$total = isset( $totals[ $r['post_type'] ] ) ? $totals[ $r['post_type'] ] : 0;
				if ( $n >= 3 && $total > 0 && ( $n / $total ) >= 0.6 ) {
					$out[] = array(
						'code'      => $code,
						'post_type' => $r['post_type'],
						'n'         => $n,
						'total'     => $total,
					);
				}
			}
		}

		usort( $out, function ( $a, $b ) {
			return $b['n'] - $a['n'];
		} );

		return $out;
	}

	/**
	 * Re-apply ignore flags to one stored row and persist - the instant-
	 * effect path when an ignore is toggled (no rescan required because the
	 * issues are already stored). Posts only; archives use reflag_by_ref().
	 *
	 * @param int $post_id Post ID.
	 */
	public static function reflag( $post_id ) {
		$post_id = absint( $post_id );
		if ( $post_id > 0 ) {
			self::reflag_by_ref( 'post:' . $post_id );
		}
	}

	/**
	 * Re-apply ignore flags to one stored row by ref and persist.
	 *
	 * @param string $ref Row ref ('post:{ID}' / 'url:{sha1}').
	 */
	public static function reflag_by_ref( $ref ) {
		global $wpdb;

		$row = self::get_by_ref( $ref );
		if ( null === $row ) {
			return;
		}

		$issues = self::apply_ignore_flags( (array) $row['issues'], (int) $row['post_id'] );
		$index  = self::issue_index( $issues );

		// Update keyed on the primary key - unambiguous even though every
		// archive row shares post_id 0.
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- custom table update.
		$wpdb->update(
			self::table(),
			array(
				'issues'           => wp_json_encode( $issues ),
				'issue_count'      => self::count_active_issues( $issues ),
				'severity_rank'    => self::severity_rank( $issues ),
				'search_rank'      => self::severity_rank( $issues, 'search' ),
				'social_rank'      => self::severity_rank( $issues, 'social' ),
				'ai_rank'          => self::severity_rank( $issues, 'ai' ),
				'structure_rank'   => self::severity_rank( $issues, 'structure' ),
				'issue_codes'      => $index['codes'],
				'issue_severities' => $index['severities'],
			),
			array( 'id' => absint( $row['id'] ) ),
			array( '%s', '%d', '%d', '%d', '%d', '%d', '%d', '%s', '%s' ),
			array( '%d' )
		);
	}

	/**
	 * Reflag every stored row containing an issue code (sitewide toggle).
	 * Bounded by the scan cap; hundreds of rows rewrite in well under a
	 * second. If a future site makes this heavy, route it through the
	 * scan queue.
	 *
	 * @param string $code Issue code.
	 */
	public static function reflag_rows_with_code( $code ) {
		global $wpdb;

		$table = self::table();
		$like  = '%' . $wpdb->esc_like( '"code":"' . sanitize_key( $code ) . '"' ) . '%';

		// Select refs, not post_ids - archive rows all share post_id 0.
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- custom table read; table name not user input.
		$refs = (array) $wpdb->get_col( $wpdb->prepare( "SELECT ref FROM {$table} WHERE issues LIKE %s", $like ) );

		foreach ( $refs as $ref ) {
			self::reflag_by_ref( (string) $ref );
		}
	}

	/**
	 * JSON-decode the issues/extras columns of a raw row.
	 *
	 * @param array $row Raw DB row.
	 * @return array
	 */
	private static function decode_row( array $row ) {
		$row['issues'] = json_decode( (string) $row['issues'], true );
		$row['extras'] = json_decode( (string) $row['extras'], true );
		$row['issues'] = is_array( $row['issues'] ) ? $row['issues'] : array();
		$row['extras'] = is_array( $row['extras'] ) ? $row['extras'] : array();
		return $row;
	}

	/**
	 * Compact per-page snapshot of every stored row, keyed by ref (v9: was
	 * post_id - archive rows all share post_id 0, so the diff needs the ref
	 * key), for the change-tracking engine: the active (non-ignored) issues
	 * plus the diffable scalar values. A single lightweight read of just the
	 * columns a diff needs - deliberately not the full query() rows. Core
	 * helper (free) so the premium engine reads through one stable surface.
	 *
	 * @return array<string,array> ref => { title, permalink, scan_status,
	 *               issues:[{code,area,severity,message}], values:{...} }
	 */
	public static function snapshot_rows() {
		global $wpdb;

		$table = self::table();
		$rows  = (array) $wpdb->get_results(
			"SELECT ref, post_title, permalink, scan_status, seo_title, meta_description, og_title, og_description, og_image_url, canonical_url, robots, issues FROM {$table}",
			ARRAY_A
		);

		$out = array();
		foreach ( $rows as $row ) {
			$issues = json_decode( (string) $row['issues'], true );
			$issues = is_array( $issues ) ? $issues : array();

			$active = array();
			foreach ( $issues as $issue ) {
				if ( ! empty( $issue['ignored'] ) ) {
					continue;
				}
				$active[] = array(
					'code'     => isset( $issue['code'] ) ? (string) $issue['code'] : '',
					'area'     => isset( $issue['area'] ) ? (string) $issue['area'] : '',
					'severity' => isset( $issue['severity'] ) ? (string) $issue['severity'] : '',
					'message'  => isset( $issue['message'] ) ? (string) $issue['message'] : '',
				);
			}

			$out[ (string) $row['ref'] ] = array(
				'title'       => (string) $row['post_title'],
				'permalink'   => (string) $row['permalink'],
				'scan_status' => (string) $row['scan_status'],
				'issues'      => $active,
				'values'      => array(
					'seo_title'        => (string) $row['seo_title'],
					'meta_description' => (string) $row['meta_description'],
					'og_title'         => (string) $row['og_title'],
					'og_description'   => (string) $row['og_description'],
					'og_image_url'     => (string) $row['og_image_url'],
					'canonical_url'    => (string) $row['canonical_url'],
					'robots'           => (string) $row['robots'],
				),
			);
		}

		return $out;
	}

	// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
}
