<?php

namespace App\Http\Controllers;

use Carbon\Carbon;
use App\Models\Fees;
use App\Models\User;
use App\Models\Status;
use App\Models\ServiceName;
use Illuminate\Support\Str;
use App\Models\WaterPayment;
use App\Models\AdvertisementTypes;
use Illuminate\Support\Facades\DB;
use App\Http\Controllers\Controller;
use App\Services\Pwd\GrantingTelecomService;
use App\Models\MedicalHealth\GrantNursingLicense;
use App\Models\CityStructure\CityStructurePartMap;
use App\Models\MedicalHealth\RenewalNursingLicense;
use App\Models\CityStructure\CityStructureZoneCertificate;
use App\Services\ConstructionDepartment\RoadCuttingService;


class PaymentSummary extends Controller
{
    public function paymentoptions($serviceId = null, $id)
    {
        $id = decrypt($id);

        $serviceName = ServiceName::select('model', 'service_id', 'service_name', 'id')
            ->where('service_id', $serviceId)
            ->first();

        if (!$serviceName) {
            abort(404, 'Service not found');
        }

        $modelClassName = $serviceName->model;
        if (!class_exists($modelClassName)) {
            abort(500, 'Associated model class not found.');
        }

        $item = $modelClassName::where('id', $id)->first();

        if (!$item) {
            abort(404, 'Item not found');
        }

        $userdata = User::find($item->user_id);

        $amount = 0;

        if (isset($item->plot_no, $item->size_hoarding, $item->type_hoarding)) {
            $length = (float) $item->plot_no;
            $width = (float) $item->size_hoarding;
            $area = $length * $width;

            $typerate = AdvertisementTypes::find($item->type_hoarding);

            if ($typerate) {
                $bannerrate = $area * $typerate->rate;
                $yearrate = $bannerrate * 12;
                $rateper = $yearrate * 0.2;
                $sumrate = $yearrate + $rateper;
                $halfsum = $sumrate / 2;
                $totalrate = $sumrate + $halfsum;
            } else {
                $totalrate = 0;
            }
        } else {
            $totalrate = 0;
        }
        $service_iditem = $item->service_id ?? null;
        if ($service_iditem == 75 && !empty($item->registration_details_marriage_date_in_english)) {
            $givenDate = Carbon::parse($item->registration_details_marriage_date_in_english);
            $today = Carbon::today();
            $daysDifference = $today->diffInDays($givenDate);

            if ($daysDifference >= 1 && $daysDifference <= 90) {
                $amount = 50;
            } elseif ($daysDifference <= 365) {
                $amount = 150;
            } elseif ($daysDifference <= 9999) {
                $amount = 250;
            }
        } elseif ($service_iditem == 2005) {
            $amount = $totalrate;
        } elseif ($service_iditem == 2032) {
            $amount = GrantingTelecomService::calculateGrossTotal($item->id);
        }elseif ($service_iditem == 187) {
            $amount = RoadCuttingService::calculateGrossTotal($item->id);
        }elseif ($service_iditem == 186) {
            $typerate = CityStructurePartMap::find($item->id);

            if ($typerate) {
                $maprate = DB::table('partmapstypes')
                    ->where('id', $typerate->maptype)
                    ->value('rate');
                $amount = $maprate ?? 0;
            } else {
                $amount = 0;
            }
        } elseif ($service_iditem == 185) {
            $typerate = CityStructureZoneCertificate::where('application_no', $item->application_no)->first();

            if ($typerate) {
                $maprate = DB::table('partmapstypes')
                    ->where('id', $typerate->maptype)
                    ->value('rate');

                $amount = $maprate ?? 0;
            } else {
                $amount = 0;
            }
        } elseif ($service_iditem == 2024) {
            $rate = GrantNursingLicense::find($item->id);

            if ($rate) {
                $bedrate = DB::table('healths_new')
                    ->where('range', $rate->no_of_beds)
                    ->value('new');

                $amount = $bedrate ?? 0;
            } else {
                $amount = 0;
            }
        } elseif ($service_iditem == 2025) {
            $rate = RenewalNursingLicense::find($item->id);
            if ($rate) {
                $bedrate = DB::table('healths_renewal')
                    ->where('range', $rate->no_of_beds)
                    ->value('renewal');

                $amount = $bedrate ?? 0;
            } else {
                $amount = 0;
            }
        } elseif ($service_iditem == 23) {  // water ownership change
            $amount = $item->total_connection * $this->getWaterFine($service_iditem, $item->Water_Connection_Usage ?? null, $item->subUsage ?? null);
        } elseif ($service_iditem == 26) {

            $amount = $this->calculateWaterFineDabavAmount($service_iditem, $item);
        } elseif ($service_iditem == 8922) {
            $amount = $this->calculateWaterConnectionRepairAmount($service_iditem, $item);
        } elseif ($service_iditem == 8923) {
            $amount = $this->calculateWaterTransferPermissionAmount($service_iditem, $item);
        } elseif ($service_iditem == 22) {
            $amount = $this->calculateWaterConnectionAmount($service_iditem, $item);
        } elseif ($service_iditem == 23) {
            $amount = $this->calculateChangeInAmount($service_iditem, $item);
        } elseif ($service_iditem == 15) {
            $amount = $this->calculateWaterReConnectionAmount($service_iditem, $item);
        } elseif ($service_iditem == 2033) {
            $amount = $this->calculateWaterTreeProtectionAmount($service_iditem, $item);
        } elseif ($service_iditem == 16) {
            $amount = $this->calculateTemporaryPermanentDisconnectionWaterSupplyAmount($service_iditem, $item);
        } elseif ($service_iditem == 17) {
            $amount = $this->calculateUsageChangeAmount($service_iditem, $item);
        } elseif ($service_iditem == 25) {
            $amount = $this->calculateDefectiveWaterMeterAmount($service_iditem, $item);
        } elseif ($service_iditem == 21) {

            $rate = $this->getWaterFine(
                $service_iditem,
                $item->water_connection_use ?? null,
                $item->water_connection_sub_use ?? null
            );

            // Step 3: Initialize variables
            $jrTotal = 0;
            $noOfTap = $item->no_of_tap ?? 1;

            // Step 4: Calculate J.R. total safely
            if (!empty($item) && isset($item->jramount) && isset($item->running_meter)) {
                $jrAmount = floatval($item->jramount);
                $runningMeter = floatval($item->running_meter);
                $jrTotal = $jrAmount * $runningMeter;
            }
            $amount = ($noOfTap * floatval($rate)) + $jrTotal;
        }
        elseif ($service_iditem == 30) {     // plumber licence
            $amount = 5000;
        } elseif ($service_iditem == 31) {     // plumber licence renew
            $amount = 3000;
        }
        // elseif ($service_iditem == 188)
        //     {     // water drainage
        //     $amount = 25000;
        // }
        elseif ($service_iditem == 20) {     // unavaibility of water supply
            $amount = 5000;
        } elseif ($service_iditem == 19) {     // unavaibility of water supply
            $amount = 5000;
        } elseif ($service_iditem == 8926) {     // unavaibility of water supply
            $amount = 5000;
        } elseif ($service_iditem == 8927) {     // unavaibility of water supply
            $amount = 5000;
        }
        // elseif ($service_iditem == 188) {     // unavaibility of water supply
        //     $amount = 25000;
        // }
        else {
            // $fees = Fees::where('service_name_id', $serviceName->id)->first();
            $fees = Fees::where('service_name_id', $serviceName->service_id)->first();
            $amount = $fees->fees ?? 0;
        }


       if ($service_iditem == 8925 || $service_iditem == 8921) {
            // Death & Birth Certificate calculation
            $noOfCertificates = (int) $item->no_of_certificate;
            $amount = $amount * $noOfCertificates;
        }

        $amount = round($amount);
        $order_id = 'ORD-' . date('Ymd') . '-' . Str::random(10);
        $service = ServiceName::find($serviceId);

        $status = Status::where('service_id',$service_iditem)->where('table_id',$item->id)->first();
        $status->fees_amount =$amount;
        $status->save();
        return view('payment.index', compact(
            'item',
            'serviceId',
            'id',
            'userdata',
            'amount',
            'order_id',
            'serviceName'
        ));
    }


