<?php
namespace Burst\Admin\DB_Upgrade;

use Burst\Admin\Reports\DomainTypes\Report_Content_Block;
use Burst\Admin\Reports\DomainTypes\Report_Day_Of_Week;
use Burst\Admin\Reports\DomainTypes\Report_Format;
use Burst\Admin\Reports\DomainTypes\Report_Frequency;
use Burst\Admin\Reports\DomainTypes\Report_Week_Of_Month;
use Burst\Admin\Reports\Report;
use Burst\Traits\Admin_Helper;
use Burst\Traits\Database_Helper;
use Burst\Traits\Helper;
use Burst\Traits\Sanitize;

defined( 'ABSPATH' ) || die();
class DB_Upgrade {
	use Admin_Helper;
	use Database_Helper;
	use Helper;
	use Sanitize;

	private int $cron_interval = MINUTE_IN_SECONDS;
	private int $batch         = 100000;

	/**
	 * DB_Upgrade constructor.
	 */
	public function init(): void {
		add_action( 'burst_daily', [ $this, 'upgrade' ] );
		add_action( 'admin_init', [ $this, 'maybe_fire_upgrade' ] );
		add_action( 'burst_upgrade_iteration', [ $this, 'upgrade' ] );
		add_filter( 'burst_tasks', [ $this, 'add_progress_notice' ] );
	}

	/**
	 * Ensure upgrade progress even when Cron is not working, when the user visits the dashboard.
	 */
	public function maybe_fire_upgrade(): void {
		// no data is used from the form data. only compared.
        //phpcs:ignore
		if ( isset( $_GET['page'] ) && $_GET['page'] === 'burst' ) {
			$this->upgrade();
		}
	}

	/**
	 * If there is any upgrade running
	 */
	public function progress_complete(): bool {
		return $this->get_progress( 'all', 'all' ) >= 100;
	}

	/**
	 * Add a notice about the progress to the admin dashboard in burst.
	 * phpcs:ignore
     * @param array $warnings //array of warnings in burst.
     * phpcs:ignore
     * @return array
	 */
	public function add_progress_notice( array $warnings ): array {
		$progress = $this->get_progress( 'all', BURST_VERSION );
		if ( $progress < 100 ) {
			$progress   = round( $progress, 2 );
			$warnings[] = [
				'id'          => 'upgrade_progress',
				'condition'   => [
					'type'     => 'serverside',
					'function' => '!(new \Burst\Admin\DB_Upgrade\DB_Upgrade() )->progress_complete()',
				],
				'status'      => 'all',
				'msg'         => $this->sprintf(
					// translators: %s: progress of the upgrade.
					__( 'An upgrade is running in the background, and is currently at %s.', 'burst-statistics' ),
					$progress . '%'
				) . ' ' .
					__( 'For large databases this process may take a while. Your data will be tracked as usual.', 'burst-statistics' ),
				'icon'        => 'open',
				'dismissible' => false,
			];
		}

		return $warnings;
	}

	/**
	 * Get progress of the upgrade process
	 */
	public function get_progress( string $type, string $version ): float {
		$total_upgrades     = $this->get_db_upgrades( $type, $version );
		$remaining_upgrades = $total_upgrades;
		// check if all upgrades are done.
		$count_remaining_upgrades = 0;
		$intermediate_percentage  = 0;
		$intermediates            = [];
		foreach ( $remaining_upgrades as $upgrade ) {
			// if any upgrade is not done.
			if ( get_option( "burst_db_upgrade_$upgrade" ) ) {
				++$count_remaining_upgrades;
				// check if there's an intermediate progress count. If so, we add it as a percentage to the progress.
				$has_intermediate = get_transient( "burst_progress_$upgrade" );
				if ( $has_intermediate ) {
					$intermediates[ $upgrade ] = $has_intermediate;
				}
			}
		}
		$intermediate         = reset( $intermediates );
		$count_total_upgrades = count( $total_upgrades );
		// upgrade percentage for one upgrade is 100 / total upgrades.
		$upgrade_percentage_one_upgrade = $count_total_upgrades === 0 ? 100 : 100 / $count_total_upgrades;
		if ( $intermediate ) {
			$intermediate_percentage = $intermediate * $upgrade_percentage_one_upgrade;
		}
		$count_total_upgrades = 0 === $count_total_upgrades ? 1 : $count_total_upgrades;

		$percentage = 100 - ( $count_remaining_upgrades / $count_total_upgrades ) * 100;
		$percentage = $percentage + $intermediate_percentage;
		if ( $percentage > 100 ) {
			$percentage = 100;
		}

		return $percentage;
	}

