<?php 
class User_Model extends CI_Model {

    public function create_user($data) {
        $this->db->insert("user_table", $data);
        return $this->db->insert_id("user_user_id_seq");
    }

    public function update_user($data, $user_id) {
        $this->db->where("user_id", $user_id)
                 ->update("user_table", $data);
       
        return $this->db->affected_rows();
    }

    public function get_user_by_email($email, $lang_code = 1) {

        return $this->db->select("user_table.*, country_name,verified_status")
                        ->from("user_table")
                        ->join("country", "country.country_id = user_table.user_country_id and country.country_language_code = {$lang_code} ", "left")
                        ->join("seller_details","seller_details.user_id = user_table.user_id","left")
                        ->where("user_email_id", $email)
                        ->get()
                        ->row();
    }

    public function get_user_by_id($user_id, $lang_code = 1) {

        return $this->db->select("user_table.*, country_name")
                        ->from("user_table")
                        ->join("country", "country.country_id = user_table.user_country_id and country.country_language_code = {$lang_code} ", "left")
                        ->where("user_id", $user_id)
                        ->get()
                        ->row();
    }

    public function get_user($condition, $lang_code = 1) {

        return $this->db->select("user_table.*, country_name")
                        ->from("user_table")
                        ->join("country", "country.country_id = user_table.user_country_id and country.country_language_code = {$lang_code} ", "left")
                        ->where($condition)
                        ->get()
                        ->row();
                       // echo $this->db->last_query();
    }
    
    public function get_cart_details($user_id) {
        return $this->db->select("*")
                        ->from("cart")
                        ->where("user_id",$user_id)
                        ->get()
                        ->result();
    }

    public function create_user_address($data) {
        $this->db->insert("user_shiping_details", $data);
        return $this->db->insert_id("user_shiping_details_user_shiping_details_id_seq");
    }

    public function update_user_address($data, $condition) {
        $this->db->where($condition)
                  ->update("user_shiping_details", $data);
        return $this->db->affected_rows();
    }

    public function delete_user_address($shipping_address_id) {
        $this->db->where("user_shiping_details_id", $shipping_address_id)
                  ->delete("user_shiping_details");
        return $this->db->affected_rows();
    }

    public function get_user_address($user_id, $lang_code = "1") {
        return $this->db->select("*, user_shiping_details.first_name as s_first_name, user_shiping_details.last_name as s_last_name ")
                        ->from("user_shiping_details")
                        ->join("user_table", "user_table.user_id = user_shiping_details.user_shiping_details_user_id", "left")
                        ->join("country", "country.country_id = user_shiping_details.user_shiping_country_id and country_language_code={$lang_code}", "left")
                        ->join("city", "city.city_id = user_shiping_details.user_shiping_details_city and city_language_code={$lang_code}", "left")
                        ->where("user_shiping_details_user_id", $user_id)
                        ->where("user_shiping_details_status", 1)
                        ->where("user_shiping_details_loc_type", 0)
                        ->order_by("default_address_status desc, user_shiping_details_id desc")
                        ->get()
                        ->result();
    }
    public function get_user_billing_address($user_id, $lang_code = "1") {
        return $this->db->select("*, user_shiping_details.user_shiping_details_loc_type AS address_type, user_shiping_details.first_name as s_first_name, user_shiping_details.last_name as s_last_name ")
                        ->from("user_shiping_details")
                        ->join("user_table", "user_table.user_id = user_shiping_details.user_shiping_details_user_id", "left")
                        ->join("country", "country.country_id = user_shiping_details.user_shiping_country_id and country_language_code={$lang_code}", "left")
                        ->join("city", "city.city_id = user_shiping_details.user_shiping_details_city and city_language_code={$lang_code}", "left")
                        ->where("user_shiping_details_user_id", $user_id)
                        ->where("user_shiping_details_status", 1)
                        ->where("user_shiping_details_loc_type", 1)
                        ->order_by("default_address_status desc, user_shiping_details_id desc")
                        ->get()
                        ->result();
    }

    public function get_user_address_by_id($user_id, $shipping_id, $lang_code = "1") {
         return $this->db->select("*, user_shiping_details.first_name as s_first_name, user_shiping_details.last_name as s_last_name,user_shiping_details.user_shiping_zipcode")
                        ->from("user_shiping_details")
                        ->join("user_table", "user_table.user_id = user_shiping_details.user_shiping_details_user_id", "left")
                        ->join("country", "country.country_id = user_shiping_details.user_shiping_country_id and country_language_code={$lang_code}", "left")
                        ->join("city", "city.city_id = user_shiping_details.user_shiping_details_city and city_language_code={$lang_code}", "left")
                        ->where("user_shiping_details_user_id", $user_id)
                        ->where("user_shiping_details_id", $shipping_id)
                        ->where("user_shiping_details_status", 1)
                        ->order_by("user_shiping_details_id", "desc")
                        ->get()
                        ->row();//echo $this->db->last_query();exit;
    } 

    public function get_user_default_address($user_id, $lang_code = "1") {
         $this->db->select("*, user_shiping_details.first_name as s_first_name, user_shiping_details.last_name as s_last_name")
                        ->from("user_shiping_details")
                        ->join("user_table", "user_table.user_id = user_shiping_details.user_shiping_details_user_id", "left")
                        ->join("country", "country.country_id = user_shiping_details.user_shiping_country_id and country_language_code={$lang_code}", "left")
                        ->join("city", "city.city_id = user_shiping_details.user_shiping_details_city and city_language_code={$lang_code}", "left")
                        ->where("user_shiping_details_user_id", $user_id)
                        ->where("default_address_status", 1)
                        ->get()
                        ->row();
    }

    public function get_favourites($user_id, $lang_code = "1", $limit = "", $filter = "", $sort = "", $device_cart_id='') {
        return $this->db->query(
            $this->get_favourites_query($user_id, $lang_code , $limit, $filter , $sort)
            )->result();
    }

    public function get_favourites_count($user_id, $lang_code = "1", $filter = "") {
        return $this->db->query( "select count(*) as count from (" .
                                    $this->get_favourites_query($user_id, $lang_code , "", $filter )
                                . " ) as product_fav_count"
                                )->row()->count;
    }

