<?php

namespace App\Console\Commands;

use App\Models\Zone;
use Carbon\CarbonImmutable;
use Illuminate\Console\Command;
use Illuminate\Support\Facades\DB;

/**
 * Voltcast Forecast Bench scoring. This file IS the published scoring source
 * (served verbatim at /bench/scoring) — change it and the public sees the diff.
 *
 * Rules:
 * 1. A target day is scored once its realized day-ahead curve is complete.
 * 2. Realized curve = source-collapsed prices (ENTSO-E preferred over SMARD),
 *    the same curve served by GET /v1/prices — no special bench truth.
 * 3. Each submitted point is matched to a realized period by exact timestamp;
 *    hourly submissions match all four 15-minute periods of that hour.
 * 4. MAE and RMSE over matched periods; a submission needs ≥20 matches to score.
 * 5. Voltcast P50 (from the run issued BEFORE the deadline) and the naive
 *    persistence baseline (D-1 curve replayed) are auto-entered daily under
 *    the same rules. Scores are never edited or removed.
 */
class ScoreBench extends Command
{
    protected $signature = 'voltcast:score-bench';

    protected $description = 'Score matured Forecast Bench submissions and auto-enter the system participants';

    public function handle(): int
    {
        $this->autoEnterSystemRows();

        $pending = DB::table('bench_submissions')
            ->whereNull('scored_at')
            ->where('target_date', '<', now()->toDateString())
            ->where('target_date', '>=', now()->subDays(10)->toDateString())
            ->get();

        $scored = 0;
        foreach ($pending as $submission) {
            $realized = $this->realizedCurve((int) $submission->zone_id, $submission->target_date);
            if (count($realized) < 20) {
                continue; // curve not complete yet — wait, never guess
            }

            $points = json_decode($submission->points, true);
            // ≤30 points = hourly submission: each value covers 4 quarter-hours.
            $spans = count($points) <= 30 ? [0, 15, 30, 45] : [0];
            $errors = [];
            foreach ($points as $point) {
                $ts = CarbonImmutable::parse($point['ts'])->utc();
                foreach ($spans as $offset) {
                    $key = $ts->addMinutes($offset)->toIso8601ZuluString();
                    if (isset($realized[$key])) {
                        $errors[] = $realized[$key] - (float) $point['value'];
                    }
                }
            }
            if (count($errors) < 20) {
                continue;
            }

            DB::table('bench_submissions')->where('id', $submission->id)->update([
                'mae' => round(array_sum(array_map('abs', $errors)) / count($errors), 2),
                'rmse' => round(sqrt(array_sum(array_map(fn ($e) => $e ** 2, $errors)) / count($errors)), 2),
                'scored_periods' => count($errors),
                'scored_at' => now(),
            ]);
            $scored++;
        }

        if ($scored > 0) {
            $this->info("Scored {$scored} submissions.");
        }

        return self::SUCCESS;
    }

    /** Voltcast P50 + persistence baseline, entered for tomorrow before the deadline. */
    private function autoEnterSystemRows(): void
    {
        foreach (Zone::query()->where('active', true)->where('is_launch_zone', true)->get() as $zone) {
            $tomorrow = CarbonImmutable::now($zone->timezone)->addDay();
            $dayStart = $tomorrow->startOfDay()->utc();
            $deadline = CarbonImmutable::now('Europe/Berlin')->setTime(12, 0);

            // Voltcast P50 from the latest run issued before today's deadline.
            $run = DB::table('forecast_runs')
                ->where('zone_id', $zone->id)
                ->where('model_version', 'not like', '%backtest%')
                ->whereExists(fn ($q) => $q->selectRaw('1')->from('forecasts')->whereColumn('forecasts.run_id', 'forecast_runs.id'))
                ->where('issued_at', '<=', $deadline)
                ->orderByDesc('issued_at')->first();
            if ($run) {
                $points = DB::table('forecasts')
                    ->where('run_id', $run->id)
                    ->where('target_start', '>=', $dayStart)
                    ->where('target_start', '<', $dayStart->addDay())
                    ->orderBy('target_start')
                    ->get(['target_start', 'q50'])
                    ->map(fn ($r) => ['ts' => CarbonImmutable::parse($r->target_start)->utc()->toIso8601ZuluString(), 'value' => (float) $r->q50]);
                $this->upsertSystem($zone->id, $tomorrow->toDateString(), 'Voltcast P50', $points->all());
            }

            // Persistence baseline: today's curve (known pre-deadline) replayed tomorrow.
            $todayCurve = $this->realizedCurve($zone->id, CarbonImmutable::now($zone->timezone)->toDateString());
            if (count($todayCurve) >= 20) {
                $points = collect($todayCurve)->map(fn ($value, $ts) => [
                    'ts' => CarbonImmutable::parse($ts)->addDay()->toIso8601ZuluString(),
                    'value' => $value,
                ])->values();
                $this->upsertSystem($zone->id, $tomorrow->toDateString(), 'Persistence baseline', $points->all());
            }
        }
    }

    private function upsertSystem(int $zoneId, string $targetDate, string $name, array $points): void
    {
        if ($points === []) {
            return;
        }
        DB::table('bench_submissions')->updateOrInsert(
            ['user_id' => null, 'zone_id' => $zoneId, 'target_date' => $targetDate, 'display_name' => $name],
            ['points' => json_encode($points), 'submitted_at' => now(),
                'mae' => null, 'rmse' => null, 'scored_periods' => null, 'scored_at' => null],
        );
    }

    /** @return array<string, float> ts => price, source-collapsed */
    private function realizedCurve(int $zoneId, string $targetDate): array
    {
        $zone = Zone::find($zoneId);
        $start = CarbonImmutable::parse($targetDate, $zone->timezone)->startOfDay()->utc();

        return DB::table('day_ahead_prices')
            ->where('zone_id', $zoneId)
            ->where('delivery_start', '>=', $start)
            ->where('delivery_start', '<', $start->addDay())
            ->orderBy('delivery_start')
            ->orderByRaw("CASE source WHEN 'entsoe' THEN 0 WHEN 'smard' THEN 1 ELSE 9 END")
            ->get(['delivery_start', 'price_eur_mwh'])
            ->unique('delivery_start')
            ->mapWithKeys(fn ($r) => [CarbonImmutable::parse($r->delivery_start)->utc()->toIso8601ZuluString() => (float) $r->price_eur_mwh])
            ->all();
    }
}