	/**
	 * Init the upgrades
	 * - upgrade only if admin is logged in
	 * - only one upgrade at a time
	 */
	public function upgrade(): void {
		if ( defined( 'BURST_NO_UPGRADE' ) && BURST_NO_UPGRADE ) {
			return;
		}
		if ( ! $this->has_admin_access() ) {
			return;
		}
		$upgrade_running = get_transient( 'burst_upgrade_running' );
		if ( $upgrade_running ) {
			return;
		}
		set_transient( 'burst_upgrade_running', true, 60 );
		// check if we need to upgrade.
		$db_upgrades = $this->get_db_upgrades( 'free', 'all' );
		// check if all upgrades are done.
		$do_upgrade = false;
		foreach ( $db_upgrades as $upgrade ) {
			// if any upgrade is not done.
			if ( get_option( "burst_db_upgrade_$upgrade" ) ) {
				$do_upgrade = $upgrade;
				// if we need to upgrade break the loop.
				break;
			}
		}

		// @phpstan-ignore-next-line.
		if ( WP_DEBUG ) {
			// log all upgrades that still need to be done.
			foreach ( $db_upgrades as $upgrade ) {
				if ( get_option( "burst_db_upgrade_$upgrade" ) ) {
					self::error_log( "Upgrade $upgrade still needs to be done." );
				}
			}
		}

		// ensure that the tasks get updated with the continuing upgrade process.
		if ( $do_upgrade ) {
			\Burst\burst_loader()->admin->tasks->schedule_task_validation();
		}
		// only one upgrade at a time.
		if ( 'bounces' === $do_upgrade ) {
			$this->upgrade_bounces();
		}
		if ( 'goals_remove_columns' === $do_upgrade ) {
			$this->upgrade_goals_remove_columns();
		}
		if ( 'goals_set_conversion_metric' === $do_upgrade ) {
			$this->upgrade_goals_set_conversion_metric();
		}
		if ( 'drop_user_agent' === $do_upgrade ) {
			$this->upgrade_drop_user_agent();
		}
		if ( 'empty_referrer_when_current_domain' === $do_upgrade ) {
			$this->upgrade_empty_referrer_when_current_domain();
		}
		if ( 'strip_domain_names_from_entire_page_url' === $do_upgrade ) {
			$this->upgrade_strip_domain_names_from_entire_page_url();
		}

		if ( 'create_lookup_tables' === $do_upgrade ) {
			$this->create_lookup_tables();
		}
		if ( 'init_lookup_ids' === $do_upgrade ) {
			$this->initialize_lookup_ids();
		}
		if ( 'upgrade_lookup_tables' === $do_upgrade ) {
			$this->upgrade_lookup_tables();
		}
		if ( 'upgrade_lookup_tables_drop_columns' === $do_upgrade ) {
			$this->upgrade_lookup_tables_drop_columns();
		}

		if ( 'drop_page_id_column' === $do_upgrade ) {
			$this->upgrade_drop_page_id_column();
		}

		if ( 'rename_entire_page_url_column' === $do_upgrade ) {
			$this->change_column_name_entire_page_url();
		}

		if ( 'drop_path_from_parameters_column' === $do_upgrade ) {
			$this->drop_path_from_parameters_column();
		}

		if ( 'fix_missing_session_ids' === $do_upgrade ) {
			delete_option( 'burst_db_upgrade_fix_missing_session_ids' );
		}

		if ( 'clean_orphaned_session_ids' === $do_upgrade ) {
			delete_option( 'burst_db_upgrade_clean_orphaned_session_ids' );
		}

		if ( 'add_page_ids' === $do_upgrade ) {
			$this->upgrade_add_page_ids();
		}

		if ( 'move_referrers_to_sessions' === $do_upgrade ) {
			$this->upgrade_referrers();
		}

		if ( 'fix_trailing_slash_on_referrers' === $do_upgrade ) {
			$this->fix_trailing_slash_on_referrers();
		}

		if ( 'move_reports_to_new_tables' === $do_upgrade ) {
			$this->move_reports_to_new_tables();
		}

		if ( 'move_columns_to_sessions' === $do_upgrade ) {
			$this->upgrade_move_columns_to_sessions();
		}

		// check free progress, because pro upgrades are hooked to burst_upgrade_iteration.
		if ( $this->get_progress( 'free', 'all' ) < 100 ) {
			// free upgrades not finished yet.
			wp_schedule_single_event( time() + $this->cron_interval, 'burst_upgrade_iteration' );
		} else {
			wp_clear_scheduled_hook( 'burst_upgrade_iteration' );
			// if pro upgrades are not finished yet, do them.
			if ( $this->get_progress( 'pro', 'all' ) < 100 ) {
				delete_transient( 'burst_upgrade_running' );
				do_action( 'burst_upgrade_pro_iteration' );
			}
		}

		delete_transient( 'burst_upgrade_running' );
	}

	/**
	 * Move reports to new tables
	 */
	private function move_reports_to_new_tables(): void {
		if ( ! $this->has_admin_access() ) {
			return;
		}

		if ( ! $this->table_exists( 'burst_reports' ) ) {
			return;
		}

		$option_name = 'burst_db_upgrade_move_reports_to_new_tables';
		if ( ! get_option( $option_name ) ) {
			return;
		}

		// Check if old reports table exists.
		$old_reports = $this->get_option( 'email_reports_mailinglist' );

		if ( empty( $old_reports ) ) {
			delete_option( $option_name );
			return;
		}

		$reports_to_migrate = [];

		foreach ( $old_reports as $old_report ) {

			if ( ! isset( $old_report['email'] ) ) {
				continue;
			}

			$frequency = $old_report['frequency'] ?? 'weekly';

			$reports_to_migrate[ $frequency ][] = $old_report['email'];
		}

		foreach ( $reports_to_migrate as $frequency => $emails ) {
			$report = new Report();

			$week_of_month = Report_Frequency::MONTHLY === $frequency ? Report_Week_Of_Month::FIRST : Report_Week_Of_Month::default();
			$day_of_week   = Report_Frequency::WEEKLY === $frequency || Report_Frequency::MONTHLY === $frequency ? Report_Day_Of_Week::MONDAY : Report_Day_Of_Week::default();
			$send_time     = '09:00';
			$content       = defined( 'BURST_PRO' ) ? Report_Content_Block::all() : Report_Content_Block::default();

			$report->set_format( Report_Format::default() )
					->set_frequency( $frequency )
					->set_day_of_week( $day_of_week )
					->set_week_of_month( $week_of_month )
					->set_send_time( $send_time )
					->set_content( $content )
					->set_recipients( $emails )
					->set_enabled( true )
					->set_scheduled( true );

			$report->save();
		}

		burst_delete_option( 'email_reports_mailinglist' );
		delete_option( $option_name );
	}

