<?php

namespace WPKIN_SHOP_MANAGER\API\AdminApi;

use Error;

if ( ! defined( 'ABSPATH' ) ) {
	wp_die( esc_html__( 'You can\'t access this page', 'shop-manager' ) );
}

/**
 * ApiHelper
 *
 * @since 1.0.0
 */
class ApiHelper {


	/**
	 * ================= Start Dashboard Stats Helper ===============================
	 */

	public function wpkin_get_wc_order_statuses() {
		// Get all registered order statuses
		$wc_statuses = array_keys( wc_get_order_statuses() );

		// Get additional statuses that might be used
		$additional_statuses = [ 'draft', 'publish' ];

		// Combine and filter the statuses
		$all_statuses = array_unique( array_merge( $wc_statuses, $additional_statuses ) );

		// Filter out auto-draft and trash statuses
		$filtered_statuses = array_diff( $all_statuses, [ 'auto-draft', 'trash' ] );

		// Get actual used statuses
		$used_statuses = $this->wpkin_get_used_order_statuses();

		// Combine all statuses and remove any duplicates
		return array_unique( array_merge( $filtered_statuses, $used_statuses ) );
	}

	private function wpkin_get_used_order_statuses() {
		$transient_name = 'wpkin_used_order_statuses';
		$used_statuses = get_transient( $transient_name );

		if ( $used_statuses === false ) {
			$orders = wc_get_orders(
				[
					'limit' => -1,
					'return' => 'ids',
				]
			);

			$used_statuses = [];
			foreach ( $orders as $order_id ) {
				$order = wc_get_order( $order_id );
				if ( $order ) {
					$used_statuses[] = $order->get_status();
				}
			}

			$used_statuses = array_unique( $used_statuses );
			set_transient( $transient_name, $used_statuses, HOUR_IN_SECONDS );
		}

		return $used_statuses;
	}

	public function wpkin_get_total_sales( $date_range = null ) {
		$cache_key = 'wpkin_total_sales_' . md5( wp_json_encode( $date_range ) );
		$total_sales = wp_cache_get( $cache_key );

		if ( false === $total_sales ) {
			$order_statuses = $this->wpkin_get_wc_order_statuses();
			$args = [
				'status' => $order_statuses,
				'return' => 'ids',
				'limit' => -1,
			];

			if ( $date_range ) {
				$args['date_created'] = $date_range['start'] . '...' . $date_range['end'];
			}

			$orders = wc_get_orders( $args );

			$total_sales = 0;
			foreach ( $orders as $order_id ) {
				$order = wc_get_order( $order_id );
				if ( $order ) {
					$total_sales += $order->get_total();
				}
			}

			wp_cache_set( $cache_key, $total_sales, '', HOUR_IN_SECONDS );
		}

		return floatval( $total_sales );
	}

	public function wpkin_get_total_orders( $date_range = null ) {
		$cache_key    = 'wpkin_total_orders_' . md5( wp_json_encode( $date_range ) );
		$total_orders = wp_cache_get( $cache_key );

		if ( false === $total_orders ) {
			$query_args = [
				'status' => $this->wpkin_get_wc_order_statuses(),
				'limit' => 1,
				'return' => 'ids',
				'paginate' => true,
			];

			if ( $date_range ) {
				$query_args['date_created'] = $date_range['start'] . '...' . $date_range['end'];
			}

			$orders = wc_get_orders( $query_args );
			$total_orders = $orders->total;

			wp_cache_set( $cache_key, $total_orders, '', HOUR_IN_SECONDS );
		}

		return (int) $total_orders;
	}

	public function wpkin_get_average_order_value( $date_range = null ) {
		$cache_key = 'wpkin_avg_order_value_' . md5( wp_json_encode( $date_range ) );
		$average_order_value = wp_cache_get( $cache_key );

		if ( false === $average_order_value ) {
			$order_statuses = $this->wpkin_get_wc_order_statuses();
			$args = [
				'status' => $order_statuses,
				'return' => 'ids',
				'limit' => -1,
			];

			if ( $date_range ) {
				$args['date_created'] = $date_range['start'] . '...' . $date_range['end'];
			}

			$orders = wc_get_orders( $args );

			$total_sales = 0;
			$order_count = count( $orders );

			foreach ( $orders as $order_id ) {
				$order = wc_get_order( $order_id );
				if ( $order ) {
					$total_sales += $order->get_total();
				}
			}

			$average_order_value = $order_count > 0 ? $total_sales / $order_count : 0;

			wp_cache_set( $cache_key, $average_order_value, '', HOUR_IN_SECONDS );
		}

		return floatval( $average_order_value );
	}

	public function wpkin_get_new_customers_count( $date_range = null ) {
		$cache_key = 'wpkin_new_customers_count_' . md5( wp_json_encode( $date_range ) );
		$new_customers_count = wp_cache_get( $cache_key );

		if ( false === $new_customers_count ) {
			$order_statuses = $this->wpkin_get_wc_order_statuses();
			$args = [
				'status' => $order_statuses,
				'return' => 'ids',
				'limit' => -1,
				'customer' => '0', // Exclude guest orders
			];

			if ( $date_range ) {
				$args['date_created'] = $date_range['start'] . '...' . $date_range['end'];
			}

			$orders = wc_get_orders( $args );

			$customer_ids = [];
			foreach ( $orders as $order_id ) {
				$order = wc_get_order( $order_id );
				if ( $order ) {
					$customer_id = $order->get_customer_id();
					if ( $customer_id > 0 ) {
						$customer_ids[] = $customer_id;
					}
				}
			}

			$new_customers_count = count( array_unique( $customer_ids ) );

			wp_cache_set( $cache_key, $new_customers_count, '', HOUR_IN_SECONDS );
		}

		return (int) $new_customers_count;
	}

	public function wpkin_get_revenue_data( $days = 30 ) {
		$cache_key = 'wpkin_revenue_data_' . $days;
		$revenue_data = wp_cache_get( $cache_key );

		if ( false === $revenue_data ) {
			$order_statuses = $this->wpkin_get_wc_order_statuses();
			$end_date = gmdate( 'Y-m-d', time() );
			$start_date = gmdate( 'Y-m-d', strtotime( "-$days days", strtotime( $end_date ) ) );

			$args = [
				'status' => $order_statuses,
				'date_created' => $start_date . '...' . $end_date,
				'return' => 'ids',
				'limit' => -1,
			];

			$orders = wc_get_orders( $args );

			$daily_revenue = [];
			foreach ( $orders as $order_id ) {
				$order = wc_get_order( $order_id );
				if ( $order ) {
					$date = $order->get_date_created()->format( 'Y-m-d' );
					$amount = $order->get_total();
					if ( ! isset( $daily_revenue[ $date ] ) ) {
						$daily_revenue[ $date ] = 0;
					}
					$daily_revenue[ $date ] += $amount;
				}
			}

			$revenue_data = [];
			$current_date = strtotime( $start_date );
			$end_timestamp = strtotime( $end_date );
			while ( $current_date <= $end_timestamp ) {
				$date = gmdate( 'Y-m-d', $current_date );
				$revenue_data[] = [
					'date' => $date,
					'amount' => isset( $daily_revenue[ $date ] ) ? floatval( $daily_revenue[ $date ] ) : 0,
					'type' => 'Revenue',
				];
				$current_date = strtotime( '+1 day', $current_date );
			}

			wp_cache_set( $cache_key, $revenue_data, '', HOUR_IN_SECONDS );
		}

		return $revenue_data;
	}

	public function wpkin_get_top_products( $date_range = null, $limit = 10 ) {
		$cache_key = 'wpkin_top_products_' . md5( wp_json_encode( $date_range ) . $limit );
		$top_products = wp_cache_get( $cache_key );

		if ( false === $top_products ) {
			$order_statuses = $this->wpkin_get_wc_order_statuses();
			$args = [
				'status' => $order_statuses,
				'limit' => -1,
				'return' => 'ids',
			];

			if ( $date_range ) {
				$args['date_created'] = $date_range['start'] . '...' . $date_range['end'];
			}

			$orders = wc_get_orders( $args );

			$product_data = [];
			foreach ( $orders as $order_id ) {
				$order = wc_get_order( $order_id );
				if ( $order ) {
					foreach ( $order->get_items() as $item ) {
						$product_name = $item->get_name();
						if ( ! isset( $product_data[ $product_name ] ) ) {
							$product_data[ $product_name ] = [
								'sales' => 0,
								'revenue' => 0,
							];
						}
						$product_data[ $product_name ]['sales'] += $item->get_quantity();
						$product_data[ $product_name ]['revenue'] += $item->get_total();
					}
				}
			}

			arsort( $product_data );
			$product_data = array_slice( $product_data, 0, $limit, true );

			$top_products = [];
			foreach ( $product_data as $product => $data ) {
				$top_products[] = [
					'key' => count( $top_products ),
					'product' => $product,
					'sales' => (int) $data['sales'],
					'revenue' => floatval( $data['revenue'] ),
				];
			}

			wp_cache_set( $cache_key, $top_products, '', HOUR_IN_SECONDS );
		}

		return $top_products;
	}

	public function wpkin_get_revenue_data_by_range( $date_range ) {
		$cache_key = 'wpkin_revenue_data_range_' . md5( wp_json_encode( $date_range ) );
		$revenue_data = wp_cache_get( $cache_key );

		if ( false === $revenue_data ) {
			$order_statuses = $this->wpkin_get_wc_order_statuses();
			$args = [
				'status' => $order_statuses,
				'date_created' => $date_range['start'] . '...' . $date_range['end'],
				'return' => 'ids',
				'limit' => -1,
			];

			$orders = wc_get_orders( $args );

			$daily_revenue = [];
			foreach ( $orders as $order_id ) {
				$order = wc_get_order( $order_id );
				if ( $order ) {
					$date = $order->get_date_created()->format( 'Y-m-d' );
					$amount = $order->get_total();
					if ( ! isset( $daily_revenue[ $date ] ) ) {
						$daily_revenue[ $date ] = 0;
					}
					$daily_revenue[ $date ] += $amount;
				}
			}

			$revenue_data = [];
			$current = strtotime( $date_range['start'] );
			$end = strtotime( $date_range['end'] );

			while ( $current <= $end ) {
				$date = gmdate( 'Y-m-d', $current );
				$revenue_data[] = [
					'date' => $date,
					'amount' => isset( $daily_revenue[ $date ] ) ? floatval( $daily_revenue[ $date ] ) : 0,
					'type' => 'Revenue',
				];
				$current = strtotime( '+1 day', $current );
			}

			wp_cache_set( $cache_key, $revenue_data, '', HOUR_IN_SECONDS );
		}

		return $revenue_data;
	}
	/**
	 * ================= End Dashboard Stats Helper ===============================
	 */

	/**
	 * ================= Start Search orders Helpers ===============================
	 */

