<?php
/**
 * Versioned DB migrator.
 *
 * Runs incremental upgrades on the activity log table when the plugin
 * version changes. Each migration is idempotent.
 *
 * @package ActivityPilot
 */

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

// Schema migrations require direct database access.

final class APWPM_Migrator {

	/**
	 * Run all pending migrations.
	 */
	public static function migrate() {
		$installed = (string) get_option( 'apwpm_db_version', '0' );

		if ( version_compare( $installed, '1.1.0', '<' ) ) {
			self::migrate_to_1_1_0();
			update_option( 'apwpm_db_version', '1.1.0' );
		}

		if ( version_compare( $installed, '1.2.0', '<' ) ) {
			self::migrate_to_1_2_0();
			update_option( 'apwpm_db_version', '1.2.0' );
		}

		if ( version_compare( $installed, '1.3.0', '<' ) ) {
			self::migrate_to_1_3_0();
			update_option( 'apwpm_db_version', '1.3.0' );
		}

		if ( version_compare( $installed, '1.4.0', '<' ) ) {
			self::migrate_to_1_4_0();
			update_option( 'apwpm_db_version', '1.4.0' );
		}
	}

	/**
	 * 1.4.0 — rename short `ap_` storage identifiers to the `apwpm_` prefix.
	 */
	protected static function migrate_to_1_4_0() {
		global $wpdb;

		$prefix = isset( $wpdb->base_prefix ) ? $wpdb->base_prefix : $wpdb->prefix;

		$tables = array(
			'ap_activity_logs' => 'apwpm_activity_logs',
			'ap_comments'      => 'apwpm_comments',
			'ap_views'         => 'apwpm_views',
		);

		foreach ( $tables as $from => $to ) {
			APWPM_DB::rename_table( $prefix . $from, $prefix . $to );
		}

		$options = array(
			'ap_settings',
			'ap_db_version',
			'ap_hashchain_secret',
			'ap_hashchain_last',
			'ap_file_hashes',
			'ap_debug_log_offset',
			'ap_pii_salt',
		);

		foreach ( $options as $old_key ) {
			$new_key = 'apwpm_' . substr( $old_key, 3 );
			if ( false === get_option( $new_key, false ) && false !== get_option( $old_key, false ) ) {
				update_option( $new_key, get_option( $old_key ), false );
				delete_option( $old_key );
			}
		}

		$legacy_crons = array(
			'ap_daily_cleanup',
			'ap_insights_scan',
			'ap_tail_debug_log',
			'ap_queue_drain',
			'ap_file_integrity_scan',
			'ap_send_digest',
		);
		foreach ( $legacy_crons as $hook ) {
			wp_clear_scheduled_hook( $hook );
		}
	}

	/**
	 * 1.3.0 — adds columns to support hash chain, workflow (status/assignee),
	 * geolocation, and a comments table.
	 */
	protected static function migrate_to_1_3_0() {
		global $wpdb;

		$table_name      = APWPM_DB::table();
		$charset_collate = $wpdb->get_charset_collate();
		$logs_sql        = "CREATE TABLE {$table_name} (
			id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
			blog_id BIGINT(20) UNSIGNED NOT NULL DEFAULT 0,
			user_id BIGINT(20) UNSIGNED NOT NULL DEFAULT 0,
			user_login VARCHAR(60) NOT NULL DEFAULT '',
			user_role VARCHAR(60) NOT NULL DEFAULT '',
			action_type VARCHAR(60) NOT NULL DEFAULT '',
			object_type VARCHAR(60) NOT NULL DEFAULT '',
			object_id BIGINT(20) UNSIGNED NOT NULL DEFAULT 0,
			object_title VARCHAR(255) NOT NULL DEFAULT '',
			description TEXT NULL,
			context LONGTEXT NULL,
			row_hash CHAR(64) NOT NULL DEFAULT '',
			prev_hash CHAR(64) NOT NULL DEFAULT '',
			severity VARCHAR(20) NOT NULL DEFAULT 'info',
			status VARCHAR(20) NOT NULL DEFAULT 'open',
			assignee_id BIGINT(20) UNSIGNED NOT NULL DEFAULT 0,
			is_anomaly TINYINT(1) NOT NULL DEFAULT 0,
			rolled_back TINYINT(1) NOT NULL DEFAULT 0,
			rollback_of BIGINT(20) UNSIGNED NOT NULL DEFAULT 0,
			ip_address VARCHAR(45) NOT NULL DEFAULT '',
			country VARCHAR(8) NOT NULL DEFAULT '',
			city VARCHAR(80) NOT NULL DEFAULT '',
			user_agent VARCHAR(255) NOT NULL DEFAULT '',
			session_id VARCHAR(64) NOT NULL DEFAULT '',
			created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
			PRIMARY KEY  (id),
			KEY blog_id (blog_id),
			KEY user_id (user_id),
			KEY action_type (action_type),
			KEY object_type (object_type),
			KEY object_id (object_id),
			KEY severity (severity),
			KEY status (status),
			KEY assignee (assignee_id),
			KEY country (country),
			KEY is_anomaly (is_anomaly),
			KEY rollback_of (rollback_of),
			KEY session_id (session_id),
			KEY created_at (created_at),
			FULLTEXT KEY ft_description (description)
		) {$charset_collate};";

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

		$prefix         = isset( $wpdb->base_prefix ) ? $wpdb->base_prefix : $wpdb->prefix;
		$comments_table = $prefix . 'apwpm_comments';
		$sql            = "CREATE TABLE {$comments_table} (
			id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
			log_id BIGINT(20) UNSIGNED NOT NULL DEFAULT 0,
			user_id BIGINT(20) UNSIGNED NOT NULL DEFAULT 0,
			user_login VARCHAR(60) NOT NULL DEFAULT '',
			body TEXT NOT NULL,
			created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
			PRIMARY KEY  (id),
			KEY log_id (log_id),
			KEY user_id (user_id)
		) {$charset_collate};";
		dbDelta( $sql );

		// Saved filter views table.
		$views_table = $prefix . 'apwpm_views';
		$sql         = "CREATE TABLE {$views_table} (
			id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
			user_id BIGINT(20) UNSIGNED NOT NULL DEFAULT 0,
			name VARCHAR(120) NOT NULL DEFAULT '',
			filters LONGTEXT NULL,
			created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
			PRIMARY KEY  (id),
			KEY user_id (user_id)
		) {$charset_collate};";
		dbDelta( $sql );
	}

	/**
	 * 1.1.0 — adds blog_id column for multisite support and an
	 * 'is_anomaly' marker for the AI insights subsystem.
	 */
	protected static function migrate_to_1_1_0() {
		self::migrate_to_1_3_0();
	}

	/**
	 * 1.2.0 — adds a 'rolled_back' marker so users can see which events
	 * have already been reverted. Adds a 'rollback_of' reference column.
	 */
	protected static function migrate_to_1_2_0() {
		self::migrate_to_1_3_0();
	}
}