<?php

namespace App\Services;

use App\Models\User;
use App\Models\Property;
use App\Models\OpportunityAlert;
use App\Models\MortgageInformation;
use App\Models\Admin;
use DB;
use Illuminate\Support\Facades\Log;
use Illuminate\Support\Facades\Mail;
use App\Mail\RemoveMortgageInsuranceAlertMail;
use App\Mail\SameTermAlertMail;
use App\Mail\NotSameTermAlertMail;
use App\Mail\TakeCashOutAlertMail;
use App\Services\LenderPriceService;
use Illuminate\Support\Collection;
use App\Models\EmailCount;

class OpportunityAlertsServices
{
    public $closing_cost;
    public $loanTermArray;
    private LenderPriceService $lenderPriceService;
    public function __construct()
    {
        $this->closing_cost = 1000;
        $this->loanTermArray = [60, 84, 120, 180, 240, 300, 360];
        $this->lenderPriceService = new LenderPriceService();
    }

    public function updateCurrentLoanBalance($propertyId = null)
    {

        $query = MortgageInformation::whereNotNull("original_loan_balance")
        ->whereNotNull("current_interest_rate")
        ->whereNotNull("loan_term")
        ->whereNotNull("mortgage_start_date");

        if ($propertyId) {
            $query->where("id", $propertyId);
        }

        $query->chunkById(100, function ($mortgages) {
            
            foreach ($mortgages as $mortgage) {
                $ORIGINAL_LOAN_AMT = $mortgage->original_loan_balance;
                $MONTHLY_INTEREST = $mortgage->current_interest_rate;
                $TOTAL_TENURE = $mortgage->loan_term;
                $LOAN_START_DATE = $mortgage->mortgage_start_date;

                $current_loan_balance = getCurrentLoanBalance($ORIGINAL_LOAN_AMT, $MONTHLY_INTEREST, $TOTAL_TENURE, $LOAN_START_DATE);

                if (floatval($current_loan_balance) > 0) {
                    $mortgage->current_loan_balance = $current_loan_balance;
                    $mortgage->save();
                }
            }
        });
    }

    public function updateCurrentEstimatedHomeValue()
    {
        $properties = Property::whereNull("current_estimated_home_value")->get();

        $properties->each(function ($property) {
            $address1 = $property->address;
            $address2 = $property->city . "," . $property->state;

            $property_expandedprofile = (new AtomApi)->property_expandedprofile($property->user_id, $property->id, "cron", "cron", $address1, $address2);
            $attomId = $property_expandedprofile['response']->property[0]->identifier->attomId;
            if ($property_expandedprofile['success'] == 1) {

                $allevents_details = (new AtomApi)->allevents_details($property->user_id, $property->id, "atom-property-services", "jobs", $attomId);
                $current_estimated_home_value = "";
                if($allevents_details['success'] == 1) {
                    if(!empty($allevents_details['response']->property)) {
                        $current_estimated_home_value = $allevents_details['response']->property[0]->avm->amount->value ?? null;
                    }
                }

                $propertyUpdate = Property::find($property->id);
                $propertyUpdate->number_of_bedrooms = $property_expandedprofile['response']->property[0]->building->rooms->beds ?? null;
                $propertyUpdate->number_of_bathrooms = $property_expandedprofile['response']->property[0]->building->rooms->bathsTotal ?? null;
                $propertyUpdate->size_sqft = $property_expandedprofile['response']->property[0]->building->size->livingSize ?? null;
                $propertyUpdate->current_estimated_home_value = $current_estimated_home_value ?? $property_expandedprofile['response']->property[0]->assessment->market->mktTtlValue ?? null;
                $propertyUpdate->attomid = $property_expandedprofile['response']->property[0]->identifier->attomId ?? null;
                $propertyUpdate->geoidv4_n1 = $property_expandedprofile['response']->property[0]->location->geoIdV4->N1 ?? $property_expandedprofile['response']->property[0]->location->geoIdV4->N2 ?? null;
                $propertyUpdate->save();
            }
        });
    }

