<?php

namespace App\Services;

use App\Models\AnalyticsEvent;
use App\Models\AuditLog;
use App\Models\Banner;
use App\Models\Coupon;
use App\Models\ManualPaymentSubmission;
use App\Models\Order;
use App\Models\OrderItem;
use App\Models\PaymentAttempt;
use App\Models\ProductVariant;
use App\Models\Provider;
use App\Models\ProviderLog;
use App\Models\ProviderProductMapping;
use App\Models\StockCode;
use App\Models\SupportTicket;
use App\Models\User;
use App\Models\Voucher;
use App\Models\Wallet;
use App\Models\WalletEntry;
use Carbon\Carbon;
use Carbon\CarbonPeriod;
use Illuminate\Database\Eloquent\Builder;
use Illuminate\Http\Request;
use Illuminate\Support\Collection;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;

class AdminMetricsService
{
    public function rangeFromRequest(Request $request): array
    {
        $period = $request->query('period', 'month');
        $now = now();

        [$from, $to] = match ($period) {
            'today' => [$now->copy()->startOfDay(), $now->copy()->endOfDay()],
            'week' => [$now->copy()->startOfWeek(), $now->copy()->endOfWeek()],
            'year' => [$now->copy()->startOfYear(), $now->copy()->endOfYear()],
            'custom' => [
                Carbon::parse($request->query('from', $now->copy()->startOfMonth()->toDateString()))->startOfDay(),
                Carbon::parse($request->query('to', $now->toDateString()))->endOfDay(),
            ],
            default => [$now->copy()->startOfMonth(), $now->copy()->endOfMonth()],
        };

        if ($from->greaterThan($to)) {
            [$from, $to] = [$to->copy()->startOfDay(), $from->copy()->endOfDay()];
        }

        return [$period, $from, $to];
    }

    public function dashboard(Carbon $from, Carbon $to): array
    {
        return [
            'summary' => $this->summary($from, $to),
            'charts' => $this->charts($from, $to),
            'topProducts' => $this->topProducts($from, $to),
            'topCustomers' => $this->topCustomers($from, $to),
            'lowStock' => ProductVariant::with('game')
                ->whereColumn('stock_available', '<=', 'min_stock_alert')
                ->orderBy('stock_available')
                ->limit(8)
                ->get(),
            'pendingManualPayments' => ManualPaymentSubmission::with(['user', 'gateway', 'order'])
                ->where('status', 'pending')
                ->latest()
                ->limit(8)
                ->get(),
            'openTickets' => SupportTicket::with('user')
                ->whereIn('status', ['open', 'pending'])
                ->latest()
                ->limit(8)
                ->get(),
            'recentOrders' => Order::with('user')->latest()->limit(8)->get(),
            'activity' => $this->activity($from, $to),
            'funnel' => $this->funnel($from, $to),
            'alerts' => $this->alerts(),
            'providerPulse' => $this->providerPulse(),
            'profitSnapshot' => $this->profitSnapshot($from, $to),
        ];
    }

    public function analytics(Carbon $from, Carbon $to): array
    {
        return [
            'summary' => $this->summary($from, $to),
            'charts' => $this->charts($from, $to),
            'funnel' => $this->funnel($from, $to),
            'topViewedProducts' => $this->topViewedProducts($from, $to),
            'searches' => AnalyticsEvent::where('event_name', 'search')
                ->whereBetween('occurred_at', [$from, $to])
                ->selectRaw("json_extract(metadata, '$.query') as query, count(*) as total")
                ->groupBy('query')
                ->orderByDesc('total')
                ->limit(12)
                ->get(),
        ];
    }

