<?php

namespace App\Exports;

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 ExportPurchaseReport 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 = DB::table('inventory as i')
            ->select(
                'cm.name AS model_name',
                'cv.name AS variant_name',
                'i.vin_number',
                'interiorcolor.name AS interior_color',
                'exteriorcolor.name AS exterior_color',
                DB::raw("IFNULL(i.status, 'N/A') AS status"),
                'i.ex_showroom_price',
                DB::raw("IFNULL(DATE_FORMAT(i.kin_invoice_date, '%d-%m-%Y'), 'N/A') AS kin_invoice_date"),
                'i.kin_invoice_no',
                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')
            ->whereNotIn('i.status', ['allocatedDealer', 'deleted'])
            ->when($this->vin, function ($query) {
                return $query->where('i.vin_number', 'like', "%{$this->vin}%");
            })
            ->groupBy('cm.name', 'cv.name', 'i.vin_number', 'interiorcolor.name', 'exteriorcolor.name', 'i.status', 'i.ex_showroom_price', 'i.kin_invoice_date', 'i.kin_invoice_no', 'users.first_name', 'users.last_name', 'Team_master.team_name')
            ->orderBy('i.id', 'desc')
            ->get();

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

        return $data;
    }

    public function headings(): array
    {
        return [
            "Model Name", "Variant", "VIN Number", "Interior", "Exterior", "Status", "Ex Showroom Price", "KIN Invoice Date", "KIN Invoice Number", "Team Leader", "KEC 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;
    }
}
