<?php
class ModelCatalogCategory extends Model {

    public function findSameCategory($category_id, $text){

        $cache_name = $category_id . '_' . $text;
        //if($this->cache->cache_get($this->session->data['language'] . '_category_' . $cache_name, 'same_categories/'))
            //return $this->cache->cache_get($this->session->data['language'] . '_category_' . $cache_name, 'same_categories/');

        $query = $this->db->query("SELECT c.category_id, cd.name, cd.href, c.parent_id FROM ps_category c 
        LEFT JOIN ps_category_description cd ON (c.category_id = cd.category_id) 
        WHERE c.status = '1' AND c.parent_id = '". $category_id ."' AND cd.name LIKE '%". $this->db->escape($text) ."%' LIMIT 1 ");

       

        //$this->cache->cache_set($this->session->data['language'] . '_category_' . $cache_name, $query->row, 'same_categories/');
        //return $this->cache->cache_get($this->session->data['language'] . '_category_' . $category_id, 'same_categories/');

        return $query->row;
    }

    public function findSameCategories($category_id, $text){

        if (strpos($text, '/') !== false) {
            $text = str_replace('/', '_', $text);
        }

        $cache_name = $category_id . '_' . $text;
        if($this->cache->cache_get($this->session->data['language'] . '_categories_' . $cache_name, 'same_categories/'))
            return $this->cache->cache_get($this->session->data['language'] . '_categories_' . $cache_name, 'same_categories/');

        $ek_sql = '';
        if($category_id){
            $ek_sql = " AND c.parent_id IN (SELECT category_id FROM ps_category WHERE parent_id = '". $category_id ."' )";
        }

        $query = $this->db->query("SELECT c.category_id, cd.name, c.parent_id FROM ps_category c 
        LEFT JOIN ps_category_description cd ON (c.category_id = cd.category_id) 
        WHERE c.status = '1' AND cd.name LIKE '%". $this->db->escape($text) ."%' ". $ek_sql ." GROUP BY c.category_id ");

        $this->cache->cache_set($this->session->data['language'] . '_categories_' . $cache_name, $query->rows, 'same_categories/');
        //return $this->cache->cache_get($this->session->data['language'] . '_categories_' . $category_id, 'same_categories/');

        return $query->rows;
    }

	public function getCategory($category_id) {

        /*if(isset($this->session->data['user_id']) AND $category_id == 4994){
            $query = $this->db->query("SELECT DISTINCT * FROM ps_category c 
            LEFT JOIN ps_category_description cd ON (c.category_id = cd.category_id) 
            LEFT JOIN ps_category_to_store c2s ON (c.category_id = c2s.category_id) 
            WHERE c.category_id = '" . (int)$category_id . "' AND cd.language_id = '" . (int)$this->config->get('config_language_id') . "' AND c2s.store_id = '" . (int)$this->config->get('config_store_id') . "' ");
            return $query->row;

        }*/

	    if($this->cache->cache_get($this->session->data['language'] . '_category_info' . $category_id, 'categories/'))
	        return $this->cache->cache_get($this->session->data['language'] . '_category_info' . $category_id, 'categories/');


		$query = $this->db->query("SELECT DISTINCT * FROM ps_category c 
        LEFT JOIN ps_category_description cd ON (c.category_id = cd.category_id) 
        LEFT JOIN ps_category_to_store c2s ON (c.category_id = c2s.category_id) 
        WHERE c.category_id = '" . (int)$category_id . "' AND cd.language_id = '" . (int)$this->config->get('config_language_id') . "' AND c2s.store_id = '" . (int)$this->config->get('config_store_id') . "' AND c.status = '1'");

        $this->cache->cache_set($this->session->data['language'] . '_category_info' . $category_id, $query->row, 'categories/');
		return $this->cache->cache_get($this->session->data['language'] . '_category_info' . $category_id, 'categories/');
        //return $query->row;

	}

    public function getCategoryPath($category_id) {

        if($this->cache->cache_get('category_path_' . $this->session->data['language'] .'_'. $category_id, 'categories/'))
            return $this->cache->cache_get('category_path_' . $this->session->data['language'] .'_'. $category_id, 'categories/');

        $query = $this->db->query("SELECT cd.name, cd.href ,cp.path_id AS category_id FROM ps_category_path cp 
        LEFT JOIN ps_category_description cd ON (cp.path_id = cd.category_id) WHERE cp.category_id = '" . (int)$category_id . "' AND cd.language_id = 1 ORDER BY cp.level ");


        $this->cache->cache_set('category_path_' . $this->session->data['language'] .'_' . $category_id, $query->rows, 'categories/');
        return $query->rows;
    }

    public function getTopCategoryByCategoryId($category_id){

        $query = $this->db->query("SELECT path_id FROM ps_category_path WHERE category_id = '". (int)$category_id ."' AND level = 0;");
        if(isset($query->row['path_id'])){
            return $query->row['path_id'];
        }else{
            return false;
        }

    }

    public function get_hide_cat($cat_id)
    {
        $query = $this->db->query("select hide_cat from " . DB_PREFIX . "category where category_id='".$cat_id."'");
        if(isset($query->row['hide_cat'])){
            return $query->row['hide_cat'];
        }else{
            return 0;
        }
    }

	public function getCategories($parent_id = 0) {

        if($this->cache->cache_get('categories_' . $this->session->data['language'] .'_'. $parent_id, 'categories/'))
            return $this->cache->cache_get('categories_' . $this->session->data['language'] .'_'. $parent_id, 'categories/');

		$query = $this->db->query("SELECT * FROM ps_category c 
        LEFT JOIN ps_category_description cd ON (c.category_id = cd.category_id) 
        LEFT JOIN ps_category_to_store c2s ON (c.category_id = c2s.category_id) 
        WHERE c.parent_id = '" . (int)$parent_id . "' AND cd.language_id = '" . (int)$this->config->get('config_language_id') . "' AND c2s.store_id = '" . (int)$this->config->get('config_store_id') . "'  AND c.status = '1' ORDER BY c.sort_order, c.date_added DESC"); // LCASE(cd.name) yerine c.date_added DESC

        $this->cache->cache_set('categories_' . $this->session->data['language'] .'_' . $parent_id, $query->rows, 'categories/');
        return $query->rows;

    }

    public function getCategoryByProductId($product_id){

        return $this->db->query("SELECT c.category_id, cd.href FROM ps_product_to_category p2c
        LEFT JOIN ps_category c ON (p2c.category_id = c.category_id)
        LEFT JOIN ps_category_description cd ON (c.category_id = cd.category_id)
        WHERE p2c.product_id = '". $product_id ."' ")->row;

    }

	public function getCategoryFilters($category_id) {

		$implode = array();

		$query = $this->db->query("SELECT filter_id FROM " . DB_PREFIX . "category_filter WHERE category_id = '" . (int)$category_id . "'");

		foreach ($query->rows as $result) {
			$implode[] = (int)$result['filter_id'];
		}

		$filter_group_data = array();
		if ($implode) {
			$filter_group_query = $this->db->query("SELECT DISTINCT f.filter_group_id, fgd.name, fg.sort_order FROM " . DB_PREFIX . "filter f LEFT JOIN " . DB_PREFIX . "filter_group fg ON (f.filter_group_id = fg.filter_group_id) LEFT JOIN " . DB_PREFIX . "filter_group_description fgd ON (fg.filter_group_id = fgd.filter_group_id) WHERE f.filter_id IN (" . implode(',', $implode) . ") AND fgd.language_id = '" . (int)$this->config->get('config_language_id') . "' GROUP BY f.filter_group_id ORDER BY fg.sort_order, LCASE(fgd.name)");

			foreach ($filter_group_query->rows as $filter_group) {
				$filter_data = array();
				$filter_query = $this->db->query("SELECT DISTINCT f.filter_id, fd.name FROM " . DB_PREFIX . "filter f LEFT JOIN " . DB_PREFIX . "filter_description fd ON (f.filter_id = fd.filter_id) WHERE f.filter_id IN (" . implode(',', $implode) . ") AND f.filter_group_id = '" . (int)$filter_group['filter_group_id'] . "' AND fd.language_id = '" . (int)$this->config->get('config_language_id') . "' ORDER BY f.sort_order, LCASE(fd.name)");

				foreach ($filter_query->rows as $filter) {
					$filter_data[] = array(
						'filter_id' => $filter['filter_id'],
						'name'      => $filter['name']
					);
				}

				if ($filter_data) {
					$filter_group_data[] = array(
						'filter_group_id' => $filter_group['filter_group_id'],
						'name'            => $filter_group['name'],
						'filter'          => $filter_data
					);
				}
			}
		}

		return $filter_group_data;

	}

	public function getCategoryLayoutId($category_id) {

		$query = $this->db->query("SELECT * FROM " . DB_PREFIX . "category_to_layout WHERE category_id = '" . (int)$category_id . "' AND store_id = '" . (int)$this->config->get('config_store_id') . "'");
		if ($query->num_rows) {
			return $query->row['layout_id'];
		} else {
			return 0;
		}

	}

	public function getTotalCategoriesByCategoryId($parent_id = 0) {
		$query = $this->db->query("SELECT COUNT(*) AS total FROM " . DB_PREFIX . "category c LEFT JOIN " . DB_PREFIX . "category_to_store c2s ON (c.category_id = c2s.category_id) WHERE c.parent_id = '" . (int)$parent_id . "' AND c2s.store_id = '" . (int)$this->config->get('config_store_id') . "' AND c.status = '1'");
		return $query->row['total'];
	}

	public function getSubCategory($parent_id = 0, $language_id){
        
        if($this->cache->cache_get($this->session->data['language'] . '_sub_category_' . $parent_id, 'categories/'))
            return $this->cache->cache_get($this->session->data['language'] . '_sub_category_' . $parent_id, 'categories/');

        $query = $this->db->query("SELECT c.category_id, c.date_added, cd.name, c.column, c.image, c.parent_id, c.top, cd.href FROM ps_category c 
        LEFT JOIN ps_category_description cd ON (c.category_id = cd.category_id) 
        LEFT JOIN ps_category_to_store c2s ON (c.category_id = c2s.category_id) 
        WHERE c.parent_id = '" . (int)$parent_id . "' AND 
        cd.language_id = '" . (int)$language_id . "' 
        AND c2s.store_id = '" . (int)$this->config->get('config_store_id') . "'  
        AND c.status = '1' ORDER BY c.sort_order, c.date_added DESC");  // LCASE(cd.name) yerine c.date_added DESC

        $this->cache->cache_set($this->session->data['language'] . '_sub_category_' . $parent_id, $query->rows, 'categories/');
        return $query->rows;
        //return $query->rows;

    }

	private function getTotalProductsCount($category_id){
        return (int)$this->db->query("SELECT product_id FROM ps_product_to_category WHERE category_id = '" . (int)$category_id . "' GROUP BY product_id ")->num_rows;
    }

    public function getJsonCategory(){
        $categories = $this->cache->cache_get($this->session->data['language']. '-category', 'category/');

        if(!$categories){
            $categories = $this->createCache();
        }
        return $categories;
    }

    public function getSubCategoryHeaderCache($parent_id = 0, $language_id){
        
        return $this->db->query("SELECT * FROM ps_category c 
            LEFT JOIN ps_category_description cd ON (c.category_id = cd.category_id) 
            WHERE c.parent_id = '" . (int)$parent_id . "' AND 
            cd.language_id = 1
            AND c.status = 1 ORDER BY c.sort_order, c.date_added DESC ")->rows;

    }

    public function createCache(){

        $this->load->model('tool/image');
        $this->load->model('catalog/product');
        $this->load->model('localisation/language');

        $categories = array();

        if ($this->request->server['HTTPS']) {
			$config_url = $this->config->get('config_ssl') . 'image/';
		} else {
			$config_url = $this->config->get('config_url') . 'image/';
		}

        $languages = $this->model_localisation_language->getLanguages();

        foreach ($languages as $l_key => $language){
            $categories = array();
            if($language['status']) {
                foreach ($this->getSubCategoryHeaderCache(0, $language['language_id']) as $key1 => $c_data) {
                    if ($c_data['top']) {
                        $category2 = array();
                        $categories2_data = $this->getSubCategoryHeaderCache($c_data['category_id'], $language['language_id']);
                        foreach ($categories2_data as $key2 => $c2_data) {
                            if ($c2_data['top']) {
                                $category3 = array();
                                $categories3_data = $this->getSubCategoryHeaderCache($c2_data['category_id'], $language['language_id']);
                                foreach ($categories3_data as $key3 => $c3_data) {
                                    if ($c3_data['top']) {
                                        // $category4 = array();
                                        // $categories4_data = $this->getSubCategoryHeaderCache($c3_data['category_id'], $language['language_id']);
                                        // foreach ($categories4_data as $key4 => $c4_data) {
                                        //     if ($c4_data['top']) {
                                        //         $category4[] = array(
                                        //             'name' => $c4_data['name'],
                                        //             'href' => $c4_data['href'],
                                        //             'image' => isset($c4_data['image']) ? $this->model_tool_image->resize($c4_data['image'], $this->config->get($this->config->get('config_theme') . '_image_cart_width'), $this->config->get($this->config->get('config_theme') . '_image_cart_height')) : false,
                                        //             'top' => 1,
                                        //             'column' => $c4_data['column'] ? $c4_data['column'] : 1,
                                        //             'count' => $this->config->get('config_product_count') ? $this->getTotalProductsCount($c4_data['category_id']) : '',
                                        //         );
                                        //     }
                                        // }
                                        $category3[] = array(
                                            'name' => $c3_data['name'],
                                            'href' => $c3_data['href'],
                                            'category_id' => $c3_data['category_id'],
                                            'image' => '',
                                            //'image' => isset($c3_data['image']) ? $this->model_tool_image->resize($c3_data['image'], $this->config->get($this->config->get('config_theme') . '_image_cart_width'), $this->config->get($this->config->get('config_theme') . '_image_cart_height')) : false,
                                            'children' => [],
                                            'top' => 1,
                                            'column' => $c3_data['column'] ? $c3_data['column'] : 1,
                                            'count' => '',
                                        );
                                    }
                                }
                                $category2[] = array(
                                    'name' => $c2_data['name'],
                                    'href' => $c2_data['href'],
                                    'category_id' => $c2_data['category_id'],
                                    'image' => '',
                                    //'image' => isset($c2_data['image']) ? $this->model_tool_image->resize($c2_data['image'], $this->config->get($this->config->get('config_theme') . '_image_cart_width'), $this->config->get($this->config->get('config_theme') . '_image_cart_height')) : false,
                                    'top' => 1,
                                    'children' => $category3,
                                    'column' => $c2_data['column'] ? $c2_data['column'] : 1,
                                );
                            }
                        }
                        $categories[] = array(
                            'name' => $c_data['name'],
                            'href' => $c_data['href'],
                            'category_id' => $c_data['category_id'],
                            'image' => '',
                            //'image' => isset($c_data['image']) ? $this->model_tool_image->resize($c_data['image'], $this->config->get($this->config->get('config_theme') . '_image_cart_width'), $this->config->get($this->config->get('config_theme') . '_image_cart_height')) : false,
                            'no_cache_image' => $c_data['image'] ? $config_url . $c_data['image'] : '',
                            'children' => $category2,
                            'top' => 1,
                            'column' => $c_data['column'] ? $c_data['column'] : 1,
                        );
                        $this->cache->cache_set($l_key . '-category', $categories, 'category/');
                    }
                }
            }
        }


        return $this->cache->cache_get($this->session->data['language']. '-category', 'category/');

        // BİLAL 26/02/2019


    }

    private function categoryProductRelated($category_id){

		if ($this->config->get('config_category_product_related')) {
			$this->load->model('catalog/category');
			$this->load->model('catalog/product');
			$products = array();
			$results = $this->getCategoryProductRelated($category_id);

			foreach ($results as $result) {
                $product = $this->model_catalog_product->getProduct($result);
                if($product){
                    $image = '';
                    if ($product['image']) {
                        $image = $this->model_tool_image->resize($product['image'], $this->config->get($this->config->get('config_theme') . '_image_product_width'), $this->config->get($this->config->get('config_theme') . '_image_product_height'));
                    } else {
                        $image = $this->model_tool_image->resize('placeholder.png', $this->config->get($this->config->get('config_theme') . '_image_product_width'), $this->config->get($this->config->get('config_theme') . '_image_product_height'));
                    }

                    if ($this->customer->isLogged() || !$this->config->get('config_customer_price')) {
                        $price = $this->currency->format($this->tax->calculate($product['price'], $product['tax_class_id'], $this->config->get('config_tax')), $this->session->data['currency']);
                    } else {
                        $price = false;
                    }


                    if ((float)$product['special']) {
                        $special = $this->currency->format($this->tax->calculate($product['special'], $product['tax_class_id'], $this->config->get('config_tax')), $this->session->data['currency']);
                    } else {
                        $special = false;
                    }


                    if ($this->config->get('config_tax')) {
                        $tax = $this->currency->format((float)$product['special'] ? $product['special'] : $product['price'], $this->session->data['currency']);
                    } else {
                        $tax = false;
                    }

                    $products[] = array(
                        'product_id' => $product['product_id'],
                        'quantity' => $product['quantity'],
                        'manufacturer' => $product['manufacturer'],
                        'thumb' => $image,
                        'name' => $product['name'],
                        'description' => utf8_substr(strip_tags(html_entity_decode($product['description'], ENT_QUOTES, 'UTF-8')), 0, $this->config->get($this->config->get('config_theme') . '_product_description_length')) . '..',
                        'price' => $price,
                        'special' => $special,
                        'tax' => $tax,
                        'rating' => $product['rating'],
                        'minimum' => $product['minimum'] > 0 ? $product['minimum'] : 1,
                        'href' => $this->url->link('product/product', '&product_id=' . $product['product_id'], true),
                        'percentsaving' => $product['special'] && $product['price'] ? round((($product['price'] - $product['special']) / $product['price']) * 100, 0) : '',
                    );
                }
			}

			return $products;
		}else {
			return false;
		}

	}

    public function getBestSellerProductsByCategory($category_id){

        if($this->cache->cache_get($this->session->data['language'] . '_BestSellerProductsByCategory_' . $category_id, 'categories/'))
            return $this->cache->cache_get($this->session->data['language'] . '_BestSellerProductsByCategory_' . $category_id, 'categories/');

        $product_data = array();

        $query = $this->db->query("SELECT p.product_id FROM ps_product p 
            LEFT JOIN ps_product_to_category p2c ON (p.product_id = p2c.product_id)
            WHERE p.status = '1' AND p.product_showcase = '1' AND p.date_available <= NOW() AND p2c.category_id = '". (int)$category_id ."' ORDER BY p.sort_order LIMIT 4 ");

        $this->load->model('catalog/product');

        foreach ($query->rows as $result) {
            $product_data[$result['product_id']] = $this->model_catalog_product->getProduct($result['product_id']);
        }

        $this->cache->cache_set($this->session->data['language'] . '_BestSellerProductsByCategory_' . $category_id, $product_data, 'categories/');
        return $this->cache->cache_get($this->session->data['language'] . '_BestSellerProductsByCategory_' . $category_id, 'categories/');
        //return $product_data;

    }

    public function getProductToCategoryFilters($category_id, $filter_group_id)
    {
        $query = $this->db->query("SELECT pf.filter_id, f.image, f.sub_image, fd.name, f.sort_order FROM ps_product_filter pf 
            LEFT JOIN ps_filter f ON (pf.filter_id = f.filter_id)
            LEFT JOIN ps_filter_description fd ON (f.filter_id = fd.filter_id)
            LEFT JOIN ps_product_to_category p2c ON (pf.product_id = p2c.product_id) 
            WHERE p2c.category_id = '". (int)$category_id ."' AND f.image != '' AND f.filter_group_id = '". (int)$filter_group_id ."' GROUP BY f.filter_id ORDER BY f.sort_order  ");

        return $query->rows;

    }

    public function getFilterInfo($filter_id)
    {
        $query = $this->db->query("SELECT * FROM ps_filter f LEFT JOIN ps_filter_description fd ON (f.filter_id = fd.filter_id) WHERE f.filter_id = '". (int)$filter_id ."' AND fd.language_id = 1 ");
        return $query->row;
    }

    public function getPreviewFilters($filter_group_id)
    {
        $query = $this->db->query("SELECT filter_id FROM ps_filter WHERE filter_group_id = '". (int)$filter_group_id ."' AND preview = 1 ");
        return $query->rows;

    }





}