<?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 ExportCancelReasonReport 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"),
                    'obf.customer_name',
                    'branches.name AS branch_name',
                    'car_model.name AS product', 
                    'car_varient.name AS car_varient_name',
                    'inventory.vin_number',
                    'Team_master.team_name',
                    DB::raw("CONCAT(us.first_name,' ',us.last_name) AS kec_name"),
                    'obf.on_road_price',
                    'obf.booking_date',
                    DB::raw("CONCAT(UCASE(LEFT(REPLACE(obf.status, '_', ' '), 1)), 
                    LOWER(SUBSTRING(REPLACE(obf.status, '_', ' '), 2))) AS status"),
                    'cancellation.name as cancel_reason',
                    'obf.cancel_remark',
                )
                ->leftJoin('products', 'obf.product_id', '=', 'products.id')
                ->leftJoin('branches', 'obf.branch_id', '=', 'branches.id')
                ->leftJoin('car_varient', 'car_varient.id', '=', 'products.veriant')
                ->leftjoin('users as us','us.id','obf.sales_person_id')
                ->leftjoin('Team_master','Team_master.id','us.team_name')
                ->join('inventory', 'inventory.name', '=', 'products.id')
                ->leftJoin('car_model', 'car_model.id', '=', 'products.car_model')
                ->leftJoin('cancellation', 'cancellation.id', '=', 'obf.cancel_reason')
                ->where('obf.status', 'cancel')
                ->groupBy('car_model.name', 'car_varient.name', 'obf.status')
                ->get();

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

        return $data;
    }

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

    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;
    }
}