	/**
	 * Add search conditions to query arguments
	 *
	 * @param array $query_args Existing query arguments
	 * @param array $params Search parameters
	 * @return array Modified query args with search conditions
	 */
	public function wpkin_search_orders( $query_args, $params ) {
		global $wpdb;
		$search_query = $params['search'];

		// For order ID search
		if ( is_numeric( $search_query ) && strlen( $search_query ) < 6 ) {
			$order_id = intval( $search_query );
			$order = wc_get_order( $order_id );

			if ( $order ) {
				$query_args['post__in'] = [ $order_id ];
				return $query_args;
			}
		}

		// Initialize array to store found order IDs
		$found_order_ids = [];

		// Search by email directly using WooCommerce function
		if ( is_email( $search_query ) ) {
			$email_args = [
				'billing_email' => $search_query,
				'limit' => -1,
				'return' => 'ids',
			];

			$email_orders = wc_get_orders( $email_args );
			$found_order_ids = array_merge( $found_order_ids, $email_orders );
		}

		// Search using wc_order_addresses table for phone and customer name
		$address_query_parts = [];
		$address_query_values = [];

		// Improved phone number search
		// First, clean the search query to get just digits
		$clean_phone = preg_replace( '/[^0-9]/', '', $search_query );

		// If we have at least 4 digits, search for phone
		if ( strlen( $clean_phone ) >= 4 ) {
			// Search for the cleaned phone number (digits only)
			$address_query_parts[] = "REPLACE(REPLACE(REPLACE(REPLACE(phone, '(', ''), ')', ''), '-', ''), ' ', '') LIKE %s";
			$address_query_values[] = '%' . $wpdb->esc_like( $clean_phone ) . '%';

			// Also search for the original format in case it's stored that way
			$address_query_parts[] = 'phone LIKE %s';
			$address_query_values[] = '%' . $wpdb->esc_like( $search_query ) . '%';
		}

		// Search by name
		$address_query_parts[] = 'first_name LIKE %s';
		$address_query_values[] = '%' . $wpdb->esc_like( $search_query ) . '%';

		$address_query_parts[] = 'last_name LIKE %s';
		$address_query_values[] = '%' . $wpdb->esc_like( $search_query ) . '%';

		// If we have query parts for the address table
		if ( ! empty( $address_query_parts ) ) {
			$address_query = "SELECT DISTINCT order_id FROM {$wpdb->prefix}wc_order_addresses 
                         WHERE " . implode( ' OR ', $address_query_parts );

			$prepared_address_query = $wpdb->prepare( $address_query, $address_query_values );

			$address_order_ids = $wpdb->get_col( $prepared_address_query );

			$found_order_ids = array_merge( $found_order_ids, $address_order_ids );
		}

		// If we have a space in the search query, try to match first and last name
		if ( strpos( $search_query, ' ' ) !== false ) {
			$name_parts = explode( ' ', $search_query, 2 );
			if ( count( $name_parts ) === 2 ) {
				$full_name_query = $wpdb->prepare(
					"SELECT DISTINCT order_id FROM {$wpdb->prefix}wc_order_addresses 
                WHERE (first_name LIKE %s AND last_name LIKE %s)",
					'%' . $wpdb->esc_like( $name_parts[0] ) . '%',
					'%' . $wpdb->esc_like( $name_parts[1] ) . '%'
				);

				$full_name_order_ids = $wpdb->get_col( $full_name_query );

				$found_order_ids = array_merge( $found_order_ids, $full_name_order_ids );
			}
		}

		// Fallback to meta query if no results from address table or if the table doesn't exist
		if ( empty( $found_order_ids ) ) {
			// Check if the table exists
			$table_exists = $wpdb->get_var( "SHOW TABLES LIKE '{$wpdb->prefix}wc_order_addresses'" );

			if ( ! $table_exists ) {

				// Fallback to meta query for phone search
				$meta_phone_query = "
                SELECT DISTINCT post_id 
                FROM {$wpdb->postmeta} 
                WHERE meta_key = '_billing_phone' 
                AND (
                    meta_value LIKE %s 
                    OR REPLACE(REPLACE(REPLACE(REPLACE(meta_value, '(', ''), ')', ''), '-', ''), ' ', '') LIKE %s
                )
            ";

				$prepared_meta_phone_query = $wpdb->prepare(
					$meta_phone_query,
					'%' . $wpdb->esc_like( $search_query ) . '%',
					'%' . $wpdb->esc_like( $clean_phone ) . '%'
				);

				$phone_order_ids = $wpdb->get_col( $prepared_meta_phone_query );
				$found_order_ids = array_merge( $found_order_ids, $phone_order_ids );

				// Fallback to meta query for name search
				$meta_args = [
					'meta_query' => [
						'relation' => 'OR',
						[
							'key' => '_billing_first_name',
							'value' => $search_query,
							'compare' => 'LIKE',
						],
						[
							'key' => '_billing_last_name',
							'value' => $search_query,
							'compare' => 'LIKE',
						],
					],
					'limit' => -1,
					'return' => 'ids',
				];

				$meta_orders = wc_get_orders( $meta_args );
				$found_order_ids = array_merge( $found_order_ids, $meta_orders );
			}
		}

		// Remove duplicates and ensure we have valid IDs
		$found_order_ids = array_unique( array_filter( $found_order_ids ) );

		if ( ! empty( $found_order_ids ) ) {
			// If we found orders, use post__in to filter
			$query_args['post__in'] = $found_order_ids;
		} else {
			// No orders found, return an empty array
			$query_args['post__in'] = [ 0 ]; // This will return no results
		}

		return $query_args;
	}

	/**
	 * ================= End Search orders Helpers ===============================
	 */

	/**
	 * Get filter options for products
	 */
	public function wpkin_get_product_filter_options() {
		// Get product categories
		$categories = get_terms(
			[
				'taxonomy'   => 'product_cat',
				'hide_empty' => false,
			]
		);

		$formatted_categories = array_map(
			function ( $term ) {
				return [
					'id'    => $term->term_id,
					'name'  => $term->name,
					'slug'  => $term->slug,
					'count' => $term->count,
				];
			},
			$categories
		);

		// Get product types
		$product_types = get_terms(
			[
				'taxonomy'   => 'product_type',
				'hide_empty' => false,
			]
		);

		$formatted_product_types = array_map(
			function ( $term ) {
				return [
					'id'    => $term->term_id,
					'name'  => $term->name,
					'slug'  => $term->slug,
					'count' => $term->count,
				];
			},
			$product_types
		);

		// Get product tags
		$tags = get_terms(
			[
				'taxonomy'   => 'product_tag',
				'hide_empty' => false,
			]
		);

		$formatted_tags = array_map(
			function ( $term ) {
				return [
					'id'    => $term->term_id,
					'name'  => $term->name,
					'slug'  => $term->slug,
					'count' => $term->count,
				];
			},
			$tags
		);

		// Get brands
		$brands = [];
		if ( taxonomy_exists( 'product_brand' ) ) {
			$brand_terms = get_terms(
				[
					'taxonomy'   => 'product_brand',
					'hide_empty' => false,
				]
			);

			$brands = array_map(
				function ( $term ) {
					return [
						'id'    => $term->term_id,
						'name'  => $term->name,
						'slug'  => $term->slug,
						'count' => $term->count,
					];
				},
				$brand_terms
			);
		}

		return [
			'categories'    => $formatted_categories,
			'product_types' => $formatted_product_types,
			'tags'          => $formatted_tags,
			'brands'        => $brands,
			'stock_statuses' => [
				[
					'id' => 'instock',
					'name' => __( 'In Stock', 'shop-manager' ),
				],
				[
					'id' => 'outofstock',
					'name' => __( 'Out of Stock', 'shop-manager' ),
				],
				[
					'id' => 'onbackorder',
					'name' => __( 'On Backorder', 'shop-manager' ),
				],
			],
		];
	}