	/**
	 * Get the upgrades
	 *
	 * @return string[]
	 */
	protected function get_db_upgrades( string $plugin_type, string $select_version ): array {
		$upgrades = apply_filters(
			'burst_db_upgrades',
			[
				'1.4.2.1' => [
					'bounces',
					'goals_remove_columns',
				],
				'1.5.2'   => [
					'goals_set_conversion_metric',
				],
				'1.5.3'   => [
					'empty_referrer_when_current_domain',
					'strip_domain_names_from_entire_page_url',
					'drop_user_agent',
				],
				'1.7.0'   => [
					'create_lookup_tables',
					'init_lookup_ids',
					'upgrade_lookup_tables',

					// the below upgrades are handled within the create and upgrade look up tables functions, but are added here for the progress calculation.
					'create_lookup_tables_browser',
					'create_lookup_tables_browser_version',
					'create_lookup_tables_platform',
					'create_lookup_tables_device',
					'upgrade_lookup_tables_browser',
					'upgrade_lookup_tables_browser_version',
					'upgrade_lookup_tables_platform',
					'upgrade_lookup_tables_device',
					// end progress only upgrade items.

					'upgrade_lookup_tables_drop_columns',
					'drop_page_id_column',
				],
				'1.7.1'   => [
					'rename_entire_page_url_column',
					'drop_path_from_parameters_column',
				],
				'2.0.4'   => [
					'fix_missing_session_ids',
					'clean_orphaned_session_ids',
				],
				'2.2.6'   => [
					'add_page_ids',
				],
				'3.1.4'   => [
					'move_referrers_to_sessions',
				],
				'3.1.4.1' => [
					'fix_trailing_slash_on_referrers',
				],
				'3.2.0'   => [
					'move_reports_to_new_tables',
				],
				'3.2.3'   => [
					'move_columns_to_sessions',
				],
			]
		);

		// Get all upgrades from all versions.
		$all_upgrades = [];
		foreach ( $upgrades as $upgrade_version => $upgrade ) {
			$all_upgrades = array_merge( $all_upgrades, $upgrade );
		}

		if ( $plugin_type === 'all' && $select_version === 'all' ) {
			return $all_upgrades;
		}

		// Handle special selectors for pro and free upgrades.
		if ( $plugin_type === 'pro' ) {
			// Get only pro upgrades - these are determined by filter and will have 'pro_' prefix.
			$pro_upgrades = [];
			foreach ( $all_upgrades as $upgrade ) {
				if ( strpos( $upgrade, 'pro_' ) === 0 ) {
					$pro_upgrades[] = $upgrade;
				}
			}
			return $pro_upgrades;
		}

		if ( $plugin_type === 'free' ) {
			// Get only free upgrades - these don't have 'pro_' prefix.
			$free_upgrades = [];
			foreach ( $all_upgrades as $upgrade ) {
				if ( strpos( $upgrade, 'pro_' ) !== 0 ) {
					$free_upgrades[] = $upgrade;
				}
			}
			return $free_upgrades;
		}

		// Handle version-based selection (original behavior).
		$version_upgrades = [];
		foreach ( $upgrades as $upgrade_version => $upgrade ) {
			if ( version_compare( $upgrade_version, $select_version, '>=' ) ) {
				$version_upgrades = array_merge( $version_upgrades, $upgrade );
			}
		}
		return $version_upgrades;
	}

	/**
	 * Upgrade bounces
	 */
	private function upgrade_bounces(): void {
		if ( ! $this->has_admin_access() ) {
			return;
		}
		if ( ! get_option( 'burst_db_upgrade_bounces' ) ) {
			return;
		}

		global $wpdb;

		$result = $wpdb->query(
			"UPDATE {$wpdb->prefix}burst_statistics
                SET bounce = 0
                WHERE
                  (session_id IN (
                    SELECT session_id
                    FROM (
                      SELECT session_id
                      FROM {$wpdb->prefix}burst_statistics
                      GROUP BY session_id
                      HAVING COUNT(*) >= 2
                    ) as t
                  ))"
		);

		if ( $result === false ) {
			return;
		}

		$result = $wpdb->query(
			"UPDATE {$wpdb->prefix}burst_statistics
                SET bounce = 0
                WHERE bounce = 1 AND time_on_page > 5000"
		);

		// if query is successful.
		if ( $result !== false ) {
			delete_option( 'burst_db_upgrade_bounces' );
		} else {
			self::error_log( 'db upgrade bounces failed' );
		}
	}

	/**
	 * Drop event and action columns from the goals table
	 */
	private function upgrade_goals_remove_columns(): void {
		if ( ! $this->has_admin_access() ) {
			return;
		}
		if ( ! get_option( 'burst_db_upgrade_goals_remove_columns' ) ) {
			return;
		}

		global $wpdb;
		// check if columns exist first.
		$columns = $wpdb->get_col( "DESC {$wpdb->prefix}burst_goals", 0 );
		if ( ! in_array( 'event', $columns, true ) || ! in_array( 'action', $columns, true ) ) {
			delete_option( 'burst_db_upgrade_goals_remove_columns' );
			return;
		}

		// run an sql query to remove the columns `event` and `action`.
		$remove = $wpdb->query(
			"ALTER TABLE {$wpdb->prefix}burst_goals
                DROP COLUMN `event`,
                DROP COLUMN `action`"
		);

		if ( $remove !== false ) {
			delete_option( 'burst_db_upgrade_goals_remove_columns' );
		}
	}

	/**
	 * Set the conversion metric to pageviews for all goals.
	 */
	private function upgrade_goals_set_conversion_metric(): void {
		if ( ! $this->has_admin_access() ) {
			return;
		}
		$option_name = 'burst_db_upgrade_goals_set_conversion_metric';
		if ( ! get_option( $option_name ) ) {
			return;
		}

		global $wpdb;
		// set conversion_metric to 'pageviews' for all goals.
		$add_conversion_metric = $wpdb->query(
			"UPDATE {$wpdb->prefix}burst_goals
                SET conversion_metric = 'pageviews'
                WHERE conversion_metric IS NULL OR conversion_metric = ''"
		);

		if ( $add_conversion_metric !== false ) {
			delete_option( $option_name );
		}
	}

