<?php
$loader = $this->registry->get('load');
$loader->dmodel('marketplace/mktsqlproduto');

class DModelmarketplacemktprodutoskyhub extends DModelmarketplacemktsqlproduto
{

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

        $this->load->dmodel('marketplace/mktprice');
        $result = $this->dmodel_marketplace_mktprice->getStandardMktPrice(Type_MarketPlace::Tp_SkyHub);

        $this->customer_group_id = $result->getCustomerGroupId();
    }

    public function getProduct($product_id)
    {

        $mkt_table_price = $this->getCustomerGroupId();
        $product_id = (int)$product_id;
        $defaultLanguage = (int)$this->config->get('config_language_id');
        $tipoProduto = Type_ProdutoMkt::Tp_MktProd_Normal;
        $standard = Type_DsimDnao::Tp_Sim;
        $marketplace = Type_MarketPlace::Tp_SkyHub;

        $sql = " SELECT DISTINCT"
            . "   p.*, "
            . "   pd.*, "
            . "   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, "
            . "   mp.marketId as product_marketId, "
            . "   mp.codigo_barras as mkt_codigo_barras, "
            . "   p.product_id as product_key_id, "
            . "   pkit.product_kit_id AS product_kit_id,"
            . "   mp.marketplace_price_id as marketplace_price_id, "
            . "   mp.marketplaceprice_config_md5 as marketplaceprice_config_md5, "
            . "   mpi.marketplace_image_id as marketplace_image_id, "
            . "   mpi.id_futura as marketplace_image_id_futura, "
            . " COALESCE(pdis.price, p.price) AS table_price "
            . " 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_skyhub_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_discount pdis ON (pdis.product_id = p.product_id) AND pdis.customer_group_id = " . $mkt_table_price . ""
            . " LEFT JOIN oc_product_kit pkit on pkit.product_id = p.product_id and pkit.site_utiliza='S' "
            . " 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) "
            . " WHERE p.product_id = '" . $product_id . "' AND pd.language_id = '" . $defaultLanguage . "'";

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

    }


    public function getProductGrid($product_key_id, array $data = [])
    {

        $mkt_table_price = $this->getCustomerGroupId();
        $product_key_id = (int)$product_key_id;
        $defaultLanguage = (int)$this->config->get('config_language_id');
        $tipoProduto = Type_ProdutoMkt::Tp_MktProd_Grade;
        $standard = Type_DsimDnao::Tp_Sim;
        $marketplace = Type_MarketPlace::Tp_SkyHub;

        $sql = " SELECT "
            . "      (TRUE) AS IsGrid, "
            . "       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, "
            . "       (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, "
            . "       pg.product_grid_id as product_key_id, "
            . "       mpi.marketplace_image_id as marketplace_image_id, "
            . "       mpi.id_futura as marketplace_image_id_futura, "
            . "       COALESCE(pdis.price, p.price) AS table_price "
            . "   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_skyhub_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_product_discount pdis ON (pdis.product_id = p.product_id) AND pdis.customer_group_id = " . $mkt_table_price . ""
            . "   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) "
            . "   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_skyhub_infos sinf2 on (sinf2.product_key_id = pgd2.product_grid_id  and sinf2.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.quantity DESC "
            . "   LIMIT 1) = p.product_id";

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

        return $query->row;

    }

    public function getProductsFromGrid($product_key_id, array $data = [])
    {
        $data = array_merge($data, ['mkt_table_price' => $this->getCustomerGroupId()]);

        return parent::getProductsFromGridSql($product_key_id, Type_MarketPlace::Tp_SkyHub, $data);
    }

    public function getMktProdutos(array $data = [])
    {
        $data['status_produto_integracao'] = TypeSkyHub_Status::Tp_Habilitado;
        return parent::getMktProdutosSql(Type_MarketPlace::Tp_SkyHub, $data);
    }

    public function getMktVariationId($product_id)
    {
        return parent::getMktVariationIdSql($product_id, Type_MarketPlace::Tp_SkyHub);
    }

}