<?php
/**
 * Creates and upgrades the plugin's custom database tables.
 *
 * @package ABDS_SEO
 */

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

/**
 * Schema installer/upgrader for the plugin's custom tables: the core SEO
 * set (scans, page_scores, migration_log, changes_log, rank_history), the
 * Stage 5 CRO tables (cro_events raw/append-only, cro_heatmap aggregated
 * bins, cro_rage rage events + masked fragments), and the R1 audit_* set
 * (audit_runs, audit_urls, audit_issues) — schema only for now, created
 * empty and unread until R3's background site-wide audit writes to them.
 * audit_* is the intended eventual replacement for scans/page_scores (a
 * normalized per-issue model vs. the older per-page issues_json blob);
 * that migration/deprecation is its own deliberate R3 step, not part of
 * this schema-only addition, so audit_issues.severity uses the R3-target
 * vocabulary (critical/high/medium/low/notice, per master TZ §52) rather
 * than matching page_scores.issues_json's older critical/warning/info set.
 * Upgrades are additive via dbDelta — a version bump adds the new tables
 * without touching existing data.
 */
class ABDS_SEO_DB_Installer {

	/**
	 * Option name the installed schema version is stored under.
	 */
	const DB_VERSION_OPTION = 'abds_seo_db_version';

	/**
	 * Single source of truth for the `abds_seo_*` table suffixes this
	 * installer owns. `drop_tables()` derives its list from this constant
	 * (rather than a second hand-typed array) so the two can't drift apart;
	 * `install()`'s CREATE TABLE blocks still name each table explicitly
	 * for SQL readability, but every suffix added there must be added here
	 * too — DbInstallerTest asserts the two stay in sync.
	 */
	const TABLE_SUFFIXES = array(
		'scans',
		'page_scores',
		'migration_log',
		'changes_log',
		'rank_history',
		'cro_events',
		'cro_heatmap',
		'cro_rage',
		'audit_runs',
		'audit_urls',
		'audit_issues',
	);

	/**
	 * Creates (or upgrades) all custom tables via dbDelta.
	 */
	public static function install() {
		global $wpdb;

		require_once ABSPATH . 'wp-admin/includes/upgrade.php';

		$charset_collate = $wpdb->get_charset_collate();

		$scans_table         = $wpdb->prefix . 'abds_seo_scans';
		$page_scores_table   = $wpdb->prefix . 'abds_seo_page_scores';
		$migration_log_table = $wpdb->prefix . 'abds_seo_migration_log';
		$changes_log_table   = $wpdb->prefix . 'abds_seo_changes_log';
		$rank_history_table  = $wpdb->prefix . 'abds_seo_rank_history';
		$cro_events_table    = $wpdb->prefix . 'abds_seo_cro_events';
		$cro_heatmap_table   = $wpdb->prefix . 'abds_seo_cro_heatmap';
		$cro_rage_table      = $wpdb->prefix . 'abds_seo_cro_rage';
		$audit_runs_table    = $wpdb->prefix . 'abds_seo_audit_runs';
		$audit_urls_table    = $wpdb->prefix . 'abds_seo_audit_urls';
		$audit_issues_table  = $wpdb->prefix . 'abds_seo_audit_issues';

		$sql = "CREATE TABLE {$scans_table} (
			id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
			started_at DATETIME NOT NULL,
			finished_at DATETIME NULL,
			status VARCHAR(20) NOT NULL DEFAULT 'running',
			pages_scanned INT UNSIGNED NOT NULL DEFAULT 0,
			avg_score DECIMAL(5,2) NULL,
			PRIMARY KEY  (id),
			KEY status (status)
		) {$charset_collate};

		CREATE TABLE {$page_scores_table} (
			id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
			post_id BIGINT UNSIGNED NOT NULL,
			technical_score DECIMAL(5,2) NULL,
			content_score DECIMAL(5,2) NULL,
			issues_json LONGTEXT NULL,
			last_scanned DATETIME NOT NULL,
			PRIMARY KEY  (id),
			UNIQUE KEY post_id (post_id),
			KEY last_scanned (last_scanned)
		) {$charset_collate};

		CREATE TABLE {$migration_log_table} (
			id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
			source_plugin VARCHAR(50) NOT NULL,
			started_at DATETIME NOT NULL,
			finished_at DATETIME NULL,
			pages_total INT UNSIGNED NOT NULL DEFAULT 0,
			pages_migrated INT UNSIGNED NOT NULL DEFAULT 0,
			status VARCHAR(20) NOT NULL DEFAULT 'running',
			PRIMARY KEY  (id),
			KEY source_plugin (source_plugin)
		) {$charset_collate};

