<?php
/**
 * Migration
 *
 * @category   WordPress_Plugin
 * @package    Common
 * @subpackage Migration
 * @author     miniOrange <info@miniorange.com>
 * @license    MIT/Expat
 * @link       https://plugins.miniorange.com/woocommerce-api-product-sync-with-woocommerce-rest-apis
 */

namespace MOWCPS\Common\Migration;

use MOWCPS\Common\Constants\Constants;
use MOWCPS\Common\Controllers\Rest_API\Supplier_Rest_Controller;
use MOWCPS\Common\Models\Connection_Model;
use MOWCPS\Common\Models\Setting_Model;
use MOWCPS\Common\Models\Supplier_Model;
use MOWCPS\Common\Services\ReadFileData\Read_File_Data;
use MOWCPS\Common\Services\Cache\Cache_Service;
use MOWCPS\Common\Utils\Model_Utils;
use MOWCPS\Common\Utils\Utils;

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

/**
 * Class Migration
 *
 * Handles the creation and migration of all supplier-related database tables.
 *
 * @package MOWCPS\Common\Migration
 */
class Migration {
	/**
	 * WordPress database object.
	 *
	 * @var \wpdb
	 */
	protected $wpdb;

	/**
	 * Database charset collate.
	 *
	 * @var string
	 */
	protected $charset_collate;

	/**
	 * List of all tables managed by this migration.
	 *
	 * @var array
	 */
	private $tables;

	/**
	 * Whether the migration is required.
	 *
	 * @var bool
	 */
	protected static $migration_required = false;

	/**
	 * Whether the migration is required.
	 *
	 * @var bool
	 */
	protected static $is_new_installation = false;

	/**
	 * Initialize the migration class.
	 */
	public function __construct() {
		global $wpdb;
		$this->wpdb            = $wpdb;
		$this->charset_collate = $wpdb->get_charset_collate();
		$this->tables          = array(
			Model_Utils::TABLE_SUPPLIERS,
			Model_Utils::TABLE_AUTHORIZATION,
			Model_Utils::TABLE_ATTRIBUTE_MAPPING,
			Model_Utils::TABLE_SYNC_OPERATIONS,
			Model_Utils::TABLE_CONNECTIONS,
			Model_Utils::TABLE_SYNC_SETTINGS,
			Model_Utils::TABLE_SYNC_OPERATION_CONNECTIONS,
			Model_Utils::TABLE_SYNC_QUEUE,
			Model_Utils::TABLE_PRODUCT_SYNC_AUDIT,
			Model_Utils::TABLE_PREVIEW_FILTERS,
		);
	}

	/**
	 * Initialize plugin db or check for migration
	 */
	public function check_for_migration() {
		$previous_installed_version = Model_Utils::get_option( 'mowcps_last_version' );
		$current_version            = Utils::get_version_number();

		// TODO - Remove after 22.6.0
		if ( false !== $previous_installed_version && $previous_installed_version <= "22.5.0" ) {
			$this->migrate_ftp_attribute_mapping();
		}

		if ( false === $previous_installed_version ) {
			$this->create_tables();
			$this->add_postmeta_performance_index();
			self::$migration_required  = true;
			self::$is_new_installation = true;
		} elseif ( ( $previous_installed_version < $current_version ) ) {
			$this->migrate_supplier_table();
			$this->migrate_connection_table();
			$this->add_postmeta_performance_index();
			$this->mo_update_attribute_mapping_enum_column();
			$this->migrate_turn14_images();
			$this->migrate_midocean_branding_connections();
			$this->migrate_amrod_branding_prices_connection();
			$this->migrate_barron_branding_connections();
			$this->migrate_amrod_updated_products_and_branding_connection();
			$this->update_supplier_configuration();
			$this->add_product_sync_audit_variation_success_parsed_column();
			$this->add_audit_mode_column();
			self::$migration_required = true;
		}
		// Always run idempotent authorization table migration to ensure ENUM includes all values.
		$this->migrate_authorization_table();
		if ( self::$migration_required ) {
			global $wpdb;
			$suppliers_t = Model_Utils::get_table_name( Model_Utils::TABLE_SUPPLIERS );
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Invalidate connection-list transients after migration; suppliers table only.
			foreach ( (array) $wpdb->get_col( "SELECT uuid FROM `{$suppliers_t}`" ) as $uuid ) {
				Cache_Service::forget( \MOWCPS\Common\Handlers\Rest_API\Connection_Handler::CACHE_KEY_PREFIX . $uuid );
			}
		}
		Model_Utils::update_option( 'mowcps_last_version', $current_version );
	}

	/**
	 * Migrate FTP attribute mapping.
	 *
	 * Converts legacy numeric column indexes in supplier_attribute to header names when the file uses
	 * a header row, or to column_{n} (1-based) when it does not, matching Read_File_Data behavior.
	 */
	private function migrate_ftp_attribute_mapping() {
		$suppliers = Supplier_Model::get_all( array( 'sync_type' => 'ftp' ) );

		if ( is_wp_error( $suppliers ) || empty( $suppliers ) ) {
			return;
		}

		$ftp_supplier_ids = array();
		foreach ( $suppliers as $supplier ) {
			$sync_type = strtoupper( (string) ( $supplier['sync_type'] ?? '' ) );
			if ( Constants::SYNC_TYPE_FTP === $sync_type && ! empty( $supplier['uuid'] ) ) {
				$ftp_supplier_ids[] = $supplier['uuid'];
			}
		}

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

		$supplier_controller = new Supplier_Rest_Controller();
		$export_request      = new class( $ftp_supplier_ids ) {
			private $json_params;
			public function __construct( $supplier_ids ) {
				$this->json_params = array(
					'supplier_ids' => $supplier_ids,
				);
			}
			public function get_json_params() {
				return $this->json_params;
			}
		};

		$export_response = $supplier_controller->export_bulk_suppliers( $export_request );
		if ( ! is_object( $export_response ) || ! method_exists( $export_response, 'get_data' ) ) {
			return;
		}

		$export_response_data = $export_response->get_data();
		if ( empty( $export_response_data['success'] ) || empty( $export_response_data['data']['file_content'] ) ) {
			return;
		}

		$decoded_file_content = base64_decode( (string) $export_response_data['data']['file_content'], true );
		if ( false === $decoded_file_content || '' === $decoded_file_content ) {
			return;
		}

		$exported_supplier_data = json_decode( $decoded_file_content, true );
		if ( ! is_array( $exported_supplier_data ) || empty( $exported_supplier_data ) ) {
			return;
		}

		$migrated_supplier_data = array();
		foreach ( $exported_supplier_data as $index => $supplier_config ) {
			$supplier_uuid = $ftp_supplier_ids[ $index ] ?? '';
			if ( '' === $supplier_uuid || ! is_array( $supplier_config ) ) {
				continue;
			}

			$header_context = $this->migrate_ftp_get_header_context( $supplier_uuid );
			if ( empty( $header_context['should_migrate'] ) ) {
				continue;
			}

			$migrated_supplier_data[] = $this->migrate_ftp_transform_config_attributes(
				$supplier_config,
				$header_context['use_first_row_as_header'],
				$header_context['ordered_headers']
			);
		}

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

		$import_request = new class( $migrated_supplier_data ) {
			private $json_params;
			public function __construct( $supplier_data ) {
				$this->json_params = array(
					'overwrite_if_exist' => true,
					'data'               => $supplier_data,
				);
			}
			public function get_json_params() {
				return $this->json_params;
			}
		};

		$supplier_controller->import_bulk_supplier( $import_request );
	}

