<?php
/**
 * Migration Handler for Extra Product Options
 *
 * Handles migration from old flat structure to new hierarchical structure,
 * and between post_meta and custom tables.
 *
 * OLD Structure (flat):
 *   general[field_id] = field_data (fields directly in general array)
 *
 * NEW Structure (hierarchical):
 *   general[section_id][row_groups][group_id][columns_groups][columns_id][rows][row_id][fields][field_id] = field_data
 *
 * @package Extra_Product_Options_For_WooCommerce
 * @since   4.4.0
 */

// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared
// phpcs:disable WordPress.DB.PreparedSQL.NotPrepared
// Table names in this file are constructed from $wpdb->prefix which is safe.

if ( ! defined( 'ABSPATH' ) ) {
	exit; // Exit if accessed directly.
}

/**
 * EPOFW Migration Handler
 */
class EPOFW_Migration {

	/**
	 * Check if addon data is in old flat structure
	 *
	 * Old structure: general[field_id] = array with 'field' key containing 'type'
	 * New structure: general[section_id] = array with 'section_id', 'row_groups' keys
	 *
	 * @since 4.4.0
	 * @param array $addon_data Addon data to check.
	 * @return bool True if old flat structure, false if new hierarchical.
	 */
	public static function is_old_flat_structure( $addon_data ) {
		if ( empty( $addon_data['general'] ) || ! is_array( $addon_data['general'] ) ) {
			return false;
		}

		// Check first item in general array.
		$first_item = reset( $addon_data['general'] );

		if ( ! is_array( $first_item ) ) {
			return false;
		}

		// Old structure has 'field' with 'type' directly.
		// New structure has 'section_id', 'section_title', 'row_groups'.
		if ( isset( $first_item['field'] ) && isset( $first_item['field']['type'] ) ) {
			return true;
		}

		// Also check if it has section markers (new structure).
		if ( isset( $first_item['section_id'] ) || isset( $first_item['row_groups'] ) ) {
			return false;
		}

		// If has 'field' key at top level, it's old structure.
		if ( isset( $first_item['field'] ) ) {
			return true;
		}

		return false;
	}

