<?php

namespace App\Http\Controllers;

use App\Http\Controllers\Controller;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Validator;
use Illuminate\Support\Facades\Session;

class Ordercontroller extends Controller
{
    public function web_order()
    {
        return view('order/home');
    }
    public function search_by_vehicle()
    {
        return view('order/search_by_vehicle');
    }
    public function search_by_tire()
    {
        return view('order/search_by_tire');
    }
    public function search_by_wheels()
    {
        return view('order/search_by_wheels');
    }
    public function search_by_other()
    {
        return view('order/search_by_other');
    }
    public function get_all_brand()
    {
        $brand_list = DB::table('tbl_brand')
        ->select('*')
        ->where('status','active')
        ->orderBy('id','DESC')
        ->get();
        return $brand_list;
    }
    public function get_all_model(Request $request)
    {
        $brand_id = $request->brand_id;
        $model_list = DB::table('tbl_vehicle_type')
            ->select('*')
            ->orderBy('id', 'DESC')
            ->get()
            ->map(function ($variant) use ($brand_id) {
                $variant->model = DB::table('tbl_model')
                    ->select('*')
                    ->where('brand_id', $brand_id)
                    ->where('vehicle_type', $variant->id)
                    ->where('status', 'active')
                    ->orderBy('id', 'DESC')
                    ->get();
                return $variant;
        });
        return $model_list;
    }
    public function get_all_variant(Request $request)
    {
        $brand_id = $request->brand_id;
        $model_id = $request->model_id;
        $year = $request->year;
        $type = $request->vehicle_type;
        $variant_list = DB::table('tbl_vehicle_info')
        ->select('*')
        ->where('brand',$brand_id)
        ->where('model',$model_id)
        ->where('vehicle_type',$type)
        ->where('year',$year)
        ->where('status','active')
        ->orderBy('id','DESC')
        ->get();
        return $variant_list;
    }
    public function get_variant_detail(Request $request)
    {
        $id = $request->variant_id;
        $variant_detail = DB::table('tbl_vehicle_info')
        ->select('tbl_vehicle_info.*','tbl_diameter.diameter','tbl_rim_width.rim_width')
        ->join('tbl_rim_width', 'tbl_rim_width.id', '=', 'tbl_vehicle_info.rim_width_range')
        ->join('tbl_diameter', 'tbl_diameter.id', '=', 'tbl_vehicle_info.center_hub_diameter')
        ->where('tbl_vehicle_info.id',$id)
        ->where('tbl_vehicle_info.status','active')
        ->orderBy('tbl_vehicle_info.id','DESC')
        ->get()
        ->map(function ($product_category) {
                $product_category->category = DB::table('tbl_product_category')
                    ->select('*')
                    ->where('status', 'active')
                    ->where('category', '!=', 'Other')
                    ->orderBy('id', 'ASC')
                    ->get();
                return $product_category;
        });
        return $variant_detail;
    }
    public function get_variant_product(Request $request)
    {
        $product_category = $request->product_category;
        $size = $request->size;

        $product_list = DB::table('tbl_products')
        ->select('tbl_products.id','tbl_products.title','tbl_products.main_price','tbl_products.offer_price',
        'tbl_products.sku','tbl_products.size','tbl_products.rim_size','tbl_products.stock_qty','tbl_product_type.product_type','tbl_product_category.category')
        ->join('tbl_product_category', 'tbl_product_category.id', '=', 'tbl_products.product_category')
        ->join('tbl_product_type', 'tbl_product_type.id', '=', 'tbl_products.product_type')
        ->when($product_category == 'Tire', function ($query) use ($size) {
        return $query->where('tbl_products.size', $size);
        }, function ($query) use ($size) {
            return $query->where('tbl_products.rim_size', $size);
        })
        ->orderBy('id','DESC')
        ->get()
        ->map(function ($product) {
                $product->added_to_cart = DB::table('tbl_cart')
                    ->select('id')
                    ->where('user_id', Session::get('user_data')->id)
                    ->where('product_id', $product->id)
                    ->count();
                return $product;
        });

        return $product_list;
    }
    public function add_to_cart(Request $request)
    {
        $qty = $request->product_qty;
        $product_id = $request->product_id;
        $user_id = Session::get('user_data')->id;
        $cart_check = DB::table('tbl_cart')
        ->select('*')
        ->where('user_id',$user_id)
        ->where('product_id',$product_id)
        ->first();
        if($cart_check)
        {
            $updatedata = DB::table('tbl_cart')
            ->where('id',$cart_check->id)
            ->update(array('qty'=>$qty,'updated_at'=>date('Y-m-d H:i:s')));
            if($updatedata)
            {
                $res = array('status'=>true,'msg'=>'Cart qty updated');
                return $res;
            }
            else
            {
                $res = array('status'=>false,'msg'=>'Something went wrong');
                return $res;
            }
        }
        else
        {
            $savedata = DB::table('tbl_cart')
            ->insert(array('qty'=>$qty,'user_id'=>$user_id,'product_id'=>$product_id,'created_at'=>date('Y-m-d H:i:s')));
            if($savedata)
            {
                $res = array('status'=>true,'msg'=>'Product added to cart');
                return $res;
            }
            else
            {
                $res = array('status'=>false,'msg'=>'Something went wrong');
                return $res;
            }
        }
    }
    public function cart_list()
    {
        $user_detail = DB::table('tbl_dealer')
        ->select('*')
        ->where('id', Session::get('user_data')->id)
        ->first();
        return view('order/cart')->with('user_detail',$user_detail);
    }
    public function get_cart_list()
    {
        $user_id = Session::get('user_data')->id;
        $cart_list = DB::table('tbl_cart')
        ->select('tbl_cart.id','tbl_cart.qty','tbl_cart.product_id','tbl_products.title','tbl_products.main_price','tbl_products.offer_price',
        'tbl_products.sku','tbl_products.size','tbl_products.rim_size','tbl_products.stock_qty','tbl_product_type.product_type','tbl_product_category.category')
        ->join('tbl_products', 'tbl_products.id', '=', 'tbl_cart.product_id')
        ->join('tbl_product_category', 'tbl_product_category.id', '=', 'tbl_products.product_category')
        ->join('tbl_product_type', 'tbl_product_type.id', '=', 'tbl_products.product_type')
        ->where('tbl_cart.user_id',$user_id)
        ->get();
        return $cart_list;
    }
    public function get_cart_count()
    {
        $cart_count = DB::table('tbl_cart')
        ->select('*')
        ->where('user_id', Session::get('user_data')->id)
        ->count();
        return $cart_count;
    }
    public function update_cart_qty(Request $request)
    {
        $updatedata = DB::table('tbl_cart')
            ->where('id',$request->cart_id)
            ->update(array('qty'=>$request->qty,'updated_at'=>date('Y-m-d H:i:s')));
            if($updatedata)
            {
                $res = array('status'=>true,'msg'=>'Cart qty updated');
                return $res;
            }
            else
            {
                $res = array('status'=>false,'msg'=>'Something went wrong');
                return $res;
            }
    }
    public function delete_cart_item(Request $request)
    {
        $updatedata = DB::table('tbl_cart')
            ->where('id',$request->cart_id)
            ->delete();
            if($updatedata)
            {
                $res = array('status'=>true,'msg'=>'Cart item deleted');
                return $res;
            }
            else
            {
                $res = array('status'=>false,'msg'=>'Something went wrong');
                return $res;
            }
    }
    public function save_order(Request $request)
    {
        DB::beginTransaction();
        $user_id = Session::get('user_data')->id;
        $cart_list = DB::table('tbl_cart')
        ->select('tbl_cart.id','tbl_cart.qty','tbl_cart.product_id','tbl_products.title','tbl_products.main_price','tbl_products.offer_price',
        'tbl_products.sku','tbl_products.size','tbl_products.rim_size','tbl_products.stock_qty','tbl_product_category.category')
        ->join('tbl_products', 'tbl_products.id', '=', 'tbl_cart.product_id')
        ->join('tbl_product_category', 'tbl_product_category.id', '=', 'tbl_products.product_category')
        ->where('tbl_cart.user_id',$user_id)
        ->get();
        $order_id = 'ORD'.rand(10000,99999);
        $sub_total_amount = 0;
        $total_amount = 0;
        $hst = 13;
        $order_arr = array(
            'user_id'=>$user_id,
            'order_id'=>$order_id,
            'shipping_address'=>$request->shipping_address,
            'shipping_contact'=>$request->shipping_contact,
            'shipping_name'=>$request->shipping_name,
            'order_date'=>date('Y-m-d'),
            'special_instruction'=>$request->special_msg,
            'po'=>$request->po,
            'total_item'=>$request->total_item,
            'status'=>'received',
            'created_at'=>date('Y-m-d H:i:s'),
        );
        $savedata = DB::table('tbl_order')
        ->insert($order_arr);
        if($savedata)
        {
            foreach ($cart_list as $value) 
            {
                if($value->category == 'Tire')
                {
                    $otsf_fee = 4.5*$value->qty;
                    $sub_total_amount = $sub_total_amount+($value->offer_price*$value->qty)+$otsf_fee;
                }
                else
                {
                    $otsf_fee = 0;
                    $sub_total_amount = $sub_total_amount+($value->offer_price*$value->qty)+$otsf_fee;
                }

                $product_arr = array(
                    'product_id'=>$value->product_id,
                    'user_id'=>$user_id,
                    'order_id'=>$order_id,
                    'product_name'=>$value->title,
                    'size'=>$value->size,
                    'rim_size'=>$value->rim_size,
                    'sku'=>$value->sku,
                    'qty'=>$value->qty,
                    'price'=>$value->offer_price,
                    'otsf_fee'=>$otsf_fee,
                    'total_price'=>($value->offer_price*$value->qty)+$otsf_fee,
                    'product_category'=>$value->category,
                    'created_at'=>date('Y-m-d H:i:s')
                );
                DB::table('tbl_order_product')
                ->insert($product_arr);

                DB::table('tbl_products')
                ->where('id',$value->product_id)
                ->decrement('stock_qty', $value->qty, ['updated_at' => date('Y-m-d H:i:s')]);

            }
            $hst_amount = ($hst / 100) * $sub_total_amount;
            $total_amount = $sub_total_amount+$hst_amount;

            DB::table('tbl_order')
            ->where('order_id',$order_id)
            ->update(array('total_amount'=>round($total_amount,2),'hst_amount'=>round( $hst_amount,2),'sub_total_amount'=>$sub_total_amount,'updated_at'=>date('Y-m-d H:i:s')));

            DB::table('tbl_cart')
            ->where('user_id',$user_id)
            ->delete();
            DB::commit();
            $res = array('status'=>true,'msg'=>'Order placed');
            return $res;
        }
        else
        {
            DB::rollBack();
            $res = array('status'=>false,'msg'=>'Something went wrong');
            return $res;
        }
    }
    public function track_order(Request $request)
    {
        $order_list = DB::table('tbl_order')
        ->select('*')
        ->where('user_id', Session::get('user_data')->id)
        ->get();
        return view('order/order_list')->with('order_list',$order_list);
    }
    public function get_wheel_quick_size(Request $request)
    {
        $diameter_list = DB::table('tbl_products')
        ->select('tbl_diameter.id','tbl_diameter.diameter as value')
        ->join('tbl_diameter', 'tbl_diameter.id', '=', 'tbl_products.center_hub_diameter')
        ->where('tbl_products.status','active')
        ->groupBy('tbl_diameter.id')
        ->groupBy('tbl_diameter.diameter')
        ->get();

        $rim_width_list = DB::table('tbl_products')
        ->select('tbl_rim_width.id','tbl_rim_width.rim_width as value')
        ->join('tbl_rim_width', 'tbl_rim_width.id', '=', 'tbl_products.rim_width_range')
        ->where('tbl_products.status','active')
        ->groupBy('tbl_rim_width.id')
        ->groupBy('tbl_rim_width.rim_width')
        ->get();

        $bolt_pattern_list = DB::table('tbl_products')
        ->select('tbl_products.bolt_circle as value')
        ->where('tbl_products.status','active')
        ->whereNotNull('tbl_products.bolt_circle')
        ->groupBy('tbl_products.bolt_circle')
        ->get();

        $arr = [
            array(
            'title' => 'Diameter',
            'type' =>'diameter',
            'data'=>$diameter_list
            ),
            array(
            'title' => 'Rim Width',
            'type' =>'rim_width',
            'data'=>$rim_width_list
            ),
            array(
            'title' => 'Bolt Pattern',
            'type' =>'bolt_pattern',
            'data'=>$bolt_pattern_list
            )
        ];
        return $arr;
    }
    public function get_tire_quick_size(Request $request)
    {
        $tire_width_list = DB::table('tbl_products')
        ->select('tbl_load_index.id','tbl_load_index.load_index as value')
        ->join('tbl_load_index', 'tbl_load_index.id', '=', 'tbl_products.load_index')
        ->where('tbl_products.status','active')
        ->groupBy('tbl_load_index.id')
        ->groupBy('tbl_load_index.load_index')
        ->get();

        $rim_diameter_list = DB::table('tbl_products')
        ->select('tbl_diameter.id','tbl_diameter.diameter as value')
        ->join('tbl_diameter', 'tbl_diameter.id', '=', 'tbl_products.center_hub_diameter')
        ->where('tbl_products.status','active')
        ->groupBy('tbl_diameter.id')
        ->groupBy('tbl_diameter.diameter')
        ->get();

        $aspect_ratio_list = DB::table('tbl_products')
        ->select('tbl_products.aspect_ratio as value')
        ->where('tbl_products.status','active')
        ->where('tbl_products.aspect_ratio','!=','')
        ->get();

        $arr = [
                array(
                'title' => 'Tire Width',
                'type' =>'tire_width',
                'data'=>$tire_width_list
                ),
                array(
                'title' => 'Aspect Ratio',
                'type' =>'aspect_ratio',
                'data'=>$aspect_ratio_list
                ),
                array(
                'title' => 'Rim Diameter',
                'type' =>'rim_diameter',
                'data'=>$rim_diameter_list
                )
            ];
        return $arr;
    }
    public function get_tire_by_brand()
    {
        $brand_list = DB::table('tbl_brand')
        ->select('id','brand_name as value')
        ->where('status','active')
        ->get();
        return $brand_list;
    }
    public function search_product_by_size(Request $request)
    {
        $product_category = 'Tire';
        $aspect_ratio = $request->aspect_ratio;
        $tire_width = $request->tire_width;
        $rim_diameter = $request->rim_diameter;
        $load_index = $request->load_index;
        $speed_rating = $request->speed_rating;
        $product_type = $request->product_type;
        $hide_winter = $request->hide_winter;
        $winter_only = $request->winter_only;
        $all_weather_only = $request->all_weather_only;
        $on_sale_only = $request->on_sale_only;

        $product_list = DB::table('tbl_products')
        ->select(
        'tbl_products.id',
        'tbl_products.title',
        'tbl_products.main_price',
        'tbl_products.offer_price',
        'tbl_products.sku',
        'tbl_products.size',
        'tbl_products.stock_qty',
        'tbl_product_category.category'
        )
        ->join('tbl_product_category', 'tbl_product_category.id', '=', 'tbl_products.product_category')
        ->when(!is_null($aspect_ratio), function ($query) use ($aspect_ratio) {
            return $query->where('tbl_products.aspect_ratio', $aspect_ratio);
        })
        ->when(!is_null($tire_width), function ($query) use ($tire_width) {
            return $query->join('tbl_load_index', 'tbl_load_index.id', '=', 'tbl_products.load_index')
                ->where('tbl_load_index.load_index', $tire_width);
        })
        ->when(!is_null($rim_diameter), function ($query) use ($rim_diameter) {
            return $query->join('tbl_diameter', 'tbl_diameter.id', '=', 'tbl_products.center_hub_diameter')
                ->where('tbl_diameter.diameter', $rim_diameter);
        })
        ->when(!is_null($load_index), function ($query) use ($load_index) {
            return $query->where('tbl_products.load_index', $load_index);
        })
        ->when(!is_null($speed_rating), function ($query) use ($speed_rating) {
            return $query->where('tbl_products.speed_rating', $speed_rating);
        })
        ->when(!is_null($product_type), function ($query) use ($product_type) {
            return $query->where('tbl_products.product_type', $product_type);
        })
        ->when(($hide_winter), function ($query) use ($hide_winter) {
            return $query->where('tbl_products.not_for_winter', 'Yes');
        })
        ->when(($winter_only), function ($query) use ($winter_only) {
            return $query->where('tbl_products.for_winter_only', 'Yes');
        })
        ->when(($all_weather_only), function ($query) use ($all_weather_only) {
            return $query->where('tbl_products.for_all_weather', 'Yes');
        })
        ->when(($on_sale_only), function ($query) use ($on_sale_only) {
            return $query->where('tbl_products.onsale_only', 'Yes');
        })
        ->where('tbl_product_category.category', $product_category)
        ->where('tbl_products.status', 'active')
        ->orderBy('tbl_products.id', 'DESC')
        ->get()
        ->map(function ($product) {
            $product->added_to_cart = DB::table('tbl_cart')
                ->where('user_id', Session::get('user_data')->id)
                ->where('product_id', $product->id)
                ->count();
            return $product;
        });
        return $product_list;
    }
    public function search_product_by_brand(Request $request)
    {
        $product_category = 'Tire';
        $brand = $request->brand;
        $load_index = $request->load_index;
        $speed_rating = $request->speed_rating;
        $product_type = $request->product_type;
        $hide_winter = $request->hide_winter;
        $winter_only = $request->winter_only;
        $all_weather_only = $request->all_weather_only;
        $on_sale_only = $request->on_sale_only;

        $size_list = DB::table('tbl_vehicle_info')
        ->select('size')
        ->where('status','active')
        ->where('brand',$brand)
        ->get();
        $size_arr = [];
        foreach($size_list as $sl)
        {
            array_push($size_arr, $sl->size);
        }

        $product_list = DB::table('tbl_products')
        ->select(
        'tbl_products.id',
        'tbl_products.title',
        'tbl_products.main_price',
        'tbl_products.offer_price',
        'tbl_products.sku',
        'tbl_products.size',
        'tbl_products.rim_size',
        'tbl_products.stock_qty',
        'tbl_product_category.category'
        )
        ->join('tbl_product_category', 'tbl_product_category.id', '=', 'tbl_products.product_category')
        ->where('tbl_product_category.category', $product_category)
        ->where('tbl_products.status', 'active')
        ->where(function ($query) use ($size_arr) {
        $query->orWhereIn('tbl_products.size', $size_arr);
        })
        ->when(!is_null($load_index), function ($query) use ($load_index) {
            return $query->where('tbl_products.load_index', $load_index);
        })
        ->when(!is_null($speed_rating), function ($query) use ($speed_rating) {
            return $query->where('tbl_products.speed_rating', $speed_rating);
        })
        ->when(!is_null($product_type), function ($query) use ($product_type) {
            return $query->where('tbl_products.product_type', $product_type);
        })
        ->when(($hide_winter), function ($query) use ($hide_winter) {
            return $query->where('tbl_products.not_for_winter', 'Yes');
        })
        ->when(($winter_only), function ($query) use ($winter_only) {
            return $query->where('tbl_products.for_winter_only', 'Yes');
        })
        ->when(($all_weather_only), function ($query) use ($all_weather_only) {
            return $query->where('tbl_products.for_all_weather', 'Yes');
        })
        ->when(($on_sale_only), function ($query) use ($on_sale_only) {
            return $query->where('tbl_products.onsale_only', 'Yes');
        })
        ->orderBy('tbl_products.id', 'DESC')
        ->get()
        ->map(function ($product) {
            $product->added_to_cart = DB::table('tbl_cart')
                ->where('user_id', Session::get('user_data')->id)
                ->where('product_id', $product->id)
                ->count();
            return $product;
        });
        return $product_list;
    }
    public function vehicle_product_search_by_search_key(Request $request)
    {
        $search_key = $request->search_key;
        $product_list = DB::table('tbl_products')
            ->select(
            'tbl_products.id',
            'tbl_products.title',
            'tbl_products.main_price',
            'tbl_products.offer_price',
            'tbl_products.sku',
            'tbl_products.size',
            'tbl_products.rim_size',
            'tbl_products.stock_qty',
            'tbl_product_category.category'
            )
            ->join('tbl_product_category', 'tbl_product_category.id', '=', 'tbl_products.product_category')
            ->where(function ($query) use ($search_key) {
            $query->where('tbl_products.title', 'like', '%'.$search_key.'%')
                  ->orWhere('tbl_products.sku', 'like', '%'.$search_key.'%')
                  ->orWhere('tbl_products.size', 'like', '%'.$search_key.'%')
                  ->orWhere('tbl_products.rim_size', 'like', '%'.$search_key.'%')
                  ->orWhere('tbl_products.bolt_circle', 'like', '%'.$search_key.'%');
            })
            ->where('tbl_products.status', 'active')
            ->orderBy('tbl_products.id', 'DESC')
            ->get()
            ->map(function ($product) {
                $product->added_to_cart = DB::table('tbl_cart')
                    ->where('user_id', Session::get('user_data')->id)
                    ->where('product_id', $product->id)
                    ->count();
                return $product;
        });
        return $product_list;
    }
    public function product_search_by_search_key(Request $request)
    {
        $search_key = $request->search_key;
        $hide_winter = $request->hide_winter;
        $winter_only = $request->winter_only;
        $all_weather_only = $request->all_weather_only;
        $on_sale_only = $request->on_sale_only;
        $product_list = DB::table('tbl_products')
            ->select(
            'tbl_products.id',
            'tbl_products.title',
            'tbl_products.main_price',
            'tbl_products.offer_price',
            'tbl_products.sku',
            'tbl_products.size',
            'tbl_products.rim_size',
            'tbl_products.stock_qty',
            'tbl_product_category.category'
            )
            ->join('tbl_product_category', 'tbl_product_category.id', '=', 'tbl_products.product_category')
            ->where('tbl_product_category.category', 'Tire')
            ->where(function ($query) use ($search_key) {
            $query->where('tbl_products.title', 'like', '%'.$search_key.'%')
                  ->orWhere('tbl_products.size', 'like', '%'.$search_key.'%')
                  ->orWhere('tbl_products.rim_size', 'like', '%'.$search_key.'%')
                  ->orWhere('tbl_products.sku', 'like', '%'.$search_key.'%')
                  ->orWhere('tbl_products.bolt_circle', 'like', '%'.$search_key.'%');
            })
            ->when(($hide_winter), function ($query) use ($hide_winter) {
                return $query->where('tbl_products.not_for_winter', 'Yes');
            })
            ->when(($winter_only), function ($query) use ($winter_only) {
                return $query->where('tbl_products.for_winter_only', 'Yes');
            })
            ->when(($all_weather_only), function ($query) use ($all_weather_only) {
                return $query->where('tbl_products.for_all_weather', 'Yes');
            })
            ->when(($on_sale_only), function ($query) use ($on_sale_only) {
                return $query->where('tbl_products.onsale_only', 'Yes');
            })
            ->where('tbl_products.status', 'active')
            ->orderBy('tbl_products.id', 'DESC')
            ->get()
            ->map(function ($product) {
                $product->added_to_cart = DB::table('tbl_cart')
                    ->where('user_id', Session::get('user_data')->id)
                    ->where('product_id', $product->id)
                    ->count();
                return $product;
        });
        return $product_list;
    }
    public function wheel_product_search_by_search_key(Request $request)
    {
        $search_key = $request->search_key;
        $rim_width = $request->rim_width;
        $diameter = $request->diameter;
        $offset_distance = $request->offset_distance;
        $overall_diameter = $request->overall_diameter;
        $on_sale_only = $request->on_sale_only;
        $product_list = DB::table('tbl_products')
            ->select(
            'tbl_products.id',
            'tbl_products.title',
            'tbl_products.main_price',
            'tbl_products.offer_price',
            'tbl_products.sku',
            'tbl_products.year',
            'tbl_products.size',
            'tbl_products.rim_size',
            'tbl_products.stock_qty',
            'tbl_product_category.category'
            )
            ->join('tbl_product_category', 'tbl_product_category.id', '=', 'tbl_products.product_category')
            ->join('tbl_brand', 'tbl_brand.id', '=', 'tbl_products.brand')
            ->where('tbl_product_category.category', 'Other')
            ->where(function ($query) use ($search_key) {
            $query->where('tbl_brand.brand_name', 'like', '%'.$search_key.'%')
                  ->orWhere('tbl_products.title', 'like', '%'.$search_key.'%')
                  ->orWhere('tbl_products.sku', 'like', '%'.$search_key.'%')
                  ->orWhere('tbl_products.year', 'like', '%'.$search_key.'%')
                  ->orWhere('tbl_products.bolt_circle', 'like', '%'.$search_key.'%');
            })
            ->when(!is_null($rim_width), function ($query) use ($rim_width) {
                return $query->join('tbl_rim_width', 'tbl_rim_width.id', '=', 'tbl_products.rim_width_range')
                    ->where('tbl_rim_width.rim_width', $rim_width);
            })
            ->when(!is_null($diameter), function ($query) use ($diameter) {
                return $query->join('tbl_diameter', 'tbl_diameter.id', '=', 'tbl_products.center_hub_diameter')
                    ->where('tbl_diameter.diameter', $diameter);
            })
            ->when(!is_null($offset_distance), function ($query) use ($offset_distance) {
                return $query->where('tbl_products.offset_distance', $offset_distance);
            })
            ->when(!is_null($overall_diameter), function ($query) use ($overall_diameter) {
                return $query->where('tbl_products.overall_diameter', $overall_diameter);
            })
            ->when(($on_sale_only), function ($query) use ($on_sale_only) {
                return $query->where('tbl_products.onsale_only', 'Yes');
            })
            ->where('tbl_products.status', 'active')
            ->orderBy('tbl_products.id', 'DESC')
            ->get()
            ->map(function ($product) {
                $product->added_to_cart = DB::table('tbl_cart')
                    ->where('user_id', Session::get('user_data')->id)
                    ->where('product_id', $product->id)
                    ->count();
                return $product;
        });
        return $product_list;
    }
    public function search_product_by_wheel_size(Request $request)
    {
        $product_category = 'Wheels';
        $bolt_pattern = $request->bolt_pattern;
        $rim_width = $request->rim_width;
        $diameter = $request->diameter;
        $offset_distance = $request->offset_distance;
        $overall_diameter = $request->overall_diameter;
        $on_sale_only = $request->on_sale_only;

        $product_list = DB::table('tbl_products')
        ->select(
        'tbl_products.id',
        'tbl_products.title',
        'tbl_products.main_price',
        'tbl_products.offer_price',
        'tbl_products.sku',
        'tbl_products.rim_size',
        'tbl_products.stock_qty',
        'tbl_product_category.category'
        )
        ->join('tbl_product_category', 'tbl_product_category.id', '=', 'tbl_products.product_category')
        ->when(!is_null($bolt_pattern), function ($query) use ($bolt_pattern) {
            return $query->where('tbl_products.bolt_circle', $bolt_pattern);
        })
        ->when(!is_null($rim_width), function ($query) use ($rim_width) {
            return $query->join('tbl_rim_width', 'tbl_rim_width.id', '=', 'tbl_products.rim_width_range')
                ->where('tbl_rim_width.rim_width', $rim_width);
        })
        ->when(!is_null($diameter), function ($query) use ($diameter) {
            return $query->join('tbl_diameter', 'tbl_diameter.id', '=', 'tbl_products.center_hub_diameter')
                ->where('tbl_diameter.diameter', $diameter);
        })
        ->when(!is_null($offset_distance), function ($query) use ($offset_distance) {
            return $query->where('tbl_products.offset_distance', $offset_distance);
        })
        ->when(!is_null($overall_diameter), function ($query) use ($overall_diameter) {
            return $query->where('tbl_products.overall_diameter', $overall_diameter);
        })
        ->when(($on_sale_only), function ($query) use ($on_sale_only) {
            return $query->where('tbl_products.onsale_only', 'Yes');
        })
        ->where('tbl_product_category.category', $product_category)
        ->where('tbl_products.status', 'active')
        ->orderBy('tbl_products.id', 'DESC')
        ->get();

        if ($product_list->isNotEmpty() && Session::has('user_data')) {
            $user_id = Session::get('user_data')->id;
            $product_list = $product_list->map(function ($product) use ($user_id) {
                $product->added_to_cart = DB::table('tbl_cart')
                    ->where('user_id', $user_id)
                    ->where('product_id', $product->id)
                    ->count();
                return $product;
            });
        }
        return $product_list;
    }
    public function search_product_by_wheel_brand(Request $request)
    {
        $product_category = 'Wheels';
        $brand = $request->brand;
        $rim_width = $request->rim_width;
        $diameter = $request->diameter;
        $offset_distance = $request->offset_distance;
        $overall_diameter = $request->overall_diameter;
        $on_sale_only = $request->on_sale_only;

        $size_list = DB::table('tbl_vehicle_info')
        ->select('rim_size')
        ->where('status','active')
        ->where('brand',$brand)
        ->get();
        $rim_size_arr = [];
        foreach($size_list as $sl)
        {
            array_push($rim_size_arr, $sl->rim_size);
        }

        $product_list = DB::table('tbl_products')
        ->select(
        'tbl_products.id',
        'tbl_products.title',
        'tbl_products.main_price',
        'tbl_products.offer_price',
        'tbl_products.sku',
        'tbl_products.size',
        'tbl_products.rim_size',
        'tbl_products.stock_qty',
        'tbl_product_category.category'
        )
        ->join('tbl_product_category', 'tbl_product_category.id', '=', 'tbl_products.product_category')
        ->where('tbl_product_category.category', $product_category)
        ->where('tbl_products.status', 'active')
        ->where(function ($query) use ($rim_size_arr) {
        $query->orWhereIn('tbl_products.rim_size', $rim_size_arr);
        })
        ->when(!is_null($rim_width), function ($query) use ($rim_width) {
            return $query->where('tbl_products.rim_width_range', $rim_width);
        })
        ->when(!is_null($diameter), function ($query) use ($diameter) {
            return $query->where('tbl_products.center_hub_diameter', $diameter);
        })
        ->when(!is_null($offset_distance), function ($query) use ($offset_distance) {
            return $query->where('tbl_products.offset_distance', $offset_distance);
        })
        ->when(!is_null($overall_diameter), function ($query) use ($overall_diameter) {
            return $query->where('tbl_products.overall_diameter', $overall_diameter);
        })
        ->when(($on_sale_only), function ($query) use ($on_sale_only) {
            return $query->where('tbl_products.onsale_only', 'Yes');
        })
        ->orderBy('tbl_products.id', 'DESC')
        ->get()
        ->map(function ($product) {
            $product->added_to_cart = DB::table('tbl_cart')
                ->where('user_id', Session::get('user_data')->id)
                ->where('product_id', $product->id)
                ->count();
            return $product;
        });
        return $product_list;
    }
    public function get_wheel_by_brand()
    {
        $brand_list = DB::table('tbl_brand')
        ->select('id','brand_name as value')
        ->where('status','active')
        ->get();
        return $brand_list;
    }
    public function get_other_brand()
    {
        $brand_list = DB::table('tbl_brand')
        ->select('id','brand_name as value')
        ->where('status','active')
        ->get();
        return $brand_list;
    }
    public function search_other_product(Request $request)
    {
        $product_category = 'Other';
        $brand = $request->brand;
        $on_sale_only = $request->on_sale_only;

        $product_list = DB::table('tbl_products')
        ->select(
        'tbl_products.id',
        'tbl_products.title',
        'tbl_products.main_price',
        'tbl_products.offer_price',
        'tbl_products.sku',
        'tbl_products.size',
        'tbl_products.rim_size',
        'tbl_products.stock_qty',
        'tbl_product_category.category'
        )
        ->join('tbl_product_category', 'tbl_product_category.id', '=', 'tbl_products.product_category')
        ->where('tbl_product_category.category', 'Other')
        ->where(function ($query) use ($brand) {
        $query->where('tbl_products.title', 'like', '%'.$brand.'%')
              ->orWhere('tbl_products.sku', 'like', '%'.$brand.'%')
              ->orWhere('tbl_products.bolt_circle', 'like', '%'.$brand.'%');
        })
        ->when(($on_sale_only), function ($query) use ($on_sale_only) {
            return $query->where('tbl_products.onsale_only', 'Yes');
        })
        ->where('tbl_products.status', 'active')
        ->orderBy('tbl_products.id', 'DESC')
        ->get()
        ->map(function ($product) {
            $product->added_to_cart = DB::table('tbl_cart')
                ->where('user_id', Session::get('user_data')->id)
                ->where('product_id', $product->id)
                ->count();
            return $product;
        });
        return $product_list;
    }
    public function get_load_index()
    {
        $load_index = DB::table('tbl_load_index')
        ->select('*')
        ->orderBy('id','DESC')
        ->get();
        return  $load_index;
    }
    public function get_speed_rating()
    {
        $load_index = DB::table('tbl_speed_rating')
        ->select('*')
        ->orderBy('id','DESC')
        ->get();
        return  $load_index;
    }
    public function get_product_type()
    {
        $load_index = DB::table('tbl_product_type')
        ->select('*')
        ->where('status','active')
        ->orderBy('id','DESC')
        ->get();
        return  $load_index;
    }
    public function get_all_diameter()
    {
        $all_diameter = DB::table('tbl_diameter')
        ->select('*')
        ->orderBy('id','DESC')
        ->get();
        return  $all_diameter;
    }
    public function get_all_rim_width()
    {
        $rim_width = DB::table('tbl_rim_width')
        ->select('*')
        ->orderBy('id','DESC')
        ->get();
        return  $rim_width;
    }
    public function get_all_offset()
    {
        $all_offset = DB::table('tbl_products')
        ->select('offset_distance')
        ->where('status','active')
        ->whereNotNull('offset_distance')
        ->orderBy('id','DESC')
        ->get();
        return  $all_offset;
    }
    public function get_all_overall_diameter()
    {
        $all_over_all_diameter = DB::table('tbl_products')
        ->select('overall_diameter')
        ->where('status','active')
        ->whereNotNull('overall_diameter')
        ->orderBy('id','DESC')
        ->get();
        return  $all_over_all_diameter;
    }
}
