<?php
/**
 * Maintenance Trait
 *
 * Provides database maintenance and optimization methods.
 *
 * @package Opti_Behavior
 * @copyright 2025 OptiUser
 * @version 1.0.3
 */

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

// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared
// phpcs:disable WordPress.DB.PreparedSQL.NotPrepared
// phpcs:disable WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare
// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery
// phpcs:disable WordPress.DB.DirectDatabaseQuery.NoCaching
// phpcs:disable PluginCheck.Security.DirectDB.UnescapedDBParameter

/**
 * Maintenance Trait
 *
 * Provides methods for database maintenance, cleanup, and optimization.
 *
 * @since 1.0.0
 */
trait Opti_Behavior_Maintenance_Trait {

	/**
	 * Delete all analytics data across plugin tables.
	 *
	 * @since 1.0.0
	 * @global wpdb $wpdb WordPress database abstraction object.
	 */
	private function delete_all_analytics_data_impl() {
		global $wpdb;
		$prefix = $wpdb->prefix;

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table names from $wpdb->prefix; analytics queries.
		$tables = $wpdb->get_col( "SHOW TABLES LIKE '" . $prefix . "optibehavior_%'" );
		if ( ! is_array( $tables ) ) {
			$tables = array();
		}

		// Also get tables with opti_behavior_ pattern (for funnels and other features)
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table names from $wpdb->prefix; analytics queries.
		$tables_underscore = $wpdb->get_col( "SHOW TABLES LIKE '" . $prefix . "opti\_behavior\_%'" );
		if ( is_array( $tables_underscore ) ) {
			$tables = array_merge( $tables, $tables_underscore );
		}

		foreach ( array( 'optibehavior_norm', 'optibehavior_unread' ) as $legacy ) {
			$full = $prefix . $legacy;
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,PluginCheck.Security.DirectDB.UnescapedDBParameter -- Analytics data requires direct DB access.
			$exists = $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $full ) );
			if ( $exists ) {
				$tables[] = $full;
			}
		}

		$tables = array_values(
			array_unique(
				array_filter(
					$tables,
					function ( $t ) use ( $prefix ) {
						return is_string( $t ) && (
							strpos( $t, $prefix . 'optibehavior_' ) === 0 ||
							strpos( $t, $prefix . 'opti_behavior_' ) === 0
						);
					}
				)
			)
		);

		foreach ( $tables as $table ) {
			$safe_table = esc_sql( $table );
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table names from $wpdb->prefix; analytics queries.
			$wpdb->query( "TRUNCATE TABLE `" . $safe_table . "`" );
		}

		$like_key     = $wpdb->esc_like( '_transient_optibehavior_top_users_' ) . '%';
		$like_timeout = $wpdb->esc_like( '_transient_timeout_optibehavior_top_users_' ) . '%';
		$options_table = esc_sql( $wpdb->prefix . 'options' );
		$wpdb->query(
			$wpdb->prepare(
				"DELETE FROM " . $options_table . " WHERE option_name LIKE %s OR option_name LIKE %s",
				$like_key,
				$like_timeout
			)
		);

		// Delete file storage data as well
		$core = isset( $this->heatmap ) ? $this->heatmap : Opti_Behavior_Heatmap_Core::get_instance();
		$file_storage = $core->get_file_storage();
		if ( $file_storage ) {
			$debug_manager = $core->get_debug_manager();
			$debug_manager->log( 'Deleting file storage data', 'info', 'maintenance' );

			// Get storage directory
			$upload_dir = wp_upload_dir();
			$storage_dir = trailingslashit( $upload_dir['basedir'] ) . 'opti-behavior-data/';

			if ( file_exists( $storage_dir ) ) {
				// Delete all files in events and recordings directories
				$this->delete_directory_contents( $storage_dir . 'events/' );
				$this->delete_directory_contents( $storage_dir . 'recordings/' );

				// Delete ALL heatmap hash folders (url_hash directories containing clicks/moves/scrolls)
				// These folders have MD5 hash names like 'a1b2c3d4e5f6...'
				$this->delete_heatmap_hash_folders( $storage_dir );

				// Also delete heatmaps subfolder and its hash folders if it exists
				if ( file_exists( $storage_dir . 'heatmaps/' ) ) {
					$this->delete_heatmap_hash_folders( $storage_dir . 'heatmaps/' );
					$this->delete_directory_contents( $storage_dir . 'heatmaps/' );
				}

				$debug_manager->log( 'File storage data deleted (including heatmap hash folders)', 'info', 'maintenance' );
			}
		}

		if ( function_exists( 'wp_cache_flush' ) ) {
			wp_cache_flush();
		}
		add_action(
			'admin_notices',
			function () {
				echo '<div class="notice notice-success is-dismissible"><p>' . esc_html__( 'All opti-behavior Analytics tables and files were emptied successfully.', 'opti-behavior' ) . '</p></div>';
			}
		);
	}

	/**
	 * Delete logs within a date range.
	 *
	 * @since 1.0.0
	 * @global wpdb $wpdb WordPress database abstraction object.
	 * @param string $start Start date.
	 * @param string $end   End date.
	 */
	private function delete_logs_in_date_range_impl( $start, $end ) {
		global $wpdb;
		if ( ! $start || ! $end ) {
			return;
		}
		$start = gmdate( 'Y-m-d 00:00:00', strtotime( $start ) );
		$end   = gmdate( 'Y-m-d 23:59:59', strtotime( $end ) );

		$events_table     = esc_sql( $wpdb->prefix . 'optibehavior_events' );
		$pageviews_table  = esc_sql( $wpdb->prefix . 'optibehavior_pageviews' );
		$sessions_table   = esc_sql( $wpdb->prefix . 'optibehavior_sessions' );
		$recordings_table = esc_sql( $wpdb->prefix . 'optibehavior_recordings' );
		$referrers_table  = esc_sql( $wpdb->prefix . 'optibehavior_referrers' );
		$outbound_table   = esc_sql( $wpdb->prefix . 'optibehavior_outbound_clicks' );

		$wpdb->query( $wpdb->prepare( "DELETE FROM " . $events_table . " WHERE insert_at BETWEEN %s AND %s", $start, $end ) );
		$wpdb->query( $wpdb->prepare( "DELETE FROM " . $pageviews_table . " WHERE view_time BETWEEN %s AND %s", $start, $end ) );

		// Bug 4: Delete session-linked tables that lack a direct date column via JOIN.
		// Must run BEFORE sessions are deleted so the JOIN resolves correctly.
		$session_pages_tbl = esc_sql( $wpdb->prefix . 'optibehavior_session_pages' );
		$friction_tbl      = esc_sql( $wpdb->prefix . 'optibehavior_friction' );
		$errors_tbl        = esc_sql( $wpdb->prefix . 'optibehavior_errors' );
		$performance_tbl   = esc_sql( $wpdb->prefix . 'optibehavior_performance' );
		foreach ( array( $session_pages_tbl, $friction_tbl, $errors_tbl, $performance_tbl ) as $_dr_tbl ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table names from $wpdb->prefix; analytics queries.
			$_dr_tbl_name    = str_replace( esc_sql( $wpdb->prefix ), '', $_dr_tbl );
			$_dr_tbl_exists  = $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $wpdb->prefix . $_dr_tbl_name ) );
			if ( $_dr_tbl_exists ) {
				$wpdb->query( $wpdb->prepare(
					"DELETE t FROM " . $_dr_tbl . " t
					INNER JOIN " . $sessions_table . " s ON s.id = t.session_id
					WHERE s.start_time BETWEEN %s AND %s",
					$start,
					$end
				) );
			}
		}

		$wpdb->query( $wpdb->prepare( "DELETE FROM " . $sessions_table . " WHERE start_time BETWEEN %s AND %s", $start, $end ) );

		// Bug 6: Collect recording file paths from DB before deleting DB rows,
		// so files are deleted by session start_time (not filesystem mtime).
		$recording_file_paths = $wpdb->get_col( $wpdb->prepare(
			"SELECT file_path FROM " . $recordings_table . " WHERE start_time BETWEEN %s AND %s AND file_path IS NOT NULL AND file_path != ''",
			$start,
			$end
		) );
		if ( ! empty( $recording_file_paths ) ) {
			$rec_upload_dir = wp_upload_dir();
			$rec_base_dir   = trailingslashit( $rec_upload_dir['basedir'] ) . 'opti-behavior-data/';
			foreach ( $recording_file_paths as $_rec_path ) {
				if ( ! empty( $_rec_path ) ) {
					$_rec_full = $rec_base_dir . ltrim( $_rec_path, '/\\' );
					if ( file_exists( $_rec_full ) ) {
						wp_delete_file( $_rec_full );
					}
				}
			}
		}
		$wpdb->query( $wpdb->prepare( "DELETE FROM " . $recordings_table . " WHERE start_time BETWEEN %s AND %s", $start, $end ) );

		$wpdb->query( $wpdb->prepare( "DELETE FROM " . $referrers_table . " WHERE created_at BETWEEN %s AND %s", $start, $end ) );
		$wpdb->query( $wpdb->prepare( "DELETE FROM " . $outbound_table . " WHERE created_at BETWEEN %s AND %s", $start, $end ) );

		$form_interactions_table = esc_sql( $wpdb->prefix . 'optibehavior_form_interactions' );
		$form_submissions_table  = esc_sql( $wpdb->prefix . 'optibehavior_form_submissions' );
		$wpdb->query( $wpdb->prepare( "DELETE FROM " . $form_interactions_table . " WHERE created_at BETWEEN %s AND %s", $start, $end ) );
		$wpdb->query( $wpdb->prepare( "DELETE FROM " . $form_submissions_table . " WHERE created_at BETWEEN %s AND %s", $start, $end ) );

		$like_key      = $wpdb->esc_like( '_transient_optibehavior_top_users_' ) . '%';
		$like_timeout  = $wpdb->esc_like( '_transient_timeout_optibehavior_top_users_' ) . '%';
		$options_table = esc_sql( $wpdb->prefix . 'options' );
		$wpdb->query(
			$wpdb->prepare(
				"DELETE FROM " . $options_table . " WHERE option_name LIKE %s OR option_name LIKE %s",
				$like_key,
				$like_timeout
			)
		);

		// Bug 6: Only delete events files by filemtime; recordings are handled above via DB file_path.
		$core = isset( $this->heatmap ) ? $this->heatmap : Opti_Behavior_Heatmap_Core::get_instance();
		$file_storage = $core->get_file_storage();
		if ( $file_storage ) {
			$this->delete_file_storage_in_date_range( $start, $end );
		}

		// Bug 5: Remove orphaned visitor records after session deletion.
		$this->delete_orphaned_visitors();

		add_action(
			'admin_notices',
			function () {
				echo '<div class="notice notice-success is-dismissible"><p>' . esc_html__( 'Logs within the selected date range were deleted from database and files.', 'opti-behavior' ) . '</p></div>';
			}
		);
	}

	/**
	 * Delete file storage data within a date range.
	 *
	 * @since 1.0.0
	 * @param string $start Start date in MySQL format.
	 * @param string $end   End date in MySQL format.
	 */
	private function delete_file_storage_in_date_range( $start, $end ) {
		$upload_dir = wp_upload_dir();
		$storage_dir = trailingslashit( $upload_dir['basedir'] ) . 'opti-behavior-data/';

		if ( ! file_exists( $storage_dir ) ) {
			return;
		}

		$start_timestamp = strtotime( $start );
		$end_timestamp   = strtotime( $end );

		// Bug 6: Only delete events files here; recordings are deleted via DB file_path records
		// in delete_logs_in_date_range_impl() to ensure date accuracy.
		foreach ( array( 'events' ) as $type ) {
			$type_dir = $storage_dir . $type . '/';
			if ( ! file_exists( $type_dir ) ) {
				continue;
			}

			$this->delete_files_by_date_range( $type_dir, $start_timestamp, $end_timestamp );
		}
	}

	/**
	 * Delete files within a date range by checking file modification time.
	 *
	 * @since 1.0.0
	 * @param string $dir            Directory to scan.
	 * @param int    $start_timestamp Start timestamp.
	 * @param int    $end_timestamp   End timestamp.
	 */
	private function delete_files_by_date_range( $dir, $start_timestamp, $end_timestamp ) {
		if ( ! file_exists( $dir ) ) {
			return;
		}

		$iterator = new RecursiveIteratorIterator(
			new RecursiveDirectoryIterator( $dir, RecursiveDirectoryIterator::SKIP_DOTS ),
			RecursiveIteratorIterator::CHILD_FIRST
		);

		$deleted_count = 0;
		foreach ( $iterator as $item ) {
			if ( $item->isFile() ) {
				$file_time = $item->getMTime();
				if ( $file_time >= $start_timestamp && $file_time <= $end_timestamp ) {
					wp_delete_file( $item->getRealPath() );
					$deleted_count++;
				}
			}
		}

		// Log using core debug manager
		$core = isset( $this->heatmap ) ? $this->heatmap : Opti_Behavior_Heatmap_Core::get_instance();
		$debug_manager = $core->get_debug_manager();
		$debug_manager->log( "Deleted $deleted_count files from $dir within date range", 'debug', 'maintenance' );
	}

	/**
	 * Recursively delete all contents of a directory.
	 *
	 * @since 1.0.0
	 * @param string $dir Directory path to clear.
	 */
	private function delete_directory_contents( $dir ) {
		if ( ! file_exists( $dir ) || ! is_dir( $dir ) ) {
			return;
		}

		// Initialize WP_Filesystem
		global $wp_filesystem;
		if ( ! function_exists( 'WP_Filesystem' ) ) {
			require_once ABSPATH . 'wp-admin/includes/file.php';
		}
		WP_Filesystem();

		$items = new RecursiveIteratorIterator(
			new RecursiveDirectoryIterator( $dir, RecursiveDirectoryIterator::SKIP_DOTS ),
			RecursiveIteratorIterator::CHILD_FIRST
		);

		foreach ( $items as $item ) {
			if ( $item->isDir() ) {
				$wp_filesystem->rmdir( $item->getRealPath() );
			} else {
				wp_delete_file( $item->getRealPath() );
			}
		}

		// Log using core debug manager
		$core = isset( $this->heatmap ) ? $this->heatmap : Opti_Behavior_Heatmap_Core::get_instance();
		$debug_manager = $core->get_debug_manager();
		$debug_manager->log( 'Deleted directory contents: ' . $dir, 'debug', 'maintenance' );
	}

	/**
	 * Delete all heatmap hash folders in the storage directory.
	 *
	 * Heatmap data is stored in folders named with MD5 hashes of URLs.
	 * Structure: opti-behavior-data/{url_hash}/clicks/
	 *            opti-behavior-data/{url_hash}/moves/
	 *            opti-behavior-data/{url_hash}/scrolls/
	 *
	 * @since 1.0.0
	 * @param string $storage_dir Base storage directory path.
	 */
	private function delete_heatmap_hash_folders( $storage_dir ) {
		if ( ! file_exists( $storage_dir ) || ! is_dir( $storage_dir ) ) {
			return;
		}

		// Initialize WP_Filesystem
		global $wp_filesystem;
		if ( ! function_exists( 'WP_Filesystem' ) ) {
			require_once ABSPATH . 'wp-admin/includes/file.php';
		}
		WP_Filesystem();

		$deleted_count = 0;
		$items = scandir( $storage_dir );

		foreach ( $items as $item ) {
			// Skip special entries and known non-hash directories
			if ( $item === '.' || $item === '..' || $item === 'index.php' || $item === '.htaccess' ) {
				continue;
			}

			// Skip known named directories (events, recordings)
			if ( in_array( $item, array( 'events', 'recordings' ), true ) ) {
				continue;
			}

			$item_path = $storage_dir . $item;

			// Only process directories (hash folders are directories)
			if ( is_dir( $item_path ) ) {
				// Check if this looks like a hash folder (32 character hex string = MD5 hash)
				// Or any other folder that contains heatmap data (clicks, moves, scrolls subdirs)
				$is_hash_folder = ( strlen( $item ) === 32 && ctype_xdigit( $item ) );
				$has_heatmap_subdirs = is_dir( $item_path . '/clicks' ) ||
				                       is_dir( $item_path . '/moves' ) ||
				                       is_dir( $item_path . '/scrolls' );

				if ( $is_hash_folder || $has_heatmap_subdirs ) {
					// Recursively delete the entire hash folder and its contents
					$this->delete_directory_recursive( $item_path );
					$deleted_count++;
				}
			}
		}

		// Log the deletion
		$core = isset( $this->heatmap ) ? $this->heatmap : Opti_Behavior_Heatmap_Core::get_instance();
		$debug_manager = $core->get_debug_manager();
		$debug_manager->log( "Deleted $deleted_count heatmap hash folders from storage", 'info', 'maintenance' );
	}

	/**
	 * Recursively delete a directory and all its contents.
	 *
	 * @since 1.0.0
	 * @param string $dir Directory path to delete.
	 * @return bool True on success, false on failure.
	 */
	private function delete_directory_recursive( $dir ) {
		if ( ! file_exists( $dir ) || ! is_dir( $dir ) ) {
			return true;
		}

		// Initialize WP_Filesystem
		global $wp_filesystem;
		if ( ! function_exists( 'WP_Filesystem' ) ) {
			require_once ABSPATH . 'wp-admin/includes/file.php';
		}
		WP_Filesystem();

		$items = new RecursiveIteratorIterator(
			new RecursiveDirectoryIterator( $dir, RecursiveDirectoryIterator::SKIP_DOTS ),
			RecursiveIteratorIterator::CHILD_FIRST
		);

		foreach ( $items as $item ) {
			if ( $item->isDir() ) {
				$wp_filesystem->rmdir( $item->getRealPath() );
			} else {
				wp_delete_file( $item->getRealPath() );
			}
		}

		// Finally, remove the directory itself
		return $wp_filesystem->rmdir( $dir );
	}

	/**
	 * Conditionally ensure database indexes for performance.
	 *
	 * Runs at most once per day.
	 *
	 * @since 1.0.0
	 */
	public function maybe_ensure_db_indexes_impl() {
		if ( ! current_user_can( 'manage_options' ) ) {
			return;
		}
		$flag = get_option( 'optibehavior_indexes_last_checked' );
		if ( $flag && time() - intval( $flag ) < DAY_IN_SECONDS ) {
			return;
		}
		update_option( 'optibehavior_indexes_last_checked', time(), false );
		$this->ensure_db_indexes_impl();
	}

	/**
	 * Create missing database indexes.
	 *
	 * @since 1.0.0
	 * @global wpdb $wpdb WordPress database abstraction object.
	 */
	private function ensure_db_indexes_impl() {
		global $wpdb;
		$prefix = $wpdb->prefix;
		$defs   = array(
			$prefix . "optibehavior_events"    => array(
				'idx_events_page_event_time' => 'CREATE INDEX idx_events_page_event_time ON %s (page_id2, event, insert_at)',
				'idx_events_event_time'      => 'CREATE INDEX idx_events_event_time ON %s (event, insert_at)',
			),
			$prefix . "optibehavior_pages"     => array(
				'idx_pages_url' => 'CREATE INDEX idx_pages_url ON %s (url(191))',
			),
			$prefix . "optibehavior_pageviews" => array(
				'idx_pv_url'        => 'CREATE INDEX idx_pv_url ON %s (url(191))',
				'idx_pv_session_id' => 'CREATE INDEX idx_pv_session_id ON %s (session_id)',
				'idx_pv_view_time'  => 'CREATE INDEX idx_pv_view_time ON %s (view_time)',
			),
			$prefix . "optibehavior_sessions"  => array(
				'idx_sessions_start_time' => 'CREATE INDEX idx_sessions_start_time ON %s (start_time)',
				'idx_sessions_visitor'    => 'CREATE INDEX idx_sessions_visitor ON %s (visitor_id)',
			),
		);
		foreach ( $defs as $table => $idxs ) {
			foreach ( $idxs as $name => $tpl ) {
				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,PluginCheck.Security.DirectDB.UnescapedDBParameter -- Analytics data requires direct DB access.
				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
				$exists = $wpdb->get_var(
					$wpdb->prepare(
						'SELECT COUNT(1) FROM information_schema.STATISTICS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = %s AND INDEX_NAME = %s',
						$table,
						$name
					)
				);
				if ( (int) $exists === 0 ) {
					$safe_table = esc_sql( $table );
					// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.NotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter -- Query built from prepared parts.
					// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
					$wpdb->query( sprintf( $tpl, $safe_table ) );
				}
			}
		}
	}

	/**
	 * Conditionally backfill referrers from entry page.
	 *
	 * Runs at most once per day.
	 *
	 * @since 1.0.0
	 */
	public function maybe_backfill_referrers_impl() {
		if ( ! function_exists( 'current_user_can' ) || ! current_user_can( 'manage_options' ) ) {
			return;
		}
		if ( ! function_exists( 'get_option' ) || ! function_exists( 'update_option' ) ) {
			return;
		}
		$flag = get_option( 'optibehavior_referrers_last_backfill' );
		if ( $flag && time() - intval( $flag ) < ( defined( 'DAY_IN_SECONDS' ) ? DAY_IN_SECONDS : 86400 ) ) {
			return;
		}
		update_option( 'optibehavior_referrers_last_backfill', time(), false );
		$this->backfill_referrers_from_entry_page_impl( 30, 2000 );
	}

	/**
	 * Backfill utm_source from entry_page query string.
	 *
	 * @since 1.0.0
	 * @global wpdb $wpdb WordPress database abstraction object.
	 * @param int $days  Number of days to look back.
	 * @param int $limit Maximum number of rows to process.
	 */
	private function backfill_referrers_from_entry_page_impl( $days = 30, $limit = 2000 ) {
		global $wpdb;
		if ( ! isset( $wpdb ) ) {
			return;
		}
		$since = gmdate( 'Y-m-d H:i:s', time() - $days * 86400 );
		$table = esc_sql( $wpdb->prefix . 'optibehavior_sessions' );
		// phpcs:disable WordPress.DB.PreparedSQLPlaceholders.LikeWildcardsInQuery -- Intentional literal LIKE wildcards for utm_source matching.
		$rows = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT id, entry_page FROM " . $table . "
				 WHERE start_time >= %s AND (utm_source IS NULL OR utm_source='')
				   AND entry_page LIKE '%%utm_source=%%'
				 LIMIT %d",
				$since,
				(int) $limit
			)
		);
		// phpcs:enable WordPress.DB.PreparedSQLPlaceholders.LikeWildcardsInQuery
		if ( ! $rows ) {
			return;
		}
		foreach ( $rows as $r ) {
			$entry = is_string( $r->entry_page ) ? $r->entry_page : '';
			if ( $entry === '' ) {
				continue;
			}
			$qs = wp_parse_url( $entry, PHP_URL_QUERY );
			if ( ! $qs ) {
				continue;
			}
			parse_str( $qs, $params );
			$src = isset( $params['utm_source'] ) ? trim( $params['utm_source'] ) : '';
			if ( $src === '' ) {
				continue;
			}
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			$wpdb->update( $table, array( 'utm_source' => $src ), array( 'id' => $r->id ) );
		}
	}

	/**
	 * Fix Android OS detection - update visitors where OS is 'Linux' but device is mobile.
	 * This corrects the issue where Android devices were incorrectly categorized as Linux.
	 *
	 * @since 1.0.4
	 * @global wpdb $wpdb WordPress database abstraction object.
	 * @return array Results with count of updated records.
	 */
	private function fix_android_os_detection_impl() {
		global $wpdb;

		// Find all visitors where os='Linux' and device_type='mobile' or 'tablet'
		// These are very likely Android devices that were misclassified
		$table = esc_sql( $wpdb->prefix . 'optibehavior_visitors' );

		$updated = $wpdb->query(
			"UPDATE " . $table . "
			 SET os = 'Android'
			 WHERE LOWER(os) = 'linux'
			   AND device_type IN ('mobile', 'tablet')"
		);

		return array(
			'success' => true,
			'updated' => $updated,
			'message' => sprintf(
				'Updated %d visitor record(s) from Linux to Android based on device type',
				$updated
			),
		);
	}

	/**
	 * AJAX handler for fixing Android OS detection.
	 *
	 * @since 1.0.4
	 */
	public function ajax_fix_android_os() {
		check_ajax_referer( 'opti_behavior_maintenance', 'nonce' );

		if ( ! current_user_can( 'manage_options' ) ) {
			wp_send_json_error( array( 'message' => esc_html__( 'Permission denied', 'opti-behavior' ) ) );
		}

		$result = $this->fix_android_os_detection_impl();
		wp_send_json_success( $result );
	}

	/**
	 * Update the visitor daily stats summary table.
	 *
	 * This function aggregates session data into a summary table for faster
	 * Top Engaged Users queries. Should be called via cron or after bulk operations.
	 *
	 * @since 1.0.5
	 * @param string|null $date Optional specific date to update (Y-m-d format). Defaults to yesterday and today.
	 * @return array Results with counts.
	 */
	public function update_visitor_daily_stats( $date = null ) {
		global $wpdb;

		$sessions_table = esc_sql( $wpdb->prefix . 'optibehavior_sessions' );
		$summary_table = esc_sql( $wpdb->prefix . 'optibehavior_visitor_daily_stats' );

		// Check if summary table exists, create if not
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table names from $wpdb->prefix; analytics queries.
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$table_exists = $wpdb->get_var( "SHOW TABLES LIKE '" . $summary_table . "'" );
		if ( ! $table_exists ) {
			$this->create_visitor_daily_stats_table();
		}

		// Determine which dates to update
		if ( $date ) {
			$dates = array( $date );
		} else {
			// Update yesterday and today by default
			$dates = array(
				gmdate( 'Y-m-d', strtotime( '-1 day' ) ),
				gmdate( 'Y-m-d' ),
			);
		}

		$updated = 0;
		$inserted = 0;

		foreach ( $dates as $stat_date ) {
			$result = $wpdb->query( $wpdb->prepare(
				"INSERT INTO " . $summary_table . "
					(visitor_id, stat_date, sessions, total_duration, total_page_views, user_id, last_seen, traffic_type, country_code)
				SELECT
					s.visitor_id,
					DATE(s.start_time) as stat_date,
					COUNT(*) as sessions,
					SUM(GREATEST(s.duration, 0)) as total_duration,
					SUM(COALESCE(s.page_views, 0)) as total_page_views,
					MAX(s.user_id) as user_id,
					MAX(COALESCE(s.end_time, s.start_time)) as last_seen,
					MAX(s.traffic_type) as traffic_type,
					'' as country_code
				FROM " . $sessions_table . " s
				WHERE DATE(s.start_time) = %s
				GROUP BY s.visitor_id, DATE(s.start_time)
				ON DUPLICATE KEY UPDATE
					sessions = VALUES(sessions),
					total_duration = VALUES(total_duration),
					total_page_views = VALUES(total_page_views),
					user_id = VALUES(user_id),
					last_seen = VALUES(last_seen),
					traffic_type = VALUES(traffic_type)",
				$stat_date
			) );

			if ( $result !== false ) {
				$updated += $result;
			}
		}

		return array(
			'dates_processed' => count( $dates ),
			'rows_affected'   => $updated,
		);
	}

	/**
	 * Create the visitor daily stats summary table.
	 *
	 * @since 1.0.5
	 */
	private function create_visitor_daily_stats_table() {
		global $wpdb;

		$table_name = $wpdb->prefix . 'optibehavior_visitor_daily_stats';
		$charset_collate = $wpdb->get_charset_collate();

		$sql = "CREATE TABLE IF NOT EXISTS " . $table_name . " (
			id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
			visitor_id VARCHAR(64) NOT NULL,
			stat_date DATE NOT NULL,
			sessions INT UNSIGNED NOT NULL DEFAULT 0,
			total_duration INT UNSIGNED NOT NULL DEFAULT 0,
			total_page_views INT UNSIGNED NOT NULL DEFAULT 0,
			user_id BIGINT UNSIGNED DEFAULT NULL,
			last_seen DATETIME DEFAULT NULL,
			traffic_type VARCHAR(20) DEFAULT 'human',
			country_code CHAR(2) DEFAULT '',
			created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
			UNIQUE KEY idx_visitor_date (visitor_id, stat_date),
			KEY idx_stat_date (stat_date),
			KEY idx_total_duration (stat_date, traffic_type, total_duration DESC),
			KEY idx_traffic_date (traffic_type, stat_date)
		) " . $charset_collate . ";";

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

	/**
	 * Rebuild the entire visitor daily stats summary table.
	 * Use sparingly as it processes all sessions.
	 *
	 * @since 1.0.5
	 * @return array Results with counts.
	 */
	public function rebuild_visitor_daily_stats() {
		global $wpdb;

		$sessions_table = esc_sql( $wpdb->prefix . 'optibehavior_sessions' );
		$summary_table = esc_sql( $wpdb->prefix . 'optibehavior_visitor_daily_stats' );

		// Create table if needed
		$this->create_visitor_daily_stats_table();

		// Truncate existing data
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table names from $wpdb->prefix; analytics queries.
		$wpdb->query( "TRUNCATE TABLE " . $summary_table );

		// Rebuild from sessions
		$result = $wpdb->query(
			"INSERT INTO " . $summary_table . "
				(visitor_id, stat_date, sessions, total_duration, total_page_views, user_id, last_seen, traffic_type, country_code)
			SELECT
				s.visitor_id,
				DATE(s.start_time) as stat_date,
				COUNT(*) as sessions,
				SUM(GREATEST(s.duration, 0)) as total_duration,
				SUM(COALESCE(s.page_views, 0)) as total_page_views,
				MAX(s.user_id) as user_id,
				MAX(COALESCE(s.end_time, s.start_time)) as last_seen,
				MAX(s.traffic_type) as traffic_type,
				'' as country_code
			FROM " . $sessions_table . " s
			GROUP BY s.visitor_id, DATE(s.start_time)"
		);

		return array(
			'rows_inserted' => $result !== false ? $result : 0,
		);
	}

	// =========================================================================
	// Smart Data Cleanup Methods
	// =========================================================================

	/**
	 * Count sessions matching the given conditions (for preview).
	 *
	 * @since 1.0.9
	 * @param array $conditions Cleanup conditions array.
	 * @return int Number of matching sessions.
	 */
	public function count_sessions_by_conditions( $conditions ) {
		global $wpdb;
		$sessions_table = esc_sql( $wpdb->prefix . 'optibehavior_sessions' );

		$where_clauses = $this->build_session_condition_clauses( $conditions );
		if ( empty( $where_clauses ) ) {
			return 0;
		}

		$where_sql = implode( ' OR ', $where_clauses );

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table names from $wpdb->prefix; analytics queries.
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$count = $wpdb->get_var( "SELECT COUNT(*) FROM " . $sessions_table . " WHERE " . $where_sql );

		return absint( $count );
	}

	/**
	 * Count bot/spam sessions.
	 *
	 * @since 1.0.9
	 * @return int Number of bot sessions.
	 */
	public function count_bot_sessions() {
		global $wpdb;
		$sessions_table = esc_sql( $wpdb->prefix . 'optibehavior_sessions' );
		$bot_visits_table = esc_sql( $wpdb->prefix . 'optibehavior_bot_visits' );

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table names from $wpdb->prefix; analytics queries.
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$session_count = $wpdb->get_var( "SELECT COUNT(*) FROM " . $sessions_table . " WHERE traffic_type = 'bot'" );
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table names from $wpdb->prefix; analytics queries.
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$bot_count = $wpdb->get_var( "SELECT COUNT(*) FROM " . $bot_visits_table );

		return absint( $session_count ) + absint( $bot_count );
	}

	/**
	 * Get session IDs matching conditions in batches.
	 *
	 * @since 1.0.9
	 * @param array $conditions Cleanup conditions.
	 * @param int   $limit      Batch size.
	 * @param int   $offset     Offset for pagination.
	 * @return array Array of session IDs.
	 */
	private function get_matching_session_ids( $conditions, $limit = 500, $offset = 0 ) {
		global $wpdb;
		$sessions_table = esc_sql( $wpdb->prefix . 'optibehavior_sessions' );

		$where_clauses = $this->build_session_condition_clauses( $conditions );
		if ( empty( $where_clauses ) ) {
			return array();
		}

		$where_sql = implode( ' OR ', $where_clauses );

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table names from $wpdb->prefix; analytics queries.
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$ids = $wpdb->get_col( $wpdb->prepare(
			// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			"SELECT id FROM " . $sessions_table . " WHERE " . $where_sql . " LIMIT %d OFFSET %d",
			$limit,
			$offset
		) );

		return $ids ? $ids : array();
	}

	/**
	 * Build WHERE clauses from conditions array.
	 * Conditions use OR logic between groups but AND within each condition.
	 *
	 * @since 1.0.9
	 * @param array $conditions Conditions array.
	 * @return array Array of SQL WHERE clause strings.
	 */
	private function build_session_condition_clauses( $conditions ) {
		global $wpdb;
		$clauses = array();

		// Time-based: sessions older than X days
		$older_than_days = isset( $conditions['older_than_days'] ) ? absint( $conditions['older_than_days'] ) : 0;
		if ( $older_than_days > 0 ) {
			$cutoff = gmdate( 'Y-m-d H:i:s', time() - ( $older_than_days * DAY_IN_SECONDS ) );
			$clauses[] = $wpdb->prepare( 'start_time < %s', $cutoff );
		}

		// Time-based: inactive visitors (no return in X days)
		$inactive_days = isset( $conditions['inactive_visitor_days'] ) ? absint( $conditions['inactive_visitor_days'] ) : 0;
		if ( $inactive_days > 0 ) {
			$cutoff = gmdate( 'Y-m-d H:i:s', time() - ( $inactive_days * DAY_IN_SECONDS ) );
			$visitors_table = esc_sql( $wpdb->prefix . 'optibehavior_visitors' );
			$clauses[] = $wpdb->prepare(
				// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
				"visitor_id IN (SELECT id FROM " . $visitors_table . " WHERE last_visit < %s)",
				$cutoff
			);
		}

		// Quality: session duration < X seconds, older than Y days
		$min_duration = isset( $conditions['min_duration'] ) ? absint( $conditions['min_duration'] ) : 0;
		if ( $min_duration > 0 ) {
			$age_days = isset( $conditions['min_duration_age_days'] ) ? absint( $conditions['min_duration_age_days'] ) : 0;
			$clause = $wpdb->prepare( 'duration < %d', $min_duration );
			if ( $age_days > 0 ) {
				$cutoff = gmdate( 'Y-m-d H:i:s', time() - ( $age_days * DAY_IN_SECONDS ) );
				$clause .= $wpdb->prepare( ' AND start_time < %s', $cutoff );
			}
			$clauses[] = '(' . $clause . ')';
		}

		// Quality: session duration > X seconds, older than Y days
		$max_duration = isset( $conditions['max_duration'] ) ? absint( $conditions['max_duration'] ) : 0;
		if ( $max_duration > 0 ) {
			$age_days = isset( $conditions['max_duration_age_days'] ) ? absint( $conditions['max_duration_age_days'] ) : 0;
			$clause = $wpdb->prepare( 'duration > %d', $max_duration );
			if ( $age_days > 0 ) {
				$cutoff = gmdate( 'Y-m-d H:i:s', time() - ( $age_days * DAY_IN_SECONDS ) );
				$clause .= $wpdb->prepare( ' AND start_time < %s', $cutoff );
			}
			$clauses[] = '(' . $clause . ')';
		}

		// Quality: events < X, older than Y days
		$min_events = isset( $conditions['min_events'] ) ? absint( $conditions['min_events'] ) : 0;
		if ( $min_events > 0 ) {
			$age_days = isset( $conditions['min_events_age_days'] ) ? absint( $conditions['min_events_age_days'] ) : 0;
			$clause = $wpdb->prepare( 'events_count < %d', $min_events );
			if ( $age_days > 0 ) {
				$cutoff = gmdate( 'Y-m-d H:i:s', time() - ( $age_days * DAY_IN_SECONDS ) );
				$clause .= $wpdb->prepare( ' AND start_time < %s', $cutoff );
			}
			$clauses[] = '(' . $clause . ')';
		}

		// Quality: events > X
		$max_events = isset( $conditions['max_events'] ) ? absint( $conditions['max_events'] ) : 0;
		if ( $max_events > 0 ) {
			$clauses[] = $wpdb->prepare( 'events_count > %d', $max_events );
		}

		// Quality: page views < X
		$min_page_views = isset( $conditions['min_page_views'] ) ? absint( $conditions['min_page_views'] ) : 0;
		if ( $min_page_views > 0 ) {
			$clauses[] = $wpdb->prepare( 'page_views < %d', $min_page_views );
		}

		// Quality: bounce only, older than Y days
		$bounce_only = isset( $conditions['bounce_only'] ) ? (bool) $conditions['bounce_only'] : false;
		if ( $bounce_only ) {
			$age_days = isset( $conditions['bounce_age_days'] ) ? absint( $conditions['bounce_age_days'] ) : 0;
			$clause = 'is_bounce = 1 AND page_views <= 1';
			if ( $age_days > 0 ) {
				$cutoff = gmdate( 'Y-m-d H:i:s', time() - ( $age_days * DAY_IN_SECONDS ) );
				$clause .= $wpdb->prepare( ' AND start_time < %s', $cutoff );
			}
			$clauses[] = '(' . $clause . ')';
		}

		// Bot/Spam sessions
		$include_bots = isset( $conditions['include_bots'] ) ? (bool) $conditions['include_bots'] : false;
		if ( $include_bots ) {
			$clauses[] = "traffic_type = 'bot'";
		}

		// Visitor behavior: single-visit visitors inactive for X days
		$single_visit = isset( $conditions['single_visit_only'] ) ? (bool) $conditions['single_visit_only'] : false;
		if ( $single_visit ) {
			$age_days = isset( $conditions['single_visit_age_days'] ) ? absint( $conditions['single_visit_age_days'] ) : 30;
			$cutoff = gmdate( 'Y-m-d H:i:s', time() - ( $age_days * DAY_IN_SECONDS ) );
			$visitors_table = esc_sql( $wpdb->prefix . 'optibehavior_visitors' );
			$clauses[] = $wpdb->prepare(
				// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
				"visitor_id IN (SELECT id FROM " . $visitors_table . " WHERE visit_count = 1 AND last_visit < %s)",
				$cutoff
			);
		}

		return $clauses;
	}

	/**
	 * Delete bot/spam sessions with cascading deletion.
	 * Returns batch info for step-by-step AJAX processing.
	 *
	 * @since 1.0.9
	 * @param int $step Current step (0-based).
	 * @return array Result with status, progress, and counts.
	 */
	public function delete_bot_sessions_impl( $step = 0 ) {
		global $wpdb;
		$sessions_table = esc_sql( $wpdb->prefix . 'optibehavior_sessions' );
		$bot_visits_table = esc_sql( $wpdb->prefix . 'optibehavior_bot_visits' );
		$batch_size = 500;

		if ( $step === 0 ) {
			// Step 0: Get total count and store for progress tracking
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table names from $wpdb->prefix; analytics queries.
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			$total = $wpdb->get_var( "SELECT COUNT(*) FROM " . $sessions_table . " WHERE traffic_type = 'bot'" );
			update_option( 'optibehavior_bot_cleanup_total', absint( $total ), false );
			update_option( 'optibehavior_bot_cleanup_deleted', 0, false );
		}

		// Get batch of bot session IDs
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table names from $wpdb->prefix; analytics queries.
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$session_ids = $wpdb->get_col( $wpdb->prepare(
			// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			"SELECT id FROM " . $sessions_table . " WHERE traffic_type = 'bot' LIMIT %d",
			$batch_size
		) );

		if ( empty( $session_ids ) ) {
			// No more bot sessions - also truncate bot_visits table
			// Bug 9: Guard against missing bot_visits table before TRUNCATE.
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table names from $wpdb->prefix; analytics queries.
			$bot_tbl_exists = $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $wpdb->prefix . 'optibehavior_bot_visits' ) );
			if ( $bot_tbl_exists ) {
				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table names from $wpdb->prefix; analytics queries.
				$wpdb->query( "TRUNCATE TABLE " . $bot_visits_table );
			}

			// Clean orphaned visitors
			$this->delete_orphaned_visitors();

			// Clear caches
			$this->clear_analytics_caches();

			$total = get_option( 'optibehavior_bot_cleanup_total', 0 );
			$deleted = get_option( 'optibehavior_bot_cleanup_deleted', 0 );

			// Log the cleanup
			$this->add_cleanup_log( 'manual', $deleted, 0, 0 );

			delete_option( 'optibehavior_bot_cleanup_total' );
			delete_option( 'optibehavior_bot_cleanup_deleted' );

			return array(
				'status'   => 'completed',
				'total'    => $total,
				'deleted'  => $deleted,
				'progress' => 100,
			);
		}

		// Cascading delete for this batch
		$deleted_count = $this->cascade_delete_sessions( $session_ids );

		// Update progress
		$prev_deleted = get_option( 'optibehavior_bot_cleanup_deleted', 0 );
		update_option( 'optibehavior_bot_cleanup_deleted', $prev_deleted + $deleted_count, false );

		$total = get_option( 'optibehavior_bot_cleanup_total', 1 );
		$current_deleted = $prev_deleted + $deleted_count;
		$progress = $total > 0 ? min( 95, round( ( $current_deleted / $total ) * 100 ) ) : 50;

		return array(
			'status'   => 'processing',
			'step'     => $step + 1,
			'deleted'  => $current_deleted,
			'total'    => $total,
			'progress' => $progress,
			'message'  => sprintf(
				/* translators: 1: number deleted, 2: total */
				__( 'Deleted %1$d of %2$d bot sessions...', 'opti-behavior' ),
				$current_deleted,
				$total
			),
		);
	}

	/**
	 * Delete sessions matching conditions with cascading deletion.
	 * Returns batch info for step-by-step AJAX processing.
	 *
	 * @since 1.0.9
	 * @param array $conditions Cleanup conditions.
	 * @param int   $step       Current step (0-based).
	 * @return array Result with status, progress, and counts.
	 */
	public function delete_sessions_by_conditions_impl( $conditions, $step = 0 ) {
		global $wpdb;
		$batch_size = 500;

		if ( $step === 0 ) {
			// Step 0: Count total matching and store
			$total = $this->count_sessions_by_conditions( $conditions );
			update_option( 'optibehavior_smart_cleanup_total', $total, false );
			update_option( 'optibehavior_smart_cleanup_deleted', 0, false );
		}

		// Get batch of matching session IDs
		$session_ids = $this->get_matching_session_ids( $conditions, $batch_size, 0 );

		if ( empty( $session_ids ) ) {
			// No more matching sessions
			$delete_orphans = isset( $conditions['delete_orphaned_visitors'] ) ? (bool) $conditions['delete_orphaned_visitors'] : true;
			if ( $delete_orphans ) {
				$this->delete_orphaned_visitors();
			}

			$this->clear_analytics_caches();

			$total = get_option( 'optibehavior_smart_cleanup_total', 0 );
			$deleted = get_option( 'optibehavior_smart_cleanup_deleted', 0 );

			$this->add_cleanup_log( 'manual', $deleted, 0, 0 );

			delete_option( 'optibehavior_smart_cleanup_total' );
			delete_option( 'optibehavior_smart_cleanup_deleted' );

			return array(
				'status'   => 'completed',
				'total'    => $total,
				'deleted'  => $deleted,
				'progress' => 100,
			);
		}

		// Cascading delete for this batch
		$deleted_count = $this->cascade_delete_sessions( $session_ids );

		// Update progress
		$prev_deleted = get_option( 'optibehavior_smart_cleanup_deleted', 0 );
		update_option( 'optibehavior_smart_cleanup_deleted', $prev_deleted + $deleted_count, false );

		$total = get_option( 'optibehavior_smart_cleanup_total', 1 );
		$current_deleted = $prev_deleted + $deleted_count;
		$progress = $total > 0 ? min( 95, round( ( $current_deleted / $total ) * 100 ) ) : 50;

		return array(
			'status'   => 'processing',
			'step'     => $step + 1,
			'deleted'  => $current_deleted,
			'total'    => $total,
			'progress' => $progress,
			'message'  => sprintf(
				/* translators: 1: number deleted, 2: total */
				__( 'Deleted %1$d of %2$d sessions...', 'opti-behavior' ),
				$current_deleted,
				$total
			),
		);
	}

	/**
	 * Cascade delete sessions and all related data.
	 *
	 * @since 1.0.9
	 * @param array $session_ids Array of session IDs to delete.
	 * @return int Number of sessions deleted.
	 */
	private function cascade_delete_sessions( $session_ids ) {
		global $wpdb;

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

		// Build safe IN clause
		$placeholders = implode( ',', array_fill( 0, count( $session_ids ), '%s' ) );

		$related_tables = array(
			'optibehavior_events'            => 'session_id',
			'optibehavior_pageviews'         => 'session_id',
			'optibehavior_recordings'        => 'session_id',
			'optibehavior_session_pages'     => 'session_id',
			'optibehavior_referrers'         => 'session_id',
			'optibehavior_outbound_clicks'   => 'session_id',
			'optibehavior_friction'          => 'session_id',
			'optibehavior_errors'            => 'session_id',
			'optibehavior_performance'       => 'session_id',
			'optibehavior_form_interactions' => 'session_id', // Bug 2: form analytics cascade
			'optibehavior_form_submissions'  => 'session_id', // Bug 2: form analytics cascade
		);

		// Delete recording files before removing DB records
		$recordings_table = esc_sql( $wpdb->prefix . 'optibehavior_recordings' );
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare,PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table names from $wpdb->prefix; analytics queries.
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$file_paths = $wpdb->get_col( $wpdb->prepare(
			// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			"SELECT file_path FROM " . $recordings_table . " WHERE session_id IN (" . $placeholders . ") AND file_path IS NOT NULL AND file_path != ''",
			...$session_ids
		) );

		if ( ! empty( $file_paths ) ) {
			// Bug 1: recording file_path is relative; prepend absolute base dir.
			$upload_dir = wp_upload_dir();
			$base_dir   = trailingslashit( $upload_dir['basedir'] ) . 'opti-behavior-data/';
			foreach ( $file_paths as $path ) {
				if ( ! empty( $path ) ) {
					$full_path = $base_dir . ltrim( $path, '/\\' );
					if ( file_exists( $full_path ) ) {
						wp_delete_file( $full_path );
					}
				}
			}
		}

		// Delete from all related tables
		foreach ( $related_tables as $table_suffix => $column ) {
			$table = esc_sql( $wpdb->prefix . $table_suffix );
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table names from $wpdb->prefix; analytics queries.
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			$exists = $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $wpdb->prefix . $table_suffix ) );
			if ( $exists ) {
				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare,PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table names from $wpdb->prefix; analytics queries.
				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
				$wpdb->query( $wpdb->prepare(
					// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
					"DELETE FROM " . $table . " WHERE " . $column . " IN (" . $placeholders . ")",
					...$session_ids
				) );
			}
		}

		// Delete the sessions themselves
		$sessions_table = esc_sql( $wpdb->prefix . 'optibehavior_sessions' );
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare,PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table names from $wpdb->prefix; analytics queries.
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$deleted = $wpdb->query( $wpdb->prepare(
			// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			"DELETE FROM " . $sessions_table . " WHERE id IN (" . $placeholders . ")",
			...$session_ids
		) );

		return $deleted !== false ? $deleted : 0;
	}

	/**
	 * Delete orphaned visitors (visitors with no remaining sessions).
	 *
	 * @since 1.0.9
	 * @return int Number of visitors deleted.
	 */
	private function delete_orphaned_visitors() {
		global $wpdb;
		$visitors_table = esc_sql( $wpdb->prefix . 'optibehavior_visitors' );
		$sessions_table = esc_sql( $wpdb->prefix . 'optibehavior_sessions' );

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.PreparedSQL.NotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter -- No user input, table names from esc_sql()
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$deleted = $wpdb->query(
			// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			"DELETE v FROM " . $visitors_table . " v
			 LEFT JOIN " . $sessions_table . " s ON s.visitor_id = v.id
			 WHERE s.id IS NULL"
		);

		return $deleted !== false ? $deleted : 0;
	}

	/**
	 * Clear analytics-related transient caches.
	 *
	 * @since 1.0.9
	 */
	private function clear_analytics_caches() {
		global $wpdb;
		$options_table = esc_sql( $wpdb->prefix . 'options' );

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table names from $wpdb->prefix; analytics queries.
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$wpdb->query( "DELETE FROM " . $options_table . " WHERE option_name LIKE '_transient_optibehavior_%'" );
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table names from $wpdb->prefix; analytics queries.
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$wpdb->query( "DELETE FROM " . $options_table . " WHERE option_name LIKE '_transient_timeout_optibehavior_%'" );
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table names from $wpdb->prefix; analytics queries.
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$wpdb->query( "DELETE FROM " . $options_table . " WHERE option_name LIKE '_transient_opti_behavior_%'" );
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table names from $wpdb->prefix; analytics queries.
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$wpdb->query( "DELETE FROM " . $options_table . " WHERE option_name LIKE '_transient_timeout_opti_behavior_%'" );

		// Bug 3: Remove visitor_daily_stats rows with no matching sessions (orphaned summary data).
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table names from $wpdb->prefix; analytics queries.
		$vds_tbl_exists = $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $wpdb->prefix . 'optibehavior_visitor_daily_stats' ) );
		if ( $vds_tbl_exists ) {
			$summary_table  = esc_sql( $wpdb->prefix . 'optibehavior_visitor_daily_stats' );
			$sessions_table = esc_sql( $wpdb->prefix . 'optibehavior_sessions' );
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table names from $wpdb->prefix; analytics queries.
			$wpdb->query(
				"DELETE vds FROM " . $summary_table . " vds
				 LEFT JOIN " . $sessions_table . " s
				   ON s.visitor_id = vds.visitor_id
				   AND DATE(s.start_time) = vds.stat_date
				 WHERE s.id IS NULL"
			);
		}

		if ( function_exists( 'wp_cache_flush' ) ) {
			wp_cache_flush();
		}
	}

	/**
	 * Run scheduled cleanup using saved conditions.
	 *
	 * @since 1.0.9
	 */
	public function run_scheduled_cleanup() {
		$settings = get_option( 'opti_behavior_auto_cleanup_settings', array() );

		if ( empty( $settings['enabled'] ) ) {
			return;
		}

		// Bug 7: Monthly frequency is scheduled as 'daily' by WordPress; check elapsed time ourselves.
		if ( isset( $settings['frequency'] ) && $settings['frequency'] === 'monthly' ) {
			$last_run = ! empty( $settings['last_run'] ) ? intval( $settings['last_run'] ) : 0;
			if ( $last_run && ( time() - $last_run ) < 30 * DAY_IN_SECONDS ) {
				return; // Not yet 30 days since last monthly cleanup.
			}
		}

		$conditions = isset( $settings['conditions'] ) ? $settings['conditions'] : array();
		if ( empty( $conditions ) ) {
			return;
		}

		// Add orphan setting to conditions
		$conditions['delete_orphaned_visitors'] = ! empty( $settings['delete_orphaned_visitors'] );

		// Count before deletion for logging
		$total_to_delete = $this->count_sessions_by_conditions( $conditions );

		if ( $total_to_delete === 0 ) {
			// Update last_run even if nothing to delete
			$settings['last_run'] = current_time( 'timestamp' );
			update_option( 'opti_behavior_auto_cleanup_settings', $settings );
			return;
		}

		// Process all matching sessions (non-batched since this is cron)
		$total_deleted = 0;
		$batch_size = 500;

		while ( true ) {
			$session_ids = $this->get_matching_session_ids( $conditions, $batch_size, 0 );
			if ( empty( $session_ids ) ) {
				break;
			}
			$deleted = $this->cascade_delete_sessions( $session_ids );
			$total_deleted += $deleted;

			// Safety: prevent infinite loop if deletion fails
			if ( $deleted === 0 ) {
				break;
			}
		}

		// Clean orphaned visitors
		if ( ! empty( $conditions['delete_orphaned_visitors'] ) ) {
			$this->delete_orphaned_visitors();
		}

		$this->clear_analytics_caches();

		// Update last_run
		$settings['last_run'] = current_time( 'timestamp' );
		update_option( 'opti_behavior_auto_cleanup_settings', $settings );

		// Log the cleanup
		$this->add_cleanup_log( 'scheduled', $total_deleted, 0, 0 );
	}

	/**
	 * Add a cleanup log entry.
	 *
	 * @since 1.0.9
	 * @param string $type             Cleanup type (manual|scheduled).
	 * @param int    $sessions_deleted  Number of sessions deleted.
	 * @param int    $events_deleted    Number of events deleted.
	 * @param int    $files_deleted     Number of files deleted.
	 */
	public function add_cleanup_log( $type, $sessions_deleted, $events_deleted = 0, $files_deleted = 0 ) {
		$logs = get_option( 'opti_behavior_cleanup_logs', array() );
		if ( ! is_array( $logs ) ) {
			$logs = array();
		}

		array_unshift( $logs, array(
			'timestamp'        => current_time( 'mysql' ),
			'type'             => sanitize_text_field( $type ),
			'sessions_deleted' => absint( $sessions_deleted ),
			'events_deleted'   => absint( $events_deleted ),
			'files_deleted'    => absint( $files_deleted ),
		) );

		// Keep only last 20 entries
		$logs = array_slice( $logs, 0, 20 );

		update_option( 'opti_behavior_cleanup_logs', $logs, false );
	}

	/**
	 * Get cleanup log entries.
	 *
	 * @since 1.0.9
	 * @param int $limit Maximum entries to return.
	 * @return array Array of log entries.
	 */
	public function get_cleanup_logs( $limit = 10 ) {
		$logs = get_option( 'opti_behavior_cleanup_logs', array() );
		if ( ! is_array( $logs ) ) {
			return array();
		}
		return array_slice( $logs, 0, $limit );
	}
}

