<?php

namespace App\Filament\Pages;

use Filament\Pages\Page;
use Illuminate\Support\Facades\DB;

class Analytics extends Page
{
    protected static ?string $navigationIcon = 'heroicon-o-chart-pie';
    protected static ?string $navigationLabel = 'آمار و تحلیل';
    protected static ?string $navigationGroup = 'آمار و تحلیل';
    protected static string $view = 'filament.pages.analytics';

    public function getTotalUsers(): int
    {
        return DB::table('users')->count();
    }

    public function getActiveUsers(): int
    {
        return DB::table('users')
            ->where('created_at', '>=', now()->subDays(7))
            ->count();
    }

    public function getPendingPayments(): int
    {
        return DB::table('payments')
            ->where('status', 'pending')
            ->count();
    }

    public function getRecentErrors(): int
    {
        return DB::table('logs')
            ->where('level', 'error')
            ->where('created_at', '>=', now()->subHour())
            ->count();
    }

    public function getPaymentStats(): array
    {
        $since = now()->subDays(30);
        $total = DB::table('payments')->where('created_at', '>=', $since)->count();
        $successful = DB::table('payments')->where('created_at', '>=', $since)->where('status', 'ok')->count();
        $failed = $total - $successful;

        return [
            'successful' => $successful,
            'failed' => $failed,
            'success_rate' => $total > 0 ? round(($successful / $total) * 100, 1) : 0,
            'fail_rate' => $total > 0 ? round(($failed / $total) * 100, 1) : 0,
        ];
    }

    public function getCampaignStats(): array
    {
        $since = now()->subDays(7);

        $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');

        $stats = [];
        foreach ($delivered as $cid => $d) {
            $c = (int)($clicks[$cid] ?? 0);
            $ctr = $d ? round(($c / $d) * 100, 1) : 0.0;
            $stats[] = [
                'campaign_id' => $cid,
                'delivered' => $d,
                'clicks' => $c,
                'ctr' => $ctr,
            ];
        }

        return $stats;
    }

    public function getCityStats(): array
    {
        $since = now()->subDays(7);

        return DB::table('opportunities')
            ->join('cities', 'opportunities.city_id', '=', 'cities.id')
            ->leftJoin('saved_ads', function($join) use ($since) {
                $join->on('opportunities.id', '=', 'saved_ads.opportunity_id')
                     ->where('saved_ads.created_at', '>=', $since);
            })
            ->where('opportunities.created_at', '>=', $since)
            ->select(
                'cities.name as city',
                DB::raw('count(opportunities.id) as opportunities'),
                DB::raw('count(saved_ads.id) as saved')
            )
            ->groupBy('cities.id', 'cities.name')
            ->orderBy('saved', 'desc')
            ->limit(10)
            ->get()
            ->toArray();
    }

    public function getAiStats(): array
    {
        $since = now()->subDays(7);

        $stats = DB::table('chats')
            ->where('created_at', '>=', $since)
            ->selectRaw('
                count(*) as requests,
                avg(latency_ms) as avg_latency,
                sum(token_in + token_out) as tokens,
                sum(case when latency_ms > 2500 then 1 else 0 end) as errors
            ')
            ->first();

        return [
            'requests' => (int)($stats->requests ?? 0),
            'avg_latency' => (float)($stats->avg_latency ?? 0),
            'tokens' => (int)($stats->tokens ?? 0),
            'errors' => (int)($stats->errors ?? 0),
        ];
    }
}

