<?php
/**
 * Class ORder.
 * includes/model/order.php
 *
 * @package OrderSyncWithGoogleSheetForWooCommerce.
 */

namespace OrderSyncWithGoogleSheetForWooCommerce;

defined( 'ABSPATH' ) || die( 'No script kiddies please!' );

if ( ! class_exists( '\OrderSyncWithGoogleSheetForWooCommerce\Order' ) ) {
	/**
	 * Class Order
	 */
	class Order extends Base {
		/**
		 * Utility Trait to use in all classes globally.
		 */
		use Utilities;

		/**
		 * Sync all orders
		 *
		 * @param boolean $condition Sync condtion applied.
		 */
		public function sync_batch_all($offset, $batch_size ) {//phpcs:ignore
			if ( $this->app->is_plugin_ready() !== true ) {
				return false;
			}
			$data = $this->format_order_informations($offset, $batch_size);
			// Fix: Use batch-aware header processing for consistent column alignment.
			$rearranged_data = $this->get_header_with_values_for_batch($data, $offset);

			$google_sheet = new Sheet();

			$updated = $google_sheet->append_batch_product_to_sheet($rearranged_data, $offset);
			$google_sheet->update_google_sheet_sorting();
			return wp_validate_boolean( $updated );
		}
		/**
		 * Retrieves and formats WooCommerce order information.
		 *
		 * This method determines the storage method for WooCommerce orders (custom orders table or default posts table)
		 * and retrieves order data in batches. The retrieved data is then formatted by merging headers with the data.
		 *
		 * @param int $offset The offset for retrieving orders (used for pagination).
		 * @param int $batch_size The number of orders to retrieve in one batch.
		 * @return array The formatted order data, including headers and order details.
		 */
		public function format_order_informations( $offset, $batch_size ) {
			$order_data_storage_option = get_option( 'woocommerce_custom_orders_table_enabled' );
			if ( 'yes' === $order_data_storage_option ) {
				$data = $this->get_all_hpos_orders([], false, $offset, $batch_size);
			} else {
				$data = $this->get_all_orders([], false, $offset, $batch_size);
			}
			$data = $this->marge_header_with_data_offset($data, $offset);
			return $data;
		}

		/**
		 * Sync all orders
		 *
		 * @param boolean $condition Sync condtion applied.
		 */
		public function sync_all( $condition = false ) {//phpcs:ignore
			if ( $this->app->is_plugin_ready() !== true ) {
				return false;
			}

			$order_data_storage_option = get_option( 'woocommerce_custom_orders_table_enabled' );
			if ( 'yes' === $order_data_storage_option ) {
				$data = $this->get_all_hpos_orders();
			} else {
				$data = $this->get_all_orders();
			}
			$data = $this->marge_header_with_data($data);
			$google_sheet = new Sheet();
			$updated = $google_sheet->update_values( 'A1', $data );
			$google_sheet->update_google_sheet_sorting();

			// Fix: Clear batch cache after full sync completion.
			$this->clear_batch_cache();

			return wp_validate_boolean( $updated );
		}

		/**
		 * Sync all orders
		 *
		 * @param boolean $condition Sync condtion applied.
		 */
		public function get_header_with_values( $data ) {
			$sheets_info = [];
			$sheet = new Sheet();
			$sheets_header_info = $sheet->fetch_sheet_header();

			// Rearrange data to match sheet headers.
			$rearranged_data = $this->rearrange_data_by_sheet_headers($data, $sheets_header_info);
			return $rearranged_data;
		}

		/**
		 * Get header with values for batch processing with persistent caching
		 * Replaces static variables with WordPress options to maintain state across HTTP requests
		 *
		 * @param array $data The order data
		 * @param int   $offset The batch offset
		 * @return array The rearranged data with proper column alignment
		 */
		public function get_header_with_values_for_batch( $data, $offset ) {
			// Fix: Cache keys for persistent storage across HTTP requests.
			$cache_key_sheet_headers = 'osgsw_batch_sheet_headers_cache';
			$cache_key_original_headers = 'osgsw_batch_original_headers_cache';
			$cache_key_timestamp = 'osgsw_batch_cache_timestamp';

			$cache_expiry = 3600; // 1 hour in seconds.
			$cache_timestamp = get_option( $cache_key_timestamp, 0 );
			$cache_expired = ( time() - $cache_timestamp ) > $cache_expiry;

			if ( $offset === 0 || $cache_expired ) {
				$this->clear_batch_cache();

				$sheet = new Sheet();
				$cached_sheet_headers = $sheet->fetch_sheet_header();
				$header_row = $data[0];

				update_option( $cache_key_sheet_headers, $cached_sheet_headers );
				update_option( $cache_key_original_headers, $header_row );
				update_option( $cache_key_timestamp, time() );

				$rearranged_data = $this->rearrange_data_by_sheet_headers( $data, $cached_sheet_headers );
				return $rearranged_data;
			}

			if ( $offset > 0 ) {
				$cached_sheet_headers = get_option( $cache_key_sheet_headers );
				$cached_original_headers = get_option( $cache_key_original_headers );

				if ( empty( $cached_sheet_headers ) || empty( $cached_original_headers ) ) {
					$sheet = new Sheet();
					$cached_sheet_headers = $sheet->fetch_sheet_header();
					$cached_original_headers = $data[0];

					update_option( $cache_key_sheet_headers, $cached_sheet_headers );
					update_option( $cache_key_original_headers, $cached_original_headers );
					update_option( $cache_key_timestamp, time() );
				}

				$data_with_headers = array_merge( [ $cached_original_headers ], $data );

				$rearranged_data = $this->rearrange_data_by_sheet_headers( $data_with_headers, $cached_sheet_headers );

				array_shift( $rearranged_data );

				return $rearranged_data;
			}

			// Fallback to regular processing
			return $this->get_header_with_values( $data );
		}

		/**
		 * Clear batch cache - updated to work with WordPress options
		 *
		 * @return void
		 */
		public function clear_batch_cache() {
			// Fix: Clear all cache keys including timestamp
			delete_option( 'osgsw_batch_sheet_headers_cache' );
			delete_option( 'osgsw_batch_original_headers_cache' );
			delete_option( 'osgsw_batch_cache_timestamp' );
		}

		/**
		 * Rearrange data by sheet headers with preference and weight consideration.
		 *
		 * @param array $data The data to rearrange.
		 * @param array $sheet_headers The sheet headers to match against.
		 * @return array The rearranged data.
		 */
		public function rearrange_data_by_sheet_headers( $data, $sheet_headers ) {
			// Find which sheet headers exist in our current data.
			$valid_sheet_headers = [];
			$mapping = [];
			$current_headers = $data[0];

			foreach ( $sheet_headers as $sheet_index => $sheet_header ) {
				$found = false;
				foreach ( $current_headers as $data_index => $data_header ) {
					if ( $sheet_header === $data_header ) {
						$mapping[ $sheet_index ] = $data_index;
						$valid_sheet_headers[] = $sheet_header;
						$found = true;
						break;
					}
				}
			}

			// Find columns in the current data that aren't in the sheet headers.
			$used_data_indices = array_values($mapping);
			$new_columns = [];
			$new_column_indices = [];

			foreach ( $current_headers as $data_index => $data_header ) {
				if ( ! in_array($data_index, $used_data_indices) ) {
					$new_columns[] = $data_header;
					$new_column_indices[] = $data_index;
				}
			}

			$column_preferences = get_option('osgsw_column_preferences', array());
			$columns = new Column();
			$all_columns = $columns->get_all_columns();

			// Create a mapping from header label to key and weight.
			$header_mapping = array();
			foreach ( $all_columns as $key => $column ) {
				if ( isset($column['label']) ) {
					$header_mapping[ $column['label'] ] = array(
						'key' => $key,
						'weight' => isset($column['weight']) ? $column['weight'] : 999,
					);
				}
			}

			// Determine the order for combined headers based on preferences or weights.
			$all_headers = array_merge($valid_sheet_headers, $new_columns);

			$position_map = array();

			if ( ! empty($column_preferences) ) {
				// For each header, determine its position based on preferences or weight.
				foreach ( $all_headers as $header ) {
					$key = isset($header_mapping[ $header ]) ? $header_mapping[ $header ]['key'] : '';
					$position = null;

					// Look for this header in user preferences.
					foreach ( $column_preferences as $pref ) {
						if ( isset($pref['headerValue']) && $pref['headerValue'] === $header ) {
							// User has a preference for this column.
							$position = isset($pref['weight']) ? (int) $pref['weight'] : null;
							break;
						} elseif ( isset($pref['headerKey'], $key) && $pref['headerKey'] === $key ) {
							// Match by key instead of label.
							$position = isset($pref['weight']) ? (int) $pref['weight'] : null;
							break;
						}
					}

					// If no preference found, use the default weight.
					if ( $position === null && isset($header_mapping[ $header ]) ) {
						$position = $header_mapping[ $header ]['weight'];
					} elseif ( $position === null ) {
						// If still no position, assign a large weight to put it at the end.
						$position = 1000 + count($position_map);
					}

					$position_map[ $header ] = $position;
				}
			} else {
				// No user preferences, use weights from column definitions.
				foreach ( $all_headers as $header ) {
					if ( isset($header_mapping[ $header ]) ) {
						$position_map[ $header ] = $header_mapping[ $header ]['weight'];
					} else {
						// Here else for If no weight defined, assign a large weight to put it at the end.
						$position_map[ $header ] = 1000 + count($position_map);
					}
				}
			}

			// Sort headers by their determined positions.
			asort($position_map);
			$combined_headers = array_keys($position_map);

			// Create the mapping correctly to preserve all original data indices.
			$original_indices = array();

			// First add the valid sheet headers with their mapped indices.
			foreach ( $valid_sheet_headers as $header ) {
				foreach ( $mapping as $sheet_index => $data_index ) {
					if ( $sheet_headers[ $sheet_index ] === $header ) {
						$original_indices[ $header ] = $data_index;
						break;
					}
				}
			}

			// Then add the new columns with their indices.
			foreach ( $new_columns as $index => $header ) {
				$original_indices[ $header ] = $new_column_indices[ $index ];
			}

			// Create the new mapping array that maps new positions to original data indices.
			$new_mapping = array();
			foreach ( $combined_headers as $new_index => $header ) {
				if ( isset($original_indices[ $header ]) ) {
					$new_mapping[ $new_index ] = $original_indices[ $header ];
				}
			}

			// First row will be the combined headers.
			$rearranged_data = [];
			$rearranged_data[] = $combined_headers;

			// Rearrange each data row according to the combined header order.
			for ( $i = 1; $i < count($data); $i++ ) {

				// Initialize with empty values.
				$new_row = array_fill(0, count($combined_headers), '');

				// Fill in the values according to the new mapping.
				foreach ( $new_mapping as $new_index => $original_data_index ) {
					if ( isset($data[ $i ][ $original_data_index ]) ) {
						$new_row[ $new_index ] = $data[ $i ][ $original_data_index ];
					}
				}

				$rearranged_data[] = $new_row;
			}

			return $rearranged_data;
		}



		/**
		 * Marge header with data.
		 *
		 * @return array
		 */
		public function marge_header_with_data_offset( $data, $offset = 0 ) {
			$data = $this->duplicate_based_on_product_names($data);
			if ( $offset ) {
				return $data;
			} else {
				$columns = new Column();
				$headers = [ $columns->get_column_names() ];
				return array_merge( $headers, $data );
			}
		}
		/**
		 * Marge header with data.
		 *
		 * @return array
		 */
		public function marge_header_with_data( $data, $single = false ) {
			$data = $this->duplicate_based_on_product_names($data);
			$columns = new Column();
			$headers = [ $columns->get_column_names() ];

			return array_merge( $headers, $data );
		}
		/**
		 * Converts a string of product IDs into a corresponding string of SKUs.
		 *
		 * This function accepts a string of product IDs (separated by commas)
		 * or a single product ID, retrieves their SKUs using WordPress meta functions,
		 * and returns a concatenated string of SKUs. If the input is empty, invalid,
		 * or no SKU is found, appropriate fallback messages are returned.
		 *
		 * @param string $product_id_info A string containing one or more product IDs separated by commas.
		 *                                Example: "12,34,56" or "12".
		 *
		 * @return string A comma-separated string of SKUs, or an error message if no valid SKUs are found.
		 *                Example outputs:
		 *                - "SKU123,SKU456"
		 *                - "** No SKU Found **"
		 *                - "** Invalid Product ID **"
		 *                - "** SKU is Empty **"
		 */
		public function format_id_to_sku( $product_id_info ) {
			// Handle empty or specific placeholder input.
			if ( empty($product_id_info) || 'Sku is empty' === $product_id_info ) {
				return '** SKU is Empty **';
			}

			if ( strpos($product_id_info, ',') !== false ) {
				$product_ids = array_map('trim', explode(',', $product_id_info));
			} else {
				$product_ids = [ trim($product_id_info) ];
			}

			$new_sku = [];
			if ( is_array($product_ids) && ! empty($product_ids) ) {
				foreach ( $product_ids as $product_id ) {
					if ( is_numeric($product_id) && $product_id > 0 ) {
						$sku = get_post_meta($product_id, '_sku', true);
						if ( ! empty($sku) ) {
							$new_sku[] = $sku;
						} else {
							$new_sku[] = '** No SKU Found **';
						}
					} else {
						$new_sku[] = '** Invalid Product ID **';
					}
				}
			}

			if ( ! empty($new_sku) ) {
				return implode('ssgsw_sep,', $new_sku);
			}

			return '** No SKUs Found **';
		}

		/**
		 * Divied products items.
		 *
		 * @param array $data
		 */
		public function duplicate_based_on_product_names( $data ) {
			$order_items = get_option('osgsw_multiple_itmes', false);
			$order_qty = get_option('osgsw_show_product_qt', false);
			$total_items = get_option('osgsw_sync_total_items', false);
			$product_sku = get_option('osgsw_product_sku_sync', false);
			$sync_total_price = get_option('osgsw_sync_total_price', false);
			$separator = get_separator();

			return array_reduce($data, function (
				$result,
				$row
			) use (
				$order_items,
				$order_qty,
				$sync_total_price,
				$total_items,
				$product_sku,
				$separator
			) {
				if ( $order_items ) {
					if ( isset($row['order_items']) && ! empty($row['order_items']) ) {
						// Split using the actual separator with the prefix.
						$split_pattern = 'ssgsw_wppool_' . preg_quote($separator, '/') . ' ';
						$products = preg_split('/' . $split_pattern . '/', $row['order_items']);

						$count = is_array($products) ? count($products) : 0;

						// Format the combined row with actual separator.
						$formatted_products = array_map('trim', array_filter($products));
						$row['order_items'] = implode(" $separator ", $formatted_products)
							. ' [' . $count . ' Products]';

						if ( $product_sku && isset($row['product_sku_sync']) ) {
							$row['product_sku_sync'] = $this->format_id_to_sku($row['product_sku_sync']);
						}

						$result[] = $this->format_serilization_data($row, false, 0, $order_qty);

						// Process individual products.
						foreach ( $products as $key_p => $product ) {
							if ( empty(trim($product)) ) {
								continue;
							}

							$key_up = $key_p + 1;
							$new_row = $row;

							if ( $order_qty ) {
								$new_row['order_items'] = $this->format_qty_beside_product($product);
							} else {
								$new_row['order_items'] = $this->remove_ssgsw_text($product);
							}

							$new_row['order_items'] = trim($new_row['order_items'])
								. ' [Product ' . $key_up . ' of ' . $count . ']';

							if ( $total_items ) {
								$new_row['qty'] = $this->get_osgsw_dynamic_qty_and_price(
									$product,
									'ssgsw_wppool_qty'
								);
							}

							if ( $sync_total_price ) {
								$new_row['order_total'] = $this->get_osgsw_dynamic_qty_and_price(
									$product,
									'ssgsw_wppool_price'
								);
							}

							$result[] = $this->format_serilization_data($new_row, true, $key_p, $order_qty);
						}
					} else {
						if ( $product_sku && isset($row['product_sku_sync']) ) {
							$row['product_sku_sync'] = $this->format_id_to_sku($row['product_sku_sync']);
						}
						$result[] = $this->format_serilization_data($row, false, 0, $order_qty);
					}
				} else {
					if ( $product_sku && isset($row['product_sku_sync']) ) {
						$row['product_sku_sync'] = $this->format_id_to_sku($row['product_sku_sync']);
					}
					$result[] = $this->format_serilization_data($row, false, 0, $order_qty);
				}
				return $result;
			}, []);
		}



		/**
		 * Format qty for beside the product.
		 *
		 * @return string
		 */
		public function format_qty_beside_product( $inputText ) {
			$outputText = preg_replace('/ssgsw_wppool_qty\s*:\s*([\d]+),?\s*(ssgsw_wppool_price\s*:\s*[\d]+\s*)?/', 'qty: $1', $inputText);
			return $outputText;
		}
		/**
		 * Remove osgsw_text from order.
		 *
		 * @return string
		 */
		public function remove_ssgsw_text( $inputText ) {
			if ( strpos($inputText, 'ssgsw_wppool_') !== false ) {
				$outputText = preg_replace('/\([^()]*ssgsw_wppool_[^()]*\)/', '', $inputText);
				return trim($outputText);
			}
			return $inputText;
		}
		/**
		 * Get qty and price value
		 * NEW
		 *
		 * @return mixed
		 */
		public function get_osgsw_dynamic_qty_and_price( $inputText, $key ) {

			// Look specifically for ssgsw_wppool parameters.
			if ( preg_match('/ssgsw_wppool_(?:qty|price):[^)]+\)$/', $inputText, $lastParentheses) ) {
				$paramString = $lastParentheses[0];

				// Extract the specific parameter.
				if ( $key === 'ssgsw_wppool_qty' ) {
					if ( preg_match('/ssgsw_wppool_qty:\s*(\d+)/', $paramString, $matches) ) {
						$value = (int) $matches[1];
						return $value;
					}
				} else if ( $key === 'ssgsw_wppool_price' ) {
					if ( preg_match('/ssgsw_wppool_price:\s*([\d.]+)/', $paramString, $matches) ) {
						$value = number_format( (float) $matches[1], 2, '.', '');
						return $value;
					}
				}
			}

			return ( $key === 'ssgsw_wppool_qty' ) ? 0 : '0.00';
		}

		/**
		 * Format serilization data.
		 *
		 * @return array
		 */
		public function format_serilization_data( $rows = [], $sub = false, $key1 = 0, $qty = false ) {
			$formatted_row = [];
			foreach ( $rows as $key => $row ) {
				if ( $sub ) {
					$condition = $this->collection_of_visiable_values($key);
					if ( 2 === $condition ) {
						if ( $key === 'order_total' ) {
							// Format decimal numbers for price.
							$formatted_row[ $key ] = number_format( (float) $row, 2, '.', '');
						} else {
							$formatted_row[ $key ] = ssgsw_format_item_meta($row);
						}
					} else if ( 3 === $condition ) {
						$new_meta = $this->split_by_delimiter($row, $key1);
						if ( empty($new_meta) ) {
							$unserialize = ssgsw_format_item_meta($row);
							$formatted_row[ $key ] = $unserialize;
						} else {
							$formatted_row[ $key ] = ssgsw_format_item_meta($new_meta);
						}
					} else {
						$formatted_row[ $key ] = '';
					}
				} else {
					if ( $key === 'order_items' ) {
						if ( $qty ) {
							$formatted_row[ $key ] = $this->remove_osgsw_sep_from_text($row, 'ssgsw_wppool_');
						} else {
							$replace = $this->remove_ssgsw_text($row);
							$formatted_row[ $key ] = $this->remove_osgsw_sep_from_text($replace, 'ssgsw_wppool_');
						}
					} else if ( $key === 'order_total' ) {
						// Format decimal numbers for price.
						$unserialize_data = ssgsw_format_item_meta($row);
						$formatted_row[ $key ] = number_format( (float) $unserialize_data, 2, '.', '');
					} else {
						$unserialize_data = ssgsw_format_item_meta($row);
						$formatted_row[ $key ] = $this->remove_osgsw_sep_from_text($unserialize_data, 'ssgsw_sep');
					}
				}
			}

			return array_values( (array) $formatted_row);
		}

		/**
		 * Remove osgsw separater from text.
		 *
		 * @return string
		 */
		public function remove_osgsw_sep_from_text( $input_text, $delimiter ) {
			if ( strpos($input_text, $delimiter) !== false ) {
				$output_text = str_replace($delimiter, '', $input_text);
				return $output_text;
			}
			return $input_text;
		}
		/**
		 * Split sting to array.
		 *
		 * @return string.
		 */
		public function split_by_delimiter( $input_text, $key ) {
			$delimiter = 'ssgsw_sep,';
			if ( strpos($input_text, $delimiter) !== false ) {
				$result_array = preg_split('/' . preg_quote($delimiter, '/') . '/', $input_text, -1, PREG_SPLIT_NO_EMPTY);
				return isset($result_array[ $key ]) ? $result_array[ $key ] : '';
			}
			return '';
		}
		/**
		 * Callection of visiable values for individual row products.
		 *
		 * @return boolean
		 */
		public function collection_of_visiable_values( $key ) {
			$cols = [
				'order_id',
				'order_items',
				'status',
				'qty',
				'order_total',
			];
			if ( in_array($key, $cols) ) {
				return 2;
			}
			if ( $key === 'product_sku_sync' ) {
				return 3;
			}
			$text = 'Itemmeta';
			if ( strpos($key, $text) !== false ) {
				return 3;
			}
		}

		/**
		 * Build filter conditions for order queries based on user settings.
		 *
		 * @param string $table_prefix The table prefix (p for posts table, p for wc_orders table).
		 * @param string $status_column The status column name (post_status for posts, status for wc_orders).
		 * @param string $date_column The date column name (post_date for posts, date_created_gmt for wc_orders).
		 * @return string The WHERE conditions to append to the query.
		 */
		private function build_filter_conditions( $table_prefix = 'p', $status_column = 'post_status', $date_column = 'post_date' ) {
			$is_license_active = osgsw_is_ultimate_license_activated();

			if ( $is_license_active ) {
				if ( class_exists( '\OrderSyncWithGoogleSheetForWooCommerceUltimate\FilterExtension' ) ) {
					$ultimate_order = new \OrderSyncWithGoogleSheetForWooCommerceUltimate\FilterExtension();
					return $ultimate_order->build_filter_conditions( '', $table_prefix, $status_column, $date_column );
				}
				return '';
			} else {
				// No filtering.
				return '';
			}
		}

		/**
		 * Check if a specific order matches the current filter criteria.
		 * This is a PRO feature - returns true (all orders match) in free version.
		 *
		 * @param int $order_id The order ID to check.
		 * @return bool True if the order matches the filters or filtering is disabled, false otherwise.
		 */
		public function order_matches_filters( $order_id ) {
			$is_license_active = osgsw_is_ultimate_license_activated();

			if ( $is_license_active ) {
				if ( class_exists( '\OrderSyncWithGoogleSheetForWooCommerceUltimate\FilterExtension' ) ) {
					$ultimate_order = new \OrderSyncWithGoogleSheetForWooCommerceUltimate\FilterExtension();
					return $ultimate_order->order_matches_filters( true, $order_id );
				}
				// If PRO plugin is not available but license is active, allow all orders.
				return true;
			} else {
				// Free version - no filtering, all orders match.
				return true;
			}
		}

		/**
		 * Get filter conditions for count queries.
		 * This is a public wrapper for build_filter_conditions to be used in count queries.
		 *
		 * @param string $table_prefix The table prefix.
		 * @param string $status_column The status column name.
		 * @param string $date_column The date column name.
		 * @return string The filter conditions.
		 */
		public function get_filter_conditions_for_count( $table_prefix, $status_column, $date_column ) {
			return $this->build_filter_conditions( $table_prefix, $status_column, $date_column );
		}

		/**
		 * Get all orders.
		 *
		 * @param array $order_id for retribing single order.
		 * @param int   $trash for deleting single order.
		 *
		 * @return array
		 */
		public function get_all_orders( $order_ids = [], $trash = false, $offset = 0, $batch_size = 0 ) {
			global $wpdb;
			$total_discount   = true === wp_validate_boolean( osgsw_get_option( 'total_discount', false ) );
			$make_billing_shipping_seperate = true === wp_validate_boolean( osgsw_get_option( 'make_billing_shipping_seperate', false ) );
			$order_date       = true === wp_validate_boolean( osgsw_get_option( 'show_order_date', false ) );
			$payment_method   = true === wp_validate_boolean( osgsw_get_option( 'show_payment_method', false ) );
			$transaction_id   = true === wp_validate_boolean( osgsw_get_option( 'transaction_id', false ) );
			$customer_note    = true === wp_validate_boolean( osgsw_get_option( 'show_customer_note', false ) );
			$order_url        = true === wp_validate_boolean( osgsw_get_option( 'show_order_url', false ) );
			$who_place_order  = true === wp_validate_boolean( osgsw_get_option( 'who_place_order', false ) );
			$show_product_qt  = true === wp_validate_boolean( osgsw_get_option( 'show_product_qt', false ) );
			$show_custom_meta_fields = true === wp_validate_boolean( osgsw_get_option( 'show_custom_meta_fields', false ) );
			$show_custom_fields = true === wp_validate_boolean( osgsw_get_option( 'show_custom_fields', false ) );
			$sync_total_items = true === wp_validate_boolean( osgsw_get_option( 'sync_total_items', false ) );
			$sync_total_price = true === wp_validate_boolean( osgsw_get_option( 'sync_total_price', false ) );

			$make_billing_shipping_seperate = true === wp_validate_boolean( osgsw_get_option( 'make_billing_shipping_seperate', false ) );
			$billing_deatils  = true === wp_validate_boolean( osgsw_get_option( 'show_billing_details', false ) );
			$shipping_details = true === wp_validate_boolean( osgsw_get_option( 'add_shipping_details_sheet', false ) );

			$sync_order_products = wp_validate_boolean(get_option(OSGSW_PREFIX . 'sync_order_products', true));
			$sync_order_status = wp_validate_boolean(get_option(OSGSW_PREFIX . 'sync_order_status', true));

			// Billing address fields.
			$billing_fields_enabled = false;
			$billing_first_name = true === wp_validate_boolean(osgsw_get_option('show_billing_first_name', false));
			$billing_last_name = true === wp_validate_boolean(osgsw_get_option('show_billing_last_name', false));
			$billing_address_1 = true === wp_validate_boolean(osgsw_get_option('show_billing_address_1', false));
			$billing_city = true === wp_validate_boolean(osgsw_get_option('show_billing_city', false));
			$billing_postcode = true === wp_validate_boolean(osgsw_get_option('show_billing_postcode', false));
			$billing_country = true === wp_validate_boolean(osgsw_get_option('show_billing_country', false));

			// Check if any billing field is enabled.
			if ( $billing_first_name || $billing_last_name || $billing_address_1 || $billing_city || $billing_postcode || $billing_country ) {
				$billing_fields_enabled = true;
			}

			// Shipping address fields.
			$shipping_fields_enabled = false;
			$shipping_first_name = true === wp_validate_boolean(osgsw_get_option('show_shipping_first_name', false));
			$shipping_last_name = true === wp_validate_boolean(osgsw_get_option('show_shipping_last_name', false));
			$shipping_address_1 = true === wp_validate_boolean(osgsw_get_option('show_shipping_address_1', false));
			$shipping_city = true === wp_validate_boolean(osgsw_get_option('show_shipping_city', false));
			$shipping_postcode = true === wp_validate_boolean(osgsw_get_option('show_shipping_postcode', false));
			$shipping_country = true === wp_validate_boolean(osgsw_get_option('show_shipping_country', false));

			if ( $shipping_first_name || $shipping_last_name || $shipping_address_1 || $shipping_city || $shipping_postcode || $shipping_country ) {
				$shipping_fields_enabled = true;
			}

			$order_urls       = esc_url( admin_url( 'post.php?' ) );
			$product_sku_sync = wp_validate_boolean(get_option(OSGSW_PREFIX . 'product_sku_sync', false));

			$db_separator = 'ssgsw_wppool_' . get_separator() . ' ';
			$separator = get_separator();

			$order = 'SELECT
				p.ID as order_id';

			if ( $sync_order_products ) {
				$order .= ", (SELECT IFNULL(GROUP_CONCAT(
					CONCAT(
						oi.order_item_name, 
						' (ssgsw_wppool_qty: ', 
						IFNULL(c.meta_value, '0'), 
						',ssgsw_wppool_price: ', 
						IFNULL(pt.meta_value, '0'), 
						')'
					) 
					SEPARATOR %s
				), '** No Products **')
				FROM {$wpdb->prefix}woocommerce_order_items AS oi 
				LEFT JOIN {$wpdb->prefix}woocommerce_order_itemmeta AS c 
					ON oi.order_item_id = c.order_item_id AND c.meta_key = '_qty' 
				LEFT JOIN {$wpdb->prefix}woocommerce_order_itemmeta AS pt 
					ON oi.order_item_id = pt.order_item_id AND pt.meta_key = '_line_total' 
				WHERE oi.order_item_type = 'line_item' AND oi.order_id = p.ID) AS order_items";

				$order = $wpdb->prepare($order, $db_separator);
			}

			if ( $sync_order_status ) {
				$order .= ', p.post_status as status';
			}

			if ( $sync_total_items ) {
				$order .= ", (SELECT IFNULL(SUM(c.meta_value), 0 ) FROM {$wpdb->prefix}woocommerce_order_items AS oi JOIN {$wpdb->prefix}woocommerce_order_itemmeta AS c ON c.order_item_id = oi.order_item_id AND c.meta_key = '_qty' WHERE oi.order_id = p.ID) AS qty";
			}
			if ( $product_sku_sync ) {
				$product_id_query = "
					(
						SELECT IFNULL(
							GROUP_CONCAT(c.meta_value SEPARATOR ','), 
							'Sku is empty'
						) 
						FROM {$wpdb->prefix}woocommerce_order_items AS oi 
						LEFT JOIN {$wpdb->prefix}woocommerce_order_itemmeta AS c 
						ON oi.order_item_id = c.order_item_id 
						AND c.meta_key = %s 
						WHERE oi.order_id = p.ID
					) AS product_sku_sync
				";
				$prepared_query = $wpdb->prepare($product_id_query, '_product_id');
				$order .= ', ' . $prepared_query;
			}

			if ( $sync_total_price ) {
				$order .= ", MAX( CASE WHEN pm.meta_key = '_order_total' AND p.ID = pm.post_id THEN pm.meta_value END ) as order_total";
			}

			// For Free.
			if ( ! osgsw_is_ultimate_license_activated() ) {
				if ( ! $make_billing_shipping_seperate ) {
					if ( $billing_deatils && $billing_fields_enabled ) {
						$order .= ", CONCAT_WS(' ', ";
						$billing_concat_fields = [];

						if ( $billing_first_name ) {
							$billing_concat_fields[] = "MAX( CASE WHEN pm.meta_key = '_billing_first_name' AND p.ID = pm.post_id THEN pm.meta_value END )";
						}
						if ( $billing_last_name ) {
							$billing_concat_fields[] = "MAX( CASE WHEN pm.meta_key = '_billing_last_name' AND p.ID = pm.post_id THEN pm.meta_value END )";
						}
						if ( $billing_address_1 ) {
							$billing_concat_fields[] = "CONCAT('Address 1: ', IFNULL(MAX( CASE WHEN pm.meta_key = '_billing_address_1' AND p.ID = pm.post_id THEN pm.meta_value END ), ''))";
						}
						if ( $billing_city ) {
							$billing_concat_fields[] = "CONCAT('City: ', IFNULL(MAX( CASE WHEN pm.meta_key = '_billing_city' AND p.ID = pm.post_id THEN pm.meta_value END ), ''))";
						}
						if ( $billing_postcode ) {
							$billing_concat_fields[] = "CONCAT('Postcode: ', IFNULL(MAX( CASE WHEN pm.meta_key = '_billing_postcode' AND p.ID = pm.post_id THEN pm.meta_value END ), ''))";
						}
						if ( $billing_country ) {
							$billing_concat_fields[] = "CONCAT('Country: ', IFNULL(MAX( CASE WHEN pm.meta_key = '_billing_country' AND p.ID = pm.post_id THEN pm.meta_value END ), ''))";
						}

						$order .= implode(', ', $billing_concat_fields);
						$order .= ') as billing_details';
					}

					if ( $shipping_details && $shipping_fields_enabled ) {
						$order .= ", CONCAT_WS(' ', ";
						$shipping_concat_fields = [];

						if ( $shipping_first_name ) {
							$shipping_concat_fields[] = "MAX( CASE WHEN pm.meta_key = '_shipping_first_name' AND p.ID = pm.post_id THEN pm.meta_value END )";
						}
						if ( $shipping_last_name ) {
							$shipping_concat_fields[] = "MAX( CASE WHEN pm.meta_key = '_shipping_last_name' AND p.ID = pm.post_id THEN pm.meta_value END )";
						}
						if ( $shipping_address_1 ) {
							$shipping_concat_fields[] = "CONCAT('Address 1: ', IFNULL(MAX( CASE WHEN pm.meta_key = '_shipping_address_1' AND p.ID = pm.post_id THEN pm.meta_value END ), ''))";
						}
						if ( $shipping_city ) {
							$shipping_concat_fields[] = "CONCAT('City: ', IFNULL(MAX( CASE WHEN pm.meta_key = '_shipping_city' AND p.ID = pm.post_id THEN pm.meta_value END ), ''))";
						}
						if ( $shipping_postcode ) {
							$shipping_concat_fields[] = "CONCAT('Postcode: ', IFNULL(MAX( CASE WHEN pm.meta_key = '_shipping_postcode' AND p.ID = pm.post_id THEN pm.meta_value END ), ''))";
						}
						if ( $shipping_country ) {
							$shipping_concat_fields[] = "CONCAT('Country: ', IFNULL(MAX( CASE WHEN pm.meta_key = '_shipping_country' AND p.ID = pm.post_id THEN pm.meta_value END ), ''))";
						}

						$order .= implode(', ', $shipping_concat_fields);
						$order .= ') as shipping_details';
					}
				} else {
					// Billing address fields.
					if ( $billing_deatils && $billing_fields_enabled ) {
						if ( $billing_first_name ) {
							$order .= ", MAX( CASE WHEN pm.meta_key = '_billing_first_name' AND p.ID = pm.post_id THEN pm.meta_value END ) as billing_first_name";
						}
						if ( $billing_last_name ) {
							$order .= ", MAX( CASE WHEN pm.meta_key = '_billing_last_name' AND p.ID = pm.post_id THEN pm.meta_value END ) as billing_last_name";
						}
						if ( $billing_address_1 ) {
							$order .= ", MAX( CASE WHEN pm.meta_key = '_billing_address_1' AND p.ID = pm.post_id THEN pm.meta_value END ) as billing_address_1";
						}
						if ( $billing_city ) {
							$order .= ", MAX( CASE WHEN pm.meta_key = '_billing_city' AND p.ID = pm.post_id THEN pm.meta_value END ) as billing_city";
						}
						if ( $billing_postcode ) {
							$order .= ", MAX( CASE WHEN pm.meta_key = '_billing_postcode' AND p.ID = pm.post_id THEN pm.meta_value END ) as billing_postcode";
						}
						if ( $billing_country ) {
							$order .= ", MAX( CASE WHEN pm.meta_key = '_billing_country' AND p.ID = pm.post_id THEN pm.meta_value END ) as billing_country";
						}
					}

					// Shipping address fields.
					if ( $shipping_details && $shipping_fields_enabled ) {
						if ( $shipping_first_name ) {
							$order .= ", MAX( CASE WHEN pm.meta_key = '_shipping_first_name' AND p.ID = pm.post_id THEN pm.meta_value END ) as shipping_first_name";
						}
						if ( $shipping_last_name ) {
							$order .= ", MAX( CASE WHEN pm.meta_key = '_shipping_last_name' AND p.ID = pm.post_id THEN pm.meta_value END ) as shipping_last_name";
						}
						if ( $shipping_address_1 ) {
							$order .= ", MAX( CASE WHEN pm.meta_key = '_shipping_address_1' AND p.ID = pm.post_id THEN pm.meta_value END ) as shipping_address_1";
						}
						if ( $shipping_city ) {
							$order .= ", MAX( CASE WHEN pm.meta_key = '_shipping_city' AND p.ID = pm.post_id THEN pm.meta_value END ) as shipping_city";
						}
						if ( $shipping_postcode ) {
							$order .= ", MAX( CASE WHEN pm.meta_key = '_shipping_postcode' AND p.ID = pm.post_id THEN pm.meta_value END ) as shipping_postcode";
						}
						if ( $shipping_country ) {
							$order .= ", MAX( CASE WHEN pm.meta_key = '_shipping_country' AND p.ID = pm.post_id THEN pm.meta_value END ) as shipping_country";
						}
					}
				}
			}

			if ( defined('OSGSW_ULTIMATE_VERSION') && OSGSW_ULTIMATE_VERSION > '1.0.5' ) {
				$order = apply_filters('ossgs_order_post_fields', $order);
			} else {
				// backward compatibility for old version.
				if ( $total_discount ) {
					$order .= ", MAX( CASE WHEN pm.meta_key = '_cart_discount' AND p.ID = pm.post_id THEN pm.meta_value END ) as discount";
				}

				if ( $order_date ) {
					$order .= ', p.post_date';
				}
				if ( $payment_method ) {
					$order .= ", IFNULL(MAX( CASE WHEN pm.meta_key = '_payment_method' AND p.ID = pm.post_id THEN pm.meta_value END ), 'No Method Selected' ) as method_title";
				}
				if ( $customer_note ) {
					$order .= ", IFNULL(p.post_excerpt, 'No notes from customer' ) as customer_note";
				}
				if ( $who_place_order ) {
					$order .= ", IFNULL(
						CASE 
							WHEN MAX(CASE WHEN pm.meta_key = '_customer_user' AND p.ID = pm.post_id THEN pm.meta_value END) = 0 THEN 'Anonymous user'
							ELSE (SELECT u.user_login FROM {$wpdb->prefix}users u WHERE u.ID = MAX(CASE WHEN pm.meta_key = '_customer_user' AND p.ID = pm.post_id THEN pm.meta_value END))
						END, 
						'Anonymous user'
					) as who_place_order";
				}
				if ( $order_url ) {
					$order .= ', ';
					$order .= "CONCAT('" . $order_urls . "','post=',pm.post_id,'&action=edit')";
					$order .= ' as order_urls';
				}
				if ( $show_custom_meta_fields && $show_custom_fields ) {
					$custom_fields = osgsw_get_option( 'show_custom_fields' );
					foreach ( $custom_fields as $value ) {
						$item_extis = osgsw_divided_prefix($value, '(Itemmeta)');
						if ( $item_extis ) {
							$value2 = $item_extis['before'];
							$custom_field = $wpdb->prepare(
								"(
									SELECT IFNULL(
										GROUP_CONCAT(CONCAT(order_item_name, '(ssgsw_itemmeta_value:', c.meta_value, 'ssgsw_itemmeta_end)') SEPARATOR 'ssgsw_sep, '), 
										'** No Item Found **'
									) 
									FROM {$wpdb->prefix}woocommerce_order_items AS oi 
									LEFT JOIN {$wpdb->prefix}woocommerce_order_itemmeta AS c 
									ON oi.order_item_id = c.order_item_id 
									AND c.meta_key = %s 
									WHERE oi.order_id = p.ID
								) AS %s",
								$value2, $value
							);
							$order .= ', ' . $custom_field;
						} else {
							$custom_field = $wpdb->prepare("IFNULL(MAX( CASE WHEN pm.meta_key = %s AND p.ID = pm.post_id THEN pm.meta_value END ), '' ) as %s", $value, $value);
							$order .= ', ' . $custom_field;
						}
					}
				}
			}

			$order .= " FROM 
				{$wpdb->prefix}posts p 
				LEFT JOIN {$wpdb->prefix}postmeta pm ON p.ID = pm.post_id
				LEFT JOIN {$wpdb->prefix}woocommerce_order_items oi ON p.ID = oi.order_id
				LEFT JOIN {$wpdb->prefix}woocommerce_order_itemmeta b ON oi.order_item_id = b.order_item_id
				WHERE ";

			if ( $order_ids && count($order_ids) > 0 ) {
				$order .= 'p.ID IN (' . implode( ',', $order_ids ) . ') AND ';
			}
			$order .= "p.post_type = 'shop_order' ";
			if ( ! $trash ) {
				$order .= "AND p.post_status NOT IN ('trash', 'auto-draft') ";
			}

			// Add filter conditions for order status and date filtering.
			$order .= $this->build_filter_conditions( 'p', 'post_status', 'post_date' );
			$order .= 'GROUP BY p.ID';
			if ( $batch_size > 0 ) {
				$order .= $wpdb->prepare( ' LIMIT %d OFFSET %d', $batch_size, $offset );
			}
			$posts = $wpdb->get_results( $order, ARRAY_A ); //phpcs:ignore
			return $posts;
		}


		/**
		 * Get Hpos orders.
		 *
		 * @param int   $order_id for retribing single order.
		 * @param mixed $trash for deleting single order.
		 *
		 * @return array
		 */
		public function get_all_hpos_orders( $order_ids = [], $trash = false, $offset = 0, $batch_size = 0 ) {
			global $wpdb;

			// Core options.
			$total_discount = true === wp_validate_boolean(osgsw_get_option('total_discount', false));
			$order_date = true === wp_validate_boolean(osgsw_get_option('show_order_date', false));
			$payment_method = true === wp_validate_boolean(osgsw_get_option('show_payment_method', false));
			$transaction_id = true === wp_validate_boolean(osgsw_get_option('transaction_id', false));
			$customer_note = true === wp_validate_boolean(osgsw_get_option('show_customer_note', false));
			$order_url = true === wp_validate_boolean(osgsw_get_option('show_order_url', false));
			$who_place_order = true === wp_validate_boolean(osgsw_get_option('who_place_order', false));
			$show_product_qt = true === wp_validate_boolean(osgsw_get_option('show_product_qt', false));
			$show_custom_meta_fields = true === wp_validate_boolean(osgsw_get_option('show_custom_meta_fields', false));
			$show_custom_fields = true === wp_validate_boolean(osgsw_get_option('show_custom_fields', false));
			$sync_total_items = true === wp_validate_boolean(osgsw_get_option('sync_total_items', false));
			$sync_total_price = true === wp_validate_boolean(osgsw_get_option('sync_total_price', false));
			$product_sku_sync = wp_validate_boolean(get_option(OSGSW_PREFIX . 'product_sku_sync', false));

			$sync_order_products = wp_validate_boolean(get_option(OSGSW_PREFIX . 'sync_order_products', true));
			$sync_order_status = wp_validate_boolean(get_option(OSGSW_PREFIX . 'sync_order_status', true));

			$make_billing_shipping_seperate = true === wp_validate_boolean( osgsw_get_option( 'make_billing_shipping_seperate', false ) );
			$billing_deatils = true === wp_validate_boolean(osgsw_get_option('show_billing_details', false));
			$shipping_details = true === wp_validate_boolean(osgsw_get_option('add_shipping_details_sheet', false));

			// Billing address fields.
			$billing_fields_enabled = false;
			$billing_first_name = true === wp_validate_boolean(osgsw_get_option('show_billing_first_name', false));
			$billing_last_name = true === wp_validate_boolean(osgsw_get_option('show_billing_last_name', false));
			$billing_address_1 = true === wp_validate_boolean(osgsw_get_option('show_billing_address_1', false));
			$billing_city = true === wp_validate_boolean(osgsw_get_option('show_billing_city', false));
			$billing_postcode = true === wp_validate_boolean(osgsw_get_option('show_billing_postcode', false));
			$billing_country = true === wp_validate_boolean(osgsw_get_option('show_billing_country', false));

			// Check if any billing field is enabled.
			if ( $billing_first_name || $billing_last_name || $billing_address_1 || $billing_city || $billing_postcode || $billing_country ) {
				$billing_fields_enabled = true;
			}

			// Shipping address fields.
			$shipping_fields_enabled = false;
			$shipping_first_name = true === wp_validate_boolean(osgsw_get_option('show_shipping_first_name', false));
			$shipping_last_name = true === wp_validate_boolean(osgsw_get_option('show_shipping_last_name', false));
			$shipping_address_1 = true === wp_validate_boolean(osgsw_get_option('show_shipping_address_1', false));
			$shipping_city = true === wp_validate_boolean(osgsw_get_option('show_shipping_city', false));
			$shipping_postcode = true === wp_validate_boolean(osgsw_get_option('show_shipping_postcode', false));
			$shipping_country = true === wp_validate_boolean(osgsw_get_option('show_shipping_country', false));

			if ( $shipping_first_name || $shipping_last_name || $shipping_address_1 || $shipping_city || $shipping_postcode || $shipping_country ) {
				$shipping_fields_enabled = true;
			}

			$order_urls = esc_url(admin_url('post.php?'));
			$db_separator = 'ssgsw_wppool_' . get_separator() . ' ';
			$separator = get_separator();

			$order = 'SELECT p.id as order_id';

			if ( $sync_order_products ) {
				$order .= ", (SELECT IFNULL(GROUP_CONCAT(
					CONCAT(
						oi.order_item_name, 
						' (ssgsw_wppool_qty: ', 
						IFNULL(c.meta_value, '0'), 
						',ssgsw_wppool_price: ', 
						IFNULL(pt.meta_value, '0'), 
						')'
					) 
					SEPARATOR %s
				), '** No Products **')
				FROM {$wpdb->prefix}woocommerce_order_items AS oi 
				LEFT JOIN {$wpdb->prefix}woocommerce_order_itemmeta AS c 
					ON oi.order_item_id = c.order_item_id AND c.meta_key = '_qty' 
				LEFT JOIN {$wpdb->prefix}woocommerce_order_itemmeta AS pt 
					ON oi.order_item_id = pt.order_item_id AND pt.meta_key = '_line_total' 
				WHERE oi.order_item_type = 'line_item' AND oi.order_id = p.ID) AS order_items";

				$order = $wpdb->prepare($order, $db_separator);
			}

			if ( $sync_order_status ) {
				$order .= ', p.status';
			}

			if ( $sync_total_items ) {
				$order .= ", (SELECT IFNULL(SUM(c.meta_value), 0) 
					FROM {$wpdb->prefix}woocommerce_order_items AS oi 
					JOIN {$wpdb->prefix}woocommerce_order_itemmeta AS c 
					ON c.order_item_id = oi.order_item_id AND c.meta_key = '_qty' 
					WHERE oi.order_id = p.id) AS qty";
			}

			if ( $product_sku_sync ) {
				$product_id_query = "
					(SELECT IFNULL(
						GROUP_CONCAT(c.meta_value SEPARATOR ','), 
						'Sku is empty'
					) 
					FROM {$wpdb->prefix}woocommerce_order_items AS oi 
					LEFT JOIN {$wpdb->prefix}woocommerce_order_itemmeta AS c 
					ON oi.order_item_id = c.order_item_id 
					AND c.meta_key = %s 
					WHERE oi.order_id = p.id
					) AS product_sku_sync";
				$prepared_query = $wpdb->prepare($product_id_query, '_product_id');
				$order .= ', ' . $prepared_query;
			}

			if ( $sync_total_price ) {
				$order .= ', MAX(p.total_amount) as order_total';
			}

			if ( ! osgsw_is_ultimate_license_activated() ) {

				if ( ! $make_billing_shipping_seperate ) {

					if ( $billing_deatils && $billing_fields_enabled ) {
						$order .= ", CONCAT_WS(' ', ";
						$billing_concat_fields = [];

						if ( $billing_first_name ) {
							$billing_concat_fields[] = "IFNULL(ba.first_name, '')";
						}
						if ( $billing_last_name ) {
							$billing_concat_fields[] = "IFNULL(ba.last_name, '')";
						}

						if ( $billing_address_1 ) {
							$billing_concat_fields[] = "IFNULL(CONCAT('Address 1: ', ba.address_1), '')";
						}

						if ( $billing_city ) {
							$billing_concat_fields[] = "IFNULL(CONCAT('City: ', ba.city), '')";
						}

						if ( $billing_postcode ) {
							$billing_concat_fields[] = "IFNULL(CONCAT('Postcode: ', ba.postcode), '')";
						}
						if ( $billing_country ) {
							$billing_concat_fields[] = "IFNULL(CONCAT('Country: ', ba.country), '')";
						}

						$order .= implode(', ', $billing_concat_fields);
						$order .= ') as billing_details';
					}

					if ( $shipping_details && $shipping_fields_enabled ) {
						$order .= ", CONCAT_WS(' ', ";
						$shipping_concat_fields = [];

						if ( $shipping_first_name ) {
							$shipping_concat_fields[] = "IFNULL(sa.first_name, '')";
						}
						if ( $shipping_last_name ) {
							$shipping_concat_fields[] = "IFNULL(sa.last_name, '')";
						}

						if ( $shipping_address_1 ) {
							$shipping_concat_fields[] = "IFNULL(CONCAT('Address 1: ', sa.address_1), '')";
						}

						if ( $shipping_city ) {
							$shipping_concat_fields[] = "IFNULL(CONCAT('City: ', sa.city), '')";
						}

						if ( $shipping_postcode ) {
							$shipping_concat_fields[] = "IFNULL(CONCAT('Postcode: ', sa.postcode), '')";
						}
						if ( $shipping_country ) {
							$shipping_concat_fields[] = "IFNULL(CONCAT('Country: ', sa.country), '')";
						}

						$order .= implode(', ', $shipping_concat_fields);
						$order .= ') as shipping_details';
					}
				} else {
					// Free part Billing address fields.
					if ( $billing_deatils && $billing_fields_enabled ) {
						if ( $billing_first_name ) {
							$order .= ", IFNULL(ba.first_name, '') as billing_first_name";
						}
						if ( $billing_last_name ) {
							$order .= ", IFNULL(ba.last_name, '') as billing_last_name";
						}
						if ( $billing_address_1 ) {
							$order .= ", IFNULL(ba.address_1, '') as billing_address_1";
						}
						if ( $billing_city ) {
							$order .= ", IFNULL(ba.city, '') as billing_city";
						}
						if ( $billing_postcode ) {
							$order .= ", IFNULL(ba.postcode, '') as billing_postcode";
						}
						if ( $billing_country ) {
							$order .= ", IFNULL(ba.country, '') as billing_country";
						}
					}

					// Free part Shipping address fields.
					if ( $shipping_details && $shipping_fields_enabled ) {
						if ( $shipping_first_name ) {
							$order .= ", IFNULL(sa.first_name, '') as shipping_first_name";
						}
						if ( $shipping_last_name ) {
							$order .= ", IFNULL(sa.last_name, '') as shipping_last_name";
						}
						if ( $shipping_address_1 ) {
							$order .= ", IFNULL(sa.address_1, '') as shipping_address_1";
						}
						if ( $shipping_city ) {
							$order .= ", IFNULL(sa.city, '') as shipping_city";
						}
						if ( $shipping_postcode ) {
							$order .= ", IFNULL(sa.postcode, '') as shipping_postcode";
						}
						if ( $shipping_country ) {
							$order .= ", IFNULL(sa.country, '') as shipping_country";
						}
					}
				}
			}

			if ( defined('OSGSW_ULTIMATE_VERSION') && OSGSW_ULTIMATE_VERSION > '1.0.5' ) {
				$order = apply_filters('ossgs_order_hpos_fields', $order);
			} else {
				// backward compatibility for old version.
				if ( $total_discount ) {
					$order .= ', MAX(cf.discount_total_amount) as discount';
				}
				if ( $order_date ) {
					$order .= ', p.date_created_gmt';
				}
				if ( $payment_method ) {
					$order .= ", IFNULL(p.payment_method_title, 'No Method Selected') as method_title";
				}
				if ( $customer_note ) {
					$order .= ", IFNULL(p.customer_note, 'No notes from customer') as customer_note";
				}
				if ( $who_place_order ) {
					$order .= ", IFNULL(
						CASE 
							WHEN MAX(p.customer_id) = 0 THEN 'Anonymous user'
							ELSE (SELECT u.user_login FROM {$wpdb->prefix}users u WHERE u.ID = p.customer_id)
						END, 
						'Anonymous user'
					) as who_place_order";
				}
				if ( $order_url ) {
					$order .= ', ';
					$order .= "CONCAT('" . $order_urls . "','post=',p.id,'&action=edit')";
					$order .= ' as order_urls';
				}
				if ( $show_custom_meta_fields && $show_custom_fields ) {
					$custom_fields = osgsw_get_option('show_custom_fields');
					foreach ( $custom_fields as $value ) {
						$item_extis = osgsw_divided_prefix($value, '(Itemmeta)');
						if ( $item_extis ) {
							$value2 = $item_extis['before'];
							$custom_field = $wpdb->prepare(
								"(
									SELECT IFNULL(
										GROUP_CONCAT(CONCAT(order_item_name, '(ssgsw_itemmeta_value:', c.meta_value, 'ssgsw_itemmeta_end)') SEPARATOR 'ssgsw_sep, '), 
										'** No Item Found **'
									) 
									FROM {$wpdb->prefix}woocommerce_order_items AS oi 
									LEFT JOIN {$wpdb->prefix}woocommerce_order_itemmeta AS c 
									ON oi.order_item_id = c.order_item_id 
									AND c.meta_key = %s 
									WHERE oi.order_id = p.id
								) AS %s",
								$value2, $value
							);
							$order .= ', ' . $custom_field;
						} else {
							$custom_field = $wpdb->prepare("IFNULL(MAX(CASE WHEN pm.meta_key = %s AND p.id = pm.order_id THEN pm.meta_value END), '') as %s", $value, $value);
							$order .= ', ' . $custom_field;
						}
					}
				}
			}

			// Base tables.
			$order .= " FROM {$wpdb->prefix}wc_orders p 
				LEFT JOIN {$wpdb->prefix}wc_orders_meta pm ON p.id = pm.order_id
				LEFT JOIN {$wpdb->prefix}woocommerce_order_items oi ON p.id = oi.order_id
				LEFT JOIN {$wpdb->prefix}woocommerce_order_itemmeta b ON oi.order_item_id = b.order_item_id 
				LEFT JOIN {$wpdb->prefix}wc_order_operational_data cf ON p.id = cf.order_id";

			// Others table to join billing addresses.
			if ( $billing_deatils ) {
				$order .= " LEFT JOIN {$wpdb->prefix}wc_order_addresses ba ON p.id = ba.order_id AND ba.address_type = 'billing'";
			}

			// Add join for shipping addresses.
			if ( $shipping_details ) {
				$order .= " LEFT JOIN {$wpdb->prefix}wc_order_addresses sa ON p.id = sa.order_id AND sa.address_type = 'shipping'";
			}

			$order .= ' WHERE ';
			if ( $order_ids && count($order_ids) > 0 ) {
				$order .= 'p.id IN (' . implode(',', $order_ids) . ') AND ';
			}
			$order .= "p.type = 'shop_order' ";
			if ( ! $trash ) {
				$order .= "AND p.status NOT IN ('trash', 'auto-draft') ";
			}

			// Add filter conditions for order status and date filtering.
			$order .= $this->build_filter_conditions( 'p', 'status', 'date_created_gmt' );
			$order .= 'GROUP BY p.id';
			if ( $batch_size > 0 ) {
				$order .= $wpdb->prepare(' LIMIT %d OFFSET %d', $batch_size, $offset);
			}
			$posts = $wpdb->get_results($order, ARRAY_A); //phpcs:ignore
			return $posts;
		}

		/**
		 * Find out row key form google sheets info.
		 *
		 * @param array $data sheet information.
		 * @param mixed $id product id.
		 *
		 * @return mixed
		 */
		public function find_out_range_row( $data, $id ) {
			$new_index = null;
			foreach ( $data as $row => $row_data ) {
				if ( is_array($row_data) && array_key_exists( 0, $row_data ) ) {
					if ( $row_data[0] == $id ) { //phpcs:ignore
						$new_index = $row + 1;
						break;
					}
				}
			}
			return $new_index;
		}
		/**
		 * Get first sheets data and compare id exits and resturn range.
		 *
		 * @param mixed $id product id.
		 * @param mixed $data first sheets data.
		 *
		 * @return mixed
		 */
		public function find_out_range( $id, $data ) {
			$index_informations = osgsw_format_index_number($data);
			$order_items = get_option('osgsw_multiple_itmes', false );
			$first_index = osgsw_get_index_number_option($id, $index_informations);
			if ( $order_items ) {
				$get_prev_values = osgsw_get_previous_value($index_informations, $id);
				if ( $get_prev_values ) {
					$first_index = $get_prev_values + 2;
				}
			}
			return $first_index;
		}

		/**
		 * Bulk update billing and shipping information for multiple orders.
		 *
		 * @param array $orders Array of orders to update.
		 * @param int   $start_index Starting index for the sheet.
		 * @param int   $end_index Ending index for the sheet.
		 *
		 * @return bool True on success, false on failure.
		 */
		public function bulk_chank_update( $orders = [], $start_index = 2, $end_index = 2 ) {
			global $wpdb;
			$get_bulk_edit_option = wp_validate_boolean(get_option( 'osgsw_bulk_edit_option2', 1));
			$order_data_storage_option = get_option( 'woocommerce_custom_orders_table_enabled' );
			$order_ids = [];
			$case_statements = [];
			$sheet = new Sheet();
			$sheets_info = $sheet->get_first_columns();
			if ( empty($sheets_info) ) {
				$sheets_info = $sheet->get_first_columns();
			}
			$sheet_index = osgsw_format_index_number($sheets_info);
			// Check if plugin is ready to use.
			foreach ( $orders as $key => $order ) {
				// Decode the JSON string to access nested properties.
				$extra_data = json_decode($order['osgsw_extra_data_info'], true);
				$order_id = isset($extra_data['order_Id']) ? $extra_data['order_Id'] : null;
				$order_status = isset($order['order_status']) ? $order['order_status'] : null;

				if ( $order_status && $order_id ) {
					if ( ! in_array($order_id, $order_ids) ) {
						$case_statements[] = $wpdb->prepare('WHEN %d THEN %s', $order_id, $order_status);
						$order_ids[] = $order_id;
					}
				} else if ( ! $order_status && $order_id ) {
					$order_ids[] = $order_id;
				}

				if ( ! $get_bulk_edit_option ) {
					return false;
				}
			}
			if ( ! empty($order_ids) ) {
				$this->batch_update_table($case_statements, $order_ids, $wpdb, $order_data_storage_option );
				$start_index = isset($orders[0]['index_number']) ? $orders[0]['index_number'] : false;
				$order_items = get_option('osgsw_multiple_itmes', false );
				if ( $order_items ) {
					$first_id = isset($orders[0]['order_Id']) ? $orders[0]['order_Id'] : false;
					if ( $start_index && $first_id ) {
						$find_index_from_sheet = osgsw_get_index_number_option($first_id, $sheet_index);
						if ( $find_index_from_sheet ) {
							if ( $find_index_from_sheet !== $start_index ) {
								$get_prev_values = osgsw_get_previous_value($sheet_index, $first_id);
								if ( $get_prev_values ) {
									$start_index = $get_prev_values + 2;
								}
							}
						}
					}
				}
				$order_ids = array_unique($order_ids);
				$retrive_informations = $this->get_multiple_order_informations($order_ids);
				$update_values = $sheet->update_multiple_row_values($retrive_informations, $start_index );
				if ( ! $update_values ) {
					$sheet->update_multiple_row_values($retrive_informations, $start_index );
				}
			}

			return true;
		}


		/**
		 * Update billing and shipping information in WooCommerce.
		 *
		 * @param array  $billing_shipping_data Array of billing and shipping data.
		 * @param object $wpdb WordPress database object.
		 * @param bool   $order_data_storage_option Whether to use custom orders table.
		 */
		public function bulk_billing_shipping_update( $orders = [], $start_index = 2, $end_index = 2 ) {
			global $wpdb;
			$get_bulk_edit_option = wp_validate_boolean(get_option( 'osgsw_bulk_edit_option2', 1));
			$order_data_storage_option = get_option( 'woocommerce_custom_orders_table_enabled' );
			$order_ids = [];
			$billing_shipping_data = [];
			$sheet = new Sheet();
			$sheets_info = $sheet->get_first_columns();
			if ( empty($sheets_info) ) {
				$sheets_info = $sheet->get_first_columns();
			}
			$sheet_index = osgsw_format_index_number($sheets_info);

			// Check if plugin is ready to use.
			foreach ( $orders as $key => $order ) {
				// Decode the JSON string to access nested properties.
				$extra_data = json_decode($order['osgsw_extra_data_info'], true);
				$order_id = isset($extra_data['order_Id']) ? $extra_data['order_Id'] : null;

				if ( $order_id ) {
					$order_ids[] = $order_id;

					// Extract billing and shipping data from the order
					$billing_shipping_info = $this->extract_billing_shipping_data( $order, $extra_data );
					if ( ! empty( $billing_shipping_info ) ) {
						$billing_shipping_data[ $order_id ] = $billing_shipping_info;
					}
				}

				if ( ! $get_bulk_edit_option ) {
					return false;
				}
			}

			if ( ! empty($order_ids) && ! empty($billing_shipping_data) ) {
				// Update billing and shipping information in WooCommerce
				$this->batch_update_billing_shipping( $billing_shipping_data, $wpdb, $order_data_storage_option );

				$start_index = isset($orders[0]['index_number']) ? $orders[0]['index_number'] : false;
				$order_items = get_option('osgsw_multiple_itmes', false );
				if ( $order_items ) {
					$first_id = isset($orders[0]['order_Id']) ? $orders[0]['order_Id'] : false;
					if ( $start_index && $first_id ) {
						$find_index_from_sheet = osgsw_get_index_number_option($first_id, $sheet_index);
						if ( $find_index_from_sheet ) {
							if ( $find_index_from_sheet !== $start_index ) {
								$get_prev_values = osgsw_get_previous_value($sheet_index, $first_id);
								if ( $get_prev_values ) {
									$start_index = $get_prev_values + 2;
								}
							}
						}
					}
				}

				$order_ids = array_unique($order_ids);
				$retrive_informations = $this->get_multiple_order_informations($order_ids);
				$update_values = $sheet->update_multiple_row_values($retrive_informations, $start_index );
				if ( ! $update_values ) {
					$sheet->update_multiple_row_values($retrive_informations, $start_index );
				}
			}

			return true;
		}

		/**
		 * Extract billing and shipping data from order array
		 *
		 * @param array $order The order data
		 * @param array $extra_data The decoded extra data
		 * @return array Billing and shipping data
		 */
		private function extract_billing_shipping_data( $order, $extra_data ) {
			$billing_shipping_fields = [
				// Billing fields
				'show_billing_first_name' => '_billing_first_name',
				'show_billing_last_name' => '_billing_last_name',
				'show_billing_address_1' => '_billing_address_1',
				'show_billing_city' => '_billing_city',
				'show_billing_postcode' => '_billing_postcode',
				'show_billing_country' => '_billing_country',
				'show_billing_address_2' => '_billing_address_2',
				'show_billing_company' => '_billing_company',
				'show_billing_state' => '_billing_state',
				'show_billing_email' => '_billing_email',
				'show_billing_phone' => '_billing_phone',
				// Shipping fields
				'show_shipping_first_name' => '_shipping_first_name',
				'show_shipping_last_name' => '_shipping_last_name',
				'show_shipping_address_1' => '_shipping_address_1',
				'show_shipping_city' => '_shipping_city',
				'show_shipping_postcode' => '_shipping_postcode',
				'show_shipping_country' => '_shipping_country',
				'show_shipping_address_2' => '_shipping_address_2',
				'show_shipping_company' => '_shipping_company',
				'show_shipping_state' => '_shipping_state',
				'show_shipping_email' => '_shipping_email',
				'show_shipping_phone' => '_shipping_phone',
			];

			$extracted_data = [];

			// Check both order array and extra_data for fields
			foreach ( $billing_shipping_fields as $field_key => $meta_key ) {
				$value = null;

				// First check in the main order array
				if ( isset( $order[ $field_key ] ) ) {
					$value = $order[ $field_key ];
				}
				// Then check in extra_data
				elseif ( isset( $extra_data[ $field_key ] ) ) {
					$value = $extra_data[ $field_key ];
				}

				if ( $value !== null ) {
					$extracted_data[ $meta_key ] = $value;
				}
			}

			return $extracted_data;
		}

		/**
		 * Batch update billing and shipping information for multiple orders
		 *
		 * @param array  $billing_shipping_data Array of order_id => billing/shipping data
		 * @param object $wpdb WordPress database object
		 * @param string $order_data_storage_option WooCommerce order storage option
		 * @return bool Success status
		 */
		public function batch_update_billing_shipping( $billing_shipping_data, $wpdb, $order_data_storage_option ) {
			if ( 'yes' === $order_data_storage_option ) {
				// Use WooCommerce HPOS (High-Performance Order Storage)
				return $this->batch_update_billing_shipping_hpos( $billing_shipping_data, $wpdb );
			} else {
				// Use traditional post meta
				return $this->batch_update_billing_shipping_postmeta( $billing_shipping_data, $wpdb );
			}
		}

		/**
		 * Update billing/shipping data using WooCommerce CRUD API (for HPOS)
		 *
		 * @param array  $billing_shipping_data Array of order_id => billing/shipping data
		 * @param object $wpdb WordPress database object
		 * @return bool Success status
		 */
		private function batch_update_billing_shipping_hpos( $billing_shipping_data, $wpdb ) {
			$success = true;
			foreach ( $billing_shipping_data as $order_id => $fields ) {
				$order = wc_get_order( $order_id );
				if ( $order ) {
					foreach ( $fields as $meta_key => $meta_value ) {
						if ( strpos( $meta_key, '_billing_' ) === 0 ) {
							$field = substr( $meta_key, 9 );
							$setter = 'set_billing_' . $field;
							if ( method_exists( $order, $setter ) ) {
								$order->$setter( $meta_value );
							} else {
								$order->update_meta_data( $meta_key, $meta_value );
							}
						} elseif ( strpos( $meta_key, '_shipping_' ) === 0 ) {
							$field = substr( $meta_key, 10 );
							$setter = 'set_shipping_' . $field;
							if ( method_exists( $order, $setter ) ) {
								$order->$setter( $meta_value );
							} else {
								$order->update_meta_data( $meta_key, $meta_value );
							}
						} else {
							$order->update_meta_data( $meta_key, $meta_value );
						}
					}
					$order->save();
				} else {
					$success = false;
				}
			}
			return $success;
		}

		/**
		 * Update billing/shipping data using WooCommerce CRUD API (for traditional post meta)
		 *
		 * @param array  $billing_shipping_data Array of order_id => billing/shipping data
		 * @param object $wpdb WordPress database object
		 * @return bool Success status
		 */
		private function batch_update_billing_shipping_postmeta( $billing_shipping_data, $wpdb ) {
			$success = true;
			foreach ( $billing_shipping_data as $order_id => $fields ) {
				$order = wc_get_order( $order_id );
				if ( $order ) {
					foreach ( $fields as $meta_key => $meta_value ) {
						if ( strpos( $meta_key, '_billing_' ) === 0 ) {
							$field = substr( $meta_key, 9 );
							$setter = 'set_billing_' . $field;
							if ( method_exists( $order, $setter ) ) {
								$order->$setter( $meta_value );
							} else {
								$order->update_meta_data( $meta_key, $meta_value );
							}
						} elseif ( strpos( $meta_key, '_shipping_' ) === 0 ) {
							$field = substr( $meta_key, 10 );
							$setter = 'set_shipping_' . $field;
							if ( method_exists( $order, $setter ) ) {
								$order->$setter( $meta_value );
							} else {
								$order->update_meta_data( $meta_key, $meta_value );
							}
						} else {
							$order->update_meta_data( $meta_key, $meta_value );
						}
					}
					$order->save();
				} else {
					$success = false;
				}
			}
			return $success;
		}

		/**
		 * Update billing/shipping data for a single order using WooCommerce CRUD API
		 *
		 * @param int   $order_id The order ID
		 * @param array $fields Array of meta_key => value for billing/shipping
		 * @return bool Success status
		 */
		public function single_billing_shipping_update( $order_id, $fields ) {
			$order = wc_get_order( $order_id );
			if ( $order ) {
				foreach ( $fields as $meta_key => $meta_value ) {
					if ( strpos( $meta_key, '_billing_' ) === 0 ) {
						$field = substr( $meta_key, 9 );
						$setter = 'set_billing_' . $field;
						if ( method_exists( $order, $setter ) ) {
							$order->$setter( $meta_value );
						} else {
							$order->update_meta_data( $meta_key, $meta_value );
						}
					} elseif ( strpos( $meta_key, '_shipping_' ) === 0 ) {
						$field = substr( $meta_key, 10 );
						$setter = 'set_shipping_' . $field;
						if ( method_exists( $order, $setter ) ) {
							$order->$setter( $meta_value );
						} else {
							$order->update_meta_data( $meta_key, $meta_value );
						}
					} else {
						$order->update_meta_data( $meta_key, $meta_value );
					}
				}
				$order->save();
				return true;
			}
			return false;
		}

		/**
		 * Update billing and shipping information in WooCommerce.
		 *
		 * @param array $orders Orders.
		 *
		 * @return mixed
		 */
		public function bulk_billing_shipping_update_simple( $orders = [] ) {
			return $this->bulk_billing_shipping_update( $orders );
		}




		/**
		 * Performs a batch update on the `status` column of either the `wc_orders` or `posts` table.
		 * The function uses a `CASE` statement to update multiple rows in one query.
		 *
		 * @param array  $case_statements         An array of `CASE` statement parts to update the `status` field.
		 *                                       Each element should be a string like `WHEN <order_id> THEN '<status>'`.
		 * @param array  $order_ids               An array of order IDs (or post IDs) to update.
		 * @param object $wpdb                    The WordPress `$wpdb` object used to interact with the database.
		 * @param string $order_data_storage_option Indicates where the order data is stored:
		 *                                       'yes' for the `wc_orders` table, and 'no' for the `posts` table.
		 *
		 * @return bool  Returns `true` if the update was successful, `false` otherwise.
		 *               Returns `false` if invalid data is provided or the query fails.
		 */
		public function batch_update_table( $case_statements, $order_ids, $wpdb, $order_data_storage_option ) {

			$case_sql = implode( ' ', $case_statements );
			$order_ids_sql = implode( ',', array_map( 'intval', $order_ids ) );

			if ( 'yes' === $order_data_storage_option ) {
				$table_name = $wpdb->prefix . 'wc_orders';
				$key_column = 'id';
				$status = 'status';
			} else {
				$table_name = $wpdb->prefix . 'posts';
				$key_column = 'ID';
				$status = 'post_status';
			}

			$query = "
				UPDATE $table_name
				SET $status = CASE $key_column
					$case_sql
				END
				WHERE $key_column IN ($order_ids_sql)
			";

			$result = $wpdb->query( $query );
			return $result !== false;
		}

		/**
		 * Update orders.
		 *
		 * @param array $orders Orders.
		 *
		 * @return mixed
		 */
		public function bulk_update( $orders = [] ) {

			$column = new Column();

			$get_bulk_edit_option = wp_validate_boolean(get_option( 'osgsw_bulk_edit_option2', 1));
			$sheet = new Sheet();
			$sheets_info = [];
			if ( is_array($orders) && ! array_key_exists( 'index_number', $orders['0'] ) ) {
				$sheets_info = $sheet->get_first_columns();
			}
			// Check if plugin is ready to use.
			foreach ( $orders as $key => $order ) {
				if ( isset($order['order_status']) && isset($order['order_Id']) ) {
					$this->update_hpos_order_status($order['order_status'], $order['order_Id']);
					$this->update_post_table_order_status($order['order_status'], $order['order_Id']);
				}
				if ( isset($order['order_Id']) && ! empty($order['order_Id']) ) {
					if ( ! empty($sheets_info) ) {
						$this->batch_update_delete_and_append($order['order_Id'], 'update', null,$sheets_info);
					} else {
						$find_out_range = $this->find_out_child_ranges($order['product_names'], $order['index_number'] );
						$start_index = isset($find_out_range[0]) ? $find_out_range[0] : null;
						$end_index = isset($find_out_range[1]) ? $find_out_range[1] : null;
						$this->batch_update_delete_and_append($order['order_Id'], 'update', $start_index, [], $end_index);
					}
				}
				if ( ! $get_bulk_edit_option ) {
					return false;
				}
			}
		}
		/**
		 *  Find out child and parent ranges from the text.
		 *
		 * @return string
		 */
		public function find_out_child_ranges( $input_text, $index_number ) {
			$order_items = get_option('osgsw_multiple_itmes', false );
			if ( $order_items ) {
				if ( preg_match('/\[(\d+) Products\]\s*$/', $input_text, $matches) ) {
					// Match [X Products] at the end of the string.
					$value = isset($matches[1]) ? intval($matches[1]) : null;
					if ( $value ) {
						return [
							$index_number,
							$index_number + $value,
						];
					}
				}
				if ( preg_match('/\[Product (\d+) of (\d+)\]\s*$/', $input_text, $matches) ) {
					// Match [Product Y of Z] at the end of the string.
					if ( ! empty($matches) ) {
						$first = isset($matches[1]) ? intval($matches[1]) : 1;
						$end = isset($matches[2]) ? intval($matches[2]) : 1;
						$first_index = $index_number - $first;
						$last_index = $first_index + $end;
						return [
							$first_index,
							$last_index,
						];
					}
				}
			}

			// Default return if no matches.
			return [
				$index_number,
				$index_number,
			];
		}
		/**
		 * Get Single order information.
		 *
		 * @param mixed $order_id order id.
		 *
		 * @return mixed
		 */
		public function get_multiple_order_informations( $order_ids ) {

			$order_data_storage_option = get_option( 'woocommerce_custom_orders_table_enabled' );
			if ( 'yes' === $order_data_storage_option ) {
				$data = $this->get_all_hpos_orders($order_ids, true);
			} else {
				$data = $this->get_all_orders($order_ids, true);
			}

			$columns = new Column();
			$header = $columns->get_column_names();
			$data_with_header = $this->marge_header_with_data($data, $offset = 0);

			$rearranged_data = $this->get_header_with_values($data_with_header);
			$data_with_header = $this->duplicate_based_on_product_names($rearranged_data);
			// Remove the header row from the final data.
			$data_without_header = $this->remove_header_row($data_with_header);
			return $data_without_header;
		}

		/**
		 * Merge header with data- Remove the first row (header).
		 *
		 * @param array $data data.
		 * @param int   $offset offset.
		 *
		 * @return array
		 */
		public function remove_header_row( $data ) {
			if ( empty($data) || count($data) <= 1 ) {
				return [];
			}
			array_shift($data);
			return $data;
		}


		/**
		 * Get Single order information.
		 *
		 * @param mixed $order_id order id.
		 *
		 * @return mixed
		 */
		public function get_single_order( $order_id ) {
			$order_data_storage_option = get_option( 'woocommerce_custom_orders_table_enabled' );
			if ( 'yes' === $order_data_storage_option ) {
				$data = $this->get_all_hpos_orders([ $order_id ], true);
			} else {
				$data = $this->get_all_orders([ $order_id ], true);
			}

			$columns = new Column();
			$header = $columns->get_column_names();
			$data_with_header = $this->marge_header_with_data($data, $offset = 0);

			$rearranged_data = $this->get_header_with_values($data_with_header);

			// data need to rearrange here.
			$data_with_header = $this->duplicate_based_on_product_names($rearranged_data);
			// Remove the header row from the final data.
			$data_without_header = $this->remove_header_row($data_with_header);
			return $data_without_header;
		}
		/**
		 * Update delete and append order in google sheets check by id.
		 *
		 * @param mixed  $order_id product id.
		 * @param string $type update type.
		 * @param string $range sheet index.
		 * @param array  $sheets sheets data.
		 *
		 * @return boolean
		 */
		public function batch_update_delete_and_append( $order_id, $type = 'update', $start_range = null, $sheets = [], $end_range = null ) {
			try {
				if ( ! $this->app->is_plugin_ready() ) {
					return __('Plugin is not ready to use.', 'order-sync-with-google-sheets-for-woocommerce');
				}
				$sheet = new Sheet();
				$values = $this->get_single_order($order_id);
				$values_count = is_array($values) ? count($values) : 0;
				if ( $start_range != null ) { //phpcs:ignore
					if ( ! empty($values) ) {
						$check_false = $sheet->update_single_row_values($start_range, $values, null, $end_range );
						if ( ! $check_false ) {
							$sheet->update_single_row_values($start_range, $values, null, $end_range );
						}
					}
				} else {
					if ( 'append' === $type ) {
						if ( ! empty($values) ) {

							$sheets = $sheet->get_first_columns();
							if ( empty($sheets) ) {
								$sheets = $sheet->get_first_columns();
							}

							$existing_range = $this->find_out_range($order_id, $sheets);
							if ( $existing_range !== null ) {
								// Order already exists, update instead of append
								$values_count = is_array($values) ? count($values) : 0;
								if ( $values_count > 1 ) {
									$end_range = $existing_range + $values_count;
									$max = $end_range - 1;
								} else {
									$max = $existing_range;
								}
								$check_error = $sheet->update_single_row_values($existing_range, $values, null, $max);
								if ( ! $check_error ) {
									$sheet->update_single_row_values($existing_range, $values, null, $max);
								}
							} else {
								// Order doesn't exist, safe to append
								$new_add = $sheet->append_new_row($values);
								if ( ! $new_add ) {
									$sheet->append_new_row($values);
								}
							}
						}
					} else {
						if ( 'update' === $type ) {
							$range = $this->find_out_range($order_id, $sheets);
							if ( $range !== null ) { //phpcs:ignore
								if ( $values_count > 1 ) {
									$end_range = $range + $values_count;
									$max = $end_range - 1;
								} else {
									$max = $range;
								}
								$check_error = $sheet->update_single_row_values($range, $values, null, $max);
								if ( ! $check_error ) {
									$sheet->update_single_row_values($range, $values, null, $max);
								}
							} else {
								$new_add = $sheet->append_new_row($values);
								if ( ! $new_add ) {
									$sheet->append_new_row($values);
								}
							}
						} else if ( 'trash' === $type ) {
							$range = $this->find_out_range($order_id, $sheets);
							if ( $range !== null ) { //phpcs:ignore
								if ( $values_count > 1 ) {
									$end_range = $range + $values_count;
									$max = $end_range - 1;
								} else {
									$max = $range;
								}
								$check_delete = $sheet->delete_single_row($range, $max);
								if ( ! $check_delete ) {
									$check_delete = $sheet->delete_single_row($range, $max);
								}
							}
						} else if ( 'untrash' === $type ) {
							$check_back = $sheet->append_new_row($values, 'untrash');
							if ( ! $check_back ) {
								$sheet->append_new_row($values, 'untrash');
							}
						}
					}
				}
				return true;
			} catch ( \Exception $e ) {
				update_option('osgsw_license_sync', true);
			}
		}
		/**
		 * Update Hpos order status.
		 *
		 * @param string $order_status order status.
		 * @param int    $order_id order id.
		 *
		 * @return mixed
		 */
		public function update_hpos_order_status( $order_status = '', $order_id = '' ) {
			global $wpdb;
			$table_name = $wpdb->prefix . 'wc_orders';
			$update_status = $wpdb->update(
				$table_name,
				[ 'status' => $order_status ],
				[ 'id' => $order_id ],
			);
			if ( $update_status ) {
				return true;
			} else {
				return false;
			}
		}
		/**
		 * Update post table order status.
		 *
		 * @param string $order_status order status.
		 * @param int    $order_id order id.
		 *
		 * @return mixed
		 */
		public function update_post_table_order_status( $order_status = '', $order_id = '' ) {
			$order_data = array(
				'ID'          => $order_id,
				'post_status' => $order_status,
			);
			$update_post = wp_update_post( $order_data );
			if ( $update_post ) {
				return true;
			} else {
				return false;
			}
		}
	}
}
