<?php

declare(strict_types=1);

namespace App\Services\Analytics;

use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;

final class DiscoveryFeedbackReportService
{
    public function buildReport(string $from, string $to, int $limit = 20): array
    {
        if (! Schema::hasTable('user_discovery_events')) {
            return [
                'overview' => $this->emptyOverview(),
                'daily_feedback' => [],
                'trend_summary' => $this->emptyTrendSummary(),
                'by_surface' => [],
                'by_algo_surface' => [],
                'top_artworks' => [],
            ];
        }

        $dailyFeedback = $this->dailyFeedback($from, $to);
        $trendSummary = $this->trendSummary($dailyFeedback);
        $surfaceTrendMap = $this->surfaceTrendMap($from, $to);
        $bySurface = $this->attachSurfaceTrendMap($this->bySurface($from, $to), $surfaceTrendMap);
        $algoSurfaceTrendMap = $this->algoSurfaceTrendMap($from, $to);
        $byAlgoSurface = $this->attachAlgoSurfaceTrendMap($this->byAlgoSurface($from, $to), $algoSurfaceTrendMap);

        return [
            'overview' => $this->overview($from, $to),
            'daily_feedback' => $dailyFeedback,
            'trend_summary' => $trendSummary,
            'by_surface' => $bySurface,
            'by_algo_surface' => $byAlgoSurface,
            'top_artworks' => $this->topArtworks($from, $to, $limit),
            'latest_aggregated_date' => $this->latestAggregatedDate(),
        ];
    }

    private function overview(string $from, string $to): array
    {
        $row = DB::table('user_discovery_events')
            ->selectRaw('COUNT(*) AS total_events')
            ->selectRaw('COUNT(DISTINCT user_id) AS unique_users')
            ->selectRaw('COUNT(DISTINCT artwork_id) AS unique_artworks')
            ->selectRaw("SUM(CASE WHEN event_type = 'view' THEN 1 ELSE 0 END) AS views")
            ->selectRaw("SUM(CASE WHEN event_type = 'click' THEN 1 ELSE 0 END) AS clicks")
            ->selectRaw("SUM(CASE WHEN event_type = 'favorite' THEN 1 ELSE 0 END) AS favorites")
            ->selectRaw("SUM(CASE WHEN event_type = 'download' THEN 1 ELSE 0 END) AS downloads")
            ->selectRaw("SUM(CASE WHEN event_type = 'hide_artwork' THEN 1 ELSE 0 END) AS hidden_artworks")
            ->selectRaw("SUM(CASE WHEN event_type = 'dislike_tag' THEN 1 ELSE 0 END) AS disliked_tags")
            ->selectRaw("SUM(CASE WHEN event_type = 'unhide_artwork' THEN 1 ELSE 0 END) AS undo_hidden_artworks")
            ->selectRaw("SUM(CASE WHEN event_type = 'undo_dislike_tag' THEN 1 ELSE 0 END) AS undo_disliked_tags")
            ->whereBetween('event_date', [$from, $to])
            ->first();

        $views = (int) ($row->views ?? 0);
        $clicks = (int) ($row->clicks ?? 0);
        $favorites = (int) ($row->favorites ?? 0);
        $downloads = (int) ($row->downloads ?? 0);
        $hiddenArtworks = (int) ($row->hidden_artworks ?? 0);
        $dislikedTags = (int) ($row->disliked_tags ?? 0);
        $undoHiddenArtworks = (int) ($row->undo_hidden_artworks ?? 0);
        $undoDislikedTags = (int) ($row->undo_disliked_tags ?? 0);
        $feedbackActions = $favorites + $downloads;
        $negativeFeedbackActions = $hiddenArtworks + $dislikedTags;
        $undoActions = $undoHiddenArtworks + $undoDislikedTags;

        return [
            'total_events' => (int) ($row->total_events ?? 0),
            'unique_users' => (int) ($row->unique_users ?? 0),
            'unique_artworks' => (int) ($row->unique_artworks ?? 0),
            'views' => $views,
            'clicks' => $clicks,
            'favorites' => $favorites,
            'downloads' => $downloads,
            'feedback_actions' => $feedbackActions,
            'hidden_artworks' => $hiddenArtworks,
            'disliked_tags' => $dislikedTags,
            'negative_feedback_actions' => $negativeFeedbackActions,
            'undo_hidden_artworks' => $undoHiddenArtworks,
            'undo_disliked_tags' => $undoDislikedTags,
            'undo_actions' => $undoActions,
            'ctr' => round($views > 0 ? $clicks / $views : 0.0, 6),
            'favorite_rate_per_click' => round($clicks > 0 ? $favorites / $clicks : 0.0, 6),
            'download_rate_per_click' => round($clicks > 0 ? $downloads / $clicks : 0.0, 6),
            'feedback_rate_per_click' => round($clicks > 0 ? $feedbackActions / $clicks : 0.0, 6),
            'negative_feedback_rate_per_click' => round($clicks > 0 ? $negativeFeedbackActions / $clicks : 0.0, 6),
            'undo_rate_per_negative_feedback' => round($negativeFeedbackActions > 0 ? $undoActions / $negativeFeedbackActions : 0.0, 6),
        ];
    }