	/**
	 * Create a product variation for a variable product
	 *
	 * @param int $product_id Parent variable product ID
	 * @param array $variation_data Variation data
	 * @return int|\WP_Error The variation ID or an error object
	 */
	public function wpkin_create_product_variation($product_id, $variation_data) {
		try {
			// Get the parent variable product
			$parent = wc_get_product($product_id);
			if (!$parent || $parent->get_type() !== 'variable') {
				return new \WP_Error('invalid_parent', __('Parent product is not a variable product', 'shop-manager'));
			}

			// Create a new variation product
			$variation = new \WC_Product_Variation();
			$variation->set_parent_id($product_id);

			// Set variation attributes
			if (!empty($variation_data['attributes']) && is_array($variation_data['attributes'])) {
				$attributes = [];
				$parent_attributes = $parent->get_attributes();

				foreach ($variation_data['attributes'] as $attribute) {
					if (empty($attribute['name']) || !isset($attribute['option'])) {
						continue;
					}

					$attribute_name = sanitize_title($attribute['name']);
					$attribute_value = wc_clean($attribute['option']);

					// Format attribute name correctly for WooCommerce
					// For taxonomy attributes, use the full taxonomy name
					$final_attribute_name = '';
					$is_taxonomy_attribute = false;

					// Check if the attribute exists in the parent product
					foreach ($parent_attributes as $parent_attribute) {
						$parent_attr_name = '';

						if ($parent_attribute->is_taxonomy()) {
							$taxonomy = $parent_attribute->get_taxonomy();
							$parent_attr_name = str_replace('pa_', '', $taxonomy);

							// Compare case-insensitively but preserve original case for the actual attribute
							if (strtolower($parent_attr_name) === strtolower($attribute_name)) {
								$final_attribute_name = 'attribute_' . $taxonomy;
								$is_taxonomy_attribute = true;
								break;
							}
						} else {
							// For custom attributes, use the exact attribute name from parent product
							$original_name = $parent_attribute->get_name();
							$parent_attr_name = sanitize_title($original_name);

							// Try both exact match and lowercase comparison for flexibility
							if (
								$parent_attr_name === $attribute_name ||
								strtolower($parent_attr_name) === strtolower($attribute_name) ||
								strtolower($original_name) === strtolower($attribute_name)
							) {
								$final_attribute_name = 'attribute_' . sanitize_title($original_name);
								break;
							}
						}
					}

					// If we couldn't find a matching parent attribute, skip this one
					if (empty($final_attribute_name)) {
						continue;
					}

					// If this is a taxonomy attribute, we need the term slug, not the name
					if ($is_taxonomy_attribute) {
						$taxonomy = str_replace('attribute_', '', $final_attribute_name);
						$term = get_term_by('name', $attribute_value, $taxonomy);

						if ($term) {
							$attribute_value = $term->slug;
						} else {
							// Try to create the term
							$term_data = wp_insert_term($attribute_value, $taxonomy);
							if (!is_wp_error($term_data)) {
								$term = get_term_by('id', $term_data['term_id'], $taxonomy);
								if ($term) {
									$attribute_value = $term->slug;
								}
							} else {
								return new \WP_Error('Error creating term for variation: ' . $term_data->get_error_message());
							}
						}
					}

					// Set the attribute
					$attributes[$final_attribute_name] = $attribute_value;
				}

				// Set all attributes on the variation
				$variation->set_attributes($attributes);
			}

			// Set variation prices
			if (isset($variation_data['regular_price'])) {
				$regular_price = wc_format_decimal($variation_data['regular_price']);
				$variation->set_regular_price($regular_price);
			}

			if (isset($variation_data['sale_price'])) {
				$sale_price = wc_format_decimal($variation_data['sale_price']);

				// Validate sale price is not greater than regular price
				if (
					isset($variation_data['regular_price']) &&
					$sale_price > wc_format_decimal($variation_data['regular_price'])
				) {
					return new \WP_Error('invalid_price', __('Sale price cannot be greater than regular price', 'shop-manager'));
				}

				$variation->set_sale_price($sale_price);
			}

			// Set sale schedule dates
			if (isset($variation_data['date_on_sale_from'])) {
				$variation->set_date_on_sale_from($variation_data['date_on_sale_from']);
			}

			if (isset($variation_data['date_on_sale_to'])) {
				$variation->set_date_on_sale_to($variation_data['date_on_sale_to']);
			}

			// Set SKU
			if (isset($variation_data['sku']) && !empty($variation_data['sku'])) {
				$sku = trim(sanitize_text_field($variation_data['sku']));

				// Check if SKU is unique
				$existing_product = wc_get_product_id_by_sku($sku);
				if ($existing_product && $existing_product !== $product_id) {
					return new \WP_Error(
						'duplicate_sku',
						sprintf(__('SKU "%s" is already in use', 'shop-manager'), $sku)
					);
				}

				$variation->set_sku($sku);
			}

			// Set GTIN/UPC/EAN/ISBN
			if (isset($variation_data['global_unique_id'])) {
				$global_id = sanitize_text_field($variation_data['global_unique_id']);
				$variation->update_meta_data('_global_unique_id', $global_id);
			}

			// Set status (enabled/disabled)
			if (isset($variation_data['status'])) {
				$variation->set_status($variation_data['status']);
			} else if (isset($variation_data['enabled'])) {
				$status = $variation_data['enabled'] === false ? 'private' : 'publish';
				$variation->set_status($status);
			}

			// Set stock status and quantity
			if (isset($variation_data['stock_status'])) {
				$stock_status = sanitize_text_field($variation_data['stock_status']);
				$valid_statuses = ['instock', 'outofstock', 'onbackorder'];

				if (!in_array($stock_status, $valid_statuses)) {
					return new \WP_Error(
						'invalid_stock_status',
						__('Invalid stock status. Must be one of: instock, outofstock, onbackorder', 'shop-manager')
					);
				}

				$variation->set_stock_status($stock_status);
			}

			// Set stock management
			if (isset($variation_data['manage_stock'])) {
				$manage_stock = $variation_data['manage_stock'] === 'yes' || $variation_data['manage_stock'] === true;
				$variation->set_manage_stock($manage_stock);

				if ($manage_stock && isset($variation_data['stock_quantity'])) {
					$stock_quantity = absint($variation_data['stock_quantity']);
					$variation->set_stock_quantity($stock_quantity);
				}
			}

			// Update backorders
			if (isset($variation_data['backorders'])) {
				$variation->set_backorders($variation_data['backorders']);
			}

			// Update low stock threshold
			if (isset($variation_data['low_stock_amount'])) {
				$low_stock_amount = absint($variation_data['low_stock_amount']);
				$variation->set_low_stock_amount($low_stock_amount);
			}

			// Update sold individually
			if (isset($variation_data['sold_individually'])) {
				$sold_individually = $variation_data['sold_individually'] === 'yes' || $variation_data['sold_individually'] === true;
				$variation->set_sold_individually($sold_individually);
			}

			// Set description
			if (isset($variation_data['description'])) {
				$description = $variation_data['description'];
				$variation->set_description(wp_kses_post($description));
			}

			// Set dimensions
			if (isset($variation_data['dimensions']) && is_array($variation_data['dimensions'])) {
				if (isset($variation_data['dimensions']['length'])) {
					$variation->set_length(wc_format_decimal($variation_data['dimensions']['length']));
				}

				if (isset($variation_data['dimensions']['width'])) {
					$variation->set_width(wc_format_decimal($variation_data['dimensions']['width']));
				}

				if (isset($variation_data['dimensions']['height'])) {
					$variation->set_height(wc_format_decimal($variation_data['dimensions']['height']));
				}
			}

			// Set weight
			if (isset($variation_data['weight'])) {
				$variation->set_weight(wc_format_decimal($variation_data['weight']));
			}

			// Set virtual product status
			if (isset($variation_data['virtual'])) {
				$variation->set_virtual($variation_data['virtual'] === 'yes' || $variation_data['virtual'] === true);
			}

			// Set downloadable product status
			if (isset($variation_data['downloadable'])) {
				$is_downloadable = $variation_data['downloadable'] === 'yes' || $variation_data['downloadable'] === true;
				$variation->set_downloadable($is_downloadable);

				// Set downloadable files
				if ($is_downloadable && isset($variation_data['downloads']) && is_array($variation_data['downloads'])) {
					$downloads = [];

					foreach ($variation_data['downloads'] as $download) {
						if (empty($download['name']) || empty($download['file'])) {
							continue;
						}

						$download_id = md5($download['file']);
						$downloads[$download_id] = [
							'id' => $download_id,
							'name' => wc_clean($download['name']),
							'file' => wc_clean($download['file']),
						];
					}

					$variation->set_downloads($downloads);
				}

				// Update download limit and expiry
				if (isset($variation_data['download_limit'])) {
					// Pass the value as is - empty string is valid
					$download_limit = $variation_data['download_limit'];
					$variation->set_download_limit($download_limit);
				}

				if (isset($variation_data['download_expiry'])) {
					// Pass the value as is - empty string is valid
					$download_expiry = $variation_data['download_expiry'];
					$variation->set_download_expiry($download_expiry);
				}
			}

			// Set image
			if (!empty($variation_data['image']) && !empty($variation_data['image']['id'])) {
				$image_id = absint($variation_data['image']['id']);

				// Verify the image exists
				if (!wp_get_attachment_url($image_id)) {
					return new \WP_Error("Warning: Image with ID {$image_id} does not exist");
				} else {
					$variation->set_image_id($image_id);
				}
			}

			// Set menu order (position)
			if (isset($variation_data['menu_order'])) {
				$variation->set_menu_order(absint($variation_data['menu_order']));
			}

			// Set shipping class
			if (isset($variation_data['shipping_class'])) {
				$variation->set_shipping_class_id(
					$variation_data['shipping_class'] === '' ? 0 : absint($variation_data['shipping_class'])
				);
			}

			// Set tax class
			if (isset($variation_data['tax_class'])) {
				$variation->set_tax_class(sanitize_text_field($variation_data['tax_class']));
			}

			// Save the variation
			$variation_id = $variation->save();

			if (!$variation_id) {
				return new \WP_Error(
					'variation_creation_failed',
					__('Failed to create product variation', 'shop-manager')
				);
			}

			return $variation_id;
		} catch (\Exception $e) {
			return new \WP_Error('variation_creation_failed', $e->getMessage());
		}
	}

	/**
	 * Update a product variation
	 *
	 * @param int $product_id Parent variable product ID
	 * @param int $variation_id Variation ID to update
	 * @param array $variation_data Variation data
	 * @return int|\WP_Error The variation ID or an error object
	 */
	public function wpkin_update_product_variation($product_id, $variation_id, $variation_data) {
		try {
			// Get the variation to update
			$variation = wc_get_product($variation_id);

			if (!$variation || $variation->get_parent_id() != $product_id) {
				return new \WP_Error('invalid_variation', __('Invalid variation ID or parent product', 'shop-manager'));
			}

			// Get the parent variable product
			$parent = wc_get_product($product_id);
			if (!$parent || $parent->get_type() !== 'variable') {
				return new \WP_Error('invalid_parent', __('Parent product is not a variable product', 'shop-manager'));
			}

			// Set variation attributes
			if (!empty($variation_data['attributes']) && is_array($variation_data['attributes'])) {
				$attributes = [];
				$parent_attributes = $parent->get_attributes();

				foreach ($variation_data['attributes'] as $attribute) {
					if (empty($attribute['name']) || !isset($attribute['option'])) {
						continue;
					}

					$attribute_name = sanitize_title($attribute['name']);
					$attribute_value = wc_clean($attribute['option']);

					// Format attribute name correctly for WooCommerce
					// For taxonomy attributes, use the full taxonomy name
					$final_attribute_name = '';
					$is_taxonomy_attribute = false;

					// Check if the attribute exists in the parent product
					foreach ($parent_attributes as $parent_attribute) {
						$parent_attr_name = '';

						if ($parent_attribute->is_taxonomy()) {
							$taxonomy = $parent_attribute->get_taxonomy();
							$parent_attr_name = str_replace('pa_', '', $taxonomy);

							// Compare case-insensitively but preserve original case for the actual attribute
							if (strtolower($parent_attr_name) === strtolower($attribute_name)) {
								$final_attribute_name = 'attribute_' . $taxonomy;
								$is_taxonomy_attribute = true;
								break;
							}
						} else {
							// For custom attributes, use the exact attribute name from parent product
							$original_name = $parent_attribute->get_name();
							$parent_attr_name = sanitize_title($original_name);

							// Try both exact match and lowercase comparison for flexibility
							if (
								$parent_attr_name === $attribute_name ||
								strtolower($parent_attr_name) === strtolower($attribute_name) ||
								strtolower($original_name) === strtolower($attribute_name)
							) {
								$final_attribute_name = 'attribute_' . sanitize_title($original_name);
								break;
							}
						}
					}

					// If we couldn't find a matching parent attribute, skip this one
					if (empty($final_attribute_name)) {
						continue;
					}

					// If this is a taxonomy attribute, we need the term slug, not the name
					if ($is_taxonomy_attribute) {
						$taxonomy = str_replace('attribute_', '', $final_attribute_name);
						$term = get_term_by('name', $attribute_value, $taxonomy);

						if ($term) {
							$attribute_value = $term->slug;
						} else {
							// Try to create the term
							$term_data = wp_insert_term($attribute_value, $taxonomy);
							if (!is_wp_error($term_data)) {
								$term = get_term_by('id', $term_data['term_id'], $taxonomy);
								if ($term) {
									$attribute_value = $term->slug;
								}
							} else {
								return new \WP_Error('Error creating term for variation: ' . $term_data->get_error_message());
							}
						}
					}

					// Set the attribute
					$attributes[$final_attribute_name] = $attribute_value;
				}

				// Set all attributes on the variation
				if (!empty($attributes)) {
					$variation->set_attributes($attributes);
				}
			}

			// Update SKU
			if (isset($variation_data['sku'])) {
				$sku = trim(sanitize_text_field($variation_data['sku']));
				$variation->set_sku($sku);
			}

			// Update GTIN/UPC/EAN/ISBN
			if (isset($variation_data['global_unique_id'])) {
				$global_id = sanitize_text_field($variation_data['global_unique_id']);
				$variation->update_meta_data('_global_unique_id', $global_id);
			}

			// Update status (enabled/disabled)
			if (isset($variation_data['status'])) {
				$variation->set_status($variation_data['status']);
			} else if (isset($variation_data['enabled'])) {
				$status = $variation_data['enabled'] === false ? 'private' : 'publish';
				$variation->set_status($status);
			}

			// Update prices
			if (isset($variation_data['regular_price'])) {
				$regular_price = wc_format_decimal($variation_data['regular_price']);
				$variation->set_regular_price($regular_price);
			}

			if (isset($variation_data['sale_price'])) {
				$sale_price = wc_format_decimal($variation_data['sale_price']);
				if (
					isset($variation_data['regular_price']) &&
					$sale_price > wc_format_decimal($variation_data['regular_price'])
				) {
					return new \WP_Error('invalid_price', __('Sale price cannot be greater than regular price', 'shop-manager'));
				}
				$variation->set_sale_price($sale_price);
			}

			// Update sale schedule dates
			$variation->set_date_on_sale_from($variation_data['date_on_sale_from']);
			$variation->set_date_on_sale_to($variation_data['date_on_sale_to']);

			// Update stock
			if (isset($variation_data['manage_stock'])) {
				$manage_stock = $variation_data['manage_stock'] === 'yes' || $variation_data['manage_stock'] === true;
				$variation->set_manage_stock($manage_stock);

				if ($manage_stock && isset($variation_data['stock_quantity'])) {
					$variation->set_stock_quantity(wc_stock_amount($variation_data['stock_quantity']));
				}
			}

			if (isset($variation_data['stock_status'])) {
				$variation->set_stock_status($variation_data['stock_status']);
			}

			// Update backorders
			if (isset($variation_data['backorders'])) {
				$variation->set_backorders($variation_data['backorders']);
			}

			// Update low stock threshold
			if (isset($variation_data['low_stock_amount'])) {
				$low_stock_amount = absint($variation_data['low_stock_amount']);
				$variation->set_low_stock_amount($low_stock_amount);
			}

			// Update sold individually
			if (isset($variation_data['sold_individually'])) {
				$sold_individually = $variation_data['sold_individually'] === 'yes' || $variation_data['sold_individually'] === true;
				$variation->set_sold_individually($sold_individually);
			}

			// Update virtual status
			if (isset($variation_data['virtual'])) {
				$is_virtual = $variation_data['virtual'] === 'yes' || $variation_data['virtual'] === true;
				$variation->set_virtual($is_virtual);
			}

			// Update downloadable status and files
			if (isset($variation_data['downloadable'])) {
				$is_downloadable = $variation_data['downloadable'] === 'yes' || $variation_data['downloadable'] === true;
				$variation->set_downloadable($is_downloadable);

				if ($is_downloadable && isset($variation_data['downloads']) && is_array($variation_data['downloads'])) {
					$downloads = [];
					foreach ($variation_data['downloads'] as $download) {
						if (empty($download['name']) || empty($download['file'])) {
							continue;
						}

						$download_id = md5($download['file']);
						$downloads[$download_id] = [
							'id' => $download_id,
							'name' => wc_clean($download['name']),
							'file' => wc_clean($download['file']),
						];
					}

					$variation->set_downloads($downloads);
				}

				// Update download limit and expiry
				if (isset($variation_data['download_limit'])) {
					// Pass the value as is - empty string is valid
					$download_limit = $variation_data['download_limit'];
					$variation->set_download_limit($download_limit);
				}

				if (isset($variation_data['download_expiry'])) {
					// Pass the value as is - empty string is valid
					$download_expiry = $variation_data['download_expiry'];
					$variation->set_download_expiry($download_expiry);
				}
			}

			// Update dimensions
			if (!empty($variation_data['dimensions'])) {
				if (isset($variation_data['dimensions']['length'])) {
					$variation->set_length(wc_format_decimal($variation_data['dimensions']['length']));
				}

				if (isset($variation_data['dimensions']['width'])) {
					$variation->set_width(wc_format_decimal($variation_data['dimensions']['width']));
				}

				if (isset($variation_data['dimensions']['height'])) {
					$variation->set_height(wc_format_decimal($variation_data['dimensions']['height']));
				}
			}

			// Update weight
			if (isset($variation_data['weight'])) {
				$variation->set_weight(wc_format_decimal($variation_data['weight']));
			}

			// Update description
			if (isset($variation_data['description'])) {
				$description = $variation_data['description'];
				$variation->set_description(wp_kses_post($description));
			}

			// Update image
			if (isset($variation_data['image']) && !empty($variation_data['image']['id'])) {
				$variation->set_image_id(absint($variation_data['image']['id']));
			}

			// Update shipping class
			if (isset($variation_data['shipping_class'])) {
				if (empty($variation_data['shipping_class'])) {
					$variation->set_shipping_class_id(0);
				} else {
					$shipping_class_id = absint($variation_data['shipping_class']);
					$variation->set_shipping_class_id($shipping_class_id);
				}
			}

			// Update tax class
			if (isset($variation_data['tax_class'])) {
				$variation->set_tax_class($variation_data['tax_class']);
			}

			// Update menu order
			if (isset($variation_data['menu_order'])) {
				$variation->set_menu_order(absint($variation_data['menu_order']));
			}

			// Save the variation
			$variation_id = $variation->save();

			if (!$variation_id) {
				return new \WP_Error(
					'variation_update_failed',
					__('Failed to update product variation', 'shop-manager')
				);
			}

			return $variation_id;
		} catch (\Exception $e) {
			return new \WP_Error('variation_update_failed', $e->getMessage());
		}
	}

