<?php

namespace App\Nexus\Analytics;

use App\Models\Investment;
use App\Models\Plan;
use Carbon\Carbon;
use Illuminate\Support\Facades\Cache;
use Illuminate\Support\Facades\DB;

/**
 * NEXUS Analytics - BotAnalyticsService
 *
 * Phase 8 v3: Date range support on all methods, AJAX pagination,
 * historical settled bots archive.
 *
 * Current month: shows active bots (status=2)
 * Historical months: shows bots that settled during that period (status=3)
 *   plus any still-active bots that overlap the period
 *
 * @package App\Nexus\Analytics
 */
class BotAnalyticsService
{
    /**
     * Dashboard glance module. Always current data.
     * Cached 60 seconds.
     */
    public function getGlanceSummary(): array
    {
        return Cache::remember('nexus.bots.glance', 60, function () {
            $bots = $this->getActiveBots();
            $totalBots = count($bots);
            $totalCapital = 0; $totalProfit = 0;
            $onTrack = 0; $hot = 0; $cold = 0;
            $planCounts = [];

            foreach ($bots as $b) {
                $totalCapital += $b['capital'];
                $totalProfit += $b['current_profit'];
                if ($b['track_status'] === 'ON_TRACK') $onTrack++;
                elseif ($b['track_status'] === 'HOT') $hot++;
                else $cold++;
                $plan = $b['plan_name'];
                if (!isset($planCounts[$plan])) $planCounts[$plan] = 0;
                $planCounts[$plan]++;
            }

            return [
                'total_bots'    => $totalBots,
                'total_capital' => round($totalCapital, 2),
                'total_profit'  => round($totalProfit, 2),
                'on_track'      => $onTrack,
                'hot'           => $hot,
                'cold'          => $cold,
                'plan_counts'   => $planCounts,
            ];
        });
    }

    /**
     * Full overview with per-plan summaries.
     * Date-aware: current month shows active bots, historical shows settled + overlap.
     * Cached 60s current, 5min historical.
     */
    public function getFullOverview(?string $startDate = null, ?string $endDate = null): array
    {
        $start = $startDate ? Carbon::parse($startDate) : Carbon::now()->startOfMonth();
        $end = $endDate ? Carbon::parse($endDate) : Carbon::now();
        $isCurrent = $this->isCurrentPeriod($start);
        $cacheTtl = $isCurrent ? 60 : 300;
        $cacheKey = 'nexus.bots.overview_' . $start->toDateString() . '_' . $end->toDateString();

        return Cache::remember($cacheKey, $cacheTtl, function () use ($start, $end, $isCurrent) {
            $bots = $isCurrent
                ? $this->getActiveBots()
                : $this->getHistoricalBots($start, $end);

            $plans = [];
            foreach ($bots as $b) {
                $planId = $b['plan_id'];
                if (!isset($plans[$planId])) {
                    $plans[$planId] = [
                        'plan_id'           => $planId,
                        'plan_name'         => $b['plan_name'],
                        'nexus_monthly_min' => $b['nexus_monthly_min'],
                        'nexus_monthly_max' => $b['nexus_monthly_max'],
                        'nexus_daily_min'   => $b['nexus_daily_min'] ?? 0,
                        'nexus_daily_max'   => $b['nexus_daily_max'] ?? 0,
                        'success_rate_min'  => $b['success_rate_min'] ?? 85,
                        'success_rate_max'  => $b['success_rate_max'] ?? 99,
                        'bots'              => [],
                        'total_capital'     => 0,
                        'total_profit'      => 0,
                        'on_track'          => 0,
                        'hot'               => 0,
                        'cold'              => 0,
                        'settled'           => 0,
                    ];
                }
                $plans[$planId]['bots'][] = $b;
                $plans[$planId]['total_capital'] += $b['capital'];
                $plans[$planId]['total_profit'] += $b['current_profit'];

                $ts = $b['track_status'] ?? '';
                if ($ts === 'ON_TRACK') $plans[$planId]['on_track']++;
                elseif ($ts === 'HOT') $plans[$planId]['hot']++;
                elseif ($ts === 'COLD') $plans[$planId]['cold']++;
                if ($b['is_settled'] ?? false) $plans[$planId]['settled']++;
            }

            foreach ($plans as &$p) {
                $p['total_capital'] = round($p['total_capital'], 2);
                $p['total_profit'] = round($p['total_profit'], 2);
                $p['avg_profit_pct'] = $p['total_capital'] > 0
                    ? round(($p['total_profit'] / $p['total_capital']) * 100, 2) : 0;
                $p['bot_count'] = count($p['bots']);
            }
            unset($p);
            ksort($plans);

            $totalBots = count($bots);
            return [
                'totals' => [
                    'bots'     => $totalBots,
                    'capital'  => round(array_sum(array_column($plans, 'total_capital')), 2),
                    'profit'   => round(array_sum(array_column($plans, 'total_profit')), 2),
                    'on_track' => array_sum(array_column($plans, 'on_track')),
                    'hot'      => array_sum(array_column($plans, 'hot')),
                    'cold'     => array_sum(array_column($plans, 'cold')),
                    'settled'  => array_sum(array_column($plans, 'settled')),
                ],
                'plans'       => array_values($plans),
                'is_current'  => $this->isCurrentPeriod(Carbon::parse($plans ? array_values($plans)[0]['plan_id'] : null) ?: Carbon::now()),
            ];
        });
    }