    public function customers(Carbon $from, Carbon $to, ?Request $request = null): array
    {
        $query = trim((string) $request?->query('q', ''));
        $status = $request?->query('status');
        $risk = $request?->query('risk');
        $wallet = $request?->query('wallet');
        $sort = $request?->query('sort', 'latest');

        $customers = User::query()
            ->with('wallet')
            ->withCount('orders')
            ->withSum('orders as total_spent_range', 'total_minor')
            ->where('role', '!=', 'admin')
            ->when($query !== '', function ($builder) use ($query) {
                $builder->where(function ($nested) use ($query) {
                    $nested->where('name', 'like', "%{$query}%")
                        ->orWhere('display_name', 'like', "%{$query}%")
                        ->orWhere('email', 'like', "%{$query}%")
                        ->orWhere('phone', 'like', "%{$query}%")
                        ->orWhere('public_id', 'like', "%{$query}%");
                });
            })
            ->when($status, fn ($builder) => $builder->where('status', $status))
            ->when($risk === 'high', fn ($builder) => $builder->where('risk_score', '>=', 70))
            ->when($risk === 'medium', fn ($builder) => $builder->whereBetween('risk_score', [35, 69]))
            ->when($risk === 'low', fn ($builder) => $builder->where('risk_score', '<', 35))
            ->when($wallet === 'frozen', fn ($builder) => $builder->whereHas('wallet', fn ($walletQuery) => $walletQuery->where('is_frozen', true)))
            ->when($sort === 'spent', fn ($builder) => $builder->orderByDesc('total_spent_minor'))
            ->when($sort === 'orders', fn ($builder) => $builder->orderByDesc('orders_count'))
            ->when($sort === 'risk', fn ($builder) => $builder->orderByDesc('risk_score'))
            ->when($sort === 'latest', fn ($builder) => $builder->latest())
            ->paginate(18)
            ->withQueryString();

        return [
            'summary' => [
                'total' => User::where('role', '!=', 'admin')->count(),
                'new' => User::where('role', '!=', 'admin')->whereBetween('created_at', [$from, $to])->count(),
                'active' => User::where('role', '!=', 'admin')->where('status', 'active')->count(),
                'frozenWallets' => Wallet::where('is_frozen', true)->count(),
            ],
            'customers' => $customers,
            'filters' => compact('query', 'status', 'risk', 'wallet', 'sort'),
        ];
    }

    public function finance(Carbon $from, Carbon $to): array
    {
        return [
            'summary' => [
                'revenue' => $this->paidOrders($from, $to)->sum('total_minor'),
                'discounts' => Order::whereBetween('created_at', [$from, $to])->sum('discount_minor'),
                'walletBalance' => Wallet::sum('balance_minor'),
                'manualPending' => ManualPaymentSubmission::where('status', 'pending')->sum('amount_minor'),
                'failedPayments' => PaymentAttempt::where('status', 'failed')->whereBetween('created_at', [$from, $to])->count(),
                'grossProfit' => $this->grossProfit($from, $to),
                'walletCredits' => WalletEntry::where('amount_minor', '>', 0)->whereBetween('created_at', [$from, $to])->sum('amount_minor'),
                'walletDebits' => abs((int) WalletEntry::where('amount_minor', '<', 0)->whereBetween('created_at', [$from, $to])->sum('amount_minor')),
                'vouchers' => Voucher::count(),
                'coupons' => Coupon::count(),
            ],
            'manualPayments' => ManualPaymentSubmission::with(['user', 'gateway', 'order'])->latest()->limit(12)->get(),
            'payments' => PaymentAttempt::with('order')->latest()->limit(12)->get(),
            'walletEntries' => WalletEntry::with('wallet.user')->latest()->limit(12)->get(),
            'gatewayPerformance' => ManualPaymentSubmission::query()
                ->with('gateway')
                ->whereBetween('created_at', [$from, $to])
                ->select('manual_payment_gateway_id', 'status', DB::raw('count(*) as total'), DB::raw('sum(amount_minor) as amount_minor'))
                ->groupBy('manual_payment_gateway_id', 'status')
                ->latest('total')
                ->limit(16)
                ->get(),
            'paymentStatuses' => PaymentAttempt::query()
                ->whereBetween('created_at', [$from, $to])
                ->select('gateway', 'status', DB::raw('count(*) as total'), DB::raw('sum(amount_minor) as amount_minor'))
                ->groupBy('gateway', 'status')
                ->orderByDesc('total')
                ->limit(12)
                ->get(),
        ];
    }