    public function get_favourites_query($user_id, $lang_code = "1", $limit = "", $filter = "", $sort = "", $device_cart_id='') {

        $cart_select = "0 AS cart_quantity,";
        $cart_join = "";
        if ( $user_id > 0 || !empty($device_cart_id) ) {
            $cart_select = "COALESCE(cart.quantity, 0) AS cart_quantity,";

            if ( $user_id > 0 ) {
                $cart_join = "left join cart on product_attribute.product_attribute_id = cart.product_attribute_id and cart.user_id = '{$user_id}'";
            } else {
                $cart_join = "left join cart on product_attribute.product_attribute_id = cart.product_attribute_id and cart.user_id = '0' AND cart.anonimous_id = '{$device_cart_id}'";
            }
        }

        return "select 
                                        product.product_id,
                                        product.product_name,
                                        product.product_name_arabic,
                                        product.product_type,
                                        product.product_desc_full,
                                        product.product_desc_full_arabic,
                                        product.product_desc_short,
                                        product.product_desc_short_arabic,
                                        product.product_sale_from,
                                        product.product_sale_to,
                                        product.product_tag,
                                        product.product_status,
                                        product.product_variation_type,
                                        product.product_taxable,

                                        product.product_unique_iden,
                                        product.product_brand_id,
                                        product_attribute.product_attribute_id,
                                        product_attribute.manage_stock,
                                        product_attribute.stock_quantity,
                                        product_attribute.allow_back_order,
                                        product_attribute.stock_status,
                                        product_attribute.sold_individually,
                                        product_attribute.weight,
                                        product_attribute.length,
                                        product_attribute.height,
                                        product_attribute.width,
                                        product_attribute.shipping_class,
                                        product_attribute.sale_price,
                                        product_attribute.regular_price,
                                        product_attribute.image as product_image,
                                        product_attribute.image,
                                        product_attribute.shipping_note,
                                        product_attribute.title as pa_title,

                                        favourate.id as favourate_id,
                                        favourate.favourate_added_time,

                                        pv_str.product_attr_variation,
                                        pv_str.product_attr_variation_arabic,

                                        pv_str.attribute_ids,
                                        pv_str.attribute_values_ids,

                                        {$cart_select}

                                        (select sum(purchase_qty) from temp_product_order_history where temp_product_order_history.product_id = product.product_id group by temp_product_order_history.product_id limit 1 ) as product_on_process,
                                        (select sum(purchase_qty) from product_order_history where product_order_history.product_id = product.product_id  group by product_order_history.product_id limit 1) as product_order_placed 
                                from favourate 
                                inner join product on product.product_id = favourate.product_id	
                                left join product_attribute on  product_attribute.product_attribute_id = favourate.product_attribute_id
                                {$cart_join} 
                                left join (select 
                                                product_variations.product_attribute_id, 
                                                string_agg(attribute_values::text, ', ') as product_attr_variation, 
                                                string_agg(attribute_values_arabic::text, ', ') as product_attr_variation_arabic,
                                                string_agg(product_variations.attribute_id::text, ', ') as attribute_ids,
                                                string_agg(product_variations.attribute_values_id::text, ', ') as attribute_values_ids
                                            from product_variations 
                                            left join attribute_values on attribute_values.attribute_values_id = product_variations.attribute_values_id
                                            left join attribute on attribute.attribute_id = product_variations.attribute_id 
                                            group by product_variations.product_attribute_id) pv_str on pv_str.product_attribute_id = favourate.product_attribute_id  
                                where favourate.user_id = '{$user_id}'  {$filter} order by favourate.id	desc  $limit ";
    }

    public function delete_user_password_requests($user_id) {
        return $this->db->where("user_id", $user_id)
                        ->delete("password_reset");
    }

    public function create_passoword_reset_request($data) {
        $this->db->insert("password_reset", $data);
       // echo $this->db->last_query();exit;
        return $this->db->insert_id("password_reset_reset_id_seq");
    }
    
 function checkLinkExpired($userId,$code)
        {
              $now = gmdate("Y-m-d H:i:s");
              // $now= '2018-12-07 08:38:31';
              $sql = " select * from password_reset where user_id=".$userId." and reset_request_date<='".$now."' and reset_code= '".$code."'" ; 
              $rs = $this->db->query($sql);
              return $row = $rs->result();
        }

    public function get_country($condition) {
        return $this->db->select("*")
                        ->from("country")
                        ->where($condition)
                        ->get()
                        ->row();
    }

    public function create_temp_user($data) {
        $this->db->insert("user_table_temp", $data);
        return $this->db->insert_id("user_table_temp_temp_user_id_seq");
    }

    public function update_temp_user($data, $user_id) {
        $this->db->where("temp_user_id", $user_id)
                 ->update("user_table_temp", $data);

        return $this->db->affected_rows();
    }

    public function get_temp_user($condition, $lang_code = 1) {

        return $this->db->select("user_table_temp.*, country_name")
                        ->from("user_table_temp")
                        ->join("country", "country.country_id = user_table_temp.user_country_id and country.country_language_code = {$lang_code} ", "left")
                        ->where($condition)
                        ->get()
                        ->row();
    }

    public function create_user_transfer($data) {
        $this->db->insert("cash_point_transaction", $data);
        return $this->db->insert_id("cash_point_transaction_cp_transaction_id_seq");
    }

    public function update_user_transfer($data, $cp_transaction_id) {

        $this->db->where("cp_transaction_id", $cp_transaction_id)
                ->update("cash_point_transaction", $data);

        return $this->db->affected_rows();

    }

    public function get_transaction($condition) {
        return $this->db->select("*")
                        ->from("cash_point_transaction")
                        ->where($condition)
                        ->get()
                        ->row();
    }

    public function delete_previous_temp_transactions($user_id) {
        return $this->db->where("from_user_id", $user_id)
                        ->delete("cash_point_transaction");
    }

