<?php

class DModelaccountcustomer extends Model
{

  public function getCustomer($customer_id)
  {
    $query = $this->db->query("SELECT DISTINCT * FROM " . DB_PREFIX . "customer WHERE customer_id = '" . (int)$customer_id . "'");

    return $query->row;
  }

  public function getIps($customer_id)
  {
    $query = $this->db->query("SELECT * FROM `" . DB_PREFIX . "customer_ip` WHERE customer_id = '" . (int)$customer_id . "'");

    return $query->rows;
  }

  public function isBanIp($ip)
  {
    $query = $this->db->query("SELECT * FROM `" . DB_PREFIX . "customer_ban_ip` WHERE ip = '" . $this->db->escape($ip) . "'");

    return $query->num_rows;
  }

  public function getLastOrders($customer_id,$status_id, $limit){
    $query = $this->db->query("SELECT * FROM `" . DB_PREFIX . "order` WHERE customer_id = '" . (int)$customer_id . "' and order_status_id = '" . (int)$status_id . "' ORDER BY order_id DESC LIMIT $limit");

    return $query->rows;
  }

  public function getAddress($customer_id) {
      $query = $this->db->query("SELECT DISTINCT * FROM " . DB_PREFIX . "address a INNER JOIN " . DB_PREFIX . "customer c ON c.address_id = a.address_id WHERE c.customer_id = '" . (int)$customer_id ."'");

      return $query->row;
  }

