<?php

namespace App\Http\Controllers\Admin;

use App\Http\Controllers\Controller;
use App\Models\MemberProfile;
use App\Models\District;
use App\Models\Unit;
use App\Models\State;
use Illuminate\Http\Request;

class ReportController extends Controller
{
    public function index()
{
    return view('admin.reports', [
        // ── location dropdowns ──
        'states'    => State::all(),
        'districts' => District::all(),
        'units'     => Unit::all(),

        // ── member counts ──
        'totalMembers'    => MemberProfile::count(),
        'activeMembers'   => MemberProfile::where('status', 'ACTIVE')->count(),
        'approvedMembers' => MemberProfile::where('status', 'APPROVED')->count(),
        'pendingMembers'  => MemberProfile::where('status', 'PENDING')->count(),
        'rejectedMembers' => MemberProfile::where('status', 'REJECTED')->count(),
        'lifeMembers'     => MemberProfile::where('membership_type', 'LIFE')->count(),
        'annualMembers'   => MemberProfile::where('membership_type', 'ANNUAL')->count(),

        // ── revenue totals ──
        'totalRevenue'  => MemberProfile::join('membership_plans','member_profiles.plan_id','=','membership_plans.id')
                               ->sum('membership_plans.amount'),
        'lifeRevenue'   => MemberProfile::where('membership_type','LIFE')
                               ->join('membership_plans','member_profiles.plan_id','=','membership_plans.id')
                               ->sum('membership_plans.amount'),
        'annualRevenue' => MemberProfile::where('membership_type','ANNUAL')
                               ->join('membership_plans','member_profiles.plan_id','=','membership_plans.id')
                               ->sum('membership_plans.amount'),

        // ── monthly registrations (last 6 months) ──
        'monthlyData'    => MemberProfile::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(),

        // ── monthly revenue (last 6 months) ──
        'monthlyRevenue' => MemberProfile::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(),

        // ── top units (members + revenue) ──
        'topUnits'       => Unit::withCount('memberProfiles')
                                ->withSum(['memberProfiles as unit_revenue' => fn($q) =>
                                    $q->join('membership_plans','member_profiles.plan_id','=','membership_plans.id')
                                      ->selectRaw('SUM(membership_plans.amount)')
                                ], 'membership_plans.amount')
                                ->orderByDesc('member_profiles_count')->take(5)->get(),

        // ── top districts (members + revenue) ──
        'topDistricts'   => District::withCount('memberProfiles')
                                ->withSum(['memberProfiles as district_revenue' => fn($q) =>
                                    $q->join('membership_plans','member_profiles.plan_id','=','membership_plans.id')
                                      ->selectRaw('SUM(membership_plans.amount)')
                                ], 'membership_plans.amount')
                                ->orderByDesc('member_profiles_count')->take(5)->get(),
    ]);
}

    // ─────────────────────────────────
    // AJAX PREVIEW
    // ─────────────────────────────────
    public function preview(Request $request)
    {
        $query = MemberProfile::with(['user', 'unit', 'district', 'state', 'plan']);

        if ($request->filled('state_id'))        $query->where('state_id', $request->state_id);
        if ($request->filled('district_id'))     $query->where('district_id', $request->district_id);
        if ($request->filled('unit_id'))         $query->where('unit_id', $request->unit_id);
        if ($request->filled('status'))          $query->where('status', $request->status);
        if ($request->filled('membership_type')) $query->where('membership_type', $request->membership_type);
        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();

        // Total amount for filtered set
        $amountQuery = MemberProfile::join('membership_plans', 'member_profiles.plan_id', '=', 'membership_plans.id');
        if ($request->filled('state_id'))        $amountQuery->where('member_profiles.state_id', $request->state_id);
        if ($request->filled('district_id'))     $amountQuery->where('member_profiles.district_id', $request->district_id);
        if ($request->filled('unit_id'))         $amountQuery->where('member_profiles.unit_id', $request->unit_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('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);
        $totalAmount = $amountQuery->sum('membership_plans.amount');

        return response()->json([
            'count'        => $members->count(),
            'total_amount' => $totalAmount,
            'members'      => $members->map(fn($m) => [
                'name'            => $m->user->name,
                'email'           => $m->user->email,
                'phone'           => $m->user->phone,
                'organization'    => $m->organization,
                'unit'            => $m->unit->name,
                'district'        => $m->district->name,
                'state'           => $m->state->name,
                '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,
            ]),
        ]);
    }

    // ─────────────────────────────────
    // CSV DOWNLOAD
    // ─────────────────────────────────
    public function download(Request $request)
    {
        $query = MemberProfile::with(['user', 'unit', 'district', 'state', 'plan']);

        if ($request->filled('status'))          $query->where('status', $request->status);
        if ($request->filled('membership_type')) $query->where('membership_type', $request->membership_type);
        if ($request->filled('district_id'))     $query->where('district_id', $request->district_id);
        if ($request->filled('unit_id'))         $query->where('unit_id', $request->unit_id);
        if ($request->filled('state_id'))        $query->where('state_id', $request->state_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_members_report_' . now()->format('d_M_Y') . '.csv';

        $headers = [
            'Content-Type'        => 'text/csv',
            'Content-Disposition' => "attachment; filename=\"{$filename}\"",
        ];

        $callback = 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', 'Unit', '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->unit->name,
                    $m->district->name,
                    $m->state->name,
                    $m->joined_date ?? '—',
                    $m->expiry_date ?? '—',
                    $m->created_at->format('d M Y'),
                ]);
            }

            fclose($file);
        };

        return response()->stream($callback, 200, $headers);
    }
}