    public function removeMortgageInsuranceAlerts($property_id = 0, $user_id = 0, $notifyBorrower = true)
    {
        $property_id = 3;
        Log::info("removeMortgageInsuranceAlerts started", ['property_id' => $property_id]);
        /* Delete existing opportunity data for the specific property for overwriting the new opportunity data */
        DB::table('opportunity_alerts')->where("alert_type", "remove_mortgage_insurance")->delete();
        Log::info("Existing opportunity alerts deleted");
        $SQL = "
            SELECT P.id AS property_id, P.user_id, P.address, P.city, P.state, P.zipcode, P.current_estimated_home_value, M.id AS mortgage_id,
            M.current_loan_balance, M.loan_type, M.current_interest_rate, M.monthly_pmi_payment, M.original_loan_balance,
            M.pmi, (P.current_estimated_home_value*(80/100)) AS xx,
            P.property_type, P.number_of_units, M.estimated_credit_score, P.occupancy, M.mortgage_start_date, M.loan_term, M.loan_program, P.property_tax, P.home_insurance,P.assigned_to
            FROM `mortgage_information` AS M
            LEFT JOIN `properties` AS P
            ON M.property_id = P.id
            WHERE  P.current_estimated_home_value > 0 AND M.current_loan_balance > 0 AND M.pmi = 'Y' AND M.current_loan_balance < (P.current_estimated_home_value*(80/100))
            #AND P.status = 1
            AND (SELECT COUNT(*) FROM `opportunity_alerts` WHERE property_id = P.id AND mortgage_id = M.id AND current_estimated_home_value = P.current_estimated_home_value AND current_loan_balance = M.current_loan_balance AND alert_type = 'remove_mortgage_insurance') = 0
            AND P.deleted_at is null

            #AND P.id = 82
             
        ";

        if ($property_id > 0) {
            $SQL .= " AND P.id = '" . $property_id . "' ";
        }

        $rows = collect(DB::select($SQL));
        Log::info("Fetched properties", ['count' => $rows->count()]);
        $rows->each(function ($info) use ($notifyBorrower) {
            Log::info("Processing property", ['property_id' => $info->property_id]);
            /* Get the remaining loan terms(Pending years to complete the loan) */
            $TOTAL_TENURE_LEFT = calculateRemainingEMI($info->mortgage_start_date, $info->loan_term);
            Log::info("Calculated remaining tenure", ['TOTAL_TENURE_LEFT' => $TOTAL_TENURE_LEFT]);
            $terms_search = (collect($this->loanTermArray)->filter(function ($d) use ($TOTAL_TENURE_LEFT) {

                return true;
            })->map(function ($n) {
                return $n / 12;
            })->toArray());

            if (strtolower($info->loan_program) == "fha") {
                $mortgageTypes = "Conventional";
            } else {
                $mortgageTypes = $info->loan_program;
            }


            /* 
            * Get closest terms for remainig term, 
            * If remainig terms are 17yrs then closest can be [15,20]. 
            * If remainig terms are 15yrs then closest can be [15] like wise.  
            */
            $search_loan_term = $this->getClosestYear($TOTAL_TENURE_LEFT, $this->loanTermArray);
            Log::info("Closest loan terms", ['search_loan_term' => $search_loan_term]);
            $terms_search = array();
            foreach ($search_loan_term as $term) {
                $terms_search[] = $term / 12;
            }

            //Get laon officer ID from property
            $property = Property::where("id", $info->property_id)->first();
            $assigned_to = $property->assigned_to ?? 1;
            Log::info("Assigned loan officer", ['assigned_to' => $assigned_to]);
            $current_loan_balance = getCurrentLoanBalance($info->original_loan_balance, $info->current_interest_rate, $info->loan_term, $info->mortgage_start_date) ?? $info->current_loan_balance;
            $neatcap_collection =  $this->lenderPriceService->callLenderPriceApi([
                "creditScore" => $info->estimated_credit_score,
                "propertyType" => $info->property_type,
                "occupancyType" => $info->occupancy,
                "zip" => $info->zipcode,
                "homeValue" => $info->current_estimated_home_value,
                "currentLoanBalance" => $current_loan_balance,
                "current_rate" => $info->current_interest_rate,
                "terms" => $terms_search,
                "state_code" => strtoupper($info->state),
                "mortgageTypes" => $mortgageTypes,
                "numberOfUnit" => $info->number_of_units,
                "assigned_loan_officer" => $assigned_to,
            ]);
            /* Filtered rates */
            //$filteredRates = $this->filerRates($neatcap_collection, $info->current_interest_rate);
            $neatcap_collection = collect($neatcap_collection);
            Log::info("Fetched lender price offers", ['count' => $neatcap_collection->count()]);
            if ($neatcap_collection->count() > 0) {

                $neatcap_collection->each(function ($new_offer) use ($info, $TOTAL_TENURE_LEFT, $assigned_to) {
                    Log::info("Processing new offer", ['rate' => $new_offer->rate]);
                    if ($new_offer) {
                        if ($new_offer->rate < $info->current_interest_rate) {
                            Log::info("Offer qualifies for alert", ['current_rate' => $info->current_interest_rate, 'new_rate' => $new_offer->rate]);
                            $send_alert = false;
                            $current_loan_balance = getCurrentLoanBalance($info->original_loan_balance, $info->current_interest_rate, $info->loan_term, $info->mortgage_start_date);

                            $discounted_rate = floatval($info->current_interest_rate) - floatval($new_offer->rate);

                            if ($discounted_rate >= 0.25) {

                                if (floatval($new_offer->closingCosts) < $this->closing_cost) { //1000
                                    $send_alert = true;
                                }
                            }

                            if ($discounted_rate >= 0.375 && !$send_alert) {
                                $one_prct_loan_amount = $current_loan_balance * (2 / 100);
                                if (floatval($new_offer->closingCosts) <= $one_prct_loan_amount) {
                                    $send_alert = true;
                                }
                            }

                            if ($send_alert) {
                                $current_monthly_payment = getMonthlyEMI($info->original_loan_balance, $info->current_interest_rate, $info->loan_term)['EMI'];

                                $current_mortgage_payment_monthly = floatval($current_monthly_payment);
                                $current_mortgage_payment_lifetime = $current_mortgage_payment_monthly * $TOTAL_TENURE_LEFT; //$info->loan_term;

                                $current_mortgage_insurance_payment_monthly = floatval($info->monthly_pmi_payment);
                                $current_mortgage_insurance_payment_lifetime = $current_mortgage_insurance_payment_monthly * $TOTAL_TENURE_LEFT; //$info->loan_term;
                                $current_property_tax = ($info->property_tax ?? 0) + ($info->home_insurance ?? 0);
                                $current_property_tax_monthly = $current_property_tax / 12;
                                $current_property_tax_lifetime = $current_property_tax_monthly * $TOTAL_TENURE_LEFT; //$info->loan_term;
                                $current_interest_rate = $info->current_interest_rate;



                                // Interest remaining on the current loan - Interest to be paid over the term of the new loan
                                $interest_remaining_on_current_loan = 0;
                                $interest_remaining_on_current_loan = ($current_monthly_payment * $TOTAL_TENURE_LEFT) -  $current_loan_balance;


                                //==== proposed offer ==========================
                                $proposed_mortgage_payment_monthly = $new_offer->monthlyPayment;
                                $proposed_mortgage_payment_lifetime = $proposed_mortgage_payment_monthly * $new_offer->term;

                                $proposed_property_tax = ($info->property_tax ?? 0) + ($info->home_insurance ?? 0);
                                $proposed_property_tax_monthly = $proposed_property_tax / 12;
                                $proposed_property_tax_lifetime = $proposed_property_tax_monthly * $new_offer->term;
                                $proposed_mortgage_insurance_payment_monthly = 0;
                                $proposed_mortgage_insurance_payment_lifetime = 0;
                                $proposed_interest_rate = $new_offer->rate;
                                $proposed_term = $new_offer->term;
                                $proposed_one_time_closing_cost = $new_offer->closingCosts;

                                $proposed_interest_cost = 0;
                                $proposed_interest_cost = ($proposed_mortgage_payment_monthly * $proposed_term) -  $current_loan_balance;

                                $total_saving = $interest_remaining_on_current_loan - $proposed_interest_cost;
                                $margin = calculateMargin($assigned_to, $current_loan_balance);
                                $mdata = [
                                    "current_mortgage_payment_monthly" => $current_mortgage_payment_monthly,
                                    "current_mortgage_payment_lifetime" => $current_mortgage_payment_lifetime,
                                    "current_monthly_payment" => $current_monthly_payment,
                                    "current_loan_balance" => $current_loan_balance,
                                    "original_loan_balance" => $info->original_loan_balance,
                                    "current_mortgage_insurance_payment_monthly" => $current_mortgage_insurance_payment_monthly,
                                    "current_mortgage_insurance_payment_lifetime" => $current_mortgage_insurance_payment_lifetime,
                                    "current_property_tax" => $info->property_tax,
                                    "current_property_tax_monthly" => $current_property_tax_monthly,
                                    "current_property_tax_lifetime" => $current_property_tax_lifetime,
                                    "current_interest_rate" => $current_interest_rate,
                                    "term_remaining" => $TOTAL_TENURE_LEFT,
                                    "interest_remaining_on_current_loan" => $interest_remaining_on_current_loan,

                                    "proposed_mortgage_payment_monthly" => $proposed_mortgage_payment_monthly,
                                    "proposed_mortgage_payment_lifetime" => $proposed_mortgage_payment_lifetime,
                                    "proposed_property_tax" => $proposed_property_tax,
                                    "proposed_property_tax_monthly" => $proposed_property_tax_monthly,
                                    "proposed_property_tax_lifetime" => $proposed_property_tax_lifetime,
                                    "proposed_mortgage_insurance_payment_monthly" => 0,
                                    "proposed_mortgage_insurance_payment_lifetime" => 0,
                                    "proposed_interest_rate" => $proposed_interest_rate,
                                    "proposed_term" => $proposed_term,
                                    "proposed_one_time_closing_cost" => $proposed_one_time_closing_cost,
                                    "proposed_interest_cost" => $proposed_interest_cost,
                                    "total_saving" => $total_saving,
                                    "cost_or_credit" => floatval($new_offer->amount) + 1495.00 + floatval($margin)
                                ];

                                // Interest remaining on the current loan - Interest to be paid over the term of the new loan

                                $interest_saving = $interest_remaining_on_current_loan - $new_offer->totalInterest;
                                //$emi_monthly_saving = $current_monthly_payment - $new_offer->monthlyPayment;
                                $emi_monthly_saving = calculateMonthlySavings($current_monthly_payment, $info->monthly_pmi_payment, $new_offer->monthlyPayment, $new_offer->lender_price['mi']);
                                $annualPurchaseRate = calculateAnnualPurchaseRate($new_offer->rate, $new_offer->totalInterest, $new_offer->amount, $current_loan_balance, $proposed_term);
                                $other_info = json_encode([
                                    "address" => $info->address,
                                    "city" => $info->city,
                                    "zipcode" => $info->zipcode,
                                    "discounted_rate" => $discounted_rate,
                                    "current_loan_balance" => $current_loan_balance,
                                    "current_monthly_payment" => $current_monthly_payment,
                                    "future_monthly_payment" => $new_offer->monthlyPayment,
                                    "emi_monthly_saving" => $emi_monthly_saving,
                                    "annual_purchase_rate" => $annualPurchaseRate,
                                    "REMAINING_TERM" => $TOTAL_TENURE_LEFT,
                                    "interest_remaining_on_current_loan" => $interest_remaining_on_current_loan,
                                    "interest_to_be_paid_for_new_loan" => $new_offer->totalInterest,
                                    "interest_saving" => $interest_saving,
                                    "api_response" => $new_offer,
                                    "loan_type" =>  $info->loan_type,
                                    "mdata" => $mdata
                                ]);
                                Log::info("Creating opportunity alert", ['property_id' => $info->property_id]);
                                $opportunity_alert = OpportunityAlert::create([
                                    "alert_type" => "remove_mortgage_insurance",
                                    "user_id" => $info->user_id,
                                    "property_id" => $info->property_id,
                                    "mortgage_id" => $info->mortgage_id,
                                    "current_estimated_home_value" => $info->current_estimated_home_value,
                                    "property_type" => $info->property_type,
                                    "current_loan_balance" => $current_loan_balance,
                                    "other_info" => $other_info,
                                ]);
                            }
                        }
                    }
                });
            }

            /* Processing alert notification to user */
            if (isset($notifyBorrower) && $notifyBorrower) {
                $this->processAlertNotificationForBorrower("remove_mortgage_insurance", $info, $info->mortgage_id);
            }
            Log::info("Processed borrower notification", ['property_id' => $info->property_id]);
        });
        Log::info("removeMortgageInsuranceAlerts completed");
    }

