<?php
/**
 * Issue storage and queries.
 *
 * @package A11yMate
 */

namespace A11yMate\Store;

use A11yMate\Rules\Registry;
use A11yMate\Schema;
use A11yMate\Support\Fingerprint;

defined( 'ABSPATH' ) || exit;

/**
 * Grouped issues: one row per rule + element + snippet, with the pages it appears on.
 */
class Issues {

	const PRIORITY_SQL = "(CASE severity WHEN 'critical' THEN 10 WHEN 'serious' THEN 6 WHEN 'moderate' THEN 3 ELSE 1 END) * (1 + 0.5 * LN(GREATEST(page_count, 1)))";

	/**
	 * Record the findings for one page.
	 *
	 * @param int    $scan_id  Scan.
	 * @param int    $page_id  Page row id.
	 * @param string $url      Page URL.
	 * @param array  $findings Findings: rule, selector, snippet, detail.
	 * @param string $viewport all|desktop|mobile.
	 * @return int Number of findings stored.
	 */
	public static function record( $scan_id, $page_id, $url, array $findings, $viewport = 'all' ) {
		global $wpdb;
		$issues = Schema::table( 'issues' );
		$occ    = Schema::table( 'occurrences' );
		$now    = gmdate( 'Y-m-d H:i:s' );
		$stored = 0;

		foreach ( $findings as $f ) {
			$rule = Registry::get( isset( $f['rule'] ) ? $f['rule'] : '' );
			if ( ! $rule ) {
				continue;
			}
			$selector = substr( (string) ( isset( $f['selector'] ) ? $f['selector'] : '' ), 0, 500 );
			$snippet  = substr( (string) ( isset( $f['snippet'] ) ? $f['snippet'] : '' ), 0, 600 );
			$detail   = isset( $f['detail'] ) && is_array( $f['detail'] ) ? wp_json_encode( $f['detail'] ) : null;
			$fp       = Fingerprint::make( $rule['id'], $selector, $snippet );

			$existing = $wpdb->get_row( $wpdb->prepare( 'SELECT id, state, last_scan FROM %i WHERE fingerprint = %s', $issues, $fp ), ARRAY_A ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery
			if ( ! $existing ) {
				$wpdb->insert( // phpcs:ignore WordPress.DB.DirectDatabaseQuery
					$issues,
					array(
						'fingerprint'      => $fp,
						'rule_id'          => $rule['id'],
						'severity'         => $rule['severity'],
						'category'         => $rule['category'],
						'pass'             => 'rendered' === $rule['pass'] ? 'rendered' : 'server',
						'mode'             => $rule['mode'],
						'selector'         => $selector,
						'snippet'          => $snippet,
						'state'            => 'open',
						'first_scan'       => $scan_id,
						'last_scan'        => $scan_id,
						'first_seen'       => $now,
						'last_seen'        => $now,
						'page_count'       => 1,
						'occurrence_count' => 1,
						'sample_url'       => $url,
						'detail'           => $detail,
					)
				);
				$issue_id = (int) $wpdb->insert_id;
			} else {
				$issue_id = (int) $existing['id'];
				$set      = array(
					'last_scan' => $scan_id,
					'last_seen' => $now,
				);
				if ( 'fixed' === $existing['state'] ) {
					$set['state']    = 'open';
					$set['fixed_at'] = null;
				}
				if ( (int) $existing['last_scan'] !== (int) $scan_id ) {
					$set['occurrence_count'] = 1;
					$set['sample_url']       = $url;
					if ( null !== $detail ) {
						$set['detail'] = $detail;
					}
				}
				$wpdb->update( $issues, $set, array( 'id' => $issue_id ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery
				if ( (int) $existing['last_scan'] === (int) $scan_id ) {
					$wpdb->query( $wpdb->prepare( 'UPDATE %i SET occurrence_count = occurrence_count + 1 WHERE id = %d', $issues, $issue_id ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery
				}
			}
			$wpdb->query( // phpcs:ignore WordPress.DB.DirectDatabaseQuery
				$wpdb->prepare(
					"INSERT INTO %i (issue_id, scan_id, page_id, viewport, detail) VALUES (%d, %d, %d, %s, %s)
					ON DUPLICATE KEY UPDATE viewport = IF(viewport = VALUES(viewport), viewport, 'both')",
					$occ,
					$issue_id,
					$scan_id,
					$page_id,
					$viewport,
					wp_json_encode(
						array(
							'selector' => $selector,
							'snippet'  => $snippet,
						)
					)
				)
			);
			++$stored;
		}
		return $stored;
	}

	/**
	 * Recompute how many pages each issue touched in a scan.
	 *
	 * @param int $scan_id Scan.
	 * @return void
	 */
	public static function refresh_counts( $scan_id ) {
		global $wpdb;
		$issues = Schema::table( 'issues' );
		$occ    = Schema::table( 'occurrences' );
		$wpdb->query( // phpcs:ignore WordPress.DB.DirectDatabaseQuery
			$wpdb->prepare(
				'UPDATE %i i SET page_count = (SELECT COUNT(DISTINCT o.page_id) FROM %i o WHERE o.issue_id = i.id AND o.scan_id = %d) WHERE i.last_scan = %d',
				$issues,
				$occ,
				$scan_id,
				$scan_id
			)
		);
	}

	/**
	 * Mark issues that did not turn up in this scan as fixed.
	 *
	 * An issue only counts as fixed if a page it was on last time was scanned again this time and it
	 * did not show up. If the scan covered everything (it was not cut off by the page limit), issues
	 * whose pages no longer exist are closed too.
	 *
	 * @param int      $scan_id       Scan.
	 * @param string[] $passes        Passes that ran (server, rendered).
	 * @param string[] $enabled_rules Rule ids that were active.
	 * @param bool     $full_scan     Whether the scan was not cut short by the page limit.
	 * @return int Number marked fixed.
	 */
	public static function mark_fixed( $scan_id, array $passes, array $enabled_rules, $full_scan = false ) {
		global $wpdb;
		if ( ! $passes || ! $enabled_rules ) {
			return 0;
		}
		$issues  = Schema::table( 'issues' );
		$occ     = Schema::table( 'occurrences' );
		$pages   = Schema::table( 'pages' );
		$pass_ph = implode( ', ', array_fill( 0, count( $passes ), '%s' ) );
		$rule_ph = implode( ', ', array_fill( 0, count( $enabled_rules ), '%s' ) );
		$now     = gmdate( 'Y-m-d H:i:s' );
		$head    = array_merge( array( $issues, $now, $scan_id ), $passes, $enabled_rules );

		// Only lists of %s placeholders and the fixed text below are interpolated; every value is a placeholder argument.
		// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare -- the placeholder lists are generated, not user input.
		// Re-scanned page, issue gone.
		$wpdb->query( // phpcs:ignore WordPress.DB.DirectDatabaseQuery
			$wpdb->prepare(
				"UPDATE %i i SET i.state = 'fixed', i.fixed_at = %s WHERE i.state = 'open' AND i.last_scan < %d AND i.pass IN ({$pass_ph}) AND i.rule_id IN ({$rule_ph}) AND EXISTS (
					SELECT 1 FROM %i o
					JOIN %i po ON po.id = o.page_id
					JOIN %i pn ON pn.url_hash = po.url_hash AND pn.scan_id = %d AND pn.error IS NULL AND pn.server_done = 1 AND ( i.pass = 'server' OR pn.rendered_done = 1 )
					WHERE o.issue_id = i.id AND o.scan_id = i.last_scan )",
				array_merge( $head, array( $occ, $pages, $pages, $scan_id ) )
			)
		);
		$count = (int) $wpdb->rows_affected;

		if ( $full_scan ) {
			// Pages that no longer exist.
			$wpdb->query( // phpcs:ignore WordPress.DB.DirectDatabaseQuery
				$wpdb->prepare(
					"UPDATE %i i SET i.state = 'fixed', i.fixed_at = %s WHERE i.state = 'open' AND i.last_scan < %d AND i.pass IN ({$pass_ph}) AND i.rule_id IN ({$rule_ph}) AND NOT EXISTS (
						SELECT 1 FROM %i o
						JOIN %i po ON po.id = o.page_id
						JOIN %i pn ON pn.url_hash = po.url_hash AND pn.scan_id = %d
						WHERE o.issue_id = i.id AND o.scan_id = i.last_scan )",
					array_merge( $head, array( $occ, $pages, $pages, $scan_id ) )
				)
			);
			$count += (int) $wpdb->rows_affected;
		}
		// phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare
		return $count;
	}

	/**
	 * Query grouped issues.
	 *
	 * @param array $args state, severity, category, rule, mode, pass, q, orderby, order, page, per_page.
	 * @return array{items:array,total:int,facets:array}
	 */
	public static function query( array $args ) {
		global $wpdb;
		$t = Schema::table( 'issues' );

		// An empty value means "no filter on this column", so the query text is always the same.
		$state = isset( $args['state'] ) ? (string) $args['state'] : 'open';
		if ( 'all' === $state ) {
			$state = '';
		}
		$filters = array();
		foreach ( array( 'severity', 'category', 'mode', 'pass', 'rule' ) as $col ) {
			$filters[ $col ] = ! empty( $args[ $col ] ) ? (string) $args[ $col ] : '';
		}
		$search = ! empty( $args['q'] ) ? (string) $args['q'] : '';
		$like   = '' === $search ? '' : '%' . $wpdb->esc_like( $search ) . '%';
		$vals   = array(
			$state,
			$state,
			$filters['severity'],
			$filters['severity'],
			$filters['category'],
			$filters['category'],
			$filters['mode'],
			$filters['mode'],
			$filters['pass'],
			$filters['pass'],
			$filters['rule'],
			$filters['rule'],
			$search,
			$like,
			$like,
			$like,
			$like,
		);

		$orderby = isset( $args['orderby'] ) ? $args['orderby'] : 'priority';
		$asc     = isset( $args['order'] ) && 'asc' === strtolower( $args['order'] );
		// Whitelisted ORDER BY clauses; nothing from the request reaches the query text.
		switch ( $orderby ) {
			case 'pages':
				$order = $asc ? 'page_count ASC' : 'page_count DESC';
				break;
			case 'severity':
				$order = $asc ? "FIELD(severity,'critical','serious','moderate','minor') DESC" : "FIELD(severity,'critical','serious','moderate','minor') ASC";
				break;
			case 'seen':
				$order = $asc ? 'last_seen ASC' : 'last_seen DESC';
				break;
			default:
				$order = $asc ? self::PRIORITY_SQL . ' ASC' : self::PRIORITY_SQL . ' DESC';
		}
		$per    = max( 1, min( 200, isset( $args['per_page'] ) ? (int) $args['per_page'] : 30 ) );
		$page   = max( 1, isset( $args['page'] ) ? (int) $args['page'] : 1 );
		$offset = ( $page - 1 ) * $per;

		// $order comes from the whitelist above; every value is a placeholder argument.
		$total = (int) $wpdb->get_var( // phpcs:ignore WordPress.DB.DirectDatabaseQuery
			$wpdb->prepare( // phpcs:ignore WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber -- values are spread from an array with one entry per placeholder.
				"SELECT COUNT(*) FROM %i WHERE ( %s = '' OR state = %s ) AND ( %s = '' OR severity = %s ) AND ( %s = '' OR category = %s ) AND ( %s = '' OR mode = %s ) AND ( %s = '' OR pass = %s ) AND ( %s = '' OR rule_id = %s )
				AND ( %s = '' OR selector LIKE %s OR snippet LIKE %s OR rule_id LIKE %s OR sample_url LIKE %s )",
				...array_merge( array( $t ), $vals )
			)
		);
		$rows  = $wpdb->get_results( // phpcs:ignore WordPress.DB.DirectDatabaseQuery,PluginCheck.Security.DirectDB.UnescapedDBParameter -- $order is one of a few fixed strings.
			$wpdb->prepare( // phpcs:ignore WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber -- values are spread from an array with one entry per placeholder.
				"SELECT * FROM %i WHERE ( %s = '' OR state = %s ) AND ( %s = '' OR severity = %s ) AND ( %s = '' OR category = %s ) AND ( %s = '' OR mode = %s ) AND ( %s = '' OR pass = %s ) AND ( %s = '' OR rule_id = %s )
				AND ( %s = '' OR selector LIKE %s OR snippet LIKE %s OR rule_id LIKE %s OR sample_url LIKE %s )
				ORDER BY {$order}, id ASC LIMIT %d OFFSET %d", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- $order is one of a few fixed strings.
				...array_merge( array( $t ), $vals, array( $per, $offset ) )
			),
			ARRAY_A
		);

		$items = array();
		foreach ( (array) $rows as $row ) {
			$items[] = self::shape( $row );
		}
		return array(
			'items'  => $items,
			'total'  => $total,
			'pages'  => (int) ceil( $total / $per ),
			'facets' => self::facets(),
		);
	}

	/**
	 * Counts for the filter chips.
	 *
	 * @return array
	 */
	public static function facets() {
		global $wpdb;
		$t     = Schema::table( 'issues' );
		$out   = array(
			'state'    => array(),
			'severity' => array(),
			'category' => array(),
			'mode'     => array(),
		);
		$state = $wpdb->get_results( $wpdb->prepare( 'SELECT state, COUNT(*) n FROM %i GROUP BY state', $t ), ARRAY_A ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery
		foreach ( (array) $state as $r ) {
			$out['state'][ $r['state'] ] = (int) $r['n'];
		}
		$facets = array(
			'severity' => $wpdb->get_results( $wpdb->prepare( "SELECT severity AS k, COUNT(*) n FROM %i WHERE state = 'open' GROUP BY severity", $t ), ARRAY_A ), // phpcs:ignore WordPress.DB.DirectDatabaseQuery
			'category' => $wpdb->get_results( $wpdb->prepare( "SELECT category AS k, COUNT(*) n FROM %i WHERE state = 'open' GROUP BY category", $t ), ARRAY_A ), // phpcs:ignore WordPress.DB.DirectDatabaseQuery
			'mode'     => $wpdb->get_results( $wpdb->prepare( "SELECT mode AS k, COUNT(*) n FROM %i WHERE state = 'open' GROUP BY mode", $t ), ARRAY_A ), // phpcs:ignore WordPress.DB.DirectDatabaseQuery
		);
		foreach ( $facets as $col => $rows ) {
			foreach ( (array) $rows as $r ) {
				$out[ $col ][ $r['k'] ] = (int) $r['n'];
			}
		}
		return $out;
	}

	/**
	 * Shape a DB row for the API.
	 *
	 * @param array $row Row.
	 * @return array
	 */
	public static function shape( array $row ) {
		$rule = Registry::get( $row['rule_id'] );
		return array(
			'id'               => (int) $row['id'],
			'rule_id'          => $row['rule_id'],
			'title'            => $rule ? $rule['title'] : $row['rule_id'],
			'severity'         => $row['severity'],
			'category'         => $row['category'],
			'pass'             => $row['pass'],
			'mode'             => $row['mode'],
			'wcag'             => $rule ? $rule['wcag'] : null,
			'selector'         => $row['selector'],
			'snippet'          => $row['snippet'],
			'state'            => $row['state'],
			'ignore_reason'    => $row['ignore_reason'],
			'ignore_note'      => $row['ignore_note'],
			'first_seen'       => $row['first_seen'],
			'last_seen'        => $row['last_seen'],
			'fixed_at'         => $row['fixed_at'],
			'page_count'       => (int) $row['page_count'],
			'occurrence_count' => (int) $row['occurrence_count'],
			'sample_url'       => $row['sample_url'],
			'detail'           => $row['detail'] ? json_decode( $row['detail'], true ) : null,
		);
	}

	/**
	 * One issue with its rule copy and affected pages.
	 *
	 * @param int $id Issue id.
	 * @return array|null
	 */
	public static function get( $id ) {
		global $wpdb;
		$t   = Schema::table( 'issues' );
		$row = $wpdb->get_row( $wpdb->prepare( 'SELECT * FROM %i WHERE id = %d', $t, $id ), ARRAY_A ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery
		if ( ! $row ) {
			return null;
		}
		$out          = self::shape( $row );
		$out['rule']  = Registry::get( $row['rule_id'] );
		$out['pages'] = self::pages_for( (int) $id, (int) $row['last_scan'] );
		return $out;
	}

	/**
	 * Pages an issue was seen on in a scan.
	 *
	 * @param int $issue_id Issue.
	 * @param int $scan_id  Scan.
	 * @return array
	 */
	public static function pages_for( $issue_id, $scan_id ) {
		global $wpdb;
		$occ  = Schema::table( 'occurrences' );
		$pg   = Schema::table( 'pages' );
		$rows = $wpdb->get_results( // phpcs:ignore WordPress.DB.DirectDatabaseQuery
			$wpdb->prepare(
				'SELECT p.id, p.url, p.title, p.kind, o.viewport, o.detail FROM %i o JOIN %i p ON p.id = o.page_id WHERE o.issue_id = %d AND o.scan_id = %d ORDER BY p.id ASC LIMIT 200',
				$occ,
				$pg,
				$issue_id,
				$scan_id
			),
			ARRAY_A
		);
		foreach ( $rows as &$r ) {
			$d            = $r['detail'] ? json_decode( $r['detail'], true ) : array();
			$r['snippet'] = is_array( $d ) && isset( $d['snippet'] ) ? $d['snippet'] : '';
			unset( $r['detail'] );
		}
		return (array) $rows;
	}

	/**
	 * Ignore an issue, with a reason.
	 *
	 * @param int    $id     Issue.
	 * @param string $reason false-positive|wont-fix|handled-elsewhere.
	 * @param string $note   Free text.
	 * @return bool
	 */
	public static function ignore( $id, $reason, $note = '' ) {
		global $wpdb;
		$reasons = array( 'false-positive', 'wont-fix', 'handled-elsewhere' );
		if ( ! in_array( $reason, $reasons, true ) ) {
			$reason = 'wont-fix';
		}
		$ok = $wpdb->update( // phpcs:ignore WordPress.DB.DirectDatabaseQuery
			Schema::table( 'issues' ),
			array(
				'state'         => 'ignored',
				'ignore_reason' => $reason,
				'ignore_note'   => sanitize_textarea_field( $note ),
			),
			array( 'id' => (int) $id )
		);
		self::sync_latest_score();
		return false !== $ok;
	}

	/**
	 * Re-open an ignored issue.
	 *
	 * @param int $id Issue.
	 * @return bool
	 */
	public static function reopen( $id ) {
		global $wpdb;
		$ok = $wpdb->update( // phpcs:ignore WordPress.DB.DirectDatabaseQuery
			Schema::table( 'issues' ),
			array(
				'state'         => 'open',
				'ignore_reason' => null,
				'ignore_note'   => null,
			),
			array( 'id' => (int) $id )
		);
		self::sync_latest_score();
		return false !== $ok;
	}

	/**
	 * Keep the latest scan's stored score in step with ignore/reopen.
	 *
	 * @return void
	 */
	private static function sync_latest_score() {
		$latest = Scans::latest_complete();
		if ( $latest ) {
			Scans::update( (int) $latest['id'], array( 'score' => Score::site()['score'] ) );
		}
	}

	/**
	 * "Top fixes": rules ranked by severity x pages affected.
	 *
	 * @param int $limit Limit.
	 * @return array
	 */
	public static function top_fixes( $limit = 6 ) {
		global $wpdb;
		$t    = Schema::table( 'issues' );
		$occ  = Schema::table( 'occurrences' );
		$rows = (array) $wpdb->get_results( // phpcs:ignore WordPress.DB.DirectDatabaseQuery
			$wpdb->prepare(
				"SELECT i.rule_id, i.severity, COUNT(DISTINCT i.id) AS issues, COUNT(DISTINCT o.page_id) AS pages
				FROM %i i LEFT JOIN %i o ON o.issue_id = i.id AND o.scan_id = i.last_scan
				WHERE i.state = 'open' AND i.mode = 'automated' GROUP BY i.rule_id, i.severity",
				$t,
				$occ
			),
			ARRAY_A
		);
		$w    = Score::weights();
		$site = Score::site();
		$pen  = $site['penalty'];
		$all  = (array) $wpdb->get_results( $wpdb->prepare( "SELECT rule_id, severity, page_count FROM %i WHERE state = 'open' AND mode = 'automated'", $t ), ARRAY_A ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery
		$by   = array();
		foreach ( $all as $r ) {
			$by[ $r['rule_id'] ] = ( isset( $by[ $r['rule_id'] ] ) ? $by[ $r['rule_id'] ] : 0 ) + Score::issue_penalty( $r['severity'], $r['page_count'] );
		}
		$out = array();
		foreach ( $rows as $r ) {
			$rule = Registry::get( $r['rule_id'] );
			if ( ! $rule ) {
				continue;
			}
			$rank  = ( isset( $w[ $r['severity'] ] ) ? $w[ $r['severity'] ] : 1 ) * max( 1, (int) $r['pages'] );
			$gain  = Score::to_score( max( 0, $pen - ( isset( $by[ $r['rule_id'] ] ) ? $by[ $r['rule_id'] ] : 0 ) ) ) - $site['score'];
			$out[] = array(
				'rule_id'  => $r['rule_id'],
				'title'    => $rule['title'],
				'severity' => $r['severity'],
				'category' => $rule['category'],
				'issues'   => (int) $r['issues'],
				'pages'    => (int) $r['pages'],
				'rank'     => $rank,
				'gain'     => max( 0, $gain ),
				'fix'      => $rule['fix'],
			);
		}
		usort(
			$out,
			static function ( $a, $b ) {
				return $b['rank'] <=> $a['rank'];
			}
		);
		return array_slice( $out, 0, $limit );
	}

	/**
	 * Open-issue counts by severity (automated and needs-review separately).
	 *
	 * @return array
	 */
	public static function counts() {
		global $wpdb;
		$t    = Schema::table( 'issues' );
		$rows = (array) $wpdb->get_results( $wpdb->prepare( "SELECT severity, mode, COUNT(*) n FROM %i WHERE state = 'open' GROUP BY severity, mode", $t ), ARRAY_A ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery
		$out  = array(
			'severity' => array(
				'critical' => 0,
				'serious'  => 0,
				'moderate' => 0,
				'minor'    => 0,
			),
			'review'   => 0,
			'open'     => 0,
		);
		foreach ( $rows as $r ) {
			$out['open'] += (int) $r['n'];
			if ( 'automated' === $r['mode'] ) {
				$out['severity'][ $r['severity'] ] += (int) $r['n'];
			} else {
				$out['review'] += (int) $r['n'];
			}
		}
		return $out;
	}
}