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

namespace MOWCPS\Common\Utils;

use WP_Error;
use MOWCPS\Common\Constants\Constants;
use MOWCPS\Common\Constants\Plugin_Filters;
use MOWCPS\Common\Handlers\SyncAnalytics\Sync_Analytics;
use MOWCPS\Common\Utils\Configuration_Loader;
use MOWCPS\Common\Models\Supplier_Model;
use MOWCPS\Common\Suppliers\Sns_Activewear;
use MOWCPS\Common\Constants\Plugin_Hooks;

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

/**
 * Class Model_Utils
 *
 * Utility functions for model operations
 */
class Model_Utils {

	/**
	 * Database table name constants
	 *
	 * Usage examples:
	 * - In constructor: $this->table_name = Model_Utils::get_table_name( Model_Utils::TABLE_SUPPLIERS );
	 * - In queries: "SELECT * FROM {$this->table_name} WHERE..."
	 * - Direct usage: Model_Utils::get_table_name( Model_Utils::TABLE_CONNECTIONS )
	 */
	const TABLE_SUPPLIERS                  = 'mowcps_suppliers';
	const TABLE_CONNECTIONS                = 'mowcps_connections';
	const TABLE_AUTHORIZATION              = 'mowcps_authorization';
	const TABLE_ATTRIBUTE_MAPPING          = 'mowcps_attribute_mapping';
	const TABLE_SYNC_OPERATIONS            = 'mowcps_sync_operations';
	const TABLE_SYNC_SETTINGS              = 'mowcps_sync_settings';
	const TABLE_SYNC_OPERATION_CONNECTIONS = 'mowcps_sync_operation_connections';
	const TABLE_SYNC_QUEUE                 = 'mowcps_sync_queue';
	const TABLE_SYNC_SCHEDULE              = 'mowcps_sync_schedule';
	const TABLE_SYNC_FILTERS               = 'mowcps_sync_filters';
	const TABLE_PREVIEW_FILTERS            = 'mowcps_preview_filters';
	const TABLE_PRODUCT_SYNC_AUDIT         = 'mowcps_product_sync_audit';
	const TABLE_ORDER_SYNC                 = 'mowcps_order_sync';
	const TABLE_ORDER_SYNC_AUDIT           = 'mowcps_order_sync_audit';
	const TABLE_ORDER_SYNC_PENDING         = 'mowcps_order_sync_pending';

	/**
	 * Get full table name with WordPress prefix
	 *
	 * @param string $table_constant Table constant from this class.
	 * @return string Full table name with prefix
	 */
	public static function get_table_name( $table_constant ) {
		global $wpdb;
		return $wpdb->prefix . $table_constant;
	}

	/**
	 * Add connection names to records that have connection_id field
	 *
	 * @param array $records Array of records with connection_id field.
	 * @return array Records with connection_uuid and connection_name added
	 */
	public static function add_connection_names( $records ) {
		if ( empty( $records ) ) {
			return $records;
		}

		// Extract unique connection IDs to minimize database queries.
		$connection_ids = array();
		foreach ( $records as $record ) {
			if ( isset( $record['connection_id'] ) && ! in_array( $record['connection_id'], $connection_ids, true ) ) {
				$connection_ids[] = $record['connection_id'];
			}
		}

		if ( empty( $connection_ids ) ) {
			return $records;
		}

		// Fetch connection names in a single query.
		global $wpdb;
		$table_name   = self::get_table_name( self::TABLE_CONNECTIONS );
		$placeholders = implode( ',', array_fill( 0, count( $connection_ids ), '%d' ) );
		$connections  = $wpdb->get_results( //phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom query.
			$wpdb->prepare(
				"SELECT id, uuid, name FROM `{$table_name}` WHERE id IN ($placeholders)", //phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber -- Dynamic placeholders and table name are safe
				...$connection_ids
			),
			ARRAY_A
		);

		// Create a lookup array for connection data.
		$connection_lookup = array();
		foreach ( $connections as $connection ) {
			$connection_lookup[ $connection['id'] ] = array(
				'connection_uuid' => $connection['uuid'],
				'connection_name' => $connection['name'],
			);
		}

		// Add connection data to each record.
		foreach ( $records as &$record ) {
			if ( isset( $record['connection_id'] ) && isset( $connection_lookup[ $record['connection_id'] ] ) ) {
				$record['connection_uuid'] = $connection_lookup[ $record['connection_id'] ]['connection_uuid'];
				$record['connection_name'] = $connection_lookup[ $record['connection_id'] ]['connection_name'];
			}
		}

		return $records;
	}

	/**
	 * Convert connection IDs to UUIDs in advanced configuration fields
	 *
	 * @param array $records Array of records with advanced_configuration field.
	 * @return array Records with connection IDs converted to UUIDs in advanced_configuration
	 */
	public static function convert_advanced_config_connection_ids_to_uuids( $records ) {
		if ( empty( $records ) ) {
			
			return $records;
		}

		// Handle single record (convert to array for processing).
		$is_single_record = ! isset( $records[0] ) || ! is_array( $records[0] );
		if ( $is_single_record ) {
			$records = array( $records );
		}

		// Extract unique connection IDs from advanced_configuration to minimize database queries.
		$connection_ids = array();
		foreach ( $records as $record ) {
			if ( isset( $record['advanced_configuration']['fetch_from_other_api']['connection'] ) ) {
				$connection_id = $record['advanced_configuration']['fetch_from_other_api']['connection'];
				if ( is_numeric( $connection_id ) && ! in_array( $connection_id, $connection_ids, true ) ) {
					$connection_ids[] = (int) $connection_id;
				}
			}
		}

		if ( empty( $connection_ids ) ) {
			return $is_single_record ? $records[0] : $records;
		}

		// Fetch connection UUIDs in a single query.
		global $wpdb;
		$table_name   = self::get_table_name( self::TABLE_CONNECTIONS );
		$placeholders = implode( ',', array_fill( 0, count( $connection_ids ), '%d' ) );
		$connections  = $wpdb->get_results( //phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom query.
			$wpdb->prepare(
				"SELECT id, uuid FROM `{$table_name}` WHERE id IN ($placeholders)", //phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber -- Dynamic placeholders and table name are safe
				...$connection_ids
			),
			ARRAY_A
		);

		// Create a lookup array for connection UUIDs.
		$connection_lookup = array();
		foreach ( $connections as $connection ) {
			$connection_lookup[ $connection['id'] ] = $connection['uuid'];
		}

		// Convert connection IDs to UUIDs in advanced_configuration.
		foreach ( $records as &$record ) {
			if ( isset( $record['advanced_configuration']['fetch_from_other_api']['connection'] ) ) {
				$connection_id = $record['advanced_configuration']['fetch_from_other_api']['connection'];
				if ( is_numeric( $connection_id ) && isset( $connection_lookup[ (int) $connection_id ] ) ) {
					$record['advanced_configuration']['fetch_from_other_api']['connection'] = $connection_lookup[ (int) $connection_id ];
				}
			}
		}

		// Return single record if input was single record.
		return $is_single_record ? $records[0] : $records;
	}

