<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

return new class extends Migration
{
    /**
     * Run the migrations.
     */
    public function up(): void
    {
        // Products table indexes
        Schema::table('products', function (Blueprint $table) {
            // Composite index for homepage featured products query
            $table->index(['is_active', 'is_featured'], 'idx_products_active_featured');
            
            // Composite index for in-stock active products (only if current_stock exists)
            if (Schema::hasColumn('products', 'current_stock')) {
                $table->index(['is_active', 'current_stock'], 'idx_products_active_stock');
            }
            
            // Index for price range queries
            $table->index('price', 'idx_products_price');
        });

        // Orders table indexes
        Schema::table('orders', function (Blueprint $table) {
            // Composite index for admin order filtering
            $table->index(['status', 'payment_status'], 'idx_orders_status_payment');
            
            // Index for date-based queries
            $table->index('placed_at', 'idx_orders_placed_at');
            
            // Composite index for user order history
            $table->index(['user_id', 'status'], 'idx_orders_user_status');
            
            // Index for payment status queries
            $table->index('payment_status', 'idx_orders_payment_status');
        });

        // Cart items table indexes
        Schema::table('cart_items', function (Blueprint $table) {
            // Composite index for cart item lookups (prevents duplicates)
            $table->unique(['cart_id', 'product_id'], 'idx_cart_items_cart_product_unique');
        });

        // Categories table indexes
        Schema::table('categories', function (Blueprint $table) {
            // Composite index for active category queries
            $table->index(['parent_id', 'is_active'], 'idx_categories_parent_active');
            
            // Composite index for sorted category queries
            $table->index(['is_active', 'sort_order'], 'idx_categories_active_sort');
        });

        // Stock reservations table indexes (already added in migration, but ensure they exist)
        if (Schema::hasTable('stock_reservations')) {
            Schema::table('stock_reservations', function (Blueprint $table) {
                // Ensure composite index exists for product status queries
                // Skip index check for SQLite (used in tests)
                if (config('database.default') !== 'sqlite' && !$this->indexExists('stock_reservations', 'idx_stock_reservations_product_status')) {
                    $table->index(['product_id', 'status'], 'idx_stock_reservations_product_status');
                } elseif (config('database.default') === 'sqlite') {
                    // For SQLite, just add the index (will fail gracefully if exists)
                    try {
                        $table->index(['product_id', 'status'], 'idx_stock_reservations_product_status');
                    } catch (\Exception $e) {
                        // Index might already exist, ignore
                    }
                }
            });
        }
    }

    /**
     * Reverse the migrations.
     */
    public function down(): void
    {
        Schema::table('products', function (Blueprint $table) {
            $table->dropIndex('idx_products_active_featured');
            if (Schema::hasColumn('products', 'current_stock')) {
                $table->dropIndex('idx_products_active_stock');
            }
            $table->dropIndex('idx_products_price');
        });

        Schema::table('orders', function (Blueprint $table) {
            $table->dropIndex('idx_orders_status_payment');
            $table->dropIndex('idx_orders_placed_at');
            $table->dropIndex('idx_orders_user_status');
            $table->dropIndex('idx_orders_payment_status');
        });

        Schema::table('cart_items', function (Blueprint $table) {
            $table->dropUnique('idx_cart_items_cart_product_unique');
        });

        Schema::table('categories', function (Blueprint $table) {
            $table->dropIndex('idx_categories_parent_active');
            $table->dropIndex('idx_categories_active_sort');
        });

        if (Schema::hasTable('stock_reservations')) {
            Schema::table('stock_reservations', function (Blueprint $table) {
                // Skip index check for SQLite
                if (config('database.default') !== 'sqlite' && $this->indexExists('stock_reservations', 'idx_stock_reservations_product_status')) {
                    $table->dropIndex('idx_stock_reservations_product_status');
                } elseif (config('database.default') === 'sqlite') {
                    // For SQLite, try to drop (will fail gracefully if doesn't exist)
                    try {
                        $table->dropIndex('idx_stock_reservations_product_status');
                    } catch (\Exception $e) {
                        // Index might not exist, ignore
                    }
                }
            });
        }
    }

    /**
     * Check if an index exists on a table
     * Note: SQLite doesn't support information_schema, so this only works for MySQL/PostgreSQL
     */
    protected function indexExists(string $table, string $index): bool
    {
        $connection = Schema::getConnection();
        $driver = $connection->getDriverName();
        
        // SQLite doesn't have information_schema
        if ($driver === 'sqlite') {
            return false; // Always return false for SQLite, let the migration handle it
        }
        
        $databaseName = $connection->getDatabaseName();
        
        try {
            $result = $connection->select(
                "SELECT COUNT(*) as count FROM information_schema.statistics 
                 WHERE table_schema = ? AND table_name = ? AND index_name = ?",
                [$databaseName, $table, $index]
            );
            
            return $result[0]->count > 0;
        } catch (\Exception $e) {
            return false;
        }
    }
};