<?php
namespace App\Http\Controllers\Admin;

use App\Http\Controllers\Controller;
use App\Models\Trade;
use App\Models\User;
use Illuminate\Support\Facades\DB;

class AdminReportController extends Controller
{
    public function index()
    {
        $daily_revenue = Trade::select(
                DB::raw('DATE(created_at) as date'),
                DB::raw('SUM(CASE WHEN status="lost" THEN amount ELSE 0 END) - SUM(CASE WHEN status="won" THEN actual_profit ELSE 0 END) as house_profit'),
                DB::raw('COUNT(*) as total_trades'),
                DB::raw('SUM(amount) as volume')
            )
            ->whereDate('created_at', '>=', now()->subDays(30))
            ->groupBy(DB::raw('DATE(created_at)'))
            ->orderBy('date', 'desc')
            ->get();

        $top_user_rows = DB::table('trades')
            ->select('trades.user_id', DB::raw('SUM(trades.amount) as total_volume'), DB::raw('COUNT(trades.id) as trade_count'))
            ->join('users', 'users.id', '=', 'trades.user_id')
            ->where('users.role', 'user')
            ->groupBy('trades.user_id')
            ->orderByDesc('total_volume')
            ->take(10)
            ->get();

        $user_map  = User::whereIn('id', $top_user_rows->pluck('user_id'))->get()->keyBy('id');
        $top_users = $top_user_rows->map(function ($row) use ($user_map) {
            $u = $user_map[$row->user_id] ?? null;
            $row->id    = $row->user_id;
            $row->name  = $u->name ?? 'N/A';
            $row->email = $u->email ?? '';
            return $row;
        });

        $asset_stats = Trade::select(
                'asset_symbol',
                DB::raw('COUNT(*) as total'),
                DB::raw('SUM(CASE WHEN status="won" THEN 1 ELSE 0 END) as wins'),
                DB::raw('SUM(CASE WHEN status="lost" THEN 1 ELSE 0 END) as losses'),
                DB::raw('SUM(amount) as volume')
            )
            ->groupBy('asset_symbol')
            ->orderByDesc('total')
            ->take(15)
            ->get();

        $registrations = User::select(DB::raw('DATE(created_at) as date'), DB::raw('COUNT(*) as count'))
            ->where('role', 'user')
            ->whereDate('created_at', '>=', now()->subDays(30))
            ->groupBy(DB::raw('DATE(created_at)'))
            ->orderBy('date', 'desc')
            ->get();

        return view('admin.reports.index', compact('daily_revenue', 'top_users', 'asset_stats', 'registrations'));
    }
}
