<?php
/**
 * Scan results: two small tables (pages, issues) and the score built from them.
 *
 * @package Fixlight
 */

namespace Fixlight;

defined( 'ABSPATH' ) || exit;

/**
 * Persistence and scoring.
 */
class Store {

	const DB_VERSION = '3';

	/**
	 * Hooks.
	 */
	public static function init() {
		add_action( 'deleted_post', array( __CLASS__, 'forget_post' ) );
		add_action( 'transition_post_status', array( __CLASS__, 'status_changed' ), 10, 3 );
		add_action( Background::DAILY_HOOK, array( __CLASS__, 'prune' ), 10, 0 );
	}

	/**
	 * Pages table name.
	 *
	 * @return string
	 */
	public static function pages_table() {
		global $wpdb;
		return $wpdb->prefix . 'fixlight_pages';
	}

	/**
	 * Issues table name.
	 *
	 * @return string
	 */
	public static function issues_table() {
		global $wpdb;
		return $wpdb->prefix . 'fixlight_issues';
	}

	/**
	 * Events table name: the dated record of what was found and fixed.
	 *
	 * @return string
	 */
	public static function events_table() {
		global $wpdb;
		return $wpdb->prefix . 'fixlight_events';
	}

	/**
	 * Creates or updates the tables.
	 */
	public static function install() {
		global $wpdb;
		require_once ABSPATH . 'wp-admin/includes/upgrade.php';

		$charset = $wpdb->get_charset_collate();
		$pages   = self::pages_table();
		$issues  = self::issues_table();
		$events  = self::events_table();
		$reports = Reports::table();

		dbDelta(
			"CREATE TABLE {$pages} (
				id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
				url text NOT NULL,
				url_hash char(32) NOT NULL,
				object_id bigint(20) unsigned NOT NULL DEFAULT 0,
				title text NOT NULL,
				php_scanned_at datetime DEFAULT NULL,
				axe_scanned_at datetime DEFAULT NULL,
				score_before smallint(5) DEFAULT NULL,
				score_after smallint(5) DEFAULT NULL,
				error varchar(255) NOT NULL DEFAULT '',
				PRIMARY KEY  (id),
				UNIQUE KEY url_hash (url_hash)
			) {$charset};
			CREATE TABLE {$issues} (
				id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
				page_id bigint(20) unsigned NOT NULL,
				engine varchar(10) NOT NULL,
				rule varchar(100) NOT NULL,
				impact varchar(10) NOT NULL DEFAULT '',
				level varchar(4) NOT NULL DEFAULT '',
				wcag varchar(20) NOT NULL DEFAULT '',
				before_count int(10) unsigned NOT NULL DEFAULT 0,
				after_count int(10) unsigned NOT NULL DEFAULT 0,
				sample text NOT NULL,
				help varchar(255) NOT NULL DEFAULT '',
				help_url varchar(255) NOT NULL DEFAULT '',
				PRIMARY KEY  (id),
				KEY page_engine (page_id,engine),
				KEY rule (rule)
			) {$charset};
			CREATE TABLE {$events} (
				id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
				url_hash char(32) NOT NULL,
				url text NOT NULL,
				engine varchar(10) NOT NULL,
				rule varchar(100) NOT NULL,
				event varchar(20) NOT NULL,
				count int(10) unsigned NOT NULL DEFAULT 0,
				created_at datetime NOT NULL,
				PRIMARY KEY  (id),
				KEY url_hash (url_hash),
				KEY created_at (created_at)
			) {$charset};
			CREATE TABLE {$reports} (
				id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
				created_at datetime NOT NULL,
				page_url text NOT NULL,
				message text NOT NULL,
				contact varchar(200) NOT NULL DEFAULT '',
				status varchar(20) NOT NULL DEFAULT 'new',
				replied_at datetime DEFAULT NULL,
				closed_at datetime DEFAULT NULL,
				PRIMARY KEY  (id),
				KEY status (status),
				KEY created_at (created_at)
			) {$charset};"
		);
		update_option( 'fixlight_db_version', self::DB_VERSION, false );
		self::seed_events();
	}

	/**
	 * Starts the dated record from the results already stored, so sites that
	 * update from a version without it get a "first seen" baseline.
	 */
	private static function seed_events() {
		global $wpdb;
		// Once only: after old events are pruned, an empty table must not be seeded again.
		if ( get_option( 'fixlight_events_seeded' ) ) {
			return;
		}
		update_option( 'fixlight_events_seeded', '1', false );
		if ( (int) $wpdb->get_var( $wpdb->prepare( 'SELECT COUNT(*) FROM %i', self::events_table() ) ) > 0 ) { // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			return;
		}
		$rows = $wpdb->get_results( $wpdb->prepare( 'SELECT p.url, p.php_scanned_at, p.axe_scanned_at, i.engine, i.rule, i.before_count, i.after_count FROM %i i JOIN %i p ON p.id = i.page_id', self::issues_table(), self::pages_table() ), ARRAY_A ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		foreach ( (array) $rows as $row ) {
			$time = 'axe' === $row['engine'] ? $row['axe_scanned_at'] : $row['php_scanned_at'];
			$base = array(
				'url_hash'   => md5( $row['url'] ),
				'url'        => $row['url'],
				'engine'     => $row['engine'],
				'rule'       => $row['rule'],
				'created_at' => $time ? $time : current_time( 'mysql', true ),
			);
			$wpdb->insert( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- The plugin's own events table.
				self::events_table(),
				array_merge(
					$base,
					array(
						'event' => 'found',
						'count' => max( (int) $row['before_count'], (int) $row['after_count'] ),
					)
				)
			);
			if ( 0 === (int) $row['after_count'] ) {
				$wpdb->insert( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- The plugin's own events table.
					self::events_table(),
					array_merge(
						$base,
						array(
							'event' => 'fixed',
							'count' => (int) $row['before_count'],
						)
					)
				);
			}
		}
	}

	/**
	 * Normalizes a URL for storage: no scan parameters, no fragment.
	 *
	 * @param string $url URL.
	 * @return string
	 */
	public static function normalize_url( $url ) {
		$url = remove_query_arg( array( 'fixlight_scan', 'fixlight_off', 'fixlight_frame', 'fixlight_id' ), $url );
		$url = preg_replace( '/#.*$/', '', $url );
		return $url;
	}

	/**
	 * Finds or creates the row for a page.
	 *
	 * @param string $url       Page URL.
	 * @param string $title     Page title.
	 * @param int    $object_id Post id, 0 for the home page.
	 * @return int Page id.
	 */
	public static function page_id( $url, $title = '', $object_id = 0 ) {
		global $wpdb;
		$url  = self::normalize_url( $url );
		$hash = md5( $url );
		$id   = (int) $wpdb->get_var( $wpdb->prepare( 'SELECT id FROM %i WHERE url_hash = %s', self::pages_table(), $hash ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		if ( $id ) {
			if ( '' !== $title ) {
				$wpdb->update( self::pages_table(), array( 'title' => $title ), array( 'id' => $id ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			}
			return $id;
		}
		$wpdb->insert( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
			self::pages_table(),
			array(
				'url'       => $url,
				'url_hash'  => $hash,
				'object_id' => (int) $object_id,
				'title'     => $title,
			)
		);
		return (int) $wpdb->insert_id;
	}

	/**
	 * Replaces the PHP-engine results of a page.
	 *
	 * @param int   $page_id Page id.
	 * @param array $results Rule id => before, after, sample.
	 */
	public static function save_php_results( $page_id, array $results ) {
		$rows = array();
		foreach ( $results as $rule => $result ) {
			$definition = Catalog::rule( $rule );
			$rows[]     = array(
				'rule'         => $rule,
				'impact'       => $definition ? $definition['impact'] : 'minor',
				'level'        => $definition ? $definition['level'] : 'BP',
				'wcag'         => $definition ? $definition['wcag'] : '',
				'before_count' => $result['before'],
				'after_count'  => $result['after'],
				'sample'       => $result['sample'],
				'help'         => $definition ? $definition['title'] : $rule,
				'help_url'     => '',
			);
		}
		self::replace( $page_id, 'php', $rows, 'php_scanned_at' );
	}

	/**
	 * Replaces the browser (axe-core) results of a page.
	 *
	 * @param int   $page_id Page id.
	 * @param array $before  Compact axe violations with fixes off.
	 * @param array $after   Compact axe violations with fixes on.
	 */
	public static function save_axe_results( $page_id, array $before, array $after ) {
		$merged = array();
		foreach ( array(
			'before' => $before,
			'after'  => $after,
		) as $phase => $violations ) {
			foreach ( $violations as $violation ) {
				if ( empty( $violation['id'] ) ) {
					continue;
				}
				$rule = sanitize_key( $violation['id'] );
				if ( ! isset( $merged[ $rule ] ) ) {
					$levels          = Catalog::level_from_axe_tags( isset( $violation['tags'] ) ? (array) $violation['tags'] : array() );
					$merged[ $rule ] = array(
						'rule'         => $rule,
						'impact'       => isset( $violation['impact'] ) && isset( Catalog::IMPACT_WEIGHTS[ $violation['impact'] ] ) ? $violation['impact'] : 'minor',
						'level'        => $levels['level'],
						'wcag'         => $levels['wcag'],
						'before_count' => 0,
						'after_count'  => 0,
						'sample'       => '',
						'help'         => isset( $violation['help'] ) ? sanitize_text_field( $violation['help'] ) : $rule,
						'help_url'     => isset( $violation['helpUrl'] ) ? esc_url_raw( $violation['helpUrl'] ) : '',
					);
				}
				$merged[ $rule ][ $phase . '_count' ] = isset( $violation['count'] ) ? absint( $violation['count'] ) : 0;
				$sample                               = isset( $violation['sample'] ) ? (string) $violation['sample'] : '';
				if ( '' !== $sample && ( 'after' === $phase || '' === $merged[ $rule ]['sample'] ) ) {
					$merged[ $rule ]['sample'] = function_exists( 'mb_substr' ) ? mb_substr( $sample, 0, 300 ) : substr( $sample, 0, 300 );
				}
			}
		}
		self::replace( $page_id, 'axe', array_values( $merged ), 'axe_scanned_at' );
	}

	/**
	 * Replaces one engine's rows for a page and refreshes its score.
	 *
	 * @param int    $page_id Page id.
	 * @param string $engine  php or axe.
	 * @param array  $rows    Issue rows.
	 * @param string $column  Timestamp column to update.
	 */
	private static function replace( $page_id, $engine, array $rows, $column ) {
		global $wpdb;
		$previous = $wpdb->get_results( $wpdb->prepare( 'SELECT rule, before_count, after_count FROM %i WHERE page_id = %d AND engine = %s', self::issues_table(), $page_id, $engine ), ARRAY_A ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$wpdb->delete( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			self::issues_table(),
			array(
				'page_id' => $page_id,
				'engine'  => $engine,
			)
		);
		foreach ( $rows as $row ) {
			if ( 0 === (int) $row['before_count'] && 0 === (int) $row['after_count'] ) {
				continue;
			}
			$row['page_id'] = $page_id;
			$row['engine']  = $engine;
			$wpdb->insert( self::issues_table(), $row ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
		}
		// The time goes first: the score depends on which engine checked the page last.
		$wpdb->update( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			self::pages_table(),
			array(
				$column => current_time( 'mysql', true ),
				'error' => '',
			),
			array( 'id' => $page_id )
		);
		$scores = self::page_scores( $page_id );
		$wpdb->update( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			self::pages_table(),
			array(
				'score_before' => $scores['before'],
				'score_after'  => $scores['after'],
			),
			array( 'id' => $page_id )
		);
		delete_transient( 'fixlight_summary' );

		$events = self::record_events( $page_id, $engine, is_array( $previous ) ? $previous : array(), $rows );

		/**
		 * Fires after a page's results for one engine are saved.
		 *
		 * @param int    $page_id Page id.
		 * @param string $engine  php or axe.
		 * @param array  $rows    The saved issue rows.
		 * @param array  $events  The events recorded for this save.
		 */
		do_action( 'fixlight_results_saved', $page_id, $engine, $rows, $events );
	}

	/**
	 * Compares a page's previous and new results and records what changed:
	 * a problem first found, fixed by Fixlight, fixed in the site itself
	 * (gone), or back after being fixed.
	 *
	 * @param int    $page_id  Page id.
	 * @param string $engine   php or axe.
	 * @param array  $previous Previous rows (rule, before_count, after_count).
	 * @param array  $rows     New rows.
	 * @return array[] Recorded events.
	 */
	private static function record_events( $page_id, $engine, array $previous, array $rows ) {
		global $wpdb;
		$old = array();
		foreach ( $previous as $row ) {
			$old[ $row['rule'] ] = array( (int) $row['before_count'], (int) $row['after_count'] );
		}
		$new = array();
		foreach ( $rows as $row ) {
			if ( (int) $row['before_count'] > 0 || (int) $row['after_count'] > 0 ) {
				$new[ $row['rule'] ] = array( (int) $row['before_count'], (int) $row['after_count'] );
			}
		}

		$events = array();
		foreach ( array_unique( array_merge( array_keys( $old ), array_keys( $new ) ) ) as $rule ) {
			$was = isset( $old[ $rule ] ) ? $old[ $rule ] : null;
			$now = isset( $new[ $rule ] ) ? $new[ $rule ] : null;
			if ( null === $was && null !== $now ) {
				$events[] = array( $rule, 'found', max( $now[0], $now[1] ) );
				if ( 0 === $now[1] ) {
					$events[] = array( $rule, 'fixed', $now[0] );
				}
			} elseif ( null !== $was && null === $now ) {
				$events[] = array( $rule, 'resolved', max( $was[0], $was[1] ) );
			} elseif ( null !== $was && null !== $now ) {
				if ( $was[1] > 0 && 0 === $now[1] ) {
					$events[] = array( $rule, 'fixed', $now[0] );
				} elseif ( 0 === $was[1] && $now[1] > 0 ) {
					$events[] = array( $rule, 'returned', $now[1] );
				}
			}
		}
		if ( ! $events ) {
			return array();
		}

		$url   = (string) $wpdb->get_var( $wpdb->prepare( 'SELECT url FROM %i WHERE id = %d', self::pages_table(), $page_id ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$time  = current_time( 'mysql', true );
		$saved = array();
		foreach ( $events as $event ) {
			$row = array(
				'url_hash'   => md5( $url ),
				'url'        => $url,
				'engine'     => $engine,
				'rule'       => $event[0],
				'event'      => $event[1],
				'count'      => $event[2],
				'created_at' => $time,
			);
			$wpdb->insert( self::events_table(), $row ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
			$saved[] = $row;
		}
		return $saved;
	}

	/**
	 * The dated record of found and fixed problems, newest first.
	 *
	 * @param array $args since (UTC mysql datetime), url, limit.
	 * @return array[] Event rows.
	 */
	public static function events( array $args = array() ) {
		global $wpdb;
		$args  = array_merge(
			array(
				'since' => '1970-01-01 00:00:00',
				'url'   => '',
				'limit' => 500,
			),
			$args
		);
		$limit = max( 1, min( 5000, (int) $args['limit'] ) );
		if ( '' !== $args['url'] ) {
			return $wpdb->get_results( $wpdb->prepare( 'SELECT * FROM %i WHERE url_hash = %s AND created_at >= %s ORDER BY created_at DESC, id DESC LIMIT %d', self::events_table(), md5( self::normalize_url( $args['url'] ) ), $args['since'], $limit ), ARRAY_A ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		}
		return $wpdb->get_results( $wpdb->prepare( 'SELECT * FROM %i WHERE created_at >= %s ORDER BY created_at DESC, id DESC LIMIT %d', self::events_table(), $args['since'], $limit ), ARRAY_A ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
	}

	/**
	 * Daily clean-up: deletes events older than the retention period, and
	 * issue rows whose page is gone (deleted while it was being checked).
	 * Each delete is limited, so a big backlog is cleared over a few days
	 * without locking the tables for long.
	 *
	 * @param int $limit   Rows per delete.
	 * @param int $batches Most event deletes per run.
	 * @return int Number of events deleted.
	 */
	public static function prune( $limit = 5000, $batches = 4 ) {
		global $wpdb;
		$limit = max( 1, (int) $limit );
		// Found with a plain read first, so the delete only locks the rows it removes.
		$gone = $wpdb->get_col( $wpdb->prepare( 'SELECT DISTINCT i.page_id FROM %i i LEFT JOIN %i p ON p.id = i.page_id WHERE p.id IS NULL LIMIT 100', self::issues_table(), self::pages_table() ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		if ( $gone ) {
			$in = implode( ',', array_fill( 0, count( $gone ), '%d' ) );
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Placeholders are built from the count.
			$wpdb->query( $wpdb->prepare( "DELETE FROM %i WHERE page_id IN ({$in}) AND page_id NOT IN ( SELECT id FROM %i ) LIMIT %d", array_merge( array( self::issues_table() ), $gone, array( self::pages_table(), $limit ) ) ) );
			delete_transient( 'fixlight_summary' );
		}

		/**
		 * Filters how many days of the dated record of events are kept. Older
		 * events are deleted once a day; 0 keeps every event.
		 *
		 * @since 0.1.0
		 *
		 * @param int $days Days to keep. Default 400, a little over a year.
		 */
		$days = (int) apply_filters( 'fixlight_events_keep_days', 400 );
		if ( $days < 1 ) {
			return 0;
		}
		$before  = gmdate( 'Y-m-d H:i:s', time() - $days * DAY_IN_SECONDS );
		$deleted = 0;
		for ( $i = 0; $i < $batches; $i++ ) {
			$rows     = (int) $wpdb->query( $wpdb->prepare( 'DELETE FROM %i WHERE created_at < %s ORDER BY created_at ASC, id ASC LIMIT %d', self::events_table(), $before, $limit ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			$deleted += $rows;
			if ( $rows < $limit ) {
				break;
			}
		}
		return $deleted;
	}

	/**
	 * Records that a page could not be scanned.
	 *
	 * @param int    $page_id Page id.
	 * @param string $message Short reason.
	 */
	public static function save_error( $page_id, $message ) {
		global $wpdb;
		$wpdb->update( self::pages_table(), array( 'error' => substr( $message, 0, 250 ) ), array( 'id' => $page_id ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
	}

	/**
	 * Forgets a post's results when it stops being published (draft, private, trash).
	 *
	 * @param string   $new_status New post status.
	 * @param string   $old_status Previous post status.
	 * @param \WP_Post $post       Post.
	 */
	public static function status_changed( $new_status, $old_status, $post ) {
		if ( 'publish' === $old_status && 'publish' !== $new_status && $post instanceof \WP_Post ) {
			self::forget_post( $post->ID );
		}
	}

	/**
	 * Deletes the current results of a post's pages (deleted or unpublished).
	 * The dated record of events is kept.
	 *
	 * @param int $post_id Post id.
	 */
	public static function forget_post( $post_id ) {
		global $wpdb;
		$post_id = (int) $post_id;
		if ( $post_id < 1 ) {
			return;
		}
		$page_ids = $wpdb->get_col( $wpdb->prepare( 'SELECT id FROM %i WHERE object_id = %d', self::pages_table(), $post_id ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		self::delete_pages( $page_ids );
	}

	/**
	 * Deletes the results of post pages that a finished full pass did not check,
	 * such as old addresses after a permalink change: rows with a post id whose
	 * last successful server check is older than the start of the pass. A row
	 * whose page failed to load is kept while its address is still the post's
	 * permalink, so the error stays visible. Rows without a post id (home page)
	 * are never removed.
	 *
	 * @param string|int $started_gmt Start of the pass: UTC "Y-m-d H:i:s", or a Unix timestamp.
	 * @return int Number of pages removed.
	 */
	public static function prune_stale( $started_gmt ) {
		global $wpdb;
		$started = is_numeric( $started_gmt ) ? gmdate( 'Y-m-d H:i:s', (int) $started_gmt ) : (string) $started_gmt;
		$rows    = $wpdb->get_results( $wpdb->prepare( "SELECT id, url, object_id, error FROM %i WHERE object_id > 0 AND ( php_scanned_at < %s OR ( php_scanned_at IS NULL AND error <> '' ) )", self::pages_table(), $started ), ARRAY_A ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$stale   = array();
		foreach ( (array) $rows as $row ) {
			if ( '' !== $row['error'] && self::normalize_url( (string) get_permalink( (int) $row['object_id'] ) ) === $row['url'] ) {
				continue;
			}
			$stale[] = $row['id'];
		}
		// Pages only the browser checked, before this pass, at an address the post no longer has.
		$browser_only = $wpdb->get_results( $wpdb->prepare( "SELECT id, url, object_id FROM %i WHERE object_id > 0 AND php_scanned_at IS NULL AND error = '' AND axe_scanned_at < %s", self::pages_table(), $started ), ARRAY_A ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		foreach ( (array) $browser_only as $row ) {
			$permalink = 'publish' === get_post_status( (int) $row['object_id'] ) ? (string) get_permalink( (int) $row['object_id'] ) : '';
			if ( '' === $permalink || self::normalize_url( $permalink ) !== $row['url'] ) {
				$stale[] = $row['id'];
			}
		}
		// An old home page address, after the site moved to https or another domain.
		$home  = self::normalize_url( home_url( '/' ) );
		$roots = $wpdb->get_results( $wpdb->prepare( 'SELECT id, url FROM %i WHERE object_id = 0 AND url_hash <> %s', self::pages_table(), md5( $home ) ), ARRAY_A ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		foreach ( (array) $roots as $row ) {
			$path = (string) wp_parse_url( $row['url'], PHP_URL_PATH );
			$same = wp_parse_url( $row['url'], PHP_URL_SCHEME ) === wp_parse_url( $home, PHP_URL_SCHEME ) && wp_parse_url( $row['url'], PHP_URL_HOST ) === wp_parse_url( $home, PHP_URL_HOST );
			if ( ! $same && trailingslashit( $path ) === trailingslashit( (string) wp_parse_url( $home, PHP_URL_PATH ) ) ) {
				$stale[] = (int) $row['id'];
			}
		}
		self::delete_pages( array_values( array_unique( array_map( 'intval', $stale ) ) ) );
		return count( $stale );
	}

	/**
	 * Deletes pages and their issues.
	 *
	 * @param array $page_ids Page ids.
	 */
	private static function delete_pages( array $page_ids ) {
		global $wpdb;
		foreach ( $page_ids as $page_id ) {
			$wpdb->delete( self::issues_table(), array( 'page_id' => (int) $page_id ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			$wpdb->delete( self::pages_table(), array( 'id' => (int) $page_id ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		}
		if ( $page_ids ) {
			delete_transient( 'fixlight_summary' );
		}
	}

	/**
	 * The issues that count for a page: the newer check for rules both engines
	 * run, plus each engine's own checks. A rule is counted once.
	 *
	 * @param int $page_id Page id.
	 * @return array[] Issue rows.
	 */
	public static function page_issues( $page_id ) {
		global $wpdb;
		$rows  = $wpdb->get_results( $wpdb->prepare( 'SELECT * FROM %i WHERE page_id = %d', self::issues_table(), $page_id ), ARRAY_A ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$times = $wpdb->get_row( $wpdb->prepare( 'SELECT php_scanned_at, axe_scanned_at, object_id FROM %i WHERE id = %d', self::pages_table(), $page_id ), ARRAY_A ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		if ( is_array( $times ) ) {
			$times['modified'] = (int) $times['object_id'] > 0 ? (string) get_post_field( 'post_modified_gmt', (int) $times['object_id'], 'raw' ) : '';
		}
		return self::dedupe( $rows, is_array( $times ) && self::browser_counts( $times ) ? array( $page_id => true ) : array() );
	}

	/**
	 * Whether a page's browser check counts for the rules both checks run. The
	 * browser sees what the theme's CSS hides, so it is the better check; a
	 * newer server check replaces it only when the page's content changed after
	 * the browser check (the owner fixed something and saved).
	 *
	 * @param array $page Page row: php_scanned_at, axe_scanned_at, and modified (the post's GMT modified time, '' for none).
	 * @return bool
	 */
	private static function browser_counts( array $page ) {
		if ( empty( $page['axe_scanned_at'] ) ) {
			return false;
		}
		$axe = (string) $page['axe_scanned_at'];
		if ( empty( $page['php_scanned_at'] ) || $axe >= (string) $page['php_scanned_at'] ) {
			return true;
		}
		$modified = isset( $page['modified'] ) ? (string) $page['modified'] : '';
		return '' === $modified || '0000-00-00 00:00:00' === $modified || $modified <= $axe;
	}

	/**
	 * Keeps one row per page and rule. For rules both engines run, only the
	 * newer check of the page counts, even when it found nothing (nothing found
	 * is not stored as a row).
	 *
	 * @param array[] $rows      Issue rows.
	 * @param array   $axe_pages Set of page ids whose browser check is the newer one.
	 * @return array[]
	 */
	private static function dedupe( array $rows, array $axe_pages ) {
		$kept = array();
		foreach ( $rows as $row ) {
			$newer = isset( $axe_pages[ $row['page_id'] ] ) ? 'axe' : 'php';
			if ( $newer !== $row['engine'] && in_array( $row['rule'], Catalog::AXE_EQUIVALENTS, true ) ) {
				continue;
			}
			$key = $row['page_id'] . '|' . $row['rule'];
			if ( ! isset( $kept[ $key ] ) || 'axe' === $row['engine'] ) {
				$kept[ $key ] = $row;
			}
		}
		return array_values( $kept );
	}

	/**
	 * Weight of one rule failing n times on a page (Lighthouse-style, log-damped).
	 *
	 * @param string $impact Impact.
	 * @param string $level  A, AA, AAA or BP.
	 * @param int    $count  Instances.
	 * @return float
	 */
	public static function weight( $impact, $level, $count ) {
		if ( $count < 1 ) {
			return 0.0;
		}
		$impact_weight = isset( Catalog::IMPACT_WEIGHTS[ $impact ] ) ? Catalog::IMPACT_WEIGHTS[ $impact ] : 1;
		$level_weight  = isset( Catalog::LEVEL_WEIGHTS[ $level ] ) ? Catalog::LEVEL_WEIGHTS[ $level ] : 0.3;
		return $impact_weight * $level_weight * ( 1 + log( min( $count, 32 ), 2 ) );
	}

	/**
	 * Before and after scores (0–100) for a page.
	 *
	 * @param int $page_id Page id.
	 * @return array{before:int,after:int}
	 */
	public static function page_scores( $page_id ) {
		$before = 0.0;
		$after  = 0.0;
		foreach ( self::page_issues( $page_id ) as $row ) {
			$before += self::weight( $row['impact'], $row['level'], (int) $row['before_count'] );
			$after  += self::weight( $row['impact'], $row['level'], (int) $row['after_count'] );
		}
		return array(
			'before' => (int) round( max( 0, 100 - $before ) ),
			'after'  => (int) round( max( 0, 100 - $after ) ),
		);
	}

	/**
	 * The issue rows that count on the whole site, the same rows in the same
	 * order as dedupe() keeps from all rows read by id. Reads the rows in
	 * batches, so memory stays small however big the site is.
	 *
	 * @return \Generator Issue rows (page_id, rule, impact, level, wcag, before_count, after_count, sample, help, help_url).
	 */
	private static function counted_rows() {
		global $wpdb;
		$issues = self::issues_table();
		$equiv  = Catalog::AXE_EQUIVALENTS;
		$rules  = implode( ',', array_fill( 0, count( $equiv ), '%s' ) );
		// Rows saved while this runs are left for the next summary.
		$last   = (int) $wpdb->get_var( $wpdb->prepare( 'SELECT MAX(id) FROM %i', $issues ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$cursor = 0;
		while ( $cursor < $last ) {
			$batch = $wpdb->get_results( $wpdb->prepare( 'SELECT id, page_id FROM %i WHERE id > %d AND id <= %d ORDER BY id ASC LIMIT 1000', $issues, $cursor, $last ), ARRAY_A ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			if ( ! $batch ) {
				break;
			}
			$from   = $cursor;
			$cursor = (int) $batch[ count( $batch ) - 1 ]['id'];
			$pages  = array_values( array_unique( array_map( 'intval', wp_list_pluck( $batch, 'page_id' ) ) ) );
			$in     = implode( ',', array_fill( 0, count( $pages ), '%d' ) );
			$args   = array_merge( array( $issues, self::pages_table(), $wpdb->posts ), $pages, array( $last ), $equiv, array( $issues, $from, $cursor ) );
			// As dedupe() does: per page and rule, only rows of the check that counts for rules both engines run (the
			// test of browser_counts()), then the last browser row or else the first server row. dedupe() puts
			// that row where the first of these rows was, so the pairs whose first row is in this batch come now.
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Placeholders are built from the counts.
			$rows = $wpdb->get_results( $wpdb->prepare( "SELECT w.page_id, w.rule, w.impact, w.level, w.wcag, w.before_count, w.after_count, w.sample, w.help, w.help_url FROM ( SELECT i.page_id, MIN(i.id) AS first_id, COALESCE( MAX( IF( i.engine = 'axe', i.id, NULL ) ), MIN(i.id) ) AS kept_id FROM %i i LEFT JOIN %i p ON p.id = i.page_id LEFT JOIN %i po ON po.ID = p.object_id AND p.object_id > 0 WHERE i.page_id IN ({$in}) AND i.id <= %d AND ( i.rule NOT IN ({$rules}) OR i.engine = IF( p.axe_scanned_at IS NOT NULL AND ( p.php_scanned_at IS NULL OR p.axe_scanned_at >= p.php_scanned_at OR po.post_modified_gmt IS NULL OR po.post_modified_gmt = '0000-00-00 00:00:00' OR po.post_modified_gmt <= p.axe_scanned_at ), 'axe', 'php' ) ) GROUP BY i.page_id, i.rule ) k JOIN %i w ON w.id = k.kept_id WHERE k.first_id > %d AND k.first_id <= %d ORDER BY k.first_id ASC", $args ), ARRAY_A );
			foreach ( (array) $rows as $row ) {
				yield $row;
			}
		}
	}

	/**
	 * Site-wide numbers for the dashboard (cached until the next scan).
	 * Pages are counted in the database and issues read in batches, so
	 * memory stays small on big sites. page_list holds only the pages that
	 * could not be checked (the first 500; pages_failed counts them all), and
	 * page_titles only the pages given as examples.
	 *
	 * @return array
	 */
	public static function summary() {
		// Texts are translated, so the cache is only valid for this version and language.
		// The 2 is the shape of the data: a summary cached before pages_failed is not used.
		$cache_key = FIXLIGHT_VERSION . '|2|' . determine_locale();
		$cached    = get_transient( 'fixlight_summary' );
		if ( is_array( $cached ) && isset( $cached['key'], $cached['data'] ) && $cache_key === $cached['key'] ) {
			return $cached['data'];
		}
		global $wpdb;
		$counts = $wpdb->get_row( $wpdb->prepare( "SELECT COUNT(*) AS pages, COUNT(score_after) AS scored, COUNT(axe_scanned_at) AS browser, SUM(error <> '') AS failed FROM %i", self::pages_table() ), ARRAY_A ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		// The home page counts twice. Stored addresses are normalized, so the hash finds it.
		$home       = md5( self::normalize_url( home_url( '/' ) ) );
		$scores     = $wpdb->get_row( $wpdb->prepare( 'SELECT SUM(IF(url_hash = %s, 2, 1)) AS weight, SUM(IF(url_hash = %s, 2, 1) * score_before) AS sum_before, SUM(IF(url_hash = %s, 2, 1) * score_after) AS sum_after, MAX(php_scanned_at) AS php, MAX(axe_scanned_at) AS axe FROM %i WHERE score_after IS NOT NULL', $home, $home, $home, self::pages_table() ), ARRAY_A ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$scored     = (int) $scores['weight'];
		$sum_before = (int) $scores['sum_before'];
		$sum_after  = (int) $scores['sum_after'];
		$last_scan  = max( '', (string) $scores['php'], (string) $scores['axe'] );

		$rules = array();
		foreach ( self::counted_rows() as $row ) {
			$rule = $row['rule'];
			if ( ! isset( $rules[ $rule ] ) ) {
				$definition     = Catalog::rule( $rule );
				$rules[ $rule ] = array(
					'rule'        => $rule,
					'title'       => $definition ? $definition['title'] : $row['help'],
					'why'         => $definition ? $definition['why'] : '',
					'how'         => $definition ? $definition['how'] : '',
					'help_url'    => $row['help_url'],
					'impact'      => $row['impact'],
					'level'       => $row['level'],
					'wcag'        => $row['wcag'],
					'fix'         => Catalog::fix_for_rule( $rule ),
					'before'      => 0,
					'after'       => 0,
					'pages'       => 0,
					'pages_after' => 0,
					'weight'      => 0.0,
					'sample'      => '',
					'examples'    => array(),
				);
			}
			$rules[ $rule ]['before'] += (int) $row['before_count'];
			$rules[ $rule ]['after']  += (int) $row['after_count'];
			++$rules[ $rule ]['pages'];
			if ( (int) $row['after_count'] > 0 ) {
				++$rules[ $rule ]['pages_after'];
				$rules[ $rule ]['weight'] += self::weight( $row['impact'], $row['level'], (int) $row['after_count'] );
				if ( count( $rules[ $rule ]['examples'] ) < 5 ) {
					$rules[ $rule ]['examples'][] = array(
						'page_id' => (int) $row['page_id'],
						'count'   => (int) $row['after_count'],
						'sample'  => $row['sample'],
					);
				}
			}
		}
		uasort(
			$rules,
			static function ( $a, $b ) {
				if ( $a['weight'] === $b['weight'] ) {
					return $b['before'] - $a['before'];
				}
				return $b['weight'] > $a['weight'] ? 1 : -1;
			}
		);

		$fixed     = 0;
		$remaining = 0;
		foreach ( $rules as $rule ) {
			$fixed     += max( 0, $rule['before'] - $rule['after'] );
			$remaining += $rule['after'];
		}

		$examples = array();
		foreach ( $rules as $rule ) {
			foreach ( $rule['examples'] as $example ) {
				$examples[ $example['page_id'] ] = true;
			}
		}
		$page_titles = array();
		if ( $examples ) {
			$in = implode( ',', array_fill( 0, count( $examples ), '%d' ) );
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Placeholders are built from the count.
			$titled = $wpdb->get_results( $wpdb->prepare( "SELECT id, url, title FROM %i WHERE id IN ({$in}) ORDER BY id ASC", array_merge( array( self::pages_table() ), array_keys( $examples ) ) ), ARRAY_A );
			foreach ( (array) $titled as $page ) {
				$page_titles[ (int) $page['id'] ] = array(
					'url'   => $page['url'],
					'title' => '' !== $page['title'] ? $page['title'] : $page['url'],
				);
			}
		}
		$failed = $wpdb->get_results( $wpdb->prepare( "SELECT id, url, object_id, title, score_before, score_after, php_scanned_at, axe_scanned_at, error FROM %i WHERE error <> '' ORDER BY id ASC LIMIT 500", self::pages_table() ), ARRAY_A ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching

		$summary = array(
			'pages'        => (int) $counts['pages'],
			'pages_scored' => (int) $counts['scored'],
			'score_before' => $scored ? (int) round( $sum_before / $scored ) : null,
			'score_after'  => $scored ? (int) round( $sum_after / $scored ) : null,
			'fixed'        => $fixed,
			'remaining'    => $remaining,
			'last_scan'    => $last_scan,
			'browser_scan' => (int) $counts['browser'] > 0,
			'rules'        => array_values( $rules ),
			'page_titles'  => $page_titles,
			'page_list'    => (array) $failed,
			'pages_failed' => (int) $counts['failed'],
		);
		set_transient(
			'fixlight_summary',
			array(
				'key'  => $cache_key,
				'data' => $summary,
			),
			DAY_IN_SECONDS
		);
		return $summary;
	}

	/**
	 * Deletes every current result (used by "Start over"). The dated record of
	 * past events is kept: it is history, not current state.
	 */
	public static function reset() {
		global $wpdb;
		$wpdb->query( $wpdb->prepare( 'TRUNCATE TABLE %i', self::issues_table() ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange
		$wpdb->query( $wpdb->prepare( 'TRUNCATE TABLE %i', self::pages_table() ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange
		delete_transient( 'fixlight_summary' );
	}
}