    public function create_user_cash_points($data) {
        $this->db->insert("cash_points", $data);
        return $this->db->insert_id("cash_points_cash_points_id_seq");
    }

    public function create_user_transfer_points($data) {
        $this->db->insert("cash_points_transfer", $data);
        return $this->db->insert_id("cash_points_transfer_cp_transfer_id_seq");
    }

    public function create_coupon_usage($data) {
        $this->db->insert("coupon_usage", $data);
        return $this->db->insert_id("coupon_usage_coupon_usage_id_seq");
    }

    public function get_coupon_usage($condition) {
        return $this->db->select("*")
                        ->from("coupon_usage")
                        ->where($condition)
                        ->get()
                        ->row();
    }

    public function get_coupon_usage_count($condition) {
        return $this->db->select("count(*) as count")
                        ->from("coupon_usage")
                        ->where($condition)
                        ->get()
                        ->row()
                        ->count;
    }

    public function get_coupon($condition) {
        return $this->db->select("*")
                        ->from("coupon")
                        ->where($condition)
                        ->get()
                        ->row();
    }

    public function get_countries($condition) {
        return $this->db->select("country_id::text as country_id, country_name, country_dial_code::text as dial_code", false)
                        ->from("country")
                        ->where($condition)
                        ->get()
                        ->result();
    }

    public function get_cities($condition) {
        return $this->db->select("city_id::text as city_id, city_name", false)
                        ->from("city")
                        ->where($condition)
                        ->order_by("city_name", "asc")
                        ->get()
                        ->result();
    }

    public function get_user_cash_point_transaction_query($user_id, $limit = "", $filter = "", $sort = "") {
        return "select 
                    cash_points.*,
                    product_order_details.order_no,
                    (from_user_table.user_first_name || ' ' || from_user_table.user_last_name) as from_user_name,
                    (to_user_table.user_first_name || ' ' || to_user_table.user_last_name) as to_user_name
                from cash_points 
                left join product_order_details on product_order_details.order_block_id=cash_points.order_block_id 
                left join cash_points_transfer on cash_points_transfer.cp_transfer_id = cash_points.transfer_id	
                left join user_table from_user_table on from_user_table.user_id = cash_points_transfer.from_user_id 
                left join user_table to_user_table on to_user_table.user_id = cash_points_transfer.to_user_id 
                where cash_points.cp_user_id = {$user_id} {$filter} {$sort} {$limit}";
    }

    public function get_user_cash_point_transaction_count($user_id, $filter = "") {
        return $this->db->query( "select count(*) as count from (" .
                                    $this->get_user_cash_point_transaction_query($user_id, "", $filter )
                                . " ) as fav_count"
                                )->row()->count;
    }

    public function get_user_cash_point_transaction($user_id, $limit = "", $filter = "", $sort = "") {
        return $this->db->query(
                                $this->get_user_cash_point_transaction_query($user_id, $limit, $filter, $sort)
                                )->result();
    }

    public function get_order_count($order_block_id){
        $this->db->select("count(product_id) as product_count");
        $this->db->from("product_order_history");
        $this->db->where("order_block_id",$order_block_id);
        return $this->db->get()->row();
    }

    private function _getUserOrderHistoryQuery( $params, $limit=0, $offset=0 )
    {
        $language = ($this->session->userdata('language') ? $this->session->userdata('language') : 1);
        $language = $language > 0 ? $language : 1;

        if ( isset($params['user_id']) && ($params['user_id']) ) {
            $this->db->where('pod.user_id', $params['user_id']);
        }
        if ( isset($params['search_key']) && $params['search_key'] ) {
            $search_key_where = "(pod.order_block_id::text ILIKE '%" . $params['search_key'] . "%' OR pod.order_no ILIKE '%" . $params['search_key'] . "%' OR LOWER(p.product_name) ILIKE '%" . $params['search_key'] . "%')";
            $this->db->where($search_key_where);
        }        
        if ( isset($params['order_start_date']) && $params['order_start_date'] ) {
            $this->db->where('DATE(pod.order_placed_date) >=', $params['order_start_date']);
        }
        if ( isset($params['order_end_date']) && $params['order_end_date'] ) {
            $this->db->where('DATE(pod.order_placed_date) <=', $params['order_end_date']);
        }
        if ( isset($params['order_status']) && ($params['order_status'] >= 0) ) {
            $this->db->where('poh.deliver_status', $params['order_status']);
        }
        if ( isset($params['order_block_id']) && ($params['order_block_id'] >= 0) ) {
            $this->db->where('pod.order_block_id', $params['order_block_id']);
        }
        if ( $limit > 0 ) {
            $this->db->limit($limit);
        }
        if ( $offset ) {
            $this->db->offset($offset);
        }
        $query = $this->db->select('DISTINCT ON (pod.order_block_id) pod.order_block_id, pod.user_id, pod.shipping_charge, poh.deliver_status, pod.measurement_charge, SUM(poh.product_sub_price) AS order_sub_total, SUM(poh.product_total_tax) AS order_tax_total, SUM(poh.product_total_shipping_charge) AS order_shipping_charge_total, pod.total_price_uc AS order_grand_total, pod.actual_amount_paid, pod.order_no, pod.order_placed_date, pod.vat_perc, pod.redeemed_amount, pod.used_cash_points, pod.earned_cash_points, pod.discount_price, COUNT( p.product_id) AS product_count, ush.user_shiping_details_building, ush.user_shiping_details_street, ush.user_shiping_details_loc, ush.user_shiping_details_landmark, ush.user_shiping_details_flatno, ush.user_shiping_details_floorno, ush.user_shiping_details_phone, ush.user_shiping_details_latitude, ush.user_shiping_details_longitude, ush.first_name AS user_first_name, ush.last_name AS user_last_name,ush.user_shiping_zipcode, ct.city_name, cn.country_name, pod_report.issue_count')
                            ->from('product_order_history poh')
                            ->join('product p', 'p.product_id = poh.product_id', 'left')
                            ->join('product_order_details pod', 'pod.order_block_id = poh.order_block_id', 'left')
                            ->join('user_shiping_details ush', 'ush.user_shiping_details_id = pod.shipping_address_id', 'left')
                            ->join('city ct', 'ush.user_shiping_details_city = ct.city_id', 'left')
                            ->join('country cn', 'cn.country_id = ct.city_country_id', 'left')
                            ->join('(SELECT COUNT(order_issue_id) AS issue_count, order_block_id FROM product_order_report_issue GROUP BY order_block_id) AS pod_report', 'pod_report.order_block_id = pod.order_block_id', 'left')
                            ->where('pod.order_block_id !=', NULL)                            
                            ->where("(ct.city_language_code = {$language} OR ct.city_language_code IS NULL)")
                            ->where("(cn.country_language_code = {$language} OR cn.country_language_code IS NULL)")
                            ->group_by(['pod.order_block_id','pod.shipping_charge', 'pod.user_id', 'pod.total_price_uc', 'poh.deliver_status', 'pod.measurement_charge', 'pod.order_no', 'pod.order_placed_date', 'pod.actual_amount_paid', 'pod.vat_perc', 'pod.redeemed_amount', 'pod.used_cash_points', 'pod.earned_cash_points', 'pod.discount_price', 'ush.user_shiping_details_building', 'ush.user_shiping_details_street', 'ush.user_shiping_details_loc', 'ush.user_shiping_details_landmark', 'ush.user_shiping_details_flatno', 'ush.user_shiping_details_floorno', 'ush.user_shiping_details_phone', 'ush.user_shiping_details_latitude', 'ush.user_shiping_details_longitude', 'ush.first_name', 'ush.last_name', 'ush.user_shiping_zipcode','ct.city_name', 'cn.country_name', 'pod_report.issue_count'])
                            ->order_by('pod.order_block_id', 'desc')
                            ->get();
        return $query;
    }