    function getWaterFine($id, $usage, $subUsage)
    {
        $query = WaterPayment::where('service_id', $id)->where('type', $usage);

        if ($subUsage) {
            $query->where('sub_type', $subUsage);
        }
        return $query->value('rate');
    }
    function calculateWaterFineDabavAmount($serviceId, $item)
    {
        $waterPaymentQuery = DB::table('water_payments')
            ->where('service_id', $serviceId);

        if (!empty($item->Water_Connection_Usage)) {
            $waterPaymentQuery->where('type', $item->Water_Connection_Usage);
        }


        if (!empty($item->panycha_type_add)) {
            $waterPaymentQuery->where('panyacha_type', $item->panycha_type_add);
        }

        $baseRate = floatval($waterPaymentQuery->value('rate') ?? 0);

        $connectionRecord = $item;

        if ($connectionRecord) {
            $baseRate *= floatval($connectionRecord->total_connection ?? 1);
        }
        $jrAmountTotal = 0;
        if ($connectionRecord && !empty($connectionRecord->jramountone) && !empty($connectionRecord->running_meterone)) {
            $jrAmountTotal = $connectionRecord->jramountone * $connectionRecord->running_meterone;
        }

        $finalAmount = $baseRate ?? 0;

        $amount = ($finalAmount + $jrAmountTotal);

        return $amount;
    }