    private function bySurface(string $from, string $to): array
    {
        if (Schema::hasTable('discovery_feedback_daily_metrics')) {
            return DB::table('discovery_feedback_daily_metrics')
                ->selectRaw('surface')
                ->selectRaw('SUM(views) AS views')
                ->selectRaw('SUM(clicks) AS clicks')
                ->selectRaw('SUM(favorites) AS favorites')
                ->selectRaw('SUM(downloads) AS downloads')
                ->selectRaw('SUM(feedback_actions) AS feedback_actions')
                ->selectRaw('SUM(hidden_artworks) AS hidden_artworks')
                ->selectRaw('SUM(disliked_tags) AS disliked_tags')
                ->selectRaw('SUM(negative_feedback_actions) AS negative_feedback_actions')
                ->selectRaw('SUM(undo_hidden_artworks) AS undo_hidden_artworks')
                ->selectRaw('SUM(undo_disliked_tags) AS undo_disliked_tags')
                ->selectRaw('SUM(undo_actions) AS undo_actions')
                ->selectRaw('SUM(unique_users) AS unique_users')
                ->selectRaw('SUM(unique_artworks) AS unique_artworks')
                ->whereBetween('metric_date', [$from, $to])
                ->groupBy('surface')
                ->orderByDesc('clicks')
                ->orderByDesc('favorites')
                ->get()
                ->map(fn ($row): array => $this->formatEventSummaryRow($row, ['surface' => (string) ($row->surface ?? 'unknown')]))
                ->all();
        }

        $surfaceExpression = $this->surfaceExpression();

        return DB::table('user_discovery_events')
            ->selectRaw($surfaceExpression . ' AS surface')
            ->selectRaw('COUNT(*) AS total_events')
            ->selectRaw('COUNT(DISTINCT user_id) AS unique_users')
            ->selectRaw('COUNT(DISTINCT artwork_id) AS unique_artworks')
            ->selectRaw("SUM(CASE WHEN event_type = 'view' THEN 1 ELSE 0 END) AS views")
            ->selectRaw("SUM(CASE WHEN event_type = 'click' THEN 1 ELSE 0 END) AS clicks")
            ->selectRaw("SUM(CASE WHEN event_type = 'favorite' THEN 1 ELSE 0 END) AS favorites")
            ->selectRaw("SUM(CASE WHEN event_type = 'download' THEN 1 ELSE 0 END) AS downloads")
            ->selectRaw("SUM(CASE WHEN event_type = 'hide_artwork' THEN 1 ELSE 0 END) AS hidden_artworks")
            ->selectRaw("SUM(CASE WHEN event_type = 'dislike_tag' THEN 1 ELSE 0 END) AS disliked_tags")
            ->selectRaw("SUM(CASE WHEN event_type = 'unhide_artwork' THEN 1 ELSE 0 END) AS undo_hidden_artworks")
            ->selectRaw("SUM(CASE WHEN event_type = 'undo_dislike_tag' THEN 1 ELSE 0 END) AS undo_disliked_tags")
            ->whereBetween('event_date', [$from, $to])
            ->groupBy(DB::raw($surfaceExpression))
            ->orderByDesc('clicks')
            ->orderByDesc('favorites')
            ->get()
            ->map(fn ($row): array => $this->formatEventSummaryRow($row, ['surface' => (string) ($row->surface ?? 'unknown')]))
            ->all();
    }

    private function byAlgoSurface(string $from, string $to): array
    {
        if (Schema::hasTable('discovery_feedback_daily_metrics')) {
            return DB::table('discovery_feedback_daily_metrics')
                ->selectRaw('algo_version')
                ->selectRaw('surface')
                ->selectRaw('SUM(views) AS views')
                ->selectRaw('SUM(clicks) AS clicks')
                ->selectRaw('SUM(favorites) AS favorites')
                ->selectRaw('SUM(downloads) AS downloads')
                ->selectRaw('SUM(feedback_actions) AS feedback_actions')
                ->selectRaw('SUM(hidden_artworks) AS hidden_artworks')
                ->selectRaw('SUM(disliked_tags) AS disliked_tags')
                ->selectRaw('SUM(negative_feedback_actions) AS negative_feedback_actions')
                ->selectRaw('SUM(undo_hidden_artworks) AS undo_hidden_artworks')
                ->selectRaw('SUM(undo_disliked_tags) AS undo_disliked_tags')
                ->selectRaw('SUM(undo_actions) AS undo_actions')
                ->selectRaw('SUM(unique_users) AS unique_users')
                ->selectRaw('SUM(unique_artworks) AS unique_artworks')
                ->whereBetween('metric_date', [$from, $to])
                ->groupBy('algo_version', 'surface')
                ->orderBy('algo_version')
                ->orderByDesc('clicks')
                ->get()
                ->map(fn ($row): array => $this->formatEventSummaryRow($row, [
                    'algo_version' => (string) ($row->algo_version ?? ''),
                    'surface' => (string) ($row->surface ?? 'unknown'),
                ]))
                ->all();
        }

        $surfaceExpression = $this->surfaceExpression();

        return DB::table('user_discovery_events')
            ->selectRaw('algo_version')
            ->selectRaw($surfaceExpression . ' AS surface')
            ->selectRaw('COUNT(*) AS total_events')
            ->selectRaw('COUNT(DISTINCT user_id) AS unique_users')
            ->selectRaw('COUNT(DISTINCT artwork_id) AS unique_artworks')
            ->selectRaw("SUM(CASE WHEN event_type = 'view' THEN 1 ELSE 0 END) AS views")
            ->selectRaw("SUM(CASE WHEN event_type = 'click' THEN 1 ELSE 0 END) AS clicks")
            ->selectRaw("SUM(CASE WHEN event_type = 'favorite' THEN 1 ELSE 0 END) AS favorites")
            ->selectRaw("SUM(CASE WHEN event_type = 'download' THEN 1 ELSE 0 END) AS downloads")
            ->selectRaw("SUM(CASE WHEN event_type = 'hide_artwork' THEN 1 ELSE 0 END) AS hidden_artworks")
            ->selectRaw("SUM(CASE WHEN event_type = 'dislike_tag' THEN 1 ELSE 0 END) AS disliked_tags")
            ->selectRaw("SUM(CASE WHEN event_type = 'unhide_artwork' THEN 1 ELSE 0 END) AS undo_hidden_artworks")
            ->selectRaw("SUM(CASE WHEN event_type = 'undo_dislike_tag' THEN 1 ELSE 0 END) AS undo_disliked_tags")
            ->whereBetween('event_date', [$from, $to])
            ->groupBy('algo_version', DB::raw($surfaceExpression))
            ->orderBy('algo_version')
            ->orderByDesc('clicks')
            ->get()
            ->map(fn ($row): array => $this->formatEventSummaryRow($row, [
                'algo_version' => (string) ($row->algo_version ?? ''),
                'surface' => (string) ($row->surface ?? 'unknown'),
            ]))
            ->all();
    }

