<div class="content-wrapper">
    <div class="container-xxl flex-grow-1 container-p-y">
        <div class="row">
            <div class="col">
                <div class="card shadow">
                    <div class="card-header text-uppercase custom-header bg-primary text-bg-info fw-bold">
                        <div class="col-6">
                            <i class="menu-icon ti ti-users-group"></i>Group Report
                        </div>
                    </div>
                    <div class="card-body">
                        <div class="table-responsive" style="max-height: 600px; overflow-y: auto;">
                            <table id="groupReportTable" class="table table-sm table-bordered" style="min-width: 1400px;">
                                <thead class="table-light sticky-top">
                                    <tr>
                                        <th>#</th>
                                        <th>Division</th>
                                        <th>Village</th>
                                        <th>Small Group</th>
                                        <th>No of Members</th>
                                        <th>Total Savings</th>
                                        <th>Total Death Subscription</th>
                                        <th>Total Death Donation</th>
                                        <th>Total Other Income</th>
                                        <th>Loan Capital</th>
                                        <th>Entry Fees</th>
                                        
                                    </tr>
                                </thead>
                                <tbody>
                                    @php
                                        $counter = 1;
                                        $overallMembers = 0;
                                        $overallSavings = 0;
                                        $overallDeathSub = 0;
                                        $overallDeathDonation = 0;
                                        $overallOtherIncome = 0;
                                        $overallLoanCap = 0;
                                        $overallInterest = 0;
                                        $overallGrandTotal = 0;

                                        $smallGroupsByVillage = $getSmallGroup->groupBy('villageId');
                                        $villagesByDivision = $getVillage->groupBy('divisionId');
                                        $divisionsSorted = $getDivision->sortBy('divisionName');

                                        function numberToWords($number) {
                                            $formatter = new \NumberFormatter("en", \NumberFormatter::SPELLOUT);
                                            return ucfirst($formatter->format($number)) . ' Rupees';
                                        }
                                    @endphp

                                    @foreach ($divisionsSorted as $division)
                                        @php
                                            $divisionMembersTotal = 0;
                                            $divisionSavingsTotal = 0;
                                            $divisionDeathSubTotal = 0;
                                            $divisionDeathDonationTotal = 0;
                                            $divisionOtherIncomeTotal = 0;
                                            $divisionLoanCapTotal = 0;
                                            $divisionInterestTotal = 0;
                                            $divisionGrandTotal = 0;

                                            $villages = $villagesByDivision->get($division->id, collect())->sortBy('villageName');
                                        @endphp

                                        @foreach ($villages as $village)
                                            @php
                                                $sgList = $smallGroupsByVillage->get($village->id, collect());
                                                $villageMembersOnly = $getMember->where('villageId', $village->id)->where('smallGroupStatus','No');

                                                $villageTotalMembers = 0;
                                                $villageTotalSavings = 0;
                                                $villageTotalDeathSub = 0;
                                                $villageTotalDeathDonation = 0;
                                                $villageTotalOtherIncome = 0;
                                                $villageLoanCap = 0;
                                                $villageTotalInterest = 0;
                                                $villageGrandTotal = 0;
                                            @endphp

                                            {{-- Small Groups --}}
                                            @foreach ($sgList as $sg)
                                                @php
                                                    $members = $getMember->where('smallGroupId', $sg->id);
                                                    $totalMembers = $members->count();
                                                    $totalSavings = $members->sum(fn($m) => $getSavingData->where('memberId', $m->uniqueId)->sum('totalAmount'));
                                                    $totalDeathSub = $members->sum(fn($m) => $getDeathSubscription->where('memberId', $m->uniqueId)->sum('totalAmount'));
                                                    $totalDeathDonation = $members->sum(fn($m) => $getDeathDonation->where('memberId', $m->id)->count() * 10000);
                                                    $totalOtherIncome = $members->sum(fn($m) => $getOtherIncome->where('memberId', $m->uniqueId)->sum('totalAmount'));
                                                    $loanCap = $members->sum(fn($m) => $getLoansData->where('memberId', $m->id)->sum('principal'));
                                                    $totalInterest = $members->sum(function($m) use ($getLoansData, $getLoanRepaymentData) {
                                                        $loans = $getLoansData->where('memberId', $m->id);
                                                        return $loans->sum(fn($loan) => $getLoanRepaymentData->where('loanId', $loan->id)->sum('interestPayment'));
                                                    });
                                                    $grandTotal = $totalSavings + $loanCap + $totalInterest;

                                                    $villageTotalMembers += $totalMembers;
                                                    $villageTotalSavings += $totalSavings;
                                                    $villageTotalDeathSub += $totalDeathSub;
                                                    $villageTotalDeathDonation += $totalDeathDonation;
                                                    $villageTotalOtherIncome += $totalOtherIncome;
                                                    $villageLoanCap += $loanCap;
                                                    $villageTotalInterest += $totalInterest;
                                                    $villageGrandTotal += $grandTotal;
                                                @endphp
                                                <tr>
                                                    <td>{{ $counter++ }}</td>
                                                    <td>{{ $division->divisionName }}</td>
                                                    <td>{{ $village->villageName }}</td>
                                                    <td>{{ $sg->smallGroupName }}</td>
                                                    <td>{{ $totalMembers }}</td>
                                                    <td>{{ number_format($totalSavings,2) }}</td>
                                                    <td>{{ number_format($totalDeathSub,2) }}</td>
                                                    <td>{{ number_format($totalDeathDonation,2) }}</td>
                                                    <td>{{ number_format($totalOtherIncome,2) }}</td>
                                                    <td>{{ number_format($loanCap,2) }}</td>
                                                    <td>-</td>
                                                    
                                                </tr>
                                            @endforeach

                                            {{-- Village-only members --}}
                                            @if($villageMembersOnly->count() > 0)
                                                @php
                                                    $villageOnlyMembers = $villageMembersOnly->count();
                                                    $villageOnlySavings = $villageMembersOnly->sum(fn($m) => $getSavingData->where('memberId', $m->uniqueId)->sum('totalAmount'));
                                                    $villageOnlyDeathSub = $villageMembersOnly->sum(fn($m) => $getDeathSubscription->where('memberId', $m->uniqueId)->sum('totalAmount'));
                                                    $villageOnlyDeathDonation = $villageMembersOnly->sum(fn($m) => $getDeathDonation->where('memberId', $m->id)->count() * 10000);
                                                    $villageOnlyOtherIncome = $villageMembersOnly->sum(fn($m) => $getOtherIncome->where('memberId', $m->uniqueId)->sum('totalAmount'));
                                                    $villageOnlyLoanCap = $villageMembersOnly->sum(fn($m) => $getLoansData->where('memberId', $m->id)->sum('principal'));
                                                    $villageOnlyInterest = $villageMembersOnly->sum(function($m) use ($getLoansData,$getLoanRepaymentData){
                                                        $loans = $getLoansData->where('memberId',$m->id);
                                                        return $loans->sum(fn($loan) => $getLoanRepaymentData->where('loanId',$loan->id)->sum('interestPayment'));
                                                    });
                                                    $villageOnlyGrandTotal = $villageOnlySavings + $villageOnlyLoanCap + $villageOnlyInterest;

                                                    $villageTotalMembers += $villageOnlyMembers;
                                                    $villageTotalSavings += $villageOnlySavings;
                                                    $villageTotalDeathSub += $villageOnlyDeathSub;
                                                    $villageTotalDeathDonation += $villageOnlyDeathDonation;
                                                    $villageTotalOtherIncome += $villageOnlyOtherIncome;
                                                    $villageLoanCap += $villageOnlyLoanCap;
                                                    $villageTotalInterest += $villageOnlyInterest;
                                                    $villageGrandTotal += $villageOnlyGrandTotal;
                                                @endphp
                                                <tr class="table-info fw-bold">
                                                    <td>{{ $counter++ }}</td>
                                                    <td>{{ $division->divisionName }}</td>
                                                    <td>{{ $village->villageName }}</td>
                                                    <td>Saving Members</td>
                                                    <td>{{ $villageOnlyMembers }}</td>
                                                    <td>{{ number_format($villageOnlySavings,2) }}</td>
                                                    <td>{{ number_format($villageOnlyDeathSub,2) }}</td>
                                                    <td>{{ number_format($villageOnlyDeathDonation,2) }}</td>
                                                    <td>{{ number_format($villageOnlyOtherIncome,2) }}</td>
                                                    <td>{{ number_format($villageOnlyLoanCap,2) }}</td>
                                                    <td>-</td>
                                                   
                                                </tr>
                                            @endif

                                            {{-- Village total with total in words --}}
                                            <tr class="table-secondary fw-bold">
                                                <td>{{ $counter++ }}</td>
                                                <td>{{ $division->divisionName }}</td>
                                                <td>Total of {{ $village->villageName }}</td>
                                                <td>-</td>
                                                <td>{{ $villageTotalMembers }}</td>
                                                <td>{{ number_format($villageTotalSavings,2) }}</td>
                                                <td>{{ number_format($villageTotalDeathSub,2) }}</td>
                                                <td>{{ number_format($villageTotalDeathDonation,2) }}</td>
                                                <td>{{ number_format($villageTotalOtherIncome,2) }}</td>
                                                <td>{{ number_format($villageLoanCap,2) }}</td>
                                                <td>-</td>
                                               
                                            </tr>

                                            @php
                                                $divisionMembersTotal += $villageTotalMembers;
                                                $divisionSavingsTotal += $villageTotalSavings;
                                                $divisionDeathSubTotal += $villageTotalDeathSub;
                                                $divisionDeathDonationTotal += $villageTotalDeathDonation;
                                                $divisionOtherIncomeTotal += $villageTotalOtherIncome;
                                                $divisionLoanCapTotal += $villageLoanCap;
                                                $divisionInterestTotal += $villageTotalInterest;
                                                $divisionGrandTotal += $villageGrandTotal;
                                            @endphp
                                        @endforeach

                                        {{-- Division total with total in words --}}
                                        <tr class="table-warning fw-bold">
                                            <td>{{ $counter++ }}</td>
                                            <td>Total of {{ $division->divisionName }}</td>
                                            <td>-</td>
                                            <td>-</td>
                                            <td>{{ $divisionMembersTotal }}</td>
                                            <td>{{ number_format($divisionSavingsTotal,2) }}</td>
                                            <td>{{ number_format($divisionDeathSubTotal,2) }}</td>
                                            <td>{{ number_format($divisionDeathDonationTotal,2) }}</td>
                                            <td>{{ number_format($divisionOtherIncomeTotal,2) }}</td>
                                            <td>{{ number_format($divisionLoanCapTotal,2) }}</td>
                                            <td>-</td>
                                            
                                            </td>
                                        </tr>

                                        @php
                                            $overallMembers += $divisionMembersTotal;
                                            $overallSavings += $divisionSavingsTotal;
                                            $overallDeathSub += $divisionDeathSubTotal;
                                            $overallDeathDonation += $divisionDeathDonationTotal;
                                            $overallOtherIncome += $divisionOtherIncomeTotal;
                                            $overallLoanCap += $divisionLoanCapTotal;
                                            $overallInterest += $divisionInterestTotal;
                                            $overallGrandTotal += $divisionGrandTotal;
                                        @endphp
                                    @endforeach

                                    {{-- Overall total with total in words --}}
                                    <tr class="table-dark fw-bold">
                                        <td>{{ $counter++ }}</td>
                                        <td>-</td>
                                        <td>-</td>
                                        <td>-</td>
                                        <td>{{ $overallMembers }}</td>
                                        <td>{{ number_format($overallSavings,2) }}</td>
                                        <td>{{ number_format($overallDeathSub,2) }}</td>
                                        <td>{{ number_format($overallDeathDonation,2) }}</td>
                                        <td>{{ number_format($overallOtherIncome,2) }}</td>
                                        <td>{{ number_format($overallLoanCap,2) }}</td>
                                        <td>-</td>
                                        
                                    </tr>
                                </tbody>
                            </table>
                        </div>
                    </div>
                </div>
            </div>
        </div>
    </div>

<script>
$(document).ready(function () {
    $('#groupReportTable').DataTable({
        paging: true,
        ordering: true,
        order: [[2, 'asc']], // Village alphabetical
        fixedHeader: true,
        scrollY: '500px',
        scrollCollapse: true,
        responsive: true,
    });
});
</script>
