<?php
namespace App\Http\Controllers\Admin;

use Illuminate\Http\Request;
use App\Http\Controllers\Controller;

use Carbon\Carbon;
use Illuminate\Support\Collection;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;
use App\Models\tbl_commission_detail;
use App\Service\AssociateSellingNew;
use App\Service\AmountCalculationNew;
use Maatwebsite\Excel\Facades\Excel;
use App\Exports\CommissionDataExport;
use App\Exports\CommissionDetailsDataExport;
class CommissionCalculationNewController extends Controller
{
    private Collection $listAmountCalculationNewInDr; 
    public function __construct(){
        $this->listAmountCalculationInDr = new Collection();
    }

    public function CommissionCalculation(Request $request)
    {
       
        if (is_logged_in()) {
            $submitbtn = $request->submitbtn;
          
             $currentMonth =date("F", strtotime("-1 month"))  ;
            $month_names = array("January","February","March","April","May","June","July","August","September","October","November","December");
            $start_year = 2020;
            $monthname = $request->monthname ? $request->monthname : $currentMonth;
            $year = $request->year ? $request->year : date("Y");
            $timestamp    = strtotime("$monthname $year");
            $startDate = date('Y-m-01', $timestamp);
            $endDate  = date('Y-m-t', $timestamp);
            $GetAdminCommissionSummaryReport = [];
            if($endDate){
                 if($submitbtn == "View"){
                 $GetAdminCommissionSummaryReport = DB::select("CALL GetAdminCommissionSummaryReport('$endDate')"); 
                 }
               
            }
            
            if($submitbtn == "Export"){
                if($endDate){
                     $GetAdminCommissionSummaryReport = DB::select("CALL GetAdminCommissionSummaryReport('$endDate')"); 
                       return Excel::download(new CommissionDataExport($GetAdminCommissionSummaryReport), 'Commission_Data_List.xlsx');
                }
               
            }
           
            
            if($submitbtn == "Calculate"){
                $startCalculation = $this->startCalculation($startDate,$endDate);
            }

            return view('Admin/Report/BrokerageCalculationNew',compact('month_names','start_year','monthname','year','GetAdminCommissionSummaryReport','endDate'));
        }
    }

    public function CommissionCalculationDetails(Request $request){
        $associate_id = $request->associate_id;
        $sale_type = $request->sale_type;
        $enddate = $request->enddate;
        $type = $request->type;
        if(!empty($associate_id) && !empty($sale_type) && !empty($enddate)){
                 $data = DB::select("CALL GetAdminCommissionDetailAssociateWiseReport('$associate_id','$sale_type','$enddate')"); 
                  if($type == "Export"){
                      return Excel::download(new CommissionDetailsDataExport($data), 'Commission__Details_Data_List.xlsx');
                    }
             return response()->json(['status'=>200,"message"=>"Record Found","data" => $data],200);
        }else{
             return response()->json(['status'=>400,"message"=>"No Record Found"],200);
        }
    }
     
    /** @var \Illuminate\Support\Collection<int, AssociateSelling> $list_Associate_Selling */
    /** @var AssociateSelling $list_Associate_Selling */