    private function topArtworks(string $from, string $to, int $limit): array
    {
        $surfaceExpression = $this->surfaceExpression();

        return DB::table('user_discovery_events as e')
            ->leftJoin('artworks as a', 'a.id', '=', 'e.artwork_id')
            ->selectRaw('e.artwork_id')
            ->selectRaw('a.title as artwork_title')
            ->selectRaw('e.algo_version')
            ->selectRaw($surfaceExpression . ' AS surface')
            ->selectRaw("SUM(CASE WHEN e.event_type = 'view' THEN 1 ELSE 0 END) AS views")
            ->selectRaw("SUM(CASE WHEN e.event_type = 'click' THEN 1 ELSE 0 END) AS clicks")
            ->selectRaw("SUM(CASE WHEN e.event_type = 'favorite' THEN 1 ELSE 0 END) AS favorites")
            ->selectRaw("SUM(CASE WHEN e.event_type = 'download' THEN 1 ELSE 0 END) AS downloads")
            ->selectRaw("SUM(CASE WHEN e.event_type = 'hide_artwork' THEN 1 ELSE 0 END) AS hidden_artworks")
            ->selectRaw("SUM(CASE WHEN e.event_type = 'dislike_tag' THEN 1 ELSE 0 END) AS disliked_tags")
            ->selectRaw("SUM(CASE WHEN e.event_type = 'unhide_artwork' THEN 1 ELSE 0 END) AS undo_hidden_artworks")
            ->selectRaw("SUM(CASE WHEN e.event_type = 'undo_dislike_tag' THEN 1 ELSE 0 END) AS undo_disliked_tags")
            ->whereBetween('e.event_date', [$from, $to])
            ->groupBy('e.artwork_id', 'a.title', 'e.algo_version', DB::raw($surfaceExpression))
            ->get()
            ->map(fn ($row): array => $this->formatEventSummaryRow($row, [
                'artwork_id' => (int) ($row->artwork_id ?? 0),
                'artwork_title' => (string) ($row->artwork_title ?? ''),
                'algo_version' => (string) ($row->algo_version ?? ''),
                'surface' => (string) ($row->surface ?? 'unknown'),
            ]))
            ->sort(static function (array $left, array $right): int {
                $favoriteCompare = $right['favorites'] <=> $left['favorites'];
                if ($favoriteCompare !== 0) {
                    return $favoriteCompare;
                }

                $downloadCompare = $right['downloads'] <=> $left['downloads'];
                if ($downloadCompare !== 0) {
                    return $downloadCompare;
                }

                return $right['clicks'] <=> $left['clicks'];
            })
            ->take($limit)
            ->values()
            ->all();
    }