    /*
    * Author: Vidhi Shah
    * Added: 21th Aug 2024
    * Description: This function is used to get offers for take cash out
    * Added some values which was not stored in DB and for this reason alerts were not getting displayed on the dashboard
    *
    * Updated: 2nd Dec 2024
    * Description: Made changes in query, properties with more than 1 mortgage information will be skipped for this opportunity.
    */
    public function takeCashOutAlerts($property_id = 0, $user_id = 0, $notifyBorrower = true)
    {
        $property_id = 3;
        Log::info('Starting takeCashOutAlerts function', ['property_id' => $property_id]);
        /// primary residence ========================
        DB::table('opportunity_alerts')->where("alert_type", "take_cash_out")->delete();
        Log::info('Deleted previous take_cash_out alerts');
        $SQL_PRIMARY = "
            SELECT 
                P.id AS property_id, 
                P.user_id, 
                P.address, 
                P.city, 
                P.zipcode, 
                P.current_estimated_home_value, 
                P.occupancy, 
                P.property_type, 
                M.id AS mortgage_id, 
                M.mortgage_start_date, 
                M.loan_term, 
                M.original_loan_balance, 
                M.current_interest_rate, 
                M.current_loan_balance,
                (P.current_estimated_home_value * (80 / 100)) AS xx,  
                ((P.current_estimated_home_value * (80 / 100)) - M.current_loan_balance) AS potential_cash_out_value, 
                P.assigned_to
            FROM 
                `mortgage_information` AS M
            LEFT JOIN 
                `properties` AS P
            ON 
                M.property_id = P.id
            WHERE 
                P.current_estimated_home_value > 0 
                AND M.current_loan_balance > 0 
                AND M.current_loan_balance < (P.current_estimated_home_value * (80 / 100))
                AND P.occupancy LIKE '%Primary%'
                AND (
                    SELECT COUNT(*) 
                    FROM `opportunity_alerts`
                    WHERE 
                        property_id = P.id 
                        AND mortgage_id = M.id 
                        AND current_estimated_home_value = P.current_estimated_home_value
                        AND current_loan_balance = M.current_loan_balance 
                        AND occupancy LIKE '%Primary%' 
                        AND alert_type = 'take_cash_out'
                ) = 0
                AND P.deleted_at IS NULL
                AND (
                    SELECT COUNT(*) 
                    FROM `mortgage_information` AS subM 
                    WHERE subM.property_id = P.id
                ) = 1

        ";

        if ($property_id > 0) {
            $SQL_PRIMARY .= " AND P.id = '" . $property_id . "' ";
        }
        Log::info('Executing SQL for primary residence', ['query' => $SQL_PRIMARY]);
        $rows_primary = collect(DB::select($SQL_PRIMARY));
        Log::info('Fetched primary residence records', ['count' => $rows_primary->count()]);
        $rows_primary->each(function ($info) use ($notifyBorrower) {
            Log::info('Processing primary residence property', ['property_id' => $info->property_id]);
            $potential_cash_out_value = $info->potential_cash_out_value;
            if ($potential_cash_out_value > 500000) {
                $potential_cash_out_value = 500000;
            }
            $original_potential_cash_out_value = $potential_cash_out_value;
            // Round down to the nearest 1000
            $potential_cash_out_value = floor($potential_cash_out_value / 1000) * 1000;
            Log::info('Rounded potential cash out value', [
                'property_id' => $info->property_id,
                'original_value' => $original_potential_cash_out_value,
                'rounded_value' => $potential_cash_out_value
            ]);

            $REMAINING_TERM = calculateRemainingEMI($info->mortgage_start_date, $info->loan_term);

            $current_loan_balance = getCurrentLoanBalance($info->original_loan_balance, $info->current_interest_rate, $info->loan_term, $info->mortgage_start_date);
            
            $current_monthly_payment = getMonthlyEMI($info->original_loan_balance, $info->current_interest_rate, $info->loan_term)['EMI'];
            $current_mortgage_payment_monthly = floatval($current_monthly_payment);
            $interest_remaining_on_current_loan = 0;
            $interest_remaining_on_current_loan = ($current_monthly_payment * $REMAINING_TERM) -  $current_loan_balance;
            Log::info('Calculated financial data', [
                'potential_cash_out_value' => $potential_cash_out_value,
                'remaining_term' => $REMAINING_TERM,
                'current_monthly_payment' => $current_mortgage_payment_monthly,
                'interest_remaining' => $interest_remaining_on_current_loan
            ]);
            $mdata = [
                "current_estimated_home_value" => $info->current_estimated_home_value,
                "current_loan_balance" => $current_loan_balance,
                "occupancy" => $info->occupancy,
                "property_type" => $info->property_type,
                "potential_cash_out_value" => $potential_cash_out_value,
                "current_monthly_payment" => $current_mortgage_payment_monthly,
                "interest_remaining_on_current_loan" => $interest_remaining_on_current_loan,
                "current_mortgage_payment_monthly" => $current_mortgage_payment_monthly,
            ];


            $other_info = json_encode([
                "address" => $info->address,
                "city" => $info->city,
                "zipcode" => $info->zipcode,
                "potential_cash_out_value" => $potential_cash_out_value,
                "interest_remaining_on_current_loan" => $interest_remaining_on_current_loan,
                "mdata" => $mdata
            ]);
            
            $opportunity_alert = OpportunityAlert::create([
                "alert_type" => "take_cash_out",
                "user_id" => $info->user_id,
                "property_id" => $info->property_id,
                "mortgage_id" => $info->mortgage_id,
                "current_estimated_home_value" => $info->current_estimated_home_value,
                "current_loan_balance" => $current_loan_balance,
                "other_info" => $other_info,
                "occupancy" => $info->occupancy,
                "property_type" => $info->property_type
            ]);
            Log::info('Created opportunity alert', ['property_id' => $info->property_id]);

            // Processing alert notification to user
            if (isset($notifyBorrower) && $notifyBorrower) {
                $this->processAlertNotificationForBorrower("take_cash_out", $info, $info->mortgage_id);
            }
        
        });


        /// secondary residence ========================
        $SQL_SECONDARY = "
            SELECT 
            P.id AS property_id, 
            P.user_id, 
            P.address, 
            P.city, 
            P.zipcode, 
            P.current_estimated_home_value, 
            P.occupancy, 
            P.property_type, 
            M.id AS mortgage_id, 
            M.mortgage_start_date, 
            M.loan_term, 
            M.original_loan_balance, 
            M.current_interest_rate, 
            M.current_loan_balance,
            (P.current_estimated_home_value * (60 / 100)) AS xx,  
            ((P.current_estimated_home_value * (75 / 100)) - M.current_loan_balance) AS potential_cash_out_value, 
            P.assigned_to
        FROM 
            `mortgage_information` AS M
        LEFT JOIN 
            `properties` AS P
        ON 
            M.property_id = P.id
        WHERE 
            P.current_estimated_home_value > 0 
            AND M.current_loan_balance > 0 
            AND M.current_loan_balance < (P.current_estimated_home_value * (60 / 100))
            AND P.occupancy IN ('Investment Property, Land') 
            AND P.property_type IN ('Single Family Residence', 'Condominium')
            AND (
                SELECT COUNT(*) 
                FROM `opportunity_alerts`
                WHERE 
                    property_id = P.id 
                    AND mortgage_id = M.id 
                    AND current_estimated_home_value = P.current_estimated_home_value
                    AND current_loan_balance = M.current_loan_balance 
                    AND occupancy = P.occupancy 
                    AND P.property_type = P.property_type  
                    AND alert_type = 'take_cash_out'
            ) = 0
            AND P.deleted_at IS NULL
            AND (
                SELECT COUNT(*) 
                FROM `mortgage_information` AS subM 
                WHERE subM.property_id = P.id
            ) = 1

        ";

        if ($property_id > 0) {
            $SQL_SECONDARY .= " AND P.id = '" . $property_id . "' ";
        }

        Log::info('Executing SQL for secondary residence', ['query' => $SQL_SECONDARY]);
        $rows_secondary = collect(DB::select($SQL_SECONDARY));
        Log::info('Fetched secondary residence records', ['count' => $rows_secondary->count()]);
        // dd($rows_secondary);

        $rows_secondary->each(function ($info) use ($notifyBorrower) {
            Log::info('Processing secondary residence property', ['property_id' => $info->property_id]);
            $potential_cash_out_value = $info->potential_cash_out_value;
            if ($potential_cash_out_value > 500000) {
                $potential_cash_out_value = 500000;
            }
            $original_potential_cash_out_value = $potential_cash_out_value;
            // Round down to the nearest 1000
            $potential_cash_out_value = floor($potential_cash_out_value / 1000) * 1000;
            Log::info('Rounded potential cash out value', [
                'property_id' => $info->property_id,
                'original_value' => $original_potential_cash_out_value,
                'rounded_value' => $potential_cash_out_value
            ]);

            $REMAINING_TERM = calculateRemainingEMI($info->mortgage_start_date, $info->loan_term);
            $current_loan_balance = getCurrentLoanBalance($info->original_loan_balance, $info->current_interest_rate, $info->loan_term, $info->mortgage_start_date);
            $current_monthly_payment = getMonthlyEMI($info->original_loan_balance, $info->current_interest_rate, $info->loan_term)['EMI'];
            $current_mortgage_payment_monthly = floatval($current_monthly_payment);
            $interest_remaining_on_current_loan = 0;
            $interest_remaining_on_current_loan = ($current_monthly_payment * $REMAINING_TERM) -  $current_loan_balance;
            Log::info('Calculated financial data', [
                'potential_cash_out_value' => $potential_cash_out_value,
                'remaining_term' => $REMAINING_TERM,
                'current_monthly_payment' => $current_mortgage_payment_monthly,
                'interest_remaining' => $interest_remaining_on_current_loan
            ]);
            $mdata = [
                "current_estimated_home_value" => $info->current_estimated_home_value,
                "current_loan_balance" => $current_loan_balance,
                "occupancy" => $info->occupancy,
                "property_type" => $info->property_type,
                "potential_cash_out_value" => $potential_cash_out_value,
                "current_monthly_payment" => $current_mortgage_payment_monthly,
                "interest_remaining_on_current_loan" => $interest_remaining_on_current_loan,
                "current_mortgage_payment_monthly" => $current_mortgage_payment_monthly,
            ];

            $other_info = json_encode([
                "address" => $info->address,
                "city" => $info->city,
                "zipcode" => $info->zipcode,
                "potential_cash_out_value" => $potential_cash_out_value,
                "interest_remaining_on_current_loan" => $interest_remaining_on_current_loan,
                "mdata" => $mdata
            ]);

            $opportunity_alert = OpportunityAlert::create([
                "alert_type" => "take_cash_out",
                "user_id" => $info->user_id,
                "property_id" => $info->property_id,
                "mortgage_id" => $info->mortgage_id,
                "current_estimated_home_value" => $info->current_estimated_home_value,
                "current_loan_balance" => $current_loan_balance,
                "other_info" => $other_info,
                "occupancy" => $info->occupancy,
                "property_type" => $info->property_type
            ]);
            Log::info('Created opportunity alert', ['property_id' => $info->property_id]);
            /* Processing alert notification to user */
            if (isset($notifyBorrower) && $notifyBorrower) {
                $this->processAlertNotificationForBorrower("take_cash_out", $info, $info->mortgage_id);
            }
        });
        Log::info('Completed takeCashOutAlerts function');
    }

