<?php

namespace CodeClouds\Unify\Model;

/**
 * Connection post type model.
 * @package CodeClouds\Unify
 */
class Tools_model
{

	/**
	 * Get a connection by ID.
	 * @param int|WP_Post|null $ID
	 * @return array
	 */
	public static function get_post($ID)
	{
		return \get_post($ID);
	}

	/**
	 * Get post meta by connection ID.
	 * @param int $ID
	 * @return array
	 */
	public static function get_post_meta($ID)
	{
		return \get_post_meta($ID);
	}

	public static function get_post_with_meta()
	{
		// Check cache first
		$cache_key = 'unify_products_with_meta';
		$cached_results = wp_cache_get($cache_key, 'unify_tools');
		
		if (false !== $cached_results) {
			return $cached_results;
		}

		// Use WordPress APIs instead of direct database query
		$products = get_posts([
			'post_type' => 'product',
			'post_status' => 'publish',
			'numberposts' => -1,
			'orderby' => 'date',
			'order' => 'DESC',
			'fields' => 'ids', // Only get IDs for better performance
			'no_found_rows' => true, // Skip pagination count query
			'update_post_meta_cache' => false, // Skip meta cache update initially
			'update_post_term_cache' => false, // Skip term cache update initially
		]);

		$results = [];
		$upload_dir = wp_upload_dir();
		$base_url = $upload_dir['baseurl'];

		foreach ($products as $product_id) {
			// Get the product object only when needed
			$product = get_post($product_id);
			
			$thumbnail_id = get_post_meta($product_id, '_thumbnail_id', true);
			$thumbnail_url = '';
			
			if ($thumbnail_id) {
				$thumbnail_file = get_post_meta($thumbnail_id, '_wp_attached_file', true);
				if ($thumbnail_file) {
					$thumbnail_url = $base_url . '/' . $thumbnail_file;
				}
			}

			// Get product categories
			$categories = wp_get_post_terms($product_id, 'product_cat');
			$category_name = '';
			$category_slug = '';
			$category_id = '';
			
			if (!empty($categories) && !is_wp_error($categories)) {
				$category = $categories[0]; // Get first category
				$category_name = $category->name;
				$category_slug = $category->slug;
				$category_id = $category->term_id;
			}

			$results[] = [
				'ID' => $product_id,
				'post_title' => $product->post_title,
				'thumbnail' => $thumbnail_url,
				'post_type' => $product->post_type,
				'category_name' => $category_name,
				'category_slug' => $category_slug,
				'category_id' => $category_id,
			];
		}

		// Cache the results for 1 hour
		wp_cache_set($cache_key, $results, 'unify_tools', HOUR_IN_SECONDS);

		return $results;
	}

	public static function delete_post($post_ID)
	{
		\wp_trash_post($post_ID);
	}

	public static function get_products_with_meta($request = [], $post_ID = '')
	{
		$all_products = [];
		$args = ['post_type' => 'product'];

		if (!empty($request))
		{
			$args['posts_per_page'] = $request['posts_per_page'];
			$args['paged'] = $request['paged'];
			if($request['orderby']){
				$args['orderby'] = $request['orderby'];
			}			
			if($request['order']){
				$args['order'] = $request['order'];
			}			
			if(!empty($request['orderby']) && $request['orderby']=='price'){
				// For price sorting, use a more efficient approach with caching
				$cache_key = 'unify_products_price_sorted_' . md5(serialize($request));
				$cached_results = wp_cache_get($cache_key, 'unify_tools');
				
				if (false !== $cached_results) {
					return $cached_results;
				}
				
				// Use WooCommerce's built-in price sorting if available
				if (function_exists('wc_get_products')) {
					$wc_products = wc_get_products([
						'status' => 'publish',
						'limit' => $request['posts_per_page'] ?? -1,
						'page' => $request['paged'] ?? 1,
						'orderby' => 'price',
						'order' => $request['order'] ?? 'DESC',
						'return' => 'ids',
					]);
					
					// Convert to expected format
					$products_data = [];
					foreach ($wc_products as $product_id) {
						$product = wc_get_product($product_id);
						if ($product) {
							$products_data[] = (object)[
								'ID' => $product_id,
								'post_title' => $product->get_name(),
								'post_type' => 'product',
							];
						}
					}
					
					$result = (object)[
						'posts' => $products_data,
						'found_posts' => count($products_data),
					];
					
					wp_cache_set($cache_key, $result, 'unify_tools', 30 * MINUTE_IN_SECONDS);
					return $result;
				}
				
				// Fallback: Use direct database query with proper indexing
				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- Direct query needed for performance with complex price sorting and JOIN operations that WP_Query cannot efficiently handle
				global $wpdb;
				
				// Sanitize order direction - only allow ASC or DESC
				$order_direction = 'DESC' === strtoupper($request['order']) ? 'DESC' : 'ASC';
				$limit = isset($request['posts_per_page']) ? (int) $request['posts_per_page'] : 10;
				$offset = isset($request['paged']) ? ((int) $request['paged'] - 1) * $limit : 0;
				
				// Build query with validated order direction (safe since it's validated above)
				if ('DESC' === $order_direction) {
					$base_query = "
						SELECT p.ID, p.post_title, p.post_type
						FROM {$wpdb->posts} p
						INNER JOIN {$wpdb->postmeta} pm ON p.ID = pm.post_id
						WHERE p.post_type = 'product'
						AND p.post_status = 'publish'
						AND pm.meta_key = '_price'
						AND pm.meta_value != ''
						ORDER BY CAST(pm.meta_value AS DECIMAL(10,2)) DESC
						LIMIT %d OFFSET %d
					";
				} else {
					$base_query = "
						SELECT p.ID, p.post_title, p.post_type
						FROM {$wpdb->posts} p
						INNER JOIN {$wpdb->postmeta} pm ON p.ID = pm.post_id
						WHERE p.post_type = 'product'
						AND p.post_status = 'publish'
						AND pm.meta_key = '_price'
						AND pm.meta_value != ''
						ORDER BY CAST(pm.meta_value AS DECIMAL(10,2)) ASC
						LIMIT %d OFFSET %d
					";
				}
				
				// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- $base_query contains static SQL with placeholders, safe for prepare()
				$query = $wpdb->prepare($base_query, $limit, $offset);
				
				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.PreparedSQL.NotPrepared -- Direct query needed for performance with complex price sorting that WP_Query cannot efficiently handle
				$products_data = $wpdb->get_results($query);
				
				$result = (object)[
					'posts' => $products_data,
					'found_posts' => count($products_data),
				];
				
				wp_cache_set($cache_key, $result, 'unify_tools', 30 * MINUTE_IN_SECONDS);
				return $result;
			}
		}
		$products = new \WP_Query($args);

		if (!empty($products->posts))
		{
			foreach ($products->posts as $key => $value)
			{
				$all_products[$key] = (array) $value;
				$metas = Connection::get_post_meta($value->ID);
		
				foreach ($metas as $k => $val)
				{
					if (in_array($k, ['codeclouds_unify_connection', 'codeclouds_unify_shipping', 'codeclouds_unify_offer_id', 'codeclouds_unify_billing_model_id', 'codeclouds_unify_group_id']))
					{
						$all_products[$key][$k] = !empty($val[1])?$val[1]:$val[0];
					}
				}
			}
		}

		return ['list' => $all_products, 'total' => $products->max_num_pages];
	}

	

}