    private function dailyFeedback(string $from, string $to): array
    {
        if (Schema::hasTable('discovery_feedback_daily_metrics')) {
            return DB::table('discovery_feedback_daily_metrics')
                ->selectRaw('metric_date')
                ->selectRaw('SUM(views) AS views')
                ->selectRaw('SUM(clicks) AS clicks')
                ->selectRaw('SUM(favorites) AS favorites')
                ->selectRaw('SUM(downloads) AS downloads')
                ->selectRaw('SUM(feedback_actions) AS feedback_actions')
                ->selectRaw('SUM(hidden_artworks) AS hidden_artworks')
                ->selectRaw('SUM(disliked_tags) AS disliked_tags')
                ->selectRaw('SUM(negative_feedback_actions) AS negative_feedback_actions')
                ->selectRaw('SUM(undo_hidden_artworks) AS undo_hidden_artworks')
                ->selectRaw('SUM(undo_disliked_tags) AS undo_disliked_tags')
                ->selectRaw('SUM(undo_actions) AS undo_actions')
                ->whereBetween('metric_date', [$from, $to])
                ->groupBy('metric_date')
                ->orderBy('metric_date')
                ->get()
                ->map(fn ($row): array => [
                    'date' => (string) $row->metric_date,
                    'views' => (int) ($row->views ?? 0),
                    'clicks' => (int) ($row->clicks ?? 0),
                    'favorites' => (int) ($row->favorites ?? 0),
                    'downloads' => (int) ($row->downloads ?? 0),
                    'feedback_actions' => (int) ($row->feedback_actions ?? 0),
                    'hidden_artworks' => (int) ($row->hidden_artworks ?? 0),
                    'disliked_tags' => (int) ($row->disliked_tags ?? 0),
                    'negative_feedback_actions' => (int) ($row->negative_feedback_actions ?? 0),
                    'undo_hidden_artworks' => (int) ($row->undo_hidden_artworks ?? 0),
                    'undo_disliked_tags' => (int) ($row->undo_disliked_tags ?? 0),
                    'undo_actions' => (int) ($row->undo_actions ?? 0),
                ])
                ->all();
        }

        return DB::table('user_discovery_events')
            ->selectRaw('event_date')
            ->selectRaw("SUM(CASE WHEN event_type = 'view' THEN 1 ELSE 0 END) AS views")
            ->selectRaw("SUM(CASE WHEN event_type = 'click' THEN 1 ELSE 0 END) AS clicks")
            ->selectRaw("SUM(CASE WHEN event_type = 'favorite' THEN 1 ELSE 0 END) AS favorites")
            ->selectRaw("SUM(CASE WHEN event_type = 'download' THEN 1 ELSE 0 END) AS downloads")
            ->selectRaw("SUM(CASE WHEN event_type = 'hide_artwork' THEN 1 ELSE 0 END) AS hidden_artworks")
            ->selectRaw("SUM(CASE WHEN event_type = 'dislike_tag' THEN 1 ELSE 0 END) AS disliked_tags")
            ->selectRaw("SUM(CASE WHEN event_type = 'unhide_artwork' THEN 1 ELSE 0 END) AS undo_hidden_artworks")
            ->selectRaw("SUM(CASE WHEN event_type = 'undo_dislike_tag' THEN 1 ELSE 0 END) AS undo_disliked_tags")
            ->whereBetween('event_date', [$from, $to])
            ->groupBy('event_date')
            ->orderBy('event_date')
            ->get()
            ->map(fn ($row): array => [
                'date' => (string) $row->event_date,
                'views' => (int) ($row->views ?? 0),
                'clicks' => (int) ($row->clicks ?? 0),
                'favorites' => (int) ($row->favorites ?? 0),
                'downloads' => (int) ($row->downloads ?? 0),
                'feedback_actions' => (int) (($row->favorites ?? 0) + ($row->downloads ?? 0)),
                'hidden_artworks' => (int) ($row->hidden_artworks ?? 0),
                'disliked_tags' => (int) ($row->disliked_tags ?? 0),
                'negative_feedback_actions' => (int) (($row->hidden_artworks ?? 0) + ($row->disliked_tags ?? 0)),
                'undo_hidden_artworks' => (int) ($row->undo_hidden_artworks ?? 0),
                'undo_disliked_tags' => (int) ($row->undo_disliked_tags ?? 0),
                'undo_actions' => (int) (($row->undo_hidden_artworks ?? 0) + ($row->undo_disliked_tags ?? 0)),
            ])
            ->all();
    }

    /**
     * @param array<int, array<string, mixed>> $rows
     * @param array<string, array<string, mixed>> $surfaceTrendMap
     * @return array<int, array<string, mixed>>
     */
    private function attachSurfaceTrendMap(array $rows, array $surfaceTrendMap): array
    {
        $rows = array_map(function (array $row) use ($surfaceTrendMap): array {
            $surface = (string) ($row['surface'] ?? 'unknown');

            return array_merge($row, [
                'trend' => $surfaceTrendMap[$surface] ?? $this->emptySurfaceTrend(),
            ]);
        }, $rows);

        return $this->sortRowsByTrendRisk($rows);
    }

    /**
     * @param array<int, array<string, mixed>> $rows
     * @param array<string, array<string, mixed>> $algoSurfaceTrendMap
     * @return array<int, array<string, mixed>>
     */
    private function attachAlgoSurfaceTrendMap(array $rows, array $algoSurfaceTrendMap): array
    {
        $rows = array_map(function (array $row) use ($algoSurfaceTrendMap): array {
            $algoVersion = (string) ($row['algo_version'] ?? '');
            $surface = (string) ($row['surface'] ?? 'unknown');
            $key = $this->algoSurfaceTrendKey($algoVersion, $surface);

            return array_merge($row, [
                'trend' => $algoSurfaceTrendMap[$key] ?? $this->emptySurfaceTrend(),
            ]);
        }, $rows);

        return $this->sortRowsByTrendRisk($rows);
    }

