<?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('isReSale', 0)
                ->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, 17]))) {
                        $tmp_commission = true;
                    } else {
                        $tmp_commission = false;
                    }

                    if ($hierarchy && count($hierarchy) > 0) {

                        $diamondSlabStarted = false;
                        $diamondSlab        = 100;
                        $diamondLevel       = 0;

                        // GLOBAL balance tracking for Diamond tier
                        // Reset tab hoga: NOT eligible + Power Leg + ratio < 1 dobara aye
                        $balance_tracking_started = false;
                        $balance_prev_slab        = 0;
                        $balance_prev_ratio       = 0;
                        $balance_prev_balance     = 0;

                        // Remark: track karo kis associate ko last slab diya
                        $lastSlabGivenToAssocId = $from_associate_id;

                        // Skip log: jo associate skip hue unka record agle wale ke remark mein jayega
                        $skippedAssociatesLog = [];

                        foreach ($hierarchy as $key => $value) {

                            $AmountCalculation      = new AmountCalculationNew();
                            $hierarchy_associate_id = $value;

                            // -------------------------------------------------------
                            // SKIP CHECK
                            // -------------------------------------------------------
                            if (
                                  !empty($hierarchy_associate_id) &&
                                isset($list_Associate_Selling[$hierarchy_associate_id]) &&
                                $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
                                // $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)
                            ) {
                                // Skipped associate ka log banao — agle wale ke remark mein jayega
                                $skippedAssociatesLog[] = [
                                    'associate_id'       => $hierarchy_associate_id,
                                    'DirectQty'          => $list_Associate_Selling[$hierarchy_associate_id]->curDtRangeDirectQty,
                                    'Consolidated_Ratio' => $list_Associate_Selling[$hierarchy_associate_id]->Consolidated_Ratio,
                                    'InDirectQty'        => $list_Associate_Selling[$hierarchy_associate_id]->curDtRangeInDirectQty,
                                    'Pl1TotalQty'        => $list_Associate_Selling[$hierarchy_associate_id]->Pl1curDtRangeTotalQty,
                                ];
                                continue;
                            }

                            // -------------------------------------------------------
                            // Basic plot/booking info
                            // -------------------------------------------------------
                            $AmountCalculation->from_associate_id         = $from_associate_id;
                            $AmountCalculation->associate_id              = $hierarchy_associate_id;
                            $AmountCalculation->plot_id                   = $listPlotMaster[$i]->id;
                            $AmountCalculation->plot_category             = $plot_category;
                            $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;
                            $remarkDiamondLevel        = 0;
                            $remarkBalanceCarryForward = false;
                            $remarkBalancePrevSlab     = $balance_prev_slab;
                            $remarkBalancePrevRatio    = $balance_prev_ratio;
                            $remarkBalancePrevBalance  = $balance_prev_balance;

                            // Skipped associates ka log — is associate ke remark mein daalenge
                            $remarkSkippedBefore = $skippedAssociatesLog;
                            $skippedAssociatesLog = []; // Reset after attaching to this associate

                            // -------------------------------------------------------
                            // SLAB DIFF CALCULATION
                            // -------------------------------------------------------

                            if ($diamondSlabStarted == true && $list_Associate_Selling[$hierarchy_associate_id]->TotalOverAllQty >= 10000) {

                                // --- Diamond Tier ---
                                $diamondLevel++;
                                $remarkDiamondLevel = $diamondLevel;
                                $AmountCalculation->allow_day_diff_commission = false;

                                // Eligibility check
                                $isEligible = (
                                    $list_Associate_Selling[$hierarchy_associate_id]->Consolidated_Eligibility == 1
                                    || $list_Associate_Selling[$hierarchy_associate_id]->CurrentDtRange_Eligibility == 1
                                );

                                // Power leg check
                                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;
                                }

                                $currentRatio = $list_Associate_Selling[$hierarchy_associate_id]->CurrentDtRange_Ratio;

                                // -------------------------------------------------------
                                // CASE A: ELIGIBLE
                                // -------------------------------------------------------
                                if ($isEligible) {
                                    $remarkEligibility = 'Yes';
                                    $remarkPowerLeg    = $inPowerLeg == 1 ? 'Yes' : 'No';

                                    if ($balance_tracking_started) {
                                        // Balance carry forward
                                        $remarkBalanceCarryForward    = true;
                                        $AmountCalculation->slab_diff = ($diamondSlab * $balance_prev_ratio)
                                                                        + ($balance_prev_slab * $balance_prev_balance);
                                        $lastSlabGivenToAssocId       = $hierarchy_associate_id;
                                        // Update prev slab, ratio/balance same rehte hain
                                        $balance_prev_slab = $diamondSlab;
                                    } else {
                                        // No balance active — full slab
                                        $AmountCalculation->slab_diff = $diamondSlab;
                                        $lastSlabGiven_CommSlab       = $diamondSlab;
                                        $lastSlabGivenToAssocId       = $hierarchy_associate_id;
                                    }

                                    $AmountCalculation->extra_slab_diff = 0;

                                // -------------------------------------------------------
                                // CASE B: NOT ELIGIBLE + POWER LEG
                                // -------------------------------------------------------
                                } elseif ($inPowerLeg == 1) {
                                    $remarkEligibility = 'No';
                                    $remarkPowerLeg    = 'Yes';

                                    if ($currentRatio < 1) {
                                        // Power Leg + ratio < 1 → partial + balance tracking RESET & fresh start
                                        $AmountCalculation->slab_diff = $diamondSlab * $currentRatio;
                                        $lastSlabGivenToAssocId       = $hierarchy_associate_id;

                                        // Fresh start (reset purana balance)
                                        $balance_tracking_started = true;
                                        $balance_prev_slab        = $diamondSlab;
                                        $balance_prev_ratio       = $currentRatio;
                                        $balance_prev_balance     = 1 - $currentRatio;

                                    } else {
                                        // Power Leg + ratio >= 1
                                        if ($balance_tracking_started) {
                                            // Balance carry forward
                                            $remarkBalanceCarryForward    = true;
                                            $AmountCalculation->slab_diff = ($diamondSlab * $balance_prev_ratio)
                                                                            + ($balance_prev_slab * $balance_prev_balance);
                                            $lastSlabGivenToAssocId       = $hierarchy_associate_id;
                                            // Update prev slab only; ratio/balance carry same
                                            $balance_prev_slab = $diamondSlab;
                                        } else {
                                            // No balance active — full slab
                                            $AmountCalculation->slab_diff = $diamondSlab;
                                            $lastSlabGivenToAssocId       = $hierarchy_associate_id;
                                        }
                                    }

                                // -------------------------------------------------------
                                // CASE C: NOT ELIGIBLE + NOT POWER LEG
                                // Full slab milega, balance reset NAHI hoga
                                // -------------------------------------------------------
                                } else {
                                    $remarkEligibility            = 'No';
                                    $remarkPowerLeg               = 'No';
                                    $AmountCalculation->slab_diff = $diamondSlab;
                                    $lastSlabGiven_CommSlab       = $diamondSlab;
                                    $lastSlabGivenToAssocId       = $hierarchy_associate_id;
                                    // NOTE: balance_tracking_started reset NAHI hoga yahan
                                    // Balance aage ke power leg associate ke liye zinda rahega
                                }

                                // Decrement diamondSlab after each diamond level
                                $diamondSlab -= 25;
                                if ($diamondSlab < 35) {
                                    $diamondSlab = 35;
                                }

                            } else if ($list_Associate_Selling[$hierarchy_associate_id]->TotalOverAllQty >= 0) {

                                // --- Normal Tier ---
                                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 {
                                    // Power leg check
                                    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;
                                    }

                                    $remarkEligibility = 'No';

                                    if ($inPowerLeg == 1) {
                                        $remarkPowerLeg               = 'Yes';
                                        $AmountCalculation->slab_diff = ($list_Associate_Selling[$hierarchy_associate_id]->commissionValue - $lastSlabGiven_CommSlab)
                                                                        * ($list_Associate_Selling[$hierarchy_associate_id]->CurrentDtRange_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;
                                    }

                                    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;
                                    }
                                }
                            }

                            // -------------------------------------------------------
                            // Remark context set karo — assignAllVariable se pehle
                            // -------------------------------------------------------
                            $AmountCalculation->remarkTier                   = $remarkTier;
                            $AmountCalculation->remarkEligibility            = $remarkEligibility;
                            $AmountCalculation->remarkPowerLeg               = $remarkPowerLeg;
                            $AmountCalculation->remarkLastSlabBeforeThis     = $remarkLastSlabBeforeThis;
                            $AmountCalculation->remarkLastSlabToAssocId      = $remarkLastSlabToAssocId;
                            $AmountCalculation->remarkCurrentDtRange_Ratio   = $list_Associate_Selling[$hierarchy_associate_id]->CurrentDtRange_Ratio;
                            $AmountCalculation->remarkExtraPercent           = $listPlotMaster[$i]->give_extra_commission == 1
                                                                                ? $listPlotMaster[$i]->extra_commission_percent
                                                                                : 1;
                            $AmountCalculation->remarkDiamondSlab            = $remarkDiamondSlab;
                            $AmountCalculation->remarkDiamondLevel           = $remarkDiamondLevel;
                            $AmountCalculation->remarkExtraCommLastSlab      = $last_associat_actual_comm_slab;
                            $AmountCalculation->remarkBalanceCarryForward    = $remarkBalanceCarryForward;
                            $AmountCalculation->remarkBalancePrevSlab        = $remarkBalancePrevSlab;
                            $AmountCalculation->remarkBalancePrevRatio       = $remarkBalancePrevRatio;
                            $AmountCalculation->remarkBalancePrevBalance     = $remarkBalancePrevBalance;

                            // Skipped associates log — audit ke liye
                            // Format: "SKIPPED BEFORE THIS: AssocId=XX (DirectQty=0, Ratio=0.50, InDirect=500, Pl1Total=500)"
                            if (!empty($remarkSkippedBefore)) {
                                $skipLines = [];
                                foreach ($remarkSkippedBefore as $skipped) {
                                    $skipLines[] = "AssocId={$skipped['associate_id']}"
                                        . " [DirectQty={$skipped['DirectQty']}"
                                        . ", Ratio={$skipped['Consolidated_Ratio']}"
                                        . ", InDirectQty={$skipped['InDirectQty']}"
                                        . ", Pl1TotalQty={$skipped['Pl1TotalQty']}]"
                                        . " → SKIPPED (no direct sale, ratio<1, all qty from PL1)";
                                }
                                $AmountCalculation->remarkSkippedAssociates = implode(' | ', $skipLines);
                            } else {
                                $AmountCalculation->remarkSkippedAssociates = '';
                            }

                            $AmountCalculation->assignAllVariable();
                            $this->listAmountCalculationInDr->push(clone $AmountCalculation);

                            // Diamond flag update (end of loop)
                            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);
        }
    }

    // Changed on  11-06-2026 
    // New conditions are Diamond tier mein:

    // 1. NOT eligible + Power Leg + ratio < 1  -> diamondSlab × ratio  (balance tracking start),  if already started then fresh start
    // 2. NOT eligible + Power Leg + ratio >= 1 -> balance carry forward (agar active) ya full slab
    // 3. NOT eligible + NOT Power Leg          -> full diamondSlab
    // 4. Eligible                              -> balance carry forward ya full slab

    // dekho,
    // ratio ka matlab hota hai ki apan usko kuch kam karke de rahe hai, 
    // agar power leg nahi hai, to pura hi jayega, means ratio se multiply hi nahi hoga,

    // Reset tab hoga, jab vapas, not eligible ho + power leg ho + <1 ratio ho
    
    /* 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,17]))){
                    $tmp_commission = true;
                } else {
                    $tmp_commission = false;
                }

                if($hierarchy && count($hierarchy) > 0){
                    $diamondSlabStarted     = false;
                    $diamondSlab            = 100;
                    $diamondLevel           = 0;

                    // GLOBAL balance tracking for Diamond tier
                    // Ek baar kisi ka bhi ratio < 1 aya (after diamond started), toh ye ON ho jayega
                    $balance_tracking_started = false;  // Has any ratio < 1 been encountered in diamond?
                    $balance_prev_slab        = 0;      // Previous diamond slab value
                    $balance_prev_ratio       = 0;      // Previous ratio used
                    $balance_prev_balance     = 0;      // Previous balance to carry

                    // 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;
                        $remarkDiamondLevel       = 0;
                        $remarkBalanceCarryForward = false;
                        $remarkBalancePrevSlab     = $balance_prev_slab;
                        $remarkBalancePrevRatio    = $balance_prev_ratio;
                        $remarkBalancePrevBalance  = $balance_prev_balance;

                        // ---------------------------------------------------
                        // Slab diff calculation
                        // ---------------------------------------------------
                        if($diamondSlabStarted == true && $list_Associate_Selling[$hierarchy_associate_id]->TotalOverAllQty >= 10000){

                            $diamondLevel++;
                            $remarkDiamondLevel = $diamondLevel;

                            $AmountCalculation->allow_day_diff_commission = false;

                            // Check eligibility
                            $isEligible = ($list_Associate_Selling[$hierarchy_associate_id]->Consolidated_Eligibility == 1 || 
                                           $list_Associate_Selling[$hierarchy_associate_id]->CurrentDtRange_Eligibility == 1);

                            // $isEligible = $list_Associate_Selling[$hierarchy_associate_id]->CurrentDtRange_Eligibility == 1;


                            // Check power leg
                            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;
                            }

                            // Get current ratio
                            $currentRatio = $list_Associate_Selling[$hierarchy_associate_id]->CurrentDtRange_Ratio;

                            // --- GLOBAL CHECK: Agar kisi ka bhi ratio < 1, activate balance tracking ---
                            if(!$isEligible && $currentRatio < 1){
                                // Agar balance tracking already active thi, reset karo
                                if($balance_tracking_started){
                                    $balance_tracking_started = false;
                                }
                                
                                // Fresh calculation
                                $AmountCalculation->slab_diff = $diamondSlab * $currentRatio;
                                $lastSlabGivenToAssocId       = $hierarchy_associate_id;

                                // START new balance tracking globally
                                $balance_tracking_started = true;
                                $balance_prev_slab        = $diamondSlab;
                                $balance_prev_ratio       = $currentRatio;
                                $balance_prev_balance     = 1 - $currentRatio;

                                $remarkEligibility = 'No';
                                $remarkPowerLeg    = $inPowerLeg == 1 ? 'Yes' : 'No';
                            }
                            // --- CASE 1: ELIGIBLE ---
                            elseif($isEligible){
                                $remarkEligibility = 'Yes';
                                
                                // Check if balance tracking is active
                                if($balance_tracking_started){
                                    // Use balance carry forward
                                    $remarkBalanceCarryForward    = true;
                                    $AmountCalculation->slab_diff = ($diamondSlab * $balance_prev_ratio) + ($balance_prev_slab * $balance_prev_balance);
                                    $lastSlabGiven_CommSlab       = $AmountCalculation->slab_diff;
                                    $lastSlabGivenToAssocId       = $hierarchy_associate_id;
                                } else {
                                    // No balance active, full slab
                                    $AmountCalculation->slab_diff = $diamondSlab;
                                    $lastSlabGiven_CommSlab       = $diamondSlab;
                                    $lastSlabGivenToAssocId       = $hierarchy_associate_id;
                                }
                                
                                $AmountCalculation->extra_slab_diff = 0;
                                $lastSlabGiven_ExtraCommSlab        = 0;

                                // // Balance RESET (eligible ne le liya, carry forward khatam)
                                // $balance_tracking_started = false;
                                $balance_prev_slab        = $diamondSlab;
                                // $balance_prev_ratio       = 0;
                                // $balance_prev_balance     = 0;

                            }
                            // --- CASE 2: NOT ELIGIBLE + POWER LEG + Ratio >= 1 ---
                            elseif($inPowerLeg == 1){
                                $remarkEligibility = 'No';
                                $remarkPowerLeg    = 'Yes';

                                // --- CASE 2A: Balance tracking active (ratio >= 1) ---
                                if($balance_tracking_started){
                                    $remarkBalanceCarryForward    = true;
                                    $AmountCalculation->slab_diff = ($diamondSlab * $balance_prev_ratio) + ($balance_prev_slab * $balance_prev_balance);
                                    $lastSlabGivenToAssocId       = $hierarchy_associate_id;

                                    // Balance continues (update slab, keep ratio & balance same)
                                    $balance_prev_slab = $diamondSlab;
                                    // balance_prev_ratio stays same
                                    // balance_prev_balance stays same

                                }
                                // --- CASE 2B: Ratio >= 1 but balance tracking NOT started yet ---
                                else {
                                    // Full slab (normal case before any ratio < 1 encountered)
                                    $AmountCalculation->slab_diff = $diamondSlab;
                                    $lastSlabGivenToAssocId       = $hierarchy_associate_id;
                                }

                            }
                            // --- CASE 3: NOT ELIGIBLE + NOT POWER LEG (full slab, balance reset) ---
                            else {
                                $remarkEligibility                  = 'No';
                                $remarkPowerLeg                     = 'No';
                                $AmountCalculation->slab_diff       = $diamondSlab;
                                $AmountCalculation->extra_slab_diff = 0;
                                $lastSlabGiven_CommSlab             = $diamondSlab;
                                $lastSlabGiven_ExtraCommSlab        = 0;
                                $lastSlabGivenToAssocId             = $hierarchy_associate_id;

                                // Balance reset
                                $balance_tracking_started = false;
                                $balance_prev_slab        = 0;
                                $balance_prev_ratio       = 0;
                                $balance_prev_balance     = 0;
                            }

                            $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 {
                                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;
                                }
                                $remarkEligibility = 'No';
                                if($inPowerLeg == 1){
                                    $remarkPowerLeg                 = 'Yes';
                                    $AmountCalculation->slab_diff   = ($list_Associate_Selling[$hierarchy_associate_id]->commissionValue - $lastSlabGiven_CommSlab) * ($list_Associate_Selling[$hierarchy_associate_id]->CurrentDtRange_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;
                                }
                                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;
                                }
                            }
                        }

                        // ---------------------------------------------------
                        // Remark context set karo — assignAllVariable se pehle
                        // ---------------------------------------------------
                        $AmountCalculation->remarkTier                   = $remarkTier;
                        $AmountCalculation->remarkEligibility            = $remarkEligibility;
                        $AmountCalculation->remarkPowerLeg               = $remarkPowerLeg;
                        $AmountCalculation->remarkLastSlabBeforeThis     = $remarkLastSlabBeforeThis;
                        $AmountCalculation->remarkLastSlabToAssocId      = $remarkLastSlabToAssocId;
                        $AmountCalculation->remarkCurrentDtRange_Ratio   = $list_Associate_Selling[$hierarchy_associate_id]->CurrentDtRange_Ratio;
                        $AmountCalculation->remarkExtraPercent           = $listPlotMaster[$i]->give_extra_commission == 1 ? $listPlotMaster[$i]->extra_commission_percent : 1;
                        $AmountCalculation->remarkDiamondSlab            = $remarkDiamondSlab;
                        $AmountCalculation->remarkDiamondLevel           = $remarkDiamondLevel;
                        $AmountCalculation->remarkExtraCommLastSlab      = $last_associat_actual_comm_slab;
                        $AmountCalculation->remarkBalanceCarryForward    = $remarkBalanceCarryForward;
                        $AmountCalculation->remarkBalancePrevSlab        = $remarkBalancePrevSlab;
                        $AmountCalculation->remarkBalancePrevRatio       = $remarkBalancePrevRatio;
                        $AmountCalculation->remarkBalancePrevBalance     = $remarkBalancePrevBalance;

                        $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);
        }
    } */

    

  

    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; 
        }
    }

   
}