    /**
     * AJAX paginated bot table for a single plan.
     * Date-aware: current shows active, historical shows settled + overlap.
     * NOT cached (pagination params change constantly).
     */
    public function getBotsTable(
        int $planId,
        ?string $startDate = null,
        ?string $endDate = null,
        string $sort = 'capital',
        string $dir = 'desc',
        ?string $search = null,
        int $limit = 20,
        int $offset = 0
    ): array {
        $start = $startDate ? Carbon::parse($startDate) : Carbon::now()->startOfMonth();
        $end = $endDate ? Carbon::parse($endDate) : Carbon::now();
        $isCurrent = $this->isCurrentPeriod($start);

        $query = DB::table('investments as i')
            ->join('plans as p', 'i.plan_id', '=', 'p.id')
            ->join('users as u', 'i.user_id', '=', 'u.id')
            ->where('i.plan_id', $planId)
            ->whereNotNull('p.nexus_monthly_min');

        if ($isCurrent) {
            // Active bots only
            $query->where('i.status', 2)
                ->where('i.funded', 1)
                ->where('i.is_paused', 0)
                ->where('i.is_settled', 0);
        } else {
            // Bots that were alive during the period
            $query->where(function ($q) use ($start, $end) {
                // Active bots that overlap the period
                $q->where(function ($q2) use ($start, $end) {
                    $q2->where('i.status', 2)
                        ->where('i.buy_date', '<=', $end->endOfDay())
                        ->where('i.end_date', '>=', $start->startOfDay());
                })
                // Settled bots that settled during the period
                ->orWhere(function ($q2) use ($start, $end) {
                    $q2->where('i.status', 3)
                        ->where('i.is_settled', 1)
                        ->whereBetween('i.updated_at', [$start->startOfDay(), $end->endOfDay()]);
                });
            });
        }

        if ($search) {
            $query->where(function ($q) use ($search) {
                $q->where('u.username', 'LIKE', '%' . $search . '%')
                  ->orWhere('i.id', $search);
            });
        }

        // Count before pagination
        $total = $query->count();

        // Sort mapping
        $sortMap = [
            'bot_id'          => 'i.id',
            'username'        => 'u.username',
            'capital'         => 'i.amount',
            'current_profit'  => 'i.pfit',
            'profit_pct'      => DB::raw('(i.pfit / i.amount)'),
            'drift'           => DB::raw('(i.pfit - (((p.nexus_monthly_min + ((LEAST(GREATEST((i.amount - p.min_fund) / GREATEST(p.max_fund - p.min_fund, 1), 0), 1)) * (p.nexus_monthly_max - p.nexus_monthly_min))) / 100) * i.amount * (DATEDIFF(NOW(), i.buy_date) / GREATEST(DATEDIFF(i.end_date, i.buy_date), 1))))'),
            'days_remaining'  => DB::raw('DATEDIFF(i.end_date, NOW())'),
            'track_status'    => DB::raw('(i.pfit / i.amount)'), // approximate: sort by profit % as proxy
        ];

        $sortCol = $sortMap[$sort] ?? 'i.amount';
        $sortDir = strtolower($dir) === 'asc' ? 'asc' : 'desc';

        $rows = $query->select([
                'i.id as bot_id', 'i.user_id', 'u.username', 'i.plan_id',
                'p.name as plan_name', 'i.amount', 'i.balance', 'i.pfit',
                'i.f_pfit', 'i.trade_min', 'i.trade_max', 'i.buy_date',
                'i.end_date', 'i.status as bot_status', 'i.is_settled',
                'p.min_fund', 'p.max_fund',
                'p.nexus_monthly_min', 'p.nexus_monthly_max',
                'p.nexus_daily_min', 'p.nexus_daily_max',
                'p.nexus_success_rate_min', 'p.nexus_success_rate_max',
            ])
            ->orderBy($sortCol, $sortDir)
            ->offset($offset)
            ->limit($limit)
            ->get();

        $data = [];
        $now = Carbon::now();
        foreach ($rows as $row) {
            $data[] = $this->buildBotRecord($row, $now);
        }

        return [
            'data'  => $data,
            'total' => $total,
            'pages' => max(1, (int) ceil($total / $limit)),
            'page'  => (int) floor($offset / $limit) + 1,
            'limit' => $limit,
        ];
    }

