<?php

use system\library\marketplace\MktPriceAdjustments;

final class DModelmercadolivremeliproduto extends Model
{

    private $mktPriceAdjustments;

    public function __construct($registry)
    {
        parent::__construct($registry);

        $this->mktPriceAdjustments = new MktPriceAdjustments($registry, ['tipo_integracao' => Type_MarketPlace::Tp_Mercadolivre]);
    }


    public function getTypeShippingValidate($value)
    {

        $sql = "SELECT status FROM `" . DB_PREFIX . "mercadolivre_type_shipping` WHERE code = '" . $value . "' ";

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

        if ($query->rows) {
            return $query->row['status'];
        } else {
            return false;
        }
    }

    public function getMktProductGrid($product_id)
    {
        $sql = "select mkt_variation_id from oc_marketplace_product_variation where product_id = $product_id";
        $query = $this->db->query($sql);
        return $query->rows ? $query->row['mkt_variation_id'] : false;
    }

    public function getAdtionalFieldsML($product, $type_product)
    {
        $param = array();
        $param['mercaBuyMode'] = Type_MercadoLivreMode::Tp_Buy_it_now;
        $param['mercaListType'] = (empty($product['mercaListType'])) ? $this->config->get('mercadolivre_mercaListType') : $product['mercaListType'];
        $param['mercaCondition'] = (empty($product['mercaCondition'])) ? $this->config->get('mercadolivre_mercaCondition') : $product['mercaCondition'];
        $param['mercaShipping'] = (empty($product['mercaShipping'])) ? $this->config->get('mercadolivre_shipping_alter') : $product['mercaShipping'];
        $param['mercaShippingFree'] = (empty($product['mercaShippingFree'])) ? $this->config->get('mercadolivre_shipping_free') : $product['mercaShippingFree'];
        $param['mercaId'] = $product['product_marketId'];
        $param['meli_price_ajust'] = 0;
        $param['meli_status'] = $product['mkt_status'];

        if ((empty($product['mercaId_category'])) && (empty($product['product_marketId']))) {
            if ($this->config->get('mercadolivre_category_auto')) {
                $result = $this->mercadolivre->getCategoryAuto($product['name']);
                $mercadolivre_category = ($result && (isset($result[0]->category_id))) ? $result[0]->category_id : $this->config->get('mercadolivre_mercaId');
            } else {
                $mercadolivre_category = $this->config->get('mercadolivre_mercaId');
            }
            $param['category_id'] = $mercadolivre_category;
        } else {
            $param['category_id'] = $product['mercaId_category'];
        }

        $param['meli_price_ajust'] = $this->mktPriceAdjustments->computedAdjustmentTotal($product['table_price'], $product['marketplace_price_id']);

        /*FIM: Função nova de acrescimo/desconto para anúncio no mercado livre utilizando tabela de preço e multiplas configurações de acrescimo/desconto*/

        if (!empty($product['mercaWarranty']) && !empty($product['mercaWarrantyType']) && !empty($product['mercaWarrantyTypeTime'])) {
            $sale_terms = array();
            $sale_terms[] = array(
                "id" => "WARRANTY_TYPE",
                "value_id" => $product['mercaWarrantyType']
            );
            $sale_terms[] = array(
                "id" => "WARRANTY_TIME",
                "value_name" => $product['mercaWarranty'] . ' ' . Type_TempoGarantia::getNickFromValue($product['mercaWarrantyTypeTime'])
            );
            $param['sale_terms'] = $sale_terms;
        } else {
            //retrocompatibilidade
            $param['mercaWarranty'] = (empty($product['mercaWarranty'])) ? $this->config->get('mercadolivre_mercaWarranty') : $product['mercaWarranty'];
        }


        $attributes_notvariable = array();
        //Atributos dinamicos sem variaçõpes....
        if ($this->config->get('mercadolivre_attr_status')) {

            $attr_notvariable = $this->getAttrNotVariable($product['product_key_id'], $type_product, Type_DsimDnao::Tp_Nao);

            $enviamarca_sistema = true;
            foreach ($attr_notvariable as $value) {

                if (($value['attribute_id'] == Type_MercadoLivreIDField::Tp_Marca) && ($value['value_fixed'] == Type_DsimDnao::Tp_Sim)) {
                    $enviamarca_sistema = false;
                }

                if ($value['attribute_value_id']) {

                    if($value['attribute_id'] == 'SIZE_GRID_ID'){
                        $attributes_notvariable[] = array(
                            'id' => $value['attribute_id'],
                            'value_name' => ($value['attribute_value_na']) ? '-1' : $value['attribute_value_id']
                        );
                    }else{
                        $attributes_notvariable[] = array(
                            'id' => $value['attribute_id'],
                            'value_id' => ($value['attribute_value_na']) ? '-1' : $value['attribute_value_id'],
                            'value_name' => ($value['attribute_value_na']) ? null : $value['attribute_value'] . ' ' . $value['attribute_unit_value']
                        );
                    }
                } else {
                    $attributes_notvariable[] = array(
                        'id' => $value['attribute_id'],
                        'value_id' => ($value['attribute_value_na']) ? '-1' : $value['attribute_value_id'],
                        'value_name' => ($value['attribute_value_na']) ? null : $value['attribute_value'] . ' ' . $value['attribute_unit_value']
                    );
                }

            }

            if (($enviamarca_sistema) && (!is_null($product['manufacturer_name']))) {
                $attributes_notvariable[] = array(
                    'id' => Type_MercadoLivreIDField::Tp_Marca,
                    'value_id' => ($product['manufacturer_name']) ? '' : '-1',
                    'value_name' => ($product['manufacturer_name']) ? $product['manufacturer_name'] : null,
                );
            }

            //atributos automáticos pelo sistema...
            if (($product['codigo_barra'] != "") && (!empty($product['codigo_barra'])) && (ftr_validacao::validaTipo($type_product, Type_ProdutoMkt::Tp_MktProd_Normal))) {
                // antes era variável entre GTIN, EAN ou UPC, agora foi unificado como GTIN
                $tipocod = 'GTIN';

                foreach ($attributes_notvariable as $key => $value) {
                    if ($value['id'] == 'GTIN') {
                        $keyGtin = $key;
                    }
                }

                if (in_array(substr($product['codigo_barra'], 0, 3), range(200, 299))) {
                    $gtinAttr = array(
                        'id' => $tipocod,
                        'value_id' => null
                    );
                } else {
                    $gtinAttr = array(
                        'id' => $tipocod,
                        'value_name' => $product['codigo_barra']
                    );
                }

                if (!empty($keyGtin)) {
                    $attributes_notvariable[$keyGtin] = $gtinAttr;
                } else {
                    $attributes_notvariable[] = $gtinAttr;
                }
            }
        }

        if (sizeof($attributes_notvariable) > 0) {
            $param['attributes'] = $attributes_notvariable;
        }

        $shipping = array();
        if (($this->mercadolivre->isShippingAllowed($param['category_id'])) || ($param['mercaShipping'] == Type_MercadoLivreModoEnvio::Tp_NotSpecified)) {
            $shipping['mode'] = 'not_specified';

            if ($product['mercaShippingFree'] == Type_Status::Tp_Habilitado) {
                $shipping['free_shipping'] = true;
            } else {
                $shipping['free_shipping'] = false;
            }

            $shipping['methods'] = array();
        } else {

            $TypeShipping = $this->getTypeShippingValidate($param['mercaShipping']);

            if (!$TypeShipping) {
                throw new exception("O tipo de frete selecionado não esta habilitado para o vendedor no Mercado Livre");
            }

            $mercadolivre_shipping_alter = empty($product['mercaShipping']) ? $param['mercaShipping'] : $product['mercaShipping'];

            if ($mercadolivre_shipping_alter == Type_MercadoLivreModoEnvio::Tp_MercadoEnvio10 && $product['length'] && $product['width'] && $product['height'] && $product['weight']) {
                $shipping = array();
                $shipping['mode'] = 'me1';

                //adicionei aqui pra fazer o uso do campo em tela, porem esta opcao esta sempre bloqueada, entao esta apenas pra nao fica desatualizado
                if ($product['mercaShippingFree'] == Type_Status::Tp_Habilitado) {
                    $shipping['free_shipping'] = true;
                } else {
                    $shipping['free_shipping'] = false;
                }

                $shipping['methods'] = array();
                $shipping['local_pick_up'] = false;
            }

            if ($mercadolivre_shipping_alter == Type_MercadoLivreModoEnvio::Tp_MercadoEnvio11 && $product['length'] && $product['width'] && $product['height'] && $product['weight']) {
                $shipping = array();
                $shipping['mode'] = 'me1';

                //adicionei aqui pra fazer o uso do campo em tela, porem esta opcao esta sempre bloqueada, entao esta apenas pra nao fica desatualizado
                if ($product['mercaShippingFree'] == Type_Status::Tp_Habilitado) {
                    $shipping['free_shipping'] = true;
                } else {
                    $shipping['free_shipping'] = false;
                }

                $shipping['methods'] = array();
                $shipping['local_pick_up'] = true;
            }

            if ($mercadolivre_shipping_alter == Type_MercadoLivreModoEnvio::Tp_MercadoEnvio20 && $product['length'] && $product['width'] && $product['height'] && $product['weight']) {
                $shipping = array();
                $shipping['mode'] = 'me2';
                $shipping['local_pick_up'] = false;
                if ($product['mercaShippingFree'] == Type_Status::Tp_Habilitado) {
                    $shipping['free_shipping'] = true;
                    $shipping['methods'][] = array('id' => 100009, 'rule' => array('free_mode' => 'country', 'value' => null));
                } else {
                    $shipping['free_shipping'] = false;
                    $shipping['methods'] = array();
                }
            }

            if ($mercadolivre_shipping_alter == Type_MercadoLivreModoEnvio::Tp_MercadoEnvio21 && $product['length'] && $product['width'] && $product['height'] && $product['weight']) {
                $shipping = array();
                $shipping['mode'] = 'me2';
                $shipping['local_pick_up'] = true;
                if ($product['mercaShippingFree'] == Type_Status::Tp_Habilitado) {
                    $shipping['free_shipping'] = true;
                    $shipping['methods'][] = array('id' => 100009, 'rule' => array('free_mode' => 'country', 'value' => null));
                } else {
                    $shipping['free_shipping'] = false;
                    $shipping['methods'] = array();
                }
            }

        }

        $param['shipping'] = $shipping;
        return $param;
    }

