<?php

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

return new class extends Migration {
    public function up(): void
    {
        if (! Schema::hasTable('artworks')) {
            return;
        }

        if (DB::getDriverName() === 'sqlite') {
            DB::statement('DROP INDEX IF EXISTS artworks_slug_unique');
            DB::statement('DROP INDEX IF EXISTS artworks_slug');
            DB::statement('CREATE INDEX IF NOT EXISTS artworks_slug_index ON artworks (slug)');

            $this->normalizeArtworkSlugs();

            return;
        }

        Schema::table('artworks', function (Blueprint $table) {
            if ($this->indexExists('artworks', 'artworks_slug_unique')) {
                $table->dropUnique('artworks_slug_unique');
            }

            if (! $this->indexExists('artworks', 'artworks_slug_index') && ! $this->indexExists('artworks', 'artworks_slug')) {
                $table->index('slug');
            }
        });

        $this->normalizeArtworkSlugs();
    }

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

        if (DB::getDriverName() === 'sqlite') {
            DB::statement('DROP INDEX IF EXISTS artworks_slug_index');
            DB::statement('CREATE UNIQUE INDEX IF NOT EXISTS artworks_slug_unique ON artworks (slug)');

            return;
        }

        Schema::table('artworks', function (Blueprint $table) {
            if ($this->indexExists('artworks', 'artworks_slug_index')) {
                $table->dropIndex('artworks_slug_index');
            } elseif ($this->indexExists('artworks', 'artworks_slug')) {
                $table->dropIndex('artworks_slug');
            }

            if (! $this->indexExists('artworks', 'artworks_slug_unique')) {
                $table->unique('slug');
            }
        });
    }

    private function indexExists(string $tableName, string $indexName): bool
    {
        $driver = DB::getDriverName();

        if ($driver === 'mysql') {
            return DB::table('information_schema.statistics')
                ->where('table_schema', DB::getDatabaseName())
                ->where('table_name', $tableName)
                ->where('index_name', $indexName)
                ->exists();
        }

        if ($driver === 'pgsql') {
            return DB::table('pg_indexes')
                ->where('schemaname', 'public')
                ->where('tablename', $tableName)
                ->where('indexname', $indexName)
                ->exists();
        }

        return false;
    }

    private function normalizeArtworkSlugs(): void
    {
        DB::table('artworks')
            ->select(['id', 'title'])
            ->orderBy('id')
            ->chunkById(500, function (Collection $artworks): void {
                foreach ($artworks as $artwork) {
                    $slug = Str::limit(Str::slug((string) ($artwork->title ?? '')) ?: 'artwork', 160, '');

                    DB::table('artworks')
                        ->where('id', $artwork->id)
                        ->update(['slug' => $slug]);
                }
            });
    }
};