	/**
	 * Convert supplier UUIDs to IDs in configuration fields
	 *
	 * @param array $records Array of records with configuration field.
	 * @return array Records with supplier UUIDs converted to IDs in configuration
	 */
	public static function convert_supplier_uuids_to_ids_in_config( $records ) {
		if ( empty( $records ) ) {
			return $records;
		}

		// Handle single record (convert to array for processing).
		$is_single_record = ! isset( $records[0] ) || ! is_array( $records[0] );
		if ( $is_single_record ) {
			$records = array( $records );
		}

		// Extract unique supplier UUIDs from configuration to minimize database queries.
		$supplier_uuids = array();
		foreach ( $records as $record ) {
			// Check order_suppliers field (skip 'allow_existing_products').
			if ( isset( $record['configuration']['order_suppliers'] ) && is_array( $record['configuration']['order_suppliers'] ) ) {
				foreach ( $record['configuration']['order_suppliers'] as $supplier_uuid ) {
					if ( is_string( $supplier_uuid ) && 'allow_existing_products' !== $supplier_uuid && ! in_array( $supplier_uuid, $supplier_uuids, true ) ) {
						$supplier_uuids[] = $supplier_uuid;
					}
				}
			}

			// Check allowed_suppliers field (skip 'allow_all_website_products').
			if ( isset( $record['configuration']['allow_connections_for_outbound'] ) && is_array( $record['configuration']['allow_connections_for_outbound'] ) ) {
				foreach ( $record['configuration']['allow_connections_for_outbound'] as $supplier_uuid ) {
					if ( is_string( $supplier_uuid ) && 'allow_all_website_products' !== $supplier_uuid && ! in_array( $supplier_uuid, $supplier_uuids, true ) ) {
						$supplier_uuids[] = $supplier_uuid;
					}
				}
			}
		}

		if ( empty( $supplier_uuids ) ) {
			return $is_single_record ? $records[0] : $records;
		}

		// Fetch supplier IDs in a single query.
		global $wpdb;
		$table_name   = self::get_table_name( self::TABLE_SUPPLIERS );
		$placeholders = implode( ',', array_fill( 0, count( $supplier_uuids ), '%s' ) );
		$suppliers    = $wpdb->get_results( //phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom query.
			$wpdb->prepare(
				"SELECT id, uuid FROM `{$table_name}` WHERE uuid IN ($placeholders)", //phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber -- Dynamic placeholders and table name are safe
				...$supplier_uuids
			),
			ARRAY_A
		);

		// Create a lookup array for supplier IDs.
		$supplier_lookup = array();
		foreach ( $suppliers as $supplier ) {
			$supplier_lookup[ $supplier['uuid'] ] = (int) $supplier['id'];
		}

		// Convert supplier UUIDs to IDs in configuration.
		foreach ( $records as &$record ) {
			// Convert order_suppliers.
			if ( isset( $record['configuration']['order_suppliers'] ) && is_array( $record['configuration']['order_suppliers'] ) ) {
				$converted_suppliers = array();
				foreach ( $record['configuration']['order_suppliers'] as $supplier_uuid ) {
					if ( 'allow_existing_products' === $supplier_uuid ) {
						// Keep the special key as-is.
						$converted_suppliers[] = $supplier_uuid;
					} elseif ( is_string( $supplier_uuid ) && isset( $supplier_lookup[ $supplier_uuid ] ) ) {
						$converted_suppliers[] = $supplier_lookup[ $supplier_uuid ];
					}
				}
				$record['configuration']['order_suppliers'] = $converted_suppliers;
			}

			// Convert allowed_suppliers.
			if ( isset( $record['configuration']['allow_connections_for_outbound'] ) && is_array( $record['configuration']['allow_connections_for_outbound'] ) ) {
				$converted_suppliers = array();
				foreach ( $record['configuration']['allow_connections_for_outbound'] as $supplier_uuid ) {
					if ( 'allow_all_website_products' === $supplier_uuid ) {
						// Keep the special key as-is.
						$converted_suppliers[] = $supplier_uuid;
					} elseif ( is_string( $supplier_uuid ) && isset( $supplier_lookup[ $supplier_uuid ] ) ) {
						$converted_suppliers[] = $supplier_lookup[ $supplier_uuid ];
					}
				}
				$record['configuration']['allow_connections_for_outbound'] = $converted_suppliers;
			}
		}

		// Return single record if input was single record.
		return $is_single_record ? $records[0] : $records;
	}

	/**
	 * Convert supplier IDs to UUIDs in configuration fields
	 *
	 * @param array $records Array of records with configuration field.
	 * @return array Records with supplier IDs converted to UUIDs in configuration
	 */
	public static function convert_supplier_ids_to_uuids_in_config( $records ) {
		if ( empty( $records ) ) {
			return $records;
		}

		// Handle single record (convert to array for processing).
		$is_single_record = ! isset( $records[0] ) || ! is_array( $records[0] );
		if ( $is_single_record ) {
			$records = array( $records );
		}

		// Extract unique supplier IDs from configuration to minimize database queries.
		$supplier_ids = array();
		foreach ( $records as $record ) {
			// Check order_suppliers field.
			if ( isset( $record['configuration']['order_suppliers'] ) && is_array( $record['configuration']['order_suppliers'] ) ) {
				foreach ( $record['configuration']['order_suppliers'] as $supplier_id ) {
					if ( is_numeric( $supplier_id ) && ! in_array( (int) $supplier_id, $supplier_ids, true ) ) {
						$supplier_ids[] = (int) $supplier_id;
					}
				}
			}

			// Check allowed_suppliers field.
			if ( isset( $record['configuration']['allow_connections_for_outbound'] ) && is_array( $record['configuration']['allow_connections_for_outbound'] ) ) {
				foreach ( $record['configuration']['allow_connections_for_outbound'] as $supplier_id ) {
					if ( is_numeric( $supplier_id ) && ! in_array( (int) $supplier_id, $supplier_ids, true ) ) {
						$supplier_ids[] = (int) $supplier_id;
					}
				}
			}
		}

		if ( empty( $supplier_ids ) ) {
			return $is_single_record ? $records[0] : $records;
		}

		// Fetch supplier UUIDs in a single query.
		global $wpdb;
		$table_name   = self::get_table_name( self::TABLE_SUPPLIERS );
		$placeholders = implode( ',', array_fill( 0, count( $supplier_ids ), '%d' ) );
		$suppliers    = $wpdb->get_results( //phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom query.
			$wpdb->prepare(
				"SELECT id, uuid FROM `{$table_name}` WHERE id IN ($placeholders)", //phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber -- Dynamic placeholders and table name are safe
				...$supplier_ids
			),
			ARRAY_A
		);

		// Create a lookup array for supplier UUIDs.
		$supplier_lookup = array();
		foreach ( $suppliers as $supplier ) {
			$supplier_lookup[ (int) $supplier['id'] ] = $supplier['uuid'];
		}

		// Convert supplier IDs to UUIDs in configuration.
		foreach ( $records as &$record ) {
			// Convert order_suppliers.
			if ( isset( $record['configuration']['order_suppliers'] ) && is_array( $record['configuration']['order_suppliers'] ) ) {
				$converted_suppliers = array();
				foreach ( $record['configuration']['order_suppliers'] as $supplier_id ) {
					if ( 'allow_existing_products' === $supplier_id ) {
						// Keep the special key as-is.
						$converted_suppliers[] = $supplier_id;
					} elseif ( is_numeric( $supplier_id ) && isset( $supplier_lookup[ (int) $supplier_id ] ) ) {
						$converted_suppliers[] = $supplier_lookup[ (int) $supplier_id ];
					}
				}
				$record['configuration']['order_suppliers'] = $converted_suppliers;
			}

			// Convert allowed_suppliers.
			if ( isset( $record['configuration']['allow_connections_for_outbound'] ) && is_array( $record['configuration']['allow_connections_for_outbound'] ) ) {
				$converted_suppliers = array();
				foreach ( $record['configuration']['allow_connections_for_outbound'] as $supplier_id ) {
					if ( 'allow_all_website_products' === $supplier_id ) {
						// Keep the special key as-is.
						$converted_suppliers[] = $supplier_id;
					} elseif ( is_numeric( $supplier_id ) && isset( $supplier_lookup[ (int) $supplier_id ] ) ) {
						$converted_suppliers[] = $supplier_lookup[ (int) $supplier_id ];
					}
				}
				$record['configuration']['allow_connections_for_outbound'] = $converted_suppliers;
			}
		}

		// Return single record if input was single record.
		return $is_single_record ? $records[0] : $records;
	}

