<?php
namespace App\Nexus\Analytics;

use App\Models\Bid;
use App\Models\Sell;
use App\Models\User;
use App\Nexus\Engine\EconomyGuardian;
use App\Nexus\Engine\SessionController;
use App\Nexus\Models\NexusSession;
use Carbon\Carbon;
use Illuminate\Support\Facades\Cache;
use Illuminate\Support\Facades\DB;

/**
 * NEXUS Analytics - DashboardService
 *
 * Aggregates data for the Overview dashboard page.
 * Read-only. Never writes to the database.
 * Returns well-structured arrays ready for blade views or JSON API.
 *
 * PERFORMANCE NOTES (March 9, 2026 -- Phase 6 rewrite):
 * - getTradingActivityChart: 48 queries down to 2 (GROUP BY HOUR)
 * - getProfitCapWarnings: N+1 eliminated (JOIN instead of User::find loop)
 * - getSessionResultSummary: 3 queries down to 1 (conditional COUNT)
 * - getSessionMetrics: 4 queries down to 2 (combined bid count + volume)
 * - All methods cached 30-60 seconds
 *
 * PHASE 6.5 ADDITIONS (March 2026):
 * - All methods accept optional $startDate/$endDate for historical access
 * - getSessionMetrics accepts $date for stat strip date awareness
 * - getTradingActivityChart accepts date range instead of hours
 * - getSessionResultSummary accepts $date
 * - getSessionHistory: paginated session list with stats
 * - getSessionDetail: expanded session with criteria, rejections, winners
 *
 * @package App\Nexus\Analytics
 */