    public function setWarningsMeli(MktProduto $produto)
    {
        $shipping = $produto->getMktRetorno()->getParam('shipping');
        $shipping_mode = $this->mercadolivre->getShippingType($shipping->mode, $shipping->local_pick_up);
        $free_shipping = (isset($shipping->free_shipping) && ($shipping->free_shipping)) ? 1 : 0;
        $custom_tilte = $produto->getCustomTitle();

        if ($produto->getParam('mercaShipping') != $shipping_mode) {
            $this->session->data['mkt_warning'][$produto->getProductid()] = 'A opção de Tipo de Frete foi alterada devido a regras do mercado livre';
        }

        if (($produto->getParam('mercaShippingFree') != $free_shipping) && ($free_shipping)) {
            $this->session->data['mkt_warning'][$produto->getProductid()] = 'A opção de Frete Grátis foi habilitada no anúncio pelas políticas do mercado livre';
            //https://www.mercadolivre.com.br/ajuda/quanto-custa-vender-um-produto_1338
        }

        if ((isset($custom_tilte)) && (!empty($custom_tilte)) && (strtolower($custom_tilte) != strtolower($produto->getMktRetorno()->getmktName()))) {
            $this->session->data['mkt_warning'][$produto->getProductid()] = 'O título do anuncio foi alterado devido a regras do mercado livre';
        }

    }

