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

define('PRODUCT_QUERY', 'products.alias as alias, products.image1 as image1,
products.title as title, products.id_products as id_products,
products.created_at, products.new_arrival, products.preorder_message, product_details.price');

define('PRODUCT_DETAILS_JOIN', 'product_details.product_id = products.id_products');

class Product_m extends MY_Model
{
		protected $_table_name = 'products';
		protected $_primary_key = 'id_products';
		protected $_order_by = 'id_products';

		public $rules = array(
				array(
						'field'   => 'product_name',
						'label'   => 'Product Name',
						'rules'   => 'trim|required|callback__cek_existing_product_title'
				),
				array(
							'field'   => 'brand_id',
							'label'   => 'Brand ID',
							'rules'   => 'trim|required'
						),
				array(
							'field'   => 'category_id[]',
							'label'   => 'Category ID',
							'rules'   => 'required'
						),  
				array(
							'field'   => 'color_code',
							'label'   => 'Color Code',
							'rules'   => 'trim'
						),  
					array(
							'field'   => 'description',
							'label'   => 'Description',
							'rules'   => 'trim' 
						),   
					array(
							'field'   => 'long_description',
							'label'   => 'Long Description',
							'rules'   => 'trim' 
						),      
					array(
							'field'   => 'additional_information',
							'label'   => 'Additional Information',
							'rules'   => 'trim' 
						),      
				array(
						'field'   => 'product_status',
						'label'   => 'Product status',
						'rules'   => 'trim|required'
					),
					array(
						'field'   => 'new_arrival',
						'label'   => 'New Arrival',
						'rules'   => 'trim'
					),    
					array(
						'field'   => 'meta_description',
						'label'   => 'meta_description',
						'rules'   => 'trim'
					),   
					array(
						'field'   => 'meta_keywords',
						'label'   => 'meta_keywords',
						'rules'   => 'trim'
					),     
			); 

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

		//ADMIN WEBSITE
		//pagination included
		function get_all_products($limit, $start) {
			$this->db->select('*');
			$this->db->from('products');
			$this->db->order_by('created_at', 'DESC');
			$this->db->limit($limit, $start);
			$query = $this->db->get();		
			return $query->result(); 
		}

		//pagination included
		function get_all_products_search_product($keyword) {
			$this->db->select('*');
			$this->db->from('products');
			$this->db->like('products.title', $keyword);
			$this->db->order_by('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();
		}

		//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->title = '';	
			$products->id_products = '';	
			$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->popular_product = '';	
			$products->image1 = '';	
			$products->image2 = '';	
			$products->image3 = '';	
			$products->image4 = '';	
			$products->image5 = '';	
			$products->meta_description = '';	
			$products->meta_keywords = '';	
			$products->color_code = '';
			$products->preorder_message = '';
			$products->international_custom_order = '';
			return $products;
		}

		//function add new product
		function add_product($data) {
			$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);	
		}

		//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 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('image1');
			$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 brand id
		public function get_products_by_brand($brand_id, $limit, $start, $sort_product_by) {

			$this->db->distinct('product_details.product_id');
			//$this->db->select('*');
			$this->db->select('products.alias as alias, products.image1 as image1, products.image2 as image2, products.title as title, products.id_products as id_products, products.created_at, product_details.price');
			$this->db->from('products'); 
			$this->db->join('product_details', 'products.id_products = product_details.product_id');
			$this->db->where('products.brand_id', $brand_id);
			$this->db->where('products.product_status', 1);
			
			if ($sort_product_by != NULL) {
				
				switch ($sort_product_by) {
					case 'price-asc':
						$this->db->order_by('product_details.price', 'ASC');
						break;
					case 'price-desc':
						$this->db->order_by('product_details.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;	
				}

			} else {
				$this->db->order_by('products.created_at', 'DESC');
			}
			
			$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) {

			$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);
			
			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 {

				$this->db->order_by('created_at', 'DESC');
			}
			
			$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) { 

			$this->db->distinct('product_details.product_id');
			//$this->db->select('*');
			$this->db->select('products.alias as alias, products.image1 as image1, products.image2 as image2, products.title as title, products.id_products as id_products, products.created_at, product_details.price');
			$this->db->from('products');
			$this->db->join('product_details', PRODUCT_DETAILS_JOIN);
			$this->db->where('products.new_arrival', 'yes');
			$this->db->where('products.product_status', 1);
			
			if ($this->session->userdata('sort_product')) {
				
				switch ($this->session->userdata('sort_product')) {
				
					case 'price-asc':
						$this->db->order_by('product_details.price', 'ASC');
						break;
					case 'price-desc':
						$this->db->order_by('product_details.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 {
				$this->db->order_by('products.created_at', 'DESC');
			}

			$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();
		}

		public function count_products_all()
		{
				$this->db->select('products.id_products');
				$this->db->from('products');
				$this->db->join('product_details', PRODUCT_DETAILS_JOIN, 'left');
				
				if($this->session->userdata('filter_product'))
				{
						$this->db->join('categories', 'categories.id_categories = category_product.id_category');
						$this->db->where('product_details.price >=', (int) $this->session->userdata('filter_product')['chosenPriceFrom']);
						$this->db->where('product_details.price <=', (int) $this->session->userdata('filter_product')['chosenPriceTo']);
						//$this->db->where('categories.meta_keywords', $this->session->userdata('filter_product')['chosenProductType']);
				}

				$this->db->where('product_status', 1);

				$this->db->group_by('products.id_products');
				$query = $this->db->get();
				return $query->num_rows();
		}

		public function get_products_all($limit, $start)
		{
				$this->db->select(PRODUCT_QUERY);
				$this->db->from('products');
				$this->db->join('product_details', PRODUCT_DETAILS_JOIN, 'left');

				if($this->session->userdata('filter_product'))
				{
						$this->db->join('categories', 'categories.id_categories = category_product.id_category');
						$this->db->where('product_details.price >=', (int) $this->session->userdata('filter_product')['chosenPriceFrom']);
						$this->db->where('product_details.price <=', (int) $this->session->userdata('filter_product')['chosenPriceTo']);
						//$this->db->where('categories.meta_keywords', $this->session->userdata('filter_product')['chosenProductType']);
				}
				
				$this->db->where('products.product_status', 1);
				$this->db->group_by('products.id_products');
				
				if ($this->session->userdata('sort_product'))
				{
						switch ($this->session->userdata('sort_product'))
						{
							case 'price-asc':
									$this->db->order_by('product_details.price', 'ASC');
									break;
							case 'price-desc':
									$this->db->order_by('product_details.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;
							default:
								$this->db->order_by('products.created_at', 'DESC');
								break;
						}
				}
				else
				{
					$this->db->order_by('products.created_at', 'DESC');
				}

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

		public function count_products_by_category($category_id)
		{
				$this->db->select('id_products');
				$this->db->from('products');
				$this->db->join('category_product', 'products.id_products = category_product.id_product');
				$this->db->join('product_details', PRODUCT_DETAILS_JOIN);

				if($this->session->userdata('filter_product'))
				{
						$this->db->join('categories', 'categories.id_categories = category_product.id_category');
						$this->db->where('product_details.price >=', (int) $this->session->userdata('filter_product')['chosenPriceFrom']);
						$this->db->where('product_details.price <=', (int) $this->session->userdata('filter_product')['chosenPriceTo']);
						//$this->db->where('categories.meta_keywords', $this->session->userdata('filter_product')['chosenProductType']);
				}

				$this->db->where('id_category', $category_id);
				$this->db->where('products.product_status', '1');

				$this->db->group_by('products.id_products');
				$query = $this->db->get();
				return $query->num_rows();
		}

		public function get_products_by_category($category_id, $limit, $start)
		{
				$this->db->select(PRODUCT_QUERY);
				$this->db->from('products');
				$this->db->join('category_product', 'products.id_products = category_product.id_product');
				$this->db->join('product_details', PRODUCT_DETAILS_JOIN);

				if($this->session->userdata('filter_product'))
				{
						$this->db->join('categories', 'categories.id_categories = category_product.id_category');
						$this->db->where('product_details.price >=', (int) $this->session->userdata('filter_product')['chosenPriceFrom']);
						$this->db->where('product_details.price <=', (int) $this->session->userdata('filter_product')['chosenPriceTo']);
						//$this->db->where('categories.meta_keywords', $this->session->userdata('filter_product')['chosenProductType']);
				}

				$this->db->where('category_product.id_category', $category_id);
				$this->db->where('products.product_status', '1');

				$this->db->group_by('products.id_products');

				if($this->session->userdata('sort_product'))
				{
						switch ($this->session->userdata('sort_product'))
						{
								case 'price-asc':
									$this->db->order_by('product_details.price', 'ASC');
									break;
								case 'price-desc':
									$this->db->order_by('product_details.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;
								default:
								$this->db->order_by('products.created_at', 'DESC');
									break;
						}
				}
				else
				{
						$this->db->order_by('products.created_at', 'DESC');
				}
				$this->db->limit($limit, $start);
				$query = $this->db->get();

				return $query->result();
		}

		//IS USED: get products by bestseller
		public function get_products_bestseller($limit, $start) { 

			$this->db->distinct('product_details.product_id');
			//$this->db->select('*');
			$this->db->select('products.alias as alias, products.image1 as image1, products.image2 as image2, products.title as title, products.id_products as id_products, products.created_at, product_details.price');
			$this->db->from('products');
			$this->db->join('product_details', PRODUCT_DETAILS_JOIN);
			$this->db->where('products.best_seller', 'yes');
			$this->db->where('products.product_status', 1);
			
			if ($this->session->userdata('sort_product')) {
				
				switch ($this->session->userdata('sort_product')) {
				
					case 'price-asc':
						$this->db->order_by('product_details.price', 'ASC');
						break;
					case 'price-desc':
						$this->db->order_by('product_details.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 {
				$this->db->order_by('products.created_at', 'DESC');
			}

			$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) {

			//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);
			
			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 {

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

			$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) {
			$this->db->distinct('product_details.product_id');
			//$this->db->select('*');
			$this->db->select('products.alias as alias, products.image1 as image1, products.image2 as image2, products.title as title, products.id_products as id_products, products.created_at, product_details.price');
			$this->db->from('products');
			$this->db->join('product_details', PRODUCT_DETAILS_JOIN);
			
			$this->db->where('products.product_status', 1);
			$this->db->where('product_details.discounted_price !=', 0);
			
			if ($this->session->userdata('sort_product')) { 
				
				switch ($this->session->userdata('sort_product')) { 
				
					case 'price-asc':
						$this->db->order_by('product_details.price', 'ASC');
						break;
					case 'price-desc':
						$this->db->order_by('product_details.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 { 
				$this->db->order_by('created_at', 'DESC');
			}
			$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_JOIN);
			$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('id_products');
				$this->db->from('products');
				$this->db->join('product_details', PRODUCT_DETAILS_JOIN, 'left');
				$this->db->where('products.product_status', '1');
				$this->db->like('products.title', $keyword);
				$this->db->or_like('products.categories', $keyword);
				$this->db->group_by('products.id_products');
				
				$query = $this->db->get();
				return $query->num_rows();
		}

		public function get_products_by_search($keyword, $limit, $start)
		{
				//get products
				$this->db->select(PRODUCT_QUERY);
				$this->db->from('products');
				$this->db->join('product_details', PRODUCT_DETAILS_JOIN, 'left');
				$this->db->where('products.product_status', '1');
				$this->db->like('products.title', $keyword);
				$this->db->or_like('products.categories', $keyword);
				$this->db->group_by('products.id_products');

				if ($this->session->userdata('sort_product'))
				{
						switch ($this->session->userdata('sort_product'))
						{
								case 'price-asc':
										$this->db->order_by('product_details.price', 'ASC');
										break;
								case 'price-desc':
										$this->db->order_by('product_details.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;
								default:
										break;
						}
				}
				else
				{
						$this->db->order_by('products.created_at', 'DESC');
				}
				$this->db->limit($limit, $start);
				$query = $this->db->get();
				return $query->result();
		}

		//cek if product title already exist
		public function cek_existing_product_title($str, $product_current_id) {
			$this->db->select('id_products');
			$this->db->from('products'); 
			$this->db->where('title', $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();
		}
}