<?php

class DModelmarketplacemktsqlproduto extends Model
{

    /**
     * @var null
     */
    protected $customer_group_id = null;


    /**
     * @return null
     */
    public function getCustomerGroupId()
    {
        return $this->customer_group_id;
    }

    /**
     * @param null $customer_group_id
     */
    public function setCustomerGroupId($customer_group_id): void
    {
        $this->customer_group_id = $customer_group_id;
    }

    /**
     * @param $product_id
     * @param $marketplace
     * @param array $data
     * @return mixed
     */
    public function getProductSql($product_id, $marketplace, array $data = [])
    {
        $product_id = (int)$product_id;
        $defaultLanguage = (int)$this->config->get('config_language_id');
        $tipoProduto = Type_ProdutoMkt::Tp_MktProd_Normal;
        $mkt_table_price = isset($data['mkt_table_price']) ? $data['mkt_table_price'] : null;
        $standard = Type_DsimDnao::Tp_Sim;

        $this->language->load('module/futuragrade');
        $title_cor = $this->language->get('heading_title_cor');
        $title_size = $this->language->get('heading_title_size');
        $title_specification = (ftr_geral::isIsset($this->config->get('futuragrade_title_specification')) && $this->config->get('futuragrade_title_specification') != "") ? $this->config->get('futuragrade_title_specification') : $this->language->get('heading_title_specification');

        $sql = " SELECT DISTINCT"
            . "   p.*, "
            . "   pd.*, "
            . "   minf.*, "
            . "   ginf.*, "
            . "   sinf.*, "
            . "   mp.status as mkt_status, "
            . "   mp.custom_title as mkt_custom_title, "
            . "   mp.sold_quantity as mkt_sold_quantity, "
            . "   (m.name) AS manufacturer_name, "
            . "   (SELECT keyword FROM oc_url_alias WHERE query = 'product_id=" . $product_id . "') AS keyword, "
            . "   (pc.description) AS color, "
            . "   if(pc.description != '', '$title_cor', '') as cor_description,"
            . "   if(ps.description != '', '$title_size', '') as tamanho_description,"
            . "   if(psf.description != '', '$title_specification', '') as especificacao_description,"
            . "   (ps.description) AS size,  "
            . "   (psf.description) AS specification, "
            . "   mp.marketId as product_marketId, "
            . "   mp.codigo_barras as mkt_codigo_barras, "
            . "   mp.marketplace_price_id as marketplace_price_id, "
            . "   mp.marketplaceprice_config_md5 as marketplaceprice_config_md5, "
            . "   mp.link_type as link_type, "
            . "   mpi.marketplace_image_id as marketplace_image_id, "
            . "   mpi.id_futura as marketplace_image_id_futura, "
            . "   pkit.product_kit_id AS product_kit_id,"
            . "   p.product_id as product_key_id ";

        if (!is_null($mkt_table_price)) {
            $sql .= ", COALESCE(pdis.price, p.price) AS table_price ";
        }

        $sql .= " FROM oc_product p "
            . " LEFT join oc_marketplace_product mp on (mp.product_key_id = p.product_id and mp.tipo_integracao = " . $marketplace . " and mp.tipo_produto = " . Type_ProdutoMkt::Tp_MktProd_Normal . ") "
            . " LEFT join oc_marketplace_product_image mpi on (mpi.product_id = p.product_id and mpi.tipo_marketplace = " . $marketplace . ") "
            . " LEFT join oc_mercadolivre_infos minf on (minf.product_key_id = p.product_id and minf.tipo_produto = " . $tipoProduto . ") "
            . " LEFT join oc_galeriapage_infos ginf on (ginf.product_key_id = p.product_id and ginf.tipo_produto = " . $tipoProduto . ") "
            . " LEFT join oc_shopee_infos sinf on (sinf.product_key_id = p.product_id and sinf.tipo_produto = " . $tipoProduto . ") "
            . " LEFT JOIN oc_product_description pd ON (p.product_id = pd.product_id) "
            . " LEFT JOIN oc_manufacturer m ON (m.manufacturer_id = p.manufacturer_id) "
            . " LEFT JOIN oc_product_color pc ON (pc.productcolor_id = p.product_color_id) "
            . " LEFT JOIN oc_product_size ps ON (ps.productsize_id = p.product_size_id) "
            . " LEFT JOIN oc_product_kit pkit on pkit.product_id = p.product_id and pkit.site_utiliza='S' "
            . " LEFT JOIN oc_product_specification psf ON (psf.productspecification_id = p.product_specification_id) "
            . " LEFT JOIN oc_marketplace_price mpri on ( (mpri.marketplace_price_id = mp.marketplace_price_id or mpri.standard = " . $standard . ") and mpri.marketplace_price_md5 != mp.marketplaceprice_config_md5) ";

        if (!is_null($mkt_table_price)) {
            $sql .= " LEFT JOIN oc_product_discount pdis ON (pdis.product_id = p.product_id) AND pdis.customer_group_id = " . $mkt_table_price;
        }

        $sql .= " WHERE p.product_id = '" . $product_id . "' AND pd.language_id = '" . $defaultLanguage . "'";

        $query = $this->db->query($sql);
        return $query->row;
    }