    public function getAttrNotVariable($product_id, $type_product, $value_fixed = null)
    {
        $sql = 'select * from oc_mercadolivre_prod_attr where tipo_produto = ' . $type_product . ' and product_key_id = ' . $product_id . (is_null($value_fixed) ? '' : ' and value_fixed = "' . $value_fixed . '"');
        $query = $this->db->query($sql);
        return $query->rows;
    }

    public function hasMercadolivreInfos($product_key_id, $type_product)
    {
        $sql = "select product_key_id from oc_mercadolivre_infos where product_key_id = $product_key_id and tipo_produto = $type_product";
        $query = $this->db->query($sql);
        return ($query->num_rows) == true;
    }

    public function salvarDadosMercadoLivre(MktProduto $produto, $type_product)
    {


        if ($this->hasMercadolivreInfos($produto->getProductid(), $type_product)) {
            $sql = "UPDATE oc_mercadolivre_infos set
                      mercaListType     = ?,
                      mercaCondition    = ?,
                      mercaShippingFree = ?,
                      mercaShipping     = ?,
                      mercaId_category  = ?,
                      mercaTree         = ?
                    WHERE product_key_id    = ? and tipo_produto = $type_product";
        } else {
            $sql = "insert into oc_mercadolivre_infos set
                      mercaListType     = ?,
                      mercaCondition    = ?,
                      mercaShippingFree = ?,
                      mercaShipping     = ?,
                      mercaId_category  = ?,
                      mercaTree         = ?,
                      product_key_id    = ?,
                      tipo_produto      = $type_product";
        }

        $shipping = $produto->getMktRetorno()->getParam('shipping');
        $free_shipping = (isset($shipping->free_shipping) && ($shipping->free_shipping)) ? 1 : 0;
        $shipping_mode = $this->mercadolivre->getShippingType($shipping->mode, $shipping->local_pick_up);

        $result = $this->db->executeSql($sql,
            [
                $produto->getMktRetorno()->getParam('listing_type_id'),
                $produto->getMktRetorno()->getParam('condition'),
                $free_shipping,
                $shipping_mode,
                $produto->getMktRetorno()->getParam('category_id'),
                $this->mercadolivre->getCategoryRecursiva($produto->getMktRetorno()->getParam('category_id')),
                $produto->getProductid()]);

        //saveMLPicturesId
        if ($produto->hasVariations()) {

            $aux = true;

            foreach ($produto->getVariacao() as $keyVar => $variacao) {

                if (isset($produto->getMktRetorno()->getParam('variations')[$keyVar])) {

                    $imagesProd = $variacao->getImagens()->getImagens();
                    $imagesProdMkt = $produto->getMktRetorno()->getParam('variations')[$keyVar]->picture_ids;

                    foreach ($imagesProd as $keyProd => $img) {
                        if ($img->getId() == 0 && $aux) {
                            $sql = "UPDATE oc_mercadolivre_infos SET mlpictureid = '" . $imagesProdMkt[$keyProd] . "' WHERE product_key_id = " . $produto->getProductid() . " and tipo_produto = $type_product";
                        } else {
                            $sql = "UPDATE oc_product_image SET mlpictureid = '" . $imagesProdMkt[$keyProd] . "' WHERE product_image_id = " . $img->getId();
                        }
                        $query = $this->db->query($sql);
                        $aux = false;
                    }

                }
            }

        } else {
            $images = $produto->getPictures();
            $mktImages = $produto->getMktRetorno()->getParam('pictures');

            foreach ($images->getImagens() as $key => $img) {
                if ($img->getId() == 0) {
                    $sql = "UPDATE oc_mercadolivre_infos SET mlpictureid = '" . $mktImages[$key]->id . "' WHERE product_key_id = " . $produto->getProductid();
                } else {
                    $sql = "UPDATE oc_product_image SET mlpictureid = '" . $mktImages[$key]->id . "' WHERE product_image_id = " . $img->getId();
                }
                $query = $this->db->query($sql);
            }
        }

        //saveMlTitle
        $this->db->query("UPDATE oc_marketplace_product SET custom_title = '" . $this->db->escape($produto->getMktRetorno()->getMktName()) . "' WHERE product_key_id = '" . $produto->getProductid() . "' and tipo_produto = '" . $type_product . "'");

    }