	/**
	 * Find matching parent attribute
	 *
	 * @param array $parent_attributes Parent product attributes
	 * @param string $search_name Attribute name to search for
	 * @return array|null Found attribute data or null
	 */
	private function find_matching_parent_attribute($parent_attributes, $search_name) {
		// WooCommerce attributes are objects - let's properly handle them
		if (empty($parent_attributes)) {
			return null;
		}

		// Remove any 'attribute_' prefix if it exists
		$clean_search_name = str_replace('attribute_', '', $search_name);
		$clean_search_name = str_replace('pa_', '', $clean_search_name);
		
		// Sanitize the search name
		$search_sanitized = sanitize_title($clean_search_name);

		// Loop through each attribute to find a match
		foreach ($parent_attributes as $attr) {
			// Skip if not a valid attribute object
			if (!($attr instanceof \WC_Product_Attribute)) {
				continue;
			}

			$original_name = $attr->get_name();
			
			// Remove any 'pa_' prefix for taxonomy attributes
			$clean_original_name = str_replace('pa_', '', $original_name);
			$attr_sanitized = sanitize_title($clean_original_name);

			// Try multiple matching approaches
			if (
				// Exact name match
				$original_name === $search_name ||
				$clean_original_name === $clean_search_name ||
				// Case-insensitive name match
				strtolower($clean_original_name) === strtolower($clean_search_name) ||
				// Sanitized name match
				$attr_sanitized === $search_sanitized
			) {
				// Check if it's a taxonomy or custom attribute
				$is_taxonomy = $attr->is_taxonomy();
				$taxonomy = $is_taxonomy ? $original_name : '';

				return [
					'taxonomy' => $taxonomy,
					'is_taxonomy' => $is_taxonomy,
					'sanitized_name' => $attr_sanitized,
					'original_name' => $original_name,
				];
			}
		}
		
		return null;
	}

	/**
	 * Parse and sanitize request parameters
	 *
	 * @param WP_REST_Request $request
	 * @return array
	 */
	public function wpkin_parse_request_parameters( $request ) {
		$params = [
			// Pagination
			'page' => $request->get_param( 'page' ) ? max( 1, intval( $request->get_param( 'page' ) ) ) : 1,
			'per_page' => $request->get_param( 'per_page' ) ? max( 1, intval( $request->get_param( 'per_page' ) ) ) : 10,

			// Basic filters
			'status' => $request->get_param( 'status' ) ? sanitize_text_field( $request->get_param( 'status' ) ) : '',
			'is_trash' => $request->get_param( 'trash' ) === 'true',

			// Add search parameter
			'search' => $request->get_param( 'search' ) ? sanitize_text_field( $request->get_param( 'search' ) ) : '',

			// Sorting
			'sort_by' => $request->get_param( 'sort_by' ),
			'sort_order' => $request->get_param( 'sort_order' ) === 'asc' ? 'ASC' : 'DESC',

			// Special parameters
			'maintain_pagination' => $request->get_param( 'maintain_pagination' ),

			// Date range
			'date_from' => $request->get_param( 'date_from' ) ? sanitize_text_field( $request->get_param( 'date_from' ) ) : '',
			'date_to' => $request->get_param( 'date_to' ) ? sanitize_text_field( $request->get_param( 'date_to' ) ) : '',
		];

		/**
		 *  Advanced Filter
		 */
		// Order Information
		$params['order_id']     = $request->get_param( 'orderId' );
		$params['order_status'] = $request->get_param( 'orderStatus' );
		$dateRange              = $this->wpkin_parse_json_param( $request, 'dateRange' );
		if ( is_array( $dateRange ) && count( $dateRange ) === 2 ) {
			$params['date_from'] = $dateRange[0];
			$params['date_to']   = $dateRange[1];
		}
		$params['total_min']      = $request->get_param( 'totalMin' ) ? floatval( $request->get_param( 'totalMin' ) ) : null;
		$params['total_max']      = $request->get_param( 'totalMax' ) ? floatval( $request->get_param( 'totalMax' ) ) : null;
		$params['payment_method'] = $request->get_param( 'paymentMethod' );
		$params['shipping_method'] = $request->get_param( 'shippingMethod' );

		// Customer Information
		$params['customer_name']  = $request->get_param( 'customerName' );
		$params['customer_email'] = $request->get_param( 'customerEmail' );
		$params['customer_phone'] = $request->get_param( 'customerPhone' );
		$params['is_guest']       = $request->get_param( 'isGuest' );

		//Product Information
		$params['product_name']       = $request->get_param( 'productName' );
		$params['product_sku']        = $request->get_param( 'productSku' );
		$params['product_categories'] = $request->get_param( 'productCategory' );

		// Shipping & Billing Information
		$params['shipping_country'] = $request->get_param( 'shippingCountry' );
		$params['shipping_state']    = $request->get_param( 'shippingState' );

		//Financial Information
		$params['coupon_code'] = $request->get_param( 'couponCode' );
		$params['is_refunded'] = $request->get_param( 'isRefunded' );
		$params['is_paid']     = $request->get_param( 'isPaid' );

		// Additional Search Options
		$params['order_notes']     = $request->get_param( 'orderNotes' );
		$params['custom_meta']     = $request->get_param( 'customMeta' );

		return $params;
	}

	/**
	 * Helper method to parse JSON parameters
	 *
	 * @param WP_REST_Request $request
	 * @param string $param
	 * @return mixed
	 */
	private function wpkin_parse_json_param( $request, $param ) {
		$value = $request->get_param( $param );
		if ( empty( $value ) ) {
			return null;
		}

		if ( is_string( $value ) ) {
			$decoded = json_decode( $value, true );
			if ( json_last_error() === JSON_ERROR_NONE ) {
				return $decoded;
			}
		}

		return $value;
	}

	/**
	 * Build query arguments
	 *
	 * @param array $params
	 * @return array
	 */
	public function wpkin_build_query_args( $params ) {
		$query_args = [
			'limit' => $params['per_page'],
			'page' => $params['page'],
			'paginate' => true,
			'return' => 'objects',
		];

		// Handle status
		if ( $params['is_trash'] ) {
			$query_args['status'] = 'trash';
		} elseif ( ! empty( $params['status'] ) ) {
			$clean_status = str_replace( 'wc-', '', $params['status'] );
			$query_args['status'] = $clean_status;
		}

		// Add date created filter
		if ( ! empty( $params['date_from'] ) && ! empty( $params['date_to'] ) ) {
			$query_args['date_created'] = $params['date_from'] . '...' . $params['date_to'];
		}

		return $query_args;
	}