    public function getUserOrderHistoryCount( $params )
    {
        return $this->_getUserOrderHistoryQuery($params)->num_rows();
    }

    public function getUserOrderHistory( $params, $limit, $offset )
    {
        return $this->_getUserOrderHistoryQuery($params, $limit, $offset)->result();
    }
    public function getUserOrderById( $id, $user_id=null )
    {
        $params = [
            'user_id' => $user_id,
            'order_block_id' => $id,
        ];
        return $this->_getUserOrderHistoryQuery($params, 1, 0)->row();        
    }
    public function getUserOrderProducts( $order_id, $vendor_id=null, $limit=0, $offset=0 )
    {
        $lang_code = $this->lang_code;
        
        if ( isset($vendor_id) && $vendor_id ) {
            $this->db->where('pod.user_id', $vendor_id);
        }
        if ( $limit > 0 ) {
            $this->db->limit($limit);
        }
        if ( $offset ) {
            $this->db->offset($offset);
        }

        // Fetch products
        $this->db->select('product.product_id,product.product_type, pattr.product_attribute_id AS product_variant_id,poh.tailor_request,poh.deliver_status,poh.product_total_shipping_charge,product.product_vender_id,poh.product_total_shipping_charge');
        if ( $lang_code == "2" ) {
            $this->db->select("product.product_name_arabic AS product_name, STRING_AGG( (cattr.cat_attribute_name_arabic || '=' || po_uattr.attribute_value || '=' || COALESCE(cattr_val.cat_attribute_val_image, '') || '=' || COALESCE(cattr.cat_attribute_sort_order, '1') || '=' || COALESCE(cattr_val.cat_attribute_val_label_arabic, '') ), '###') AS category_attribute_value,seller.store_name_arabic AS store_name");
        } else {
            $this->db->select("product.product_name AS product_name, STRING_AGG( (cattr.cat_attribute_name || '=' || po_uattr.attribute_value || '=' || COALESCE(cattr_val.cat_attribute_val_image, '') || '=' || COALESCE(cattr.cat_attribute_sort_order, '1') || '=' || COALESCE(cattr_val.cat_attribute_val_label, '') ), '###') AS category_attribute_value,seller.store_name AS store_name");
        }
        $this->db->select("seller.seller_details_id AS store_id, pattr.image, poh.purchase_qty, poh.measurement_id, poh.unit_price_uc AS unit_price, (poh.purchase_qty * poh.unit_price_uc) AS product_total, poh.unit_measurement_charge_uc AS measurement_charge, poh.product_total_uc AS total_amount, poh.comment");

        $this->db->from('product_order_history poh');
        $this->db->join('product_attribute pattr', 'poh.product_attribute_id = pattr.product_attribute_id', 'left');
        $this->db->join('product_order_details pod', 'pod.order_block_id = poh.order_block_id', 'left');
        $this->db->join('product product', 'pattr.product_id = product.product_id', 'left');
        $this->db->join('seller_details seller', 'product.product_vender_id = seller.user_id', 'left');
        $this->db->join('product_order_user_attribute po_uattr', 'poh.history_id = po_uattr.history_id', 'left');
        $this->db->join('category_attribute cattr', 'po_uattr.attribute_id = cattr.cat_attribute_id', 'left');
        $this->db->join('category_attribute_values cattr_val', 'po_uattr.attribute_value_id = cattr_val.cat_attribute_val_id', 'left');
        $this->db->where('poh.order_block_id', $order_id);
        $this->db->order_by('poh.history_id ASC');
        $this->db->group_by(['poh.history_id', 'product.product_id', 'pattr.product_attribute_id', 'pattr.image', 'poh.purchase_qty', 'poh.measurement_id', 'poh.unit_price_uc', 'poh.unit_measurement_charge_uc', 'poh.product_total_uc', 'poh.comment', 'seller.seller_details_id']);
        if ( $lang_code == "2" ) {
            $this->db->group_by(['product.product_name_arabic','seller.store_name_arabic']);
        } else {
            $this->db->group_by(['product.product_name','seller.store_name']);
        }
        $products = $this->db->get()->result();

        $measurement_ids = array_filter(array_column($products, 'measurement_id'));
        $user_measurement_points = [];
        if (! empty($measurement_ids) ) {
            $this->db->select("ump.user_measurement_id, mp.point_name AS name, umv.measurement_value AS value");
            $this->db->from("user_measurement_point ump");
            $this->db->join("user_measurement_value umv", "ump.user_measurement_id = umv.user_measurement_id");
            $this->db->join("measurement_point mp","umv.measurement_point_id = mp.point_id");
            $this->db->where_in("ump.user_measurement_id", $measurement_ids);
            $measurement_points = $this->db->get()->result();
            foreach ( $measurement_points as $m_row ) {
                $user_measurement_points[$m_row->user_measurement_id][] = [
                    'name' => $m_row->name,
                    'value' => $m_row->value,
                ];
            }
        }

        $product_variant_ids = array_filter(array_column($products, 'product_variant_id'));
        $product_attribute_values = $this->getProductAttributeVals($product_variant_ids);

        foreach ( $products as $i => $product ) {
            // Product Attributes
            $products[$i]->product_attributes = [];
            if ( ($product->product_variant_id) > 0 && (array_key_exists($product->product_variant_id, $product_attribute_values) !== FALSE) ) {
                $t_values = $product_attribute_values[$product->product_variant_id];
                foreach ( $t_values as $t_val ) {
                    $t_image = $t_val->attribute_value_image ?? "";
                    if (! empty($t_image) ) {
                        $t_image = $this->config->item('upload_path') . $this->config->item('product_attribute_value_icon_upload_dir') . $t_image;
                        if ( file_exists(FCPATH . $t_image) ) {
                            $t_image = base_url() . $t_image;
                        } else {
                            $t_image = "";
                        }                        
                    }
                    $products[$i]->product_attributes[] = [
                        'name' => $t_val->attribute_name,
                        'value' => $t_val->attribute_values,
                        'image' => $t_image,
                        'color' => $t_val->attribute_value_color,
                        'type' => $t_val->attribute_type,
                    ];
                }
            }

            // Category Attributes
            $category_attributes = [];
            if (! empty($product->category_attribute_value) ) {
                $t_attribute_value = array_map('trim', explode('###', $product->category_attribute_value));
                foreach ($t_attribute_value as $t_rec) {
                    $t_rec = array_map('trim', explode('=', $t_rec));
                    if ( count($t_rec) > 1 ) {
                        $t_image = $t_rec[2] ?? "";
                        if (! empty($t_image) ) {
                            $t_image = $this->config->item('upload_path') . $this->config->item('cat_attribute_value_icon_upload_dir') . $t_image;
                            if ( file_exists(FCPATH . $t_image) ) {
                                $t_image = base_url()  . $t_image;
                            } else {
                                $t_image = "";
                            }                            
                        }
                        $t_sort_order = $t_rec[3] ?? 1;
                        $t_value = ( isset($t_rec[4]) && !empty($t_rec[4]) ) ? $t_rec[4] : $t_rec[1];
                        $category_attributes[] = [
                            'name' => $t_rec[0],
                            'value' => $t_value,
                            'image' => $t_image,
                            'sort_order' => (int) $t_sort_order,
                        ];
                    }
                }
                if (! empty($category_attributes) ) {
                    usort($category_attributes, function($a, $b) {
                        return $a['sort_order'] <=> $b['sort_order'];
                    });
                }
            }
            $products[$i]->category_attributes = $category_attributes;
            unset($product->category_attribute_value);

            $products[$i]->measurement_points = [];
            if ( ($product->measurement_id) > 0 && (array_key_exists($product->measurement_id, $user_measurement_points) !== FALSE) ) {
                $products[$i]->measurement_points = $user_measurement_points[$product->measurement_id];
            }
        }
        
        return $products;
    }
     public function getProductAttributeVals( array $product_attribute_ids=array() )
    {
        if (! empty($product_attribute_ids) ) {
            $this->db->select(
                'pvar.product_variations_id,
                pvar.product_id,
                pvar.product_attribute_id,
                attr.attribute_id,
                attrv.attribute_values_id,
                attr_type.attribute_type_uid AS attribute_type'
            );

            if ( $this->lang_code == 2 ) {
                $this->db->select('attr.attribute_name_arabic AS attribute_name, attrv.attribute_values_arabic AS attribute_values, attrv.attribute_value_label_arabic AS attribute_value_label');
            } else {
                $this->db->select('attr.attribute_name, attrv.attribute_values, attrv.attribute_value_label');
            }

            $this->db->select('attrv.attribute_value_in, attrv.attribute_value_color, attrv.attribute_value_image');
            
            $this->db->from('product_variations pvar');
            $this->db->join('attribute attr', 'attribute_id', 'inner');
            $this->db->join('attribute_values attrv', 'attribute_values_id', 'inner');
            $this->db->join('attribute_type attr_type', 'attr.attribute_type = attr_type.attribute_type_id', 'left');
            $this->db->where_in('pvar.product_attribute_id', $product_attribute_ids);
            
            $this->db->group_by(['pvar.product_attribute_id', 'pvar.product_id', 'pvar.product_variations_id', 'attr.attribute_id', 'attr.attribute_name', 'attrv.attribute_values_id', 'attr_type.attribute_type_uid']);

            $this->db->order_by('attrv.attribute_value_sort_order', 'asc');

            $result = $this->db->get()->result();
            $attribute_values = [];
            foreach ( $result as $row ) {
                $attribute_values[$row->product_attribute_id][] = $row;
            }

            return $attribute_values;
        }
        return [];
    }
    public function getUserOrderProducts_cart( $cart_id, $user_id=null, $limit=0, $offset=0 )
    {
        $lang_code = $this->lang_code;
        
        // if ( isset($user_id) && $user_id ) {
        //     $this->db->where('pod.user_id', $user_id);
        // }
        if ( $limit > 0 ) {
            $this->db->limit($limit);
        }
        if ( $offset ) {
            $this->db->offset($offset);
        }

        // Fetch products
        $this->db->select('product.product_id, pattr.product_attribute_id AS product_variant_id');
        if ( $lang_code == "2" ) {
            $this->db->select("product.product_name_arabic AS product_name, STRING_AGG( (cattr.cat_attribute_name_arabic || '=' || po_uattr.attribute_value || '=' || COALESCE(cattr_val.cat_attribute_val_image, '') || '=' || COALESCE(cattr.cat_attribute_sort_order, '1') || '=' || COALESCE(cattr_val.cat_attribute_val_label_arabic, '') ), '###') AS category_attribute_value");
        } else {
            $this->db->select("product.product_name AS product_name, STRING_AGG( (cattr.cat_attribute_name || '=' || po_uattr.attribute_value || '=' || COALESCE(cattr_val.cat_attribute_val_image, '') || '=' || COALESCE(cattr.cat_attribute_sort_order, '1') || '=' || COALESCE(cattr_val.cat_attribute_val_label, '') ), '###') AS category_attribute_value");
        }
        $this->db->select("pattr.image, poh.quantity, poh.measurement_id,  poh.comment");

        $this->db->from('cart poh');
        $this->db->join('product_attribute pattr', 'poh.product_attribute_id = pattr.product_attribute_id', 'left');
       
        $this->db->join('product product', 'pattr.product_id = product.product_id', 'left');
        $this->db->join('cart_user_attribute_value po_uattr', 'poh.cart_id = po_uattr.cart_id', 'left');
        $this->db->join('category_attribute cattr', 'po_uattr.attribute_id = cattr.cat_attribute_id', 'left');
        $this->db->join('category_attribute_values cattr_val', 'po_uattr.attribute_value_id = cattr_val.cat_attribute_val_id', 'left');
        $this->db->where('poh.cart_id', $cart_id);
        $this->db->order_by('poh.cart_id ASC');
        $this->db->group_by(['poh.cart_id', 'product.product_id', 'pattr.product_attribute_id', 'pattr.image', 'poh.quantity', 'poh.measurement_id',  'poh.comment']);
        if ( $lang_code == "2" ) {
            $this->db->group_by(['product.product_name_arabic']);
        } else {
            $this->db->group_by(['product.product_name']);
        }
        $products = $this->db->get()->result();

        $measurement_ids = array_filter(array_column($products, 'measurement_id'));
        $user_measurement_points = [];
        if (! empty($measurement_ids) ) {
            $this->db->select("ump.user_measurement_id, mp.point_name AS name, umv.measurement_value AS value");
            $this->db->from("user_measurement_point ump");
            $this->db->join("user_measurement_value umv", "ump.user_measurement_id = umv.user_measurement_id");
            $this->db->join("measurement_point mp","umv.measurement_point_id = mp.point_id");
            $this->db->where_in("ump.user_measurement_id", $measurement_ids);
            $measurement_points = $this->db->get()->result();
            foreach ( $measurement_points as $m_row ) {
                $user_measurement_points[$m_row->user_measurement_id][] = [
                    'name' => $m_row->name,
                    'value' => $m_row->value,
                ];
            }
        }

        $product_variant_ids = array_filter(array_column($products, 'product_variant_id'));
        $product_attribute_values = $this->getProductAttributeVals($product_variant_ids);

        foreach ( $products as $i => $product ) {
            // Product Attributes
            $products[$i]->product_attributes = [];
            if ( ($product->product_variant_id) > 0 && (array_key_exists($product->product_variant_id, $product_attribute_values) !== FALSE) ) {
                $t_values = $product_attribute_values[$product->product_variant_id];
                foreach ( $t_values as $t_val ) {
                    $t_image = $t_val->attribute_value_image ?? "";
                    if (! empty($t_image) ) {
                        $t_image = $this->config->item('upload_path') . $this->config->item('product_attribute_value_icon_upload_dir') . $t_image;
                        if ( file_exists(FCPATH . $t_image) ) {
                            $t_image = base_url() . $t_image;
                        } else {
                            $t_image = "";
                        }                        
                    }
                    $products[$i]->product_attributes[] = [
                        'name' => $t_val->attribute_name,
                        'value' => $t_val->attribute_values,
                        'image' => $t_image,
                        'color' => $t_val->attribute_value_color,
                        'type' => $t_val->attribute_type,
                    ];
                }
            }

            // Category Attributes
            $category_attributes = [];
            if (! empty($product->category_attribute_value) ) {
                $t_attribute_value = array_map('trim', explode('###', $product->category_attribute_value));
                foreach ($t_attribute_value as $t_rec) {
                    $t_rec = array_map('trim', explode('=', $t_rec));
                    if ( count($t_rec) > 1 ) {
                        $t_image = $t_rec[2] ?? "";
                        if (! empty($t_image) ) {
                            $t_image = $this->config->item('upload_path') . $this->config->item('cat_attribute_value_icon_upload_dir') . $t_image;
                            if ( file_exists(FCPATH . $t_image) ) {
                                $t_image = base_url() . $t_image;
                            } else {
                                $t_image = "";
                            }                            
                        }
                        $t_sort_order = $t_rec[3] ?? 1;
                        $t_value = ( isset($t_rec[4]) && !empty($t_rec[4]) ) ? $t_rec[4] : $t_rec[1];
                        $category_attributes[] = [
                            'name' => $t_rec[0],
                            'value' => $t_value,
                            'image' => $t_image,
                            'sort_order' => (int) $t_sort_order,
                        ];
                    }
                }
                if (! empty($category_attributes) ) {
                    usort($category_attributes, function($a, $b) {
                        return $a['sort_order'] <=> $b['sort_order'];
                    });
                }
            }
            $products[$i]->category_attributes = $category_attributes;
            unset($product->category_attribute_value);

            $products[$i]->measurement_points = [];
            if ( ($product->measurement_id) > 0 && (array_key_exists($product->measurement_id, $user_measurement_points) !== FALSE) ) {
                $products[$i]->measurement_points = $user_measurement_points[$product->measurement_id];
            }
        }
        
        return $products;
    }

