<?php
/**
 * Data access for scans, items, links and findings.
 *
 * @package SiteAuditHub
 */

namespace SiteAuditHub;

defined( 'ABSPATH' ) || exit;

// phpcs:disable WordPress.DB.DirectDatabaseQuery -- The plugin's own custom tables have no WordPress API.

/**
 * Repository (all queries prepared; JSON only, never unserialize).
 */
class Repository {

	const ACTIVE_STATUSES = array( 'queued', 'running', 'paused' );
	const SEVERITIES      = array( 'critical', 'high', 'medium', 'low', 'info' );
	const RETIRED_CHECKS  = array( 'health_file_edit', 'a11y_form_label', 'a11y_placeholder_label' );

	/* ---------------------------------------------------------------------
	 * Scans
	 * ------------------------------------------------------------------- */

	/**
	 * Create a scan row.
	 *
	 * @param string $type    site|integrity.
	 * @param string $trigger manual|scheduled.
	 * @param array  $config  Settings snapshot.
	 * @return int
	 */
	public static function create_scan( $type, $trigger, array $config ) {
		global $wpdb;
		$prev = self::latest_completed( $type );
		$wpdb->insert(
			Db::table( 'scans' ),
			array(
				'scan_type'      => $type,
				'status'         => 'queued',
				'stage'          => '',
				'trigger_source' => $trigger,
				'created_by'     => get_current_user_id(),
				'created_at'     => Db::now(),
				'prev_scan_id'   => $prev ? (int) $prev->id : 0,
				'state'          => Db::encode( array() ),
				'summary'        => Db::encode( array() ),
				'config'         => Db::encode( $config ),
			)
		);
		return (int) $wpdb->insert_id;
	}

	/**
	 * Hydrate a scan row.
	 *
	 * @param object|null $row Row.
	 * @return object|null
	 */
	private static function hydrate( $row ) {
		if ( ! $row ) {
			return null;
		}
		$row->state   = Db::decode( $row->state );
		$row->summary = Db::decode( $row->summary );
		$row->config  = Db::decode( $row->config );
		foreach ( array( 'id', 'total_items', 'processed_items', 'failed_items', 'skipped_items', 'findings_count', 'prev_scan_id', 'created_by' ) as $int ) {
			$row->$int = (int) $row->$int;
		}
		return $row;
	}

	/**
	 * Get scan by id.
	 *
	 * @param int $id ID.
	 * @return object|null
	 */
	public static function get_scan( $id ) {
		global $wpdb;
		$table = Db::table( 'scans' );
		return self::hydrate( $wpdb->get_row( $wpdb->prepare( 'SELECT * FROM %i WHERE id = %d', $table, $id ) ) );
	}

	/**
	 * Update scan columns (arrays for state/summary/config are JSON encoded).
	 *
	 * @param int   $id     ID.
	 * @param array $fields Fields.
	 */
	public static function update_scan( $id, array $fields ) {
		global $wpdb;
		foreach ( array( 'state', 'summary', 'config' ) as $json ) {
			if ( isset( $fields[ $json ] ) && is_array( $fields[ $json ] ) ) {
				$fields[ $json ] = Db::encode( $fields[ $json ] );
			}
		}
		$wpdb->update( Db::table( 'scans' ), $fields, array( 'id' => (int) $id ) );
	}

	/**
	 * Active (queued/running/paused) scan of a type, or any type.
	 *
	 * @param string|null $type Type.
	 * @return object|null
	 */
	public static function active_scan( $type = null ) {
		global $wpdb;
		$table = Db::table( 'scans' );
		if ( $type ) {
			$row = $wpdb->get_row( $wpdb->prepare( "SELECT * FROM %i WHERE scan_type = %s AND status IN ('queued','running','paused') ORDER BY id DESC LIMIT 1", $table, $type ) );
		} else {
			$row = $wpdb->get_row( $wpdb->prepare( "SELECT * FROM %i WHERE status IN ('queued','running','paused') ORDER BY id DESC LIMIT 1", $table ) );
		}
		return self::hydrate( $row );
	}