    public function getClosestYear($search, $arr)
    {
        $closest = null;
        foreach ($arr as $item) {
            if ($closest === null || abs($search - $closest) > abs($item - $search)) {
                $closest = $item;
            }
        }

        $return_array = [];
        $return_array[] = $closest;
        // dump($arr);
        if ($search > $closest) {
            // dump("find highest");
            $asearch = array_search($closest, $arr);
            if ($asearch > -1 && max($arr) > $search) {
                $return_array[] = $arr[$asearch + 1];
            }
        } else if ($search < $closest) {
            // dump("find lowest");

            $asearch = array_search($closest, $arr);
            if ($asearch > 0) {
                $return_array[] = $arr[$asearch - 1];
            }
        }
        // dump($return_array);
        // return $closest;

        return $return_array;
    }

    public function lowerRateSameTermAlerts(int $propertyId = 0, int $userId = 0, $notifyBorrower = true)
    {
        try {
            Log::info("LRST (Lower your payment) - Starting lowerRateSameTermAlerts process", ['propertyId' => $propertyId, 'userId' => $userId]);

            // Get user IDs based on provided userId (or all relevant users)
            $userIds = $this->getUserIds($userId);

            //$userIds = [16];
            $userIds = [1122,1123,1124,1125];
            // Retrieve filtered mortgage records for the given users and property
            $mortgages = $this->getFilteredMortgages($userIds, $propertyId);
            Log::info("LRST (Lower your payment) - Fetched mortgages", ['count' => count($mortgages)]);

            foreach ($mortgages as $group) {
                try {
                    $firstMortgage = $group->first();
                    $secondMortgage = $group->skip(1)->first();
                    $subordinateLoanAmount = $secondMortgage->current_loan_balance ?? 0;

                    Log::info("LRST (Lower your payment) - Processing Mortgage", ['mortgage_id' => $firstMortgage->id, 'subordinate_loan_amount' => $subordinateLoanAmount]);

                    // Calculate remaining term in months
                    $REMAINING_TERM = calculateRemainingEMI($firstMortgage->mortgage_start_date, $firstMortgage->loan_term);
                    Log::info("LRST (Lower your payment) - Calculated remaining term", ['remaining_term' => $REMAINING_TERM]);

                    // Determine the closest available loan terms
                    $searchLoanTerms = $this->getClosestYear($REMAINING_TERM, $this->loanTermArray);
                    $terms_search = array_map(fn($term) => $term / 12, $searchLoanTerms);

                    // Get assigned loan officer
                    $assignedTo = Property::find($firstMortgage->property_id)?->assigned_to ?? 1;

                    // Prepare API request payload
                    $current_loan_balance = getCurrentLoanBalance($firstMortgage->original_loan_balance, $firstMortgage->current_interest_rate, $firstMortgage->loan_term, $firstMortgage->mortgage_start_date) ?? $firstMortgage->current_loan_balance;
                    $apiPayload = [
                        "creditScore" => $firstMortgage->estimated_credit_score,
                        "propertyType" => $firstMortgage->property->property_type,
                        "occupancyType" => $firstMortgage->property->occupancy,
                        "zip" => $firstMortgage->property->zipcode,
                        "homeValue" => $firstMortgage->property->current_estimated_home_value,
                        "currentLoanBalance" => $current_loan_balance,
                        "current_rate" => $firstMortgage->current_interest_rate,
                        "terms" => $terms_search,
                        "state_code" => strtoupper($firstMortgage->property->state),
                        "mortgageTypes" => $firstMortgage->loan_program,
                        "numberOfUnit" => $firstMortgage->number_of_units,
                        "assigned_loan_officer" => $assignedTo,
                        "subordinateLoanAmount" => $subordinateLoanAmount,
                    ];

                    Log::info("LRST (Lower your payment) - apiPayload", ['payload' => $apiPayload]);

                    // Call Lender Price API
                    if(!empty($firstMortgage) && !empty($firstMortgage->property) && !empty($firstMortgage->property->current_estimated_home_value) && $firstMortgage->property->current_estimated_home_value > 0){
                        Log::info("LRST (Lower your payment) - Calling Lender Price API", ['payload' => $apiPayload]);
                        $apiResponse = $this->lenderPriceService->callLenderPriceApi($apiPayload);
                        Log::info("LRST (Lower your payment) - Lender Price API response received for LRST", ['response' => $apiResponse]);

                        // Process the API response
                        $this->processApiResponseforLRST($apiResponse, $firstMortgage, $REMAINING_TERM, $notifyBorrower, $assignedTo);
                        Log::info("LRST (Lower your payment) - Finished processing Mortgage", ['mortgage_id' => $firstMortgage->id]);
                    }

                    
                } catch (\Exception $e) {
                    Log::error("LRST (Lower your payment) - Error processing mortgage", ['error' => $e->getMessage(), 'mortgage_id' => $firstMortgage->id ?? null]);
                }
            }

            Log::info("LRST (Lower your payment) - Completed lowerRateSameTermAlerts process");
        } catch (\Exception $e) {
            Log::error("LRST (Lower your payment) - Error in lowerRateSameTermAlerts", ['error' => $e->getMessage()]);
        }
    }

    public function lowerRateNotSameTermAlerts(int $propertyId = 0, int $userId = 0, $notifyBorrower = true)
    {
        try {
            Log::info("LRNST (Lower interest cost) - Starting lowerRateNotSameTermAlerts process", [
                'property_id' => $propertyId,
                'user_id' => $userId
            ]);

            // Get user IDs based on filtering criteria
            $userIds = $this->getUserIds($userId);
            //$userIds = [16];
            $userIds = [1122,1123,1124,1125];
            // Fetch mortgages for the given users and property
            $mortgages = $this->getFilteredMortgages($userIds, $propertyId);

            foreach ($mortgages as $group) {
                $firstMortgage = $group->first();
                $secondMortgage = $group->skip(1)->first();
                $subordinateLoanAmount = $secondMortgage->current_loan_balance ?? 0;

                Log::info("LRNST (Lower interest cost) - Processing Mortgage", [
                    'mortgage_id' => $firstMortgage->id,
                    'user_id' => $firstMortgage->user_id,
                    'property_id' => $firstMortgage->property_id,
                ]);

                // Calculate the borrower's current loan balance
                $current_loan_balance = getCurrentLoanBalance(
                    $firstMortgage->original_loan_balance,
                    $firstMortgage->current_interest_rate,
                    $firstMortgage->loan_term,
                    $firstMortgage->mortgage_start_date
                ) ?? $firstMortgage->current_loan_balance;

                // Calculate borrower's current monthly payment
                $current_monthly_payment = getMonthlyEMI(
                    $firstMortgage->original_loan_balance,
                    $firstMortgage->current_interest_rate,
                    $firstMortgage->loan_term
                )['EMI'];

                // Define 80% of the original loan balance as a threshold
                $original_loan_balance_80_prct = $firstMortgage->original_loan_balance * 0.8;

                Log::info("LRNST (Lower interest cost) - Calculated loan balances", [
                    'current_loan_balance' => $current_loan_balance,
                    'original_loan_balance_80_prct' => $original_loan_balance_80_prct,
                ]);

                // Check if the current loan balance is below 80% of the original loan balance
                if ($current_loan_balance < $original_loan_balance_80_prct) {
                    Log::info("LRNST (Lower interest cost) - Eligible for lower interest cost analysis", [
                        'current_loan_balance' => $current_loan_balance,
                        'original_loan_balance_80_prct' => $original_loan_balance_80_prct
                    ]);

                    // Define available term options
                    $termArray = [25, 20, 15, 10];

                    // Calculate remaining loan term in months
                    $REMAINING_TERM = calculateRemainingEMI($firstMortgage->mortgage_start_date, $firstMortgage->loan_term);

                    // Determine search term (5 years less than remaining term)
                    $search_term = ($REMAINING_TERM / 12) - 5;
                    $terms = [];

                    // Ensure there is at least 10 months remaining
                    if ($REMAINING_TERM > 10) {
                        if ($REMAINING_TERM < 15) {
                            // If remaining term is less than 15 years, only consider a 10-year term
                            $terms[] = 10;
                        } else {
                            // Otherwise, find the closest available terms from the list
                            foreach ($termArray as $term) {
                                if (count($terms) < 2 && $term <= $search_term) {
                                    $terms[] = $term;
                                }
                            }
                        }

                        Log::info("LRNST (Lower interest cost) - Determined loan terms for API request", ['terms_criteria' => $terms]);

                        // Prepare data for LenderPrice API request
                        $assignedTo = Property::find($firstMortgage->property_id)?->assigned_to ?? 1;

                        $apiRequestData = [
                            "creditScore" => $firstMortgage->estimated_credit_score,
                            "propertyType" => $firstMortgage->property->property_type,
                            "occupancyType" => $firstMortgage->property->occupancy,
                            "zip" => $firstMortgage->property->zipcode,
                            "homeValue" => $firstMortgage->property->current_estimated_home_value,
                            "currentLoanBalance" => $current_loan_balance,
                            "current_rate" => $firstMortgage->current_interest_rate,
                            "terms" => $terms,
                            "state_code" => strtoupper($firstMortgage->property->state),
                            "mortgageTypes" => $firstMortgage->loan_program,
                            "numberOfUnit" => $firstMortgage->number_of_units,
                            "assigned_loan_officer" => $assignedTo,
                            "subordinateLoanAmount" => $subordinateLoanAmount,
                        ];

                        Log::info("LRNST (Lower interest cost) - Calling LenderPrice API", ['api_request' => $apiRequestData]);

                        // Call LenderPrice API
                        if(!empty($firstMortgage) && !empty($firstMortgage->property) && !empty($firstMortgage->property->current_estimated_home_value) && $firstMortgage->property->current_estimated_home_value > 0){
                            $apiResponse = $this->lenderPriceService->callLenderPriceApi($apiRequestData);
                            Log::info("LRNST (Lower interest cost) - Lender Price API response received for LRNST", ['response' => $apiResponse]);

                            // Process API response
                            $this->processApiResponseforLRNST($apiResponse, $firstMortgage, $REMAINING_TERM, $notifyBorrower, $assignedTo);
                        }    

                        
                    }
                }
            }

            Log::info("LRNST (Lower interest cost) - Completed lowerRateNotSameTermAlerts process");
        } catch (\Exception $e) {
            Log::error("LRNST (Lower interest cost) - Error processing lowerRateNotSameTermAlerts", [
                'error_message' => $e->getMessage(),
                'property_id' => $propertyId,
                'user_id' => $userId,
            ]);
        }
    }