    public function saveAttrNotVariable($data, $product_id, $tipo_produto)
    {
        $this->load->type('tipocomum');
        //se for grade, deleta todos os itens de grade,,,
        /*if (array_key_exists('grade', $data['attr_value'])){

            foreach($data['attr_value']['grade'] as $key => $value){
                $sql = "delete from oc_mercadolivre_prod_attr where product_key_id = $key and tipo_produto = " . $value['tipo_produto'];
                $this->db->query($sql);
            }

        }else{*/
        $sql = "delete from oc_mercadolivre_prod_attr where product_key_id = $product_id and tipo_produto = " . $tipo_produto;
        $this->db->query($sql);
        //}


        $fieldlistml = $this->getFieldsListAttrNotVariable($data['mercaId'], $product_id, $tipo_produto);
        if ($fieldlistml) {

            if (isset($data['attr_value'])) {

                foreach ($data['attr_value'] as $key => $value) {

                    if ($data['attr_value_fixed'][$key] == Type_DsimDnao::Tp_Sim) {
                        $data['attr_value_fixed'][$key] = Type_DsimDnao::Tp_Sim;
                    } else {
                        if ($key == Type_MercadoLivreIDField::Tp_Marca) {//marca é fora da regra, pego do produto para preencher o valor se não for fixed...
                            continue;
                        } else {
                            $data['attr_value_fixed'][$key] = Type_DsimDnao::Tp_Nao;
                        }
                    }

                    $attribute_value_id = $this->getOptionSelectedAttrNotVariable($fieldlistml, $key, $data['attr_value'][$key]);
                    $attr_unit_value = isset($data['attr_unit_value'][$key]) ? $data['attr_unit_value'][$key] : '';
                    $attribute_value_na = isset($data['attr_value_na'][$key]) ? 1 : 0;

                    if (($data['attr_value'][$key] != "") || ($attribute_value_id != "") || ($attribute_value_na != "")) {

                        $product_key_id = $product_id;
                        $attribute_id = $key;
                        $attribute_label = $this->db->escape($data['attr_label'][$key]);
                        $attribute_value = $this->db->escape(html_entity_decode($data['attr_value'][$key]));
                        $attribute_value_id = $this->db->escape($attribute_value_id);
                        $attribute_unit_value = $this->db->escape($attr_unit_value);
                        $value_fixed = $this->db->escape($data['attr_value_fixed'][$key]);
                        $attribute_value_na = $this->db->escape($attribute_value_na);
                        $attribute_variable = Type_MlAttrTipo::Tp_Normal;

                        $sql = "INSERT INTO `oc_mercadolivre_prod_attr`
                            (`attribute_id`, `attribute_label`, `attribute_value`, `attribute_value_id`, `attribute_unit_value`, `value_fixed`, `attribute_value_na`, `attribute_variable`, `product_key_id`, `tipo_produto`)
                        VALUES
                            ('{$attribute_id}', '{$attribute_label}', '{$attribute_value}', '{$attribute_value_id}', '{$attribute_unit_value}', '{$value_fixed}', {$attribute_value_na}, {$attribute_variable}, {$product_key_id}, {$tipo_produto})";

                        $this->db->query($sql);

                    }

                }

            }

        }

    }