	/**
	 * Apply filters to query arguments
	 *
	 * @param array $query_args
	 * @param array $params
	 * @return array|bool
	 */
	public function wpkin_apply_filters( $query_args, $params ) {
		global $wpdb;

		// Track if we need to apply post__in filter
		$has_custom_filters = false;
		$matching_order_ids = [];

		// Detect HPOS using the official WC API when available, falling back to option check.
		if ( class_exists( '\Automattic\WooCommerce\Utilities\OrderUtil' ) ) {
			$using_hpos = \Automattic\WooCommerce\Utilities\OrderUtil::custom_orders_table_usage_is_enabled();
		} else {
			$is_hpos_enabled   = get_option( 'woocommerce_custom_orders_table_enabled' ) === 'yes';
			$hpos_table_exists = $wpdb->get_var( "SHOW TABLES LIKE '{$wpdb->prefix}wc_orders'" ) === "{$wpdb->prefix}wc_orders";
			$using_hpos        = $is_hpos_enabled && $hpos_table_exists;
		}

		// Order ID filter
		if ( ! empty( $params['order_id'] ) ) {
			$order_id = (int) $params['order_id'];
			$order = wc_get_order( $order_id );

			if ( $order ) {
				$query_args['post__in'] = isset( $query_args['post__in'] )
					? array_intersect( $query_args['post__in'], [ $order_id ] )
					: [ $order_id ];

				if ( isset( $query_args['post__in'] ) && empty( $query_args['post__in'] ) ) {
					return false; // No matches
				}
			} else {
				return false; // Order ID doesn't exist
			}
		}

		// Order status filter
		if ( ! empty( $params['order_status'] ) ) {
			$statuses = is_array( $params['order_status'] )
				? $params['order_status']
				: explode( ',', $params['order_status'] );

			$clean_statuses = array_map(
				function ( $status ) {
					return str_replace( 'wc-', '', $status );
				},
				$statuses
			);

			$query_args['status'] = $clean_statuses;
		}

		// Total min/max filter
		if ( ! empty( $params['total_min'] ) || ! empty( $params['total_max'] ) ) {
			if ( $using_hpos ) {
				// HPOS query approach
				$total_query = "SELECT id FROM {$wpdb->prefix}wc_orders WHERE 1=1";

				if ( ! empty( $params['total_min'] ) ) {
					$min = floatval( $params['total_min'] );
					$total_query .= $wpdb->prepare( ' AND total_amount >= %f', $min );
				}

				if ( ! empty( $params['total_max'] ) ) {
					$max = floatval( $params['total_max'] );
					$total_query .= $wpdb->prepare( ' AND total_amount <= %f', $max );
				}
			} else {
				// Traditional meta query approach
				$total_query = "SELECT DISTINCT post_id FROM {$wpdb->postmeta} WHERE meta_key = '_order_total'";

				if ( ! empty( $params['total_min'] ) ) {
					$min = floatval( $params['total_min'] );
					$total_query .= ' AND meta_value >= ' . sprintf( '%.2f', $min );
				}

				if ( ! empty( $params['total_max'] ) ) {
					$max = floatval( $params['total_max'] );
					$total_query .= ' AND meta_value <= ' . sprintf( '%.2f', $max );
				}
			}

			$found_ids = $wpdb->get_col( $total_query );

			if ( ! empty( $found_ids ) ) {
				$has_custom_filters = true;
				$matching_order_ids = ! empty( $matching_order_ids )
					? array_intersect( $matching_order_ids, $found_ids )
					: $found_ids;

				if ( empty( $matching_order_ids ) ) {
					return false; // No matches after intersection
				}
			} else {
				return false; // No matches for total range
			}
		}

		// Payment method filter
		if ( ! empty( $params['payment_method'] ) ) {
			$payment_methods = is_array( $params['payment_method'] )
				? $params['payment_method']
				: explode( ',', $params['payment_method'] );

			$placeholders = implode( ', ', array_fill( 0, count( $payment_methods ), '%s' ) );

			if ( $using_hpos ) {
				// HPOS query approach
				$payment_query = $wpdb->prepare(
					"SELECT id FROM {$wpdb->prefix}wc_orders 
					WHERE payment_method IN ($placeholders)",
					$payment_methods
				);
			} else {
				// Traditional meta query approach
				$payment_query = $wpdb->prepare(
					"SELECT DISTINCT post_id FROM {$wpdb->postmeta} 
					WHERE meta_key = '_payment_method' 
					AND meta_value IN ($placeholders)",
					$payment_methods
				);
			}

			$found_ids = $wpdb->get_col( $payment_query );

			if ( ! empty( $found_ids ) ) {
				$has_custom_filters = true;
				$matching_order_ids = ! empty( $matching_order_ids )
					? array_intersect( $matching_order_ids, $found_ids )
					: $found_ids;

				if ( empty( $matching_order_ids ) ) {
					return false;
				}
			} else {
				return false;
			}
		}

		// Handle shipping method filter
		if ( ! empty( $params['shipping_method'] ) ) {
			$shipping_methods = is_array( $params['shipping_method'] )
				? $params['shipping_method']
				: explode( ',', $params['shipping_method'] );

			// First, try to match by method ID in meta
			$meta_conditions = [];
			$meta_params = [];

			// Second, prepare for matching by display name
			$method_conditions = [];
			$query_params = [];

			foreach ( $shipping_methods as $method ) {
				// Add condition for the shipping method ID in meta
				$meta_conditions[] = 'oim.meta_value LIKE %s';
				$meta_params[] = '%' . $wpdb->esc_like( $method ) . '%';

				// Also add condition for the display name (with proper formatting)
				$method_conditions[] = 'order_item_name LIKE %s'; // Fixed: removed 'oi.' prefix

				// Convert method ID to a more readable format that might match the stored name
				// e.g., "free_shipping" becomes "Free shipping"
				$readable_method = ucwords( str_replace( '_', ' ', $method ) );
				$query_params[] = '%' . $wpdb->esc_like( $readable_method ) . '%';
			}

			// First query: try to match by method ID in meta
			if ( ! empty( $meta_conditions ) ) {
				$meta_condition_string = implode( ' OR ', $meta_conditions );

				$meta_shipping_query = $wpdb->prepare(
					"SELECT DISTINCT oi.order_id 
					FROM {$wpdb->prefix}woocommerce_order_items oi
					JOIN {$wpdb->prefix}woocommerce_order_itemmeta oim ON oi.order_item_id = oim.order_item_id
					WHERE oi.order_item_type = 'shipping' 
					AND oim.meta_key = 'method_id'
					AND ($meta_condition_string)",
					$meta_params
				);

				$meta_found_ids = $wpdb->get_col( $meta_shipping_query );
			} else {
				$meta_found_ids = [];
			}

			// Second query: try to match by display name
			$condition_string = implode( ' OR ', $method_conditions );

			$name_shipping_query = $wpdb->prepare(
				"SELECT DISTINCT order_id 
				FROM {$wpdb->prefix}woocommerce_order_items 
				WHERE order_item_type = 'shipping' 
				AND ($condition_string)",
				$query_params
			);

			$name_found_ids = $wpdb->get_col( $name_shipping_query );

			// Combine results from both queries
			$found_ids = array_unique( array_merge( $meta_found_ids, $name_found_ids ) );

			if ( ! empty( $found_ids ) ) {
				$has_custom_filters = true;
				$matching_order_ids = ! empty( $matching_order_ids )
					? array_intersect( $matching_order_ids, $found_ids )
					: $found_ids;

				if ( empty( $matching_order_ids ) ) {
					return false;
				}
			} else {
				return false;
			}
		}

		// Handle customer name filter
		if ( ! empty( $params['customer_name'] ) ) {
			$customer_name = $params['customer_name'];
			$found_ids = [];

			// Check if wc_order_addresses table exists
			$table_exists = $wpdb->get_var( "SHOW TABLES LIKE '{$wpdb->prefix}wc_order_addresses'" );

			if ( $table_exists ) {
				// If we have a space in the name, try to match first and last name
				if ( strpos( $customer_name, ' ' ) !== false ) {
					$name_parts = explode( ' ', $customer_name, 2 );
					if ( count( $name_parts ) === 2 ) {
						$full_name_query = $wpdb->prepare(
							"SELECT DISTINCT order_id FROM {$wpdb->prefix}wc_order_addresses 
                        WHERE (first_name LIKE %s AND last_name LIKE %s)",
							'%' . $wpdb->esc_like( $name_parts[0] ) . '%',
							'%' . $wpdb->esc_like( $name_parts[1] ) . '%'
						);
						$found_ids = $wpdb->get_col( $full_name_query );
					}
				}

				// If no results or no space in name, search in first_name or last_name
				if ( empty( $found_ids ) ) {
					$name_query = $wpdb->prepare(
						"SELECT DISTINCT order_id FROM {$wpdb->prefix}wc_order_addresses 
                    WHERE first_name LIKE %s OR last_name LIKE %s",
						'%' . $wpdb->esc_like( $customer_name ) . '%',
						'%' . $wpdb->esc_like( $customer_name ) . '%'
					);
					$found_ids = $wpdb->get_col( $name_query );
				}
			} else {
				// Fallback to meta query
				$name_meta_args = [
					'meta_query' => [
						'relation' => 'OR',
						[
							'key' => '_billing_first_name',
							'value' => $customer_name,
							'compare' => 'LIKE',
						],
						[
							'key' => '_billing_last_name',
							'value' => $customer_name,
							'compare' => 'LIKE',
						],
					],
					'limit' => -1,
					'return' => 'ids',
				];
				$found_ids = wc_get_orders( $name_meta_args );
			}

			if ( ! empty( $found_ids ) ) {
				$has_custom_filters = true;
				$matching_order_ids = ! empty( $matching_order_ids )
					? array_intersect( $matching_order_ids, $found_ids )
					: $found_ids;

				if ( empty( $matching_order_ids ) ) {
					return false; // No matches after intersection
				}
			} else {
				return false; // No matches for customer name
			}
		}

		// Handle customer email filter
		if ( ! empty( $params['customer_email'] ) ) {
			$email = sanitize_text_field( $params['customer_email'] );

			// Define search and replace patterns for REGEXP
			$filter_search  = [ '*', ', ', ',' ];
			$filter_replace = [ '%', '|', '|' ];

			$filter = str_replace( $filter_search, $filter_replace, $email );

			if ( $using_hpos ) {
				// HPOS query approach
				$email_query = $wpdb->prepare(
					"SELECT id FROM {$wpdb->prefix}wc_orders 
					WHERE billing_email REGEXP %s",
					$filter
				);
			} else {
				// Traditional meta query approach
				$email_query = $wpdb->prepare(
					"SELECT DISTINCT post_id FROM {$wpdb->postmeta} 
					WHERE meta_key = '_billing_email' 
					AND meta_value REGEXP %s",
					$filter
				);
			}

			$found_ids = $wpdb->get_col( $email_query );

			if ( ! empty( $found_ids ) ) {
				$has_custom_filters = true;
				$matching_order_ids = ! empty( $matching_order_ids )
					? array_intersect( $matching_order_ids, $found_ids )
					: $found_ids;

				if ( empty( $matching_order_ids ) ) {
					return false;
				}
			} else {
				return false;
			}
		}

		// Handle customer phone filter
		if ( ! empty( $params['customer_phone'] ) ) {
			$phone = $params['customer_phone'];
			$found_ids = [];

			if ( strlen( $phone ) >= 4 ) {
				// Exact email match
				$phone_args = [
					'billing_phone' => $phone,
					'limit' => -1,
					'return' => 'ids',
				];
				$found_ids = wc_get_orders( $phone_args );
			} else {
				// Partial email match
				global $wpdb;
				$meta_phone_query = "
					SELECT DISTINCT post_id 
					FROM {$wpdb->postmeta} 
					WHERE meta_key = '_billing_phone' 
					AND (
						meta_value LIKE %s 
						OR REPLACE(REPLACE(REPLACE(REPLACE(meta_value, '(', ''), ')', ''), '-', ''), ' ', '') LIKE %s
					)
				";
				$found_ids = $wpdb->get_col( $meta_phone_query );
			}

			if ( ! empty( $found_ids ) ) {
				$has_custom_filters = true;
				$matching_order_ids = ! empty( $matching_order_ids )
					? array_intersect( $matching_order_ids, $found_ids )
					: $found_ids;

				if ( empty( $matching_order_ids ) ) {
					return false; // No matches after intersection
				}
			} else {
				return false; // No matches for customer email
			}
		}

		// Guest Orders filter
		if ( isset( $params['is_guest'] ) ) {
			if ( $using_hpos ) {
				// HPOS query approach
				if ( $params['is_guest'] === 'guest' ) {
					$guest_query = "SELECT id FROM {$wpdb->prefix}wc_orders 
								WHERE customer_id = 0";
				} else {
					$guest_query = "SELECT id FROM {$wpdb->prefix}wc_orders 
								WHERE customer_id > 0";
				}
			} else {
				// Traditional meta query approach
				if ( $params['is_guest'] === 'guest' ) {
					$guest_query = "SELECT DISTINCT post_id FROM {$wpdb->postmeta} 
								WHERE meta_key = '_customer_user' 
								AND (meta_value = '0' OR meta_value = '')";
				} else {
					$guest_query = "SELECT DISTINCT post_id FROM {$wpdb->postmeta} 
								WHERE meta_key = '_customer_user' 
								AND meta_value > 0";
				}
			}

			$found_ids = $wpdb->get_col( $guest_query );

			if ( ! empty( $found_ids ) ) {
				$has_custom_filters = true;
				$matching_order_ids = ! empty( $matching_order_ids )
					? array_intersect( $matching_order_ids, $found_ids )
					: $found_ids;

				if ( empty( $matching_order_ids ) ) {
					return false;
				}
			} else {
				return false;
			}
		}

		// Handle product name filter
		if ( ! empty( $params['product_name'] ) ) {
			$product_name = sanitize_text_field( $params['product_name'] );

			// Define search and replace patterns for REGEXP
			$filter_search  = [ '*', ', ', ',' ];
			$filter_replace = [ '%', '|', '|' ];

			$filter = str_replace( $filter_search, $filter_replace, $product_name );

			// Product name filter uses the same tables for both HPOS and traditional
			$product_query = $wpdb->prepare(
				"SELECT DISTINCT order_id FROM {$wpdb->prefix}woocommerce_order_items 
				WHERE order_item_type = 'line_item' 
				AND order_item_name REGEXP %s",
				$filter
			);

			$found_ids = $wpdb->get_col( $product_query );

			if ( ! empty( $found_ids ) ) {
				$has_custom_filters = true;
				$matching_order_ids = ! empty( $matching_order_ids )
					? array_intersect( $matching_order_ids, $found_ids )
					: $found_ids;

				if ( empty( $matching_order_ids ) ) {
					return false;
				}
			} else {
				return false;
			}
		}

		// Handle product SKU filter
		if ( ! empty( $params['product_sku'] ) ) {
			$sku = sanitize_text_field( $params['product_sku'] );

			// Define search and replace patterns for REGEXP
			$filter_search  = [ '*', ', ', ',' ];
			$filter_replace = [ '%', '|', '|' ];

			$filter = str_replace( $filter_search, $filter_replace, $sku );

			// SKU filter uses the same tables for both HPOS and traditional
			$sku_query = $wpdb->prepare(
				"SELECT DISTINCT oi.order_id 
				FROM {$wpdb->prefix}woocommerce_order_items oi
				INNER JOIN {$wpdb->prefix}woocommerce_order_itemmeta oim ON oi.order_item_id = oim.order_item_id
				INNER JOIN {$wpdb->postmeta} pm ON oim.meta_value = pm.post_id
				WHERE oi.order_item_type = 'line_item'
				AND oim.meta_key = '_product_id'
				AND pm.meta_key = '_sku'
				AND pm.meta_value REGEXP %s",
				$filter
			);

			$found_ids = $wpdb->get_col( $sku_query );

			if ( ! empty( $found_ids ) ) {
				$has_custom_filters = true;
				$matching_order_ids = ! empty( $matching_order_ids )
					? array_intersect( $matching_order_ids, $found_ids )
					: $found_ids;

				if ( empty( $matching_order_ids ) ) {
					return false;
				}
			} else {
				return false;
			}
		}

		// Handle product categories filter
		if ( ! empty( $params['product_categories'] ) ) {
			$categories = is_array( $params['product_categories'] )
				? $params['product_categories']
				: explode( ',', $params['product_categories'] );

			$category_ids = array_map( 'intval', $categories );
			$placeholders = implode( ',', array_fill( 0, count( $category_ids ), '%d' ) );

			// Product categories filter uses the same tables for both HPOS and traditional
			$product_ids_query = $wpdb->prepare(
				"SELECT object_id FROM {$wpdb->term_relationships}
				WHERE term_taxonomy_id IN (
					SELECT term_taxonomy_id FROM {$wpdb->term_taxonomy}
					WHERE term_id IN ($placeholders) AND taxonomy = 'product_cat'
				)",
				$category_ids
			);

			$product_ids = $wpdb->get_col( $product_ids_query );

			if ( ! empty( $product_ids ) ) {
				$product_placeholders = implode( ',', array_fill( 0, count( $product_ids ), '%d' ) );

				$order_ids_query = $wpdb->prepare(
					"SELECT DISTINCT order_id 
					FROM {$wpdb->prefix}woocommerce_order_items oi
					JOIN {$wpdb->prefix}woocommerce_order_itemmeta oim ON oi.order_item_id = oim.order_item_id
					WHERE oi.order_item_type = 'line_item'
					AND oim.meta_key = '_product_id'
					AND oim.meta_value IN ($product_placeholders)",
					$product_ids
				);

				$found_ids = $wpdb->get_col( $order_ids_query );

				if ( ! empty( $found_ids ) ) {
					$has_custom_filters = true;
					$matching_order_ids = ! empty( $matching_order_ids )
						? array_intersect( $matching_order_ids, $found_ids )
						: $found_ids;

					if ( empty( $matching_order_ids ) ) {
						return false;
					}
				} else {
					return false;
				}
			} else {
				return false;
			}
		}

		// Shipping Country filter
		if ( ! empty( $params['shipping_country'] ) ) {
			$shipping_countries = is_array( $params['shipping_country'] )
				? $params['shipping_country']
				: explode( ',', $params['shipping_country'] );

			$placeholders = implode( ', ', array_fill( 0, count( $shipping_countries ), '%s' ) );

			if ( $using_hpos ) {
				// Check if wc_order_addresses table exists
				$addresses_table_exists = $wpdb->get_var( "SHOW TABLES LIKE '{$wpdb->prefix}wc_order_addresses'" ) === "{$wpdb->prefix}wc_order_addresses";

				if ( $addresses_table_exists ) {
					// HPOS query approach using the addresses table
					$country_query = $wpdb->prepare(
						"SELECT DISTINCT order_id FROM {$wpdb->prefix}wc_order_addresses 
                WHERE address_type = 'shipping' 
                AND country IN ($placeholders)",
						$shipping_countries
					);
				} else {
					// Fallback to the orders meta table
					$country_query = $wpdb->prepare(
						"SELECT DISTINCT order_id FROM {$wpdb->prefix}wc_order_meta 
                WHERE meta_key = 'shipping_country' 
                AND meta_value IN ($placeholders)",
						$shipping_countries
					);
				}
			} else {
				// Traditional meta query approach
				$country_query = $wpdb->prepare(
					"SELECT DISTINCT post_id FROM {$wpdb->postmeta} 
            WHERE meta_key = '_shipping_country' 
            AND meta_value IN ($placeholders)",
					$shipping_countries
				);
			}

			$found_ids = $wpdb->get_col( $country_query );

			if ( ! empty( $found_ids ) ) {
				$has_custom_filters = true;
				$matching_order_ids = ! empty( $matching_order_ids )
					? array_intersect( $matching_order_ids, $found_ids )
					: $found_ids;

				if ( empty( $matching_order_ids ) ) {
					return false;
				}
			} else {
				return false;
			}
		}

		// Shipping State filter
		if ( ! empty( $params['shipping_state'] ) ) {
			$states = is_array( $params['shipping_state'] )
				? $params['shipping_state']
				: explode( ',', $params['shipping_state'] );

			// Extract state codes from country:state format
			$clean_states = [];
			foreach ( $states as $state ) {
				if ( strpos( $state, ':' ) !== false ) {
					$parts = explode( ':', $state );
					$clean_states[] = $parts[1];
				} else {
					$clean_states[] = $state;
				}
			}
			$placeholders = implode( ', ', array_fill( 0, count( $clean_states ), '%s' ) );

			if ( $using_hpos ) {
				// Check if wc_order_addresses table exists
				$addresses_table_exists = $wpdb->get_var( "SHOW TABLES LIKE '{$wpdb->prefix}wc_order_addresses'" ) === "{$wpdb->prefix}wc_order_addresses";

				if ( $addresses_table_exists ) {
					// HPOS query approach using the addresses table
					$state_query = $wpdb->prepare(
						"SELECT DISTINCT order_id FROM {$wpdb->prefix}wc_order_addresses 
                WHERE address_type = 'shipping' 
                AND state IN ($placeholders)",
						$clean_states
					);
				} else {
					// Fallback to the orders meta table
					$state_query = $wpdb->prepare(
						"SELECT DISTINCT order_id FROM {$wpdb->prefix}wc_order_meta 
						WHERE meta_key = 'shipping_state' 
						AND meta_value IN ($placeholders)",
						$clean_states
					);
				}
			} else {
				// Traditional meta query approach
				$state_query = $wpdb->prepare(
					"SELECT DISTINCT post_id FROM {$wpdb->postmeta} 
					WHERE meta_key = '_shipping_state' 
					AND meta_value IN ($placeholders)",
					$clean_states
				);
			}

			$found_ids = $wpdb->get_col( $state_query );

			if ( ! empty( $found_ids ) ) {
				$has_custom_filters = true;
				$matching_order_ids = ! empty( $matching_order_ids )
					? array_intersect( $matching_order_ids, $found_ids )
					: $found_ids;

				if ( empty( $matching_order_ids ) ) {
					return false;
				}
			} else {
				return false;
			}
		}

		// Handle coupon code filter
		if ( ! empty( $params['coupon_code'] ) ) {
			$coupon_code = sanitize_text_field( $params['coupon_code'] );

			// Define search and replace patterns for REGEXP
			$filter_search  = [ '*', ', ', ',' ];
			$filter_replace = [ '%', '|', '|' ];

			$filter = str_replace( $filter_search, $filter_replace, $coupon_code );

			// Coupon code filter uses the same tables for both HPOS and traditional
			$coupon_query = $wpdb->prepare(
				"SELECT DISTINCT order_id 
				FROM {$wpdb->prefix}woocommerce_order_items
				WHERE order_item_type = 'coupon'
				AND order_item_name REGEXP %s",
				$filter
			);

			$found_ids = $wpdb->get_col( $coupon_query );

			if ( ! empty( $found_ids ) ) {
				$has_custom_filters = true;
				$matching_order_ids = ! empty( $matching_order_ids )
					? array_intersect( $matching_order_ids, $found_ids )
					: $found_ids;

				if ( empty( $matching_order_ids ) ) {
					return false;
				}
			} else {
				return false;
			}
		}

		// Is refunded filter
		if ( isset( $params['is_refunded'] ) ) {
			if ( $using_hpos ) {
				if ( $params['is_refunded'] === 'refund' ) {
					// For HPOS, check for refunded orders
					$refund_query = "SELECT DISTINCT o.id 
									FROM {$wpdb->prefix}wc_orders o
									JOIN {$wpdb->prefix}wc_orders r ON o.id = r.parent_order_id
									WHERE r.type = 'shop_order_refund'";
				} else {
					// For HPOS, check for non-refunded orders
					$refund_query = "SELECT o.id 
									FROM {$wpdb->prefix}wc_orders o
									LEFT JOIN {$wpdb->prefix}wc_orders r ON o.id = r.parent_order_id AND r.type = 'shop_order_refund'
									WHERE o.type = 'shop_order' AND r.id IS NULL";
				}
			} elseif ( $params['is_refunded'] === 'refund' ) {
					// Traditional approach for refunded orders
					$refund_query = "SELECT DISTINCT p.post_parent 
									FROM {$wpdb->posts} p
									WHERE p.post_type = 'shop_order_refund'";
			} else {
				// Traditional approach for non-refunded orders
				$refund_query = "SELECT DISTINCT p.ID 
									FROM {$wpdb->posts} p
									LEFT JOIN {$wpdb->posts} r ON p.ID = r.post_parent AND r.post_type = 'shop_order_refund'
									WHERE p.post_type = 'shop_order' AND r.ID IS NULL";
			}

			$found_ids = $wpdb->get_col( $refund_query );

			if ( ! empty( $found_ids ) ) {
				$has_custom_filters = true;
				$matching_order_ids = ! empty( $matching_order_ids )
					? array_intersect( $matching_order_ids, $found_ids )
					: $found_ids;

				if ( empty( $matching_order_ids ) ) {
					return false;
				}
			} else {
				return false;
			}
		}

		// Payment Status (is_paid) filter
		if ( isset( $params['is_paid'] ) ) {
			if ( $using_hpos ) {
				// Get all order statuses
				$order_statuses = wc_get_order_statuses();

				// First, get the actual status values from the database
				$status_query = "SELECT DISTINCT status FROM {$wpdb->prefix}wc_orders";
				$available_statuses = $wpdb->get_col( $status_query );

				if ( $params['is_paid'] === 'paid' ) {
					// For paid orders, look for completed, processing statuses
					$paid_statuses = [];
					foreach ( $available_statuses as $status ) {
						$lower_status = strtolower( $status );
						if (
							strpos( $lower_status, 'completed' ) !== false ||
							strpos( $lower_status, 'processing' ) !== false ||
							strpos( $lower_status, 'refunded' ) !== false
						) {
							$paid_statuses[] = $status;
						}
					}

					if ( ! empty( $paid_statuses ) ) {
						$status_placeholders = implode( "','", array_map( 'esc_sql', $paid_statuses ) );
						$paid_query = "SELECT id FROM {$wpdb->prefix}wc_orders 
                            WHERE status IN ('$status_placeholders')";
					} else {
						// Fallback - check for transaction_id which indicates payment
						$paid_query = "SELECT id FROM {$wpdb->prefix}wc_orders 
                            WHERE transaction_id != '' AND transaction_id IS NOT NULL";
					}
				} else {
					// For unpaid orders, look for pending, on-hold, failed, cancelled statuses
					$unpaid_statuses = [];
					foreach ( $available_statuses as $status ) {
						$lower_status = strtolower( $status );
						if (
							strpos( $lower_status, 'pending' ) !== false ||
							strpos( $lower_status, 'on-hold' ) !== false ||
							strpos( $lower_status, 'failed' ) !== false ||
							strpos( $lower_status, 'cancelled' ) !== false
						) {
							$unpaid_statuses[] = $status;
						}
					}

					if ( ! empty( $unpaid_statuses ) ) {
						$status_placeholders = implode( "','", array_map( 'esc_sql', $unpaid_statuses ) );
						$paid_query = "SELECT id FROM {$wpdb->prefix}wc_orders 
                            WHERE status IN ('$status_placeholders')";
					} else {
						// Fallback - check for empty transaction_id which might indicate no payment
						$paid_query = "SELECT id FROM {$wpdb->prefix}wc_orders 
                            WHERE transaction_id = '' OR transaction_id IS NULL";
					}
				}
			} else {
				// Traditional meta query approach
				if ( $params['is_paid'] === 'paid' ) {
					// Get paid statuses (completed, processing, refunded)
					$paid_statuses = [ 'wc-completed', 'wc-processing', 'wc-refunded' ];
					$status_placeholders = implode( "','", $paid_statuses );

					$paid_query = "SELECT DISTINCT p.ID FROM {$wpdb->posts} p
                        WHERE p.post_type = 'shop_order'
                        AND p.post_status IN ('$status_placeholders')";
				} else {
					// Get unpaid statuses (pending, on-hold, failed, cancelled)
					$unpaid_statuses = [ 'wc-pending', 'wc-on-hold', 'wc-failed', 'wc-cancelled' ];
					$status_placeholders = implode( "','", $unpaid_statuses );

					$paid_query = "SELECT DISTINCT p.ID FROM {$wpdb->posts} p
                        WHERE p.post_type = 'shop_order'
                        AND p.post_status IN ('$status_placeholders')";
				}
			}

			$found_ids = $wpdb->get_col( $paid_query );

			if ( ! empty( $found_ids ) ) {
				$has_custom_filters = true;
				$matching_order_ids = ! empty( $matching_order_ids )
					? array_intersect( $matching_order_ids, $found_ids )
					: $found_ids;

				if ( empty( $matching_order_ids ) ) {
					return false;
				}
			} else {
				return false;
			}
		}

		// Order Notes filter
		if ( ! empty( $params['order_notes'] ) ) {
			$notes_text = sanitize_text_field( $params['order_notes'] );

			// Use LIKE instead of REGEXP for better compatibility
			$like_term = '%' . $wpdb->esc_like( $notes_text ) . '%';

			$found_ids = [];

			if ( $using_hpos ) {
				// First check the customer_note field in the orders table
				$customer_note_query = $wpdb->prepare(
					"SELECT id FROM {$wpdb->prefix}wc_orders
            WHERE customer_note LIKE %s",
					$like_term
				);

				$found_ids = $wpdb->get_col( $customer_note_query );

				// Also check the comments table for order notes
				$comments_query = $wpdb->prepare(
					"SELECT DISTINCT comment_post_ID FROM {$wpdb->comments}
					WHERE comment_type = 'order_note'
					AND comment_content LIKE %s",
					$like_term
				);

				$comment_ids = $wpdb->get_col( $comments_query );

				// Combine the results
				$found_ids = array_unique( array_merge( $found_ids, $comment_ids ) );
			} else {
				// For traditional storage, check both customer_note meta and comments
				$meta_query = $wpdb->prepare(
					"SELECT DISTINCT post_id FROM {$wpdb->postmeta}
					WHERE meta_key = '_customer_note'
					AND meta_value LIKE %s",
					$like_term
				);

				$meta_ids = $wpdb->get_col( $meta_query );

				// Also check the comments table
				$comments_query = $wpdb->prepare(
					"SELECT DISTINCT comment_post_ID FROM {$wpdb->comments}
					WHERE comment_type = 'order_note'
					AND comment_content LIKE %s",
					$like_term
				);

				$comment_ids = $wpdb->get_col( $comments_query );

				// Combine the results
				$found_ids = array_unique( array_merge( $meta_ids, $comment_ids ) );
			}

			if ( ! empty( $found_ids ) ) {
				$has_custom_filters = true;
				$matching_order_ids = ! empty( $matching_order_ids )
					? array_intersect( $matching_order_ids, $found_ids )
					: $found_ids;

				if ( empty( $matching_order_ids ) ) {
					return false;
				}
			} else {
				return false;
			}
		}

		// Custom Meta filter
		if ( ! empty( $params['custom_meta'] ) ) {
			$custom_meta = explode( ':', $params['custom_meta'] );
			if ( count( $custom_meta ) == 2 ) {
				$key = sanitize_text_field( $custom_meta[0] );
				$value = sanitize_text_field( $custom_meta[1] );

				if ( $using_hpos ) {
					// For HPOS, we need to check the wc_orders_meta table
					$custom_query = $wpdb->prepare(
						"SELECT DISTINCT order_id FROM {$wpdb->prefix}wc_order_meta 
						WHERE meta_key = %s 
						AND meta_value = %s",
						$key,
						$value
					);
				} else {
					// Traditional meta query approach
					$custom_query = $wpdb->prepare(
						"SELECT DISTINCT post_id FROM {$wpdb->postmeta} 
						WHERE meta_key = %s 
						AND meta_value = %s",
						$key,
						$value
					);
				}

				$found_ids = $wpdb->get_col( $custom_query );

				if ( ! empty( $found_ids ) ) {
					$has_custom_filters = true;
					$matching_order_ids = ! empty( $matching_order_ids )
						? array_intersect( $matching_order_ids, $found_ids )
						: $found_ids;

					if ( empty( $matching_order_ids ) ) {
						return false;
					}
				} else {
					return false;
				}
			}
		}

		// Apply the matching order IDs if we have custom filters
		if ( $has_custom_filters && ! empty( $matching_order_ids ) ) {
			$query_args['post__in'] = isset( $query_args['post__in'] )
				? array_intersect( $query_args['post__in'], $matching_order_ids )
				: $matching_order_ids;

			if ( isset( $query_args['post__in'] ) && empty( $query_args['post__in'] ) ) {
				return false;
			}
		}

		return $query_args;
	}