    /*
    * This function is used to filter the rates
    * Consequnces rate margin must be minimum 0.125
    * Check for Amount, there should be no duplicate amount
    * if amount is same with the positive sign, include the lowest rate (A)
    * if amount is same with the negative sign, include the lowest rate (B)
    * rate (A) & (B) should display
    */
    public function filerRates($data, $currentRate)
    {
        $result = collect($data)->groupBy('lender_price.mortgageType')
            ->map(function ($group) use ($currentRate) {
                return $group->groupBy('term')
                    ->map(function ($termGroup) use ($currentRate) {

                        // Step 1: Filter out rates that are greater than or equal to the current rate
                        $filteredRates = $termGroup->filter(function ($item) use ($currentRate) {
                            return $item->rate < $currentRate; // Exclude rates >= currentRate
                        });

                        // If no rates are left after filtering, return an empty collection
                        if ($filteredRates->isEmpty()) {
                            return collect();
                        }

                        // Step 2: Get unique rates with a minimum margin of 0.125 between them
                        $uniqueRates = $filteredRates->reduce(function ($carry, $item) {
                            $rate = $item->rate;
                            $isUnique = true;

                            foreach ($carry as $existingItem) {
                                if (abs($existingItem->rate - $rate) < 0.125) {
                                    $isUnique = false;
                                    break;
                                }
                            }

                            if ($isUnique) {
                                $carry->push($item);
                            }

                            return $carry;
                        }, collect());

                        // Step 3: Remove duplicates based on amount
                        return $uniqueRates->groupBy(function ($item) {
                            return abs($item->amount);
                        })->map(function ($costGroup) {
                            $positiveAmounts = $costGroup->filter(fn($item) => $item->amount > 0);
                            $negativeAmounts = $costGroup->filter(fn($item) => $item->amount < 0);

                            $result = collect();

                            if ($positiveAmounts->isNotEmpty()) {
                                $result->push($positiveAmounts->sortBy('rate')->first());
                            }

                            if ($negativeAmounts->isNotEmpty()) {
                                $result->push($negativeAmounts->sortBy('rate')->first());
                            }

                            return $result;
                        })->flatten(1);
                    })
                    ->flatten(1);
            })
            ->flatten(1)
            ->values()
            ->all();

        return $result;
    }

