<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;
use DB;


class Inventory extends Model{
    use HasFactory;
    protected $table = 'inventory';
    
    protected $fillable = [
        'name',
        'veriant',
        'branch_id',
        'key_number',
        'engine_number',
        'vin_number',
        'basic_price',
        'aging_days',
        'ex_showroom_price',
        'kin_invoice_no',
        'kin_invoice_date',
        'manufacturing_year',
        'interior_color',
        'exterior_color',
        'purchase_price',
        'kin_margin',
        'fuel_type',
        'status',
        'inventory_type',
        'remark',
        'created_by',
        'created_at',
        'updated_by',
        'updated_at',
        'offer_applicable',
        'fsc_code'
    ];
    
    public function obf()
    {
        return $this->hasMany(OBF::class, 'inventory_id','id');
    }

    public function export($slug){

        $collection = DB::table('inventory as i') 
                ->select( 
                        'b.name as branch',
                        'cm.name as car_name',
                        'cv.name as varient_name',
                        'exteriorcolor.name as exteriorcolor_name',
                        'interiorcolor.name as interiorcolor_name',
                        'i.kin_invoice_no',
                        'i.kin_invoice_date', 
                        'i.manufacturing_year',
                        'i.key_number', 
                        'i.engine_number',  
                        'i.vin_number',
                        'i.ex_showroom_price', 
                        'i.status',
                        'i.purchase_price',
                        'i.kin_margin',
                        'fule_type.title as fuel_type',
                        'i.inventory_type',
                        'i.offer_applicable',
                        'i.remark',
                        'i.fsc_code')
                ->leftjoin('branches as b', 'b.id', 'i.branch_id')
                ->leftjoin('products', 'i.name', 'products.id')
                ->leftjoin('car_model as cm', 'cm.id', 'products.car_model')
                ->leftjoin('car_varient as cv', 'cv.id', 'products.veriant')
                ->leftjoin('interiorcolor', 'interiorcolor.id', 'products.interior_color')
                ->leftjoin('exteriorcolor', 'exteriorcolor.id', 'products.exterior_color')
                ->leftjoin('fule_type','fule_type.id','i.fuel_type')
                ->groupBy('i.name')
                ->orderBy('i.aging_days', 'desc')
                ->where('i.status','!=',"allocatedDealer")
                ->where('i.status','!=','deleted');
        
        if($slug != 'all')
            $collection->where(['i.status' => $slug]);
        
        $data = $collection->get();
        if($data->isNotEmpty()){
            return $data;
        }else{
            return null;
        }
    }
    
    public function DemoInventoryexport($slug){

        $collection = DB::table('inventory as i')
            ->select(
                'i.id',
                'i.name',
                'b.name as branch',
                'i.veriant',
                'i.inventory_type',
                'i.ex_showroom_price',
                'i.status',
                'i.aging_days',
                'cv.name as varient_name',
                'cm.name as car_name',
                'i.fsc_code',
                'i.vin_number',
                DB::raw('count(obf.id) as obf_id'),DB::raw('(CASE WHEN i.status = "allocated" THEN obf.customer_name END) AS customer_name'),
                'i.purchase_price',
                'i.kin_margin',
                'i.manufacturing_year',
                'fule_type.title as fuel_type',
                DB::raw("CONCAT(users.first_name,' ',users.last_name) AS sales_person_name"),
                'Team_master.team_name')

            ->leftjoin('branches as b', 'b.id', 'i.branch_id')
            ->leftjoin('products', 'i.name', 'products.id')
            ->leftjoin('car_model as cm', 'cm.id', 'products.car_model')
            ->leftjoin('car_varient as cv', 'cv.id', 'products.veriant')
            ->leftJoin('exteriorcolor','exteriorcolor.id','=','products.exterior_color')
            ->leftJoin('interiorcolor','interiorcolor.id','=','products.interior_color')
            ->leftJoin('obf','obf.product_id','=','i.name')
            ->leftjoin('fule_type','fule_type.id','=','i.fuel_type')
            ->leftjoin('users','users.id','=','obf.sales_person_id')
            ->leftjoin('Team_master','Team_master.id','users.team_name')
            ->groupBy('i.name')
            ->orderBy('i.id', 'desc')
            ->where('i.status','!=',"allocatedDealer")
            ->where('i.status','!=','deleted')
            ->where('i.inventory_type','=','Demo'); 
            
        if($slug != 'all')
            $collection->where(['i.status' => $slug]);
        
        $data = $collection->get();
        if($data->isNotEmpty()){
            return $data;
        }else{
            return null;
        }
    }
}
