<?php
final class DModelcatalogproduct extends Model
{

  public function getPriceTabelaproduct_id($product_id)
  {
    $customer_group_id = $this->config->get('config_customer_group_id');
    $special_row = $this->db->query("SELECT price FROM " . DB_PREFIX . "product_special WHERE product_id = '" . $product_id . "' AND customer_group_id = '" . (int)$customer_group_id . "' AND ((date_start = '0000-00-00' OR date_start < NOW()) AND (date_end = '0000-00-00' OR date_end > NOW())) ORDER BY priority ASC, price ASC LIMIT 1")->row;
    if ($special_row && (float)$special_row['price']) {
      return $special_row['price'];
    }
    return 0;
  }

  public function getProductBrinde($product_id, $stockOnly=false)
  { //config_stock_checkout
    $data_language = $this->config->get('config_data_language');
    if ((!isset($data_language) || $data_language == Type_DataLanguage::Tp_Portugues_Brasil) || ($this->ftrgeralregistry->isAdmin())) {
      unset($data_language);
    }

    $sql = "SELECT
        od.product_id,
        od.product_brind_id,
        p.image,
        p.data_atualizacao_replicacao,
        p.qty,
        p.qty as quantity,
        p.price,
        p.model,
        p.width,
        p.length,
        p.height,
        p.larg_progressiva,
        p.comp_progressiva,
        p.alt_progressiva,
        p.id_futura,
        p.weight,";

    $sql .= isset($data_language) ? "coalesce(pt.descricao_ecommerce, pd.name) as name" : "pd.name as name";
    $sql .= " FROM oc_order_discount od
      INNER JOIN oc_product p ON p.product_id = od.product_brind_id
      INNER JOIN oc_product_description pd ON pd.product_id = p.product_id";
    $sql .= isset($data_language) ? " LEFT JOIN oc_product_translate pt ON (p.product_id = pt.product_id and pt.linguagem = '" . $data_language . "')" : "";
    $sql .= " WHERE od.product_id = $product_id AND od.tipo_promocao = " . Type_Promocao::Tp_Brinde;

    if ($stockOnly){
        $sql .= " AND p.qty > 0";
    }

    $query = $this->db->query($sql);
    if (count($query->row) > 0) {
      return $query->row;
    } else {
      return false;
    }
  }

  public function getTipoProduct($product_id)
  {
    $sql = "select tipo from oc_product where product_id = $product_id";
    $query = $this->db->query($sql);
    return $query->row;
  }

  public function getDescricaoProduct($product_id)
  {
    $sql = "
          select pd.name from oc_product  p
          inner join oc_product_description pd on (pd.product_id = p.product_id)
          where p.product_id = $product_id";
    $query = $this->db->query($sql);
    return $query->row;
  }

  public function getProductsSpecifications($product_id)
  {
    $sql = "
            select
                pc.description as color_description,
                ps.description as size_description,
                psp.description as specification_description,
                p.product_id,
                p.codigo_barra
            from oc_product p
            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 psp on (psp.productspecification_id = p.product_specification_id)
            where p.product_id = $product_id
        ";

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

    return $query->rows;
  }


  public function getProductImages($product_id, $limit = null, $tipoMarketplace = false)
  {
    $camposMkt = ($tipoMarketplace !== false) ? ' , pi.marketplace_image_id , pi.id_futura as marketplace_image_id_futura ' : '' ;
    $joinMkt = ($tipoMarketplace !== false) ? " LEFT JOIN " . DB_PREFIX . "marketplace_product_image pi ON pi.product_image_id = i.product_image_id AND pi.tipo_marketplace = " . (int)$tipoMarketplace : '' ;

    $sql = "SELECT i.* " . $camposMkt .
          " FROM " . DB_PREFIX . "product_image i";

    $sql .= $joinMkt;

    $sql .= " WHERE i.product_id = '" . (int)$product_id . "'
        ORDER BY i.sort_order, i.id_futura, i.product_image_id";

    if ($limit > 0){
        $sql .= " limit ".$limit;
    }

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

    return $query->rows;
  }