    /**
     * @return array<string, array<string, mixed>>
     */
    private function surfaceTrendMap(string $from, string $to): array
    {
        $rows = $this->dailySurfaceMetrics($from, $to);
        if ($rows === []) {
            return [];
        }

        $dates = array_values(array_unique(array_map(
            static fn (array $row): string => (string) $row['date'],
            $rows,
        )));
        sort($dates);

        $latestDate = $dates[array_key_last($dates)] ?? null;
        $previousDate = count($dates) > 1 ? $dates[count($dates) - 2] : null;

        $grouped = [];
        foreach ($rows as $row) {
            $date = (string) ($row['date'] ?? '');
            $surface = (string) ($row['surface'] ?? 'unknown');
            $grouped[$surface][$date] = $row;
        }

        $trendMap = [];
        foreach ($grouped as $surface => $surfaceRows) {
            $latest = $latestDate !== null ? ($surfaceRows[$latestDate] ?? null) : null;
            $previous = $previousDate !== null ? ($surfaceRows[$previousDate] ?? null) : null;

            $trendMap[$surface] = [
                'latest_day' => $latest,
                'previous_day' => $previous,
                'overall_status' => $this->overallTrendStatus($latest, $previous),
                'deltas' => [
                    'feedback_actions' => $this->formatTrendDelta($latest, $previous, 'feedback_actions', 'up'),
                    'negative_feedback_actions' => $this->formatTrendDelta($latest, $previous, 'negative_feedback_actions', 'down'),
                    'undo_actions' => $this->formatTrendDelta($latest, $previous, 'undo_actions', 'up'),
                ],
            ];
        }

        return $trendMap;
    }

    /**
     * @return array<string, array<string, mixed>>
     */
    private function algoSurfaceTrendMap(string $from, string $to): array
    {
        $rows = $this->dailyAlgoSurfaceMetrics($from, $to);
        if ($rows === []) {
            return [];
        }

        $dates = array_values(array_unique(array_map(
            static fn (array $row): string => (string) $row['date'],
            $rows,
        )));
        sort($dates);

        $latestDate = $dates[array_key_last($dates)] ?? null;
        $previousDate = count($dates) > 1 ? $dates[count($dates) - 2] : null;

        $grouped = [];
        foreach ($rows as $row) {
            $date = (string) ($row['date'] ?? '');
            $algoVersion = (string) ($row['algo_version'] ?? '');
            $surface = (string) ($row['surface'] ?? 'unknown');
            $grouped[$this->algoSurfaceTrendKey($algoVersion, $surface)][$date] = $row;
        }

        $trendMap = [];
        foreach ($grouped as $key => $algoSurfaceRows) {
            $latest = $latestDate !== null ? ($algoSurfaceRows[$latestDate] ?? null) : null;
            $previous = $previousDate !== null ? ($algoSurfaceRows[$previousDate] ?? null) : null;

            $trendMap[$key] = [
                'latest_day' => $latest,
                'previous_day' => $previous,
                'overall_status' => $this->overallTrendStatus($latest, $previous),
                'deltas' => [
                    'feedback_actions' => $this->formatTrendDelta($latest, $previous, 'feedback_actions', 'up'),
                    'negative_feedback_actions' => $this->formatTrendDelta($latest, $previous, 'negative_feedback_actions', 'down'),
                    'undo_actions' => $this->formatTrendDelta($latest, $previous, 'undo_actions', 'up'),
                ],
            ];
        }

        return $trendMap;
    }

    /**
     * @return array<int, array<string, mixed>>
     */
    private function dailySurfaceMetrics(string $from, string $to): array
    {
        if (Schema::hasTable('discovery_feedback_daily_metrics')) {
            return DB::table('discovery_feedback_daily_metrics')
                ->selectRaw('metric_date')
                ->selectRaw('surface')
                ->selectRaw('SUM(feedback_actions) AS feedback_actions')
                ->selectRaw('SUM(negative_feedback_actions) AS negative_feedback_actions')
                ->selectRaw('SUM(undo_actions) AS undo_actions')
                ->whereBetween('metric_date', [$from, $to])
                ->groupBy('metric_date', 'surface')
                ->orderBy('metric_date')
                ->get()
                ->map(fn ($row): array => [
                    'date' => (string) $row->metric_date,
                    'surface' => (string) ($row->surface ?? 'unknown'),
                    'feedback_actions' => (int) ($row->feedback_actions ?? 0),
                    'negative_feedback_actions' => (int) ($row->negative_feedback_actions ?? 0),
                    'undo_actions' => (int) ($row->undo_actions ?? 0),
                ])
                ->all();
        }

        $surfaceExpression = $this->surfaceExpression();

        return DB::table('user_discovery_events')
            ->selectRaw('event_date')
            ->selectRaw($surfaceExpression . ' AS surface')
            ->selectRaw("SUM(CASE WHEN event_type = 'favorite' THEN 1 ELSE 0 END) + SUM(CASE WHEN event_type = 'download' THEN 1 ELSE 0 END) AS feedback_actions")
            ->selectRaw("SUM(CASE WHEN event_type = 'hide_artwork' THEN 1 ELSE 0 END) + SUM(CASE WHEN event_type = 'dislike_tag' THEN 1 ELSE 0 END) AS negative_feedback_actions")
            ->selectRaw("SUM(CASE WHEN event_type = 'unhide_artwork' THEN 1 ELSE 0 END) + SUM(CASE WHEN event_type = 'undo_dislike_tag' THEN 1 ELSE 0 END) AS undo_actions")
            ->whereBetween('event_date', [$from, $to])
            ->groupBy('event_date', DB::raw($surfaceExpression))
            ->orderBy('event_date')
            ->get()
            ->map(fn ($row): array => [
                'date' => (string) $row->event_date,
                'surface' => (string) ($row->surface ?? 'unknown'),
                'feedback_actions' => (int) ($row->feedback_actions ?? 0),
                'negative_feedback_actions' => (int) ($row->negative_feedback_actions ?? 0),
                'undo_actions' => (int) ($row->undo_actions ?? 0),
            ])
            ->all();
    }