    public function get_order_by_shipping_id($shipping_address_id) {

         return $this->db->select("product_order_details.*")
                        ->from("product_order_details")                        
                        ->where("shipping_address_id", $shipping_address_id)
                        ->get()
                        ->row();
    }

    public function get_favourite_shops($user_id){

        $this->db->select("*");
        $this->db->from("favourate_sellers fs");
        $this->db->join("seller_details sd","fs.seller_id = sd.user_id");
        $this->db->where("fs.user_id",$user_id);
        return $this->db->get()->result();
    }

    public function registerVender( $data=[] )
    {

        $time_now = gmdate("Y-m-d H:i:s");
        $user_type = "O";
        // if($data['user_type'] =="2"){
        //     $user_type = "A";
        // }elseif($data['user_type'] =="3"){
        //     $user_type = "C";
        // }

        $inputUserBasic['company_name']        =    $data['first_name']." ".$data['last_name'];
        $inputUserBasic['user_first_name']     =    $data['first_name'];
        $inputUserBasic['user_last_name']      =    $data['last_name'];
        $inputUserBasic['user_email_id']       =    $data['email'];
        $inputUserBasic['user_country_id']     =    $data['country'];
        $inputUserBasic['user_city_id']        =    $data['city'];
        $inputUserBasic['user_password']       =    password_hash($data['password'], PASSWORD_DEFAULT, ['cost' => 12]);
        $inputUserBasic['user_status']         =    1;
        $inputUserBasic['user_deleted']        =    0;
        //$inputUserBasic['user_created_by']     =    'A';
        $inputUserBasic['user_created_date']   =    gmdate("Y-m-d H:i:s");
        $inputUserBasic['user_type']           =    $user_type;
        $inputUserBasic['user_custom_id']      =    $this->getUserCustomId();

        $inputUserBasic['dial_code']           =    $data['dial_code'] ?? '';
        $inputUserBasic['trade_license_no']    =    $data['trade_licence'] ?? '';
        $inputUserBasic['phone_number']        =    $data['phone_number'] ?? '';
        $inputUserBasic['phone_verified']      =    $data['phone_verified'] ?? 0;
        //$inputUserBasic['image']               =    $data['image'] ?? '';
        $inputUserBasic['user_location']       =    $data['location'] ?? '';
        $inputUserBasic['user_latitude']       =    $data['latitude'] ?? '';
        $inputUserBasic['user_longitude']      =    $data['longitude'] ?? '';

        $this->db->select('count(user_email_id) as count');
        $this->db->from('user_table c');
        $this->db->where('user_email_id', $inputUserBasic['user_email_id']);
        $query = $this->db->get();

        if ($query->row()->count>0) {
            return 3;
            exit;
        }

        $this->db->select('count(user_custom_id) as count');
        $this->db->from('user_table c');
        $this->db->where('user_custom_id', $inputUserBasic['user_custom_id']);
        $query = $this->db->get();

        if ($query->row()->count>0) {
            return 4;
            exit;
        }

        $this->db->trans_start();

        $this->db->insert('user_table', $inputUserBasic);
        $insertId = $this->db->insert_id();

        $this->db->trans_complete();

        if ($this->db->trans_status() === false) {
            return 0;
        } else {
            return 1;
        }
    }

