<?php

namespace App\Nexus\Analytics;

use App\Models\Bid;
use App\Models\Market;
use App\Models\Sell;
use App\Nexus\Engine\EconomyGuardian;
use App\Nexus\Models\NexusEconomySnapshot;
use Carbon\Carbon;
use Illuminate\Support\Facades\Cache;
use Illuminate\Support\Facades\DB;

/**
 * NEXUS Analytics - MarketMetricsService
 *
 * Market-level analytics for the Markets dashboard page.
 * Read-only. Returns structured arrays.
 *
 * PHASE 6 PERFORMANCE (March 9, 2026):
 * - getDynamicPricingHistory: 141 queries down to 2
 * - getMarketWinRates: 41 queries down to 1
 * - All methods cached 60-120 seconds
 *
 * PHASE 6.5 ADDITIONS:
 * - getMarketWinRates: accepts date range
 * - getCategoryActivity: accepts date range
 * - No changes to getDynamicPricingHistory (already 7-day window)
 * - No changes to getEconomyIndicators (point-in-time)
 *
 * @package App\Nexus\Analytics
 */
class MarketMetricsService
{
    /**
     * Effective sell margin per market over the last N days.
     * Unchanged from Phase 6. Already uses a 7-day window.
     * Cached 120 seconds.
     */
    public function getDynamicPricingHistory(int $days = 7): array
    {
        return Cache::remember('nexus.markets.pricing_history_' . $days, 120, function () use ($days) {
            $startDate = Carbon::now()->subDays($days);
            $markets = Market::all(['id', 'name', 'sell_margin']);

            $marginData = Sell::where('type', 'manual_sell')
                ->where('status', 1)
                ->where('initial_price', '>', 0)
                ->where('updated_at', '>=', $startDate)
                ->selectRaw('
                    market_id,
                    DATE(updated_at) as sell_date,
                    AVG((amount - initial_price) / initial_price * 100) as avg_margin
                ')
                ->groupBy('market_id', DB::raw('DATE(updated_at)'))
                ->get();

            $marginIndex = [];
            foreach ($marginData as $row) {
                $marginIndex[$row->market_id . '_' . $row->sell_date] = round((float) $row->avg_margin, 2);
            }

            $history = [];
            foreach ($markets as $market) {
                $dailyData = [];
                for ($i = $days - 1; $i >= 0; $i--) {
                    $date = Carbon::now()->subDays($i)->toDateString();
                    $key = $market->id . '_' . $date;
                    $dailyData[] = [
                        'date'   => $date,
                        'margin' => $marginIndex[$key] ?? round((float) $market->sell_margin, 2),
                    ];
                }

                $history[] = [
                    'market_id'   => $market->id,
                    'market_name' => $market->name,
                    'base_margin' => (float) $market->sell_margin,
                    'daily_data'  => $dailyData,
                ];
            }

            return $history;
        });
    }

    /**
     * Sell success rates per market.
     *
     * PHASE 6.5: Accepts date range. Defaults to current month.
     * Cached 60s current month, 5 min historical.
     */
    public function getMarketWinRates(?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 && $start->year === Carbon::now()->year;
        $cacheTtl = $isCurrentMonth ? 60 : 300;
        $cacheKey = 'nexus.markets.win_rates_' . $start->toDateString() . '_' . $end->toDateString();

        return Cache::remember($cacheKey, $cacheTtl, function () use ($start, $end) {
            $markets = Market::all(['id', 'name'])->keyBy('id');

            $stats = Sell::where('type', 'manual_sell')
                ->whereIn('status', [1, 2])
                ->whereBetween('updated_at', [$start, $end])
                ->selectRaw('
                    market_id,
                    COUNT(*) as total_sells,
                    SUM(CASE WHEN status = 1 THEN 1 ELSE 0 END) as wins
                ')
                ->groupBy('market_id')
                ->get()
                ->keyBy('market_id');

            return $markets->map(function ($market) use ($stats) {
                $row = $stats->get($market->id);
                $total = $row ? (int) $row->total_sells : 0;
                $won = $row ? (int) $row->wins : 0;

                return [
                    'market_id'   => $market->id,
                    'market_name' => $market->name,
                    'total_sells' => $total,
                    'wins'        => $won,
                    'losses'      => $total - $won,
                    'win_rate'    => $total > 0 ? round(($won / $total) * 100, 2) : 0,
                ];
            })->values()->toArray();
        });
    }

    /**
     * Bid and sell counts per product category.
     *
     * PHASE 6.5: Accepts date range. Defaults to current month.
     * Cached 60s current month, 5 min historical.
     */
    public function getCategoryActivity(?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 && $start->year === Carbon::now()->year;
        $cacheTtl = $isCurrentMonth ? 60 : 300;
        $cacheKey = 'nexus.markets.category_activity_' . $start->toDateString() . '_' . $end->toDateString();

        return Cache::remember($cacheKey, $cacheTtl, function () use ($start, $end) {
            $bidsByCategory = Bid::where('type', 'manual_bid')
                ->whereBetween('bid_time', [$start, $end])
                ->join('products', 'bids.product_id', '=', 'products.id')
                ->select('products.category', DB::raw('COUNT(*) as bid_count'))
                ->groupBy('products.category')
                ->pluck('bid_count', 'products.category')
                ->toArray();

            $sellsByCategory = Sell::where('type', 'manual_sell')
                ->whereBetween('sell_time', [$start, $end])
                ->join('products', 'sells.product_id', '=', 'products.id')
                ->select('products.category', DB::raw('COUNT(*) as sell_count'))
                ->groupBy('products.category')
                ->pluck('sell_count', 'products.category')
                ->toArray();

            $categories = array_unique(array_merge(
                array_keys($bidsByCategory),
                array_keys($sellsByCategory)
            ));

            $result = [];
            foreach ($categories as $category) {
                if (empty($category)) continue;
                $result[] = [
                    'category' => $category,
                    'bids'     => $bidsByCategory[$category] ?? 0,
                    'sells'    => $sellsByCategory[$category] ?? 0,
                ];
            }

            usort($result, fn($a, $b) => ($b['bids'] + $b['sells']) - ($a['bids'] + $a['sells']));

            return $result;
        });
    }

    /**
     * Economy indicators with labels.
     * Point-in-time snapshot. Unchanged from Phase 6.
     * Cached 60 seconds.
     */
    public function getEconomyIndicators(): array
    {
        return Cache::remember('nexus.markets.economy_indicators', 60, function () {
            $guardian = new EconomyGuardian();
            $liquidity = $guardian->computeLiquidityIndex();

            $marketCounts = Sell::where('type', 'manual_sell')
                ->where('status', 0)
                ->select('market_id', DB::raw('COUNT(*) as cnt'))
                ->groupBy('market_id')
                ->pluck('cnt')
                ->toArray();

            $marketBalance = 0;
            if (count($marketCounts) > 1) {
                $avg = array_sum($marketCounts) / count($marketCounts);
                $variance = array_sum(array_map(fn($c) => pow($c - $avg, 2), $marketCounts)) / count($marketCounts);
                $cv = $avg > 0 ? sqrt($variance) / $avg : 1;
                $marketBalance = round(max(0, min(1, 1 - $cv)), 2);
            }

            $startOfMonth = Carbon::now()->startOfMonth();
            $winCounts = Sell::where('type', 'manual_sell')
                ->where('status', 1)
                ->where('updated_at', '>=', $startOfMonth)
                ->select('user_id', DB::raw('COUNT(*) as cnt'))
                ->groupBy('user_id')
                ->pluck('cnt')
                ->toArray();

            $fairness = 0;
            if (count($winCounts) > 1) {
                $avg = array_sum($winCounts) / count($winCounts);
                $variance = array_sum(array_map(fn($c) => pow($c - $avg, 2), $winCounts)) / count($winCounts);
                $cv = $avg > 0 ? sqrt($variance) / $avg : 1;
                $fairness = round(max(0, min(1, 1 - $cv)), 2);
            }

            return [
                'liquidity' => [
                    'value' => $liquidity,
                    'label' => $liquidity >= 0.6 ? 'Healthy' : ($liquidity >= 0.3 ? 'Moderate' : 'Low'),
                    'color' => $liquidity >= 0.6 ? 'green' : ($liquidity >= 0.3 ? 'yellow' : 'red'),
                ],
                'market_balance' => [
                    'value' => $marketBalance,
                    'label' => $marketBalance >= 0.6 ? 'Balanced' : ($marketBalance >= 0.3 ? 'Uneven' : 'Concentrated'),
                    'color' => $marketBalance >= 0.6 ? 'green' : ($marketBalance >= 0.3 ? 'yellow' : 'red'),
                ],
                'fairness' => [
                    'value' => $fairness,
                    'label' => $fairness >= 0.6 ? 'Fair' : ($fairness >= 0.3 ? 'Moderate' : 'Concentrated'),
                    'color' => $fairness >= 0.6 ? 'green' : ($fairness >= 0.3 ? 'yellow' : 'red'),
                ],
            ];
        });
    }
}
