<?php

declare(strict_types=1);

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;

/**
 * Add a composite unique index on (article_id, ip) to news_views so that
 * duplicate view inserts at the DB level are impossible even if the session
 * guard is bypassed (e.g. server restart mid-request).
 *
 * A separate unique index on (article_id, user_id) is added for logged-in
 * users, skipping NULL user_ids so guest records don't conflict.
 */
return new class extends Migration
{
    public function up(): void
    {
        if (! Schema::hasTable('news_views')) {
            return;
        }

        $driver = DB::getDriverName();

        Schema::table('news_views', function (Blueprint $table) use ($driver): void {
            // De-duplicate existing rows before adding the index.
            // Keep only the earliest record per (article_id, ip) pair.
            if ($driver === 'sqlite') {
                DB::statement("
                    DELETE FROM news_views
                    WHERE ip IS NOT NULL
                      AND id IN (
                        SELECT nv1.id
                        FROM news_views nv1
                        INNER JOIN news_views nv2
                            ON  nv2.article_id = nv1.article_id
                            AND nv2.ip         = nv1.ip
                            AND nv2.id         < nv1.id
                        WHERE nv1.ip IS NOT NULL
                    )
                ");
            } else {
                DB::statement("
                    DELETE nv1 FROM news_views nv1
                    INNER JOIN news_views nv2
                        ON  nv2.article_id = nv1.article_id
                        AND nv2.ip         = nv1.ip
                        AND nv2.id         < nv1.id
                    WHERE nv1.ip IS NOT NULL
                ");
            }

            // De-duplicate by (article_id, user_id) for authenticated users.
            if ($driver === 'sqlite') {
                DB::statement("
                    DELETE FROM news_views
                    WHERE user_id IS NOT NULL
                      AND id IN (
                        SELECT nv1.id
                        FROM news_views nv1
                        INNER JOIN news_views nv2
                            ON  nv2.article_id = nv1.article_id
                            AND nv2.user_id    = nv1.user_id
                            AND nv2.id         < nv1.id
                        WHERE nv1.user_id IS NOT NULL
                    )
                ");
            } else {
                DB::statement("
                    DELETE nv1 FROM news_views nv1
                    INNER JOIN news_views nv2
                        ON  nv2.article_id = nv1.article_id
                        AND nv2.user_id    = nv1.user_id
                        AND nv2.id         < nv1.id
                    WHERE nv1.user_id IS NOT NULL
                ");
            }

            if (! $this->indexExists('news_views', 'news_views_article_ip_unique')) {
                $table->unique(['article_id', 'ip'], 'news_views_article_ip_unique');
            }

            if (! $this->indexExists('news_views', 'news_views_article_user_unique')) {
                $table->unique(['article_id', 'user_id'], 'news_views_article_user_unique');
            }
        });
    }

    public function down(): void
    {
        if (! Schema::hasTable('news_views')) {
            return;
        }

        Schema::table('news_views', function (Blueprint $table): void {
            if ($this->indexExists('news_views', 'news_views_article_ip_unique')) {
                $table->dropUnique('news_views_article_ip_unique');
            }

            if ($this->indexExists('news_views', 'news_views_article_user_unique')) {
                $table->dropUnique('news_views_article_user_unique');
            }
        });
    }

    private function indexExists(string $table, string $indexName): bool
    {
        if (DB::getDriverName() === 'sqlite') {
            return collect(DB::select("PRAGMA index_list('{$table}')"))
                ->contains(static fn (object $row): bool => ($row->name ?? null) === $indexName);
        }

        $indexes = DB::select("SHOW INDEX FROM `{$table}` WHERE Key_name = ?", [$indexName]);

        return count($indexes) > 0;
    }
};