    public function startCalculation(string $startDate, string $endDate)
    {
        try {
                    
            // Parse dates
            $startDate = date("Y-m-d", strtotime($startDate));          
            $endDate = date("Y-m-d", strtotime($endDate));
           
            // Delete records in transaction
            DB::transaction(function () use ($endDate) {
                DB::table('tbl_commission_summary')
                    ->where('processed_on', '>=', $endDate)
                    ->delete();
                DB::table('tbl_commission_detail')
                    ->where('processed_on', '>=', $endDate)
                    ->delete();

                //$maxSummaryId = DB::table('tbl_commission_summary')->max('commission_summary_id') ?? 0;
                // echo "maxSummaryId ".$maxSummaryId;
                // DB::statement("ALTER TABLE tbl_commission_summary AUTO_INCREMENT = " . ($maxSummaryId + 1));
                //$maxDetailId = DB::table('tbl_commission_detail')->max('commission_detail_id') ?? 0;
                //  echo "maxDetailId ".$maxDetailId;die;
                // DB::statement("ALTER TABLE tbl_commission_detail AUTO_INCREMENT = " . ($maxDetailId + 1));
            });
                    
       
            // Fetch data
            $this->listPlotMaster = DB::table('tbl_plot_master')
                ->leftJoin('tbl_schemes', 'tbl_schemes.id', '=', 'tbl_plot_master.scheme_id')
                ->select('tbl_plot_master.*', 'tbl_schemes.day_diff_master_id as scheme_day_diff_master_id')
                ->whereBetween('allotment_date', [$startDate, $endDate])
                ->where('plot_status','Sold')
                ->whereNull('tbl_plot_master.deleted_at')
                // ->where('is_re_sale', false)
                ->orderBy('sold_date')
                ->get();
                $list_Associate_Selling = [];
                // UnComment This line for calculation
                // DB::statement('CALL calculateTotalSalesCommInsertWithoutCumulativeOpeningStock(?, ?)', [$startDate, $endDate]);
                $list_Associate_Selling = collect(DB::table('tblassociate_selling')->where('Processed_on','=',"$endDate")->get())
                    ->map(function ($row) {
                        $item = new AssociateSellingNew();
                        foreach ($row as $key => $value) {
                            $item->$key = $value;
                        }
                        return $item;
                    })
                    ->keyBy('Associate_id');        
            
            $startTime = now();
            
            $this->calculateInDirectCommission($list_Associate_Selling,$this->listPlotMaster);
               
            $this->bulkInsertToSql($endDate); // Placeholder
                 DB::statement('CALL calculateDirectSalesAndCommissionSummary(?, ?)', [$startDate, $endDate]);
            echo response()->json(['message' => 'Calculation completed successfully']);
        } catch (\Exception $ex) {
            Log::error('Calculation error: ' . $ex->getMessage());
            return response()->json(['error' => 'Calculation failed'], 500);
        }
    }