	/**
	 * Transform old flat structure to new hierarchical structure
	 *
	 * Converts:
	 *   general[field_id] = field_data
	 * To:
	 *   general[section_id][row_groups][group_id][columns_groups][columns_id][rows][row_id][fields][field_id] = field_data
	 *
	 * Structure: One Section → Multiple Row Groups (one per field) → One Columns Group → One Row → One Field
	 *
	 * Each field gets its own row_group for independent layout control.
	 *
	 * @since 4.4.0
	 * @param array $addon_data Old addon data with flat structure.
	 * @return array New addon data with hierarchical structure.
	 */
	public static function transform_to_new_structure( $addon_data ) {
		if ( empty( $addon_data['general'] ) || ! is_array( $addon_data['general'] ) ) {
			return $addon_data;
		}

		$base_time  = time();
		$section_id = 'migrated_section_' . $base_time;

		// Build row_groups - each field gets its own row_group with one row and one field.
		$row_groups  = array();
		$field_order = 1;

		foreach ( $addon_data['general'] as $field_id => $field_data ) {
			if ( ! is_array( $field_data ) ) {
				continue;
			}

			// Skip if this looks like a section (already new structure).
			if ( isset( $field_data['section_id'] ) || isset( $field_data['row_groups'] ) ) {
				continue;
			}

			// Generate unique group_id and row_id for this field.
			$group_id = 'migrated_group_' . $base_time . '_' . $field_order;
			$row_id   = 'migrated_row_' . $base_time . '_' . $field_order;

			// Add field_id if not present.
			if ( ! isset( $field_data['field_id'] ) ) {
				$field_data['field_id'] = $field_id;
			}

			// Add field_order for proper ordering.
			if ( ! isset( $field_data['field_order'] ) ) {
				$field_data['field_order'] = 1; // Each field is first (and only) in its row.
			}

			// Generate columns_id for new columns_groups structure.
			$columns_id = 'columns_' . $base_time . '_' . $field_order;

			// Add row_association for new structure.
			$field_data['row_association'] = array(
				'section_id'    => $section_id,
				'group_id'      => $group_id,
				'columns_id'    => $columns_id,
				'columns_order' => 0,
				'row_id'        => $row_id,
				'layout'        => 'single',
				'columns'       => 1,
			);

			// Transform options format for fields with options (select, radio, checkbox, etc.).
			$field_data = self::transform_field_options( $field_data );

			// Create row_group for this field.
			// Structure: row_group → columns_groups → rows → fields.
			$row_groups[ $group_id ] = array(
				'row_group_settings' => array(
					'layout'  => 'single',
					'columns' => 1,
				),
				'columns_groups'     => array(
					$columns_id => array(
						'columns_id'    => $columns_id,
						'columns_order' => 0,
						'layout_type'   => 'single',
						'columns_count' => 1,
						'rows'          => array(
							$row_id => array(
								'row_number'      => 1,
								'column_settings' => array(),
								'fields'          => array(
									$field_id => $field_data,
								),
							),
						),
					),
				),
			);

			++$field_order;
		}

		// If no fields found, return original data.
		if ( empty( $row_groups ) ) {
			return $addon_data;
		}

		// Build new hierarchical structure.
		// One Section → Multiple Row Groups (each with one row and one field).
		$new_general = array(
			$section_id => array(
				'section_id'       => $section_id,
				'section_title'    => __( 'Options', 'extra-product-options-for-woocommerce' ),
				'section_order'    => 1,
				'section_status'   => 'on',
				'section_settings' => array(
					// Migrated addons: show_label OFF by default (no section title/styling on frontend).
					// Admin can enable it later in section settings if needed.
					'is_migrated'         => true,
					'show_label'          => '',      // OFF - section label not visible on frontend.
					'collapsible'         => '',
					'collapsed'           => '',
					'section_class'       => '',
					'section_description' => '',
				),
				'row_groups'       => $row_groups,
			),
		);

		// Replace general with new structure.
		$addon_data['general'] = $new_general;

		// Add migration flag to addon data.
		$addon_data['is_migrated_from_old'] = true;
		$addon_data['migration_date']       = current_time( 'mysql' );

		return $addon_data;
	}

