<?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 setParcelsTable($product_price, $parcel_number, $parcel_with_discounts, $discount_parcels_value, $parcel_with_no_fees, $increase_value, $minimum_parcel = null)
  {
    $table_parcels = [];

    $totalMinimalParcels = 0;
    $totalDiscountParcels = 0;
    $totalParcelsWithNoFees = 0;

    $i = 0;

    if(isset($minimum_parcel)){
      $price = null;

      while ($i < $parcel_with_discounts) {
        $discount_parcels_value = !empty($discount_parcels_value) ? str_replace(['%', ','], ['', '.'], $discount_parcels_value) : 0;
        $total_value_with_discounts = $product_price - ($product_price * ($discount_parcels_value / 100));

        if($total_value_with_discounts / ($i + 1) > $minimum_parcel){
          $price = $total_value_with_discounts / ($i + 1);

          $table_parcels['values'][] = array(
            'value' => $this->currency->format($total_value_with_discounts / ($i + 1)),
            'total' => $this->currency->format($total_value_with_discounts)
          );

          $totalMinimalParcels += 1;
          $totalDiscountParcels += 1;
        }

        $i++;
      }
  
      if($totalMinimalParcels == 0){
        $i = 0;
      }else{
        $parcel_with_no_fees = $parcel_with_no_fees + $i; 
      }
  
      while ($i < $parcel_with_no_fees) {

        if(($product_price / ($i + 1)) > $minimum_parcel){
          $price = $product_price / ($i + 1);
          $table_parcels['values'][] = array(
            'value' => $this->currency->format(($product_price / ($i + 1))),
            'total' => $this->currency->format($product_price)
          );
          
          $totalMinimalParcels += 1;
          $totalParcelsWithNoFees += 1;
        }

        $i++;
      }

      if($totalMinimalParcels == 0){
        $i = 0;
      }
  
      while ($i < $parcel_number) {
        $total_value_with_fees = ftr_geral::juroComposto($product_price, ($i + 1), $increase_value);

        if(($total_value_with_fees / ($i + 1)) > $minimum_parcel){
          $price = $total_value_with_fees / ($i + 1);
          $table_parcels['values'][] = array(
            'value' => $this->currency->format(($total_value_with_fees / ($i + 1))),
            'total' => $this->currency->format($total_value_with_fees)
          );

          $totalMinimalParcels += 1;
        }

        $i++;
      }

      if($price){
        $price = number_format($price, 2,',','.');
      }else{
        $price = $product_price;
      }

      if(!isset($table_parcels['values'])){
        $table_parcels['values'] = [];
      }

      $table_parcels['price'] = $price;
      $table_parcels['total_minimal_parcels'] = $totalMinimalParcels;
      $table_parcels['total_discount_parcels'] = $totalDiscountParcels;
      $table_parcels['total_parcel_no_fees'] = $totalParcelsWithNoFees;
      $table_parcels['minimum_parcel'] = $minimum_parcel;

      return $table_parcels;
    }else{
      while ($i < $parcel_with_discounts) {
        $discount_parcels_value = !empty($discount_parcels_value) ? str_replace(['%', ','], ['', '.'], $discount_parcels_value) : 0;
        $total_value_with_discounts = $product_price - ($product_price * ($discount_parcels_value / 100));
        $table_parcels[$i]['value'] = $this->currency->format($total_value_with_discounts / ($i + 1));
        $table_parcels[$i]['total'] = $this->currency->format($total_value_with_discounts);
        $i++;
      }
  
      $parcel_with_no_fees = $parcel_with_no_fees + $i; 
  
      while ($i < $parcel_with_no_fees) {
        $table_parcels[$i]['value'] = $this->currency->format(($product_price / ($i + 1)));
        $table_parcels[$i]['total'] = $this->currency->format($product_price);
        $i++;
      }
  
      while ($i < $parcel_number) {
        $total_value_with_fees = ftr_geral::juroComposto($product_price, ($i + 1), $increase_value);
        $table_parcels[$i]['value'] = $this->currency->format(($total_value_with_fees / ($i + 1)));
        $table_parcels[$i]['total'] = $this->currency->format($total_value_with_fees);
        $i++;
      }
  
      return $table_parcels;
    }

  }

  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 (!empty($result)) {
      $product_category_data = array($result['sub_category_id'], $result['category_id']);
    }

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

    return $product_category_data;
  }

  public function getProductCategory($product_id){
    $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(count($result) > 0){
      return $result['category_id'];
    }else{
      return null;
    }
  }

  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' => intval($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;
  }
}