	/**
	 * Drop the user agent column from the statistics table
	 */
	private function upgrade_drop_user_agent(): void {
		if ( ! $this->has_admin_access() ) {
			return;
		}

		$option_name = 'burst_db_upgrade_drop_user_agent';
		if ( ! get_option( $option_name ) ) {
			return;
		}

		global $wpdb;
		// check if columns exist first.
		$columns = $wpdb->get_col( "DESC {$wpdb->prefix}burst_statistics", 0 );
		if ( ! in_array( 'user_agent', $columns, true ) ) {
			delete_option( 'burst_db_upgrade_drop_user_agent' );
			return;
		}

		// drop user_agent column.
		$drop_user_agent = $wpdb->query( "ALTER TABLE {$wpdb->prefix}burst_statistics DROP COLUMN `user_agent`" );

		if ( $drop_user_agent !== false ) {
			delete_option( $option_name );
		}
	}

	/**
	 * Drop the page_id column from the statistics table
	 */
	private function upgrade_empty_referrer_when_current_domain(): void {
		if ( ! $this->has_admin_access() ) {
			return;
		}
		$option_name = 'burst_db_upgrade_empty_referrer_when_current_domain';
		if ( ! get_option( $option_name ) ) {
			return;
		}

		global $wpdb;
		$home_url = home_url();
		// empty referrer when starts with current domain.
        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- $home_url is sanitized by home_url().
		$empty_referrer_when_current_domain = $wpdb->query( "UPDATE {$wpdb->prefix}burst_statistics SET referrer = null WHERE referrer LIKE '$home_url%'" );

		if ( $empty_referrer_when_current_domain !== false ) {
			delete_option( $option_name );
		}
	}

	/**
	 * Drop the page_id column from the statistics table
	 */
	private function upgrade_strip_domain_names_from_entire_page_url(): void {
		if ( ! $this->has_admin_access() ) {
			return;
		}
		$option_name = 'burst_db_upgrade_strip_domain_names_from_entire_page_url';
		if ( ! get_option( $option_name ) ) {
			return;
		}

		if ( ! $this->column_exists( 'burst_statistics', 'entire_page_url' ) ) {
			delete_option( $option_name );
			return;
		}

		global $wpdb;
		// make sure it does not end with slash.
		$home_url = untrailingslashit( home_url() );

		// strip home url from entire_page_url where it starts with home_url.
        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- $home_url is sanitized by home_url().
		$strip_domain_names_from_entire_page_url = $wpdb->query( "UPDATE {$wpdb->prefix}burst_statistics SET entire_page_url = REPLACE(entire_page_url, '$home_url', '') WHERE entire_page_url LIKE '$home_url%'" );
		if ( $strip_domain_names_from_entire_page_url !== false ) {
			delete_option( $option_name );
		}
	}

	/**
	 * Upgrade statistics table to use lookup tables instead.
	 */
	private function create_lookup_tables(): void {
		if ( ! $this->has_admin_access() ) {
			return;
		}
		global $wpdb;
		$items = [ 'device', 'browser', 'browser_version', 'platform' ];
		// check if required tables exists.
		$selected_item = false;
		foreach ( $items as $item ) {
			$table = $item . 's';
			// check if table exists.
			if ( ! $this->table_exists( 'burst_' . $table ) ) {
				return;
			}

			// check if this table already was upgraded.
			if ( ! get_option( "burst_db_upgrade_create_lookup_tables_$item" ) ) {
				continue;
			}

			$selected_item = $item;
			break;
		}

		if ( $selected_item ) {
			// check if the $selected_item column exists in the wp_burst_statistics table.
			if ( ! $this->column_exists( 'burst_statistics', $selected_item ) ) {
				// already dropped, so mark this one as completed.
				delete_option( "burst_db_upgrade_create_lookup_tables_$selected_item" );

				// if all other lookup tables also have been dropped, stop all upgrades, as there's nothing to upgrade.
				if (
					! get_option( 'burst_db_upgrade_upgrade_lookup_tables_browser' ) &&
					! get_option( 'burst_db_upgrade_upgrade_lookup_tables_browser_version' ) &&
					! get_option( 'burst_db_upgrade_upgrade_lookup_tables_platform' ) &&
					! get_option( 'burst_db_upgrade_upgrade_lookup_tables_device' )
				) {
					delete_option( 'burst_db_upgrade_create_lookup_tables' );
					delete_option( 'burst_db_upgrade_init_lookup_ids' );
					delete_option( 'burst_db_upgrade_upgrade_lookup_tables' );
					delete_option( 'burst_db_upgrade_upgrade_lookup_tables_drop_columns' );
				}
				return;
			}
			$wpdb->query(
            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- $selected_item is selected from a predefined array.
				"INSERT INTO {$wpdb->prefix}burst_{$selected_item}s (name) SELECT DISTINCT $selected_item FROM {$wpdb->prefix}burst_statistics WHERE $selected_item IS NOT NULL AND $selected_item NOT IN (SELECT name FROM {$wpdb->prefix}burst_{$selected_item}s);"
			);
			delete_option( "burst_db_upgrade_create_lookup_tables_$selected_item" );
		}

		// check if all items have been created.
		$missing_items = [];
		foreach ( $items as $item ) {
			// check if table is updated with data yet.
			if ( ! get_option( "burst_db_upgrade_create_lookup_tables_$item" ) ) {
				continue;
			}
			$missing_items[] = $item;
		}

		// stop upgrading if all have been completed.
		if ( count( $missing_items ) === 0 ) {
			delete_option( 'burst_db_upgrade_create_lookup_tables' );
		}
	}

	/**
	 * To reliably be able to check if the upgrade is completed, we set an initial bogus value for the lookup id's.
	 */
	private function initialize_lookup_ids(): void {
		if ( ! $this->has_admin_access() ) {
			return;
		}

		// only start if the lookup tables have been created.
		if ( get_option( 'burst_db_upgrade_create_lookup_tables' ) ) {
			return;
		}

		if ( ! get_option( 'burst_db_upgrade_upgrade_lookup_tables' ) ) {
			return;
		}

		global $wpdb;
		$wpdb->query(
			"UPDATE {$wpdb->prefix}burst_statistics SET
                           browser_id = 999999,
                           browser_version_id = 999999,
                           platform_id = 999999,
                           device_id = 999999"
		);

		delete_option( 'burst_db_upgrade_init_lookup_ids' );
	}

