<?php

namespace App\Http\Controllers\Product;

use App\Http\Controllers\Controller;
use App\Exports\PriceListExport;
use App\Exports\ProductsExport;
use App\Models\MasterType;
use Illuminate\Http\Request;
use App\Models\Product;
use App\Models\ProductAlternativeUnit;
use App\Models\ProductAttribute;
use App\Models\ProductAttributeType;
use App\Models\ProductBrand;
use App\Models\ProductCategory;
use App\Models\ProductGroup;
use App\Models\ProductItemType;
use App\Models\TaxMaster;
use App\Models\PrimaryUnit;
use App\Models\ProductPrice;
use App\Models\ProductWarehouse;
use App\Models\ProductLocationRack;
use App\Models\ProductLocation;
use App\Imports\ProductExcelImport;
use App\Models\BusinessDivision;
use App\Models\ProductUpload;
use Maatwebsite\Excel\Facades\Excel;
use App\Models\MenuModelPermission;
use App\Models\ProductFeature;
use App\Models\ProductTestPackage;
use App\Models\Stock;
use App\Models\Company;
use Carbon\Carbon;
use Yajra\DataTables\Facades\DataTables;
use App\Models\ProductPackaging;
use Illuminate\Support\Facades\Auth;

class ProductsController extends Controller
{
    function __construct()
    {
        // $this->middleware('permission:product-list', ['only' => ['index']]);
    }
    /**
     * Display a listing of the resource.
     *
     * @return \Illuminate\Http\Response
     */
    public function index(Request $request)
    {

        \Session::forget('category_id');
        
        $productCategory = ProductCategory::where('status','1')
        ->latest('priority')
        ->get(['id','name']);

        $page        = $request->page ?? 1;
        $category_id = $request->get('category_id')??'';
        $status      = $request->status??1;

       

        $dataSession = \Session::get('category_id')??[];
       
        if (!isset($dataSession['category_id']) || $dataSession['category_id'] == '' || $category_id) {
            $dataSession['category_id'] = $category_id;

            \Session::put('category_id', $dataSession);
           
        }
       
        $SessionCategory = isset($dataSession['category_id'])?$dataSession['category_id']:'';
       
        $data = Product::select([
                'products.id',
                'products.name',
                'products.hsncode',
                'products.convertion_factor',
                'products.priceasper',
                'product_prices.purchase_price',
                'product_prices.sale_price',
                'product_prices.mrp',
                'product_categories.name as category',
                'product_groups.name as composition',
                'product_brands.name as brand',
                'tax_masters.name as tax',
                'primary_units.name as primary_unit',
                'product_alternative_units.name as alternative_unit',
                'test_packages.name as testName',
                'product_packagings.name as packaging_name',
                'products.status',
                'product_prices.batch',
                'products.manage_stock'
            ])
            ->leftjoin('product_brands','product_brands.id','products.brand_id')
            ->leftjoin('product_categories','product_categories.id','products.category_id')
            ->leftjoin('tax_masters','tax_masters.id','products.tax_slab_id')
            ->leftjoin('primary_units', 'primary_units.id', 'products.primary_unit_id')
            ->leftjoin('product_alternative_units', 'product_alternative_units.id', 'products.alternative_unit_id')
            ->leftjoin('product_prices', 'product_prices.product_id', 'products.id')
            ->leftjoin('product_test_packages', 'product_test_packages.product_id', 'products.id')
            ->leftjoin('test_packages', 'product_test_packages.test_package_id', 'test_packages.id')
            ->leftjoin('product_groups', 'product_groups.id', 'products.composition_id')
            ->leftjoin('product_packagings', 'product_packagings.id', 'products.product_packaging_id')
            ->where('products.status',$status);
            $data = $data->when(!empty($SessionCategory) && !empty($SessionCategory) && $SessionCategory!='', function ($query) use ($SessionCategory) {
                $query->where('products.category_id',$SessionCategory);
            });

        if ($request->ajax()) {
            $sort_by      = $request->get('sortby') ?? 10;
            $sort_type    = $request->get('sorttype');
            $search_query = $request->get('query');
            $search_type  = $request->get('search_type');
           
           

            $data = $data->when(!empty($search_query) && !empty($search_type), function ($query) use ($search_query, $search_type) {
                if ($search_type == 'hsncode') {
                    $query->where('products.hsncode', 'like', '%'.$search_query.'%');
                } else if ($search_type == 'name') {
                    $query->where('products.name', 'like', '%'.$search_query.'%');
                    $query->OrWhere('products.short_name', 'like', '%'.$search_query.'%');
                } else if ($search_type == 'category') {
                    $query->where('product_categories.name', 'like', '%'.$search_query.'%');
                } else if ($search_type == 'composition') {
                    $query->where('product_groups.name', 'like', '%'.$search_query.'%');
                } else if ($search_type == 'brand') {
                    $query->where('product_brands.name', 'like', '%'.$search_query.'%');
                }
            });
           
            $data = $data->orderBy('products.id','ASC')
            ->groupBy('products.id')
            ->paginate($sort_by);
            return view('products.table', compact('data','SessionCategory'));
        }
        else
        {
            $data = $data->groupBy('products.id')
                ->orderBy('products.id','ASC')
                ->paginate(10);

            return view('products.index',compact('data','productCategory','SessionCategory'));
        }
    }

    /**
     * Show the form for creating a new resource.
     *
     * @return \Illuminate\Http\Response
     */
    public function create()
    {
        $alternative_units = ProductAlternativeUnit::select(['id','name','is_default'])->where(['status' => 1])->get();
        $attribute_types   = ProductAttributeType::select(['id','name','is_default'])->where(['status' => 1])->get();
        $attributes        = ProductAttribute::select(['id','name','is_default'])->where(['status' => 1])->get();
        $categories        = ProductCategory::select(['id','name','is_default'])->where(['status' => 1])->get();
        $item_types        = ProductItemType::select(['id','name','is_default'])->where(['status' => 1])->get();
        $brands            = ProductBrand::select(['id','name','is_default'])->where(['status' => 1])->get();
        $groups            = ProductGroup::select(['id','name','is_default'])->where(['status' => 1])->get();
        $tax_master        = TaxMaster::select(['id','name','default'])->where(['status' => '1'])->get();
        $primary_units     = PrimaryUnit::select(['id','name','is_default'])->where(['status' => 1])->whereNull('deleted_at')->get();
        $warehouses        = ProductWarehouse::select(['id','name','is_default'])->where(['status' => 1])->get();
        $location_racks    = ProductLocationRack::select(['id','name','is_default'])->where(['status' => 1])->get();
        $business_division = BusinessDivision::select(['id','name','is_default'])->where(['status' => 1])->get();
        $product_packaging = ProductPackaging::select(['id','name'])->where(['status' => 1])->get();

        return view('products.create', compact(
                'alternative_units',
                'attributes',
                'attribute_types',
                'brands',
                'categories',
                'groups',
                'item_types',
                'tax_master',
                'primary_units',
                'alternative_units',
                'warehouses',
                'location_racks',
                'business_division',
                'product_packaging',
            )
        );
    }

    /**
     * Store a newly created resource in storage.
     *
     * @param  \Illuminate\Http\Request  $request
     * @return \Illuminate\Http\Response
     */
    public function store(Request $request)
    {
        $validator = \Validator::make($request->all(), [
            'name'        => 'required',
            'tax_slab_id' => 'required',
            'brand_id'    => 'required',
            'category_id' => 'required',
            'mrp'         => 'required',
            // 'batch'    => 'required',
        ]);

        if ($validator->fails()) {
            if ($request->ajax()) {
                return response()->json([
                    'success' => false,
                    'message' => $validator->getMessageBag()->first(),
                    'data'    => []
                ]);
            }
            return redirect()->back()->with('error', $validator->getMessageBag()->first());
        }

        $product = [
            'name'                 => $request->name ?? '',
            'product_code'         => strtoupper(str_replace(' ', '_', $request->product_code)) ?? '',
            'short_name'           => $request->short_name ?? '',
            'description'          => $request->description ?? '',
            'shortcode'            => $request->shortcode ?? '',
            'hsncode'              => $request->hsncode ?? '',
            'tax_slab_id'          => $request->tax_slab_id ?? '',
            'brand_id'             => $request->brand_id ?? '',
            'category_id'          => $request->category_id ?? '',
            'composition_id'       => $request->composition_id ?? '',
            'attribute_group_main' => $request->attribute_group_main ?? '',
            'attribute_group_sub'  => $request->attribute_group_sub ?? '',
            'convertion_factor'    => $request->convertion_factor ?? '1',
            'priceasper'           => $request->priceasper ?? '',
            'item_type_id'         => $request->item_type_id ?? '',
            'item_min_stock_qty'   => $request->item_min_stock_qty ?? '',
            'warehouse_id'         => $request->warehouse_id ?? '',
            'manage_stock'         => $request->manage_stock ?? '',
            'alternative_unit_id'  => $request->alternative_unit_id ?? '',
            'primary_unit_id'      => $request->primary_unit_id ?? '',
            'business_division_id' => $request->business_division_id ?? '',
            'product_packaging_id' => $request->product_packaging_id ?? '',
            'additional_info'      => $request->additional_info ?? '',
            'company_id'           => \Auth::user()->company_id ?? 1,
            ];

        $created_product = Product::create($product);

        if ($request->location_rack_id != '') {
            ProductLocation::create([
                'product_id'       => $created_product->id,
                'location_rack_id' => $request->location_rack_id
            ]);
        }



        $product_price = [
            'product_id'      => $created_product->id ?? '',
            'bar_code'        => $request->bar_code ?? '',
            'batch'           => $request->batch ?? '',
            'exp_date'        => $request->exp_date != '' ? date('Y-m-d', strtotime($request->exp_date)) : null,
            'mfg_date'        => $request->mfg_date != '' ? date('Y-m-d', strtotime($request->mfg_date)) : null,
            'mfg_cost'        => $request->mfg_cost ?? '0',
            'min_sale_price'  => $request->min_sale_price ?? '0',
            'purchase_price'  => $request->purchase_price ?? '0',
            'wholesale_price' => $request->wholesale_price ?? '0',
            'sale_price'      => $request->sale_price ?? '0',
            'mrp'             => $request->mrp ?? '0',
        ];

        // dd($product_price);
        $sub_attribute_ids = $request->sub_attribute_id ?? [];

        // Create default product features with status 'No'
        $module_code = MenuModelPermission::select('id', 'code')->get();
        foreach ($module_code as $key => $record) {
            $data = [
                'module_code'  => $record->id,
                'product_code' => $request->shortcode,
                'status'       => '0'
            ];

            ProductFeature::create($data);
        }

        if (isset($request->main_attribute_id) && count($request->main_attribute_id) > 0) {
            foreach($request->main_attribute_id as $key => $main_attribute) {
                $product_price['main_attribute_id'] = $main_attribute;
                $product_price['sub_attribute_id']  = $sub_attribute_ids[$key] ?? '';
                $productPrice = ProductPrice::create($product_price);
                $product_price_batch_id = $productPrice->id;
            }
        } else {
            $productPrice = ProductPrice::create($product_price);
            $product_price_batch_id = $productPrice->id;
        }

        /************************ Stock In *************/
        
        if($request->priceasper=='Yes' && $request->item_type_id==1)
        {
            //stockIn($voucher_id='',$voucher_type='',$created_product->id,$request->item_min_stock_qty,$request->min_sale_price,$invoice_batch_id='',$product_price_batch_id,"OPENINGSTOCK");
        }

        if(isset($request->redirect_url) && $request->redirect_url=='purchase/create')
        {
            return redirect($request->redirect_url)
            ->with('success','Product created successfully');
        }
        elseif(isset($request->redirect_url) && $request->redirect_url!='')
        {
            return redirect()->route('doctor.create')
            ->with('success','Product created successfully');
        }else{
            return redirect()->route('products.index')
            ->with('success','Product created successfully');
        }



    }