	/**
	 * Apply sorting to query arguments
	 *
	 * @param array $query_args
	 * @param array $params
	 * @return array
	 */
	public function wpkin_apply_sorting( $query_args, $params ) {
		$sort_by = $params['sort_by'];
		$sort_order = $params['sort_order'];

		if ( ! empty( $sort_by ) ) {
			if ( $params['is_trash'] ) {
				// For trash orders, modify WP_Query args
				$query_args = $this->wpkin_apply_trash_sorting( $query_args, $sort_by, $sort_order );
			} else {
				// For regular WC_Order_Query
				$query_args = $this->wpkin_apply_regular_sorting( $query_args, $sort_by, $sort_order, $params );
			}
		} else {
			// Default ordering if no sort specified
			if ( $params['is_trash'] ) {
				$query_args['orderby'] = 'date';
				$query_args['order'] = 'DESC';
			} else {
				$query_args['orderby'] = 'date_created';
				$query_args['order'] = 'DESC';
			}
		}

		return $query_args;
	}

	/**
	 * Apply sorting for trash orders
	 *
	 * @param array $query_args
	 * @param string $sort_by
	 * @param string $sort_order
	 * @return array
	 */
	private function wpkin_apply_trash_sorting( $query_args, $sort_by, $sort_order ) {
		switch ( $sort_by ) {
			case 'orderNumber':
				$query_args['orderby'] = 'ID';
				$query_args['order'] = $sort_order;
				break;

			case 'date':
				$query_args['orderby'] = 'date';
				$query_args['order'] = $sort_order;
				break;

			case 'status':
				// Not applicable for trash, default to date
				$query_args['orderby'] = 'date';
				$query_args['order'] = $sort_order;
				break;

			case 'total':
				$query_args['orderby'] = 'meta_value_num';
				$query_args['meta_key'] = '_order_total';
				$query_args['order'] = $sort_order;
				break;

			case 'customer':
				$query_args['orderby'] = 'meta_value';
				$query_args['meta_key'] = '_billing_last_name';
				$query_args['order'] = $sort_order;
				break;

			default:
				$query_args['orderby'] = 'date';
				$query_args['order'] = 'DESC';
		}

		return $query_args;
	}

