<?php

namespace Hura8\Components\Product\Model;

use Hura8\Components\Brand\Model\BrandModel;
use Hura8\Database\iConnectDB;
use Hura8\Interfaces\TableName;
use Hura8\Traits\ClassCacheTrait;


class ProductFilterModel
{

    use ClassCacheTrait;

    /* @var $db iConnectDB */
    protected $db;


    public function __construct() {
        $this->db = get_db('', ENABLE_DB_DEBUG);
    }


    public function findProductListIdMatchFilters(array $SQLConditionFromFilters) {
        list(
                $where_query,
                $current_page_id,
                $number_per_page,
                $ordering_clause
            ) = $SQLConditionFromFilters;

        $db_condition = join(" ", $where_query);

        $total_number = 0;
        $query = $this->db->runQuery("SELECT COUNT(`id`) AS total FROM ".TableName::PRODUCT."  WHERE 1 ".$db_condition." ");
        if($rs = $this->db->fetchAssoc($query)) {
            $total_number = $rs['total'];
        }

        $query = $this->db->runQuery("
                                SELECT * FROM ".TableName::PRODUCT." 
                                WHERE 1 ".$db_condition." 
                                ".$ordering_clause."
                                LIMIT ". ($current_page_id - 1) * $number_per_page .", ".$number_per_page."
                            ");

        $item_list = $this->db->fetchAll($query);

        return [$total_number, $item_list];
    }


    public function ratingList(array $list_product_ids) {

        if(!sizeof($list_product_ids)) return [];

        list($parameterized_ids, $bind_types) = create_bind_sql_parameter_from_value_list($list_product_ids, 'int');

        $query = $this->db->runQuery(
            "
                        SELECT `review_rate`, COUNT(`id`) as num
                        FROM ".TableName::PRODUCT." 
                        WHERE `id` IN (".$parameterized_ids.")
                        GROUP BY `review_rate`
                        ORDER BY review_rate ASC 
                    ",
            $bind_types,
            $list_product_ids
        );

        return $this->db->fetchAll($query);
    }


    public function categoryList(array $current_category_ids, array $list_product_ids) {
        if(!sizeof($list_product_ids)) {
            return [];
        }

        list($parameterized_ids, $bind_types) = create_bind_sql_parameter_from_value_list($list_product_ids, 'int');

        $query = $this->db->runQuery(
            "
                          SELECT `category_id`, COUNT(`item_id`) AS numPro
                          FROM ".TableName::PRODUCT_PER_CATEGORY."  
                          WHERE `item_id` IN (".$parameterized_ids.") 
                          GROUP BY `category_id` ",
            $bind_types,
            $list_product_ids
        );

        //lay ket qua dem
        $result_count = [];
        foreach ($this->db->fetchAll($query) as $rs ){
            $result_count[$rs['category_id']] = $rs['numPro'];
        }

        if(sizeof($result_count)) {

            $objProductCategoryModel = new ProductCategoryModel();

            $final_result = [];
            foreach ($objProductCategoryModel->getListByIds(array_keys($result_count)) as $rs ){

                $rs['count'] = $result_count[$rs['id']];
                $rs['is_selected'] = in_array($rs['id'], $current_category_ids);

                $final_result[] = $rs;
            }

            return $final_result;
        }

        return [];
    }


    public function brandList(array $current_brand_ids, array $list_product_ids) {

        if(!sizeof($list_product_ids)) {
            return [];
        }

        list($parameterized_ids, $bind_types) = create_bind_sql_parameter_from_value_list($list_product_ids, 'int');

        $query = $this->db->runQuery(
            "       SELECT `brand_id`, COUNT(`id`) AS total
                          FROM ".TableName::PRODUCT."  
                          WHERE `id` IN (".$parameterized_ids.") 
                          GROUP BY `brand_id` 
                          ORDER BY total DESC 
                          LIMIT 100",
            $bind_types,
            $list_product_ids
        );


        $neededList = array();
        foreach ( $this->db->fetchAll($query) as $result ) {
            $neededList[$result['brand_id']] = array(
                "id" 	=> $result['brand_id'] ,
                "count" => $result['total'] ,
            );
        }

        $brand_list_ids[] = array_keys($neededList);
        $result_list = array();

        if(sizeof($brand_list_ids)) {

            $objBrandModel = new BrandModel();

            foreach ( $objBrandModel->getListByIds($brand_list_ids) as $_brand ) {

                $item_count = (isset($neededList[$_brand['id']])) ? $neededList[$_brand['id']]['count'] : 0;

                $_brand['count'] = $item_count;
                $_brand["is_selected"] = in_array($_brand['id'], $current_brand_ids);


                $result_list[$_brand['id']] = $_brand;
            }
        }

        return array_values($result_list);
    }


    public function attributeList(array $current_filter_attribute_value_ids, array $current_category_ids, array $list_product_ids) {
        if(!sizeof($list_product_ids)) {
            return [];
        }

        $category_attribute_filter_array = $this->countNumProPerAttributeForCategory($current_category_ids, $list_product_ids);

        if(!sizeof($category_attribute_filter_array)) {
            return [];
        }

        $filter_box = array();

        foreach($category_attribute_filter_array as $attributeIdGroup){

            $attribute_value_array  = $attributeIdGroup['value_list'];
            $attribute_name         = $attributeIdGroup['name'];
            $attribute_selected     = 0;
            $filterList             = array();

            $count_num              = 0;

            foreach($attribute_value_array as $att_value_id => $rs_value){
                $att_value_name         = $rs_value["info"]["name"];
                $att_value_description  = $rs_value["info"]["description"];
                $count_pro              = $rs_value["pro_count"];
                $is_selected            = in_array($att_value_id, $current_filter_attribute_value_ids);
                if($is_selected) {
                    $attribute_selected     = 1;
                }

                unset($this_filter_query, $this_filter_col_value);

                //gioi han danh sach
                if($count_num > 50)  {
                    break;
                }

                $count_num++;

                $filterList[] = array(
                    "id"        => $att_value_id ,
                    "name"      => $att_value_name ,
                    'api_key'       => $rs_value["info"]["api_key"],
                    "description" => $att_value_description,
                    "count"     => $count_pro ,
                    "is_selected" => $is_selected,
                );
            }

            if($count_num > 0){
                $filter_box[] = [
                    "name"              => $attribute_name,
                    'filter_code'       => $attributeIdGroup['filter_code'],
                    "is_selected"       => $attribute_selected,
                    "value_list"        => $filterList ,
                ];
            }

        }

        return $filter_box;
    }


    protected function countNumProPerAttributeForCategory(array $current_category_ids, array $list_product_ids){

        if(!sizeof($list_product_ids)) {
            return [];
        }

        $category_attributes = $this->getAttributesForCategory($current_category_ids);
        $att_value_id_list = [];
        foreach ($category_attributes as $attribute) {
            $att_value_id_list = array_merge($att_value_id_list, array_keys($attribute['value_list']));
        }

        // count product per attribute-value-id
        $result_count = array();
        if(sizeof($att_value_id_list)) {
            $query = $this->db->runQuery("
                          SELECT 
                                pro_att.`attr_value_id`, 
                                COUNT(DISTINCT ".TB_PRODUCT_CATEGORY.".pro_id) AS numPro
                          FROM ".TB_PRODUCT_ATTRIBUTE." pro_att
                          LEFT JOIN ".TB_PRODUCT_CATEGORY." ON pro_att.pro_id = ".TB_PRODUCT_CATEGORY.".pro_id 
                          WHERE pro_att.pro_id IN (".join(',', $list_product_ids ).") 
                                AND pro_att.`attr_value_id` IN (".join(",", $att_value_id_list).")
                          GROUP BY pro_att.`attr_value_id` ");

            //lay ket qua dem
            foreach ($this->db->fetchAll($query) as $rs ){
                $result_count[$rs['attr_value_id']] = $rs['numPro'];
            }
        }

        $final_result = [];
        foreach ($category_attributes as $attribute) {
            $new_value_list = [];
            foreach ($attribute['value_list'] as $_value_id => $_value_info) {
                $pro_count = isset($result_count[$_value_id]) ? $result_count[$_value_id] : 0;
                if($pro_count) {
                    $_value_info['pro_count'] = $pro_count;
                    $new_value_list[$_value_id] = $_value_info;
                }
            }
            $attribute['value_list'] = $new_value_list;
            $final_result[] = $attribute;
        }

        return $final_result;
    }


    // 15-07-2020 get all attributes and values for a list of category
    protected function getAttributesForCategory(array $category_ids){

        if(!sizeof($category_ids)) {
            return array();
        }


        $final_result = array();//array to return

        $category_att_id_list = array();
        $attribute_array = array(); //keep & re-order attribute

        list($parameterized_ids, $bind_types) = create_bind_sql_parameter_from_value_list($category_ids, 'int');

        $query = $this->db->runQuery("
							SELECT `attr_id`, `ordering` FROM `idv_attribute_category`
							WHERE `category_id` IN (". $parameterized_ids .") AND `status` = 1 ", $bind_types, $category_ids);

        foreach ($this->db->fetchAll($query) as $rs ){
            $category_att_id_list[] = $rs['attr_id'];
            $attribute_array[$rs['attr_id']] = $rs['ordering'];
        }

        if(!sizeof($category_att_id_list)) {
            return array();
        }


        $att_value_id_list = array();
        $attribute_info = array();
        $attribute_att_value_item = array();
        $attribute_value_info = array();
        $query = $this->db->runQuery("
							SELECT 
								attval.id , 
								attval.attributeId , 
								attval.value , 
								attval.description , 
								attval.api_key , 
								attval.value_en ,
								att.attribute_code , 
								att.filter_code,
								att.name , 
								att.ordering
							FROM ".TB_ATTRIBUTE_VALUE." attval
							LEFT JOIN ".TB_ATTRIBUTE." att ON attval.attributeId = att.id
							WHERE att.id IN (".join(',', $category_att_id_list).") AND att.isSearch = 1 
							ORDER BY attval.ordering DESC ");

        foreach ($this->db->fetchAll($query) as $rs ){
            $att_value_id   = $rs['id'];
            $att_id         = $rs['attributeId'];

            $att_value_id_list[] = $att_value_id;

            $attribute_att_value_item[$att_id][] = $att_value_id; //in ordering

            $attribute_info[$att_id] = array(
                "id"        => $att_id,
                "name"      => $rs['name'],
                "code"      => $rs['attribute_code'],
                "filter_code" => $rs['filter_code'],
            );
            $attribute_value_info[$att_value_id] = array(
                "id"        => $att_value_id,
                "name"      => $rs['value'],
                "description"   => $rs['description'],
                "api_key"   => $rs['api_key'],
            );
        }

        //cho ra ket qua
        arsort($attribute_array); //sap xep thu tu cua attribute theo ordering

        foreach($attribute_array as $att_id => $att_order){
            $this_attribute_atr_value_item = array();
            if(isset($attribute_att_value_item[$att_id])) {
                foreach($attribute_att_value_item[$att_id] as $att_value_id){
                    $this_attribute_atr_value_item[$att_value_id] = array(
                        "id"    => $att_value_id,
                        "info"  => $attribute_value_info[$att_value_id],
                    );
                }
            }

            if(isset($attribute_info[$att_id])) {
                $info = $attribute_info[$att_id];
                $info['value_list'] = $this_attribute_atr_value_item;

                $final_result[] = $info;
            }
        }

        return $final_result;
    }


    public function priceList($current_max_price, $current_min_price, array $list_product_ids, array $category_price_range) {

        if(!sizeof($list_product_ids)) {
            return [];
        }

        $total_prices  = sizeof($category_price_range);
        if( ! $total_prices ){
            return [];
        }

        //pad head and tail with 0
        array_unshift($category_price_range, 0);
        $category_price_range[] = 0;

        $result = array();
        $queries = [];

        for($i = 0; $i <= $total_prices ; $i++){

            $range_key = md5($category_price_range[$i] .'-'. $category_price_range[$i+1] );
            $queries[] = $this->buildRangeQuery($list_product_ids, $range_key, $category_price_range[$i], $category_price_range[$i+1]);

            $min_price      = intval($category_price_range[$i]);
            $max_price      = intval($category_price_range[$i+1]);
            $is_selected    = ($max_price == $current_max_price && $min_price == $current_min_price );

            $result[$range_key] = array(
                "min"       => $min_price ,
                "max"       => $max_price,
                "count"     => 0 ,
                "is_selected" => ($is_selected) ? 1 : 0 ,
            );

        }

        $query = $this->db->runQuery(" SELECT `range_key`, `total` FROM ( ".join(' UNION ALL ', $queries)." ) AS tmp");
        foreach ($this->db->fetchAll($query) as $rs) {
            $result[$rs['range_key']]['count'] = $rs['total'];
        }

        // get only range with product-count > 0
        return array_values(array_filter( $result , function ($item){
            return $item['count'] > 0;
        }));
    }


    private function buildRangeQuery(array $list_product_ids, $range_key, $minPrice, $maxPrice){

        $minPrice = (int) $minPrice;
        $maxPrice = (int) $maxPrice;
        if($minPrice < 10) $minPrice = 10;

        $new_price_range = " AND `id` IN (".join(',', $list_product_ids ).") ";

        if($maxPrice > 0 && $minPrice > 0){
            $new_price_range .= " AND ( `price` BETWEEN '".$minPrice."' AND '".$maxPrice."' ) ";
        }elseif($maxPrice > 0){
            $new_price_range .= " AND `price` < '".$maxPrice."' ";
        }elseif($minPrice > 0 && $maxPrice == 0){
            $new_price_range .= " AND `price` >='".$minPrice."' ";
        }

        return " SELECT '".$range_key."' AS `range_key`, COUNT(`id`) AS total 
                 FROM ".TableName::PRODUCT." 
                 WHERE 1 ".$new_price_range;
    }


}