    function calculateWaterConnectionRepairAmount($serviceId, $item)
    {
        try {
            // Fetch matching record from water_payments table
            $paymentData = DB::table('water_payments')
                ->where('service_id', $serviceId)
                ->where('type', $item->resident_type)
                ->where('panyacha_type', $item->tap_size)
                ->first();

            // Get rate safely, default 0 if not found
            $rate = $paymentData ? floatval($paymentData->rate) : 0;

            // Get comment multiplier, default 1 if not provided
            $commentValue = isset($item->comment) ? floatval($item->comment) : 1;

            // Calculate final amount
            $finalAmount = $rate * $commentValue;

            return $finalAmount;
        } catch (\Exception $e) {
            // Return 0 on error
            return 0;
        }
    }




    function calculateWaterTransferPermissionAmount($serviceId, $item)
    {
        try {
            // Fetch matching record from water_payments table
            $paymentData = DB::table('water_payments')
                ->where('service_id', $serviceId)
                ->where('type', $item->resident_type)
                ->where('panyacha_type', $item->tap_size)
                ->first();

            // Get rate safely, default 0 if not found
            $rate = $paymentData ? floatval($paymentData->rate) : 0;

            // Get comment multiplier, default 1 if not provided
            $commentValue = isset($item->comment) ? floatval($item->comment) : 1;

            // Calculate final amount
            $finalAmount = $rate * $commentValue;

            return $finalAmount;
        } catch (\Exception $e) {
            // Return 0 on error
            return 0;
        }
    }


    // function calculateWaterConnectionAmount_old($item)
    // {
    //     // Ensure both values exist, otherwise default to 0
    //     $waterConnectionSize = floatval($item->Water_Connection_Size ?? 0);
    //     $totalConnection     = floatval($item->total_connection ?? 0);

    //     // Final amount calculation and round it
    //     $amount = round($waterConnectionSize * $totalConnection);

    //     return $amount;
    // }


    public function calculateWaterConnectionAmount($serviceId, $item)
{
    $waterConnectionSize = floatval($item->Water_Connection_Size ?? 0);
    $totalConnection     = floatval($item->total_connection ?? 0);
    $amount = round($waterConnectionSize * $totalConnection);
    return $amount;
}


    function calculateChangeInAmount($serviceId, $item)
    {

        $waterConnectionSize = floatval($item->Water_Connection_Size_Count ?? 0);
        $totalConnection     = floatval($item->total_connection ?? 0);


        $amount = round($waterConnectionSize * $totalConnection);

        return $amount;
    }


    function calculateWaterReConnectionAmount($serviceId, $item)
    {
        // Get values safely (default 0 if missing)
        $totalConnection     = floatval($item->total_connection ?? 0);
        $waterConnectionSize = floatval($item->Water_Connection_Size_Count ?? 0);

        // Multiply and round to nearest integer
        $amount = round($totalConnection * $waterConnectionSize);

        return $amount;
    }


    public function calculateTemporaryPermanentDisconnectionWaterSupplyAmount($serviceId, $item)
{
    $noOfExistingCon     = floatval($item->no_of_existing_con ?? 0);
    $waterConnectionSize = floatval($item->Water_Connection_Size_Count ?? 0);

    $amount = round($noOfExistingCon * $waterConnectionSize);

    return $amount;
}