	/**
	 * Apply sorting for regular orders with special handling for total
	 *
	 * @param array $query_args
	 * @param string $sort_by
	 * @param string $sort_order
	 * @param array $params
	 * @return array
	 */
	private function wpkin_apply_regular_sorting( $query_args, $sort_by, $sort_order, $params = [] ) {
		if ( $sort_by === 'total' ) {
			// Check if we're maintaining pagination during total sort
			$maintain_pagination = isset( $params['maintain_pagination'] ) && $params['maintain_pagination'] === 'true';

			// Get all orders first (without pagination)
			$all_orders_args = $query_args;
			$all_orders_args['limit'] = -1; // Get all orders
			$all_orders_args['paginate'] = false;
			unset( $all_orders_args['page'] ); // Remove pagination

			try {
				// Get all orders that match our criteria
				$all_orders = wc_get_orders( $all_orders_args );

				if ( ! empty( $all_orders ) ) {
					// Sort orders by total
					usort(
						$all_orders,
						function ( $a, $b ) use ( $sort_order ) {
							$total_a = floatval( $a->get_total() );
							$total_b = floatval( $b->get_total() );

							if ( $total_a == $total_b ) {
								return 0;
							}

							if ( $sort_order === 'ASC' ) {
								return ( $total_a < $total_b ) ? -1 : 1;
							} else {
								return ( $total_a > $total_b ) ? -1 : 1;
							}
						}
					);

					// Get order IDs in sorted order
					$sorted_ids = array_map(
						function ( $order ) {
							return $order->get_id();
						},
						$all_orders
					);

					// Apply pagination manually
					$per_page = $query_args['limit'];
					$page = $query_args['page'];
					$offset = ( $page - 1 ) * $per_page;

					// Get the slice of IDs for the current page
					$paged_ids = array_slice( $sorted_ids, $offset, $per_page );

					if ( ! empty( $paged_ids ) ) {
						// Override the query to use our sorted and paged IDs
						$query_args['post__in'] = $paged_ids;
						$query_args['orderby'] = 'post__in'; // Preserve our custom order

						// Store total count for pagination
						$query_args['_total_count'] = count( $sorted_ids );
					} elseif ( $page > 1 && ! $maintain_pagination ) {
						// If we're on a page that doesn't have results, and not maintaining pagination,
						// adjust to page 1
						$query_args['page'] = 1;
						$paged_ids = array_slice( $sorted_ids, 0, $per_page );

						if ( ! empty( $paged_ids ) ) {
							$query_args['post__in'] = $paged_ids;
							$query_args['orderby'] = 'post__in';
							$query_args['_total_count'] = count( $sorted_ids );
						}
					}
				}
			} catch ( \Exception $e ) {
				// Log error but continue with default sorting
				error_log( 'Error during total sorting: ' . $e->getMessage() );
			}

			return $query_args;
		}

		// Handle other sorting options
		switch ( $sort_by ) {
			case 'orderNumber':
				$query_args['orderby'] = 'ID';
				$query_args['order'] = $sort_order;
				break;

			case 'date':
				$query_args['orderby'] = 'date_created';
				$query_args['order'] = $sort_order;
				break;

			case 'status':
				$query_args['orderby'] = 'status';
				$query_args['order'] = $sort_order;
				break;

			default:
				$query_args['orderby'] = 'date_created';
				$query_args['order'] = 'DESC';
		}

		return $query_args;
	}