    /**
     * Alerts: at-risk bots, f_pfit overrides, unhealthy plans.
     * Always current (alerts are not historical). Cached 60s.
     */
    public function getAlerts(): array
    {
        return Cache::remember('nexus.bots.alerts', 60, function () {
            $bots = $this->getActiveBots();
            $alerts = [];
            $planColdCounts = [];
            $planTotals = [];

            foreach ($bots as $b) {
                $plan = $b['plan_name'];
                if (!isset($planTotals[$plan])) { $planTotals[$plan] = 0; $planColdCounts[$plan] = 0; }
                $planTotals[$plan]++;
                if ($b['track_status'] === 'COLD') $planColdCounts[$plan]++;

                if ($b['track_status'] === 'COLD' && $b['days_remaining'] <= 5) {
                    $alerts[] = [
                        'type' => 'at_risk', 'level' => $b['days_remaining'] <= 2 ? 'critical' : 'warning',
                        'bot_id' => $b['bot_id'], 'user' => $b['username'], 'plan' => $plan,
                        'message' => 'Bot #' . $b['bot_id'] . ' (' . $b['username'] . ') -- COLD with ' . $b['days_remaining'] . 'd remaining. Drift: $' . number_format(abs($b['drift']), 2) . ' behind.',
                    ];
                }

                if ($b['has_f_pfit']) {
                    $alerts[] = [
                        'type' => 'f_pfit', 'level' => 'info',
                        'bot_id' => $b['bot_id'], 'user' => $b['username'], 'plan' => $plan,
                        'message' => 'Bot #' . $b['bot_id'] . ' (' . $b['username'] . ') -- admin f_pfit override active ($' . number_format($b['f_pfit'], 2) . ').',
                    ];
                }
            }

            foreach ($planTotals as $plan => $total) {
                if ($total > 0) {
                    $coldPct = ($planColdCounts[$plan] / $total) * 100;
                    if ($coldPct > 30) {
                        $alerts[] = [
                            'type' => 'plan_health', 'level' => $coldPct > 60 ? 'critical' : 'warning',
                            'plan' => $plan,
                            'message' => $plan . ' -- ' . round($coldPct, 0) . '% of bots are COLD (' . $planColdCounts[$plan] . '/' . $total . ').',
                        ];
                    }
                }
            }

            usort($alerts, function ($a, $b) {
                $order = ['critical' => 0, 'warning' => 1, 'info' => 2];
                return ($order[$a['level']] ?? 3) - ($order[$b['level']] ?? 3);
            });

            return $alerts;
        });
    }

