<?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\Services\CalculationNew;
use App\Services\AssociateSelling;
use App\Services\AssociateExtraDetails;
use App\Services\LastQtrPinLevel;
use App\Services\NewCalculation;
use App\Services\AmountCalculation;
use App\Services\LastGiven;
class CommissionCalculationController extends Controller
{
    private $clsReportType; // Placeholder for clsReportType
    private Collection $listStockMaster; // Placeholder for TblStockMaster
    private Collection $listAssociateSelling;
    private Collection $listSchemeMaster; // Placeholder for TblSchemeMaster
    private Collection $listOldPinLevel;
    private array $dictAssociateSelling;
    private array $dictCalculation; // Placeholder for clsCalculation
    private array $dictCalculationNew;
    private array $dictAssociateExtraDetails;
    private Collection $listAmountCalculationDr;
    private Collection $listAmountCalculationInDr;
    private Collection $listCombineDirectIndirect;
    private Collection $hierarchy;
    private Collection $clsLastGivenSlab;
    private Collection $listSummary;
    private Collection $dtSummary; // Representing DataTable as Collection
    private Carbon $endDate;
    private array $tempEligibilityCheckMonthly;

    public function __construct()
    {
        $this->clsReportType = new \stdClass(); // Placeholder, awaiting clsReportType
        $this->listStockMaster = new Collection();
        $this->listAssociateSelling = new Collection();
        $this->listSchemeMaster = new Collection();
        $this->listOldPinLevel = new Collection();
        $this->dictAssociateSelling = [];
        $this->dictCalculation = []; // Placeholder, awaiting clsCalculation
        $this->dictCalculationNew = [];
        $this->dictAssociateExtraDetails = [];
        $this->listAmountCalculationDr = new Collection();
        $this->listAmountCalculationInDr = new Collection();
        $this->listCombineDirectIndirect = new Collection();
        $this->hierarchy = new Collection();
        $this->clsLastGivenSlab = new Collection();
        $this->listSummary = new Collection();
        $this->dtSummary = new Collection();
        $this->endDate = Carbon::now();
        $this->tempEligibilityCheckMonthly = [];
    }

    public function BrokerageCalculation(Request $request)
    {
       
        if (is_logged_in()) {
            $submitbtn = $request->submitbtn;

            $month_names = array("January","February","March","April","May","June","July","August","September","October","November","December");
            $start_year = 2020;
            $monthname = $request->monthname;
            $year = $request->year;
            
            if($submitbtn == "Calculate"){
               
                $timestamp    = strtotime("$monthname $year");
                 $startDate = date('Y-m-01', $timestamp);
                $endDate  = date('Y-m-t', $timestamp);
                
                $startCalculation = $this->startCalculation($startDate,$endDate);
                die;
            }

            return view('Admin/Report/BrokerageCalculation',compact('month_names','start_year','monthname','year'));
        }
    }

