<?php

namespace App\Http\Controllers\Component1;

use App\Http\Controllers\Controller;
use App\Models\C1Group;
use App\Models\C1Member;
use App\Models\C1WeofBeneficiary;
use App\Models\C1Support;
use Illuminate\View\View;
use Illuminate\Support\Facades\DB;

class C1DashboardController extends Controller
{
    public function __invoke(): View
    {
        // 1. Registered VSLA/Community Groups
        $totalGroups = C1Group::count();

        // 2. Registered Members (active only)
        $totalMembers = C1Member::active()->count();

        // 3. WEOF Beneficiaries
        //$totalWeof = C1WeofBeneficiary::count();

        // 4. Total Supports (any type)
        $totalSupports = C1Support::count();

        // 5. KPI Quick Stats (example – you can replace with real KPI logic later)
        $kpiStats = [
            'groups'        => $totalGroups,
            'members'       => $totalMembers,
            'supports'      => $totalSupports,
        ];

        // === Core Stats ===
        $stats = [
            'total_groups'      => C1Group::count(),
            'total_members'     => C1Member::sum(DB::raw('num_hh_members_total')) ?: C1Member::count(),
            'active_groups'     => C1Group::whereHas('members', fn($q) => $q->where('active', true))->count(),
        ];

        // === Chart 1: Groups by County ===
        $groupsByCounty = C1Group::select('county', DB::raw('count(*) as total'))
            ->groupBy('county')
            ->orderByDesc('total')
            ->limit(10)
            ->get();

        // === Chart 2: Gender Distribution (Members) ===
        $genderData = [
            'female' => C1Member::where('gender', 'female')->count(),
            'male'   => C1Member::where('gender', 'male')->count(),
        ];

        // === Chart 4: Training Completion Rate (Core 1–4) ===
        $trainingCompletion = [
            'Core 1' => C1Member::where('completed_core_1', true)->count(),
            'Core 2' => C1Member::where('completed_core_2', true)->count(),
            'Core 3' => C1Member::where('completed_core_3', true)->count(),
            'Core 4' => C1Member::where('completed_core_4', true)->count(),
        ];

        // === GBV Vulnerability ===
        $gbvVulnerable = C1Group::sum('num_women_disability')
                       + C1Group::sum('num_widows')
                       + C1Group::sum('num_women_heads_hh');
        $groupsByNature = C1Group::groupBy('nature')->selectRaw('nature, count(*) as achieved')->get();
        $totalFemale = C1Member::where('gender', 'female')->count();
        $membershipByNature = C1Group::groupBy('nature')->selectRaw('nature, sum(num_women) as female, sum(num_men) as male, sum(num_adolescent_girls) as adol_girls, sum(total_members) as total')->get();
        $membershipByNature2 = C1Group::query()
            ->selectRaw("
                nature,
                SUM(num_women) as female,
                SUM(num_men) as male,
                SUM(num_adolescent_girls) as adol_girls,
                SUM(total_members) as total
            ")
            ->groupBy('nature')
            ->get()
            ->keyBy('nature')
            ->map(function ($item) {
                return [
                    'female'      => (int) $item->female,
                    'male'        => (int) $item->male,
                    'adol_girls'  => (int) $item->adol_girls,
                    'total'       => (int) $item->total,
                ];
            });

        $countyCoords = [
            'Wau'        => ['lat' => 7.70, 'lon' => 28.00],
            'Jur River'  => ['lat' => 8.00, 'lon' => 28.50],
            'Torit'      => ['lat' => 4.41, 'lon' => 32.57],
            'Magwi'      => ['lat' => 4.13, 'lon' => 32.30],
            'Juba' => ['lat' => 4.8594, 'lon' => 31.5713],
            'Wau' => ['lat' => 7.7011, 'lon' => 27.9953],
            'Malakal' => ['lat' => 9.5334, 'lon' => 31.6605],
            'Yambio' => ['lat' => 4.5721, 'lon' => 28.3955],
            'Bor' => ['lat' => 6.2092, 'lon' => 31.5589],
            'Rumbek' => ['lat' => 6.8116, 'lon' => 29.6912],
            'Aweil' => ['lat' => 8.7680, 'lon' => 27.3966],
            'Yei' => ['lat' => 4.0951, 'lon' => 30.6773],
            'Bentiu' => ['lat' => 9.2573, 'lon' => 29.8037],
            'Kuajok' => ['lat' => 8.3090, 'lon' => 27.9960],
            // Add more counties/payams as needed, e.g.
        ];

        $stateCoords = [
            'Central Equatoria'       => ['lat' => 4.85, 'lon' => 31.36],
            'Eastern Equatoria'       => ['lat' => 4.75, 'lon' => 33.18],
            'Jonglei'                 => ['lat' => 7.40, 'lon' => 32.06],
            'Lakes'                   => ['lat' => 6.63, 'lon' => 29.98],
            'Northern Bahr el Ghazal' => ['lat' => 8.73, 'lon' => 26.90],
            'Unity'                   => ['lat' => 9.00, 'lon' => 29.70],
            'Western Bahr el Ghazal'  => ['lat' => 7.88, 'lon' => 25.86],
            'Western Equatoria'       => ['lat' => 5.31, 'lon' => 28.40],
            'Upper Nile' => ['lat' => 10.30, 'lon' => 32.70],
            'Warrap'     => ['lat' => 8.12, 'lon' => 28.30],
        ];

        // Fetch groups and prepare locations for the Group Map (one marker per group, using county coords)
        $groups = C1Group::select('id', 'name', 'county', 'payam')->get();
        $groupLocations = $groups->map(function ($group) use ($countyCoords) {
            $coords = $countyCoords[$group->county] ?? ['lat' => 6.88, 'lon' => 31.31]; // Fallback to South Sudan center
    
            return [
                'lat'  => $coords['lat'],
                'lon'  => $coords['lon'],
                'name' => $group->name . ' (' . $group->county . ', ' . $group->payam . ')',
            ];
        })->toArray();

        $groupsByState = C1Group::select('state', DB::raw('count(*) as total'))
            ->groupBy('state')
            ->orderByDesc('total')
            ->limit(10)
            ->get();
        $groupLocationsByState = $groupsByState->map(function ($group) use ($stateCoords) {
            $coords = $stateCoords[$group->state] ?? ['lat' => 6.88, 'lon' => 31.31];
            return [
                'lat'  => $coords['lat'],
                'lon'  => $coords['lon'],
                'name' => $group->state,
            ];
        })->toArray();
        // For Member Map: Aggregate members by group county (or use group locations)
        // Example: Count members per county for clustered markers
        $memberLocations = C1Group::withCount('members') // Assuming you have hasMany('members') relation in C1Group model
        ->get()
        ->groupBy('county')
        ->map(function ($groupsInCounty) use ($countyCoords) {
            $county = $groupsInCounty->first()->county;
            $coords = $countyCoords[$county] ?? ['lat' => 6.88, 'lon' => 31.31];
            $totalMembers = $groupsInCounty->sum('members_count');

            return [
                'lat'  => $coords['lat'],
                'lon'  => $coords['lon'],
                'name' => "County: $county<br>Total Members: $totalMembers",
            ];
        })->values()->toArray();

        $trainingsCumulative = C1Member::selectRaw("
            (CASE 
                WHEN groups.nature = 'matured' THEN 'matured'
                WHEN groups.nature = 'progressing' THEN 'progressive' 
                ELSE 'new' END) as nature,
            SUM(CASE WHEN gender = 'female' THEN 1 ELSE 0 END) as female,
            SUM(CASE WHEN gender = 'male' THEN 1 ELSE 0 END) as male,
            SUM(CASE WHEN gender = 'female' AND age BETWEEN 14 AND 24 THEN 1 ELSE 0 END) as adol_girls,
            COUNT(*) as total
        ")
        ->join('c1_groups as groups', 'c1_members.group_id', '=', 'groups.id')
        ->where(function($q) {
            $q->where('completed_core_1', 1)
            ->orWhere('completed_core_2', 1)
            ->orWhere('completed_core_3', 1)
            ->orWhere('completed_core_4', 1)
            ->orWhere('received_specialized_1', 1);
        })
        ->groupBy('nature')
        ->get()
        ->keyBy('nature')
        ->map(fn($item) => [
            'female' => (int)$item->female,
            'male' => (int)$item->male,
            'adol_girls' => (int)$item->adol_girls,
            'total' => (int)$item->total,
        ]);
        $kitsGroupsCumulative = C1Group::selectRaw("
            (CASE 
                WHEN nature = 'matured' THEN 'matured'
                WHEN nature = 'progressing' THEN 'progressive' 
                ELSE 'new' END) as nature,
            SUM(num_women) as female,
            SUM(num_men) as male,
            SUM(num_adolescent_girls) as adol_girls,
            SUM(total_members) as total
        ")
        ->groupBy('nature')
        ->get()
        ->keyBy('nature')
        ->map(function ($item) {
            return [
                'female'      => (int) $item->female,
                'male'        => (int) $item->male,
                'adol_girls'  => (int) $item->adol_girls,
                'total'       => (int) $item->total,
            ];
        });
        
        $kitsMembersCumulative = C1Member::selectRaw("
            (CASE 
                WHEN groups.nature = 'matured' THEN 'matured'
                WHEN groups.nature = 'progressing' THEN 'progressive' 
                ELSE 'new' END) as nature,
            SUM(CASE WHEN c1_members.gender = 'female' THEN 1 ELSE 0 END) as female,
            SUM(CASE WHEN c1_members.gender = 'male' THEN 1 ELSE 0 END) as male,
            SUM(CASE WHEN c1_members.gender = 'female' AND c1_members.age BETWEEN 14 AND 24 THEN 1 ELSE 0 END) as adol_girls,
            COUNT(*) as total
        ")
        ->join('c1_groups as groups', 'c1_members.group_id', '=', 'groups.id')
        ->where(function($q) {
            $q->where('c1_members.received_certificate', 1);
        })
        ->groupBy('nature')
        ->get()
        ->keyBy('nature')
        ->map(fn($item) => [
            'female' => (int)$item->female,
            'male' => (int)$item->male,
            'adol_girls' => (int)$item->adol_girls,
            'total' => (int)$item->total,
        ]);

        $productiveAssetGroups = C1Group::selectRaw("
            (CASE 
                WHEN nature = 'matured' THEN 'matured'
                WHEN nature = 'progressing' THEN 'progressive' 
                ELSE 'new' END) as nature,
            SUM(num_women) as female,
            SUM(num_men) as male,
            SUM(num_adolescent_girls) as adol_girls,
            SUM(total_members) as total
        ")
        ->groupBy('nature')
        ->get()
        ->keyBy('nature')
        ->map(function ($item) {
            return [
                'female'      => (int) $item->female,
                'male'        => (int) $item->male,
                'adol_girls'  => (int) $item->adol_girls,
                'total'       => (int) $item->total,
            ];
        });
        $newBusinessGroups = C1Group::selectRaw("
            (CASE 
                WHEN nature = 'matured' THEN 'matured'
                WHEN nature = 'progressing' THEN 'progressive' 
                ELSE 'new' END) as nature,
            SUM(num_women) as female,
            SUM(num_men) as male,
            SUM(num_adolescent_girls) as adol_girls,
            SUM(total_members) as total
        ")
        ->groupBy('nature')
        ->get()
        ->keyBy('nature')
        ->map(function ($item) {
            return [
                'female'      => (int) $item->female,
                'male'        => (int) $item->male,
                'adol_girls'  => (int) $item->adol_girls,
                'total'       => (int) $item->total,
            ];
        });
        $capacityBuildingCumulative = C1Group::selectRaw("
            (CASE 
                WHEN nature = 'matured' THEN 'matured'
                WHEN nature = 'progressing' THEN 'progressive' 
                ELSE 'new' END) as nature,
            SUM(num_women) as female,
            SUM(num_men) as male,
            SUM(num_adolescent_girls) as adol_girls,
            SUM(total_members) as total
        ")
        ->groupBy('nature')
        ->get()
        ->keyBy('nature')
        ->map(function ($item) {
            return [
                'female'      => (int) $item->female,
                'male'        => (int) $item->male,
                'adol_girls'  => (int) $item->adol_girls,
                'total'       => (int) $item->total,
            ];
        });
        //groups receiving capacity building
        $groupsReceivingCapacityBuilding = C1Group::selectRaw("
            (CASE 
                WHEN nature = 'matured' THEN 'matured'
                WHEN nature = 'progressing' THEN 'progressive' 
                ELSE 'new' END) as nature,
            SUM(num_women) as female,
            SUM(num_men) as male,
            SUM(num_adolescent_girls) as adol_girls,
            SUM(total_members) as total
        ")
        ->groupBy('nature')
        ->get()
        ->keyBy('nature')
        ->map(function ($item) {
            return [
                'female'      => (int) $item->female,
                'male'        => (int) $item->male,
                'adol_girls'  => (int) $item->adol_girls,
                'total'       => (int) $item->total,
            ];
        });
        return view('component1.dashboard', compact('groupsByNature', 'totalFemale', 'membershipByNature', 'membershipByNature2', 'stats', 'groupsByCounty', 'genderData', 'trainingCompletion', 'gbvVulnerable', 'kpiStats', 'groupLocations', 'groupLocationsByState', 'memberLocations', 'trainingsCumulative','kitsGroupsCumulative', 'kitsMembersCumulative', 'productiveAssetGroups',  'newBusinessGroups', 'capacityBuildingCumulative', 'groupsReceivingCapacityBuilding'));

    }
}