    public function updateProductMLData($data, $product_id, $type_product)
    {
        if ($this->hasMercadolivreInfos($product_id, $type_product)) {
            $sql = "UPDATE oc_mercadolivre_infos SET "
                . " mercaId_category = '" . $this->db->escape($data['mercaId']) . "',"
                . " mercaTree = '" . $this->db->escape($data['mercaTree']) . "',"
                . " mercaBuyMode = '" . Type_MercadoLivreMode::Tp_Buy_it_now . "',"
                . " mercaListType = '" . $this->db->escape($data['mercaListType']) . "',"
                . " mercaCondition = '" . $this->db->escape($data['mercaCondition']) . "',"
                . " mercaWarranty = '" . $this->db->escape($data['mercaWarranty']) . "',"
                . " mercaWarrantyType = '" . $this->db->escape($data['mercaWarrantyType']) . "',"
                . " mercaWarrantyTypeTime = '" . $this->db->escape($data['mercaWarrantyTypeTime']) . "',"
                . " mercaShipping = '" . $this->db->escape($data['mercaShipping']) . "',"
                . " mercaShippingFree = '" . $this->db->escape($data['mercaShippingFree']) . "',"
                . " mercaVariationColor = '" . $this->db->escape($data['mercaVariationColor'] ?? "") . "',"
                . " mercaVariationSize = '" . $this->db->escape($data['mercaVariationSize'] ?? "") . "',"
                . " mercaVariationSpecification = '" . $this->db->escape($data['mercaVariationSpecification'] ?? "") . "'"
                . " WHERE product_key_id = '" . (int)$product_id . "' and tipo_produto = " . $type_product;
        } else {
            $sql = "insert into oc_mercadolivre_infos set "
                . " mercaId_category = '" . $this->db->escape($data['mercaId']) . "',"
                . " mercaTree = '" . $this->db->escape($data['mercaTree']) . "',"
                . " mercaBuyMode = '" . Type_MercadoLivreMode::Tp_Buy_it_now . "',"
                . " mercaListType = '" . $this->db->escape($data['mercaListType']) . "',"
                . " mercaCondition = '" . $this->db->escape($data['mercaCondition']) . "',"
                . " mercaWarranty = '" . $this->db->escape($data['mercaWarranty']) . "',"
                . " mercaWarrantyType = '" . $this->db->escape($data['mercaWarrantyType']) . "',"
                . " mercaWarrantyTypeTime = '" . $this->db->escape($data['mercaWarrantyTypeTime']) . "',"
                . " mercaShipping = '" . $this->db->escape($data['mercaShipping']) . "',"
                . " mercaShippingFree = '" . $this->db->escape($data['mercaShippingFree']) . "',"
                . " mercaVariationColor = '" . $this->db->escape($data['mercaVariationColor'] ?? "") . "',"
                . " mercaVariationSize = '" . $this->db->escape($data['mercaVariationSize'] ?? "") . "',"
                . " mercaVariationSpecification = '" . $this->db->escape($data['mercaVariationSpecification'] ?? "") . "',"
                . " product_key_id = '" . (int)$product_id . "',"
                . " tipo_produto = " . $type_product;
        }

        $this->db->query($sql);

    }

    public function getMercadolivreProductCatalog($product_id, $catalog_ad_id = false)
    {
        $sqlFields = "";
        $sqlLeftJoin = "";
        if ($catalog_ad_id) {
            $sqlFields = ", mac.id AS ad_catalog_id, mac.marketplace_product_id, mac.mercadolivre_product_catalog_id, mac.catalog_ad ";
            $sqlLeftJoin = " left join oc_mercadolivre_ad_catalog mac on (mac.marketplace_product_id = " . $catalog_ad_id . ")" . " and mac.mercadolivre_product_catalog_id = mpc.id ";
        }

        $sql = " select mpc.* ";
        $sql .= $sqlFields;
        $sql .= " from oc_mercadolivre_product_catalog mpc ";
        $sql .= $sqlLeftJoin;
        $sql .= " where product_id = " . $product_id;
        $query = $this->db->query($sql);
        return $query->row;
    }

    public function hasMercadolivreProductCatalog($product_id)
    {
        $sql = "select * from oc_mercadolivre_product_catalog where product_id = " . $product_id;
        $query = $this->db->query($sql);
        return ($query->num_rows) == true;
    }

    public function saveProductMLCatalog($product_id, $product_catalog_id, $data)
    {
        if ($this->hasMercadolivreProductCatalog($product_id, $product_catalog_id)) {
            $sql = "UPDATE oc_mercadolivre_product_catalog SET "
                . " product_catalog_id = '" . $this->db->escape($product_catalog_id) . "',"
                . " name = '" . $this->db->escape($data['name']) . "'"
                . " WHERE product_id = '" . (int)$product_id . "'";
        } else {
            $sql = "insert into oc_mercadolivre_product_catalog set "
                . " product_id = '" . (int)$product_id . "',"
                . " product_catalog_id = '" . $this->db->escape($product_catalog_id) . "',"
                . " name = '" . $this->db->escape($data['name']) . "'";
        }

        $this->db->query($sql);

    }