    public function marketing(Carbon $from, Carbon $to): array
    {
        return [
            'summary' => [
                'activeBanners' => Banner::where('is_active', true)->count(),
                'activeCoupons' => Coupon::where('is_active', true)->count(),
                'productViews' => $this->events($from, $to, 'product_view')->count(),
                'searches' => $this->events($from, $to, 'search')->count(),
                'cartAdds' => $this->events($from, $to, 'add_to_cart')->count(),
                'conversion' => $this->conversionRate($from, $to),
            ],
            'funnel' => $this->funnel($from, $to),
            'topViewedProducts' => $this->topViewedProducts($from, $to),
            'topProducts' => $this->topProducts($from, $to),
            'coupons' => Coupon::withCount('redemptions')->latest()->limit(12)->get(),
            'searches' => AnalyticsEvent::where('event_name', 'search')
                ->whereBetween('occurred_at', [$from, $to])
                ->selectRaw("json_extract(metadata, '$.query') as query, count(*) as total")
                ->groupBy('query')
                ->orderByDesc('total')
                ->limit(12)
                ->get(),
            'favorites' => DB::table('favorites')
                ->join('games', 'games.id', '=', 'favorites.game_id')
                ->select('games.name_ar', DB::raw('count(*) as total'))
                ->groupBy('games.name_ar')
                ->orderByDesc('total')
                ->limit(10)
                ->get(),
        ];
    }

    public function operations(Carbon $from, Carbon $to): array
    {
        return [
            'summary' => [
                'pendingOrders' => Order::where('fulfillment_status', 'pending')->count(),
                'processingOrders' => Order::where('fulfillment_status', 'processing')->count(),
                'failedFulfillment' => Order::where('fulfillment_status', 'failed')->count(),
                'lowStock' => ProductVariant::whereColumn('stock_available', '<=', 'min_stock_alert')->count(),
                'availableCodes' => StockCode::where('status', 'available')->count(),
                'openTickets' => SupportTicket::whereIn('status', ['open', 'pending'])->count(),
                'providerFailures' => Schema::hasTable('provider_logs') ? ProviderLog::where('status', 'failed')->whereBetween('created_at', [$from, $to])->count() : 0,
                'manualReview' => ManualPaymentSubmission::where('status', 'pending')->count(),
            ],
            'queue' => Order::with(['user', 'items'])->whereIn('fulfillment_status', ['pending', 'processing', 'failed'])->latest()->limit(12)->get(),
            'lowStock' => ProductVariant::with('game')->whereColumn('stock_available', '<=', 'min_stock_alert')->orderBy('stock_available')->limit(12)->get(),
            'tickets' => SupportTicket::with('user')->whereIn('status', ['open', 'pending'])->latest()->limit(12)->get(),
            'providerLogs' => Schema::hasTable('provider_logs')
                ? ProviderLog::with(['provider', 'order', 'orderItem'])->latest()->limit(15)->get()
                : collect(),
            'providerPulse' => $this->providerPulse(),
            'manualPayments' => ManualPaymentSubmission::with(['user', 'gateway'])
                ->where('status', 'pending')
                ->latest()
                ->limit(10)
                ->get(),
            'agingOrders' => Order::with('user')
                ->whereIn('fulfillment_status', ['pending', 'claimed', 'processing', 'info_requested'])
                ->where('created_at', '<=', now()->subHours(2))
                ->latest()
                ->limit(10)
                ->get(),
        ];
    }