    /**
     * Display the specified resource.
     *
     * @param  int  $id
     * @return \Illuminate\Http\Response
     */
    public function show(Request $request,$id)
    {   
        $page = isset($request->page)?$request->page:1;
        $productName = Product::select('id','name')->where('id',$id)->first();
        $product = Product::select([
                'products.*',
                'product_categories.name as category',
                'product_groups.name as composition',
                'product_brands.name as brand',
                'tax_masters.name as tax',
                'primary_units.name as primary_unit',
                'product_alternative_units.name as alternative_unit',
                'product_item_types.name as item_type',
                'product_warehouses.name as warehouse',
                'business_divisions.name as devisions',
                'product_packagings.name as packaging_names',

            ])
            ->leftjoin('product_brands','product_brands.id','products.brand_id')
            ->leftjoin('product_categories','product_categories.id','products.category_id')
            ->leftjoin('tax_masters','tax_masters.id','products.tax_slab_id')
            ->leftjoin('primary_units', 'primary_units.id', 'products.primary_unit_id')
            ->leftjoin('product_alternative_units', 'product_alternative_units.id', 'products.alternative_unit_id')
            ->leftjoin('product_groups', 'product_groups.id', 'products.composition_id')
            ->leftjoin('product_item_types', 'product_item_types.id', 'products.item_type_id')
            ->leftjoin('product_warehouses', 'product_warehouses.id', 'products.warehouse_id')
            ->leftjoin('business_divisions','business_divisions.id','products.business_division_id')
            ->leftjoin('product_packagings','product_packagings.id','products.product_packaging_id')
            ->where('products.id', $id)
            ->first();

        $prices = ProductPrice::where('product_id', $id)->get();

        return view('products.show', compact('product', 'prices','productName','page'));
    }

    /**
     * Show the form for editing the specified resource.
     *
     * @param  int  $id
     * @return \Illuminate\Http\Response
     */
    public function edit(Request $request,$id)
    {
        $page = isset($request->page)?$request->page:1;
        $alternative_units = ProductAlternativeUnit::select(['id','name','is_default'])->where(['status' => 1])->get();
        $categories        = ProductCategory::select(['id','name','is_default'])->where(['status' => 1])->get();
        $item_types        = ProductItemType::select(['id','name','is_default'])->where(['status' => 1])->get();
        $brands            = ProductBrand::select(['id','name','is_default'])->where(['status' => 1])->get();
        $groups            = ProductGroup::select(['id','name','is_default'])->where(['status' => 1])->get();
        $tax_master        = TaxMaster::select(['id','name','default'])->where(['status' => '1'])->get();
        $primary_units     = PrimaryUnit::select(['id','name'])->where(['status' => 1])->whereNull('deleted_at')->get();
        $warehouses        = ProductWarehouse::select(['id','name'])->where(['status' => 1])->get();
        $location_racks    = ProductLocationRack::select(['id','name'])->where(['status' => 1])->get();
        $business_division = BusinessDivision::select(['id','name','is_default'])->where(['status' => 1])->get();
        $product_packaging = ProductPackaging::select(['id','name'])->where(['status' => 1])->get();
        $product           = Product::find($id);

        return view('products.edit', compact(
                'product',
                'alternative_units',
                'brands',
                'categories',
                'groups',
                'item_types',
                'tax_master',
                'primary_units',
                'alternative_units',
                'warehouses',
                'location_racks',
                'business_division',
                'product_packaging',
                'page'
            )
        );
    }

    /**
     * Update the specified resource in storage.
     *
     * @param  \Illuminate\Http\Request  $request
     * @param  int  $id
     * @return \Illuminate\Http\Response
     */
    public function update(Request $request, $id)
    {
        $currentPage = $request->page??1;
        $validator = \Validator::make($request->all(), [
            'name'        => 'required',
            'tax_slab_id' => 'required',
            'brand_id'    => 'required',
            'category_id' => 'required',
        ]);

        if ($validator->fails()) {
            if ($request->ajax()) {
                return response()->json([
                    'success' => false,
                    'message' => $validator->getMessageBag()->first(),
                    'data'    => []
                ]);
            }
            return redirect()->back()->with('error', $validator->getMessageBag()->first());
        }

        $data = $request->except([
            '_token',
            '_method'
        ]);

        $products = Product::find($id);

        if (!$products) {
            return redirect()->route('products.index',['page' => $currentPage])
                ->with('error','Product not found!');
        }
        $data['company_id'] = \Auth::user()->company_id ?? 1;
        $products->update($data);

        if ($request->ajax()) {
            return response()->json([
                'success' => true,
                'message' => 'Product updated successfully',
                'data'    => ['name'=> $products->name,
                              'id'  => $products->id]
            ]);
        }
        return redirect()->route('products.index',['page' => $currentPage])
            ->with('success','Product updated successfully');
    }

    /**
     * Remove the specified resource from storage.
     *
     * @param  int  $id
     * @return \Illuminate\Http\Response
     */
    public function destroy(Request $request)
    {
        ProductUpload::whereId($request->id)->delete();

        return response()->json([
            'success' => true,
            'message' => ['Deleted successfully'],
            'data'    => []
        ]);
    }

    public function getAttributes(Request $request)
    {
        $attributes = ProductAttribute::select(['id','name'])->where(['status' => 1, 'product_attribute_type_id' => $request->attribute_group_id])->get();

        return view('product-attributes.attributes', compact('attributes'));
    }

    public function getstatuses(Request $request, $id)
    {
        $product = Product::select(['id', 'status', 'enable_serial_number_tracking', 'allow_negative_stock', 'is_raw_material'])->where('id', $id)->first();

        return view('products.statuses', compact('product'));
    }

    public function getSubAttributeGroup(Request $request)
    {
        $attributes = ProductAttributeType::select(['id','name'])
            ->where(['status' => 1])
            ->where('id', '!=', $request->attribute_group_id)
            ->get();

        return view('product-attributes.attributes', compact('attributes'));
    }

    public function productPrices(Request $request, $id)
    {
        $prices = ProductPrice::where('product_id', $id)->get();

        return view('products.prices', compact('prices'));
    }

    public function changeIsRawMaterial(Request $request)
    {
       if ($request->ajax())
       {
           $data   = array('is_raw_material' => $request->status);
           $Update = Product::where('id', '=', $request->id)->update($data);

           if($Update){
               return response()->json([
                   'success'=>true,
                   'message'=>['Product Status successfully updated.'],
                   'data'=>[
                       'redirect'=>'/buckets/',
                       'reload'=>true,
                   ]
               ]);
           }
           else{
               return response()->json([
                   'success'=>false,
                   'message'=>['Error for change status'],
                   'data'=>[
                       'redirect'=>'',
                   ]
               ]);
           }
       }
    }

    public function changeSerialTracking(Request $request)
    {
       if ($request->ajax())
       {
           $data   = array('enable_serial_number_tracking' => $request->status);
           $Update = Product::where('id', '=', $request->id)->update($data);

           if($Update){
               return response()->json([
                   'success'=>true,
                   'message'=>['Product Status successfully updated.'],
                   'data'=>[
                       'redirect'=>'/buckets/',
                       'reload'=>true,
                   ]
               ]);
           }
           else{
               return response()->json([
                   'success'=>false,
                   'message'=>['Error for change status'],
                   'data'=>[
                       'redirect'=>'',
                   ]
               ]);
           }
       }
    }

    public function changeNegativeStock(Request $request)
    {
       if ($request->ajax())
       {
           $data   = array('allow_negative_stock' => $request->status);
           $Update = Product::where('id', '=', $request->id)->update($data);

           if($Update){
               return response()->json([
                   'success'=>true,
                   'message'=>['Product Status successfully updated.'],
                   'data'=>[
                       'redirect'=>'/buckets/',
                       'reload'=>true,
                   ]
               ]);
           }
           else{
               return response()->json([
                   'success'=>false,
                   'message'=>['Error for change status'],
                   'data'=>[
                       'redirect'=>'',
                   ]
               ]);
           }
       }
    }

    public function changeStatus(Request $request)
    {
       if ($request->ajax())
       {
           $data   = array('status' => $request->status);
           $Update = Product::where('id', '=', $request->id)->update($data);

           if($Update){
               return response()->json([
                   'success'=>true,
                   'message'=>['Product Status successfully updated.'],
                   'data'=>[
                       'redirect'=>'/buckets/',
                       'reload'=>true,
                   ]
               ]);
           }
           else{
               return response()->json([
                   'success'=>false,
                   'message'=>['Error for change status'],
                   'data'=>[
                       'redirect'=>'',
                   ]
               ]);
           }
       }
    }

    public function importProductForm(Request $request)
    {
        $query = ProductUpload::query(); // Use query() method to start a new query

        if ($request->ajax()) {
            $sort_by = $request->get('sortby') ?? 10;
            $sort_type = $request->get('sorttype');
            $search_query = $request->get('query');

            $query->when(!empty($search_query), function ($query) use ($search_query) {
                return $query->where('product_name', 'like', '%' . $search_query . '%');
            });

            $data = $query->paginate($sort_by);

            return view('products.product-import-table', compact('data'));
        } else {
            $data = $query->paginate(10);
            return view('products.bulk-import', compact('data'));
        }

    }



    public function importForm(Request $request)
    {
        $businessDivision = BusinessDivision::select(['id','name','code'])->where('status',1)->get();
        return view('products.import',compact('businessDivision'));
    }

    public function editProduct(Request $request, $id)
    {
        $data = ProductUpload::find($id);

        return view('products.edit-modal', compact('data'));
    }

