<?php

namespace App\Http\Controllers;

use App\Models\CouponRedemption;
use App\Models\Order;
use App\Models\OrderItem;
use App\Models\ReferralReward;
use App\Models\SupportTicket;
use App\Models\User;
use App\Models\VoucherRedemption;
use App\Models\WalletEntry;
use Carbon\Carbon;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;

class AdminReportController extends Controller
{
    public function index(Request $request)
    {
        [$from, $to] = $this->dateRange($request);

        $orders = Order::query()->whereBetween('created_at', [$from, $to]);
        $paidOrders = (clone $orders)->where('payment_status', 'paid');

        return view('admin.reports.index', [
            'from' => $from,
            'to' => $to,
            'metrics' => [
                'orders' => (clone $orders)->count(),
                'paid_orders' => (clone $paidOrders)->count(),
                'revenue_minor' => (clone $paidOrders)->sum('total_minor'),
                'discount_minor' => (clone $orders)->sum('discount_minor'),
                'cashback_minor' => (clone $orders)->sum('cashback_minor'),
                'new_users' => User::whereBetween('created_at', [$from, $to])->count(),
                'voucher_topups_minor' => VoucherRedemption::whereBetween('created_at', [$from, $to])->sum('amount_minor'),
                'referral_rewards_minor' => ReferralReward::whereBetween('created_at', [$from, $to])->sum(DB::raw('referrer_reward_minor + referred_reward_minor')),
                'open_tickets' => SupportTicket::where('status', 'open')->count(),
            ],
            'dailyRevenue' => $this->dailyRevenue($from, $to),
            'ordersByStatus' => $this->ordersByStatus($from, $to),
            'topProducts' => $this->topProducts($from, $to),
            'topCustomers' => $this->topCustomers($from, $to),
            'couponUsage' => $this->couponUsage($from, $to),
            'voucherUsage' => $this->voucherUsage($from, $to),
            'walletMovement' => $this->walletMovement($from, $to),
            'referralRewards' => $this->referralRewards($from, $to),
            'supportByStatus' => $this->supportByStatus($from, $to),
        ]);
    }

    private function dateRange(Request $request): array
    {
        $from = $request->date('from')?->startOfDay() ?? now()->subDays(30)->startOfDay();
        $to = $request->date('to')?->endOfDay() ?? now()->endOfDay();

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

        return [$from, $to];
    }

    private function dailyRevenue(Carbon $from, Carbon $to)
    {
        return Order::selectRaw('DATE(created_at) as day, COUNT(*) as orders_count, SUM(total_minor) as revenue_minor')
            ->where('payment_status', 'paid')
            ->whereBetween('created_at', [$from, $to])
            ->groupBy('day')
            ->orderBy('day')
            ->get();
    }

    private function ordersByStatus(Carbon $from, Carbon $to)
    {
        return Order::selectRaw('payment_status, fulfillment_status, COUNT(*) as orders_count, SUM(total_minor) as total_minor')
            ->whereBetween('created_at', [$from, $to])
            ->groupBy('payment_status', 'fulfillment_status')
            ->orderByDesc('orders_count')
            ->get();
    }

    private function topProducts(Carbon $from, Carbon $to)
    {
        return OrderItem::query()
            ->join('orders', 'orders.id', '=', 'order_items.order_id')
            ->selectRaw('order_items.sku_snapshot, order_items.name_snapshot, SUM(order_items.quantity) as units, SUM(order_items.total_minor) as sales_minor')
            ->whereBetween('orders.created_at', [$from, $to])
            ->groupBy('order_items.sku_snapshot', 'order_items.name_snapshot')
            ->orderByDesc('sales_minor')
            ->limit(10)
            ->get();
    }

    private function topCustomers(Carbon $from, Carbon $to)
    {
        return User::query()
            ->join('orders', 'orders.user_id', '=', 'users.id')
            ->selectRaw('users.id, users.name, users.email, users.public_id, COUNT(orders.id) as orders_count, SUM(orders.total_minor) as spend_minor')
            ->whereBetween('orders.created_at', [$from, $to])
            ->groupBy('users.id', 'users.name', 'users.email', 'users.public_id')
            ->orderByDesc('spend_minor')
            ->limit(10)
            ->get();
    }

    private function couponUsage(Carbon $from, Carbon $to)
    {
        return CouponRedemption::query()
            ->join('coupons', 'coupons.id', '=', 'coupon_redemptions.coupon_id')
            ->selectRaw('coupons.code, coupons.name_ar, COUNT(coupon_redemptions.id) as uses_count, SUM(coupon_redemptions.discount_minor) as discount_minor')
            ->whereBetween('coupon_redemptions.created_at', [$from, $to])
            ->groupBy('coupons.code', 'coupons.name_ar')
            ->orderByDesc('uses_count')
            ->limit(10)
            ->get();
    }

    private function voucherUsage(Carbon $from, Carbon $to)
    {
        return VoucherRedemption::query()
            ->join('vouchers', 'vouchers.id', '=', 'voucher_redemptions.voucher_id')
            ->selectRaw('vouchers.code, vouchers.name_ar, COUNT(voucher_redemptions.id) as uses_count, SUM(voucher_redemptions.amount_minor) as amount_minor')
            ->whereBetween('voucher_redemptions.created_at', [$from, $to])
            ->groupBy('vouchers.code', 'vouchers.name_ar')
            ->orderByDesc('amount_minor')
            ->limit(10)
            ->get();
    }

    private function walletMovement(Carbon $from, Carbon $to)
    {
        return WalletEntry::selectRaw('type, COUNT(*) as entries_count, SUM(amount_minor) as amount_minor')
            ->whereBetween('created_at', [$from, $to])
            ->groupBy('type')
            ->orderByDesc('entries_count')
            ->get();
    }

    private function referralRewards(Carbon $from, Carbon $to)
    {
        return ReferralReward::query()
            ->with(['referrer', 'referred'])
            ->whereBetween('created_at', [$from, $to])
            ->latest()
            ->limit(10)
            ->get();
    }

    private function supportByStatus(Carbon $from, Carbon $to)
    {
        return SupportTicket::selectRaw('status, priority, COUNT(*) as tickets_count')
            ->whereBetween('created_at', [$from, $to])
            ->groupBy('status', 'priority')
            ->orderByDesc('tickets_count')
            ->get();
    }
}