    public function system(): array
    {
        return [
            'health' => [
                'PHP' => PHP_VERSION,
                'Laravel' => app()->version(),
                'Environment' => app()->environment(),
                'Database' => config('database.default'),
                'Cache' => config('cache.default'),
                'Queue' => config('queue.default'),
            ],
            'counts' => [
                'orders' => Order::count(),
                'users' => User::count(),
                'products' => ProductVariant::count(),
                'events' => Schema::hasTable('analytics_events') ? AnalyticsEvent::count() : 0,
                'providers' => Schema::hasTable('providers') ? Provider::count() : 0,
                'stock_codes' => StockCode::count(),
            ],
            'auditLogs' => AuditLog::with('user')->latest()->limit(15)->get(),
            'activity' => $this->activity(now()->subDays(7), now()),
            'readiness' => [
                ['label' => 'APP_KEY', 'status' => filled(config('app.key')), 'note' => 'مفتاح تشفير التطبيق'],
                ['label' => 'APP_DEBUG', 'status' => ! config('app.debug'), 'note' => 'يفضل إغلاقه في الإنتاج'],
                ['label' => 'Queue', 'status' => filled(config('queue.default')), 'note' => config('queue.default')],
                ['label' => 'Mail', 'status' => filled(config('mail.default')), 'note' => config('mail.default')],
                ['label' => 'Payments', 'status' => filled(config('raseednow.payments.driver')), 'note' => config('raseednow.payments.driver')],
            ],
            'providerPulse' => $this->providerPulse(),
        ];
    }

    private function summary(Carbon $from, Carbon $to): array
    {
        $paidOrders = $this->paidOrders($from, $to);
        $paidCount = (clone $paidOrders)->count();
        $revenue = (clone $paidOrders)->sum('total_minor');

        return [
            'revenue' => $revenue,
            'paidOrders' => $paidCount,
            'pendingOrders' => Order::where('payment_status', 'pending')->whereBetween('created_at', [$from, $to])->count(),
            'failedPayments' => PaymentAttempt::where('status', 'failed')->whereBetween('created_at', [$from, $to])->count(),
            'aov' => $paidCount > 0 ? (int) round($revenue / $paidCount) : 0,
            'newUsers' => User::whereBetween('created_at', [$from, $to])->count(),
            'returningUsers' => $this->returningUsers($from, $to),
            'walletTopups' => WalletEntry::whereIn('type', ['manual_topup', 'voucher_redeem', 'credit'])
                ->whereBetween('created_at', [$from, $to])
                ->sum('amount_minor'),
            'supportLoad' => SupportTicket::whereBetween('created_at', [$from, $to])->count(),
            'conversionRate' => $this->conversionRate($from, $to),
            'productViews' => $this->events($from, $to, 'product_view')->count(),
            'addToCart' => $this->events($from, $to, 'add_to_cart')->count(),
        ];
    }

    private function charts(Carbon $from, Carbon $to): array
    {
        $labels = collect(CarbonPeriod::create($from->copy()->startOfDay(), '1 day', $to->copy()->startOfDay()))
            ->map(fn (Carbon $date) => $date->format('Y-m-d'));

        $revenue = $this->dailyOrderAggregate($from, $to, 'sum(total_minor)');
        $orders = $this->dailyOrderAggregate($from, $to, 'count(*)');
        $users = $this->dailyUserAggregate($from, $to);
        $views = $this->dailyEventAggregate($from, $to, 'product_view');

        return [
            'sales' => [
                'labels' => $labels->values(),
                'series' => [
                    ['name' => 'الإيرادات', 'data' => $labels->map(fn ($day) => round(($revenue[$day] ?? 0) / 100, 2))->values()],
                    ['name' => 'الطلبات', 'data' => $labels->map(fn ($day) => (int) ($orders[$day] ?? 0))->values()],
                ],
            ],
            'growth' => [
                'labels' => $labels->values(),
                'series' => [
                    ['name' => 'مستخدمون جدد', 'data' => $labels->map(fn ($day) => (int) ($users[$day] ?? 0))->values()],
                    ['name' => 'مشاهدات المنتجات', 'data' => $labels->map(fn ($day) => (int) ($views[$day] ?? 0))->values()],
                ],
            ],
        ];
    }

