<?php

namespace App\Exports;

use App\Models\Branch;
use App\Models\Order;
use App\Models\Texmaster;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Str;
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 ExportSalesReport implements FromCollection, WithHeadings, WithStyles, ShouldAutoSize
{
    protected $slug;
    protected $vin;
    protected $headings = [];

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

        // Static headings
        $this->headings = [
            'No.', 'Branch', 'KEC Name', 'Team Leader', 'Date', 'Particulars', 'Bill to', 'Bill to Address',
            'Ship to', 'Ship to Address', 'Invoice No', 'Voucher Ref. No.- Hypo', 'GSTIN/UIN', 'PAN No.',
            'Addhar Card No', 'Pan Link Status', 'Quantity', 'VIN No.', 'Engine No.', 'Model', 'Varient',
            'Colour', 'Gross Total'
        ];

        $this->appendDynamicHeadings();
    }

    protected function appendDynamicHeadings()
    {
        $taxes = Texmaster::where(['gst' => 28, 'status' => 'active'])
            ->whereIn('compensation_cess', [1, 3, 17, 22])
            ->orderByRaw("FIELD(compensation_cess, 1, 3, 17, 22)")
            ->get();

        $tax_labels = [];
        foreach ($taxes as $tax) {
            $tax_labels[] = "({$tax->gst}%+{$tax->compensation_cess}%)";
        }

        $branches = Branch::select('id', 'city')->where('status', 'active')->get();

        foreach ($branches as $branch) {
            $b_name = '';
            if (str_contains($branch->city, 'Rajkot - Nana Mava')) {
                $b_name = 'Sales- 1S -Local';
            } elseif (str_contains($branch->city, 'Rajkot')) {
                $b_name = 'Sales-Rjt-Local';
            } elseif (str_contains($branch->city, 'Jamnagar')) {
                $b_name = 'Sales-Jmgr-Local';
            } elseif (str_contains($branch->city, 'Junagadh')) {
                $b_name = 'Sales-Jnd-Local';
            } elseif (str_contains($branch->city, 'Morbi')) {
                $b_name = 'Sales-Morbi-Local';
            } elseif (str_contains($branch->city, 'Mehsana')) {
                $b_name = 'Sales-Msn-Local';
            } elseif (str_contains($branch->city, 'Kadi')) {
                $b_name = 'Sales-Kadi-Local';
            }

            foreach ($tax_labels as $tax) {
                $this->headings[] = "$b_name $tax";
            }
        }

        foreach ($tax_labels as $tax) {
            $this->headings[] = "Sales-Interstate $tax";
        }

        $this->headings[] = 'CGST Output -14%';
        $this->headings[] = 'SGST Output -14%';
        $this->headings[] = 'IGST Output -28%';

        foreach ([1, 3, 17, 22] as $cess) {
            $this->headings[] = "Compensation Cess Output @ {$cess}%";
        }
    }

    public function collection()
    {
        $taxes = Texmaster::where(['gst' => 28, 'status' => 'active'])
            ->whereIn('compensation_cess', [1, 3, 17, 22])
            ->orderByRaw("FIELD(compensation_cess, 1, 3, 17, 22)")
            ->get();

        $branches = Branch::select('id', 'city')->where('status', 'active')->get();
        $cess = Texmaster::select('id','compensation_cess')->where('status','active')->orderBy('compensation_cess')->get();

        $branchTaxKeys = [];
        $taxMap = [];
        foreach ($branches as $branch) {
            $b_name = '';
            if (str_contains($branch->city, 'Rajkot - Nana Mava')) {
                $b_name = 'Sales- 1S -Local';
            } elseif (str_contains($branch->city, 'Rajkot')) {
                $b_name = 'Sales-Rjt-Local';
            } elseif (str_contains($branch->city, 'Jamnagar')) {
                $b_name = 'Sales-Jmgr-Local';
            } elseif (str_contains($branch->city, 'Junagadh')) {
                $b_name = 'Sales-Jnd-Local';
            } elseif (str_contains($branch->city, 'Morbi')) {
                $b_name = 'Sales-Morbi-Local';
            } elseif (str_contains($branch->city, 'Mehsana')) {
                $b_name = 'Sales-Msn-Local';
            } elseif (str_contains($branch->city, 'Kadi')) {
                $b_name = 'Sales-Kadi-Local';
            }

            foreach ($taxes as $tax) {
                $key = "$b_name ({$tax->gst}%+{$tax->compensation_cess}%)";
                $branchTaxKeys[$key] = [
                    'branch_id' => $branch->id,
                    'tax_id' => $tax->id
                ];
                $taxMap[$key] = 0;
            }
        }

        foreach ($taxes as $tax) {
            $key = "Sales-Interstate ({$tax->gst}%+{$tax->compensation_cess}%)";
            $taxMap[$key] = 0;
        }

        $records = Order::select(
            DB::raw("DATE_FORMAT(orders.invoice_date, '%d-%m-%Y') as date"),
            DB::raw("COALESCE(obf.customer_name, dealer_management.dealer_name) AS particulars"),
            DB::raw("COALESCE(obf.customer_name, dealer_management.dealer_name) AS bill_to"),
            DB::raw("COALESCE(obf.address, dealer_management.dealer_address) AS bill_to_address"),
            'obf.ship_to_customer_name', 'obf.ship_to_address', 'orders.invoice_no',
            'finance.name as hypo',
            DB::raw("COALESCE(obf.gst, dealer_management.gst_no) AS gst_no"),
            'obf.pan_number as pan_no', 'obf.adhar_number', 'obf.aadhar_pan_link',
            DB::raw("'1.0 Qty' as qty"),
            'orders.vin_number', 'orders.engine_number', 'car_model.name AS car_model',
            'car_varient.name AS car_varient', 'exteriorcolor.name as exterior_color',
            'orders.total as gross_total', 'orders.on_road_price as branch_wise_amt',
            'orders.cgst', 'orders.sgst', 'orders.igst', 'orders.compensation_cess',
            'orders.branch_id', 'tex_master.id as text_master_id',
            DB::raw("CONCAT(users.first_name,' ',users.last_name) AS sales_person_name"),
            'Team_master.team_name', 'branches.name as branch_name'
        )
        ->leftJoin('obf', 'orders.obf_id', '=', 'obf.id')
        ->leftJoin('dealer_management', 'dealer_management.id', '=', 'orders.dealer_id')
        ->leftJoin('obf_finance', 'obf_finance.obf_id', '=', 'obf.id')
        ->leftJoin('finance', 'finance.id', '=', 'obf_finance.finance_name')
        ->leftJoin('inventory', 'inventory.id',  '=', 'orders.inventory_id')
        ->leftJoin('products', 'inventory.name', '=', 'products.id')
        ->leftJoin('car_model', 'products.car_model', '=', 'car_model.id')
        ->leftJoin('car_varient', 'products.veriant', '=', 'car_varient.id')
        ->leftJoin('tex_master', 'car_varient.tex_master_id', '=', 'tex_master.id')
        ->leftJoin('exteriorcolor', 'exteriorcolor.id', '=', 'products.exterior_color')
        ->leftJoin('branches','branches.id','=','obf.branch_id')
        ->leftJoin('users','users.id','=','obf.sales_person_id')
        ->leftJoin('Team_master','Team_master.id','users.team_name')
        ->whereNotNull('orders.invoice_no')
        ->whereNotNull('orders.invoice_date')
        ->groupBy('orders.id')
        ->get();

        $collection = [];
        $index = 1;

        foreach ($records as $row) {
            $data = [
                $index++, $row->branch_name, $row->sales_person_name, $row->team_name, $row->date, $row->particulars,
                $row->bill_to, $row->bill_to_address, $row->ship_to_customer_name, $row->ship_to_address,
                $row->invoice_no, $row->hypo, $row->gst_no, $row->pan_no, $row->adhar_number,
                $row->aadhar_pan_link, $row->qty, $row->vin_number, $row->engine_number,
                $row->car_model, $row->car_varient, $row->exterior_color, $row->gross_total
            ];

            $taxAmounts = [];
            foreach ($branchTaxKeys as $key => $match) {
                $taxAmounts[$key] = ($row->branch_id == $match['branch_id'] && $row->text_master_id == $match['tax_id'])
                    ? round($row->branch_wise_amt, 2) : 0;
            }

            foreach ($taxes as $tax) {
                $key = "Sales-Interstate ({$tax->gst}%+{$tax->compensation_cess}%)";
                $taxAmounts[$key] = 0;
            }

            $taxAmounts['CGST Output -14%'] = $row->cgst ?? 0;
            $taxAmounts['SGST Output -14%'] = $row->sgst ?? 0;
            $taxAmounts['IGST Output -28%'] = $row->igst ?? 0;

            foreach ($cess as $item) {
                $expectedCess = round(($row->branch_wise_amt * (float)$item->compensation_cess) / 100, 2);
                $actualCess = round((float) $row->compensation_cess, 2);
                $taxAmounts["Compensation Cess Output @ {$item->compensation_cess}%"] = abs($actualCess - $expectedCess) < 1 ? $expectedCess : 0;
            }

            $collection[] = array_merge($data, array_values($taxAmounts));
        }

        return collect($collection);
    }

    public function headings(): array
    {
        return $this->headings;
    }

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

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