    public function updateProduct(Request $request, $id)
    {
        $validator = \Validator::make($request->all(), [
            'product_name'      => 'required',
            'business_division' => 'required',
            'composition'       => 'required',
            // 'product_description' => 'required',
            // 'product_short_name'  => 'required',
            // 'product_short_code'  => 'required',
            // 'product_hsn'         => 'required',
            // 'purchase_price'      => 'required',
            'sale_price'          => 'required',
            'product_mrp'         => 'required',
            // 'minimum_sale_price'  => 'required',
            // 'barcode'             => 'required',
            // 'opening_stock_pack'  => 'required',
            // 'opening_stock_loose' => 'required',
            // 'opening_stock_value' => 'required',
            // 'batch'               => 'required',
            // 'mfg_date'            => 'required',
            // 'exp_date'            => 'required',
            // 'min_stock_qty'       => 'required',
            'brand'               => 'required',
            'category'            => 'required',
            'primary_unit'        => 'required',
            'alternative_unit'    => 'required',
            'tax'                 => 'required',
            // 'conversion_factor'   => 'required' If not then will be 1
        ]);

        if ($validator->fails()) {
            if ($request->ajax()) {
                return response()->json([
                    'success' => false,
                    'message' => $validator->getMessageBag()->first(),
                    'data'    => []
                ]);
            }

            return redirect()->back()->with('error', $validator->getMessageBag()->first());
        }

        $data = $request->except(['_token']);

        $data['conversion_factor'] = !isset($data['conversion_factor']) || empty($data['conversion_factor']) ? 1
                : $data['conversion_factor'];

        ProductUpload::whereId($id)->update($data);

        if ($request->ajax()) {
            return response()->json([
                'success' => true,
                'message' => 'Product updated successfully'
            ]);
        }

        return redirect()->back()
            ->with('success','Product updated successfully');
    }

    public function importBulkProducts(Request $request)
    {
        ini_set('max_execution_time', 500);

        $validator = \Validator::make($request->all(), [
            'file'                  => 'required|mimes:xlsx',
            'business_division_id'  => 'required'
        ]);

        if ($validator->fails()) {
            if ($request->ajax()) {
                return response()->json([
                    'success' => false,
                    'message' => 'The file must be latest version of excel after 2003. File of type: xlsx .',
                    'data'    => []
                ]);
            }

            return redirect()->back()->with('error', $validator->getMessageBag()->first());
        }

        $records  = [];
        $data     = Excel::toArray(new ProductExcelImport, $request->file('file'));
        $data     = isset($data[0]) ? $data[0] : [];
        $headings = [];

        if (isset($data[0])) {
            $headings = $data[0];
            unset($data[0]);
        }

        if (!empty($data)) {
            // Truncate table.
            \DB::table('product_uploads')->truncate();

            foreach ($data as $key => $record) {
                $mfg_date = null;
                $exp_date = null;

                if (empty($record[array_search("Product Name", $headings)])) {
                    continue;
                }

                $mfg_date_index = array_search("Mfg. Date", $headings);
                if ($mfg_date_index !== false && isset($record[$mfg_date_index]) && !empty($record[$mfg_date_index]) && is_numeric($record[$mfg_date_index]) && $record[$mfg_date_index] > 0) {
                    $excel_mfg_date = $record[$mfg_date_index];
                    if ($excel_mfg_date != 0) {
                        $mfg_date = \PhpOffice\PhpSpreadsheet\Shared\Date::excelToDateTimeObject($excel_mfg_date);
                        $mfg_date = Carbon::instance($mfg_date)->startOfDay();
                    }
                }

                $exp_date_index = array_search("Exp. Date", $headings);
                if ($exp_date_index !== false && isset($record[$exp_date_index]) && !empty($record[$exp_date_index]) && is_numeric($record[$exp_date_index]) && $record[$exp_date_index] > 0) {
                    $excel_exp_date = $record[$exp_date_index];
                    if ($excel_exp_date != 0) {
                        $exp_date = \PhpOffice\PhpSpreadsheet\Shared\Date::excelToDateTimeObject($excel_exp_date);
                        $exp_date = Carbon::instance($exp_date)->startOfDay()->endOfMonth();
                    }
                }

                $tax_index = array_search("Tax%", $headings);
                $tax = isset($record[$tax_index]) ? $record[$tax_index] : 0;
                // Populate the $records array
                $records[$key] = [
                    'product_name'        => $record[array_search("Product Name", $headings)],
                    'product_description' => $record[array_search("Product Description", $headings)],
                    'product_short_name'  => $record[array_search("Product Short Name", $headings)],
                    'product_short_code'  => $record[array_search("Product Short Code", $headings)],
                    'product_hsn'         => isset($record[array_search("HSN Number", $headings)]) ? $record[array_search("HSN Number", $headings)] : null,
                    'purchase_price'      => $record[array_search("Purchase Price", $headings)],
                    'sale_price'          => $record[array_search("Sale Price", $headings)],
                    'product_mrp'         => $record[array_search("MRP", $headings)],
                    // 'minimum_sale_price'  => $record[array_search("Minimum Sale Price", $headings)],
                    // 'barcode'             => $record[array_search("Barcode", $headings)],
                    'opening_stock_pack'  => $record[array_search("Opening Stock Pack", $headings)],
                    'opening_stock_loose' => $record[array_search("Opening Stock Loose", $headings)],
                    'opening_stock_value' => $record[array_search("Opening Stock Value", $headings)],
                    'batch'               => $record[array_search("Batch", $headings)],
                    'mfg_date'            => $mfg_date,
                    'exp_date'            => $exp_date,
                    'min_stock_qty'       => $record[array_search("Min. Stock Quantity", $headings)],
                    'brand'               => $record[array_search("Brand/Company", $headings)],
                    'category'            => $record[array_search("Category", $headings)],
                    'composition'         => $record[array_search("Salt/Composition", $headings)],
                    'primary_unit'        => $record[array_search("Primary Unit", $headings)],
                    'alternative_unit'    => $record[array_search("Alt. Unit", $headings)],
                    'packaging'           => $record[array_search("Packaging", $headings)],
                    'tax'                 => $tax,
                    'conversion_factor'   => $record[array_search("Conversion Factor", $headings)],
                    'company_id'          => auth()->user()->company_id,
                    'business_division'   => isset($record[array_search("Business Division", $headings)]) ? $record[array_search("Business Division", $headings)] : $request->business_division_id,
                    'manage_stock'        => isset($record[array_search("Manage Stock", $headings)]) ? $record[array_search("Manage Stock", $headings)] : null,
                    'status'              => '1',
                    'created_at'          => now(),
                    'updated_at'          => now(),
                ];
            }

            ProductUpload::insert($records);

            return response()->json([
                'success' => true,
                'message' => 'Products are uploaded successfully.',
                'data'    => []
            ]);
        } else {
            return response()->json([
                'success' => false,
                'message' => 'Products data not found!',
                'data'    => []
            ]);
        }
    }

    // public function importProducts(Request $request)
    // {
    //     ini_set('max_execution_time', 500);
    //     $data = ProductUpload::all();
    //     $duplicate = $unique = $emptyName = $emptyPrimaryUnit = $recordsDeleted = 0;

    //     if (count($data) > 0) {

    //         foreach ($data as $key => $record) {
    //             // dd($record->business_division);
    //             if (!isset($record->product_name) && !isset($record->primary_unit)) {
    //                 $emptyName += 1;
    //                 $emptyPrimaryUnit += 1;
    //                 continue; // Skip this record and move to the next one
    //             }

    //             if (!isset($record->product_name)) {
    //                 $emptyName += 1;
    //                 continue;
    //             }

    //             if (!isset($record->primary_unit)) {
    //                 $emptyPrimaryUnit += 1;
    //                 continue;
    //             }

    //             $records[$key]['tax'] = $record[21];

    //             $exists = Product::where('name', $record->product_name)
    //                 ->where('primary_unit_id', $this->primaryUnitExists($record->primary_unit))
    //                 ->exists();

    //                 if (!$exists) {
    //                     $tax_id               = $this->taxMasterExists($record->tax);
    //                     $business_division_id = !empty($record->business_division) ? $this->businessDivisionExists($record->business_division) : 1;
    //                     $brand_id             = $this->brandExists($record->brand);
    //                     $group_id            = !empty($record->composition) ? $this->productGroup($record->composition) : 1;
    //                     $category_id         = $this->categoryExists($record->category);
    //                     $primary_unit_id     = $this->primaryUnitExists($record->primary_unit);
    //                     $alternative_unit_id = $this->alternativeUnitExists($record->alternative_unit, $primary_unit_id, $record->company_id);

    //                     $created_product = Product::create([
    //                         'name'                 => $record->product_name,
    //                         'short_name'           => $record->product_short_name,
    //                         'description'          => $record->product_description,
    //                         'shortcode'            => $record->product_short_code,
    //                         'hsncode'              => $record->product_hsn,
    //                         'item_min_stock_qty'   => $record->min_stock_qty,
    //                         'convertion_factor'    => $record->conversion_factor,
    //                         'brand_id'             => $brand_id,
    //                         'category_id'          => $category_id,
    //                         'primary_unit_id'      => $primary_unit_id,
    //                         'alternative_unit_id'  => $alternative_unit_id,
    //                         'company_id'           => $record->company_id,
    //                         'tax_slab_id'          => $tax_id,
    //                         'manage_stock'         => $record->manage_stock ?? 'yes',
    //                         'business_division_id' => $business_division_id,
    //                         'composition_id'       => $group_id,
    //                         'status'               => '1'
    //                     ]);

    //                     Stock::create([
    //                         'entry_date'            => date('Y-m-d'),
    //                         'product_id'            => $created_product->id,
    //                         'pack_qty_in'           => $record->opening_stock_pack??'',
    //                         'loose_qty_in'          => $record->opening_stock_loose??'',
    //                         'taxable_unit_price'    => $record->sale_price ?? '',
    //                         'created_by_user_id'    => auth()->user()->id,
    //                         'company_id'            => $record->company_id,
    //                         'financial_year_id'     => $this->financialyear(),
    //                         'status'                => '1',
    //                     ]);
    //                     $existingProductPrice = ProductPrice::where('batch', $record->batch)
    //                     ->first();

    //                     if (!$existingProductPrice) {
    //                         $product_price = [
    //                             'mrp'                 => $record->product_mrp ?? '',
    //                             'batch'               => $record->batch ?? 'B001',
    //                             'bar_code'            => $record->barcode ?? '',
    //                             'exp_date'            => $record->exp_date ?? null,
    //                             'mfg_date'            => $record->mfg_date ?? null,
    //                             'mfg_cost'            => $record->mfg_cost ?? '',
    //                             'product_id'          => $created_product->id ?? '',
    //                             'sale_price'          => $record->sale_price ?? '',
    //                             'min_sale_price'      => $record->minimum_sale_price ?? '',
    //                             'purchase_price'      => $record->purchase_price ?? '',
    //                             'wholesale_price'     => $record->wholesale_price ?? '',
    //                             'opening_stock_loose' => $record->opening_stock_loose ?? '0',
    //                             'opening_stock_value' => $record->opening_stock_value ?? '0',
    //                             'opening_stock_pack'  => $record->opening_stock_pack ?? '0',
    //                         ];
    //                         // dd($product_price);
    //                         ProductPrice::create($product_price);
    //                     }

