<?php

namespace App\Http\Controllers\Component2;

use App\Http\Controllers\Controller;
use App\Models\C2SeedlabBeneficiary;
use App\Models\C2Grant;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;

class C2SeedlabController extends Controller
{
    public function index()
    {
        $beneficiaries = C2SeedlabBeneficiary::with('grant')
            ->latest()
            ->paginate(50);

        // Base stats (for quick reference, but not used in cards anymore)
        $totalBeneficiaries = C2SeedlabBeneficiary::count();
        $femaleCount = C2SeedlabBeneficiary::whereIn('gender', ['Female', 'Adol.Girl'])->count();
        $withGrant = C2SeedlabBeneficiary::whereHas('grant')->count();
        $totalDisbursed = C2Grant::where('grantable_type', C2SeedlabBeneficiary::class)
                                ->sum('total_received');

        // WEOF Result Data (from Excel Sheet 0, hard-coded targets)
        $woefResults = [
            [
                'category' => 'SeedLab Boothcamp Training',
                'target' => 1182,
                'achieved' => C2SeedlabBeneficiary::where(function ($query) {
                    $query->where('module_1', 'Completed')
                          ->orWhere('module_2', 'Completed')
                          ->orWhere('module_3', 'Completed')
                          ->orWhere('module_4', 'Completed')
                          ->orWhere('module_5', 'Completed')
                          ->orWhere('module_6', 'Completed')
                          ->orWhere('module_7', 'Completed')
                          ->orWhere('module_8', 'Completed');
                })->count(), // Achieved: Beneficiaries with at least one module completed
            ],
            [
                'category' => 'SeedLab Participants who received Grant',
                'target' => 659,
                'achieved' => $withGrant,
            ],
            [
                'category' => 'GrowthLab Tailored Training',
                'target' => 18,
                'achieved' => 18, // Hard-coded from Excel; replace with query if dynamic
            ],
            [
                'category' => 'GrowthLab Grant who received Grant',
                'target' => 18,
                'achieved' => 8, // Hard-coded; replace if needed
            ],
            [
                'category' => 'WEOF Connect Participation',
                'target' => 677,
                'achieved' => C2SeedlabBeneficiary::where('woef_connect_participation', '>', 0)->count(),
            ],
            [
                'category' => 'Women SGBs graduated to next level',
                'target' => 'NA',
                'achieved' => 0,
            ],
        ];

        // SeedLab Capacity Building Data by Training Centre (Cumulative, from Excel Row14+)
        $byCentre = C2SeedlabBeneficiary::select(
            'training_centre',
            DB::raw('SUM(CASE WHEN gender = "Female" THEN 1 ELSE 0 END) as female'),
            DB::raw('SUM(CASE WHEN gender = "Adol.Girl" THEN 1 ELSE 0 END) as adol_girls'),
            DB::raw('COUNT(*) as total')
        )->groupBy('training_centre')->get();

        // SeedLab Participants Receiving Grant Cumulative by Centre
        $grantsByCentre = C2SeedlabBeneficiary::select(
            'training_centre',
            DB::raw('SUM(CASE WHEN gender = "Female" THEN 1 ELSE 0 END) as female'),
            DB::raw('SUM(CASE WHEN gender = "Adol.Girl" THEN 1 ELSE 0 END) as adol_girls'),
            DB::raw('COUNT(*) as total')
        )->whereHas('grant')->groupBy('training_centre')->get();

        // SeedLab Participants Receiving Capacity Building Cumulative by Cohort
        $byCohort = C2SeedlabBeneficiary::select(
            'cohort_year',
            DB::raw('SUM(CASE WHEN gender = "Female" THEN 1 ELSE 0 END) as female'),
            DB::raw('SUM(CASE WHEN gender = "Adol.Girl" THEN 1 ELSE 0 END) as adol_girls'),
            DB::raw('COUNT(*) as total')
        )->where(function ($query) {
            $query->where('module_1', 'Completed')->orWhere('module_2', 'Completed'); // Example; adjust for "capacity building"
        })->groupBy('cohort_year')->get();

        // SeedLab Businesses that Received Grant Cumulative by Tranche (assuming tranches in grant model; simplify if needed)
        $grantsByTranche = [
            '1st Tranche' => C2Grant::where('total_received', '>', 0)->count(), // Adjust logic if tranches are separate fields
            '2nd Tranche' => 0, // From Excel; query if dynamic
        ];

        // Data for Visualizations
        $chartData = [
            'targets_vs_achieved' => [
                'labels' => array_column($woefResults, 'category'),
                'targets' => array_map(fn($item) => is_numeric($item['target']) ? $item['target'] : 0, $woefResults),
                'achieved' => array_column($woefResults, 'achieved'),
            ],
            'gender_distribution' => C2SeedlabBeneficiary::select('gender', DB::raw('COUNT(*) as count'))->groupBy('gender')->pluck('count', 'gender')->toArray(),
            'sector_distribution' => C2SeedlabBeneficiary::select('business_sector', DB::raw('COUNT(*) as count'))->groupBy('business_sector')->pluck('count', 'business_sector')->toArray(),
            'county_distribution' => C2SeedlabBeneficiary::select('county', DB::raw('COUNT(*) as count'))->groupBy('county')->pluck('count', 'county')->toArray(),
        ];

        $countyData = C2SeedlabBeneficiary::select('county', DB::raw('COUNT(*) as count'))
            ->groupBy('county')
            ->pluck('count', 'county')
            ->toArray();

        $maxCount = $countyData ? max($countyData) : 1; // Avoid divide by zero

        return view('component2.seedlab.index', compact(
            'beneficiaries', 'totalBeneficiaries', 'femaleCount', 'withGrant', 'totalDisbursed',
            'woefResults', 'byCentre', 'grantsByCentre', 'byCohort', 'grantsByTranche', 'chartData', 'countyData', 'maxCount'
        ));
    }

