<?php
/**
 * Database tables.
 *
 * @package PageSpeedBenchmark
 */

namespace PageSpeedBenchmark;

defined( 'ABSPATH' ) || exit;

/**
 * Creates and upgrades the plugin tables.
 */
class Schema {

	const DB_VERSION        = 2;
	const DB_VERSION_OPTION = 'psbench_db_version';

	/**
	 * Prefix that pre-release builds used for their tables and options.
	 */
	const LEGACY_PREFIX = 'psb_';

	/**
	 * Samples table name.
	 */
	public static function samples_table(): string {
		global $wpdb;

		return $wpdb->prefix . 'psbench_samples';
	}

	/**
	 * Runs table name.
	 */
	public static function runs_table(): string {
		global $wpdb;

		return $wpdb->prefix . 'psbench_runs';
	}

	/**
	 * Activation: create tables and default settings.
	 */
	public static function install(): void {
		global $wpdb;
		require_once ABSPATH . 'wp-admin/includes/upgrade.php';

		self::migrate_legacy_prefix();

		$charset = $wpdb->get_charset_collate();
		$samples = self::samples_table();
		$runs    = self::runs_table();

		dbDelta(
			"CREATE TABLE {$samples} (
				id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
				created_at datetime NOT NULL,
				source varchar(10) NOT NULL DEFAULT 'visit',
				run_id bigint(20) unsigned DEFAULT NULL,
				req_id char(36) DEFAULT NULL,
				request_type varchar(10) NOT NULL DEFAULT 'page',
				path varchar(255) NOT NULL DEFAULT '',
				template varchar(191) NOT NULL DEFAULT '',
				user_id bigint(20) unsigned NOT NULL DEFAULT 0,
				status smallint(5) unsigned DEFAULT NULL,
				total_ms int(10) unsigned DEFAULT NULL,
				ttfb_ms int(10) unsigned DEFAULT NULL,
				queries int(10) unsigned DEFAULT NULL,
				sql_ms int(10) unsigned DEFAULT NULL,
				http_count smallint(5) unsigned DEFAULT NULL,
				http_ms int(10) unsigned DEFAULT NULL,
				memory_kb int(10) unsigned DEFAULT NULL,
				html_bytes int(10) unsigned DEFAULT NULL,
				detail longtext DEFAULT NULL,
				PRIMARY KEY  (id),
				KEY created_at (created_at),
				KEY path_created (path(191), created_at),
				KEY run_id (run_id),
				KEY req_id (req_id)
			) {$charset};"
		);

		dbDelta(
			"CREATE TABLE {$runs} (
				id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
				label varchar(191) NOT NULL DEFAULT '',
				status varchar(10) NOT NULL DEFAULT 'queued',
				created_by bigint(20) unsigned NOT NULL DEFAULT 0,
				created_at datetime NOT NULL,
				finished_at datetime DEFAULT NULL,
				heartbeat_at datetime DEFAULT NULL,
				pages longtext NOT NULL,
				runs_per_page smallint(5) unsigned NOT NULL DEFAULT 5,
				total int(10) unsigned NOT NULL DEFAULT 0,
				done int(10) unsigned NOT NULL DEFAULT 0,
				current_path varchar(255) NOT NULL DEFAULT '',
				PRIMARY KEY  (id),
				KEY status (status)
			) {$charset};"
		);

		update_option( self::DB_VERSION_OPTION, self::DB_VERSION, false );
		Settings::ensure_defaults();
	}

	/**
	 * Upgrade tables after a plugin update that changed them.
	 */
	public static function maybe_upgrade(): void {
		if ( (int) get_option( self::DB_VERSION_OPTION ) < self::DB_VERSION ) {
			self::install();
		}
	}

	/**
	 * Pre-release builds used the prefix "psb"; 1.0.0 uses "psbench"
	 * (WordPress.org asks for a prefix of at least four characters). Move
	 * tables, settings and the cron event over once, so an upgrade keeps
	 * every sample and snapshot.
	 */
	private static function migrate_legacy_prefix(): void {
		global $wpdb;

		foreach ( array(
			'samples' => self::samples_table(),
			'runs'    => self::runs_table(),
		) as $name => $table ) {
			$legacy = $wpdb->prefix . self::LEGACY_PREFIX . $name;
			if ( self::table_exists( $legacy ) && ! self::table_exists( $table ) ) {
				$wpdb->query( $wpdb->prepare( 'RENAME TABLE %i TO %i', $legacy, $table ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- one-time move of the plugin's own tables to the new prefix.
			}
		}

		$legacy_settings = get_option( self::LEGACY_PREFIX . 'settings' );
		if ( is_array( $legacy_settings ) && false === get_option( Settings::OPTION ) ) {
			add_option( Settings::OPTION, $legacy_settings, '', false );
		}
		delete_option( self::LEGACY_PREFIX . 'settings' );
		delete_option( self::LEGACY_PREFIX . 'db_version' );
		wp_clear_scheduled_hook( self::LEGACY_PREFIX . 'purge_old_samples' );
	}

	/**
	 * Whether a table exists.
	 *
	 * @param string $table Full table name.
	 */
	private static function table_exists( string $table ): bool {
		global $wpdb;

		return $table === $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $wpdb->esc_like( $table ) ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- one-time check of the plugin's own tables.
	}
}