	/**
	 * Build FTP header context from the cached file under data/external-supplier/file-response/{connection_id}/.
	 *
	 * @param string $supplier_uuid Supplier UUID.
	 * @return array{use_first_row_as_header: bool, ordered_headers: array, should_migrate: bool}
	 */
	private function migrate_ftp_get_header_context( $supplier_uuid ) {
		$default = array(
			'use_first_row_as_header' => true,
			'ordered_headers'         => array(),
			'should_migrate'          => true,
		);

		$connections = Connection_Model::get_by_supplier_uuid( $supplier_uuid );
		if ( is_wp_error( $connections ) || empty( $connections ) ) {
			return $default;
		}

		$ftp_connection = null;
		foreach ( $connections as $connection ) {
			$is_ftp = isset( $connection['type'] ) && Constants::CONNECTION_TYPE_FTP === strtolower( (string) $connection['type'] );
			if ( ! $is_ftp ) {
				continue;
			}

			if ( null === $ftp_connection ) {
				$ftp_connection = $connection;
			}
		}

		if ( null === $ftp_connection ) {
			return $default;
		}

		$context = array(
			'use_first_row_as_header' => (bool) ( $ftp_connection['configuration']['use_first_row_as_header'] ?? true ),
			'ordered_headers'         => array(),
			'should_migrate'          => true,
		);

		$file_settings = Setting_Model::get_by_connection_and_operation( $ftp_connection['id'], 'file-sync', array( 'configuration' => true ) );
		$filename      = is_array( $file_settings ) && ! empty( $file_settings['configuration']['file_name'] )
			? (string) $file_settings['configuration']['file_name']
			: '';

		$file_directory = MOWCPS_PLUGIN_DIR . DIRECTORY_SEPARATOR . 'data' . DIRECTORY_SEPARATOR . 'external-supplier' . DIRECTORY_SEPARATOR . 'api-file-response' . DIRECTORY_SEPARATOR . $ftp_connection['id'];
		$file_directory = wp_normalize_path( $file_directory );
		$file_full_path = wp_normalize_path( $file_directory . DIRECTORY_SEPARATOR . $filename );

		if ( '' === $filename || ! file_exists( $file_full_path ) || ! is_readable( $file_full_path ) ) {
			$context['should_migrate'] = false;
			return $context;
		}

		$delimiter  = is_array( $file_settings ) && isset( $file_settings['configuration']['delimiter'] ) ? $file_settings['configuration']['delimiter'] : false;
		$port       = (int) ( $ftp_connection['configuration']['port'] ?? 21 );

		$header_data = Read_File_Data::read_data_from_file(
			$file_directory,
			$filename,
			1,
			10,
			$context['use_first_row_as_header'],
			$delimiter
		);

		$header_data = $header_data[0];

		if ( ! is_array( $header_data ) || empty( $header_data ) ) {
			$context['should_migrate'] = false;
			return $context;
		}

		if ( ! is_array( $header_data ) || empty( $header_data ) ) {
			$context['should_migrate'] = false;
			return $context;
		}

		$header_data_keys = array_keys( $header_data );
		$is_assoc_row   = $header_data_keys !== range( 0, count( $header_data ) - 1 );

		if ( $context['use_first_row_as_header'] ) {
			$context['ordered_headers'] = $is_assoc_row ? array_values( $header_data_keys ) : array_map( 'strval', array_values( $header_data ) );
		}

		return $context;
	}

	/**
	 * Recursively migrate legacy index-based configuration values to attribute names.
	 *
	 * @param mixed $config Supplier config.
	 * @param bool  $use_first_row_as_header Whether to map against first-row header keys.
	 * @param array $ordered_headers Header order list.
	 * @return mixed
	 */
	private function migrate_ftp_transform_config_attributes( $config, $use_first_row_as_header, $ordered_headers ) {
		if ( ! is_array( $config ) ) {
			return $config;
		}

		$keys_to_transform = array(
			'parent_identifier',
			'main_product_unique_identifier',
			'variable_product_unique_identifier',
			'supplier_attribute',
		);

		foreach ( $config as $key => $value ) {
			if ( is_array( $value ) ) {
				$config[ $key ] = $this->migrate_ftp_transform_config_attributes( $value, $use_first_row_as_header, $ordered_headers );
			}

			if ( in_array( $key, $keys_to_transform, true ) && is_scalar( $value ) ) {
				$config[ $key ] = $this->migrate_ftp_resolve_supplier_attribute( (string) $value, $use_first_row_as_header, $ordered_headers );
			}
		}

		return $config;
	}

	/**
	 * Map legacy 0-based column index string to header name or column_{1-based index}.
	 *
	 * Supports plain indices (e.g. "0") and structured paths that keep separators such as "=>" and ","
	 * (e.g. "0=>1,2,3"); each digit run is replaced while delimiter positions are preserved.
	 *
	 * @param string $supplier_attribute Stored supplier_attribute value.
	 * @param bool   $use_first_row_as_header Whether to prefer header names from file.
	 * @param array  $ordered_headers Zero-based list of header/column labels from file sample.
	 * @return string
	 */
	private function migrate_ftp_resolve_supplier_attribute( $supplier_attribute, $use_first_row_as_header, $ordered_headers ) {
		if ( ! is_scalar( $supplier_attribute ) ) {
			return '';
		}

		$trimmed = trim( (string) $supplier_attribute );
		if ( '' === $trimmed ) {
			return '';
		}

		return (string) preg_replace_callback(
			'/[0-9]+/',
			function ( $matches ) use ( $use_first_row_as_header, $ordered_headers ) {
				$index = (int) $matches[0];
				if ( $use_first_row_as_header && ! empty( $ordered_headers ) && isset( $ordered_headers[ $index ] ) ) {
					return (string) $ordered_headers[ $index ];
				}

				return 'column_' . ( $index + 1 );
			},
			$trimmed
		);
	}

	/**
	 * Add variation_success_parsed to product sync audit when missing (idempotent).
	 *
	 * @return void
	 */
	private function add_product_sync_audit_variation_success_parsed_column() {
		$table = Model_Utils::get_table_name( Model_Utils::TABLE_PRODUCT_SYNC_AUDIT );
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name is controlled.
		$column = $this->wpdb->get_results( "SHOW COLUMNS FROM `{$table}` LIKE 'variation_success_parsed'" );
		if ( ! empty( $column ) ) {
			return;
		}
		$this->wpdb->query(
			"ALTER TABLE `{$table}` ADD COLUMN `variation_success_parsed` BIGINT NULL AFTER `parsed_failed`"
		); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.SchemaChange -- Idempotent schema upgrade.
	}

	/**
	 * Update supplier configuration.
	 */
	private function update_supplier_configuration() {
		Model_Utils::update_supplier_configuration();
	}

	/**
	 * Run the up migration.
	 *
	 * Creates all necessary tables and their relationships.
	 */
	public function create_tables() {
		$sql = array();

		// Create suppliers table.
		$sql[] = "CREATE TABLE IF NOT EXISTS `{$this->wpdb->prefix}mowcps_suppliers` (
            `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
            `uuid` CHAR(36) NOT NULL,
            `name` VARCHAR(50) NOT NULL,
            `is_active` BOOLEAN NOT NULL DEFAULT '1',
            `app_type` ENUM('amrod', 'barron', 'sns-activewear', 'shopify', 'woocommerce', 'midocean', 'sanmar', 'turn14', 'trends', 'promostandards', 'promostandards-sanmar', 'promostandards-sanmar-ca', 'promostandards-saintregis', 'utteam', 'toptex', 'ingram-micro', 'intex-global', 'kmq', 'malfini','syscom', 'jasani', 'custom') NOT NULL DEFAULT 'custom',
            `sync_type` ENUM('API', 'FTP', 'FILE') NOT NULL DEFAULT 'API',
            `debug_mode` BOOLEAN NOT NULL DEFAULT '0',
			`audit_mode` BOOLEAN NOT NULL DEFAULT '0',
            `unique_product_identifier` VARCHAR(255) NULL,
            `unique_product_variation_identifier` VARCHAR(255) NULL,
            `details` JSON NULL,
            UNIQUE KEY `mowcps_suppliers_uuid_unique` (`uuid`),
            UNIQUE KEY `mowcps_suppliers_name_unique` (`name`)
        ) {$this->charset_collate};";

		// Create authorization table.
		$sql[] = "CREATE TABLE IF NOT EXISTS `{$this->wpdb->prefix}mowcps_authorization` (
            `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
            `supplier_id` BIGINT UNSIGNED NOT NULL,
            `auth_type` ENUM('no-auth', 'basic', 'bearer', 'api-key', 'oauth2', 'custom') NOT NULL DEFAULT 'no-auth',
            `auth_settings` JSON NULL,
            UNIQUE KEY `mowcps_authorization_supplier_id_unique` (`supplier_id`)
        ) {$this->charset_collate};";