    //                     \DB::table('product_uploads')->where('product_name', $record->product_name)->delete();

    //                     $unique += 1;
    //                 } else {
    //                     // Delete the existing record as it has both product_name and primary_unit
    //                     \DB::table('product_uploads')->where('product_name', $record->product_name)->delete();
    //                 $duplicate += 1;
    //                 $recordsDeleted += 1;
    //             }
    //         }

    //             // Do not truncate table.

    //             $errorMessage = '';

    //             if ($emptyName > 0 && $emptyPrimaryUnit > 0) {
    //                 $errorMessage .= $emptyName . ' records have empty product names and ' .
    //                                 $emptyPrimaryUnit . ' records have empty primary units. Please fill the product name and primary unit. ';
    //             } elseif ($emptyName > 0) {
    //                 $errorMessage .= $emptyName . ' records have empty product names. Please fill the product name. ';
    //             } elseif ($emptyPrimaryUnit > 0) {
    //                 $errorMessage .= $emptyPrimaryUnit . ' records have empty primary units. Please fill the primary unit. ';
    //             }

    //             return response()->json([
    //                 'success' => true,
    //                 'message' => $unique . ' products imported. ' . $duplicate . ' duplicate records skipped.',
    //                 'data'    => [
    //                     'inserted' => $unique,
    //                     'deleted'  => $recordsDeleted,
    //                     'not_inserted' => $emptyName + $emptyPrimaryUnit,
    //                 ],
    //             ]);
    //         } else {
    //             return response()->json([
    //                 'success' => false,
    //                 'message' => 'Products data not found!',
    //                 'data'    => [],
    //             ]);
    //         }
    // }
    // public function importProducts(Request $request)
    // {
    //     ini_set('max_execution_time', 500);
    //     $data = ProductUpload::all();
    //     $duplicate = $unique = $emptyName = $emptyPrimaryUnit = $recordsDeleted = 0;

    //     if (count($data) > 0) {

    //         foreach ($data as $key => $record) {
    //             if (!isset($record->product_name) && !isset($record->primary_unit)) {
    //                 $emptyName += 1;
    //                 $emptyPrimaryUnit += 1;
    //                 continue; // Skip this record and move to the next one
    //             }

    //             if (!isset($record->product_name)) {
    //                 $emptyName += 1;
    //                 continue;
    //             }

    //             if (!isset($record->primary_unit)) {
    //                 $emptyPrimaryUnit += 1;
    //                 continue;
    //             }

    //             // If batch is empty, skip this record
    //             if (empty($record->batch)) {
    //                 continue;
    //             }

    //             // Check if there exists a product with the same name and primary unit
    //             $existingProduct = Product::where('name', $record->product_name)
    //                 ->where('primary_unit_id', $this->primaryUnitExists($record->primary_unit))
    //                 ->first();

    //             if (!$existingProduct) {
    //                 // Product doesn't exist, create it
    //                 $tax_id               = $this->taxMasterExists($record->tax);
    //                 $business_division_id = !empty($record->business_division) ? $this->businessDivisionExists($record->business_division) : 1;
    //                 $brand_id             = $this->brandExists($record->brand);
    //                 $group_id             = !empty($record->composition) ? $this->productGroup($record->composition) : 1;
    //                 $category_id          = $this->categoryExists($record->category);
    //                 $primary_unit_id      = $this->primaryUnitExists($record->primary_unit);
    //                 $alternative_unit_id  = $this->alternativeUnitExists($record->alternative_unit, $primary_unit_id, $record->company_id);

    //                 $created_product = Product::create([
    //                     'name'                 => $record->product_name,
    //                     'short_name'           => $record->product_short_name,
    //                     'description'          => $record->product_description,
    //                     'shortcode'            => $record->product_short_code,
    //                     'hsncode'              => $record->product_hsn,
    //                     'item_min_stock_qty'   => $record->min_stock_qty,
    //                     'convertion_factor'    => $record->conversion_factor,
    //                     'brand_id'             => $brand_id,
    //                     'category_id'          => $category_id,
    //                     'primary_unit_id'      => $primary_unit_id,
    //                     'alternative_unit_id'  => $alternative_unit_id,
    //                     'company_id'           => $record->company_id,
    //                     'tax_slab_id'          => $tax_id,
    //                     'manage_stock'         => $record->manage_stock ?? 'yes',
    //                     'business_division_id' => $business_division_id,
    //                     'composition_id'       => $group_id,
    //                     'status'               => '1',
    //                 ]);

    //                 Stock::create([
    //                     'entry_date'            => date('Y-m-d'),
    //                     'product_id'            => $created_product->id,
    //                     'pack_qty_in'           => $record->opening_stock_pack??'',
    //                     'loose_qty_in'          => $record->opening_stock_loose??'',
    //                     'taxable_unit_price'    => $record->sale_price ?? '',
    //                     'created_by_user_id'    => auth()->user()->id,
    //                     'company_id'            => $record->company_id,
    //                     'financial_year_id'     => $this->financialyear(),
    //                     'status'                => '1',
    //                 ]);
    //                 $existingProductPrice = ProductPrice::where('batch', $record->batch)
    //                 ->first();

    //                 if (!$existingProductPrice) {
    //                     $product_price = [
    //                         'mrp'                 => $record->product_mrp ?? '',
    //                         'batch'               => $record->batch ?? 'B001',
    //                         'bar_code'            => $record->barcode ?? '',
    //                         'exp_date'            => $record->exp_date ?? null,
    //                         'mfg_date'            => $record->mfg_date ?? null,
    //                         'mfg_cost'            => $record->mfg_cost ?? '',
    //                         'product_id'          => $created_product->id ?? '',
    //                         'sale_price'          => $record->sale_price ?? '',
    //                         'min_sale_price'      => $record->minimum_sale_price ?? '',
    //                         'purchase_price'      => $record->purchase_price ?? '',
    //                         'wholesale_price'     => $record->wholesale_price ?? '',
    //                         'opening_stock_loose' => $record->opening_stock_loose ?? '0',
    //                         'opening_stock_value' => $record->opening_stock_value ?? '0',
    //                         'opening_stock_pack'  => $record->opening_stock_pack ?? '0',
    //                     ];
    //                     // dd($product_price);
    //                     ProductPrice::create($product_price);
    //                 }

    //                 \DB::table('product_uploads')->where('product_name', $record->product_name)->delete();

    //                 $unique += 1;
    //             }else {
    //                 // Check if there exists a product price with the same batch for the existing product
    //                 $existingProductPrice = ProductPrice::where('product_id', $existingProduct->id)
    //                     ->where('batch', $record->batch)
    //                     ->exists();

    //                 if (!$existingProductPrice) {
    //                     // Product price with the same batch doesn't exist, create it
    //                     $product_price = [
    //                         'mrp'                 => $record->product_mrp ?? '',
    //                         'batch'               => $record->batch ?? '',
    //                         'bar_code'            => $record->barcode ?? '',
    //                         'exp_date'            => $record->exp_date ?? null,
    //                         'mfg_date'            => $record->mfg_date ?? null,
    //                         'mfg_cost'            => $record->mfg_cost ?? '',
    //                         'product_id'          => $existingProduct->id,
    //                         'sale_price'          => $record->sale_price ?? '',
    //                         'min_sale_price'      => $record->minimum_sale_price ?? '',
    //                         'purchase_price'      => $record->purchase_price ?? '',
    //                         'wholesale_price'     => $record->wholesale_price ?? '',
    //                         'opening_stock_loose' => $record->opening_stock_loose ?? '0',
    //                         'opening_stock_value' => $record->opening_stock_value ?? '0',
    //                         'opening_stock_pack'  => $record->opening_stock_pack ?? '0',
    //                     ];
    //                     ProductPrice::create($product_price);

    //                     $unique += 1;
    //                 } else {
    //                     // Duplicate product price, skip this record
    //                     $duplicate += 1;
    //                     $recordsDeleted += 1;
    //                 }
    //             }
    //         }

    //         // Error message handling...

    //         return response()->json([
    //             'success' => true,
    //             'message' => $unique . ' products imported. ' . $duplicate . ' duplicate records skipped.',
    //             'data'    => [
    //                 'inserted'     => $unique,
    //                 'deleted'      => $recordsDeleted,
    //                 'not_inserted' => $emptyName + $emptyPrimaryUnit,
    //             ],
    //         ]);
    //     } else {
    //         return response()->json([
    //             'success' => false,
    //             'message' => 'Products data not found!',
    //             'data'    => [],
    //         ]);
    //     }
    // }
    // public function importProducts(Request $request)
    // {
    //     ini_set('max_execution_time', 500);
    //     $data = ProductUpload::all();
    //     $duplicate = $unique = $emptyName = $emptyPrimaryUnit = $recordsDeleted = 0;
    //     $emptyBatchHandled = [];

    //     if (count($data) > 0) {
    //         foreach ($data as $key => $record) {
    //             if (!isset($record->product_name) && !isset($record->primary_unit)) {
    //                 $emptyName += 1;
    //                 $emptyPrimaryUnit += 1;
    //                 continue; // Skip this record and move to the next one
    //             }

    //             if (!isset($record->product_name)) {
    //                 $emptyName += 1;
    //                 continue;
    //             }

    //             if (!isset($record->primary_unit)) {
    //                 $emptyPrimaryUnit += 1;
    //                 continue;
    //             }

    //             // Check if there exists a product with the same name and primary unit
    //             $existingProduct = Product::where('name', $record->product_name)
    //                 ->where('primary_unit_id', $this->primaryUnitExists($record->primary_unit))
    //                 ->first();

    //             if (!$existingProduct) {
    //                 // Product doesn't exist, create it
    //                 $tax_id               = $this->taxMasterExists($record->tax);
    //                 $business_division_id = !empty($record->business_division) ? $this->businessDivisionExists($record->business_division) : 1;
    //                 $brand_id             = $this->brandExists($record->brand);
    //                 $group_id             = !empty($record->composition) ? $this->productGroup($record->composition) : 1;
    //                 $category_id          = $this->categoryExists($record->category);
    //                 $primary_unit_id      = $this->primaryUnitExists($record->primary_unit);
    //                 $alternative_unit_id  = $this->alternativeUnitExists($record->alternative_unit, $primary_unit_id, $record->company_id);

    //                 $created_product = Product::create([
    //                     'name'                 => $record->product_name,
    //                     'short_name'           => $record->product_short_name,
    //                     'description'          => $record->product_description,
    //                     'shortcode'            => $record->product_short_code,
    //                     'hsncode'              => $record->product_hsn,
    //                     'item_min_stock_qty'   => $record->min_stock_qty,
    //                     'convertion_factor'    => $record->conversion_factor,
    //                     'brand_id'             => $brand_id,
    //                     'category_id'          => $category_id,
    //                     'primary_unit_id'      => $primary_unit_id,
    //                     'alternative_unit_id'  => $alternative_unit_id,
    //                     'company_id'           => $record->company_id,
    //                     'tax_slab_id'          => $tax_id,
    //                     'manage_stock'         => $record->manage_stock ?? 'yes',
    //                     'business_division_id' => $business_division_id,
    //                     'composition_id'       => $group_id,
    //                     'status'               => '1',
    //                 ]);