		CREATE TABLE {$changes_log_table} (
			id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
			post_id BIGINT UNSIGNED NOT NULL,
			user_id BIGINT UNSIGNED NOT NULL,
			change_type VARCHAR(50) NOT NULL,
			before_value LONGTEXT NULL,
			after_value LONGTEXT NULL,
			created_at DATETIME NOT NULL,
			PRIMARY KEY  (id),
			KEY post_id (post_id),
			KEY created_at (created_at)
		) {$charset_collate};

		CREATE TABLE {$rank_history_table} (
			id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
			post_id BIGINT UNSIGNED NULL,
			keyword VARCHAR(191) NOT NULL,
			location VARCHAR(191) NULL,
			position SMALLINT UNSIGNED NULL,
			checked_at DATETIME NOT NULL,
			PRIMARY KEY  (id),
			KEY keyword_checked (keyword, checked_at),
			KEY post_id (post_id)
		) {$charset_collate};

		CREATE TABLE {$cro_events_table} (
			id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
			session_hash CHAR(64) NOT NULL,
			post_id BIGINT UNSIGNED NOT NULL,
			event_type VARCHAR(20) NOT NULL,
			x SMALLINT UNSIGNED NULL,
			y SMALLINT UNSIGNED NULL,
			scroll_depth TINYINT UNSIGNED NULL,
			device VARCHAR(20) NOT NULL DEFAULT 'desktop',
			created_at DATETIME NOT NULL,
			PRIMARY KEY  (id),
			KEY post_created (post_id, created_at),
			KEY created_at (created_at)
		) {$charset_collate};

		CREATE TABLE {$cro_heatmap_table} (
			id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
			post_id BIGINT UNSIGNED NOT NULL,
			map_type VARCHAR(20) NOT NULL,
			device VARCHAR(20) NOT NULL DEFAULT 'desktop',
			x_bucket SMALLINT UNSIGNED NOT NULL DEFAULT 0,
			y_bucket SMALLINT UNSIGNED NOT NULL DEFAULT 0,
			scroll_bucket TINYINT UNSIGNED NOT NULL DEFAULT 0,
			hits INT UNSIGNED NOT NULL DEFAULT 0,
			updated_at DATETIME NOT NULL,
			PRIMARY KEY  (id),
			UNIQUE KEY bin (post_id, map_type, device, x_bucket, y_bucket, scroll_bucket),
			KEY post_map (post_id, map_type)
		) {$charset_collate};

		CREATE TABLE {$cro_rage_table} (
			id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
			session_hash CHAR(64) NOT NULL,
			post_id BIGINT UNSIGNED NOT NULL,
			x SMALLINT UNSIGNED NULL,
			y SMALLINT UNSIGNED NULL,
			click_count TINYINT UNSIGNED NOT NULL DEFAULT 0,
			fragment LONGTEXT NULL,
			created_at DATETIME NOT NULL,
			PRIMARY KEY  (id),
			KEY post_created (post_id, created_at),
			KEY created_at (created_at)
		) {$charset_collate};

		CREATE TABLE {$audit_runs_table} (
			id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
			status VARCHAR(20) NOT NULL DEFAULT 'queued',
			trigger_type VARCHAR(20) NOT NULL DEFAULT 'manual',
			started_at DATETIME NULL,
			finished_at DATETIME NULL,
			urls_total INT UNSIGNED NOT NULL DEFAULT 0,
			urls_completed INT UNSIGNED NOT NULL DEFAULT 0,
			urls_failed INT UNSIGNED NOT NULL DEFAULT 0,
			issues_found INT UNSIGNED NOT NULL DEFAULT 0,
			avg_score DECIMAL(5,2) NULL,
			initiated_by BIGINT UNSIGNED NULL,
			meta_json LONGTEXT NULL,
			created_at DATETIME NOT NULL,
			PRIMARY KEY  (id),
			KEY status (status),
			KEY started_at (started_at)
		) {$charset_collate};

		CREATE TABLE {$audit_urls_table} (
			id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
			run_id BIGINT UNSIGNED NOT NULL,
			object_type VARCHAR(20) NOT NULL DEFAULT 'post',
			object_id BIGINT UNSIGNED NULL,
			url TEXT NOT NULL,
			url_hash CHAR(64) NOT NULL,
			status VARCHAR(20) NOT NULL DEFAULT 'pending',
			http_status SMALLINT UNSIGNED NULL,
			score DECIMAL(5,2) NULL,
			issues_count INT UNSIGNED NOT NULL DEFAULT 0,
			checked_at DATETIME NULL,
			created_at DATETIME NOT NULL,
			PRIMARY KEY  (id),
			KEY run_id (run_id),
			KEY url_hash (url_hash),
			KEY object (object_type, object_id),
			KEY status (status)
		) {$charset_collate};