    /* Processing notifcation to the borrowers */
    public function processAlertNotificationForBorrower($alert_type, $mortgage, $mortgage_id)
    {
        Log::info("Processing alert notification for borrower", [
            'alert_type' => $alert_type,
            'mortgage_id' => $mortgage_id,
            'user_id' => $mortgage->user_id,
        ]);
        /* Sending notification to borrowers regarding refinancing opportunity */
        $user = User::select("id","email", "mobile", "consent_to_receive_message", "consent_to_receive_email", "partner_id", "unsubscribe_token", "unsubscribe_token_created_at", "unsubscribed_from_opportunity_alerts")->whereNotNull('last_login_at')->find($mortgage->user_id);
        if (empty($user)) {
            Log::warning("User not found", ['user_id' => $mortgage->user_id]);
        }

        $opportunityAlerts = [];
        if (!empty($user)) {
            $offerAlertExists = OpportunityAlert::where("alert_type", $alert_type)
                ->where("property_id", $mortgage->property_id)
                ->where("mortgage_id", $mortgage_id)
                ->where("user_id", $mortgage->user_id)
                ->where("read_status", "0")
                ->exists();
            if (!$offerAlertExists) {
                Log::info("No unread opportunity alert found", ['alert_type' => $alert_type]);
            }    
            if ($offerAlertExists) {
                $opportunity_alerts = OpportunityAlert::where("alert_type", $alert_type)
                    ->where("property_id", $mortgage->property_id)
                    ->orderBy("created_at", "DESC")
                    ->get();

                $opportunity_alerts->each(function ($value) use (&$opportunityAlerts) {
                    $value->other_info = json_decode($value->other_info);
                    $propertyId = $value->property_id;
                    $propertyName = $value->other_info->address . ", " . $value->other_info->city . ", " . $value->other_info->zipcode;

                    // Group by property_id
                    if (!isset($opportunityAlerts[$propertyId])) {
                        $mortgage_info = MortgageInformation::where("property_id", $propertyId)
                            ->where("user_id", $value->user_id)
                            ->first();
                        $property = Property::where("id", $propertyId)->first();
                        $opportunityAlerts[$propertyId] = [
                            'user_id' => $value->user_id,
                            'property_id' => $propertyId,
                            'property_name' => $propertyName,
                            'assigned_loan_officer' => $property->assigned_to,
                            'current_estimated_home_value' => $property->current_estimated_home_value,
                            'mortgage_info' => $mortgage_info,
                            'alerts' => []
                        ];
                    }

                    // Initialize alert_type array if not set
                    if (!isset($opportunityAlerts[$propertyId]['alerts'][$value->alert_type])) {
                        $opportunityAlerts[$propertyId]['alerts'][$value->alert_type] = [];
                    }

                    $opportunityAlerts[$propertyId]['alerts'][$value->alert_type][] = $value;
                });
            }


            // Process each property and sort the alerts
            $opportunityAlerts = collect($opportunityAlerts)->map(function ($property) {
                $property['alerts'] = collect($property['alerts'])->map(function ($alerts, $alertType) {
                    // Sort based on criteria specific to alert_type
                    $alerts = collect($alerts)->sort(function ($a, $b) use ($alertType) {
                        if ($alertType === 'lower_rate_same_term') {
                            return ($b->other_info->emi_monthly_saving <=> $a->other_info->emi_monthly_saving);
                        } elseif ($alertType === 'lower_rate_not_same_term') {
                            return ($b->other_info->interest_saving <=> $a->other_info->interest_saving);
                        } elseif ($alertType === 'remove_mortgage_insurance') {
                            return ($b->other_info->emi_monthly_saving <=> $a->other_info->emi_monthly_saving);
                        }
                        // Add more sorting criteria for other alert types if necessary
                        return 0;
                    })->take(1); // Get the top most alerts

                    return $alerts;
                });

                return $property;
            });

            // Output the result
            $opportunityAlerts->each(function ($property)  use ($user, $mortgage, $alert_type) {
                $mdataList = collect($property['alerts'][$alert_type])->map(function ($item) {
                    // Access the `mdata` property inside `other_info`
                    return $item->other_info;
                })->values()->toArray();

                $mdataList[0]->property_id = $mortgage->property_id;
                $mdataList[0]->property_name = $property['property_name'];
                $emailmdata = (array)$mdataList[0];

                /*When there is no LO assigned to borrower property, default LO will be Admin */
                $loanOfficerId = $property['assigned_loan_officer'] ?? 1;
                $admin = Admin::where('id', $loanOfficerId)->first();
                $apply_now = getenv("APPLY_NOW_FOR_INSTAREFI");
                
                if ($admin) {
                    #From Email Data
                    $apply_now = ($admin && !empty($admin->apply_now_url)) ? $admin->apply_now_url : getenv("APPLY_NOW_FOR_INSTAREFI");
                    Log::info("Check for Apply now out when Loan Officer exists", [
                        'alert_type' => $alert_type,
                        'apply_now' => $apply_now
                    ]);
                    
                    $loanOfficerData = [
                        'email' => $admin->email,
                        'name' => $admin->name,
                        'mobile' => $admin->mobile,
                        'nmls_number' => $admin->nmls_number,
                        'apply_now' => $apply_now
                    ];
                } else {
                    Log::info("Check for Apply now out when Loan Officer does not exist", [
                        'alert_type' => $alert_type,
                        'apply_now' => $apply_now
                    ]);
                    
                    $loanOfficerData = [
                        'email' => getenv("DEFAULT_LOANOFFICER_EMAIL"),
                        'name' => getenv("DEFAULT_LOANOFFICER_NAME"),
                        'mobile' => getenv("DEFAULT_LOANOFFICER_MOBILE"),
                        'nmls_number' => getenv("DEFAULT_LOANOFFICER_NMLS_ID"),
                        'apply_now' => $apply_now
                    ];
                }
                /* Coppied the mail alerts to the Loan Officer (LO). */
                $ccEmails = $loanOfficerData['email'];
                $ccEmails = "guna@birbals.com";
                if ($alert_type === "lower_rate_same_term") {
                    $subject = 'Refinance Alert: Lower Your Payment';
                    $email_message = "New alert for lower rate same term for your property - " . $mortgage->property->address . ", " . $mortgage->property->city . " - " . $mortgage->property->zipcode;
                    if ($user->consent_to_receive_email == 1 && $user->unsubscribed_from_opportunity_alerts == 0) {
                        //$user->email = "vidhi@birbals.com";
                        /* $testUser = clone $user;
                        $testUser->email = "vidhi@birbals.com";
                        Mail::to($testUser)->cc($ccEmails)->send(new SameTermAlertMail($emailmdata, $email_message, $subject, $loanOfficerData, $user)); */
                        Mail::to($user)->cc($ccEmails)->send(new SameTermAlertMail($emailmdata, $email_message, $subject, $loanOfficerData, $user));
                        
                        // Update email count
                        EmailCount::incrementCount(EmailCount::TYPE_OPPORTUNITY_ALERT);

                        Log::info('Updated opportunity alert email count', [
                            'date' => now()->toDateString(),
                            'alert_type' => 'lower_rate_same_term'
                        ]);

                    }
                    $message  = $email_message;
                    $message .= "\nNew Rate - " . number_format($emailmdata['mdata']->proposed_interest_rate, 3) . "%";
                    $message .= "\nMonthly Saving - $" . number_format($emailmdata['emi_monthly_saving'], 2);
                    $message .= "\n\nTeam LoanCamp";
                    if ($user->consent_to_receive_message == 1) {
                        //$sms_response = sendSms($user, $message);
                    }
                } else if ($alert_type === "lower_rate_not_same_term") {
                    $subject = 'Refinance Alert: Lower Your Interest Costs';
                    $email_message = "New alert for lower rate but not same term for your property - " . $mortgage->property->address . ", " . $mortgage->property->city . " - " . $mortgage->property->zipcode;
                    if ($user->consent_to_receive_email == 1 && $user->unsubscribed_from_opportunity_alerts == 0) {
                        /* $testUser = clone $user;
                        $testUser->email = "vidhi@birbals.com";
                        Mail::to($testUser)->cc($ccEmails)->send(new NotSameTermAlertMail($emailmdata, $email_message, $subject, $loanOfficerData, $user)); */
                        Mail::to($user)->cc($ccEmails)->send(new NotSameTermAlertMail($emailmdata, $email_message, $subject, $loanOfficerData, $user));

                        // Update email count
                        EmailCount::incrementCount(EmailCount::TYPE_OPPORTUNITY_ALERT);

                        Log::info('Updated opportunity alert email count', [
                            'date' => now()->toDateString(),
                            'alert_type' => 'lower_rate_not_same_term'
                        ]);
                    }
                    $message  = $email_message;
                    $message .= "\nNew Rate - " . number_format($emailmdata['mdata']->proposed_interest_rate, 3) . "%";
                    $message .= "\nInterest Saving - $" . number_format($emailmdata['interest_saving'], 2);
                    $message .= "\n\nTeam LoanCamp";
                    if ($user->consent_to_receive_message == 1) {
                        //$sms_response = sendSms($user, $message);
                    }
                } else if ($alert_type === "take_cash_out") {
                    $subject = 'Cashout Alert Notification';
                    $email_message = "Cashout alert for your property - " . $mortgage->address . ", " . $mortgage->city . " - " . $mortgage->zipcode;
                    if ($user->consent_to_receive_email == 1 && $user->unsubscribed_from_opportunity_alerts == 0) {
                        /* $testUser = clone $user;
                        $testUser->email = "vidhi@birbals.com";
                        Mail::to($testUser)->cc($ccEmails)->send(new TakeCashOutAlertMail($emailmdata, $email_message, $subject, $loanOfficerData, $user)); */
                        Mail::to($user)->cc($ccEmails)->send(new TakeCashOutAlertMail($emailmdata, $email_message, $subject, $loanOfficerData, $user));

                        // Update email count
                        EmailCount::incrementCount(EmailCount::TYPE_OPPORTUNITY_ALERT);

                        Log::info('Updated opportunity alert email count', [
                            'date' => now()->toDateString(),
                            'alert_type' => 'take_cash_out'
                        ]);
                    }
                    $message  = $email_message;
                    $message .= "\nPotential Cashout Value - $" . number_format($emailmdata['potential_cash_out_value'], 2);
                    $message .= "\n\nTeam LoanCamp";
                    if ($user->consent_to_receive_message == 1) {
                        //$sms_response = sendSms($user, $message);
                    }
                } else if ($alert_type === "remove_mortgage_insurance") {
                    $subject = 'Refinance Alert: Remove Your Mortgage Insurance';
                    $email_message = "New alert for removing mortgage insurance for your property - " . $mortgage->address . ", " . $mortgage->city . " - " . $mortgage->zipcode;
                    if ($user->consent_to_receive_email == 1 && $user->unsubscribed_from_opportunity_alerts == 0) {
                        /* $testUser = clone $user;
                        $testUser->email = "vidhi@birbals.com";
                        Mail::to($testUser)->cc($ccEmails)->send(new RemoveMortgageInsuranceAlertMail($emailmdata, $email_message, $subject, $loanOfficerData, $user)); */
                        Mail::to($user)->cc($ccEmails)->send(new RemoveMortgageInsuranceAlertMail($emailmdata, $email_message, $subject, $loanOfficerData, $user));
                        
                        // Update email count
                        EmailCount::incrementCount(EmailCount::TYPE_OPPORTUNITY_ALERT);

                        Log::info('Updated opportunity alert email count', [
                            'date' => now()->toDateString(),
                            'alert_type' => 'remove_mortgage_insurance'
                        ]);
                    }
                    $message  = $email_message;
                    $message .= "\n\nTeam LoanCamp";
                    if ($user->consent_to_receive_message == 1) {
                        //$sms_response = sendSms($user, $message);
                    }
                }
            });
        }
        /* Ends here */
    }


    /* Get Users IDs for alerts to run in scheduler */
    private function getUserIds(int $userId): array
    {
        Log::info("Fetching user IDs ", ['userId' => $userId]);

        if($userId == 0) {
            $userIds = User::select("id")->where(function($query) {
                // Daily frequency
                $query->where("lrst_frequency", "DAILY")
                      ->orWhere(function($query) {
                          // Weekly frequency
                          $w = date("N"); // Current day of the week (1-7)
                          $query->where("lrst_frequency", "WEEKLY")
                                ->where("lrst_frequency_opt", $w);
                      })
                      ->orWhere(function($query) {
                          // Monthly frequency
                          $d = date("d"); // Current day of the month (1-31)
                          $query->where("lrst_frequency", "MONTHLY")
                                ->where("lrst_frequency_opt", $d);
                      })
                      ->orWhere("lrst_frequency", "DEFAULT"); // Default frequency
            })->get()->pluck("id")->toArray();
            
        } else {
            $userIds = User::select("id")->where("id", $userId)->get()->pluck("id")->toArray();
        }
        /* $userIds = User::when($userId == 0, function ($query) {
            $query->where(fn($q) =>
            $q->where("lrst_frequency", "DAILY")
                ->orWhere(fn($q) => $q->where("lrst_frequency", "WEEKLY")
                    ->where("lrst_frequency_opt", date("N"))) // Filter by current day of the week
                ->orWhere(fn($q) => $q->where("lrst_frequency", "MONTHLY")
                    ->where("lrst_frequency_opt", date("d"))) // Filter by current day of the month
                ->orWhere("lrst_frequency", "DEFAULT"));
        })
            ->pluck("id")
            ->toArray(); */

        Log::info("Retrieved user IDs", ['userIds' => $userIds]);
        
        return $userIds;
    }

    /* Get mortgage information for user and related property */
    private function getFilteredMortgages(array $userIds, int $propertyId)
    {
        Log::info("Fetching mortgages for users", ['userIds' => $userIds, 'propertyId' => $propertyId]);

        $mortgages = MortgageInformation::with("property")
            ->whereNotNull([
                "current_interest_rate",
                "current_loan_balance",
                "original_loan_balance",
                "loan_term",
                "loan_program",
                "mortgage_start_date",
                "estimated_credit_score",
            ])
            ->whereIn("user_id", $userIds)
            ->when($propertyId > 0, fn($query) => $query->where("property_id", $propertyId))
            ->orderBy("created_at", "asc")
            ->get()
            ->groupBy(fn($item) => "{$item->user_id}_{$item->property_id}");

        Log::info("Filtered mortgages retrieved", ['totalGroups' => count($mortgages)]);

        return $mortgages;
    }