    //                 Stock::create([
    //                     'entry_date'            => date('Y-m-d'),
    //                     'product_id'            => $created_product->id,
    //                     'pack_qty_in'           => $record->opening_stock_pack ?? '',
    //                     'loose_qty_in'          => $record->opening_stock_loose ?? '',
    //                     'taxable_unit_price'    => $record->sale_price ?? '',
    //                     'created_by_user_id'    => auth()->user()->id,
    //                     'company_id'            => $record->company_id,
    //                     'financial_year_id'     => $this->financialyear(),
    //                     'status'                => '1',
    //                 ]);

    //                 // Handle product price
    //                 $this->handleProductPrice($record, $created_product->id);

    //                 \DB::table('product_uploads')->where('product_name', $record->product_name)->delete();

    //                 $unique += 1;
    //             } else {
    //                 // Check if there exists a product price with the same batch for the existing product
    //                 $existingProductPrice = ProductPrice::where('product_id', $existingProduct->id)
    //                     ->where('batch', $record->batch)
    //                     ->exists();

    //                 if (!$existingProductPrice) {
    //                     // Handle empty batch separately
    //                     if (empty($record->batch)) {
    //                         if (in_array($existingProduct->id, $emptyBatchHandled)) {
    //                             continue; // Skip if empty batch for this product has been handled
    //                         } else {
    //                             $record->batch = 'B001';
    //                             $emptyBatchHandled[] = $existingProduct->id;
    //                         }
    //                     }

    //                     // Handle product price
    //                     $this->handleProductPrice($record, $existingProduct->id);

    //                     $unique += 1;
    //                 } else {
    //                     // Duplicate product price, skip this record
    //                     $duplicate += 1;
    //                     $recordsDeleted += 1;
    //                 }
    //             }
    //         }

    //         // Error message handling...

    //         return response()->json([
    //             'success' => true,
    //             'message' => $unique . ' products imported. ' . $duplicate . ' duplicate records skipped.',
    //             'data'    => [
    //                 'inserted'     => $unique,
    //                 'deleted'      => $recordsDeleted,
    //                 'not_inserted' => $emptyName + $emptyPrimaryUnit,
    //             ],
    //         ]);
    //     } else {
    //         return response()->json([
    //             'success' => false,
    //             'message' => 'Products data not found!',
    //             'data'    => [],
    //         ]);
    //     }
    // }
    //working just B001 is adding in all empty record
    // public function importProducts(Request $request)
    // {
    //     ini_set('max_execution_time', 500);
    //     $data = ProductUpload::all();
    //     $duplicate = $unique = $emptyName = $emptyPrimaryUnit = $recordsDeleted = 0;

    //     if (count($data) > 0) {

    //         foreach ($data as $key => $record) {
    //             if (!isset($record->product_name) && !isset($record->primary_unit)) {
    //                 $emptyName += 1;
    //                 $emptyPrimaryUnit += 1;
    //                 continue; // Skip this record and move to the next one
    //             }

    //             if (!isset($record->product_name)) {
    //                 $emptyName += 1;
    //                 continue;
    //             }

    //             if (!isset($record->primary_unit)) {
    //                 $emptyPrimaryUnit += 1;
    //                 continue;
    //             }

    //             // If batch is empty, set it to "B001"
    //             if (empty($record->batch)) {
    //                 $record->batch = 'B001';
    //             }

    //             // Check if there exists a product with the same name and primary unit
    //             $existingProduct = Product::where('name', $record->product_name)
    //                 ->where('primary_unit_id', $this->primaryUnitExists($record->primary_unit))
    //                 ->first();

    //             if (!$existingProduct) {
    //                 // Product doesn't exist, create it
    //                 $tax_id               = $this->taxMasterExists($record->tax);
    //                 $business_division_id = !empty($record->business_division) ? $this->businessDivisionExists($record->business_division) : 1;
    //                 $brand_id             = $this->brandExists($record->brand);
    //                 $group_id             = !empty($record->composition) ? $this->productGroup($record->composition) : 1;
    //                 $category_id          = $this->categoryExists($record->category);
    //                 $primary_unit_id      = $this->primaryUnitExists($record->primary_unit);
    //                 $alternative_unit_id  = $this->alternativeUnitExists($record->alternative_unit, $primary_unit_id, $record->company_id);

    //                 $created_product = Product::create([
    //                     'name'                 => $record->product_name,
    //                     'short_name'           => $record->product_short_name,
    //                     'description'          => $record->product_description,
    //                     'shortcode'            => $record->product_short_code,
    //                     'hsncode'              => $record->product_hsn,
    //                     'item_min_stock_qty'   => $record->min_stock_qty,
    //                     'convertion_factor'    => $record->conversion_factor,
    //                     'brand_id'             => $brand_id,
    //                     'category_id'          => $category_id,
    //                     'primary_unit_id'      => $primary_unit_id,
    //                     'alternative_unit_id'  => $alternative_unit_id,
    //                     'company_id'           => $record->company_id,
    //                     'tax_slab_id'          => $tax_id,
    //                     'manage_stock'         => $record->manage_stock ?? 'yes',
    //                     'business_division_id' => $business_division_id,
    //                     'composition_id'       => $group_id,
    //                     'status'               => '1',
    //                 ]);

    //                 Stock::create([
    //                     'entry_date'            => date('Y-m-d'),
    //                     'product_id'            => $created_product->id,
    //                     'pack_qty_in'           => $record->opening_stock_pack ?? '',
    //                     'loose_qty_in'          => $record->opening_stock_loose ?? '',
    //                     'taxable_unit_price'    => $record->sale_price ?? '',
    //                     'created_by_user_id'    => auth()->user()->id,
    //                     'company_id'            => $record->company_id,
    //                     'financial_year_id'     => $this->financialyear(),
    //                     'status'                => '1',
    //                 ]);
    //                 $existingProductPrice = ProductPrice::where('batch', $record->batch)
    //                 ->first();

    //                 if (!$existingProductPrice) {
    //                     $product_price = [
    //                         'mrp'                 => $record->product_mrp ?? '',
    //                         'batch'               => $record->batch,
    //                         'bar_code'            => $record->barcode ?? '',
    //                         'exp_date'            => $record->exp_date ?? null,
    //                         'mfg_date'            => $record->mfg_date ?? null,
    //                         'mfg_cost'            => $record->mfg_cost ?? '',
    //                         'product_id'          => $created_product->id ?? '',
    //                         'sale_price'          => $record->sale_price ?? '',
    //                         'min_sale_price'      => $record->minimum_sale_price ?? '',
    //                         'purchase_price'      => $record->purchase_price ?? '',
    //                         'wholesale_price'     => $record->wholesale_price ?? '',
    //                         'opening_stock_loose' => $record->opening_stock_loose ?? '0',
    //                         'opening_stock_value' => $record->opening_stock_value ?? '0',
    //                         'opening_stock_pack'  => $record->opening_stock_pack ?? '0',
    //                     ];
    //                     // dd($product_price);
    //                     ProductPrice::create($product_price);
    //                 }

    //                 \DB::table('product_uploads')->where('product_name', $record->product_name)->delete();

    //                 $unique += 1;
    //             } else {
    //                 // Check if there exists a product price with the same batch for the existing product
    //                 $existingProductPrice = ProductPrice::where('product_id', $existingProduct->id)
    //                     ->where('batch', $record->batch)
    //                     ->exists();

    //                 if (!$existingProductPrice) {
    //                     // Product price with the same batch doesn't exist, create it
    //                     $product_price = [
    //                         'mrp'                 => $record->product_mrp ?? '',
    //                         'batch'               => $record->batch,
    //                         'bar_code'            => $record->barcode ?? '',
    //                         'exp_date'            => $record->exp_date ?? null,
    //                         'mfg_date'            => $record->mfg_date ?? null,
    //                         'mfg_cost'            => $record->mfg_cost ?? '',
    //                         'product_id'          => $existingProduct->id,
    //                         'sale_price'          => $record->sale_price ?? '',
    //                         'min_sale_price'      => $record->minimum_sale_price ?? '',
    //                         'purchase_price'      => $record->purchase_price ?? '',
    //                         'wholesale_price'     => $record->wholesale_price ?? '',
    //                         'opening_stock_loose' => $record->opening_stock_loose ?? '0',
    //                         'opening_stock_value' => $record->opening_stock_value ?? '0',
    //                         'opening_stock_pack'  => $record->opening_stock_pack ?? '0',
    //                     ];
    //                     ProductPrice::create($product_price);

    //                     $unique += 1;
    //                 } else {
    //                     // Duplicate product price, skip this record
    //                     $duplicate += 1;
    //                     $recordsDeleted += 1;
    //                 }
    //             }
    //         }

    //         // Error message handling...

    //         return response()->json([
    //             'success' => true,
    //             'message' => $unique . ' products imported. ' . $duplicate . ' duplicate records skipped.',
    //             'data'    => [
    //                 'inserted'     => $unique,
    //                 'deleted'      => $recordsDeleted,
    //                 'not_inserted' => $emptyName + $emptyPrimaryUnit,
    //             ],
    //         ]);
    //     } else {
    //         return response()->json([
    //             'success' => false,
    //             'message' => 'Products data not found!',
    //             'data'    => [],
    //         ]);
    //     }
    // }
    // public function importProducts(Request $request)
    // {
    //     ini_set('max_execution_time', 500);
    //     $data = ProductUpload::all();
    //     $duplicate = $unique = $emptyName = $emptyPrimaryUnit = $recordsDeleted = 0;

    //     $batchSeriesCounter = 1; // Counter for generating unique batch series (B001, B002, etc.)
    //     $processedEmptyBatches = []; // Track product IDs that have been assigned a batch

    //     if (count($data) > 0) {
    //         foreach ($data as $key => $record) {
    //             if (empty($record->product_name) || empty($record->primary_unit)) {
    //                 if (empty($record->product_name)) {
    //                     $emptyName += 1;
    //                 }
    //                 if (empty($record->primary_unit)) {
    //                     $emptyPrimaryUnit += 1;
    //                 }
    //                 continue;
    //             }

    //             $product_id = $record->product_id ?? 0;

    //             // If batch is empty and the product ID hasn't been processed yet, assign a unique batch series
    //             if (empty($record->batch)) {
    //                 if (!isset($processedEmptyBatches[$product_id])) {
    //                     $record->batch = 'B' . str_pad($batchSeriesCounter, 3, '0', STR_PAD_LEFT);
    //                     $batchSeriesCounter++;
    //                     $processedEmptyBatches[$product_id] = true;
    //                 } else {
    //                     // Skip this record if the batch has already been assigned for this product ID
    //                     continue;
    //                 }
    //             }

