<?php
    class Product {
        public $conn;
        public $db_prefix;
        public $product_id;
        public $ratings;
        public $reviews = array();
        public $categories = array();
        public $images = array();
        public $parent_categories = array();
        public $sub_categories = array();

        public function __construct(Database $db, Utils $utils, Config $config){
            $this->db = $db;
            $this->utils = $utils;
            $this->config = $config;
        }

        function getProduct($product_id){
            $stmt = $this->db->query("SELECT * FROM {$this->db->prefix}products WHERE id=:id LIMIT 1", array(":id"=>$product_id));
            
            if($stmt->num_rows > 0){
                $categories         = $this->getProductCategories($stmt->row['id']);
                $shipping_agencies  = $this->getProductShippingAgencies($stmt->row['id']);
                $badges             = $this->getProductBadges($stmt->row['id']);
                $delivery_areas     = $this->getProductDeliveryAreas($stmt->row['id']);
                $categories_        = array();
                foreach($categories as $category) {
                    $categories_[] = $category['id'];
                }

                $stmt->row['categories'] = $categories_;
                $stmt->row['delivery_areas'] = $delivery_areas;
                $stmt->row['shipping_agencies'] = $shipping_agencies;
                $stmt->row['badges'] = $badges;

                return $stmt->row;
            }
        }

        function getBrand($brand_id){
            $stmt = $this->db->query("SELECT b.id, b.status, b.title, b.image, b.featured, u.url FROM {$this->db->prefix}brands b LEFT JOIN {$this->db->prefix}urls u ON u.element = b.id WHERE url_type='brand' AND b.id=:brand_id", array(":brand_id"=>$brand_id));
            if($stmt->num_rows > 0) {
                $brand = array(
                    'id' => $stmt->row['id'],
                    'status' => $stmt->row['status'],
                    'title' => $stmt->row['title'],
                    'image' => $stmt->row['image'],
                    'featured' => $stmt->row['featured'],
                    'url' => $stmt->row['url']
                );
                return $brand;
            }
            return false;
        }

        function getBrands($data = array()){
            $brands = array();
            $query = "SELECT b.id, b.status, b.title, b.image, u.url FROM {$this->db->prefix}brands b LEFT JOIN {$this->db->prefix}urls u ON u.element = b.id WHERE url_type='brand'";
            
            if(isset($data['search_keywords']) && !empty($data['search_keywords'])) {
                $keywords = explode(' ', $data['search_keywords']);
                $searchTermKeywords = array();
                foreach ($keywords as $word) {
                    $word                 = $this->db->escape($word);
                    $searchTermKeywords[] = "(title LIKE '%$word%')";
                }
                $query .= ' AND '.implode(' AND ', $searchTermKeywords);
            }

            $stmt = $this->db->query($query);
            if($stmt->num_rows > 0) {
                foreach($stmt->rows as $row){
                    $row['status'] = $row['status'] == 1 ? '<span class="badge bg-success">Aktif</span>' : '<span class="badge bg-danger">Pasif</span>';
                    $brands[] = $row;
                }
            }
            return $brands;
        }

        function getTags(){
            $tags = array();
            $query = "SELECT * FROM {$this->db->prefix}product_tags";
            $stmt = $this->db->query($query);
            if($stmt->num_rows > 0) {
                $tags = $stmt->rows;
            }
            return $tags;
        }

        function getCollection($collection_id){
            $stmt = $this->db->query("SELECT * FROM {$this->db->prefix}collections WHERE id=:collection_id", array(":collection_id"=>$collection_id));
            if($stmt->num_rows > 0) {
                $collection = $stmt->row;
                return $collection;
            }
            return false;
        }

        function getCollections($data = array()){
            $collections = array();
            $query = "SELECT b.id, b.status, b.title, b.description, u.url FROM {$this->db->prefix}collections b LEFT JOIN {$this->db->prefix}urls u ON u.element = b.id WHERE url_type='collection'";
            
            if(isset($data['search_keywords']) && !empty($data['search_keywords'])) {
                $keywords = explode(' ', $data['search_keywords']);
                $searchTermKeywords = array();
                foreach ($keywords as $word) {
                    $word                 = $this->db->escape($word);
                    $searchTermKeywords[] = "(title LIKE '%$word%')";
                }
                $query .= ' AND '.implode(' AND ', $searchTermKeywords);
            }
            
            $stmt = $this->db->query($query);
            if($stmt->num_rows > 0) {
                foreach($stmt->rows as $row){
                    $row['status'] = $row['status'] == 1 ? '<span class="badge bg-success">Aktif</span>' : '<span class="badge bg-danger">Pasif</span>';
                    $collections[] = $row;
                }
            }
            return $collections;
        }

        function getCollectionProducts($collection_id){
            $products = array();
            $stmt = $this->db->query("SELECT * FROM {$this->db->prefix}collection_products WHERE collection_id=:collection_id", array(":collection_id"=>$collection_id));
            if($stmt->num_rows > 0) {
                foreach($stmt->rows as $row) {
                    $products[] = $row['product_id'];
                }
                
                return $products;
            }
            return array();
        }

        public function deleteCollection($id){
            $this->db->query("DELETE FROM {$this->db->prefix}collections WHERE id=:id", array(":id"=>$id));
            $this->db->query("DELETE FROM {$this->db->prefix}collection_products WHERE collection_id=:id", array(":id"=>$id));
            $this->db->query("DELETE FROM {$this->db->prefix}urls WHERE url_type='collection' AND element=:id", array(":id"=>$id));
        }

        public function deleteBrand($id){
            $this->db->query("DELETE FROM {$this->db->prefix}brands WHERE id=:id", array(":id"=>$id));
            $this->db->query("DELETE FROM {$this->db->prefix}urls WHERE url_type='brand' AND element=:id", array(":id"=>$id));
        }

        public function getProductBadges($product_id){
            $badges = array();
            $stmt = $this->db->query("SELECT * FROM {$this->db->prefix}product_to_badge WHERE product_id=:id", array(':id'=>$product_id));
            if($stmt->num_rows > 0) {
                foreach($stmt->rows as $row) {
                    $badges[] = $row['badge_id'];
                }
            }
            return $badges;
        }

        function getBadges($data = array()){
            $badges = array();
            $query = "SELECT * FROM {$this->db->prefix}product_badges WHERE 1";
            
            if(isset($data['search_keywords']) && !empty($data['search_keywords'])) {
                $keywords = explode(' ', $data['search_keywords']);
                $searchTermKeywords = array();
                foreach ($keywords as $word) {
                    $word                 = $this->db->escape($word);
                    $searchTermKeywords[] = "(description LIKE '%$word%')";
                }
                $query .= ' AND '.implode(' AND ', $searchTermKeywords);
            }

            if(isset($data['status'])){
                if($data['status'] != ''){
                    $query .= " AND status =".(int)$data['status'];
                }
            }

            if(isset($data['sort'])){
                $query .= " ORDER BY " . $data['sort'];
            }else{
                $query .= " ORDER BY id";
            }
    
            if(isset($data['order'])){
                $query .= " ".$data['order'];
            }else{
                $query .= " DESC";
            }
    
            if (isset($data['offset']) || isset($data['limit'])) {
                if($data['offset'] < 0){
                    $data['offset'] = 0;
                }
    
                if($data['limit'] < 1){
                    $data['limit'] = 24;
                }
    
                $query .= " LIMIT " . (int)$data['offset'] . "," . (int)$data['limit'];
            }

            $stmt = $this->db->query($query);
            if($stmt->num_rows > 0) {
                foreach($stmt->rows as $row){
                    $row['status'] = $row['status'] == 1 ? '<span class="badge bg-success">Aktif</span>' : '<span class="badge bg-danger">Pasif</span>';
                    $badges[] = $row;
                }
            }
            return $badges;
        }

        public function getTotalBadges($data = array()){
            $query = "SELECT COUNT(id) AS 'total' FROM {$this->db->prefix}product_badges WHERE 1";
            
            if(isset($data['search_keywords']) && !empty($data['search_keywords'])) {
                $keywords = explode(' ', $data['search_keywords']);
                $searchTermKeywords = array();
                foreach ($keywords as $word) {
                    $word                 = $this->db->escape($word);
                    $searchTermKeywords[] = "(description LIKE '%$word%')";
                }
                $query .= ' AND '.implode(' AND ', $searchTermKeywords);
            }

            if(isset($data['status'])){
                if($data['status'] != ''){
                    $query .= " AND status =".(int)$data['status'];
                }
            }

            $stmt = $this->db->query($query);
            if($stmt->num_rows > 0) {
                return $stmt->row['total'];
            }
            return 0;
        }

        public function deleteBadge($id){
            $this->db->query("DELETE FROM {$this->db->prefix}product_badges WHERE id=:id", array(":id"=>$id));
        }

        public function deleteProduct($id){
            $this->db->query("DELETE FROM {$this->db->prefix}products WHERE id=:id", array(":id"=>$id));
            $this->db->query("DELETE FROM {$this->db->prefix}urls WHERE url_type='product' AND element=:id", array(":id"=>$id));
        }

        public function deleteProductReview($id){
            $this->db->query("DELETE FROM {$this->db->prefix}product_reviews WHERE id=:id", array(":id"=>$id));
        }

        function getProductImages($product_id){
            $product_images = array();
            $stmt = $this->db->query("SELECT * FROM {$this->db->prefix}product_images WHERE product_id=:id", array(":id"=>$product_id));
            if($stmt->num_rows > 0) {
                $product_images = $stmt->rows;
            }
            return $product_images;
        }

        public function getProductReviews($data = array()){
            $reviews = array();
            $query = "SELECT id, status, product_id, rating, text, title, user_name, DATE_FORMAT(date_added, '%Y-%m-%d') as 'date_added', (SELECT title FROM {$this->db->prefix}products WHERE id=r.product_id) AS 'product_title' FROM {$this->db->prefix}product_reviews r WHERE 1";
            if(isset($data['search_keywords'])){
                $keywords = explode(' ', $data['search_keywords']);
                $searchTermKeywords = array();
                foreach ($keywords as $word) {
                    $word                 = $this->db->escape($word);
                    $searchTermKeywords[] = "title LIKE '%$word%' OR text LIKE '%$word%' OR user_name LIKE '%$word%'";
                }
                $query .= ' AND '.implode(' AND ', $searchTermKeywords);
            }

            if(isset($data['status'])){
                if($data['status'] != ''){
                    $query .= " AND status =".(int)$data['status'];
                }
            }

            if(isset($data['sort'])){
                $query .= " ORDER BY " . $data['sort'];
            }else{
                $query .= " ORDER BY id";
            }
    
            if(isset($data['order'])){
                $query .= " ".$data['order'];
            }else{
                $query .= " DESC";
            }
    
            if (isset($data['offset']) || isset($data['limit'])) {
                if($data['offset'] < 0){
                    $data['offset'] = 0;
                }
    
                if($data['limit'] < 1){
                    $data['limit'] = 24;
                }
    
                $query .= " LIMIT " . (int)$data['offset'] . "," . (int)$data['limit'];
            }

            $stmt = $this->db->query($query);
            if($stmt->num_rows > 0) {
                foreach($stmt->rows as $row) {
                    $status = $row['status'] == 1 ? '<span class="badge bg-success">Aktif</span>' : '<span class="badge bg-danger">Pasif</span>';
                    $url = $this->utils->getItemURL($row['product_id'], "product");
                    $item = array(
                        'id'            => $row['id'],
                        'status'        => $status,
                        'product_id'    => $row['product_id'],
                        'product_title' => $row['product_title'],
                        'rating'        => $row['rating'],
                        'review'        => $row['text'],
                        'title'         => $row['title'],
                        'user_name'     => $row['user_name'],
                        'date_added'    => $row['date_added'],
                        'url'           => $url['url']
                    );
                    array_push($reviews, $item);
                }
            }

            return $reviews;
        }
        

        public function getTotalProductReviews($data = array()){
            $query = "SELECT COUNT(id) AS 'total' FROM {$this->db->prefix}product_reviews WHERE 1";
            
            if(isset($data['search_keywords'])){
                $keywords = explode(' ', $data['search_keywords']);
                $searchTermKeywords = array();
                foreach ($keywords as $word) {
                    $word                 = $this->db->escape($word);
                    $searchTermKeywords[] = "title LIKE '%$word%' OR text LIKE '%$word%' OR user_name LIKE '%$word%'";
                }
                $query .= ' AND '.implode(' AND ', $searchTermKeywords);
            }

            if(isset($data['status'])){
                if($data['status'] != ''){
                    $query .= " AND status =".(int)$data['status'];
                }
            }

            $stmt = $this->db->query($query);
            if($stmt->num_rows > 0) {
                return $stmt->row['total'];
            }
            return 0;
        }

        public function getProductReview($id){
            $review = array();
            $query = "SELECT r.*, (SELECT title FROM {$this->db->prefix}products WHERE id=r.product_id) AS 'product_title' FROM {$this->db->prefix}product_reviews r WHERE id=:id";
            
            $stmt = $this->db->query($query, array(':id'=>$id));
            if($stmt->num_rows > 0) {
                $review = $stmt->row;
            }

            return $review;
        }

        function getProductRatings(){
            $rating_1	= 0;
            $rating_2	= 0;
            $rating_3	= 0;
            $rating_4	= 0;
            $rating_5	= 0;
            $average	= 0;
            $total		= 0;

            $stmt = $this->db->query("SELECT rating FROM {$this->db->prefix}product_reviews WHERE product_id=:id AND status='1'", array(":id"=>$this->product_id));
            if($stmt->num_rows > 0) {
                foreach($stmt->rows as $row) {
                    if($row['rating'] == 1){
                        $rating_1++;
                    }else if($row['rating'] == 2){
                        $rating_2++;
                    }else if($row['rating'] == 3){
                        $rating_3++;
                    }else if($row['rating'] == 4){
                        $rating_4++;
                    }else if($row['rating'] == 5){
                        $rating_5++;
                    }
                    $total++;
                }
            }

            $total_rating	    = $rating_1*1 + $rating_2*2 + $rating_3*3 + $rating_4*4 + $rating_5*5;
            $average            = $total_rating != 0 ? round($total_rating/$total,1) : 0;
            $rating_1_percent	= $rating_1 != 0 ? round($rating_1/$total*100,2) : 0;
            $rating_2_percent	= $rating_2 != 0 ? round($rating_2/$total*100,2) : 0;
            $rating_3_percent	= $rating_3 != 0 ? round($rating_3/$total*100,2) : 0;
            $rating_4_percent	= $rating_4 != 0 ? round($rating_4/$total*100,2) : 0;
            $rating_5_percent	= $rating_5 != 0 ? round($rating_5/$total*100,2) : 0;
        
            $this->ratings = array('rating_1' => $rating_1, 'rating_2' => $rating_2, 'rating_3' => $rating_3, 'rating_4' => $rating_4, 'rating_5' => $rating_5, 'rating_1_percent' => $rating_1_percent, 'rating_2_percent' => $rating_2_percent, 'rating_3_percent' => $rating_3_percent, 'rating_4_percent' => $rating_4_percent, 'rating_5_percent' => $rating_5_percent, 'average' => $average, 'total' => $total);
        }

        public function getProductCategories($product_id) {
            $categories = array();
            $query = "SELECT c.id, c.title, image, u.url AS 'link' FROM {$this->db->prefix}categories c LEFT JOIN {$this->db->prefix}product_to_category cc ON c.id=cc.category_id LEFT JOIN {$this->db->prefix}urls u ON c.id=u.element WHERE c.status=1 AND url_type='product-category' AND cc.product_id=:id AND c.status=1";
            $stmt = $this->db->query($query, array(":id"=>$product_id));
            if($stmt->num_rows > 0) {
                foreach($stmt->rows as $row) {
                    $item = array(
                        'id'                     => $row['id'],
                        'title'                  => $row['title'],
                        'image'                  => $row['image'],
                        'link'                   => $row['link']
                    );
                    array_push($categories, $item);
                }
            }
            return $categories;
        }


        public function getProductShippingAgencies($product_id){
            $shipping_agencies = array();
            $stmt = $this->db->query("SELECT * FROM {$this->db->prefix}product_shipping WHERE product_id=:id", array(':id'=>$product_id));
            if($stmt->num_rows > 0) {
                foreach($stmt->rows as $row) {
                    $shipping_agencies[] = $row['shipping_agency'];
                }
            }
            return $shipping_agencies;
        }

        public function getCategory($category_id){
            $category = array();
            $query = "SELECT * FROM {$this->db->prefix}categories WHERE status=1 AND id=:id LIMIT 1";
            $stmt = $this->db->query($query, array(":id"=>$category_id));
            if($stmt->num_rows > 0) {
                $category = $stmt->row;
            }

            return $category;
        }

        public function deleteCategory($id){
            $this->db->query("DELETE FROM {$this->db->prefix}categories WHERE id=:id", array(":id"=>$id));
            $this->db->query("DELETE FROM {$this->db->prefix}urls WHERE url_type='product-category' AND element=:id", array(":id"=>$id));
        }

        public function getCategories($data = array()) {
            $categories = array();
            $parent = 0;
            if(isset($data['parent']) && !empty($data['parent'])) {
                $parent = $data['parent'];
            }
            $query = "SELECT c.id, c.status, c.title, image, installment_count, installment_count_business, u.url AS 'link' FROM {$this->db->prefix}categories c LEFT JOIN {$this->db->prefix}urls u ON c.id=u.element WHERE url_type='product-category' AND parent=:parent";
            
            if(isset($data['search_keywords']) && !empty($data['search_keywords'])){
                $keywords = explode(' ', $data['search_keywords']);
                $searchTermKeywords = array();
                foreach ($keywords as $word) {
                    $word                 = $this->db->escape($word);
                    $searchTermKeywords[] = "title LIKE '%$word%'";
                }
                $query .= ' AND '.implode(' AND ', $searchTermKeywords);
            }

            if(isset($data['status'])){
                if($data['status'] != ''){
                    $query .= " AND c.status =".$data['status'];
                }
            }
            
            if(isset($data['sort'])){
                $query .= " ORDER BY c." . $data['sort'];
            }else{
                $query .= " ORDER BY c.id";
            }
    
            if(isset($data['order'])){
                $query .= " ".$data['order'];
            }else{
                $query .= " DESC";
            }
    
            if (isset($data['offset']) || isset($data['limit'])) {
                if($data['offset'] < 0){
                    $data['offset'] = 0;
                }
    
                if($data['limit'] < 1){
                    $data['limit'] = 24;
                }
    
                $query .= " LIMIT " . (int)$data['offset'] . "," . (int)$data['limit'];
            }

            $stmt = $this->db->query($query, array(':parent'=>(int)$parent));
            if($stmt->num_rows > 0) {
                foreach($stmt->rows as $row){ 
                    $sub_items = array();
                    if($this->isCategoryHaveChildren($row["id"])) {
                        $data['parent']=$row["id"];
                        $sub_items = $this->getCategories($data);
                    }
                    $row['product_count'] = $this->getCategoryProductCount($row['id']);

                    $item = array(
                        'id'                     => $row['id'],
                        'status'                 => $row['status'] == 1 ? '<span class="badge bg-success">Aktif</span>' : '<span class="badge bg-danger">Pasif</span>',
                        'title'                  => $row['title'],
                        'image'                  => $row['image'],
                        'installment_count'      => $row['installment_count'],
                        'installment_count_business' => $row['installment_count_business'],
                        'sub_items'              => $sub_items,
                        'url'                    => $row['link']
                    );
                    array_push($categories, $item);
                }
            }

            return $categories;
        }

        public function getTotalCategories($data = array()){
            $parent = 0;
            if(isset($data['parent']) && !empty($data['parent'])) {
                $parent = $data['parent'];
            }
            $query = "SELECT COUNT(DISTINCT c.id) AS 'total' FROM {$this->db->prefix}categories c WHERE 1 AND parent=:parent";

            if(isset($data['search_keywords'])){
                $keywords = explode(' ', $data['search_keywords']);
                $searchTermKeywords = array();
                foreach ($keywords as $word) {
                    $word                 = $this->db->escape($word);
                    $searchTermKeywords[] = "title LIKE '%$word%'";
                }
                $query .= ' AND '.implode(' AND ', $searchTermKeywords);
            }

            if(isset($data['status'])){
                if($data['status'] != ''){
                    $query .= " AND c.status =".$data['status'];
                }
            }
            
            $stmt = $this->db->query($query);
            if($stmt->num_rows > 0){
                $this->total_categories      = $stmt->row['total'];
            }else{
                $this->total_categories      = 0;
            }

            return $this->total_categories;
        }

        public function getSuppliers($data = array()) {
            $suppliers = array();
            $query = "SELECT * FROM {$this->db->prefix}suppliers WHERE 1";
            
            if(isset($data['search_keywords'])){
                $keywords = explode(' ', $data['search_keywords']);
                $searchTermKeywords = array();
                foreach ($keywords as $word) {
                    $word                 = $this->db->escape($word);
                    $searchTermKeywords[] = "title LIKE '%$word%'";
                }
                $query .= ' AND '.implode(' AND ', $searchTermKeywords);
            }

            if(isset($data['status'])){
                if($data['status'] != ''){
                    $query .= " AND status =".$data['status'];
                }
            }
            
            if(isset($data['sort'])){
                $query .= " ORDER BY " . $data['sort'];
            }else{
                $query .= " ORDER BY id";
            }
    
            if(isset($data['order'])){
                $query .= " ".$data['order'];
            }else{
                $query .= " DESC";
            }
    
            if (isset($data['offset']) || isset($data['limit'])) {
                if($data['offset'] < 0){
                    $data['offset'] = 0;
                }
    
                if($data['limit'] < 1){
                    $data['limit'] = 24;
                }
    
                $query .= " LIMIT " . (int)$data['offset'] . "," . (int)$data['limit'];
            }

            $stmt = $this->db->query($query);
            if($stmt->num_rows > 0) {
                $suppliers = $stmt->rows;
            }

            return $suppliers;
        }

        public function isCategoryHaveChildren($id) {
            $result = $this->db->query("SELECT COUNT(id) AS 'count' FROM {$this->db->prefix}categories WHERE status=1 AND parent=:parent", array(':parent'=>(int)$id));
            if($result->row['count']>0){
                return true;
            }
            return false;
        }

        public function getCategoryProductCount($category_id) {
            $total_products = 0;
            $query = "SELECT COUNT(id) AS 'total' FROM {$this->db->prefix}product_to_category WHERE";
            $this->sub_categories = array();
            $sub_categories = $this->getSubCategoryIds($category_id);
            if(!empty($sub_categories)){
                $sub_categories = implode(",",$sub_categories);
                $query .= " category_id IN (".(int)$category_id.",".$sub_categories.")";
            }else{
                $query .= " category_id =".(int)$category_id;
            }

            $result = $this->db->query($query);
            if($result->num_rows > 0){
                $total_products      = $result->row['total'];
            }

            return $total_products;
        }

        public function getCategoryParents($category = 0) {
            if($category != 0) {
                $query = "SELECT * FROM {$this->db->prefix}categories WHERE status=1 AND id=:id";
    
                $result = $this->db->query($query, array(':id'=>(int)$category));
                if($result->num_rows > 0){
                    $result->row['url'] = $this->getUrl('product-category', $result->row['id']);
                    $this->parent_categories[] = $result->row;
                    if($result->row['parent'] != 0) {
                        $this->getCategoryParents($result->row["parent"]);
                    }
                }
            }

            return $this->parent_categories;
        }

        public function getSubCategories($parent){
            $sub_categories = array();
            $stmt = $this->db->query("SELECT c.id, c.title, (SELECT url FROM {$this->db->prefix}urls WHERE element=c.id AND url_type='category') AS 'url' FROM {$this->db->prefix}categories c WHERE c.status=1 AND c.parent=:parent", array(':parent'=>$parent));
            if($stmt->num_rows > 0) {
                foreach($stmt->rows as $row) {
                    $item = array(
                        'id'        => $row['id'],
                        'title'     => $row['title'],
                        'url'       => $row['url']
                    );
                    array_push($sub_categories, $item);
                    $this->getSubCategories($row['id']);
                }
            }else{
                return $sub_categories;
            }
        }

        function getSubCategoryIds($cat_id){
            $sub_categories = array();
            $stmt = $this->db->query("SELECT id FROM {$this->db->prefix}categories WHERE parent=:parent AND status=1", array(':parent'=>$cat_id));
            if($stmt->num_rows > 0) {
                foreach($stmt->rows as $row) {
                    array_push($sub_categories, $row['id']);
                }
            }
            return $sub_categories;
        }

        public function getProducts($data = array()) {
            $products = array();
            $query = "SELECT p.id, p.status, p.title, p.image, price, discounted_price, discount_rate, selling_price, free_shipping, new, stock_tracking, stock_level, stock_critical_level, sku, brand_id, p.sold, (SELECT url FROM {$this->db->prefix}urls WHERE element=p.id AND url_type='product') AS 'url' FROM {$this->db->prefix}products p LEFT JOIN {$this->db->prefix}product_to_category c ON c.product_id = p.id WHERE 1";
            
            if(isset($data['category_id']) && !empty($data['category_id'])){
                $sub_categories = $this->getSubCategoryIds($data['category_id']);
                if(!empty($sub_categories)){
                    $sub_categories = implode(",",$sub_categories);
                    $query .= " AND c.category_id IN (".(int)$data['category_id'].",".$sub_categories.")";
                }else{
                    $query .= " AND c.category_id =".$data['category_id'];
                }
            }

            if(isset($data['status'])){
                if($data['status'] != ''){
                    $query .= " AND p.status =".$data['status'];
                }
            }

            if(isset($data['brand_id']) && !empty($data['brand_id'])){
                $query .= " AND p.brand_id =".(int)$data['brand_id'];
            }

            if(isset($data['author_id']) && !empty($data['author_id'])){
                $query .= " AND p.id IN (SELECT product_id FROM {$this->db->prefix}product_to_author WHERE author_id = ".(int)$data['author_id'] . ")";
            }

            if(isset($data['search_keywords']) && !empty($data['search_keywords'])){
                $keywords = explode(' ', $data['search_keywords']);
                $searchTermKeywords = array();
                foreach ($keywords as $word) {
                    $word                 = $this->db->escape($word);
                    $searchTermKeywords[] = "((title LIKE '%$word%') OR (ean LIKE '%$word%') OR (isbn LIKE '%$word%') OR (sku LIKE '%$word%') OR (search_keywords LIKE '%$word%'))";
                }
                $query .= ' AND '.implode(' AND ', $searchTermKeywords);
            }

            

            if(isset($data['price_min']) && !empty($data['price_min'])){
                if($data['price_min'] != 0 && $data['price_max']  != 0){
                    $query .= " AND p.selling_price BETWEEN ".$data['price_min']." AND ".$data['price_max'];
                }else if($data['price_min']  != 0 && $data['price_max'] == 0){
                    $query .= " AND p.selling_price > ".$data['price_min'];
                }else if($data['price_min']  == 0 && $data['price_max'] != 0){
                    $query .= " AND p.selling_price <= ".$data['price_max'];
                }
            }

            if(isset($data['featured']) && !empty($data['featured'])){
                $query .= " AND p.featured =".(int)$data['featured'];
            }

            if(isset($data['free_shipping']) && !empty($data['free_shipping'])){
                $query .= " AND p.free_shipping =".(int)$data['free_shipping'];
            }

            if(isset($data['new']) && !empty($data['new'])){
                $query .= " AND p.new =".(int)$data['new'];
            }

            $query .= " GROUP BY p.id";
            
            if(isset($data['sort'])){
                if($data['sort'] == 'price'){
                    $query .= " ORDER BY p.selling_price";
                }else{
                    $query .= " ORDER BY " . $data['sort'];
                }
            }else{
                $query .= " ORDER BY p.id";
            }
    
            if(isset($data['order'])){
                $query .= " ".$data['order'];
            }else{
                $query .= " DESC";
            }
    
            if (isset($data['offset']) || isset($data['limit'])) {
                if($data['offset'] < 0){
                    $data['offset'] = 0;
                }
    
                if($data['limit'] < 1){
                    $data['limit'] = 24;
                }
    
                $query .= " LIMIT " . (int)$data['offset'] . "," . (int)$data['limit'];
            }
            
            $stmt = $this->db->query($query);
            if($stmt->num_rows > 0) {
                foreach($stmt->rows as $row) { 
                    $categories = $this->getProductCategories($row['id']);
                    $brand = $this->getBrand($row['brand_id']);
                    $status = $row['status'];
                    if($status == 1) {
                        $status = '<span class="badge bg-success">Satışta</span>';
                    }else if($status == 0) {
                        $status = '<span class="badge bg-danger">Satışa Kapalı</span>';
                    }else {
                        $status = 'Bilinmiyor';
                    }
                    $item = array(
                        'id'                     => $row['id'],
                        'status'                 => $status,
                        'status_id'              => $row['status'],
                        'title'                  => $row['title'],
                        'image'                  => $row['image'],
                        'price'                  => $row['price'],
                        'discounted_price'       => $row['discounted_price'],
                        'discount_rate'          => $row['discount_rate'],
                        'selling_price'          => $row['selling_price'],
                        'free_shipping'          => $row['free_shipping'],
                        'stock_tracking'         => $row['stock_tracking'],
                        'stock_level'            => $row['stock_level'],
                        'stock_critical_level'   => $row['stock_critical_level'],
                        'new'                    => $row['new'],
                        'sku'                    => $row['sku'],
                        'sold'                   => $row['sold'],
                        'url'                    => $row['url'],
                        'categories'             => $categories,
                        'brand'                  => $brand != false ? $brand['title'] : '',
                        'is_available'           => $row['stock_tracking'] == 1 && $row['stock_level'] <=0 ? false : true,
                        'stock_status'           => $row['stock_tracking'] == 1 && $row['stock_level'] <=0 ? "out-of-stock" : ""
                    );
                    array_push($products, $item);
                }
            }

            return $products;
        }

        public function getTotalProducts($data = array()){
            $query = "SELECT COUNT(DISTINCT p.id) AS 'total' FROM {$this->db->prefix}products p LEFT JOIN {$this->db->prefix}product_to_category c ON c.product_id = p.id WHERE 1";
            if(isset($data['category_id']) && !empty($data['category_id'])){
                $sub_categories = $this->getSubCategoryIds($data['category_id']);
                if(!empty($sub_categories)){
                    $sub_categories = implode(",",$sub_categories);
                    $query .= " AND c.category_id IN (".(int)$data['category_id'].",".$sub_categories.")";
                }else{
                    $query .= " AND c.category_id =".(int)$data['category_id'];
                }
            }

            if(isset($data['status'])){
                if($data['status'] != ''){
                    $query .= " AND p.status =".$data['status'];
                }
            }

            if(isset($data['brand_id']) && !empty($data['brand_id'])){
                $query .= " AND p.brand_id =".(int)$data['brand_id'];
            }

            if(isset($data['search_keywords']) && !empty($data['search_keywords'])){
                $keywords = explode(' ', $data['search_keywords']);
                $searchTermKeywords = array();
                foreach ($keywords as $word) {
                    $word                 = $this->db->escape($word);
                    $searchTermKeywords[] = "((title LIKE '%$word%') OR (ean LIKE '%$word%') OR (isbn LIKE '%$word%') OR (sku LIKE '%$word%') OR (search_keywords LIKE '%$word%'))";
                }
                $query .= ' AND '.implode(' AND ', $searchTermKeywords);
            }

            if(isset($data['price_min']) && !empty($data['price_min'])){
                if($data['price_min'] != 0 && $data['price_max']  != 0){
                    $query .= " AND p.selling_price BETWEEN ".$data['price_min']." AND ".$data['price_max'];
                }else if($data['price_min']  != 0 && $data['price_max'] == 0){
                    $query .= " AND p.selling_price >= ".$data['price_min'];
                }else if($data['price_min']  == 0 && $data['price_max'] != 0){
                    $query .= " AND p.selling_price <= ".$data['price_max'];
                }
            }

            if(isset($data['featured']) && $data['featured'] != 0){
                $query .= " AND p.featured =".(int)$data['featured'];
            }

            if(isset($data['free_shipping']) && $data['free_shipping'] != 0){
                $query .= " AND p.free_shipping =".(int)$data['free_shipping'];
            }

            if(isset($data['new']) && $data['new'] != 0){
                $query .= " AND p.new =".(int)$data['new'];
            }
            
            $stmt = $this->db->query($query);
            if($stmt->num_rows > 0){
                $this->total_products      = $stmt->row['total'];
            }else{
                $this->total_products      = 0;
            }

            return $this->total_products;
        }

        public function getAdditionalImage($product_id){
            $images = array();
            $stmt = $this->db->query("SELECT image, alt_text FROM {$this->db->prefix}product_images WHERE product_id=:id LIMIT 1", array(':id'=>(int)$product_id));
            if($stmt->num_rows > 0) {
                foreach($stmt->rows as $row) {
                    $item = array(
                        'image'        => $row['image'],
                        'alt_text'     => $row['alt_text']
                    );
                    array_push($images, $item);
                }
            }
            return $images;
        }

        public function getProductBasePrice($product_id){
            $stmt = $this->db->query("SELECT price FROM {$this->db->prefix}products WHERE id=:id", array(':id'=>$product_id));
            if($stmt->num_rows > 0) {
                return $stmt->row['price'];
            }
            return 0;
        }

        public function getProductPrices($product_id){
            $product_prices = array();
            $stmt = $this->db->query("SELECT price, discounted_price, discount_rate, selling_price, buying_price FROM {$this->db->prefix}products WHERE id=:id", array(':id'=>$product_id));
            if($stmt->num_rows > 0) {
                foreach($stmt->rows as $row) {
                    $product_prices = array(
                        'price'             => $row['price'],
                        'discount_rate'     => $row['discount_rate'],
                        'selling_price'     => $row['selling_price'],
                        'discounted_price'  => $row['discounted_price'],
                        'buying_price'      => $row['buying_price']
                    );
                }
            }
            return $product_prices;
        }

        public function getProductDeliveryAreas($product_id){
            $delivery_areas = array();
            $stmt = $this->db->query("SELECT * FROM {$this->db->prefix}delivery_areas WHERE product_id=:id", array(':id'=>$product_id));
            if($stmt->num_rows > 0) {
                foreach($stmt->rows as $row) {
                    $delivery_areas[] = $row['city'];
                }
            }
            return $delivery_areas;
        }

        public function getCriticalStocs() {
            $products = array();
            $query = "SELECT * FROM {$this->db->prefix}products WHERE status=1 AND (stock_critical_level != 0 AND stock_critical_level>stock_level) OR stock_level<1 ORDER BY id DESC";
            
            $stmt = $this->db->query($query);
            if($stmt->num_rows > 0) {
                $products = $stmt->rows;
            }

            return $products;
        }

        public function getHitCounts($date = "", $limit = 10) {
            $products = array();

            if(isset($date) && !empty($date)){
                $query = "SELECT p.id, p.title, h.visit FROM {$this->db->prefix}products p LEFT JOIN {$this->db->prefix}hit_counter h ON p.id = h.element WHERE type='product' AND date ='".$date."' ORDER BY h.visit DESC LIMIT ". (int)$limit;
            }else{
                $query = "SELECT p.id, p.title, h.visit FROM {$this->db->prefix}products p LEFT JOIN (SELECT element, SUM(visit) AS 'visit' FROM {$this->db->prefix}hit_counter WHERE type='product' GROUP BY element) h ON p.id = h.element ORDER BY h.visit DESC LIMIT ". (int)$limit;
            }

            $stmt = $this->db->query($query);
            if($stmt->num_rows > 0) {
                $products = $stmt->rows;
            }

            return $products;
        }

        public function getUrl($type, $element) {
            $url = "#";
            $query = "SELECT url FROM {$this->db->prefix}urls WHERE url_type=:url_type AND element=:id";
            $result = $this->db->query($query, array(':id'=>$element, ':url_type'=>$type));
            if($result->num_rows > 0) {
                $url = $result->row['url'];
            }

            return $url;
        }
    }
?>