<?php
/**
 * File: Ecre_Migration_Handler.php
 *
 * Handles data migration from old WooCommerce order meta structure
 * to new custom tables for improved performance and analytics.
 *
 * @package ECRE
 * @since   1.0.0
 */

namespace ECRE;

if ( ! class_exists( 'ECRE\Ecre_Migration_Handler' ) ) {
	/**
	 * Handles batch migration of existing referral and reward data
	 * to new custom database tables.
	 *
	 * @since 1.0.0
	 */
	class Ecre_Migration_Handler {

		/**
		 * Batch size for processing orders
		 *
		 * @var int
		 */
		const BATCH_SIZE = 50;

		/**
		 * Maximum execution time per batch (in seconds)
		 *
		 * @var int
		 */
		const MAX_EXECUTION_TIME = 40;

		/**
		 * Migration status option key
		 *
		 * @var string
		 */
		const MIGRATION_STATUS_KEY = 'ecre_migration_status';

		/**
		 * Database instance
		 *
		 * @var Ecre_Database
		 */
		private $database;

		/**
		 * Constructor
		 */
		public function __construct() {
			if ( class_exists( '\ECRE\Ecre_Database' ) ) {
				$this->database = new \ECRE\Ecre_Database();
			}

			add_action( 'wp_ajax_ecre_check_migration_needed', array( $this, 'ajax_check_migration_needed' ) );
			add_action( 'wp_ajax_ecre_start_migration', array( $this, 'ajax_start_migration' ) );
			add_action( 'wp_ajax_ecre_get_migration_progress', array( $this, 'ajax_get_migration_progress' ) );
			add_action( 'wp_ajax_ecre_pause_migration', array( $this, 'ajax_pause_migration' ) );
			add_action( 'wp_ajax_ecre_resume_migration', array( $this, 'ajax_resume_migration' ) );
			add_action( 'wp_ajax_ecre_cancel_migration', array( $this, 'ajax_cancel_migration' ) );
			add_action( 'wp_ajax_ecre_dismiss_migration_notice', array( $this, 'ajax_dismiss_migration_notice' ) );

			// Register background process hook.
			add_action( 'ecre_process_migration_batch', array( $this, 'process_migration_batch' ) );
		}

		/**
		 * Check if WooCommerce is using HPOS
		 */
		private function is_hpos_enabled() {
			return class_exists( 'Automattic\WooCommerce\Utilities\OrderUtil' ) &&
			\Automattic\WooCommerce\Utilities\OrderUtil::custom_orders_table_usage_is_enabled();
		}

		/**
		 * Check if migration is needed
		 */
		public function ajax_check_migration_needed() {
			check_ajax_referer( 'ecre_admin_nonce', 'nonce' );

			// Check if notice has been dismissed.
			$is_dismissed     = get_option( 'ecre_migration_notice_dismissed', false );
			$is_fresh_install = $this->is_fresh_installation();

			global $wpdb;

			// Check if there's old data that needs migration.
			if ( $this->is_hpos_enabled() ) {
				// HPOS queries.
				$old_referral_orders = $wpdb->get_var(
					"SELECT COUNT(*) FROM {$wpdb->postmeta} pm
					INNER JOIN {$wpdb->prefix}wc_orders o ON pm.post_id = o.id
					WHERE pm.meta_key = '_referral_user_id'
					AND o.type = 'shop_order'"
				);

				$old_reward_orders = $wpdb->get_var(
					"SELECT COUNT(*) FROM {$wpdb->postmeta} pm
					INNER JOIN {$wpdb->prefix}wc_orders o ON pm.post_id = o.id
					WHERE pm.meta_key = '_reward_user_id'
					AND o.type = 'shop_order'"
				);
			} else {
				// Legacy queries.
				$old_referral_orders = $wpdb->get_var(
					"SELECT COUNT(*) FROM {$wpdb->postmeta} pm
					INNER JOIN {$wpdb->posts} p ON pm.post_id = p.ID
					WHERE pm.meta_key = '_referral_user_id'
					AND p.post_type = 'shop_order'"
				);

				$old_reward_orders = $wpdb->get_var(
					"SELECT COUNT(*) FROM {$wpdb->postmeta} pm
					INNER JOIN {$wpdb->posts} p ON pm.post_id = p.ID
					WHERE pm.meta_key = '_reward_user_id'
					AND p.post_type = 'shop_order'"
				);
			}

			$old_referral_coupons = $wpdb->get_var(
				"SELECT COUNT(*) FROM {$wpdb->postmeta} pm
				INNER JOIN {$wpdb->posts} p ON pm.post_id = p.ID
				WHERE p.post_type = 'shop_coupon' 
				AND p.post_status != 'trash'
				AND pm.meta_key = 'referral_user_id'"
			);

			$old_reward_coupons = $wpdb->get_var(
				"SELECT COUNT(*) FROM {$wpdb->postmeta} pm
				INNER JOIN {$wpdb->posts} p ON pm.post_id = p.ID
				WHERE p.post_type = 'shop_coupon' 
				AND p.post_status != 'trash'
				AND pm.meta_key = 'reward_user_id'"
			);

			$total_old_data = $old_referral_orders + $old_reward_orders + $old_referral_coupons + $old_reward_coupons;

			wp_send_json_success(
				array(
					'needsMigration'    => $total_old_data > 0 && ! $is_fresh_install,
					'isNoticeDismissed' => $is_dismissed,
					'oldDataCount'   => array(
						'orders'  => $old_referral_orders + $old_reward_orders,
						'coupons' => $old_referral_coupons + $old_reward_coupons,
						'users'   => $this->get_users_with_referral_coupons_count(),
					),
					'hpos_enabled'      => $this->is_hpos_enabled(),
				)
			);
		}

		/**
		 * Start migration process
		 */
		public function ajax_start_migration() {
			check_ajax_referer( 'ecre_admin_nonce', 'nonce' );

			$migration_id = uniqid( 'ecre_migration_', true );

			// Get total items to migrate.
			$total_items = $this->get_total_migration_items();

			// Initialize migration status.
			$migration_status = array(
				'migration_id'           => $migration_id,
				'status'                 => 'running',
				'started_at'             => current_time( 'mysql' ),
				'total_orders'           => $total_items['orders'],
				'processed_orders'       => 0,
				'migrated_referrals'     => 0,
				'migrated_rewards'       => 0,
				'migrated_coupons'       => 0,
				'migrated_referrers'     => 0,
				'current_step'           => 'Starting migration...',
				'errors'                 => array(),
				'last_referral_order_id' => 0,
				'last_reward_order_id'   => 0,
				'batch_number'           => 0,
				'hpos_enabled'           => $this->is_hpos_enabled(),
				'referrals_completed'    => false,
				'rewards_completed'      => false,
			);

			$update_result = update_option( self::MIGRATION_STATUS_KEY . '_' . $migration_id, $migration_status );
			$this->process_migration_batch( $migration_id );

			// Schedule first batch for background processing.
			$scheduled = wp_schedule_single_event( time() + 5, 'ecre_process_migration_batch', array( $migration_id ) );

			// Also check if WP Cron is working.
			$next_cron = wp_next_scheduled( 'ecre_process_migration_batch', array( $migration_id ) );

			wp_send_json_success(
				array(
					'migrationId'  => $migration_id,
					'totalItems'   => $total_items['orders'],
					'scheduled'    => $scheduled,
					'next_cron'    => $next_cron,
					'hpos_enabled' => $this->is_hpos_enabled(),
				)
			);
		}

		/**
		 * Process a batch of migration data
		 *
		 * @param string $migration_id Migration ID.
		 */
		public function process_migration_batch( $migration_id ) {
			$migration_status = get_option( self::MIGRATION_STATUS_KEY . '_' . $migration_id );

			if ( ! $migration_status ) {
				return;
			}

			if ( 'running' !== $migration_status['status'] ) {
				return;
			}

			$start_time   = time();
			$batch_errors = array();

			try {
				// Update current step.
				$migration_status['current_step'] = 'Processing batch ' . ( $migration_status['batch_number'] + 1 );
				$migration_status['batch_number']++;

				$update_result = update_option( self::MIGRATION_STATUS_KEY . '_' . $migration_id, $migration_status );

				// FIRST: Process referrers (only in first batch) - THIS SHOULD BE FIRST.
				if ( 1 === $migration_status['batch_number'] ) {
					$referrer_results = $this->process_referrers();
					if ( $referrer_results ) {
						$migration_status['migrated_referrers'] += $referrer_results['migrated'];
						$batch_errors                            = array_merge( $batch_errors, $referrer_results['errors'] );
					}
				}

				// SECOND: Process referral coupons (only in first batch).
				if ( 1 === $migration_status['batch_number'] ) {
					$coupon_results = $this->process_referral_coupons();
					if ( $coupon_results ) {
						$migration_status['migrated_coupons'] += $coupon_results['migrated'];
						$batch_errors                          = array_merge( $batch_errors, $coupon_results['errors'] );
					}
				}

				// Process orders with referral data (only if not completed).
				if ( ! $migration_status['referrals_completed'] ) {
					$referral_results = $this->process_referral_orders_batch( $migration_status['last_referral_order_id'] );

					if ( $referral_results ) {
						$migration_status['processed_orders']      += $referral_results['processed'];
						$migration_status['migrated_referrals']    += $referral_results['migrated'];
						$migration_status['last_referral_order_id'] = $referral_results['last_order_id'];
						$batch_errors                               = array_merge( $batch_errors, $referral_results['errors'] );

						// Mark referrals as completed if no more found.
						if ( 0 === $referral_results['processed'] ) {
							$migration_status['referrals_completed'] = true;
						}
					}
				}

				// Process orders with reward data (only if not completed).
				if ( ! $migration_status['rewards_completed'] ) {
					$reward_results = $this->process_reward_orders_batch( $migration_status['last_reward_order_id'] );

					if ( $reward_results ) {
						$migration_status['processed_orders']    += $reward_results['processed'];
						$migration_status['migrated_rewards']    += $reward_results['migrated'];
						$migration_status['last_reward_order_id'] = $reward_results['last_order_id'];
						$batch_errors                             = array_merge( $batch_errors, $reward_results['errors'] );

						// Mark rewards as completed if no more found.
						if ( 0 === $reward_results['processed'] ) {
							$migration_status['rewards_completed'] = true;
						}
					}
				}

				// Update migration status.
				$migration_status['errors'] = array_merge( $migration_status['errors'], $batch_errors );

				// Check if migration is complete - both referrals and rewards must be completed.
				if ( $migration_status['referrals_completed'] && $migration_status['rewards_completed'] ) {
					$migration_status['status']       = 'completed';
					$migration_status['completed_at'] = current_time( 'mysql' );
					$migration_status['current_step'] = 'Migration completed successfully!';
					$migration_status['progress']     = 100;
				} else {
					// Schedule next batch if there's still data to process.
					$execution_time = time() - $start_time;

					if ( $execution_time < self::MAX_EXECUTION_TIME ) {
						$next_schedule = wp_schedule_single_event( time() + 2, 'ecre_process_migration_batch', array( $migration_id ) );
					} else {
						$next_schedule = wp_schedule_single_event( time() + 30, 'ecre_process_migration_batch', array( $migration_id ) );
					}
				}

				$final_update = update_option( self::MIGRATION_STATUS_KEY . '_' . $migration_id, $migration_status );

			} catch ( Exception $e ) {
				$migration_status['errors'][] = 'Batch processing error: ' . $e->getMessage();
				$migration_status['status'] = 'error';
				update_option( self::MIGRATION_STATUS_KEY . '_' . $migration_id, $migration_status );
			}

			// Memory cleanup every 10 batches.
			if ( 0 === $migration_status['batch_number'] % 10 ) {
				wp_cache_flush();
				if ( function_exists( 'gc_collect_cycles' ) ) {
					gc_collect_cycles();
				}
			}
		}

		/**
		 * Retrieves the correct query for fetching orders depending on whether HPOS or legacy order storage is used.
		 *
		 * This method dynamically adjusts the query logic to support both High-Performance Order Storage (HPOS)
		 * and the legacy posts-based order storage. It ensures compatibility with different WooCommerce
		 * order storage systems when fetching orders by meta key.
		 *
		 * @param string $meta_key      The meta key used to filter the orders.
		 * @param int    $last_order_id The ID of the last processed order, used for pagination or batching.
		 * @param int    $limit         The maximum number of orders to retrieve.
		 *
		 * @return WP_Query|array The query object or array of orders depending on the storage method.
		 */
		private function get_orders_query( $meta_key, $last_order_id, $limit ) {
			global $wpdb;

			if ( $this->is_hpos_enabled() ) {
				// HPOS enabled - use wp_wc_orders table.
				return $wpdb->prepare(
					"SELECT DISTINCT pm.post_id as order_id, pm.meta_value as user_id
					FROM {$wpdb->postmeta} pm
					INNER JOIN {$wpdb->prefix}wc_orders o ON pm.post_id = o.id
					WHERE pm.meta_key = %s
					AND o.type = 'shop_order'
					AND pm.post_id > %d
					ORDER BY pm.post_id ASC
					LIMIT %d",
					$meta_key,
					$last_order_id,
					$limit
				);
			} else {
				// Legacy - use wp_posts table.
				return $wpdb->prepare(
					"SELECT DISTINCT pm.post_id as order_id, pm.meta_value as user_id
					FROM {$wpdb->postmeta} pm
					INNER JOIN {$wpdb->posts} p ON pm.post_id = p.ID
					WHERE pm.meta_key = %s
					AND p.post_type = 'shop_order'
					AND pm.post_id > %d
					ORDER BY pm.post_id ASC
					LIMIT %d",
					$meta_key,
					$last_order_id,
					$limit
				);
			}
		}

		/**
		 * Process referral orders batch
		 *
		 * @param int $last_order_id Last processed order ID.
		 * @return array Results of batch processing
		 */
		private function process_referral_orders_batch( $last_order_id ) {
			global $wpdb;

			$results = array(
				'processed'     => 0,
				'migrated'      => 0,
				'last_order_id' => $last_order_id,
				'errors'        => array(),
			);

			// Check if database class exists.
			if ( ! $this->database ) {
				$results['errors'][] = 'Database class not available';
				return $results;
			}

			// Get orders with referral data using HPOS-aware query.
			$query  = $this->get_orders_query( '_referral_user_id', $last_order_id, self::BATCH_SIZE );
			// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- The query is already properly prepared in the get_orders_query() method, so no further preparation is necessary.
			$orders = $wpdb->get_results( $query );

			foreach ( $orders as $order_data ) {
				try {
					$order_id = $order_data->order_id;
					$referral_user_id = $order_data->user_id;

					// Check if already migrated.
					$existing = $wpdb->get_var(
						$wpdb->prepare(
							"SELECT id FROM {$wpdb->prefix}ecre_referrals_orders 
							WHERE order_id = %d AND referrer_user_id = %d",
							$order_id,
							$referral_user_id
						)
					);

					if ( $existing ) {
						$results['processed']++;
						$results['last_order_id'] = $order_id;
						continue;
					}

					$order = wc_get_order( $order_id );
					if ( ! $order ) {
						$error               = "Order not found: {$order_id}";
						$results['errors'][] = $error;
						$results['processed']++;
						$results['last_order_id'] = $order_id;
						continue;
					}

					if ( empty( $referral_user_id ) ) {
						$results['processed']++;
						$results['last_order_id'] = $order_id;
						continue;
					}

					// FIXED: Get the actual coupon code and ID that were used in this order.
					$coupon_code = get_post_meta( $order_id, '_referral_coupon_code', true );
					$coupon_id   = get_post_meta( $order_id, '_referral_coupon_id', true );

					// If coupon code not found in order meta, try to find it from applied coupons.
					if ( empty( $coupon_code ) ) {
						$applied_coupons = $order->get_coupon_codes();
						foreach ( $applied_coupons as $applied_coupon_code ) {
							$applied_coupon_id       = wc_get_coupon_id_by_code( $applied_coupon_code );
							$coupon_referral_user_id = get_post_meta( $applied_coupon_id, 'referral_user_id', true );

							if ( $coupon_referral_user_id == $referral_user_id ) {
								$coupon_code = $applied_coupon_code;
								$coupon_id = $applied_coupon_id;
								break;
							}
						}
					}

					// Store referral data using database methods.
					if ( method_exists( $this->database, 'store_referral_order_data' ) ) {
						$this->database->store_referral_order_data( $order, $coupon_code, $coupon_id, $referral_user_id );
					}

					// Update referrer stats if methods exist.
					if ( method_exists( $this->database, 'update_referrer_stats_on_order' ) ) {
						$this->database->update_referrer_stats_on_order( $order, $coupon_code, $coupon_id, $referral_user_id );
					}

					if ( method_exists( $this->database, 'update_dashboard_summary_stats_on_order' ) ) {
						$this->database->update_dashboard_summary_stats_on_order( $order, $coupon_code, $coupon_id, $referral_user_id );
					}

					// Update order status data if exists.
					$order_status = $order->get_status();
					if ( method_exists( $this->database, 'update_referral_status' ) ) {
						$this->database->update_referral_status( $order_id, $order_status );
					}

					// Check if reward was generated for this order.
					$coupon_generated       = get_post_meta( $order_id, 'ecre_reward_coupon_generated', true );
					$reward_point_generated = get_post_meta( $order_id, 'ecre_reward_point_generated', true );

					if ( ! empty( $coupon_generated ) || ! empty( $reward_point_generated ) ) {
						if ( method_exists( $this->database, 'update_referrer_rewards_on_completion' ) ) {
							$this->database->update_referrer_rewards_on_completion( $referral_user_id );
						}
						if ( method_exists( $this->database, 'update_dashboard_summary_rewards_on_completion' ) ) {
							$this->database->update_dashboard_summary_rewards_on_completion( $referral_user_id );
						}
					}

					$results['migrated']++;
					$results['processed']++;
					$results['last_order_id'] = $order_id;

				} catch ( Exception $e ) {
					$error               = "Error processing referral order {$order_id}: " . $e->getMessage();
					$results['errors'][] = $error;
					$results['processed']++;
					$results['last_order_id'] = $order_id;
				}
			}
			return $results;
		}

		/**
		 * Process reward orders batch
		 *
		 * @param int $last_order_id Last processed order ID.
		 * @return array Results of batch processing
		 */
		private function process_reward_orders_batch( $last_order_id ) {
			global $wpdb;

			$results = array(
				'processed'     => 0,
				'migrated'      => 0,
				'last_order_id' => $last_order_id,
				'errors'        => array(),
			);

			// Check if database class exists.
			if ( ! $this->database ) {
				$results['errors'][] = 'Database class not available';
				return $results;
			}

			// Get orders with reward data using HPOS-aware query.
			$query = $this->get_orders_query( '_reward_user_id', $last_order_id, self::BATCH_SIZE );
			// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- The query is already properly prepared in the get_orders_query() method, so no further preparation is necessary.
			$orders = $wpdb->get_results( $query );

			foreach ( $orders as $order_data ) {
				try {
					$order_id       = $order_data->order_id;
					$reward_user_id = $order_data->user_id;

					// Check if already migrated.
					$existing = $wpdb->get_var(
						$wpdb->prepare(
							"SELECT id FROM {$wpdb->prefix}ecre_rewards_orders 
							WHERE order_id = %d",
							$order_id
						)
					);

					if ( $existing ) {
						$results['processed']++;
						$results['last_order_id'] = $order_id;
						continue;
					}

					$order = wc_get_order( $order_id );
					if ( ! $order ) {
						$error               = "Order not found: {$order_id}";
						$results['errors'][] = $error;
						$results['processed']++;
						$results['last_order_id'] = $order_id;
						continue;
					}

					if ( empty( $reward_user_id ) ) {
						$results['processed']++;
						$results['last_order_id'] = $order_id;
						continue;
					}

					// FIXED: Get the actual reward coupon code and ID that were used in this order.
					$coupon_code = get_post_meta( $order_id, '_reward_coupon_code', true );
					$coupon_id   = get_post_meta( $order_id, '_reward_coupon_id', true );

					// If coupon code not found in order meta, try to find it from applied coupons.
					if ( empty( $coupon_code ) ) {
						$applied_coupons = $order->get_coupon_codes();
						foreach ( $applied_coupons as $applied_coupon_code ) {
							$applied_coupon_id     = wc_get_coupon_id_by_code( $applied_coupon_code );
							$coupon_reward_user_id = get_post_meta( $applied_coupon_id, 'reward_user_id', true );

							if ( $coupon_reward_user_id == $reward_user_id ) {
								$coupon_code = $applied_coupon_code;
								$coupon_id   = $applied_coupon_id;
								break;
							}
						}
					}

					// Get applied referral data.
					$applied_referral_user_id     = get_post_meta( $coupon_id, 'ecre_applied_referral_user_id', true );
					$applied_referral_coupon_code = get_post_meta( $coupon_id, 'ecre_applied_referral_coupon_code', true );
					$applied_referral_coupon_id   = get_post_meta( $coupon_id, 'ecre_applied_referral_coupon_id', true );

					// Store reward data in new table.
					if ( method_exists( $this->database, 'store_reward_order_data' ) ) {
						$this->database->store_reward_order_data(
							$order,
							$coupon_code,
							$coupon_id,
							$reward_user_id,
							$applied_referral_coupon_code,
							$applied_referral_coupon_id,
							$applied_referral_user_id
						);
					}

					$results['migrated']++;
					$results['processed']++;
					$results['last_order_id'] = $order_id;

				} catch ( Exception $e ) {
					$error               = "Error processing reward order {$order_id}: " . $e->getMessage();
					$results['errors'][] = $error;
					$results['processed']++;
					$results['last_order_id'] = $order_id;
				}
			}

			return $results;
		}

		/**
		 * Get total migration items count
		 */
		private function get_total_migration_items() {
			global $wpdb;

			if ( $this->is_hpos_enabled() ) {
				$referral_orders = $wpdb->get_var(
					"SELECT COUNT(*) FROM {$wpdb->postmeta} pm
					INNER JOIN {$wpdb->prefix}wc_orders o ON pm.post_id = o.id
					WHERE pm.meta_key = '_referral_user_id'
					AND o.type = 'shop_order'"
				);

				$reward_orders = $wpdb->get_var(
					"SELECT COUNT(*) FROM {$wpdb->postmeta} pm
					INNER JOIN {$wpdb->prefix}wc_orders o ON pm.post_id = o.id
					WHERE pm.meta_key = '_reward_user_id'
					AND o.type = 'shop_order'"
				);
			} else {
				$referral_orders = $wpdb->get_var(
					"SELECT COUNT(*) FROM {$wpdb->postmeta} pm
					INNER JOIN {$wpdb->posts} p ON pm.post_id = p.ID
					WHERE pm.meta_key = '_referral_user_id'
					AND p.post_type = 'shop_order'"
				);

				$reward_orders = $wpdb->get_var(
					"SELECT COUNT(*) FROM {$wpdb->postmeta} pm
					INNER JOIN {$wpdb->posts} p ON pm.post_id = p.ID
					WHERE pm.meta_key = '_reward_user_id'
					AND p.post_type = 'shop_order'"
				);
			}

			return array(
				'orders' => $referral_orders + $reward_orders,
			);
		}

		/**
		 * Get users with referral coupons count.
		 */
		private function get_users_with_referral_coupons_count() {
			global $wpdb;

			return $wpdb->get_var(
				"SELECT COUNT(DISTINCT pm.meta_value) FROM {$wpdb->postmeta} pm
				INNER JOIN {$wpdb->posts} p ON pm.post_id = p.ID
				WHERE p.post_type = 'shop_coupon' 
				AND p.post_status != 'trash'
				AND pm.meta_key = 'referral_user_id'"
			);
		}

		/**
		 * Process referral coupons - FIXED VERSION
		 *
		 * @return array Results of processing
		 */
		private function process_referral_coupons() {
			global $wpdb;

			$results = array(
				'migrated' => 0,
				'errors'   => array(),
			);

			// Check if database class exists.
			if ( ! $this->database ) {
				$results['errors'][] = 'Database class not available';
				return $results;
			}

			// Get reward coupons that need migration.
			$coupons = $wpdb->get_results(
				"SELECT p.ID as coupon_id, p.post_title as coupon_code, pm.meta_value as reward_user_id
				FROM {$wpdb->posts} p
				INNER JOIN {$wpdb->postmeta} pm ON p.ID = pm.post_id
				WHERE p.post_type = 'shop_coupon'
				AND p.post_status = 'publish'
				AND pm.meta_key = 'reward_user_id'"
			);

			foreach ( $coupons as $coupon_data ) {
				try {
					$coupon_id      = $coupon_data->coupon_id;
					$coupon_code    = $coupon_data->coupon_code;
					$reward_user_id = $coupon_data->reward_user_id;

					// Check if already migrated.
					$existing = $wpdb->get_var(
						$wpdb->prepare(
							"SELECT id FROM {$wpdb->prefix}ecre_reward_coupons 
							WHERE coupon_id = %d",
							$coupon_id
						)
					);

					if ( $existing ) {
						continue;
					}

					// Get coupon object for additional data.
					$coupon = new \WC_Coupon( $coupon_id );
					if ( ! $coupon->get_id() ) {
						$error               = "Coupon not found: {$coupon_id}";
						$results['errors'][] = $error;
						continue;
					}

					// Get additional coupon metadata.
					$discount_type   = $coupon->get_discount_type();
					$discount_amount = $coupon->get_amount();
					$usage_limit     = $coupon->get_usage_limit();
					$usage_count     = $coupon->get_usage_count();
					$expires         = $coupon->get_date_expires();
					$product_ids     = $coupon->get_product_ids();
					$category_ids    = $coupon->get_product_categories();

					// Get order ID if available - FIXED: Default to 0 instead of NULL.
					$order_id = get_post_meta( $coupon_id, 'ecre_order_id', true );
					$order_id = $order_id ? intval( $order_id ) : 0;

					// Prepare coupon data for storage.
					$coupon_data_array = array(
						'referrer_user_id'      => $reward_user_id,
						'order_id'              => $order_id,
						'coupon_code'           => $coupon_code,
						'coupon_id'             => $coupon_id,
						'discount_type'         => $discount_type,
						'discount_amount'       => $discount_amount,
						'usage_limit'                => $usage_limit ? $usage_limit : 0,
						'usage_count'                => $usage_count,
						'status'                     => $coupon->get_status() === 'publish' ? 'active' : 'inactive',
						'expires_at'                 => $expires ? $expires->date( 'Y-m-d H:i:s' ) : null,
						'include_products'           => ! empty( $product_ids ) ? $product_ids : null,
						'exclude_products'           => null,
						'include_product_categories' => ! empty( $category_ids ) ? $category_ids : null,
						'exclude_product_categories' => null,
					);

					// Store coupon data using database methods.
					if ( method_exists( $this->database, 'store_reward_coupon_data' ) ) {
						$this->database->store_reward_coupon_data( $coupon_data_array );
						$results['migrated']++;
					}
				} catch ( Exception $e ) {
					$error               = "Error processing reward coupon {$coupon_id}: " . $e->getMessage();
					$results['errors'][] = $error;
				}
			}

			return $results;
		}

		/**
		 * Process referrers - FIXED VERSION
		 *
		 * @return array Results of processing
		 */
		private function process_referrers() {
			global $wpdb;

			$results = array(
				'migrated' => 0,
				'errors'   => array(),
			);

			// Check if database class exists.
			if ( ! $this->database ) {
				$results['errors'][] = 'Database class not available';
				return $results;
			}

			// Get unique users with referral coupons, excluding trash.
			$users = $wpdb->get_results(
				"SELECT pm.meta_value as user_id, 
						p.post_title as coupon_code, 
						p.ID as coupon_id
				FROM {$wpdb->postmeta} pm
				INNER JOIN {$wpdb->posts} p ON pm.post_id = p.ID
				WHERE pm.meta_key = 'referral_user_id'
				AND p.post_type = 'shop_coupon'
				AND p.post_status != 'trash'
				GROUP BY pm.meta_value
				ORDER BY pm.meta_value"
			);

			foreach ( $users as $user_data ) {
				try {
					$user_id     = $user_data->user_id;
					$coupon_code = $user_data->coupon_code;
					$coupon_id   = $user_data->coupon_id;

					// Better duplicate checking - check if referrer table is empty first.
					$referrer_table_count = $wpdb->get_var( "SELECT COUNT(*) FROM {$wpdb->prefix}ecre_referrers" );

					if ( 0 === (int) $referrer_table_count ) {
						$existing = false;
					} else {
						$existing = $wpdb->get_var(
							$wpdb->prepare(
								"SELECT id FROM {$wpdb->prefix}ecre_referrers 
								WHERE user_id = %d",
								$user_id
							)
						);
					}

					if ( $existing ) {
						continue;
					}

					// FIXED: Use correct method names from old code.
					if ( method_exists( $this->database, 'update_dashboard_summary_referrers_on_creation' ) ) {
						$this->database->update_dashboard_summary_referrers_on_creation( $user_id, 'increment' );
					}

					if ( method_exists( $this->database, 'store_referrer' ) ) {
						$store_result = $this->database->store_referrer( $user_id, $coupon_code, $coupon_id );
						if ( $store_result ) {
							$results['migrated']++;
						}
					}
				} catch ( Exception $e ) {
					$error               = "Error processing referrer {$user_id}: " . $e->getMessage();
					$results['errors'][] = $error;
				}
			}
			return $results;
		}

		/**
		 * Get migration progress
		 */
		public function ajax_get_migration_progress() {
			check_ajax_referer( 'ecre_admin_nonce', 'nonce' );

			// Fix the undefined index error.
			if ( ! isset( $_POST['migration_id'] ) ) {
				wp_send_json_error( 'Migration ID not provided' );
			}

			$migration_id = sanitize_text_field( wp_unslash( $_POST['migration_id'] ) );

			$migration_status = get_option( self::MIGRATION_STATUS_KEY . '_' . $migration_id );

			if ( ! $migration_status ) {
				wp_send_json_error( 'Migration not found' );
			}

			// Calculate progress percentage.
			$progress = 0;
			if ( $migration_status['total_orders'] > 0 ) {
				$progress = ( $migration_status['processed_orders'] / $migration_status['total_orders'] ) * 100;
			} elseif ( 'completed' === $migration_status['status'] ) {
				$progress = 100;
			}

			$migration_status['progress'] = min( 100, max( 0, $progress ) );

			wp_send_json_success( $migration_status );
		}

		/**
		 * Pause migration
		 */
		public function ajax_pause_migration() {
			check_ajax_referer( 'ecre_admin_nonce', 'nonce' );

			if ( ! isset( $_POST['migration_id'] ) ) {
				wp_send_json_error( 'Migration ID not provided' );
			}

			$migration_id     = sanitize_text_field( wp_unslash( $_POST['migration_id'] ) );
			$migration_status = get_option( self::MIGRATION_STATUS_KEY . '_' . $migration_id );

			if ( ! $migration_status ) {
				wp_send_json_error( 'Migration not found' );
			}

			$migration_status['status']       = 'paused';
			$migration_status['current_step'] = 'Migration paused by user';

			update_option( self::MIGRATION_STATUS_KEY . '_' . $migration_id, $migration_status );

			// Clear any scheduled events.
			wp_clear_scheduled_hook( 'ecre_process_migration_batch', array( $migration_id ) );

			wp_send_json_success( array( 'status' => 'paused' ) );
		}

		/**
		 * Resume migration
		 */
		public function ajax_resume_migration() {
			check_ajax_referer( 'ecre_admin_nonce', 'nonce' );

			if ( ! isset( $_POST['migration_id'] ) ) {
				wp_send_json_error( 'Migration ID not provided' );
			}

			$migration_id     = sanitize_text_field( wp_unslash( $_POST['migration_id'] ) );
			$migration_status = get_option( self::MIGRATION_STATUS_KEY . '_' . $migration_id );

			if ( ! $migration_status ) {
				wp_send_json_error( 'Migration not found' );
			}

			$migration_status['status']       = 'running';
			$migration_status['current_step'] = 'Resuming migration...';

			update_option( self::MIGRATION_STATUS_KEY . '_' . $migration_id, $migration_status );

			// Schedule next batch.
			wp_schedule_single_event( time() + 2, 'ecre_process_migration_batch', array( $migration_id ) );

			wp_send_json_success( array( 'status' => 'running' ) );
		}

		/**
		 * Cancel migration
		 */
		public function ajax_cancel_migration() {
			check_ajax_referer( 'ecre_admin_nonce', 'nonce' );

			if ( ! isset( $_POST['migration_id'] ) ) {
				wp_send_json_error( 'Migration ID not provided' );
			}

			$migration_id     = sanitize_text_field( wp_unslash( $_POST['migration_id'] ) );
			$migration_status = get_option( self::MIGRATION_STATUS_KEY . '_' . $migration_id );

			if ( ! $migration_status ) {
				wp_send_json_error( 'Migration not found' );
			}

			$migration_status['status']       = 'cancelled';
			$migration_status['current_step'] = 'Migration cancelled by user';
			$migration_status['completed_at'] = current_time( 'mysql' );

			update_option( self::MIGRATION_STATUS_KEY . '_' . $migration_id, $migration_status );

			// Clear any scheduled events.
			wp_clear_scheduled_hook( 'ecre_process_migration_batch', array( $migration_id ) );

			wp_send_json_success( array( 'status' => 'cancelled' ) );
		}

		/**
		 * Clear migration data for fresh start
		 */
		public function ajax_clear_migration_data() {
			check_ajax_referer( 'ecre_admin_nonce', 'nonce' );

			global $wpdb;

			// Clear all migration tables.
			$wpdb->query( "TRUNCATE TABLE {$wpdb->prefix}ecre_referrers" );
			$wpdb->query( "TRUNCATE TABLE {$wpdb->prefix}ecre_reward_coupons" );
			wp_send_json_success( 'Migration data cleared' );
		}

		/**
		 * Dismiss migration notice
		 */
		public function ajax_dismiss_migration_notice() {
			check_ajax_referer( 'ecre_admin_nonce', 'nonce' );
			// Set the option to hide migration notice permanently.
			update_option( 'ecre_migration_notice_dismissed', true );
			wp_send_json_success(
				array(
					'message' => 'Migration notice dismissed successfully',
				)
			);
		}

		/**
		 * Check if this is a fresh installation
		 */
		private function is_fresh_installation() {
			// Get the version when plugin was first installed.
			$first_install_version = get_option( 'ecre_first_install_version', false );

			// If no first install version recorded, this could be an old installation.
			if ( ! $first_install_version ) {
				return false;
			}
			// Define the version where migration feature was introduced.
			$migration_feature_version = '2.6.0';

			// If first install was after migration feature, it's fresh.
			return version_compare( $first_install_version, $migration_feature_version, '>=' );
		}
	}
}