    public function getMercaId($mercaId)
    {

        $sql = "SELECT mp.*,
		COALESCE(p.product_id,pgd.product_id) product_id
		FROM oc_marketplace_product  mp
		LEFT JOIN oc_product p ON p.product_id = mp.product_key_id AND mp.tipo_produto = " . Type_ProdutoMkt::Tp_MktProd_Normal .
            " LEFT JOIN oc_product_grid_detail pgd ON pgd.product_grid_id = mp.product_key_id AND mp.tipo_produto = " . Type_ProdutoMkt::Tp_MktProd_Grade .
            " WHERE mp.marketId = '" . $mercaId . "' LIMIT 1";

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

        return $query->row;

    }

    public function getMercadolivreAdCatalogByAdId($main_ad_id)
    {
        $sql = "select * from oc_mercadolivre_ad_catalog
            where marketplace_product_id = " . $main_ad_id;
        $query = $this->db->query($sql);
        return $query->rows;
    }

    public function getMercadolivreAdCatalogByMlAdId($ml_ad_id)
    {
        $sql = "select * from oc_mercadolivre_ad_catalog ad
            left join oc_mercadolivre_product_catalog pr on pr.id = ad.mercadolivre_product_catalog_id
            left join oc_marketplace_product mp on mp.id = ad.marketplace_product_id
            where catalog_ad = '" . $ml_ad_id . "'";
        $query = $this->db->query($sql);
        return $query->row;
    }

    public function hasMercadolivreAdCatalogByMainAd($main_ad)
    {
        $sql = "select * from oc_mercadolivre_ad_catalog ac
                inner join oc_marketplace_product mp on ac.marketplace_product_id = mp.id
                where mp.marketId = '" . $main_ad . "' and tipo_integracao = " . Type_MarketPlace::Tp_Mercadolivre;
        $query = $this->db->query($sql);
        return ($query->num_rows) == true;
    }

    public function hasMercadolivreAdCatalogByMlAdId($ml_ad_id)
    {
        $sql = "select * from oc_mercadolivre_ad_catalog
            where catalog_ad = '" . $ml_ad_id . "'";
        $query = $this->db->query($sql);
        return ($query->num_rows) == true;
    }

    public function hasMercadolivreAdCatalogByAdId($main_ad_id)
    {
        $sql = "select * from oc_mercadolivre_ad_catalog
            where marketplace_product_id = " . $main_ad_id;
        $query = $this->db->query($sql);
        return $query->num_rows;
    }

    public function hasMercadolivreAdCatalog($main_ad_id, $product_catalog_id)
    {
        $sql = "select * from oc_mercadolivre_ad_catalog
            where marketplace_product_id = " . $main_ad_id . "
            and mercadolivre_product_catalog_id = " . $product_catalog_id;
        $query = $this->db->query($sql);
        return ($query->num_rows) == true;
    }

    public function saveAdMLCatalog($main_ad_id, $product_catalog_id, $data)
    {
        if ($this->hasMercadolivreAdCatalog($main_ad_id, $product_catalog_id)) {
            $sql = "UPDATE oc_mercadolivre_ad_catalog SET "
                . " catalog_ad = '" . $this->db->escape($data['catalog_ad']) . "'"
                . " WHERE marketplace_product_id = " . (int)$main_ad_id
                . " AND mercadolivre_product_catalog_id = " . (int)$product_catalog_id;
        } else {
            $sql = "insert into oc_mercadolivre_ad_catalog set "
                . " marketplace_product_id = " . (int)$main_ad_id . ","
                . " mercadolivre_product_catalog_id = " . (int)$product_catalog_id . ","
                . " catalog_ad = '" . $this->db->escape($data['catalog_ad']) . "'";
        }

        $this->db->query($sql);

    }

    public function insertMercadolivreAdCatalog($product_id, $product_catalog_id, $main_ad_marketid, $catalog_ad_marketid, $main_ad_from_ml = true)
    {
        $catalogProductId = $product_catalog_id;
        $catalogProduct = $this->mercadolivre->getProductCatalog($catalogProductId);

        $dataProdCatalog = [];
        $dataProdCatalog['name'] = $catalogProduct->name;
        $this->saveProductMLCatalog($product_id, $catalogProductId, $dataProdCatalog);

        $product_catalog = $this->getMercadolivreProductCatalog($product_id);
        // esse parametro define se o id passado, já é o id do anuncio na oc_marketplace_product ou é do ML
        if ($main_ad_from_ml) {
            $adData = $this->dmodel_mercadolivre_meliproduto->getMercaId($main_ad_marketid);
            $main_ad_id = $adData['id'];
        } else {
            $main_ad_id = $main_ad_marketid;
        }
        $dataMLCatalog = ['catalog_ad' => $catalog_ad_marketid];
        $this->dmodel_mercadolivre_meliproduto->saveAdMLCatalog($main_ad_id, $product_catalog['id'], $dataMLCatalog);
    }

