<?php

namespace App\Http\Controllers\UnitAdmin;

use App\Http\Controllers\Controller;
use App\Models\MemberProfile;
use App\Models\UnitAdmin;
use App\Models\Unit;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\Auth;

class UnitAdminReportController extends Controller
{
    private function myUnitId(): int
    {
        return UnitAdmin::where('user_id', Auth::id())->value('unit_id');
    }

    public function index()
    {
        $unitId = $this->myUnitId();

        // ── Load unit with district + state (same as dashboard) ──
        $unit = Unit::with('district.state')->find($unitId);

        $monthlyData = MemberProfile::where('unit_id', $unitId)
            ->selectRaw('MONTH(created_at) as month, YEAR(created_at) as year, COUNT(*) as count')
            ->where('created_at', '>=', now()->subMonths(6))
            ->groupBy('year', 'month')->orderBy('year')->orderBy('month')->get();

        $monthlyPayments = MemberProfile::where('member_profiles.unit_id', $unitId)
            ->join('membership_plans', 'member_profiles.plan_id', '=', 'membership_plans.id')
            ->selectRaw('MONTH(member_profiles.created_at) as month,
                         YEAR(member_profiles.created_at)  as year,
                         SUM(membership_plans.amount)       as total')
            ->where('member_profiles.created_at', '>=', now()->subMonths(6))
            ->groupBy('year', 'month')->orderBy('year')->orderBy('month')->get();

        $totalAmountCollected = MemberProfile::where('member_profiles.unit_id', $unitId)
            ->join('membership_plans', 'member_profiles.plan_id', '=', 'membership_plans.id')
            ->sum('membership_plans.amount');

        $planBreakdown = MemberProfile::where('member_profiles.unit_id', $unitId)
            ->join('membership_plans', 'member_profiles.plan_id', '=', 'membership_plans.id')
            ->selectRaw('membership_plans.id          as plan_id,
                         membership_plans.name        as plan_name,
                         membership_plans.amount,
                         membership_plans.type,
                         COUNT(*)                     as count,
                         SUM(membership_plans.amount) as total_amount')
            ->groupBy('membership_plans.id', 'membership_plans.name',
                      'membership_plans.amount', 'membership_plans.type')
            ->orderByDesc('total_amount')->get();

        return view('unit-admin.reports', [
            // ── same as dashboard ──
            'unit'                 => $unit,
            'totalMembers'         => MemberProfile::where('unit_id', $unitId)->count(),
            'activeMembers'        => MemberProfile::where('unit_id', $unitId)->where('status', 'ACTIVE')->count(),
            'approvedMembers'      => MemberProfile::where('unit_id', $unitId)->where('status', 'APPROVED')->count(),
            'pendingMembers'       => MemberProfile::where('unit_id', $unitId)->where('status', 'PENDING')->count(),
            'rejectedMembers'      => MemberProfile::where('unit_id', $unitId)->where('status', 'REJECTED')->count(),
            'lifeMembers'          => MemberProfile::where('unit_id', $unitId)->where('membership_type', 'LIFE')->count(),
            'annualMembers'        => MemberProfile::where('unit_id', $unitId)->where('membership_type', 'ANNUAL')->count(),
            'totalAmountCollected' => $totalAmountCollected,
            'monthlyData'          => $monthlyData,
            'monthlyPayments'      => $monthlyPayments,
            'planBreakdown'        => $planBreakdown,
        ]);
    }

    public function preview(Request $request)
    {
        $unitId = $this->myUnitId();

        $query = MemberProfile::with(['user', 'unit', 'district', 'state', 'plan'])
                              ->where('unit_id', $unitId);

        if ($request->filled('status'))          $query->where('status', $request->status);
        if ($request->filled('membership_type')) $query->where('membership_type', $request->membership_type);
        if ($request->filled('plan_id'))         $query->where('plan_id', $request->plan_id);
        if ($request->filled('month'))           $query->whereMonth('created_at', $request->month);
        if ($request->filled('year'))            $query->whereYear('created_at', $request->year);
        if ($request->filled('date_from'))       $query->whereDate('created_at', '>=', $request->date_from);
        if ($request->filled('date_to'))         $query->whereDate('created_at', '<=', $request->date_to);

        $members = $query->latest()->get();

        $amountQuery = MemberProfile::where('member_profiles.unit_id', $unitId)
            ->join('membership_plans', 'member_profiles.plan_id', '=', 'membership_plans.id');

        if ($request->filled('status'))          $amountQuery->where('member_profiles.status', $request->status);
        if ($request->filled('membership_type')) $amountQuery->where('member_profiles.membership_type', $request->membership_type);
        if ($request->filled('plan_id'))         $amountQuery->where('member_profiles.plan_id', $request->plan_id);
        if ($request->filled('month'))           $amountQuery->whereMonth('member_profiles.created_at', $request->month);
        if ($request->filled('year'))            $amountQuery->whereYear('member_profiles.created_at', $request->year);
        if ($request->filled('date_from'))       $amountQuery->whereDate('member_profiles.created_at', '>=', $request->date_from);
        if ($request->filled('date_to'))         $amountQuery->whereDate('member_profiles.created_at', '<=', $request->date_to);

        return response()->json([
            'count'        => $members->count(),
            'total_amount' => $amountQuery->sum('membership_plans.amount'),
            'members'      => $members->map(fn($m) => [
                'name'            => $m->user->name,
                'email'           => $m->user->email,
                'phone'           => $m->user->phone,
                'organization'    => $m->organization,
                'membership_type' => $m->membership_type,
                'membership_id'   => $m->membership_id ?? '—',
                'status'          => $m->status,
                'registered_on'   => $m->created_at->format('d M Y'),
                'plan_name'       => $m->plan?->name ?? '—',
                'plan_amount'     => $m->plan?->amount ?? 0,
            ]),
        ]);
    }

    public function download(Request $request)
    {
        $unitId    = $this->myUnitId();
        $unit      = Unit::find($unitId);
        $unitName  = $unit?->name ?? 'unit';

        $query = MemberProfile::with(['user', 'district', 'state', 'plan'])
                              ->where('unit_id', $unitId);

        if ($request->filled('status'))          $query->where('status', $request->status);
        if ($request->filled('membership_type')) $query->where('membership_type', $request->membership_type);
        if ($request->filled('plan_id'))         $query->where('plan_id', $request->plan_id);
        if ($request->filled('month'))           $query->whereMonth('created_at', $request->month);
        if ($request->filled('year'))            $query->whereYear('created_at', $request->year);
        if ($request->filled('date_from'))       $query->whereDate('created_at', '>=', $request->date_from);
        if ($request->filled('date_to'))         $query->whereDate('created_at', '<=', $request->date_to);

        $members  = $query->latest()->get();
        $filename = 'eca_' . str_replace(' ', '_', strtolower($unitName))
                  . '_report_' . now()->format('d_M_Y') . '.csv';

        return response()->stream(function () use ($members) {
            $file = fopen('php://output', 'w');
            fputcsv($file, [
                'S.No', 'Name', 'Email', 'Phone',
                'Organization', 'Designation', 'Experience',
                'Membership Plan', 'Plan Amount (₹)',
                'Membership Type', 'Membership ID',
                'Status', 'District', 'State',
                'Joined Date', 'Expiry Date', 'Registered On',
            ]);
            foreach ($members as $i => $m) {
                fputcsv($file, [
                    $i + 1,
                    $m->user->name,    $m->user->email,  $m->user->phone,
                    $m->organization,  $m->designation,  $m->experience . ' yrs',
                    $m->plan?->name ?? '—', $m->plan?->amount ?? 0,
                    $m->membership_type, $m->membership_id ?? '—',
                    $m->status,
                    $m->district->name, $m->state->name,
                    $m->joined_date ?? '—', $m->expiry_date ?? '—',
                    $m->created_at->format('d M Y'),
                ]);
            }
            fclose($file);
        }, 200, [
            'Content-Type'        => 'text/csv',
            'Content-Disposition' => "attachment; filename=\"{$filename}\"",
        ]);
    }
}
