<?php

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

return new class extends Migration
{
    /**
     * Run the migrations.
     */
    public function up(): void
    {
        // Primero, crear versiones para cada combinación única de product_id + version
        $uniqueVersions = DB::table('scss_files')
            ->select('product_id', 'version', DB::raw('MIN(created_at) as created_at'), DB::raw('MIN(created_by) as created_by'))
            ->groupBy('product_id', 'version')
            ->get();

        foreach ($uniqueVersions as $versionData) {
            DB::table('scss_versions')->insertGetId([
                'product_id' => $versionData->product_id,
                'version' => $versionData->version,
                'description' => null,
                'created_by' => $versionData->created_by,
                'updated_by' => $versionData->created_by,
                'created_at' => $versionData->created_at,
                'updated_at' => $versionData->created_at,
            ]);
        }

        // Agregar columna scss_version_id (temporalmente nullable)
        Schema::table('scss_files', function (Blueprint $table) {
            $table->foreignId('scss_version_id')
                ->nullable()
                ->after('product_id')
                ->constrained('scss_versions')
                ->cascadeOnUpdate()
                ->cascadeOnDelete();
        });

        // Migrar los datos: asignar scss_version_id a cada archivo
        foreach ($uniqueVersions as $versionData) {
            $scssVersionId = DB::table('scss_versions')
                ->where('product_id', $versionData->product_id)
                ->where('version', $versionData->version)
                ->value('id');

            if ($scssVersionId) {
                DB::table('scss_files')
                    ->where('product_id', $versionData->product_id)
                    ->where('version', $versionData->version)
                    ->update(['scss_version_id' => $scssVersionId]);
            }
        }

        // Primero eliminar la foreign key de product_id (esto puede eliminar automáticamente algunos índices)
        Schema::table('scss_files', function (Blueprint $table) {
            $table->dropForeign(['product_id']);
        });

        // Obtener nombres reales de los índices de la tabla y eliminar los relacionados con product_id/version.
        // Se usa Schema::getIndexes() en lugar de "SHOW INDEX", que solo existe en MySQL y hacía
        // imposible ejecutar la suite de tests sobre sqlite (ver known-issues MGR-019).
        foreach (Schema::getIndexes('scss_files') as $index) {
            if ($index['primary'] ?? false) {
                continue;
            }

            if (!in_array('product_id', $index['columns']) && !in_array('version', $index['columns'])) {
                continue;
            }

            try {
                Schema::table('scss_files', function (Blueprint $table) use ($index) {
                    $table->dropIndex($index['name']);
                });
            } catch (\Exception $e) {
                // El índice ya no existe (p. ej. lo arrastró el dropForeign anterior), continuar
            }
        }

        // Hacer scss_version_id NOT NULL
        Schema::table('scss_files', function (Blueprint $table) {
            $table->foreignId('scss_version_id')->nullable(false)->change();
        });

        // Eliminar columnas product_id y version
        Schema::table('scss_files', function (Blueprint $table) {
            $table->dropColumn(['product_id', 'version']);
        });

        // Agregar nuevos índices
        Schema::table('scss_files', function (Blueprint $table) {
            $table->unique(['scss_version_id', 'filename']);
            $table->index(['scss_version_id']);
        });
    }

    /**
     * Reverse the migrations.
     */
    public function down(): void
    {
        Schema::table('scss_files', function (Blueprint $table) {
            // Eliminar índices nuevos
            $table->dropUnique(['scss_version_id', 'filename']);
            $table->dropIndex(['scss_version_id']);

            // Agregar columnas de vuelta
            $table->foreignId('product_id')
                ->after('id')
                ->constrained('products')
                ->cascadeOnUpdate()
                ->cascadeOnDelete();
            $table->string('version', 10)->after('product_id');
        });

        // Migrar datos de vuelta
        DB::statement('UPDATE scss_files
            INNER JOIN scss_versions ON scss_files.scss_version_id = scss_versions.id
            SET scss_files.product_id = scss_versions.product_id,
                scss_files.version = scss_versions.version');

        Schema::table('scss_files', function (Blueprint $table) {
            $table->foreignId('product_id')->nullable(false)->change();
            $table->string('version', 10)->nullable(false)->change();

            // Eliminar scss_version_id
            $table->dropForeign(['scss_version_id']);
            $table->dropColumn('scss_version_id');

            // Restaurar índices antiguos
            $table->unique(['product_id', 'version', 'filename']);
            $table->index(['product_id', 'version']);
        });
    }
};