    public function insertMercadolivreAdCatalogForced($produto, $product_mkt)
    {
        // se tem item relation, e é catalog required, e nao tem ainda na oc_mercadolivre_ad_catalog, insere na tabela a ligação do catalogo
        if ($product_mkt['tipo_produto'] == Type_ProdutoMkt::Tp_MktProd_Normal) {
            $gtin = (empty($produto->getParam('attributes')) ? false : $produto->getParam('attributes')[array_search('GTIN', array_column($produto->getParam('attributes'), 'id'))]->value_name);
            if (!empty($produto->getParam('item_relations')) && $gtin) {
                $paramSearch = array('status' => 'active', 'product_barcode' => $gtin);
                $result = $this->mercadolivre->searchProductCatalogMercadoLivre($paramSearch);
                if ($result) {
                    if (current($result->results)->settings->listing_strategy == Type_CatalogSetting::catalogRequired) {
                        if (!$this->dmodel_mercadolivre_meliproduto->hasMercadolivreAdCatalogByMlAdId(current($produto->getParam('item_relations'))->id)) {
                            $this->insertMercadolivreAdCatalog($product_mkt['product_key_id'], $produto->getParam('catalog_product_id'),
                                $produto->getmktIntegrationId(), current($produto->getParam('item_relations'))->id);
                        }
                    }
                }
            }

            if(!empty($produto->getmktVariations())){
                $product_id = $product_mkt['product_key_id'];
                $type_product = (string) Type_ProdutoMkt::Tp_MktProd_Normal;
                $type_marketplace = (string) Type_MarketPlace::Tp_Mercadolivre;

                $this->dmodel_marketplace_mktproduto->synchronizeProduct($product_id, $type_product, $type_marketplace, true, true);
            }

        } else {
            if ($produto->getmktVariations()) {
                foreach ($produto->getmktVariations() as $key => $variation) {
                    if (isset($variation->id) && $variation->id) {
                        $variationMl = $this->mercadolivre->getProductVariation($produto->getmktIntegrationId(), $variation->id);
                    } else {
                        $variationMl = false;
                    }
                    $gtin = (empty($variationMl->attributes) ? false : $variationMl->attributes[array_search('GTIN', array_column($variationMl->attributes, 'id'))]->value_name);
                    if (!empty($variationMl->item_relations) && $gtin) {
                        $paramSearch = array('status' => 'active', 'product_barcode' => $gtin);
                        $result = $this->mercadolivre->searchProductCatalogMercadoLivre($paramSearch);
                        if ($result && $result->results) {
                            if (current($result->results)->settings->listing_strategy == Type_CatalogSetting::catalogRequired) {
                                if (!$this->dmodel_mercadolivre_meliproduto->hasMercadolivreAdCatalogByMlAdId(current($variationMl->item_relations)->id)) {
                                    $this->insertMercadolivreAdCatalog($variation->seller_custom_field, $variation->catalog_product_id,
                                        $produto->getmktIntegrationId(), current($variation->item_relations)->id);
                                }
                            }
                        }
                    }
                }
            }
        }
    }

    public function getProductGridCategoryVariation($category_id, $template_id)
    {
        $sql = "select * from oc_marketplace_category_variation where category_id = '$category_id'";
        if (isset($template_id)) {
            $sql .= " and template_id = $template_id";
        } else {
            $sql .= " and template_id is null";
        }
        $query = $this->db->query($sql);
        return $query->rows;
    }

    public function hasProductGridCategoryVariation($category_id, $template_id)
    {
        return (boolean)$this->getProductGridCategoryVariation($category_id, $template_id);
    }

    public function updateProductGridCategoryVariation($data)
    {
        if (isset($data['templateGridId'])) {
            $templateGridId = $data['templateGridId'];
        } else {
            $this->load->model('catalog/product');
            $templateInfo = $this->model_catalog_product->getProductGridTemplate($data['product_ml']);
            $templateGridId = $templateInfo['product_grid_template_id'];
        }
        if ($this->hasProductGridCategoryVariation($data['mercaId'], $templateGridId)) {
            $sql = "UPDATE oc_marketplace_category_variation SET "
                . " color_attribute = '" . $this->db->escape($data['mercaVariationColor'] ?? "") . "',"
                . " size_attribute = '" . $this->db->escape($data['mercaVariationSize'] ?? "") . "',"
                . " specification_attribute = '" . $this->db->escape($data['mercaVariationSpecification'] ?? "") . "'"
                . " WHERE category_id = '" . $data['mercaId'];
            $sql .= isset($templateGridId) ? "' and template_id = " . $templateGridId : "' and template_id is null";
        } else {
            $sql = "insert into oc_marketplace_category_variation set "
                . " category_id = '" . $this->db->escape($data['mercaId']) . "',";
            $sql .= isset($templateGridId) ? " template_id = '" . $this->db->escape($templateGridId) . "'," : "";
            $sql .= " color_attribute = '" . $this->db->escape($data['mercaVariationColor'] ?? "") . "',"
                . " size_attribute = '" . $this->db->escape($data['mercaVariationSize'] ?? "") . "',"
                . " specification_attribute = '" . $this->db->escape($data['mercaVariationSpecification'] ?? "") . "'";
        }
        $this->db->query($sql);
    }