		CREATE TABLE {$audit_issues_table} (
			id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
			run_id BIGINT UNSIGNED NOT NULL,
			audit_url_id BIGINT UNSIGNED NOT NULL,
			issue_key VARCHAR(100) NOT NULL,
			category VARCHAR(50) NOT NULL,
			severity VARCHAR(20) NOT NULL,
			message TEXT NULL,
			details_json LONGTEXT NULL,
			status VARCHAR(20) NOT NULL DEFAULT 'open',
			created_at DATETIME NOT NULL,
			resolved_at DATETIME NULL,
			PRIMARY KEY  (id),
			KEY run_id (run_id),
			KEY audit_url_id (audit_url_id),
			KEY category_severity (category, severity),
			KEY issue_key (issue_key)
		) {$charset_collate};";

		dbDelta( $sql );

		update_option( self::DB_VERSION_OPTION, ABDS_SEO_DB_VERSION );
	}

	/**
	 * Re-runs dbDelta if the stored DB version is behind the plugin's.
	 * Hooked to `plugins_loaded` so upgrades between plugin versions are picked up
	 * without requiring reactivation.
	 */
	public static function maybe_upgrade() {
		if ( get_option( self::DB_VERSION_OPTION ) !== ABDS_SEO_DB_VERSION ) {
			self::install();
			self::migrate_legacy_minor_severity();
		}
	}

	/**
	 * One-time data fix-up for the Stage 1 -> Stage 2 rename of
	 * ABDS_SEO_Site_Scanner::SEVERITY_MINOR ('minor') to SEVERITY_INFO
	 * ('info'): rewrites the persisted "severity" value inside every
	 * wp_abds_seo_page_scores.issues_json blob that still has the old
	 * string, so already-scanned pages don't keep reporting a severity
	 * level that no longer exists anywhere else in the plugin's output.
	 * The WHERE clause makes this a no-op once no legacy rows remain, so
	 * it's safe to call unconditionally alongside install() rather than
	 * needing its own separate one-shot version gate.
	 */
	protected static function migrate_legacy_minor_severity(): void {
		global $wpdb;
		$table = $wpdb->prefix . 'abds_seo_page_scores';

		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $table is built only from $wpdb->prefix plus a hardcoded literal, never user input; the LIKE value is a hardcoded literal passed through $wpdb->prepare(). Runs once per DB-version bump inside maybe_upgrade(), not a cacheable read path.
		$rows = $wpdb->get_results( $wpdb->prepare( "SELECT id, issues_json FROM {$table} WHERE issues_json LIKE %s", '%"severity":"minor"%' ) );

		foreach ( $rows as $row ) {
			$issues = json_decode( $row->issues_json, true );

			if ( ! is_array( $issues ) ) {
				continue;
			}

			foreach ( $issues as &$issue ) {
				if ( isset( $issue['severity'] ) && 'minor' === $issue['severity'] ) {
					$issue['severity'] = 'info';
				}
			}
			unset( $issue );

			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $table is built only from $wpdb->prefix plus a hardcoded literal, never user input; $wpdb->update() safely escapes its values via the format array. Runs once per DB-version bump inside maybe_upgrade(), not a cacheable read path.
			$wpdb->update(
				$table,
				array( 'issues_json' => wp_json_encode( $issues ) ),
				array( 'id' => $row->id ),
				array( '%s' ),
				array( '%d' )
			);
		}
	}

	/**
	 * Drops all custom tables. Only called from uninstall.php when the user
	 * opts out of retaining plugin data.
	 */
	public static function drop_tables() {
		global $wpdb;

		foreach ( self::TABLE_SUFFIXES as $suffix ) {
			$table = $wpdb->prefix . 'abds_seo_' . $suffix;
			// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $wpdb->prepare() cannot parameterize identifiers; $table is built only from $wpdb->prefix plus one of the hardcoded TABLE_SUFFIXES entries, never from user input. This is DDL that only runs once, on explicit uninstall, so query caching does not apply.
			$wpdb->query( "DROP TABLE IF EXISTS `{$table}`" );
		}

		delete_option( self::DB_VERSION_OPTION );
	}
}