<?php
/**
 * The plugin's own tables (docs/05 §4), created with dbDelta on activation and on upgrade.
 *
 * @package GetReport
 */

defined( 'ABSPATH' ) || exit;

/**
 * Table names, schema and upgrades.
 */
class GetReport_Db {

	/** Bump when a CREATE TABLE below changes. */
	const VERSION = 1;
	const OPTION  = 'getreport_db_version';

	/**
	 * A table's full name.
	 *
	 * @param string $name bot_hits | bot_daily | ref_daily | audit | fixes.
	 */
	public static function table( $name ) {
		global $wpdb;
		return $wpdb->prefix . 'gr_' . $name;
	}

	/** Creates or upgrades the tables when the schema version changed. */
	public static function maybe_upgrade() {
		if ( (int) get_option( self::OPTION, 0 ) >= self::VERSION ) {
			return;
		}
		self::install();
	}

	/** Creates or upgrades every table. */
	public static function install() {
		global $wpdb;
		require_once ABSPATH . 'wp-admin/includes/upgrade.php';
		$c = $wpdb->get_charset_collate();

		// Paths are stored without query strings or fragments and cut to 191 characters (§3.4).
		dbDelta(
			'CREATE TABLE ' . self::table( 'bot_hits' ) . " (
  id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  day date NOT NULL,
  bot varchar(40) NOT NULL,
  path varchar(191) NOT NULL,
  ip_prefix varchar(50) NOT NULL DEFAULT '',
  verified tinyint(1) NOT NULL DEFAULT 0,
  status smallint(5) unsigned NOT NULL DEFAULT 0,
  source varchar(10) NOT NULL DEFAULT 'live',
  PRIMARY KEY  (id),
  KEY day_bot (day,bot)
) $c;"
		);
		dbDelta(
			'CREATE TABLE ' . self::table( 'bot_daily' ) . " (
  day date NOT NULL,
  bot varchar(40) NOT NULL,
  verified tinyint(1) NOT NULL DEFAULT 0,
  hits int(10) unsigned NOT NULL DEFAULT 0,
  unique_paths int(10) unsigned NOT NULL DEFAULT 0,
  PRIMARY KEY  (day,bot,verified)
) $c;"
		);
		dbDelta(
			'CREATE TABLE ' . self::table( 'ref_daily' ) . " (
  day date NOT NULL,
  source varchar(30) NOT NULL,
  path varchar(191) NOT NULL,
  count int(10) unsigned NOT NULL DEFAULT 0,
  PRIMARY KEY  (day,source,path)
) $c;"
		);
		dbDelta(
			'CREATE TABLE ' . self::table( 'audit' ) . " (
  post_id bigint(20) unsigned NOT NULL,
  lang varchar(12) NOT NULL DEFAULT '',
  score tinyint(3) unsigned NOT NULL DEFAULT 0,
  subs text NOT NULL,
  check_ids text NOT NULL,
  words int(10) unsigned NOT NULL DEFAULT 0,
  audited_at datetime NOT NULL,
  PRIMARY KEY  (post_id),
  KEY score (score)
) $c;"
		);
		dbDelta(
			'CREATE TABLE ' . self::table( 'fixes' ) . " (
  id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  fix_key varchar(40) NOT NULL,
  target varchar(191) NOT NULL DEFAULT '',
  before_value longtext NULL,
  after_value longtext NULL,
  applied_by bigint(20) unsigned NOT NULL DEFAULT 0,
  applied_at datetime NOT NULL,
  reverted_at datetime NULL,
  PRIMARY KEY  (id),
  KEY fix_key (fix_key)
) $c;"
		);
		update_option( self::OPTION, self::VERSION, false );
	}

	/** Drops every table (uninstall with "Delete all data" on). */
	public static function drop() {
		global $wpdb;
		foreach ( array( 'bot_hits', 'bot_daily', 'ref_daily', 'audit', 'fixes' ) as $t ) {
			$wpdb->query( $wpdb->prepare( 'DROP TABLE IF EXISTS %i', self::table( $t ) ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- the plugin's own tables, on uninstall
		}
		delete_option( self::OPTION );
	}
}