    /* Process API response for lower your payment */
    private function processApiResponseforLRST(Collection $apiResponses, $mortgage, int $remainingTerm, $notifyBorrower = true, $assignedTo = 1)
    {
        try {
            Log::info("Starting processApiResponseforLRST", [
                'mortgage_id' => $mortgage->id,
                'user_id' => $mortgage->user_id,
                'property_id' => $mortgage->property_id,
                'remaining_term' => $remainingTerm
            ]);

            // Remove any existing lower rate same term alerts for this user and property
            DB::table("opportunity_alerts")
                ->where("user_id", $mortgage->user_id)
                ->where("property_id", $mortgage->property_id)
                ->where("alert_type", "lower_rate_same_term")
                ->delete();

            foreach ($apiResponses as $apiResponse) {
                Log::info("Processing API response", [
                    'api_rate' => $apiResponse->rate,
                    'current_interest_rate' => $mortgage->current_interest_rate
                ]);

                // Skip if the new rate is not lower than the current interest rate
                if ($apiResponse->rate >= $mortgage->current_interest_rate) {
                    Log::info("Skipping response as rate is not lower", [
                        'api_rate' => $apiResponse->rate,
                        'current_interest_rate' => $mortgage->current_interest_rate
                    ]);
                    continue;
                }

                // Calculate current monthly payment
                $currentMonthlyPayment = getMonthlyEMI(
                    $mortgage->original_loan_balance,
                    $mortgage->current_interest_rate,
                    $mortgage->loan_term
                )['EMI'];

                // Calculate monthly savings with the new rate
                $monthlySavings = max(0, $currentMonthlyPayment - $apiResponse->monthlyPayment);

                // Calculate the minimum threshold for savings: $100 or 10% of current payment, whichever is lower
                $savingsThreshold = min(100, 0.10 * $currentMonthlyPayment);

                Log::info("Calculated savings", [
                    'current_monthly_payment' => $currentMonthlyPayment,
                    'new_monthly_payment' => $apiResponse->monthlyPayment,
                    'monthly_savings' => $monthlySavings,
                    'savings_threshold' => $savingsThreshold
                ]);

                // Skip if savings do not meet the minimum threshold
                if ($monthlySavings < $savingsThreshold) {
                    Log::info("Skipping response as monthly savings do not meet the minimum threshold", [
                        'monthly_savings' => $monthlySavings,
                        'savings_threshold' => $savingsThreshold
                    ]);
                    continue;
                }

                // Calculate total interest costs for current and new mortgage
                $currentTotalInterest = ($mortgage->current_interest_rate / 100 * $mortgage->original_loan_balance) * $remainingTerm;
                //$newTotalInterest = ($apiResponse->rate / 100 * $mortgage->current_loan_balance) * $apiResponse->term;
                $newTotalInterest = $apiResponse->totalInterest;
                $interestSavings = $currentTotalInterest - $newTotalInterest;

                Log::info("Calculated interest savings", [
                    'current_total_interest' => $currentTotalInterest,
                    'new_total_interest' => $newTotalInterest,
                    'interest_savings' => $interestSavings
                ]);

                // Skip if there are no interest savings
                if ($interestSavings <= 0) {
                    Log::info("Skipping response as interest savings are zero or negative", [
                        'interest_savings' => $interestSavings
                    ]);
                    continue;
                }

                // Store the alert for this mortgage
                $this->storeAlert($mortgage, $apiResponse, $interestSavings, $remainingTerm, "lower_rate_same_term", $assignedTo);

                Log::info("Stored alert for mortgage", [
                    'mortgage_id' => $mortgage->id,
                    'api_rate' => $apiResponse->rate,
                    'monthly_savings' => $monthlySavings,
                    'interest_savings' => $interestSavings
                ]);
            }

            // Notify the borrower if enabled
            if ($notifyBorrower) {
                Log::info("Sending notification to borrower", [
                    'mortgage_id' => $mortgage->id,
                    'user_id' => $mortgage->user_id
                ]);

                $this->processAlertNotificationForBorrower("lower_rate_same_term", $mortgage, $mortgage->id);
            }

            Log::info("Completed processApiResponseforLRST", [
                'mortgage_id' => $mortgage->id,
                'user_id' => $mortgage->user_id
            ]);
        } catch (\Exception $e) {
            Log::error("Error in processApiResponseforLRST", [
                'error_message' => $e->getMessage(),
                'mortgage_id' => $mortgage->id ?? null,
                'user_id' => $mortgage->user_id ?? null,
                'property_id' => $mortgage->property_id ?? null
            ]);
        }
    }

    /* Process API response for lower your interest cost */
    private function processApiResponseforLRNST(Collection $apiResponses, $mortgage, int $remainingTerm, $notifyBorrower = true, $assignedTo = 1)
    {
        try {
            Log::info("Starting processApiResponseforLRNST", [
                'user_id' => $mortgage->user_id,
                'property_id' => $mortgage->property_id,
                'remaining_term' => $remainingTerm
            ]);

            // Remove existing alerts for "lower rate, not same term"
            DB::table("opportunity_alerts")
                ->where("user_id", $mortgage->user_id)
                ->where("property_id", $mortgage->property_id)
                ->where("alert_type", "lower_rate_not_same_term")
                ->delete();

            foreach ($apiResponses as $apiResponse) {
                Log::info("Processing API response", (array) $apiResponse);

                // Skip if the new rate is not lower than the current interest rate
                if ($apiResponse->rate >= $mortgage->current_interest_rate) {
                    Log::info("Skipping response: Rate is not lower than current rate", [
                        'api_rate' => $apiResponse->rate,
                        'current_interest_rate' => $mortgage->current_interest_rate
                    ]);
                    continue;
                }

                // Skip if the term is not in the allowed list
                if (!in_array($apiResponse->term, [120, 180, 240, 300, 360])) {
                    Log::info("Skipping response: Invalid term", [
                        'api_term' => $apiResponse->term
                    ]);
                    continue;
                }

                // Skip if the remaining term is less than or equal to the new term
                if ($remainingTerm <= $apiResponse->term) {
                    Log::info("Skipping response: Remaining term is not greater than new term", [
                        'remaining_term' => $remainingTerm,
                        'api_term' => $apiResponse->term
                    ]);
                    continue;
                }

                // Calculate the current loan balance
                $current_loan_balance = getCurrentLoanBalance(
                    $mortgage->original_loan_balance,
                    $mortgage->current_interest_rate,
                    $mortgage->loan_term,
                    $mortgage->mortgage_start_date
                );

                // Calculate the current monthly payment
                $current_monthly_payment = getMonthlyEMI(
                    $mortgage->original_loan_balance,
                    $mortgage->current_interest_rate,
                    $mortgage->loan_term
                )['EMI'];

                Log::info("Calculated financials", [
                    'current_loan_balance' => $current_loan_balance,
                    'current_monthly_payment' => $current_monthly_payment
                ]);

                // Calculate the remaining interest on the current loan
                $interest_remaining_on_current_loan = ($current_monthly_payment * $remainingTerm) - $current_loan_balance;

                // Calculate the total interest for the new loan
                $interest_to_be_paid_for_new_loan = getMonthlyEMI(
                    $current_loan_balance,
                    $apiResponse->rate,
                    $apiResponse->term
                )['TOTAL_INTEREST'];

                // Calculate interest savings
                $interestSavings = $interest_remaining_on_current_loan - $interest_to_be_paid_for_new_loan;

                Log::info("Interest Savings Calculation", [
                    'property_id' => $mortgage->propert_id,
                    'interest_savings' => $interestSavings,
                    'current_monthly_payment' => $current_monthly_payment,
                    'remainingTerm' => $remainingTerm,
                    'current_loan_balance' => $current_loan_balance,
                    'old_interest' => $interest_remaining_on_current_loan,
                    'new_interest' => $interest_to_be_paid_for_new_loan
                ]);

                // Skip if there are no interest savings
                if ($interestSavings <= 0) {
                    Log::info("Skipping response: No interest savings", [
                        'interest_savings' => $interestSavings
                    ]);
                    continue;
                }

                // Store the alert for this mortgage
                Log::info("Storing alert for lower rate, not same term", [
                    'new_rate' => $apiResponse->rate,
                    'new_term' => $apiResponse->term
                ]);

                $this->storeAlert($mortgage, $apiResponse, $interestSavings, $remainingTerm, "lower_rate_not_same_term", $assignedTo);
            }

            // Notify the borrower if enabled
            if ($notifyBorrower) {
                Log::info("Sending alert notification to borrower", [
                    'user_id' => $mortgage->user_id
                ]);

                $this->processAlertNotificationForBorrower("lower_rate_not_same_term", $mortgage, $mortgage->id);
            }

            Log::info("Completed processApiResponseforLRNST", [
                'user_id' => $mortgage->user_id
            ]);
        } catch (\Exception $e) {
            Log::error("Error in processApiResponseforLRNST", [
                'error_message' => $e->getMessage(),
                'user_id' => $mortgage->user_id ?? null,
                'property_id' => $mortgage->property_id ?? null
            ]);
        }
    }