    public function calculateInDirectCommission($list_Associate_Selling, $listPlotMaster){
        try {
            $hierarchy = [];
    
            if(count($listPlotMaster) > 0){
                for ($i = 0; $i < count($listPlotMaster); $i++) {
    
                    $from_associate_id = $listPlotMaster[$i]->last_hold_book_by_associate;
    
                    // Slab: original commissionValue (extra_percent ab qty pe apply hoga, slab pe nahi)
                    $lastSlabGiven_CommSlab = $list_Associate_Selling[$from_associate_id]->commissionValue;
    
                    // newQty = extra_commission_percent × qty (ek baar plot level pe)
                    $originalQty = $listPlotMaster[$i]->brokergeable_size;
                    $newQty = $listPlotMaster[$i]->give_extra_commission == 1
                        ? ($listPlotMaster[$i]->extra_commission_percent) * $originalQty
                        : $originalQty;
    
                    $hierarchy = explode(',', $listPlotMaster[$i]->associate_list);
    
                    $plot_road_size = $listPlotMaster[$i]->road_size;
                    $plot_category  = $listPlotMaster[$i]->plot_category;
                    $plot_facing    = $listPlotMaster[$i]->plot_facing;
                    $plot_shape     = $listPlotMaster[$i]->plot_shape;
                    $last_associat_actual_comm_slab = $list_Associate_Selling[$from_associate_id]->commissionValue;
    
                    if($plot_category == 1 && $plot_road_size >= 0 && $plot_road_size < 60 && ($plot_facing == 10 || in_array($plot_shape, [9,10,13,14,15]))){
                        $tmp_commission = true;
                    } else {
                        $tmp_commission = false;
                    }
    
                    if($hierarchy && count($hierarchy) > 0){
                        $diamondSlabStarted     = false;
                        $diamondSlab            = 100;
                        $balance_slab_diff      = 0;
                        $diamondLevel           = 0; // track karo kon sa diamond level hai (1st, 2nd, 3rd...)
    
                        // Remark: track karo kis associate ko last slab diya
                        $lastSlabGivenToAssocId = $from_associate_id;
    
                        foreach ($hierarchy as $key => $value) {
                            $AmountCalculation      = new AmountCalculationNew();
                            $hierarchy_associate_id = $value;
    
                            if($list_Associate_Selling[$hierarchy_associate_id]->curDtRangeDirectQty == 0 && $list_Associate_Selling[$hierarchy_associate_id]->Consolidated_Ratio < 1 && ($list_Associate_Selling[$hierarchy_associate_id]->curDtRangeInDirectQty == $list_Associate_Selling[$hierarchy_associate_id]->Pl1curDtRangeTotalQty)){
                                continue;
                            }
    
                            $AmountCalculation->from_associate_id         = $from_associate_id;
                            $AmountCalculation->associate_id              = $hierarchy_associate_id;
                            $AmountCalculation->plot_id                   = $listPlotMaster[$i]->id;
                            $AmountCalculation->booking_date              = $listPlotMaster[$i]->sold_date;
                            $AmountCalculation->allotment_date            = $listPlotMaster[$i]->allotment_date;
                            $AmountCalculation->apf_date                  = $listPlotMaster[$i]->apf_date;
                            $AmountCalculation->qty                       = $originalQty;
                            $AmountCalculation->newQty                    = $newQty;
                            $AmountCalculation->give_extra_commission      = $listPlotMaster[$i]->give_extra_commission;
                            $AmountCalculation->allow_day_diff_commission  = $listPlotMaster[$i]->give_daydiff_commission;
                            $AmountCalculation->scheme_day_diff_master_id  = $listPlotMaster[$i]->scheme_day_diff_master_id;
                            $AmountCalculation->commi_slab                 = $list_Associate_Selling[$hierarchy_associate_id]->commissionValue;
                            $AmountCalculation->dayDiff                    = $AmountCalculation->getDayDiffAttribute();
                            $AmountCalculation->dayDiffPercent             = $AmountCalculation->getDayDiffPercentAttribute();
    
                            // Diamond detection
                            if($diamondSlabStarted == false){
                                if($list_Associate_Selling[$from_associate_id]->commissionValue >= 800){
                                    $diamondSlabStarted = true;
                                }
                                elseif($list_Associate_Selling[$hierarchy_associate_id]->TotalOverAllQty >= 10000 && $list_Associate_Selling[$from_associate_id]->commissionValue >= 800){
                                    $diamondSlabStarted = true;
                                } else {
                                    if($key > 0 && $list_Associate_Selling[$from_associate_id]->commissionValue >= 800 &&
                                        $list_Associate_Selling[$hierarchy[$key-1]]->commissionValue >= 800){
                                        $diamondSlabStarted = true;
                                    }
                                }
                            } else {
                                $AmountCalculation->allow_day_diff_commission = 0;
                            }
    
                            // ---------------------------------------------------
                            // Remark context snapshot (slab_diff se pehle)
                            // ---------------------------------------------------
                            $remarkLastSlabBeforeThis = $lastSlabGiven_CommSlab;
                            $remarkLastSlabToAssocId  = $lastSlabGivenToAssocId;
                            $remarkTier               = ($diamondSlabStarted && $list_Associate_Selling[$hierarchy_associate_id]->TotalOverAllQty >= 10000)
                                                        ? 'Diamond Slab (ABPS & Above)'
                                                        : 'Normal';
                            $remarkEligibility        = '';
                            $remarkPowerLeg           = '';
                            $remarkDiamondSlab        = $diamondSlab;  // current diamond slab before decrement
                            $remarkDiamondLevel       = 0;             // will be set below if diamond
    
                            // ---------------------------------------------------
                            // Slab diff calculation
                            // ---------------------------------------------------
                            if($diamondSlabStarted == true && $list_Associate_Selling[$hierarchy_associate_id]->TotalOverAllQty >= 10000){
    
                                $diamondLevel++;                       // diamond level increment
                                $remarkDiamondLevel = $diamondLevel;   // remark ke liye
    
                                $AmountCalculation->allow_day_diff_commission = false;
    
                                if($list_Associate_Selling[$hierarchy_associate_id]->Consolidated_Eligibility == 1 || $list_Associate_Selling[$hierarchy_associate_id]->CurrentDtRange_Eligibility == 1){
                                    $remarkEligibility              = 'Yes';
                                    $AmountCalculation->slab_diff   = $diamondSlab;
                                    $AmountCalculation->extra_slab_diff = 0;
                                    $lastSlabGiven_CommSlab         = $diamondSlab;
                                    $lastSlabGiven_ExtraCommSlab    = 0;
                                    $lastSlabGivenToAssocId         = $hierarchy_associate_id;
                                } else {
                                    $remarkEligibility = 'No';
                                    if($key == 0){
                                        $inPowerLeg = $list_Associate_Selling[$from_associate_id]->inPowerLegOfSponsor == 1 ? 1 : 0;
                                    } else {
                                        $inPowerLeg = $list_Associate_Selling[$hierarchy[$key-1]]->inPowerLegOfSponsor == 1 ? 1 : 0;
                                    }
                                    if($inPowerLeg == 1){
                                        $remarkPowerLeg = 'Yes';
                                        if($balance_slab_diff > 0){
                                            $AmountCalculation->slab_diff = ($diamondSlab * $lastSlabGiven_CommSlab) + ($balance_last_diamond_slab * $balance_slab_diff);
                                            $balance_last_diamond_slab    = $diamondSlab;
                                            $balance_slab_diff            = 1 - $list_Associate_Selling[$hierarchy_associate_id]->Consolidated_Ratio;
                                        } else {
                                            $AmountCalculation->slab_diff = $diamondSlab * ($list_Associate_Selling[$hierarchy_associate_id]->Consolidated_Ratio);
                                            $balance_last_diamond_slab    = $diamondSlab;
                                            $balance_slab_diff            = 1 - $list_Associate_Selling[$hierarchy_associate_id]->Consolidated_Ratio;
                                        }
                                        $lastSlabGivenToAssocId = $hierarchy_associate_id;
                                    } else {
                                        $remarkPowerLeg                     = 'No';
                                        $AmountCalculation->slab_diff       = $diamondSlab;
                                        $AmountCalculation->extra_slab_diff = 0;
                                        $lastSlabGiven_CommSlab             = $diamondSlab;
                                        $lastSlabGiven_ExtraCommSlab        = 0;
                                        $lastSlabGivenToAssocId             = $hierarchy_associate_id;
                                    }
                                }
    
                                $diamondSlab -= 25;
                                if($diamondSlabStarted == true && $diamondSlab < 35){
                                    $diamondSlab = 35;
                                }
    
                            } else if ($list_Associate_Selling[$hierarchy_associate_id]->TotalOverAllQty >= 0){
    
                                if($list_Associate_Selling[$hierarchy_associate_id]->Consolidated_Eligibility == 1 || $list_Associate_Selling[$hierarchy_associate_id]->CurrentDtRange_Eligibility == 1){
                                    $remarkEligibility              = 'Yes';
                                    $AmountCalculation->slab_diff   = $list_Associate_Selling[$hierarchy_associate_id]->commissionValue - $lastSlabGiven_CommSlab;
                                    $lastSlabGiven_CommSlab         = $list_Associate_Selling[$hierarchy_associate_id]->commissionValue;
                                    $lastSlabGivenToAssocId         = $hierarchy_associate_id;
    
                                    if($tmp_commission == true){
                                        $AmountCalculation->ExComm      = (200 * $originalQty * ($AmountCalculation->commi_slab - $last_associat_actual_comm_slab)) / 800;
                                        $last_associat_actual_comm_slab = $AmountCalculation->commi_slab;
                                    } else {
                                        $AmountCalculation->ExComm = 0;
                                    }
                                } else {
                                    $remarkEligibility = 'No';
                                    if($list_Associate_Selling[$hierarchy_associate_id]->inPowerLegOfSponsor == 1){
                                        $remarkPowerLeg                 = 'Yes';
                                        $AmountCalculation->slab_diff   = ($list_Associate_Selling[$hierarchy_associate_id]->commissionValue - $lastSlabGiven_CommSlab) * ($list_Associate_Selling[$hierarchy_associate_id]->Consolidated_Ratio);
                                        $lastSlabGiven_CommSlab        += $AmountCalculation->slab_diff;
                                        $lastSlabGivenToAssocId         = $hierarchy_associate_id;
                                    } else {
                                        $remarkPowerLeg                 = 'No';
                                        $AmountCalculation->slab_diff   = $list_Associate_Selling[$hierarchy_associate_id]->commissionValue - $lastSlabGiven_CommSlab;
                                        $lastSlabGiven_CommSlab         = $list_Associate_Selling[$hierarchy_associate_id]->commissionValue;
                                        $lastSlabGivenToAssocId         = $hierarchy_associate_id;
                                    }
                                }
                            }
    
                            // ---------------------------------------------------
                            // Remark context set karo — assignAllVariable se pehle
                            // ---------------------------------------------------
                            $AmountCalculation->remarkTier                = $remarkTier;
                            $AmountCalculation->remarkEligibility         = $remarkEligibility;
                            $AmountCalculation->remarkPowerLeg            = $remarkPowerLeg;
                            $AmountCalculation->remarkLastSlabBeforeThis  = $remarkLastSlabBeforeThis;
                            $AmountCalculation->remarkLastSlabToAssocId   = $remarkLastSlabToAssocId;
                            $AmountCalculation->remarkConsolidatedRatio   = $list_Associate_Selling[$hierarchy_associate_id]->Consolidated_Ratio;
                            $AmountCalculation->remarkExtraPercent        = $listPlotMaster[$i]->give_extra_commission == 1 ? $listPlotMaster[$i]->extra_commission_percent : 1;
                            $AmountCalculation->remarkDiamondSlab         = $remarkDiamondSlab;   // current fixed slab before decrement
                            $AmountCalculation->remarkDiamondLevel        = $remarkDiamondLevel;  // 1st, 2nd, 3rd level
                            $AmountCalculation->remarkExtraCommLastSlab   = $last_associat_actual_comm_slab; // ExComm ke liye last actual slab
    
                            $AmountCalculation->assignAllVariable();
    
                            $this->listAmountCalculationInDr->push(clone $AmountCalculation);
    
                            if($list_Associate_Selling[$hierarchy_associate_id]->TotalOverAllQty >= 10000){
                                $diamondSlabStarted = true;
                            }
                        }
                    }
                }
            }
        } catch (\Exception $ex) {
            Log::error('Calculation InDirectCommission error: ' . $ex->getMessage());
            return response()->json(['error' => 'Calculation InDirectCommission failed'], 500);
        }
    }
    

