<?php

namespace App\Exports;

use App\Models\C1Group;
use App\Models\C1Member;
use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\WithMultipleSheets;
use Maatwebsite\Excel\Concerns\Exportable;

class VslaMonthlyReportExport implements WithMultipleSheets
{
    use Exportable;

    public function sheets(): array
    {
        return [
            new class implements FromCollection {
                public function collection()
                {
                    return C1Member::with('group')->get()->map(function ($m, $index) {
                        return [
                            'SN' => $index + 1,
                            'Name of VSLA Member' => $m->name,
                            'Category' => ucfirst($m->group->nature ?? ''),
                            'Group Name' => $m->group->name ?? '',
                            'State' => $m->group->state ?? '',
                            'County' => $m->group->county ?? '',
                            'Payam' => $m->group->payam ?? '',
                            'Boma' => $m->group->boma ?? '',
                            'Age' => $m->age,
                            'Gender' => $m->gender == 'female' ? 'Female' : ($m->gender == 'male' ? 'Male' : 'Adol.Girl'),
                            // Trainings
                            'VSLA Operations, Management, and Leadership' => $m->completed_core_2 ? 'Yes' : '',
                            'Functional Literacy and Financial Literacy & Management' => $m->completed_core_1 ? 'Yes' : '',
                            'Business Development and Entrepreneurship' => $m->completed_core_3 ? 'Yes' : '',
                            'Digital and information literacy' => $m->completed_core_4 ? 'Yes' : '',
                            'Specialized Training' => $m->received_specialized_1 ? 'Yes' : '',
                            'Existing Main Income Generation Activities' => $m->existing_income_activities,
                            'Received Certificate' => $m->received_certificate ? 'Yes' : 'No',
                            'New Business Created' => $m->started_new_business ? 'Yes' : '',
                            'Type of New Business Created' => '',
                            // Productive Assets (simplified – expand as needed)
                            'Productive Asset A' => '',
                            'Total Cost of Productive Asset deployed' => 0,
                        ];
                    });
                }
            },

            // Sheet 2: VSLA Groups
            new class implements FromCollection {
                public function collection()
                {
                    return C1Group::all()->map(function ($g, $index) {
                        return [
                            'SN' => $index + 1,
                            'VSLA Group Name' => $g->name,
                            'Category' => ucfirst($g->nature ?? ''),
                            'State' => $g->state,
                            'County' => $g->county,
                            'Payam' => $g->payam,
                            'Boma' => $g->boma,
                            'Total Female' => $g->num_women,
                            'Total Male' => $g->num_men,
                            'Adolescent Girls' => $g->num_adolescent_girls,
                            'Total Members' => $g->total_members,
                            'Average Participation' => $g->average_participation,
                            'Received Certificate' => $g->received_certificate ? 'Received' : 'Not received',
                            'New Business Created' => $g->created_new_business ? 'Yes' : '',
                            'Productive Asset A' => '',
                            'Total Cost of Productive Assets deployed' => $g->total_value_in_kind ?? 0,
                        ];
                    });
                }
            },

            // Sheet 3: Summary Data (exact replica of your template)
            new class implements FromCollection {
                public function collection()
                {
                    $groups = C1Group::selectRaw('
                        nature,
                        COUNT(*) as achieved,
                        SUM(num_women) as female,
                        SUM(num_men) as male,
                        SUM(num_adolescent_girls) as adol_girls,
                        SUM(total_members) as total_members
                    ')->groupBy('nature')->get()->keyBy('nature');

                    $targets = ['mature' => 240, 'progressive' => 160, 'new' => 400, 'total' => 800];

                    $rows = collect();

                    // Header rows
                    $rows->push(['Summary Report _ Component 1']);
                    $rows->push([]); $rows->push([]); $rows->push(['VSLA Engagement Data - WBGS']);

                    $rows->push(['VSLA Category', 'RF Target', 'Achieved', '%Progress', '', '', '', '', '', 'VSLA Membership', 'RF Target', 'Female', 'Male', 'Adolescent Girls', 'Total']);
                    
                    foreach (['mature', 'progressive', 'new'] as $cat) {
                        $achieved = $groups->get($cat)->achieved ?? 0;
                        $target = $targets[$cat] ?? 0;
                        $progress = $target > 0 ? round(($achieved / $target) * 100, 2) : 0;

                        $mem = $groups->get($cat);
                        $rows->push([
                            ucfirst($cat),
                            $target,
                            $achieved,
                            $progress . '%',
                            '', '', '', '',
                            '',
                            ucfirst($cat),
                            $target,
                            $mem->female ?? 0,
                            $mem->male ?? 0,
                            $mem->adol_girls ?? 0,
                            $mem->total_members ?? 0
                        ]);
                    }

                    // Total row
                    $totalAchieved = $groups->sum('achieved');
                    $totalProgress = round(($totalAchieved / 800) * 100, 2);
                    $totalMem = $groups->sum(fn($g) => $g->total_members ?? 0);

                    $rows->push([
                        'Total', 800, $totalAchieved, $totalProgress . '%',
                        '', '', '', '', '',
                        'Total', 800,
                        $groups->sum('female'),
                        $groups->sum('male'),
                        $groups->sum('adol_girls'),
                        $totalMem
                    ]);

                    return $rows;
                }
            },

            // Sheet 4: Dropdown C1 (static)
            new class implements FromCollection {
                public function collection()
                {
                    return collect([
                        ['Category', 'State', 'County', 'Payam', 'Module', 'Certificate', 'Kit', 'Productive Asset', 'Gender'],
                        ['Mature', 'WBGS', 'Wau', 'Wau South', 'Not Started', 'Received', 'Yes', 'Received', 'Female'],
                        ['Progressive', 'EES', 'Jur River', 'Wau North', 'Ongoing', 'Not received', 'No', 'Not received', 'Male'],
                        ['New', '', 'Torit', 'Baggari', 'Completed', '', '', '', 'Adol.Girl'],
                        // Add more as needed
                    ]);
                }
            },
        ];
    }
}