<?php

namespace App\Http\Controllers\Admin;

use App\Http\Controllers\Controller;
use App\Models\Subscription;
use App\Models\Client;
use App\Models\Movie;
use App\Models\Package;
use App\Models\Post;
use App\Models\TvShow;
use Carbon\Carbon;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;

class AdminController extends Controller
{
    public function index(Request $request)
    {
        $now = Carbon::now();
        $months = range(1, 12);
        $baseQuery = Subscription::where('status', 1);

        // Fetch actual data from DB
        $activeSubscriptions = Subscription::where('status', 1)
            ->selectRaw('MONTH(created_at) as month, COUNT(*) as total')
            ->whereYear('created_at', $now->year)
            ->groupBy('month')
            ->pluck('total', 'month')
            ->toArray();

        $earnings = Subscription::whereNotIn('status', [0, 2])
            ->selectRaw('MONTH(created_at) as month, SUM(price) as total')
            ->whereYear('created_at', $now->year)
            ->groupBy('month')
            ->pluck('total', 'month')
            ->toArray();

        $clients = Client::selectRaw('MONTH(created_at) as month, COUNT(*) as total')
            ->whereYear('created_at', $now->year)
            ->groupBy('month')
            ->pluck('total', 'month')
            ->toArray();
        $movies = Movie::selectRaw('MONTH(created_at) as month, COUNT(*) as total')
            ->whereYear('created_at', $now->year)
            ->groupBy('month')
            ->orderBy('month')
            ->pluck('total', 'month')
            ->toArray();

        $series = TvShow::selectRaw('MONTH(created_at) as month, COUNT(*) as total')
            ->whereYear('created_at', $now->year)
            ->groupBy('month')
            ->orderBy('month')
            ->pluck('total', 'month')
            ->toArray();

        // Fill in missing months with 0
        $filledActive = collect($months)->mapWithKeys(fn($m) => [($m - 1) => $activeSubscriptions[$m] ?? 0])->toArray();
        $filledEarnings = collect($months)->mapWithKeys(fn($m) => [($m - 1) => $earnings[$m] ?? 0])->toArray();
        $filledClients = collect($months)->mapWithKeys(fn($m) => [($m - 1) => $clients[$m] ?? 0])->toArray();
        $filledMovies = collect($months)->mapWithKeys(fn($m) => [($m - 1) => $movies[$m] ?? 0])->toArray();
        $filledSeries = collect($months)->mapWithKeys(fn($m) => [($m - 1) => $series[$m] ?? 0])->toArray();


        $data = [
            'charts' => [
                'active_subscriptions' => $filledActive,
                'earning' => $filledEarnings,
                'client' => $filledClients,
                'total_movies' => $filledMovies,
                'total_series' => $filledSeries,
            ],
            'best_client'                => (clone $baseQuery)->select('client_id', DB::raw('COUNT(*) as total'))
                ->groupBy('client_id')
                ->orderByDesc('total') // avoid orderByDesc('created_at') here
                ->take(5)
                ->get()
                ->pluck('total', 'client_id'),
            'packages'                   => Package::take(5)->get(),
            'subscriptions'              => Subscription::with('client')->latest()->get(),
            'total_active_subscriptions' => Subscription::where('status', 1)->where('expire_date', '>=', $now)->count(),
            'last_month_subscriptions'   => Subscription::whereYear('created_at', $now->year)->whereMonth('created_at', $now->month)->count(),
            'total_earning'              => Subscription::whereNotIn('status', [0, 2])->sum('price'),
            'last_month_earning'         => Subscription::whereNotIn('status', [0, 2])->whereYear('created_at', $now->year)->whereMonth('created_at', $now->month)->sum('price'),
            'total_client'               => Client::count(),
            'last_month_client'          => Client::whereYear('created_at', $now->year)->whereMonth('created_at', $now->month)->count(),
            'total_movies' => Movie::count(), // Assuming you have a Movie model
            'total_series' => TvShow::count(),
            'best_selling_plans' => Subscription::select('plan_id', DB::raw('COUNT(*) as total'))
                ->groupBy('plan_id')
                ->orderByDesc('total')
                ->take(5)
                ->pluck('total', 'plan_id'),
            'recent_subscriptions' => Subscription::with('client')
                ->latest()
                ->take(5)
                ->get(),
            'best_clients' => (clone $baseQuery)->select('client_id', DB::raw('COUNT(*) as total'))
                ->groupBy('client_id')
                ->orderByDesc('total') // avoid orderByDesc('created_at') here
                ->take(5)
                ->get()
                ->pluck('total', 'client_id'),
        ];

        return view('admin.dashboard.index', $data);
    }
}
