<?php
namespace App\Nexus\Analytics;

use App\Models\Bid;
use App\Models\Sell;
use App\Models\User;
use Carbon\Carbon;
use Illuminate\Support\Facades\Cache;
use Illuminate\Support\Facades\DB;

/**
 * NEXUS Analytics - UserMetricsService
 *
 * User-level analytics for the Users dashboard page.
 * Read-only. Returns structured arrays.
 *
 * PHASE 6 PERFORMANCE (March 9, 2026):
 * - getTopBidders: 61 queries down to 1 (JOIN + subquery)
 * - All methods cached 60 seconds
 *
 * PHASE 6.5 ADDITIONS:
 * - getTopBidders: accepts sort, search, pagination, date range
 * - getWinLossRatio: accepts date range
 * - getUserParticipation: platform participation metrics
 * - getDailyActiveBidders: unique bidders per day for charting
 * - getWinRateDistribution: histogram of user win rates
 * - getFirstTimeStats: first-time bid/sell guarantee usage
 * - All historical queries use sells/bids tables, NOT nexus_user_metrics
 *
 * @package App\Nexus\Analytics
 */
class UserMetricsService
{
    /**
     * Top bidders with full sort, search, pagination, and date range.
     *
     * PHASE 6.5: Complete rewrite for AJAX table support.
     * Queries source tables (sells, bids) for historical accuracy.
     * Returns paginated result with total count for pagination controls.
     *
     * Cache key includes all params. TTL: 30s for interactive, 300s for historical.
     */
    public function getTopBidders(
        int $limit = 20,
        ?string $startDate = null,
        ?string $endDate = null,
        string $sortBy = 'wins',
        string $sortDir = 'desc',
        ?string $search = null,
        int $offset = 0
    ): array {
        $start = $startDate ? Carbon::parse($startDate)->startOfDay() : Carbon::now()->startOfMonth();
        $end   = $endDate ? Carbon::parse($endDate)->endOfDay() : Carbon::now()->endOfDay();

        $isCurrentMonth = $start->month === Carbon::now()->month && $start->year === Carbon::now()->year;
        $cacheTtl       = ($search || $offset > 0) ? 30 : ($isCurrentMonth ? 60 : 300);

        // Cache key must include all params
        $cacheKey = 'nexus.users.top_bidders_' . md5(
            $limit . $start->toDateString() . $end->toDateString() .
            $sortBy . $sortDir . ($search ?? '') . $offset
        );

        return Cache::remember($cacheKey, $cacheTtl, function () use ($limit, $start, $end, $sortBy, $sortDir, $search, $offset) {
            // Validate sort column against whitelist
            $allowedSorts = ['username', 'wins', 'losses', 'win_rate', 'total_profit', 'total_volume', 'profit_percent'];
            if (! in_array($sortBy, $allowedSorts)) {
                $sortBy = 'wins';
            }

            $sortDir = strtolower($sortDir) === 'asc' ? 'asc' : 'desc';

            // Loss counts subquery -- scoped to date range
            $lossSubquery = DB::table('sells')
                ->where('type', 'manual_sell')
                ->where('status', 2)
                ->whereBetween('updated_at', [$start, $end])
                ->select('user_id', DB::raw('COUNT(*) as loss_count'))
                ->groupBy('user_id');

            // Bid counts subquery -- total bids + ICTP bids in one pass
            $bidSubquery = DB::table('bids')
                ->where('type', 'manual_bid')
                ->whereBetween('bid_time', [$start, $end])
                ->select(
                    'user_id',
                    DB::raw('COUNT(*) as total_bid_count'),
                    DB::raw("SUM(CASE WHEN wallet_source = 'ictp' THEN 1 ELSE 0 END) as ictp_bid_count")
                )
                ->groupBy('user_id');

            $baseQuery = DB::table('sells as s')
                ->join('users as u', 's.user_id', '=', 'u.id')
                ->leftJoinSub($lossSubquery, 'lc', 's.user_id', '=', 'lc.user_id')
                ->leftJoinSub($bidSubquery, 'bc', 's.user_id', '=', 'bc.user_id')
                ->where('s.type', 'manual_sell')
                ->where('s.status', 1)
                ->whereBetween('s.updated_at', [$start, $end])
                ->select(
                    's.user_id',
                    'u.username',
                    DB::raw('COUNT(*) as wins'),
                    DB::raw('COALESCE(lc.loss_count, 0) as losses'),
                    DB::raw('COALESCE(bc.total_bid_count, 0) as total_bids'),
                    DB::raw('COALESCE(bc.ictp_bid_count, 0) as ictp_bids'),
                    DB::raw('COALESCE(SUM(s.amount - s.initial_price), 0) as total_profit'),
                    DB::raw('COALESCE(SUM(s.amount * s.quantity), 0) as total_volume')
                )
                ->groupBy('s.user_id', 'u.username', 'lc.loss_count', 'bc.total_bid_count', 'bc.ictp_bid_count');

            // Search filter
            if ($search) {
                $baseQuery->where('u.username', 'LIKE', '%' . $search . '%');
            }

            // Get total count (before pagination) for the paginator
            // We need a count of the grouped results
            $countQuery = DB::table(DB::raw("({$baseQuery->toSql()}) as counted"))
                ->mergeBindings($baseQuery)
                ->selectRaw('COUNT(*) as total');
            $total = $countQuery->value('total') ?? 0;

            // Apply sort
            // win_rate and profit_percent are computed, need to sort by expression
            $orderExpression = match ($sortBy) {
                'username'       => 'u.username',
                'wins'           => 'wins',
                'losses'         => DB::raw('COALESCE(lc.loss_count, 0)'),
                'total_profit'   => DB::raw('COALESCE(SUM(s.amount - s.initial_price), 0)'),
                'total_volume'   => DB::raw('COALESCE(SUM(s.amount * s.quantity), 0)'),
                'profit_percent' => DB::raw('CASE WHEN SUM(s.amount * s.quantity) > 0 THEN SUM(s.amount - s.initial_price) / SUM(s.amount * s.quantity) * 100 ELSE 0 END'),
                'win_rate'       => DB::raw('CASE WHEN (COUNT(*) + COALESCE(lc.loss_count, 0)) > 0 THEN COUNT(*) / (COUNT(*) + COALESCE(lc.loss_count, 0)) * 100 ELSE 0 END'),
                default          => 'wins',
            };

            $results = (clone $baseQuery)
                ->orderBy($orderExpression, $sortDir)
                ->offset($offset)
                ->limit($limit)
                ->get();

            $data = $results->map(function ($row) {
                $wins        = (int) $row->wins;
                $losses      = (int) $row->losses;
                $totalTrades = $wins + $losses;
                $totalVolume = (float) $row->total_volume;
                $totalProfit = (float) $row->total_profit;

                return [
                    'user_id'        => $row->user_id,
                    'username'       => $row->username ?? 'Unknown',
                    'wins'           => $wins,
                    'losses'         => $losses,
                    'win_rate'       => $totalTrades > 0 ? round(($wins / $totalTrades) * 100, 1) : 0,
                    'total_profit'   => round($totalProfit, 2),
                    'total_volume'   => round($totalVolume, 2),
                    'profit_percent' => $totalVolume > 0 ? round(($totalProfit / $totalVolume) * 100, 2) : 0,
                    'total_bids'     => (int) $row->total_bids,
                    'ictp_bids'      => (int) $row->ictp_bids,
                ];
            })->toArray();

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

    /**
     * Users approaching their monthly profit cap.
     * Uses nexus_user_metrics (running counter). No date param.
     * Cached 60 seconds.
     */
    public function getUsersApproachingCap(float $threshold = 70.0): array
    {
        $cacheKey = 'nexus.users.approaching_cap_' . (int) $threshold;

        return Cache::remember($cacheKey, 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 percent
                ')
                ->orderByDesc('percent')
                ->limit(50)
                ->get()
                ->map(fn($row) => [
                    'user_id'  => $row->user_id,
                    'username' => $row->username ?? 'Unknown',
                    'profit'   => (float) $row->profit,
                    'cap'      => (float) $row->cap,
                    'percent'  => (float) $row->percent,
                ])
                ->toArray();
        });
    }

    /**
     * Users currently in loss accumulation phase.
     * Uses nexus_user_metrics. No date param.
     * Cached 60 seconds.
     */
    public function getUsersInLossAccumulation(): array
    {
        return Cache::remember('nexus.users.loss_accumulation', 60, function () {
            return DB::table('nexus_user_metrics as m')
                ->join('users as u', 'm.user_id', '=', 'u.id')
                ->where('m.consecutive_losses', '>', 0)
                ->select(
                    'm.user_id',
                    'u.username',
                    'm.consecutive_losses',
                    'm.total_wins',
                    'm.total_losses'
                )
                ->orderByDesc('m.consecutive_losses')
                ->limit(50)
                ->get()
                ->map(fn($row) => [
                    'user_id'            => $row->user_id,
                    'username'           => $row->username ?? 'Unknown',
                    'consecutive_losses' => (int) $row->consecutive_losses,
                    'total_wins'         => (int) $row->total_wins,
                    'total_losses'       => (int) $row->total_losses,
                ])
                ->toArray();
        });
    }

    /**
     * Platform-wide win/loss ratio.
     *
     * PHASE 6.5: Accepts date range. Queries sells table (source), not metrics.
     * Cached 60 seconds for current month, 5 minutes for historical.
     */
    public function getWinLossRatio(?string $startDate = null, ?string $endDate = null): array
    {
        $start = $startDate ? Carbon::parse($startDate)->startOfDay() : Carbon::now()->startOfMonth();
        $end   = $endDate ? Carbon::parse($endDate)->endOfDay() : Carbon::now()->endOfDay();

        $isCurrentMonth = $start->month === Carbon::now()->month;
        $cacheTtl       = $isCurrentMonth ? 60 : 300;
        $cacheKey       = 'nexus.users.win_loss_ratio_' . $start->toDateString() . '_' . $end->toDateString();

        return Cache::remember($cacheKey, $cacheTtl, function () use ($start, $end) {
            $counts = Sell::where('type', 'manual_sell')
                ->whereIn('status', [1, 2])
                ->whereBetween('updated_at', [$start, $end])
                ->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();

            $wins   = (int) ($counts->wins ?? 0);
            $losses = (int) ($counts->losses ?? 0);
            $total  = $wins + $losses;

            return [
                'wins'      => $wins,
                'losses'    => $losses,
                'total'     => $total,
                'win_rate'  => $total > 0 ? round(($wins / $total) * 100, 2) : 0,
                'loss_rate' => $total > 0 ? round(($losses / $total) * 100, 2) : 0,
            ];
        });
    }

    /**
     * User counts bucketed by capital tier.
     * Point-in-time from nexus_user_metrics. No date param.
     * Cached 60 seconds.
     */
    public function getCapitalDistribution(): array
    {
        return Cache::remember('nexus.users.capital_dist', 60, function () {
            $row = DB::table('nexus_user_metrics')
                ->selectRaw("
                    SUM(CASE WHEN monthly_capital >= 0 AND monthly_capital < 100 THEN 1 ELSE 0 END) as t1,
                    SUM(CASE WHEN monthly_capital >= 100 AND monthly_capital < 1000 THEN 1 ELSE 0 END) as t2,
                    SUM(CASE WHEN monthly_capital >= 1000 AND monthly_capital < 10000 THEN 1 ELSE 0 END) as t3,
                    SUM(CASE WHEN monthly_capital >= 10000 AND monthly_capital < 100000 THEN 1 ELSE 0 END) as t4,
                    SUM(CASE WHEN monthly_capital >= 100000 THEN 1 ELSE 0 END) as t5
                ")
                ->first();

            return [
                ['label' => '$0-$100', 'count' => (int) ($row->t1 ?? 0)],
                ['label' => '$100-$1K', 'count' => (int) ($row->t2 ?? 0)],
                ['label' => '$1K-$10K', 'count' => (int) ($row->t3 ?? 0)],
                ['label' => '$10K-$100K', 'count' => (int) ($row->t4 ?? 0)],
                ['label' => '$100K+', 'count' => (int) ($row->t5 ?? 0)],
            ];
        });
    }

    /**
     * User counts bucketed by profit percentage.
     * Point-in-time from nexus_user_metrics. No date param.
     * Cached 60 seconds.
     */
    public function getProfitDistribution(): array
    {
        return Cache::remember('nexus.users.profit_dist', 120, function () {
            // Compute active capital in bulk: balance + ictp_balance + ownership value
            // Uses same formula as NexusUserMetric::calculateActiveCapital()
            $rows = DB::table('nexus_user_metrics as m')
                ->join('users as u', 'm.user_id', '=', 'u.id')
                ->where('m.monthly_profit', '>', 0)
                ->select('m.user_id', 'm.monthly_profit', 'u.balance', 'u.ictp_balance')
                ->get();

            if ($rows->isEmpty()) {
                return [
                    ['label' => '0%', 'count' => 0],
                    ['label' => '0-2%', 'count' => 0],
                    ['label' => '2-5%', 'count' => 0],
                    ['label' => '5-8%', 'count' => 0],
                    ['label' => '8-10%', 'count' => 0],
                    ['label' => '10%+', 'count' => 0],
                ];
            }

            // Bulk fetch ownership values per user
            $ownershipValues = DB::table('ownerships')
                ->whereIn('user_id', $rows->pluck('user_id'))
                ->groupBy('user_id')
                ->selectRaw('user_id, COALESCE(SUM(`value` * `quantity`), 0) as own_val')
                ->pluck('own_val', 'user_id');

            // Bucket users by profit % of active capital
            $buckets = [0, 0, 0, 0, 0, 0];
            foreach ($rows as $row) {
                $activeCapital = (float) $row->balance + (float) ($row->ictp_balance ?? 0)
                 + (float) ($ownershipValues[$row->user_id] ?? 0);
                if ($activeCapital <= 0) {
                    continue;
                }

                $pct = ((float) $row->monthly_profit / $activeCapital) * 100;
                if ($pct < 0.01) {
                    $buckets[0]++;
                } elseif ($pct < 2) {
                    $buckets[1]++;
                } elseif ($pct < 5) {
                    $buckets[2]++;
                } elseif ($pct < 8) {
                    $buckets[3]++;
                } elseif ($pct < 10) {
                    $buckets[4]++;
                } else {
                    $buckets[5]++;
                }

            }

            return [
                ['label' => '0%', 'count' => $buckets[0]],
                ['label' => '0-2%', 'count' => $buckets[1]],
                ['label' => '2-5%', 'count' => $buckets[2]],
                ['label' => '5-8%', 'count' => $buckets[3]],
                ['label' => '8-10%', 'count' => $buckets[4]],
                ['label' => '10%+', 'count' => $buckets[5]],
            ];
        });
    }

    // ── PHASE 6.5 NEW METHODS ──

    /**
     * Platform participation metrics.
     *
     * All counts query source tables (users, bids, sells) for accuracy.
     * Registered Users is all-time. Others respect the date range.
     * Cached 60 seconds for current month, 5 minutes for historical.
     */
    public function getUserParticipation(?string $startDate = null, ?string $endDate = null): array
    {
        $start = $startDate ? Carbon::parse($startDate)->startOfDay() : Carbon::now()->startOfMonth();
        $end   = $endDate ? Carbon::parse($endDate)->endOfDay() : Carbon::now()->endOfDay();

        $isCurrentMonth = $start->month === Carbon::now()->month;
        $cacheTtl       = $isCurrentMonth ? 60 : 300;
        $cacheKey       = 'nexus.users.participation_' . $start->toDateString() . '_' . $end->toDateString();

        return Cache::remember($cacheKey, $cacheTtl, function () use ($start, $end) {
            // Total registered users (all time)
            $totalUsers = User::count();

            // Active traders (ever placed a manual bid, all time)
            $activeTraders = DB::table('bids')
                ->where('type', 'manual_bid')
                ->distinct('user_id')
                ->count('user_id');

            // Unique bidders in this period
            $uniqueBidders = DB::table('bids')
                ->where('type', 'manual_bid')
                ->whereBetween('bid_time', [$start, $end])
                ->distinct('user_id')
                ->count('user_id');

            // New traders: users whose FIRST ever bid falls within the date range
            $newTraders = DB::table('bids as b1')
                ->where('b1.type', 'manual_bid')
                ->whereRaw('b1.bid_time = (SELECT MIN(b2.bid_time) FROM bids b2 WHERE b2.user_id = b1.user_id AND b2.type = "manual_bid")')
                ->whereBetween('b1.bid_time', [$start, $end])
                ->distinct('b1.user_id')
                ->count('b1.user_id');

            // First-time bid wins used in this period
            $firstBidWins = DB::table('nexus_user_metrics')
                ->where('first_bid_win_used', 1)
                ->whereBetween('updated_at', [$start, $end])
                ->count();

            // First-time sell wins used in this period
            $firstSellWins = DB::table('nexus_user_metrics')
                ->where('first_sell_granted', 1)
                ->whereBetween('updated_at', [$start, $end])
                ->count();

            // ICTP bidders: unique users who bid from ICTP wallet in this period
            $ictpBidders = DB::table('bids')
                ->where('type', 'manual_bid')
                ->where('wallet_source', 'ictp')
                ->whereBetween('bid_time', [$start, $end])
                ->distinct('user_id')
                ->count('user_id');

            // Total ICTP bids in this period
            $ictpBidCount = DB::table('bids')
                ->where('type', 'manual_bid')
                ->where('wallet_source', 'ictp')
                ->whereBetween('bid_time', [$start, $end])
                ->count();

            return [
                'registered_users' => $totalUsers,
                'active_traders'   => $activeTraders,
                'unique_bidders'   => $uniqueBidders,
                'new_traders'      => $newTraders,
                'first_bid_wins'   => $firstBidWins,
                'first_sell_wins'  => $firstSellWins,
                'ictp_bidders'     => $ictpBidders,
                'ictp_bid_count'   => $ictpBidCount,
            ];
        });
    }

    /**
     * Daily unique bidder counts for charting.
     *
     * Returns one data point per day within the range.
     * Used for the Daily Active Bidders line chart.
     * Cached 60s current month, 5 min historical.
     */
    public function getDailyActiveBidders(?string $startDate = null, ?string $endDate = null): array
    {
        $start = $startDate ? Carbon::parse($startDate)->startOfDay() : Carbon::now()->startOfMonth();
        $end   = $endDate ? Carbon::parse($endDate)->endOfDay() : Carbon::now()->endOfDay();

        $isCurrentMonth = $start->month === Carbon::now()->month;
        $cacheTtl       = $isCurrentMonth ? 60 : 300;
        $cacheKey       = 'nexus.users.daily_bidders_' . $start->toDateString() . '_' . $end->toDateString();

        return Cache::remember($cacheKey, $cacheTtl, function () use ($start, $end) {
            $dailyCounts = DB::table('bids')
                ->where('type', 'manual_bid')
                ->whereBetween('bid_time', [$start, $end])
                ->selectRaw('DATE(bid_time) as bid_date, COUNT(DISTINCT user_id) as unique_bidders')
                ->groupByRaw('DATE(bid_time)')
                ->orderBy('bid_date')
                ->pluck('unique_bidders', 'bid_date');

            // Fill in all days including zeros
            $result = [];
            $cursor = $start->copy();
            while ($cursor <= $end) {
                $dateStr  = $cursor->toDateString();
                $result[] = [
                    'date'  => $dateStr,
                    'label' => $cursor->format('M d'),
                    'count' => (int) ($dailyCounts[$dateStr] ?? 0),
                ];
                $cursor->addDay();
            }

            return $result;
        });
    }

    /**
     * Win rate distribution histogram.
     *
     * Buckets users by their win rate in the selected period.
     * Queries source tables (sells) for historical accuracy.
     * Cached 60s current month, 5 min historical.
     */
    public function getWinRateDistribution(?string $startDate = null, ?string $endDate = null): array
    {
        $start = $startDate ? Carbon::parse($startDate)->startOfDay() : Carbon::now()->startOfMonth();
        $end   = $endDate ? Carbon::parse($endDate)->endOfDay() : Carbon::now()->endOfDay();

        $isCurrentMonth = $start->month === Carbon::now()->month;
        $cacheTtl       = $isCurrentMonth ? 60 : 300;
        $cacheKey       = 'nexus.users.win_rate_dist_' . $start->toDateString() . '_' . $end->toDateString();

        return Cache::remember($cacheKey, $cacheTtl, function () use ($start, $end) {
            // Per-user win rates from source data
            $userRates = DB::table('sells')
                ->where('type', 'manual_sell')
                ->whereIn('status', [1, 2])
                ->whereBetween('updated_at', [$start, $end])
                ->selectRaw('
                    user_id,
                    SUM(CASE WHEN status = 1 THEN 1 ELSE 0 END) as wins,
                    COUNT(*) as total
                ')
                ->groupBy('user_id')
                ->having('total', '>=', 1) // At least 1 evaluated sell
                ->get();

            // Bucket into win rate ranges
            $buckets = [
                '0-10%'   => 0,
                '10-20%'  => 0,
                '20-30%'  => 0,
                '30-50%'  => 0,
                '50-70%'  => 0,
                '70-100%' => 0,
            ];

            foreach ($userRates as $user) {
                $rate = $user->total > 0 ? ($user->wins / $user->total) * 100 : 0;
                if ($rate < 10) {
                    $buckets['0-10%']++;
                } elseif ($rate < 20) {
                    $buckets['10-20%']++;
                } elseif ($rate < 30) {
                    $buckets['20-30%']++;
                } elseif ($rate < 50) {
                    $buckets['30-50%']++;
                } elseif ($rate < 70) {
                    $buckets['50-70%']++;
                } else {
                    $buckets['70-100%']++;
                }

            }

            $result = [];
            foreach ($buckets as $label => $count) {
                $result[] = ['label' => $label, 'count' => $count];
            }

            return $result;
        });
    }

    /**
     * Available months with data for the month selector dropdown.
     *
     * Returns a list of months (YYYY-MM) that have at least one sell.
     * Cached 5 minutes.
     */
    public function getAvailableMonths(): array
    {
        return Cache::remember('nexus.users.available_months', 300, function () {
            $months = DB::table('sells')
                ->where('type', 'manual_sell')
                ->selectRaw("DISTINCT DATE_FORMAT(sell_time, '%Y-%m') as month")
                ->orderByDesc('month')
                ->pluck('month')
                ->toArray();

            // Ensure current month is always included even with no data
            $currentMonth = Carbon::now()->format('Y-m');
            if (! in_array($currentMonth, $months)) {
                array_unshift($months, $currentMonth);
            }

            return $months;
        });
    }
}