<?php

namespace App\Models;

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

class offer extends Model{
    use HasFactory;
    protected $table = 'offer';
    
    protected $fillable = [
        'title',
        'fk_branch_id',
        'fk_car_model_id',
        'fk_fuel_id',
        'fk_car_varient_id',
        'start_date',
        'end_date',
        'amount',
        'offer_type',
        'description',
        'status',
        'company_contribution',
        'vin_number',
        'created_at',
        'created_by',
        'updated_at',
        'updated_by',
    ];

    public function export($slug){
        $query = DB::table('offer as i')
            ->select(
                'i.title',                                
                'i.start_date',                           
                'i.end_date',                             
                'i.offer_type',                           
                'i.amount',                               
                'i.company_contribution',                 
                DB::raw("GROUP_CONCAT(DISTINCT b.name SEPARATOR '\n') as branch"), 
                'cm.name as car_name',                    
                'ft.title as fuel_type',                   
                DB::raw("GROUP_CONCAT(DISTINCT cv.name SEPARATOR '\n') as varient_name"), 
                'i.vin_number',                           
                'i.description',
                'i.status',
            )
    
            ->leftJoin('branches as b', DB::raw("FIND_IN_SET(b.id, i.fk_branch_id)"), '>', DB::raw('0'))
            ->leftJoin('car_varient as cv', DB::raw("FIND_IN_SET(cv.id, i.fk_car_varient_id)"), '>', DB::raw('0'))
            ->leftJoin('car_model as cm', 'cm.id', 'i.fk_car_model_id')
            ->leftJoin('fule_type as ft', 'ft.id', 'i.fk_fuel_id')
    
            ->groupBy('i.id')
            ->orderBy('i.id', 'desc');
    
        // ✅ Apply filter BEFORE get
        if($slug != 'all'){
            $query->where('i.status', $slug);
        }
    
        $data = $query->get();
    
        // ✅ ALWAYS return collection (even empty)
        return $data;
    }
}