	/**
	 * Execute the orders query with support for custom total count
	 *
	 * @param array $query_args
	 * @param array $params
	 * @return array|bool
	 */
	public function wpkin_execute_orders_query( $query_args, $params ) {
		// If we have post__in but it's empty, return false (no results)
		if ( isset( $query_args['post__in'] ) && empty( $query_args['post__in'] ) ) {
			return false;
		}

		try {
			// Check if we have a custom total count from total sorting
			$custom_total = isset( $query_args['_total_count'] ) ? intval( $query_args['_total_count'] ) : null;

			// Remove our custom parameter before passing to WooCommerce
			if ( isset( $query_args['_total_count'] ) ) {
				unset( $query_args['_total_count'] );
			}

			// Execute the query
			$orders_query = wc_get_orders( $query_args );

			// Use our custom total if available
			$total_orders = $custom_total !== null ? $custom_total : $orders_query->total;

			return [
				'orders' => $orders_query->orders,
				'total_orders' => $total_orders,
				'total_pages' => ceil( $total_orders / $params['per_page'] ),
			];
		} catch ( \Exception $e ) {
			throw new \Exception( 'Error retrieving orders: ' . $e->getMessage() );
		}
	}

	/**
	 * Format orders for response
	 *
	 * @param array $orders
	 * @return array
	 */
	public function wpkin_format_orders( $orders ) {
		$formatted_orders = [];

		foreach ( $orders as $order ) {
			try {
				if ( ! ( $order instanceof \WC_Order ) ) {
					continue; // Skip if not a valid order object
				}

				// Get WC_Countries instance for country/state name conversion
				$wc_countries = new \WC_Countries();
				$countries = $wc_countries->get_countries();

				// Get billing country and state full names
				$billing_country_code = $order->get_billing_country();
				$billing_state_code = $order->get_billing_state();
				$billing_country_full = isset( $countries[ $billing_country_code ] ) ? $countries[ $billing_country_code ] : $billing_country_code;

				$billing_states = $wc_countries->get_states( $billing_country_code );
				$billing_state_full = isset( $billing_states[ $billing_state_code ] ) ? $billing_states[ $billing_state_code ] : $billing_state_code;

				// Get shipping country and state full names
				$shipping_country_code = $order->get_shipping_country();
				$shipping_state_code = $order->get_shipping_state();
				$shipping_country_full = isset( $countries[ $shipping_country_code ] ) ? $countries[ $shipping_country_code ] : $shipping_country_code;

				$shipping_states = $wc_countries->get_states( $shipping_country_code );
				$shipping_state_full = isset( $shipping_states[ $shipping_state_code ] ) ? $shipping_states[ $shipping_state_code ] : $shipping_state_code;

				// Build line items array
				$line_items = $this->wpkin_get_formatted_line_items( $order );

				// Create formatted order data
				$order_data = $order->get_data();
				$formatted_order = [
					'id' => $order->get_id(),
					'orderNumber' => '#' . $order->get_order_number(),
					'customer' => $order->get_formatted_billing_full_name(),
					'date' => isset( $order_data['date_created'] ) ?
						$order_data['date_created']->date( 'Y-m-d H:i:s' ) : date( 'Y-m-d H:i:s' ),
					'total' => $order->get_total(),
					'status' => $order->get_status(),
					'billing_email' => $order->get_billing_email(),
					'billing_phone' => $order->get_billing_phone(),
					'billing_first_name' => $order->get_billing_first_name(),
					'billing_last_name' => $order->get_billing_last_name(),
					'billing_company' => $order->get_billing_company(),
					'billing_address_1' => $order->get_billing_address_1(),
					'billing_address_2' => $order->get_billing_address_2(),
					'billing_city' => $order->get_billing_city(),
					'billing_state' => $order->get_billing_state(),
					'billing_state_full' => $billing_state_full,
					'billing_postcode' => $order->get_billing_postcode(),
					'billing_country' => $order->get_billing_country(),
					'billing_country_full' => $billing_country_full,
					'shipping_first_name' => $order->get_shipping_first_name(),
					'shipping_last_name' => $order->get_shipping_last_name(),
					'shipping_company' => $order->get_shipping_company(),
					'shipping_address_1' => $order->get_shipping_address_1(),
					'shipping_address_2' => $order->get_shipping_address_2(),
					'shipping_city' => $order->get_shipping_city(),
					'shipping_state' => $order->get_shipping_state(),
					'shipping_state_full' => $shipping_state_full,
					'shipping_postcode' => $order->get_shipping_postcode(),
					'shipping_country' => $order->get_shipping_country(),
					'shipping_country_full' => $shipping_country_full,
					'payment_method' => $order->get_payment_method_title(),
					'shipping_method' => $order->get_shipping_method(),
					'customer_note' => $order->get_customer_note(),
					'line_items' => $line_items,
					'items' => $line_items,
					'last_updated_by'   => $order->get_meta( '_sm_last_updated_by' ) ?: '',
					'last_updated_date' => $order->get_meta( '_sm_last_updated_date' ) ?: '',
				];

				$formatted_orders[] = $formatted_order;
			} catch ( \Exception $e ) {
				// Log error and continue with next order
				error_log( 'Error processing order ' . $order->get_id() . ': ' . $e->getMessage() );
				continue;
			}
		}

		return $formatted_orders;
	}

	/**
	 * Get formatted line items for an order
	 *
	 * @param WC_Order $order
	 * @return array
	 */
	private function wpkin_get_formatted_line_items( $order ) {
		$line_items = [];

		foreach ( $order->get_items() as $item_id => $item ) {
			// Get item data safely
			$item_data = [
				'id' => $item_id,  // Add item_id as id
				'name' => $item->get_name() ?? 'Unknown Product',  // Use get_name() directly
				'quantity' => $item->get_quantity() ?? 1,  // Use get_quantity() directly
				'total' => $item->get_total() ?? 0,  // Use get_total() directly
				'product_id' => $item->get_product_id() ?? 0,  // Use get_product_id() directly
				'variation_id' => $item->get_variation_id() ?? 0,  // Use get_variation_id() directly
				'price' => 0,
				'variation' => '',
				'sku' => '',
				'image_url' => wc_placeholder_img_src(),
			];

			// Try to get the product and add product details
			$product = null;
			if ( ! empty( $item_data['variation_id'] ) ) {
				$product = wc_get_product( $item_data['variation_id'] );
			} elseif ( ! empty( $item_data['product_id'] ) ) {
				$product = wc_get_product( $item_data['product_id'] );
			}

			if ( $product ) {
				$item_data['price'] = $product->get_price();
				$item_data['sku'] = $product->get_sku();

				// Get product image
				$image_id = $product->get_image_id();
				if ( $image_id ) {
					$item_data['image_url'] = wp_get_attachment_image_url( $image_id, 'thumbnail' );
				} else {
					// Try to get parent product image if variation has no image
					if ( $product->is_type( 'variation' ) ) {
						$parent_product = wc_get_product( $product->get_parent_id() );
						if ( $parent_product ) {
							$parent_image_id = $parent_product->get_image_id();
							if ( $parent_image_id ) {
								$item_data['image_url'] = wp_get_attachment_image_url( $parent_image_id, 'thumbnail' );
							}
						}
					}
				}

				// Add variation info
				if ( ! empty( $item_data['variation_id'] ) && function_exists( 'wc_get_formatted_variation' ) ) {
					$item_data['variation'] = wc_get_formatted_variation( $product, true );
				}
			} else {
				// Calculate price from total if we can
				if ( $item_data['quantity'] > 0 && $item_data['total'] > 0 ) {
					$item_data['price'] = $item_data['total'] / $item_data['quantity'];
				}
			}

			$line_items[] = $item_data;
		}

		return $line_items;
	}
}