	/**
	 * All runnable scans (queued/running).
	 *
	 * @return array
	 */
	public static function runnable_scans() {
		global $wpdb;
		$table = Db::table( 'scans' );
		$rows  = $wpdb->get_results( $wpdb->prepare( "SELECT * FROM %i WHERE status IN ('queued','running') ORDER BY id ASC LIMIT 5", $table ) );
		return array_map( array( __CLASS__, 'hydrate' ), (array) $rows );
	}

	/**
	 * Latest completed scan of a type.
	 *
	 * @param string $type Type.
	 * @return object|null
	 */
	public static function latest_completed( $type ) {
		global $wpdb;
		$table = Db::table( 'scans' );
		return self::hydrate( $wpdb->get_row( $wpdb->prepare( "SELECT * FROM %i WHERE scan_type = %s AND status = 'completed' ORDER BY id DESC LIMIT 1", $table, $type ) ) );
	}

	/**
	 * List scans.
	 *
	 * @param int         $limit  Limit.
	 * @param int         $offset Offset.
	 * @param string|null $type   Type filter.
	 * @return array
	 */
	public static function list_scans( $limit = 20, $offset = 0, $type = null ) {
		global $wpdb;
		$table = Db::table( 'scans' );
		if ( $type ) {
			$rows = $wpdb->get_results( $wpdb->prepare( 'SELECT * FROM %i WHERE scan_type = %s ORDER BY id DESC LIMIT %d OFFSET %d', $table, $type, $limit, $offset ) );
		} else {
			$rows = $wpdb->get_results( $wpdb->prepare( 'SELECT * FROM %i ORDER BY id DESC LIMIT %d OFFSET %d', $table, $limit, $offset ) );
		}
		return array_map( array( __CLASS__, 'hydrate' ), (array) $rows );
	}

	/**
	 * Count scans.
	 *
	 * @return int
	 */
	public static function count_scans() {
		global $wpdb;
		$table = Db::table( 'scans' );
		return (int) $wpdb->get_var( $wpdb->prepare( 'SELECT COUNT(*) FROM %i', $table ) );
	}

	/**
	 * Delete a scan and its children in bounded chunks.
	 *
	 * @param int $id Scan ID.
	 */
	public static function delete_scan( $id ) {
		global $wpdb;
		foreach ( array( 'items', 'links', 'findings' ) as $child ) {
			$table = Db::table( $child );
			do {
				$ids = $wpdb->get_col( $wpdb->prepare( 'SELECT id FROM %i WHERE scan_id = %d LIMIT 2000', $table, $id ) );
				if ( $ids ) {
					$ids          = array_map( 'intval', $ids );
					$placeholders = implode( ', ', array_fill( 0, count( $ids ), '%d' ) );
					// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber -- $placeholders holds only %d placeholders, one per ID.
					$wpdb->query( $wpdb->prepare( "DELETE FROM %i WHERE id IN ( $placeholders )", array_merge( array( $table ), $ids ) ) );
				}
			} while ( count( $ids ) === 2000 );
		}
		$wpdb->delete( Db::table( 'scans' ), array( 'id' => (int) $id ) );
	}

	/* ---------------------------------------------------------------------
	 * Items
	 * ------------------------------------------------------------------- */

	/**
	 * Add an item if it does not exist yet for this scan.
	 *
	 * @param int    $scan_id   Scan.
	 * @param string $key       URL or file key.
	 * @param string $type      url|external|file.
	 * @param string $source    Source label.
	 * @param int    $object_id Post ID etc.
	 * @param int    $depth     Link depth.
	 * @param int    $in_sitemap Sitemap flag.
	 * @return bool Inserted.
	 */
	public static function add_item( $scan_id, $key, $type, $source, $object_id = 0, $depth = 0, $in_sitemap = 0 ) {
		global $wpdb;
		$table = Db::table( 'items' );
		$hash  = sha1( $key );
		$res   = $wpdb->query(
			$wpdb->prepare( "INSERT IGNORE INTO %i (scan_id, item_hash, item_key, item_type, source, object_id, depth, in_sitemap, status) VALUES (%d, %s, %s, %s, %s, %d, %d, %d, 'pending')", $table, $scan_id, $hash, $key, $type, $source, $object_id, $depth, $in_sitemap )
		);
		return (bool) $res;
	}