	/**
	 * Transform field options from old format to new format
	 *
	 * Old format (associative array):
	 *   'options' => array(
	 *       'Label 1' => array('Label 1', 'fixed', '10'),
	 *       'Label 2' => array('Label 2', 'fixed', '20'),
	 *   )
	 *
	 * New format (indexed array of objects):
	 *   'options' => array(
	 *       array('label' => 'Label 1', 'price_type' => 'fixed', 'price' => '10'),
	 *       array('label' => 'Label 2', 'price_type' => 'fixed', 'price' => '20'),
	 *   )
	 *
	 * @since 4.4.0
	 * @param array $field_data Field data array.
	 * @return array Field data with transformed options.
	 */
	public static function transform_field_options( $field_data ) {
		// Check if field has options in epofw_field_settings.
		if ( empty( $field_data['epofw_field_settings']['options'] ) ) {
			return $field_data;
		}

		$old_options = $field_data['epofw_field_settings']['options'];

		// Check if already in new format (indexed array with 'label' key).
		if ( isset( $old_options[0] ) && is_array( $old_options[0] ) && isset( $old_options[0]['label'] ) ) {
			return $field_data; // Already new format.
		}

		$new_options = array();

		foreach ( $old_options as $key => $option_data ) {
			if ( is_string( $option_data ) && strpos( $option_data, '||' ) !== false ) {
				// Pipe-delimited string formats:
				// - Non-switcher: "Label||fixed||10||opt_id" (4 parts)
				// - Switcher: "Label||fixed||10||switcher_value||opt_id" (5 parts)
				// - Legacy: "Label||fixed||10" or "Label||fixed||10||switcher" (3-4 parts without opt_id).
				$parts      = explode( '||', $option_data );
				$new_option = array(
					'label'      => isset( $parts[0] ) ? $parts[0] : $key,
					'price_type' => isset( $parts[1] ) ? $parts[1] : 'fixed',
					'price'      => isset( $parts[2] ) ? $parts[2] : '0',
				);

				// Handle parts[3] - could be opt_id, switcher value, or legacy format.
				if ( isset( $parts[3] ) && ! empty( $parts[3] ) ) {
					// Check if parts[3] is an opt_id (starts with 'opt_').
					if ( strpos( $parts[3], 'opt_' ) === 0 ) {
						// Non-switcher format: parts[3] is opt_id.
						$new_option['id'] = $parts[3];
						if ( isset( $parts[4] ) ) {
							$new_option['stock'] = $parts[4];
						}
					} else {
						// Switcher format: parts[3] is switcher value.
						$new_option['swticher'] = $parts[3];
						// Check if parts[4] is opt_id.
						if ( isset( $parts[4] ) && ! empty( $parts[4] ) && strpos( $parts[4], 'opt_' ) === 0 ) {
							$new_option['id'] = $parts[4];
							if ( isset( $parts[5] ) ) {
								$new_option['stock'] = $parts[5];
							}
						}
					}
				}

				$new_options[] = $new_option;
			} elseif ( is_array( $option_data ) ) {
				$new_option = array(
					'label'      => isset( $option_data[0] ) ? $option_data[0] : $key,
					'price_type' => isset( $option_data[1] ) ? $option_data[1] : 'fixed',
					'price'      => isset( $option_data[2] ) ? $option_data[2] : '0',
				);

				// Handle switcher value if present (for colorswitcher, imageswitcher, etc.).
				if ( isset( $option_data[3] ) && ! empty( $option_data[3] ) ) {
					$new_option['swticher'] = $option_data[3];
				}

				// Handle opt_id if present.
				if ( isset( $option_data['id'] ) && ! empty( $option_data['id'] ) ) {
					$new_option['id'] = $option_data['id'];
				}

				// Handle stock if present.
				if ( isset( $option_data['stock'] ) && '' !== $option_data['stock'] ) {
					$new_option['stock'] = $option_data['stock'];
				}

				$new_options[] = $new_option;
			} elseif ( is_string( $option_data ) ) {
				// Simple string (just label, no price info).
				$new_options[] = array(
					'label'      => $option_data,
					'price_type' => 'fixed',
					'price'      => '0',
				);
			}
		}

		// Update field data with new options format.
		$field_data['epofw_field_settings']['options'] = $new_options;

		return $field_data;
	}

	/**
	 * Migrate all addons from post_meta to custom tables
	 *
	 * Automatically detects and transforms old flat structure to new hierarchical.
	 * After successful migration, post_meta is deleted (custom tables only).
	 *
	 * @since 4.4.0
	 * @return array Migration results with success/error counts.
	 */
	public static function migrate_to_custom_tables() {
		global $wpdb;

		$results = array(
			'total'       => 0,
			'success'     => 0,
			'transformed' => 0,
			'errors'      => 0,
			'skipped'     => 0,
			'messages'    => array(),
		);

		// Handle multisite: migrate all sites in the network.
		if ( is_multisite() ) {
			$sites = get_sites( array( 'number' => 0 ) ); // Get all sites.

			foreach ( $sites as $site ) {
				switch_to_blog( $site->blog_id );

				$site_results = self::migrate_current_site_addons();

				// Merge results.
				$results['total']       += $site_results['total'];
				$results['success']     += $site_results['success'];
				$results['transformed'] += $site_results['transformed'];
				$results['errors']      += $site_results['errors'];
				$results['skipped']     += $site_results['skipped'];

				// Add site context to messages.
				foreach ( $site_results['messages'] as $message ) {
					$results['messages'][] = "[Site {$site->blog_id}] {$message}";
				}

				restore_current_blog();
			}
		} else {
			// Single site: migrate current site.
			$results = self::migrate_current_site_addons();
		}

		return $results;
	}