  public function getMetrics($customer_id, $params = array()){
    $result = array();

    if (count($params)==0 || $params['qtd_pedidos']){
      $result['qtd_pedidos'] = $this->db->query("SELECT COUNT(*) AS qtd_pedidos FROM oc_order WHERE customer_id = $customer_id")->row["qtd_pedidos"];
    }
    if (count($params)==0 || $params['first_order']){
      $query = $this->db->query("SELECT
      o.date_added as data_primeiro_pedido,
      DATEDIFF(NOW(),o.date_added) AS dias_primeiro_pedido
      FROM oc_order o WHERE customer_id = $customer_id
      ORDER BY order_id asc LIMIT 1")->row;
      $result['data_primeiro_pedido'] = $query['data_primeiro_pedido'];
      $result['dias_primeiro_pedido'] = $query['dias_primeiro_pedido'];
    }
    if (count($params)==0 || $params['last_order']){
      $query = $this->db->query("SELECT
      o.date_added as data_ultimo_pedido,
      DATEDIFF(NOW(),o.date_added) AS dias_ultimo_pedido
      FROM oc_order o WHERE customer_id = $customer_id
      ORDER BY order_id desc LIMIT 1")->row;
      $result['data_ultimo_pedido'] = $query['data_ultimo_pedido'];
      $result['dias_ultimo_pedido'] = $query['dias_ultimo_pedido'];
    }
    if (count($params)==0 || $params['media_compra']){
      $result['media_compras'] = $this->db->query("SELECT
      COALESCE(avg(o.total),0) media
      FROM oc_order o
      WHERE o.customer_id = $customer_id
      and o.date_added BETWEEN CURDATE() - INTERVAL 180 DAY AND CURDATE()")->row["media"];
    }

    return $result;

  }

  public function createOCCustomer($data)
  {

    try {
      $this->db->startTransaction();

      //como essa info nao vem dos marketplaces, ele adiciona o customer com padrao de NAO CONTRIBUENTE
      $defaultContribuinte = Type_PjContribuoIcms::Tp_NaoContribuinte;

      $customer_type = '';
      if(isset($data['cpf']) && $data['cpf'] != ''){
        $customer_type = Type_CadastroPessoa::Tp_Fisico;
      }  elseif(isset($data['cnpj']) && $data['cnpj'] != ''){
        $customer_type = Type_CadastroPessoa::Tp_Juridico;
      }

      // oc_customer
      $this->db->query("
          INSERT INTO " . DB_PREFIX . "customer SET
          status =1,
          approved = 1,
          newsletter = 0,
          customer_group_id = " . (int)$this->config->get('config_customer_group_id') . ",
          email = '" . $this->db->escape($data['email']) . "',
          firstname = '" . $this->db->escape($data['firstname']) . "',
          lastname = '" . $this->db->escape($data['lastname']) . "',
          telephone = '" . $this->db->escape($data['telephone']) . "',
          cpf = '" . $this->db->escape($data['cpf']) . "',
          cnpj = '" . $this->db->escape($data['cnpj']) . "',
          razao_social = '" . $this->db->escape($data['razao_social']) . "',
          ip = '" . $this->db->escape($this->request->server['REMOTE_ADDR']) . "',
          password = '" . $this->db->escape(md5(rand())) . "',
          origem_cadastro = '" . $this->db->escape(Type_OrigemSistema::Tp_Marketplace) . "',
          cadastro_atualizado_ml = '" . $this->db->escape(Type_DsimDnao::Tp_Nao) . "',
          mkt_integracao_tipo = '" . $this->db->escape($data['mkt_integracao_tipo']) . "',
          mkt_tipo = '" . $this->db->escape($data['mkt_tipo']) . "',
          mkt_buyer_id = '" . $this->db->escape($data['mkt_buyer_id']) . "',
          sexo = '" . $this->db->escape($data['genero']) . "',
          data_nascimento = '" . $this->db->escape($data['birth_day']) . "',
          contribuinte = '" . $defaultContribuinte . "',
          customer_type = '" . $customer_type . "',
          date_added = NOW()
        ");

      $customer_id = $this->db->getLastId();

      // oc_address

      //endereço faturamento
      $this->db->query("
          INSERT INTO " . DB_PREFIX . "address SET
          customer_id = " . (int)$customer_id . ",
          firstname = '" . $this->db->escape($data['firstname']) . "',
          lastname = '" . $this->db->escape($data['lastname']) . "',
          address_1 = '" . $this->db->escape($data['adresses']['billing']['address_1']) . "',
          address_2 = '" . $this->db->escape($data['adresses']['billing']['address_2']) . "',
          numero    = '" . $this->db->escape($data['adresses']['billing']['numero']) . "',
          city = '" . $this->db->escape($data['adresses']['billing']['city']) . "',
          zone_id = " . (int)$data['adresses']['billing']['state_zone_id'] . ",
          postcode = '" . $this->db->escape($data['adresses']['billing']['postcode']) . "',
          country_id = " . (int)$data['adresses']['billing']['dadosPais']['country_id'] . ",
          tipo_logradouro    = '" . $this->db->escape($data['adresses']['billing']['tipo_logradouro']) . "',
          logradouro    = '" . $this->db->escape($data['adresses']['billing']['logradouro']) . "',
          codigo_municipio = " . (int)$data['adresses']['billing']['codigo_municipio'] . ",
          complemento = '" . $this->db->escape($data['adresses']['billing']['complemento']) . "'
        ");

      $address_id = $this->db->getLastId();
      $this->db->query("UPDATE " . DB_PREFIX . "customer SET address_id = " . (int)$address_id . " WHERE customer_id = " . (int)$customer_id);

      //endereço entrega
      $this->db->query("
          INSERT INTO " . DB_PREFIX . "address SET
          customer_id = " . (int)$customer_id . ",
          firstname = '" . $this->db->escape($data['firstname']) . "',
          lastname = '" . $this->db->escape($data['lastname']) . "',
          address_1 = '" . $this->db->escape($data['adresses']['shipping']['address_1']) . "',
          address_2 = '" . $this->db->escape($data['adresses']['shipping']['address_2']) . "',
          numero    = '" . $this->db->escape($data['adresses']['shipping']['numero']) . "',
          city = '" . $this->db->escape($data['adresses']['shipping']['city']) . "',
          zone_id = " . (int)$data['adresses']['shipping']['state_zone_id'] . ",
          postcode = '" . $this->db->escape($data['adresses']['shipping']['postcode']) . "',
          country_id = " . (int)$data['adresses']['shipping']['dadosPais']['country_id'] . ",
          tipo_logradouro    = '" . $this->db->escape($data['adresses']['shipping']['tipo_logradouro']) . "',
          logradouro    = '" . $this->db->escape($data['adresses']['shipping']['logradouro']) . "',
          codigo_municipio = " . (int)$data['adresses']['shipping']['codigo_municipio'] . ",
          complemento = '" . $this->db->escape($data['adresses']['shipping']['complemento']) . "'
        ");

      $this->db->commit();
    } catch (Exception $e) {
      $this->db->rollback();
      throw new exception('Erro ao baixar pedido. Informações do comprador não encontradas.'.$e->getMessage());
    }

    return $customer_id;

  }

  public function updateOCCustomerMktPlace($customer_id, $data)
  {
    try {
      $this->db->startTransaction();
      $this->db->query("
          UPDATE " . DB_PREFIX . "customer SET
          email = '" . $this->db->escape($data['email']) . "',
          firstname = '" . $this->db->escape($data['firstname']) . "',
          lastname = '" . $this->db->escape($data['lastname']) . "',
          telephone = '" . $this->db->escape($data['telephone']) . "',
          cpf = '" . $this->db->escape($data['cpf']) . "',
          cnpj = '" . $this->db->escape($data['cnpj']) . "',
          razao_social = '" . $this->db->escape($data['razao_social']) . "',
          sexo = '" . $this->db->escape(!empty($data['genero']) ? $data['genero'] : null) . "',
          data_nascimento = '" . $this->db->escape(!empty($data['birth_day']) ? $data['birth_day'] : null) . "'
          where customer_id = " . $this->db->escape($customer_id)
      );

      $this->db->commit();
    } catch (Exception $e) {
      $this->db->rollback();
      throw new exception('Erro ao baixar pedido. Informações do comprador não encontradas.'.$e->getMessage());
    }

    return $customer_id;

  }

  public function updateOCCustomerAddressMktPlace($address_id, $data, $customerData)
  {
    try {
      $this->db->startTransaction();
      $this->db->query("
          UPDATE " . DB_PREFIX . "address SET
          firstname = '" . $this->db->escape($customerData['firstname']) . "',
          lastname = '" . $this->db->escape($customerData['lastname']) . "',
          address_1 = '" . $this->db->escape($data['address_1']) . "',
          address_2 = '" . $this->db->escape($data['address_2']) . "',
          numero    = '" . $this->db->escape($data['numero']) . "',
          city = '" . $this->db->escape($data['city']) . "',
          zone_id = " . (int)$data['state_zone_id'] . ",
          postcode = '" . $this->db->escape($data['postcode']) . "',
          country_id = " . (int)$data['dadosPais']['country_id'] . ",
          tipo_logradouro    = '" . $this->db->escape($data['tipo_logradouro']) . "',
          logradouro    = '" . $this->db->escape($data['logradouro']) . "',
          codigo_municipio = " . (int)$data['codigo_municipio'] . ",
          complemento = '" . $this->db->escape($data['complemento']) . "'
          where address_id = " . $this->db->escape($address_id)
      );

      $address_id = $this->db->getLastId();

      $this->db->commit();
    } catch (Exception $e) {
      $this->db->rollback();
      throw new exception('Erro ao baixar pedido. Informações do endereço do comprador não encontradas.'.$e->getMessage());
    }

    return $address_id;
  }

    public function updateOCCustomerName($customer_id, $data)
    {
        try {
            $this->db->startTransaction();
            $this->db->query("
              UPDATE " . DB_PREFIX . "customer SET
              firstname = '" . $this->db->escape($data['firstname']) . "',
              lastname = '" . $this->db->escape($data['lastname']) . "'
              where customer_id = " . $this->db->escape($customer_id)
            );

            $this->db->commit();
        } catch (Exception $e) {
            $this->db->rollback();
            throw new exception('Erro ao baixar pedido. Erro ao atualizar nome do cliente. Exception: '.$e->getMessage());
        }

        return $customer_id;

    }

  public function getTotalCustomersByCnpjCpf($dados)
  {

    $sql = "SELECT * FROM " . DB_PREFIX . "customer where ";

    if (strlen($dados) > 11) {
      $sql .= " cnpj = '" . $this->db->escape($dados) . "' ";
    } else {
      $sql .= " cpf = '" . $this->db->escape($dados) . "' ";
    }
    $query = $this->db->query($sql);

    return $query->row;

  }

  public function getCustomerByEmail($email)
  {

    $sql = "SELECT * FROM " . DB_PREFIX . "customer where email = '$email'";

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

    return $query->row;

  }

  public function getCustomerByMktBuyerId($mkt_buyer_id)
  {
    $sql = "SELECT * FROM " . DB_PREFIX . "customer
            where mkt_buyer_id = '" . $mkt_buyer_id .  "'
            order by date_added desc
            limit 1";

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

    return $query->row;
  }

  public function getCustomerByMktNick($nick)
  {
    $sql = "SELECT * FROM " . DB_PREFIX . "customer
            where lastname like '%" . $nick .  "%'
            order by date_added asc
            limit 1";

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

    return $query->row;
  }

  public function updateMktBuyerId($mkt_buyer_id, $customer_id)
  {

    $sql = "UPDATE oc_customer SET mkt_buyer_id = ".$mkt_buyer_id." WHERE customer_id = ".$customer_id;
    $query = $this->db->query($sql);

  }

  public function updateOCCustomerBillingAddress($customerData, $addressData)
  {
    if (isset($customerData['address_id']) && isset($addressData['billing'])) {
      $billingAddressId = $customerData['address_id'];
      
      if ($addressData['billing']['city'] != "Mercadolivre" && $addressData['billing']['address_1'] != "Mercadolivre" && $addressData['billing']['postcode'] != "00000000") {
        $this->updateOCCustomerAddressMktPlace($billingAddressId, $addressData['billing'], $customerData);
      }
    }
  }
}