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

class Product_m extends MY_Model
{

	protected $_table_name = 'products';
	protected $_primary_key = 'id_products';
	protected $_order_by = 'id_products';
	private $website_product_ordering = NULL;

	function __construct()
	{
		parent::__construct();
		//get website product ordering
		$this->db->select('website_product_ordering')->from('configuration')->where('id_configuration', 1);
		$this->website_product_ordering = $this->db->get()->row()->website_product_ordering;
	}

	public $rules_special_discount = array(

		array(
			'field'   => 'title',
			'label'   => 'Promo Name',
			'rules'   => 'trim|required'
		),
		array(
			'field'   => 'discount_value',
			'label'   => 'discount value',
			'rules'   => 'trim|required|numeric'
		),
		array(
			'field'   => 'promostart',
			'label'   => 'Promo Start',
			'rules'   => 'trim'
		),
		array(
			'field'   => 'promoend',
			'label'   => 'Promo End',
			'rules'   => 'trim'
		),
	);

	public $rules_bundle = array(

		array(
			'field'   => 'title',
			'label'   => 'title',
			'rules'   => 'trim|required'
		),
		array(
			'field'   => 'price',
			'label'   => 'price',
			'rules'   => 'trim|required|numeric'
		),
	);

	function add_special_discount($data)
	{
		$this->db->insert('special_discount', $data);
		return $this->db->insert_id();
	}

	function add_bundle($data)
	{
		$this->db->insert('bundle', $data);
		return $this->db->insert_id();
	}

	function edit_bundle($id, $data)
	{
		$this->db->where('id', $id);
		$this->db->update('bundle', $data);
	}

	function edit_special_discount($id, $data)
	{
		$this->db->where('id_discount', $id);
		$this->db->update('special_discount', $data);
	}

	public $rules = array(
		array(
			'field'   => 'product_name',
			'label'   => 'Product Name',
			'rules'   => 'trim|required'
		),
		array(
			'field'   => 'brand_id',
			'label'   => 'Brand ID',
			'rules'   => 'trim|required'
		),
		array(
			'field'   => 'shipment_id[]',
			'label'   => 'Shipment ID',
			'rules'   => 'required'
		),
		array(
			'field'   => 'category_id[]',
			'label'   => 'Category ID',
			'rules'   => 'required'
		),
		array(
			'field'   => 'indent_dp',
			'label'   => 'DP untuk Indent',
			'rules'   => 'numeric'
		),
	);

	//ADMIN WEBSITE
	//pagination included
	function get_all_products($limit, $start)
	{
		$this->db->select('p.*');

		$this->db->select("(SELECT image FROM product_images pi 
                        WHERE pi.product_id = p.id_products 
                        AND pi.status = 1 
                        AND pi.priority = 1 
                        ORDER BY pi.image DESC LIMIT 1) AS product_image");

		$this->db->from('products p');

		$this->db->order_by('p.product_status', 'ASC');
		$this->db->order_by('p.title', 'ASC');

		$this->db->limit($limit, $start);

		$query = $this->db->get();

		return $query->result();
	}

	//pagination included
	function get_all_products_search_product($keyword)
	{
		$this->db->select('p.*');

		$this->db->select("(SELECT image FROM product_images pi 
                        WHERE pi.product_id = p.id_products 
                        AND pi.status = 1 
                        AND pi.priority = 1 
                        ORDER BY pi.image DESC LIMIT 1) AS product_image");

		$this->db->from('products p');

		$this->db->like('p.title', $keyword);

		$this->db->order_by('p.created_at', 'DESC');

		$query = $this->db->get();

		return $query->result();
	}

	//function count all record for category 
	public function record_count()
	{
		return $this->db->get('products')->num_rows();
	}

	public function record_count_special_discount()
	{
		return $this->db->get('special_discount')->num_rows();
	}

	public function record_count_bundle()
	{
		return $this->db->get('bundle')->num_rows();
	}


	//function count all record for search product
	public function record_count_search_product($keyword)
	{
		$this->db->select('*');
		$this->db->from('products');
		$this->db->like('products.title', $keyword);
		$query = $this->db->get();
		return $query->num_rows();
	}

	//function to display new product, where all fields are empty
	public function get_new()
	{
		$products = new stdClass();
		$products->id_products = '';
		$products->title = '';
		$products->product_status = '';
		$products->description = '';
		$products->description_en = '';
		$products->long_description = '';
		$products->long_description_en = '';
		$products->additional_information = '';
		$products->additional_information_en = '';
		$products->new_arrival = '';
		$products->best_seller = '';
		$products->restock = '';
		$products->customer_id_exc = '';
		$products->popular_product = '';
		$products->meta_description = '';
		$products->meta_title = '';
		$products->priority = '';
		$products->indent_dp = 50;
		$products->indent_time = '';
		return $products;
	}

	//function add new product
	function add_product($data)
	{
		$user_id = $this->session->userdata('admin')['id'];
		$activity = 'User menambah produk(' . $data['title'] . ')';

		log_activity($user_id, $activity);
		$this->db->insert('products', $data);
		return $this->db->insert_id();
	}

	//function edit product
	function edit_product($id, $data)
	{
		$this->db->where('id_products', $id);
		$this->db->update('products', $data);

		$user_id = $this->session->userdata('admin')['id'];
		$activity = 'User mengedit produk(' . $data['title'] . ')';

		log_activity($user_id, $activity);
	}

	//update orphan products to remove category
	function update_product_category($id, $data)
	{
		$this->db->where('category_id', $id);
		$this->db->update('products', $data);
	}

	//update orphan products to remove brand
	function update_product_brand($id, $data)
	{
		$this->db->where('brand_id', $id);
		$this->db->update('products', $data);
	}

	//select chosen product at frontend
	function selected_product($chosen_product_id)
	{
		$this->db->select('*');
		$this->db->from('products');
		$this->db->where('id_products', $chosen_product_id);
		$query = $this->db->get();
		return $query->row();
	}

	//update product stock in database after deduct from purchased stock
	function update_product_stock($id, $stock_data)
	{
		$this->db->where('id_products', $id);
		$this->db->update('products', $stock_data);
	}

	//Public Page 
	public function get_products()
	{
		$this->db->select('*');
		$this->db->from('products');
		$this->db->join('categories', 'categories.id_categories = products.category_id');
		$this->db->order_by('category', 'asc');
		$query = $this->db->get();
		return $query->result();
	}
	public function get_active_product()
	{
		$this->db->select('*');
		$this->db->from('products');
		$this->db->where('product_status', 1);
		$this->db->order_by('id_products', 'asc');
		$query = $this->db->get();
		return $query->result();
	}

	public function get_product()
	{
		$this->db->select('*');
		$this->db->from('products');
		$this->db->order_by('id_products', 'asc');
		$query = $this->db->get();
		return $query->result();
	}

	//Public Page 
	public function get_special_discount()
	{
		$this->db->select('*');
		$this->db->from('special_discount');
		$query = $this->db->get();
		return $query->result();
	}

	public function get_bundle()
	{
		$this->db->select('*');
		$this->db->from('bundle');
		$query = $this->db->get();
		return $query->result();
	}