    /**
     * @return array<int, array<string, mixed>>
     */
    private function dailyAlgoSurfaceMetrics(string $from, string $to): array
    {
        if (Schema::hasTable('discovery_feedback_daily_metrics')) {
            return DB::table('discovery_feedback_daily_metrics')
                ->selectRaw('metric_date')
                ->selectRaw('algo_version')
                ->selectRaw('surface')
                ->selectRaw('SUM(feedback_actions) AS feedback_actions')
                ->selectRaw('SUM(negative_feedback_actions) AS negative_feedback_actions')
                ->selectRaw('SUM(undo_actions) AS undo_actions')
                ->whereBetween('metric_date', [$from, $to])
                ->groupBy('metric_date', 'algo_version', 'surface')
                ->orderBy('metric_date')
                ->get()
                ->map(fn ($row): array => [
                    'date' => (string) $row->metric_date,
                    'algo_version' => (string) ($row->algo_version ?? ''),
                    'surface' => (string) ($row->surface ?? 'unknown'),
                    'feedback_actions' => (int) ($row->feedback_actions ?? 0),
                    'negative_feedback_actions' => (int) ($row->negative_feedback_actions ?? 0),
                    'undo_actions' => (int) ($row->undo_actions ?? 0),
                ])
                ->all();
        }

        $surfaceExpression = $this->surfaceExpression();

        return DB::table('user_discovery_events')
            ->selectRaw('event_date')
            ->selectRaw('algo_version')
            ->selectRaw($surfaceExpression . ' AS surface')
            ->selectRaw("SUM(CASE WHEN event_type = 'favorite' THEN 1 ELSE 0 END) + SUM(CASE WHEN event_type = 'download' THEN 1 ELSE 0 END) AS feedback_actions")
            ->selectRaw("SUM(CASE WHEN event_type = 'hide_artwork' THEN 1 ELSE 0 END) + SUM(CASE WHEN event_type = 'dislike_tag' THEN 1 ELSE 0 END) AS negative_feedback_actions")
            ->selectRaw("SUM(CASE WHEN event_type = 'unhide_artwork' THEN 1 ELSE 0 END) + SUM(CASE WHEN event_type = 'undo_dislike_tag' THEN 1 ELSE 0 END) AS undo_actions")
            ->whereBetween('event_date', [$from, $to])
            ->groupBy('event_date', 'algo_version', DB::raw($surfaceExpression))
            ->orderBy('event_date')
            ->get()
            ->map(fn ($row): array => [
                'date' => (string) $row->event_date,
                'algo_version' => (string) ($row->algo_version ?? ''),
                'surface' => (string) ($row->surface ?? 'unknown'),
                'feedback_actions' => (int) ($row->feedback_actions ?? 0),
                'negative_feedback_actions' => (int) ($row->negative_feedback_actions ?? 0),
                'undo_actions' => (int) ($row->undo_actions ?? 0),
            ])
            ->all();
    }

    private function algoSurfaceTrendKey(string $algoVersion, string $surface): string
    {
        return $algoVersion . '|' . $surface;
    }

    /**
     * @param array<int, array<string, mixed>> $rows
     * @return array<int, array<string, mixed>>
     */
    private function sortRowsByTrendRisk(array $rows): array
    {
        usort($rows, function (array $left, array $right): int {
            $leftLevel = (string) ($left['trend']['overall_status']['level'] ?? 'neutral');
            $rightLevel = (string) ($right['trend']['overall_status']['level'] ?? 'neutral');

            $levelCompare = $this->trendLevelRank($leftLevel) <=> $this->trendLevelRank($rightLevel);
            if ($levelCompare !== 0) {
                return $levelCompare;
            }

            $leftScore = (int) ($left['trend']['overall_status']['score'] ?? 0);
            $rightScore = (int) ($right['trend']['overall_status']['score'] ?? 0);
            $scoreCompare = $leftScore <=> $rightScore;
            if ($scoreCompare !== 0) {
                return $scoreCompare;
            }

            $clickCompare = ((int) ($right['clicks'] ?? 0)) <=> ((int) ($left['clicks'] ?? 0));
            if ($clickCompare !== 0) {
                return $clickCompare;
            }

            return ((int) ($right['feedback_actions'] ?? 0)) <=> ((int) ($left['feedback_actions'] ?? 0));
        });

        return $rows;
    }

    private function trendLevelRank(string $level): int
    {
        return match ($level) {
            'risk' => 0,
            'watch' => 1,
            'healthy' => 2,
            default => 3,
        };
    }

