<?php
/**
 * Accesswell_DB
 *
 * Results table: the latest check of each page, saved by the site scan or
 * when an editor checks a page while browsing.
 *
 * @package Accesswell
 */

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

class Accesswell_DB {

	const DB_VERSION = '1';

	/** Most issues kept per page. */
	const MAX_ISSUES = 300;

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

	public static function install() {
		global $wpdb;
		require_once ABSPATH . 'wp-admin/includes/upgrade.php';
		$table   = self::table();
		$charset = $wpdb->get_charset_collate();

		dbDelta(
			"CREATE TABLE {$table} (
				id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
				url_hash char(32) NOT NULL,
				page_url text NOT NULL,
				post_id bigint(20) unsigned NOT NULL DEFAULT 0,
				checked_at datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
				errors int(11) unsigned NOT NULL DEFAULT 0,
				warnings int(11) unsigned NOT NULL DEFAULT 0,
				notices int(11) unsigned NOT NULL DEFAULT 0,
				issues longtext NOT NULL,
				PRIMARY KEY  (id),
				UNIQUE KEY url_hash (url_hash),
				KEY post_id (post_id)
			) {$charset};"
		);

		update_option( 'accesswell_db_version', self::DB_VERSION, false );
	}

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

	public static function drop() {
		global $wpdb;
		$wpdb->query( $wpdb->prepare( 'DROP TABLE IF EXISTS %i', self::table() ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.DirectDatabaseQuery.SchemaChange -- Plugin-owned table, removed on uninstall.
		delete_option( 'accesswell_db_version' );
	}

	/**
	 * Store the result of checking a page.
	 *
	 * @param string $url     Page address.
	 * @param int    $post_id Post shown on that page, or 0.
	 * @param array  $issues  Clean issues: { rule, impact, wcag, message, selector, snippet }.
	 */
	public static function save( $url, $post_id, array $issues ) {
		global $wpdb;

		$issues = array_slice( $issues, 0, self::MAX_ISSUES );
		$counts = array(
			'error'   => 0,
			'warning' => 0,
			'notice'  => 0,
		);
		foreach ( $issues as $issue ) {
			++$counts[ $issue['impact'] ];
		}

		$hash = md5( $url );
		$data = array(
			'url_hash'   => $hash,
			'page_url'   => $url,
			'post_id'    => (int) $post_id,
			'checked_at' => current_time( 'mysql', true ),
			'errors'     => $counts['error'],
			'warnings'   => $counts['warning'],
			'notices'    => $counts['notice'],
			'issues'     => wp_json_encode( $issues ),
		);
		$format = array( '%s', '%s', '%d', '%s', '%d', '%d', '%d', '%s' );

		$id = $wpdb->get_var( $wpdb->prepare( 'SELECT id FROM %i WHERE url_hash = %s', self::table(), $hash ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Plugin-owned table.
		if ( $id ) {
			$wpdb->update( self::table(), $data, array( 'id' => (int) $id ), $format, array( '%d' ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Plugin-owned table.
		} else {
			$wpdb->insert( self::table(), $data, $format ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- Plugin-owned table.
		}
	}

	/**
	 * Every checked page, newest first, with issues decoded.
	 *
	 * @return array
	 */
	public static function all() {
		global $wpdb;
		$rows = $wpdb->get_results( $wpdb->prepare( 'SELECT * FROM %i ORDER BY errors DESC, warnings DESC, checked_at DESC', self::table() ), ARRAY_A ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Plugin-owned table.
		foreach ( $rows as &$row ) {
			$decoded       = json_decode( (string) $row['issues'], true );
			$row['issues'] = is_array( $decoded ) ? $decoded : array();
		}
		return $rows;
	}

	/**
	 * @param string $url Page address.
	 * @return array|null
	 */
	public static function get( $url ) {
		global $wpdb;
		$row = $wpdb->get_row( $wpdb->prepare( 'SELECT * FROM %i WHERE url_hash = %s', self::table(), md5( $url ) ), ARRAY_A ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Plugin-owned table.
		if ( $row ) {
			$decoded       = json_decode( (string) $row['issues'], true );
			$row['issues'] = is_array( $decoded ) ? $decoded : array();
		}
		return $row;
	}

	public static function clear() {
		global $wpdb;
		$wpdb->query( $wpdb->prepare( 'DELETE FROM %i', self::table() ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Plugin-owned table.
	}
}