    //             $existingProduct = Product::where('name', $record->product_name)
    //                 ->where('primary_unit_id', $this->primaryUnitExists($record->primary_unit))
    //                 ->first();

    //             if (!$existingProduct) {
    //                 $tax_id = $this->taxMasterExists($record->tax);
    //                 $business_division_id = !empty($record->business_division) ? $this->businessDivisionExists($record->business_division) : 1;
    //                 $brand_id = $this->brandExists($record->brand);
    //                 $group_id = !empty($record->composition) ? $this->productGroup($record->composition) : 1;
    //                 $category_id = $this->categoryExists($record->category);
    //                 $primary_unit_id = $this->primaryUnitExists($record->primary_unit);
    //                 $alternative_unit_id = $this->alternativeUnitExists($record->alternative_unit, $primary_unit_id, $record->company_id);

    //                 $created_product = Product::create([
    //                     'name' => $record->product_name,
    //                     'short_name' => $record->product_short_name,
    //                     'description' => $record->product_description,
    //                     'shortcode' => $record->product_short_code,
    //                     'hsncode' => $record->product_hsn,
    //                     'item_min_stock_qty' => $record->min_stock_qty,
    //                     'convertion_factor' => $record->conversion_factor,
    //                     'brand_id' => $brand_id,
    //                     'category_id' => $category_id,
    //                     'primary_unit_id' => $primary_unit_id,
    //                     'alternative_unit_id' => $alternative_unit_id,
    //                     'company_id' => $record->company_id,
    //                     'tax_slab_id' => $tax_id,
    //                     'manage_stock' => $record->manage_stock ?? 'yes',
    //                     'business_division_id' => $business_division_id,
    //                     'composition_id' => $group_id,
    //                     'status' => '1',
    //                 ]);

    //                 Stock::create([
    //                     'entry_date' => date('Y-m-d'),
    //                     'product_id' => $created_product->id,
    //                     'pack_qty_in' => $record->opening_stock_pack ?? '',
    //                     'loose_qty_in' => $record->opening_stock_loose ?? '',
    //                     'taxable_unit_price' => $record->sale_price ?? '',
    //                     'created_by_user_id' => auth()->user()->id,
    //                     'company_id' => $record->company_id,
    //                     'financial_year_id' => $this->financialyear(),
    //                     'status' => '1',
    //                 ]);

    //                 if (!ProductPrice::where('batch', $record->batch)->exists()) {
    //                     $product_price = [
    //                         'mrp' => $record->product_mrp ?? '',
    //                         'batch' => $record->batch,
    //                         'bar_code' => $record->barcode ?? '',
    //                         'exp_date' => $record->exp_date ?? null,
    //                         'mfg_date' => $record->mfg_date ?? null,
    //                         'mfg_cost' => $record->mfg_cost ?? '',
    //                         'product_id' => $created_product->id ?? '',
    //                         'sale_price' => $record->sale_price ?? '',
    //                         'min_sale_price' => $record->minimum_sale_price ?? '',
    //                         'purchase_price' => $record->purchase_price ?? '',
    //                         'wholesale_price' => $record->wholesale_price ?? '',
    //                         'opening_stock_loose' => $record->opening_stock_loose ?? '0',
    //                         'opening_stock_value' => $record->opening_stock_value ?? '0',
    //                         'opening_stock_pack' => $record->opening_stock_pack ?? '0',
    //                     ];
    //                     ProductPrice::create($product_price);
    //                 }

    //                 \DB::table('product_uploads')->where('product_name', $record->product_name)->delete();
    //                 $unique += 1;
    //             } else {
    //                 $existingProductPrice = ProductPrice::where('product_id', $existingProduct->id)
    //                     ->where('batch', $record->batch)
    //                     ->exists();

    //                 if (!$existingProductPrice) {
    //                     $product_price = [
    //                         'mrp' => $record->product_mrp ?? '',
    //                         'batch' => $record->batch,
    //                         'bar_code' => $record->barcode ?? '',
    //                         'exp_date' => $record->exp_date ?? null,
    //                         'mfg_date' => $record->mfg_date ?? null,
    //                         'mfg_cost' => $record->mfg_cost ?? '',
    //                         'product_id' => $existingProduct->id,
    //                         'sale_price' => $record->sale_price ?? '',
    //                         'min_sale_price' => $record->minimum_sale_price ?? '',
    //                         'purchase_price' => $record->purchase_price ?? '',
    //                         'wholesale_price' => $record->wholesale_price ?? '',
    //                         'opening_stock_loose' => $record->opening_stock_loose ?? '0',
    //                         'opening_stock_value' => $record->opening_stock_value ?? '0',
    //                         'opening_stock_pack' => $record->opening_stock_pack ?? '0',
    //                     ];
    //                     ProductPrice::create($product_price);
    //                     $unique += 1;
    //                 } else {
    //                     $duplicate += 1;
    //                     $recordsDeleted += 1;
    //                 }
    //             }
    //         }

