<?php

declare(strict_types=1);

namespace App\Console\Commands;

use Illuminate\Console\Command;
use Illuminate\Support\Facades\DB;

final class AggregateTagInteractionAnalyticsCommand extends Command
{
    protected $signature = 'analytics:aggregate-tag-interactions {--date= : Date (Y-m-d), defaults to yesterday}';

    protected $description = 'Aggregate tag interaction analytics into daily metrics by surface, tag, source tag, and query';

    public function handle(): int
    {
        $date = $this->option('date')
            ? (string) $this->option('date')
            : now()->subDay()->toDateString();

        $normalizedTag = "COALESCE(tag_slug, '')";
        $normalizedSourceTag = "COALESCE(source_tag_slug, '')";
        $normalizedQuery = "LOWER(TRIM(COALESCE(query, '')))";

        $rows = DB::table('tag_interaction_events')
            ->selectRaw('surface')
            ->selectRaw("{$normalizedTag} AS tag_slug")
            ->selectRaw("{$normalizedSourceTag} AS source_tag_slug")
            ->selectRaw("{$normalizedQuery} AS query")
            ->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')
            ->whereDate('event_date', $date)
            ->where('event_type', 'click')
            ->groupBy('surface', DB::raw($normalizedTag), DB::raw($normalizedSourceTag), DB::raw($normalizedQuery))
            ->get();

        DB::transaction(function () use ($date, $rows): void {
            DB::table('tag_interaction_daily_metrics')
                ->where('metric_date', $date)
                ->delete();

            $payload = $rows->map(static function ($row) use ($date): array {
                return [
                    'metric_date' => $date,
                    'surface' => (string) $row->surface,
                    'tag_slug' => trim((string) ($row->tag_slug ?? '')),
                    'source_tag_slug' => trim((string) ($row->source_tag_slug ?? '')),
                    'query' => trim((string) ($row->query ?? '')),
                    '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),
                    'created_at' => now(),
                    'updated_at' => now(),
                ];
            })->all();

            foreach (array_chunk($payload, 500) as $chunk) {
                if ($chunk !== []) {
                    DB::table('tag_interaction_daily_metrics')->insert($chunk);
                }
            }
        });

        $this->info("Aggregated tag interaction analytics for {$date}.");

        return self::SUCCESS;
    }
}