	/**
	 * Upgrade existing table to load id's from lookup tables
	 */
	private function upgrade_lookup_tables(): void {
		if ( ! $this->has_admin_access() ) {
			return;
		}

		// only start if the lookup tables have been created.
		if ( get_option( 'burst_db_upgrade_create_lookup_tables' ) ) {
			return;
		}

		if ( get_option( 'burst_db_upgrade_init_lookup_ids' ) ) {
			return;
		}

		if ( ! get_option( 'burst_db_upgrade_upgrade_lookup_tables' ) ) {
			return;
		}

		global $wpdb;
		// check if required tables exists.
		$items         = [ 'browser', 'browser_version', 'device', 'platform' ];
		$selected_item = false;
		foreach ( $items as $item ) {
			$table = $item . 's';
			// check if table exists. If not, start the create upgrade again.
			if ( ! $this->table_exists( 'burst_' . $table ) ) {
				update_option( "burst_db_upgrade_create_lookup_tables_$item", true, false );
				update_option( 'burst_db_upgrade_create_lookup_tables', true, false );
				return;
			}

			// check if this table contains data.
			// if not, ensure that the update for this table is started again.
            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- $item is selected from a predefined array.
			$count = (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$wpdb->prefix}burst_{$item}s" );
			if ( 0 === $count ) {
				update_option( "burst_db_upgrade_create_lookup_tables_$item", true, false );
				update_option( 'burst_db_upgrade_create_lookup_tables', true, false );
				return;
			}

			// check if this table already was upgraded.
			if ( ! get_option( "burst_db_upgrade_upgrade_lookup_tables_$item" ) ) {
				continue;
			}

			// check if column exists.
			$columns = $wpdb->get_col( "DESC {$wpdb->prefix}burst_statistics" );
			if ( ! in_array( $item . '_id', $columns, true ) ) {
				// already dropped, so mark this one as completed.
				delete_option( "burst_db_upgrade_upgrade_lookup_tables_$item" );
				continue;
			}
			$selected_item = $item;
		}

		// we have lookup tables with values. Now we can upgrade the statistics table.
		if ( $selected_item ) {
			$batch         = $this->batch;
			$selected_item = $this->sanitize_lookup_table_type( $selected_item );
			$start         = microtime( true );
			// check what's still to do.
            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared	 -- $selected_item is selected from a predefined array.
			$remaining_count = (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$wpdb->prefix}burst_statistics where {$selected_item}_id = 999999" );
			$total_count     = (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$wpdb->prefix}burst_statistics" );
			$done_count      = $total_count - $remaining_count;

			// store progress for $selected_item, to show it in the progress notice.
			$progress = 0 === $total_count ? 1 : $done_count / $total_count;
			$progress = round( $progress, 2 );

			if ( ! $this->column_exists( 'burst_statistics', $selected_item ) ) {
				// already dropped, so mark this one as completed.
				delete_option( "burst_db_upgrade_upgrade_lookup_tables_$selected_item" );
				return;
			}

			set_transient( "burst_progress_upgrade_lookup_tables_$selected_item", $progress, HOUR_IN_SECONDS );
			// measure time elapsed during query.
			if ( $done_count < $total_count ) {
                // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- $selected_item is selected from a predefined array.
				$wpdb->query(
					"UPDATE {$wpdb->prefix}burst_statistics AS t
                    JOIN (
                        SELECT p.{$selected_item}, p.ID, COALESCE(m.ID, 0) as {$selected_item}_id
                        FROM {$wpdb->prefix}burst_statistics p
                        LEFT JOIN {$wpdb->prefix}burst_{$selected_item}s m ON p.{$selected_item} = m.name
                        WHERE p.{$selected_item}_id = 999999
                        LIMIT $batch
                    ) AS s ON t.ID = s.ID
                    SET t.{$selected_item}_id = s.{$selected_item}_id;"
				);
                // phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared
				$end               = microtime( true );
				$time_elapsed_secs = $end - $start;
			} else {
				// completed upgrade.
				delete_option( "burst_db_upgrade_upgrade_lookup_tables_$selected_item" );
				delete_transient( "burst_progress_upgrade_lookup_tables_$selected_item" );
			}
		}

		// check if all items have been upgraded.
		$total_not_completed = 0;
		foreach ( $items as $item ) {
            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- $item is selected from a predefined array.
			$count = (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$wpdb->prefix}burst_statistics WHERE {$item}_id = 999999 " );
			if ( 0 === $count ) {
				delete_option( "burst_db_upgrade_upgrade_lookup_tables_$item" );
				delete_transient( "burst_progress_upgrade_lookup_tables_$item" );
			}
			$total_not_completed += $count;
		}

		// stop upgrading if all have been completed.
		if ( 0 === $total_not_completed ) {
			delete_option( 'burst_db_upgrade_upgrade_lookup_tables' );
		}
	}

