<?php

namespace App\Http\Controllers\POS;

use App\Http\Controllers\Controller;
use Illuminate\Http\Request;
use Carbon\Carbon;
use App\Models\sale;
use App\Models\saleProduct;
use App\Models\product;
use App\Models\saleItem;
use App\Models\People;
use App\Models\purchaseProductItem;
use App\Models\purchaseProductsBadgstock;
use App\Models\ToDoList;
use App\Models\salesPayment;
use App\Models\productCategory;
use App\Models\saleReturn;
use App\Models\purchaseProduct;

use Illuminate\Support\Facades\DB;
class saleController extends Controller
{

    
    public function saleList()
    {   
        $ToDoList = ToDoList::get();
        return view('Section.POS.saleList',compact('ToDoList'));
      
    }



    public function saleData(Request $request)
    {   
        $data = $request->all();
        $id =$data['id'];
       
        $products = product::get();
        $ToDoList = ToDoList::get();


        $sale = sale::leftJoin('people','sales.customer_id','=','people.id')
        ->where('sales.id', $id)->select('sales.*','people.name')->first();

        $saleProduct = saleProduct::leftJoin('products','sale_products.product_id','=','products.id')
        ->leftJoin('sales','sale_products.sale_id','=','sales.id')
        ->where('sale_products.sale_id', $id)
        ->where('sale_products.type', 'product')
        ->select('sale_products.*','products.name as product_name','sales.date')->get();
        

        $saleItem = saleItem::leftJoin('sale_products','sale_items.sale_product_id','=','sale_products.id')
        ->leftJoin('purchase_product_items','sale_items.item_id','=','purchase_product_items.id')
        ->leftJoin('products','purchase_product_items.product_id','=','products.id')
        ->leftJoin('sales','sale_items.sale_id','=','sales.id')
        // ->where('sale_products.type', 'product item')
        ->where('sale_items.sale_id', $id)
        ->select('sale_items.*','products.name as product_name','sales.date')->get();

        // dd(vars: $saleItem);
       
        // $suppliers = People::where('type', 'Supplier')->get();
        $customer = People::where('id', $sale->customer_id)->first();
        // if($purchaseType == 'Add Product'){
        return view('Section.POS.Sale.saleList',compact('ToDoList','saleItem' ,'customer','saleProduct','sale'));
        // }
       
      
    }
    
