<?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
        $indexes = DB::select("SHOW INDEX FROM scss_files WHERE Key_name != 'PRIMARY'");
        $indexGroups = [];

        // Agrupar índices por nombre
        foreach ($indexes as $index) {
            $indexName = $index->Key_name;
            if (!isset($indexGroups[$indexName])) {
                $indexGroups[$indexName] = [];
            }
            $indexGroups[$indexName][] = $index->Column_name;
        }

        // Eliminar índices que contengan product_id o version
        foreach ($indexGroups as $indexName => $columns) {
            if (in_array('product_id', $columns) || in_array('version', $columns)) {
                try {
                    DB::statement("ALTER TABLE scss_files DROP INDEX `{$indexName}`");
                } catch (\Exception $e) {
                    // El índice ya no existe, continuar
                }
            }
        }

        // Hacer scss_version_id NOT NULL
        DB::statement('ALTER TABLE scss_files MODIFY scss_version_id BIGINT UNSIGNED NOT NULL');

        // 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']);
        });
    }
};