    /**
     * @param object $row
     * @param array<string, mixed> $base
     * @return array<string, mixed>
     */
    private function formatEventSummaryRow(object $row, array $base): array
    {
        $views = (int) ($row->views ?? 0);
        $clicks = (int) ($row->clicks ?? 0);
        $favorites = (int) ($row->favorites ?? 0);
        $downloads = (int) ($row->downloads ?? 0);
        $hiddenArtworks = (int) ($row->hidden_artworks ?? 0);
        $dislikedTags = (int) ($row->disliked_tags ?? 0);
        $undoHiddenArtworks = (int) ($row->undo_hidden_artworks ?? 0);
        $undoDislikedTags = (int) ($row->undo_disliked_tags ?? 0);
        $feedbackActions = $favorites + $downloads;
        $negativeFeedbackActions = (int) ($row->negative_feedback_actions ?? ($hiddenArtworks + $dislikedTags));
        $undoActions = (int) ($row->undo_actions ?? ($undoHiddenArtworks + $undoDislikedTags));

        return array_merge($base, [
            'total_events' => (int) ($row->total_events ?? ($views + $clicks + $favorites + $downloads + $hiddenArtworks + $dislikedTags + $undoHiddenArtworks + $undoDislikedTags)),
            'unique_users' => (int) ($row->unique_users ?? 0),
            'unique_artworks' => (int) ($row->unique_artworks ?? 0),
            'views' => $views,
            'clicks' => $clicks,
            'favorites' => $favorites,
            'downloads' => $downloads,
            'feedback_actions' => $feedbackActions,
            'hidden_artworks' => $hiddenArtworks,
            'disliked_tags' => $dislikedTags,
            'negative_feedback_actions' => $negativeFeedbackActions,
            'undo_hidden_artworks' => $undoHiddenArtworks,
            'undo_disliked_tags' => $undoDislikedTags,
            'undo_actions' => $undoActions,
            'ctr' => round($views > 0 ? $clicks / $views : 0.0, 6),
            'favorite_rate_per_click' => round($clicks > 0 ? $favorites / $clicks : 0.0, 6),
            'download_rate_per_click' => round($clicks > 0 ? $downloads / $clicks : 0.0, 6),
            'feedback_rate_per_click' => round($clicks > 0 ? $feedbackActions / $clicks : 0.0, 6),
            'negative_feedback_rate_per_click' => round($clicks > 0 ? $negativeFeedbackActions / $clicks : 0.0, 6),
            'undo_rate_per_negative_feedback' => round($negativeFeedbackActions > 0 ? $undoActions / $negativeFeedbackActions : 0.0, 6),
        ]);
    }

    private function surfaceExpression(): string
    {
        if (DB::connection()->getDriverName() === 'sqlite') {
            return "COALESCE(NULLIF(JSON_EXTRACT(meta, '$.gallery_type'), ''), NULLIF(JSON_EXTRACT(meta, '$.surface'), ''), 'unknown')";
        }

        return "COALESCE(NULLIF(JSON_UNQUOTE(JSON_EXTRACT(meta, '$.gallery_type')), ''), NULLIF(JSON_UNQUOTE(JSON_EXTRACT(meta, '$.surface')), ''), 'unknown')";
    }

    private function emptyOverview(): array
    {
        return [
            'total_events' => 0,
            'unique_users' => 0,
            'unique_artworks' => 0,
            'views' => 0,
            'clicks' => 0,
            'favorites' => 0,
            'downloads' => 0,
            'feedback_actions' => 0,
            'hidden_artworks' => 0,
            'disliked_tags' => 0,
            'negative_feedback_actions' => 0,
            'undo_hidden_artworks' => 0,
            'undo_disliked_tags' => 0,
            'undo_actions' => 0,
            'ctr' => 0.0,
            'favorite_rate_per_click' => 0.0,
            'download_rate_per_click' => 0.0,
            'feedback_rate_per_click' => 0.0,
            'negative_feedback_rate_per_click' => 0.0,
            'undo_rate_per_negative_feedback' => 0.0,
        ];
    }

    private function latestAggregatedDate(): ?string
    {
        if (! Schema::hasTable('discovery_feedback_daily_metrics')) {
            return null;
        }

        $date = DB::table('discovery_feedback_daily_metrics')->max('metric_date');

        return $date ? (string) $date : null;
    }

    /**
     * @param array<int, array<string, mixed>> $dailyFeedback
     * @return array<string, mixed>
     */
    private function trendSummary(array $dailyFeedback): array
    {
        if ($dailyFeedback === []) {
            return $this->emptyTrendSummary();
        }

        $latest = $dailyFeedback[array_key_last($dailyFeedback)] ?? null;
        $previous = count($dailyFeedback) > 1 ? $dailyFeedback[count($dailyFeedback) - 2] : null;
        $recentSeven = array_slice($dailyFeedback, -7);

        return [
            'latest_day' => $latest,
            'previous_day' => $previous,
            'rolling_7d_average' => [
                'views' => $this->averageFromRows($recentSeven, 'views'),
                'clicks' => $this->averageFromRows($recentSeven, 'clicks'),
                'feedback_actions' => $this->averageFromRows($recentSeven, 'feedback_actions'),
                'negative_feedback_actions' => $this->averageFromRows($recentSeven, 'negative_feedback_actions'),
                'undo_actions' => $this->averageFromRows($recentSeven, 'undo_actions'),
            ],
            'deltas' => [
                'feedback_actions' => $this->formatTrendDelta($latest, $previous, 'feedback_actions', 'up'),
                'negative_feedback_actions' => $this->formatTrendDelta($latest, $previous, 'negative_feedback_actions', 'down'),
                'undo_actions' => $this->formatTrendDelta($latest, $previous, 'undo_actions', 'up'),
            ],
            'overall_status' => $this->overallTrendStatus($latest, $previous),
        ];
    }