    public function startCalculation(string $startDate, string $endDate)
    {
       
        try {
           
            // Initialize collections
            $this->listAmountCalculationDr = new Collection();
            $this->listAmountCalculationInDr = new Collection();
            $this->listCombineDirectIndirect = new Collection();
            $this->dictAssociateSelling = [];
            $this->dictAssociateExtraDetails = [];
            
            // 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->listStockMaster = DB::table('tbl_plot_master')
                ->whereBetween('sold_date', [$startDate, $endDate])
                // ->where('is_re_sale', false)
                ->orderBy('sold_date')
                ->get();
            
            $this->listAssociateSelling = collect(DB::select('CALL calculateTotalSalesUserWise_WithComm(?, ?, ?)', [1, $startDate, $endDate]))
                ->map(function ($item) {
                    return new AssociateSelling([
                        'associate_id' => $item->AssociateId,
                        'name' => $item->name ?? null,
                        'cur_dt_range_direct_qty' => isset($item->curDtRangeDirectQty) ? (string) $item->curDtRangeDirectQty : null,
                        'cur_dt_range_indirect_qty' => isset($item->curDtRangeInDirectQty) ? (string) $item->curDtRangeInDirectQty : null,
                        'total_direct_qty' => isset($item->TotalDirectQty) ? (string) $item->TotalDirectQty : null,
                        'total_indirect_qty' => isset($item->TotalInDirectQty) ? (string) $item->TotalInDirectQty : null,
                        'total_over_all_qty' => isset($item->TotalOverAllQtyWithOpeningStock) ? (string) $item->TotalOverAllQtyWithOpeningStock : null,
                        'pin_id' => $item->PinId ?? null,
                        'pinname' => $item->PinName ?? null,
                        'comission' => isset($item->commissionValue) ? (string) $item->commissionValue : null,
                        'extra_comission' => isset($item->extraCommission) ? (string) $item->extraCommission : null,
                        'pl1_associate_id' => $item->Pl1AssociateId ?? null,
                        'pl1_name' => $item->Pl1AssociateName ?? null,
                        // 'pl1_cur_dt_range_direct_qty' => isset($item->PL1curDtRangeDirectQty) ? (string) $item->PL1curDtRangeDirectQty : null,
                        // 'pl1_cur_dt_range_indirect_qty' => isset($item->PL1curDtRangeInDirectQty) ? (string) $item->PL1curDtRangeInDirectQty : null,
                        'pl1_total_direct_qty' => isset($item->Pl1TotalOverAllQtyWithOpeningStock) ? (string) $item->Pl1TotalOverAllQtyWithOpeningStock : null,
                        'pl1_total_indirect_qty' => 0,
                        'processed_on' => $endDate ?? null,
                        // 'hierarchy' => $item->hierarchy ?? null,
                        // 'qty_commission_given_power_leg1' => isset($item->QtyCommissionGivenPowerLeg1) ? (string) $item->QtyCommissionGivenPowerLeg1 : null,
                    ]);
                });
                
            $this->listSchemeMaster = DB::table('tbl_schemes')->get();
          
            $this->listAssociateExtraDetails = DB::table('tbl_associates as am')
                ->select([
                    'am.id as associate_id',
                    'am.sponsor_id as introducer_code',
                    'am2.name as introducer_name',
                    'am.father_name as father_or_husband_name',
                     DB::raw("CASE WHEN am.active = 'Active' THEN 1 ELSE 0 END as is_active"),
                    'am.rera_no as rera_no',
                    'tm.id as team_name'
                ])
                ->leftJoin('tbl_associates as am2', 'am.sponsor_id', '=', 'am2.id')
                ->leftJoin('tbl_teams as tm', 'am.team_id', '=', 'tm.id')
                ->get()
                ->map(fn ($item) => new AssociateExtraDetails((array) $item));
            

            // change krna hai sql query se
            // $sql = $this->clsReportType->getSqlQuery('LastAchievedPinLevel', '2001-01-01', '2001-01-01'); // Placeholder
            $sql = "Select id as associate_id , coalesce(pin_id,0) as old_pin_id , '' as old_pin_level , '2025-06-30' as processed_on 
            from tbl_associates a where a.deleted_at is null" ;
            $this->listOldPinLevel = collect(DB::select($sql))->map(fn ($item) => new LastQtrPinLevel((array) $item));
            
            $dictPinRange = DB::table('tbl_pins')
                ->where('active',1)->wherenull('deleted_at')
                ->pluck('fromRange', 'id')
                ->map(fn ($value) => (string) $value)
                ->toArray();
           
            // Update pin levels
            $listOldPinLevel = $this->listOldPinLevel;
            $this->listAssociateSelling = $this->listAssociateSelling->map(function ($item) use ($dictPinRange, $listOldPinLevel) {
                $associatePinRangeStart = $dictPinRange[$item->pin_id] ?? '0';
                $powerLegTotalQty1 = bcadd(
                    $item->pl1_total_direct_qty ?? '0',
                    $item->pl1_total_indirect_qty ?? '0',
                    2
                );
                $threshold = bcdiv(bcmul($associatePinRangeStart, '33', 2), '100', 2);
                if (bccomp(bcsub($item->total_over_all_qty ?? '0', $powerLegTotalQty1, 2), $threshold, 2) < 0) {
                    $oldPin = $this->listOldPinLevel->firstWhere('associate_id', $item->associate_id);
                    if ($oldPin) {
                        $item->pinname = $oldPin->old_pin_level;
                        $item->pin_id = $oldPin->old_pin_id;
                    }
                }
                return $item;
            });
            
            $this->dictAssociateSelling = $this->listAssociateSelling->keyBy('associate_id')->toArray();
          
            $this->dictAssociateExtraDetails = $this->listAssociateExtraDetails->keyBy('associate_id')->toArray();
             
            // Map to CalculationNew instances
            // $this->dictCalculationNew = $this->listAssociateSelling->mapWithKeys(function ($item) {
            //     $calculationNew = new CalculationNew($item);
            //     return [$item->associate_id => $calculationNew];
            // })->toArray();
           //print_r($this->dictCalculationNew[1]->getKeyPointPercentageAttribute());die;

           //$this->dictCalculationNew = 

             $this->dictCalculationNew = $this->listAssociateSelling->map(function ($f) {
                //$calculation = new Calculation($f);
                
                $calculationNew = new CalculationNew($f);
                return ([
                    'associate_id' => $f->associate_id,
                    'name' => $f->name,
                    'cur_dt_range_direct_qty' => $f->cur_dt_range_direct_qty,
                    'cur_dt_range_indirect_qty' => $f->cur_dt_range_indirect_qty,
                    'total_direct_qty' => $f->total_direct_qty,
                    'total_indirect_qty' => $f->total_indirect_qty,
                    'total_over_all_qty' => $f->total_over_all_qty,
                    'pin_id' => $f->pin_id,
                    'pinname' => $f->pinname,
                    'comission' => $f->comission,
                    'extra_comission' => $f->extra_comission,
                    'pl1_associate_id' => $f->pl1_associate_id,
                    'pl1_name' => $f->pl1_name,
                    'pl1_cur_dt_range_direct_qty' => $f->pl1_cur_dt_range_direct_qty,
                    'pl1_cur_dt_range_indirect_qty' => $f->pl1_cur_dt_range_indirect_qty,
                    'pl1_total_direct_qty' => $f->pl1_total_direct_qty,
                    'pl1_total_indirect_qty' => $f->pl1_total_indirect_qty,
                    // 'pl2_associate_id' => $f->pl2_associate_id,
                    // 'pl2_name' => $f->pl2_name,
                    // 'pl2_cur_dt_range_direct_qty' => $f->pl2_cur_dt_range_direct_qty,
                    // 'pl2_cur_dt_range_indirect_qty' => $f->pl2_cur_dt_range_indirect_qty,
                    // 'pl2_total_direct_qty' => $f->pl2_total_direct_qty,
                    // 'pl2_total_indirect_qty' => $f->pl2_total_indirect_qty,
                    'processed_on' => $f->processed_on,
                    'hierarchy' => $f->hierarchy,
                    'power_leg_total_qty1' => $calculationNew->getPowerLegTotalQty1Attribute(),
                    // 'power_leg_total_qty2' => $calculationNew->power_leg_total_qty2,
                    'key_point_percentage' => $calculationNew->getKeyPointPercentageAttribute(),
                    'cond_achieved' => $calculationNew->getCondAchievedAttribute(),
                    'max_qty_commission_power_leg1' => $calculationNew->getMaxQtyCommissionPowerLeg1Attribute(),
                    // 'max_qty_commission_power_leg2' => $calculationNew->max_qty_commission_power_leg2,
                    'qty_commission_given_power_leg1' => $calculationNew->getQtyCommissionGivenPowerLeg1Attribute(),
                    // 'qty_commission_given_power_leg2' => $calculationNew->qty_commission_given_power_leg2,
                    'cq_total_sale' => $f->total_over_all_qty,
                    'cq_total_sale_power_leg1' => $f->pl1_total_direct_qty,
                    // 'cq_total_sale_power_leg2' => $calculationNew->cq_total_sale_power_leg2,
                    'is_eligible_for_commission' => $calculationNew->getIsEligibleForCommissionAttribute()
                ]);
            })->keyBy('associate_id')->toArray();
            
            $startTime = now();
            
            $this->calculateDirectCommission();
          
            $newCalculation = new NewCalculation(); // Placeholder for clsNewCalculation
            $this->listAmountCalculationInDr = $newCalculation->calculateInDirectCommission(
                $this->listStockMaster,
                $this->dictAssociateSelling,
                $this->listSchemeMaster,
                $this->dictAssociateExtraDetails,
                $this->dictCalculationNew,
                $this->endDate->year
            ); // Placeholder
            //  print_r($this->listAmountCalculationDr);die;
            $this->loadSummary(); // Placeholder
            $allRecord = $this->listAmountCalculationDr->concat($this->listAmountCalculationInDr);
            $this->bulkInsertToSql($allRecord,$endDate); // Placeholder

            return response()->json(['message' => 'Calculation completed successfully']);
        } catch (\Exception $ex) {
            Log::error('Calculation error: ' . $ex->getMessage());
            return response()->json(['error' => 'Calculation failed'], 500);
        }
    }