    //         return response()->json([
    //             'success' => true,
    //             'message' => $unique . ' products imported. ' . $duplicate . ' duplicate records skipped.',
    //             'data' => [
    //                 'inserted' => $unique,
    //                 'deleted' => $recordsDeleted,
    //                 'not_inserted' => $emptyName + $emptyPrimaryUnit,
    //             ],
    //         ]);
    //     } else {
    //         return response()->json([
    //             'success' => false,
    //             'message' => 'Products data not found!',
    //             'data' => [],
    //         ]);
    //     }
    // }
    public function importProducts(Request $request)
    {
        ini_set('max_execution_time', 500);
        $data      = ProductUpload::all();
        $duplicate = $unique = $emptyName = $emptyPrimaryUnit = $recordsDeleted = $datamissing = 0;

        $batchSeries           = []; // Track the batch series for each product ID
        $processedEmptyBatches = []; // Track product IDs that have been assigned a batch

        if (count($data) > 0) {
            foreach ($data as $key => $record) {
                if (empty($record->product_name)) {
                    if (empty($record->product_name)) {
                        $emptyName += 1;
                    }

                    continue;
                }

                $product_id = $record->product_id ?? 0;

                // If batch is empty, set it to a unique value based on product ID
                if (empty($record->batch)) {
                    if (!isset($batchSeries[$product_id])) {
                        $batchSeries[$product_id] = 1;
                    } else {
                        $batchSeries[$product_id] += 1;
                    }

                    if (!isset($processedEmptyBatches[$product_id]) || $product_id == 0) {
                        $record->batch = 'B' . str_pad($batchSeries[$product_id], 3, '0', STR_PAD_LEFT);
                        $processedEmptyBatches[$product_id] = true;
                    } else {
                        continue; // Skip this record as the batch has already been assigned for this product ID
                    }
                }

                $existingProduct = Product::where('name', $record->product_name)
                    // ->where('primary_unit_id', $this->primaryUnitExists($record->primary_unit))
                    ->first();

                if (!$existingProduct) {
                    $tax_id               = $this->taxMasterExists($record->tax);
                    $business_division_id = !empty($record->business_division) ? $this->businessDivisionExists($record->business_division) : '';
                    $brand_id             = !empty($record->brand) ? $this->brandExists($record->brand) : '';
                    $group_id             = !empty($record->composition) ? $this->productGroup($record->composition) : '';
                    $category_id          = !empty($record->category) ? $this->categoryExists($record->category) : '';
                    $primary_unit_id      = !empty($record->primary_unit) ? $this->primaryUnitExists($record->primary_unit) : '';
                    $alternative_unit_id  = !empty($record->alternative_unit) ? $this->alternativeUnitExists($record->alternative_unit, $primary_unit_id, $record->company_id) : '';
                    $packaging_id         = isset($record->packaging)?$this->packagingExists($record->packaging):'';

                    if (
                        $business_division_id == '' || 
                        $brand_id             == '' || 
                        $group_id             == '' || 
                        $category_id          == '' || 
                        $primary_unit_id      == '' || 
                        $alternative_unit_id  == ''
                    ) {
                        $datamissing += 1;
                        continue;
                    }

                    $created_product = Product::create([
                        'name'                 => $record->product_name,
                        'short_name'           => $record->product_short_name,
                        'description'          => $record->product_description,
                        'shortcode'            => $record->product_short_code,
                        'hsncode'              => $record->product_hsn,
                        'item_min_stock_qty'   => $record->min_stock_qty,
                        'convertion_factor'    => $record->conversion_factor,
                        'brand_id'             => $brand_id,
                        'category_id'          => $category_id,
                        'primary_unit_id'      => $primary_unit_id,
                        'alternative_unit_id'  => $alternative_unit_id,
                        'company_id'           => $record->company_id,
                        'tax_slab_id'          => $tax_id,
                        'manage_stock'         => $record->manage_stock ?? 'yes',
                        'business_division_id' => $business_division_id,
                        'composition_id'       => $group_id,
                        'status'               => '1',
                        'product_packaging_id' => $packaging_id ?? '',
                    ]);

                    if (!ProductPrice::where('batch', $record->batch)->where('product_id', $created_product->id)->exists()) {
                        $product_price = [
                            'mrp'                 => $record->product_mrp ?? '',
                            'batch'               => $record->batch,
                            // 'bar_code'         => $record->barcode ?? '',
                            'exp_date'            => $record->exp_date ?? null,
                            'mfg_date'            => $record->mfg_date ?? null,
                            'mfg_cost'            => $record->mfg_cost ?? '',
                            'product_id'          => $created_product->id ?? '',
                            'sale_price'          => $record->sale_price ?? '',
                            'min_sale_price'      => $record->minimum_sale_price ?? '',
                            'purchase_price'      => $record->purchase_price ?? '',
                            'wholesale_price'     => $record->wholesale_price ?? '',
                            'opening_stock_loose' => $record->opening_stock_loose ?? '0',
                            'opening_stock_value' => $record->opening_stock_value ?? '0',
                            'opening_stock_pack'  => $record->opening_stock_pack ?? '0',
                        ];
                        $ProductPrice =  ProductPrice::create($product_price); 
                    }
                    $product_price_batch_id = isset($ProductPrice->id)?$ProductPrice->id:'';
                   
                    // stockIn($voucher_id='',$voucher_type='',$created_product->id,$record->opening_stock_pack,$record->sale_price,$invoice_batch_id='',$product_price_batch_id,"OPENINGSTOCK");
                    

                    \DB::table('product_uploads')->where('product_name', $record->product_name)->delete();
                    $unique += 1;
                } else {
                    $existingProductBatchPrice = ProductPrice::where('product_id', $existingProduct->id)
                        ->where('batch', $record->batch)
                        ->whereNotNull('batch')
                        ->exists();
                    $existingProductPrice = ProductPrice::where('product_id', $existingProduct->id)
                        ->where('sale_price', $record->sale_price)
                        ->first();

                    if (!$existingProductBatchPrice || empty($record->batch)) {
                        $batch_count = preg_replace('/[^0-9]/', '', $record->batch);
                        $batch       = $record->batch;

                        while (ProductPrice::where('product_id', $existingProduct->id)
                        ->where('batch', $batch)
                        ->exists()) {
                            $batch_count = $batch_count + 1;
                            $batch       = 'B' . str_pad($batch_count, 3, '0', STR_PAD_LEFT);
                        }

                        $product_price = [
                            'mrp'                 => $record->product_mrp ?? '',
                            'batch'               => $record->batch,
                            'bar_code'            => $record->barcode ?? '',
                            'exp_date'            => $record->exp_date ?? null,
                            'mfg_date'            => $record->mfg_date ?? null,
                            'mfg_cost'            => $record->mfg_cost ?? '',
                            'product_id'          => $existingProduct->id,
                            'sale_price'          => $record->sale_price ?? '',
                            // 'min_sale_price'   => $record->minimum_sale_price ?? '',
                            'purchase_price'      => $record->purchase_price ?? '',
                            'wholesale_price'     => $record->wholesale_price ?? '',
                            'opening_stock_loose' => $record->opening_stock_loose ?? '0',
                            'opening_stock_value' => $record->opening_stock_value ?? '0',
                            'opening_stock_pack'  => $record->opening_stock_pack ?? '0',
                        ];
                        ProductPrice::create($product_price);
                        $unique += 1;

                        \DB::table('product_uploads')->where('product_name', $record->product_name)->delete();
                    } else if (!$existingProductPrice) {
                        $batch_count = preg_replace('/[^0-9]/', '', $record->batch);
                        $batch = $record->batch;

                        while (ProductPrice::where('product_id', $existingProduct->id)
                        ->where('batch', $batch)
                        ->exists()) {
                            $batch_count  = $batch_count + 1;
                            $batch = 'B' . str_pad($batch_count, 3, '0', STR_PAD_LEFT);
                        }

                        $product_price = [
                            'mrp'                 => $record->product_mrp ?? '',
                            'batch'               => $batch,
                            // 'bar_code'         => $record->barcode ?? '',
                            'exp_date'            => $record->exp_date ?? null,
                            'mfg_date'            => $record->mfg_date ?? null,
                            'mfg_cost'            => $record->mfg_cost ?? '',
                            'product_id'          => $existingProduct->id,
                            'sale_price'          => $record->sale_price ?? '',
                            // 'min_sale_price'   => $record->minimum_sale_price ?? '',
                            'purchase_price'      => $record->purchase_price ?? '',
                            'wholesale_price'     => $record->wholesale_price ?? '',
                            'opening_stock_loose' => $record->opening_stock_loose ?? '0',
                            'opening_stock_value' => $record->opening_stock_value ?? '0',
                            'opening_stock_pack'  => $record->opening_stock_pack ?? '0',
                        ];
                        ProductPrice::create($product_price);

                        \DB::table('product_uploads')->where('product_name', $record->product_name)->delete();
                    } else {
                        if ($existingProductPrice) {
                            $existingProductPrice->update([
                                'mrp'                 => $record->product_mrp ?? '',
                                'batch'               => $batch,
                                // 'bar_code'         => $record->barcode ?? '',
                                'exp_date'            => $record->exp_date ?? null,
                                'mfg_date'            => $record->mfg_date ?? null,
                                'mfg_cost'            => $record->mfg_cost ?? '',
                                'product_id'          => $existingProduct->id,
                                'sale_price'          => $record->sale_price ?? '',
                                // 'min_sale_price'   => $record->minimum_sale_price ?? '',
                                'purchase_price'      => $record->purchase_price ?? '',
                                'wholesale_price'     => $record->wholesale_price ?? '',
                                'opening_stock_loose' => $record->opening_stock_loose ?? '0',
                                'opening_stock_value' => $record->opening_stock_value ?? '0',
                                'opening_stock_pack'  => $record->opening_stock_pack ?? '0',
                            ]);
                            \DB::table('product_uploads')->where('product_name', $record->product_name)->delete();
                        }

                        $duplicate += 1;
                        $recordsDeleted += 1;
                    }
                }
            }
            $message = $unique > 0 ? $unique.' products imported. ' : '';
            $message .= $duplicate > 0 ? $duplicate.' duplicate records skipped. ' : '';
            $message .= $datamissing > 0 ? $datamissing.' records missing required fields.' : '';

            return response()->json([
                'success' => true,
                'message' => $message,
                'data' => [
                    'inserted'     => $unique,
                    'deleted'      => $recordsDeleted,
                    'not_inserted' => $emptyName + $emptyPrimaryUnit,
                ],
            ]);
        } else {
            return response()->json([
                'success' => false,
                'message' => 'Products data not found!',
                'data' => [],
            ]);
        }
    }
    public function importUpdateOPStockProducts(Request $request)
    {
        ini_set('max_execution_time', 500);
        $data    = ProductUpload::whereNotNull('batch')->get();
        $updated = 0;

        if (count($data) > 0) {
            foreach ($data as $key => $record) {
                if (empty($record->batch) || empty($record->product_name)) {
                    continue;
                }

                $existingProduct = Product::where('name', $record->product_name)->first();

                if (!$existingProduct) {
                    continue;
                } else {
                    $existingProductBatchPrice = ProductPrice::where('product_id', $existingProduct->id)
                        ->where('batch', $record->batch)
                        ->first();

                    if ($existingProductBatchPrice) {
                        $existingProductBatchPrice = $existingProductBatchPrice->update([
                            'mrp'                 => $record->product_mrp ?? '',
                            'batch'               => $record->batch,
                            'bar_code'            => $record->barcode ?? '',
                            'exp_date'            => $record->exp_date ?? null,
                            'mfg_date'            => $record->mfg_date ?? null,
                            'mfg_cost'            => $record->mfg_cost ?? '',
                            'product_id'          => $existingProduct->id,
                            'sale_price'          => $record->sale_price ?? '',
                            'min_sale_price'      => $record->minimum_sale_price ?? '',
                            'purchase_price'      => $record->purchase_price ?? '',
                            'wholesale_price'     => $record->wholesale_price ?? '',
                            'opening_stock_loose' => $record->opening_stock_loose ?? '0',
                            'opening_stock_value' => $record->opening_stock_value ?? '0',
                            'opening_stock_pack'  => $record->opening_stock_pack ?? '0',
                        ]);
                        $updated += 1;

                        \DB::table('product_uploads')->where('product_name', $record->product_name)->delete();
                    } else {
                        continue;
                    }
                }
            }

            return response()->json([
                'success' => true,
                'message' => $updated . ' records updated.',
                'data'    => [
                    'updated' => $updated
                ],
            ]);
        } else {
            return response()->json([
                'success' => false,
                'message' => 'Products data not found!',
                'data' => [],
            ]);
        }
    }
    // Records with empty product_name or primary_unit are skipped.
    // If a batch is empty, assign a unique batch ID based on the product ID.
    // Ensure that duplicate batches are skipped.
    // Ensure that empty records are added once per product ID with a unique batch series.
    // To address your requirement that each product_id should have a unique batch series added only once, and subsequent empty batch records for that product_id should be skipped, we'll make sure to track and assign the batch series correctly. Here's the updated code:
    public function productGroup($group_name)
    {
        $group = productGroup::where('name', $group_name)->first();

        if (!$group) {
            $priority = productGroup::max('priority');
            $priority = $priority != '' ? $priority : 0;

            $group = productGroup::create([
                'name'        => $group_name,
                'code'        => strtoupper(str_replace(' ', '_', $group_name)),
                'description' => '',
                'priority'    => $priority + 1,
                'status'      => '1'
            ]);

            return $group->id;
        }

        return $group->id;
    }

    public function brandExists($brand_name)
    {
        $brand = ProductBrand::where('name', $brand_name)->first();

        if (!$brand) {
            $priority = ProductBrand::max('priority');
            $priority = $priority != '' ? $priority : 0;

            $brand = ProductBrand::create([
                'name'     => $brand_name,
                'type'     => 'Self',
                'priority' => $priority,
                'status'   => '1'
            ]);
            // dd($brand);
            return $brand->id;
        }

        return $brand->id;
    }

    public function categoryExists($category_name)
    {
        $category = ProductCategory::where('name', $category_name)->first();

        if (!$category) {
            $priority = ProductCategory::max('priority');
            $priority = $priority != '' ? $priority : 0;

            $category = ProductCategory::create([
                'name'     => $category_name,
                'priority' => $priority,
                'status'   => '1'
            ]);
            return $category->id;
        }

        return $category->id;
    }

    public function packagingExists($packaging_name)
    {
        $packaging = ProductPackaging::where('name', $packaging_name)->first();

        if (!$packaging) {
            $priority = ProductPackaging::max('priority');
            $priority = $priority != '' ? $priority : 0;

            $packaging = ProductPackaging::create([
                'name'     => $packaging_name,
                'priority' => $priority,
                'status'   => '1'
            ]);
            return $packaging->id;
        }

        return $packaging->id;
    }

    public function primaryUnitExists($primary_unit_name)
    {
        $primary_unit = PrimaryUnit::where('name', $primary_unit_name)->first();

        if (!$primary_unit) {
            $primary_unit = PrimaryUnit::create([
                'name' => $primary_unit_name,
            ]);

            return $primary_unit->id;
        }

        return $primary_unit->id;
    }

    public function alternativeUnitExists($alternative_unit_name, $primary_unit_id, $company_id)
    {
        $alternative_unit = ProductAlternativeUnit::where('name', $alternative_unit_name)->first();

        if (!$alternative_unit) {
            $priority = ProductAlternativeUnit::max('priority');
            $priority = $priority != '' ? $priority : 0;

            $alternative_unit = ProductAlternativeUnit::create([
                'name'            => $alternative_unit_name,
                'primary_unit_id' => $primary_unit_id,
                'company_id'      => $company_id,
                'priority'        => $priority,
                'status'          => '1'
            ]);

            return $alternative_unit->id;
        }

        return $alternative_unit->id;
    }

    public function taxMasterExists($total_percentage)
    {
        $tax_master = TaxMaster::where('total_percentage', 'like',  '%'.$total_percentage.'%')->first();

        return $tax_master->id ?? '';
    }

    public function businessDivisionExists($business_division_name)
    {
        $business_division = BusinessDivision::where('name', 'like',  '%'.$business_division_name.'%')->first();
        // $business_division_by_default = BusinessDivision::where('name', 'like',  '%'.$business_division_name.'%')->where('is_default',1)->first();
        // dd($business_division_by_default);
        return $business_division->id ?? 1;
    }