		// Create attribute mapping table.
		$sql[] = "CREATE TABLE IF NOT EXISTS `{$this->wpdb->prefix}mowcps_attribute_mapping` (
            `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
            `uuid` CHAR(36) NOT NULL,
            `supplier_id` BIGINT UNSIGNED NOT NULL,
            `connection_id` BIGINT UNSIGNED NOT NULL,
            `product_type` ENUM('parent') NOT NULL DEFAULT 'parent',
            `type` ENUM('core') NOT NULL DEFAULT 'core',
            `wc_attribute` VARCHAR(255) NOT NULL,
            `supplier_attribute` VARCHAR(255) NOT NULL,
            `advanced_configuration` JSON NULL,
            UNIQUE KEY `mowcps_attribute_mapping_uuid_unique` (`uuid`),
            UNIQUE KEY `unique_attribute_mapping` (`supplier_id`, `product_type`, `type`, `wc_attribute`)
        ) {$this->charset_collate};";

		// Create sync operations table.
		$sql[] = "CREATE TABLE IF NOT EXISTS `{$this->wpdb->prefix}mowcps_sync_operations` (
            `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
            `uuid` CHAR(36) NOT NULL,
            `supplier_id` BIGINT UNSIGNED NOT NULL,
            `operation_type` ENUM('data-sync', 'performance-optimization') NOT NULL DEFAULT 'data-sync',
            `operation` ENUM('product-operations', 'file-sync', 'page', 'batch') NOT NULL DEFAULT 'product-operations',
            `is_active` BOOLEAN NOT NULL DEFAULT '0',
            UNIQUE KEY `mowcps_sync_operations_uuid_unique` (`uuid`)
        ) {$this->charset_collate};";

		// Create connections table.
		$sql[] = "CREATE TABLE IF NOT EXISTS `{$this->wpdb->prefix}mowcps_connections` (
            `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
            `uuid` CHAR(36) NOT NULL,
            `supplier_id` BIGINT UNSIGNED NOT NULL,
            `name` VARCHAR(50) NOT NULL,
            `type` ENUM('api', 'ftp', 'file') NOT NULL DEFAULT 'api',
            `is_active` BOOLEAN NOT NULL DEFAULT '0',
            `is_primary` BOOLEAN NOT NULL DEFAULT '0',
            `resource_type` ENUM('product', 'order') NOT NULL DEFAULT 'product',
            `priority` INT NOT NULL,
            `configuration` JSON NOT NULL,
            UNIQUE KEY `mowcps_connections_supplier_id_name_unique` (`supplier_id`, `name`),
            UNIQUE KEY `mowcps_connections_uuid_unique` (`uuid`)
        ) {$this->charset_collate};";

		// Create sync settings table.
		$sql[] = "CREATE TABLE IF NOT EXISTS `{$this->wpdb->prefix}mowcps_sync_settings` (
            `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
            `operation_id` BIGINT UNSIGNED NOT NULL,
            `configuration` JSON NULL
        ) {$this->charset_collate};";

		// Create sync operation connections table.
		$sql[] = "CREATE TABLE IF NOT EXISTS `{$this->wpdb->prefix}mowcps_sync_operation_connections` (
            `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
            `sync_operation_id` BIGINT UNSIGNED NOT NULL,
            `connection_id` BIGINT UNSIGNED NOT NULL,
            UNIQUE KEY `sync_operation_id_connection_id_unique` (`sync_operation_id`, `connection_id`)
        ) {$this->charset_collate};";

		// Create sync queue table.
		$sql[] = "CREATE TABLE IF NOT EXISTS `{$this->wpdb->prefix}mowcps_sync_queue` (
            `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
            `supplier_id` BIGINT UNSIGNED NOT NULL,
            `mode` ENUM('manual', 'automatic') NOT NULL DEFAULT 'manual',
            `schedule_id` BIGINT UNSIGNED NULL,
            `connection_id` BIGINT UNSIGNED NOT NULL,
            `attribute_mapping_id` BIGINT UNSIGNED NOT NULL
        ) {$this->charset_collate};";

		// Create product sync audit table.
		$sql[] = "CREATE TABLE IF NOT EXISTS `{$this->wpdb->prefix}mowcps_product_sync_audit` (
            `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
            `supplier_id` BIGINT UNSIGNED NOT NULL,
            `schedule_id` BIGINT UNSIGNED NULL,
            `connection_id` BIGINT UNSIGNED NULL,
            `batch_number` BIGINT NULL,
            `initiate_sync` BOOLEAN NOT NULL DEFAULT '1',
            `sync_type` ENUM('vendor-available', 'vendor-unavailable', 'complete-connection') NOT NULL DEFAULT 'vendor-available',
            `status` VARCHAR(255) NOT NULL,
            `status_message` TEXT NOT NULL,
            `start_time` DATETIME NOT NULL,
            `end_time` DATETIME NULL,
            `total_parsed` BIGINT NULL,
            `parsed_success` BIGINT NULL,
            `parsed_failed` BIGINT NULL,
            `variation_success_parsed` BIGINT NULL,
            `current_child_product_count` BIGINT NULL,
            `additional_data` JSON NULL
        ) {$this->charset_collate};";

		// Create preview filters table.
		$sql[] = "CREATE TABLE IF NOT EXISTS `{$this->wpdb->prefix}mowcps_preview_filters` (
            `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
            `supplier_id` BIGINT UNSIGNED NOT NULL,
            `is_revert_mode` BOOLEAN NOT NULL DEFAULT '0',
            `is_active` BOOLEAN NOT NULL DEFAULT '0',
            `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
            `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            UNIQUE KEY `mowcps_preview_filters_supplier_id_unique` (`supplier_id`)
        ) {$this->charset_collate};";

		require_once ABSPATH . 'wp-admin/includes/upgrade.php';
		// Create tables first using dbDelta (without foreign keys).
		foreach ( $sql as $query ) {
			dbDelta( $query );
		}

		// Add foreign key constraints separately using direct queries (dbDelta doesn't handle these reliably).
		$this->add_foreign_key_constraints();
	}

	/**
	 * Add foreign key constraints with CASCADE delete rules.
	 *
	 * This method is called after table creation to ensure foreign keys are properly set up.
	 */
	private function add_foreign_key_constraints() {
		// Define constraints with metadata for checking existence.
		$constraints = array(
			array(
				'name'  => 'mowcps_sync_queue_supplier_id_foreign',
				'table' => 'mowcps_sync_queue',
				'query' => "ALTER TABLE `{$this->wpdb->prefix}mowcps_sync_queue` 
					ADD CONSTRAINT `mowcps_sync_queue_supplier_id_foreign` 
					FOREIGN KEY (`supplier_id`) REFERENCES `{$this->wpdb->prefix}mowcps_suppliers` (`id`)
					ON DELETE CASCADE",
			),
			array(
				'name'  => 'mowcps_product_sync_audit_connection_id_foreign',
				'table' => 'mowcps_product_sync_audit',
				'query' => "ALTER TABLE `{$this->wpdb->prefix}mowcps_product_sync_audit`
					ADD CONSTRAINT `mowcps_product_sync_audit_connection_id_foreign` 
					FOREIGN KEY (`connection_id`) REFERENCES `{$this->wpdb->prefix}mowcps_connections` (`id`)
					ON DELETE CASCADE",
			),
			array(
				'name'  => 'mowcps_sync_settings_operation_id_foreign',
				'table' => 'mowcps_sync_settings',
				'query' => "ALTER TABLE `{$this->wpdb->prefix}mowcps_sync_settings`
					ADD CONSTRAINT `mowcps_sync_settings_operation_id_foreign` 
					FOREIGN KEY (`operation_id`) REFERENCES `{$this->wpdb->prefix}mowcps_sync_operations` (`id`)
					ON DELETE CASCADE",
			),
			array(
				'name'  => 'mowcps_sync_queue_attribute_mapping_id_foreign',
				'table' => 'mowcps_sync_queue',
				'query' => "ALTER TABLE `{$this->wpdb->prefix}mowcps_sync_queue`
					ADD CONSTRAINT `mowcps_sync_queue_attribute_mapping_id_foreign` 
					FOREIGN KEY (`attribute_mapping_id`) REFERENCES `{$this->wpdb->prefix}mowcps_attribute_mapping` (`id`)
					ON DELETE CASCADE",
			),
			array(
				'name'  => 'mowcps_attribute_mapping_connection_id_foreign',
				'table' => 'mowcps_attribute_mapping',
				'query' => "ALTER TABLE `{$this->wpdb->prefix}mowcps_attribute_mapping`
					ADD CONSTRAINT `mowcps_attribute_mapping_connection_id_foreign` 
					FOREIGN KEY (`connection_id`) REFERENCES `{$this->wpdb->prefix}mowcps_connections` (`id`)
					ON DELETE CASCADE",
			),
			array(
				'name'  => 'mowcps_sync_queue_connection_id_foreign',
				'table' => 'mowcps_sync_queue',
				'query' => "ALTER TABLE `{$this->wpdb->prefix}mowcps_sync_queue`
					ADD CONSTRAINT `mowcps_sync_queue_connection_id_foreign` 
					FOREIGN KEY (`connection_id`) REFERENCES `{$this->wpdb->prefix}mowcps_connections` (`id`)
					ON DELETE CASCADE",
			),
			array(
				'name'  => 'mowcps_sync_operation_connections_connection_id_foreign',
				'table' => 'mowcps_sync_operation_connections',
				'query' => "ALTER TABLE `{$this->wpdb->prefix}mowcps_sync_operation_connections`
					ADD CONSTRAINT `mowcps_sync_operation_connections_connection_id_foreign` 
					FOREIGN KEY (`connection_id`) REFERENCES `{$this->wpdb->prefix}mowcps_connections` (`id`)
					ON DELETE CASCADE",
			),
			array(
				'name'  => 'mowcps_sync_operations_supplier_id_foreign',
				'table' => 'mowcps_sync_operations',
				'query' => "ALTER TABLE `{$this->wpdb->prefix}mowcps_sync_operations`
					ADD CONSTRAINT `mowcps_sync_operations_supplier_id_foreign` 
					FOREIGN KEY (`supplier_id`) REFERENCES `{$this->wpdb->prefix}mowcps_suppliers` (`id`)
					ON DELETE CASCADE",
			),
			array(
				'name'  => 'mowcps_connections_supplier_id_foreign',
				'table' => 'mowcps_connections',
				'query' => "ALTER TABLE `{$this->wpdb->prefix}mowcps_connections`
					ADD CONSTRAINT `mowcps_connections_supplier_id_foreign` 
					FOREIGN KEY (`supplier_id`) REFERENCES `{$this->wpdb->prefix}mowcps_suppliers` (`id`)
					ON DELETE CASCADE",
			),
			array(
				'name'  => 'mowcps_authorization_supplier_id_foreign',
				'table' => 'mowcps_authorization',
				'query' => "ALTER TABLE `{$this->wpdb->prefix}mowcps_authorization`
					ADD CONSTRAINT `mowcps_authorization_supplier_id_foreign` 
					FOREIGN KEY (`supplier_id`) REFERENCES `{$this->wpdb->prefix}mowcps_suppliers` (`id`)
					ON DELETE CASCADE",
			),
			array(
				'name'  => 'mowcps_sync_operation_connections_sync_operation_id_foreign',
				'table' => 'mowcps_sync_operation_connections',
				'query' => "ALTER TABLE `{$this->wpdb->prefix}mowcps_sync_operation_connections`
					ADD CONSTRAINT `mowcps_sync_operation_connections_sync_operation_id_foreign` 
					FOREIGN KEY (`sync_operation_id`) REFERENCES `{$this->wpdb->prefix}mowcps_sync_operations` (`id`)
					ON DELETE CASCADE",
			),
			array(
				'name'  => 'mowcps_attribute_mapping_supplier_id_foreign',
				'table' => 'mowcps_attribute_mapping',
				'query' => "ALTER TABLE `{$this->wpdb->prefix}mowcps_attribute_mapping`
					ADD CONSTRAINT `mowcps_attribute_mapping_supplier_id_foreign` 
					FOREIGN KEY (`supplier_id`) REFERENCES `{$this->wpdb->prefix}mowcps_suppliers` (`id`)
					ON DELETE CASCADE",
			),
			array(
				'name'  => 'mowcps_product_sync_audit_supplier_id_foreign',
				'table' => 'mowcps_product_sync_audit',
				'query' => "ALTER TABLE `{$this->wpdb->prefix}mowcps_product_sync_audit`
					ADD CONSTRAINT `mowcps_product_sync_audit_supplier_id_foreign` 
					FOREIGN KEY (`supplier_id`) REFERENCES `{$this->wpdb->prefix}mowcps_suppliers` (`id`)
					ON DELETE CASCADE",
			),
			array(
				'name'  => 'mowcps_preview_filters_supplier_id_foreign',
				'table' => 'mowcps_preview_filters',
				'query' => "ALTER TABLE `{$this->wpdb->prefix}mowcps_preview_filters`
					ADD CONSTRAINT `mowcps_preview_filters_supplier_id_foreign` 
					FOREIGN KEY (`supplier_id`) REFERENCES `{$this->wpdb->prefix}mowcps_suppliers` (`id`)
					ON DELETE CASCADE",
			),
		);

		foreach ( $constraints as $constraint ) {
			// Check if constraint already exists.
			$existing_constraint = $this->wpdb->get_var(
				$this->wpdb->prepare( // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- $this->wpdb variable is a class variable and is assigned the value of global $wpdb.
					'SELECT CONSTRAINT_NAME 
					FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS 
					WHERE CONSTRAINT_SCHEMA = %s 
					AND CONSTRAINT_NAME = %s',
					DB_NAME,  // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- DB_NAME is a constant and is assigned the value of the database name.
					$constraint['name'] // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- safe placeholder value from validated array.
				)
			);

			// Only add constraint if it doesn't exist.
			if ( ! $existing_constraint ) {
				$this->wpdb->query( $constraint['query'] ); //phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared -- Required for foreign key constraints.
			}
		}
	}

	/**
	 * Run the down migration.
	 *
	 * Drops all tables in reverse order of creation.
	 */
	public function drop_tables() {
		foreach ( array_reverse( $this->tables ) as $table ) {
			$this->wpdb->query(
				$this->wpdb->prepare( // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- $this->wpdb variable is a class variable and is assigned the value of global $wpdb.
					'DROP TABLE IF EXISTS `' . $this->wpdb->prefix . '%s`', // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- $this->wpdb->prefix is a string and is escaped using the escape_prefix method.
					$table // phpcs:ignore WordPress.Security.EscapeOutput.OutputNotEscaped -- $table is a string and is escaped using the escape_prefix method.
				)
			);
		}
	}

	/**
	 * Migrate suppliers table.
	 *
	 * Handles all migrations for the suppliers table.
	 * Currently adds 'FILE' to the sync_type ENUM if it doesn't exist.
	 */
	public function migrate_supplier_table() {
		global $wpdb;

		$table_name = $wpdb->prefix . 'mowcps_suppliers';

		// Check if table exists.
		if ( ! $this->table_exists( $table_name ) ) {
			return;
		}

		// Define new ENUM values.
		$enum_values = array(
			'amrod',
			'barron',
			'sns-activewear',
			'shopify',
			'woocommerce',
			'midocean',
			'sanmar',
			'parts-unlimited',
			'parts-europe',
			'turn14',
			'promostandards',
			'promostandards-sanmar',
			'promostandards-sanmar-ca',
			'promostandards-saintregis',
			'ingram-micro',
			'intex-global',
			'utteam',
			'trends',
			'toptex',
			'kmq',
			'jasani',
			'malfini',
			'syscom',
			'custom',
		);

		$enum_list = implode( "', '", array_map( 'esc_sql', $enum_values ) );

		// Build and execute ALTER TABLE statement.
		$sql = "ALTER TABLE `{$table_name}` 
				MODIFY COLUMN `app_type` 
				ENUM('{$enum_list}') 
				NOT NULL DEFAULT 'custom';";

		$result = $wpdb->query( $sql ); //phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared -- Required for ENUM column modification.

		// Check if 'FILE' value exists in sync_type ENUM.
		$column_info = $this->wpdb->get_row(
			$this->wpdb->prepare(
				"SELECT COLUMN_TYPE FROM INFORMATION_SCHEMA.COLUMNS 
				WHERE TABLE_SCHEMA = %s 
				AND TABLE_NAME = %s 
				AND COLUMN_NAME = 'sync_type'",
				DB_NAME,
				$table_name
			)
		);

		if ( $column_info && false === strpos( $column_info->COLUMN_TYPE, "'FILE'" ) ) {
			// Add 'FILE' to the ENUM.
			$this->wpdb->query(
				"ALTER TABLE `{$table_name}` 
				MODIFY COLUMN `sync_type` ENUM('API', 'FTP', 'FILE') NOT NULL DEFAULT 'API'"
			); //phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared -- Required for schema migration.
		}
	}

	/**
	 * Migrate connections table.
	 *
	 * Handles all migrations for the connections table.
	 * Currently adds 'file' to the type ENUM if it doesn't exist.
	 */
	private function migrate_connection_table() {
		$table_name = $this->wpdb->prefix . Model_Utils::TABLE_CONNECTIONS;

		// Check if table exists.
		if ( ! $this->table_exists( $table_name ) ) {
			return;
		}

		// Check if 'file' value exists in type ENUM.
		$column_info = $this->wpdb->get_row(
			$this->wpdb->prepare(
				"SELECT COLUMN_TYPE FROM INFORMATION_SCHEMA.COLUMNS 
				WHERE TABLE_SCHEMA = %s 
				AND TABLE_NAME = %s 
				AND COLUMN_NAME = 'type'",
				DB_NAME,
				$table_name
			)
		);

		if ( $column_info && false === strpos( $column_info->COLUMN_TYPE, "'file'" ) ) {
			// Add 'file' to the ENUM.
			$this->wpdb->query(
				"ALTER TABLE `{$table_name}` 
				MODIFY COLUMN `type` ENUM('api', 'ftp', 'file') NOT NULL DEFAULT 'api'"
			); //phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared -- Required for schema migration.
		}
	}

	/**
	 * Migrate authorization table.
	 *
	 * Finds all Shopify OAuth suppliers (details.use_oauth = true) and ensures their
	 * authorization row uses auth_type = 'oauth2' with the correct nested auth_settings
	 * structure expected by the standard OAuth2_Auth_Strategy.
	 *
	 * This covers three cases:
	 *  - Row has auth_type = '' (shopify-oauth was silently rejected by the ENUM).
	 *  - Row has auth_type = 'shopify-oauth' (user ran a manual SQL fix previously).
	 *  - Row has the old flat auth_settings shape from an earlier code iteration.
	 */
	private function migrate_authorization_table() {
		$auth_table      = $this->wpdb->prefix . Model_Utils::TABLE_AUTHORIZATION;
		$suppliers_table = $this->wpdb->prefix . Model_Utils::TABLE_SUPPLIERS;

		if ( ! $this->table_exists( $auth_table ) || ! $this->table_exists( $suppliers_table ) ) {
			return;
		}

		// Find all Shopify OAuth suppliers.
		$shopify_oauth_suppliers = $this->wpdb->get_results( //phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- One-time migration query.
			$this->wpdb->prepare(
				"SELECT id, details FROM `{$suppliers_table}` WHERE app_type = %s AND JSON_EXTRACT(details, '$.use_oauth') = true",
				'shopify'
			)
		);

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

		foreach ( $shopify_oauth_suppliers as $supplier ) {
			$details       = json_decode( $supplier->details, true );
			$domain        = $details['domain'] ?? '';
			$client_id     = $details['client_id'] ?? '';
			$client_secret = $details['client_secret'] ?? '';

			$auth_settings = array(
				'key'               => Constants::SHOPIFY_ACCESS_TOKEN_HEADER_NAME,
				'secret'            => '',
				'request_settings'  => array(
					'access_token_url'          => 'https://' . $domain . '/admin/oauth/access_token',
					'client_id'                 => $client_id,
					'client_secret'             => $client_secret,
					'grant_type'                => 'client_credentials',
					'send_client_credentials'   => array( 'body' ),
					'authorization_header_name' => Constants::SHOPIFY_ACCESS_TOKEN_HEADER_NAME,
				),
				'response_settings' => array(
					'token_key'      => 'access_token',
					'expires_in_key' => 'expires_in',
					'expires_in'     => 86399,
					'token_type'     => '',
				),
			);

			$existing_id = $this->wpdb->get_var( //phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Migration check.
				$this->wpdb->prepare(
					"SELECT id FROM `{$auth_table}` WHERE supplier_id = %d",
					(int) $supplier->id
				)
			);

			if ( $existing_id ) {
				$this->wpdb->update( //phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Migration update.
					$auth_table,
					array(
						'auth_type'     => 'oauth2',
						'auth_settings' => wp_json_encode( $auth_settings ),
					),
					array( 'supplier_id' => (int) $supplier->id ),
					array( '%s', '%s' ),
					array( '%d' )
				);
			} else {
				$this->wpdb->insert( //phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- Migration insert.
					$auth_table,
					array(
						'supplier_id'   => (int) $supplier->id,
						'auth_type'     => 'oauth2',
						'auth_settings' => wp_json_encode( $auth_settings ),
					),
					array( '%d', '%s', '%s' )
				);
			}
		}
	}

	/**
	 * Check if a table exists.
	 *
	 * @param string $table_name Full table name with prefix.
	 * @return bool True if table exists, false otherwise.
	 */
	private function table_exists( $table_name ) {
		$result = $this->wpdb->get_var(
			$this->wpdb->prepare(
				'SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES 
				WHERE TABLE_SCHEMA = %s 
				AND TABLE_NAME = %s',
				DB_NAME,
				$table_name
			)
		);
		return (bool) $result;
	}

	/**
	 * Update ENUM column type in the mowcps_attribute_mapping table to include integration.
	 * Added in version 22.3.0
	 */
	public function mo_update_attribute_mapping_enum_column() {
		global $wpdb;

		$table_name = $wpdb->prefix . 'mowcps_attribute_mapping';

		// Define new ENUM values.
		$enum_values = array(
			'core',
			'attribute',
			'taxonomy',
			'custom',
			'integration',
		);

		$enum_list = implode( "', '", array_map( 'esc_sql', $enum_values ) );

		// Build and execute ALTER TABLE statement.
		$sql = "ALTER TABLE `{$table_name}` 
				MODIFY COLUMN `type` 
				ENUM('{$enum_list}') 
				NOT NULL DEFAULT 'core';";

		$result = $wpdb->query( $sql ); //phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared -- Required for ENUM column modification.
	}

	/**
	 * Add performance index on wp_postmeta for bulk product deletion queries.
	 * Creates a compound index on (meta_key, meta_value) to dramatically speed up
	 * queries that filter by both meta_key and meta_value.
	 *
	 * This index specifically optimizes the bulk deletion query:
	 * SELECT post_id FROM wp_postmeta WHERE meta_key = 'mowcps_connection_name_product_created' AND meta_value IN (...).
	 *
	 * Impact: Can reduce query time from minutes to milliseconds for large datasets.
	 *
	 * Note: meta_value is limited to 191 chars in the index (max for utf8mb4)
	 * Added in version 22.2.0
	 */
	public function add_postmeta_performance_index() {
		global $wpdb;

		$index_name = 'mowcps_meta_key_value_idx';

		// Check if index already exists to avoid errors.
		$index_exists = $wpdb->get_var(
			$wpdb->prepare(
				'SELECT COUNT(1) 
				FROM INFORMATION_SCHEMA.STATISTICS 
				WHERE table_schema = %s 
				AND table_name = %s 
				AND index_name = %s',
				DB_NAME,
				$wpdb->postmeta,
				$index_name
			)
		);

		if ( $index_exists ) {
			return true;
		}

		// Create compound index on (meta_key, meta_value)
		// Limiting meta_value to 191 characters for utf8mb4 compatibility.
		$sql = "CREATE INDEX `{$index_name}` ON `{$wpdb->postmeta}` (`meta_key`(191), `meta_value`(191))";

		$result = $wpdb->query( $sql ); //phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared -- Required for index creation.

		return ( false === $result ) ? false : true;
	}

	/**
	 * Migrate Turn 14 Images configuration.
	 *
	 * This migration handles the transition from the old Turn 14 configuration where
	 * images were fetched from the Items connection using 'attributes->thumbnail'
	 * to the new configuration where images have their own dedicated connection
	 * with endpoint '/v1/items/data' and use 'files' with advanced configuration.
	 *
	 * Migration steps:
	 * 1. Find Turn 14 supplier (only one can exist per site)
	 * 2. Check if Images connection already exists (skip if it does)
	 * 3. Check if old images mapping exists under Items connection
	 * 4. If old mapping exists, create new Images connection and update mapping
	 *
	 * Added in version 22.4.0
	 */
	public function migrate_turn14_images() {
		global $wpdb;

		$suppliers_table           = $wpdb->prefix . Model_Utils::TABLE_SUPPLIERS;
		$connections_table         = $wpdb->prefix . Model_Utils::TABLE_CONNECTIONS;
		$attribute_mapping_table   = $wpdb->prefix . Model_Utils::TABLE_ATTRIBUTE_MAPPING;
		$sync_operations_table     = $wpdb->prefix . Model_Utils::TABLE_SYNC_OPERATIONS;
		$sync_settings_table       = $wpdb->prefix . Model_Utils::TABLE_SYNC_SETTINGS;
		$sync_op_connections_table = $wpdb->prefix . Model_Utils::TABLE_SYNC_OPERATION_CONNECTIONS;

		// Find Turn 14 supplier (only one can exist per site).
		$supplier = $wpdb->get_row(
			"SELECT id, uuid FROM `{$suppliers_table}` WHERE app_type = 'turn14'",
			ARRAY_A
		); //phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared -- Required for migration.

		if ( ! $supplier ) {
			return; // No Turn 14 supplier found.
		}

		$supplier_id = $supplier['id'];

		// Check if Images connection already exists.
		$images_connection_exists = $wpdb->get_var(
			$wpdb->prepare(
				"SELECT COUNT(*) FROM `{$connections_table}` WHERE supplier_id = %d AND name = 'Images'",
				$supplier_id
			)
		); //phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared -- Required for migration check.

		if ( $images_connection_exists ) {
			return; // Images connection already exists, skip migration.
		}

		// Get Items connection ID.
		$items_connection = $wpdb->get_row(
			$wpdb->prepare(
				"SELECT id, uuid, configuration FROM `{$connections_table}` WHERE supplier_id = %d AND name = 'Items'",
				$supplier_id
			),
			ARRAY_A
		); //phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared -- Required for migration.

		if ( ! $items_connection ) {
			return; // No Items connection found.
		}

		// Check if old images mapping exists under Items connection.
		$old_images_mapping = $wpdb->get_row(
			$wpdb->prepare(
				"SELECT * FROM `{$attribute_mapping_table}` 
				WHERE connection_id = %d 
				AND wc_attribute = 'images' 
				AND supplier_attribute = 'attributes->thumbnail'",
				$items_connection['id']
			),
			ARRAY_A
		); //phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared -- Required for migration check.

		if ( ! $old_images_mapping ) {
			return; // No old images mapping found, skip migration.
		}

		// Parse the Items connection configuration to get the base endpoint URL.
		$items_config = json_decode( $items_connection['configuration'], true );
		$base_url     = '';
		if ( isset( $items_config['endpoint'] ) ) {
			// Extract base URL from Items endpoint (e.g., https://api.turn14.com or https://api<domain>.turn14.com).
			$parsed_url = wp_parse_url( $items_config['endpoint'] );
			if ( isset( $parsed_url['scheme'] ) && isset( $parsed_url['host'] ) ) {
				$base_url = $parsed_url['scheme'] . '://' . $parsed_url['host'];
			}
		}

		if ( empty( $base_url ) ) {
			return; // Could not determine base URL.
		}

		// Create new Images connection.
		$images_connection_uuid = wp_generate_uuid4();
		$images_endpoint        = $base_url . '/v1/items/data';

		$images_configuration = wp_json_encode(
			array(
				'auth_enabled'           => true,
				'method'                 => 'GET',
				'endpoint'               => $images_endpoint,
				'headers'                => array(
					'Content-Type' => 'application/json',
					'Accept'       => 'application/json',
				),
				'body'                   => new \stdClass(),
				'prerequisites'          => array(
					'product_response'                   => 'data',
					'variable_response'                  => null,
					'main_product_unique_identifier'     => 'id',
					'variable_product_unique_identifier' => '',
				),
				'advanced_configuration' => new \stdClass(),
			)
		);

		$wpdb->insert(
			$connections_table,
			array(
				'uuid'          => $images_connection_uuid,
				'supplier_id'   => $supplier_id,
				'name'          => 'Images',
				'type'          => 'api',
				'is_active'     => 1,
				'is_primary'    => 0,
				'resource_type' => 'product',
				'priority'      => 4,
				'configuration' => $images_configuration,
			),
			array( '%s', '%d', '%s', '%s', '%d', '%d', '%s', '%d', '%s' )
		); //phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Required for migration.

		$images_connection_id = $wpdb->insert_id;

		// Update the existing images attribute mapping to point to new Images connection.
		$advanced_config = wp_json_encode(
			array(
				'translation'          => array(
					'is_active'  => false,
					'attributes' => array(
						array(
							'apiValue'        => '',
							'condition'       => 'equals',
							'translatedValue' => '',
						),
					),
				),
				'image_src_attr'       => 'links->0->url',
				'image_path_attr'      => '',
				'fetch_from_other_api' => array(
					'is_active'  => false,
					'connection' => '',
				),
			)
		);

		$wpdb->update(
			$attribute_mapping_table,
			array(
				'connection_id'          => $images_connection_id,
				'supplier_attribute'     => 'files',
				'advanced_configuration' => $advanced_config,
			),
			array( 'id' => $old_images_mapping['id'] ),
			array( '%d', '%s', '%s' ),
			array( '%d' )
		); //phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Required for migration.

		// Create product-operations operation for Images connection.
		$product_op_uuid = wp_generate_uuid4();

		$wpdb->insert(
			$sync_operations_table,
			array(
				'uuid'           => $product_op_uuid,
				'supplier_id'    => $supplier_id,
				'operation_type' => 'data-sync',
				'operation'      => 'product-operations',
				'is_active'      => 1,
			),
			array( '%s', '%d', '%s', '%s', '%d' )
		); //phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Required for migration.

		$product_op_id = $wpdb->insert_id;

		// Add product operation settings.
		$product_op_config = wp_json_encode(
			array(
				'available_products'   => array(
					'add'                   => false,
					'update'                => true,
					'update_product_status' => false,
					'product_status'        => 'publish',
				),
				'unavailable_products' => array(
					'operation'   => 'none',
					'products_of' => 'none',
				),
			)
		);

		$wpdb->insert(
			$sync_settings_table,
			array(
				'operation_id'  => $product_op_id,
				'configuration' => $product_op_config,
			),
			array( '%d', '%s' )
		); //phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Required for migration.

		// Link product operation to Images connection.
		$wpdb->insert(
			$sync_op_connections_table,
			array(
				'sync_operation_id' => $product_op_id,
				'connection_id'     => $images_connection_id,
			),
			array( '%d', '%d' )
		); //phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Required for migration.

		// Create page operation for Images connection.
		$page_op_uuid = wp_generate_uuid4();

		$wpdb->insert(
			$sync_operations_table,
			array(
				'uuid'           => $page_op_uuid,
				'supplier_id'    => $supplier_id,
				'operation_type' => 'performance-optimization',
				'operation'      => 'page',
				'is_active'      => 1,
			),
			array( '%s', '%d', '%s', '%s', '%d' )
		); //phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Required for migration.

		$page_op_id = $wpdb->insert_id;

		// Add page operation settings.
		$page_op_config = wp_json_encode(
			array(
				'type'        => 'regular',
				'location'    => 'query-parameter',
				'page_param'  => 'page',
				'limit_param' => '',
				'limit_value' => '',
			)
		);

		$wpdb->insert(
			$sync_settings_table,
			array(
				'operation_id'  => $page_op_id,
				'configuration' => $page_op_config,
			),
			array( '%d', '%s' )
		); //phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Required for migration.

		// Link page operation to Images connection.
		$wpdb->insert(
			$sync_op_connections_table,
			array(
				'sync_operation_id' => $page_op_id,
				'connection_id'     => $images_connection_id,
			),
			array( '%d', '%d' )
		); //phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Required for migration.
	}

	/**
	 * Migrate Midocean supplier: add "Midocean Branding" and "Midocean Branding Prices" connections.
	 *
	 * For existing Midocean suppliers created in a previous version (without these connections),
	 * creates the two branding connections and links them to product-operations so they can be
	 * used in sync. Authorization is shared at supplier level (same as other Midocean connections).
	 *
	 * Added in version 22.3.0
	 */
	public function migrate_midocean_branding_connections() {
		global $wpdb;

		$suppliers_table           = $wpdb->prefix . Model_Utils::TABLE_SUPPLIERS;
		$connections_table         = $wpdb->prefix . Model_Utils::TABLE_CONNECTIONS;
		$sync_operations_table     = $wpdb->prefix . Model_Utils::TABLE_SYNC_OPERATIONS;
		$sync_settings_table       = $wpdb->prefix . Model_Utils::TABLE_SYNC_SETTINGS;
		$sync_op_connections_table = $wpdb->prefix . Model_Utils::TABLE_SYNC_OPERATION_CONNECTIONS;

		$midocean_suppliers = $wpdb->get_results(
			"SELECT id FROM `{$suppliers_table}` WHERE app_type = 'midocean'",
			ARRAY_A
		); //phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared -- Required for migration.

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

		$branding_connections = array(
			array(
				'name'          => 'Midocean Branding',
				'priority'      => 4,
				'configuration' => array(
					'auth_enabled'           => true,
					'method'                 => 'GET',
					'endpoint'               => 'https://api.midocean.com/gateway/printdata/1.0',
					'headers'                => array(
						'Content-Type' => 'application/json',
						'Accept'       => 'application/json',
					),
					'body'                   => new \stdClass(),
					'prerequisites'          => new \stdClass(),
					'advanced_configuration' => new \stdClass(),
				),
			),
			array(
				'name'          => 'Midocean Branding Prices',
				'priority'      => 5,
				'configuration' => array(
					'auth_enabled'           => true,
					'method'                 => 'GET',
					'endpoint'               => 'https://api.midocean.com/gateway/printpricelist/2.0/',
					'headers'                => array(
						'Content-Type' => 'application/json',
						'Accept'       => 'application/json',
					),
					'body'                   => new \stdClass(),
					'prerequisites'          => new \stdClass(),
					'advanced_configuration' => new \stdClass(),
				),
			),
		);

		$product_op_config = array(
			'available_products'   => array(
				'add'                   => false,
				'update'                => true,
				'update_product_status' => false,
				'product_status'        => 'publish',
			),
			'unavailable_products' => array(
				'operation'   => 'none',
				'products_of' => 'none',
			),
		);

		foreach ( $midocean_suppliers as $row ) {
			$supplier_id = (int) $row['id'];

			foreach ( $branding_connections as $conn_def ) {
				$exists = $wpdb->get_var(
					$wpdb->prepare(
						"SELECT id FROM `{$connections_table}` WHERE supplier_id = %d AND name = %s",
						$supplier_id,
						$conn_def['name']
					)
				); //phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared -- Required for migration check.

				if ( $exists ) {
					continue;
				}

				$conn_uuid   = wp_generate_uuid4();
				$conn_config = wp_json_encode( $conn_def['configuration'] );

				$wpdb->insert(
					$connections_table,
					array(
						'uuid'          => $conn_uuid,
						'supplier_id'   => $supplier_id,
						'name'          => $conn_def['name'],
						'type'          => 'api',
						'is_active'     => 1,
						'is_primary'    => 0,
						'resource_type' => 'product',
						'priority'      => $conn_def['priority'],
						'configuration' => $conn_config,
					),
					array( '%s', '%d', '%s', '%s', '%d', '%d', '%s', '%d', '%s' )
				); //phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Required for migration.

				$connection_id = $wpdb->insert_id;
				if ( ! $connection_id ) {
					continue;
				}

				$op_uuid = wp_generate_uuid4();
				$wpdb->insert(
					$sync_operations_table,
					array(
						'uuid'           => $op_uuid,
						'supplier_id'    => $supplier_id,
						'operation_type' => 'data-sync',
						'operation'      => 'product-operations',
						'is_active'      => 1,
					),
					array( '%s', '%d', '%s', '%s', '%d' )
				); //phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Required for migration.

				$operation_id = $wpdb->insert_id;
				if ( $operation_id ) {
					$wpdb->insert(
						$sync_settings_table,
						array(
							'operation_id'  => $operation_id,
							'configuration' => wp_json_encode( $product_op_config ),
						),
						array( '%d', '%s' )
					); //phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Required for migration.

					$wpdb->insert(
						$sync_op_connections_table,
						array(
							'sync_operation_id' => $operation_id,
							'connection_id'     => $connection_id,
						),
						array( '%d', '%d' )
					); //phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Required for migration.
				}
			}
		}
	}

	/**
	 * Add audit_mode BOOLEAN column to the suppliers table if it does not exist.
	 *
	 * This is idempotent: safe to call on every version check.
	 *
	 * @return void
	 */
	public function add_audit_mode_column() {
		$table_name = $this->wpdb->prefix . Model_Utils::TABLE_SUPPLIERS;

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

		$column_exists = $this->wpdb->get_var(
			$this->wpdb->prepare(
				'SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
				WHERE TABLE_SCHEMA = %s
				AND TABLE_NAME = %s
				AND COLUMN_NAME = %s',
				DB_NAME,
				$table_name,
				'audit_mode'
			)
		); //phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Migration check.

		if ( ! $column_exists ) {
			$this->wpdb->query(
				"ALTER TABLE `{$table_name}` ADD COLUMN `audit_mode` BOOLEAN NOT NULL DEFAULT '0' AFTER `debug_mode`"
			); //phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared -- Schema migration.
		}
	}

	/**
	 * Migrate Amrod supplier: add "Amrod Branding Prices" connection.
	 *
	 * For existing Amrod suppliers created in a previous version (without this connection),
	 * creates the branding prices connection and links it to product-operations.
	 * Authorization is shared at supplier level (same as other Amrod connections).
	 *
	 * Added in version 22.3.0
	 */
	public function migrate_amrod_branding_prices_connection() {
		global $wpdb;

		$suppliers_table           = $wpdb->prefix . Model_Utils::TABLE_SUPPLIERS;
		$connections_table         = $wpdb->prefix . Model_Utils::TABLE_CONNECTIONS;
		$sync_operations_table     = $wpdb->prefix . Model_Utils::TABLE_SYNC_OPERATIONS;
		$sync_settings_table       = $wpdb->prefix . Model_Utils::TABLE_SYNC_SETTINGS;
		$sync_op_connections_table = $wpdb->prefix . Model_Utils::TABLE_SYNC_OPERATION_CONNECTIONS;

		$amrod_suppliers = $wpdb->get_results(
			"SELECT id FROM `{$suppliers_table}` WHERE app_type = 'amrod'",
			ARRAY_A
		); //phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared -- Required for migration.

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

		$connection_name = 'Amrod Branding Prices';
		$priority        = 8;
		$configuration  = array(
			'auth_enabled'           => true,
			'method'                 => 'GET',
			'endpoint'               => 'https://vendorapi.amrod.co.za/api/v1/BrandingPrices/',
			'headers'                => array(
				'Content-Type' => 'application/json',
				'Accept'       => 'application/json',
			),
			'body'                   => new \stdClass(),
			'prerequisites'          => new \stdClass(),
			'advanced_configuration' => new \stdClass(),
		);

		$product_op_config = array(
			'available_products'   => array(
				'add'                   => false,
				'update'                => true,
				'update_product_status' => false,
				'product_status'        => 'publish',
			),
			'unavailable_products' => array(
				'operation'   => 'none',
				'products_of' => 'none',
			),
		);

		foreach ( $amrod_suppliers as $row ) {
			$supplier_id = (int) $row['id'];

			$exists = $wpdb->get_var(
				$wpdb->prepare(
					"SELECT id FROM `{$connections_table}` WHERE supplier_id = %d AND name = %s",
					$supplier_id,
					$connection_name
				)
			); //phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared -- Required for migration check.

			if ( $exists ) {
				continue;
			}

			$conn_uuid   = wp_generate_uuid4();
			$conn_config = wp_json_encode( $configuration );

			$wpdb->insert(
				$connections_table,
				array(
					'uuid'          => $conn_uuid,
					'supplier_id'   => $supplier_id,
					'name'          => $connection_name,
					'type'          => 'api',
					'is_active'     => 1,
					'is_primary'    => 0,
					'resource_type' => 'product',
					'priority'      => $priority,
					'configuration' => $conn_config,
				),
				array( '%s', '%d', '%s', '%s', '%d', '%d', '%s', '%d', '%s' )
			); //phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Required for migration.

			$connection_id = $wpdb->insert_id;
			if ( ! $connection_id ) {
				continue;
			}

			$op_uuid = wp_generate_uuid4();
			$wpdb->insert(
				$sync_operations_table,
				array(
					'uuid'           => $op_uuid,
					'supplier_id'    => $supplier_id,
					'operation_type' => 'data-sync',
					'operation'      => 'product-operations',
					'is_active'      => 1,
				),
				array( '%s', '%d', '%s', '%s', '%d' )
			); //phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Required for migration.

			$operation_id = $wpdb->insert_id;
			if ( $operation_id ) {
				$wpdb->insert(
					$sync_settings_table,
					array(
						'operation_id'  => $operation_id,
						'configuration' => wp_json_encode( $product_op_config ),
					),
					array( '%d', '%s' )
				); //phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Required for migration.

				$wpdb->insert(
					$sync_op_connections_table,
					array(
						'sync_operation_id' => $operation_id,
						'connection_id'     => $connection_id,
					),
					array( '%d', '%d' )
				); //phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Required for migration.
			}
		}
	}

	/**
	 * Migrate Barron supplier: add "Branding" and "Branding Prices" connections.
	 *
	 * For existing Barron suppliers created in a previous version (without these connections),
	 * creates the two branding connections and links them to product-operations so they can be
	 * used in sync. Authorization is shared at supplier level (same as other Barron connections).
	 *
	 * Added in version 22.5.0
	 */
	public function migrate_barron_branding_connections() {
		global $wpdb;

		$suppliers_table           = $wpdb->prefix . Model_Utils::TABLE_SUPPLIERS;
		$connections_table         = $wpdb->prefix . Model_Utils::TABLE_CONNECTIONS;
		$sync_operations_table     = $wpdb->prefix . Model_Utils::TABLE_SYNC_OPERATIONS;
		$sync_settings_table       = $wpdb->prefix . Model_Utils::TABLE_SYNC_SETTINGS;
		$sync_op_connections_table = $wpdb->prefix . Model_Utils::TABLE_SYNC_OPERATION_CONNECTIONS;

		$barron_suppliers = $wpdb->get_results(
			"SELECT id FROM `{$suppliers_table}` WHERE app_type = 'barron'",
			ARRAY_A
		); //phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared -- Required for migration.

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

		$prerequisites = array(
			'product_response'                   => '',
			'variable_response'                => array(
				'parent_identifier' => 'StockCode',
			),
			'main_product_unique_identifier'     => 'StockCode+##_p##',
			'variable_product_unique_identifier' => 'StockID',
		);

		$branding_connections = array(
			array(
				'name'          => 'Branding',
				'priority'      => 2,
				'configuration' => array(
					'auth_enabled'           => true,
					'method'                 => 'GET',
					'endpoint'               => 'https://wslive.kevro.co.za/StockFeed.asmx/ListAllBrandingInformation',
					'query_params'           => array(
						'entityID'   => '<entity_id>',
						'username'   => '<username>',
						'psw'        => '<password>',
						'ReturnType' => 'json',
					),
					'headers'                => array(),
					'body'                   => array(
						'type' => 'xml',
						'data' => '',
					),
					'prerequisites'          => $prerequisites,
					'advanced_configuration' => array(),
				),
			),
			array(
				'name'          => 'Branding Prices',
				'priority'      => 3,
				'configuration' => array(
					'auth_enabled'           => true,
					'method'                 => 'GET',
					'endpoint'               => 'https://wslive.kevro.co.za/StockFeed.asmx/GetBrandingPricing',
					'query_params'           => array(
						'entityID'   => '<entity_id>',
						'username'   => '<username>',
						'psw'        => '<password>',
						'ReturnType' => 'json',
					),
					'headers'                => array(),
					'body'                   => array(
						'type' => 'xml',
						'data' => '',
					),
					'prerequisites'          => $prerequisites,
					'advanced_configuration' => array(),
				),
			),
		);

		$product_op_config = array(
			'available_products'   => array(
				'add'                   => false,
				'update'                => true,
				'update_product_status' => false,
				'product_status'        => 'publish',
			),
			'unavailable_products' => array(
				'operation'   => 'none',
				'products_of' => 'none',
			),
		);

		foreach ( $barron_suppliers as $row ) {
			$supplier_id = (int) $row['id'];

			foreach ( $branding_connections as $conn_def ) {
				$exists = $wpdb->get_var(
					$wpdb->prepare(
						"SELECT id FROM `{$connections_table}` WHERE supplier_id = %d AND name = %s",
						$supplier_id,
						$conn_def['name']
					)
				); //phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared -- Required for migration check.

				if ( $exists ) {
					continue;
				}

				$conn_uuid   = wp_generate_uuid4();
				$conn_config = wp_json_encode( $conn_def['configuration'] );

				$wpdb->insert(
					$connections_table,
					array(
						'uuid'          => $conn_uuid,
						'supplier_id'   => $supplier_id,
						'name'          => $conn_def['name'],
						'type'          => 'api',
						'is_active'     => 1,
						'is_primary'    => 0,
						'resource_type' => 'product',
						'priority'      => $conn_def['priority'],
						'configuration' => $conn_config,
					),
					array( '%s', '%d', '%s', '%s', '%d', '%d', '%s', '%d', '%s' )
				); //phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Required for migration.

				$connection_id = $wpdb->insert_id;
				if ( ! $connection_id ) {
					continue;
				}

				$op_uuid = wp_generate_uuid4();
				$wpdb->insert(
					$sync_operations_table,
					array(
						'uuid'           => $op_uuid,
						'supplier_id'    => $supplier_id,
						'operation_type' => 'data-sync',
						'operation'      => 'product-operations',
						'is_active'      => 1,
					),
					array( '%s', '%d', '%s', '%s', '%d' )
				); //phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Required for migration.

				$operation_id = $wpdb->insert_id;
				if ( $operation_id ) {
					$wpdb->insert(
						$sync_settings_table,
						array(
							'operation_id'  => $operation_id,
							'configuration' => wp_json_encode( $product_op_config ),
						),
						array( '%d', '%s' )
					); //phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Required for migration.

					$wpdb->insert(
						$sync_op_connections_table,
						array(
							'sync_operation_id' => $operation_id,
							'connection_id'     => $connection_id,
						),
						array( '%d', '%d' )
					); //phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Required for migration.
				}
			}
		}
	}
			
	/**
	 * Migrate Amrod supplier: upgrade "Updated Products" to GetUpdatedProductsAndBranding and prerequisites.
	 *
	 * Legacy configs used GetUpdatedProducts with variable_response.variations and an empty
	 * variable_product_unique_identifier. Current supplier JSON uses GetUpdatedProductsAndBranding,
	 * variable_path (not variations), and fullCode for both unique identifiers.
	 */
	public function migrate_amrod_updated_products_and_branding_connection() {
		global $wpdb;

		$suppliers_table   = $wpdb->prefix . Model_Utils::TABLE_SUPPLIERS;
		$connections_table = $wpdb->prefix . Model_Utils::TABLE_CONNECTIONS;

		$new_endpoint = 'https://vendorapi.amrod.co.za/api/v1/Products/GetUpdatedProductsAndBranding';

		$connections = $wpdb->get_results(
			"SELECT c.id, c.configuration FROM `{$connections_table}` c
			INNER JOIN `{$suppliers_table}` s ON s.id = c.supplier_id
			WHERE s.app_type = 'amrod' AND c.name = 'Updated Products'",
			ARRAY_A
		); //phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared -- Required for migration.

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

		foreach ( $connections as $conn_row ) {
			$config = json_decode( $conn_row['configuration'], true );
			if ( ! is_array( $config ) ) {
				continue;
			}

			$config_before = wp_json_encode( $config );

			$endpoint         = isset( $config['endpoint'] ) ? (string) $config['endpoint'] : '';
			$legacy_endpoint  = ( false !== strpos( $endpoint, 'GetUpdatedProducts' ) && false === strpos( $endpoint, 'GetUpdatedProductsAndBranding' ) );
			if ( $legacy_endpoint ) {
				$config['endpoint'] = $new_endpoint;
			}

			if ( ! isset( $config['prerequisites'] ) || ! is_array( $config['prerequisites'] ) ) {
				$config['prerequisites'] = array();
			}
			$pre = &$config['prerequisites'];

			if ( ! isset( $pre['variable_response'] ) || ! is_array( $pre['variable_response'] ) ) {
				$pre['variable_response'] = array();
			}
			$vr = &$pre['variable_response'];

			$had_variations_key = isset( $vr['variations'] );
			if ( $had_variations_key ) {
				$vr['variable_path'] = is_string( $vr['variations'] ) ? $vr['variations'] : 'variants';
				unset( $vr['variations'] );
			}

			$empty_variable_uid = ( ! isset( $pre['variable_product_unique_identifier'] ) || '' === $pre['variable_product_unique_identifier'] );
			$migrate_prereqs    = ( $legacy_endpoint || $had_variations_key || $empty_variable_uid );

			if ( $migrate_prereqs && ! isset( $vr['variable_path'] ) ) {
				$vr['variable_path'] = 'variants';
			}
			if ( $empty_variable_uid ) {
				$pre['variable_product_unique_identifier'] = 'fullCode';
			}
			if ( ! array_key_exists( 'product_response', $pre ) ) {
				$pre['product_response'] = '';
			}
			if ( $migrate_prereqs && ( ! isset( $pre['main_product_unique_identifier'] ) || '' === $pre['main_product_unique_identifier'] ) ) {
				$pre['main_product_unique_identifier'] = 'fullCode';
			}

			if ( wp_json_encode( $config ) === $config_before ) {
				continue;
			}

			$wpdb->update(
				$connections_table,
				array( 'configuration' => wp_json_encode( $config ) ),
				array( 'id' => (int) $conn_row['id'] ),
				array( '%s' ),
				array( '%d' )
			); //phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Required for migration.
		}
	}
}