    // public function calculateInDirectCommission($list_Associate_Selling,$listPlotMaster){

    //     try {
    //         $hierarchy = [];
              
    //          if(count($listPlotMaster) > 0){
    //             for ($i=0; $i < count($listPlotMaster); $i++) { 
                    
    //                 $from_associate_id = $listPlotMaster[$i]->last_hold_book_by_associate;
                     
    //                 $lastSlabGiven_CommSlab = $list_Associate_Selling[$from_associate_id]->commissionValue;
    //                 $lastSlabGiven_ExtraCommSlab = $list_Associate_Selling[$from_associate_id]->extraCommission;

    //                 $hierarchy = explode(',',$listPlotMaster[$i]->associate_list);
                  
                    
    //                 if($hierarchy && count($hierarchy) > 0){
    //                     $diamondSlabStarted = false;
    //                     $diamondSlab = 100;
    //                     $balance_slab_diff = 0;
                        

    //                     foreach ($hierarchy as $key => $value) {
    //                         $AmountCalculation = new AmountCalculationNew();
    //                         $hierarchy_associate_id = $value;
                             
    //                         $AmountCalculation->from_associate_id = $from_associate_id;
    //                         $AmountCalculation->associate_id = $hierarchy_associate_id;
    //                         $AmountCalculation->plot_id = $listPlotMaster[$i]->id;
    //                         $AmountCalculation->booking_date = $listPlotMaster[$i]->sold_date;
    //                         $AmountCalculation->allotment_date = $listPlotMaster[$i]->allotment_date;
    //                         $AmountCalculation->qty = $listPlotMaster[$i]->brokergeable_size;
    //                         $AmountCalculation->give_extra_commission = $listPlotMaster[$i]->give_extra_commission;
    //                         // $AmountCalculation->eligible_for_comission = $attributes['eligible_for_comission'] ?? false;
    //                         // $AmountCalculation->pin_id = $attributes['pin_id'] ?? 0;
    //                         // $AmountCalculation->pin_level = $attributes['pin_level'] ?? null;
    //                         // $AmountCalculation->commi_slab = $attributes['commi_slab'] ?? '0';
    //                         // $AmountCalculation->ex_commi_slab = $attributes['ex_commi_slab'] ?? '0';
    //                         // $AmountCalculation->slab_diff = $attributes['slab_diff'] ?? '0';
    //                         // $thiAmountCalculations->extra_slab_diff = $attributes['extra_slab_diff'] ?? '0';
    //                         $AmountCalculation->dayDiff = $AmountCalculation->getDayDiffAttribute();
    //                         $AmountCalculation->dayDiffPercent = $AmountCalculation->getDayDiffPercentAttribute();
    //                         // $AmountCalculation->comm = $attributes['comm'] ?? '0'
    //                         // $AmountCalculation->ExComm= $attributes['ExComm'] ?? '0';;