    public function getFieldsListAttrNotVariable($mercaCatId)
    {
        if ($mercaCatId == '') {
            return false;
        }
        //$fieldList = $this->cache->get('attributes_nvar_.' . $mercaCatId);
        $fieldList = false;

        if (!$fieldList) {
            $fieldList = $this->mercadolivre->getAttributesNotVariables($mercaCatId);
            if ($fieldList) {
                foreach ($fieldList as $key => $value) {
                    //remove os códigos, pois é campo especifico do sistema...$value['id']
                    if ((($value['type'])) && ($value['type'] == Type_MercadoLivreType::Tp_IdentProduto)) {
                        unset($fieldList[$key]);
                    }

                    if ($value['hidden']) {
                        unset($fieldList[$key]);
                    }

                    if ($value['id'] == Type_MercadoLivreIDField::Tp_ItemCond) {
                        unset($fieldList[$key]);
                    }

                }
                $this->cache->set('attributes_nvar_.' . $mercaCatId, $fieldList, 2592000 /* valor em segundos(30 dias) */);
            }


        }

        return $fieldList;
    }


    public function getOptionSelectedAttrNotVariable($fieldsml, $attr_id, $attrValue)
    {
        $attribute_value_id = "";
        foreach ($fieldsml as $value) {
            if ($value['id'] == $attr_id) {

                if (sizeof($value['options']) > 0) {
                    foreach ($value['options'] as $key => $value) {
                        if ($value == $attrValue) {
                            $attribute_value_id = $key;
                            break;
                        }
                    }
                }

            }
        }
        return $attribute_value_id;
    }

    public function getProductsMercadolivreHaveGradeItem($product_id)
    {

        $sql = "select"
            . "   (p.product_id) as product_id "
            . "from " . DB_PREFIX . "marketplace_product mp "
            . "inner join " . DB_PREFIX . "product p on (p.product_id = mp.product_key_id and mp.tipo_produto = 0) "
            . "WHERE p.tipo in " . ftr_geral::sqlIn(Type_ProdutoGrade::getEnumList()) . " and p.product_id = " . $product_id;

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

        if (isset($query->row['product_id'])) {
            return $query->row['product_id'];
        } else {
            return false;
        }

    }


    public function getMetrics($tempoLimite = false)
    {

        $marketids = $this->db->query('SELECT marketId from oc_marketplace_product where tipo_integracao = ' . Type_MarketPlace::Tp_Mercadolivre . ' and status = "' . Type_MercadoLivreItemStatus::Tp_Active . '"')->rows;
        if ($marketids) {

            $marketids_groups = array_chunk($marketids, 10);
            foreach ($marketids_groups as $key => $marketids_group) {
                try {
                    $this->db->startTransaction();

                    foreach ($marketids_group as $marketid) {
                        $reviews = $this->mercadolivre->getReviews($marketid['marketId']);
                        $review = (isset($reviews->rating_average) AND ($reviews->rating_average != null)) ? $reviews->rating_average : 0;
                        $this->db->query("UPDATE oc_marketplace_product SET rating = " . (int)$review . " WHERE marketId = '" . $marketid['marketId'] . "' and tipo_integracao = '" . Type_MarketPlace::Tp_Mercadolivre . "'");
                    }

                    $ids = implode(',', array_map(function ($array) {
                        return $array['marketId'];
                    }, $marketids_group));

                    $visits = $this->mercadolivre->getVisits($ids);

                    if ($visits) {
                        foreach ($visits as $visit) {
                            $this->db->query("UPDATE oc_marketplace_product SET total_visits = " . (int)$visit->total_visits . " WHERE marketId = '" . $visit->item_id . "' and tipo_integracao = '" . Type_MarketPlace::Tp_Mercadolivre . "'");
                        }
                    }

                    $this->db->commit();

                } catch (Exception $e) {
                    $this->db->rollback();
                    $this->mercadolivre->setErrorML('MERCADOLIVRE METRICS/VISITS API Error: ' . $e);
                }

                if (($tempoLimite) && (new DateTime($this->db->dataAtual()) > $tempoLimite)) {
                    break;
                }
            }

        }

    }

    public function getApiCallIdItems()
    {
        $callType = Type_CallbackTopic::getNickFromConstValue(Type_CallbackTopic::Tp_Items);
        $this->db->query("DELETE FROM `" . DB_PREFIX . "marketplace_api_call` WHERE tipo_integracao = " . Type_MarketPlace::Tp_Mercadolivre . " AND callType = '$callType' AND  done = 1");
        return $this->db->query("SELECT * FROM `" . DB_PREFIX . "marketplace_api_call` WHERE tipo_integracao = " . Type_MarketPlace::Tp_Mercadolivre . " AND callType = '$callType' and `done` = '0' limit 100 ")->rows;
    }


}