    /**
     * Settlement pipeline. Date-aware.
     * Current: expiring soon + settled this month.
     * Historical: settled during selected period.
     * Cached 60s current, 5min historical.
     */
    public function getSettlementPipeline(?string $startDate = null, ?string $endDate = null): array
    {
        $start = $startDate ? Carbon::parse($startDate) : Carbon::now()->startOfMonth();
        $end = $endDate ? Carbon::parse($endDate) : Carbon::now();
        $isCurrent = $this->isCurrentPeriod($start);
        $cacheTtl = $isCurrent ? 60 : 300;
        $cacheKey = 'nexus.bots.settlement_' . $start->toDateString() . '_' . $end->toDateString();

        return Cache::remember($cacheKey, $cacheTtl, function () use ($start, $end, $isCurrent) {
            $now = Carbon::now();
            $expiringSoon = [];
            $expiringAtRisk = 0;

            // Expiring soon: only for current view
            if ($isCurrent) {
                $weekFromNow = $now->copy()->addDays(7);
                $expiringSoon = DB::table('investments as i')
                    ->join('plans as p', 'i.plan_id', '=', 'p.id')
                    ->join('users as u', 'i.user_id', '=', 'u.id')
                    ->where('i.status', 2)->where('i.funded', 1)
                    ->where('i.is_paused', 0)->where('i.is_settled', 0)
                    ->where('i.end_date', '<=', $weekFromNow)->where('i.end_date', '>', $now)
                    ->whereNotNull('p.nexus_monthly_min')
                    ->select(['i.id as bot_id', 'u.username', 'p.name as plan_name',
                        'i.amount', 'i.pfit', 'i.f_pfit', 'i.end_date',
                        'p.nexus_monthly_min', 'p.nexus_monthly_max', 'p.min_fund', 'p.max_fund'])
                    ->orderBy('i.end_date')
                    ->get()
                    ->map(function ($row) use ($now) {
                        $amount = (float) $row->amount;
                        $pfit = (float) $row->pfit;
                        $daysLeft = max(1, Carbon::parse($row->end_date)->diffInDays($now));
                        $ratio = $this->planCapitalRatio($amount, (float) $row->min_fund, (float) $row->max_fund);
                        $targetPct = (float) $row->nexus_monthly_min + ($ratio * ((float) $row->nexus_monthly_max - (float) $row->nexus_monthly_min));
                        $profitPct = $amount > 0 ? ($pfit / $amount) * 100 : 0;
                        $atRisk = $profitPct < (float) $row->nexus_monthly_min;
                        return [
                            'bot_id' => $row->bot_id, 'username' => $row->username, 'plan' => $row->plan_name,
                            'capital' => $amount, 'profit' => round($pfit, 2), 'profit_pct' => round($profitPct, 2),
                            'target_pct' => round($targetPct, 2), 'days_left' => $daysLeft,
                            'end_date' => $row->end_date, 'at_risk' => $atRisk, 'has_f_pfit' => (float) $row->f_pfit > 0,
                        ];
                    })->toArray();
                $expiringAtRisk = count(array_filter($expiringSoon, fn($b) => $b['at_risk']));
            }

            // Settled during the period
            $settledQuery = DB::table('investments as i')
                ->join('plans as p', 'i.plan_id', '=', 'p.id')
                ->join('users as u', 'i.user_id', '=', 'u.id')
                ->where('i.status', 3)->where('i.is_settled', 1)
                ->whereBetween('i.updated_at', [$start->startOfDay(), $end->endOfDay()])
                ->whereNotNull('p.nexus_monthly_min')
                ->select(['i.id as bot_id', 'u.username', 'p.name as plan_name',
                    'i.amount', 'i.pfit', 'i.f_pfit',
                    'p.nexus_monthly_min', 'p.nexus_monthly_max',
                    'i.updated_at as settled_at'])
                ->orderByDesc('i.updated_at')
                ->limit(100)
                ->get()
                ->map(function ($row) {
                    $amount = (float) $row->amount;
                    $settledProfit = (float) $row->f_pfit > 0 ? (float) $row->f_pfit : (float) $row->pfit;
                    $profitPct = $amount > 0 ? ($settledProfit / $amount) * 100 : 0;
                    $inRange = $profitPct >= (float) $row->nexus_monthly_min && $profitPct <= (float) $row->nexus_monthly_max;
                    return [
                        'bot_id' => $row->bot_id, 'username' => $row->username, 'plan' => $row->plan_name,
                        'capital' => $amount, 'profit' => round($settledProfit, 2), 'profit_pct' => round($profitPct, 2),
                        'target_min' => (float) $row->nexus_monthly_min, 'target_max' => (float) $row->nexus_monthly_max,
                        'in_range' => $inRange, 'used_f_pfit' => (float) $row->f_pfit > 0,
                        'settled_at' => $row->settled_at,
                    ];
                })->toArray();

            $settledCount = count($settledQuery);
            $inRangeCount = count(array_filter($settledQuery, fn($b) => $b['in_range']));

            return [
                'expiring_soon'       => $expiringSoon,
                'expiring_count'      => count($expiringSoon),
                'expiring_at_risk'    => $expiringAtRisk,
                'settled'             => $settledQuery,
                'settled_count'       => $settledCount,
                'settlement_accuracy' => $settledCount > 0 ? round(($inRangeCount / $settledCount) * 100, 1) : 0,
                'in_range_count'      => $inRangeCount,
                'is_current'          => $isCurrent,
            ];
        });
    }

