<?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 ExportMCPReport 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(
                        'car_model.name AS product', 
                        'car_varient.name AS car_varient_name', 
                        DB::raw("CONCAT(UCASE(LEFT(REPLACE(obf.status, '_', ' '), 1)), 
                        LOWER(SUBSTRING(REPLACE(obf.status, '_', ' '), 2))) AS status"),
                        'obf.mcp_status',
                        'obf.convinience_program as mcp',
                        'mcp.name AS mcp_name',
                        'mcp.amount AS mcp_amount',
                        'mcp_process_dms.mcp_invoice', 
                        'mcp_process_dms.mcp_invoice_date', 
                        'mcp_process_dms.dms_invoice_no',
                        'mcp_process_dms.dms_invoice_date', 
                        'mcp_process_dms.dms_gatepass_date', 
                        'mcp_process_dms.dms_location', 
                        'mcp_process_dms.dms_mcp_create_date', 
                        'mcp_process_dms.dms_mcp_certificate',
                        DB::raw("CONCAT(users.first_name,' ',users.last_name) AS sales_person_name"),
                        'Team_master.team_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('mcp','obf.convinience_program','=','mcp.id')
                    ->leftJoin('mcp_process_dms', 'obf.id', '=', 'mcp_process_dms.obf_id')
                    ->leftjoin('users','users.id','=','obf.sales_person_id')
                    ->leftjoin('Team_master','Team_master.id','users.team_name')
                    ->whereNotNull('obf.mcp_status')
                    ->whereNotNull('obf.convinience_program')
                    ->groupBy(
                        'car_model.name', 
                        'car_varient.name', 
                        'obf.status',
                        'obf.mcp_status',
                        'mcp.name',
                        'mcp.amount',
                        'mcp_process_dms.mcp_invoice',
                        'mcp_process_dms.mcp_invoice_date',
                        'mcp_process_dms.dms_invoice_no',
                        'mcp_process_dms.dms_invoice_date',
                        'mcp_process_dms.dms_gatepass_date',
                        'mcp_process_dms.dms_location',
                        'mcp_process_dms.dms_mcp_create_date',
                        'mcp_process_dms.dms_mcp_certificate')
                       ->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 [
        "Model",
        "Variant",
        "Team Leader",
        "KEC Name",
        "MCP Plan",
        "MCP Amount",
        "MCP Invoice",
        "MCP Invoice Date",
        "DMS Status",
        "MCP Status",
        "DMS Invoice No",
        "DMS Invoice Date",
        "DMS Gatepass Date",
        "DMS Location",
        "DMS MCP Create Date",
        "DMS MCP Certificate",];
    }

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