	//Public Page 
	public function get_product_by_id($id)
	{
		$this->db->select('*');
		$this->db->from('products');
		$this->db->join('categories', 'categories.id_categories = products.category_id');
		$this->db->where('id_products', $id);
		$query = $this->db->get();
		//check if product id already exist in database, to prevent display error
		if ($query->num_rows() > 0) {
			return $query->row();
		} else {
			show_404();
		}
	}
	//Public Page cart
	public function get_image($product_id)
	{
		$this->db->select('image');
		$this->db->from('products');
		$this->db->where('id_products', $product_id);
		$query = $this->db->get();
		return $query->row();
	}
	//get current stock
	public function get_current_stock($product_id)
	{
		$this->db->select('stock');
		$this->db->from('products');
		$this->db->where('id_products', $product_id);
		$query = $this->db->get();
		return $query->row();
	}
	//IS USED: get products by category id
	public function get_products_by_category($category_id, $limit, $start)
	{
		$this->db->select('id_products, brand_id, title, alias');
		$this->db->from('products');
		$this->db->join('product_details', 'products.id_products = product_details.product_id');
		$this->db->join('category_product', 'products.id_products = category_product.id_product');
		$this->db->join('stock', 'products.id_products = stock.id_product', 'left');
		$this->db->where('product_status', 1);
		$this->db->where('category_product.id_category', $category_id);
		$this->db->where('product_status', '1');

		$customer_id = $this->session->userdata('customer')['customer_id'];

		// Check if customer_id_exc is not empty and matches the customer_id from session
		$this->db->where('(
        products.customer_id_exc IS NULL
        OR products.customer_id_exc = "" 
        OR products.customer_id_exc = ' . $this->db->escape($customer_id) . '
    )');

		if ($category_id == 44) {
			$fortyFiveDaysAgo = date('Y-m-d', strtotime('-75 days'));

			$this->db->where('products.created_at >=', $fortyFiveDaysAgo);
			$this->db->order_by('products.created_at', 'DESC');
		} else {
			$this->db->order_by('
            CASE 
                WHEN (stock.stock - stock.stock_keep) <= 0 THEN 1
                ELSE 0
            END, 
            (stock.stock - stock.stock_keep) DESC
        ');

			if ($this->session->userdata('sort_product')) {
				switch ($this->session->userdata('sort_product')) {
					case 'price-asc':
						$this->db->order_by('product_details.discounted_price', 'ASC');
						break;
					case 'price-desc':
						$this->db->order_by('product_details.discounted_price', 'DESC');
						break;
					case 'name-asc':
						$this->db->order_by('products.title', 'ASC');
						break;
					case 'name-desc':
						$this->db->order_by('products.title', 'DESC');
						break;
					case 'created-date':
						$this->db->order_by('products.created_at', 'DESC');
						break;
				}
			} else {
				switch ($this->website_product_ordering) {
					case 'random':
						$this->db->order_by('products.title', 'RANDOM');
						break;
					case 'manual-order':
						$this->db->order_by('products.priority', 'ASC');
						break;
					case 'input-date-desc':
						$this->db->order_by('products.created_at', 'DESC');
						break;
					case 'input-date-asc':
						$this->db->order_by('products.created_at', 'ASC');
						break;
				}
			}
		}

		$this->db->group_by('product_details.product_id');
		$this->db->limit($limit, $start);
		$query = $this->db->get();
		return $query->result();
	}

	//IS USED: count products by category
	public function count_products_by_category($category_id)
	{
		$this->db->select('*');
		$this->db->from('products');
		$this->db->join('category_product', 'products.id_products = category_product.id_product');
		$this->db->where('category_product.id_category', $category_id);
		$this->db->where('products.product_status', '1');
		$query = $this->db->get();
		return $query->num_rows();
	}
	//IS USED: get products by brand id
	public function get_products_by_brand($brand_id, $limit, $start, $sort_product_by)
	{
		$customer_id = $this->session->userdata('customer')['customer_id'];
		$this->db->select('*');
		$this->db->from('products');
		$this->db->where('brand_id', $brand_id);
		$this->db->where('product_status', 1);

		// Check if customer_id_exc is not empty and matches the customer_id from session
		$this->db->where('(
        products.customer_id_exc IS NULL
        OR products.customer_id_exc = ""
        OR products.customer_id_exc = ' . $this->db->escape($customer_id) . '
    )');

		if ($sort_product_by != NULL) {
			switch ($sort_product_by) {
				case 'price-asc':
					$this->db->order_by('sale_price', 'ASC');
					break;
				case 'price-desc':
					$this->db->order_by('sale_price', 'DESC');
					break;
				case 'name-asc':
					$this->db->order_by('title', 'ASC');
					break;
				case 'name-desc':
					$this->db->order_by('title', 'DESC');
					break;
			}
		} else {
			switch ($this->website_product_ordering) {
				case 'random':
					$this->db->order_by('title', 'RANDOM');
					break;
				case 'manual-order':
					$this->db->order_by('priority', 'ASC');
					break;
				case 'input-date-desc':
					$this->db->order_by('created_at', 'DESC');
					break;
				case 'input-date-asc':
					$this->db->order_by('created_at', 'ASC');
					break;
			}
		}
		$this->db->limit($limit, $start);
		$query = $this->db->get();
		return $query->result();
	}

	//IS USED: count products by brand id
	public function count_products_by_brand($brand_id)
	{
		$this->db->select('*');
		$this->db->from('products');
		$this->db->where('brand_id', $brand_id);
		$this->db->where('product_status', 1);
		$query = $this->db->get();
		return $query->num_rows();
	}
	//get products by promotion id
	public function get_products_by_promotion($promotion_id, $limit, $start, $sort_product_by)
	{
		$customer_id = $this->session->userdata('customer')['customer_id'];
		$this->db->select('*');
		$this->db->from('products');
		$this->db->join('promotion_product', 'promotion_product.id_product = products.id_products');
		$this->db->where('promotion_product.id_promotion', $promotion_id);
		$this->db->where('product_status', 1);

		// Check if customer_id_exc is not empty and matches the customer_id from session
		$this->db->where('(
        products.customer_id_exc IS NULL
        OR products.customer_id_exc = ""
        OR products.customer_id_exc = ' . $this->db->escape($customer_id) . '
    )');

		if ($sort_product_by != NULL) {
			switch ($sort_product_by) {
				case 'price-asc':
					$this->db->order_by('price', 'ASC');
					break;
				case 'price-desc':
					$this->db->order_by('price', 'DESC');
					break;
				case 'name-asc':
					$this->db->order_by('title', 'ASC');
					break;
				case 'name-desc':
					$this->db->order_by('title', 'DESC');
					break;
			}
		} else {
			switch ($this->website_product_ordering) {
				case 'random':
					$this->db->order_by('products.title', 'RANDOM');
					break;
				case 'manual-order':
					$this->db->order_by('products.priority', 'ASC');
					break;
				case 'input-date-desc':
					$this->db->order_by('products.created_at', 'DESC');
					break;
				case 'input-date-asc':
					$this->db->order_by('products.created_at', 'ASC');
					break;
			}
		}
		$this->db->limit($limit, $start);
		$query = $this->db->get();
		return $query->result();
	}

	//count products by promotion id
	public function count_products_by_promotion($promotion_id)
	{
		$this->db->select('*');
		$this->db->from('products');
		$this->db->where('promotion_id', $promotion_id);
		$this->db->where('product_status', 1);
		$query = $this->db->get();
		return $query->num_rows();
	}
	//IS USED: get products by new arrival
	public function get_products_new_arrival($limit, $start)
	{
		$customer_id = $this->session->userdata('customer')['customer_id'];
		$this->db->select(
			'products.*'
		);
		$this->db->from('products');
		$this->db->join('product_details', 'product_details.product_id = products.id_products');
		$this->db->where('products.new_arrival', 'yes');
		$this->db->where('products.product_status', 1);

		// Check if customer_id_exc is not empty and matches the customer_id from session
		$this->db->where('(
        products.customer_id_exc IS NULL
        OR products.customer_id_exc = ""
        OR products.customer_id_exc = ' . $this->db->escape($customer_id) . '
    )');

		$this->db->group_by('product_details.product_id');
		if ($this->session->userdata('sort_product')) {
			switch ($this->session->userdata('sort_product')) {
				case 'price-asc':
					$this->db->order_by('product_details.discounted_price', 'ASC');
					break;
				case 'price-desc':
					$this->db->order_by('product_details.discounted_price', 'DESC');
					break;
				case 'name-asc':
					$this->db->order_by('products.title', 'ASC');
					break;
				case 'name-desc':
					$this->db->order_by('products.title', 'DESC');
					break;
				case 'created-at':
					$this->db->order_by('products.created_at', 'DESC');
					break;
			}
		} else {
			switch ($this->website_product_ordering) {
				case 'random':
					$this->db->order_by('products.title', 'RANDOM');
					break;
				case 'manual-order':
					$this->db->order_by('products.priority', 'ASC');
					break;
				case 'input-date-desc':
					$this->db->order_by('products.created_at', 'DESC');
					break;
				case 'input-date-asc':
					$this->db->order_by('products.created_at', 'ASC');
					break;
			}
		}
		$this->db->limit($limit, $start);
		$query = $this->db->get();
		return $query->result();
	}

	//IS USED: count products by new arrival
	public function count_products_new_arrival()
	{
		$this->db->select('*');
		$this->db->from('products');
		$this->db->where('new_arrival', 'yes');
		$this->db->where('product_status', 1);
		$query = $this->db->get();
		return $query->num_rows();
	}
	//IS USED: get products by new arrival
	public function get_products_all($limit, $start)
	{
		$customer_id = $this->session->userdata('customer')['customer_id'];
		$this->db->select('products.id_products, products.brand_id, products.title, products.alias');
		$this->db->from('products');
		$this->db->join('product_details', 'product_details.product_id = products.id_products');
		$this->db->where('product_status', '1');
		$this->db->group_by('product_details.product_id');
		// Check if customer_id_exc is not empty and matches the customer_id from session
		$this->db->where('(
			products.customer_id_exc IS NULL
			OR products.customer_id_exc = ""
			OR products.customer_id_exc = ' . $this->db->escape($customer_id) . '
		)');
		if ($this->session->userdata('sort_product')) {
			switch ($this->session->userdata('sort_product')) {
				case 'price-asc':
					$this->db->order_by('product_details.discounted_price', 'ASC');
					break;
				case 'price-desc':
					$this->db->order_by('product_details.discounted_price', 'DESC');
					break;
				case 'name-asc':
					$this->db->order_by('products.title', 'ASC');
					break;
				case 'name-desc':
					$this->db->order_by('products.title', 'DESC');
					break;
				case 'created-at':
					$this->db->order_by('products.created_at', 'DESC');
					break;
			}
		} else {
			switch ($this->website_product_ordering) {
				case 'random':
					$this->db->order_by('products.title', 'RANDOM');
					break;
				case 'manual-order':
					$this->db->order_by('products.priority', 'ASC');
					break;
				case 'input-date-desc':
					$this->db->order_by('products.created_at', 'DESC');
					break;
				case 'input-date-asc':
					$this->db->order_by('products.created_at', 'ASC');
					break;
			}
		}
		$this->db->limit($limit, $start);
		$query = $this->db->get();
		return $query->result();
	}
	//IS USED: count products by all
	public function count_products_all()
	{
		$this->db->select('*');
		$this->db->from('products');
		$this->db->where('product_status', 1);
		$query = $this->db->get();
		return $query->num_rows();
	}
	//IS USED: get products by bestseller
	public function get_products_bestseller($limit, $start)
	{
		$customer_id = $this->session->userdata('customer')['customer_id'];
		$this->db->select(
			'products.*'
		);
		$this->db->from('products');
		$this->db->join('product_details', 'product_details.product_id = products.id_products');
		$this->db->where('products.best_seller', 'yes');
		$this->db->where('products.product_status', 1);
		$this->db->group_by('product_details.product_id');
		// Check if customer_id_exc is not empty and matches the customer_id from session
		$this->db->where('(
			products.customer_id_exc IS NULL
			OR products.customer_id_exc = ""
			OR products.customer_id_exc = ' . $this->db->escape($customer_id) . '
		)');
		if ($this->session->userdata('sort_product')) {
			switch ($this->session->userdata('sort_product')) {
				case 'price-asc':
					$this->db->order_by('product_details.discounted_price', 'ASC');
					break;
				case 'price-desc':
					$this->db->order_by('product_details.discounted_price', 'DESC');
					break;
				case 'name-asc':
					$this->db->order_by('products.title', 'ASC');
					break;
				case 'name-desc':
					$this->db->order_by('products.title', 'DESC');
					break;
				case 'created-at':
					$this->db->order_by('products.created_at', 'DESC');
					break;
			}
		} else {
			switch ($this->website_product_ordering) {
				case 'random':
					$this->db->order_by('products.title', 'RANDOM');
					break;
				case 'manual-order':
					$this->db->order_by('products.priority', 'ASC');
					break;
				case 'input-date-desc':
					$this->db->order_by('products.created_at', 'DESC');
					break;
				case 'input-date-asc':
					$this->db->order_by('products.created_at', 'ASC');
					break;
			}
		}
		$this->db->limit($limit, $start);
		$query = $this->db->get();
		return $query->result();
	}
	//IS USED: count products by bestseller
	public function count_products_bestseller()
	{
		$this->db->select('*');
		$this->db->from('products');
		$this->db->where('best_Seller', 'yes');
		$this->db->where('product_status', 1);
		$query = $this->db->get();
		return $query->num_rows();
	}
	//count products by new arrival
	public function count_parent_products_new_arrival($parent_category)
	{
		//get parent id category
		$this->db->select('id_categories')->from('categories')->where('alias', $parent_category)->where('parent' . NULL);
		$parent_id_category = $this->db->get()->row()->id_categories;
		$this->db->select('*');
		$this->db->from('products');
		$this->db->join('category_product', 'category_product.id_product = products.id_products');
		$this->db->where('new_arrival', 'yes');
		$this->db->where('category_product.id_category', $parent_id_category);
		$this->db->where('product_status', 1);
		$query = $this->db->get();
		return $query->num_rows();
	}
	//get products by new arrival
	public function get_parent_products_new_arrival($limit, $start, $sort_product_by, $parent_category)
	{
		$customer_id = $this->session->userdata('customer')['customer_id'];
		//get parent id category
		$this->db->select('id_categories')->from('categories')->where('alias', $parent_category)->where('parent' . NULL);
		$parent_id_category = $this->db->get()->row()->id_categories;
		$this->db->select('*');
		$this->db->from('products');
		$this->db->join('category_product', 'category_product.id_product = products.id_products');
		$this->db->where('new_arrival', 'yes');
		$this->db->where('category_product.id_category', $parent_id_category);
		$this->db->where('product_status', 1);
		// Check if customer_id_exc is not empty and matches the customer_id from session
		$this->db->where('(
			products.customer_id_exc IS NULL
			OR products.customer_id_exc = ""
			OR products.customer_id_exc = ' . $this->db->escape($customer_id) . '
		)');
		if ($sort_product_by != NULL) {
			switch ($sort_product_by) {
				case 'price-asc':
					$this->db->order_by('price', 'ASC');
					break;
				case 'price-desc':
					$this->db->order_by('price', 'DESC');
					break;
				case 'name-asc':
					$this->db->order_by('title', 'ASC');
					break;
				case 'name-desc':
					$this->db->order_by('title', 'DESC');
					break;
			}
		} else {
			switch ($this->website_product_ordering) {
				case 'random':
					$this->db->order_by('products.title', 'RANDOM');
					break;
				case 'manual-order':
					$this->db->order_by('products.priority', 'ASC');
					break;
				case 'input-date-desc':
					$this->db->order_by('products.created_at', 'DESC');
					break;
				case 'input-date-asc':
					$this->db->order_by('products.created_at', 'ASC');
					break;
			}
		}
		$this->db->limit($limit, $start);
		$query = $this->db->get();
		return $query->result();
	}
	//IS USED: get products by sale item
	public function get_products_sale($limit, $start)
	{
		$customer_id = $this->session->userdata('customer')['customer_id'];
		$this->db->select('products.id_products, products.brand_id, products.title, products.alias');
		$this->db->from('products');
		$this->db->join('product_details', 'product_details.product_id = products.id_products');
		$this->db->where('products.product_status', 1);
		$this->db->where('product_details.discounted_price !=', 0);
		$this->db->group_by('product_details.product_id');
		// Check if customer_id_exc is not empty and matches the customer_id from session
		$this->db->where('(
			products.customer_id_exc IS NULL
			OR products.customer_id_exc = ""
			OR products.customer_id_exc = ' . $this->db->escape($customer_id) . '
		)');
		if ($this->session->userdata('sort_product')) {
			switch ($this->session->userdata('sort_product')) {
				case 'price-asc':
					$this->db->order_by('product_details.discounted_price', 'ASC');
					break;
				case 'price-desc':
					$this->db->order_by('product_details.discounted_price', 'DESC');
					break;
				case 'name-asc':
					$this->db->order_by('products.title', 'ASC');
					break;
				case 'name-desc':
					$this->db->order_by('products.title', 'DESC');
					break;
				case 'created-at':
					$this->db->order_by('products.created_at', 'DESC');
					break;
			}
		} else {
			switch ($this->website_product_ordering) {
				case 'random':
					$this->db->order_by('products.title', 'RANDOM');
					break;
				case 'manual-order':
					$this->db->order_by('products.priority', 'ASC');
					break;
				case 'input-date-desc':
					$this->db->order_by('products.created_at', 'DESC');
					break;
				case 'input-date-asc':
					$this->db->order_by('products.created_at', 'ASC');
					break;
			}
		}
		$this->db->limit($limit, $start);
		$query = $this->db->get();
		return $query->result();
	}
	//IS USED: count products by sale item
	public function count_products_sale()
	{
		$this->db->distinct('product_details.product_id');
		$this->db->select('products.id_products');
		$this->db->from('products');
		$this->db->join('product_details', 'product_details.product_id = products.id_products');
		$this->db->where('products.product_status', 1);
		$this->db->where('product_details.discounted_price !=', 0);
		$query = $this->db->get();
		return $query->num_rows();
	}
	//IS USED: count products by search
	public function count_products_by_search($keyword)
	{
		/* $this->db->select('*');
		$this->db->from('products'); 
		$this->db->join('brands', 'products.brand_id = brands.id_brands');
		$this->db->where('products.product_status', '1');
		$this->db->like('products.title', $keyword);
		$this->db->or_like('products.categories', $keyword);
		$this->db->or_like('brands.brand', $keyword); */
		$this->db->select('*');
		$this->db->from('products');
		$this->db->where('product_status', '1');
		$this->db->group_start();
		$this->db->like('title', $keyword);
		// $this->db->or_like('categories', $keyword);
		// $this->db->or_like('meta_description', $keyword);
		// $this->db->or_like('meta_keywords', $keyword);
		$this->db->group_end();
		$query = $this->db->get();
		return $query->num_rows();
	}
	//IS USED: get products by search
	public function get_products_by_search($keyword, $limit, $start)
	{
		$customer_id = $this->session->userdata('customer')['customer_id'];
		//get products 
		$this->db->select('*');
		$this->db->from('products');
		$this->db->where('product_status', '1');
		$this->db->group_start();
		$this->db->like('title', $keyword);
		// $this->db->or_like('categories', $keyword);
		// $this->db->or_like('meta_description', $keyword);
		// $this->db->or_like('meta_keywords', $keyword);
		$this->db->group_end();
		// Check if customer_id_exc is not empty and matches the customer_id from session
		$this->db->where('(
			products.customer_id_exc IS NULL
			OR products.customer_id_exc = ""
			OR products.customer_id_exc = ' . $this->db->escape($customer_id) . '
		)');
		if ($this->session->userdata('sort_product')) {
			switch ($this->session->userdata('sort_product')) {
				case 'price-asc':
					$this->db->order_by('sale_price', 'ASC');
					break;
				case 'price-desc':
					$this->db->order_by('sale_price', 'DESC');
					break;
				case 'name-asc':
					$this->db->order_by('title', 'ASC');
					break;
				case 'name-desc':
					$this->db->order_by('title', 'DESC');
					break;
				case 'created-at':
					$this->db->order_by('created_at', 'DESC');
					break;
			}
		} else {
			switch ($this->website_product_ordering) {
				case 'random':
					$this->db->order_by('title', 'RANDOM');
					break;
				case 'manual-order':
					$this->db->order_by('priority', 'ASC');
					break;
				case 'input-date-desc':
					$this->db->order_by('created_at', 'DESC');
					break;
				case 'input-date-asc':
					$this->db->order_by('created_at', 'ASC');
					break;
			}
		}
		$this->db->limit($limit, $start);
		$query = $this->db->get();
		return $query->result();
	}
	//cek if product title already exist
	public function cek_existing_product_code($str, $product_current_id)
	{
		$this->db->select('id_products');
		$this->db->from('products');
		$this->db->where('product_code', $str);
		if ($product_current_id != NULL) {
			$this->db->where('id_products !=', $product_current_id);
		}
		$query = $this->db->get();
		return $query->num_rows();
	}
	//function count if existing record exist
	public function count_exist($id)
	{
		$this->db->select('*');
		$this->db->from('products');
		$this->db->where('id_products', $id);
		$query = $this->db->get();
		return $query->num_rows();
	}

	public function get_new_special_discount()
	{
		$special_discount = new stdClass();
		$special_discount->title = '';
		$special_discount->type = '';
		$special_discount->value = '';
		$special_discount->time_start = '';
		$special_discount->time_end = '';
		$special_discount->status = '';
		$special_discount->brand_id = '';
		$special_discount->category_id = '';
		$special_discount->sub_category_id = '';
		return $special_discount;
	}

	public function get_new_bundle()
	{
		$bundle = new stdClass();
		$bundle->title = '';
		$bundle->status = '';
		$bundle->selled = '';
		$bundle->stock = '';
		$bundle->product_id = '';
		$bundle->price = '';
		$bundle->image = '';
		return $bundle;
	}

	public function count_exist_special_discount($id)
	{
		$this->db->select('*');
		$this->db->from('special_discount');
		$this->db->where('id_discount', $id);
		$query = $this->db->get();
		return $query->num_rows();
	}

	public function count_exist_bundle($id)
	{
		$this->db->select('*');
		$this->db->from('bundle');
		$this->db->where('id', $id);
		$query = $this->db->get();
		return $query->num_rows();
	}

	public function get_special_discount_1($id)
	{
		$this->db->select('*');
		$this->db->from('special_discount');
		$this->db->where('id_discount', $id);
		$query = $this->db->get();
		return $query->row();
	}

	public function get_bundle_1($id)
	{
		$this->db->select('*');
		$this->db->from('bundle');
		$this->db->where('id', $id);
		$query = $this->db->get();
		return $query->row();
	}

	//IS USED: count products by bestseller
	public function count_products_topoffer()
	{
		$this->db->select('*');
		$this->db->from('products');
		$this->db->where('popular_product', 'yes');
		$this->db->where('product_status', 1);
		$query = $this->db->get();
		return $query->num_rows();
	}
	//IS USED: get products by bestseller
	public function get_products_topoffer($limit, $start)
	{
		$customer_id = $this->session->userdata('customer')['customer_id'];
		$this->db->select(
			'products.*'
		);
		$this->db->from('products');
		$this->db->join('product_details', 'product_details.product_id = products.id_products');
		$this->db->where('products.popular_product', 'yes');
		$this->db->where('products.product_status', 1);
		$this->db->group_by('product_details.product_id');
		// Check if customer_id_exc is not empty and matches the customer_id from session
		$this->db->where('(
			products.customer_id_exc IS NULL
			OR products.customer_id_exc = ""
			OR products.customer_id_exc = ' . $this->db->escape($customer_id) . '
		)');
		if ($this->session->userdata('sort_product')) {
			switch ($this->session->userdata('sort_product')) {
				case 'price-asc':
					$this->db->order_by('product_details.discounted_price', 'ASC');
					break;
				case 'price-desc':
					$this->db->order_by('product_details.discounted_price', 'DESC');
					break;
				case 'name-asc':
					$this->db->order_by('products.title', 'ASC');
					break;
				case 'name-desc':
					$this->db->order_by('products.title', 'DESC');
					break;
				case 'created-at':
					$this->db->order_by('products.created_at', 'DESC');
					break;
			}
		} else {
			switch ($this->website_product_ordering) {
				case 'random':
					$this->db->order_by('products.title', 'RANDOM');
					break;
				case 'manual-order':
					$this->db->order_by('products.priority', 'ASC');
					break;
				case 'input-date-desc':
					$this->db->order_by('products.created_at', 'DESC');
					break;
				case 'input-date-asc':
					$this->db->order_by('products.created_at', 'ASC');
					break;
			}
		}
		$this->db->limit($limit, $start);
		$query = $this->db->get();
		return $query->result();
	}
	//IS USED: count products by bestseller
	public function count_products_flashsale($id)
	{
		$this->db->select('*');
		$this->db->from('flashsale_products');
		$this->db->join('products', 'products.id_products = flashsale_products.product_id');
		$this->db->where('flashsale_products.flashsale_id', $id);
		$this->db->where('products.product_status', 1);
		$this->db->order_by('flashsale_products.product_id', 'RANDOM');
		$query = $this->db->get();
		return $query->num_rows();
	}
	//IS USED: get products by bestseller
	public function get_products_flashsale($limit, $start, $id)
	{
		$customer_id = $this->session->userdata('customer')['customer_id'];
		$this->db->select('
			flashsale_products.discounted_price,
			flashsale_products.product_details_id,
            products.alias as alias, 
            products.image as image,
            products.title as title, 
            products.id_products as id_products, 
            products.sale_price
        ');
		// products.is_sale
		$this->db->from('flashsale_products');
		$this->db->join('products', 'products.id_products = flashsale_products.product_id');
		$this->db->where('flashsale_products.flashsale_id', $id);
		$this->db->where('products.product_status', 1);
		// Check if customer_id_exc is not empty and matches the customer_id from session
		$this->db->where('(
			products.customer_id_exc IS NULL
			OR products.customer_id_exc = ""
			OR products.customer_id_exc = ' . $this->db->escape($customer_id) . '
		)');
		if ($this->session->userdata('sort_product')) {
			switch ($this->session->userdata('sort_product')) {
				case 'price-asc':
					$this->db->order_by('flashsale_products.discounted_price', 'ASC');
					break;
				case 'price-desc':
					$this->db->order_by('flashsale_products.discounted_price', 'DESC');
					break;
				case 'name-asc':
					$this->db->order_by('products.title', 'ASC');
					break;
				case 'name-desc':
					$this->db->order_by('products.title', 'DESC');
					break;
				case 'created-at':
					$this->db->order_by('products.created_at', 'DESC');
					break;
			}
		} else {
			switch ($this->website_product_ordering) {
				case 'random':
					$this->db->order_by('products.title', 'RANDOM');
					break;
				case 'manual-order':
					$this->db->order_by('products.priority', 'ASC');
					break;
				case 'input-date-desc':
					$this->db->order_by('products.created_at', 'DESC');
					break;
				case 'input-date-asc':
					$this->db->order_by('products.created_at', 'ASC');
					break;
			}
		}
		$this->db->limit($limit, $start);
		$query = $this->db->get();
		return $query->result();
	}
	//IS USED: count products by bestseller
	public function count_products_flashsale_next($id)
	{
		$this->db->select('*');
		$this->db->from('flashsale_products');
		$this->db->join('products', 'products.id_products = flashsale_products.product_id');
		$this->db->where('flashsale_products.flashsale_id', $id);
		$this->db->where('products.product_status', 1);
		$this->db->order_by('flashsale_products.product_id', 'RANDOM');
		$query = $this->db->get();
		return $query->num_rows();
	}
	//IS USED: get products by bestseller
	public function get_products_flashsale_next($limit, $start, $id)
	{
		$customer_id = $this->session->userdata('customer')['customer_id'];
		$this->db->select('
			flashsale_products.discounted_price,
            flashsale_products.product_details_id,
            products.alias as alias, 
            products.image as image,
            products.title as title, 
            products.id_products as id_products, 
            products.sale_price
        ');
		// products.is_sale
		$this->db->from('flashsale_products');
		$this->db->join('products', 'products.id_products = flashsale_products.product_id');
		$this->db->where('flashsale_products.flashsale_id', $id);
		$this->db->where('products.product_status', 1);
		// Check if customer_id_exc is not empty and matches the customer_id from session
		$this->db->where('(
			products.customer_id_exc IS NULL
			OR products.customer_id_exc = ""
			OR products.customer_id_exc = ' . $this->db->escape($customer_id) . '
		)');
		if ($this->session->userdata('sort_product')) {
			switch ($this->session->userdata('sort_product')) {
				case 'price-asc':
					$this->db->order_by('flashsale_products.discounted_price', 'ASC');
					break;
				case 'price-desc':
					$this->db->order_by('flashsale_products.discounted_price', 'DESC');
					break;
				case 'name-asc':
					$this->db->order_by('products.title', 'ASC');
					break;
				case 'name-desc':
					$this->db->order_by('products.title', 'DESC');
					break;
				case 'created-at':
					$this->db->order_by('products.created_at', 'DESC');
					break;
			}
		} else {
			switch ($this->website_product_ordering) {
				case 'random':
					$this->db->order_by('products.title', 'RANDOM');
					break;
				case 'manual-order':
					$this->db->order_by('products.priority', 'ASC');
					break;
				case 'input-date-desc':
					$this->db->order_by('products.created_at', 'DESC');
					break;
				case 'input-date-asc':
					$this->db->order_by('products.created_at', 'ASC');
					break;
			}
		}
		$this->db->limit($limit, $start);
		$query = $this->db->get();
		return $query->result();
	}
	public function get_products_by_designer($designer_id, $limit, $start)
	{
		$this->db->select('*');
		$this->db->from('products');
		$this->db->where('designer_id', $designer_id);
		$this->db->where('product_status', 1);
		$this->db->limit($limit, $start);
		$query = $this->db->get();
		return $query->result();
	}
	public function get_products_by_price($price, $limit, $start)
	{
		$this->db->select('*');
		$this->db->from('product_details');
		$this->db->join('products', 'products.id_products=product_details.product_id');
		if ($price == 1) {
			$this->db->where('price <= 1000000');
		} else if ($price == 2) {
			$this->db->where('price BETWEEN 1000000 AND 10000000');
		} else if ($price == 3) {
			$this->db->where('price BETWEEN 10000000 AND 25000000');
		} else if ($price == 4) {
			$this->db->where('price BETWEEN 25000000 AND 50000000');
		} else {
			$this->db->where('price > 50000000');
		}
		$this->db->where('product_status', 1);
		$this->db->limit($limit, $start);
		$query = $this->db->get();
		return $query->result();
	}
	function excel_export()
	{
		$this->db->select('*');
		$this->db->from('products');
		$this->db->order_by('title', 'asc');
		$query = $this->db->get();
		return $query->result();
	}

	function getProductDashboard()
	{
		$this->db->select('p.title, pad.attribute_detail , s.stock ,pd.price');
		$this->db->from('products p, product_details pd, stock s, product_combination pc, product_attributes_detail pad');
		$this->db->where('p.id_products = pd.product_id and s.id_product_detail = pd.id and pc.product_details_id = pd.id and pad.id = pc.attribute_detail_id');
		$query = $this->db->get();
		return $query->result();
	}

	public function delete_special_discount($id)
	{
		$this->db->where('id_discount', $id);
		$this->db->delete('special_discount');
	}

	public function delete_bundle($id)
	{
		$this->db->where('id', $id);
		$this->db->delete('bundle');
	}

	public function update_product_discount($productId, $data)
	{
		$this->db->where('id', $productId);
		$this->db->update('product_details', $data);
	}

	public function get_product_detail($productId)
	{
		$this->db->select('price, discounted_price');
		$this->db->from('product_details');
		$this->db->where('product_id', $productId);

		$query = $this->db->get();

		return $query->row();
	}
	public function update_discounted_price($productId, $discountedPrice)
	{
		$this->db->where('product_id', $productId);
		$this->db->update('product_details', ['discounted_price' => $discountedPrice]);
	}

	public function add_product_detail($productId, $newDiscountedPrice)
	{
		$data = [
			'product_id' => $productId,
			'discounted_price' => $newDiscountedPrice
		];
		$this->db->insert('product_details', $data);
	}
	public function search_discounted_products($query)
	{
		$this->db->select('pd.*, p.title');
		$this->db->from('product_details pd');
		$this->db->join('products p', 'pd.product_id = p.id_products', 'left');
		$this->db->like('p.title', $query);
		$this->db->where('pd.discounted_price >', 0);
		$this->db->where('p.product_status =', 1);
		$query = $this->db->get();

		return $query->result();
	}

	// Old
	// public function search_products($query)
	// {
	// 	$this->db->select('p.id_products, p.title, p.alias, pi.image, pd.*');
	// 	$this->db->from('products p');
	// 	$this->db->join('product_images pi', 'p.id_products = pi.product_id', 'left');
	// 	$this->db->join('product_details pd', 'pd.product_id = p.id_products', 'left');

	// 	$this->db->like('p.title', $query);
	// 	$this->db->or_like('p.alias', $query);
	// 	$this->db->where('p.product_status', 1);
	// 	$this->db->limit(10);
	// 	$this->db->group_by('p.id_products');
	// 	return $this->db->get()->result_array();
	// }

	public function search_products($query)
	{
		$this->db->select('p.id_products, p.title, p.alias, p.description, p.description_en,
                      pi.image, pd.price, pd.discounted_price, b.brand');
		$this->db->from('products p');
		$this->db->join('product_images pi', 'p.id_products = pi.product_id AND pi.priority = 1', 'left');
		$this->db->join('product_details pd', 'pd.product_id = p.id_products', 'left');
		$this->db->join('brands b', 'p.brand_id = b.id_brands', 'left');

		// Search conditions
		if ($query) {
			// Pecah query jadi kata-kata
			$keywords = array_filter(explode(' ', trim($query)));
			$fulltext_keywords = [];
			$like_keywords = [];

			// Pisahin kata yang mungkin angka atau pendek
			foreach ($keywords as $keyword) {
				if (is_numeric($keyword) || strpos($keyword, ',') !== false || strlen($keyword) < 4) {
					// Angka, desimal (00,1), atau kata pendek masuk ke LIKE
					$like_keywords[] = $keyword;
				} else {
					// Kata normal masuk ke FULLTEXT
					$fulltext_keywords[] = $keyword;
				}
			}

			$this->db->group_start();

			// FULLTEXT untuk kata-kata panjang
			if (!empty($fulltext_keywords)) {
				$fulltext_query = '+' . implode(' +', $fulltext_keywords);
				$this->db->where("MATCH(p.title, p.alias, p.description, p.description_en) AGAINST('$fulltext_query' IN BOOLEAN MODE)", NULL, FALSE);
			}

			// LIKE untuk angka atau kata pendek
			if (!empty($like_keywords)) {
				$this->db->group_start();
				foreach ($like_keywords as $keyword) {
					$this->db->like('p.title', $keyword);
					$this->db->or_like('p.alias', $keyword);
					$this->db->or_like('p.description', $keyword);
					$this->db->or_like('p.description_en', $keyword);
				}
				$this->db->group_end();
			}

			$this->db->group_end();
		}

		$this->db->where('p.product_status', 1);
		$this->db->where('p.deleted_at', null);
		$this->db->limit(10);
		$this->db->group_by('p.id_products');
		if (!empty($fulltext_keywords)) {
			$fulltext_query = '+' . implode(' +', $fulltext_keywords);
			$this->db->order_by("MATCH(p.title, p.alias, p.description, p.description_en) AGAINST('$fulltext_query' IN BOOLEAN MODE) DESC");
		}

		$products = $this->db->get()->result_array();

		// Process each product
		foreach ($products as &$product) {
			$original_price = (float)$product['price'];
			$discounted_price = $product['discounted_price'] ? (float)$product['discounted_price'] : null;

			// Discount logic
			$product['isDiscounted'] = !is_null($discounted_price) && $discounted_price > 0 && $discounted_price < $original_price;
			$product['final_price'] = $product['isDiscounted'] ? $discounted_price : $original_price;

			// Calculate discount percentage
			if ($product['isDiscounted'] && $original_price > 0) {
				$product['discount_percentage'] = round((($original_price - $discounted_price) / $original_price) * 100);
				$product['savings'] = $original_price - $discounted_price;
			} else {
				$product['discount_percentage'] = 0;
				$product['savings'] = 0;
			}

			// Formatted prices
			$product['formatted_original_price'] = 'IDR ' . number_format($original_price, 0, ',', '.');
			$product['formatted_final_price'] = 'IDR ' . number_format($product['final_price'], 0, ',', '.');
			$product['formatted_savings'] = $product['savings'] > 0 ? 'IDR ' . number_format($product['savings'], 0, ',', '.') : null;

			// Clean up original fields to avoid confusion
			unset($product['price'], $product['discounted_price']);
		}

		return $products;
	}

	public function getFilteredProducts($product_type, $delivery, $price)
	{
		$this->db->select('products.*, product_details.price as current_price');
		$this->db->from('products');
		$this->db->join('product_details', 'product_details.product_id = products.id_products');

		// Filter product_type
		if (!empty($product_type)) {
			foreach ($product_type as $type) {
				$this->db->or_like('products.product_type', $type);
			}
		}

		if (!empty($delivery)) {
			foreach ($delivery as $method) {
				$this->db->or_like('products.shipment_method', $method);
			}
		}

		if (!empty($price) && is_numeric($price)) {
			$this->db->where('product_details.price <=', $price);
		}


		return $this->db->get()->result_array();
	}

	// Fetch all products 
	public function all_products()
	{
		$this->db->select('
			p.*,
			pd.id AS id_detail,
			pd.sku,
			pd.price,
			pd.discounted_price,
			s.warehouse_id,
			s.stock,
			s.stock_keep,
			(COALESCE(s.stock, 0) - COALESCE(s.stock_keep, 0)) AS stock_sell,
			GROUP_CONCAT(DISTINCT CONCAT_WS(": ", pa.product_attribute, pad.attribute_detail) SEPARATOR "; ") AS variant_info,
			pad.attribute_detail as atribut
		');
		$this->db->from('products p');
		$this->db->join('product_details pd', 'p.id_products = pd.product_id', 'left');
		$this->db->join('stock s', 'pd.id = s.id_product_detail AND s.warehouse_id = 1', 'left');
		$this->db->join('product_combination pc', 'pd.id = pc.product_details_id', 'left');
		$this->db->join('product_attributes pa', 'pc.attribute_id = pa.id', 'left');
		$this->db->join('product_attributes_detail pad', 'pc.attribute_detail_id = pad.id', 'left');
		$this->db->having('(COALESCE(s.stock, 0) - COALESCE(s.stock_keep, 0)) > 0');
		$this->db->group_by('pd.id');
		$this->db->order_by('p.title', 'ASC');
		$this->db->order_by('variant_info', 'ASC');

		$query = $this->db->get();
		return $query->result();
	}

	public function all_tokopedia_products()
	{
		$this->db->select('
			p.*,
			pd.id AS id_detail,
			pd.sku,
			pd.price,
			pd.discounted_price,
			s.warehouse_id,
			s.stock,
			s.stock_keep,
			(COALESCE(s.stock, 0) - COALESCE(s.stock_keep, 0)) AS stock_sell,
			GROUP_CONCAT(DISTINCT CONCAT_WS(": ", pa.product_attribute, pad.attribute_detail) SEPARATOR "; ") AS variant_info,
			pad.attribute_detail as atribut
		');
		$this->db->from('products p');
		$this->db->join('product_details pd', 'p.id_products = pd.product_id', 'left');
		$this->db->join('stock s', 'pd.id = s.id_product_detail AND s.warehouse_id = 13', 'left');
		$this->db->join('product_combination pc', 'pd.id = pc.product_details_id', 'left');
		$this->db->join('product_attributes pa', 'pc.attribute_id = pa.id', 'left');
		$this->db->join('product_attributes_detail pad', 'pc.attribute_detail_id = pad.id', 'left');
		$this->db->having('(COALESCE(s.stock, 0) - COALESCE(s.stock_keep, 0)) > 0');
		$this->db->group_by('pd.id');
		$this->db->order_by('p.title', 'ASC');
		$this->db->order_by('variant_info', 'ASC');

		$query = $this->db->get();
		return $query->result();
	}

	public function all_products_with_no_stocks()
	{
		$this->db->select('
			p.*,
			pd.id AS id_detail,
			pd.sku,
			pd.price,
			pd.discounted_price,
			s.warehouse_id,
			s.stock,
			s.stock_keep,
			(COALESCE(s.stock, 0) - COALESCE(s.stock_keep, 0)) AS stock_sell,
			GROUP_CONCAT(DISTINCT CONCAT_WS(": ", pa.product_attribute, pad.attribute_detail) SEPARATOR "; ") AS variant_info,
			pad.attribute_detail as atribut
		');
		$this->db->from('products p');
		$this->db->join('product_details pd', 'p.id_products = pd.product_id', 'left');
		$this->db->join('stock s', 'pd.id = s.id_product_detail AND s.warehouse_id = 1', 'left');
		$this->db->join('product_combination pc', 'pd.id = pc.product_details_id', 'left');
		$this->db->join('product_attributes pa', 'pc.attribute_id = pa.id', 'left');
		$this->db->join('product_attributes_detail pad', 'pc.attribute_detail_id = pad.id', 'left');
		$this->db->group_by('pd.id');
		$this->db->order_by('p.title', 'ASC');
		$this->db->order_by('variant_info', 'ASC');

		$query = $this->db->get();
		return $query->result();
	}

	public function get_products_by_retailer($retailer_id)
	{
		$warehouseMap = [
			8 => 12,
			7 => 14
		];

		$warehouse_id = isset($warehouseMap[$retailer_id]) ? $warehouseMap[$retailer_id] : 1;
		$this->db->select('
			p.*,
			pd.id AS id_detail,
			pd.sku,
			COALESCE(rp.price, pd.price) as price,
			pd.discounted_price,
			s.warehouse_id,
			s.stock,
			s.stock_keep,
			(COALESCE(s.stock, 0) - COALESCE(s.stock_keep, 0)) AS stock_sell,
			GROUP_CONCAT(DISTINCT CONCAT_WS(": ", pa.product_attribute, pad.attribute_detail) SEPARATOR "; ") AS variant_info,
			pad.attribute_detail as atribut,
			rp.min_quantity,
			(CASE WHEN rp.id_resellers_price IS NOT NULL THEN 1 ELSE 0 END) AS has_special_price
		');
		$this->db->from('products p');
		$this->db->join('product_details pd', 'p.id_products = pd.product_id', 'left');
		$this->db->join('stock s', "pd.id = s.id_product_detail AND s.warehouse_id = $warehouse_id", 'left');
		$this->db->join('product_combination pc', 'pd.id = pc.product_details_id', 'left');
		$this->db->join('product_attributes pa', 'pc.attribute_id = pa.id', 'left');
		$this->db->join('product_attributes_detail pad', 'pc.attribute_detail_id = pad.id', 'left');
		// Join dengan tabel harga reseller
		$this->db->join('resellers_price rp', "pd.id = rp.product_detail_id AND rp.reseller_id = $retailer_id", 'left');
		$this->db->having('(COALESCE(s.stock, 0) - COALESCE(s.stock_keep, 0)) > 0');
		$this->db->group_by('pd.id');
		$this->db->order_by('p.title', 'ASC');
		$this->db->order_by('variant_info', 'ASC');

		$query = $this->db->get();
		return $query->result();
	}

	// Fetch products by warehouse_id
	public function get_products_by_warehouse($warehouse_id = 1)
	{
		$this->db->select('
			p.*,
			pd.id AS id_detail,
			pd.sku,
			pd.price,
			pd.discounted_price,
			s.warehouse_id,
			s.stock,
			s.stock_keep,
			(COALESCE(s.stock, 0) - COALESCE(s.stock_keep, 0)) AS stock_sell,
			GROUP_CONCAT(DISTINCT CONCAT_WS(": ", pa.product_attribute, pad.attribute_detail) SEPARATOR "; ") AS variant_info,
			pad.attribute_detail as atribut
		');
		$this->db->from('products p');
		$this->db->join('product_details pd', 'p.id_products = pd.product_id', 'left');
		$this->db->join('stock s', 'pd.id = s.id_product_detail AND s.warehouse_id = ' . $warehouse_id, 'left');
		$this->db->join('product_combination pc', 'pd.id = pc.product_details_id', 'left');
		$this->db->join('product_attributes pa', 'pc.attribute_id = pa.id', 'left');
		$this->db->join('product_attributes_detail pad', 'pc.attribute_detail_id = pad.id', 'left');
		$this->db->where('p.deleted_at', null);
		$this->db->having('(COALESCE(s.stock, 0) - COALESCE(s.stock_keep, 0)) > 0');
		$this->db->group_by('pd.id');
		$this->db->order_by('p.title', 'ASC');
		$this->db->order_by('variant_info', 'ASC');

		$query = $this->db->get();
		return $query->result();
	}

	// Fetch all attribute (Warna, Ukuran, dll) 
	public function get_all_attributes()
	{
		$this->db->select('*');
		$this->db->from('product_attributes');
		$this->db->order_by('priority', 'ASC');

		return $this->db->get()->result();
	}

	// Fetch all shipping method
	public function get_all_shipping_methods()
	{
		return $this->db->get('shipment_method')->result();
	}

	public function get_all_payment_methods()
	{
		return $this->db->get('bank_accounts')->result();
	}

	// Fetch all attribute options (Misal kalau warna: hitam, biru. kalau ukuran: 100ml, 250ml) 
	public function get_attribute_options($attribute_id)
	{
		return $this->db->where('product_attribute_id', $attribute_id)->get('product_attributes_detail')->result();
	}

	// Hitung produk
	public function count_products($status = null)
	{
		$this->db->from('products');

		if (!is_null($status)) {
			$this->db->where('product_status', (string) $status);
			$this->db->where('deleted_at', null);
		}
		$this->db->where('deleted_at', null);

		return $this->db->count_all_results();
	}

	// Edit
	// Get product data
	public function fetch_product_by_id($id_products)
	{
		return $this->db->where('id_products', $id_products)
			->get('products')
			->row();
	}

	// Get Product Category Data
	public function fetch_product_categories($id_product)
	{
		return $this->db->select('id_category')
			->from('category_product')
			->where('id_product', $id_product)
			->get()
			->result();
	}

	public function fetch_product_shipping_methods($id_product)
	{
		return $this->db->select('shipment_method_id')
			->from('shipment_method_product')
			->where('product_id', $id_product)
			->get()
			->result();
	}

	public function fetch_product_variants($product_id)
	{
		$this->db->select('pc.*, pd.*, pad.attribute_detail, pa.product_attribute, s.stock')
			->from('product_combination pc')
			->join('product_details pd', 'pc.product_details_id = pd.id')
			->join('product_attributes_detail pad', 'pc.attribute_detail_id = pad.id')
			->join('product_attributes pa', 'pc.attribute_id = pa.id')
			->join('stock s', 'pd.id = s.id_product_detail AND s.warehouse_id = 1')
			->where('pc.product_id', $product_id);

		return $this->db->get()->result();
	}

	// Fetch attribute values data
	public function get_attribute_values($attribute_id)
	{
		$this->db->select('*')
			->from('product_attributes_detail')
			->where('product_attribute_id', $attribute_id)
			->order_by('priority', 'ASC');

		return $this->db->get()->result_array(); // Ambil hanya nilai-nya
	}

	// Hitung jumlah produk sesuai attribute id
	public function count_products_by_attribute($attribute_id)
	{
		$this->db->select('COUNT(DISTINCT product_id) as total_products');
		$this->db->from('product_combination');
		$this->db->where('attribute_id', $attribute_id);
		$query = $this->db->get();
		$result = $query->row();
		return $result ? $result->total_products : 0;
	}

	public function fetch_variant_by_id($variant_id)
	{
		$this->db->select('*')
			->from('product_attributes')
			->where('id', $variant_id);

		return $this->db->get()->row();
	}

	public function fetch_product_details_by_id($id_product)
	{
		$this->db->select('id, sku')
			->from('product_details')
			->where('product_id', $id_product);

		return $this->db->get()->result_array();
	}

	public function fetch_product_images_by_id_details($id_details)
	{
		if (empty($id_details)) {
			return [];
		}

		$this->db->from('product_images');
		$this->db->where_in('product_details_id', $id_details);

		return $this->db->get()->result_array();
	}

	public function get_product_name_by_detail_id($product_detail_id)
	{
		return $this->db->select('p.title, pd.sku')
			->from('product_details pd')
			->join('products p', 'p.id_products = pd.product_id')
			->where('pd.id', $product_detail_id)
			->get()
			->row();
	}
	public function bulk_delete_products($product_ids)
	{
		if (empty($product_ids)) {
			return false;
		}

		// Mulai transaksi database
		$this->db->trans_begin();

		try {
			$this->db->select('id');
			$this->db->where_in('product_id', $product_ids);
			$query = $this->db->get('product_details');
			$product_details_ids = array_column($query->result_array(), 'id');

			if (!empty($product_details_ids)) {
				$this->db->where_in('id_product_detail', $product_details_ids);
				$this->db->delete('stock');
			}

			if (!empty($product_details_ids)) {
				$this->db->where_in('product_details_id', $product_details_ids);
				$this->db->delete('product_images');
			}

			$this->db->where_in('product_id', $product_ids);
			$this->db->delete('product_images');

			if (!empty($product_details_ids)) {
				$this->db->where_in('product_details_id', $product_details_ids);
				$this->db->delete('product_combination');
			}

			$this->db->where_in('product_id', $product_ids);
			$this->db->delete('product_details');

			$this->db->where_in('id_products', $product_ids);
			$this->db->update('products', ['deleted_at' => date('Y-m-d H:i:s')]);

			if ($this->db->trans_status() === FALSE) {
				$this->db->trans_rollback();
				return false;
			} else {
				$this->db->trans_commit();
				return true;
			}
		} catch (Exception $e) {
			$this->db->trans_rollback();
			log_message('error', 'Bulk delete error: ' . $e->getMessage());
			return false;
		}
	}
}