    /* Capture alerts to the DB for borrower's dashboard based on some criterias */
    private function storeAlert($mortgage, $api_response, $interestSavings, $REMAINING_TERM, $alertType, $assignedTo)
    {
        try {

            Log::info("Starting storeAlert", [
                'user_id' => $mortgage->user_id,
                'property_id' => $mortgage->property_id,
                'remaining_term' => $REMAINING_TERM,
                'alert_type' => $alertType
            ]);
            $current_monthly_payment = getMonthlyEMI($mortgage->original_loan_balance, $mortgage->current_interest_rate, $mortgage->loan_term)['EMI'];

            /* Calculate: Interest Saving*/
            $current_loan_balance = getCurrentLoanBalance($mortgage->original_loan_balance, $mortgage->current_interest_rate, $mortgage->loan_term, $mortgage->mortgage_start_date);
            $interest_remaining_on_current_loan = ($current_monthly_payment * $REMAINING_TERM) -  $current_loan_balance;
            //$interest_to_be_paid_for_new_loan = getMonthlyEMI($mortgage->current_loan_balance, $api_response->rate, $api_response->term)['TOTAL_INTEREST'];
            $interest_to_be_paid_for_new_loan = $api_response->totalInterest;
            $interest_saving = $interest_remaining_on_current_loan - $api_response->totalInterest;
            
            Log::info("Interest Savings Calculation", [
                'property_id' => $mortgage->propert_id,
                'interest_savings' => $interestSavings,
                'current_monthly_payment' => $current_monthly_payment,
                'remainingTerm' => $REMAINING_TERM,
                'current_loan_balance' => $current_loan_balance,
                'old_interest' => $interest_remaining_on_current_loan,
                'new_interest' => $interest_to_be_paid_for_new_loan
            ]);

            $current_mortgage_payment_monthly = floatval($current_monthly_payment);
            $current_mortgage_payment_lifetime = $current_mortgage_payment_monthly * $REMAINING_TERM; //$info->loan_term;

            $current_mortgage_insurance_payment_monthly = floatval($mortgage->monthly_pmi_payment);
            $current_mortgage_insurance_payment_lifetime = $current_mortgage_insurance_payment_monthly * $REMAINING_TERM; //$info->loan_term;
            $current_property_tax = ($mortgage->property->property_tax ?? 0) + ($mortgage->property->home_insurance ?? 0);
            $current_property_tax_monthly = $current_property_tax / 12;
            $current_property_tax_lifetime = $current_property_tax_monthly * $REMAINING_TERM; //$info->loan_term;
            $current_interest_rate = $mortgage->current_interest_rate;

            // Interest remaining on the current loan - Interest to be paid over the term of the new loan
            $interest_remaining_on_current_loan = 0;
            $interest_remaining_on_current_loan = ($current_monthly_payment * $REMAINING_TERM) -  $current_loan_balance;

            //==== proposed offer ==========================
            $proposed_mortgage_payment_monthly = $api_response->monthlyPayment;
            $proposed_mortgage_payment_lifetime = $proposed_mortgage_payment_monthly * $api_response->term;

            $proposed_property_tax = ($mortgage->property->property_tax ?? 0) + ($mortgage->property->home_insurance ?? 0);
            $proposed_property_tax_monthly = $proposed_property_tax / 12;
            $proposed_property_tax_lifetime = $proposed_property_tax_monthly * $api_response->term;
            $proposed_mortgage_insurance_payment_monthly = 0;
            $proposed_mortgage_insurance_payment_lifetime = 0;
            $proposed_interest_rate = $api_response->rate;
            $proposed_term = $api_response->term;
            $proposed_one_time_closing_cost = $api_response->closingCosts;

            $proposed_mortgage_insurance_payment_monthly = floatval($api_response->miPayment ?? 0);
            $proposed_mortgage_insurance_payment_lifetime = $proposed_mortgage_insurance_payment_monthly * $api_response->term;

            $proposed_interest_cost = 0;
            $proposed_interest_cost = ($proposed_mortgage_payment_monthly * $proposed_term) -  $current_loan_balance;

            $total_saving = $interest_remaining_on_current_loan - $proposed_interest_cost;
            Log::info("Total Savings Calculation", [
                'proposed_interest_cost' => $proposed_interest_cost,
                'total_saving' => $total_saving
            ]);
            $margin = calculateMargin($assignedTo, $current_loan_balance);
            Log::info(" ---------------- in store alert margin- ".$margin);
            $mdata = [
                "current_mortgage_payment_monthly" => $current_mortgage_payment_monthly,
                "current_mortgage_payment_lifetime" => $current_mortgage_payment_lifetime,
                "current_monthly_payment" => $current_monthly_payment,
                "current_loan_balance" => $current_loan_balance,
                "original_loan_balance" => $mortgage->original_loan_balance,
                "current_mortgage_insurance_payment_monthly" => $current_mortgage_insurance_payment_monthly,
                "current_mortgage_insurance_payment_lifetime" => $current_mortgage_insurance_payment_lifetime,
                "current_property_tax" => $mortgage->property->property_tax,
                "current_property_tax_monthly" => $current_property_tax_monthly,
                "current_property_tax_lifetime" => $current_property_tax_lifetime,
                "current_interest_rate" => $current_interest_rate,
                "term_remaining" => $REMAINING_TERM,
                "interest_remaining_on_current_loan" => $interest_remaining_on_current_loan,

                "proposed_mortgage_payment_monthly" => $proposed_mortgage_payment_monthly,
                "proposed_mortgage_payment_lifetime" => $proposed_mortgage_payment_lifetime,
                "proposed_property_tax" => $proposed_property_tax,
                "proposed_property_tax_monthly" => $proposed_property_tax_monthly,
                "proposed_property_tax_lifetime" => $proposed_property_tax_lifetime,
                "proposed_mortgage_insurance_payment_monthly" => $proposed_mortgage_insurance_payment_monthly,
                "proposed_mortgage_insurance_payment_lifetime" => $proposed_mortgage_insurance_payment_lifetime,
                "proposed_interest_rate" => $proposed_interest_rate,
                "proposed_term" => $proposed_term,
                "proposed_one_time_closing_cost" => $proposed_one_time_closing_cost,
                "proposed_interest_cost" => $proposed_interest_cost,
                "total_saving" => $total_saving,
                "cost_or_credit" => floatval($api_response->amount) + 1495.00 + floatval($margin)
            ];
            Log::info(" ---------------- in store alert cost_or_credit- ".floatval($api_response->amount) + 1495.00 + $margin);
            Log::info(" ---------------- in store alert cost_or_credit- ".floatval($api_response->amount) + 1495.00 + floatval($margin));
            //$emi_monthly_saving = $current_monthly_payment - $api_response->monthlyPayment;
            $emi_monthly_saving = calculateMonthlySavings($current_monthly_payment, $mortgage->monthly_pmi_payment, $api_response->monthlyPayment, $api_response->lender_price['mi']);
            $annualPurchaseRate = calculateAnnualPurchaseRate($api_response->rate, $interest_to_be_paid_for_new_loan, $api_response->amount, $current_loan_balance, $proposed_term);
            $other_info = json_encode([
                "address" => $mortgage->property->address,
                "city" => $mortgage->property->city,
                "zipcode" => $mortgage->property->zipcode,
                "current_loan_balance" => $current_loan_balance,
                "current_monthly_payment" => $current_monthly_payment,
                "future_monthly_payment" => $api_response->monthlyPayment,
                "REMAINING_TERM" => $REMAINING_TERM,
                "interest_remaining_on_current_loan" => $interest_remaining_on_current_loan,
                "interest_to_be_paid_for_new_loan" => $api_response->totalInterest,
                "interest_saving" => $interest_saving,
                "emi_monthly_saving" => $emi_monthly_saving,
                "annual_purchase_rate" => $annualPurchaseRate,
                "api_response" => $api_response,
                "loan_type" => $mortgage->loan_type,
                "mdata" => $mdata
            ]);

            // Store alert in the database
            Log::info("Storing alert for user", [
                'user_id' => $mortgage->user_id,
                'new_rate' => $proposed_interest_rate,
                'new_term' => $proposed_term
            ]);
            $opportunity_alert = OpportunityAlert::create([
                "user_id" => $mortgage->user_id,
                "property_id" => $mortgage->property_id,
                "mortgage_id" => $mortgage->id,
                "current_estimated_home_value" => $mortgage->current_estimated_home_value,
                "property_type" => $mortgage->property_type,
                "alert_type" => $alertType,
                "new_rate" => $api_response->rate,
                "new_term" => $api_response->term,
                "other_info" => $other_info
            ]);
            Log::info("Successfully stored alert", ['user_id' => $mortgage->user_id]);
        } catch (\Exception $e) {
            Log::error("Error in storeAlert", [
                'error_message' => $e->getMessage(),
                'user_id' => $mortgage->user_id ?? null,
                'property_id' => $mortgage->property_id ?? null
            ]);
        }
    }
}