<?php

namespace App\Http\Controllers\pages;

use App\Http\Controllers\Controller;
use App\Models\deathdonation;
use App\Models\deathsubscription;
use App\Models\division;
use App\Models\divisiondetail;
use App\Models\loan;
use App\Models\loanpurpose;
use App\Models\loanrepayment;
use App\Models\loanschedule;
use App\Models\Meeting;
use App\Models\member;
use App\Models\otherincome;
use App\Models\profession;
use App\Models\subprofession;
use App\Models\saving;
use App\Models\smallgroup;
use App\Models\smallgroupdetail;
use App\Models\userRole;
use App\Models\village;
use App\Models\villagedetail;
use Crypt;
use Illuminate\Foundation\Auth\User;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use Carbon\Carbon;


class manage_reports_controller extends Controller

{

    function viewMemberSavingReport($id){
        $getUserRole = userRole::all();
        $getSmallGroup = smallgroup::all();
        $getVillage = village::all();
        $getDivision = division::all();
        $getMember = member::all();
        $getLoansData = loan::all();
        $getAllMemberData = member::all();
        $getSavingData = saving::all();
        $getLoanRepaymentData = loanschedule::all();
        $getDeathSubscription = deathsubscription::all();
        $getDeathDonation = deathdonation::all();
        $getOtherIncome = otherincome::all();
        $getDivisionDetails = divisiondetail::all();
        $getUsers = User::all();
        $getVillageDetails = villagedetail::all();
        $getProfession = profession::all();
        $getSubProfession = subprofession::all();
        $getSmallGroupDetails = smallgroupdetail::all();
        $getLoanRepaymentData = loanschedule::all();
        $getLoanRepaymentData = loanschedule::all();
        $getData = DB::table('accounttransectionhistories')->where('memberId', Crypt::decrypt($id))->get();
        return view( 'pages.permission.reports.view_member_saving_report_per' , compact( 'getData' , 'getUserRole', 'getSmallGroup', 'getVillage', 'getDivision', 'getMember', 'getLoansData', 'getAllMemberData', 'getSavingData', 'getLoanRepaymentData', 'getDeathSubscription', 'getDeathDonation', 'getOtherIncome', 'getDivisionDetails', 'getUsers', 'getVillageDetails', 'getProfession', 'getSubProfession', 'getSmallGroupDetails', 'getLoanRepaymentData', 'getLoanRepaymentData'));
    }


//group leader report
    function groupLeaderReport()
    {
        $getUserRole = userRole::all();
        $getSmallGroup = smallgroup::all();
        $getVillage = village::all();
        $getDivision = division::all();
        $getMember = member::all();
        $getLoansData = loan::all();
        $getAllMemberData = member::all();
        $getSavingData = saving::all();
        $getLoanRepaymentData = loanschedule::all();
        $getDeathSubscription = deathsubscription::all();
        $getDeathDonation = deathdonation::all();
        $getOtherIncome = otherincome::all();
        $getDivisionDetails = divisiondetail::all();
        $getUsers = User::all();
        $getVillageDetails = villagedetail::all();
        $getSmallGroupDetails = smallgroupdetail::all();
        return view('pages.permission.reports.group_leader_report_per', ['getSmallGroupDetails' => $getSmallGroupDetails, 'getVillageDetails' => $getVillageDetails, 'getUsers' => $getUsers, 'getDivisionDetails' => $getDivisionDetails, 'getOtherIncome' => $getOtherIncome, 'getDeathDonation' => $getDeathDonation, 'getDeathSubscription' => $getDeathSubscription, 'getLoanRepaymentData' => $getLoanRepaymentData, 'getSavingData' => $getSavingData, 'getUserRole' => $getUserRole, 'getSmallGroup' => $getSmallGroup, 'getVillage' => $getVillage, 'getDivision' => $getDivision, 'getMember' => $getMember, 'getLoansData' => $getLoansData, 'getAllMemberData' => $getAllMemberData]);
    }


// group report
    function groupReport()
    {
        $getUserRole = userRole::all();
        $getSmallGroup = smallgroup::all();
        $getVillage = village::all();
        $getDivision = division::all();
        $getMember = member::all();
        $getLoansData = loan::all();
        $getAllMemberData = member::all();
        $getSavingData = saving::all();
        $getLoanRepaymentData = loanschedule::all();
        $getDeathSubscription = deathsubscription::all();
        $getDeathDonation = deathdonation::all();
        $getOtherIncome = otherincome::all();
        return view('pages.permission.reports.group_report_per', ['getOtherIncome' => $getOtherIncome, 'getDeathDonation' => $getDeathDonation, 'getDeathSubscription' => $getDeathSubscription, 'getLoanRepaymentData' => $getLoanRepaymentData, 'getSavingData' => $getSavingData, 'getUserRole' => $getUserRole, 'getSmallGroup' => $getSmallGroup, 'getVillage' => $getVillage, 'getDivision' => $getDivision, 'getMember' => $getMember, 'getLoansData' => $getLoansData, 'getAllMemberData' => $getAllMemberData]);
    }



//loan arrears report
public function loanArreasReport(Request $request)
    {
        $arrearsDate = $request->input('arrearsDate')
            ? Carbon::parse($request->input('arrearsDate'))
            : Carbon::today();

        $divisionId = $request->divisionId;
        $villageId = $request->villageId;
        $smallgroupId = $request->smallgroupId;
        $loanMin = $request->loanMin;
        $loanMax = $request->loanMax;

        // Base query
        $query = DB::table('loans as l')
            ->join('members as m', 'l.memberId', '=', 'm.id')
            ->leftJoin('divisions as d', 'm.divisionId', '=', 'd.id')
            ->leftJoin('villages as v', 'm.villageId', '=', 'v.id')
            ->leftJoin('smallgroups as s', 'm.smallGroupId', '=', 's.id')
            ->leftJoin('users as u', 'l.loanOfficer', '=', 'u.id')
            ->leftJoin('gndivisions as gd', 'm.gnDivisionId', '=', 'gd.id')
            ->select(
                'l.id as id',
                'l.loanId',
                'l.principal',
                'l.interestRate',
                'l.loanStatus',
                'l.loanOfficer',
                'u.name as loanOfficerName',
                'm.id as memberId',
                'm.firstName',
                'm.lastName',
                'm.nicNumber',
                'm.oldAccountNumber',
                'm.divisionId',
                'm.villageId',
                'm.smallGroupId',
                'd.divisionName',
                'v.villageName',
                's.smallGroupName',
                'gd.gnDivisionName'
            )
            ->where('l.loanStatus', 'Active');

        if ($divisionId) $query->where('m.divisionId', $divisionId);
        if ($villageId) $query->where('m.villageId', $villageId);
        if ($smallgroupId) $query->where('m.smallGroupId', $smallgroupId);
        if ($loanMin) $query->where('l.principal', '>=', $loanMin);
        if ($loanMax) $query->where('l.principal', '<=', $loanMax);

        $getLoanData = $query->get();

        if ($getLoanData->isEmpty()) {
            return view('pages.permission.reports.loan_arreas_report_per', [
                'getLoanData' => collect(),
                'getDivision' => division::all(),
                'getUserRole' => userRole::all(),
                'getLoansData' => loan::all(),
                'getAllMemberData' => member::all(),
                'totalMembers' => 0,
                'totalDivisions' => 0,
                'totalVillages' => 0,
                'totalSmallGroups' => 0,
                'totalLoanAmount' => 0,
                'totalTotalArrears' => 0,
                'totalInterestArrears' => 0,
                'arrearsByDivision' => [],
                'arrearsDate' => $arrearsDate->format('Y-m-d'),
                'request' => $request,
            ]);
        }

        $loanIds = $getLoanData->pluck('id')->toArray();

        // Load schedules
        $loanSchedules = DB::table('loanschedules')
            ->whereIn('loanId', $loanIds)
            ->where('paymentDate', '<=', $arrearsDate)
            ->orderBy('paymentDate', 'asc')
            ->get(['loanId', 'principalPayment', 'paymentDate'])
            ->groupBy('loanId');

        // Load total principal repayments before arrearsDate
        $loanRepayments = DB::table('loanrepayments')
            ->whereIn('loanId', $loanIds)
            ->where('repaymentDate', '<=', $arrearsDate)
            ->select('loanId', DB::raw('SUM(principalAmount) as totalPaid'))
            ->groupBy('loanId')
            ->pluck('totalPaid', 'loanId');

        // Load last repayment dates before arrearsDate
        $lastRepayments = DB::table('loanrepayments')
            ->whereIn('loanId', $loanIds)
            ->where('repaymentDate', '<=', $arrearsDate)
            ->select('loanId', DB::raw('MAX(repaymentDate) as lastRepaymentDate'))
            ->groupBy('loanId')
            ->pluck('lastRepaymentDate', 'loanId');

        $today = Carbon::today();

        foreach ($getLoanData as $loan) {
            $schedules = $loanSchedules->get($loan->id, collect());

            // Loan Date = first payment date from schedules
            $loan->loanDate = $schedules->first()?->paymentDate;

            $totalPaid = $loanRepayments->get($loan->id, 0);

            // Calculate Due Terms
            $remainingPaid = $totalPaid;
            $dueTermsCount = 0;
            foreach ($schedules as $schedule) {
                $scheduled = $schedule->principalPayment;
                if ($remainingPaid >= $scheduled) {
                    $remainingPaid -= $scheduled;
                } else {
                    $dueTermsCount++;
                    $remainingPaid = 0;
                }
            }
            $loan->dueTerms = $dueTermsCount;

            // Calculate Principal Arrears
            $totalArrears = 0;
            $nextPaymentDate = null;
            $daysOverdue = 0;
            $remainingPaidForArrears = $totalPaid;
            foreach ($schedules as $schedule) {
                $scheduled = $schedule->principalPayment;
                if ($remainingPaidForArrears >= $scheduled) {
                    $remainingPaidForArrears -= $scheduled;
                    $scheduled = 0;
                } else {
                    $scheduled -= $remainingPaidForArrears;
                    $remainingPaidForArrears = 0;
                }

                if ($scheduled > 0) {
                    $totalArrears += $scheduled;

                    if (!$nextPaymentDate) {
                        $nextPaymentDate = $schedule->paymentDate;
                    }

                    $daysOverdue = max($daysOverdue, $today->diffInDays(Carbon::parse($schedule->paymentDate)));
                }
            }

            $loanOutstanding = $loan->principal - $totalPaid;

            // Last repayment date (before arrearsDate) or "No Payment"
            $lastRepayment = $lastRepayments->get($loan->id);
            if ($lastRepayment) {
                $loan->lastRepaymentDate = Carbon::parse($lastRepayment)->format('Y-m-d');
                $interestDays = $arrearsDate->greaterThan(Carbon::parse($lastRepayment))
                    ? Carbon::parse($lastRepayment)->diffInDays($arrearsDate)
                    : 0;
            } else {
                $loan->lastRepaymentDate = 'No Payment';
                $interestDays = $arrearsDate->greaterThan(Carbon::parse($loan->loanDate ?? $arrearsDate))
                    ? Carbon::parse($loan->loanDate ?? $arrearsDate)->diffInDays($arrearsDate)
                    : 0;
            }

            $interestRatePercent = $loan->interestRate ?? 12;
            $interestArrears = ($loanOutstanding * $interestRatePercent * $interestDays) / 36500;

            $loan->loanOutstanding = round($loanOutstanding, 2);
            $loan->totalArrears = round($totalArrears, 2);
            $loan->interestArrears = round($interestArrears, 2);
            $loan->interestDays = $interestDays;
            $loan->nextPaymentDate = $nextPaymentDate;
            $loan->daysOverdue = $daysOverdue;
        }

        // Filter loans with any arrears (principal or interest)
        $getLoanData = $getLoanData
            ->filter(fn($loan) => ($loan->totalArrears + $loan->interestArrears) > 0)
            ->values();

        // Totals
        $totalMembers = $getLoanData->unique('memberId')->count();
        $totalDivisions = $getLoanData->unique('divisionName')->count();
        $totalVillages = $getLoanData->unique('villageName')->count();
        $totalSmallGroups = $getLoanData->unique('smallGroupName')->count();
        $totalLoanAmount = $getLoanData->sum('principal');
        $totalTotalArrears = $getLoanData->sum('totalArrears');
        $totalInterestArrears = $getLoanData->sum('interestArrears');
        $totalAllArrears = $getLoanData->sum(fn($loan) => $loan->totalArrears + $loan->interestArrears);

        $arrearsByDivision = $getLoanData->groupBy('divisionName')->map(fn($loans) => [
            'division' => $loans->first()->divisionName ?? 'Unknown',
            'totalArrears' => $loans->sum('totalArrears') + $loans->sum('interestArrears'),
        ])->values();

        return view('pages.permission.reports.loan_arreas_report_per', [
            'getLoanData' => $getLoanData,
            'getDivision' => division::all(),
            'getUserRole' => userRole::all(),
            'getLoansData' => loan::all(),
            'getAllMemberData' => member::all(),
            'totalMembers' => $totalMembers,
            'totalDivisions' => $totalDivisions,
            'totalVillages' => $totalVillages,
            'totalSmallGroups' => $totalSmallGroups,
            'totalLoanAmount' => $totalLoanAmount,
            'totalTotalArrears' => $totalTotalArrears,
            'totalInterestArrears' => $totalInterestArrears,
            'totalAllArrears' => $totalAllArrears,
            'arrearsByDivision' => $arrearsByDivision,
            'arrearsDate' => $arrearsDate->format('Y-m-d'),
            'request' => $request,
        ]);
    }



// loan report

public function loanReport(Request $request)
{
    // Parse From/To dates
    $fromDate = $request->input('fromDate') 
        ? Carbon::parse($request->input('fromDate'))->startOfDay() 
        : now()->startOfMonth();
    $toDate = $request->input('toDate') 
        ? Carbon::parse($request->input('toDate'))->endOfDay() 
        : now()->endOfDay();

    // Get loans with first schedule date as loanDate
    $loans = DB::table('loans as l')
        ->leftJoin('users as u', 'l.loanOfficer', '=', 'u.id')
        ->leftJoin(DB::raw('(SELECT loanId, MIN(paymentDate) as loanDate 
                             FROM loanschedules 
                             GROUP BY loanId) as ls'), 'l.id', '=', 'ls.loanId')
        ->where(function($query) use ($fromDate, $toDate) {
            $query->whereDate('ls.loanDate', '>=', $fromDate)
                  ->whereDate('ls.loanDate', '<=', $toDate)
                  ->orWhereNull('ls.loanDate'); // include loans without schedules
        })
        ->select('l.*', 'u.name as loanOfficerName', 'ls.loanDate')
        ->get();

    $loanIds = $loans->pluck('id')->toArray();

    // Loan schedules up to To Date
    $loanSchedules = DB::table('loanschedules')
        ->whereIn('loanId', $loanIds)
        ->where('paymentDate', '<=', $toDate)
        ->orderBy('paymentDate')
        ->get(['loanId', 'principalPayment', 'paymentDate'])
        ->groupBy('loanId');

    // Total repayments up to To Date
    $loanRepayments = DB::table('loanrepayments')
        ->whereIn('loanId', $loanIds)
        ->where('repaymentDate', '<=', $toDate)
        ->select('loanId', DB::raw('SUM(principalAmount) as totalPaid'))
        ->groupBy('loanId')
        ->pluck('totalPaid', 'loanId');

    foreach ($loans as $loan) {
        $schedules = $loanSchedules->get($loan->id, collect());
        $totalPaid = $loanRepayments->get($loan->id, 0);

        // Calculate due terms
        $remainingPaid = $totalPaid;
        $dueTermsCount = 0;
        foreach ($schedules as $schedule) {
            if ($remainingPaid >= $schedule->principalPayment) {
                $remainingPaid -= $schedule->principalPayment;
            } else {
                $dueTermsCount++;
            }
        }
        $loan->dueTerms = $dueTermsCount;

        // Principal arrears
        $remainingPaidForArrears = $totalPaid;
        $principalArrears = 0;
        foreach ($schedules as $schedule) {
            $scheduled = $schedule->principalPayment;
            if ($remainingPaidForArrears >= $scheduled) {
                $remainingPaidForArrears -= $scheduled;
                $scheduled = 0;
            } else {
                $scheduled -= $remainingPaidForArrears;
                $remainingPaidForArrears = 0;
            }
            $principalArrears += $scheduled;
        }
        $loan->principalArrears = round($principalArrears, 2);

        // Interest arrears
        $lastRepaymentDate = DB::table('loanrepayments')
            ->where('loanId', $loan->id)
            ->where('repaymentDate', '<=', $toDate)
            ->orderByDesc('repaymentDate')
            ->value('repaymentDate');

        $lastRepaymentDate = $lastRepaymentDate 
            ? Carbon::parse($lastRepaymentDate) 
            : Carbon::parse($loan->loanDate ?? now()); // fallback if loanDate is null

        $interestDays = $lastRepaymentDate->diffInDays($toDate);
        $interestRatePercent = $loan->interestRate ?? 12;
        $loanOutstanding = $loan->principal - $totalPaid;
        $interestArrears = ($loanOutstanding * $interestRatePercent * $interestDays) / 36500;
        $loan->interestArrears = round($interestArrears, 2);

        // Loan Outstanding
        $loan->loanOutstanding = round($loanOutstanding, 2);
    }

    // Load other reference data
    $getUserRole = userRole::all();
    $getAllMemberData = member::with(['division', 'village', 'smallgroup'])->get();
    $getDivision = division::all();
    $getLoanPurposeData = loanpurpose::all();
    $getAllRepaymentData = loanrepayment::all();

    return view('pages.permission.reports.loan_report_per', [
        'getLoanPurposeData'   => $getLoanPurposeData,
        'getDivision'          => $getDivision,
        'getUserRole'          => $getUserRole,
        'getLoansData'         => $loans,
        'getAllMemberData'     => $getAllMemberData,
        'getAllRepaymentData'  => $getAllRepaymentData,
        'fromDate'             => $fromDate->format('Y-m-d'),
        'toDate'               => $toDate->format('Y-m-d'),
    ]);
}


//collection report
public function collectionReport(Request $request)
{
    $getUserRole = userRole::all();
    $getLoansData = loan::all();
    $getAllMemberData = member::all();
    $getDivision = division::all();
    $getVillage = village::all();
    $getSmallGroup = smallgroup::all();
    $getUser = DB::table('users')->where('userType', 'Field Officer')->get();

    // Default date range
    $fromDate = $request->fromDate ?? now()->toDateString();
    $toDate   = $request->toDate ?? now()->toDateString();

    // Fetch collection data
    $query = DB::table('accounttransectionhistories')
        ->join('users', 'accounttransectionhistories.collectionBy', '=', 'users.id')
        ->join('members', 'accounttransectionhistories.memberId', '=', 'members.id')
        ->join('divisions', 'members.divisionId', '=', 'divisions.id')
        ->join('villages', 'members.villageId', '=', 'villages.id')
        ->join('smallgroups', 'members.smallGroupId', '=', 'smallgroups.id')
        ->select(
            'accounttransectionhistories.*',
            'users.name as collectedBy',
            'members.id as memberId',
            'members.firstName as memberFirstName',
            'members.lastName as memberLastName',
            'members.nicNumber',
            'members.oldAccountNumber',
            'members.divisionId as divisionId',
            'members.villageId as villageId',
            'members.smallGroupId as smallGroupId',
            'divisions.divisionName',
            'villages.villageName',
            'smallgroups.smallGroupName'
        )
        ->whereBetween('accounttransectionhistories.repaymentDate', [$fromDate, $toDate])
        ->orderBy('accounttransectionhistories.repaymentDate', 'desc')
        ->get();

    // Group by member + date
    $groupedData = $query->groupBy(function($item){
        return $item->memberId.'|'.\Carbon\Carbon::parse($item->repaymentDate)->format('Y-m-d');
    });

    $finalData = $groupedData->map(function($group){
        $first = $group->first();
        $totalSaving = $group->filter(fn($t) => str_contains($t->description,'Saving'))->sum('amount');
        $totalPrincipal = $group->filter(fn($t) => str_contains($t->description,'Loan'))->sum('principalAmount');
        $totalInterest = $group->filter(fn($t) => str_contains($t->description,'Loan'))->sum('interest');

        return [
            'collectionDate' => \Carbon\Carbon::parse($first->repaymentDate)->format('Y-m-d'),
            'memberId' => $first->memberId,
            'divisionId' => $first->divisionId,
            'villageId' => $first->villageId,
            'smallGroupId' => $first->smallGroupId,
            'memberFirstName' => $first->memberFirstName,
            'memberLastName' => $first->memberLastName,
            'nicNumber' => $first->nicNumber,
            'oldAccountNumber' => $first->oldAccountNumber,
            'divisionName' => $first->divisionName,
            'villageName' => $first->villageName,
            'smallGroupName' => $first->smallGroupName,
            'collectedBy' => $first->collectedBy,
            'totalSaving' => $totalSaving,
            'totalPrincipal' => $totalPrincipal,
            'totalInterest' => $totalInterest,
            'totalAmount' => $totalSaving + $totalPrincipal + $totalInterest,
            'descriptions' => $group->pluck('description')->unique()->implode(', ')
        ];
    })->values();

    return view('pages.permission.reports.collection_reports_per', [
        'getUser' => $getUser,
        'getDivision' => $getDivision,
        'getVillage' => $getVillage,
        'getSmallGroup' => $getSmallGroup,
        'getUserRole' => $getUserRole,
        'getLoansData' => $getLoansData,
        'getAllMemberData' => $getAllMemberData,
        'getCollectionData' => $finalData,
        'fromDate' => $fromDate,
        'toDate' => $toDate
    ]);
}


// manager report
    function manageReports()
    {
        $getUserRole = userRole::all();
        $getLoansData = loan::all();
        $getAllMemberData = member::all();
        return view('pages.permission.reports.manage_reports_per', ['getUserRole' => $getUserRole, 'getLoansData' => $getLoansData, 'getAllMemberData' => $getAllMemberData]);
    }

   
//member report
public function memberReport(Request $request)
    {
        $filterDate = $request->input('filter_date') 
            ? Carbon::parse($request->input('filter_date')) 
            : today();
        
        // Master Data
        $getUserRole      = userRole::all();
        $getDivision      = division::all();
        $getProfession    = profession::all();
        $villages         = village::all();
        $smallGroups      = smallgroup::all();
        
        
        
        // Build base member query with relationships
        $getMemberQuery = member::with('division', 'village', 'smallgroup', 'gnDivision');
        
        // Apply filters to member query
        $this->applyMemberFilters($getMemberQuery, $request);
        
        $getMember = $getMemberQuery->get();

        
        // Get member IDs for the current page
        $memberIds = $getMember->pluck('id')->toArray(); // Database IDs
        $memberUniqueIds = $getMember->pluck('uniqueId')->toArray(); // Unique IDs
        
        // Build query for all members (for totals)
        $allMembersQuery = member::query();
        $this->applyMemberFilters($allMembersQuery, $request);
        $allMemberIds = $allMembersQuery->distinct()->pluck('id')->toArray(); // Database IDs for totals
        $allMemberUniqueIds = $allMembersQuery->distinct()->pluck('uniqueId')->toArray(); // Unique IDs for totals
        
        // Get financial data for current page members (using uniqueId)
        $savingsOnDate = $this->getSavingsOnDate($memberUniqueIds, $filterDate);
        $otherIncomeOnDate = $this->getOtherIncomeOnDate($memberUniqueIds, $filterDate);
        $deathSubscriptionOnDate = $this->getDeathSubscriptionOnDate($memberUniqueIds, $filterDate);
        
        // Get totals for all members (using uniqueId)
        $totalSavingsAll = $this->getTotalSavings($allMemberUniqueIds, $filterDate);
        $totalOtherIncomeAll = $this->getTotalOtherIncome($allMemberUniqueIds, $filterDate);
        $totalDeathSubAll = $this->getTotalDeathSubscription($allMemberUniqueIds, $filterDate);
        
        // Get raw loan data for current page members (for blade compatibility)
        $getLoansData = loan::whereDate('created_at', '<=', $filterDate)
                            ->whereIn('memberId', $memberIds) // Using database IDs
                            ->get();
        
        // Get loan data for current page members (using database IDs)
        $loanData = $this->getLoanData($memberIds, $filterDate);
        
        // Get loan totals for all members (using database IDs)
        $loanTotals = $this->getLoanTotals($allMemberIds, $filterDate);
        
        // Ensure all members have entries in financial arrays
        $this->ensureFinancialDefaults($memberUniqueIds, $savingsOnDate, $otherIncomeOnDate, $deathSubscriptionOnDate);
        
        // Prepare data for view
        $data = compact(
            'getUserRole', 'getMember', 'getDivision', 'getProfession',
            'villages', 'smallGroups', 'savingsOnDate', 'otherIncomeOnDate',
            'deathSubscriptionOnDate', 'filterDate', 'getLoansData',
            'totalSavingsAll', 'totalOtherIncomeAll', 'totalDeathSubAll'
        );
        
        // Add loan data to the view data
        $data = array_merge($data, [
            'totalCountOfTotalLoansAll' => $loanTotals['totalCountOfTotalLoansAll'],
            'totalLoansAmountAll' => $loanTotals['totalLoansAmountAll'],
            'totalActiveLoansAll' => $loanTotals['totalActiveLoansAll'],
            'totalActiveLoanAmountAll' => $loanTotals['totalActiveLoanAmountAll'],
            'totalLoanOutstandingAll' => $loanTotals['totalLoanOutstandingAll'],
            'totalPrincipalArrearsAll' => $loanTotals['totalPrincipalArrearsAll'],
            'totalInterestArrearsAll' => $loanTotals['totalInterestArrearsAll'],
            'activeLoansByMember' => $loanData['activeLoansByMember'],
            'loanOutstandingByMember' => $loanData['loanOutstandingByMember'],
            'principalArrearsByMember' => $loanData['principalArrearsByMember'],
            'dueTermsByMember' => $loanData['dueTermsByMember'],
            'interestArrearsByMember' => $loanData['interestArrearsByMember'],
            'totalActiveLoanCountByMember' => $loanData['totalActiveLoanCountByMember'],
            'totalLoanAmountByMember' => $loanData['totalLoanAmountByMember'],
            'totalLoanCountByMember' => $loanData['totalLoanCountByMember']
        ]);
        
        return view('pages.permission.reports.member_report_per', $data);
    }

    /**
     * Apply common filters to member queries
     */
    private function applyMemberFilters($query, $request)
    {
        // Apply role-based filtering if needed
        if (auth()->user()->userType != 'superAdmin') {
            $divArray = json_decode(auth()->user()->division);
            $vilArray = json_decode(auth()->user()->village);
            
            if ($divArray && $vilArray) {
                $query->whereIn('divisionId', $divArray)
                      ->whereIn('villageId', $vilArray);
            }
        }
        
        // Apply advanced filters
        if ($request->filled('division')) {
            $query->where('divisionId', $request->division);
        }
        
        if ($request->filled('village')) {
            $query->where('villageId', $request->village);
        }
        
        if ($request->filled('gn_division')) {
            $query->where('gnDivisionId', $request->gn_division);
        }
        
        if ($request->filled('small_group')) {
            $query->where('smallGroupId', $request->small_group);
        }
        
        return $query;
    }

    /**
     * Get savings data for specific members up to filter date
     */
    private function getSavingsOnDate($memberUniqueIds, $filterDate)
    {
        return DB::table('savingtransectionhistories')
            ->select('memberId', DB::raw("
                SUM(
                    CASE 
                        WHEN type = 'Credit' THEN amount
                        WHEN type = 'Debit'  THEN -amount
                        ELSE 0
                    END
                ) as total
            "))
            ->whereDate('created_at', '<=', $filterDate)
            ->whereIn('memberId', $memberUniqueIds)
            ->groupBy('memberId')
            ->pluck('total', 'memberId')
            ->toArray();
    }

    /**
     * Get other income data for specific members up to filter date
     */
    private function getOtherIncomeOnDate($memberUniqueIds, $filterDate)
    {
        return DB::table('otherincomehistories')
            ->select('memberId', DB::raw('SUM(amount) as total'))
            ->whereDate('created_at', '<=', $filterDate)
            ->whereIn('memberId', $memberUniqueIds)
            ->groupBy('memberId')
            ->pluck('total', 'memberId')
            ->toArray();
    }

    /**
     * Get death subscription data for specific members up to filter date
     */
    private function getDeathSubscriptionOnDate($memberUniqueIds, $filterDate)
    {
        return DB::table('deathtransectionhistories')
            ->select('memberId', DB::raw('SUM(amount) as total'))
            ->whereDate('created_at', '<=', $filterDate)
            ->whereIn('memberId', $memberUniqueIds)
            ->groupBy('memberId')
            ->pluck('total', 'memberId')
            ->toArray();
    }

    /**
     * Get total savings for all members up to filter date
     */
    private function getTotalSavings($allMemberUniqueIds, $filterDate)
    {
        return DB::table('savingtransectionhistories')
            ->join('members', 'savingtransectionhistories.memberId', '=', 'members.uniqueId')
            ->whereDate('savingtransectionhistories.created_at', '<=', $filterDate)
            ->whereIn('members.uniqueId', $allMemberUniqueIds)
            ->select(DB::raw("
                SUM(
                    CASE 
                        WHEN type = 'Credit' THEN amount
                        WHEN type = 'Debit'  THEN -amount
                        ELSE 0
                    END
                ) as total
            "))
            ->value('total') ?? 0;
    }

    /**
     * Get total other income for all members up to filter date
     */
    private function getTotalOtherIncome($allMemberUniqueIds, $filterDate)
    {
        return DB::table('otherincomehistories')
            ->join('members', 'otherincomehistories.memberId', '=', 'members.uniqueId')
            ->whereDate('otherincomehistories.created_at', '<=', $filterDate)
            ->whereIn('members.uniqueId', $allMemberUniqueIds)
            ->select(DB::raw('SUM(amount) as total'))
            ->value('total') ?? 0;
    }

    /**
     * Get total death subscription for all members up to filter date
     */
    private function getTotalDeathSubscription($allMemberUniqueIds, $filterDate)
    {
        return DB::table('deathtransectionhistories')
            ->join('members', 'deathtransectionhistories.memberId', '=', 'members.uniqueId')
            ->whereDate('deathtransectionhistories.created_at', '<=', $filterDate)
            ->whereIn('members.uniqueId', $allMemberUniqueIds)
            ->select(DB::raw('SUM(amount) as total'))
            ->value('total') ?? 0;
    }

    /**
     * Get loan data for specific members using database IDs
     */
    private function getLoanData($memberIds, $filterDate)
    {
        // Get loans using only database IDs (the correct way)
        $loans = loan::whereDate('created_at', '<=', $filterDate)
                    ->whereIn('memberId', $memberIds)
                    ->get();
        
        // Initialize result arrays
        $result = [
            'activeLoansByMember' => [],
            'loanOutstandingByMember' => [],
            'principalArrearsByMember' => [],
            'dueTermsByMember' => [],
            'interestArrearsByMember' => [],
            'totalLoanCountByMember' => [],
            'totalActiveLoanCountByMember' => [],
            'totalLoanAmountByMember' => [],
        ];
        
        // Process each loan
        foreach ($loans as $loan) {
            $loanId = $loan->id;
            $memberId = $loan->memberId;
            
            // Count all loans
            $result['totalLoanCountByMember'][$memberId] = ($result['totalLoanCountByMember'][$memberId] ?? 0) + 1;
            
            // Sum total loan principal
            $result['totalLoanAmountByMember'][$memberId] = ($result['totalLoanAmountByMember'][$memberId] ?? 0) + $loan->principal;
            
            // Calculate principal paid
            $principalPaid = DB::table('loanrepayments')
                ->where('loanId', $loanId)
                ->whereDate('created_at', '<=', $filterDate)
                ->sum('principalAmount');
            
            // Check if loan is active
            if ($principalPaid < $loan->principal) {
                // Track active loan principal
                $result['activeLoansByMember'][$memberId] = ($result['activeLoansByMember'][$memberId] ?? 0) + $loan->principal;
                
                // Active loan count
                $result['totalActiveLoanCountByMember'][$memberId] = ($result['totalActiveLoanCountByMember'][$memberId] ?? 0) + 1;
                
                // Loan outstanding
                $outstanding = $loan->principal - $principalPaid;
                $result['loanOutstandingByMember'][$memberId] = ($result['loanOutstandingByMember'][$memberId] ?? 0) + $outstanding;
                
                // Principal Arrears
                $scheduledPrincipal = DB::table('loanschedules')
                    ->where('loanId', $loanId)
                    ->whereDate('paymentDate', '<=', $filterDate)
                    ->sum('principalPayment');
                
                $principalArrears = $scheduledPrincipal - $principalPaid;
                if ($principalArrears > 0) {
                    $result['principalArrearsByMember'][$memberId] = ($result['principalArrearsByMember'][$memberId] ?? 0) + $principalArrears;
                }
                
                // Due Terms
                $dueSchedules = DB::table('loanschedules')
                    ->where('loanId', $loanId)
                    ->whereDate('paymentDate', '<=', $filterDate)
                    ->where('status', 'Unpaid')
                    ->get();
                
                $termsDue = $dueSchedules->where('principalPayment', '>', 0)->count();
                $result['dueTermsByMember'][$memberId] = ($result['dueTermsByMember'][$memberId] ?? 0) + $termsDue;
                
                // Interest Arrears Calculation
                $lastBalance = $outstanding;
                $lastDate = $filterDate;
                $lastRepayment = DB::table('loanrepayments')
                    ->where('loanId', $loanId)
                    ->orderByDesc('repaymentDate')
                    ->first();
                
                if ($lastRepayment && $lastRepayment->repaymentDate) {
                    $lastDate = Carbon::parse($lastRepayment->repaymentDate);
                    $lastBalance = $lastRepayment->lastLoanBalance ?? $outstanding;
                } else {
                    $firstSchedule = DB::table('loanschedules')
                        ->where('loanId', $loanId)
                        ->orderBy('paymentDate')
                        ->first();
                    
                    if ($firstSchedule && $firstSchedule->paymentDate) {
                        $lastDate = Carbon::parse($firstSchedule->paymentDate);
                        $lastBalance = $firstSchedule->balance ?? $outstanding;
                    } else {
                        $lastDate = $loan->loanDate ? Carbon::parse($loan->loanDate) : $filterDate;
                        $lastBalance = $loan->principal - $principalPaid;
                    }
                }
                
                // Calculate interest if filterDate is after lastDate
                if ($filterDate->greaterThan($lastDate)) {
                    $days = $lastDate->diffInDays($filterDate);
                    $rate = $loan->interestRate ?? 12;
                    $interest = ($lastBalance * $rate * $days) / 36500;
                    $result['interestArrearsByMember'][$memberId] = ($result['interestArrearsByMember'][$memberId] ?? 0) + round($interest, 2);
                }
            }
        }
        
        return $result;
    }

    /**
     * Get loan totals for all members using database IDs
     */
    private function getLoanTotals($allMemberIds, $filterDate)
    {
        // Initialize totals
        $totals = [
            'totalCountOfTotalLoansAll' => 0,
            'totalLoansAmountAll' => 0,
            'totalActiveLoansAll' => 0,
            'totalActiveLoanAmountAll' => 0,
            'totalLoanOutstandingAll' => 0,
            'totalPrincipalArrearsAll' => 0,
            'totalInterestArrearsAll' => 0,
        ];
        
        // Get all loans for filtered members (using database IDs)
        $allLoans = loan::whereDate('created_at', '<=', $filterDate)
            ->whereIn('memberId', $allMemberIds)
            ->get();
        
        // Process each loan
        foreach ($allLoans as $loan) {
            $loanId = $loan->id;
            
            // Count all loans
            $totals['totalCountOfTotalLoansAll']++;
            
            // Sum total loan principal
            $totals['totalLoansAmountAll'] += $loan->principal;
            
            // Calculate principal paid
            $principalPaid = DB::table('loanrepayments')
                ->where('loanId', $loanId)
                ->whereDate('created_at', '<=', $filterDate)
                ->sum('principalAmount');
            
            // Check if loan is active
            if ($principalPaid < $loan->principal) {
                // Count active loans
                $totals['totalActiveLoansAll']++;
                
                // Sum active loan principal
                $totals['totalActiveLoanAmountAll'] += $loan->principal;
                
                // Calculate outstanding
                $outstanding = $loan->principal - $principalPaid;
                $totals['totalLoanOutstandingAll'] += $outstanding;
                
                // Calculate Principal Arrears
                $scheduledPrincipal = DB::table('loanschedules')
                    ->where('loanId', $loanId)
                    ->whereDate('paymentDate', '<=', $filterDate)
                    ->sum('principalPayment');
                
                $principalArrears = $scheduledPrincipal - $principalPaid;
                if ($principalArrears > 0) {
                    $totals['totalPrincipalArrearsAll'] += $principalArrears;
                }
                
                // Calculate Interest Arrears
                $lastBalance = $outstanding;
                $lastDate = $filterDate;
                $lastRepayment = DB::table('loanrepayments')
                    ->where('loanId', $loanId)
                    ->orderByDesc('repaymentDate')
                    ->first();
                
                if ($lastRepayment && $lastRepayment->repaymentDate) {
                    $lastDate = Carbon::parse($lastRepayment->repaymentDate);
                    $lastBalance = $lastRepayment->lastLoanBalance ?? $outstanding;
                } else {
                    $firstSchedule = DB::table('loanschedules')
                        ->where('loanId', $loanId)
                        ->orderBy('paymentDate')
                        ->first();
                    
                    if ($firstSchedule && $firstSchedule->paymentDate) {
                        $lastDate = Carbon::parse($firstSchedule->paymentDate);
                        $lastBalance = $firstSchedule->balance ?? $outstanding;
                    } else {
                        $lastDate = $loan->loanDate ? Carbon::parse($loan->loanDate) : $filterDate;
                        $lastBalance = $loan->principal - $principalPaid;
                    }
                }
                
                // Calculate interest if filterDate is after lastDate
                if ($filterDate->greaterThan($lastDate)) {
                    $days = $lastDate->diffInDays($filterDate);
                    $rate = $loan->interestRate ?? 12;
                    $interest = ($lastBalance * $rate * $days) / 36500;
                    $totals['totalInterestArrearsAll'] += round($interest, 2);
                }
            }
        }
        
        return $totals;
    }

    /**
     * Ensure all members have entries in financial arrays
     */
    private function ensureFinancialDefaults($memberUniqueIds, &$savingsOnDate, &$otherIncomeOnDate, &$deathSubscriptionOnDate)
    {
        foreach ($memberUniqueIds as $uniqueId) {
            if (!isset($savingsOnDate[$uniqueId])) {
                $savingsOnDate[$uniqueId] = 0;
            }
            if (!isset($otherIncomeOnDate[$uniqueId])) {
                $otherIncomeOnDate[$uniqueId] = 0;
            }
            if (!isset($deathSubscriptionOnDate[$uniqueId])) {
                $deathSubscriptionOnDate[$uniqueId] = 0;
            }
        }
    }



//member saving report
public function memberSavingReport(Request $request)
    {
        $year = $request->input('year', now()->year);

        // Load reference data
        $getUserRole        = userRole::all();
        $getDivision        = division::all();
        $getProfession      = profession::all();
        $getSavingData      = saving::all();
        $getLoansData       = loan::all();
        $getAllMemberData   = member::all();
        $getOtherIncomeData = DB::table('otherincomes')->get();
        $getDeathSubData    = DB::table('deathsubscriptions')->get();
        $villages           = village::all();
        $smallGroups        = smallgroup::all();

        // Load members with relations
        $getMember = member::with(['division', 'village', 'smallgroup'])->get();

        // Filter saving transactions by year
        $getData = DB::table('savingtransectionhistories')
            ->whereYear('created_at', $year)
            ->get();

        // Calculate total savings per division
        $divisionSavings = [];
        foreach ($getDivision as $division) {
            $divisionSavings[$division->divisionName] = 0;
        }

        // Prepare structure for monthly savings per division (1-12 months)
        $divisionMonthlySavings = [];
        foreach ($getDivision as $division) {
            $divisionMonthlySavings[$division->divisionName] = array_fill(1, 12, 0);
        }

        // Map members by uniqueId for quick lookup
        $membersMap = $getMember->keyBy('uniqueId');

        foreach ($getData as $row) {
            if ($row->type === 'Credit') {
                $memberUniqueId = $row->memberId;
                if (isset($membersMap[$memberUniqueId])) {
                    $divId = $membersMap[$memberUniqueId]->divisionId;
                    $divisionName = $getDivision->where('id', $divId)->first()?->divisionName ?? 'Unknown';

                    $amount = $row->amount ?? 0;
                    $divisionSavings[$divisionName] += $amount;

                    $month = (int) Carbon::parse($row->created_at)->format('n');
                    $divisionMonthlySavings[$divisionName][$month] += $amount;
                }
            }
        }

        return view('pages.permission.reports.member_savings_report_per', [
            'getUserRole'           => $getUserRole,
            'getMember'             => $getMember,
            'getDivision'           => $getDivision,
            'getProfession'         => $getProfession,
            'getSavingData'         => $getSavingData,
            'getLoansData'          => $getLoansData,
            'getAllMemberData'      => $getAllMemberData,
            'getOtherIncomeData'    => $getOtherIncomeData,
            'getDeathSubData'       => $getDeathSubData,
            'villages'              => $villages,
            'smallGroups'           => $smallGroups,
            'selectedYear'          => $year,
            'getData'               => $getData,
            'divisionSavings'       => $divisionSavings,
            'divisionMonthlySavings'=> $divisionMonthlySavings,
        ]);
    }



    function openingBalanceReport()
    {
        $getUserRole = userRole::all();
        $getMember = member::all();
        $getDivision = division::all();
        $getProfession = profession::all();
        $getsavings = saving::all();
        $villages = [];
        $smallGroups = [];
        $getMember = member::with('division', 'village', 'smallgroup')->get();
        $getLoansData = loan::all();
        $getAllMemberData = member::all();
        return view('pages.permission.reports.opening_balance_report_per', ['getUserRole' => $getUserRole, 'getMember' => $getMember, 'getDivision' => $getDivision,  'villages' => $villages,  'smallGroups' => $smallGroups, 'getProfession' => $getProfession, 'getsavings' => $getsavings, 'getLoansData' => $getLoansData, 'getAllMemberData' => $getAllMemberData]);
    }
    
    function meeting_report()
    {
          $getUserRole = userRole::all();
        $getSmallGroup = smallgroup::all();
        $getVillage = village::all();
        $getDivision = division::all();
        $getMember = member::all();
        $getLoansData = loan::all();
        $getAllMemberData = member::all();
        $getSavingData = saving::all();
        $getLoanRepaymentData = loanschedule::all();
        $getDeathSubscription = deathsubscription::all();
        $getDeathDonation = deathdonation::all();
      $getMeeting = DB::table('meetings')
            ->leftJoin('resourcepeople', 'meetings.resource_person', '=', 'resourcepeople.id')
            ->leftJoin('divisions', 'meetings.division_id', '=', 'divisions.id')
            ->leftJoin('villages', 'meetings.village_id', '=', 'villages.id')
            ->leftJoin('smallgroups', 'meetings.small_group_id', '=', 'smallgroups.id')
            ->select(
                'meetings.*',
                'resourcepeople.full_name',
                'divisions.divisionName',
                'villages.villageName',
                'smallgroups.smallGroupName'
            )->get();

        return view('pages.permission.reports.meeting_report_per', compact('getMeeting', 'getUserRole', 'getSmallGroup', 'getVillage', 'getDivision', 'getMember', 'getLoansData', 'getAllMemberData', 'getSavingData', 'getLoanRepaymentData', 'getDeathSubscription', 'getDeathDonation'));
    }
}
