<?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;
use Illuminate\Http\Request;

class C1DashboardController extends Controller
{
    public function __invoke(Request $request): View
    {
        // ====================== FILTERS ======================
        $state     = $request->state;
        $county    = $request->county;
        $payam     = $request->payam;
        $fromMonth = $request->from_month;
        $toMonth   = $request->to_month;

        // Base queries with filters applied
        $groupsQuery = C1Group::query()
            ->when($state, fn($q) => $q->where('state', $state))
            ->when($county, fn($q) => $q->where('county', $county))
            ->when($payam, fn($q) => $q->where('payam', $payam))
            ->when($fromMonth, fn($q) => $q->where('month', '>=', $fromMonth . '-01'))
            ->when($toMonth,   fn($q) => $q->where('month', '<=', $toMonth . '-31'));

        $membersQuery = C1Member::query()
            ->when($state || $county || $payam, function ($q) use ($state, $county, $payam) {
                $q->whereHas(
                    'group',
                    fn($g) => $g
                        ->when($state, fn($g2) => $g2->where('state', $state))
                        ->when($county, fn($g2) => $g2->where('county', $county))
                        ->when($payam, fn($g2) => $g2->where('payam', $payam))
                );
            })
            ->when($fromMonth, fn($q) => $q->where('month', '>=', $fromMonth . '-01'))
            ->when($toMonth,   fn($q) => $q->where('month', '<=', $toMonth . '-31'));

        // ====================== QUICK STATS ======================
        $stats = [
            'total_groups'   => $groupsQuery->count(),
            'total_members'  => $membersQuery->sum(DB::raw('num_hh_members_total')) ?: $membersQuery->count(),
            'active_groups'  => $groupsQuery->whereHas('members', fn($q) => $q->where('active', true))->count(),
        ];

                // Total Supports (only from WBGS & EES – fixed polymorphic query)
        $kpiStats = [
            'supports' => C1Support::query()
                ->where(function ($query) {
                    // Case 1: Support given directly to a Group in WBGS/EES
                    $query->whereHasMorph('supportable', C1Group::class, function ($q) {
                        $q->whereIn('state', ['WBGS', 'EES']);
                    })
                    // Case 2: Support given to a Member whose Group is in WBGS/EES
                    ->orWhereHasMorph('supportable', C1Member::class, function ($q) {
                        $q->whereHas('group', function ($g) {
                            $g->whereIn('state', ['WBGS', 'EES']);
                        });
                    });
                })
                ->count()
        ];

        $groupsReceivingCapacityBuilding = $groupsQuery->clone()
            ->selectRaw("nature, SUM(total_members) as total")
            ->groupBy('nature')->get()->keyBy('nature');


        // ====================== SUPPORT PROVIDED BY STATE (WBGS & EES) ======================
        // This is the key change you asked for – real support counts per state
        $wbgsSupported = $this->getSupportedByNature('WBGS', $groupsQuery);
        $eesSupported  = $this->getSupportedByNature('EES', $groupsQuery);

        // ====================== REST OF THE CHARTS (now filtered) ======================
        $groupsByNature = $groupsQuery->clone()->selectRaw('nature, count(*) as achieved')->groupBy('nature')->get();

        $membershipByNature2 = $groupsQuery->clone()
            ->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(fn($i) => ['female' => (int)$i->female, 'male' => (int)$i->male, 'adol_girls' => (int)$i->adol_girls, 'total' => (int)$i->total]);

        $genderData = [
            'female' => $membersQuery->clone()->where('gender', 'female')->count(),
            'male'   => $membersQuery->clone()->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();

        $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 = [
            'CE'       => ['lat' => 4.85, 'lon' => 31.36],
            'EES'       => ['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],
            'WBGS'  => ['lat' => 7.88, 'lon' => 25.86],
            'WE'       => ['lat' => 5.31, 'lon' => 28.40],
            'Upper Nile' => ['lat' => 10.30, 'lon' => 32.70],
            'Warrap'     => ['lat' => 8.12, 'lon' => 28.30],
        ];

        // ====================== MAPS (filtered) ======================
        $groupLocations = $groupsQuery->clone()->select('id', 'name', 'county', 'payam')->get()->map(function ($group) {
            $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();

        $memberLocations = $groupsQuery->clone()->withCount('members')
            ->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();

        // Trainings, Kits, Capacity Building (filtered)
        $trainingsCumulative = $membersQuery->clone()->join('c1_groups as groups', 'c1_members.group_id', '=', 'groups.id')
            ->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
        ")->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,
            ]); // (keep your original selectRaw)

        $kitsGroupsCumulative = $groupsQuery->clone()->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,
            ];
        });

        // (repeat for kitsMembersCumulative, capacityBuildingCumulative, etc. – just replace C1Group:: with $groupsQuery->clone() and C1Member:: with $membersQuery->clone())

        $kitsMembersCumulative = $membersQuery->clone()->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 = $groupsQuery->clone()->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 = $groupsQuery->clone()->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 = $groupsQuery->clone()->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 = $groupsQuery->clone()->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 by county
        $groupsByCounty = $groupsQuery->clone()->selectRaw('county, count(*) as total')->groupBy('county')->get();
        
        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',
            'wbgsSupported',
            'eesSupported'
        ));
    }

    //Helper function for Support Provided By State
    private function getSupportedByNature(string $state, $baseQuery)
    {
        return $baseQuery->clone()
            ->where('state', $state)
            ->whereHas('supports')
            ->selectRaw("
                (CASE 
                    WHEN nature = 'matured' THEN 'matured'
                    WHEN nature = 'progressing' THEN 'progressive' 
                    ELSE 'new' END) as nature,
                COUNT(*) as achieved
            ")
            ->groupBy('nature')
            ->get()
            ->keyBy('nature')
            ->map(fn($item) => (int) $item->achieved);
    }
}
