<?php
/**
 * ProQyz Database Migration Utility
 * 
 * Handles migration from v1 (st_proqyz_quiz_progress) to v2 (st_proqyz_progress_submissions).
 * 
 * Features:
 * - Batch processing (100 records at a time to prevent timeouts)
 * - Progress tracking with percentage
 * - Pause/Resume capability
 * - Error logging and recovery
 * - Rollback functionality
 * - Memory-efficient processing
 * 
 * @package ST_ProQyz
 * @since   2.1.0
 */

defined( 'ABSPATH' ) || exit;

class ST_ProQyz_Migration {

	const BATCH_SIZE = 100;
	const MIGRATION_OPTION = 'st_proqyz_migration_progress';
	const MIGRATION_LOG = 'st_proqyz_migration_log';
	const MIGRATION_BACKUP = 'st_proqyz_migration_backup_ids';

	private $wpdb;
	private $old_table;
	private $old_meta_table;
	private $old_review_table;
	private $old_review_meta_table;

	private $new_table;
	private $new_meta_table;
	private $new_meta_submissions_table;
	private $new_review_table;
	private $new_review_meta_table;

	

	/**
	 * Constructor
	 */
	public function __construct() {
		global $wpdb, 
			$table_proqyz_quiz_progress,
			$table_proqyz_quiz_progress_meta,
			$table_proqyz_quiz_review,
			$table_proqyz_quiz_review_meta,

			$table_proqyz_progress_submissions,
			$table_proqyz_progress_submissions_meta,
			$table_proqyz_progress_meta_submissions,
			$table_proqyz_submissions_review,
			$table_proqyz_submissions_review_meta;


		$this->wpdb = $wpdb;
		
		// Fallback if globals aren't set yet
		$this->old_table = !empty($table_proqyz_quiz_progress) 
			? $table_proqyz_quiz_progress 
			: $wpdb->prefix . 'st_proqyz_quiz_progress';

		$this->old_meta_table = !empty($table_proqyz_quiz_progress_meta) 
			? $table_proqyz_quiz_progress_meta 
			: $wpdb->prefix . 'st_proqyz_quiz_progress_meta';

		$this->old_review_table = !empty($table_proqyz_quiz_review) 
			? $table_proqyz_quiz_review 
			: $wpdb->prefix . 'st_proqyz_quiz_progress_review';

		$this->old_review_meta_table = !empty($table_proqyz_quiz_review_meta) 
			? $table_proqyz_quiz_review_meta 
			: $wpdb->prefix . 'st_proqyz_quiz_progress_review_meta';

		# -------------------------------------------------------------------------------------	
		// progress submission table	
		$this->new_table = !empty($table_proqyz_progress_submissions) 
			? $table_proqyz_progress_submissions 
			: $wpdb->prefix . 'st_proqyz_progress_submissions';
			
		// progress submission meta table
		$this->new_meta_table = !empty($table_proqyz_progress_submissions_meta) 
			? $table_proqyz_progress_submissions_meta 
			: $wpdb->prefix . 'st_proqyz_progress_submissions_meta';

		// progress meta submissions table
		$this->new_meta_submissions_table = !empty($table_proqyz_progress_meta_submissions) 
			? $table_proqyz_progress_meta_submissions 
			: $wpdb->prefix.'st_proqyz_progress_meta_submissions';

		// submissions review table
		$this->new_review_table = !empty($table_proqyz_submissions_review) 
			? $table_proqyz_submissions_review 
			: $wpdb->prefix . 'st_proqyz_submissions_review';

		// submissions review meta table
		$this->new_review_meta_table = !empty($table_proqyz_submissions_review_meta) 
			? $table_proqyz_submissions_review_meta 
			: $wpdb->prefix . 'st_proqyz_submissions_review_meta';
	}

	/**
	 * Get migration status
	 * 
	 * @return array Migration progress data
	 */
	public function get_migration_status() {
		$progress = get_option(self::MIGRATION_OPTION, array(
			'status' => 'not_started',
			'total_records' => 0,
			'processed' => 0,
			'batch' => 0,
			'errors' => 0,
			'started_at' => null,
			'completed_at' => null,
			'last_processed_id' => 0,
		));

		// Calculate percentage
		if ($progress['total_records'] > 0) {
			$progress['percentage'] = round(($progress['processed'] / $progress['total_records']) * 100, 2);
		} else {
			$progress['percentage'] = 0;
		}

		return $progress;
	}