    /**
     * @param $product_key_id
     * @param $marketplace
     * @param array $data
     * @return mixed
     */
    public function getProductGridSql($product_key_id, $marketplace, array $data = [])
    {
        $product_key_id = (int)$product_key_id;
        $defaultLanguage = (int)$this->config->get('config_language_id');
        $tipoProduto = Type_ProdutoMkt::Tp_MktProd_Grade;
        $mkt_table_price = isset($data['mkt_table_price']) ? $data['mkt_table_price'] : null;
        $standard = Type_DsimDnao::Tp_Sim;

        $sql = " SELECT "
            . "      (TRUE) AS IsGrid, "
            . "       minf.*, "
            . "       ginf.*, "
            . "       sinf.*, "
            . "       pg.product_grid_id AS product_grid_id, "
            . "       pg.product_grid_template_id AS product_grid_template_id, "
            . "       pd.*, "
            . "       p.*, "
            . "       mp.status as mkt_status, "
            . "       mp.custom_title as mkt_custom_title, "
            . "       mp.sold_quantity as mkt_sold_quantity, "
            . "       mp.marketplace_price_id as marketplace_price_id, "
            . "       mp.marketplaceprice_config_md5 as marketplaceprice_config_md5, "
            . "       mp.link_type as link_type, "
            . "       (pc.description) AS color, "
            . "       (ps.description) AS size,  "
            . "       (psf.description) AS specification, "
            . "       (m.name) AS manufacturer_name, "
            . "       mp.marketId as product_marketId, "
            . "       mp.codigo_barras as mkt_codigo_barras, "
            . "       mpi.marketplace_image_id as marketplace_image_id, "
            . "       mpi.id_futura as marketplace_image_id_futura, "
            . "       pg.product_grid_id as product_key_id ";

        if (!is_null($mkt_table_price)) {
            $sql .= ", COALESCE(pdis.price, p.price) AS table_price ";
        }

        $sql .= "   FROM oc_product_grid pg "
            . "   INNER JOIN oc_product_grid_detail pgdetail ON (pgdetail.product_grid_id = pg.product_grid_id) "
            . "   INNER JOIN oc_product p ON (pgdetail.product_id = p.product_id) "
            . "   LEFT JOIN oc_marketplace_product mp on (mp.product_key_id = pg.product_grid_id and mp.tipo_integracao = " . $marketplace . " and mp.tipo_produto = " . Type_ProdutoMkt::Tp_MktProd_Grade . ") "
            . "   LEFT JOIN oc_marketplace_product_image mpi on (mpi.product_id = p.product_id and mpi.tipo_marketplace = " . $marketplace . ") "
            . "   LEFT JOIN oc_mercadolivre_infos minf on (minf.product_key_id = pg.product_grid_id and minf.tipo_produto = " . $tipoProduto . ") "
            . "   LEFT JOIN oc_galeriapage_infos ginf on (ginf.product_key_id = pg.product_grid_id and ginf.tipo_produto = " . $tipoProduto . ") "
            . "   LEFT JOIN oc_shopee_infos sinf on (sinf.product_key_id = pg.product_grid_id and sinf.tipo_produto = " . $tipoProduto . ") "
            . "   LEFT JOIN oc_product_description pd ON (pd.product_id = p.product_id) "
            . "   LEFT JOIN oc_manufacturer m ON (m.manufacturer_id = p.manufacturer_id) "
            . "   LEFT JOIN oc_product_color pc ON (pc.productcolor_id = p.product_color_id) "
            . "   LEFT JOIN oc_product_size ps ON (ps.productsize_id = p.product_size_id) "
            . "   LEFT JOIN oc_product_specification psf ON (psf.productspecification_id = p.product_specification_id) "
            . "   LEFT JOIN oc_marketplace_price mpri on ( (mpri.marketplace_price_id = mp.marketplace_price_id or mpri.standard = " . $standard . ") and mpri.marketplace_price_md5 != mp.marketplaceprice_config_md5) ";


        if (!is_null($mkt_table_price)) {
            $sql .= " LEFT JOIN oc_product_discount pdis ON (pdis.product_id = p.product_id) AND pdis.customer_group_id = " . $data['mkt_table_price'];
        }

        $sql .= "   WHERE pd.language_id = '1' AND (p.tipo IN (14,16)) and pg.product_grid_id = " . $product_key_id . " AND ( "
            . "   SELECT p2.product_id "
            . "   FROM oc_product p2 "
            . "   INNER JOIN oc_product_grid_detail pgd2 ON (p2.product_id = pgd2.product_id) "
            . "   INNER JOIN oc_product_description pd2 ON (pd2.product_id = p2.product_id) "
            . "   LEFT JOIN oc_product_to_store p2s2 ON (p2.product_id = p2s2.product_id) "
            . "   LEFT JOIN oc_marketplace_product mp2 on (mp2.product_key_id = pgd2.product_grid_id and mp2.tipo_integracao = " . $marketplace . " and mp2.tipo_produto = " . Type_ProdutoMkt::Tp_MktProd_Grade . ") "
            . "   LEFT JOIN oc_mercadolivre_infos minf2 on (minf2.product_key_id = pgd2.product_grid_id  and minf2.tipo_produto = " . $tipoProduto . ") "
            . "   LEFT JOIN oc_marketplace_price mpri2 on ( (mpri2.marketplace_price_id = mp2.marketplace_price_id or mpri2.standard = " . $standard . ") and mpri2.marketplace_price_md5 != mp2.marketplaceprice_config_md5) "
            . "   WHERE pd2.language_id = " . $defaultLanguage . " AND (pgd2.product_grid_id = pg.product_grid_id) and (pgd2.product_grid_id = " . $product_key_id . ") "
            . "   ORDER BY p2.status desc, p2.qty_mkt DESC "
            . "   LIMIT 1) = p.product_id";

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

        return $query->row;
    }

