<?php
  
  class quotation_model extends CI_Model
  {
    function __construct()
    {
      parent::__construct();
    }

    function insert_quotation ($data) {
      $query = $this->db->query("SELECT ((COALESCE(MAX(id), 0)) + 1) as id FROM quotation");
      $id = $query->row()->id;

      // CREAR INSTITUCION SI NO EXISTE
      $customer_id = $data['customer_id'];
      $staff_id = $this->session->userdata('rt_staff');

      $this->db->query(
        "INSERT INTO quotation (id,staff_id,customer_id,total,discount_rate,discounts,in_progress,is_done,is_canceled,create_time,update_time,status,code,rut_beneficiario,nombre_beneficiario,apellido_beneficiario,giro_beneficiario,telefono_beneficiario,direccion_beneficiario,ciudad_beneficiario,comuna_beneficiario,correo_beneficiario,codigo_postal_beneficiario) VALUES (".$id.",".$staff_id.",".$customer_id.",".$data['total'].",".$data['discount_rate'].",".$data['discounts'].",true,false,false,CURRENT_TIMESTAMP,CURRENT_TIMESTAMP,0,'".time()."','".$data['rut_customer']."','".$data['first_name_customer']."','".$data['last_name_customer']."','".$data['turn_customer']."','".$data['phone_customer']."','".$data['address_customer']."','".$data['city_customer']."','".$data['commune_customer']."','".$data['email_customer']."','".$data['postal_customer']."')"
      );

      $query = $this->db->query("SELECT ((COALESCE(MAX(id), 0)) + 1) as id FROM quotation_details");
      $last_detail_id = intval($query->row()->id);

      foreach ($data['detalle'] as $index => $detail) {
        $this->db->query(
          "INSERT INTO quotation_details (id,products_id,quotation_id,quantity_items,item_price,discount_rate,discount,total_price,create_time,update_time,cod_sap,name) VALUES (".$last_detail_id.",".$detail['id'].",".$id.",".$detail['cantidad'].",".$detail['precio'].",0,0,".$detail['total'].",CURRENT_TIMESTAMP,CURRENT_TIMESTAMP,'".$detail['codigo_sap']."','".$detail['nombre']."')"
        );

        $last_detail_id += 1;
      }

      return $id;
    }

    function get_quotation ($id) {

      $query = $this->db->query(
        "SELECT quotation.id as \"id\",
        concat(staff.first_name, ' ', staff.last_name) as \"vendedor\",
        concat(customers.first_name) as \"institucion\",
        rut_beneficiario as \"rut_cliente\",
        direccion_beneficiario as \"direccion_cliente\",
        codigo_postal_beneficiario as \"codigo_postal_cliente\",
        concat(nombre_beneficiario, ' ', apellido_beneficiario) as \"nombre_cliente\",
        giro_beneficiario as \"giro_cliente\",
        telefono_beneficiario as \"telefono_cliente\",
        correo_beneficiario as \"correo_cliente\",
        comuna_beneficiario as \"comuna_cliente\",
        ciudad_beneficiario as \"ciudad_cliente\",
        quotation.total as \"total\",
        quotation.doc_num as \"numero\",
        quotation.status as \"status\",
        to_char(quotation.create_time, 'DD/MM/YYYY') as \"fecha\",
        to_char(quotation.create_time, 'HH24:MI:SS') as \"hora\"
        FROM quotation
        INNER JOIN staff ON quotation.staff_id = staff.id
        INNER JOIN customers ON quotation.customer_id = customers.id
        WHERE quotation.id = ".$id
      );

      if ($query->num_rows() === 0) {
        return NULL;
      }

      return $query->row();
    }

    function get_detail ($quotation_id) {
      $query = $this->db->query(
        "select quotation_details.name as \"producto\",
        quotation_details.id as \"id\",
        quotation_details.quantity_items as \"cantidad\",
        quotation_details.total_price as \"total\",
        quotation_details.item_price as \"precio\",
        inventory_products.stock_on_hand as \"stock\"
        from quotation_details
        inner join inventory_products
        on quotation_details.products_id = inventory_products.products_id
        where quotation_details.quotation_id = ".$quotation_id."
        order by quotation_details.id ASC"
      );

      return $query->result();
    }

    function validate_quotation ($quotation_id) {
      $query = $this->db->query(
        "select
        sum(case quotation_details.quantity_items > inventory_products.stock_on_hand WHEN true then 1 else 0 end ) as valid
        from quotation_details
        inner join inventory_products
        on quotation_details.products_id = inventory_products.products_id
        where quotation_details.quotation_id = ".$quotation_id
      );

      $valid = $query->row()->valid;
      return intval($valid) === 0;
    }

    function get_product ($sku) {
      $query = $this->db->query(
        "select
        id, name as \"nombre\", institution_price as \"precio\",
        cod_sap as \"codigo_sap\"
        from products
        where sku = '".$sku."'
        or name = '".$sku."'
        limit 1"
      );

      if ($query->num_rows() === 0) {
        return NULL;
      }

      return $query->row(); 
    }

    function get_quotation_status ($code) {
      $query = $this->db->query(
        "select id, status
        from quotation
        where quotation.code = '".$code."'"
      );

      if ($query->num_rows() === 0) {
        return NULL;
      }

      return $query->row();
    }
  }