<?php
defined('BASEPATH') OR exit('No direct script access allowed');

class Product_model extends CI_Model { 

  function list($where, $orderby, $offset, $limit) {

    $q = "SELECT P.product_id,P.title,P.status,P.product_type,P.handle,
        P.edited_at,PV.sku,PV.inventory_quantity,PV.price
       FROM sh_product P  
       LEFT JOIN sh_product_variant PV ON P.product_id = PV.product_id
       $where 
       $orderby 
       LIMIT  $offset , $limit";

    return $this->db->query($q)->result();
  }

  function detail($product_id) {
    $q = "SELECT P.product_id,P.title,P.status,product_type,handle,body_html,
        sku,barcode,inventory_quantity,price,compare_at_price,cost,inventory_policy,
        inventory_management,fulfillment_service,requires_shipping,taxable
       FROM sh_product P  
       LEFT JOIN sh_product_variant PV ON P.product_id = PV.product_id        
       WHERE P.product_id = '$product_id' ";

    return $this->db->query($q)->result();
  }

  function images($product_id) {
    $q = "SELECT src, position as image_position FROM sh_product_image WHERE product_id = '$product_id' ";

    return $this->db->query($q)->result();
  }

  function total($where) {
    $q = "SELECT COUNT(P.product_id) as total 
      FROM sh_product P  
      LEFT JOIN sh_product_variant PV ON P.product_id = PV.product_id 
      $where";
      
    return $this->db->query($q)->result();
  }

  function productApi($where = '') {
    $q = "SELECT p.product_id, title, handle, body_html, product_type, status, published, 
        seo_description, seo_title, p.added_at as sh_created_at, p.edited_at as sh_updated_at, 
        pv.variant_id, pv.sku, pv.barcode, pv.price, pv.compare_at_price, 
        pv.cost, pv.wholesale_price, pv.grams, pv.inventory_item_id, 
        pv.inventory_quantity, pv.inventory_policy, pv.inventory_management, 
        pv.fulfillment_service, pv.requires_shipping, pv.taxable, pv.position as variant_position 
      FROM sh_product p LEFT JOIN sh_product_variant pv ON p.product_id=pv.product_id 
      WHERE pv.sku != '' $where
      GROUP BY pv.sku";

      return $this->db->query($q)->result();
  }

         
}