    /**
     * @param $product_grid_id
     * @param $marketplace
     * @param array $data
     * @return mixed
     */
    public function getProductsFromGridSql($product_grid_id, $marketplace, array $data = [])
    {
        $language = $this->language->load('module/futuragrade');
        $title_specification = (ftr_geral::isIsset($this->config->get('futuragrade_title_specification')) && $this->config->get('futuragrade_title_specification') != "") ? $this->config->get('futuragrade_title_specification') : $this->language->get('heading_title_specification');
        $title_size = $this->language->get('heading_title_size');
        $title_cor = $this->language->get('heading_title_cor');

        $filtro = '';
        $sql = "
            select
                pc.description as color_description,
                ps.description as size_description,
                psp.description as specification_description,
                (COALESCE(pgt.descricao_cor,'$title_cor')) color_template_description,
                (COALESCE(pgt.descricao_tamanho,'$title_size')) size_template_description,
                (COALESCE(pgt.descricao_especificacao,'$title_specification')) specification_template_description,
                pgt.product_grid_template_id as grid_template_id,
                pgd.product_id,
                p.codigo_barra,
                p.image,
                p.id_futura_img,
                p.qty_mkt,
                p.qty,
                p.price,
                p.status,
                mpi.marketplace_image_id as marketplace_image_id,
                mpi.id_futura as marketplace_image_id_futura";

        if (!empty($data['mkt_table_price'])) {
            $sql .= ", COALESCE(pdis.price, p.price) AS table_price ";
        }

        $sql .= " from oc_product_grid_detail pgd
            LEFT JOIN oc_product_color pc on (pc.productcolor_id = pgd.product_color_id)
            LEFT JOIN oc_product_size  ps on (ps.productsize_id  = pgd.product_size_id)
            LEFT JOIN oc_product       p  on (pgd.product_id     = p.product_id)
            LEFT JOIN oc_marketplace_product_image mpi on (mpi.product_id = p.product_id and mpi.tipo_marketplace = " . $marketplace . ")
            LEFT JOIN oc_product_specification psp on (psp.productspecification_id = pgd.product_specification_id)
            LEFT JOIN oc_product_grid pg ON (pg.product_grid_id = pgd.product_grid_id)
            LEFT JOIN oc_product_grid_template pgt ON (pg.product_grid_template_id = pgt.product_grid_template_id)";

        if (!empty($data['mkt_table_price'])) {
            $sql .= " LEFT JOIN oc_product_discount pdis ON (pdis.product_id = p.product_id) AND pdis.customer_group_id = " . $data['mkt_table_price'];
        }

        $sql .= " where pgd.product_grid_id = $product_grid_id";

        if (isset($data['filter_ativos']) && !empty($data['filter_ativos'])) {
            $filtro .= ' and p.status = ' . $data['filter_ativos'];
        }

        $sql = $sql . $filtro;

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

        return $query->rows;
    }