	/**
	 * Initialize migration
	 * Counts total records and prepares for migration
	 * 
	 * Filters: status = 1 AND session_status != 3
	 * 
	 * @return array Status information
	 */
	public function init_migration() {
		// Count total records in old table (status = 1 AND session_status != 3)
		$total = $this->wpdb->get_var("SELECT COUNT(*) FROM {$this->old_table} WHERE status = 1 AND session_status != 3");

		$progress = array(
			'status' => 'initialized',
			'total_records' => (int) $total,
			'processed' => 0,
			'batch' => 0,
			'errors' => 0,
			'started_at' => current_time('mysql'),
			'completed_at' => null,
			'last_processed_id' => 0,
		);

		update_option(self::MIGRATION_OPTION, $progress);

		// Initialize log
		update_option(self::MIGRATION_LOG, array());

		return array(
			'success' => true,
			'total_records' => $total,
			'estimated_batches' => ceil($total / self::BATCH_SIZE),
			'message' => sprintf('Ready to migrate %d records in %d batches (filtered: status=1, session_status!=3)', $total, ceil($total / self::BATCH_SIZE))
		);
	}

	/**
	 * Process one batch of migration
	 * 
	 * @param int $batch_number Optional. Specific batch to process.
	 * @return array Batch processing result
	 */
	public function process_batch($batch_number = null) {
		$progress = $this->get_migration_status();

		// Check if already completed
		if ($progress['status'] === 'completed') {
			return array(
				'success' => false,
				'message' => 'Migration already completed',
				'progress' => $progress
			);
		}

		// Set status to running
		$progress['status'] = 'running';
		update_option(self::MIGRATION_OPTION, $progress);

		$offset = $batch_number !== null ? ($batch_number * self::BATCH_SIZE) : $progress['processed'];

		// Fetch batch of records from old table (status = 1 AND session_status != 3)
		$records = $this->wpdb->get_results(
			$this->wpdb->prepare(
				"SELECT * FROM {$this->old_table} 
				WHERE status = 1 AND session_status != 3
				ORDER BY ID ASC 
				LIMIT %d OFFSET %d",
				self::BATCH_SIZE,
				$offset
			),
			ARRAY_A
		);

		if (empty($records)) {
			// Mark as completed
			$progress['status'] = 'completed';
			$progress['completed_at'] = current_time('mysql');
			update_option(self::MIGRATION_OPTION, $progress);

			return array(
				'success' => true,
				'message' => 'Migration completed successfully!',
				'progress' => $progress,
				'is_complete' => true
			);
		}

		$migrated = 0;
		$errors = 0;
		$log = get_option(self::MIGRATION_LOG, array());

		foreach ($records as $record) {
			try {
				$this->migrate_single_record($record);
				$migrated++;
				$progress['last_processed_id'] = $record['ID'];
			} catch (Exception $e) {
				$errors++;
				$log[] = array(
					'record_id' => $record['ID'],
					'error' => $e->getMessage(),
					'time' => current_time('mysql')
				);
			}
		}

		// Update progress
		$progress['processed'] += $migrated;
		$progress['batch']++;
		$progress['errors'] += $errors;
		$progress['status'] = 'paused'; // Pause after each batch for manual continuation

		update_option(self::MIGRATION_OPTION, $progress);
		update_option(self::MIGRATION_LOG, $log);

		return array(
			'success' => true,
			'migrated' => $migrated,
			'errors' => $errors,
			'progress' => $this->get_migration_status(),
			'is_complete' => false,
			'message' => sprintf('Batch %d completed. Migrated %d records, %d errors.', $progress['batch'], $migrated, $errors)
		);
	}