	/**
	 * Migrate addons for the current site/blog context.
	 *
	 * @since 4.4.0
	 * @return array Migration results.
	 */
	private static function migrate_current_site_addons() {
		$results = array(
			'total'       => 0,
			'success'     => 0,
			'transformed' => 0,
			'errors'      => 0,
			'skipped'     => 0,
			'messages'    => array(),
		);

		// Get all addon posts using the correct post type constant.
		$post_type = defined( 'EPOFW_DFT_POST_TYPE' ) ? EPOFW_DFT_POST_TYPE : 'dft_cpo';

		$addon_posts = get_posts(
			array(
				'post_type'      => $post_type,
				'posts_per_page' => -1,
				'post_status'    => array( 'publish', 'draft' ),
			)
		);

		$results['total'] = count( $addon_posts );

		// If no addon posts found, check if there's nothing to migrate.
		if ( empty( $addon_posts ) ) {
			$results['messages'][] = 'No addon posts found in database';
			return $results;
		}

		foreach ( $addon_posts as $post ) {
			$post_id = $post->ID;

			try {
				// First check if already migrated to custom tables.
				$already_migrated = get_post_meta( $post_id, 'epofw_migrated_to_tables', true );

				// Load data from post_meta.
				$addon_data = get_post_meta( $post_id, 'epofw_prd_opt_data', true );

				if ( empty( $addon_data ) ) {
					// Check if data exists in custom tables already.
					if ( class_exists( 'EPOFW_Custom_Tables' ) && EPOFW_Custom_Tables::is_enabled() ) {
						$table_data = EPOFW_Custom_Tables::load_addon( $post_id );
						if ( ! empty( $table_data ) ) {
							++$results['skipped'];
							$results['messages'][] = "Addon {$post_id}: Already in custom tables (skipped)";
							continue;
						}
					}

					++$results['skipped'];
					$results['messages'][] = "Addon {$post_id}: No data found in post_meta (skipped)";
					continue;
				}

				// Check if old structure and transform.
				if ( self::is_old_flat_structure( $addon_data ) ) {
					$addon_data = self::transform_to_new_structure( $addon_data );
					++$results['transformed'];
					$results['messages'][] = "Addon {$post_id}: Transformed from old to new structure";
				}

				// Save to custom tables.
				$saved = EPOFW_Custom_Tables::save_addon( $post_id, $addon_data );

				if ( $saved ) {
					++$results['success'];
					$results['messages'][] = "Addon {$post_id}: Migrated successfully";

					// Mark as migrated. Keep post_meta for backward compatibility (rollback to older version).
					update_post_meta( $post_id, 'epofw_migrated_to_tables', true );
					// phpcs:ignore Squiz.PHP.CommentedOutCode.Found -- Intentionally kept for reference.
				} else {
					++$results['errors'];
					$results['messages'][] = "Addon {$post_id}: Failed to save to custom tables";
				}
			} catch ( Exception $e ) {
				++$results['errors'];
				$results['messages'][] = "Addon {$post_id}: Error - " . $e->getMessage();
			}
		}

		return $results;
	}

	/**
	 * Migrate a single addon from old to new structure
	 *
	 * After successful migration, post_meta is deleted (custom tables only).
	 *
	 * @since 4.4.0
	 * @param int $post_id Post ID to migrate.
	 * @return array Migration result.
	 */
	public static function migrate_single_addon( $post_id ) {
		$result = array(
			'success'     => false,
			'transformed' => false,
			'message'     => '',
		);

		// Load data from post_meta.
		$addon_data = get_post_meta( $post_id, 'epofw_prd_opt_data', true );

		if ( empty( $addon_data ) ) {
			$result['message'] = 'No data found in post_meta';
			return $result;
		}

		// Check if old structure and transform.
		if ( self::is_old_flat_structure( $addon_data ) ) {
			$addon_data            = self::transform_to_new_structure( $addon_data );
			$result['transformed'] = true;
		}

		// Save to custom tables.
		if ( class_exists( 'EPOFW_Custom_Tables' ) ) {
			$saved = EPOFW_Custom_Tables::save_addon( $post_id, $addon_data );

			if ( $saved ) {
				// Mark as migrated. Keep post_meta for backward compatibility (rollback to older version).
				update_post_meta( $post_id, 'epofw_migrated_to_tables', true );
				// phpcs:ignore Squiz.PHP.CommentedOutCode.Found -- Intentionally kept for reference.

				$result['success'] = true;
				$result['message'] = $result['transformed']
					? 'Transformed and migrated to custom tables'
					: 'Migrated to custom tables';
			} else {
				$result['message'] = 'Failed to save to custom tables';
			}
		} else {
			$result['message'] = 'Custom tables class not available';
		}

		return $result;
	}