    //                         // change karna hai
                            
    //                         if($key == 0 && $list_Associate_Selling[$hierarchy_associate_id]->TotalOverAllQty >= 10000){
    //                             $diamondSlabStarted = true;
    //                         }

    //                         if( $diamondSlabStarted== true &&  $list_Associate_Selling[$hierarchy_associate_id]->TotalOverAllQty >= 10000){
    //                             // echo 'above 10000';
                                
    //                             if($list_Associate_Selling[$hierarchy_associate_id]->Consolidated_Eligibility == 1 || $list_Associate_Selling[$hierarchy_associate_id]->CurrentDtRange_Eligibility == 1){
    //                                  $AmountCalculation->slab_diff = $diamondSlab;
    //                                 $AmountCalculation->extra_slab_diff = 0;
    //                                 $lastSlabGiven_CommSlab = $diamondSlab;
    //                                 $lastSlabGiven_ExtraCommSlab = 0;
    //                             }
    //                             else{
    //                                 if($list_Associate_Selling[$hierarchy_associate_id]->inPowerLegOfSponsor == 1){
    //                                     if($balance_slab_diff > 0){
    //                                         $AmountCalculation->slab_diff = ($diamondSlab * $lastSlabGiven_CommSlab) + ($balance_last_diamond_slab *$balance_slab_diff ) ;
    //                                         $balance_last_diamond_slab = $diamondSlab;
    //                                         $balance_slab_diff = 1 -  $list_Associate_Selling[$hierarchy_associate_id]->Consolidated_Ratio;
    //                                     }
    //                                     else{
    //                                         $AmountCalculation->slab_diff = $diamondSlab * ($list_Associate_Selling[$hierarchy_associate_id]->Consolidated_Ratio);
    //                                         //$lastSlabGiven_CommSlab = $list_Associate_Selling[$hierarchy_associate_id]->Consolidated_Ratio ;
    //                                         $balance_last_diamond_slab = $diamondSlab;
    //                                         $balance_slab_diff = 1 -  $list_Associate_Selling[$hierarchy_associate_id]->Consolidated_Ratio;
    //                                     }