    public function create()
    {
        return view('component2.seedlab.create');
    }
    public function store(Request $request)
    {
        $validated = $request->validate([
            'sn' => 'required|integer|unique:c2_seedlab_beneficiaries,sn',
            'full_name' => 'required|string|max:191',
            'business_sector' => 'required',
            'cohort_year' => 'required',
            'bootcamp_module_1' => 'required|in:Not Completed,Completed',
            'bootcamp_module_2' => 'required|in:Not Completed,Completed',
            'bootcamp_module_3' => 'required|in:Not Completed,Completed',
            'bootcamp_module_4' => 'required|in:Not Completed,Completed',
            'bootcamp_module_5' => 'required|in:Not Completed,Completed',
            'pitch_presentation' => 'required|in:Not Completed,Completed',
            'pitch_outcome' => 'nullable|in:Passed,Failed|required_if:pitch_presentation,Completed',
            'accelerator_module_1' => 'nullable|in:Passed,Failed',
            'accelerator_module_2' => 'nullable|in:Passed,Failed',
            'accelerator_module_3' => 'nullable|in:Passed,Failed',
            'accelerator_module_4' => 'nullable|in:Passed,Failed',
            'accelerator_module_5' => 'nullable|in:Passed,Failed',
            'accelerator_module_6' => 'nullable|in:Passed,Failed',
            'accelerator_module_7' => 'nullable|in:Passed,Failed',
            'accelerator_module_8' => 'nullable|in:Passed,Failed',
            'accelerator_module_9' => 'nullable|in:Passed,Failed',
            'accelerator_module_10' => 'nullable|in:Passed,Failed',
            // add more rules as needed
        ]);

        $beneficiary = C2SeedlabBeneficiary::create($request->except('grant'));

        if ($request->filled('grant')) {
            $beneficiary->grant()->updateOrCreate([], $request->input('grant'));
        }

        return redirect()->route('component2.seedlab.index')->with('success', 'Beneficiary registered successfully!');
    }

    public function edit(C2SeedlabBeneficiary $beneficiary)
    {
        return view('component2.seedlab.create', compact('beneficiary'));
    }

    public function update(Request $request, C2SeedlabBeneficiary $beneficiary)
    {
        $beneficiary->update($request->except('grant'));

        if ($request->filled('grant')) {
            $beneficiary->grant()->updateOrCreate([], $request->input('grant'));
        }

        return back()->with('success', 'Beneficiary updated successfully!');
    }

    public function destroy(C2SeedlabBeneficiary $beneficiary)
    {
        $beneficiary->delete();
        return back()->with('success', 'Beneficiary deleted successfully!');
    }
}