    private function funnel(Carbon $from, Carbon $to): array
    {
        $steps = [
            'مشاهدات الصفحات' => $this->events($from, $to, 'page_view')->count(),
            'مشاهدات المنتجات' => $this->events($from, $to, 'product_view')->count(),
            'إضافة للسلة' => $this->events($from, $to, 'add_to_cart')->count(),
            'بدء الدفع' => $this->events($from, $to, 'checkout_start')->count(),
            'طلبات' => Order::whereBetween('created_at', [$from, $to])->count(),
            'مدفوع' => $this->paidOrders($from, $to)->count(),
        ];

        $max = max($steps ?: [1]) ?: 1;

        return collect($steps)->map(fn ($value, $label) => [
            'label' => $label,
            'value' => $value,
            'percent' => (int) round(($value / $max) * 100),
        ])->values()->all();
    }

    private function topProducts(Carbon $from, Carbon $to): Collection
    {
        return OrderItem::query()
            ->select('product_variant_id', 'name_snapshot', DB::raw('sum(quantity) as quantity'), DB::raw('sum(total_minor) as revenue_minor'))
            ->whereHas('order', fn (Builder $query) => $query->whereBetween('created_at', [$from, $to]))
            ->groupBy('product_variant_id', 'name_snapshot')
            ->orderByDesc('revenue_minor')
            ->limit(10)
            ->get();
    }

    private function topCustomers(Carbon $from, Carbon $to): Collection
    {
        return Order::query()
            ->with('user')
            ->select('user_id', 'customer_name', 'customer_email', DB::raw('count(*) as orders_count'), DB::raw('sum(total_minor) as total_minor'))
            ->whereBetween('created_at', [$from, $to])
            ->groupBy('user_id', 'customer_name', 'customer_email')
            ->orderByDesc('total_minor')
            ->limit(10)
            ->get();
    }

    private function topViewedProducts(Carbon $from, Carbon $to): Collection
    {
        return AnalyticsEvent::query()
            ->where('event_name', 'product_view')
            ->whereBetween('occurred_at', [$from, $to])
            ->select('entity_type', 'entity_id', DB::raw('count(*) as views'))
            ->groupBy('entity_type', 'entity_id')
            ->orderByDesc('views')
            ->limit(12)
            ->get();
    }

    private function activity(Carbon $from, Carbon $to): Collection
    {
        return AnalyticsEvent::with('user')
            ->whereBetween('occurred_at', [$from, $to])
            ->latest('occurred_at')
            ->limit(12)
            ->get();
    }

    private function paidOrders(Carbon $from, Carbon $to): Builder
    {
        return Order::where('payment_status', 'paid')->whereBetween('created_at', [$from, $to]);
    }

    private function events(Carbon $from, Carbon $to, string $eventName): Builder
    {
        return AnalyticsEvent::where('event_name', $eventName)->whereBetween('occurred_at', [$from, $to]);
    }

    private function conversionRate(Carbon $from, Carbon $to): float
    {
        $visits = max($this->events($from, $to, 'page_view')->distinct('session_id')->count('session_id'), 1);
        $orders = Order::whereBetween('created_at', [$from, $to])->count();

        return round(($orders / $visits) * 100, 2);
    }

    private function returningUsers(Carbon $from, Carbon $to): int
    {
        return Order::whereBetween('created_at', [$from, $to])
            ->whereNotNull('user_id')
            ->whereExists(function ($query) use ($from) {
                $query->selectRaw(1)
                    ->from('orders as previous_orders')
                    ->whereColumn('previous_orders.user_id', 'orders.user_id')
                    ->where('previous_orders.created_at', '<', $from);
            })
            ->distinct('user_id')
            ->count('user_id');
    }