    /**
     * Chart data. Date-aware.
     * Cached 60s current, 5min historical.
     */
    public function getChartData(?string $startDate = null, ?string $endDate = null): array
    {
        $start = $startDate ? Carbon::parse($startDate) : Carbon::now()->startOfMonth();
        $end = $endDate ? Carbon::parse($endDate) : Carbon::now();
        $isCurrent = $this->isCurrentPeriod($start);
        $cacheTtl = $isCurrent ? 60 : 300;
        $cacheKey = 'nexus.bots.charts_' . $start->toDateString() . '_' . $end->toDateString();

        return Cache::remember($cacheKey, $cacheTtl, function () use ($start, $end, $isCurrent) {
            $bots = $isCurrent
                ? $this->getActiveBots()
                : $this->getHistoricalBots($start, $end);

            $capitalByPlan = [];
            $trackCounts = ['ON_TRACK' => 0, 'HOT' => 0, 'COLD' => 0, 'SETTLED' => 0];
            $profitByPlan = [];

            foreach ($bots as $b) {
                $plan = $b['plan_name'];
                if (!isset($capitalByPlan[$plan])) $capitalByPlan[$plan] = 0;
                $capitalByPlan[$plan] += $b['capital'];

                $ts = $b['track_status'] ?? '';
                if (isset($trackCounts[$ts])) $trackCounts[$ts]++;

                if (!isset($profitByPlan[$plan])) {
                    $profitByPlan[$plan] = [
                        'total_profit' => 0, 'total_capital' => 0,
                        'target_min' => $b['nexus_monthly_min'], 'target_max' => $b['nexus_monthly_max'],
                    ];
                }
                $profitByPlan[$plan]['total_profit'] += $b['current_profit'];
                $profitByPlan[$plan]['total_capital'] += $b['capital'];
            }

            $profitVsTarget = [];
            foreach ($profitByPlan as $plan => $data) {
                $profitVsTarget[] = [
                    'plan' => $plan,
                    'avg_profit' => $data['total_capital'] > 0 ? round(($data['total_profit'] / $data['total_capital']) * 100, 2) : 0,
                    'target_min' => $data['target_min'],
                    'target_max' => $data['target_max'],
                ];
            }

            return [
                'capital_by_plan'  => $capitalByPlan,
                'track_counts'     => $trackCounts,
                'profit_vs_target' => $profitVsTarget,
                'total_capital'    => array_sum($capitalByPlan),
            ];
        });
    }

    /**
     * Flat list for CSV export.
     */
    public function getBotsForExport(?int $planId = null): array
    {
        $bots = $this->getActiveBots();
        if ($planId) {
            $bots = array_filter($bots, fn($b) => $b['plan_id'] == $planId);
            $bots = array_values($bots);
        }
        return $bots;
    }

    /**
     * Available months for the bot date selector.
     * Looks at both active bot buy_dates and settled bot updated_at dates.
     * Cached 5 minutes.
     */
    public function getAvailableMonths(): array
    {
        return Cache::remember('nexus.bots.available_months', 300, function () {
            // Earliest active bot
            $earliestActive = DB::table('investments')
                ->where('status', 2)->whereNotNull('buy_date')
                ->min('buy_date');

            // Earliest settled bot
            $earliestSettled = DB::table('investments')
                ->where('status', 3)->where('is_settled', 1)
                ->min('updated_at');

            $earliest = $earliestActive;
            if ($earliestSettled && (!$earliest || $earliestSettled < $earliest)) {
                $earliest = $earliestSettled;
            }

            if (!$earliest) return [Carbon::now()->format('Y-m')];

            $months = [];
            $cursor = Carbon::parse($earliest)->startOfMonth();
            $now = Carbon::now()->startOfMonth();

            while ($cursor <= $now) {
                $months[] = $cursor->format('Y-m');
                $cursor->addMonth();
            }

            return array_reverse($months);
        });
    }