	/**
	 * Decode JSON fields in records
	 *
	 * @param array|null $records Single record or array of records.
	 * @param array      $json_fields Array of field names that should be JSON decoded.
	 * @return array|null Processed records with JSON fields decoded
	 */
	public static function decode_json_fields( $records, $json_fields = array() ) {
		if ( empty( $records ) || empty( $json_fields ) ) {
			return $records;
		}

		// Handle single record (convert to array for processing).
		$is_single_record = ! isset( $records[0] ) || ! is_array( $records[0] );
		if ( $is_single_record ) {
			$records = array( $records );
		}

		// Process each record.
		foreach ( $records as &$record ) {
			if ( ! is_array( $record ) ) {
				continue;
			}

			foreach ( $json_fields as $field ) {
				if ( isset( $record[ $field ] ) && is_string( $record[ $field ] ) ) {
					$decoded = json_decode( $record[ $field ], true );
					if ( json_last_error() === JSON_ERROR_NONE ) {
						$record[ $field ] = self::preserve_non_json_data( $decoded );
					}
				}
			}
		}

		// Return single record if input was single record.
		return $is_single_record ? $records[0] : $records;
	}

	/**
	 * Preserve non-JSON data types in configuration body
	 *
	 * @param array $config Decoded configuration array.
	 * @return array Configuration with preserved data types.
	 */
	private static function preserve_non_json_data( $config ) {
		if ( ! is_array( $config ) ) {
			return $config;
		}

		// Handle body data preservation based on type.
		if ( isset( $config['body'] ) && is_array( $config['body'] ) ) {
			$body_type = $config['body']['type'] ?? 'json';
			$body_data = $config['body']['data'] ?? null;

			// For XML, GraphQL, and other non-JSON types, preserve as string.
			if ( in_array( $body_type, array( 'xml', 'graphql', 'raw' ), true ) && null !== $body_data ) {
				// If the data was incorrectly JSON decoded, it might be an array.
				// Convert it back to string if needed.
				if ( is_array( $body_data ) ) {
					// This handles cases where XML was accidentally parsed as JSON.
					$config['body']['data'] = self::restore_xml_from_array( $body_data );
				}
			}
		}

		// Handle position data preservation based on type.
		if ( isset( $config['position'] ) && is_array( $config['position'] ) ) {
			$position_type = $config['position']['type'] ?? 'json';
			$position_data = $config['position']['data'] ?? null;

			// For XML, GraphQL, and other non-JSON types, preserve as string.
			if ( in_array( $position_type, array( 'xml', 'graphql', 'raw' ), true ) && is_array( $position_data ) ) {
				// Handle position data which is structured as array with textarea elements.
				foreach ( $position_data as $index => $item ) {
					if ( is_array( $item ) && isset( $item['textarea'] ) && is_array( $item['textarea'] ) ) {
						// Restore XML from accidentally parsed array.
						$config['position']['data'][ $index ]['textarea'] = self::restore_xml_from_array( $item['textarea'] );
					}
				}
			}
		}

		return $config;
	}

	/**
	 * Attempt to restore XML from accidentally parsed array
	 *
	 * @param array $data Array that should be XML string.
	 * @return string Restored XML or original data as string.
	 */
	private static function restore_xml_from_array( $data ) {
		if ( is_array( $data ) ) {
			return wp_json_encode( $data );
		}

		return (string) $data;
	}

	/**
	 * Safely encode configuration data while preserving XML and other non-JSON body types
	 *
	 * @param array $configuration Configuration array to encode.
	 * @return string JSON encoded configuration with preserved data types.
	 */
	public static function encode_configuration_safely( $configuration ) {
		if ( ! is_array( $configuration ) ) {
			return wp_json_encode( $configuration );
		}

		// Create a copy to avoid modifying the original.
		$config_copy = $configuration;

		// Handle body data preservation during encoding.
		if ( isset( $config_copy['body'] ) && is_array( $config_copy['body'] ) ) {
			$body_type = $config_copy['body']['type'] ?? 'json';
			$body_data = $config_copy['body']['data'] ?? null;

			// For XML, GraphQL, and other non-JSON types, ensure data remains as string.
			if ( in_array( $body_type, array( 'xml', 'graphql', 'raw' ), true ) && null !== $body_data ) {
				// Ensure the body data is a string for non-JSON types.
				if ( ! is_string( $body_data ) ) {
					$config_copy['body']['data'] = (string) $body_data;
				}

				// Remove unnecessary whitespace for XML body.
				if ( 'xml' === $body_type && is_string( $config_copy['body']['data'] ) ) {
					$config_copy['body']['data'] = trim( $config_copy['body']['data'] );
				}
			}
		}

		// Handle position data preservation during encoding.
		if ( isset( $config_copy['position'] ) && is_array( $config_copy['position'] ) ) {
			$position_type = $config_copy['position']['type'] ?? 'json';
			$position_data = $config_copy['position']['data'] ?? null;

			// For XML, GraphQL, and other non-JSON types, ensure textarea data remains as string.
			if ( in_array( $position_type, array( 'xml', 'graphql', 'raw' ), true ) && is_array( $position_data ) ) {
				// Handle position data which is structured as array with textarea elements.
				foreach ( $position_data as $index => $item ) {
					if ( is_array( $item ) && isset( $item['textarea'] ) ) {
						if ( ! is_string( $item['textarea'] ) ) {
							// Ensure the textarea data is a string for non-JSON types.
							$config_copy['position']['data'][ $index ]['textarea'] = (string) $item['textarea'];
						}

						// Remove unnecessary whitespace for XML position data.
						if ( 'xml' === $position_type && is_string( $config_copy['position']['data'][ $index ]['textarea'] ) ) {
							$config_copy['position']['data'][ $index ]['textarea'] = trim( $config_copy['position']['data'][ $index ]['textarea'] );
						}
					}
				}
			}
		}

		return wp_json_encode( $config_copy );
	}

