<?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
{
    public function up()
    {
        Schema::table('orders', function (Blueprint $table) {
            // Critical indexes for filtering and sorting performance

            // Date-based filtering (most common dashboard queries)
            $table->index('date_created', 'idx_orders_date_created');
            $table->index('date_modified', 'idx_orders_date_modified');

            // Combined indexes for common filter combinations
            $table->index(['status', 'date_created'], 'idx_orders_status_date_created');
            $table->index(['payment_method', 'status'], 'idx_orders_payment_status');
            $table->index(['store_id', 'date_created'], 'idx_orders_store_date_created');
            $table->index(['store_id', 'status', 'date_created'], 'idx_orders_store_status_date');

            // Payment method filtering (COD vs non-COD separation)
            $table->index(['payment_method', 'date_created'], 'idx_orders_payment_date');

            // Currency grouping for dashboard stats
            $table->index(['currency', 'status'], 'idx_orders_currency_status');

            // Total field for revenue calculations
            $table->index(['status', 'total'], 'idx_orders_status_total');

            // Search optimization
            $table->index('order_number', 'idx_orders_order_number');

            // JSON field optimization for email/phone search
            // Note: Functional indexes only work on MySQL 8.0+, not MariaDB
            // Skip for MariaDB compatibility
            $pdo = DB::connection()->getPdo();
            $version = $pdo->getAttribute(\PDO::ATTR_SERVER_VERSION);
            $isMySql8Plus = strpos($version, 'MariaDB') === false && version_compare($version, '8.0.0', '>=');

            if ($isMySql8Plus) {
                // These will help with JSON searches in billing data (MySQL 8.0+ only)
                try {
                    DB::statement('CREATE INDEX idx_orders_billing_email ON orders ((CAST(JSON_UNQUOTE(JSON_EXTRACT(billing, "$.email")) AS CHAR(255))))');
                    DB::statement('CREATE INDEX idx_orders_billing_phone ON orders ((CAST(JSON_UNQUOTE(JSON_EXTRACT(billing, "$.phone")) AS CHAR(50))))');
                } catch (\Exception $e) {
                    // Skip if not supported
                }
            }
        });

        Schema::table('stores', function (Blueprint $table) {
            // Store performance indexes
            $table->index(['is_active', 'last_sync'], 'idx_stores_active_sync');
        });

        // Create order_logs indexes if table exists
        if (Schema::hasTable('order_logs')) {
            Schema::table('order_logs', function (Blueprint $table) {
                $table->index(['order_id', 'created_at'], 'idx_order_logs_order_created');
                $table->index('user_id', 'idx_order_logs_user');
            });
        }
    }

    public function down()
    {
        Schema::table('orders', function (Blueprint $table) {
            // Drop custom indexes
            $table->dropIndex('idx_orders_date_created');
            $table->dropIndex('idx_orders_date_modified');
            $table->dropIndex('idx_orders_status_date_created');
            $table->dropIndex('idx_orders_payment_status');
            $table->dropIndex('idx_orders_store_date_created');
            $table->dropIndex('idx_orders_store_status_date');
            $table->dropIndex('idx_orders_payment_date');
            $table->dropIndex('idx_orders_currency_status');
            $table->dropIndex('idx_orders_status_total');
            $table->dropIndex('idx_orders_order_number');
        });

        Schema::table('stores', function (Blueprint $table) {
            $table->dropIndex('idx_stores_active_sync');
        });

        if (Schema::hasTable('order_logs')) {
            Schema::table('order_logs', function (Blueprint $table) {
                $table->dropIndex('idx_order_logs_order_created');
                $table->dropIndex('idx_order_logs_user');
            });
        }

        // Drop JSON indexes if they exist (MySQL 8.0+ only)
        try {
            DB::statement('DROP INDEX idx_orders_billing_email ON orders');
        } catch (\Exception $e) {
            // Index might not exist, ignore error
        }

        try {
            DB::statement('DROP INDEX idx_orders_billing_phone ON orders');
        } catch (\Exception $e) {
            // Index might not exist, ignore error
        }
    }
};