	/**
	 * Upgrade the database to use page_ids for pages.
	 */
	private function upgrade_add_page_ids(): void {
		if ( ! $this->has_admin_access() ) {
			return;
		}

		// check if required upgrade has been completed.
		if ( ! get_option( 'burst_db_upgrade_add_page_ids' ) ) {
			return;
		}

		// check if the columsn page_id and page_type are created.
		if ( ! $this->column_exists( 'burst_statistics', 'page_id' ) || ! $this->column_exists( 'burst_statistics', 'page_type' ) ) {
			return;
		}

		// get all posts of type post or page that do not have the meta yet.
		$post_types = apply_filters( 'burst_column_post_types', get_post_types( [ 'public' => true ] ) );
		$posts      = get_posts(
			[
				'post_type'   => $post_types,
				'post_status' => 'publish',
				'numberposts' => 5,
                //phpcs:ignore
				'meta_query'  => [
					[
						'key'     => 'burst_page_id_upgraded',
						'compare' => 'NOT EXISTS',
					],
				],
			]
		);

		global $wpdb;
		$post_types   = array_values( $post_types );
		$placeholders = implode( ',', array_fill( 0, count( $post_types ), '%s' ) );
		$sql          = "SELECT COUNT(*) FROM {$wpdb->posts}
                 WHERE post_type IN ($placeholders)
                 AND post_status != 'trash'
                 AND ID NOT IN (
                     SELECT post_id FROM {$wpdb->postmeta}
                     WHERE meta_key = 'burst_page_id_upgraded')";
		// dynamic placeholder insertion with array_fill.
        //phpcs:ignore
        $sql =$wpdb->prepare($sql, ...$post_types);
        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- $post_types are sanitized post types.
		$total_count = (int) $wpdb->get_var( $sql );
		$done_count  = (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$wpdb->postmeta} WHERE meta_key = 'burst_page_id_upgraded'" );

		// store progress for $selected_item, to show it in the progress notice.
		$progress = 0 === $total_count ? 1 : $done_count / $total_count;
		$progress = round( $progress, 2 );
		set_transient( 'burst_progress_add_page_ids', $progress, HOUR_IN_SECONDS );
		if ( count( $posts ) === 0 || $total_count === 0 ) {
			delete_option( 'burst_db_upgrade_add_page_ids' );
			delete_post_meta_by_key( 'burst_page_id_upgraded' );
		}
		$permalink_structure = get_option( 'permalink_structure' );
		$is_plain_permalinks = empty( $permalink_structure );
		if ( ! empty( $posts ) ) {
			foreach ( $posts as $post ) {
				update_post_meta( $post->ID, 'burst_page_id_upgraded', 1 );
				$post_id   = $post->ID;
				$page_url  = get_permalink( $post_id );
				$post_type = get_post_type( $post_id );
				// Strip home_url from page_url.
				$page_url = str_replace( home_url(), '', $page_url );
				// plain permalinks.
				if ( $is_plain_permalinks ) {
					$param_key = ( $post_type === 'page' ) ? "page_id=$post_id" : "p=$post_id";
					$wpdb->query(
						$wpdb->prepare(
							"UPDATE {$wpdb->prefix}burst_statistics
                             SET page_id = %d, page_type = %s
                             WHERE page_url='/' AND parameters LIKE %s",
							$post_id,
							$post_type,
							$wpdb->esc_like( $param_key ) . '%'
						)
					);
				} else {
					$wpdb->query(
						$wpdb->prepare(
							"UPDATE {$wpdb->prefix}burst_statistics
                         SET page_id = %d, page_type = %s
                         WHERE page_url = %s",
							$post_id,
							$post_type,
							$page_url,
						)
					);
				}
			}
		}
	}

	/**
	 * Drop the columns that are now obsolete and moved to the lookup tables.
	 */
	private function upgrade_lookup_tables_drop_columns(): void {
		if ( ! $this->has_admin_access() ) {
			return;
		}

		// check if required upgrade has been completed.
		if ( get_option( 'burst_db_upgrade_upgrade_lookup_tables' ) ) {
			return;
		}

		global $wpdb;
		$drop_columns = [ 'browser', 'browser_version', 'device_resolution', 'device', 'platform' ];

		// check if columns exist first.
		$columns    = $wpdb->get_col( "DESC {$wpdb->prefix}burst_statistics", 0 );
		$drop_array = [];
		foreach ( $drop_columns as $drop_column ) {
			if ( get_option( "burst_db_upgrade_upgrade_lookup_tables_$drop_column" ) ) {
				continue;
			}
			if ( in_array( $drop_column, $columns, true ) ) {
				$drop_array[] = "DROP COLUMN `$drop_column`";
			}
		}

		$drop_sql = implode( ', ', $drop_array );
		$sql      = "ALTER TABLE {$wpdb->prefix}burst_statistics $drop_sql";
        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- $drop_sql is built from validated column names.
		$success = $wpdb->query( $sql );

		// check if all columns have been dropped.
		if ( $success ) {
			$completed = true;
			foreach ( $drop_columns as $drop_column ) {
				if ( in_array( $drop_column, $columns, true ) ) {
					$completed = false;
				}
			}
			if ( $completed ) {
				delete_option( 'burst_db_upgrade_upgrade_lookup_tables_drop_columns' );
			}
		}
	}


	/**
	 * Drop the page_id column from the statistics table.
	 */
	private function upgrade_drop_page_id_column(): void {
		if ( ! $this->has_admin_access() ) {
			return;
		}
		if ( ! get_option( 'burst_db_upgrade_drop_page_id_column' ) ) {
			return;
		}

		global $wpdb;
		// check if columns exist first.
		$columns = $wpdb->get_col( "DESC {$wpdb->prefix}burst_statistics", 0 );
		if ( ! in_array( 'page_id', $columns, true ) ) {
			delete_option( 'burst_db_upgrade_drop_page_id_column' );
			return;
		}

		// run an sql query to remove the columns `event` and `action`.
		$remove = $wpdb->query( "ALTER TABLE {$wpdb->prefix}burst_statistics DROP COLUMN `page_id`" );

		if ( $remove !== false ) {
			delete_option( 'burst_db_upgrade_drop_page_id_column' );
		}
	}

	/**
	 * Update the entire_page_url column to the new name, paramaters, and change to TEXT
	 */
	public function change_column_name_entire_page_url(): void {
		global $wpdb;
		$wpdb->query( "ALTER TABLE {$wpdb->prefix}burst_statistics MODIFY parameters TEXT;" );
		delete_option( 'burst_db_upgrade_rename_entire_page_url_column' );
	}