	/**
	 * Migrate a single record from old table to new tables
	 * 
	 * @param array $record Old table record
	 * @throws Exception If migration fails
	 */
	private function migrate_single_record($record) {
		// Debug: Log quiz_category for EVERY record
		if (defined('WP_DEBUG') && WP_DEBUG) {
			error_log("ProQyz Migration RECORD [ID: {$record['ID']}]: quiz_category = '{$record['quiz_category']}'");
		}
		
		// Insert into submissions table (index/filter data only)
		$submission_data = array(
			'user_id' => absint($record['user_id']),
			'quiz_category' => sanitize_text_field($record['quiz_category']),
			'session_type' => sanitize_text_field($record['session_type']),
			'session_category' => sanitize_text_field($record['session_category']),
			'session_status' => absint($record['session_status']),
			'course_category' => !empty($record['course_category']) ? sanitize_text_field($record['course_category']) : null,
			'course_id' => !empty($record['course_id']) ? sanitize_text_field($record['course_id']) : null,
			'topic_id' => !empty($record['topic_id']) ? sanitize_text_field($record['topic_id']) : null,
			'lesson_id' => !empty($record['lesson_id']) ? sanitize_text_field($record['lesson_id']) : null,
			'api_id' => sanitize_text_field($record['api_id']),
			'review' => absint($record['review']),
			'status' => absint($record['status']),
		);

		// Convert date string to Unix timestamp
		if (!empty($record['date'])) {
			// If date is already a timestamp
			if (is_numeric($record['date'])) {
				$submission_data['date_created'] = absint($record['date']);
			} else {
				// Try to parse date
				$timestamp = strtotime($record['date']);
				$submission_data['date_created'] = $timestamp !== false ? $timestamp : time();
			}
		} else {
			$submission_data['date_created'] = time();
		}

		$submission_data['date_modified'] = time();

		// Insert into submissions table
		$result = $this->wpdb->insert(
			$this->new_table,
			$submission_data,
			array(
				'%d', '%s', '%s', '%s', '%d', '%s', '%s', '%s', '%s', '%s', '%d', '%d', '%d', '%d'
			)
		);

		if (!$result) {
			throw new Exception('Failed to insert into submissions table: ' . $this->wpdb->last_error);
		}

		$submission_id = $this->wpdb->insert_id;

		// Prepare meta data to insert
		$meta_to_insert = array();
		
		// Store original blob data
		if (!empty($record['session_detail'])) {
			$meta_to_insert['session_detail'] = $record['session_detail'];
		}
		if (!empty($record['json'])) {
			$meta_to_insert['json'] = $record['json'];
		}
		if (!empty($record['quiz'])) {
			$meta_to_insert['quiz'] = $record['quiz'];
		}
		if (!empty($record['score'])) {
			$meta_to_insert['score'] = $record['score'];
		}
		
		// Store original ID for reference
		$meta_to_insert['_migrated_from_id'] = $record['ID'];
		$old_result_id = $record['ID'];

		// we need to fetch the usermeta with old_result_id including this prefix inside metakey: st_proqyz__evaluation-$old_result_id
		// get this meta value and store it in a variable
		$enrolled_for = get_user_meta($record['user_id'], "st_proqyz__evaluation-{$old_result_id}", true);
		if(!empty($enrolled_for)) {
			// need value as its stored 
			// now we first need to create its backup 
			add_user_meta($record['user_id'], "BACKUP_st_proqyz__evaluation-{$old_result_id}", $enrolled_for);
			// no we need to add our new value to the user meta
			update_user_meta($record['user_id'], "st_proqyz__evaluation-{$submission_id}", $enrolled_for);
			// now we need to delete the old meta
			delete_user_meta($record['user_id'], "st_proqyz__evaluation-{$old_result_id}");
		}

		// using the old id we need to check the review with the old id 
		// if review found, we need to change its session_id to new result id  
		// $this->wpdb->update($this->old_review_table, ['result_id' => $submission_id], ['result_id' => $old_result_id]);

		// using old id we need to check the progress_meta table with old id
		// if progress_meta found, we need to change its session_id to new result_id
		// $this->wpdb->update($this->old_meta_table, ['session_id' => $submission_id], ['session_id' => $old_result_id]); 
		

		// ===================================================================
		// Task 1: Migrate Reviews and Review Meta (Copy to new tables)
		// ===================================================================
		// Find all reviews in old_review_table where result_id = old_result_id
		$old_reviews = $this->wpdb->get_results(
			$this->wpdb->prepare(
				"SELECT * FROM {$this->old_review_table} WHERE result_id = %d",
				$old_result_id
			),
			ARRAY_A
		);

		foreach ($old_reviews as $old_review) {
			// Copy review to new_review_table with new submission_id
			$new_review_data = array(
				'result_id' => $submission_id, // Use new submission ID
				'user_id' => absint($old_review['user_id']),
				'section_id' => sanitize_text_field($old_review['section_id']),
				'status' => absint($old_review['status']),
			);

			$review_insert_result = $this->wpdb->insert(
				$this->new_review_table,
				$new_review_data,
				array('%d', '%d', '%s', '%d')
			);

			if (!$review_insert_result) {
				throw new Exception('Failed to insert review into new_review_table: ' . $this->wpdb->last_error);
			}

			$new_review_id = $this->wpdb->insert_id;
			$old_review_id = $old_review['ID'];

			// Find all review_meta entries for this old review_id
			$old_review_metas = $this->wpdb->get_results(
				$this->wpdb->prepare(
					"SELECT * FROM {$this->old_review_meta_table} WHERE review_id = %d",
					$old_review_id
				),
				ARRAY_A
			);

			// Copy each review_meta entry to new_review_meta_table with new review_id
			foreach ($old_review_metas as $old_review_meta) {
				$new_review_meta_data = array(
					'review_id' => $new_review_id, // Use new review ID
					'meta_key' => sanitize_text_field($old_review_meta['meta_key']),
					'meta_value' => $old_review_meta['meta_value'], // Keep as-is (longtext)
					'meta_status' => absint($old_review_meta['meta_status']),
				);

				$review_meta_insert_result = $this->wpdb->insert(
					$this->new_review_meta_table,
					$new_review_meta_data,
					array('%d', '%s', '%s', '%d')
				);

				if (!$review_meta_insert_result) {
					throw new Exception('Failed to insert review_meta into new_review_meta_table: ' . $this->wpdb->last_error);
				}
			}

			if (defined('WP_DEBUG') && WP_DEBUG && !empty($old_review_metas)) {
				error_log("ProQyz Migration: ✓ Migrated review [ID: {$old_review_id} → {$new_review_id}] with " . count($old_review_metas) . " meta entries");
			}
		}

		// ===================================================================
		// Task 2: Migrate Progress Meta (Copy to new_meta_submissions_table)
		// ===================================================================
		// Find all progress_meta entries in old_meta_table where session_id = old_result_id
		$old_progress_metas = $this->wpdb->get_results(
			$this->wpdb->prepare(
				"SELECT * FROM {$this->old_meta_table} WHERE session_id = %s",
				$old_result_id
			),
			ARRAY_A
		);

		foreach ($old_progress_metas as $old_progress_meta) {
			// Copy progress_meta to new_meta_submissions_table with new submission_id
			$new_progress_meta_data = array(
				'user_id' => absint($old_progress_meta['user_id']),
				'session_type' => sanitize_text_field($old_progress_meta['session_type']),
				'session_id' => (string) $submission_id, // Update to new submission ID
				'quiz_id' => sanitize_text_field($old_progress_meta['quiz_id']),
				'section_id' => sanitize_text_field($old_progress_meta['section_id']),
				'question_ids' => sanitize_text_field($old_progress_meta['question_ids']),
				'question_intervals' => sanitize_text_field($old_progress_meta['question_intervals']),
				'json' => $old_progress_meta['json'], // Keep as-is (longtext)
				'filename' => sanitize_text_field($old_progress_meta['filename']),
				'completed' => absint($old_progress_meta['completed']),
				'status' => absint($old_progress_meta['status']),
				'date' => absint($old_progress_meta['date']),
			);

			$progress_meta_insert_result = $this->wpdb->insert(
				$this->new_meta_submissions_table,
				$new_progress_meta_data,
				array('%d', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%d', '%d', '%d')
			);

			if (!$progress_meta_insert_result) {
				throw new Exception('Failed to insert progress_meta into new_meta_submissions_table: ' . $this->wpdb->last_error);
			}
		}

		if (defined('WP_DEBUG') && WP_DEBUG && !empty($old_progress_metas)) {
			error_log("ProQyz Migration: ✓ Migrated " . count($old_progress_metas) . " progress_meta entries for submission {$submission_id}");
		}

		// Extract and store specific fields from serialized data
		// Extract from session_detail: course->title, course->category
		if (!empty($record['session_detail'])) {
			$session_detail = @unserialize($record['session_detail']);
			if ($session_detail && is_object($session_detail)) {
				// Extract course title
				$course = isset($session_detail->course) ? (object) $session_detail->course : null;
				if ($course) {
					if (isset($course->title)) {
						$meta_to_insert['course_title'] = $course->title;
					}
					if (isset($course->category)) {
						$meta_to_insert['course_subcategory'] = $course->category;
					}

					if(isset($session_detail->session_category)) {
						$meta_to_insert['session_category'] = $session_detail->session_category;
						if($session_detail->session_category == 'fullmock-test') {
							if(isset($session_detail->lesson_id)) {
								$lesson_id = $session_detail->lesson_id;
								$lessons = (array) isset($session_detail->lessons) ? $session_detail->lessons : array();
								foreach($lessons as $lesson) {
									$lesson = (object) $lesson;
									if(isset($lesson->_id) && $lesson->_id == $lesson_id) {
										$meta_to_insert['lesson_title'] = $lesson->title;
										break;
									}
								}
							}
						}
					}
				}
			}
		}

		// Extract from quiz: quiz->slug, quiz->title
		if (!empty($record['quiz'])) {
			$quiz_data = @unserialize($record['quiz']);
			
			if ($quiz_data && is_object($quiz_data)) {
				// Extract quiz slug and title
				if (isset($quiz_data->quiz) && is_object($quiz_data->quiz)) {
					if (isset($quiz_data->quiz->slug)) {
						$meta_to_insert['quiz_slug'] = $quiz_data->quiz->slug;
					}
					if (isset($quiz_data->quiz->title)) {
						$meta_to_insert['quiz_title'] = $quiz_data->quiz->title;
					}
				}
				
				// Extract sections for writing and speaking quizzes
				if ($record['quiz_category'] == 'ielts-writing' || $record['quiz_category'] == 'ielts-speaking') {
					if (isset($quiz_data->sections)) {
						$sections = !empty($quiz_data->sections) ? (array) $quiz_data->sections : array();

						$simplified_sections = array();
						
						foreach ($sections as $section) {
							if (is_object($section)) {
								$simplified_section = new stdClass();
								
								// Extract only the required fields
								if (isset($section->_id)) {
									$simplified_section->_id = $section->_id;
								}
								if (isset($section->title)) {
									$simplified_section->title = $section->title;
								}
								if (isset($section->order)) {
									$simplified_section->order = $section->order;
								}
								
								$simplified_sections[] = $simplified_section;
							}
						}
						
						// Serialize and store the simplified sections array
						if (!empty($simplified_sections)) {
							$meta_to_insert['sections'] = serialize($simplified_sections);
							
							if (defined('WP_DEBUG') && WP_DEBUG) {
								error_log("ProQyz Migration SUCCESS [ID: {$record['ID']}]: Extracted " . count($simplified_sections) . " sections for {$record['quiz_category']} quiz");
							}
						} else {
							if (defined('WP_DEBUG') && WP_DEBUG) {
								error_log("ProQyz Migration WARNING [ID: {$record['ID']}]: No sections extracted (simplified_sections is empty)");
							}
						}
					}
				}
			}
		} else {
			// Debug: quiz field is empty
			if (defined('WP_DEBUG') && WP_DEBUG && ($record['quiz_category'] === 'ielts-writing' || $record['quiz_category'] === 'ielts-speaking')) {
				error_log("ProQyz Migration SKIP [ID: {$record['ID']}]: quiz field is empty!");
			}
		}

		// Insert all meta data
		$meta_inserted_count = 0;
		foreach ($meta_to_insert as $meta_key => $meta_value) {
			// Skip only if NULL or completely missing (not empty strings!)
			if ($meta_value === null || $meta_value === '') {
				// Skip empty values but log it
				if (defined('WP_DEBUG') && WP_DEBUG) {
					error_log("ProQyz Migration: Skipping empty meta key '{$meta_key}' for submission {$submission_id}");
				}
				continue;
			}

			// Log what we're inserting
			if (defined('WP_DEBUG') && WP_DEBUG) {
				$value_preview = is_string($meta_value) ? substr($meta_value, 0, 100) . '...' : $meta_value;
				error_log("ProQyz Migration: Inserting meta '{$meta_key}' for submission {$submission_id} (length: " . strlen($meta_value) . ")");
			}

			$meta_result = $this->wpdb->insert(
				$this->new_meta_table,
				array(
					'submission_id' => $submission_id,
					'meta_key' => $meta_key,
					'meta_value' => $meta_value, // Already a string
					'created_at' => time(),
				),
				array('%d', '%s', '%s', '%d')
			);

			if (!$meta_result) {
				$error_msg = 'Failed to insert meta "' . $meta_key . '": ' . $this->wpdb->last_error;
				if (defined('WP_DEBUG') && WP_DEBUG) {
					error_log("ProQyz Migration ERROR: " . $error_msg);
				}
				throw new Exception($error_msg);
			} else {
				$meta_inserted_count++;
				if (defined('WP_DEBUG') && WP_DEBUG) {
					error_log("ProQyz Migration: ✓ Meta '{$meta_key}' inserted successfully (meta_id: {$this->wpdb->insert_id})");
				}
			}
		}

		// Log summary
		if (defined('WP_DEBUG') && WP_DEBUG) {
			error_log("ProQyz Migration: ✓ Submission {$record['ID']} → {$submission_id} with {$meta_inserted_count} meta entries");
		}

		// Store mapping for rollback
		$this->add_to_backup_mapping($record['ID'], $submission_id);
	}

	/**
	 * Store ID mapping for potential rollback
	 * 
	 * @param int $old_id Old table ID
	 * @param int $new_id New table ID
	 */
	private function add_to_backup_mapping($old_id, $new_id) {
		$backup = get_option(self::MIGRATION_BACKUP, array());
		$backup[$old_id] = $new_id;
		update_option(self::MIGRATION_BACKUP, $backup);
	}

	/**
	 * Run automatic migration (all batches)
	 * WARNING: Can take a long time for large datasets
	 * 
	 * @param callable $callback Optional. Callback function for progress updates
	 * @return array Final migration result
	 */
	public function run_full_migration($callback = null) {
		// Initialize
		$init_result = $this->init_migration();
		if (!$init_result['success']) {
			return $init_result;
		}

		$total_batches = $init_result['estimated_batches'];

		// Process all batches
		for ($i = 0; $i < $total_batches; $i++) {
			$result = $this->process_batch($i);
			
			if ($callback && is_callable($callback)) {
				call_user_func($callback, $result);
			}

			if (!$result['success']) {
				return $result;
			}

			if (isset($result['is_complete']) && $result['is_complete']) {
				break;
			}

			// Prevent timeout - sleep briefly between batches
			usleep(100000); // 0.1 seconds
		}

		return $this->get_migration_status();
	}

	/**
	 * Rollback migration
	 * Truncates all new tables to remove migrated data
	 * 
	 * @return array Rollback result
	 */
	public function rollback() {
		$backup = get_option(self::MIGRATION_BACKUP, array());
		
		if (empty($backup)) {
			return array(
				'success' => false,
				'message' => 'No migration to rollback'
			);
		}

		// Get counts before truncating (for reporting)
		$submission_count = $this->wpdb->get_var("SELECT COUNT(*) FROM {$this->new_table}");
		$meta_count = $this->wpdb->get_var("SELECT COUNT(*) FROM {$this->new_meta_table}");
		$progress_meta_count = $this->wpdb->get_var("SELECT COUNT(*) FROM {$this->new_meta_submissions_table}");
		$review_count = $this->wpdb->get_var("SELECT COUNT(*) FROM {$this->new_review_table}");
		$review_meta_count = $this->wpdb->get_var("SELECT COUNT(*) FROM {$this->new_review_meta_table}");

		// Truncate all new tables (faster than DELETE and removes all data)
		// Order matters: delete child tables first to avoid foreign key issues
		
		// 1. Truncate review meta (child of review)
		$this->wpdb->query("TRUNCATE TABLE {$this->new_review_meta_table}");
		$deleted_review_meta = $review_meta_count;
		
		// 2. Truncate reviews (child of submissions)
		$this->wpdb->query("TRUNCATE TABLE {$this->new_review_table}");
		$deleted_reviews = $review_count;
		
		// 3. Truncate progress meta submissions (child of submissions)
		$this->wpdb->query("TRUNCATE TABLE {$this->new_meta_submissions_table}");
		$deleted_progress_meta = $progress_meta_count;
		
		// 4. Truncate submissions meta (child of submissions)
		$this->wpdb->query("TRUNCATE TABLE {$this->new_meta_table}");
		$deleted_meta = $meta_count;
		
		// 5. Truncate submissions (parent table)
		$this->wpdb->query("TRUNCATE TABLE {$this->new_table}");
		$deleted_submissions = $submission_count;

		// 6. remove all the user_meta which has this prefix: st_proqyz__evaluation- but dont remove the backup meta as 'BACKUP_st_proqyz__evaluation-' exclude this from the query
		$this->wpdb->query("DELETE FROM {$this->wpdb->usermeta} WHERE meta_key LIKE 'st_proqyz__evaluation-%' AND meta_key NOT LIKE 'BACKUP_st_proqyz__evaluation-%'");
		$deleted_user_meta = $this->wpdb->rows_affected;
		// now we need to rename the BACKUP_meta to the original meta key
		$this->wpdb->query("UPDATE {$this->wpdb->usermeta} SET meta_key = REPLACE(meta_key, 'BACKUP_', '') WHERE meta_key LIKE 'BACKUP_st_proqyz__evaluation-%'");
		$renamed_user_meta = $this->wpdb->rows_affected;

		// Clear migration data
		delete_option(self::MIGRATION_OPTION);
		delete_option(self::MIGRATION_LOG);
		delete_option(self::MIGRATION_BACKUP);

		// Reset DB versions (both ProQyz and main Spacetree)
		global $ST_OPTION_st_proqyz__db_version, $st_db_version_key;
		update_option($ST_OPTION_st_proqyz__db_version, '1.0');
		update_option($st_db_version_key, '1.0'); // This switches back to v1 update channel

		// Fire action
		do_action('proqyz_migration_rolled_back');

		$total_deleted = $deleted_submissions + $deleted_meta + $deleted_progress_meta + $deleted_reviews + $deleted_review_meta;

		return array(
			'success' => true,
			'deleted_submissions' => (int) $deleted_submissions,
			'deleted_meta' => (int) $deleted_meta,
			'deleted_progress_meta' => (int) $deleted_progress_meta,
			'deleted_reviews' => (int) $deleted_reviews,
			'deleted_review_meta' => (int) $deleted_review_meta,
			'total_deleted' => (int) $total_deleted,
			'message' => sprintf(
				'Rollback completed successfully!<br><br>' .
				'<strong>Truncated new tables:</strong><br>' .
				'• %s submissions<br>' .
				'• %s submission meta entries<br>' .
				'• %s progress meta entries<br>' .
				'• %s reviews<br>' .
				'• %s review meta entries<br><br>' .
				'<strong>Total:</strong> %s records removed. Switched back to v1 update channel.',
				number_format($deleted_submissions),
				number_format($deleted_meta),
				number_format($deleted_progress_meta),
				number_format($deleted_reviews),
				number_format($deleted_review_meta),
				number_format($total_deleted)
			)
		);
	}

	/**
	 * Reset migration status
	 * Useful for restarting a failed migration
	 */
	public function reset_migration() {
		delete_option(self::MIGRATION_OPTION);
		delete_option(self::MIGRATION_LOG);
		// Keep backup for potential rollback
	}

	/**
	 * Get migration logs
	 * 
	 * @return array Array of error logs
	 */
	public function get_logs() {
		return get_option(self::MIGRATION_LOG, array());
	}

	/**
	 * Verify migration integrity
	 * Compares record counts between old and new tables
	 * 
	 * @return array Verification result
	 */
	public function verify_migration() {
		$old_count = $this->wpdb->get_var("SELECT COUNT(*) FROM {$this->old_table} WHERE status = 1 AND session_status != 3");
		$new_count = $this->wpdb->get_var("SELECT COUNT(*) FROM {$this->new_table}");
		$meta_count = $this->wpdb->get_var("SELECT COUNT(*) FROM {$this->new_meta_table}");
		
		$backup = get_option(self::MIGRATION_BACKUP, array());
		$mapped_count = count($backup);

		return array(
			'old_table_count' => (int) $old_count,
			'new_table_count' => (int) $new_count,
			'meta_count' => (int) $meta_count,
			'mapped_records' => $mapped_count,
			'is_valid' => $mapped_count === (int) $new_count,
			'message' => $mapped_count === (int) $new_count 
				? 'Migration integrity verified successfully' 
				: 'Warning: Record count mismatch detected'
		);
	}

	/**
	 * Diagnose sections extraction issues
	 * Checks a few writing/speaking records to see why sections aren't being extracted
	 * 
	 * @return array Diagnostic results
	 */
	public function diagnose_sections_extraction() {
		// Get a few writing/speaking records from old table
		$test_records = $this->wpdb->get_results(
			"SELECT ID, quiz_category, quiz 
			FROM {$this->old_table} 
			WHERE (quiz_category = 'ielts-writing' OR quiz_category = 'ielts-speaking')
			AND status = 1 
			AND session_status != 3
			LIMIT 5",
			ARRAY_A
		);

		$results = array();

		foreach ($test_records as $record) {
			$result = array(
				'ID' => $record['ID'],
				'quiz_category' => $record['quiz_category'],
				'quiz_data_exists' => !empty($record['quiz']),
				'quiz_unserialized' => false,
				'sections_exists' => false,
				'sections_is_array' => false,
				'sections_count' => 0,
				'error' => null
			);

			if (!empty($record['quiz'])) {
				$quiz_data = @unserialize($record['quiz']);
				
				if ($quiz_data && is_object($quiz_data)) {
					$result['quiz_unserialized'] = true;
					
					if (isset($quiz_data->sections)) {
						$result['sections_exists'] = true;
						$result['sections_is_array'] = is_array($quiz_data->sections);
						
						if (is_array($quiz_data->sections)) {
							$result['sections_count'] = count($quiz_data->sections);
							
							// Check first section structure
							if (!empty($quiz_data->sections)) {
								$first_section = $quiz_data->sections[0];
								$result['first_section_is_object'] = is_object($first_section);
								
								if (is_object($first_section)) {
									$result['first_section_has_id'] = isset($first_section->_id);
									$result['first_section_has_title'] = isset($first_section->title);
									$result['first_section_has_order'] = isset($first_section->order);
								}
							}
						} else {
							$result['sections_type'] = gettype($quiz_data->sections);
						}
					}
				} else {
					$result['error'] = 'Failed to unserialize quiz data';
				}
			}

			$results[] = $result;
		}

		return array(
			'success' => true,
			'total_checked' => count($results),
			'records' => $results
		);
	}

	/**
	 * Clear all migrated data from new tables
	 * Useful for retrying migration from scratch
	 * 
	 * @return array Clear result
	 */
	public function clear_new_tables() {
		// Get counts before clearing
		$submission_count = $this->wpdb->get_var("SELECT COUNT(*) FROM {$this->new_table}");
		$meta_count = $this->wpdb->get_var("SELECT COUNT(*) FROM {$this->new_meta_table}");
		
		// Truncate new tables (faster than DELETE)
		$this->wpdb->query("TRUNCATE TABLE {$this->new_meta_table}");
		$this->wpdb->query("TRUNCATE TABLE {$this->new_table}");
		$this->wpdb->query("TRUNCATE TABLE {$this->new_review_table}");
		$this->wpdb->query("TRUNCATE TABLE {$this->new_review_meta_table}");
		$this->wpdb->query("TRUNCATE TABLE {$this->new_meta_submissions_table}");

		// Clear all migration-related data from wp_options
		$progress_deleted = delete_option(self::MIGRATION_OPTION);  // Progress/status
		$log_deleted = delete_option(self::MIGRATION_LOG);          // Error logs
		$backup_deleted = delete_option(self::MIGRATION_BACKUP);    // ID mappings

		// Log the action with details
		if (defined('WP_DEBUG') && WP_DEBUG) {
			error_log("ProQyz Migration: CLEAR TABLES EXECUTED");
			error_log("  - Submissions deleted: {$submission_count}");
			error_log("  - Meta entries deleted: {$meta_count}");
			error_log("  - Progress data cleared: " . ($progress_deleted ? 'YES' : 'NO'));
			error_log("  - Error logs cleared: " . ($log_deleted ? 'YES' : 'NO'));
			error_log("  - ID mappings cleared: " . ($backup_deleted ? 'YES' : 'NO'));
			error_log("ProQyz Migration: Ready for fresh migration start");
		}

		return array(
			'success' => true,
			'submissions_deleted' => (int) $submission_count,
			'meta_deleted' => (int) $meta_count,
			'progress_cleared' => $progress_deleted,
			'logs_cleared' => $log_deleted,
			'mappings_cleared' => $backup_deleted,
			'message' => sprintf(
				'✅ All migration data cleared successfully!<br><br>' .
				'<strong>Deleted:</strong><br>' .
				'• %s submission records<br>' .
				'• %s meta entries<br>' .
				'• Migration progress data<br>' .
				'• Migration error logs<br>' .
				'• ID mapping data<br><br>' .
				'<strong>You can now start migration from scratch.</strong>',
				number_format($submission_count),
				number_format($meta_count)
			)
		);
	}
}