    public function productPriceList(Request $request)
    {
        $brand              = ProductBrand::select('id','name')->get();
        $product_category   = ProductCategory::select('id','name')->get();
        $product_group      = ProductGroup::select('id','name')->get();
        $selectedBrand      = $request->product_brand ?? '';
        $selectedCategory   = $request->product_category ?? '';
        $selectedGroup      = $request->product_group ?? '';
        $company            = Company::find(Auth::user()->company_id);
        $companies          = Company::get();
        $companyDateFormate = phpToJsDateFormat($this->companyDateFormate());
        $data               = [];
        
        if ($request->ajax()) {
            $data = $this->queryFilter($request);
            

            return view('products.statement-table', compact('brand','product_category','product_group','data', 'companies', 'company','selectedBrand','selectedCategory','selectedGroup','companyDateFormate'));
        }
        else{
            $data = $this->queryFilter($request);
            
        return view('products.price-list-create', compact('brand','product_category','product_group','data', 'companies', 'company','selectedBrand','selectedCategory','selectedGroup' ,  'companyDateFormate'));
        }

    }

    public function productPriceListView()
    {
        $brand            = ProductBrand::select('id','name')->get();
        $product_category = ProductCategory::select('id','name')->get();
        $product_group    = ProductGroup::select('id','name')->get();
        return view('products.products-price-list-view', compact('brand','product_category','product_group'));
    }
    public function priceListExport(Request $request)
    {
        return Excel::download(new PriceListExport(request()), 'Price-list-export.xlsx');
    }
    public function exportProducts(Request $request)
    {
        return Excel::download(new ProductsExport(request()), 'products.xlsx');
    }
    public function importDestroy(Request $request)
    {
        ProductUpload::find($request->id)->delete();

        $Redirect = 'products-import.create';

        return response()->json([
            'success' => true,
            'message' => ['Deleted successfully'],
            'data'    => [
                'redirect' => $Redirect,
            ]
        ]);
    }
    public function clearData()
    {
        ProductUpload::truncate();

        return response()->json([
            'success' => true,
            'message' => 'Temp data cleared successfully.'
        ]);
    }


    public function additionalInfoUpdate(Request $request, $id)
    {
        $validator = \Validator::make($request->all(), [
            // 'additional_info'        => 'required',
        ]);

        if ($validator->fails()) {
            return response()->json(['success'=>false,'message'=>$validator->errors()->all()]);
        }

        $products = Product::find($id)->update(['additional_info' => $request->additional_info]);

        return response()->json([
                'success'=>true,
                'message'=>['Product updated successfully'],
            ]);
    }

    public function searchProducts(Request $request)
    {
        $searchTerm = $request->search_text;
        $results    = Product::select(['id','name'])
                    ->where('name', 'LIKE', '%' . $searchTerm . '%')
                    ->get();
        return response()->json(['result' => $results, 'status' => true]);
    }

    public function editModal(Request $request)
    {
        $productId = $request->product_id;
        $tax_master        = TaxMaster::select(['id','name','default'])->where(['status' => '1'])->get();
        $brands            = ProductBrand::select(['id','name','is_default'])->where(['status' => 1])->get();
        $categories        = ProductCategory::select(['id','name','is_default'])->where(['status' => 1])->get();
        $groups            = ProductGroup::select(['id','name','is_default'])->where(['status' => 1])->get();
        $business_division = BusinessDivision::select(['id','name','is_default'])->where(['status' => 1])->get();
        $product_packaging = ProductPackaging::select(['id','name'])->where(['status' => 1])->get();
        $primary_units     = PrimaryUnit::select(['id','name'])->where(['status' => 1])->whereNull('deleted_at')->get();
        $product           = Product::with('getProductPrice')->find($productId);
        return view('products.modal.edit', compact('tax_master',
                                                    'tax_master',
                                                    'brands',
                                                    'categories',
                                                    'groups',
                                                    'business_division',
                                                    'product_packaging',
                                                    'product',
                                                    'primary_units'));
    }

    public function updateModal(Request $request, $id)
    {
       
        $validator = \Validator::make($request->all(), [
            'name'                  => 'required',
            'tax_slab_id'           => 'required',
            'brand_id'              => 'required',
            'category_id'           => 'required',
            'primary_unit_id'       => 'required',
            'mrp'                   => 'required',
            'business_division_id'  => 'required',
            'batch'                 => 'required',
            'exp_month'             => 'required',
            'exp_year'              => 'required'
        ]);

        if ($validator->fails()) {
            if ($request->ajax()) {
                return response()->json([
                    'success' => false,
                    'message' => $validator->getMessageBag()->first(),
                    'data'    => []
                ]);
            }
            return redirect()->back()->with('error', $validator->getMessageBag()->first());
        }

        $data = $request->except([
            '_token',
            '_method',
            'batch',
            'exp_month',
            'exp_year',
            'mrp',
            'sale_price',
            'purchase_price',
            'product_price_id'
        ]);

        $products = Product::find($id);

        if (!$products) {
            return response()->json([
                    'success' => false,
                    'message' => 'Product not found!',
                    'data'    => []
                ]);
        }
        $data['company_id'] = \Auth::user()->company_id ?? 1;
        $products->update($data);

        $month            = $request->exp_month;
        $year             = $request->exp_year;
        $exp_date         = '';
        if($month && $year)
        {
            $dates    = Carbon::create($year, $month, 1);
            $exp_date = $dates->endOfMonth()->format('Y-m-d');
        }

        if(isset($request->product_price_id) && $request->product_price_id!='')
        {
            $ProductPrice = ProductPrice::find($request->product_price_id);
            $ProductPrice->update([ 'mrp'           => $request->mrp,
                                    'sale_price'    => $request->sale_price,
                                    'purchase_price'=> $request->purchase_price,
                                    'batch'         => strtoupper(trim($request->batch)),
                                    'exp_date'      => $exp_date
                                ]);
        }
        else
        {
            ProductPrice::create([  'mrp'           => $request->mrp,
                                    'sale_price'    => $request->sale_price,
                                    'purchase_price'=> $request->purchase_price,
                                    'batch'         => strtoupper(trim($request->batch)),
                                    'exp_date'      => $exp_date,
                                    'product_id'    => $products->id
                                ]);
        }

        if ($request->ajax()) {
            return response()->json([
                'success' => true,
                'message' => 'Product updated successfully',
                'data'    => ['name'=> $products->name,
                              'id'  => $products->id]
            ]);
        }
    }


    public function createModal(Request $request)
    {
        $tax_master        = TaxMaster::select(['id','name','default'])->where(['status' => '1'])->get();
        $brands            = ProductBrand::select(['id','name','is_default'])->where(['status' => 1])->get();
        $categories        = ProductCategory::select(['id','name','is_default'])->where(['status' => 1])->get();
        $groups            = ProductGroup::select(['id','name','is_default'])->where(['status' => 1])->get();
        $business_division = BusinessDivision::select(['id','name','is_default'])->where(['status' => 1])->get();
        $product_packaging = ProductPackaging::select(['id','name'])->where(['status' => 1])->get();
        $primary_units     = PrimaryUnit::select(['id','name','is_default'])->where(['status' => 1])->whereNull('deleted_at')->get();
        return view('products.modal.create', compact('tax_master',
                                                    'tax_master',
                                                    'brands',
                                                    'categories',
                                                    'groups',
                                                    'business_division',
                                                    'product_packaging',
                                                    'primary_units'));
    }

    public function StoreModal(Request $request)
    {
       
        $validator = \Validator::make($request->all(), [
            'name'                  => 'required|unique:products,name',
            'tax_slab_id'           => 'required',
            'brand_id'              => 'required',
            'category_id'           => 'required',
            'primary_unit_id'       => 'required',
            'mrp'                   => 'required',
            'business_division_id'  => 'required',
            'batch'                 => 'required',
            'exp_month'             => 'required',
            'exp_year'              => 'required'
        ]);

        if ($validator->fails()) {
            if ($request->ajax()) {
                return response()->json([
                    'success' => false,
                    'message' => $validator->getMessageBag()->first(),
                    'data'    => []
                ]);
            }
            return redirect()->back()->with('error', $validator->getMessageBag()->first());
        }

        $data = $request->except([
            '_token',
            '_method',
            'batch',
            'exp_month',
            'exp_year',
            'mrp',
            'sale_price',
            'purchase_price',
            'product_price_id'
        ]);
        $data['company_id'] = \Auth::user()->company_id ?? 1;
        $products = Product::create($data);


        $month            = $request->exp_month;
        $year             = $request->exp_year;
        $exp_date         = '';
        if($month && $year)
        {
            $dates = Carbon::create($year, $month, 1);
            $exp_date = $dates->endOfMonth()->format('Y-m-d');
        }
       
            ProductPrice::create([  'mrp'           => $request->mrp,
                                    'sale_price'    => $request->sale_price,
                                    'purchase_price'=> $request->purchase_price,
                                    'batch'         => strtoupper(trim($request->batch)),
                                    'exp_date'      => $exp_date,
                                    'product_id'    => $products->id
                                ]);

        if ($request->ajax()) {
            return response()->json([
                'success' => true,
                'message' => 'Product updated successfully',
                'data'    => [
                        'name'=> $products->name,
                        'id'  => $products->id
                ]
            ]);
        }
    }

    public function searchStockModal(Request $request)
    {
        return view('products.modal.stock-search-form');
    }
    public function searchStock(Request $request)
    {
        $searchProduct = $request->search_product;
        $productStockData = Product::select(
                                ['product_prices.id as price_id',
                                'product_prices.batch',
                                'product_prices.exp_date',
                                'products.name','products.id',
                                'products.item_min_stock_qty',
                                'product_prices.mrp',
                                'product_shortlists.id as product_short_list_id',])
                ->leftJoin('product_prices','product_prices.product_id','products.id')
                ->leftJoin('business_divisions', 'business_divisions.id', 'products.business_division_id')
                ->leftJoin('product_shortlists','product_shortlists.product_id','products.id')
                ->where('business_divisions.code', 'PHARMACY')
                ->where(function ($query) use ($searchProduct) {
                    $query->where('products.name', 'LIKE', $searchProduct . "%")
                    ->orWhere('products.hsncode', 'LIKE', $searchProduct . "%")
                    ->orWhere('products.shortcode', 'LIKE', $searchProduct . "%")
                    ->orWhere('products.product_code', 'LIKE', $searchProduct . "%");
                })
                ->groupBy('product_prices.product_id')
                ->groupBy('product_shortlists.product_id')
                ->get();
                
        return view('products.modal.stock-table',compact('productStockData'));
    }

    public function queryFilter(Request $request){

        $data = 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');

        if ($request->product_brand && $request->product_brand != 'all') {
            $data = $data->where('products.brand_id', $request->product_brand);
        }
        if ($request->product_category && $request->product_category != 'all') {
            $data = $data->where('products.category_id', $request->product_category);
        }
        if($request->product_group!=''){
            $data = $data->where('products.composition_id',$request->product_group);
        }
        $data = $data->orderBy('products.id', 'asc')->get();
        return $data;
    }
}
