<?php

namespace App\Filament\Widgets;

use App\Models\TravelAgent;
use App\Models\User;
use App\Models\Payment;
use App\Models\AiSubscription;
use Filament\Widgets\StatsOverviewWidget as BaseWidget;
use Filament\Widgets\StatsOverviewWidget\Stat;
use Illuminate\Support\Facades\DB;

class StatsOverview extends BaseWidget
{
    protected static ?int $sort = 1;
    protected function getStats(): array
    {
        $totalRevenue = Payment::where('status', 'captured')->sum('amount');
        $monthlyRevenue = Payment::where('status', 'captured')
            ->where('created_at', '>=', now()->subMonth())
            ->sum('amount');
            
        $activeSubscriptions = AiSubscription::where('status', 'active')
            ->where('end_date', '>', now())
            ->count();

        return [
            Stat::make('Total Travel Agents', TravelAgent::count())
                ->description('All registered companies')
                ->descriptionIcon('heroicon-o-building-office')
                ->color('success')
                ->chart($this->getTravelAgentGrowthChart()),

            Stat::make('Active Users', User::where('is_active', true)->count())
                ->description('Currently active users')
                ->descriptionIcon('heroicon-o-users')
                ->color('info')
                ->chart($this->getUserGrowthChart()),

            Stat::make('Total Revenue', '₹' . number_format($totalRevenue, 2))
                ->description('₹' . number_format($monthlyRevenue, 2) . ' this month')
                ->descriptionIcon('heroicon-o-currency-rupee')
                ->color('warning')
                ->chart($this->getRevenueChart()),

            Stat::make('Active Subscriptions', $activeSubscriptions)
                ->description('Currently active AI plans')
                ->descriptionIcon('heroicon-o-sparkles')
                ->color('primary')
                ->chart($this->getSubscriptionChart()),
        ];
    }

    private function getTravelAgentGrowthChart(): array
    {
        return TravelAgent::selectRaw('DATE(created_at) as date, COUNT(*) as count')
            ->where('created_at', '>=', now()->subDays(30))
            ->groupBy('date')
            ->orderBy('date')
            ->pluck('count')
            ->toArray();
    }

    private function getUserGrowthChart(): array
    {
        return User::selectRaw('DATE(created_at) as date, COUNT(*) as count')
            ->where('created_at', '>=', now()->subDays(30))
            ->groupBy('date')
            ->orderBy('date')
            ->pluck('count')
            ->toArray();
    }

    private function getRevenueChart(): array
    {
        return Payment::selectRaw('DATE(created_at) as date, SUM(amount) as total')
            ->where('status', 'captured')
            ->where('created_at', '>=', now()->subDays(30))
            ->groupBy('date')
            ->orderBy('date')
            ->pluck('total')
            ->toArray();
    }

    private function getSubscriptionChart(): array
    {
        return AiSubscription::selectRaw('DATE(created_at) as date, COUNT(*) as count')
            ->where('created_at', '>=', now()->subDays(30))
            ->groupBy('date')
            ->orderBy('date')
            ->pluck('count')
            ->toArray();
    }
}