    private function calculateDirectCommission()
    {
        try {
            foreach ($this->listStockMaster as $i => $stock) {
                $tempId = $stock->last_hold_book_by_associate;

                // Debug point for i == 153
                if ($i === 153) {
                    Log::debug('Debug point: index 153 reached');
                }
                 Log::debug('Debug point: TEst '.$i);
                if (!$this->dictAssociateExtraDetails[$tempId]->is_active) {
                    continue;
                }

                $this->listAmountCalculationDr->push(new AmountCalculation([
                    'from_associate_id' => $tempId,
                    'associate_id' => $tempId,
                    'pin_id' => $this->dictAssociateSelling[$tempId]->pin_id,
                    'pin_level' => $this->dictAssociateSelling[$tempId]->pinname,
                    'commi_slab' => $this->dictAssociateSelling[$tempId]->comission,
                    'ex_commi_slab' => $this->dictAssociateSelling[$tempId]->extra_comission,
                    'slab_diff' => $this->dictAssociateSelling[$tempId]->comission,
                    'extra_slab_diff' => $this->dictAssociateSelling[$tempId]->extra_comission,
                    'give_extra_commission' => (bool) $stock->give_extra_commission,
                    'qty' => (string) $stock->brokergeable_size,
                    'scheme_name' => $this->listSchemeMaster->firstWhere('scheme_id', $stock->scheme_id)->scheme_name ?? null,
                    'plot_id' => $stock->id,
                    'plot_name' => $stock->plot_name,
                    'allotment_date' => ($stock->allotment_date),
                    'booking_date' => ($stock->sold_date),
                    'associate_name' => $this->dictAssociateSelling[$tempId]->name,
                    'comm' =>  bcmul($stock->brokergeable_size, $this->dictAssociateSelling[$tempId]->comission, 2),
                    'ExComm' => (bool) $stock->give_extra_commission ? bcmul($stock->brokergeable_size, $this->dictAssociateSelling[$tempId]->extra_comission, 2) : '0'
                    
                ]));
            }
            
        } catch (\Exception $ex) {
            Log::error('CalculateDirectCommission error: ' . $ex->getMessage());
            throw $ex;
        }
    }

    private function loadSummary()
    {
      
        
        // Placeholder, awaiting C# code
    }

    private function bulkInsertToSql(Collection $listAmountCalculationForInsert,$endDate)
    {
       
        $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->pin_id ?? null,
                            'allowDayDiffCommission' => $item->allow_day_diff_commission ?? null,
                            'dayDiff' => $item->dayDiff ?? null,
                            'QtyGiven' => $item->qty ?? '0',
                            'GiveExtraCommission' => $item->give_extra_commission ?? '0',
                            'EligibleForComission' => $item->eligible_for_comission ?? '0',
                            'PinId' => $item->pin_id ?? '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
                        ];
                    })->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; 
        }
    }
}