    // ── PRIVATE: DATA RETRIEVAL ──

    /**
     * Active bots (current). Status=2, funded, not paused, not settled.
     */
    private function getActiveBots(): array
    {
        $rows = DB::table('investments as i')
            ->join('plans as p', 'i.plan_id', '=', 'p.id')
            ->join('users as u', 'i.user_id', '=', 'u.id')
            ->where('i.status', 2)->where('i.funded', 1)
            ->where('i.is_paused', 0)->where('i.is_settled', 0)
            ->whereNotNull('p.nexus_monthly_min')->whereNotNull('p.nexus_monthly_max')
            ->select($this->botSelectColumns())
            ->orderBy('p.id')->orderByDesc('i.amount')
            ->get();

        $result = [];
        $now = Carbon::now();
        foreach ($rows as $row) {
            if ($now->greaterThanOrEqualTo(Carbon::parse($row->end_date))) continue;
            $result[] = $this->buildBotRecord($row, $now);
        }
        return $result;
    }

    /**
     * Historical bots: active during the period OR settled during the period.
     */
    private function getHistoricalBots(Carbon $start, Carbon $end): array
    {
        $rows = DB::table('investments as i')
            ->join('plans as p', 'i.plan_id', '=', 'p.id')
            ->join('users as u', 'i.user_id', '=', 'u.id')
            ->whereNotNull('p.nexus_monthly_min')
            ->where(function ($q) use ($start, $end) {
                // Active bots overlapping the period
                $q->where(function ($q2) use ($start, $end) {
                    $q2->where('i.status', 2)
                        ->where('i.buy_date', '<=', $end->endOfDay())
                        ->where('i.end_date', '>=', $start->startOfDay());
                })
                // Settled bots during the period
                ->orWhere(function ($q2) use ($start, $end) {
                    $q2->where('i.status', 3)->where('i.is_settled', 1)
                        ->whereBetween('i.updated_at', [$start->startOfDay(), $end->endOfDay()]);
                });
            })
            ->select($this->botSelectColumns())
            ->orderBy('p.id')->orderByDesc('i.amount')
            ->get();

        $result = [];
        $now = Carbon::now();
        foreach ($rows as $row) {
            $result[] = $this->buildBotRecord($row, $now);
        }
        return $result;
    }

    /**
     * Shared SELECT columns for bot queries.
     */
    private function botSelectColumns(): array
    {
        return [
            'i.id as bot_id', 'i.user_id', 'u.username', 'i.plan_id',
            'p.name as plan_name', 'i.amount', 'i.balance', 'i.pfit', 'i.f_pfit',
            'i.trade_min', 'i.trade_max', 'i.buy_date', 'i.end_date',
            'i.status as bot_status', 'i.is_settled',
            'p.min_fund', 'p.max_fund',
            'p.nexus_monthly_min', 'p.nexus_monthly_max',
            'p.nexus_daily_min', 'p.nexus_daily_max',
            'p.nexus_success_rate_min', 'p.nexus_success_rate_max',
        ];
    }

