<?php

namespace App\Console\Commands;

use App\Models\PressRelease;
use App\Scopes\PortalScope;
use App\Services\PressRelease\CanonicalAssigner;
use Illuminate\Console\Command;
use Illuminate\Support\Facades\DB;

/**
 * Doppelveröffentlichung erheben (Anhang 2, 1c.7): wie viele Meldungen
 * liegen mit identischem Text derselben Firma auf beiden Portalen — gesamt,
 * seit dem neuen System, je Hauptkategorie, mit und ohne Zuordnung. Dazu
 * die Meldungen mit portal=both, die dieselbe Frage auf anderem Weg stellen.
 */
class ReportDuplicatePressReleases extends Command
{
    protected $signature = 'press-releases:duplicates-report';

    protected $description = 'Zählt identische Pressemitteilungen auf beiden Portalen (Doppelveröffentlichung).';

    public function handle(CanonicalAssigner $assigner): int
    {
        $pairs = $assigner->pairsQuery();

        $total = (clone $pairs)->count();
        $recent = (clone $pairs)->where('a.created_at', '>=', '2026-07-01')->count();
        $assigned = (clone $pairs)->where(fn ($q) => $q->whereColumn('a.canonical_press_release_id', '=', 'b.id')->orWhereColumn('b.canonical_press_release_id', '=', 'a.id'))->count();
        $unhashed = PressRelease::query()->withoutGlobalScope(PortalScope::class)->whereNull('content_hash')->count();

        $visible = PressRelease::query()->withoutGlobalScope(PortalScope::class)->visibleInArchive()
            ->selectRaw('portal, count(*) as total')->groupBy('portal')->toBase()->pluck('total', 'portal');

        $this->info('Doppelveröffentlichung — Stand '.now()->format('d.m.Y H:i'));
        $this->newLine();
        $this->line(sprintf('Sichtbare Meldungen: presseecho %s, businessportal24 %s, both %s',
            number_format((int) ($visible['presseecho'] ?? 0), 0, ',', '.'),
            number_format((int) ($visible['businessportal24'] ?? 0), 0, ',', '.'),
            number_format((int) ($visible['both'] ?? 0), 0, ',', '.')));
        $this->line(sprintf('Paare gleichen Inhalts auf beiden Portalen: %s (%s URLs)', number_format($total, 0, ',', '.'), number_format($total * 2, 0, ',', '.')));
        $this->line(sprintf('  davon seit 07/2026 (neues System): %s', number_format($recent, 0, ',', '.')));
        $this->line(sprintf('  davon mit kanonischer Zuordnung: %s', number_format($assigned, 0, ',', '.')));
        $this->line(sprintf('Meldungen ohne Prüfsumme: %s%s', number_format($unhashed, 0, ',', '.'), $unhashed > 0 ? ' — erst press-releases:hash-content laufen lassen' : ''));
        $this->line(sprintf('Zuordnung (config canonical.enabled): %s', $assigner->enabled() ? 'aktiv' : 'aus'));
        $this->newLine();

        $byCategory = (clone $pairs)
            ->leftJoin('categories as c', 'c.id', '=', 'a.category_id')
            ->leftJoin('categories as root', 'root.id', '=', DB::raw('coalesce(c.parent_id, c.id)'))
            ->leftJoin('category_translations as t', fn ($join) => $join->on('t.category_id', '=', 'root.id')->where('t.locale', '=', 'de'))
            ->select(DB::raw('root.id as root_id, t.slug as slug, count(*) as pairs'))
            ->groupBy('root.id', 't.slug')
            ->orderByDesc('pairs')
            ->get();

        $rows = $byCategory->map(function (object $row) use ($assigner): array {
            $slug = (string) ($row->slug ?? '—');
            $portal = $row->root_id !== null ? $assigner->preferredPortalForCategory((int) $row->root_id) : null;

            return [
                $slug,
                number_format((int) $row->pairs, 0, ',', '.'),
                $portal !== null ? $portal->label() : 'Fallback ('.config('canonical.fallback', 'first').')',
            ];
        })->all();

        $this->table(['Hauptkategorie', 'Paare', 'Erstquelle laut Zuordnung (E9)'], $rows);

        return self::SUCCESS;
    }
}