    //                                 }
    //                                 else
    //                                 {
    //                                     $AmountCalculation->slab_diff = $diamondSlab ; //* ($list_Associate_Selling[$hierarchy_associate_id]->Consolidated_Ratio);
    //                                     $AmountCalculation->extra_slab_diff = 0;
    //                                     $lastSlabGiven_CommSlab = $diamondSlab;
    //                                     $lastSlabGiven_ExtraCommSlab = 0;
    //                                 }
    //                             }       
    //                                 $diamondSlab -= 25;
                                   
    //                                 if($diamondSlabStarted == true && $diamondSlab < 35){
    //                                    $diamondSlab = 35;
    //                                }
                                    
    //                         }
    //                         //else if ($list_Associate_Selling[$hierarchy_associate_id]->TotalOverAllQty >= 5000){
    //                         else if ($list_Associate_Selling[$hierarchy_associate_id]->TotalOverAllQty >= 0){
    //                             //    echo 'above 5000';
    //                             //  print_r($list_Associate_Selling[$hierarchy_associate_id]); 
    //                             // eigibility criteria 1 hai toh
    //                             if($list_Associate_Selling[$hierarchy_associate_id]->Consolidated_Eligibility == 1 || $list_Associate_Selling[$hierarchy_associate_id]->CurrentDtRange_Eligibility == 1){
                                