	/**
	 * Resolve mixed supplier identifiers (UUID or numeric ID) to numeric supplier IDs.
	 * Product meta stores numeric ID; API often sends UUIDs.
	 *
	 * @param array $identifiers Array of supplier UUIDs (string) or numeric IDs.
	 * @return array Numeric supplier IDs (from mowcps_suppliers.id).
	 */
	private static function resolve_supplier_identifiers_to_ids( $identifiers ) {
		global $wpdb;

		if ( empty( $identifiers ) || ! is_array( $identifiers ) ) {
			return array();
		}
		$ids = array();
		$uuids = array();
		foreach ( $identifiers as $val ) {
			$val = sanitize_text_field( $val );
			if ( '' === $val ) {
				continue;
			}
			if ( is_numeric( $val ) && (int) $val > 0 ) {
				$ids[] = (int) $val;
				continue;
			}
			if ( strlen( $val ) === 36 && strpos( $val, '-' ) !== false ) {
				$uuids[] = $val;
			}
		}
		if ( ! empty( $uuids ) ) {
			$uuid_placeholders = implode( ',', array_fill( 0, count( $uuids ), '%s' ) );
			$resolved = $wpdb->get_col( $wpdb->prepare(
				"SELECT id FROM {$wpdb->prefix}mowcps_suppliers WHERE uuid IN ($uuid_placeholders)",
				...$uuids
			) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared -- Resolve UUIDs to IDs.
			$ids = array_merge( $ids, array_map( 'intval', (array) $resolved ) );
		}
		return array_values( array_unique( array_filter( $ids ) ) );
	}

	/**
	 * Delete all products associated with supplier IDs using optimized SQL.
	 *
	 * Finds all products and variations linked to suppliers and deletes them
	 * using direct SQL queries (18-52x faster than WooCommerce method).
	 * Accepts mixed identifiers (UUID or numeric ID); resolves to numeric IDs for query/cron.
	 *
	 * @param array $supplier_ids Array of supplier IDs (numeric) or UUIDs (e.g. from API).
	 * @return array|WP_Error Deletion results with counts and status, or error object.
	 */
	public static function delete_all_products_by_mixed_identifiers( $supplier_ids ) {
		global $wpdb;

		if ( empty( $supplier_ids ) ) {
			return array( 'message' => 'No suppliers provided for deletion.' );
		}

		$supplier_ids = self::resolve_supplier_identifiers_to_ids( $supplier_ids );

		if ( empty( $supplier_ids ) ) {
			return array( 'message' => 'No valid supplier IDs found for deletion.' );
		}

		$query_condition = $wpdb->prepare(
			'pm.meta_value IN (' . implode( ',', array_fill( 0, count( $supplier_ids ), '%s' ) ) . ')',
			...$supplier_ids
		);

		// Count total items and main products only (no full ID list loaded)
		$count_items_query = "
			SELECT COUNT(*) FROM $wpdb->postmeta pm
			INNER JOIN $wpdb->posts p ON pm.post_id = p.ID
			WHERE pm.meta_key = 'mowcps_connection_name_product_created' AND $query_condition
		";
		$total_items = (int) $wpdb->get_var( $count_items_query ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared -- Count by criteria.

		if ( $total_items <= 0 ) {
			return array( 'message' => 'No products found for the selected suppliers.' );
		}

		$count_products_query = "
			SELECT COUNT(*) FROM $wpdb->postmeta pm
			INNER JOIN $wpdb->posts p ON pm.post_id = p.ID
			WHERE pm.meta_key = 'mowcps_connection_name_product_created' AND $query_condition
			AND p.post_type = 'product'
		";
		$product_count = (int) $wpdb->get_var( $count_products_query ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared -- Count products only.

		self::start_bulk_deletion_cron( $supplier_ids, $product_count, $total_items );

		return array(
			'message'        => sprintf(
				'Bulk deletion started. %d products (%d total items) will be deleted in the background (1000 per run).',
				$product_count,
				$total_items
			),
			'total_products' => $product_count,
			'total_items'    => $total_items,
			'status'         => 'started',
		);
	}

	/**
	 * Update default supplier connections and authorization with new details
	 *
	 * @param array $supplier Supplier data with ID and details.
	 * @return bool True on success, false on failure.
	 */
	public static function update_default_supplier_configurations( $supplier ) {
		if ( ! $supplier || Constants::SUPPLIER_APP_TYPE_CUSTOM === $supplier['app_type'] ) {
			return false;
		}

		global $wpdb;
		$supplier_id = $supplier['id'];
		$app_type    = $supplier['app_type'];
		$details     = $supplier['details'];

		if ( empty( $details ) ) {
			return false;
		}

		// Load default supplier template data.
		$template_file = MOWCPS_PLUGIN_DIR . 'resources' . DIRECTORY_SEPARATOR . 'supplier-data' . DIRECTORY_SEPARATOR . $app_type . '.json';
		if ( ! file_exists( $template_file ) ) {
			return false;
		}

		$template_data = Configuration_Loader::load_supplier_config( $app_type );
		if ( is_wp_error( $template_data ) || empty( $template_data ) ) {
			return false;
		}

		// Prepare dynamic parameters including supplier UUID for replacement.
		$dynamic_params = $details;
		$supplier_uuid  = $supplier['uuid'] ?? '';
		if ( ! empty( $supplier_uuid ) ) {
			$dynamic_params['supplier_id'] = $supplier_uuid;
		}

		// For Shopify: ensure shopify_auth_type is always present in dynamic params so
		// the <shopify_auth_type> placeholder in shopify.json is always resolved.
		// This covers both new suppliers and legacy suppliers that pre-date the OAuth feature.
		if ( Constants::SUPPLIER_APP_TYPE_SHOPIFY === $app_type && ! array_key_exists( 'shopify_auth_type', $dynamic_params ) ) {
			$dynamic_params['shopify_auth_type'] = ! empty( $dynamic_params['use_oauth'] ) ? 'oauth2' : 'api-key';
		}

		// Process template data with dynamic parameters using shared function.
		$processed_template = Configuration_Loader::replace_dynamic_parameters( $template_data, $dynamic_params );

		// Update authorization if template has authorization settings.
		if ( isset( $processed_template['authorization'] ) ) {
			$auth_template = $processed_template['authorization'];

			$authorization_data = array(
				'auth_type'     => $auth_template['auth_type'],
				'auth_settings' => $auth_template['auth_settings'],
			);

			// Update or create authorization.
			$existing_auth = $wpdb->get_var( //phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom query.
				$wpdb->prepare(
					"SELECT id FROM {$wpdb->prefix}mowcps_authorization WHERE supplier_id = %d",
					$supplier_id
				)
			);

			if ( $existing_auth ) {
				$wpdb->update( //phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom query.
					$wpdb->prefix . 'mowcps_authorization',
					array(
						'auth_type'     => $authorization_data['auth_type'],
						'auth_settings' => wp_json_encode( $authorization_data['auth_settings'] ),
					),
					array( 'supplier_id' => $supplier_id ),
					array( '%s', '%s' ),
					array( '%d' )
				);
			}
		}

		// Update connections if template has connections.
		if ( isset( $processed_template['connections'] ) ) {
			foreach ( $processed_template['connections'] as $connection_template ) {
				// Skip SNS categories connection as per user requirement.
				if ( Constants::SUPPLIER_APP_TYPE_SNS_ACTIVEWEAR === $app_type && 'Categories' === $connection_template['name'] ) {
					continue;
				}

				// Get existing connection by name and supplier_id.
				$existing_connection = $wpdb->get_row( //phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom query.
					$wpdb->prepare(
						"SELECT uuid, configuration FROM {$wpdb->prefix}mowcps_connections WHERE supplier_id = %d AND name = %s",
						$supplier_id,
						$connection_template['name']
					)
				);

				if ( $existing_connection ) {
					// Update existing connection configuration with processed template.
					$updated_config = $connection_template['configuration'];

					$wpdb->update( //phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom query.
						$wpdb->prefix . 'mowcps_connections',
						array( 'configuration' => self::encode_configuration_safely( $updated_config ) ),
						array( 'uuid' => $existing_connection->uuid ),
						array( '%s' ),
						array( '%s' )
					);
				}
			}
		}

		// Update Toptex attribute mappings with preferred language.
		if ( Constants::SUPPLIER_APP_TYPE_TOPTEX === $app_type ) {
			$preferred_language = isset( $details['preferred_language'] ) ? $details['preferred_language'] : 'en';

			// Define the specific attribute mappings that need language replacement.
			$language_dependent_mappings = array(
				array(
					'wc_attribute'       => 'description',
					'supplier_attribute' => 'description->' . $preferred_language . '+##<br>##+##<ul><li>##+salesArguments->' . $preferred_language . '+##</li><li>##+salesArguments2->' . $preferred_language . '+##</li><li>##+salesArguments3->' . $preferred_language . '+##</li></ul>##',
				),
				array(
					'wc_attribute'       => 'name',
					'supplier_attribute' => 'designation->' . $preferred_language,
				),
				array(
					'wc_attribute'       => 'product_category',
					'supplier_attribute' => 'family->' . $preferred_language,
				),
				array(
					'wc_attribute'       => '##Color##',
					'supplier_attribute' => 'colors->' . $preferred_language,
				),
				array(
					'wc_attribute'       => '##Main Materials##',
					'supplier_attribute' => 'mainMaterials->0->' . $preferred_language,
				),
				array(
					'wc_attribute'       => '##Style##',
					'supplier_attribute' => 'style->0->' . $preferred_language,
				),
			);

			// Build CASE statement for bulk update in a single query.
			$case_parts   = array();
			$prepare_args = array();

			foreach ( $language_dependent_mappings as $mapping ) {
				$case_parts[]   = 'WHEN %s THEN %s';
				$prepare_args[] = $mapping['wc_attribute'];
				$prepare_args[] = $mapping['supplier_attribute'];
			}

			$case_statement = implode( ' ', $case_parts );

			// Build IN clause placeholders.
			$wc_attributes   = array_column( $language_dependent_mappings, 'wc_attribute' );
			$in_placeholders = implode( ',', array_fill( 0, count( $wc_attributes ), '%s' ) );

			// Add supplier_id to prepare arguments.
			$prepare_args[] = $supplier_id;

			// Add wc_attribute values for IN clause.
			foreach ( $wc_attributes as $wc_attr ) {
				$prepare_args[] = $wc_attr;
			}

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

			// Execute single bulk update query using CASE statement.
			$wpdb->query( //phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared -- Single bulk update with CASE statement for performance.
				$wpdb->prepare(
					"UPDATE `{$table_name}` 
					SET supplier_attribute = CASE wc_attribute 
						{$case_statement}
					END
					WHERE supplier_id = %d 
					AND wc_attribute IN ({$in_placeholders})", //phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Placeholders are dynamically generated but safe.
					$prepare_args
				)
			);
		}

		// Update order sync if template has order_sync (handled by premium hook).
		if ( isset( $processed_template['order_sync'] ) && has_filter( Plugin_Filters::FILTER_UPDATE_DEFAULT_ORDER_SYNC ) ) {
			do_action(
				Plugin_Filters::FILTER_UPDATE_DEFAULT_ORDER_SYNC,
				$supplier,
				$processed_template['order_sync'],
				$processed_template
			);
		}

		return true;
	}


	/**
	 * Identify if the given parameter is a UUID or ID
	 *
	 * @param string|int $identifier UUID or ID.
	 * @return array|WP_Error Array with type and value, or error.
	 */
	public static function identify_identifier( $identifier ) {
		// Numeric values are treated as database IDs.
		if ( is_numeric( $identifier ) ) {
			return array(
				'type'  => 'id',
				'value' => (int) $identifier,
			);
		}

		// Non-numeric strings are treated as UUIDs.
		if ( is_string( $identifier ) ) {
			return array(
				'type'  => 'uuid',
				'value' => $identifier,
			);
		}

		return new WP_Error(
			'ERROR_INVALID_IDENTIFIER',
			'Invalid identifier. Must be UUID string or numeric ID.',
			array( 'status' => 400 )
		);
	}

	/**
	 * Get the value from the wp_options table.
	 *
	 * @param string $key Option key.
	 * @param mixed  $default_value Default value.
	 * @return mixed
	 */
	public static function get_option( $key, $default_value = false ) {
		$return_value = get_option( $key, $default_value );
		return $return_value;
	}

	/**
	 * Store the value in the wp_options table.
	 *
	 * @param string $key   Option key.
	 * @param mixed  $value Option value.
	 *
	 * @return boolean
	 */
	public static function update_option( $key, $value ) {
		$return_value = update_option( $key, $value );
		return $return_value;
	}

	/**
	 * Delete the key and value from the wp_options table.
	 *
	 * @param  string $key Option key.
	 * @return boolean
	 */
	public static function delete_option( $key ) {
		$return_value = delete_option( $key );
		return $return_value;
	}

	/**
	 * Remove ID fields from API response data and format data types
	 *
	 * @param array|null $data Single record or array of records.
	 * @param array      $fields_to_remove List of field names to remove. Default: ['id', 'supplier_id', 'connection_id'].
	 * @return array|null Sanitized and formatted data.
	 */
	public static function sanitize_for_api_response( $data, $fields_to_remove = array( 'id', 'supplier_id', 'connection_id' ) ) {
		if ( empty( $data ) ) {
			return $data;
		}

		// Handle single record.
		if ( ! isset( $data[0] ) || ! is_array( $data[0] ) ) {
			$sanitized = self::remove_fields_from_record( $data, $fields_to_remove );
			return self::format_record_types( $sanitized );
		}

		// Handle array of records.
		$sanitized_data = array();
		foreach ( $data as $record ) {
			$sanitized        = self::remove_fields_from_record( $record, $fields_to_remove );
			$sanitized_data[] = self::format_record_types( $sanitized );
		}

		return $sanitized_data;
	}

	/**
	 * Remove specific fields from a single record
	 *
	 * @param array $record Single record.
	 * @param array $fields_to_remove List of field names to remove.
	 * @return array Record without the specified fields.
	 */
	private static function remove_fields_from_record( $record, $fields_to_remove ) {
		if ( ! is_array( $record ) ) {
			return $record;
		}

		foreach ( $fields_to_remove as $field ) {
			unset( $record[ $field ] );
		}

		return $record;
	}

	/**
	 * Format a single record's data types
	 *
	 * @param array $record Single record.
	 * @return array Record with properly formatted data types.
	 */
	private static function format_record_types( $record ) {
		if ( ! is_array( $record ) ) {
			return $record;
		}

		// Define boolean fields by table/context.
		$boolean_fields = array(
			'is_active',
			'debug_mode',
			'audit_mode',
			'is_primary',
			'initiate_sync',
			'is_conditional_sync_enabled',
		);

		// Define integer fields that should be cast to proper integers.
		$integer_fields = array(
			'priority',
			'supplier_id',
			'connection_id',
		);

		// Define JSON fields that should be decoded from JSON strings to arrays/objects.
		$json_fields = array(
			'details',
			'auth_settings',
			'advanced_configuration',
			'configuration',
			'filters',
		);

		foreach ( $record as $key => $value ) {
			// Convert boolean fields.
			if ( in_array( $key, $boolean_fields, true ) ) {
				$record[ $key ] = (bool) $value;
			} elseif ( in_array( $key, $integer_fields, true ) ) {
				// Convert integer fields.
				$record[ $key ] = (int) $value;
			} elseif ( in_array( $key, $json_fields, true ) && is_string( $value ) ) {
				// Decode JSON fields for proper API response formatting.
				$decoded = json_decode( $value, true );
				if ( json_last_error() === JSON_ERROR_NONE ) {
					$record[ $key ] = self::convert_nested_booleans( $decoded );
				}
			}
		}

		return $record;
	}

	/**
	 * Recursively convert boolean-like values in nested arrays/objects
	 *
	 * @param mixed $data Data to process.
	 * @return mixed Data with boolean strings converted to proper booleans.
	 */
	private static function convert_nested_booleans( $data ) {
		if ( is_array( $data ) ) {
			$converted = array();
			foreach ( $data as $key => $value ) {
				$converted[ $key ] = self::convert_nested_booleans( $value );
			}
			return $converted;
		} elseif ( is_string( $data ) ) {
			// Convert boolean-like strings to actual booleans.
			$lower_value = strtolower( trim( $data ) );
			if ( in_array( $lower_value, array( 'true', '1' ), true ) ) {
				return true;
			} elseif ( in_array( $lower_value, array( 'false', '0' ), true ) ) {
				return false;
			}
			// Return the original string if it's not a boolean-like value.
			return $data;
		}

		// Return other data types as-is (integers, floats, null, etc.).
		return $data;
	}

	/**
	 * Clear option specific cache.
	 *
	 * @param string $key option key.
	 *
	 * @return mixed
	 */
	public static function clear_option_cache( $key ) {
		$GLOBALS['wp_object_cache']->delete( $key, 'options' );
	}

	/**
	 * Delete all products associated with supplier UUIDs using optimized SQL.
	 *
	 * @param array $supplier_uuids Supplier UUIDs to delete products for.
	 * @return array|WP_Error Deletion results or error.
	 */
	public static function delete_all_products( $supplier_uuids ) {
		global $wpdb;

		try {
			if ( empty( $supplier_uuids ) ) {
				return;
			}

			// Convert UUIDs to supplier IDs
			$uuid_placeholders  = implode( ',', array_fill( 0, count( $supplier_uuids ), '%s' ) );
			$supplier_ids_query = $wpdb->prepare(
				"SELECT id FROM {$wpdb->prefix}mowcps_suppliers WHERE uuid IN ($uuid_placeholders)",
				...$supplier_uuids
			);
			$supplier_ids       = $wpdb->get_col( $supplier_ids_query ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared -- Custom query.

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

			$query_condition = $wpdb->prepare(
				'pm.meta_value IN (' . implode( ',', array_fill( 0, count( $supplier_ids ), '%s' ) ) . ')',
				...$supplier_ids
			);

			// Count total items and main products only (no full ID list loaded)
			$count_items_query = "
				SELECT COUNT(*) FROM $wpdb->postmeta pm
				INNER JOIN $wpdb->posts p ON pm.post_id = p.ID
				WHERE pm.meta_key = 'mowcps_connection_name_product_created' AND $query_condition
			";
			$total_items = (int) $wpdb->get_var( $count_items_query ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared -- Count by criteria.

			if ( $total_items <= 0 ) {
				return;
			}

			$count_products_query = "
				SELECT COUNT(*) FROM $wpdb->postmeta pm
				INNER JOIN $wpdb->posts p ON pm.post_id = p.ID
				WHERE pm.meta_key = 'mowcps_connection_name_product_created' AND $query_condition
				AND p.post_type = 'product'
			";
			$product_count = (int) $wpdb->get_var( $count_products_query ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared -- Count products only.

			self::start_bulk_deletion_cron( $supplier_ids, $product_count, $total_items );

			return array(
				'message'        => sprintf(
					'Bulk deletion started. %d products (%d total items) will be deleted in the background (1000 per run).',
					$product_count,
					$total_items
				),
				'total_products' => $product_count,
				'total_items'    => $total_items,
				'status'         => 'started',
			);

		} catch ( \Exception $e ) {
			return new \WP_Error(
				'product_deletion_failed',
				'Error starting product deletion: ' . $e->getMessage()
			);
		}
	}

	/**
	 * Fetch one batch of product/variation post IDs by supplier IDs (for bulk deletion).
	 * Uses LIMIT only; caller deletes the batch so next call returns the next batch.
	 *
	 * @param array $supplier_ids Supplier IDs (meta_value for mowcps_connection_name_product_created).
	 * @param int   $limit        Max number of IDs to return.
	 * @return array List of post_id values.
	 */
	private static function get_product_ids_by_supplier_ids_batch( $supplier_ids, $limit ) {
		global $wpdb;

		if ( empty( $supplier_ids ) ) {
			return array();
		}
		$supplier_ids = array_map( 'intval', $supplier_ids );
		$supplier_ids = array_filter( $supplier_ids );
		if ( empty( $supplier_ids ) ) {
			return array();
		}

		$query_condition = $wpdb->prepare(
			'pm.meta_value IN (' . implode( ',', array_fill( 0, count( $supplier_ids ), '%s' ) ) . ')',
			...$supplier_ids
		);
		$limit = max( 0, (int) $limit );

		$query = "SELECT pm.post_id
			FROM $wpdb->postmeta pm
			INNER JOIN $wpdb->posts p ON pm.post_id = p.ID
			WHERE pm.meta_key = 'mowcps_connection_name_product_created'
			AND $query_condition
			ORDER BY pm.post_id
			LIMIT %d";
		$query = $wpdb->prepare( $query, $limit ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- Condition built with prepare above; limit is integer.

		return array_map( 'intval', (array) $wpdb->get_col( $query ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared -- Batch fetch by criteria.
	}

	/**
	 * Start cron-based bulk deletion: store only criteria (supplier_ids) under a key derived from supplier IDs, schedule first batch.
	 * Option key is derived from supplier IDs so different supplier sets use different queues; cron receives supplier IDs as argument.
	 *
	 * @param array $supplier_ids   Supplier IDs that define which products to delete.
	 * @param int   $total_products Number of main products (for final notice).
	 * @param int   $total_items    Total items (products + variations, for final notice).
	 * @return void
	 */
	public static function start_bulk_deletion_cron( $supplier_ids, $total_products, $total_items ) {
		if ( empty( $supplier_ids ) ) {
			return;
		}
		$supplier_ids = array_map( 'intval', $supplier_ids );
		$supplier_ids = array_filter( $supplier_ids );
		if ( empty( $supplier_ids ) ) {
			return;
		}
		$option_key = self::get_bulk_deletion_queue_option_key( $supplier_ids );
		update_option(
			$option_key,
			array(
				'supplier_ids'   => $supplier_ids,
				'total_products' => (int) $total_products,
				'total_items'    => (int) $total_items,
			),
			false
		);
		wp_schedule_single_event( time() + 2, Plugin_Hooks::HOOK_BULK_DELETION_CRON_HOOK, array( $supplier_ids ) );
	}

	/**
	 * Option key for bulk deletion queue from supplier IDs (stable for same set).
	 *
	 * @param array $supplier_ids Supplier IDs.
	 * @return string Option key.
	 */
	private static function get_bulk_deletion_queue_option_key( $supplier_ids ) {
		$sorted = array_map( 'intval', $supplier_ids );
		$sorted = array_filter( $sorted );
		sort( $sorted );
		return Plugin_Hooks::HOOK_BULK_DELETION_QUEUE_OPTION . '_' . md5( implode( ',', $sorted ) );
	}

	/**
	 * Process one chunk of bulk deletion (invoked by cron). Receives supplier IDs as cron argument; fetches one batch via query (LIMIT), deletes, then reschedules or runs final cleanup.
	 * Legacy events scheduled without arguments are no-ops (0 args passed).
	 *
	 * @param array|null $supplier_ids Supplier IDs for this deletion (passed as cron argument); null if legacy event with no args.
	 * @return void
	 */
	public static function process_bulk_deletion_cron( $supplier_ids = null ) {
		if ( $supplier_ids === null || ! is_array( $supplier_ids ) || empty( $supplier_ids ) ) {
			return;
		}
		$supplier_ids = array_map( 'intval', $supplier_ids );
		$supplier_ids = array_filter( $supplier_ids );
		if ( empty( $supplier_ids ) ) {
			return;
		}
		$option_key = self::get_bulk_deletion_queue_option_key( $supplier_ids );
		$queue      = get_option( $option_key, null );
		if ( ! is_array( $queue ) || empty( $queue['supplier_ids'] ) ) {
			delete_option( $option_key );
			self::cleanup_after_bulk_deletion( array(), false );
			return;
		}

		ini_set( 'memory_limit', '512M' );
		set_time_limit( (int) apply_filters( 'mowcps_bulk_deletion_time_limit', 600 ) );

		$batch_size = (int) apply_filters( 'mowcps_sql_deletion_batch_size', 1000 );
		$chunk      = self::get_product_ids_by_supplier_ids_batch( $queue['supplier_ids'], $batch_size );
		$chunk_size = count( $chunk );

		$result = self::bulk_delete_products_direct_sql( $chunk, false );
		if ( is_wp_error( $result ) ) {
			delete_option( $option_key );
			self::cleanup_after_bulk_deletion( array(), false );
			return;
		}

		if ( $chunk_size < $batch_size ) {
			$total_products = isset( $queue['total_products'] ) ? (int) $queue['total_products'] : 0;
			$total_items    = isset( $queue['total_items'] ) ? (int) $queue['total_items'] : 0;
			delete_option( $option_key );
			self::cleanup_after_bulk_deletion( array(), false );
			set_transient(
				'mowcps_product_deletion_notice',
				array(
					'type'    => 'success',
					'message' => sprintf(
						'Product deletion completed! %d products (%d total items) deleted.',
						$total_products,
						$total_items
					),
				),
				300
			);
			return;
		}

		update_option( $option_key, $queue, false );
		wp_schedule_single_event( time() + 1, Plugin_Hooks::HOOK_BULK_DELETION_CRON_HOOK, array( $queue['supplier_ids'] ) );
	}

	/**
	 * Delete products and all related data using transaction-safe SQL queries.
	 *
	 * Uses atomic transactions: collects images, deletes all related data,
	 * commits changes, then cleans up. Automatic rollback on any error.
	 *
	 * @param array $product_ids Array of product IDs to delete.
	 * @param bool  $is_final_batch True if this is the only batch or the last batch (run full cleanup); false for intermediate batches (light cleanup only).
	 * @return array|WP_Error Deletion results, or WP_Error with rollback on failure.
	 */
	private static function bulk_delete_products_direct_sql( $product_ids, $is_final_batch = true ) {
		global $wpdb;

		if ( empty( $product_ids ) ) {
			return array(
				'deleted_count' => 0,
				'failed_count'  => 0,
			);
		}

		$product_ids  = array_map( 'intval', $product_ids );
		$placeholders = implode( ',', $product_ids );
		$count        = count( $product_ids );

		$wpdb->query( 'START TRANSACTION' ); //phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Required for transaction.

		try {
			// Collect image IDs before deletion (can't retrieve after products are gone)
			$image_ids = self::get_product_images_sql( $product_ids );

			// Delete from all related tables (order matters for data integrity)
			self::delete_wc_lookup_data( $placeholders );
			self::delete_product_relationships( $placeholders );
			self::delete_product_meta( $placeholders );
			self::delete_product_posts( $placeholders );

			$wpdb->query( 'COMMIT' ); //phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Required for transaction.

			// Post-deletion cleanup: caches, orphaned images, rebuild WC tables (heavy cleanup skipped when part of batched run)
			self::cleanup_after_bulk_deletion( $image_ids, ! $is_final_batch );

			return array(
				'deleted_count' => $count,
				'failed_count'  => 0,
			);

		} catch ( \Exception $e ) {
			// Rollback undoes all changes if any step fails
			$wpdb->query( 'ROLLBACK' ); //phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Required for transaction.

			return new \WP_Error(
				'deletion_failed',
				'SQL deletion failed: ' . $e->getMessage(),
				array( 'product_ids' => $product_ids )
			);
		}
	}

	/**
	 * Get product image IDs using bulk SQL queries.
	 *
	 * @param array $product_ids Array of product IDs.
	 * @return array Array of image attachment IDs.
	 */
	private static function get_product_images_sql( $product_ids ) {
		if ( empty( $product_ids ) ) {
			return array();
		}

		global $wpdb;
		$placeholders = implode( ',', array_map( 'intval', $product_ids ) );

		$thumbnails = $wpdb->get_col(
			"SELECT DISTINCT CAST(meta_value AS UNSIGNED)
			FROM {$wpdb->postmeta}
			WHERE meta_key = '_thumbnail_id'
			AND post_id IN ({$placeholders})
			AND meta_value != ''"
		); //phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Optimized bulk query with sanitized IDs.

		$galleries = $wpdb->get_col(
			"SELECT meta_value
			FROM {$wpdb->postmeta}
			WHERE meta_key = '_product_image_gallery'
			AND post_id IN ({$placeholders})
			AND meta_value != ''"
		); //phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Optimized bulk query with sanitized IDs.

		$gallery_ids = array();
		foreach ( $galleries as $gallery ) {
			$ids         = array_map( 'intval', explode( ',', $gallery ) );
			$gallery_ids = array_merge( $gallery_ids, $ids );
		}

		$all_image_ids = array_merge(
			array_map( 'intval', $thumbnails ),
			$gallery_ids
		);

		return array_filter( array_unique( $all_image_ids ) );
	}

	/**
	 * Delete WooCommerce lookup table data for products.
	 *
	 * @param string $placeholders Comma-separated product IDs.
	 */
	private static function delete_wc_lookup_data( $placeholders ) {
		global $wpdb;

		$tables = array(
			$wpdb->prefix . 'wc_product_meta_lookup'       => 'product_id',
			$wpdb->prefix . 'wc_product_attributes_lookup' => 'product_id',
			$wpdb->prefix . 'woocommerce_downloadable_product_permissions' => 'product_id',
		);

		foreach ( $tables as $table => $column ) {
			$table_exists = $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $table ) ); //phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Required for table existence check.

			if ( $table_exists ) {
				$wpdb->query(
					"DELETE FROM {$table} 
					WHERE {$column} IN ({$placeholders})"
				); //phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Bulk deletion with sanitized IDs.
			}
		}
	}

	/**
	 * Delete product term relationships and update counts.
	 *
	 * @param string $placeholders Comma-separated product IDs.
	 */
	private static function delete_product_relationships( $placeholders ) {
		global $wpdb;

		$affected_terms = $wpdb->get_col(
			"SELECT DISTINCT term_taxonomy_id 
			FROM {$wpdb->term_relationships} 
			WHERE object_id IN ({$placeholders})"
		); //phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Bulk query with sanitized IDs.

		$wpdb->query(
			"DELETE FROM {$wpdb->term_relationships} 
			WHERE object_id IN ({$placeholders})"
		); //phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Bulk deletion with sanitized IDs.

		if ( ! empty( $affected_terms ) ) {
			wp_update_term_count( $affected_terms, 'product_cat' );
			wp_update_term_count( $affected_terms, 'product_tag' );
		}
	}

	/**
	 * Delete product meta data.
	 *
	 * @param string $placeholders Comma-separated product IDs.
	 */
	private static function delete_product_meta( $placeholders ) {
		global $wpdb;

		$wpdb->query(
			"DELETE FROM {$wpdb->postmeta} 
			WHERE post_id IN ({$placeholders})"
		); //phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Bulk deletion with sanitized IDs.
	}

	/**
	 * Delete product posts.
	 *
	 * @param string $placeholders Comma-separated product IDs.
	 */
	private static function delete_product_posts( $placeholders ) {
		global $wpdb;

		$wpdb->query(
			"DELETE FROM {$wpdb->posts} 
			WHERE ID IN ({$placeholders})"
		); //phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Bulk deletion with sanitized IDs.
	}

	/**
	 * Cleanup after bulk deletion: clear caches and rebuild WC lookup tables.
	 *
	 * @param array $image_ids          Array of image attachment IDs.
	 * @param bool  $skip_heavy_cleanup When true, skip lookup table rebuild and rewrite rules flush (for intermediate batches).
	 */
	private static function cleanup_after_bulk_deletion( $image_ids, $skip_heavy_cleanup = false ) {
		wp_cache_flush();

		if ( ! empty( $image_ids ) ) {
			self::delete_unused_product_images( $image_ids );
		}

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

		if ( ! $skip_heavy_cleanup ) {
			if ( function_exists( 'wc_update_product_lookup_tables' ) ) {
				wc_update_product_lookup_tables();
			}
			delete_option( 'rewrite_rules' );
		}
	}


	/**
	 * Process a batch of products for deletion immediately with optimization.
	 *
	 * @param array $product_ids Array of product IDs to delete.
	 * @param int   $batch_number Current batch number (0-indexed).
	 * @param int   $total_batches Total number of batches.
	 * @return array Batch processing results.
	 */
	public static function delete_products_batch( $product_ids, $batch_number, $total_batches ) {
		if ( empty( $product_ids ) ) {
			return array(
				'deleted_count' => 0,
				'failed_count'  => 0,
				'batch_number'  => $batch_number + 1,
				'total_batches' => $total_batches,
			);
		}

		$deleted_count = 0;
		$failed_count  = 0;

		// Optimize for bulk operations
		global $wpdb;
		$product_ids  = array_map( 'intval', $product_ids );
		$placeholders = implode( ',', array_fill( 0, count( $product_ids ), '%d' ) );

		// Get all products data in one query for efficiency
		$products_data = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT ID, post_type 
				FROM {$wpdb->posts} 
				WHERE ID IN ($placeholders)
				AND post_type IN ('product', 'product_variation')",
				...$product_ids
			),
			ARRAY_A
		);

		foreach ( $products_data as $product_data ) {
			$product_id = $product_data['ID'];
			$product    = wc_get_product( $product_id );

			if ( ! $product ) {
				++$failed_count;
				continue;
			}

			// Get product images before deletion for proper cleanup.
			$image_ids = array();

			// Get featured image.
			$featured_image_id = $product->get_image_id();
			if ( $featured_image_id ) {
				$image_ids[] = $featured_image_id;
			}

			// Get gallery images.
			$gallery_image_ids = $product->get_gallery_image_ids();
			if ( ! empty( $gallery_image_ids ) ) {
				$image_ids = array_merge( $image_ids, $gallery_image_ids );
			}

			// For variable products, also get variation images.
			if ( $product->is_type( 'variable' ) ) {
				$variations = $product->get_children();
				foreach ( $variations as $variation_id ) {
					$variation = wc_get_product( $variation_id );
					if ( $variation ) {
						$variation_image_id = $variation->get_image_id();
						if ( $variation_image_id ) {
							$image_ids[] = $variation_image_id;
						}
					}
				}
			}

			// Use WooCommerce's force delete (bypasses trash).
			$result = $product->delete( true );

			if ( $result ) {
				++$deleted_count;
				// Clean up images after successful product deletion.
				if ( ! empty( $image_ids ) ) {
					self::delete_unused_product_images( array_unique( $image_ids ) );
				}
			} else {
				++$failed_count;
			}
		}

		// Return batch processing results.
		return array(
			'deleted_count' => $deleted_count,
			'failed_count'  => $failed_count,
			'batch_number'  => $batch_number + 1,
			'total_batches' => $total_batches,
		);
	}

	/**
	 * Delete product images that are not used by any other products.
	 * Efficient bulk checking version.
	 *
	 * @param array $image_ids Array of image attachment IDs.
	 * @return void
	 */
	private static function delete_unused_product_images( $image_ids ) {
		if ( empty( $image_ids ) ) {
			return;
		}

		global $wpdb;
		$image_ids = array_filter( array_map( 'intval', $image_ids ) );

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

		$image_ids_str = implode( ',', $image_ids );

		// Optimized: Check which images are still in use by other active products
		// Using simpler queries instead of complex CROSS JOIN with string splitting
		$used_images = array();

		// Check thumbnail usage
		$used_thumbnails = $wpdb->get_col(
			"SELECT DISTINCT CAST(pm.meta_value AS UNSIGNED) 
			FROM {$wpdb->postmeta} pm
			INNER JOIN {$wpdb->posts} p ON pm.post_id = p.ID
			WHERE pm.meta_key = '_thumbnail_id' 
			AND pm.meta_value IN ({$image_ids_str})
			AND p.post_type IN ('product', 'product_variation')
			AND p.post_status NOT IN ('trash', 'auto-draft')"
		); //phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared -- Optimized bulk query with safe variables.

		if ( ! empty( $used_thumbnails ) ) {
			$used_images = array_merge( $used_images, array_map( 'intval', $used_thumbnails ) );
		}

		// Check gallery usage using FIND_IN_SET (much faster than string splitting)
		foreach ( $image_ids as $image_id ) {
			$gallery_usage = $wpdb->get_var(
				$wpdb->prepare(
					"SELECT COUNT(*) 
					FROM {$wpdb->postmeta} pm
					INNER JOIN {$wpdb->posts} p ON pm.post_id = p.ID
					WHERE pm.meta_key = '_product_image_gallery' 
					AND FIND_IN_SET(%d, REPLACE(pm.meta_value, ' ', '')) > 0
					AND p.post_type IN ('product', 'product_variation')
					AND p.post_status NOT IN ('trash', 'auto-draft')
					LIMIT 1",
					$image_id
				)
			); //phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Optimized bulk query.

			if ( $gallery_usage > 0 ) {
				$used_images[] = $image_id;
			}
		}

		// Delete only unused images
		$unused_images = array_diff( $image_ids, array_map( 'intval', $used_images ) );
		foreach ( $unused_images as $image_id ) {
			wp_delete_attachment( $image_id, true );
		}
	}

	/**
	 * Get the config for the given sync connection.
	 *
	 * @param  string $connection_name Name of the sync connection.
	 * @return array
	 */
	public static function get_sync_config( $connection_name ) {
		$config = self::get_option( 'MOWCPS_config' );
		return $config[ $connection_name ];
	}


	/**
	 * Get the value from the wp_options table through SQL query.
	 *
	 * @param string $key Option key.
	 *
	 * @return mixed
	 */
	public static function sql_get_option( $key ) {
		global $wpdb;
		$option_value = $wpdb->get_var( $wpdb->prepare( "SELECT option_value FROM {$wpdb->options} WHERE option_name = %s LIMIT 1", $key ) );

		return $option_value;
	}

	/**
	 * Get the value from the wp_options table.
	 *
	 * @param string $supplier_id Supplier ID.
	 * @param string $schedule_id Schedule ID.
	 * @param string $connection_id Connection ID.
	 *
	 * @return Sync_Analytics
	 */
	public static function get_sync_analytics_object( $supplier_id, $schedule_id, $connection_id, $batch_number = null ) {
		$analytics_object = new Sync_Analytics( $supplier_id, $schedule_id, $connection_id, $batch_number );
		$analytics_object->set_values();
		return $analytics_object;
	}

	/**
	 * Bulk update post meta values in a single optimized SQL query.
	 *
	 * This function handles multiple post meta updates in one database transaction,
	 * significantly reducing the number of SQL queries compared to individual update_post_meta() calls.
	 *
	 * @param int   $post_id Post ID to update meta for.
	 * @param array $meta_data Associative array of meta key => meta value pairs to update.
	 *
	 * @return bool True on success, false on failure.
	 */
	public static function bulk_update_post_meta( $post_id, $meta_data ) {
		global $wpdb;

		if ( empty( $post_id ) || empty( $meta_data ) || ! is_array( $meta_data ) ) {
			return false;
		}

		// Start transaction for atomic operation.
		$wpdb->query( 'START TRANSACTION' );

		try {
			$values       = array();
			$placeholders = array();
			$meta_keys    = array();

			foreach ( $meta_data as $meta_key => $meta_value ) {

				// Normalize & serialize like update_post_meta.
				if ( is_array( $meta_value ) || is_object( $meta_value ) ) {
					$meta_value = maybe_serialize( $meta_value );
				}

				$meta_keys[] = $meta_key;

				$values[]       = $post_id;
				$values[]       = $meta_key;
				$values[]       = $meta_value;
				$placeholders[] = '(%d, %s, %s)';
			}

			// --- Step 1: Delete only the keys being replaced ---
			$in_placeholders = implode( ', ', array_fill( 0, count( $meta_keys ), '%s' ) );

			$delete_sql = $wpdb->prepare(
				"DELETE FROM {$wpdb->postmeta} 
				WHERE post_id = %d 
				AND meta_key IN ($in_placeholders)",
				array_merge( array( $post_id ), $meta_keys )
			);

			$wpdb->query( $delete_sql ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared

			// --- Step 2: Bulk insert new values ---
			$insert_sql = $wpdb->prepare(
				"INSERT INTO {$wpdb->postmeta} (post_id, meta_key, meta_value)
				VALUES " . implode( ', ', $placeholders ),
				$values
			);

			$wpdb->query( $insert_sql ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared

			// Commit transaction.
			$wpdb->query( 'COMMIT' );

			// Clear cache.
			wp_cache_delete( $post_id, 'post_meta' );

			return true;

		} catch ( \Exception $e ) {
			// Rollback on error.
			$wpdb->query( 'ROLLBACK' );
			return false;
		}
	}

	/**
	 * Update supplier configuration.
	 */
	public static function update_supplier_configuration() {
		$suppliers = Supplier_Model::get_all();
		foreach ( $suppliers as $supplier ) {
			if ( Constants::SUPPLIER_APP_TYPE_SNS_ACTIVEWEAR === $supplier['app_type'] ) {
				$sns_activewear = new Sns_Activewear();
				$sns_activewear->update_brands( $supplier );
			}
		}
	}
	
	/**
	 * Get product ID by meta key and value
	 *
	 * @param string $meta_key   The meta key to search for.
	 * @param string $meta_value The meta value to match.
	 * @return int|null The product ID if found, or null if not found.
	 */
	public static function get_product_id_by_meta( string $meta_key, string $meta_value ): ?int {
        global $wpdb;

        $product_id = $wpdb->get_var(
            $wpdb->prepare(
                "SELECT post_id 
                 FROM {$wpdb->postmeta} 
                 WHERE meta_key = %s 
                 AND meta_value = %s 
                 LIMIT 1",
                $meta_key,
                $meta_value
            )
        );

        return $product_id ? (int) $product_id : null;
    }
}