	/**
	 * Drop the path from the parameters column.
	 */
	public function drop_path_from_parameters_column(): void {
		// check if column already upgraded.
		if ( ! get_option( 'burst_db_upgrade_drop_path_from_parameters_column' ) ) {
			return;
		}

		if ( ! $this->column_exists( 'burst_statistics', 'entire_page_url' ) ) {
			delete_option( 'burst_db_upgrade_drop_path_from_parameters_column' );
			delete_option( 'burst_db_upgrade_column_offset' );
			delete_transient( 'burst_progress_drop_path_from_parameters_column' );
			return;
		}

		global $wpdb;
		$batch_size = 50000;
		$offset     = (int) get_option( 'burst_db_upgrade_column_offset', 0 );
		$sql        = "UPDATE {$wpdb->prefix}burst_statistics
                SET `parameters` = IF(LOCATE('?', `entire_page_url`) > 0, SUBSTRING(`entire_page_url`, LOCATE('?', `entire_page_url`)), '')
                WHERE ID IN (
                    SELECT ID FROM (
                        SELECT id FROM {$wpdb->prefix}burst_statistics LIMIT $offset, $batch_size
                    ) AS temp
                );";
        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- $offset and $batch_size are validated integers.
		$wpdb->query( $sql );
		$offset += $batch_size;
		update_option( 'burst_db_upgrade_column_offset', $offset );
		$total    = $wpdb->get_var( "SELECT COUNT(*) FROM {$wpdb->prefix}burst_statistics" );
		$progress = $total > 0 ? round( $offset / $total, 2 ) : 1;
		set_transient( 'burst_progress_drop_path_from_parameters_column', $progress, HOUR_IN_SECONDS );

		if ( $offset >= $total ) {
			$wpdb->query( "ALTER TABLE {$wpdb->prefix}burst_statistics DROP COLUMN entire_page_url" );
			delete_option( 'burst_db_upgrade_column_offset' );
			delete_option( 'burst_db_upgrade_drop_path_from_parameters_column' );
			delete_transient( 'burst_progress_drop_path_from_parameters_column' );
		}
	}

	/**
	 * Upgrade referrer column to normalized format in batches
	 */
	private function upgrade_referrers(): void {
		if ( ! $this->has_admin_access() ) {
			return;
		}

		if ( ! get_option( 'burst_db_upgrade_move_referrers_to_sessions' ) ) {
			return;
		}

		global $wpdb;
		// Check if both tables exist.
		if ( ! $this->table_exists( 'burst_statistics' ) || ! $this->table_exists( 'burst_sessions' ) ) {
			self::error_log( 'Missing tables, delay referrer upgrade until tables have upgraded.' );
			return;
		}

		// check if column exists.
		if ( ! $this->column_exists( 'burst_sessions', 'referrer' ) ) {
			self::error_log( 'Referrer column does not exist in sessions table, delay referrer upgrade until tables have upgraded.' );
			return;
		}

		$batch = $this->batch;
		$start = microtime( true );

		// Count remaining sessions to process (where referrer is NULL = not yet migrated).
		$remaining_count = (int) $wpdb->get_var(
			"SELECT COUNT(DISTINCT s.ID)
        FROM {$wpdb->prefix}burst_sessions s
        INNER JOIN {$wpdb->prefix}burst_statistics st ON st.session_id = s.ID
        WHERE s.referrer IS NULL"
		);

		$total_count = (int) $wpdb->get_var(
			"SELECT COUNT(DISTINCT s.ID)
        FROM {$wpdb->prefix}burst_sessions s
        INNER JOIN {$wpdb->prefix}burst_statistics st ON st.session_id = s.ID"
		);

		$done_count = $total_count - $remaining_count;

		// Calculate and store progress.
		$progress = 0 === $total_count ? 1 : $done_count / $total_count;
		$progress = round( $progress, 2 );
		set_transient( 'burst_progress_move_referrers_to_sessions', $progress, HOUR_IN_SECONDS );

		if ( $remaining_count > 0 ) {
			// STEP 1: Get batch of session IDs to process.
			$session_ids = $wpdb->get_col(
				$wpdb->prepare(
					"SELECT DISTINCT s.ID
                FROM {$wpdb->prefix}burst_sessions s
                WHERE s.referrer IS NULL
                LIMIT %d",
					$batch
				)
			);

			if ( empty( $session_ids ) ) {
				return;
			}

			$session_ids_placeholder = implode( ',', array_fill( 0, count( $session_ids ), '%d' ) );

			// STEP 2: Update sessions with normalized referrers from the earliest pageview.
            // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared	 -- $session_ids are sanitized integers.
			$sql = $wpdb->prepare(
				"UPDATE {$wpdb->prefix}burst_sessions s
            INNER JOIN (
                SELECT
                    st.session_id,
                    CASE
                        WHEN st.referrer = '' OR st.referrer IS NULL THEN ''
                        ELSE TRIM(LEADING 'www.' FROM SUBSTRING_INDEX(SUBSTRING_INDEX(st.referrer, '://', -1), '/', 1))
                    END as normalized_referrer
                FROM {$wpdb->prefix}burst_statistics st
                INNER JOIN (
                    SELECT session_id, MIN(time) as first_time
                    FROM {$wpdb->prefix}burst_statistics
                    WHERE session_id IN ({$session_ids_placeholder})
                    GROUP BY session_id
                ) earliest ON st.session_id = earliest.session_id AND st.time = earliest.first_time
            ) as first_stats ON s.ID = first_stats.session_id
            SET s.referrer = first_stats.normalized_referrer
            WHERE s.referrer IS NULL",             // phpcs:ignore
				...$session_ids
			);
            // phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared

            // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- $session_ids are sanitized integers.
			$wpdb->query( $sql );
		} else {
			self::error_log( 'All referrers processed, cleaning up.' );
			delete_option( 'burst_db_upgrade_move_referrers_to_sessions' );
			delete_transient( 'burst_progress_move_referrers_to_sessions' );

			// to do next release..
            // phpcs:ignore
			// $wpdb->query( "ALTER TABLE {$wpdb->prefix}burst_statistics DROP COLUMN referrer" );
		}
	}


