<?php

namespace App\Exports;

use App\Models\Product;
use Maatwebsite\Excel\Concerns\Exportable;
use Illuminate\Http\Request;
use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\ShouldAutoSize;
use Maatwebsite\Excel\Concerns\WithHeadings;
use Maatwebsite\Excel\Concerns\WithStyles;
use Maatwebsite\Excel\Events\AfterSheet;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
use Maatwebsite\Excel\Concerns\WithEvents;
use PhpOffice\PhpSpreadsheet\Style\Alignment;


class PriceListExport implements FromCollection, ShouldAutoSize, WithStyles, WithHeadings ,  WithEvents
{
    use Exportable;

    protected $request;
    /**
     * @return \Illuminate\Support\Collection
     */
    public function __construct(Request $request)
    {
        $this->request = $request;
    }
    public function collection()
    {
        $query = Product::select([ 
            'products.name as product_name',
            'products.description as product_description',
            'products.short_name as product_short_name',
            'products.shortcode as product_shortcode',
            'products.hsncode as product_hsncode',
            'product_prices.purchase_price as purchase_price',
            'product_prices.sale_price as sale_price',
            'product_prices.mrp as mrp',
            'product_prices.opening_stock_pack as opening_stock_pack',
            'product_prices.opening_stock_loose as opening_stock_loose',
            'product_prices.opening_stock_value as opening_stock_value',
            'product_prices.batch as batch',
            'product_prices.mfg_date as mfg_date',
            'product_prices.exp_date as exp_date',
            'product_brands.name as brand_name',
            'companies.name as company_name',
            'product_categories.name as category_name',
            'primary_units.name as primary_unit_name',
            'product_alternative_units.name as product_alternative_unit_name',
            'products.convertion_factor as convertion_factor',
            'business_divisions.name  as business_division_name',
            'products.manage_stock as product_manage_stock',
            'product_packagings.name as product_packaging_name',
            'product_groups.name as product_group_name',
            'tax_masters.total_percentage as total_percentage' ,
            ])
            ->leftjoin('tax_masters','tax_masters.id','products.tax_slab_id')
            ->leftjoin('product_prices', 'product_prices.product_id', 'products.id')
            ->leftjoin('business_divisions','products.business_division_id','business_divisions.id')
            ->leftjoin('product_packagings','products.product_packaging_id','product_packagings.id')
            ->leftjoin('product_brands','product_brands.id','products.brand_id')
            ->leftjoin('product_categories','product_categories.id','products.category_id')
            ->leftjoin('companies','products.company_id','companies.id')
            ->leftjoin('primary_units','products.primary_unit_id','primary_units.id')
            ->leftjoin('product_alternative_units','products.alternative_unit_id','product_alternative_units.id')
            ->leftjoin('product_groups', 'product_groups.id', 'products.composition_id');
    
        $product_brand    = $this->request->input('product_brand');
        $product_category = $this->request->input('product_category');
        $product_group    = $this->request->input('product_group');
    
        if ($product_brand !== null) {
            $query->where('products.brand_id', $product_brand);
        }
    
        if ($product_category !== null) {
            $query->where('products.category_id', $product_category);
        }
    
        if ($product_group !== null) {
            $query->where('products.composition_id', $product_group);
        }
    
        $data = $query->get();
    
        $finalData = [];
    
        // Add row numbers to each row in the collection
        foreach ($data as $key => $item) {
            $row = [
                'Product Name'        => $item->product_name ?? '',
                'Batch'               => $item->batch ?? '',
                'Product Description' => $item->product_description ?? '',
                'Product Short Name'  => $item->product_short_name ?? '',
                'Product Short Code'  => $item->product_shortcode ?? '',
                'HSN Number'          => $item->product_hsncode ?? '',
                'Purchase Price'      => $item->purchase_price ?? '',
                'Sale Price'          => $item->sale_price ?? '',
                'MRP'                 => $item->mrp ?? '',
                'Opening Stock Pack'  => $item->opening_stock_pack ?? '',
                'Opening Stock Loose' => $item->opening_stock_loose ?? '',
                'Opening Stock Value' => $item->opening_stock_value ?? '',
                'Mfg. Date'           => $item->mfg_date ?? '',
                'Exp. Date'           => $item->exp_date ?? '',
                'Min. Stock Quantity' => $item->wholesale_price ?? '',
                'Brand/Company'       => $item->brand_name ?? ''. $item->company_name ?? ''  ,
                'Category'            => $item->category_name ?? '',
                'Salt/Composition'    => $item->product_group_name ?? '',
                'Primary Unit'        => $item->primary_unit_name ?? '',
                'Alt. Unit'           => $item->product_alternative_unit_name ?? '',
                'Tax%'                => $item->total_percentage ? $item->total_percentage .'%' : '',
                'Conversion Factor'   => $item->convertion_factor ?? '',
                'Business Division'   => $item->business_division_name ?? '',
                'Manage Stock'        => $item->product_manage_stock ?? '',
                'Packaging'           => $item->product_packaging_name ?? '',
            ];
            
            $finalData[] = $row;
        }
    
        return collect($finalData); // Convert to a collection
    }

    public function headings(): array
    {
        return [
                'Product Name',
                'Batch',
                'Product Description',
                'Product Short Name',
                'Product Short Code',
                'HSN Number',
                'Purchase Price',
                'Sale Price',
                'MRP',
                'Opening Stock Pack',
                'Opening Stock Loose',
                'Opening Stock Value',
                'Mfg. Date',
                'Exp. Date',
                'Min. Stock Quantity',
                'Brand/Company',
                'Category',
                'Salt/Composition',
                'Primary Unit',
                'Alt. Unit',
                'Tax%',
                'Conversion Factor',
                'Business Division',
                'Manage Stock',
                'Packaging',
        ];
    }

    public function styles(Worksheet $sheet)
    {
        return [
            1 => ['font' => ['bold' => true]],
        ];
    }
    public function registerEvents(): array
    {
        return [
            AfterSheet::class => function (AfterSheet $event) {

                $event->sheet->getDelegate()->getStyle('A1:E1')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
                $event->sheet->getDelegate()->getStyle('A1:E1')->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);
            },
        ];
    }
    
}