    /**
     * @param $marketplace
     * @param array $data
     * @return mixed
     */
    public function getMktProdutosSql($marketplace, array $data = [])
    {
        $produto_normal = Type_ProdutoMkt::Tp_MktProd_Normal;
        $produto_grade = Type_ProdutoMkt::Tp_MktProd_Grade;

        $status_produto_integracao = "";
        if (!empty($data['status_produto_integracao']) && is_array($data['status_produto_integracao'])) {
            $status_produto_integracao = " and (mp.status in (";
            foreach ($data['status_produto_integracao'] as $key => $status) {
                $status_produto_integracao .= "'$status'";
                if (array_keys($data['status_produto_integracao'])[count($data['status_produto_integracao']) - 1] <> $key) {
                    $status_produto_integracao .= ',';
                }
            }
            $status_produto_integracao .= ") or mp.status = '') ";
        } else if (!empty($data['status_produto_integracao'])) {
            $status_produto_integracao = " and (mp.status = '" . $data['status_produto_integracao'] . "' or mp.status = '') ";
        }

        $sql = "SELECT
                    (mp.product_key_id) AS product_key_id,
                    (mp.marketId) AS marketId,
                    (mp.tipo_produto) AS tipo_produto,
                    mp.date_att_produto,
                    mp.date_att_estoque,
                    mp.date_last_error,
                    mp.date_att_info_skyhub,
                    mp.link_type
            FROM oc_marketplace_product mp
            INNER JOIN oc_product p on (mp.product_key_id = p.product_id)
            LEFT JOIN oc_product_image pimg on (mp.product_key_id = pimg.product_id)
            LEFT JOIN oc_marketplace_price mpri on (mpri.marketplace_price_id = mp.marketplace_price_id)
            LEFT JOIN oc_product_discount pd ON (p.product_id = pd.product_id AND pd.customer_group_id = mpri.customer_group_id)
            WHERE (mp.tipo_produto = $produto_normal) and (mp.tipo_integracao = '$marketplace') $status_produto_integracao";

        // Verifica o md5 das configs, caso esteja diferente tem que atualizar todos os produtos
        $configMd5 = "";
        if (!empty($data['config_md5']) && !empty($data['config_md5_mktfield'])) {
            $configMd5 = " OR (mp." . $data['config_md5_mktfield'] . " != '" . $data['config_md5'] . "' OR " . $data['config_md5_mktfield'] . " is null) ";
        }

        if (isset($data['link_type'])) {
            $sql .= " AND (mp.link_type = " . $data['link_type'] . ")";
        }

        if ((!empty($data['date_estoque'])) && (!empty($data['date_produto']))) {
            $sql .= " AND  ((p.data_atualizacao_estoque > mp.date_att_estoque)
                              OR (mp.date_att_estoque is null)
                              OR (p.data_atualizacao_replicacao > mp.date_att_produto)
                              OR (mp.date_att_produto is null))";
        } else {
            if (!empty($data['date_estoque'])) {
                $sql .= " AND  ((p.data_atualizacao_estoque > mp.date_att_estoque)
                                 OR (mp.date_att_estoque is null)
                                 OR (p.data_replicacao_preco > mp.date_att_price)
                                 OR (pd.data_replicacao_preco > mp.date_att_price)
                                 OR (mp.date_att_price is null)
                                 OR (mpri.marketplace_price_md5 != mp.marketplaceprice_config_md5)
                                 OR (mp.marketplaceprice_config_md5 IS NULL)
                                 OR (mp.marketplace_price_id IS NULL) ) ";
            }