	/**
	 * Upgrade referrer column to normalized format in batches
	 */
	private function fix_trailing_slash_on_referrers(): void {
		if ( ! $this->has_admin_access() ) {
			return;
		}

		if ( ! get_option( 'burst_db_upgrade_fix_trailing_slash_on_referrers' ) ) {
			return;
		}

		global $wpdb;
		$timestamp = 1734739200;

		$last_id = (int) $wpdb->get_var(
			$wpdb->prepare(
				"SELECT MIN(ID) FROM {$wpdb->prefix}burst_statistics WHERE time >= %d",
				$timestamp
			)
		);

		if ( ! empty( $last_id ) ) {
			$wpdb->query(
				$wpdb->prepare(
					"UPDATE {$wpdb->prefix}burst_sessions
                SET referrer = TRIM(TRAILING '/' FROM referrer)
                WHERE ID >= %d
                  AND referrer IS NOT NULL
                  AND referrer != ''
                  AND referrer LIKE %s",
					$last_id,
					'%/'
				)
			);
		}

		delete_option( 'burst_db_upgrade_fix_trailing_slash_on_referrers' );
	}

	/**
	 * Move browser_id, browser_version_id, platform_id, device_id,
	 * first_time_visit, bounce from statistics to sessions table.
	 */
	private function upgrade_move_columns_to_sessions(): void {
		if ( ! $this->has_admin_access() ) {
			return;
		}
		global $wpdb;

		if ( ! $this->table_exists( 'burst_statistics' ) || ! $this->table_exists( 'burst_sessions' ) ) {
			self::error_log( 'Missing tables, delaying move_columns_to_sessions upgrade.' );
			return;
		}

		if ( ! $this->column_exists( 'burst_sessions', 'browser_id' ) ) {
			self::error_log( 'browser_id column missing from sessions table, delaying upgrade.' );
			return;
		}

		if ( ! $this->column_exists( 'burst_statistics', 'browser_id' ) ) {
			delete_option( 'burst_db_upgrade_move_columns_to_sessions' );
			delete_transient( 'burst_progress_move_columns_to_sessions' );
			return;
		}

		if ( ! get_option( 'burst_db_upgrade_move_columns_to_sessions' ) ) {
			update_option( 'burst_db_upgrade_move_columns_to_sessions', true, false );
		}

		$batch = $this->batch;

		$remaining_count = (int) $wpdb->get_var(
			"SELECT COUNT(*)
			FROM {$wpdb->prefix}burst_statistics
			WHERE browser_id != 9999"
		);

		$total_count = (int) $wpdb->get_var(
			"SELECT COUNT(*)
        	FROM {$wpdb->prefix}burst_statistics"
		);

		$done_count = max( 0, $total_count - $remaining_count );
		$progress   = 0 === $total_count ? 1 : $done_count / $total_count;
		$progress   = round( $progress, 2 );
		set_transient( 'burst_progress_move_columns_to_sessions', $progress, HOUR_IN_SECONDS );

		if ( $remaining_count > 0 ) {
			$wpdb->query(
				$wpdb->prepare(
					"CREATE TEMPORARY TABLE IF NOT EXISTS burst_batch_ids AS
					SELECT ID, session_id
					FROM {$wpdb->prefix}burst_statistics
					WHERE browser_id != 9999
					ORDER BY ID
					LIMIT %d",
					$batch
				)
			);

			// Step 1: Materialize session aggregates into a temp table.
			// burst_batch_ids is only referenced once here, avoiding the
			// "Can't reopen table" MySQL limitation on temporary tables.
			$wpdb->query(
				"CREATE TEMPORARY TABLE IF NOT EXISTS burst_session_data AS
				SELECT
					st.session_id,
					MIN(CASE WHEN st.browser_id != 9999 THEN st.browser_id END)                    AS browser_id,
					MIN(CASE WHEN st.browser_id != 9999 THEN st.browser_version_id END)            AS browser_version_id,
					MIN(CASE WHEN st.browser_id != 9999 THEN st.platform_id END)                   AS platform_id,
					MIN(CASE WHEN st.browser_id != 9999 THEN st.device_id END)                     AS device_id,
					COALESCE(MIN(CASE WHEN st.browser_id != 9999 THEN st.first_time_visit END), 0) AS first_time_visit,
					CASE WHEN COUNT(st.ID) > 1 THEN 0 ELSE MAX(st.bounce) END                      AS bounce
				FROM {$wpdb->prefix}burst_statistics st
				INNER JOIN burst_batch_ids b ON st.session_id = b.session_id
				GROUP BY st.session_id"
			);

			// Step 2: Update sessions using the materialized temp table.
			// burst_session_data is a separate temp table, no double reference.
			$wpdb->query(
				"UPDATE {$wpdb->prefix}burst_sessions s
				INNER JOIN burst_session_data sd ON s.ID = sd.session_id
				SET
					s.browser_id         = sd.browser_id,
					s.browser_version_id = sd.browser_version_id,
					s.platform_id        = sd.platform_id,
					s.device_id          = sd.device_id,
					s.first_time_visit   = sd.first_time_visit,
					s.bounce             = sd.bounce"
			);

			// Mark the processed batch as done using sentinel value 9999.
			$wpdb->query(
				"UPDATE {$wpdb->prefix}burst_statistics
				INNER JOIN burst_batch_ids b ON {$wpdb->prefix}burst_statistics.ID = b.ID
				SET browser_id = 9999"
			);

			$wpdb->query( 'DROP TEMPORARY TABLE IF EXISTS burst_session_data' );
			$wpdb->query( 'DROP TEMPORARY TABLE IF EXISTS burst_batch_ids' );

		} elseif ( $this->column_exists( 'burst_statistics', 'browser_id' ) ) {
			$wpdb->query(
				"ALTER TABLE {$wpdb->prefix}burst_statistics
				DROP COLUMN `browser_id`,
				DROP COLUMN `browser_version_id`,
				DROP COLUMN `platform_id`,
				DROP COLUMN `device_id`,
				DROP COLUMN `first_time_visit`,
				DROP COLUMN `bounce`"
			);
			delete_option( 'burst_db_upgrade_move_columns_to_sessions' );
			delete_transient( 'burst_progress_move_columns_to_sessions' );
			self::error_log( 'move_columns_to_sessions upgrade complete.' );
		}
	}
}