    /**
     * Build a structured bot record from a DB row.
     * Works for both active and settled bots.
     */
    private function buildBotRecord(object $row, Carbon $now): array
    {
        $amount = (float) $row->amount;
        $pfit = (float) $row->pfit;
        $isSettled = (int) ($row->is_settled ?? 0) === 1;
        $botStatus = (int) ($row->bot_status ?? 2);

        // For settled bots, use f_pfit if set
        if ($isSettled && (float) $row->f_pfit > 0) {
            $displayProfit = (float) $row->f_pfit;
        } else {
            $displayProfit = $pfit;
        }

        $buyDate = Carbon::parse($row->buy_date);
        $endDate = Carbon::parse($row->end_date);
        $daysElapsed = max(0, $buyDate->diffInDays($now));
        $daysTotal = max(1, $buyDate->diffInDays($endDate));
        $daysRemaining = max(0, (int) $now->diffInDays($endDate, false));
        if ($now->greaterThanOrEqualTo($endDate)) $daysRemaining = 0;

        $ratio = $this->planCapitalRatio($amount, (float) $row->min_fund, (float) $row->max_fund);
        $monthlyMin = (float) $row->nexus_monthly_min;
        $monthlyMax = (float) $row->nexus_monthly_max;
        $targetPct = $monthlyMin + ($ratio * ($monthlyMax - $monthlyMin));
        $targetProfit = ($targetPct / 100) * $amount;
        $expectedByNow = $daysTotal > 0 ? $targetProfit * (min($daysElapsed, $daysTotal) / $daysTotal) : 0;
        $drift = $displayProfit - $expectedByNow;
        $driftThreshold = $targetProfit * 0.10;

        if ($isSettled) {
            // Settled bot: check if final profit is in range
            $profitPct = $amount > 0 ? ($displayProfit / $amount) * 100 : 0;
            $trackStatus = ($profitPct >= $monthlyMin && $profitPct <= $monthlyMax) ? 'SETTLED' : 'OUT_OF_RANGE';
        } elseif ($drift > $driftThreshold) {
            $trackStatus = 'HOT';
        } elseif ($drift < -$driftThreshold) {
            $trackStatus = 'COLD';
        } else {
            $trackStatus = 'ON_TRACK';
        }

        $profitOfTarget = $targetProfit > 0 ? round(($displayProfit / $targetProfit) * 100, 1) : 0;
        $globalRatio = $this->globalCapitalRatio($amount);
        $successMax = (float) ($row->nexus_success_rate_max ?? 99);
        $successMin = (float) ($row->nexus_success_rate_min ?? 85);
        $baseSuccess = $successMax - ($globalRatio * ($successMax - $successMin));

        return [
            'bot_id'            => $row->bot_id,
            'user_id'           => $row->user_id,
            'username'          => $row->username ?? 'Unknown',
            'plan_id'           => $row->plan_id,
            'plan_name'         => $row->plan_name,
            'capital'           => $amount,
            'balance'           => (float) $row->balance,
            'current_profit'    => round($displayProfit, 2),
            'profit_pct'        => $amount > 0 ? round(($displayProfit / $amount) * 100, 2) : 0,
            'target_pct'        => round($targetPct, 2),
            'target_usd'        => round($targetProfit, 2),
            'profit_of_target'  => $profitOfTarget,
            'expected_by_now'   => round($expectedByNow, 2),
            'drift'             => round($drift, 2),
            'track_status'      => $trackStatus,
            'days_elapsed'      => min($daysElapsed, $daysTotal),
            'days_remaining'    => $daysRemaining,
            'days_total'        => $daysTotal,
            'progress_pct'      => $daysTotal > 0 ? round((min($daysElapsed, $daysTotal) / $daysTotal) * 100, 1) : 0,
            'trade_min'         => (float) $row->trade_min,
            'trade_max'         => (float) $row->trade_max,
            'global_cap_ratio'  => round($globalRatio, 3),
            'base_success'      => round($baseSuccess, 1),
            'nexus_monthly_min' => $monthlyMin,
            'nexus_monthly_max' => $monthlyMax,
            'nexus_daily_min'   => (float) ($row->nexus_daily_min ?? 0),
            'nexus_daily_max'   => (float) ($row->nexus_daily_max ?? 0),
            'success_rate_min'  => (float) ($row->nexus_success_rate_min ?? 85),
            'success_rate_max'  => (float) ($row->nexus_success_rate_max ?? 99),
            'buy_date'          => $row->buy_date,
            'end_date'          => $row->end_date,
            'f_pfit'            => (float) $row->f_pfit,
            'has_f_pfit'        => (float) $row->f_pfit > 0,
            'is_settled'        => $isSettled,
            'bot_status'        => $botStatus,
        ];
    }

    // ── PRIVATE: HELPERS ──

    private function isCurrentPeriod(Carbon $start): bool
    {
        return $start->month === Carbon::now()->month && $start->year === Carbon::now()->year;
    }

    private function planCapitalRatio(float $amount, float $minFund, float $maxFund): float
    {
        $range = $maxFund - $minFund;
        return $range > 0 ? max(0, min(1, ($amount - $minFund) / $range)) : 0.5;
    }