	/**
	 * Find an item by key.
	 *
	 * @param int    $scan_id Scan.
	 * @param string $key     Key.
	 * @return object|null
	 */
	public static function find_item( $scan_id, $key ) {
		global $wpdb;
		$table = Db::table( 'items' );
		return $wpdb->get_row( $wpdb->prepare( 'SELECT * FROM %i WHERE scan_id = %d AND item_hash = %s', $table, $scan_id, sha1( $key ) ) );
	}

	/**
	 * Count items of a scan, optionally by type/status.
	 *
	 * @param int         $scan_id Scan.
	 * @param string|null $type    Type.
	 * @param array|null  $status  Statuses.
	 * @return int
	 */
	public static function count_items( $scan_id, $type = null, $status = null ) {
		global $wpdb;
		$table = Db::table( 'items' );
		$type = (string) $type;
		if ( ! $status ) {
			return (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM %i WHERE scan_id = %d AND ( %s = '' OR item_type = %s )", $table, $scan_id, $type, $type ) );
		}
		$status       = array_values( array_map( 'strval', (array) $status ) );
		$placeholders = implode( ', ', array_fill( 0, count( $status ), '%s' ) );
		return (int) $wpdb->get_var(
			// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber -- $placeholders holds only %s placeholders, one per status.
			$wpdb->prepare( "SELECT COUNT(*) FROM %i WHERE scan_id = %d AND ( %s = '' OR item_type = %s ) AND status IN ( $placeholders )", array_merge( array( $table, $scan_id, $type, $type ), $status ) )
		);
	}

	/**
	 * Status breakdown for a scan.
	 *
	 * @param int         $scan_id Scan.
	 * @param string|null $type    Type.
	 * @return array status => count
	 */
	public static function item_status_counts( $scan_id, $type = null ) {
		global $wpdb;
		$table = Db::table( 'items' );
		$type  = (string) $type;
		$rows  = $wpdb->get_results( $wpdb->prepare( "SELECT status, COUNT(*) AS c FROM %i WHERE scan_id = %d AND ( %s = '' OR item_type = %s ) GROUP BY status", $table, $scan_id, $type, $type ) );
		$out   = array();
		foreach ( (array) $rows as $row ) {
			$out[ $row->status ] = (int) $row->c;
		}
		return $out;
	}

	/**
	 * Claim pending items atomically for processing.
	 *
	 * @param int    $scan_id Scan.
	 * @param string $type    Item type.
	 * @param int    $limit   Max.
	 * @return array Rows.
	 */
	public static function claim_items( $scan_id, $type, $limit ) {
		global $wpdb;
		$table   = Db::table( 'items' );
		$rows    = $wpdb->get_results( $wpdb->prepare( "SELECT * FROM %i WHERE scan_id = %d AND item_type = %s AND status = 'pending' ORDER BY depth ASC, id ASC LIMIT %d", $table, $scan_id, $type, $limit ) );
		$claimed = array();
		foreach ( (array) $rows as $row ) {
			$ok = $wpdb->query( $wpdb->prepare( "UPDATE %i SET status = 'processing', claimed_at = %s, attempts = attempts + 1 WHERE id = %d AND status = 'pending'", $table, Db::now(), $row->id ) );
			if ( $ok ) {
				$row->attempts = (int) $row->attempts + 1;
				$claimed[]     = $row;
			}
		}
		return $claimed;
	}

	/**
	 * Recover items stuck in "processing" (interrupted worker).
	 *
	 * @param int $scan_id Scan.
	 * @param int $max_age Seconds.
	 */
	public static function recover_stale_items( $scan_id, $max_age = 300 ) {
		global $wpdb;
		$table  = Db::table( 'items' );
		$cutoff = gmdate( 'Y-m-d H:i:s', time() - $max_age );
		$wpdb->query( $wpdb->prepare( "UPDATE %i SET status = 'failed', processed_at = %s WHERE scan_id = %d AND status = 'processing' AND claimed_at < %s AND attempts >= 3", $table, Db::now(), $scan_id, $cutoff ) );
		$wpdb->query( $wpdb->prepare( "UPDATE %i SET status = 'pending' WHERE scan_id = %d AND status = 'processing' AND claimed_at < %s", $table, $scan_id, $cutoff ) );
	}

	/**
	 * Update item columns.
	 *
	 * @param int   $id     ID.
	 * @param array $fields Fields.
	 */
	public static function update_item( $id, array $fields ) {
		global $wpdb;
		if ( isset( $fields['data'] ) && is_array( $fields['data'] ) ) {
			$fields['data'] = Db::encode( $fields['data'] );
		}
		$wpdb->update( Db::table( 'items' ), $fields, array( 'id' => (int) $id ) );
	}

	/**
	 * Reset failed/inaccessible items to pending (retry).
	 *
	 * @param int $scan_id Scan.
	 * @return int Rows reset.
	 */
	public static function reset_failed_items( $scan_id ) {
		global $wpdb;
		$table = Db::table( 'items' );
		return (int) $wpdb->query( $wpdb->prepare( "UPDATE %i SET status = 'pending', attempts = 0 WHERE scan_id = %d AND status IN ('failed','inaccessible')", $table, $scan_id ) );
	}

	/**
	 * Paginated items list.
	 *
	 * @param int   $scan_id Scan.
	 * @param array $args    type, status, search, page, per_page.
	 * @return array [rows, total]
	 */
	public static function query_items( $scan_id, array $args ) {
		global $wpdb;
		$table = Db::table( 'items' );
		$type   = empty( $args['type'] ) ? '' : (string) $args['type'];
		$status = empty( $args['status'] ) ? '' : (string) $args['status'];
		$search = empty( $args['search'] ) ? '' : '%' . $wpdb->esc_like( (string) $args['search'] ) . '%';
		$per    = max( 1, min( 200, (int) ( isset( $args['per_page'] ) ? $args['per_page'] : 25 ) ) );
		$page   = max( 1, (int) ( isset( $args['page'] ) ? $args['page'] : 1 ) );
		$total  = (int) $wpdb->get_var(
			$wpdb->prepare(
				"SELECT COUNT(*) FROM %i WHERE scan_id = %d AND ( %s = '' OR item_type = %s ) AND ( %s = '' OR status = %s ) AND ( %s = '' OR item_key LIKE %s )",
				$table,
				$scan_id,
				$type,
				$type,
				$status,
				$status,
				$search,
				$search
			)
		);
		$rows   = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT id, item_key, item_type, source, depth, in_sitemap, status, http_status, attempts, response_ms, final_url, word_count, findings_count, processed_at FROM %i WHERE scan_id = %d AND ( %s = '' OR item_type = %s ) AND ( %s = '' OR status = %s ) AND ( %s = '' OR item_key LIKE %s ) ORDER BY id ASC LIMIT %d OFFSET %d",
				$table,
				$scan_id,
				$type,
				$type,
				$status,
				$status,
				$search,
				$search,
				$per,
				( $page - 1 ) * $per
			)
		);
		return array( (array) $rows, $total );
	}

	/* ---------------------------------------------------------------------
	 * Links
	 * ------------------------------------------------------------------- */

	/**
	 * Store outbound links of a crawled page (bounded).
	 *
	 * @param int   $scan_id   Scan.
	 * @param int   $source_id Item ID.
	 * @param array $links     List of [url, external(bool), anchor].
	 */
	public static function add_links( $scan_id, $source_id, array $links ) {
		global $wpdb;
		if ( empty( $links ) ) {
			return;
		}
		$table = Db::table( 'links' );
		foreach ( array_chunk( $links, 100 ) as $chunk ) {
			$args = array( $table );
			foreach ( $chunk as $link ) {
				array_push( $args, (int) $scan_id, (int) $source_id, sha1( $link[0] ), $link[0], $link[1] ? 1 : 0, mb_substr( (string) $link[2], 0, 250 ) );
			}
			$rows = implode( ', ', array_fill( 0, count( $chunk ), '(%d, %d, %s, %s, %d, %s)' ) );
			// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber -- $rows holds only placeholder groups, one per link.
			$wpdb->query( $wpdb->prepare( "INSERT INTO %i (scan_id, source_id, target_hash, target_url, is_external, anchor) VALUES $rows", $args ) );
		}
	}

	/* ---------------------------------------------------------------------
	 * Findings
	 * ------------------------------------------------------------------- */

	/**
	 * Insert a finding (duplicates within a scan are ignored).
	 *
	 * @param int   $scan_id Scan.
	 * @param array $f       Finding fields.
	 * @return bool
	 */
	public static function add_finding( $scan_id, array $f ) {
		global $wpdb;
		$table    = Db::table( 'findings' );
		$severity = in_array( $f['severity'], self::SEVERITIES, true ) ? $f['severity'] : 'info';
		$item     = isset( $f['item_key'] ) ? (string) $f['item_key'] : '';
		$fp       = sha1( $f['module'] . '|' . $f['check_id'] . '|' . $item . '|' . ( isset( $f['fp_extra'] ) ? $f['fp_extra'] : '' ) );
		$res      = $wpdb->query(
			$wpdb->prepare( "INSERT IGNORE INTO %i (scan_id, module, check_id, severity, classification, item_key, title, description, evidence, recommendation, detection_method, fingerprint, change_status, created_at) VALUES (%d, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, 'new', %s)", $table, $scan_id, $f['module'], $f['check_id'], $severity, $f['classification'], $item, mb_substr( (string) $f['title'], 0, 250 ), (string) ( isset( $f['description'] ) ? $f['description'] : '' ), mb_substr( (string) ( isset( $f['evidence'] ) ? $f['evidence'] : '' ), 0, 4000 ), (string) ( isset( $f['recommendation'] ) ? $f['recommendation'] : '' ), mb_substr( (string) ( isset( $f['method'] ) ? $f['method'] : '' ), 0, 250 ), $fp, Db::now() )
		);
		return (bool) $res;
	}

	/**
	 * Delete findings of one module for a scan (used when re-running a stage).
	 *
	 * @param int    $scan_id Scan.
	 * @param string $module  Module.
	 * @param string $item    Optional item key.
	 */
	public static function delete_findings( $scan_id, $module, $item = null ) {
		global $wpdb;
		$table = Db::table( 'findings' );
		if ( null === $item ) {
			$wpdb->query( $wpdb->prepare( 'DELETE FROM %i WHERE scan_id = %d AND module = %s', $table, $scan_id, $module ) );
		} else {
			$wpdb->query( $wpdb->prepare( 'DELETE FROM %i WHERE scan_id = %d AND module = %s AND item_key = %s', $table, $scan_id, $module, $item ) );
		}
	}

	/**
	 * Ignored fingerprints.
	 *
	 * @return array fingerprint => timestamp
	 */
	public static function ignored() {
		$list = get_option( 'siteaudithub_ignored', array() );
		return is_array( $list ) ? $list : array();
	}

	/**
	 * Mark a fingerprint ignored or not.
	 *
	 * @param string $fp     Fingerprint.
	 * @param bool   $ignore Ignore flag.
	 */
	public static function set_ignored( $fp, $ignore ) {
		$fp   = preg_replace( '/[^a-f0-9]/', '', strtolower( $fp ) );
		$list = self::ignored();
		if ( 40 !== strlen( $fp ) ) {
			return;
		}
		if ( $ignore ) {
			$list[ $fp ] = time();
			if ( count( $list ) > 5000 ) {
				asort( $list );
				$list = array_slice( $list, -5000, null, true );
			}
		} else {
			unset( $list[ $fp ] );
		}
		update_option( 'siteaudithub_ignored', $list, false );
	}

	/**
	 * Build the WHERE clause for findings queries.
	 *
	 * The returned clause contains only fixed SQL text and placeholders; every
	 * value is returned separately so callers pass it through $wpdb->prepare().
	 *
	 * @param int   $scan_id Scan.
	 * @param array $args    Filters.
	 * @return array { 0: string clause with placeholders, 1: array values }
	 */
	private static function findings_where( $scan_id, array $args ) {
		global $wpdb;
		$clause = 'scan_id = %d';
		$values = array( (int) $scan_id );

		// Checks removed in later versions are hidden from older stored scans too.
		$clause .= ' AND check_id NOT IN (' . self::placeholders( count( self::RETIRED_CHECKS ), '%s' ) . ')';
		$values  = array_merge( $values, self::RETIRED_CHECKS );

		$columns = array(
			'module'         => 'module',
			'severity'       => 'severity',
			'classification' => 'classification',
			'change_status'  => 'change_status',
			'check_id'       => 'check_id',
		);
		foreach ( $columns as $arg => $column ) {
			if ( ! empty( $args[ $arg ] ) ) {
				$list    = array_values( array_map( 'strval', (array) $args[ $arg ] ) );
				$clause .= ' AND ' . $column . ' IN (' . self::placeholders( count( $list ), '%s' ) . ')';
				$values  = array_merge( $values, $list );
			}
		}
		if ( ! empty( $args['item_key'] ) ) {
			$clause  .= ' AND item_key = %s';
			$values[] = (string) $args['item_key'];
		}
		if ( ! empty( $args['search'] ) ) {
			$like     = '%' . $wpdb->esc_like( (string) $args['search'] ) . '%';
			$clause  .= ' AND (title LIKE %s OR item_key LIKE %s)';
			$values[] = $like;
			$values[] = $like;
		}
		$ignored = array_keys( self::ignored() );
		$view    = isset( $args['view'] ) ? $args['view'] : 'open';
		if ( $ignored && 'all' !== $view ) {
			$clause .= ( 'ignored' === $view ? ' AND fingerprint IN (' : ' AND fingerprint NOT IN (' ) . self::placeholders( count( $ignored ), '%s' ) . ')';
			$values  = array_merge( $values, array_map( 'strval', $ignored ) );
		} elseif ( ! $ignored && 'ignored' === $view ) {
			$clause .= ' AND 1 = 0';
		}
		return array( $clause, $values );
	}

	/**
	 * A comma-separated list of identical placeholders.
	 *
	 * @param int    $count       Number of placeholders.
	 * @param string $placeholder %s or %d.
	 * @return string
	 */
	private static function placeholders( $count, $placeholder ) {
		$placeholder = '%d' === $placeholder ? '%d' : '%s';
		return implode( ', ', array_fill( 0, max( 1, (int) $count ), $placeholder ) );
	}

	/**
	 * Number of distinct files / items that have findings (excluding ignored).
	 *
	 * @param int   $scan_id Scan.
	 * @param array $args    Same filters as query_findings().
	 * @return int
	 */
	public static function count_finding_items( $scan_id, array $args ) {
		global $wpdb;
		list( $where, $values ) = self::findings_where( $scan_id, $args );
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber -- $where holds only fixed SQL and placeholders (see findings_where()).
		return (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(DISTINCT item_key) FROM %i WHERE $where", array_merge( array( Db::table( 'findings' ) ), $values ) ) );
	}

	/**
	 * Paginated findings.
	 *
	 * @param int   $scan_id Scan.
	 * @param array $args    Filters + page/per_page.
	 * @return array [rows, total]
	 */
	public static function query_findings( $scan_id, array $args ) {
		global $wpdb;
		$table                  = Db::table( 'findings' );
		list( $where, $values ) = self::findings_where( $scan_id, $args );
		$per                    = max( 1, min( 500, (int) ( isset( $args['per_page'] ) ? $args['per_page'] : 25 ) ) );
		$page                   = max( 1, (int) ( isset( $args['page'] ) ? $args['page'] : 1 ) );
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber -- $where holds only fixed SQL and placeholders (see findings_where()).
		$total = (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM %i WHERE $where", array_merge( array( $table ), $values ) ) );
		$rows  = $wpdb->get_results(
			// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber -- $where holds only fixed SQL and placeholders (see findings_where()).
			$wpdb->prepare( "SELECT * FROM %i WHERE $where ORDER BY CASE severity WHEN 'critical' THEN 0 WHEN 'high' THEN 1 WHEN 'medium' THEN 2 WHEN 'low' THEN 3 ELSE 4 END ASC, id ASC LIMIT %d OFFSET %d", array_merge( array( $table ), $values, array( $per, ( $page - 1 ) * $per ) ) )
		);
		return array( (array) $rows, $total );
	}

	/**
	 * Iterate all findings of a scan in chunks (exports).
	 *
	 * @param int      $scan_id  Scan.
	 * @param array    $args     Filters.
	 * @param callable $callback Called per row.
	 */
	public static function each_finding( $scan_id, array $args, callable $callback ) {
		global $wpdb;
		$table                  = Db::table( 'findings' );
		list( $where, $values ) = self::findings_where( $scan_id, $args );
		$last                   = 0;
		do {
			// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber -- $where holds only fixed SQL and placeholders (see findings_where()).
			$rows = $wpdb->get_results( $wpdb->prepare( "SELECT * FROM %i WHERE $where AND id > %d ORDER BY id ASC LIMIT 500", array_merge( array( $table ), $values, array( $last ) ) ) );
			foreach ( (array) $rows as $row ) {
				$last = (int) $row->id;
				call_user_func( $callback, $row );
			}
		} while ( count( (array) $rows ) === 500 );
	}

	/**
	 * Counts grouped by module and severity (excluding ignored).
	 *
	 * @param int $scan_id Scan.
	 * @return array module => severity => count
	 */
	public static function finding_matrix( $scan_id ) {
		global $wpdb;
		list( $where, $values ) = self::findings_where( $scan_id, array() );
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber -- $where holds only fixed SQL and placeholders (see findings_where()).
		$rows = $wpdb->get_results( $wpdb->prepare( "SELECT module, severity, COUNT(*) AS c FROM %i WHERE $where GROUP BY module, severity", array_merge( array( Db::table( 'findings' ) ), $values ) ) );
		$out  = array();
		foreach ( (array) $rows as $row ) {
			$out[ $row->module ][ $row->severity ] = (int) $row->c;
		}
		return $out;
	}

	/**
	 * Counts per check id (excluding ignored).
	 *
	 * @param int    $scan_id Scan.
	 * @param string $module  Module.
	 * @return array
	 */
	public static function check_counts( $scan_id, $module ) {
		global $wpdb;
		list( $where, $values ) = self::findings_where( $scan_id, array( 'module' => $module ) );
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber -- $where holds only fixed SQL and placeholders (see findings_where()).
		return (array) $wpdb->get_results( $wpdb->prepare( "SELECT check_id, severity, classification, MIN(title) AS title, COUNT(*) AS c FROM %i WHERE $where GROUP BY check_id, severity, classification ORDER BY c DESC", array_merge( array( Db::table( 'findings' ) ), $values ) ) );
	}

	/**
	 * Counts per change status (excluding ignored).
	 *
	 * @param int $scan_id Scan.
	 * @return array
	 */
	public static function change_counts( $scan_id ) {
		global $wpdb;
		list( $where, $values ) = self::findings_where( $scan_id, array() );
		$out                    = array(
			'new'       => 0,
			'recurring' => 0,
		);
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber -- $where holds only fixed SQL and placeholders (see findings_where()).
		$rows = $wpdb->get_results( $wpdb->prepare( "SELECT change_status, COUNT(*) AS c FROM %i WHERE $where GROUP BY change_status", array_merge( array( Db::table( 'findings' ) ), $values ) ) );
		foreach ( (array) $rows as $row ) {
			$out[ $row->change_status ] = (int) $row->c;
		}
		return $out;
	}

	/**
	 * Mark findings that also existed in the previous scan as recurring.
	 *
	 * @param int $scan_id Scan.
	 * @param int $prev_id Previous scan.
	 */
	public static function mark_recurring( $scan_id, $prev_id ) {
		global $wpdb;
		if ( ! $prev_id ) {
			return;
		}
		$table = Db::table( 'findings' );
		$last  = 0;
		do {
			$rows = $wpdb->get_results( $wpdb->prepare( 'SELECT id, fingerprint FROM %i WHERE scan_id = %d AND id > %d ORDER BY id ASC LIMIT 500', $table, $scan_id, $last ) );
			if ( ! $rows ) {
				break;
			}
			$map = array();
			foreach ( $rows as $row ) {
				$map[ $row->fingerprint ] = (int) $row->id;
				$last                     = (int) $row->id;
			}
			$in      = self::placeholders( count( $map ), '%s' );
			$args    = array_merge( array( $table, $prev_id ), array_map( 'strval', array_keys( $map ) ) );
			// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber -- $in holds only %s placeholders, one per fingerprint.
			$matches = $wpdb->get_col( $wpdb->prepare( "SELECT fingerprint FROM %i WHERE scan_id = %d AND fingerprint IN ( $in )", $args ) );
			$ids     = array();
			foreach ( (array) $matches as $fp ) {
				$ids[] = $map[ $fp ];
			}
			if ( $ids ) {
				$ids = array_map( 'intval', $ids );
				$in  = self::placeholders( count( $ids ), '%d' );
				// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber -- $in holds only %d placeholders, one per ID.
				$wpdb->query( $wpdb->prepare( "UPDATE %i SET change_status = 'recurring' WHERE id IN ( $in )", array_merge( array( $table ), $ids ) ) );
			}
		} while ( count( $rows ) === 500 );
	}

	/**
	 * Findings present in the previous scan but absent now ("resolved").
	 *
	 * Only counts findings whose item was successfully re-checked in the
	 * current scan, so a failed request never looks like a fix.
	 *
	 * @param int  $scan_id Current scan.
	 * @param int  $prev_id Previous scan.
	 * @param int  $limit   Max rows (0 = count only).
	 * @param bool $count   Return count.
	 * @return array|int
	 */
	public static function resolved_findings( $scan_id, $prev_id, $limit = 100, $count = false ) {
		global $wpdb;
		if ( ! $prev_id ) {
			return $count ? 0 : array();
		}
		$f = Db::table( 'findings' );
		$i = Db::table( 'items' );
		if ( $count ) {
			return (int) $wpdb->get_var(
				$wpdb->prepare(
					"SELECT COUNT(*) FROM %i p WHERE p.scan_id = %d
					AND NOT EXISTS (SELECT 1 FROM %i c WHERE c.scan_id = %d AND c.fingerprint = p.fingerprint)
					AND (p.item_key = '' OR p.module IN ('health','plugins') OR EXISTS (SELECT 1 FROM %i it WHERE it.scan_id = %d AND it.item_key = p.item_key AND it.status = 'done'))",
					$f,
					$prev_id,
					$f,
					$scan_id,
					$i,
					$scan_id
				)
			);
		}
		return (array) $wpdb->get_results(
			$wpdb->prepare(
				"SELECT p.* FROM %i p WHERE p.scan_id = %d
				AND NOT EXISTS (SELECT 1 FROM %i c WHERE c.scan_id = %d AND c.fingerprint = p.fingerprint)
				AND (p.item_key = '' OR p.module IN ('health','plugins') OR EXISTS (SELECT 1 FROM %i it WHERE it.scan_id = %d AND it.item_key = p.item_key AND it.status = 'done'))
				ORDER BY p.id ASC LIMIT %d",
				$f,
				$prev_id,
				$f,
				$scan_id,
				$i,
				$scan_id,
				$limit
			)
		);
	}
}