	/**
	 * Migrate all addons from custom tables back to post_meta
	 *
	 * @since 4.4.0
	 * @return array Migration results with success/error counts.
	 */
	public static function migrate_to_post_meta() {
		global $wpdb;

		$results = array(
			'total'    => 0,
			'success'  => 0,
			'errors'   => 0,
			'messages' => array(),
		);

		// Handle multisite: migrate all sites in the network.
		if ( is_multisite() ) {
			$sites = get_sites( array( 'number' => 0 ) ); // Get all sites.

			foreach ( $sites as $site ) {
				switch_to_blog( $site->blog_id );

				$site_results = self::migrate_current_site_to_post_meta();

				// Merge results.
				$results['total']   += $site_results['total'];
				$results['success'] += $site_results['success'];
				$results['errors']  += $site_results['errors'];

				// Add site context to messages.
				foreach ( $site_results['messages'] as $message ) {
					$results['messages'][] = "[Site {$site->blog_id}] {$message}";
				}

				restore_current_blog();
			}
		} else {
			// Single site: migrate current site.
			$results = self::migrate_current_site_to_post_meta();
		}

		return $results;
	}

	/**
	 * Migrate addons from custom tables to post_meta for the current site/blog context.
	 *
	 * @since 4.4.0
	 * @return array Migration results.
	 */
	private static function migrate_current_site_to_post_meta() {
		global $wpdb;

		$results = array(
			'total'    => 0,
			'success'  => 0,
			'errors'   => 0,
			'messages' => array(),
		);

		// Get all addon IDs from custom tables.
		$table_name = $wpdb->prefix . 'epofw_addons';

		// Check if table exists.
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Migration operation, one-time table existence check.
		$table_exists = $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $table_name ) );
		if ( ! $table_exists ) {
			$results['messages'][] = 'Custom tables do not exist';
			return $results;
		}

		// Escape table name for security (even though it's constructed from safe $wpdb->prefix).
		$table_name_escaped = esc_sql( $table_name );

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Migration operation, runs once during migration process, table name escaped with esc_sql().
		$addon_ids = $wpdb->get_col(
			"SELECT DISTINCT post_id FROM `{$table_name_escaped}`"
		);

		$results['total'] = count( $addon_ids );

		foreach ( $addon_ids as $post_id ) {
			try {
				// Load data from custom tables.
				$addon_data = EPOFW_Custom_Tables::load_addon( $post_id );

				if ( empty( $addon_data ) ) {
					$results['messages'][] = "Addon {$post_id}: No data found in custom tables";
					continue;
				}

				// Save to post_meta.
				update_post_meta( $post_id, 'epofw_prd_opt_data', $addon_data );

				++$results['success'];
				$results['messages'][] = "Addon {$post_id}: Migrated back to post_meta successfully";

				// Remove migration flag.
				delete_post_meta( $post_id, 'epofw_migrated_to_tables' );

			} catch ( Exception $e ) {
				++$results['errors'];
				$results['messages'][] = "Addon {$post_id}: Error - " . $e->getMessage();
			}
		}

		return $results;
	}

	/**
	 * Check if addon was migrated from old structure
	 *
	 * @since 4.4.0
	 * @param array $section_settings Section settings array.
	 * @return bool True if this section is from a migrated addon.
	 */
	public static function is_migrated_section( $section_settings ) {
		if ( empty( $section_settings ) || ! is_array( $section_settings ) ) {
			return false;
		}

		return ! empty( $section_settings['is_migrated'] );
	}

	/**
	 * Verify data integrity after migration
	 *
	 * @since 4.4.0
	 * @param int $post_id Optional specific post ID to verify.
	 * @return array Verification results.
	 */
	public static function verify_migration( $post_id = 0 ) {
		$results = array(
			'verified'   => 0,
			'mismatches' => 0,
			'details'    => array(),
		);

		// Handle multisite: verify all sites in the network.
		if ( is_multisite() && ! $post_id ) {
			$sites = get_sites( array( 'number' => 0 ) ); // Get all sites.

			foreach ( $sites as $site ) {
				switch_to_blog( $site->blog_id );

				$site_results = self::verify_current_site_migration( 0 );

				// Merge results.
				$results['verified']   += $site_results['verified'];
				$results['mismatches'] += $site_results['mismatches'];

				// Add site context to details.
				foreach ( $site_results['details'] as $detail ) {
					$results['details'][] = "[Site {$site->blog_id}] {$detail}";
				}

				restore_current_blog();
			}
		} else {
			// Single site or specific post ID: verify current site.
			$results = self::verify_current_site_migration( $post_id );
		}

		return $results;
	}

	/**
	 * Verify migration for the current site/blog context.
	 *
	 * @since 4.4.0
	 * @param int $post_id Optional specific post ID to verify.
	 * @return array Verification results.
	 */
	private static function verify_current_site_migration( $post_id = 0 ) {
		$results = array(
			'verified'   => 0,
			'mismatches' => 0,
			'details'    => array(),
		);

		$post_type = defined( 'EPOFW_DFT_POST_TYPE' ) ? EPOFW_DFT_POST_TYPE : 'dft_cpo';

		if ( $post_id ) {
			$addon_ids = array( $post_id );
		} else {
			// Get all addon posts.
			$addon_posts = get_posts(
				array(
					'post_type'      => $post_type,
					'posts_per_page' => -1,
					'post_status'    => array( 'publish', 'draft' ),
					'fields'         => 'ids',
				)
			);
			$addon_ids   = $addon_posts;
		}

		foreach ( $addon_ids as $id ) {
			// Load from both sources.
			$post_meta_data    = get_post_meta( $id, 'epofw_prd_opt_data', true );
			$custom_table_data = null;

			if ( class_exists( 'EPOFW_Custom_Tables' ) ) {
				$custom_table_data = EPOFW_Custom_Tables::load_addon( $id );
			}

			if ( empty( $post_meta_data ) && empty( $custom_table_data ) ) {
				$results['details'][ $id ] = 'No data in either source';
				continue;
			}

			// Compare field counts.
			$meta_field_count  = self::count_fields( $post_meta_data );
			$table_field_count = self::count_fields( $custom_table_data );

			if ( $meta_field_count === $table_field_count ) {
				++$results['verified'];
				$results['details'][ $id ] = "Verified: {$meta_field_count} fields match";
			} else {
				++$results['mismatches'];
				$results['details'][ $id ] = "Mismatch: post_meta has {$meta_field_count} fields, custom tables has {$table_field_count} fields";
			}
		}

		return $results;
	}

	/**
	 * Count total fields in addon data (supports both old and new structure)
	 *
	 * @since 4.4.0
	 * @param array $addon_data Addon data array.
	 * @return int Field count.
	 */
	public static function count_fields( $addon_data ) {
		$count = 0;

		if ( empty( $addon_data['general'] ) || ! is_array( $addon_data['general'] ) ) {
			return 0;
		}

		// Check if old or new structure.
		if ( self::is_old_flat_structure( $addon_data ) ) {
			// Old structure: fields directly in general.
			foreach ( $addon_data['general'] as $field_data ) {
				if ( is_array( $field_data ) && isset( $field_data['field'] ) ) {
					++$count;
				}
			}
		} else {
			// New structure: fields nested in sections > row_groups > rows > fields.
			foreach ( $addon_data['general'] as $section ) {
				if ( ! empty( $section['row_groups'] ) && is_array( $section['row_groups'] ) ) {
					foreach ( $section['row_groups'] as $group ) {
						if ( ! empty( $group['rows'] ) && is_array( $group['rows'] ) ) {
							foreach ( $group['rows'] as $row ) {
								if ( ! empty( $row['fields'] ) && is_array( $row['fields'] ) ) {
									$count += count( $row['fields'] );
								}
							}
						}
					}
				}

				// Also check legacy fields at section level.
				if ( ! empty( $section['fields'] ) && is_array( $section['fields'] ) ) {
					$count += count( $section['fields'] );
				}
			}
		}

		return $count;
	}

	/**
	 * Batch migrate addons (process in batches to avoid timeout)
	 *
	 * After successful migration, post_meta is deleted (custom tables only).
	 *
	 * @since 4.4.0
	 * @param int $batch_size Number of addons to process per batch.
	 * @param int $offset     Starting offset.
	 * @return array Migration results.
	 */
	public static function batch_migrate( $batch_size = 10, $offset = 0 ) {
		$addon_posts = get_posts(
			array(
				'post_type'      => 'epofw_product_opt',
				'posts_per_page' => $batch_size,
				'offset'         => $offset,
				'post_status'    => array( 'publish', 'draft' ),
			)
		);

		$results = array(
			'processed'   => 0,
			'success'     => 0,
			'transformed' => 0,
			'errors'      => 0,
			'has_more'    => count( $addon_posts ) === $batch_size,
		);

		foreach ( $addon_posts as $post ) {
			$addon_data = get_post_meta( $post->ID, 'epofw_prd_opt_data', true );

			if ( ! empty( $addon_data ) ) {
				// Check if old structure and transform.
				if ( self::is_old_flat_structure( $addon_data ) ) {
					$addon_data = self::transform_to_new_structure( $addon_data );
					++$results['transformed'];
				}

				// Save to custom tables.
				$saved = EPOFW_Custom_Tables::save_addon( $post->ID, $addon_data );

				if ( $saved ) {
					++$results['success'];

					// Mark as migrated. Keep post_meta for backward compatibility (rollback to older version).
					update_post_meta( $post->ID, 'epofw_migrated_to_tables', true );
					// phpcs:ignore Squiz.PHP.CommentedOutCode.Found -- Intentionally kept for reference.
				} else {
					++$results['errors'];
				}
			}

			++$results['processed'];
		}

		return $results;
	}

	/**
	 * Get migration status for all addons
	 *
	 * @since 4.4.0
	 * @return array Status summary.
	 */
	public static function get_migration_status() {
		$status = array(
			'total_addons'       => 0,
			'old_structure'      => 0,
			'new_structure'      => 0,
			'migrated_to_tables' => 0,
			'pending_migration'  => 0,
			'sites'              => array(), // Multisite: per-site breakdown.
		);

		// Handle multisite: get status for all sites in the network.
		if ( is_multisite() ) {
			$sites = get_sites( array( 'number' => 0 ) ); // Get all sites.

			foreach ( $sites as $site ) {
				switch_to_blog( $site->blog_id );

				$site_status = self::get_current_site_migration_status();

				// Merge results.
				$status['total_addons']       += $site_status['total_addons'];
				$status['old_structure']      += $site_status['old_structure'];
				$status['new_structure']      += $site_status['new_structure'];
				$status['migrated_to_tables'] += $site_status['migrated_to_tables'];
				$status['pending_migration']  += $site_status['pending_migration'];

				// Store per-site status.
				$status['sites'][ $site->blog_id ] = $site_status;

				restore_current_blog();
			}
		} else {
			// Single site: get status for current site.
			$site_status = self::get_current_site_migration_status();
			$status      = array_merge( $status, $site_status );
		}

		return $status;
	}

	/**
	 * Get migration status for the current site/blog context.
	 *
	 * @since 4.4.0
	 * @return array Status summary.
	 */
	private static function get_current_site_migration_status() {
		$status = array(
			'total_addons'       => 0,
			'old_structure'      => 0,
			'new_structure'      => 0,
			'migrated_to_tables' => 0,
			'pending_migration'  => 0,
		);

		$post_type = defined( 'EPOFW_DFT_POST_TYPE' ) ? EPOFW_DFT_POST_TYPE : 'dft_cpo';

		$addon_posts = get_posts(
			array(
				'post_type'      => $post_type,
				'posts_per_page' => -1,
				'post_status'    => array( 'publish', 'draft' ),
			)
		);

		$status['total_addons'] = count( $addon_posts );

		foreach ( $addon_posts as $post ) {
			$addon_data  = get_post_meta( $post->ID, 'epofw_prd_opt_data', true );
			$is_migrated = get_post_meta( $post->ID, 'epofw_migrated_to_tables', true );

			if ( ! empty( $addon_data ) ) {
				if ( self::is_old_flat_structure( $addon_data ) ) {
					++$status['old_structure'];
					++$status['pending_migration'];
				} else {
					++$status['new_structure'];
				}
			}

			if ( $is_migrated ) {
				++$status['migrated_to_tables'];
			}
		}

		return $status;
	}

	/**
	 * Force re-migration of a single addon from post_meta
	 *
	 * This method clears the migration flag and custom table data,
	 * then re-runs migration using the post_meta data as source.
	 * Useful when migration logic has been updated and data needs to be re-migrated.
	 *
	 * @since 4.4.0
	 * @param int $post_id Post ID to re-migrate.
	 * @return array Migration result.
	 */
	public static function force_remigrate_addon( $post_id ) {
		$result = array(
			'success'     => false,
			'transformed' => false,
			'message'     => '',
		);

		// Load data from post_meta (original source).
		$addon_data = get_post_meta( $post_id, 'epofw_prd_opt_data', true );

		if ( empty( $addon_data ) ) {
			$result['message'] = 'No data found in post_meta to re-migrate';
			return $result;
		}

		// Clear migration flags.
		delete_post_meta( $post_id, 'epofw_migrated_to_tables' );
		delete_post_meta( $post_id, 'epofw_migrated_to_custom_tables' );

		// Check if old structure and transform.
		if ( self::is_old_flat_structure( $addon_data ) ) {
			$addon_data            = self::transform_to_new_structure( $addon_data );
			$result['transformed'] = true;
		}

		// Save to custom tables (this will delete existing and insert new).
		if ( class_exists( 'EPOFW_Custom_Tables' ) ) {
			$saved = EPOFW_Custom_Tables::save_addon( $post_id, $addon_data );

			if ( $saved ) {
				// Mark as migrated.
				update_post_meta( $post_id, 'epofw_migrated_to_tables', true );
				update_post_meta( $post_id, 'epofw_migrated_to_custom_tables', true );

				$result['success'] = true;
				$result['message'] = $result['transformed']
					? 'Transformed and re-migrated to custom tables successfully'
					: 'Re-migrated to custom tables successfully';
			} else {
				$result['message'] = 'Failed to save to custom tables';
			}
		} else {
			$result['message'] = 'Custom tables class not available';
		}

		return $result;
	}

	/**
	 * Force re-migration of all addons from post_meta
	 *
	 * @since 4.4.0
	 * @return array Migration results.
	 */
	public static function force_remigrate_all() {
		$results = array(
			'total'       => 0,
			'success'     => 0,
			'transformed' => 0,
			'errors'      => 0,
			'messages'    => array(),
		);

		// Get all addon posts.
		$post_type   = defined( 'EPOFW_DFT_POST_TYPE' ) ? EPOFW_DFT_POST_TYPE : 'dft_cpo';
		$addon_posts = get_posts(
			array(
				'post_type'      => $post_type,
				'posts_per_page' => -1,
				'post_status'    => array( 'publish', 'draft' ),
			)
		);

		$results['total'] = count( $addon_posts );

		foreach ( $addon_posts as $post ) {
			$single_result = self::force_remigrate_addon( $post->ID );

			if ( $single_result['success'] ) {
				++$results['success'];
				if ( $single_result['transformed'] ) {
					++$results['transformed'];
				}
			} else {
				++$results['errors'];
			}

			$results['messages'][] = "Addon {$post->ID}: " . $single_result['message'];
		}

		return $results;
	}
}
