<?php defined('BASEPATH') or exit('No direct script access allowed');

class Product extends Public_Controller
{

	public function __construct()
	{
		parent::__construct();

		$this->load->model('Top_banner_m');
		$this->load->model('Review_m');
		$this->load->model('customer_m');
		$this->load->model('Category_m');
		$this->load->model('Statistic_m');
		$this->load->model('Footer_m');
		$this->load->library('GoogleClient');
		$this->load->library('VisitorTracking');
		$this->load->library('session');

		$loginUrl = $this->googleclient->getLoginUrl();

		$this->data_footer['googleUrl'] = $loginUrl;
		if ($this->session->userdata('site_lang') == 'english') {
			$this->lang->load('mainpage', 'english');
		} else {
			$this->lang->load('mainpage', 'indonesian');
		}
	}

	public function get($alias = NULL)
	{
		$this->load->helper('url');
		$this->visitortracking->trackVisitor();
		$referral = $this->uri->segment(3);

		// UTM data
		$utm_source = $this->input->get('utm_source');
		$utm_medium = $this->input->get('utm_medium');
		$utm_campaign = $this->input->get('utm_campaign');
		$utm_content = $this->input->get('utm_content');

		$isEnglish = $this->session->userdata('site_lang') === 'english';

		// Periksa apakah ada parameter UTM di URL saat ini
		if (!empty($utm_source) || !empty($utm_medium) || !empty($utm_campaign) || !empty($utm_content)) {
			// Hanya update session jika ada parameter UTM di URL
			$this->session->set_userdata('data_utm', [
				'utm_source' => $utm_source,
				'utm_medium' => $utm_medium,
				'utm_campaign' => $utm_campaign,
				'utm_content' => $utm_content
			]);
		}

		$data_utm = $this->session->userdata('data_utm') ?: [];
		$data['data_utm'] = $data_utm;

		$activeBanners = $this->Top_banner_m->get_active_banners();
		$websiteData = $this->db->select('website_icon, browser_title, meta_description')
			->from('configuration')
			->where('id_configuration', 1)
			->get()
			->row();


		$this->lang->load('mainpage', $this->session->userdata('site_lang') == 'english' ? 'english' : 'indonesian');

		$product = $this->db->select('id_products, brand_id, title, alias, description, description_en, long_description, long_description_en, shipment_method, priority, best_seller, created_at, product_status, new_arrival, popular_product, meta_title, meta_description, media_file_link, restock, product_suggest, product_video_link, product_guide_link, updated_at, deleted_at')
			->from('products')
			->where(['alias' => $alias, 'product_status' => 1])
			->get()
			->row();

		// Ambil ID kategori dari produk
		$category = $this->db->select('id_category')
			->from('category_product')
			->where('id_product', $product->id_products)
			->get()
			->row();

		$review_aspects = [];
		if ($category) {
			$review_aspects = $this->db->select("
					ra.id,
					ra.aspect_name,
					ra.display_name" . ($isEnglish ? "_en" : "") . " AS display_name,
					ra.description" . ($isEnglish ? "_en" : "") . " AS description
				")
				->from('review_aspects ra')
				->where('ra.id_category', $category->id_category)
				->where('ra.is_active', 1)
				->order_by('ra.sort_order', 'asc')
				->get()
				->result_array();

			// Ambil opsi rating
			foreach ($review_aspects as &$aspect) {
				$options = $this->db->select("
						rating_value,
						label" . ($isEnglish ? "_en" : "") . " AS label
					")
					->from('review_rating_options')
					->where('aspect_id', $aspect['id'])
					->order_by('rating_value', 'asc')
					->get()
					->result_array();

				$aspect['options'] = $options;
			}
		}


		if (!$product) {
			show_404();
		}
		$product_details = $this->db->select('*')
			->from('product_details')
			->where('product_id', $product->id_products)
			->get()
			->result();

		$campaign = $this->campaignmanager->get_campaign_from_session();
		if ($campaign) {
			$this->load->model('Promotional_campaign_m');
			// Check if this product is eligible for campaign
			$is_eligible = $this->Promotional_campaign_m->is_product_eligible($campaign->id, $product->id_products, $category->id_category);

			if ($is_eligible) {
				$product_details = $this->campaignmanager->apply_discount_to_products($product_details, $campaign);

				// Log product click
				$this->campaignmanager->log_campaign_access(
					$campaign->id,
					'click_product',
					$product->id_products
				);
			}
		}

		$product_image = $this->db->select('*')
			->from('product_images')
			->where(['product_details_id' => $product_details[0]->id, 'status' => 1, 'priority' => 1])
			->limit(1)
			->get()
			->row();

		// Jika ada gambar, set gambar untuk og_image, jika tidak, gunakan gambar default
		$image_url = $product_image ? base_url('uploads/product/' . $product_image->image) : base_url('uploads/product/' . $product->image);

		$this->data_header['browser_title'] .= ' - ' . $product->title;
		$this->data_header['meta_description'] = $product->meta_description;
		$this->data_header['og_title'] = $product->title;
		$this->data_header['og_description'] = strip_tags($product->description);
		$this->data_header['og_image'] = $image_url;
		$this->data_header['og_url'] = current_url();
		$this->data_header['og_type'] = 'product';

		$customer = $this->session->userdata('customer');
		$customer_id = $customer['customer_id'];
		$customer_data = $this->db->where('id_customers', $customer_id)->get('customers')->row_array();
		$reseller_id = $customer_data['reseller_id'];
		$reseller = $this->db->where('id_resellers', $customer_data['reseller_id'])->where('active', 'yes')->get('resellers')->row_array();

		foreach ($product_details as &$detail) {
			$current_price = $detail->discounted_price > 0 ? $detail->discounted_price : $detail->price;
			$original_price = $detail->discounted_price > 0 ? $detail->price : 0;

			// Check if customer is a reseller and set the MSRP price
			$is_reseller = false;
			$msrp_price = null;

			if (!empty($customer_id)) {
				if (!empty($reseller_id)) {
					// Check if reseller is valid
					if ($reseller) {
						$is_reseller = true;
						// Get reseller price for the product
						$reseller_price = $this->db->where('product_detail_id', $detail->id)
							->where('reseller_id', $customer_data['reseller_id'])
							->get('resellers_price')
							->row();

						if ($reseller_price) {
							$msrp_price = $current_price;
							$current_price = $reseller_price->price;
						}
					}
				}
			}

			$detail->current_price = $current_price;
			$detail->original_price = $original_price;
			$detail->msrp_price = $msrp_price;

			$stock = $this->db->select('stock, stock_keep, stock_reject, stock_sample')
				->from('stock')
				->where('id_product_detail', $detail->id)
				->get()
				->row();

			if ($stock) {
				// Hitung stock_sell dan pastikan tidak minus
				$stock_sell = $stock->stock - $stock->stock_keep -
					(isset($stock->stock_reject) ? $stock->stock_reject : 0) -
					(isset($stock->stock_sample) ? $stock->stock_sample : 0);
				$detail->stock_available = max(0, $stock_sell);
			} else {
				$detail->stock_available = 0;
			}

			// Ambil Variants
			$detail->variants = $this->db->select('pd.product_id as product_id, pd.id as product_detail_id,
                    pa.product_attribute, pad.attribute_detail, pad.color_hex, 
                    pc.attribute_id, pc.attribute_detail_id, COALESCE(GROUP_CONCAT(DISTINCT CONCAT_WS(": ", pa.product_attribute, pad.attribute_detail) SEPARATOR "; "), "No variants available") AS variants, 
					COALESCE(GROUP_CONCAT(DISTINCT pad.attribute_detail ORDER BY pa.product_attribute SEPARATOR " - "), "No variants available") AS formatted_variants')
				->from('product_combination pc')
				->join('product_attributes pa', 'pc.attribute_id = pa.id')
				->join('product_attributes_detail pad', 'pc.attribute_detail_id = pad.id')
				->join('product_details pd', 'pc.product_details_id = pd.id')
				->where('pc.product_details_id', $detail->id)
				->get()
				->result_array();
		}

		usort($product_details, function ($a, $b) {
			return $b->stock_available - $a->stock_available;
		});

		$initial_product_detail = $product_details[0];

		$initial_variant = $initial_product_detail->variants[0] ?? null;

		$id_detail_products = [];
		foreach ($product_details as &$detail) {
			$id_detail_products[] = $detail->id;
		}

		$attributes = $this->db->query("
			SELECT DISTINCT pa.id, pa.product_attribute, pa.is_color, pa.priority
			FROM product_attributes pa
			JOIN product_combination pc ON pa.id = pc.attribute_id
			JOIN product_details pd ON pc.product_details_id = pd.id
			WHERE pd.product_id = ?
			ORDER BY pa.priority ASC
		", [$product->id_products])->result();

		$variants_list = [];

		foreach ($attributes as $attr) {
			// Query untuk mengambil rincian atribut berdasarkan atribut yang ditemukan
			$variants[$attr->id] = $this->db->query("
				SELECT DISTINCT pad.id, pad.attribute_detail, pad.color_hex, pd.product_id as product_id, 
					pd.id as product_detail_id, pd.price, pd.discounted_price,
					s.stock, s.stock_keep, s.stock_reject, s.stock_sample,
					(s.stock - s.stock_keep - COALESCE(s.stock_reject, 0) - COALESCE(s.stock_sample, 0)) as stock_available
				FROM product_attributes_detail pad
				JOIN product_combination pc ON pad.id = pc.attribute_detail_id
				JOIN product_details pd ON pc.product_details_id = pd.id
				LEFT JOIN stock s ON pd.id = s.id_product_detail
				WHERE pd.product_id = ? 
				AND pc.attribute_id = ?
				ORDER BY GREATEST((s.stock - s.stock_keep - COALESCE(s.stock_reject, 0) - COALESCE(s.stock_sample, 0)), 0) DESC, pad.attribute_detail ASC
			", [$product->id_products, $attr->id])->result();

			// Tambahkan ke daftar varian
			$variants_list[$attr->id] = [
				'attribute' => $attr,
				'details' => $variants[$attr->id]
			];
		}

		$product_images = $this->db->select('*')
			->from('product_images')
			->where('product_id', $product->id_products)
			->where('product_details_id', $initial_product_detail->id)
			->where('status', 1)
			->order_by('priority', 'ASC')
			->get()
			->result();

		$this->db->select('
					pr.id,
					pr.review_date,
					pr.rating,
					pr.subject,
					pr.review,
					pr.is_verified_purchase,
					pr.display_name,
                    p.id_products AS product_id,
                    p.title AS product_name,
                    p.alias AS product_alias,
					p.deleted_at,
                    COUNT(pr.id) AS review_count,
                    AVG(pr.rating) AS average_rating,
					SUM(CASE WHEN pr.rating = 5 THEN 1 ELSE 0 END) AS rating_5,
					SUM(CASE WHEN pr.rating = 4 THEN 1 ELSE 0 END) AS rating_4,
					SUM(CASE WHEN pr.rating = 3 THEN 1 ELSE 0 END) AS rating_3,
					SUM(CASE WHEN pr.rating = 2 THEN 1 ELSE 0 END) AS rating_2,
					SUM(CASE WHEN pr.rating = 1 THEN 1 ELSE 0 END) AS rating_1')
			->from('product_review pr')
			->join('products p', 'p.id_products = pr.product_id')
			->where('p.product_status', 1)
			->where('p.deleted_at', null)
			->where('pr.status', 'approved')
			->where('pr.product_id', $product->id_products)
			->group_by('pr.product_id');

		$reviews = $this->db->get()->row();

		// Ambil detail ulasan
		$this->db->select('
			pr.id,
			pr.product_id,
			pr.review_date,
			pr.customer_id,
			pr.is_verified_purchase,
			pr.status,
			pr.rating,
			pr.subject,
			pr.review,
			pr.display_name,
			');
		$this->db->from('product_review pr');
		$this->db->where('pr.status', 'approved');
		$this->db->where('pr.product_id', $product->id_products);
		$this->db->order_by('pr.review_date', 'DESC');

		$review_details = $this->db->get()->result();
		foreach ($review_details as &$review) {
			$aspect_ratings = $this->db->select("
					rar.aspect_id,
					rar.rating,
					ra.aspect_name AS aspect_title,
					ra.display_name" . ($isEnglish ? "_en" : "") . " AS aspect_name,
				")
				->from('review_aspect_ratings rar')
				->join('review_aspects ra', 'rar.aspect_id = ra.id')
				->where('rar.review_id', $review->id)
				->get()
				->result();

			$review->aspect_ratings = $aspect_ratings;
		}


		// Produk Suggest
		$product_suggest = $product->product_suggest;
		if (!empty($product_suggest)) {
			$suggested_ids = explode(',', $product_suggest);
			$suggested_products = $this->db->select('
				p.id_products, 
				p.title, 
				p.alias,
				p.brand_id,
				pd.id AS id_detail, 
				pd.price, 
				pd.discounted_price,
				pd.sku, 
				COALESCE(variants.variants, "No variants available") AS variants,
				pi.image,
				pi_secondary.image AS image_secondary, 
				s.stock, 
				s.stock_keep,
				s.stock_reject,
				s.stock_sample,
				(COALESCE(s.stock, 0) - COALESCE(s.stock_keep, 0) - COALESCE(s.stock_reject, 0) - COALESCE(s.stock_sample, 0)) AS stock_sell,
				IF(COALESCE(s.stock, 0) - COALESCE(s.stock_keep, 0) - COALESCE(s.stock_reject, 0) - COALESCE(s.stock_sample, 0) <= 0, 1, 0) as sort_order,
				total_sales.total_sales,
				total_reviews.total_reviews,
				rp.price AS reseller_price,
				rp.min_quantity AS reseller_min_quantity,
				
				-- Badges Information (Added)
				COALESCE(product_badges_data.badges_names, "") AS badges_names,
				COALESCE(product_badges_data.badges_data, "") AS badges_data,
				COALESCE(product_badges_data.badges_count, 0) AS badges_count,
				
				-- Discount calculations (Added)
				CASE
					WHEN pd.discounted_price > 0 AND pd.discounted_price < pd.price
					THEN (pd.price - pd.discounted_price)
					ELSE 0
				END AS savings_amount,
				
				CASE
					WHEN pd.discounted_price > 0 AND pd.discounted_price < pd.price
					THEN ROUND(((pd.price - pd.discounted_price) / pd.price) * 100, 0)
					ELSE 0
				END AS discount_percentage
			')
				->from('products p')
				->join('product_details pd', 'p.id_products = pd.product_id', 'left')
				->join('product_images pi', 'pd.id = pi.product_details_id AND pi.priority = 1 AND pi.status = 1', 'left')
				->join('product_images pi_secondary', 'pd.id = pi_secondary.product_details_id AND pi_secondary.priority = 2 AND pi_secondary.status = 1', 'left')
				->join('stock s', 'pd.id = s.id_product_detail', 'left')
				->join('(SELECT item_id, SUM(quantity) AS total_sales FROM orders_detail WHERE warehouse_id = 1 GROUP BY item_id) total_sales', 'pd.id = total_sales.item_id', 'left')
				->join('(SELECT product_id, COUNT(id) AS total_reviews FROM product_review GROUP BY product_id) total_reviews', 'p.id_products = total_reviews.product_id', 'left')
				->join('(SELECT pc.product_details_id, GROUP_CONCAT(DISTINCT CONCAT_WS(": ", pa.product_attribute, pad.attribute_detail) SEPARATOR "; ") AS variants 
					FROM product_combination pc 
					JOIN product_attributes pa ON pc.attribute_id = pa.id 
					JOIN product_attributes_detail pad ON pc.attribute_detail_id = pad.id 
					GROUP BY pc.product_details_id) variants', 'pd.id = variants.product_details_id', 'left')
				->join('category_product cp', 'p.id_products = cp.id_product', 'inner')
				->join('resellers_price rp', 'pd.id = rp.product_detail_id', 'left')

				// Product Badges Join (Added)
				->join('(SELECT 
					pb.product_id,
					COUNT(pb.badge_id) AS badges_count,
					GROUP_CONCAT(b.name ORDER BY pb.priority ASC SEPARATOR ", ") AS badges_names,
					GROUP_CONCAT(
						CONCAT(
							b.id, ":", 
							b.name, ":", 
							b.slug, ":", 
							IFNULL(b.description, ""), ":", 
							IFNULL(b.background_color, "#FF6B6B"), ":", 
							IFNULL(b.text_color, "#FFFFFF"), ":", 
							IFNULL(b.icon, ""), ":", 
							pb.position, ":", 
							pb.priority
						)
						ORDER BY pb.priority ASC
						SEPARATOR "|"
					) AS badges_data
				FROM product_badges pb
				INNER JOIN badges b ON pb.badge_id = b.id
				WHERE pb.is_active = 1 
					AND b.is_active = 1
					AND (pb.start_date IS NULL OR pb.start_date <= NOW())
					AND (pb.end_date IS NULL OR pb.end_date >= NOW())
				GROUP BY pb.product_id) product_badges_data', 'p.id_products = product_badges_data.product_id', 'left')

				->where_in('p.id_products', $suggested_ids)
				->where('p.product_status', '1')
				->where('p.deleted_at', null)
				->having('(COALESCE(s.stock, 0) - COALESCE(s.stock_keep, 0)) > 0')
				->group_by('p.id_products')
				->order_by('sort_order', 'ASC')
				->order_by('pd.id', 'ASC');

			$this->db->cache_on();
			$suggested_products_data = $this->db->get()->result_array();
			$this->db->cache_off();

			$campaign = $this->campaignmanager->get_campaign_from_session();
			if ($campaign) {
				$this->load->model('Promotional_campaign_m');

				// Filter hanya produk yang eligible
				foreach ($suggested_products_data as $key => &$suggested_product) {
					$product_category = $this->db->select('id_category')
						->from('category_product')
						->where('id_product', $suggested_product['id_products'])
						->get()
						->row();

					if ($product_category) {
						$is_eligible = $this->Promotional_campaign_m->is_product_eligible(
							$campaign->id,
							$suggested_product['id_products'],
							$product_category->id_category
						);

						if ($is_eligible) {
							$suggested_product['is_campaign_eligible'] = true;
						}
					}
				}

				// Apply discount ke semua yang eligible sekaligus
				$suggested_products_data = $this->campaignmanager->apply_discount_to_products_array(
					$suggested_products_data,
					$campaign
				);
			}

			$suggested_products = $this->prepare_all_products_optimized($suggested_products_data);
		} else {
			$suggested_products_data = [];
		}

		$is_wishlisted = $this->_check_wishlist_status($product->id_products, $customer_id);
		$wishlist_count = $this->_check_wishlist_counts($product->id_products);

		$footer_categories = $this->Footer_m->get_all_categories();
		$footer_social_media = $this->Footer_m->get_social_media();
		$footer_payment_methods = $this->Footer_m->get_payment_methods();
		$footer_asmaradoor = $this->Footer_m->get_asmaradoor();
		$footer_bottom = $this->Footer_m->get_footer_bottom();

		// Tambahkan ke data
		$data = compact(
			'review_details',
			'product',
			'product_details',
			'id_detail_products',
			'initial_product_detail',
			'initial_variant',
			'product_images',
			'reviews',
			'suggested_products',
			'reseller',
			'variants_list',
			'is_wishlisted',
			'wishlist_count',
			'footer_categories',
			'footer_social_media',
			'footer_payment_methods',
			'footer_asmaradoor',
			'footer_bottom',
			'review_aspects'
		);

		$this->session->set_userdata('productpage_to_cart', base_url() . 'product/' . $alias);


		$this->load->view("themes/3/header_new", $this->data_header);
		$this->load->view("themes/3/product_new", $data);
		$this->load->view("themes/3/footer_new", $this->data_footer);
	}

	public function index()
	{
		$this->visitortracking->trackVisitor();

		$query = $this->get_optimized_product_query();

		$this->db->cache_on();
		$all_products = $this->db->query($query)->result_array();
		$this->db->cache_off();

		$campaign = $this->campaignmanager->get_campaign_from_session();
		if ($campaign) {
			$this->load->model('Promotional_campaign_m');

			// Filter hanya produk yang eligible
			foreach ($all_products as $key => &$all_product) {
				$product_category = $this->db->select('id_category')
					->from('category_product')
					->where('id_product', $all_product['id_products'])
					->get()
					->row();

				if ($product_category) {
					$is_eligible = $this->Promotional_campaign_m->is_product_eligible(
						$campaign->id,
						$all_product['id_products'],
						$product_category->id_category
					);

					if ($is_eligible) {
						$all_product['is_campaign_eligible'] = true;
					}
				}
			}

			// Apply discount ke semua yang eligible sekaligus
			$all_products = $this->campaignmanager->apply_discount_to_products_array(
				$all_products,
				$campaign
			);
		}

		$formatted_all_products = $this->prepare_all_products_optimized($all_products);

		$data = [
			'products' => $formatted_all_products,
			'max_price' => $this->getMaxPrice()
		];

		$meta_description = ($this->session->userdata('site_lang') == 'english')
			? "Enjoy the best intimate experience with Laci Asmara's curated pleasure. Comfortable, safe, and fun for a more exciting exploration!"
			: "Nikmati pengalaman intim terbaik dengan piranti asmara pilihan Laci Asmara. Nyaman, aman, dan menyenangkan untuk eksplorasi yang lebih menantang!";

		// Prepare header data
		$this->data_header['browser_title'] .= ' - All Products';
		$this->data_header['meta_description'] = $meta_description;

		$this->load->view("themes/3/header_new", $this->data_header);
		$this->load->view("all-products", $data);
		$this->load->view("themes/3/footer_new", $this->data_footer);
	}

	private function get_optimized_product_query()
	{
		return "
            SELECT 
            p.id_products,
            p.title,
            p.alias,
            p.brand_id,
            p.created_at,
            p.best_seller,
            p.new_arrival,
            p.popular_product,
            p.priority,
            
            -- Product Details
            COALESCE(primary_variant.id, first_variant.id) AS id_detail,
            COALESCE(primary_variant.price, first_variant.price) AS price,
            COALESCE(primary_variant.discounted_price, first_variant.discounted_price) AS discounted_price,
            COALESCE(primary_variant.sku, first_variant.sku) AS sku,
            
            -- Product Images
            COALESCE(
                main_image.image,
                COALESCE(primary_variant_image.image, first_variant_image.image),
                p.image
            ) AS image,
            COALESCE(
                main_image_secondary.image,
                COALESCE(primary_variant_image_secondary.image, first_variant_image_secondary.image),
                p.image1
            ) AS image_secondary,
            
            -- Stock Information
            stock_summary.total_stock,
            stock_summary.total_stock_keep,
            stock_summary.total_stock_sell,
            stock_summary.available_variants,
            stock_summary.total_variants,
            
            -- Current variant stock
            COALESCE(
                GREATEST(0, COALESCE(primary_variant_stock.stock, 0) - COALESCE(primary_variant_stock.stock_keep, 0)),
                GREATEST(0, COALESCE(first_variant_stock.stock, 0) - COALESCE(first_variant_stock.stock_keep, 0)),
                0
            ) AS current_variant_stock_sell,
            
            -- Availability Flags
            CASE 
                WHEN stock_summary.total_stock_sell <= 0 THEN 1 
                ELSE 0 
            END AS is_completely_sold_out,
            
            CASE 
                WHEN COALESCE(
                    GREATEST(0, COALESCE(primary_variant_stock.stock, 0) - COALESCE(primary_variant_stock.stock_keep, 0)),
                    GREATEST(0, COALESCE(first_variant_stock.stock, 0) - COALESCE(first_variant_stock.stock_keep, 0)),
                    0
                ) <= 0 THEN 1 
                ELSE 0 
            END AS current_variant_out_of_stock,
            
            CASE 
                WHEN stock_summary.available_variants > 1 THEN 1 
                ELSE 0 
            END AS has_other_variants_available,
            
            -- Variants
            COALESCE(
                primary_variant_attrs.variants,
                first_variant_attrs.variants,
                'No variants available'
            ) AS variants,
            
            -- Badges Information
            COALESCE(product_badges_data.badges_names, '') AS badges_names,
            COALESCE(product_badges_data.badges_data, '') AS badges_data,
            COALESCE(product_badges_data.badges_count, 0) AS badges_count,
            
            -- Additional data
            COALESCE(total_sales.total_sales, 0) AS total_sales,
            COALESCE(total_reviews.total_reviews, 0) AS total_reviews,
            COALESCE(reseller_price.price, 0) AS reseller_price,
            COALESCE(reseller_price.min_quantity, 0) AS reseller_min_quantity,
            COALESCE(b.priority, 0) AS brand_priority,
            
            -- Discount calculations
            CASE
                WHEN COALESCE(primary_variant.discounted_price, first_variant.discounted_price) > 0 
                     AND COALESCE(primary_variant.discounted_price, first_variant.discounted_price) < COALESCE(primary_variant.price, first_variant.price)
                THEN (COALESCE(primary_variant.price, first_variant.price) - COALESCE(primary_variant.discounted_price, first_variant.discounted_price))
                ELSE 0
            END AS savings_amount,
            
            CASE
                WHEN COALESCE(primary_variant.discounted_price, first_variant.discounted_price) > 0 
                     AND COALESCE(primary_variant.discounted_price, first_variant.discounted_price) < COALESCE(primary_variant.price, first_variant.price)
                THEN ROUND(((COALESCE(primary_variant.price, first_variant.price) - COALESCE(primary_variant.discounted_price, first_variant.discounted_price)) / COALESCE(primary_variant.price, first_variant.price)) * 100, 0)
                ELSE 0
            END AS discount_percentage,
            
            -- Sort order
            CASE 
                WHEN stock_summary.total_stock_sell <= 0 THEN 1 
                ELSE 0 
            END AS sort_order

        FROM products p

        -- Stock Summary
        LEFT JOIN (
            SELECT 
                pd.product_id,
                COUNT(DISTINCT pd.id) AS total_variants,
                SUM(COALESCE(s.stock, 0)) AS total_stock,
                SUM(COALESCE(s.stock_keep, 0)) AS total_stock_keep,
                SUM(GREATEST(0, COALESCE(s.stock, 0) - COALESCE(s.stock_keep, 0))) AS total_stock_sell,
                SUM(CASE WHEN GREATEST(0, COALESCE(s.stock, 0) - COALESCE(s.stock_keep, 0)) > 0 THEN 1 ELSE 0 END) AS available_variants
            FROM product_details pd
            LEFT JOIN stock s ON pd.id = s.id_product_detail
            GROUP BY pd.product_id
        ) stock_summary ON p.id_products = stock_summary.product_id

        -- Primary Variant
        LEFT JOIN (
            SELECT pd.*
            FROM product_details pd
            INNER JOIN (
                SELECT product_id, MIN(id) as min_id
                FROM product_details
                GROUP BY product_id
            ) min_pd ON pd.product_id = min_pd.product_id AND pd.id = min_pd.min_id
        ) primary_variant ON p.id_products = primary_variant.product_id

        LEFT JOIN (
            SELECT pd.*
            FROM product_details pd
            LEFT JOIN stock s ON pd.id = s.id_product_detail
            INNER JOIN (
                SELECT pd.product_id, MAX(GREATEST(0, COALESCE(s.stock, 0) - COALESCE(s.stock_keep, 0))) AS max_stock
                FROM product_details pd
                LEFT JOIN stock s ON pd.id = s.id_product_detail
                GROUP BY pd.product_id
            ) max_stock_pd ON pd.product_id = max_stock_pd.product_id
            LEFT JOIN stock s2 ON pd.id = s2.id_product_detail
            WHERE GREATEST(0, COALESCE(s2.stock, 0) - COALESCE(s2.stock_keep, 0)) = max_stock_pd.max_stock
        ) first_variant ON p.id_products = first_variant.product_id

        -- Stock for displayed variants
        LEFT JOIN stock primary_variant_stock ON primary_variant.id = primary_variant_stock.id_product_detail
        LEFT JOIN stock first_variant_stock ON first_variant.id = first_variant_stock.id_product_detail

        -- Product Images
        LEFT JOIN product_images main_image ON p.id_products = main_image.product_id 
            AND main_image.product_details_id = 0 
            AND main_image.priority = 1 
            AND main_image.status = '1'

        LEFT JOIN product_images main_image_secondary ON p.id_products = main_image_secondary.product_id 
            AND main_image_secondary.product_details_id = 0 
            AND main_image_secondary.priority = 2 
            AND main_image_secondary.status = '1'

        LEFT JOIN product_images primary_variant_image ON primary_variant.id = primary_variant_image.product_details_id 
            AND primary_variant_image.priority = 1 
            AND primary_variant_image.status = '1'

        LEFT JOIN product_images primary_variant_image_secondary ON primary_variant.id = primary_variant_image_secondary.product_details_id 
            AND primary_variant_image_secondary.priority = 2 
            AND primary_variant_image_secondary.status = '1'

        LEFT JOIN product_images first_variant_image ON first_variant.id = first_variant_image.product_details_id 
            AND first_variant_image.priority = 1 
            AND first_variant_image.status = '1'

        LEFT JOIN product_images first_variant_image_secondary ON first_variant.id = first_variant_image_secondary.product_details_id 
            AND first_variant_image_secondary.priority = 2 
            AND first_variant_image_secondary.status = '1'

        -- Variant Attributes
        LEFT JOIN (
            SELECT 
                pc.product_details_id,
                GROUP_CONCAT(DISTINCT CONCAT_WS(': ', pa.product_attribute, pad.attribute_detail) SEPARATOR '; ') AS variants
            FROM product_combination pc
            JOIN product_attributes pa ON pc.attribute_id = pa.id
            JOIN product_attributes_detail pad ON pc.attribute_detail_id = pad.id
            GROUP BY pc.product_details_id
        ) primary_variant_attrs ON primary_variant.id = primary_variant_attrs.product_details_id

        LEFT JOIN (
            SELECT 
                pc.product_details_id,
                GROUP_CONCAT(DISTINCT CONCAT_WS(': ', pa.product_attribute, pad.attribute_detail) SEPARATOR '; ') AS variants
            FROM product_combination pc
            JOIN product_attributes pa ON pc.attribute_id = pa.id
            JOIN product_attributes_detail pad ON pc.attribute_detail_id = pad.id
            GROUP BY pc.product_details_id
        ) first_variant_attrs ON first_variant.id = first_variant_attrs.product_details_id

        -- Product Badges (Enhanced but still simple)
        LEFT JOIN (
            SELECT 
                pb.product_id,
                COUNT(pb.badge_id) AS badges_count,
                GROUP_CONCAT(b.name ORDER BY pb.priority ASC SEPARATOR ', ') AS badges_names,
                GROUP_CONCAT(
                    CONCAT(
                        b.id, ':', 
                        b.name, ':', 
                        b.slug, ':', 
                        IFNULL(b.description, ''), ':', 
                        IFNULL(b.background_color, '#FF6B6B'), ':', 
                        IFNULL(b.text_color, '#FFFFFF'), ':', 
                        IFNULL(b.icon, ''), ':', 
                        pb.position, ':', 
                        pb.priority
                    )
                    ORDER BY pb.priority ASC
                    SEPARATOR '|'
                ) AS badges_data
            FROM product_badges pb
            INNER JOIN badges b ON pb.badge_id = b.id
            WHERE pb.is_active = 1 
                AND b.is_active = 1
                AND (pb.start_date IS NULL OR pb.start_date <= NOW())
                AND (pb.end_date IS NULL OR pb.end_date >= NOW())
            GROUP BY pb.product_id
        ) product_badges_data ON p.id_products = product_badges_data.product_id

        -- Additional joins
        LEFT JOIN (
            SELECT item_id, SUM(quantity) AS total_sales 
            FROM orders_detail 
            WHERE warehouse_id = 1 
            GROUP BY item_id
        ) total_sales ON COALESCE(primary_variant.id, first_variant.id) = total_sales.item_id

        LEFT JOIN (
            SELECT product_id, COUNT(id) AS total_reviews 
            FROM product_review 
            GROUP BY product_id
        ) total_reviews ON p.id_products = total_reviews.product_id

        LEFT JOIN resellers_price reseller_price ON COALESCE(primary_variant.id, first_variant.id) = reseller_price.product_detail_id

        LEFT JOIN brands b ON p.brand_id = b.id_brands

        INNER JOIN category_product cp ON p.id_products = cp.id_product

        WHERE p.product_status = '1'
            AND p.deleted_at IS NULL
        GROUP BY
            p.id_products
        ORDER BY 
            sort_order ASC,
            p.best_seller DESC,
            p.new_arrival DESC,
            p.popular_product DESC,
            p.priority DESC,
            COALESCE(b.priority, 0) DESC,
            p.created_at DESC
        ";
	}

	/**
	 * Optimized prepare_all_products method
	 */
	private function prepare_all_products_optimized($products)
	{
		$customer = $this->session->userdata('customer');
		$is_reseller = false;
		$reseller_price_map = [];

		// Get reseller information (existing code)
		if (!empty($customer['customer_id'])) {
			$customer_id = $customer['customer_id'];
			$customer_data = $this->db->where('id_customers', $customer_id)->get('customers')->row_array();
			if (!empty($customer_data['reseller_id'])) {
				$reseller = $this->db->where('id_resellers', $customer_data['reseller_id'])->get('resellers')->row_array();
				if ($reseller) {
					$is_reseller = true;
					$reseller_prices = $this->db->where('reseller_id', $reseller['id_resellers'])->get('resellers_price')->result_array();
					foreach ($reseller_prices as $price) {
						$reseller_price_map[$price['product_detail_id']] = $price['price'];
					}
				}
			}
		}

		$formatted_products = [];

		foreach ($products as $product) {
			// Price calculations
			$is_discounted = $product['discounted_price'] > 0;
			$default_price = $product['price'];
			$discounted_price = $product['discounted_price'];
			$current_price = $is_discounted ? $discounted_price : $default_price;
			$savings_amount = ($discounted_price > 0 && $discounted_price < $default_price)
				? ($default_price - $discounted_price)
				: 0;
			$msrp_price = null;

			// Handle reseller pricing
			if ($is_reseller && isset($reseller_price_map[$product['id_detail']])) {
				$msrp_price = $current_price;
				$current_price = $reseller_price_map[$product['id_detail']];
			}

			// Get reviews
			$review_data = $this->Review_m->get_product_reviews($product['id_products']);
			$average_rating = isset($review_data['average_rating']) ? round($review_data['average_rating'], 1) : 0;
			$total_reviews = isset($review_data['total_reviews']) ? $review_data['total_reviews'] : 0;

			// Parse variants
			$variants = !empty($product['variants']) ? explode('; ', $product['variants']) : [];

			$badges = $this->parse_badges_data($product['badges_data'] ?? '');
			$badges_names = $product['badges_names'] ?? '';

			// Stock calculations - all done in SQL now, just ensure non-negative values
			$current_variant_stock_sell = max(0, intval($product['current_variant_stock_sell'] ?? 0));
			$total_stock_sell = max(0, intval($product['total_stock_sell'] ?? 0));

			$formatted_products[] = [
				'id' => $product['id_products'],
				'title' => $product['title'],
				'id_detail' => $product['id_detail'],
				'alias' => $product['alias'],
				'sku' => $product['sku'],
				'current_price' => $current_price,
				'original_price' => $is_discounted ? $default_price : null,
				'msrp_price' => $msrp_price,
				'image' => $product['image'],
				'image_secondary' => $product['image_secondary'],
				'stock' => $product['total_stock'],
				'stock_sell' => $current_variant_stock_sell,
				'total_stock_sell' => $total_stock_sell,
				'average_rating' => $average_rating,
				'total_reviews' => $total_reviews,
				'variants' => $variants,
				'is_wishlisted' => $this->_check_wishlist_status($product['id_products'], $customer['customer_id'] ?? null),
				'created_at' => $product['created_at'],
				'brand_priority' => $product['brand_priority'],
				'savings_amount' => $savings_amount,
				'is_discounted' => $is_discounted,
				'discount_percentage' => $product['discount_percentage'],

				// Stock flags - now calculated in SQL
				'is_completely_sold_out' => (bool) $product['is_completely_sold_out'],
				'current_variant_out_of_stock' => (bool) $product['current_variant_out_of_stock'],
				'has_other_variants_available' => (bool) $product['has_other_variants_available'],

				// Additional stock information
				'total_variants' => (int) $product['total_variants'],
				'available_variants' => (int) $product['available_variants'],

				// Badges
				'badges' => $badges,
				'badges_names' => $badges_names, // Simple comma-separated names for quick display
				'badges_count' => (int) ($product['badges_count'] ?? 0),
				'has_badges' => !empty($badges),

				// New arrivals flag for badges
				'new_arrivals' => ($product['new_arrival'] === 'yes'),
				'best_seller' => ($product['best_seller'] === 'yes'),
				'popular_product' => ($product['popular_product'] === 'yes'),
			];
		}

		return $formatted_products;
	}

	private function parse_badges_data($badges_data)
	{
		$badges = [];

		if (empty($badges_data)) {
			return $badges;
		}

		$badges_raw = explode('|', $badges_data);

		foreach ($badges_raw as $badge_raw) {
			if (empty($badge_raw)) continue;

			$badge_parts = explode(':', $badge_raw);

			if (count($badge_parts) >= 9) {
				$badges[] = [
					'id' => (int) $badge_parts[0],
					'name' => $badge_parts[1],
					'slug' => $badge_parts[2],
					'description' => $badge_parts[3],
					'background_color' => !empty($badge_parts[4]) ? $badge_parts[4] : '#FF6B6B',
					'text_color' => !empty($badge_parts[5]) ? $badge_parts[5] : '#FFFFFF',
					'icon' => $badge_parts[6],
					'position' => $badge_parts[7],
					'priority' => (int) $badge_parts[8],

					// Additional computed properties
					'css_class' => 'badge-' . $badge_parts[2],
					'has_icon' => !empty($badge_parts[6]),
					'style' => sprintf(
						'background-color: %s; color: %s;',
						!empty($badge_parts[4]) ? $badge_parts[4] : '#FF6B6B',
						!empty($badge_parts[5]) ? $badge_parts[5] : '#FFFFFF'
					)
				];
			}
		}

		// Already sorted by priority in SQL, but double check
		usort($badges, function ($a, $b) {
			return $a['priority'] - $b['priority'];
		});

		return $badges;
	}

	private function _check_wishlist_status($product_id, $customer_id)
	{
		if (!$customer_id) return false;

		$exists = $this->db->where([
			'customer_id' => $customer_id,
			'product_id' => $product_id
		])->get('wishlists')->num_rows();

		return $exists > 0;
	}
	private function _check_wishlist_counts($product_id)
	{
		$count = $this->db->where([
			'product_id' => $product_id
		])
			->from('wishlists')
			->count_all_results();

		return $count;
	}


	public function getMaxPrice()
	{
		$this->db->select_max('price');
		$this->db->from('product_details');
		$result = $this->db->get()->row();
		return $result ? $result->price : 0;
	}
}