    /**
     * @param array<string, mixed>|null $latest
     * @param array<string, mixed>|null $previous
     * @return array<string, mixed>
     */
    private function overallTrendStatus(?array $latest, ?array $previous): array
    {
        if ($previous === null) {
            return [
                'level' => 'neutral',
                'label' => 'No prior day',
                'reason' => 'A second day of data is required to judge trend health.',
                'score' => 0,
            ];
        }

        $feedbackDelta = $this->formatTrendDelta($latest, $previous, 'feedback_actions', 'up');
        $negativeDelta = $this->formatTrendDelta($latest, $previous, 'negative_feedback_actions', 'down');
        $undoDelta = $this->formatTrendDelta($latest, $previous, 'undo_actions', 'up');

        $score = 0;
        $score += $feedbackDelta['status'] === 'improved' ? 2 : ($feedbackDelta['status'] === 'worse' ? -2 : 0);
        $score += $negativeDelta['status'] === 'improved' ? 2 : ($negativeDelta['status'] === 'worse' ? -2 : 0);
        $score += $undoDelta['status'] === 'improved' ? 1 : ($undoDelta['status'] === 'worse' ? -1 : 0);

        if ($score >= 3) {
            return [
                'level' => 'healthy',
                'label' => 'Healthy',
                'reason' => 'Positive signals are improving faster than negative feedback.',
                'score' => $score,
            ];
        }

        if ($score <= -2) {
            return [
                'level' => 'risk',
                'label' => 'Risk',
                'reason' => 'Negative feedback is worsening or positive engagement is slipping.',
                'score' => $score,
            ];
        }

        return [
            'level' => 'watch',
            'label' => 'Watch',
            'reason' => 'Signals are mixed and worth monitoring.',
            'score' => $score,
        ];
    }

    /**
     * @param array<int, array<string, mixed>> $rows
     */
    private function averageFromRows(array $rows, string $key): float
    {
        if ($rows === []) {
            return 0.0;
        }

        $sum = array_sum(array_map(static fn (array $row): int => (int) ($row[$key] ?? 0), $rows));

        return round($sum / count($rows), 2);
    }

    /**
     * @param array<string, mixed>|null $latest
     * @param array<string, mixed>|null $previous
     * @return array<string, mixed>
     */
    private function formatTrendDelta(?array $latest, ?array $previous, string $key, string $goodDirection): array
    {
        $latestValue = (int) ($latest[$key] ?? 0);
        if ($previous === null) {
            return [
                'value' => 0,
                'direction' => 'flat',
                'status' => 'neutral',
                'label' => 'No prior day',
            ];
        }

        $delta = $latestValue - (int) ($previous[$key] ?? 0);
        if ($delta === 0) {
            return [
                'value' => 0,
                'direction' => 'flat',
                'status' => 'neutral',
                'label' => 'Flat',
            ];
        }

        $improved = $goodDirection === 'down' ? $delta < 0 : $delta > 0;

        return [
            'value' => $delta,
            'direction' => $delta > 0 ? 'up' : 'down',
            'status' => $improved ? 'improved' : 'worse',
            'label' => sprintf('%s %s%s vs prev day', $improved ? 'Improved' : 'Worse', $delta > 0 ? '+' : '', number_format($delta)),
        ];
    }

    /**
     * @return array<string, mixed>
     */
    private function emptyTrendSummary(): array
    {
        return [
            'latest_day' => null,
            'previous_day' => null,
            'rolling_7d_average' => [
                'views' => 0.0,
                'clicks' => 0.0,
                'feedback_actions' => 0.0,
                'negative_feedback_actions' => 0.0,
                'undo_actions' => 0.0,
            ],
            'deltas' => [
                'feedback_actions' => ['value' => 0, 'direction' => 'flat', 'status' => 'neutral', 'label' => 'No prior day'],
                'negative_feedback_actions' => ['value' => 0, 'direction' => 'flat', 'status' => 'neutral', 'label' => 'No prior day'],
                'undo_actions' => ['value' => 0, 'direction' => 'flat', 'status' => 'neutral', 'label' => 'No prior day'],
            ],
            'overall_status' => [
                'level' => 'neutral',
                'label' => 'No prior day',
                'reason' => 'A second day of data is required to judge trend health.',
                'score' => 0,
            ],
        ];
    }

    /**
     * @return array<string, mixed>
     */
    private function emptySurfaceTrend(): array
    {
        return [
            'latest_day' => null,
            'previous_day' => null,
            'overall_status' => [
                'level' => 'neutral',
                'label' => 'No prior day',
                'reason' => 'A second day of data is required to judge trend health.',
                'score' => 0,
            ],
            'deltas' => [
                'feedback_actions' => ['value' => 0, 'direction' => 'flat', 'status' => 'neutral', 'label' => 'No prior day'],
                'negative_feedback_actions' => ['value' => 0, 'direction' => 'flat', 'status' => 'neutral', 'label' => 'No prior day'],
                'undo_actions' => ['value' => 0, 'direction' => 'flat', 'status' => 'neutral', 'label' => 'No prior day'],
            ],
        ];
    }
}