<?php

declare(strict_types=1);

namespace App\Services\Analytics;

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

final class TagInteractionReportService
{
    public function buildReport(string $from, string $to, int $limit = 20): array
    {
        return [
            'overview' => $this->overview($from, $to),
            'daily_clicks' => $this->dailyClicks($from, $to),
            'by_surface' => $this->bySurface($from, $to),
            'top_tags' => $this->topTags($from, $to, $limit),
            'top_queries' => $this->topQueries($from, $to, $limit),
            'top_transitions' => $this->topTransitions($from, $to, $limit),
            'latest_aggregated_date' => $this->latestAggregatedDate(),
        ];
    }

    private function overview(string $from, string $to): array
    {
        $row = DB::table('tag_interaction_events')
            ->selectRaw('COUNT(*) AS total_clicks')
            ->selectRaw('COUNT(DISTINCT user_id) AS unique_users')
            ->selectRaw("COUNT(DISTINCT CASE WHEN session_key IS NOT NULL AND session_key <> '' THEN session_key END) AS unique_sessions")
            ->selectRaw("COUNT(DISTINCT CASE WHEN tag_slug IS NOT NULL AND tag_slug <> '' THEN tag_slug END) AS distinct_tags")
            ->selectRaw('MAX(occurred_at) AS latest_event_at')
            ->whereBetween('event_date', [$from, $to])
            ->where('event_type', 'click')
            ->first();

        return [
            'total_clicks' => (int) ($row->total_clicks ?? 0),
            'unique_users' => (int) ($row->unique_users ?? 0),
            'unique_sessions' => (int) ($row->unique_sessions ?? 0),
            'distinct_tags' => (int) ($row->distinct_tags ?? 0),
            'latest_event_at' => $row->latest_event_at,
        ];
    }

    private function dailyClicks(string $from, string $to): array
    {
        if (Schema::hasTable('tag_interaction_daily_metrics')) {
            return DB::table('tag_interaction_daily_metrics')
                ->selectRaw('metric_date')
                ->selectRaw('SUM(clicks) AS clicks')
                ->whereBetween('metric_date', [$from, $to])
                ->groupBy('metric_date')
                ->orderBy('metric_date')
                ->get()
                ->map(static fn ($row): array => [
                    'date' => (string) $row->metric_date,
                    'clicks' => (int) ($row->clicks ?? 0),
                ])
                ->all();
        }

        return DB::table('tag_interaction_events')
            ->selectRaw('event_date')
            ->selectRaw('COUNT(*) AS clicks')
            ->whereBetween('event_date', [$from, $to])
            ->where('event_type', 'click')
            ->groupBy('event_date')
            ->orderBy('event_date')
            ->get()
            ->map(static fn ($row): array => [
                'date' => (string) $row->event_date,
                'clicks' => (int) ($row->clicks ?? 0),
            ])
            ->all();
    }

    private function bySurface(string $from, string $to): array
    {
        return DB::table('tag_interaction_events')
            ->selectRaw('surface')
            ->selectRaw('COUNT(*) AS clicks')
            ->selectRaw('COUNT(DISTINCT user_id) AS unique_users')
            ->selectRaw("COUNT(DISTINCT CASE WHEN session_key IS NOT NULL AND session_key <> '' THEN session_key END) AS unique_sessions")
            ->selectRaw('AVG(position) AS avg_position')
            ->whereBetween('event_date', [$from, $to])
            ->where('event_type', 'click')
            ->groupBy('surface')
            ->orderByDesc('clicks')
            ->get()
            ->map(static fn ($row): array => [
                'surface' => (string) $row->surface,
                'clicks' => (int) ($row->clicks ?? 0),
                'unique_users' => (int) ($row->unique_users ?? 0),
                'unique_sessions' => (int) ($row->unique_sessions ?? 0),
                'avg_position' => round((float) ($row->avg_position ?? 0), 2),
            ])
            ->all();
    }

