<?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
    {
        // Update variant_stock_movements table to use product_id instead of variant_id
        if (Schema::hasTable('variant_stock_movements')) {
            // Check if variant_id column exists
            if (Schema::hasColumn('variant_stock_movements', 'variant_id')) {
                // Drop the old foreign key constraint if it exists
                try {
                    $foreignKeys = DB::select("
                        SELECT CONSTRAINT_NAME 
                        FROM information_schema.KEY_COLUMN_USAGE 
                        WHERE TABLE_SCHEMA = DATABASE() 
                        AND TABLE_NAME = 'variant_stock_movements' 
                        AND COLUMN_NAME = 'variant_id' 
                        AND REFERENCED_TABLE_NAME IS NOT NULL
                    ");
                    
                    if (!empty($foreignKeys)) {
                        $constraintName = $foreignKeys[0]->CONSTRAINT_NAME;
                        DB::statement("ALTER TABLE variant_stock_movements DROP FOREIGN KEY `{$constraintName}`");
                    }
                } catch (\Exception $e) {
                    // Foreign key might not exist, continue
                }
                
                // Drop the index if it exists
                try {
                    $indexes = DB::select("SHOW INDEX FROM variant_stock_movements WHERE Column_name = 'variant_id' AND Key_name != 'PRIMARY'");
                    if (!empty($indexes)) {
                        $indexName = $indexes[0]->Key_name;
                        DB::statement("ALTER TABLE variant_stock_movements DROP INDEX `{$indexName}`");
                    }
                } catch (\Exception $e) {
                    // Index might not exist, continue
                }
                
                // Check if product_id already exists
                if (!Schema::hasColumn('variant_stock_movements', 'product_id')) {
                    // Add new product_id column
                    Schema::table('variant_stock_movements', function (Blueprint $table) {
                        $table->unsignedBigInteger('product_id')->after('id');
                    });
                    
                    // Copy data from variant_id to product_id (assuming variants will be migrated to products)
                    // Note: This assumes variant data has been migrated to products table
                    \DB::statement('UPDATE variant_stock_movements SET product_id = variant_id');
                }
                
                // Drop old variant_id column
                Schema::table('variant_stock_movements', function (Blueprint $table) {
                    $table->dropColumn('variant_id');
                });
            }
            
            // Add new foreign key and index for product_id if they don't exist
            if (Schema::hasColumn('variant_stock_movements', 'product_id')) {
                // Check if foreign key exists
                $foreignKeyExists = false;
                try {
                    $constraints = DB::select("
                        SELECT CONSTRAINT_NAME 
                        FROM information_schema.KEY_COLUMN_USAGE 
                        WHERE TABLE_SCHEMA = DATABASE() 
                        AND TABLE_NAME = 'variant_stock_movements' 
                        AND COLUMN_NAME = 'product_id' 
                        AND REFERENCED_TABLE_NAME = 'products'
                    ");
                    $foreignKeyExists = !empty($constraints);
                } catch (\Exception $e) {
                    // If query fails, assume it doesn't exist
                }
                
                if (!$foreignKeyExists) {
                    try {
                        Schema::table('variant_stock_movements', function (Blueprint $table) {
                            $table->foreign('product_id')->references('id')->on('products')->onDelete('cascade');
                        });
                    } catch (\Exception $e) {
                        // Foreign key might already exist, continue
                    }
                }
                
                // Check if index exists
                $indexExists = false;
                try {
                    $indexes = DB::select("SHOW INDEX FROM variant_stock_movements WHERE Column_name = 'product_id' AND Key_name != 'PRIMARY'");
                    $indexExists = !empty($indexes);
                } catch (\Exception $e) {
                    // If query fails, assume it doesn't exist
                }
                
                if (!$indexExists) {
                    try {
                        Schema::table('variant_stock_movements', function (Blueprint $table) {
                            $table->index('product_id');
                        });
                    } catch (\Exception $e) {
                        // Index might already exist, continue
                    }
                }
            }
        }
    }

    /**
     * Reverse the migrations.
     */
    public function down(): void
    {
        if (Schema::hasTable('variant_stock_movements')) {
            // Drop the product foreign key
            Schema::table('variant_stock_movements', function (Blueprint $table) {
                $table->dropForeign(['product_id']);
            });
            
            // Drop the index
            Schema::table('variant_stock_movements', function (Blueprint $table) {
                $table->dropIndex(['product_id']);
            });
            
            // Add variant_id column back
            Schema::table('variant_stock_movements', function (Blueprint $table) {
                $table->unsignedBigInteger('variant_id')->after('id');
            });
            
            // Copy data back (if needed)
            \DB::statement('UPDATE variant_stock_movements SET variant_id = product_id');
            
            // Drop product_id column
            Schema::table('variant_stock_movements', function (Blueprint $table) {
                $table->dropColumn('product_id');
            });
            
            // Add back the variant foreign key and index
            Schema::table('variant_stock_movements', function (Blueprint $table) {
                $table->foreign('variant_id')->references('id')->on('product_variants')->onDelete('cascade');
                $table->index('variant_id');
            });
        }
    }
};