<?php

namespace App\Filament\Widgets;

use Filament\Widgets\StatsOverviewWidget as BaseWidget;
use Filament\Widgets\StatsOverviewWidget\Stat;
use Illuminate\Support\Facades\DB;

class StatsOverview extends BaseWidget
{
    protected function getStats(): array
    {
        $since = now()->subDays(7);

        // CTR by campaign (last 7 days)
        $delivered = DB::table('push_logs')
            ->select('campaign_id', DB::raw('count(*) as delivered'))
            ->whereNotNull('delivered_at')
            ->where('delivered_at', '>=', $since)
            ->groupBy('campaign_id')
            ->pluck('delivered', 'campaign_id');

        $clicks = DB::table('push_logs')
            ->select('campaign_id', DB::raw('count(*) as clicks'))
            ->whereNotNull('clicked_cta_at')
            ->where('clicked_cta_at', '>=', $since)
            ->groupBy('campaign_id')
            ->pluck('clicks', 'campaign_id');

        $totalDelivered = $delivered->sum();
        $totalClicks = 0;
        foreach ($clicks as $cid => $c) {
            $totalClicks += $c;
        }
        $ctr = $totalDelivered > 0 ? round(($totalClicks / $totalDelivered) * 100, 1) : 0.0;

        // Saved/Delivered last 7 days
        $saved = DB::table('saved_ads')->where('created_at', '>=', $since)->count();
        $savedPercentage = $totalDelivered > 0 ? round(($saved / $totalDelivered) * 100, 1) : 0.0;

        // D1 Activation: users with >=3 distinct events within 24h of first event in last 7d
        $events = DB::table('events')->where('created_at', '>=', $since)->orderBy('user_id')->orderBy('created_at')->get();
        $activated = 0;
        $seen = [];
        foreach ($events as $e) {
            $uid = $e->user_id;
            if (!isset($seen[$uid])) {
                $seen[$uid] = ['start' => \Carbon\Carbon::parse($e->created_at), 'types' => []];
            }
            $winStart = $seen[$uid]['start'];
            if (\Carbon\Carbon::parse($e->created_at)->diffInHours($winStart) <= 24) {
                $seen[$uid]['types'][$e->type] = true;
            }
        }
        foreach ($seen as $uid => $data) {
            if (count($data['types']) >= 3) {
                $activated++;
            }
        }

        // Payment success 7d: ok / (ok + failed)
        $totalPayments = DB::table('payments')->where('created_at', '>=', $since)->count();
        $successfulPayments = DB::table('payments')->where('created_at', '>=', $since)->where('status', 'ok')->count();
        $paymentSuccessRate = $totalPayments > 0 ? round(($successfulPayments / $totalPayments) * 100, 1) : 0.0;

        return [
            Stat::make('CTR کمپین‌ها (۷ روز)', $ctr . '%')
                ->description("کل کلیک‌ها: {$totalClicks} از {$totalDelivered} ارسال")
                ->descriptionIcon('heroicon-m-cursor-arrow-rays')
                ->color($ctr >= 25 ? 'success' : ($ctr >= 15 ? 'warning' : 'danger')),

            Stat::make('نرخ ذخیره آگهی (۷ روز)', $savedPercentage . '%')
                ->description("ذخیره شده: {$saved} از {$totalDelivered} ارسال")
                ->descriptionIcon('heroicon-m-bookmark')
                ->color($savedPercentage >= 10 ? 'success' : ($savedPercentage >= 5 ? 'warning' : 'danger')),

            Stat::make('فعال‌سازی D1 (۷ روز)', $activated)
                ->description('کاربران با حداقل ۳ رویداد در ۲۴ ساعت')
                ->descriptionIcon('heroicon-m-user-group')
                ->color($activated >= 50 ? 'success' : ($activated >= 25 ? 'warning' : 'danger')),

            Stat::make('موفقیت پرداخت (۷ روز)', $paymentSuccessRate . '%')
                ->description("پرداخت‌های موفق: {$successfulPayments} از {$totalPayments}")
                ->descriptionIcon('heroicon-m-banknotes')
                ->color($paymentSuccessRate >= 98 ? 'success' : ($paymentSuccessRate >= 95 ? 'warning' : 'danger')),
        ];
    }
}