            if (!empty($data['date_produto'])) {
                $sql .= " AND ((p.data_atualizacao_replicacao > mp.date_att_produto) OR (mp.date_att_produto is null) " . $configMd5 . ") ";
            }
        }

        if (!empty($data['date_att_info_skyhub'])) {
            $sql .= " AND ((mp.date_att_info_skyhub <= '" . $data['date_att_info_skyhub'] . "') OR (mp.date_att_info_skyhub is null)) ";
        }

        $sql .= " UNION
                SELECT
                    (mp.product_key_id) AS product_key_id,
                    (mp.marketId) AS marketId,
                    (mp.tipo_produto) AS tipo_produto,
                    mp.date_att_produto,
                    mp.date_att_estoque,
                    mp.date_last_error,
                    mp.date_att_info_skyhub,
                    mp.link_type
                FROM oc_marketplace_product mp
                INNER JOIN oc_product_grid pg on (pg.product_grid_id = mp.product_key_id)
                LEFT JOIN oc_product_image pimg on (mp.product_key_id = pimg.product_id)
                LEFT JOIN oc_marketplace_price mpri on (mpri.marketplace_price_id = mp.marketplace_price_id)
                WHERE (mp.tipo_produto = $produto_grade) and (mp.tipo_integracao = '$marketplace')  $status_produto_integracao";

        if (isset($data['link_type'])) {
            $sql .= " AND (mp.link_type = " . $data['link_type'] . ")";
        }

        if ((!empty($data['date_estoque'])) && (!empty($data['date_produto']))) {
            $sql .= " AND exists(
                            select pgi2.product_id from oc_product_grid_detail pgi2
                            inner join oc_product p2 on (p2.product_id = pgi2.product_id)
                            where ((p2.data_atualizacao_estoque > mp.date_att_estoque) OR (mp.date_att_estoque is null) OR (p2.data_atualizacao_replicacao > mp.date_att_produto) OR (mp.date_att_produto is null))
                            and (pgi2.product_grid_id = pg.product_grid_id)
                           )";

            $sql .= " ORDER BY date_last_error, date_att_produto";
        } else {
            if (!empty($data['date_estoque'])) {

                $sql .= " AND exists(
                            select pgi2.product_id from oc_product_grid_detail pgi2
                            inner join oc_product p2 on (p2.product_id = pgi2.product_id)
                            LEFT JOIN oc_product_discount pd2 ON (p2.product_id = pd2.product_id AND pd2.customer_group_id = mpri.customer_group_id)
                            where ((p2.data_atualizacao_estoque > mp.date_att_estoque)
                                    OR (mp.date_att_estoque is null)
                                    OR (pd2.data_replicacao_preco > mp.date_att_price)
                                    OR (p2.data_replicacao_preco > mp.date_att_price)
                                    OR (mp.date_att_price is null) )
                             and (pgi2.product_grid_id = pg.product_grid_id)
                              OR ((mpri.marketplace_price_md5 != mp.marketplaceprice_config_md5)
                                    OR (mp.marketplaceprice_config_md5 IS NULL)
                                    OR (mp.marketplace_price_id IS NULL)))";

                $sql .= " ORDER BY date_att_estoque, date_last_error";
            }

            if (!empty($data['date_produto'])) {
                $sql .= " AND ((pg.data_atualizacao_replicacao > mp.date_att_produto) OR (mp.date_att_produto is null) " . $configMd5;

                $sql .= " OR exists(
                            select pgi2.product_id from oc_product_grid_detail pgi2
                            inner join oc_product p2 on (p2.product_id = pgi2.product_id)
                            where( p2.data_atualizacao_replicacao > mp.date_att_produto) and (pgi2.product_grid_id = pg.product_grid_id)
                           ))";

                $sql .= " ORDER BY date_att_estoque, date_last_error";
            }
        }

        if (!empty($data['date_att_info_skyhub'])) {
            $sql .= " AND ((mp.date_att_info_skyhub <= '" . $data['date_att_info_skyhub'] . "') OR (mp.date_att_info_skyhub is null) ";

            $sql .= " OR exists(
                            select pgi2.product_id from oc_product_grid_detail pgi2
                            inner join oc_product p2 on (p2.product_id = pgi2.product_id)
                            where( mp.date_att_info_skyhub <= '" . $data['date_att_info_skyhub'] . "') and (pgi2.product_grid_id = pg.product_grid_id)
                           ))

            ";

            $sql .= " ORDER BY date_att_info_skyhub";
        }

        if (isset($data['start']) || isset($data['limit'])) {
            if ($data['start'] < 0) {
                $data['start'] = 0;
            }

            if ($data['limit'] < 1) {
                $data['limit'] = 20;
            }

            $sql .= " LIMIT " . (int)$data['start'] . "," . (int)$data['limit'];
        }

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

        return $query->rows;
    }

    /**
     * @param $product_id
     * @param $marketplace
     * @return false|mixed
     */
    public function getMktVariationIdSql($product_id, $marketplace)
    {
        $sql = "select mkt_variation_id from oc_marketplace_product_variation where product_id = $product_id and tipo_integracao = $marketplace";
        $query = $this->db->query($sql);
        return $query->rows ? $query->row['mkt_variation_id'] : false;
    }

}