    //                                 $AmountCalculation->slab_diff = $list_Associate_Selling[$hierarchy_associate_id]->commissionValue - $lastSlabGiven_CommSlab;
    //                                 $AmountCalculation->extra_slab_diff = $list_Associate_Selling[$hierarchy_associate_id]->extraCommission - $lastSlabGiven_ExtraCommSlab;
    //                                 $lastSlabGiven_CommSlab = $list_Associate_Selling[$hierarchy_associate_id]->commissionValue;
    //                                 $lastSlabGiven_ExtraCommSlab = $list_Associate_Selling[$hierarchy_associate_id]->extraCommission;
                                
    //                                 //$lastSlabGiven_Commission = $AmountCalculation->slab_diff;
    //                                 //$lastSlabGiven_ExtraCommSlab = $AmountCalculation->extra_slab_diff;
    //                             }
    //                             else{
    //                                 if($list_Associate_Selling[$hierarchy_associate_id]->inPowerLegOfSponsor == 1){
    //                                     $AmountCalculation->slab_diff = ($list_Associate_Selling[$hierarchy_associate_id]->commissionValue - $lastSlabGiven_CommSlab) * ($list_Associate_Selling[$hierarchy_associate_id]->Consolidated_Ratio);

    //                                     $AmountCalculation->extra_slab_diff = ($list_Associate_Selling[$hierarchy_associate_id]->extraCommission - $lastSlabGiven_ExtraCommSlab) * ($list_Associate_Selling[$hierarchy_associate_id]->Consolidated_Ratio);

    //                                     $lastSlabGiven_CommSlab += $AmountCalculation->slab_diff;
    //                                     $lastSlabGiven_ExtraCommSlab = $AmountCalculation->extra_slab_diff;


    //                                 }else{
    //                                     $AmountCalculation->slab_diff = $list_Associate_Selling[$hierarchy_associate_id]->commissionValue - $lastSlabGiven_CommSlab;
    //                                     $AmountCalculation->extra_slab_diff = $list_Associate_Selling[$hierarchy_associate_id]->extraCommission - $lastSlabGiven_ExtraCommSlab;

