<?php
/**
 * Persistence. One table for issues, one option for the last scan's metadata.
 *
 * @package AdinavAccessibilityAuditEAA
 */

defined( 'ABSPATH' ) || exit;

class ADNVAA_Store {

	const DB_VERSION  = '1';
	const OPT_DB      = 'adnvaa_db_version';
	const OPT_SCAN    = 'adnvaa_last_scan';
	const OPT_SETTINGS = 'adnvaa_settings';

	public static function table() {
		global $wpdb;
		return $wpdb->prefix . 'adnvaa_issues';
	}

	public static function install() {
		global $wpdb;

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

		$sql = "CREATE TABLE {$table} (
			id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
			scan_id VARCHAR(32) NOT NULL DEFAULT '',
			url TEXT NOT NULL,
			rule VARCHAR(64) NOT NULL DEFAULT '',
			severity VARCHAR(16) NOT NULL DEFAULT '',
			selector TEXT NOT NULL,
			snippet TEXT NOT NULL,
			detail TEXT NOT NULL,
			found_at DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
			PRIMARY KEY (id),
			KEY scan_id (scan_id),
			KEY rule (rule),
			KEY severity (severity)
		) {$collate};";

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

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

	public static function maybe_upgrade() {
		if ( get_option( self::OPT_DB ) !== self::DB_VERSION ) {
			self::install();
		}
	}

	public static function start_scan() {
		global $wpdb;
		// A new scan replaces the old one. Keeping history is a paid-tier feature;
		// the free tier should not quietly grow a table forever on someone's host.
		// The table name is built from $wpdb->prefix and a hard-coded suffix. It can
		// never contain user input, and a table identifier cannot be passed through
		// $wpdb->prepare() in any case.
		// phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
		$wpdb->query( 'DELETE FROM ' . self::table() );
		return substr( md5( uniqid( 'aae', true ) ), 0, 32 );
	}

	public static function add_issues( $scan_id, $url, array $issues ) {
		global $wpdb;

		$now = current_time( 'mysql' );

		foreach ( $issues as $issue ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			$wpdb->insert(
				self::table(),
				array(
					'scan_id'  => $scan_id,
					'url'      => $url,
					'rule'     => $issue['rule'],
					'severity' => $issue['severity'],
					'selector' => $issue['selector'],
					'snippet'  => $issue['snippet'],
					'detail'   => $issue['detail'],
					'found_at' => $now,
				),
				array( '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s' )
			);
		}
	}

	public static function get_issues( $args = array() ) {
		global $wpdb;

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

		if ( ! empty( $args['severity'] ) ) {
			$where[]  = 'severity = %s';
			$params[] = $args['severity'];
		}
		if ( ! empty( $args['rule'] ) ) {
			$where[]  = 'rule = %s';
			$params[] = $args['rule'];
		}

		$sql = 'SELECT * FROM ' . self::table() . ' WHERE ' . implode( ' AND ', $where )
			. " ORDER BY FIELD(severity,'critical','serious','moderate','advisory'), rule, id ASC";

		if ( $params ) {
			// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
			$sql = $wpdb->prepare( $sql, $params );
		}

		// Every value is bound above; only the table identifier is interpolated, and
		// it comes from $wpdb->prefix, never from user input.
		// phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
		return $wpdb->get_results( $sql );
	}

	public static function counts_by_severity() {
		global $wpdb;
		// phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
		$rows = $wpdb->get_results( 'SELECT severity, COUNT(*) AS n FROM ' . self::table() . ' GROUP BY severity' );

		$out = array(
			ADNVAA_Rules::CRITICAL => 0,
			ADNVAA_Rules::SERIOUS  => 0,
			ADNVAA_Rules::MODERATE => 0,
			ADNVAA_Rules::ADVISORY => 0,
		);
		foreach ( (array) $rows as $r ) {
			$out[ $r->severity ] = (int) $r->n;
		}
		return $out;
	}

	public static function counts_by_rule() {
		global $wpdb;
		// phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
		$rows = $wpdb->get_results( 'SELECT rule, severity, COUNT(*) AS n FROM ' . self::table() . ' GROUP BY rule, severity' );
		$out  = array();
		foreach ( (array) $rows as $r ) {
			$out[ $r->rule ] = array(
				'n'        => (int) $r->n,
				'severity' => $r->severity,
			);
		}
		uasort(
			$out,
			static function ( $a, $b ) {
				$sa = ADNVAA_Rules::severity_rank( $a['severity'] );
				$sb = ADNVAA_Rules::severity_rank( $b['severity'] );
				return $sa === $sb ? $b['n'] - $a['n'] : $sa - $sb;
			}
		);
		return $out;
	}

	public static function save_scan_meta( array $meta ) {
		update_option( self::OPT_SCAN, $meta, false );
	}

	public static function get_scan_meta() {
		return get_option( self::OPT_SCAN, array() );
	}

	public static function settings() {
		return wp_parse_args(
			(array) get_option( self::OPT_SETTINGS, array() ),
			array(
				'entity_name'   => get_bloginfo( 'name' ),
				'contact_email' => get_bloginfo( 'admin_email' ),
				// Where regression alerts go. Defined here, in the free build,
				// even though only the premium monitor reads it: the free build
				// owns the settings form, and a key it does not know about is a
				// key it silently deletes on the next save.
				'alert_email'   => get_bloginfo( 'admin_email' ),
			)
		);
	}
}