    function calculateUsageChangeAmount($serviceId, $item)
    {
        // Get values safely (default 0 if missing)
        $totalConnection     = floatval($item->total_connection ?? 0);
        $waterConnectionSize = floatval($item->Water_Connection_Size_Count ?? 0);

        // Multiply and round to nearest integer
        $amount = round($totalConnection * $waterConnectionSize);

        return $amount;
    }





public function calculateDefectiveWaterMeterAmount($serviceId, $item)
{
    $plot_no = floatval($item->plot_no ?? 0);
    $meter_reading = floatval($item->meter_reading ?? 0);
    $amount = $plot_no * $meter_reading;
    return number_format($amount, 2, '.', '');
}


// public function calculateWaterTreeProtectionAmount_old($serviceId, $item)
// {
//     try {
        
//         $paymentData = DB::table('vrukshtypes')
//             ->where('service_id', $serviceId)
//             ->where('type', $item->type_application)
//             ->where('name', $item->reason_trim)
//             ->select('*')
//             ->selectRaw("CASE WHEN ? = 'होय' THEN yes_amount ELSE no_amount END as new_plant_amount", [$item->new_plant ?? 'नाही'])
//             ->selectRaw("CASE WHEN ? = 'होय' THEN yes_amount ELSE no_amount END as space_available_amount", [$item->space_available ?? 'नाही'])
//             ->selectRaw("CASE WHEN ? = 'होय' THEN yes_amount ELSE no_amount END as new_replant_amount", [$item->new_replant ?? 'नाही'])
//             ->selectRaw("CASE WHEN ? = 'होय' THEN yes_amount ELSE no_amount END as space_replant_amount", [$item->space_replant ?? 'नाही'])
//             ->first();

//         if (!$paymentData) {
//             return 0; // fallback if record not found
//         }

//         // Step 2: Get total number of trees
//         $treeCount = DB::table('tree_protections')
//             ->where('type_application', $item->type_application)
//             ->where('reason_trim', $item->reason_trim)
//             ->where('type_of_tree', $item->type_of_tree)
//             ->count();

//         // Step 3: Initialize total
//         $calculated_total = 0;

//         // ✅ Case 1: विकासकामातील झाडे तोडणे/ पुर्नरोपण करणे.
//         if ($item->type_application == "विकासकामातील झाडे तोडणे/ पुर्नरोपण करणे.") {

//             $paymentData->amount = $paymentData->amount ?? 0;

//             if ($item->reason_trim == "वृक्ष तोडणे") {
//                 $q1Amount = ($item->new_plant == 'होय')
//                     ? (float)$paymentData->yes_amount
//                     : (float)$paymentData->no_amount;

//                 $q2Amount = ($item->space_available == 'होय')
//                     ? (float)$paymentData->yes_amount
//                     : (float)$paymentData->no_amount;

//                 $baseTotal = ($q1Amount + $q2Amount) * (int)$item->type_of_tree;
//                 $amountTotal = (float)$paymentData->amount * (int)$item->type_of_tree;
//                 $calculated_total = $baseTotal + $amountTotal;

//             } elseif ($item->reason_trim == "वृक्ष पुनःरोपण") {
//                 $q1Amount = ($item->new_replant == 'होय')
//                     ? (float)$paymentData->yes_amount
//                     : (float)$paymentData->no_amount;

//                 $q2Amount = ($item->space_replant == 'होय')
//                     ? (float)$paymentData->yes_amount
//                     : (float)$paymentData->no_amount;

//                 $baseTotal = ($q1Amount + $q2Amount) * (int)$item->type_of_tree;
//                 $amountTotal = (float)$paymentData->amount * (int)$item->type_of_tree;
//                 $calculated_total = $baseTotal + $amountTotal;

//             } else {
//                 $calculated_total = (float)$paymentData->amount * (int)$item->type_of_tree;
//             }
//         }

//         // ✅ Case 2: सोसायटी / घरांच्या परिसरातील झाडे तोडणे / पुर्नरोपण / छाटणी करणे..
//         elseif ($item->type_application == "सोसायटी / घरांच्या परिसरातील झाडे तोडणे / पुर्नरोपण / छाटणी करणे..") {

//             $paymentData->amount = $paymentData->amount ?? 0;

//             if ($item->reason_trim == "वृक्ष तोडणे") {
//                 $q1Amount = ($item->new_plant == 'होय')
//                     ? (float)$paymentData->yes_amount
//                     : (float)$paymentData->no_amount;

//                 $q2Amount = ($item->space_available == 'होय')
//                     ? (float)$paymentData->yes_amount
//                     : (float)$paymentData->no_amount;

//                 $baseTotal = ($q1Amount + $q2Amount) * (int)$item->type_of_tree;
//                 $amountTotal = (float)$paymentData->amount * (int)$item->type_of_tree;
//                 $calculated_total = $baseTotal + $amountTotal;

//             } elseif ($item->reason_trim == "वृक्ष पुनःरोपण") {
//                 $q1Amount = ($item->new_replant == 'होय')
//                     ? (float)$paymentData->yes_amount
//                     : (float)$paymentData->no_amount;

//                 $q2Amount = ($item->space_replant == 'होय')
//                     ? (float)$paymentData->yes_amount
//                     : (float)$paymentData->no_amount;

//                 $baseTotal = ($q1Amount + $q2Amount) * (int)$item->type_of_tree;
//                 $amountTotal = (float)$paymentData->amount * (int)$item->type_of_tree;
//                 $calculated_total = $baseTotal + $amountTotal;

//             } else {
//                 $calculated_total = (float)$paymentData->amount * (int)$item->type_of_tree;
//             }
//         }

//         // ✅ Case 3: सोसायटी / घरांच्या परिसरातील झाडे तोडणे / पुर्नरोपण / छाटणी करणे. (one dot)
//         elseif ($item->type_application == "सोसायटी / घरांच्या परिसरातील झाडे तोडणे / पुर्नरोपण / छाटणी करणे.") {

//             if ($item->reason_trim == "वृक्षाची छाटणी करणे" || $item->reason_trim == "वृक्षांची छाटणी (फांद्या) उचलणे") {
//                 $rateTotal = (float)$paymentData->rate * (int)$item->type_of_tree;
//                 $amountTotal = (float)$paymentData->amount * (int)$item->type_of_tree;
//                 $calculated_total = $rateTotal + $amountTotal;
//             }
//             elseif ($item->reason_trim == "धोकादायक झाडे मुळासहित काढणे") {
//                 $rateTotal = (float)$paymentData->rate * (int)$item->type_of_tree;
//                 $amountTotal = (float)$paymentData->amount * (int)$item->type_of_tree;
//                 $calculated_total = $rateTotal + $amountTotal;
//             }
//             else {
//                 $yesNoTotal = ((float)$paymentData->yes_amount + (float)$paymentData->no_amount) * (int)$item->type_of_tree;
//                 $amountTotal = (float)$paymentData->amount * (int)$item->type_of_tree;
//                 $calculated_total = $yesNoTotal + $amountTotal;
//             }
//         }

//         // ✅ Default fallback
//         else {
//             $yesNoTotal = ((float)$paymentData->yes_amount + (float)$paymentData->no_amount) * (int)$item->type_of_tree;
//             $amountTotal = (float)$paymentData->amount * (int)$item->type_of_tree;
//             $calculated_total = $yesNoTotal + $amountTotal;
//         }

//         // Step 4: Final return
//         $paymentData->calculated_total = $calculated_total;

//         return round($paymentData->calculated_total);

//     } catch (\Exception $e) {
//         return 0;
//     }
// }


// public function calculateWaterTreeProtectionAmount_old($serviceId, $item)
// {
//     try {

//         // Step 1: Base data from vrukshtypes
//         $paymentData = DB::table('vrukshtypes')
//             ->where('service_id', $serviceId)
//             ->where('type', $item->type_application)
//             ->where('name', $item->reason_trim)
//             ->select('*')
//             ->selectRaw("CASE WHEN ? = 'होय' THEN yes_amount ELSE no_amount END as new_plant_amount", [$item->new_plant ?? 'नाही'])
//             ->selectRaw("CASE WHEN ? = 'होय' THEN yes_amount ELSE no_amount END as space_available_amount", [$item->space_available ?? 'नाही'])
//             ->selectRaw("CASE WHEN ? = 'होय' THEN yes_amount ELSE no_amount END as new_replant_amount", [$item->new_replant ?? 'नाही'])
//             ->selectRaw("CASE WHEN ? = 'होय' THEN yes_amount ELSE no_amount END as space_replant_amount", [$item->space_replant ?? 'नाही'])
//             ->first();

//         if (!$paymentData) {
//             return 0; // fallback if no matching record
//         }

//         // Step 2: Get total number of trees (for reference)
//         $treeCount = DB::table('tree_protections')
//             ->where('type_application', $item->type_application)
//             ->where('reason_trim', $item->reason_trim)
//             ->where('type_of_tree', $item->type_of_tree)
//             ->count();

//         // Step 3: Initialize total
//         $calculated_total = 0;

//         // ✅ Case 1: विकासकामातील झाडे तोडणे/ पुर्नरोपण करणे.
//         if ($item->type_application == "विकासकामातील झाडे तोडणे/ पुर्नरोपण करणे.") {

//             $paymentData->amount = $paymentData->amount ?? 0;

//             if ($item->reason_trim == "वृक्ष तोडणे") {
//                 $q1Amount = ($item->new_plant == 'होय') ? (float)$paymentData->yes_amount : (float)$paymentData->no_amount;
//                 $q2Amount = ($item->space_available == 'होय') ? (float)$paymentData->yes_amount : (float)$paymentData->no_amount;
//                 $baseTotal = ($q1Amount + $q2Amount) * (int)$item->type_of_tree;
//                 $amountTotal = (float)$paymentData->amount * (int)$item->type_of_tree;
//                 $calculated_total = $baseTotal + $amountTotal;

//             } elseif ($item->reason_trim == "वृक्ष पुनःरोपण") {
//                 $q1Amount = ($item->new_replant == 'होय') ? (float)$paymentData->yes_amount : (float)$paymentData->no_amount;
//                 $q2Amount = ($item->space_replant == 'होय') ? (float)$paymentData->yes_amount : (float)$paymentData->no_amount;
//                 $baseTotal = ($q1Amount + $q2Amount) * (int)$item->type_of_tree;
//                 $amountTotal = (float)$paymentData->amount * (int)$item->type_of_tree;
//                 $calculated_total = $baseTotal + $amountTotal;

//             } else {
//                 $calculated_total = (float)$paymentData->amount * (int)$item->type_of_tree;
//             }
//         }

//         // ✅ Case 2: सोसायटी / घरांच्या परिसरातील झाडे तोडणे / पुर्नरोपण / छाटणी करणे..
//         elseif ($item->type_application == "सोसायटी / घरांच्या परिसरातील झाडे तोडणे / पुर्नरोपण / छाटणी करणे..") {

//             $paymentData->amount = $paymentData->amount ?? 0;

//             if ($item->reason_trim == "वृक्ष तोडणे") {
//                 $q1Amount = ($item->new_plant == 'होय') ? (float)$paymentData->yes_amount : (float)$paymentData->no_amount;
//                 $q2Amount = ($item->space_available == 'होय') ? (float)$paymentData->yes_amount : (float)$paymentData->no_amount;
//                 $baseTotal = ($q1Amount + $q2Amount) * (int)$item->type_of_tree;
//                 $amountTotal = (float)$paymentData->amount * (int)$item->type_of_tree;
//                 $calculated_total = $baseTotal + $amountTotal;

//             } elseif ($item->reason_trim == "वृक्ष पुनःरोपण") {
//                 $q1Amount = ($item->new_replant == 'होय') ? (float)$paymentData->yes_amount : (float)$paymentData->no_amount;
//                 $q2Amount = ($item->space_replant == 'होय') ? (float)$paymentData->yes_amount : (float)$paymentData->no_amount;
//                 $baseTotal = ($q1Amount + $q2Amount) * (int)$item->type_of_tree;
//                 $amountTotal = (float)$paymentData->amount * (int)$item->type_of_tree;
//                 $calculated_total = $baseTotal + $amountTotal;

//             } else {
//                 $calculated_total = (float)$paymentData->amount * (int)$item->type_of_tree;
//             }
//         }

//         // ✅ Case 3: सोसायटी / घरांच्या परिसरातील झाडे तोडणे / पुर्नरोपण / छाटणी करणे. (single dot)
//         elseif ($item->type_application == "सोसायटी / घरांच्या परिसरातील झाडे तोडणे / पुर्नरोपण / छाटणी करणे.") {

//             // Case: वृक्षाची छाटणी / फांद्या उचलणे
//             if ($item->reason_trim == "वृक्षाची छाटणी करणे" || $item->reason_trim == "वृक्षांची छाटणी (फांद्या) उचलणे") {
//                 $rateTotal = (float)$paymentData->rate * (int)$item->type_of_tree;
//                 $amountTotal = (float)$paymentData->amount * (int)$item->type_of_tree;
//                 $calculated_total = $rateTotal + $amountTotal;
//             }

//             // ✅ Case: धोकादायक झाडे मुळासहित काढणे
//             elseif ($item->reason_trim == "धोकादायक झाडे मुळासहित काढणे") {

//                 // 1️⃣ Get height from tree_protections
//                $treeHeight = DB::table('tree_protections')
//                             ->where('id', $item->id ?? 0)
//                             ->value('tree_height');

//                 // 2️⃣ Match height with vrukshtypes sub_name
//                 $heightData = DB::table('vrukshtypes')
//                     ->where('service_id', $serviceId)
//                     ->where('type', $item->type_application)
//                     ->where('name', $item->reason_trim)
//                     ->where('sub_name', 'LIKE', "%{$treeHeight}%")
//                     ->select('rate', 'amount')
//                     ->first();

//                 // 3️⃣ Fallback to base if no specific height data found
//                 $rate = $heightData->rate ?? $paymentData->rate ?? 0;
//                 $amount = $heightData->amount ?? $paymentData->amount ?? 0;

//                 // 4️⃣ Calculate total
//                 $rateTotal = (float)$rate * (int)$item->type_of_tree;
//                 $amountTotal = (float)$amount * (int)$item->type_of_tree;
//                 $calculated_total = $rateTotal + $amountTotal;
//             }

//             // Default inner case
//             else {
//                 $yesNoTotal = ((float)$paymentData->yes_amount + (float)$paymentData->no_amount) * (int)$item->type_of_tree;
//                 $amountTotal = (float)$paymentData->amount * (int)$item->type_of_tree;
//                 $calculated_total = $yesNoTotal + $amountTotal;
//             }
//         }

//         // ✅ Default fallback (if no case matches)
//         else {
//             $yesNoTotal = ((float)$paymentData->yes_amount + (float)$paymentData->no_amount) * (int)$item->type_of_tree;
//             $amountTotal = (float)$paymentData->amount * (int)$item->type_of_tree;
//             $calculated_total = $yesNoTotal + $amountTotal;
//         }

//         // Step 4: Return Final Rounded Total
//         return round($calculated_total);

//     } catch (\Exception $e) {
//         // Log the error for debugging
//         \Log::error('Tree Protection Calculation Error: ' . $e->getMessage());
//         return 0;
//     }
// }


public function calculateWaterTreeProtectionAmount($serviceId, $item)
{
    try {
        // Step 1: Fetch record from vrukshtypes
        $paymentData = DB::table('vrukshtypes')
            ->where('service_id', $serviceId)
            ->where('type', $item->type_application)
            ->where('name', $item->reason_trim)
            ->select('*')
            ->selectRaw("CASE WHEN ? = 'होय' THEN yes_amount ELSE no_amount END as new_plant_amount", [$item->new_plant ?? 'नाही'])
            ->selectRaw("CASE WHEN ? = 'होय' THEN yes_amount ELSE no_amount END as space_available_amount", [$item->space_available ?? 'नाही'])
            ->selectRaw("CASE WHEN ? = 'होय' THEN yes_amount ELSE no_amount END as new_replant_amount", [$item->new_replant ?? 'नाही'])
            ->selectRaw("CASE WHEN ? = 'होय' THEN yes_amount ELSE no_amount END as space_replant_amount", [$item->space_replant ?? 'नाही'])
            ->first();

        if (!$paymentData) {
            return 0; // fallback if record not found
        }

        // Step 2: Get total number of trees
        $treeCount = DB::table('tree_protections')
            ->where('type_application', $item->type_application)
            ->where('reason_trim', $item->reason_trim)
            ->where('type_of_tree', $item->type_of_tree)
            ->count();

        // Step 3: Initialize total
        $calculated_total = 0;

        // ✅ Case 1: विकासकामातील झाडे तोडणे/ पुर्नरोपण करणे.
        if ($item->type_application == "विकासकामातील झाडे तोडणे/ पुर्नरोपण करणे.") {

            $paymentData->amount = $paymentData->amount ?? 0;

            if ($item->reason_trim == "वृक्ष तोडणे") {
                $q1Amount = ($item->new_plant == 'होय')
                    ? (float)$paymentData->yes_amount
                    : (float)$paymentData->no_amount;

                $q2Amount = ($item->space_available == 'होय')
                    ? (float)$paymentData->yes_amount
                    : (float)$paymentData->no_amount;

                $baseTotal = ($q1Amount + $q2Amount) * (int)$item->type_of_tree;
                $amountTotal = (float)$paymentData->amount * (int)$item->type_of_tree;
                $calculated_total = $baseTotal + $amountTotal;

            } elseif ($item->reason_trim == "वृक्ष पुनःरोपण") {
                $q1Amount = ($item->new_replant == 'होय')
                    ? (float)$paymentData->yes_amount
                    : (float)$paymentData->no_amount;

                $q2Amount = ($item->space_replant == 'होय')
                    ? (float)$paymentData->yes_amount
                    : (float)$paymentData->no_amount;

                $baseTotal = ($q1Amount + $q2Amount) * (int)$item->type_of_tree;
                $amountTotal = (float)$paymentData->amount * (int)$item->type_of_tree;
                $calculated_total = $baseTotal + $amountTotal;

            } else {
                $calculated_total = (float)$paymentData->amount * (int)$item->type_of_tree;
            }

        }

        // ✅ Case 2: सोसायटी / घरांच्या परिसरातील झाडे तोडणे / पुर्नरोपण / छाटणी करणे.
        elseif ($item->type_application == "सोसायटी / घरांच्या परिसरातील झाडे तोडणे / पुर्नरोपण / छाटणी करणे.") {

            // 🟢 Subcase 1: वृक्षाची छाटणी करणे
            if ($item->reason_trim == "वृक्षाची छाटणी करणे") {
                $rateTotal = (float)$paymentData->rate * (int)$item->type_of_tree;
                $amountTotal = (float)$paymentData->amount * (int)$item->type_of_tree;
                $calculated_total = $rateTotal + $amountTotal;
            }

            // 🟢 Subcase 2: वृक्षांची छाटणी (फांद्या) उचलणे
            elseif ($item->reason_trim == "वृक्षांची छाटणी (फांद्या) उचलणे") {
                $rateTotal = (float)$paymentData->rate * (int)$item->type_of_tree;
                $amountTotal = (float)$paymentData->amount * (int)$item->type_of_tree;
                $calculated_total = $rateTotal + $amountTotal;
            }

            // 🟢 Subcase 3: धोकादायक झाडे मुळासहित काढणे
            elseif ($item->reason_trim == "धोकादायक झाडे मुळासहित काढणे") {

                if ($item->tree_height == "उंची (Height) - २० फुटापर्यंत बुंध्याची जाडी (Girth) - १ फुटापर्यंत") {
                    $rateTotal = (float)$paymentData->rate * (int)$item->type_of_tree;
                    $amountTotal = (float)$paymentData->amount * (int)$item->type_of_tree;
                    $calculated_total = $rateTotal + $amountTotal;
                } 
                elseif ($item->tree_height == "उंची (Height) - २० ते ३५ फुटापर्यंत बुंध्याची जाडी (Girth) - १ ते २.५० फुट") {
                    $rateTotal = (float)$paymentData->rate * (int)$item->type_of_tree;
                    $amountTotal = (float)$paymentData->amount * (int)$item->type_of_tree;
                    $calculated_total = $rateTotal + $amountTotal;
                } 
                elseif ($item->tree_height == "उंची (Height) - ३५ ते ५० फुट किंवात्यापेक्षा जास्त बुंध्याची जाडी (Girth) - २.५० फुटापेक्षा जास्त") {
                    $rateTotal = (float)$paymentData->rate * (int)$item->type_of_tree;
                    $amountTotal = (float)$paymentData->amount * (int)$item->type_of_tree;
                    $calculated_total = $rateTotal + $amountTotal;
                } 
                else {
                    // Default if tree_height doesn't match any condition
                    $calculated_total = (float)$paymentData->amount * (int)$item->type_of_tree;
                }
            }

            // 🔸 Default for other reasons
            else {
                $yesNoTotal = ((float)$paymentData->yes_amount + (float)$paymentData->no_amount) * (int)$item->type_of_tree;
                $amountTotal = (float)$paymentData->amount * (int)$item->type_of_tree;
                $calculated_total = $yesNoTotal + $amountTotal;
            }
        }

        // ✅ Default fallback
        else {
            $yesNoTotal = ((float)$paymentData->yes_amount + (float)$paymentData->no_amount) * (int)$item->type_of_tree;
            $amountTotal = (float)$paymentData->amount * (int)$item->type_of_tree;
            $calculated_total = $yesNoTotal + $amountTotal;
        }

        // Final assign
        $paymentData->calculated_total = $calculated_total;

        return round($paymentData->calculated_total);

    } catch (\Exception $e) {
        return 0;
    }
}
}
