<?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 agregar columna feature_version_id (temporalmente nullable)
        Schema::table('features', function (Blueprint $table) {
            $table->foreignId('feature_version_id')
                ->nullable()
                ->after('product_id')
                ->constrained('feature_versions')
                ->cascadeOnUpdate()
                ->cascadeOnDelete();
        });

        // Crear versiones por defecto para cada producto que tenga features
        $productsWithFeatures = DB::table('features')
            ->select('product_id', DB::raw('MIN(created_at) as created_at'), DB::raw('MIN(created_by) as created_by'))
            ->groupBy('product_id')
            ->get();

        foreach ($productsWithFeatures as $productData) {
            // Crear una versión por defecto para cada producto
            $featureVersionId = DB::table('feature_versions')->insertGetId([
                'product_id' => $productData->product_id,
                'version' => '2026010101', // Versión por defecto
                'description' => 'Versión inicial migrada',
                'created_by' => $productData->created_by,
                'updated_by' => $productData->created_by,
                'created_at' => $productData->created_at ?? now(),
                'updated_at' => $productData->created_at ?? now(),
            ]);

            // Asignar todas las features de este producto a esta versión
            DB::table('features')
                ->where('product_id', $productData->product_id)
                ->update(['feature_version_id' => $featureVersionId]);
        }

        // Hacer feature_version_id NOT NULL solo si hay features
        if ($productsWithFeatures->isNotEmpty()) {
            DB::statement('ALTER TABLE features MODIFY feature_version_id BIGINT UNSIGNED NOT NULL');
        }

        // Eliminar foreign key e índices relacionados con product_id
        Schema::table('features', function (Blueprint $table) {
            $table->dropForeign(['product_id']);
        });

        // Obtener nombres reales de los índices de la tabla y eliminar los relacionados con product_id
        $indexes = DB::select("SHOW INDEX FROM features 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
        foreach ($indexGroups as $indexName => $columns) {
            if (in_array('product_id', $columns)) {
                try {
                    DB::statement("ALTER TABLE features DROP INDEX `{$indexName}`");
                } catch (\Exception $e) {
                    // El índice ya no existe, continuar
                }
            }
        }

        // Eliminar columna product_id
        Schema::table('features', function (Blueprint $table) {
            $table->dropColumn('product_id');
        });

        // Agregar nuevos índices
        Schema::table('features', function (Blueprint $table) {
            $table->index(['feature_version_id', 'status']);
            $table->index(['feature_version_id']);
        });
    }

    /**
     * Reverse the migrations.
     */
    public function down(): void
    {
        Schema::table('features', function (Blueprint $table) {
            // Eliminar índices nuevos
            $indexes = DB::select("SHOW INDEX FROM features WHERE Key_name != 'PRIMARY'");
            foreach ($indexes as $index) {
                if (str_contains($index->Key_name, 'feature_version_id')) {
                    try {
                        DB::statement("ALTER TABLE features DROP INDEX `{$index->Key_name}`");
                    } catch (\Exception $e) {
                        // Continuar
                    }
                }
            }

            // Agregar columna product_id de vuelta
            $table->foreignId('product_id')
                ->after('id')
                ->constrained('products')
                ->cascadeOnUpdate()
                ->cascadeOnDelete();
        });

        // Migrar datos de vuelta
        DB::statement('UPDATE features
            INNER JOIN feature_versions ON features.feature_version_id = feature_versions.id
            SET features.product_id = feature_versions.product_id');

        Schema::table('features', function (Blueprint $table) {
            $table->foreignId('product_id')->nullable(false)->change();

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

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