    private function dailyOrderAggregate(Carbon $from, Carbon $to, string $expression): Collection
    {
        return Order::whereBetween('created_at', [$from, $to])
            ->selectRaw('date(created_at) as day, '.$expression.' as value')
            ->groupBy('day')
            ->pluck('value', 'day');
    }

    private function dailyUserAggregate(Carbon $from, Carbon $to): Collection
    {
        return User::whereBetween('created_at', [$from, $to])
            ->selectRaw('date(created_at) as day, count(*) as value')
            ->groupBy('day')
            ->pluck('value', 'day');
    }

    private function dailyEventAggregate(Carbon $from, Carbon $to, string $eventName): Collection
    {
        return AnalyticsEvent::where('event_name', $eventName)
            ->whereBetween('occurred_at', [$from, $to])
            ->selectRaw('date(occurred_at) as day, count(*) as value')
            ->groupBy('day')
            ->pluck('value', 'day');
    }

    private function alerts(): array
    {
        return [
            [
                'label' => 'مدفوعات يدوية تنتظر المراجعة',
                'value' => ManualPaymentSubmission::where('status', 'pending')->count(),
                'severity' => 'orange',
                'route' => 'admin.manual-payments.submissions',
            ],
            [
                'label' => 'منتجات منخفضة المخزون',
                'value' => ProductVariant::whereColumn('stock_available', '<=', 'min_stock_alert')->count(),
                'severity' => 'red',
                'route' => 'admin.products.index',
            ],
            [
                'label' => 'تذاكر دعم مفتوحة',
                'value' => SupportTicket::whereIn('status', ['open', 'pending'])->count(),
                'severity' => 'blue',
                'route' => 'admin.support-tickets.index',
            ],
            [
                'label' => 'مدفوعات فاشلة',
                'value' => PaymentAttempt::where('status', 'failed')->count(),
                'severity' => 'red',
                'route' => 'admin.finance.index',
            ],
            [
                'label' => 'عملاء موقوفون',
                'value' => User::where('status', 'suspended')->count(),
                'severity' => 'orange',
                'route' => 'admin.customers.index',
            ],
            [
                'label' => 'محافظ مجمدة',
                'value' => Wallet::where('is_frozen', true)->count(),
                'severity' => 'orange',
                'route' => 'admin.customers.index',
            ],
        ];
    }

    private function grossProfit(Carbon $from, Carbon $to): int
    {
        return (int) OrderItem::query()
            ->whereHas('order', fn (Builder $query) => $query->where('payment_status', 'paid')->whereBetween('created_at', [$from, $to]))
            ->leftJoin('product_variants', 'product_variants.id', '=', 'order_items.product_variant_id')
            ->selectRaw('sum(order_items.total_minor - (coalesce(product_variants.cost_minor, 0) * order_items.quantity)) as profit')
            ->value('profit');
    }

    private function profitSnapshot(Carbon $from, Carbon $to): array
    {
        $revenue = $this->paidOrders($from, $to)->sum('total_minor');
        $profit = $this->grossProfit($from, $to);

        return [
            'revenue' => $revenue,
            'profit' => $profit,
            'margin' => $revenue > 0 ? round(($profit / $revenue) * 100, 1) : 0,
        ];
    }

    private function providerPulse(): array
    {
        if (! Schema::hasTable('providers')) {
            return [
                'providers' => 0,
                'activeMappings' => 0,
                'healthy' => 0,
                'failedToday' => 0,
                'recentLogs' => collect(),
                'providersList' => collect(),
            ];
        }

        return [
            'providers' => Provider::count(),
            'activeMappings' => ProviderProductMapping::where('is_active', true)->count(),
            'healthy' => Provider::where('health_status', 'healthy')->count(),
            'failedToday' => ProviderLog::where('status', 'failed')->whereDate('created_at', today())->count(),
            'recentLogs' => ProviderLog::with('provider')->latest()->limit(8)->get(),
            'providersList' => Provider::withCount(['mappings', 'logs'])->latest()->limit(8)->get(),
        ];
    }
}
