<?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.
        // 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('features') as $index) {
            if ($index['primary'] ?? false) {
                continue;
            }

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

            try {
                Schema::table('features', 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
            }
        }

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