    public function editSaleData(Request $request)
    {

        $productCategories = productCategory::get();

        $products = product::get();
        $ToDoList = ToDoList::get();

        $data = $request->all();
        $id =$data['id'];
        $sale = sale::leftJoin('people','sales.customer_id','=','people.id')
        ->where('sales.id', $id)->select('sales.*','people.name')->first();

        $saleProduct = saleProduct::leftJoin('products', 'sale_products.product_id', '=', 'products.id')
        ->leftJoin('sales', 'sale_products.sale_id', '=', 'sales.id')
        ->leftJoin('purchase_products_badgstock', 'sale_products.badgstock_id', '=', 'purchase_products_badgstock.id')
        ->leftJoin('sale_items', 'sale_items.sale_product_id', '=', 'sale_products.id')
        ->leftJoin('purchase_product_items', 'sale_items.item_id', '=', 'purchase_product_items.id')
        ->where('sale_products.sale_id', $id)
        ->selectRaw("
          CASE 
                WHEN sale_products.type = 'product' THEN (purchase_products_badgstock.quantity -  purchase_products_badgstock.sale_quantity) + sale_products.quantity
                ELSE (sale_items.quantity + purchase_product_items.quantity) 
            END as existing_quantity,
             CASE 
                WHEN sale_products.type = 'product' THEN sale_products.warranty_status 
                ELSE sale_items.warranty_status 
            END as warranty_status,
             CASE 
                WHEN sale_products.type = 'product' THEN sale_products.warranty_period 
                ELSE sale_items.warranty_period 
            END as warranty_period,
            CASE 
            WHEN sale_products.type = 'product' THEN CAST(sale_products.quantity AS SIGNED)
            ELSE CAST(sale_items.quantity AS SIGNED)
        END as quantity,
                CASE 
                WHEN sale_products.type = 'product' THEN products.code 
                ELSE sale_items.imu_number 
            END as code,
            CASE 
                WHEN sale_products.type = 'product' THEN sale_products.price 
                ELSE sale_items.price 
            END as price,
            products.name as product_name,
            sales.date,
            sales.id as saleId,
            products.code as barcode,
            products.id  as productID,
            sale_products.cost,
            sale_products.type,
            sale_products.id as saleProductsId,
            sale_items.item_id as itemId,
            sale_products.badgstock_id 
        ")
        ->get();
    
       

        $customer = People::where('id', $sale->customer_id)->first();

        $Costomers = People::get();

      

        return view('Section.POS.Sale.saleDataEdit',compact('ToDoList','Costomers','productCategories' ,'customer','saleProduct','sale'));
    
    }


    public function returnSaleData(Request $request)
    {

        $productCategories = productCategory::get();

        $products = product::get();
        $ToDoList = ToDoList::get();

        $data = $request->all();
        $id =$data['id'];
        $sale = sale::leftJoin('people','sales.customer_id','=','people.id')
        ->where('sales.id', $id)->select('sales.*','people.name')->first();

       

        $saleProduct = saleProduct::leftJoin('products', 'sale_products.product_id', '=', 'products.id')
        ->leftJoin('sales', 'sale_products.sale_id', '=', 'sales.id')
        ->leftJoin('purchase_products_badgstock', 'sale_products.badgstock_id', '=', 'purchase_products_badgstock.id')
        ->leftJoin('sale_items', 'sale_items.sale_product_id', '=', 'sale_products.id')
        ->leftJoin('purchase_product_items', 'sale_items.item_id', '=', 'purchase_product_items.id')
        ->where('sale_products.sale_id', operator: $id)
        ->selectRaw("
          CASE 
                WHEN sale_products.type = 'product' THEN (purchase_products_badgstock.quantity -  purchase_products_badgstock.sale_quantity) + sale_products.quantity
                WHEN sale_products.type = 'product item' THEN (sale_items.quantity + purchase_product_items.quantity)
            END as existing_quantity,
             CASE 
                WHEN sale_products.type = 'product' THEN sale_products.warranty_status 
                WHEN sale_products.type = 'product item' THEN sale_items.warranty_status 
            END as warranty_status,
             CASE 
                WHEN sale_products.type = 'product' THEN sale_products.warranty_period 
                ELSE sale_items.warranty_period 
            END as warranty_period,
            CASE 
            WHEN sale_products.type = 'product' THEN CAST(sale_products.quantity AS SIGNED)
            WHEN sale_products.type = 'product item' THEN CAST(sale_items.quantity AS SIGNED)
            END as quantity,
                CASE 
                WHEN sale_products.type = 'product' THEN products.code 
                ELSE sale_items.imu_number 
            END as code,
            CASE 
                WHEN sale_products.type = 'product' THEN sale_products.price 
                ELSE sale_items.price 
            END as price,
            products.name as product_name,
            sales.date,
            sales.id as saleId,
            products.code as barcode,
            products.id  as productID,
            sale_products.cost,
            sale_products.type,
            sale_products.id as saleProductsId,
            sale_items.item_id as itemId,
            sale_products.badgstock_id 
        ")
        ->get();
    
        // $products = saleProduct::leftJoin('products', 'sale_products.product_id', '=', 'products.id')
        // ->leftJoin('sales', 'sale_products.sale_id', '=', 'sales.id')
        // ->leftJoin('purchase_products_badgstock', 'sale_products.badgstock_id', '=', 'purchase_products_badgstock.id')
        // ->where('sale_products.sale_id', $id)
        // ->where('sale_products.type', 'product')
        // ->select(
        //     'sales.date',
        //     'sales.id as saleId',
        //     'products.code as barcode',
        //     'products.id as productID',
        //     'sale_products.cost',
        //     'sale_products.type',
        //     'sale_products.id as saleProductsId',
        //     DB::raw('NULL as itemId'),
        //     'sale_products.badgstock_id'
        // );
    
    // $productItems = saleItem::leftJoin('sale_products', 'sale_items.sale_product_id', '=', 'sale_products.id')
    //     ->leftJoin('sales', 'sale_products.sale_id', '=', 'sales.id')
    //     ->leftJoin('purchase_product_items', 'sale_items.item_id', '=', 'purchase_product_items.id')
    //     ->leftJoin('products', 'sale_products.product_id', '=', 'products.id')
    //     ->where('sale_products.sale_id', $id)
    //     ->select(
    //         'sales.date',
    //         'sales.id as saleId',
    //         'sale_items.imu_number as barcode',
    //         'products.id as productID',
    //         'sale_items.cost',
    //         'sale_products.type',
    //         'sale_products.id as saleProductsId',
    //         'sale_items.item_id as itemId', // ✅ Fixed here
    //         'sale_products.badgstock_id'
    //     )->get();
    
    // $saleProduct = $products->unionAll($productItems)->get();
    

     
        $customer = People::where('id', $sale->customer_id)->first();

        $Costomers = People::get();

        // dd($saleProduct);

        return view('Section.POS.Sale.saleDataReturn',compact('ToDoList','Costomers','productCategories' ,'customer','saleProduct','sale'));
    
    }
    

    public function storeSale(Request $request)
    {   
        $data = $request->all();
        // dd($data);
        
        $sale = sale::create([
            'sale_reference' => $data['sale_reference'],
            'sale_type' => $data['orderType'],
            'nick_name' => $data['draftOrderName'] ?? 'customer',
            'date' => Carbon::now()->toDateString(),
            'customer_id' => $data['customer'],
            'branch_id' => $data['branchId'],
            'total_price' => $data['finaltotalSum'],
            'total_cost' => $data['finaltotalCostSum'],
            'quantity' => $data['finaltotalQuantitySum'],
            'discount_amount' => $data['discountAmount'],
            'discount_prasentage' => $data['discount'],
            'service_charge' => $data['ServiceChanges'],
            'tax_charge' => $data['taxChanges'],
            'other_charge' => $data['otherChanges'],
            'total_other_charge' => $data['ifAnyCharges'],
            'other_charge_description' => $data['sale_reference'],
            'final_total_price' => $data['totalAmountAfterDiscount'],
            'received_amount' => $data['ReceivedAmount'],
            'outstanding_amount' => $data['outstandingAmount'],
        ]);
    

        if($data['ReceivedAmount'] > 0){
            salesPayment::create([
                    'sale_id' => $sale->id,
                    'date' => Carbon::now()->toDateString(),
                    'amount' => $data['ReceivedAmount'],
                    'method' => $data['PaymentMethods'],
                    'voucher' => $data['voucher'],
                    'payment_note' => $data['PaymentNote'],
                    'sale_note' => $data['SaleNote'],
                    'staff_note' => $data['StaffNote'],
            ]);
        }


        $mortgageData = isset($data['mortgageData']) ? $data['mortgageData'] : [];
    
        $groupedData = [];
        $saleProductMap = [];
    
        // Grouping and updating stock/product quantities
        foreach ($mortgageData as $rowData) {
            $purchaseProductsBadgstock = PurchaseProductsBadgstock::where('id', $rowData['badgstock_id'])
                ->where('product_id', $rowData['productId'])
                ->first();
    
            if ($purchaseProductsBadgstock) {
                $purchaseProductsBadgstock->sale_quantity += $rowData['quantity'];
                $purchaseProductsBadgstock->save();
            }
    
            $product = product::where('id', $rowData['productId'])->first();
            if ($product) {
                $product->sale_quantity += $rowData['quantity'];
                $product->save();
            }
    
            $key = $rowData['sale_reference'] . '-' . $rowData['productId'] . '-' . $rowData['badgstock_id'] . '-' . $rowData['itemCost'] . '-' . $rowData['price'];
    
            if (!isset($groupedData[$key])) {
                $groupedData[$key] = [
                    'productId' => $rowData['productId'],
                    'badgstock_id' => $rowData['badgstock_id'],
                    'itemCost' => $rowData['itemCost'],
                    'type'=> $rowData['type'],
                    'price' => $rowData['price'],
                    'quantity' => $rowData['quantity'],
                    'warrantyStatus' => $rowData['warrantyStatus'],
                    'Warrantyperiod' => $rowData['Warrantyperiod']
                ];
            } else {
                $groupedData[$key]['quantity'] += $rowData['quantity'];
            }
        }
    
        // Create saleProducts and store in map
        foreach ($groupedData as $key => $rowData) {
            $saleProduct = saleProduct::create([
                'product_id' => $rowData['productId'],
                'sale_id' => $sale->id,
                'badgstock_id' => $rowData['badgstock_id'],
                'type'=> $rowData['type'],
                'quantity' => $rowData['quantity'],
                'cost' => $rowData['itemCost'],
                'price' => $rowData['price'],
                'profit' => $rowData['price'] - $rowData['itemCost'],
                'warranty_status' => $rowData['warrantyStatus'],
                'warranty_period' => $rowData['Warrantyperiod'],
            ]);
    
            $saleProductMap[$key] = $saleProduct->id;
        }
    
        // Create saleItems with correct sale_product_id
        foreach ($mortgageData as $rowData) {
            if ($rowData['type'] == 'product item') {
                $key = $rowData['sale_reference'] . '-' . $rowData['productId'] . '-' . $rowData['badgstock_id'] . '-' . $rowData['itemCost'] . '-' . $rowData['price'];
    
                if (!isset($saleProductMap[$key])) {
                    continue;
                }
    
                $saleItem = saleItem::create([
                    'sale_product_id' => $saleProductMap[$key],
                    'sale_id' => $sale->id,
                    'item_id' => $rowData['itemId'],
                    'cost' => $rowData['itemCost'],
                    'price' => $rowData['price'],
                    'quantity' => $rowData['quantity'],
                    'imu_number' => $rowData['itemBarcode'],
                    'warranty_status' => $rowData['warrantyStatus'],
                    'warranty_period' => $rowData['Warrantyperiod'],
                ]);
    
                $purchaseProductItem = purchaseProductItem::where('id', $rowData['itemId'])->first();
                if ($purchaseProductItem) {
                    $purchaseProductItem->quantity -= $rowData['quantity'];
                    $purchaseProductItem->save();
                }
            }
        }
    }
    

 public function storePayment(Request $request)
{
    $data = $request->all();

    if ($data['receivedAmount'] > 0) {
        // Save the payment
        salesPayment::create([
            'sale_id'       => $data['sale_id'],
            'date'          => $data['date'],
            'amount'        => $data['receivedAmount'],
            'method'        => $data['PaymentMethods'],
            'payment_note'  => $data['PaymentNote'],
            'sale_note'     => $data['SaleNote'],
            'staff_note'    => $data['StaffNote'],
        ]);

        // Update the sale record
        $sale = sale::find($data['sale_id']);

        if ($sale) {
            // Increment the received amount
            $sale->received_amount += $data['receivedAmount'];

            // Recalculate the outstanding amount
            $sale->outstanding_amount  -= $data['outstandingAmount'];

            $sale->save();
        }
    }
}
    

    // public function storeSale(Request $request)
    // {   
    //     $data = $request->all();
       

    //     $sale =  sale::create([
    //         'sale_reference' => $data['sale_reference'],
    //         'sale_type' => $data['orderType'],
    //         'nick_name' => $data['draftOrderName'] ?? 'customer',
    //         'date' => Carbon::now()->toDateString(),
    //         'customer_id' => $data['customer'],
    //         'branch_id' => $data['branchId'],
    //         'total_price' => $data['finaltotalSum'],
    //         'total_cost' => $data['finaltotalCostSum'],
    //         'quantity' => $data['finaltotalQuantitySum'],
    //         'discount_amount' => $data['discountAmount'],
    //         'discount_prasentage' => $data['discount'],
    //         'service_charge' => $data['ServiceChanges'],
    //         'tax_charge' => $data['taxChanges'],
    //         'other_charge' => $data['otherChanges'],
    //         'total_other_charge' => $data['ifAnyCharges'],
    //         'other_charge_description' => $data['sale_reference'],
    //         'final_total_price' => $data['totalAmountAfterDiscount'],
    //         'received_amount' => $data['ReceivedAmount'],
    //         'outstanding_amount' => $data['outstandingAmount'],

            

    //     ]);
    //     $mortgageData = isset($data['mortgageData']) ? $data['mortgageData'] : [];

    //     $groupedData = [];

    //     foreach ($mortgageData as $rowData) {


    //         $purchaseProductsBadgstock = PurchaseProductsBadgstock::where('id', $rowData['badgstock_id'])
    //         ->where('product_id', $rowData['productId']) // Corrected column name
    //         ->first(); // Use first() instead of get() to get a single record

    //         if ($purchaseProductsBadgstock) {
    //             $purchaseProductsBadgstock->sale_quantity += $rowData['quantity'];
    //             $purchaseProductsBadgstock->save(); // Save the updated value
    //         }

    //         $product = product::where('id', $rowData['productId'])->first(); // Use first() instead of get() to get a single record

    //         if ($product) {
    //             $product->sale_quantity += $rowData['quantity'];
    //             $product->save(); // Save the updated value
    //         }

    //         $key = $rowData['sale_reference'] . '-' . $rowData['productId'] . '-' . $rowData['badgstock_id'] . '-' . $rowData['itemCost'] . '-' . $rowData['price'];
        
    //         if (!isset($groupedData[$key])) {
    //             $groupedData[$key] = [
    //                 'productId' => $rowData['productId'],
    //                 'badgstock_id' => $rowData['badgstock_id'],
    //                 'itemCost' => $rowData['itemCost'],
    //                 'type'=> $rowData['type'],
    //                 'price' => $rowData['price'],
    //                 'quantity' => $rowData['quantity'],
    //                 'warrantyStatus' => $rowData['warrantyStatus'],
    //                 'Warrantyperiod' => $rowData['Warrantyperiod']
    //             ];
    //         } else {
    //             // Sum up the quantity for similar products
    //             $groupedData[$key]['quantity'] += $rowData['quantity'];
    //         }

            
    //     }
        
    //     // Insert grouped data into saleProduct table
    //     foreach ($groupedData as $rowData) {
    //         $saleProduct = saleProduct::create([
    //             'product_id' => $rowData['productId'],
    //             'sale_id' => $sale->id,
    //             'badgstock_id' => $rowData['badgstock_id'],
    //             'type'=> $rowData['type'],
    //             'quantity' => $rowData['quantity'], // Insert total summed quantity
    //             'cost' => $rowData['itemCost'],
    //             'price' => $rowData['price'],
    //             'profit' => $rowData['price'] - $rowData['itemCost'],
    //             'warranty_status' => $rowData['warrantyStatus'],
    //             'warranty_period' => $rowData['Warrantyperiod'],
    //         ]);
       
    //     }
    //     foreach ($mortgageData as $rowData) {
    //     if($rowData['type']=='product item'){
    //         $saleItem = saleItem::create([
    //             'sale_product_id' => $saleProduct->id,
    //             'sale_id' => $sale->id,
    //             'item_id' => $rowData['itemId'],
    //             'cost' => $rowData['itemCost'],
    //             'price' => $rowData['price'],
    //             'quantity' => $rowData['quantity'],
    //             'imu_number' => $rowData['itemBarcode'], // Insert total summed quantity
    //             'warranty_status' => $rowData['warrantyStatus'],
    //             'warranty_period' => $rowData['Warrantyperiod'],
    //         ]);
        

    //         $purchaseProductItem = purchaseProductItem::where('id', $rowData['itemId'])->first();
    //         if ($purchaseProductItem) {
    //             $purchaseProductItem->quantity -= $rowData['quantity'];
    //             $purchaseProductItem->save(); // Save the updated value
    //         }
    //     }
    // }
    // }

    public function listSale(Request $request)
    {   
      
        $sale = sale::leftJoin('people', 'sales.customer_id', '=', 'people.id')
        ->select(
            'sales.id',
            'sales.date',
            'sales.sale_reference',
            'sales.final_total_price',
             'sales.outstanding_amount',
             'sales.received_amount',
            'sales.total_cost',
            'sales.quantity',
            'sales.received_amount',
            'people.name',
        )
        ->get();
  
        $response['data'] = $sale;
        return response()->json($response);

    }

public function listSaleByDate($date)
    {   
      
        $sale = sale::leftJoin('people', 'sales.customer_id', '=', 'people.id')
        ->whereDate('sales.date', $date) 
        ->select(
            'sales.id',
            'sales.date',
            'sales.sale_reference',
            'sales.final_total_price',
             'sales.outstanding_amount',
             'sales.received_amount',
            'sales.total_cost',
            'sales.quantity',
            'sales.received_amount',
            'people.name',
        )
        ->get();
  
        $response['data'] = $sale;
        return response()->json($response);

    }
    


    // public function updateSale(Request $request)
    // {   
    //     $data = $request->all();
    //     $saleId =   $data['saleId'];
    //     $sale = Sale::find($saleId);

    //     if ($sale) {
    //         // Update the sale record
    //         $sale->update([
    //             'sale_reference' => $data['sale_reference'],
    //             'customer_id' => $data['customer'],
    //             'branch_id' => $data['branchId'],
    //             'total_price' => $data['finaltotalSum'],
    //             'total_cost' => $data['finaltotalCostSum'],
    //             'quantity' => $data['finaltotalQuantitySum'],
    //             'discount_amount' => $data['discountAmount'],
    //             'discount_prasentage' => $data['discount'],
    //             'service_charge' => $data['ServiceChanges'],
    //             'tax_charge' => $data['taxChanges'],
    //             'other_charge' => $data['otherChanges'],
    //             'total_other_charge' => $data['ifAnyCharges'],
    //             'other_charge_description' => $data['sale_reference'],
    //             'final_total_price' => $data['totalAmountAfterDiscount'],
    //             'received_amount' => $data['ReceivedAmount'],
    //             'outstanding_amount' => $data['outstandingAmount'],
    //         ]);
    //     }

    //     $mortgageData = isset($data['mortgageData']) ? $data['mortgageData'] : [];
    //     $groupedData = [];
        
    //     // Grouping mortgageData by unique key
    //     foreach ($mortgageData as $rowData) {
    //         $key = $rowData['sale_reference'] . '-' . $rowData['productId'] . '-' . $rowData['badgstock_id'] . '-' . $rowData['itemCost'] ;
    //         // $key = $rowData['sale_reference'] . '-' . $rowData['productId'] . '-' . $rowData['badgstock_id'] . '-' . $rowData['itemCost'] . '-' . $rowData['price'];
    //         if (!isset($groupedData[$key])) {
    //             $groupedData[$key] = [
    //                 'productId' => $rowData['productId'],
    //                 'badgstock_id' => $rowData['badgstock_id'],
    //                 'itemCost' => $rowData['itemCost'],
    //                 'type' => $rowData['type'],
    //                 'price' => $rowData['price'],
    //                 'quantity' => $rowData['quantity'],
    //                 'warrantyStatus' => $rowData['warrantyStatus'],
    //                 'Warrantyperiod' => $rowData['Warrantyperiod']
    //             ];
    //         } else {
    //             // Sum up the quantity for similar products
    //             $groupedData[$key]['quantity'] += $rowData['quantity'];
    //         }
    //     }
        

    //     // dd($groupedData);
    //     // Insert or Update saleProduct table
    //     foreach ($groupedData as $rowData) {
    //         // Check if the record exists
    //         $existingSaleProduct = SaleProduct::where('sale_id', $data['saleId'])
    //             ->where('product_id', $rowData['productId'])
    //             ->where('badgstock_id', $rowData['badgstock_id'])
    //             ->first();
        
               
    //         if ($existingSaleProduct) {
    //             // Get previous quantity before updating
    //             $previousQuantity = $existingSaleProduct->quantity;
    //             $newQuantity =   $rowData['quantity'] - $previousQuantity ;


    //             $purchaseProductsBadgstock = PurchaseProductsBadgstock::where('id', $rowData['badgstock_id'])
    //             ->where('product_id', $rowData['productId']) // Corrected column name
    //             ->first(); // Use first() instead of get() to get a single record
    
    //             if ($purchaseProductsBadgstock) {
    //                 $purchaseProductsBadgstock->sale_quantity -= $previousQuantity ;
    //                 $purchaseProductsBadgstock->sale_quantity += $rowData['quantity'] ;
    //                 $purchaseProductsBadgstock->save(); // Save the updated value
    //             }
    
    //             $product = product::where('id', $rowData['productId'])->first(); // Use first() instead of get() to get a single record
    
    //             if ($product) {
    //                 $product->sale_quantity  -= $previousQuantity ;
    //                 $product->sale_quantity += $rowData['quantity'];
    //                 $product->save(); // Save the updated value
    //             }
    
                

    //             $saleProduct = $existingSaleProduct->update([
    //             'type' => $rowData['type'],
    //             'quantity' => $rowData['quantity'], // Update total summed quantity
    //             'cost' => $rowData['itemCost'],
    //             'price' => $rowData['price'],
    //             'profit' => $rowData['price'] - $rowData['itemCost'],
    //             'warranty_status' => $rowData['warrantyStatus'],
    //             'warranty_period' => $rowData['Warrantyperiod'],
    //             ]);


    //         } else {
    //             // Create a new record
    //             $saleProduct = SaleProduct::create([
    //                 'product_id' => $rowData['productId'],
    //                 'sale_id' => $data['saleId'],
    //                 'badgstock_id' => $rowData['badgstock_id'],
    //                 'type' => $rowData['type'],
    //                 'quantity' => $rowData['quantity'], // Insert total summed quantity
    //                 'cost' => $rowData['itemCost'],
    //                 'price' => $rowData['price'],
    //                 'profit' => $rowData['price'] - $rowData['itemCost'],
    //                 'warranty_status' => $rowData['warrantyStatus'],
    //                 'warranty_period' => $rowData['Warrantyperiod'],
    //             ]);


    //             $purchaseProductsBadgstock = PurchaseProductsBadgstock::where('id', $rowData['badgstock_id'])
    //             ->where('product_id', $rowData['productId']) // Corrected column name
    //             ->first(); // Use first() instead of get() to get a single record
    
    //             if ($purchaseProductsBadgstock) {
    //                 $purchaseProductsBadgstock->sale_quantity += $rowData['quantity'] ;
    //                 $purchaseProductsBadgstock->save(); // Save the updated value
    //             }
    
    //             $product = product::where('id', $rowData['productId'])->first(); // Use first() instead of get() to get a single record
    
    //             if ($product) {
    //                 $product->sale_quantity += $rowData['quantity'];
    //                 $product->save(); // Save the updated value
    //             }
    //         }
    //     }

    //     foreach ($mortgageData as $rowData) {
    //         if($rowData['type']=='product item'){

              
    //             $existingSaleItem = SaleItem::where('sale_id', $data['saleId'])
    //             ->where('sale_product_id', $existingSaleProduct->id)
    //             ->where('item_id', $rowData['itemId'])
    //             ->first();
            
    //         if ($existingSaleItem) {

    //             $previousItemQuantity = $existingSaleItem->quantity;
         
    //             // Update existing record
    //             $existingSaleItem->update([
    //                 'cost' => $rowData['itemCost'],
    //                 'price' => $rowData['price'],
    //                 'quantity' => $rowData['quantity'],
    //                 'imu_number' => $rowData['itemBarcode'], 
    //                 'warranty_status' => $rowData['warrantyStatus'],
    //                 'warranty_period' => $rowData['Warrantyperiod'],
    //             ]);

    //             $purchaseProductItem = purchaseProductItem::where('id', $rowData['itemId'])->first();
    //             if ($purchaseProductItem) {
    //                 $purchaseProductItem->quantity += $previousItemQuantity;
    //                 $purchaseProductItem->quantity -= $rowData['quantity'];
    //                 $purchaseProductItem->save(); // Save the updated value
    //             }


    //         } else {
    //             // Create new record if it does not exist
    //             SaleItem::create([
    //                 'sale_product_id' => $existingSaleProduct->id ?? $saleProduct->id,
    //                 'sale_id' => $data['saleId'],
    //                 'item_id' => $rowData['itemId'],
    //                 'cost' => $rowData['itemCost'],
    //                 'price' => $rowData['price'],
    //                 'quantity' => $rowData['quantity'],
    //                 'imu_number' => $rowData['itemBarcode'], 
    //                 'warranty_status' => $rowData['warrantyStatus'],
    //                 'warranty_period' => $rowData['Warrantyperiod'],
    //             ]);


    //             $purchaseProductItem = purchaseProductItem::where('id', $rowData['itemId'])->first();
    //             if ($purchaseProductItem) {
    //                 $purchaseProductItem->quantity -= $rowData['quantity'];
    //                 $purchaseProductItem->save(); // Save the updated value
    //             }
    //         }
            
    
               
    //         }
    //     }
        

    // }



    public function updateSale(Request $request)
{   
    $data = $request->all();
    $saleId = $data['saleId'];
    $sale = Sale::find($saleId);
// dd($data);
     $ReceivedAmountDif =  $data['ReceivedAmount'] - $data['preReceivedAmount'];
    

    if ($sale) {
        // Update the sale record
        $sale->update([
            'sale_reference' => $data['sale_reference'],
            'customer_id' => $data['customer'],
            'branch_id' => $data['branchId'],
            'total_price' => $data['finaltotalSum'],
            'total_cost' => $data['finaltotalCostSum'],
            'quantity' => $data['finaltotalQuantitySum'],
            'discount_amount' => $data['discountAmount'],
            'discount_prasentage' => $data['discount'],
            'service_charge' => $data['ServiceChanges'],
            'tax_charge' => $data['taxChanges'],
            'other_charge' => $data['otherChanges'],
            'total_other_charge' => $data['ifAnyCharges'],
            'other_charge_description' => $data['sale_reference'],
            'final_total_price' => $data['totalAmountAfterDiscount'],
            'received_amount' => $data['ReceivedAmount'],
            'outstanding_amount' => $data['outstandingAmount'],
        ]);

        if($ReceivedAmountDif > 0){
             salesPayment::create([
                    'sale_id' => $sale->id,
                    'date' => Carbon::now()->toDateString(),
                    'amount' => $ReceivedAmountDif,
                    'method' => $data['PaymentMethods'],
                    'voucher' => $data['voucher'],
                    'payment_note' => $data['PaymentNote'],
                    'sale_note' => $data['SaleNote'],
                    'staff_note' => $data['StaffNote'],
            ]);

        }
    }

    $mortgageData = $data['mortgageData'] ?? [];
    $groupedData = [];

    // Grouping mortgageData by unique key
    foreach ($mortgageData as $rowData) {
        $key = $rowData['sale_reference'] . '-' . $rowData['productId'] . '-' . $rowData['badgstock_id'] . '-' . $rowData['itemCost'];

        if (!isset($groupedData[$key])) {
            $groupedData[$key] = [
                'productId' => $rowData['productId'],
                'badgstock_id' => $rowData['badgstock_id'],
                'itemCost' => $rowData['itemCost'],
                'type' => $rowData['type'],
                'price' => $rowData['price'],
                'quantity' => $rowData['quantity'],
                'warrantyStatus' => $rowData['warrantyStatus'],
                'Warrantyperiod' => $rowData['Warrantyperiod']
            ];
        } else {
            // Sum up the quantity for similar products
            $groupedData[$key]['quantity'] += $rowData['quantity'];
        }
    }

    // Insert or Update saleProduct table
    foreach ($groupedData as $rowData) {
        // Check if the record exists
        $existingSaleProduct = SaleProduct::where('sale_id', $saleId)
            ->where('product_id', $rowData['productId'])
            ->where('badgstock_id', $rowData['badgstock_id'])
            ->first();
        
        if ($existingSaleProduct) {
            $previousQuantity = $existingSaleProduct->quantity;
            $newQuantity = $rowData['quantity'] - $previousQuantity;

            $purchaseProductsBadgstock = PurchaseProductsBadgstock::where('id', $rowData['badgstock_id'])
                ->where('product_id', $rowData['productId'])
                ->first();
            
            if ($purchaseProductsBadgstock) {
                $purchaseProductsBadgstock->sale_quantity -= $previousQuantity;
                $purchaseProductsBadgstock->sale_quantity += $rowData['quantity'];
                $purchaseProductsBadgstock->save();
            }

            $product = Product::where('id', $rowData['productId'])->first();
            if ($product) {
                $product->sale_quantity -= $previousQuantity;
                $product->sale_quantity += $rowData['quantity'];
                $product->save();
            }

            $existingSaleProduct->update([
                'type' => $rowData['type'],
                'quantity' => $rowData['quantity'],
                'cost' => $rowData['itemCost'],
                'price' => $rowData['price'],
                'profit' => $rowData['price'] - $rowData['itemCost'],
                'warranty_status' => $rowData['warrantyStatus'],
                'warranty_period' => $rowData['Warrantyperiod'],
            ]);

        } else {
            // Create a new sale product
            $saleProduct = SaleProduct::create([
                'product_id' => $rowData['productId'],
                'sale_id' => $saleId,
                'badgstock_id' => $rowData['badgstock_id'],
                'type' => $rowData['type'],
                'quantity' => $rowData['quantity'],
                'cost' => $rowData['itemCost'],
                'price' => $rowData['price'],
                'profit' => $rowData['price'] - $rowData['itemCost'],
                'warranty_status' => $rowData['warrantyStatus'],
                'warranty_period' => $rowData['Warrantyperiod'],
            ]);

            $purchaseProductsBadgstock = PurchaseProductsBadgstock::where('id', $rowData['badgstock_id'])
                ->where('product_id', $rowData['productId'])
                ->first();
            
            if ($purchaseProductsBadgstock) {
                $purchaseProductsBadgstock->sale_quantity += $rowData['quantity'];
                $purchaseProductsBadgstock->save();
            }

            $product = Product::where('id', $rowData['productId'])->first();
            if ($product) {
                $product->sale_quantity += $rowData['quantity'];
                $product->save();
            }
        }
    }

    foreach ($mortgageData as $rowData) {
        if ($rowData['type'] == 'product item') {
            $existingSaleProduct = SaleProduct::where('sale_id', $saleId)
                ->where('product_id', $rowData['productId'])
                ->where('badgstock_id', $rowData['badgstock_id'])
                ->first();

            $saleProductId = $existingSaleProduct->id ?? null;

            if ($saleProductId) {
                $existingSaleItem = SaleItem::where('sale_id', $saleId)
                    ->where('sale_product_id', $saleProductId)
                    ->where('item_id', $rowData['itemId'])
                    ->first();
                
                if ($existingSaleItem) {
                    $previousItemQuantity = $existingSaleItem->quantity;

                    // Update existing SaleItem
                    $existingSaleItem->update([
                        'cost' => $rowData['itemCost'],
                        'price' => $rowData['price'],
                        'quantity' => $rowData['quantity'],
                        'imu_number' => $rowData['itemBarcode'],
                        'warranty_status' => $rowData['warrantyStatus'],
                        'warranty_period' => $rowData['Warrantyperiod'],
                    ]);

                    $purchaseProductItem = PurchaseProductItem::where('id', $rowData['itemId'])->first();
                    if ($purchaseProductItem) {
                        $purchaseProductItem->quantity += $previousItemQuantity;
                        $purchaseProductItem->quantity -= $rowData['quantity'];
                        $purchaseProductItem->save();
                    }

                } else {
                    // Create new SaleItem
                    SaleItem::create([
                        'sale_product_id' => $saleProductId,
                        'sale_id' => $saleId,
                        'item_id' => $rowData['itemId'],
                        'cost' => $rowData['itemCost'],
                        'price' => $rowData['price'],
                        'quantity' => $rowData['quantity'],
                        'imu_number' => $rowData['itemBarcode'],
                        'warranty_status' => $rowData['warrantyStatus'],
                        'warranty_period' => $rowData['Warrantyperiod'],
                    ]);

                    $purchaseProductItem = PurchaseProductItem::where('id', $rowData['itemId'])->first();
                    if ($purchaseProductItem) {
                        $purchaseProductItem->quantity -= $rowData['quantity'];
                        $purchaseProductItem->save();
                    }
                }
            }
        }
    }
}



public function deleteSaleProduct(Request $request)
{   
    $data = $request->all();
    $saleId = $data['saleId'];


    
    $sale = Sale::find($saleId);

    if ($sale) {
        // Subtracting the given values from the existing ones
        $sale->update([
            'total_price' => $sale->total_price - $data['totalprice'],
            'total_cost' => $sale->total_cost - $data['totalCost'],
            'quantity' => $sale->quantity - $data['quantity'],    
            'final_total_price' => $sale->final_total_price - $data['totalprice'],
            'outstanding_amount' => $sale->outstanding_amount - $data['totalprice'],
        ]);
    }

    $saleProduct = SaleProduct::where('sale_id', $data['saleId'])
        ->where('badgstock_id', $data['badgstock_id'])
        ->where('id', $data['saleProductsId'])
        ->first();

    if ($saleProduct) {
        // **Ensure related SaleItems are deleted first to avoid FK constraint errors**
        // SaleItem::where('sale_product_id', $saleProduct->id)->delete();

        // Update PurchaseProductsBadgstock quantity
        $purchaseProductsBadgstock = PurchaseProductsBadgstock::where('id', $data['badgstock_id'])
            ->where('product_id', $data['product_id'])
            ->first(); 

        if ($purchaseProductsBadgstock) {
            $purchaseProductsBadgstock->sale_quantity -= $data['quantity'];
            $purchaseProductsBadgstock->save();
        }

        // Update Product sale quantity
        $product = Product::find($data['product_id']);
        if ($product) {
            $product->sale_quantity -= $data['quantity'];
            $product->save();
        }

        // **Fixing item_id check**
        if (empty($data['item_id'])) {
            $saleProduct->delete(); 
        } else {


            $SaleItem = SaleItem::where('sale_id', $data['saleId'])
            ->where('item_id', $data['item_id'])
            ->where('sale_product_id', $data['saleProductsId'])
            ->where('imu_number', (string) $data['code']) // Cast to string
            ->first();

          

        if ($SaleItem) {
            $SaleItem->delete();

            // Update PurchaseProductItem quantity
            $purchaseProductItem = PurchaseProductItem::where('id', $data['item_id'])
            ->where('imu_number', (string) $data['code'])->first();
            if ($purchaseProductItem) {
                $purchaseProductItem->quantity += $data['quantity'];
                $purchaseProductItem->save();
            }
        }





            if ($saleProduct->quantity == $data['quantity']) {
                $saleProduct->delete();
            } else {
                $saleProduct->quantity -= $data['quantity']; 
                $saleProduct->save(); 
            }

            // **Fix imu_number type issue by casting to string**
           
        }
    }
}



// public function returnSaleProduct(Request $request)
// {
//     $data = $request->all();
//     $saleId = $data['saleId'];
//     $sale = Sale::find($saleId);

//     if ($sale) {
//         // Update the sale record
//         $sale->update([
//             'total_price' => $sale->total_price - $data['totalSum'],
//             'total_cost' => $sale->total_cost - $data['totalCost'],
//             'quantity' => $sale->quantity - $data['totalQuantity'],
//             'final_total_price' => $sale->final_total_price - $data['totalSum'],
//             'outstanding_amount' => $sale->outstanding_amount - $data['totalSum'],
//         ]);

//         $mortgageData = $data['mortgageData'] ?? [];
//         $groupedData = [];

//         foreach ($mortgageData as $rowData) {
//             saleReturn::create([
//                 'product_id' => $rowData['productid'],
//                 'sale_id' => $saleId,
//                 'badgstock_id' => $rowData['badgstock_id'],
//                 'date' => now(),
//                 'type' => $rowData['type'],
//                 'quantity' => $rowData['quantity'],
//                 'name' => $rowData['name'],
//                 'code' => $rowData['imu_number'],
//             ]);

//             $key = $rowData['productid'] . '-' . $rowData['badgstock_id'] . '-' . $rowData['each_cost'];

//             if (!isset($groupedData[$key])) {
//                 $groupedData[$key] = [
//                     'productid' => $rowData['productid'],
//                     'badgstock_id' => $rowData['badgstock_id'],
//                     'item_id' => $rowData['item_id'],
//                     'type' => $rowData['type'],
//                     'totalPrice' => $rowData['totalPrice'],
//                     'quantity' => $rowData['quantity'],
//                     'saleQuantity' => $rowData['saleQuantity'],
//                     'actualQuantity' => $rowData['actualQuantity'],
//                     'actualSubtotal' => $rowData['actualSubtotal'],
//                     'each_cost' => $rowData['each_cost']
//                 ];
//             } else {
//                 $groupedData[$key]['quantity'] += $rowData['quantity'];
//             }
//         }

//         foreach ($groupedData as $rowData) {
//             $existingSaleProduct = SaleProduct::where('sale_id', $saleId)
//                 ->where('product_id', $rowData['productid'])
//                 ->where('badgstock_id', $rowData['badgstock_id'])
//                 ->first();

//             if ($existingSaleProduct) {
//                 if ($rowData['actualQuantity'] == 0) {
//                     $existingSaleProduct->delete();
//                 } else {
//                     $existingSaleProduct->update([
//                         'quantity' => $existingSaleProduct->quantity - $rowData['quantity']
//                     ]);
//                 }
//             }

//             $purchaseProductsBadgstock = PurchaseProductsBadgstock::where('id', $rowData['badgstock_id'])
//                 ->where('product_id', $rowData['productid'])
//                 ->first();

//             if ($purchaseProductsBadgstock) {
//                 $purchaseProductsBadgstock->update([
//                     'sale_quantity' => $purchaseProductsBadgstock->sale_quantity - $rowData['quantity']
//                 ]);
//             }

//             $product = Product::find($rowData['productid']);
//             if ($product) {
//                 $product->update([
//                     'sale_quantity' => $product->sale_quantity - $rowData['quantity']
//                 ]);
//             }
//         }

//         foreach ($mortgageData as $rowData) {
//             if ($rowData['type'] == 'product item') {
//                 $existingSaleProduct = SaleProduct::where('sale_id', $saleId)
//                     ->where('product_id', $rowData['productid'])
//                     ->where('badgstock_id', $rowData['badgstock_id'])
//                     ->first();

//                 $saleProductId = $existingSaleProduct->id ?? null;

//                 if ($saleProductId) {
//                     $existingSaleItem = SaleItem::where('sale_id', $saleId)
//                         ->where('sale_product_id', $saleProductId)
//                         ->where('item_id', $rowData['item_id'])
//                         ->first();

//                     if ($existingSaleItem) {
//                         if ($rowData['actualQuantity'] == 0) {
//                             $existingSaleItem->delete();
//                         } else {
//                             $existingSaleItem->update([
//                                 'quantity' => $existingSaleItem->quantity - $rowData['quantity']
//                             ]);
//                         }

//                         $purchaseProductItem = PurchaseProductItem::where('id', $rowData['item_id'])->first();
//                         if ($purchaseProductItem) {
//                             $purchaseProductItem->update([
//                                 'quantity' => $purchaseProductItem->quantity + $rowData['quantity']
//                             ]);
//                         }
//                     }
//                 }
//             }
//         }
//     }




// }






// public function returnSaleProduct(Request $request)
// {
//     $data = $request->all();
//     dd($data);
    
//     $saleId = $data['saleId'];
//     $sale = Sale::find($saleId);

//     if ($sale) {
//         // Update the sale record
//         $sale->update([
//             'total_price' => $sale->total_price - $data['totalSum'],
//             'total_cost' => $sale->total_cost - $data['totalCost'],
//             'quantity' => $sale->quantity - $data['totalQuantity'],
//             'final_total_price' => $sale->final_total_price - $data['totalSum'],
//             'outstanding_amount' => $sale->outstanding_amount - $data['totalSum'],
//         ]);

//         $mortgageData = $data['mortgageData'] ?? [];
//         $groupedData = [];

//         foreach ($mortgageData as $rowData) {
//             saleReturn::create([
//                 'product_id' => $rowData['productid'],
//                 'sale_id' => $saleId,
//                 'badgstock_id' => $rowData['badgstock_id'],
//                 'date' => now(),
//                 'type' => $rowData['type'],
//                 'quantity' => $rowData['quantity'],
//                 'name' => $rowData['name'],
//                 'code' => $rowData['imu_number'],
//             ]);

//             $key = $rowData['productid'] . '-' . $rowData['badgstock_id'] . '-' . $rowData['each_cost'];

//             if (!isset($groupedData[$key])) {
//                 $groupedData[$key] = [
//                     'productid' => $rowData['productid'],
//                     'badgstock_id' => $rowData['badgstock_id'],
//                     'item_id' => $rowData['item_id'],
//                     'type' => $rowData['type'],
//                     'totalPrice' => $rowData['totalPrice'],
//                     'quantity' => $rowData['quantity'],
//                     'saleQuantity' => $rowData['saleQuantity'],
//                     'actualQuantity' => $rowData['actualQuantity'],
//                     'actualSubtotal' => $rowData['actualSubtotal'],
//                     'each_cost' => $rowData['each_cost']
//                 ];
//             } else {
//                 $groupedData[$key]['quantity'] += $rowData['quantity'];
//             }
//         }

//         foreach ($mortgageData as $rowData) {
//               $existingSaleProduct = SaleProduct::where('sale_id', $saleId)
//                     ->where('product_id', $rowData['productid'])
//                     ->where('badgstock_id', $rowData['badgstock_id'])
//                     ->first();

//                 $existingSaleProduct->update([
//                         'quantity' => $existingSaleProduct->quantity - $rowData['quantity']
//                 ]);

//             if ($rowData['type'] == 'product item') {

//                 $saleProductId = $existingSaleProduct->id ?? null;
            

//                 if ($saleProductId) {
//                     $existingSaleItem = SaleItem::where('sale_id', $saleId)
//                         ->where('sale_product_id', $saleProductId)
//                         ->where('item_id', $rowData['item_id'])
//                         ->first();

                       
                  
                    
//                     if ($existingSaleItem) {
//                         if ($rowData['actualQuantity'] == 0) {
//                             $existingSaleItem->delete();
//                         } else {
//                             $existingSaleItem->update([
//                                 'quantity' => $existingSaleItem->quantity - $rowData['quantity']
//                             ]);
//                         }

//                         $purchaseProductItem = PurchaseProductItem::where('id', $rowData['item_id'])->first();
                      
                        
//                         if ($purchaseProductItem) {
//                             $purchaseProductItem->update([
//                                 'quantity' => $purchaseProductItem->quantity + $rowData['quantity']
//                             ]);
//                         }
//                     }
//                 }
//             }
//             else{

//             if ($existingSaleProduct) {
//                 if ($rowData['actualQuantity'] == 0) {
//                     // Delete related sale items first to avoid foreign key constraint errors
//                     $existingSaleProduct->delete();

//                 } else {
//                     $existingSaleProduct->update([
//                         'quantity' => $existingSaleProduct->quantity - $rowData['quantity']
//                     ]);
//                 }
//             }

//             }
//         }


//         foreach ($groupedData as $rowData) {
//             $existingSaleProduct = SaleProduct::where('sale_id', $saleId)
//                 ->where('product_id', $rowData['productid'])
//                 ->where('badgstock_id', $rowData['badgstock_id'])
//                 ->first();

//             // if ($existingSaleProduct) {
//             //     if ($rowData['actualQuantity'] == 0) {
//             //         // Delete related sale items first to avoid foreign key constraint errors
//             //         SaleItem::where('sale_product_id', $existingSaleProduct->id)->delete();

//             //         // Now it's safe to delete the sale product
//             //         $existingSaleProduct->delete();
//             //     } else {
//             //         $existingSaleProduct->update([
//             //             'quantity' => $existingSaleProduct->quantity - $rowData['quantity']
//             //         ]);
//             //     }
//             // }

//             $purchaseProductsBadgstock = PurchaseProductsBadgstock::where('id', $rowData['badgstock_id'])
//                 ->where('product_id', $rowData['productid'])
//                 ->first();

//             if ($purchaseProductsBadgstock) {
//                 $purchaseProductsBadgstock->update([
//                     'sale_quantity' => $purchaseProductsBadgstock->sale_quantity - $rowData['quantity']
//                 ]);
//             }

//             $product = Product::find($rowData['productid']);
//             if ($product) {
//                 $product->update([
//                     'sale_quantity' => $product->sale_quantity - $rowData['quantity']
//                 ]);
//             }
//         }

       
//     }
// }


public function returnSaleProduct(Request $request)
{
    $data = $request->all();
    // dd($data);

    DB::beginTransaction();

    try {
        $saleId = $data['saleId'];
        $sale = Sale::find($saleId);

        if (!$sale) {
            return response()->json(['error' => 'Sale not found'], 404);
        }

        // Update the main sale record
        $sale->update([
            'total_price' => $sale->total_price - $data['totalSum'],
            'total_cost' => $sale->total_cost - $data['totalCost'],
            'quantity' => $sale->quantity - $data['totalQuantity'],
            'final_total_price' => $sale->final_total_price - $data['totalSum'],
            'outstanding_amount' => $sale->outstanding_amount - $data['totalSum'],
        ]);

        $mortgageData = $data['mortgageData'] ?? [];
        $groupedData = [];

        foreach ($mortgageData as $rowData) {
            // Record the returned product
            saleReturn::create([
                'product_id' => $rowData['productid'],
                'sale_id' => $saleId,
                'badgstock_id' => $rowData['badgstock_id'],
                'date' => now(),
                'type' => $rowData['type'],
                'Retun_type' => $data['RetunType'],
                'total_price' => $rowData['totalPrice'],
                'total_cost'   => $rowData['each_cost'] * $rowData['quantity'],
                'quantity' => $rowData['quantity'],
                'name' => $rowData['name'],
                'code' => $rowData['imu_number'],
            ]);

            $key = $rowData['productid'] . '-' . $rowData['badgstock_id'] . '-' . $rowData['each_cost'];

            if (!isset($groupedData[$key])) {
                $groupedData[$key] = [
                    'productid' => $rowData['productid'],
                    'badgstock_id' => $rowData['badgstock_id'],
                    'item_id' => $rowData['item_id'],
                    'type' => $rowData['type'],
                    'totalPrice' => $rowData['totalPrice'],
                    'quantity' => $rowData['quantity'],
                    'saleQuantity' => $rowData['saleQuantity'],
                    'actualQuantity' => $rowData['actualQuantity'],
                    'actualSubtotal' => $rowData['actualSubtotal'],
                    'each_cost' => $rowData['each_cost']
                ];
            } else {
                $groupedData[$key]['quantity'] += $rowData['quantity'];
            }

            // Handle product item or single product
            $existingSaleProduct = SaleProduct::where('sale_id', $saleId)
                ->where('product_id', $rowData['productid'])
                ->where('badgstock_id', $rowData['badgstock_id'])
                ->first();

            if ($rowData['type'] === 'product item' && $existingSaleProduct) {
                $saleProductId = $existingSaleProduct->id;

                $existingSaleItem = SaleItem::where('sale_id', $saleId)
                    ->where('sale_product_id', $saleProductId)
                    ->where('item_id', $rowData['item_id'])
                    ->first();

                if ($existingSaleItem) {
                    if ($rowData['actualQuantity'] == 0) {
                        $existingSaleItem->delete();
                    } else {
                        $existingSaleItem->update([
                            'quantity' => $existingSaleItem->quantity - $rowData['quantity']
                        ]);
                    }

                    $purchaseProductItem = PurchaseProductItem::find($rowData['item_id']);
                    if ($purchaseProductItem) {
                        $purchaseProductItem->update([
                            'quantity' => $purchaseProductItem->quantity + $rowData['quantity']
                        ]);
                    }
                }
            } elseif ($existingSaleProduct) {
                if ($rowData['actualQuantity'] == 0) {
                    $existingSaleProduct->delete();
                }
            }
        }

        // Now update main SaleProduct, Product, and PurchaseProductsBadgstock quantities
        foreach ($groupedData as $rowData) {
            // dd($groupedData);
            $existingSaleProduct = SaleProduct::where('sale_id', $saleId)
                ->where('product_id', $rowData['productid'])
                ->where('badgstock_id', $rowData['badgstock_id'])
                ->first();

               
            if($rowData['type'] == "product item"){
                if ($existingSaleProduct ) {
                    $existingSaleProduct->update([
                    'quantity' => $existingSaleProduct->quantity - $rowData['quantity']
                ]);
               
                }
            }
             if($rowData['type'] == "product"){
                if ($existingSaleProduct && $rowData['actualQuantity'] > 0) {
                    $existingSaleProduct->update([
                    'quantity' => $existingSaleProduct->quantity - $rowData['quantity']
                ]);
                }else{
                    $existingSaleProduct->delete();
                }
            }

           

            $purchaseProductsBadgstock = PurchaseProductsBadgstock::where('id', $rowData['badgstock_id'])
                ->where('product_id', $rowData['productid'])
                ->first();

            if ($purchaseProductsBadgstock) {
                $purchaseProductsBadgstock->update([
                    'sale_quantity' => $purchaseProductsBadgstock->sale_quantity - $rowData['quantity']
                ]);
            }

            $product = Product::find($rowData['productid']);
            if ($product) {
                $product->update([
                    'sale_quantity' => $product->sale_quantity - $rowData['quantity']
                ]);
            }
        }

        DB::commit();
        return response()->json(['success' => true]);

    } catch (\Exception $e) {
        DB::rollBack();
        return response()->json([
            'success' => false,
            'error' => $e->getMessage()
        ], 500);
    }
}



public function returnProductData(Request $request)
{
    $data = $request->all();

    // Optional: Remove this after testing
    // dd($data);

    $mortgageData = $data['mortgageData'] ?? [];
    $groupedData = [];

    foreach ($mortgageData as $rowData) {
        // Create a record in the sale_return table
        saleReturn::create([
            'product_id'    => $rowData['productid'],
            'badgstock_id'  => $rowData['badgstock_id'],
            'date'          => now(),
            'type'          => $rowData['type'],
            'quantity'      => $rowData['quantity'],
            'name'          => $rowData['name'],
            'code'          => $rowData['imu_number'],
        ]);

        // Group data for later batch updates
        $key = $rowData['productid'] . '-' . $rowData['badgstock_id'] . '-' . $rowData['each_cost'];

        if (!isset($groupedData[$key])) {
            $groupedData[$key] = [
                'productid'       => $rowData['productid'],
                'badgstock_id'    => $rowData['badgstock_id'],
                'item_id'         => $rowData['item_id'],
                'type'            => $rowData['type'],
                'totalPrice'      => $rowData['totalPrice'],
                'quantity'        => $rowData['quantity'],
                'saleQuantity'    => $rowData['saleQuantity'],
                'actualQuantity'  => $rowData['actualQuantity'],
                'actualSubtotal'  => $rowData['actualSubtotal'],
                'each_cost'       => $rowData['each_cost'],
            ];
        } else {
            $groupedData[$key]['quantity'] += $rowData['quantity'];
        }
    }

    // Update purchase product item stock quantities
    foreach ($mortgageData as $rowData) {
        if ($rowData['type'] === 'product item') {
            $purchaseProductItem = PurchaseProductItem::find($rowData['item_id']);
            if ($purchaseProductItem) {
                $purchaseProductItem->increment('quantity', $rowData['quantity']);
            }
        }
    }

    // Update badge stock and product sale quantities
    foreach ($groupedData as $rowData) {
        $purchaseProductsBadgstock = PurchaseProductsBadgstock::where('id', $rowData['badgstock_id'])
            ->where('product_id', $rowData['productid'])
            ->first();

        if ($purchaseProductsBadgstock) {
            $purchaseProductsBadgstock->decrement('sale_quantity', $rowData['quantity']);
        }

        $product = Product::find($rowData['productid']);
        if ($product) {
            $product->decrement('sale_quantity', $rowData['quantity']);
        }
    }
}











// public function searchProductForReturn(Request $request, $id)
// {
//     $query = $request->get('query');

//     $suggestions = SaleProduct::join('products', 'sale_products.product_id', '=', 'products.id')
//     ->leftJoin('purchase_product_items', 'products.id', '=', 'purchase_product_items.product_id')
//     ->leftJoin('sale_items', 'sale_products.id', '=', 'sale_items.sale_product_id')
//     ->leftJoin('purchase_products_badgstock', 'purchase_products_badgstock.product_id', '=', 'products.id')
//     ->where('sale_products.sale_id', $id)
//     ->where(function ($q) use ($query) {
//         $q->where('products.name', 'like', "%{$query}%")
//             ->orWhere('products.code', 'like', "%{$query}%")
//             ->orWhere('sale_items.imu_number', 'like', "%{$query}%");
//     })
//     ->select([
//         'products.code',
//         'sale_items.imu_number',
//         'sale_items.item_id as itemId',
//         'purchase_product_items.id as product_items_id',
//         'purchase_products_badgstock.each_cost',
//         'purchase_products_badgstock.id as badgstock_id',
//         'sale_items.quantity as sale_quantity',
//         'sale_products.quantity as product_quantity',
//         'products.id',
//         'products.price',
//         'products.name',
//         'purchase_product_items.quantity',
//     ])
//     ->distinct()
//     ->limit(10)
//     ->get()
//     ->map(function ($item) {
//         $item->type = $item->imu_number ? 'product item' : 'product';
//         $item->quantity = $item->sale_quantity ?: $item->product_quantity;
//         $item->unique_key = $item->itemId ?: ('product_' . $item->id . '_' . $item->badgstock_id);
//         $item->imu_number = $item->imu_number ?: $item->code;
        
//         return $item;
//     })
//     ->unique('unique_key');


//     // dd($suggestions);


//  // Check if any suggestions were found
//     if ($suggestions->isEmpty()) {
//         return response()->json([
//          'success' => false,
//          'message' => 'No products found for the given query.'
//      ], 404);
//     }

//  // Return the suggestions as JSON
//     return response()->json([
//         'success' => true,
//         'suggestions' => $suggestions
//     ]);
 
//     }

public function searchProductForReturn(Request $request, $id)
{
    $query = $request->get('query');

    $suggestions = SaleProduct::join('products', 'sale_products.product_id', '=', 'products.id')
    ->leftJoin('purchase_product_items', 'products.id', '=', 'purchase_product_items.product_id')
    ->leftJoin('sale_items', 'sale_products.id', '=', 'sale_items.sale_product_id')
    ->leftJoin('purchase_products_badgstock', 'purchase_products_badgstock.product_id', '=', 'products.id')
    ->where('sale_products.sale_id', $id)
    ->where(function ($q) use ($query) {
        $q->where('products.name', 'like', "%{$query}%")
            ->orWhere('products.code', 'like', "%{$query}%")
            ->orWhere('sale_items.imu_number', 'like', "%{$query}%");
    })
    ->select([
        'products.code',
        'sale_items.imu_number',
        'sale_items.item_id as itemId',
        'purchase_product_items.id as product_items_id',
        'sale_products.cost as each_cost',
        'sale_products.badgstock_id',
        'sale_items.quantity as sale_quantity',
        'sale_products.quantity as product_quantity',
        'products.id',
        'sale_products.price',
        'products.name',
        'purchase_product_items.quantity',
    ])
    ->distinct()
    ->limit(10)
    ->get()
    ->map(function ($item) {
        $item->type = $item->imu_number ? 'product item' : 'product';
        $item->quantity = $item->sale_quantity ?: $item->product_quantity;
        $item->unique_key = $item->itemId ?: ('product_' . $item->id . '_' . $item->badgstock_id);
        $item->imu_number = $item->imu_number ?: $item->code;
        
        return $item;
    })
    ->unique('unique_key');


    // dd($suggestions);


 // Check if any suggestions were found
    if ($suggestions->isEmpty()) {
        return response()->json([
         'success' => false,
         'message' => 'No products found for the given query.'
     ], 404);
    }

 // Return the suggestions as JSON
    return response()->json([
        'success' => true,
        'suggestions' => $suggestions
    ]);
 
    }




    public function searchProductForTransfer(Request $request, $id)
{
    $query = $request->get('query');
   
$suggestions = purchaseProduct::leftJoin('products', 'purchase_products.product_id', '=', 'products.id')
        ->leftJoin('purchase_product_items', 'purchase_products.purchases_id', '=', 'purchase_product_items.purchases_id')
        ->leftJoin('purchases', 'purchase_products.purchases_id', '=', 'purchases.id')
        ->leftJoin('purchase_products_badgstock', 'purchase_products_badgstock.product_id', '=', 'products.id') 
        ->where('purchases.status', '!=', 'Pending')
         ->where('purchase_products_badgstock.branches_id', $id)
        ->where(function ($q) use ($query) {
            $q->where('products.name', 'like', "%{$query}%")
              ->orWhere('products.code', 'like', "%{$query}%")
              ->orWhere('purchase_product_items.imu_number', 'like', "%{$query}%");
        })
        ->where(function ($query) {
            $query->where(function ($q) {
                $q->whereNotNull('purchase_product_items.id')
                  ->where('purchase_product_items.quantity', '>', 0)
                  ->whereRaw('purchase_product_items.product_id = products.id');
            })
            ->orWhereNull('purchase_product_items.id');
        })
        ->distinct()
        ->select([
            'products.code',
            'products.quantity as product_quantity',
            'purchase_product_items.imu_number',
            'purchase_product_items.each_cost AS items_each_cost',
            'purchase_product_items.id as itemId',
            'purchase_products_badgstock.quantity as badgstock_quantity',
            'purchase_product_items.id as product_items_id',
            'purchase_products_badgstock.each_cost',
            'purchase_products_badgstock.id as badgstock_id',
            'purchase_products_badgstock.sale_quantity as sale_quantity',
            'products.id',
            'purchase_products_badgstock.price',
            'products.name',
            'purchase_product_items.quantity',
        ])
        ->limit(20)
        ->get()
        ->map(function ($item) {
            $item->type = $item->imu_number ? 'product item' : 'product';
            $item->each_cost = $item->items_each_cost ?: $item->each_cost;
            $item->quantity = $item->quantity ?: ($item->badgstock_quantity - $item->sale_quantity);
            $item->imu_number = $item->imu_number ?: $item->code;
            return $item;
        });

  


    // dd($suggestions);


 // Check if any suggestions were found
    if ($suggestions->isEmpty()) {
        return response()->json([
         'success' => false,
         'message' => 'No products found for the given query.'
     ], 404);
    }

 // Return the suggestions as JSON
    return response()->json([
        'success' => true,
        'suggestions' => $suggestions
    ]);
 
    }


    public function returnProduct(Request $request)
{
 $query = $request->get('query');

$suggestions = SaleProduct::join('products', 'sale_products.product_id', '=', 'products.id')
    ->join('sales', 'sale_products.sale_id', '=', 'sales.id')
    ->leftJoin('sale_items', 'sale_products.id', '=', 'sale_items.sale_product_id')
    ->where(function ($q) use ($query) {
        $q->where('products.name', 'like', "%{$query}%")
            ->orWhere('products.code', 'like', "%{$query}%")
            ->orWhere('sale_items.imu_number', 'like', "%{$query}%");
    })
    ->select([
        'products.code',
        'products.name',
        'sales.sale_reference',
        'sales.date',
        'sales.id as sales_id',
        'sale_items.imu_number',
        'sale_items.item_id as itemId',
        'sale_products.price',
        'sale_products.cost as each_cost',
        'sale_products.quantity as product_quantity',
        'sale_items.quantity as sale_quantity',
        'products.id as product_id',
        'sale_products.badgstock_id',
    ])
    ->distinct()
    ->limit(10)
    ->get()
    ->map(function ($item) {
        $item->type = $item->imu_number ? 'product item' : 'product';
        $item->quantity = $item->sale_quantity ?: $item->product_quantity;
        $item->unique_key = $item->itemId ?: ('product_' . $item->product_id . '_' . $item->badgstock_id);
        $item->imu_number = $item->imu_number ?: $item->code;
        return $item;
    })
    ->unique('unique_key') // Back again to prevent identical duplicates
    ->values(); // Reset array keys

// Check if any suggestions were found
if ($suggestions->isEmpty()) {
    return response()->json([
        'success' => false,
        'message' => 'No products found for the given query.'
    ], 404);
}

// Return the suggestions as JSON
return response()->json([
    'success' => true,
    'suggestions' => $suggestions
]);

 
    }



        public function returnProductBarcode(Request $request)
{
 $query = $request->get('query');

$suggestions = SaleProduct::join('products', 'sale_products.product_id', '=', 'products.id')
    ->join('sales', 'sale_products.sale_id', '=', 'sales.id')
    ->leftJoin('sale_items', 'sale_products.id', '=', 'sale_items.sale_product_id')
    ->where(function ($q) use ($query) {
        $q->where('sales.sale_reference', 'like', "%{$query}%");
    })
    ->select([
        'products.code',
        'products.name',
        'sales.sale_reference',
        'sales.date',
        'sales.id as sales_id',
        'sale_items.imu_number',
        'sale_items.item_id as itemId',
        'sale_products.price',
        'sale_products.cost as each_cost',
        'sale_products.quantity as product_quantity',
        'sale_items.quantity as sale_quantity',
        'products.id as product_id',
        'sale_products.badgstock_id',
    ])
    ->distinct()
    ->limit(10)
    ->get()
    ->map(function ($item) {
        $item->type = $item->imu_number ? 'product item' : 'product';
        $item->quantity = $item->sale_quantity ?: $item->product_quantity;
        $item->unique_key = $item->itemId ?: ('product_' . $item->product_id . '_' . $item->badgstock_id);
        $item->imu_number = $item->imu_number ?: $item->code;
        return $item;
    })
    ->unique('unique_key') // Back again to prevent identical duplicates
    ->values(); // Reset array keys

// Check if any suggestions were found
if ($suggestions->isEmpty()) {
    return response()->json([
        'success' => false,
        'message' => 'No products found for the given query.'
    ], 404);
}

// Return the suggestions as JSON
return response()->json([
    'success' => true,
    'suggestions' => $suggestions
]);

 
    }
    
}