    public function getUserCustomId()
    {
        $this->load->helper('string');

        $custom_id = '';
        $count = 0;
        do {
            $custom_id = random_string('alnum', 5);
            $count = $this->db->get_where('user_table', ['user_custom_id' => $custom_id])->num_rows();
        } while ($count > 0);

        return $custom_id;
    }
    function registerSupplier($data){
        
            $latitude   = "";
            $longitude  = "";
            $location  = "";
            
            $inputUserBasic['company_name']        =    $data['first_name']." ".$data['last_name'];
            $inputUserBasic['user_first_name']     =    $data['first_name'];
            $inputUserBasic['user_last_name']      =    $data['last_name'];
            $inputUserBasic['user_email_id']       =    $data['email'];
            $inputUserBasic['user_country_id']     =    $data['country'];
            $inputUserBasic['user_city_id']        =    $data['city'];
            $inputUserBasic['user_password']       =    password_hash($data['password'], PASSWORD_DEFAULT, ['cost' => 12]);
            $inputUserBasic['user_status']         =    0;
            $inputUserBasic['user_deleted']        =    0;
            $inputUserBasic['user_status']         =    0;
            $inputUserBasic['user_deleted']        =    0;
            $inputUserBasic['user_created_by']     =    '';
            $inputUserBasic['user_created_date']   =    gmdate("Y-m-d H:i:s");
            $inputUserBasic['user_type']           =    'V';
            $inputUserBasic['user_custom_id']      =    $this->getUserCustomId();
            $inputUserBasic['phone_verified']      =    $data['phone_verified'] ?? 0;
            $inputUserBasic['user_location']       =    $data['location'] ?? '';
            $inputUserBasic['user_latitude']       =    $data['latitude'] ?? '';
            $inputUserBasic['user_longitude']      =    $data['longitude'] ?? '';
            $inputUserBasic['dial_code']           =    $data['dial_code'] ?? '';
            $inputUserBasic['phone_number']        =    $data['phone_number'] ?? '';
            $inputUserBasic['phone_verified']      =    $data['phone_verified'] ?? 0;
    
            $this->db->select('count(user_email_id) as count');
            $this->db->from('user_table c');
            $this->db->where('user_email_id', $data['email']);
            $query = $this->db->get();
    
            // echo $this->db->last_query();
            if ($query->row()->count>0) {
                return 3;
                exit;
            }
                $this->db->select('count(user_custom_id) as count');
                $this->db->from('user_table c');
                $this->db->where('user_custom_id', $inputUserBasic['user_custom_id']);
                $query = $this->db->get();
               // echo $this->db->last_query();
            if ($query->row()->count>0) {
                return 4;
                exit;
            }
    
            $this->db->trans_start();
    
            $this->db->insert('user_table', $inputUserBasic);
            $insertId = $this->db->insert_id();
    
            
                $latitude   = $data['latitude'];
                $longitude  = $data['longitude'];
                $location  = $data['location'];
    
    
                $inputUserShiping['city']             =    $data['city'];
                $inputUserShiping['mobile_no']        =    $data['dial_code']."-".$data['phone_number'];
                $inputUserShiping['location']         =    $data['location'];
                $inputUserShiping['street_name']      =    $data['txt_street'];
                $inputUserShiping['building_no']      =    $data['txt_building'];
    
                //$inputUserShiping['user_shiping_details_area']     =    $data['select_area'][$i]>0?$data['select_area'][$i]:0;
                $inputUserShiping['account_type']     =    $data['select_acc_type']>0?$data['select_acc_type']:0;
                $inputUserShiping['mall_id']     =    $data['email']>0?$data['email']:1;
    
                $inputUserShiping['latitude']         =    $data['latitude'];
                $inputUserShiping['longitude']        =    $data['longitude'];
                $inputUserShiping['store_status']        =    0;
                $inputUserShiping['store_name']        =    $data['supplier_name'];
                $inputUserShiping['store_name_arabic']  =    $data['supplier_name_arabic'];
                $inputUserShiping['land_phone']        =    $data['txt_Land_Phone'];
    
                $inputUserShiping['store_created_by']  =    1;
                $inputUserShiping['store_created_date']        =    gmdate("Y-m-d H:i:s");
    
                if ($_FILES["document"]["name"]!="") {
                    $digits   =  6;
                    $randomNo = str_pad(rand(0, pow(10, $digits)-1), $digits, '0', STR_PAD_LEFT);
    
                    $filename2 = $_FILES["document"]["name"];
                    $file_ext2 = pathinfo($filename2, PATHINFO_EXTENSION);
    
                    $config2['upload_path']          =  $this->config->item('upload_path').'vendor_images/';
                    $config2['allowed_types']        =  'gif|jpg|png|pdf|doc|jpeg';
                    $config2['max_size']             =  1000;
                    //$config2['max_width']            =  1024;
                    //$config2['max_height']           =  768;
                    $config2['file_name']            =  $randomNo.".".$file_ext2;
    
                    //$this->load->library('upload', $config2);
                        $this->load->library('upload', $config2);
                    // $this->upload->initialize($config2);
    
                    if (! $this->upload->do_upload('document')) {
                        $data['status'] = 0;
                        $data['errors'] = array(
                        'document' => $this->upload->display_errors(),
                        );
                        echo json_encode($data);
                        exit();
                    } else {
                        $inputUserShiping['document_number']  =    $data['txt_doc_no'];
                        $inputUserShiping['document_type_id']     =    $data['select_doc_type']>0?$data['select_doc_type']:0;
                        $inputUserShiping['doument_name']  =    $config2['file_name'];
                    }
                }
    
    
                if ($_FILES["emirates_id"]["name"]!="") {
                    $config2 =[];
                    $digits   =  6;
                    $randomNo = str_pad(rand(0, pow(10, $digits)-1), $digits, '0', STR_PAD_LEFT);
    
                    $filename2 = $_FILES["emirates_id"]["name"];
                    $file_ext2 = pathinfo($filename2, PATHINFO_EXTENSION);
    
                    $config2['upload_path']          =  $this->config->item('upload_path').'vendor_images/';
                    $config2['allowed_types']        =  'gif|jpg|png|pdf|doc|jpeg';
                    $config2['max_size']             =  1000;
                    //$config2['max_width']            =  1024;
                    //$config2['max_height']           =  768;
                    $config2['file_name']            =  $randomNo.".".$file_ext2;
    
                    //$this->load->library('upload', $config2);
                    $this->load->library('upload', $config2);
                    $this->upload->initialize($config2);
    
                    if (! $this->upload->do_upload('emirates_id')) {
                        $data['status'] = 0;
                        $data['errors'] = array(
                        'emirates_id' => $this->upload->display_errors(),
                        );
                        echo json_encode($data);
                        exit();
                    } else {
                        $inputUserShiping['emirates_id']  =    $config2['file_name'];
                    }
                }
    
    
                if ($_FILES["trade_license"]["name"]!="") {
                    $config2 =[];
                    $digits   =  6;
                    $randomNo = str_pad(rand(0, pow(10, $digits)-1), $digits, '0', STR_PAD_LEFT);
    
                    $filename2 = $_FILES["trade_license"]["name"];
                    $file_ext2 = pathinfo($filename2, PATHINFO_EXTENSION);
    
                    $config2['upload_path']          =  $this->config->item('upload_path').'vendor_images/';
                    $config2['allowed_types']        =  'gif|jpg|png|pdf|doc|jpeg';
                    $config2['max_size']             =  1000;
                    //$config2['max_width']            =  1024;
                    //$config2['max_height']           =  768;
                    $config2['file_name']            =  $randomNo.".".$file_ext2;
    
                    //$this->load->library('upload', $config2);
                    $this->load->library('upload', $config2);
                    $this->upload->initialize($config2);
    
                    if (! $this->upload->do_upload('trade_license')) {
                        $data['status'] = 0;
                        $data['errors'] = array(
                        'trade_license' => $this->upload->display_errors(),
                        );
                        echo json_encode($data);
                        exit();
                    } else {
                        $inputUserShiping['trade_license']  =    $config2['file_name'];
                    }
                }
    
                $inputUserShiping['user_id']  = $insertId;
                $inputUserShiping['about_seller_desc']          =    $data['description'] ?? '';
                $inputUserShiping['about_seller_desc_arb']      =    $data['description_arabic'] ?? '';
                $inputUserShiping['seller_commission']          = $data['seller_commission'] ?? 0;
                $inputUserShiping['delivery_time_min']          = (int)$data['delivery_time_min'] ?? 0;
                $inputUserShiping['delivery_time_max']          = (int)$data['delivery_time_max'] ?? 0;
                $inputUserShiping['seller_bank_id']             = $data['bank'] ?? "";
                $inputUserShiping['bank_account_number']        = $data['account_number'] ?? "";
                $inputUserShiping['bank_ifsc_code']             = $data['ifsc_code'] ?? "";
                $inputUserShiping['bank_swift_code']            = $data['swift_code'] ?? "";
    
                $this->db->insert('seller_details', $inputUserShiping);
            
    
            $this->db->trans_complete();
    
            if ($this->db->trans_status() === false) {
                return 0;
            } else {
            return 1;
        }
    }
    
}