  public function getDadosGridProdutoId($productid)
  {
    $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');

    $dados = array();

    $sql = "SELECT"
      . " (pc.description) as color,"
      . " (ps.description) as size,"
      . " (pe.description) as specification,"
      . " (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"
      . " FROM oc_product p"
      . " LEFT JOIN " . DB_PREFIX . "product_color pc on (p.product_color_id = pc.productcolor_id)"
      . " LEFT JOIN " . DB_PREFIX . "product_size ps on (p.product_size_id = ps.productsize_id)"
      . " LEFT JOIN " . DB_PREFIX . "product_specification pe on (p.product_specification_id = pe.productspecification_id)"
      . " LEFT JOIN oc_product_grid_detail pgd ON pgd.product_id = p.product_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"
      . " WHERE p.product_id = '" . $productid . "' ";

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

    foreach ($query->rows as $value) {
      $dados[] = array(
        'color'         => $value['color'],
        'size'           => $value['size'],
        'specification' => $value['specification'],
        'color_template' => $value['color_template_description'],
        'size_template'  => $value['size_template_description'],
        'specification_template' => $value['specification_template_description'],
      );
    }

    return $dados;
  }

  public function getProductCategories($product_id)
  {
      $product_category_data = array();

      $result = $this->db->query("
          SELECT nsub.category_id sub_category_id,ncat.category_id category_id,ndep.category_id dept_id
          FROM oc_product p
          INNER JOIN oc_category nsub ON (p.category_id  = nsub.category_id)
          INNER JOIN oc_category ncat ON (nsub.parent_id = ncat.category_id)
          LEFT  JOIN oc_category ndep ON (ncat.parent_id = ndep.category_id)
          WHERE p.product_id = '" . (int)$product_id . "'")->row;

          if ($result->num_rows>0){
            $product_category_data = array($result['sub_category_id'],$result['category_id']);
          }

          if ($result['dept_id'] !== null){
              $product_category_data[] = $result['dept_id'];
          }

      return $product_category_data;
  }

  public function getProductKitID($productID){
    $retorno = $this->db->query('SELECT product_kit_id FROM oc_product_kit WHERE product_id ='.$productID)->row;
    if (count($retorno)>0){
        return $retorno['product_kit_id'];
    }else{
        return null;
    }
  }

  public function getProductKitItens($product_kit_id, $totalPriceKit = 0)
  {
    $itens_kit = array();
    if ($product_kit_id) {
      $this->load->dmodel('catalog/product');
      $this->load->dmodel('tool/image');

      if ($this->customer->isLogged()) {
        $customer_group_id = $this->customer->getCustomerGroupId();
      } else {
        $customer_group_id = $this->config->get('config_customer_group_id');
      }

      $sql =  "SELECT
                  pkit.price price_kit,
                  (SELECT price FROM oc_product_discount pd2 WHERE pd2.product_id = pkit.product_id AND pd2.customer_group_id = $customer_group_id AND pd2.quantity = '1' AND ((pd2.date_start = '0000-00-00' OR pd2.date_start < NOW()) AND (pd2.date_end = '0000-00-00' OR pd2.date_end > NOW())) ORDER BY pd2.priority ASC, pd2.price ASC LIMIT 1) AS discount,
                  (select coalesce(perc_discount, 0) perc_discount from oc_order_discount  where (product_id = k.product_id) and (tipo_promocao = " . Type_Promocao::Tp_PercDesconto . ") and (status = " . Type_PromocaoStatus::Tp_Ativo . ") and ((date_start = '0000-00-00') or (date_start <= CURDATE())) and ((date_end = '0000-00-00') or (date_end >= CURDATE())) ORDER BY perc_discount DESC LIMIT 1) as order_perc_discount,
                  SUM(ki.quantity) total_quantity_kit
                  FROM oc_product_kit_item ki
                  INNER JOIN oc_product_kit k ON k.product_kit_id = ki.product_kit_id
                  INNER JOIN oc_product pkit  ON pkit.product_id = k.product_id
                  WHERE k.product_kit_id = $product_kit_id";

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

      $sql = "SELECT
              pki.product_kit_item_id,
              pki.product_kit_id,
              pk.id_futura product_kit_id_futura,
              pki.quantity quantidade,
              pd.name,
              pr.price discount,

              COALESCE(pr.price,p.price) * pki.quantity discount_total,
              p.*,
              COALESCE(p.price * pki.quantity, 0) price_total
          FROM oc_product_kit_item pki
          INNER JOIN oc_product_kit pk ON pk.product_kit_id = pki.product_kit_id
          INNER JOIN oc_product p ON p.product_id = pki.product_id
          INNER JOIN oc_product_description pd ON p.product_id = pd.product_id
          LEFT JOIN (SELECT * FROM oc_product_discount pd2 WHERE  pd2.customer_group_id = $customer_group_id AND pd2.quantity = '1' AND ((pd2.date_start = '0000-00-00' OR pd2.date_start < NOW()) AND (pd2.date_end = '0000-00-00' OR pd2.date_end > NOW())) ORDER BY pd2.priority ASC, pd2.price ) pr ON pr.product_id = p.product_id
          Where pki.product_kit_id = $product_kit_id and p.status = 1";

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

      $total_qtd_kit = $infos_price_kit['total_quantity_kit'];
      $total_kit_real = array_sum(array_column($itens_kit_model,'discount_total'));
      //if ($total_kit_real==0){
        //$total_kit_real = array_sum(array_column($itens_kit_model,'price_total'));
      //}

      if($totalPriceKit>0){
        $preco_kit_total = $totalPriceKit;
      }else{
        $preco_kit_total =  $infos_price_kit['discount'] ? $infos_price_kit['discount'] : $infos_price_kit['price_kit'];
        $preco_kit_total =  ($infos_price_kit['order_perc_discount']) ? ($preco_kit_total - (($infos_price_kit['order_perc_discount'] * 0.01) * $preco_kit_total)) : $preco_kit_total;
      }

      $diferenca =  $preco_kit_total - $total_kit_real;

      $deferenca_acumulado = 0;
      $sequence_key = 0;

      foreach ($itens_kit_model as $item_kit) {
        $sequence_key += 1;
        $preco_original = $item_kit['discount'] ? $item_kit['discount'] : $item_kit['price'];
        $fator = ($preco_original * $total_qtd_kit) / $total_kit_real;
        $diferenca_rateada = round( (($diferenca * $fator) / $total_qtd_kit), 2 );
        $deferenca_acumulado += $diferenca_rateada * $item_kit['quantidade'];

        if ($item_kit['image']) {
          $tumb_img_kit = $this->dmodel_tool_image->resize($item_kit['image'], $this->config->get('config_image_thumb_width'), $this->config->get('config_image_thumb_height'));
        } else {
          $tumb_img_kit = $this->dmodel_tool_image->resize('data/sem-imagem.png', $this->config->get('config_image_thumb_width'), $this->config->get('config_image_thumb_height'));
        }

        $itens_kit[] = array(
          'sequence_key'=> $sequence_key,
          'thumb'       => $tumb_img_kit,
          'quantidade'  => $item_kit['quantidade'],
          'descricao'   => $item_kit['name'],
          'model'       => $item_kit['model'],
          'href'        => $this->url->link('product/product', 'product_id=' . $item_kit['product_id']),
          'price'       => $preco_original,
          'acerto'      => $diferenca_rateada*$item_kit['quantidade'],
          'total'       => ($item_kit['discount'] ? $item_kit['discount'] : $item_kit['price']) * $item_kit['quantidade'],
          'product_id_futura'     => $item_kit['id_futura'],
          'product_id'            => $item_kit['product_id'],
          'product_kit_id'        => $item_kit['product_kit_id'],
          'product_kit_item_id'   => $item_kit['product_kit_item_id'],
          'product_kit_id_futura' => $item_kit['product_kit_id_futura']
        );
      }

      if(!empty($itens_kit) && $diferenca<>$deferenca_acumulado){
        $key =  array_key_last($itens_kit);
        $itens_kit[$key]['acerto'] = $itens_kit[$key]['acerto'] + ($diferenca-$deferenca_acumulado);
      }

    }
    return $itens_kit;
  }

  public function getProductByModel($product_model){
      $sql = "select p.product_id from oc_product p
              where p.model = '$product_model' ";
      $query = $this->db->query($sql);
      return $query->row;
  }
}
