<?php

namespace App\Exports;

use App\Models\OBF;
use Illuminate\Support\Facades\DB;
use Illuminate\Validation\ValidationException;
use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\WithHeadings;
use Maatwebsite\Excel\Concerns\WithStyles;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
use Maatwebsite\Excel\Concerns\ShouldAutoSize;
use Maatwebsite\Excel\Validators\Failure;

class ExportStatusWiseReport implements FromCollection, WithHeadings, WithStyles, ShouldAutoSize
{
    protected $slug;
    protected $vin;

    public function __construct($slug, $vin = null) {
        $this->slug = $slug;
        $this->vin = $vin;
    }

    public function collection()
    {
        $data = OBF::select([
                DB::raw("ROW_NUMBER() OVER () AS No"),
                'car_model.name AS Model',
                'car_varient.name AS Variant',
                DB::raw("CONCAT(UCASE(LEFT(REPLACE(obf.status, '_', ' '), 1)), 
                LOWER(SUBSTRING(REPLACE(obf.status, '_', ' '), 2))) AS Status"),
                // 'obf.vin AS VIN_Number',
                'obf.on_road_price AS On_Road_Price',
                'Team_master.team_name AS Team_Leader',
                DB::raw("CONCAT(users.first_name, ' ', users.last_name) AS KEC_Name"),
                'obf.created_at AS Date',
                'branches.name AS Location',
                'obf.customer_name AS Customer_Name'
            ])
            ->leftJoin('products', 'obf.product_id', '=', 'products.id') 
            ->leftJoin('car_varient', 'car_varient.id', '=', 'products.veriant')
            ->leftJoin('car_model', 'car_model.id', '=', 'products.car_model')
            ->leftJoin('users', 'users.id', '=', 'obf.sales_person_id')
            ->leftJoin('Team_master', 'Team_master.id', '=', 'users.team_name')
            ->leftJoin('branches', 'branches.id', '=', 'obf.branch_id')  
            ->when($this->vin, function ($query) {
                return $query->where('obf.vin', 'like', "%{$this->vin}%");
            })
            ->get();

        if ($data->isEmpty()) {
            throw new \ErrorException('No Data Found!');
        }

        return $data;
    }

    public function headings(): array
    {
        return [
            "No", 
            "Model", 
            "Variant", 
            "Status", 
            // "VIN Number", 
            "On Road Price", 
            "Team Leader", 
            "KEC Name", 
            "Date", 
            "Location", 
            "Customer Name"
        ];
    }

    public function styles(Worksheet $sheet)
    {
        return [
            1 => ['font' => ['bold' => true]],
        ];
    }

    public function onFailure(Failure ...$failures)
    {
        $exception = ValidationException::withMessages(collect($failures)->map->toArray()->all());
        throw $exception;
    }
}