class DashboardService
{
    /**
     * Current session status, countdown, bid/sell activity.
     *
     * When $date is null (default), shows today's data and live session info.
     * When $date is provided, shows historical data for that date (no live session).
     *
     * Cached 30 seconds for live data. Historical data cached 5 minutes.
     */
    public function getSessionMetrics(?string $startDate = null, ?string $endDate = null): array
    {
        $today    = Carbon::today()->toDateString();
        $start    = $startDate ?? $today;
        $end      = $endDate ?? $start;
        $isRange  = $start !== $end;
        $isToday  = $start === $today && $end === $today;
        $cacheTtl = $isToday ? 30 : ($isRange ? 120 : 300);
        $cacheKey = 'nexus.dash.session_metrics_' . $start . '_' . $end;

        return Cache::remember($cacheKey, $cacheTtl, function () use ($start, $end, $isRange, $isToday) {
            // Live session info only for today
            $controller    = new SessionController();
            $activeSession = $isToday ? $controller->getActiveSession() : null;
            $isOpen        = $activeSession !== null;

            // Always check live state for session controller grid
            $liveActiveSession = $controller->getActiveSession();
            $liveIsOpen        = $liveActiveSession !== null;

            // Single query for both count and volume
            $bidQuery = Bid::where('type', 'manual_bid');
            if ($isRange) {
                $bidQuery->whereDate('bid_time', '>=', $start)->whereDate('bid_time', '<=', $end);
            } else {
                $bidQuery->whereDate('bid_time', $start);
            }
            $bidStats = $bidQuery->selectRaw('COUNT(*) as total_bids, COALESCE(SUM(amount * quantity), 0) as total_volume')
                ->first();

            $pendingSellCount = Sell::where('type', 'manual_sell')
                ->where('status', 0)->count();

            $pendingAiSellCount = Sell::where('type', 'ai_sell')
                ->where('status', 0)->count();

            // Unique bidders for range
            $bidderQuery = Bid::where('type', 'manual_bid');
            if ($isRange) {
                $bidderQuery->whereDate('bid_time', '>=', $start)->whereDate('bid_time', '<=', $end);
            } else {
                $bidderQuery->whereDate('bid_time', $start);
            }
            $uniqueBidders = $bidderQuery->distinct('user_id')->count('user_id');

            // ICTP bids for range
            $ictpQuery = Bid::where('type', 'manual_bid')->where('wallet_source', 'ictp');
            if ($isRange) {
                $ictpQuery->whereDate('bid_time', '>=', $start)->whereDate('bid_time', '<=', $end);
            } else {
                $ictpQuery->whereDate('bid_time', $start);
            }
            $ictpBids = $ictpQuery->count();

            // Win rate for range
            $winQuery = Sell::where('type', 'manual_sell')->whereIn('status', [1, 2]);
            if ($isRange) {
                $winQuery->whereDate('updated_at', '>=', $start)->whereDate('updated_at', '<=', $end);
            } else {
                $winQuery->whereDate('updated_at', $start);
            }
            $winLoss = $winQuery->selectRaw("
                    SUM(CASE WHEN status = 1 THEN 1 ELSE 0 END) as wins,
                    SUM(CASE WHEN status = 2 THEN 1 ELSE 0 END) as losses
                ")
                ->first();
            $wlTotal = (int) ($winLoss->wins ?? 0) + (int) ($winLoss->losses ?? 0);
            $winRate = $wlTotal > 0 ? round(((int) $winLoss->wins / $wlTotal) * 100, 1) : 0;

            // Always show today's sessions for the controller grid
            $sessionsForDate = NexusSession::where('session_date', Carbon::today()->toDateString())
                ->get(['id', 'session_number', 'status', 'actual_start', 'actual_end', 'total_bids']);
            $sessionsLive = $sessionsForDate->toArray();

            return [
                'date'                  => $start,
                'date_end'              => $end,
                'is_historical'         => ! $isToday,
                'is_open'               => $isOpen,
                'active_session'        => $activeSession ? [
                    'id'                => $activeSession->id,
                    'session_number'    => $activeSession->session_number,
                    'actual_start'      => $activeSession->actual_start,
                    'seconds_remaining' => $controller->getSecondsRemainingInSession(),
                ] : null,
                'next_session_time'     => ($isOpen || ! $isToday) ? null : $controller->getNextSessionTime()->toIso8601String(),
                'seconds_until_next'    => ($isOpen || ! $isToday) ? 0 : $controller->getSecondsUntilNextSession(),
                'total_bids'            => (int) $bidStats->total_bids,
                'total_bids_today'      => (int) $bidStats->total_bids, // backward compat
                'total_volume'          => round((float) $bidStats->total_volume, 2),
                'total_volume_today'    => round((float) $bidStats->total_volume, 2), // backward compat
                'unique_bidders'        => $uniqueBidders,
                'ictp_bids'             => $ictpBids,
                'pending_sell_count'    => $pendingSellCount,
                'pending_ai_sell_count' => $pendingAiSellCount,
                'win_rate'              => $winRate,
                'sessions_today'        => $sessionsForDate->toArray(),
                'sessions_live'         => $sessionsLive,
                'live_is_open'          => $liveIsOpen,
                'live_active_session'   => $liveActiveSession ? [
                    'id'                => $liveActiveSession->id,
                    'session_number'    => $liveActiveSession->session_number,
                    'actual_start'      => $liveActiveSession->actual_start,
                    'seconds_remaining' => $controller->getSecondsRemainingInSession(),
                ] : null,
            ];
        });
    }

    /**
     * Trading activity chart data.
     *
     * When showing a single day (today/yesterday): hourly granularity.
     * When showing a range (week/month): daily granularity.
     *
     * Cached 60 seconds for live, 5 minutes for historical.
     */
    public function getTradingActivityChart(?string $startDate = null, ?string $endDate = null): array
    {
        $start    = $startDate ? Carbon::parse($startDate)->startOfDay() : Carbon::now()->subHours(24);
        $end      = $endDate ? Carbon::parse($endDate)->endOfDay() : Carbon::now();
        $diffDays = $start->diffInDays($end);
        $useDaily = $diffDays > 2;

        $cacheTtl = $start->isToday() ? 60 : 300;
        $cacheKey = 'nexus.dash.activity_chart_' . $start->toDateString() . '_' . $end->toDateString();

        return Cache::remember($cacheKey, $cacheTtl, function () use ($start, $end, $useDaily) {
            if ($useDaily) {
                // Daily granularity for week/month views
                $bidCounts = Bid::where('type', 'manual_bid')
                    ->whereBetween('bid_time', [$start, $end])
                    ->selectRaw('DATE(bid_time) as d, COUNT(*) as cnt')
                    ->groupByRaw('DATE(bid_time)')
                    ->pluck('cnt', 'd');

                $sellCounts = Sell::where('type', 'manual_sell')
                    ->whereBetween('sell_time', [$start, $end])
                    ->selectRaw('DATE(sell_time) as d, COUNT(*) as cnt')
                    ->groupByRaw('DATE(sell_time)')
                    ->pluck('cnt', 'd');

                $labels   = [];
                $bidData  = [];
                $sellData = [];
                $cursor   = $start->copy();
                while ($cursor <= $end) {
                    $key        = $cursor->toDateString();
                    $labels[]   = $cursor->format('M d');
                    $bidData[]  = (int) ($bidCounts[$key] ?? 0);
                    $sellData[] = (int) ($sellCounts[$key] ?? 0);
                    $cursor->addDay();
                }
            } else {
                // Hourly granularity for single day / yesterday
                $bidCounts = Bid::where('type', 'manual_bid')
                    ->whereBetween('bid_time', [$start, $end])
                    ->selectRaw('DATE(bid_time) as d, HOUR(bid_time) as h, COUNT(*) as cnt')
                    ->groupByRaw('DATE(bid_time), HOUR(bid_time)')
                    ->get()
                    ->keyBy(fn($row) => $row->d . '_' . $row->h);

                $sellCounts = Sell::where('type', 'manual_sell')
                    ->whereBetween('sell_time', [$start, $end])
                    ->selectRaw('DATE(sell_time) as d, HOUR(sell_time) as h, COUNT(*) as cnt')
                    ->groupByRaw('DATE(sell_time), HOUR(sell_time)')
                    ->get()
                    ->keyBy(fn($row) => $row->d . '_' . $row->h);

                $labels   = [];
                $bidData  = [];
                $sellData = [];
                $hours    = (int) $start->diffInHours($end);
                for ($i = 0; $i <= $hours; $i++) {
                    $slot       = $start->copy()->addHours($i);
                    $key        = $slot->toDateString() . '_' . $slot->hour;
                    $labels[]   = $slot->format('H:i');
                    $bidData[]  = isset($bidCounts[$key]) ? (int) $bidCounts[$key]->cnt : 0;
                    $sellData[] = isset($sellCounts[$key]) ? (int) $sellCounts[$key]->cnt : 0;
                }
            }

            return [
                'labels'      => $labels,
                'granularity' => $useDaily ? 'daily' : 'hourly',
                'datasets'    => [
                    ['label' => 'Bids', 'data' => $bidData],
                    ['label' => 'Sells', 'data' => $sellData],
                ],
            ];
        });
    }

    /**
     * Session result summary: won, lost, pending counts.
     *
     * Accepts optional date. Defaults to today.
     * Cached 60 seconds for today, 5 minutes for historical.
     */
    public function getSessionResultSummary(?string $date = null): array
    {
        $targetDate = $date ? Carbon::parse($date) : Carbon::today();
        $cacheTtl   = $targetDate->isToday() ? 60 : 300;
        $cacheKey   = 'nexus.dash.result_summary_' . $targetDate->toDateString();

        return Cache::remember($cacheKey, $cacheTtl, function () use ($targetDate) {
            $counts = Sell::where('type', 'manual_sell')
                ->selectRaw("
                    SUM(CASE WHEN status = 1 AND DATE(updated_at) = ? THEN 1 ELSE 0 END) as won,
                    SUM(CASE WHEN status = 2 AND DATE(updated_at) = ? THEN 1 ELSE 0 END) as lost,
                    SUM(CASE WHEN status = 0 THEN 1 ELSE 0 END) as pending
                ", [$targetDate->toDateString(), $targetDate->toDateString()])
                ->first();

            return [
                'won'     => (int) ($counts->won ?? 0),
                'lost'    => (int) ($counts->lost ?? 0),
                'pending' => (int) ($counts->pending ?? 0),
            ];
        });
    }

    /**
     * Economy health panel data.
     * Point-in-time snapshot, no date parameter needed.
     * Cached 60 seconds.
     */
    public function getEconomyHealthPanel(): array
    {
        return Cache::remember('nexus.dash.economy_health', 60, function () {
            $guardian = new EconomyGuardian();
            $health   = $guardian->getEconomyHealth();

            return [
                'liquidity_index' => $health['liquidity_index'] ?? 0,
                'economy_health'  => $health['economy_health'] ?? 'unknown',
                'users_at_cap'    => $health['users_at_cap'] ?? 0,
                'win_rate_avg'    => $health['win_rate_avg'] ?? 0,
                'total_volume'    => $health['total_volume'] ?? 0,
            ];
        });
    }

    /**
     * Users approaching their monthly profit cap.
     * Uses nexus_user_metrics (running counter), no date param.
     * Cached 60 seconds.
     */
    public function getProfitCapWarnings(float $threshold = 70.0): array
    {
        return Cache::remember('nexus.dash.cap_warnings_' . (int) $threshold, 60, function () use ($threshold) {
            return DB::table('nexus_user_metrics as m')
                ->join('users as u', 'm.user_id', '=', 'u.id')
                ->where('m.monthly_capital', '>', 0)
                ->whereNotNull('m.monthly_profit_cap')
                ->where('m.monthly_profit_cap', '>', 0)
                ->whereRaw('(m.monthly_profit / m.monthly_profit_cap * 100) >= ?', [$threshold])
                ->selectRaw('
                    m.user_id,
                    u.username,
                    m.monthly_profit as profit,
                    m.monthly_profit_cap as cap,
                    ROUND(m.monthly_profit / m.monthly_profit_cap * 100, 1) as cap_percent
                ')
                ->orderByDesc('cap_percent')
                ->limit(50)
                ->get()
                ->map(fn($row) => [
                    'user_id'     => $row->user_id,
                    'username'    => $row->username ?? 'Unknown',
                    'profit'      => (float) $row->profit,
                    'cap'         => (float) $row->cap,
                    'cap_percent' => (float) $row->cap_percent,
                    'status'      => $row->cap_percent >= 100 ? 'capped' : 'warning',
                ])
                ->toArray();
        });
    }

    // ── PHASE 6.5 NEW METHODS ──

    /**
     * Session history: paginated list of sessions with stats.
     *
     * Returns sessions within the date range, most recent first.
     * Includes total_bids, total_sells_evaluated, approved, rejected, win rates.
     *
     * Cached 60 seconds for current month, 5 minutes for historical.
     */
    public function getSessionHistory(?string $startDate = null, ?string $endDate = null, int $limit = 30): array
    {
        $start = $startDate ? Carbon::parse($startDate)->startOfDay() : Carbon::now()->subDays(7)->startOfDay();
        $end   = $endDate ? Carbon::parse($endDate)->endOfDay() : Carbon::now()->endOfDay();

        $isCurrentMonth = $start->month === Carbon::now()->month && $start->year === Carbon::now()->year;
        $cacheTtl       = $isCurrentMonth ? 60 : 300;
        $cacheKey       = 'nexus.dash.session_history_' . $start->toDateString() . '_' . $end->toDateString() . '_' . $limit;

        return Cache::remember($cacheKey, $cacheTtl, function () use ($start, $end, $limit) {
            $sessions = NexusSession::whereBetween('session_date', [$start->toDateString(), $end->toDateString()])
                ->orderByDesc('session_date')
                ->orderByDesc('session_number')
                ->limit($limit)
                ->get([
                    'id', 'session_number', 'session_date', 'status',
                    'total_bids', 'total_bid_cost', 'total_sell_value',
                    'total_sells_evaluated', 'total_sells_approved', 'total_sells_rejected',
                    'win_rate_target', 'win_rate_actual',
                    'actual_start', 'actual_end',
                ]);

            // Get unique bidder counts per session in one query
            $sessionIds   = $sessions->pluck('id')->toArray();
            $bidderCounts = [];
            if (! empty($sessionIds)) {
                $bidderCounts = DB::table('bids')
                    ->where('type', 'manual_bid')
                    ->whereIn('nexus_session_id', $sessionIds)
                    ->select('nexus_session_id', DB::raw('COUNT(DISTINCT user_id) as unique_bidders'))
                    ->groupBy('nexus_session_id')
                    ->pluck('unique_bidders', 'nexus_session_id')
                    ->toArray();
            }

            return $sessions->map(function ($s) use ($bidderCounts) {
                return [
                    'id'               => $s->id,
                    'session_number'   => $s->session_number,
                    'session_date'     => $s->session_date,
                    'status'           => $s->status,
                    'total_bids'       => (int) $s->total_bids,
                    'total_bid_cost'   => round((float) $s->total_bid_cost, 2),
                    'total_sell_value' => round((float) $s->total_sell_value, 2),
                    'evaluated'        => (int) $s->total_sells_evaluated,
                    'approved'         => (int) $s->total_sells_approved,
                    'rejected'         => (int) $s->total_sells_rejected,
                    'win_rate_target'  => (float) $s->win_rate_target,
                    'win_rate_actual'  => (float) $s->win_rate_actual,
                    'unique_bidders'   => $bidderCounts[$s->id] ?? 0,
                    'actual_start'     => $s->actual_start,
                    'actual_end'       => $s->actual_end,
                ];
            })->toArray();
        });
    }

    /**
     * Expanded session detail for AJAX drill-down.
     *
     * Returns criteria order, rejection breakdown, and top winners.
     * Cached 5 minutes (session detail does not change after completion).
     */
    public function getSessionDetail(int $sessionId): array
    {
        return Cache::remember('nexus.dash.session_detail_' . $sessionId, 300, function () use ($sessionId) {
            $session = NexusSession::find($sessionId);
            if (! $session) {
                return ['error' => 'Session not found'];
            }

            // Criteria order from JSON column
            $criteriaOrder = $session->criteria_order;
            if (is_string($criteriaOrder)) {
                $criteriaOrder = json_decode($criteriaOrder, true) ?? [];
            }

            // Rejection breakdown from audit log
            // Rejections stored as event='sell_evaluated' with decision='rejected' in context JSON
            // Human-readable reason lives in the notes column
            $rejections = DB::table('nexus_audit_log')
                ->where('nexus_session_id', $sessionId)
                ->where('event', 'sell_evaluated')
                ->where('context', 'LIKE', '%"decision":"rejected"%')
                ->select('notes', DB::raw('COUNT(*) as cnt'))
                ->groupBy('notes')
                ->orderByDesc('cnt')
                ->get()
                ->map(fn($row) => [
                    'reason' => $row->notes ?: 'Unknown',
                    'count'  => (int) $row->cnt,
                ])
                ->toArray();

            // If no structured rejection data in audit, fall back to a simple count
            $totalRejected = (int) $session->total_sells_rejected;
            if (empty($rejections) && $totalRejected > 0) {
                $rejections = [['reason' => 'Rejection details not logged', 'count' => $totalRejected]];
            }

            // Top winners this session
            $topWinners = DB::table('sells as s')
                ->join('users as u', 's.user_id', '=', 'u.id')
                ->where('s.type', 'manual_sell')
                ->where('s.status', 1)
                ->where('s.nexus_session_id', $sessionId)
                ->where('s.initial_price', '>', 0)
                ->selectRaw('
                    s.user_id,
                    u.username,
                    s.amount as fill_price,
                    s.initial_price,
                    ROUND((s.amount - s.initial_price) / s.initial_price * 100, 2) as profit_pct
                ')
                ->orderByDesc(DB::raw('s.amount - s.initial_price'))
                ->limit(5)
                ->get()
                ->map(fn($row) => [
                    'user_id'       => $row->user_id,
                    'username'      => $row->username ?? 'Unknown',
                    'fill_price'    => round((float) $row->fill_price, 2),
                    'initial_price' => round((float) $row->initial_price, 2),
                    'profit_pct'    => (float) $row->profit_pct,
                ])
                ->toArray();

            // Unique bidders in this session
            $uniqueBidders = DB::table('bids')
                ->where('type', 'manual_bid')
                ->where('nexus_session_id', $sessionId)
                ->distinct('user_id')
                ->count('user_id');

            return [
                'session_id'       => $session->id,
                'session_number'   => $session->session_number,
                'session_date'     => $session->session_date,
                'status'           => $session->status,
                'criteria_order'   => $criteriaOrder,
                'win_rate_target'  => (float) $session->win_rate_target,
                'win_rate_actual'  => (float) $session->win_rate_actual,
                'total_bid_cost'   => round((float) $session->total_bid_cost, 2),
                'total_sell_value' => round((float) $session->total_sell_value, 2),
                'unique_bidders'   => $uniqueBidders,
                'rejections'       => $rejections,
                'top_winners'      => $topWinners,
                'session_notes'    => $session->session_notes,
            ];
        });
    }

    // ── TERMINAL METHODS ──

    /**
     * Terminal chart seed: 48 hours of hourly trade activity.
     *
     * THE LINE: dollar volume per hour (bids + sells). Goes up AND down.
     * VOLUME BARS: trade count per hour.
     * HERO VALUE: all-time cumulative bid volume (only goes up).
     * CURRENT HOUR: live per-hour volume for real-time interpolation.
     *
     * Creates a wave pattern: session peaks ($5K-$20K) + sell evaluation valleys ($50-$2K).
     * Cached 120 seconds.
     */
    public function getTerminalChartSeed(): array
    {
        return Cache::remember('nexus.terminal.chart_seed', 120, function () {
            $since = Carbon::now()->subHours(48)->startOfHour();
            $currentHourStart = Carbon::now()->startOfHour();

            // Bids per hour (all types)
            $bidHourly = Bid::where('bid_time', '>=', $since)
                ->selectRaw('DATE(bid_time) as d, HOUR(bid_time) as h, COUNT(*) as cnt, COALESCE(SUM(amount * quantity), 0) as vol')
                ->groupByRaw('DATE(bid_time), HOUR(bid_time)')
                ->get();

            // Sells evaluated per hour (all types)
            $sellHourly = Sell::whereIn('status', [1, 2])
                ->where('updated_at', '>=', $since)
                ->selectRaw('DATE(updated_at) as d, HOUR(updated_at) as h, COUNT(*) as cnt, COALESCE(SUM(amount * quantity), 0) as vol')
                ->groupByRaw('DATE(updated_at), HOUR(updated_at)')
                ->get();

            // Build lookups
            $bidMap = [];
            foreach ($bidHourly as $row) {
                $key = $row->d . ' ' . $row->h;
                $bidMap[$key] = ['cnt' => (int) $row->cnt, 'vol' => round((float) $row->vol, 2)];
            }
            $sellMap = [];
            foreach ($sellHourly as $row) {
                $key = $row->d . ' ' . $row->h;
                $sellMap[$key] = ['cnt' => (int) $row->cnt, 'vol' => round((float) $row->vol, 2)];
            }

            $labels   = [];
            $activity = [];
            $volumes  = [];

            // Walk completed hours only (current hour handled separately)
            // Volumes are PER-HOUR -- line goes up and down creating wave pattern
            // Session hours create peaks, sell evaluation hours create valleys
            $cursor = $since->copy();
            while ($cursor->lessThan($currentHourStart)) {
                $key        = $cursor->format('Y-m-d') . ' ' . $cursor->hour;
                $labels[]   = $cursor->format('M d H:00');
                $bc         = $bidMap[$key]['cnt'] ?? 0;
                $sc         = $sellMap[$key]['cnt'] ?? 0;
                $bv         = $bidMap[$key]['vol'] ?? 0;
                $sv         = $sellMap[$key]['vol'] ?? 0;
                $activity[] = $bc + $sc;
                $volumes[]  = round($bv + $sv, 2);
                $cursor->addHour();
            }

            // Current hour (partial -- per-hour volume, grows within current hour)
            $curKey          = Carbon::now()->format('Y-m-d') . ' ' . Carbon::now()->hour;
            $currentActivity = ($bidMap[$curKey]['cnt'] ?? 0) + ($sellMap[$curKey]['cnt'] ?? 0);
            $currentHourVol  = round(($bidMap[$curKey]['vol'] ?? 0) + ($sellMap[$curKey]['vol'] ?? 0), 2);

            // Cumulative bid volume since NEXUS launch (bids only, no sell double-count)
            $totalBidVol = (float) Bid::where('bid_time', '>=', '2026-03-20')
                ->sum(DB::raw('amount * quantity'));

            return [
                'labels'           => $labels,
                'activity'         => $activity,
                'volumes'          => $volumes,
                'cumulative_total' => round($totalBidVol, 2),
                'current_hour'     => [
                    'label'    => Carbon::now()->format('M d H:00'),
                    'activity' => $currentActivity,
                    'volume'   => $currentHourVol,
                ],
            ];
        });
    }

    /**
     * Terminal stats: NEXUS-specific sell counts, profit, and yesterday deltas.
     * Cached 30 seconds.
     */
    public function getTerminalStats(): array
    {
        return Cache::remember('nexus.terminal.stats', 30, function () {
            $today     = Carbon::today();
            $yesterday = Carbon::yesterday();

            // Today's NEXUS sells (nexus_session_id not null = processed by NEXUS)
            $nexusSells = Sell::where('type', 'manual_sell')
                ->whereNotNull('nexus_session_id')
                ->whereDate('updated_at', $today)
                ->selectRaw("
                    COUNT(*) as total_evaluated,
                    SUM(CASE WHEN status = 1 THEN 1 ELSE 0 END) as approved,
                    SUM(CASE WHEN status = 2 THEN 1 ELSE 0 END) as rejected,
                    COALESCE(SUM(CASE WHEN status = 1 AND initial_price > 0 THEN amount - initial_price ELSE 0 END), 0) as profit
                ")
                ->first();

            // Yesterday's platform totals for delta comparison
            $yesterdayBids = Bid::where('type', 'manual_bid')
                ->whereDate('bid_time', $yesterday)
                ->selectRaw('COUNT(*) as cnt, COALESCE(SUM(amount * quantity), 0) as vol')
                ->first();

            $yesterdaySells = Sell::where('type', 'manual_sell')
                ->whereNotNull('nexus_session_id')
                ->whereDate('updated_at', $yesterday)
                ->selectRaw("
                    COUNT(*) as total_evaluated,
                    SUM(CASE WHEN status = 1 THEN 1 ELSE 0 END) as approved,
                    SUM(CASE WHEN status = 2 THEN 1 ELSE 0 END) as rejected,
                    COALESCE(SUM(CASE WHEN status = 1 AND initial_price > 0 THEN amount - initial_price ELSE 0 END), 0) as profit
                ")
                ->first();

            // Current hour activity (fresh target for chart interpolation)
            $currentHourStart = Carbon::now()->startOfHour();
            $currentHourBids  = (int) Bid::where('bid_time', '>=', $currentHourStart)->count();
            $currentHourSells = (int) Sell::whereIn('status', [1, 2])
                ->where('updated_at', '>=', $currentHourStart)->count();
            $currentHourBidVol  = (float) Bid::where('bid_time', '>=', $currentHourStart)
                ->sum(DB::raw('amount * quantity'));
            $currentHourSellVol = (float) Sell::whereIn('status', [1, 2])
                ->where('updated_at', '>=', $currentHourStart)
                ->sum(DB::raw('amount * quantity'));

            // All-time cumulative (piggyback on chart_seed cache for efficiency)
            $seedData       = Cache::get('nexus.terminal.chart_seed');
            $cumulativeTotal = $seedData ? ($seedData['cumulative_total'] ?? 0) : 0;

            return [
                'total_sells_evaluated' => (int) ($nexusSells->total_evaluated ?? 0),
                'total_sells_approved'  => (int) ($nexusSells->approved ?? 0),
                'total_sells_rejected'  => (int) ($nexusSells->rejected ?? 0),
                'total_profit'          => round((float) ($nexusSells->profit ?? 0), 2),
                'current_hour_activity' => $currentHourBids + $currentHourSells,
                'current_hour_volume'   => round($currentHourBidVol + $currentHourSellVol, 2),
                'cumulative_total'      => round((float) $cumulativeTotal, 2),
                'deltas'                => [
                    'bids_yesterday'     => (int) ($yesterdayBids->cnt ?? 0),
                    'volume_yesterday'   => round((float) ($yesterdayBids->vol ?? 0), 2),
                    'sells_yesterday'    => (int) ($yesterdaySells->total_evaluated ?? 0),
                    'approved_yesterday' => (int) ($yesterdaySells->approved ?? 0),
                    'rejected_yesterday' => (int) ($yesterdaySells->rejected ?? 0),
                    'profit_yesterday'   => round((float) ($yesterdaySells->profit ?? 0), 2),
                ],
            ];
        });
    }
}