    private function topTags(string $from, string $to, int $limit): array
    {
        return DB::table('tag_interaction_events')
            ->selectRaw('tag_slug')
            ->selectRaw('COUNT(*) AS clicks')
            ->selectRaw('COUNT(DISTINCT user_id) AS unique_users')
            ->selectRaw("COUNT(DISTINCT CASE WHEN session_key IS NOT NULL AND session_key <> '' THEN session_key END) AS unique_sessions")
            ->selectRaw("SUM(CASE WHEN surface IN ('related_chip', 'related_cluster', 'top_companion') THEN 1 ELSE 0 END) AS recommendation_clicks")
            ->selectRaw("SUM(CASE WHEN surface IN ('search_suggestion', 'rescue_suggestion') THEN 1 ELSE 0 END) AS search_clicks")
            ->whereBetween('event_date', [$from, $to])
            ->where('event_type', 'click')
            ->whereNotNull('tag_slug')
            ->where('tag_slug', '<>', '')
            ->groupBy('tag_slug')
            ->orderByDesc('clicks')
            ->limit($limit)
            ->get()
            ->map(static fn ($row): array => [
                'tag_slug' => (string) $row->tag_slug,
                'clicks' => (int) ($row->clicks ?? 0),
                'unique_users' => (int) ($row->unique_users ?? 0),
                'unique_sessions' => (int) ($row->unique_sessions ?? 0),
                'recommendation_clicks' => (int) ($row->recommendation_clicks ?? 0),
                'search_clicks' => (int) ($row->search_clicks ?? 0),
            ])
            ->all();
    }

    private function topQueries(string $from, string $to, int $limit): array
    {
        return DB::table('tag_interaction_events')
            ->selectRaw("LOWER(TRIM(query)) AS query")
            ->selectRaw('COUNT(*) AS clicks')
            ->selectRaw("COUNT(DISTINCT CASE WHEN session_key IS NOT NULL AND session_key <> '' THEN session_key END) AS unique_sessions")
            ->selectRaw("COUNT(DISTINCT CASE WHEN tag_slug IS NOT NULL AND tag_slug <> '' THEN tag_slug END) AS resolved_tags")
            ->whereBetween('event_date', [$from, $to])
            ->where('event_type', 'click')
            ->whereNotNull('query')
            ->whereRaw("TRIM(query) <> ''")
            ->groupBy(DB::raw("LOWER(TRIM(query))"))
            ->orderByDesc('clicks')
            ->limit($limit)
            ->get()
            ->map(static fn ($row): array => [
                'query' => (string) $row->query,
                'clicks' => (int) ($row->clicks ?? 0),
                'unique_sessions' => (int) ($row->unique_sessions ?? 0),
                'resolved_tags' => (int) ($row->resolved_tags ?? 0),
            ])
            ->all();
    }

    private function topTransitions(string $from, string $to, int $limit): array
    {
        return DB::table('tag_interaction_events')
            ->selectRaw('source_tag_slug')
            ->selectRaw('tag_slug')
            ->selectRaw('COUNT(*) AS clicks')
            ->selectRaw("COUNT(DISTINCT CASE WHEN session_key IS NOT NULL AND session_key <> '' THEN session_key END) AS unique_sessions")
            ->selectRaw('AVG(position) AS avg_position')
            ->whereBetween('event_date', [$from, $to])
            ->where('event_type', 'click')
            ->whereNotNull('source_tag_slug')
            ->whereNotNull('tag_slug')
            ->where('source_tag_slug', '<>', '')
            ->where('tag_slug', '<>', '')
            ->groupBy('source_tag_slug', 'tag_slug')
            ->orderByDesc('clicks')
            ->limit($limit)
            ->get()
            ->map(static fn ($row): array => [
                'source_tag_slug' => (string) $row->source_tag_slug,
                'tag_slug' => (string) $row->tag_slug,
                'clicks' => (int) ($row->clicks ?? 0),
                'unique_sessions' => (int) ($row->unique_sessions ?? 0),
                'avg_position' => round((float) ($row->avg_position ?? 0), 2),
            ])
            ->all();
    }

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

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

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