    //                                     $lastSlabGiven_CommSlab = $list_Associate_Selling[$hierarchy_associate_id]->commissionValue;
    //                                     $lastSlabGiven_ExtraCommSlab = $list_Associate_Selling[$hierarchy_associate_id]->extraCommission;
    //                                 }
    //                             }
    //                         }

    //                         $AmountCalculation->assignAllVariable();
                             
    //                         $this->listAmountCalculationInDr->push(clone $AmountCalculation); 
    //                         // echo 'sdas';
    //                         // print_r($this->listAmountCalculationInDr); die;
                            
    //                         if($list_Associate_Selling[$hierarchy_associate_id]->TotalOverAllQty >= 10000)
    //                         {
    //                             $diamondSlabStarted = true;
    //                         }
    //                     }
    //                 }
                    
    //             }
    //             // print_r($this->listAmountCalculationInDr); die;

    //         }
    //     } catch (\Exception $ex) {
    //         Log::error('Calculation InDirectCommission error: ' . $ex->getMessage());
    //         return response()->json(['error' => 'Calculation InDirectCommission failed'], 500);
    //     }
    // }

    private function bulkInsertToSql($endDate)
    {
        $listAmountCalculationForInsert = $this->listAmountCalculationInDr;
        // print_r($listAmountCalculationForInsert); die;
        $process_date = $endDate;
        // Placeholder, awaiting C# code
        try {
            if (!$listAmountCalculationForInsert instanceof \Illuminate\Support\Collection) {
                Log::error('combinedAmountCalculation is not a collection', [
                    'value' => $listAmountCalculationForInsert
                ]);
                throw new \Exception('Invalid collection for bulk upload');
            }

            Log::info('Starting bulk upload', [
                'record_count' => $listAmountCalculationForInsert->count()
            ]);

            $listAmountCalculationForInsert->chunk(1000)->each(function ($chunk) use (&$process_date) {
                DB::beginTransaction();
               
                try {
                    $data = $chunk->map(function ($item) use (&$process_date){
                        
                        return [
                            'FromAssociateId' => $item->from_associate_id ?? 0,
                            'AssociateId' => $item->associate_id ?? null,
                            'PlotId' => $item->plot_id ?? null,
                            'allowDayDiffCommission' => $item->allow_day_diff_commission ?? null,
                            'dayDiff' => $item->dayDiff ?? null,
                            'QtyGiven' => $item->newQty ?? '0',
                            'GiveExtraCommission' => $item->give_extra_commission ?? '0',
                            'EligibleForComission' => $item->eligible_for_comission ?? '0',
                            'Commi_Slab' => $item->commi_slab ?? '0',
                            'ExCommi_Slab' => $item->ex_commi_slab ?? '0',
                            'SlabDiff' => $item->slab_diff ?? '0',
                            'ExtraSlabDiff' => $item->extra_slab_diff ?? null,
                            'Comm' => $item->comm ?? null,
                            'ExComm' => $item->ExComm ?? null,
                            'OthComm' => $item->OthComm ?? null,
                            'TotalComm' => $item->TotalComm ?? null,
                            'SaleType' => $item->SaleType ?? null,
                            'processed_on' => $process_date,
                            'CalculationRemark' => $item->CalculationRemark ?? null
                        ];
                    })->toArray();

                    tbl_commission_detail::insert($data);

                    DB::commit();
                    Log::info('Chunk inserted successfully', ['chunk_size' => count($data)]);
                } catch (\Exception $e) {
                    DB::rollBack();
                    Log::error('Error during chunk insert', [
                        'error' => $e->getMessage(),
                        'chunk_size' => count($chunk)
                    ]);
                    throw $e; // Rethrow to handle or stop processing
                }
            });

            Log::info('Bulk upload completed successfully');
        } catch (\Exception $e) {
            Log::error('Bulk upload failed', ['error' => $e->getMessage()]);
            throw $e; 
        }
    }

   
}