    private function globalCapitalRatio(float $capital): float
    {
        $floor = 50.0; $ceiling = 1000000.0;
        $capital = max($floor, min($ceiling, $capital));
        $logFloor = log10($floor); $logCeiling = log10($ceiling);
        $range = $logCeiling - $logFloor;
        return $range > 0 ? (log10($capital) - $logFloor) / $range : 0.5;
    }

    // ── TERMINAL: USER-FACING PLAN SHOWCASE ──

    /**
     * Aggregated plan data for user-facing terminal bot cards.
     * Headline numbers (capital, profit, bots) are REAL.
     * Sparkline is generated from plan ranges (Option E).
     * Cached 60 seconds.
     */
    public function getPlanShowcase(): array
    {
        return Cache::remember('nexus.terminal.plan_showcase', 60, function () {
            $bots = $this->getActiveBots();
            $plans = Plan::whereNotNull('nexus_monthly_min')->get();
            $planMap = $plans->keyBy('id');

            // Aggregate bot data per plan
            $planData = [];
            foreach ($bots as $b) {
                $pid = $b['plan_id'];
                if (!isset($planData[$pid])) {
                    $plan = $planMap[$pid] ?? null;
                    if (!$plan) continue;
                    $planData[$pid] = [
                        'name'              => $plan->name,
                        'price'             => '$' . number_format((float) $plan->price, 0),
                        'monthly_range'     => ((float) $plan->nexus_monthly_min) . '% - ' . ((float) $plan->nexus_monthly_max) . '%',
                        'capital_managed'   => 0.0,
                        'profit_this_month' => 0.0,
                        'active_bots'       => 0,
                        'sparkline'         => [],
                        'sort_order'        => (float) $plan->price,
                    ];
                }
                $planData[$pid]['capital_managed'] += $b['capital'];
                $planData[$pid]['profit_this_month'] += $b['current_profit'];
                $planData[$pid]['active_bots']++;
            }

            // Build result with sparklines and avg return
            $result = [];
            foreach ($planData as $pid => $pd) {
                $plan = $planMap[$pid] ?? null;
                $pd['capital_managed'] = round($pd['capital_managed'], 2);
                $pd['profit_this_month'] = round($pd['profit_this_month'], 2);
                $pd['avg_return_pct'] = $pd['capital_managed'] > 0
                    ? round(($pd['profit_this_month'] / $pd['capital_managed']) * 100, 1)
                    : 0.0;

                // Option E sparkline: generated from plan daily ranges
                $pd['sparkline'] = $plan
                    ? $this->generatePlanSparkline($plan)
                    : array_fill(0, 30, 0);

                unset($pd['sort_order']);
                $result[] = $pd;
            }

            // Sort by plan price ascending (DS Amazon -> Host AI)
            usort($result, function ($a, $b) {
                return (float) str_replace(['$', ',', '/mo'], '', $a['price'])
                   <=> (float) str_replace(['$', ',', '/mo'], '', $b['price']);
            });

            return $result;
        });
    }

    /**
     * Generate a 30-point sparkline from plan daily ranges.
     * Deterministic per plan per month (consistent on refresh).
     * Some days near-zero for organic appearance.
     *
     * @param Plan $plan
     * @return array 30 float values (cumulative % return)
     */
    private function generatePlanSparkline(Plan $plan): array
    {
        $seed = crc32($plan->id . Carbon::now()->format('Y-m'));
        mt_srand($seed);

        $dailyMin = (float) $plan->nexus_daily_min;
        $dailyMax = (float) $plan->nexus_daily_max;
        $points = [];
        $cumulative = 0.0;

        for ($day = 0; $day < 30; $day++) {
            $dailyPct = $dailyMin + (mt_rand(0, 10000) / 10000) * ($dailyMax - $dailyMin);
            // 15% chance of a near-zero day (organic look)
            if (mt_rand(0, 100) < 15) {
                $dailyPct *= 0.1;
            }
            // 5% chance of a slightly negative day (realistic)
            if (mt_rand(0, 100) < 5) {
                $dailyPct *= -0.3;
            }
            $cumulative += $dailyPct;
            $cumulative = max(0, $cumulative); // never go below zero
            $points[] = round($cumulative, 2);
        }

        mt_srand(); // reset global seed
        return $points;
    }
}
