<?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.
     * Adds performance indexes to module tables for optimized queries
     */
    public function up(): void
    {
        // ============================================
        // INVENTORY MODULE INDEXES
        // ============================================
        
        // inventory_items table
        if (Schema::hasTable('inventory_items')) {
            Schema::table('inventory_items', function (Blueprint $table) {
                // Branch + Category queries (common in stock lists)
                if (!$this->indexExists('inventory_items', 'inventory_items_branch_category_index')) {
                    $table->index(['branch_id', 'inventory_item_category_id'], 'inventory_items_branch_category_index');
                }
                // Branch queries (most common filter)
                if (!$this->indexExists('inventory_items', 'inventory_items_branch_id_index')) {
                    $table->index('branch_id', 'inventory_items_branch_id_index');
                }
                // Category queries
                if (!$this->indexExists('inventory_items', 'inventory_items_category_index')) {
                    $table->index('inventory_item_category_id', 'inventory_items_category_index');
                }
                // Supplier queries (purchase orders)
                if (!$this->indexExists('inventory_items', 'inventory_items_supplier_index')) {
                    $table->index('preferred_supplier_id', 'inventory_items_supplier_index');
                }
            });
        }

        // inventory_stocks table
        if (Schema::hasTable('inventory_stocks')) {
            Schema::table('inventory_stocks', function (Blueprint $table) {
                // Item + Branch (most common query for stock levels)
                if (!$this->indexExists('inventory_stocks', 'inventory_stocks_item_branch_index')) {
                    $table->index(['inventory_item_id', 'branch_id'], 'inventory_stocks_item_branch_index');
                }
                // Item queries
                if (!$this->indexExists('inventory_stocks', 'inventory_stocks_item_index')) {
                    $table->index('inventory_item_id', 'inventory_stocks_item_index');
                }
                // Branch queries
                if (!$this->indexExists('inventory_stocks', 'inventory_stocks_branch_index')) {
                    $table->index('branch_id', 'inventory_stocks_branch_index');
                }
            });
        }

        // inventory_movements table
        if (Schema::hasTable('inventory_movements')) {
            Schema::table('inventory_movements', function (Blueprint $table) {
                // Item + Date range queries (reports, history)
                if (!$this->indexExists('inventory_movements', 'inventory_movements_item_date_index')) {
                    $table->index(['inventory_item_id', 'created_at'], 'inventory_movements_item_date_index');
                }
                // Item queries
                if (!$this->indexExists('inventory_movements', 'inventory_movements_item_index')) {
                    $table->index('inventory_item_id', 'inventory_movements_item_index');
                }
                // Date range queries (reports)
                if (!$this->indexExists('inventory_movements', 'inventory_movements_created_at_index')) {
                    $table->index('created_at', 'inventory_movements_created_at_index');
                }
                // Order reference (order tracking)
                if (!$this->indexExists('inventory_movements', 'inventory_movements_order_index')) {
                    $table->index('order_id', 'inventory_movements_order_index');
                }
                // Branch queries
                if (!$this->indexExists('inventory_movements', 'inventory_movements_branch_index')) {
                    $table->index('branch_id', 'inventory_movements_branch_index');
                }
            });
        }

        // purchase_orders table
        if (Schema::hasTable('purchase_orders')) {
            Schema::table('purchase_orders', function (Blueprint $table) {
                // Status + Date (reporting, filtering)
                if (!$this->indexExists('purchase_orders', 'purchase_orders_status_date_index')) {
                    $table->index(['status', 'created_at'], 'purchase_orders_status_date_index');
                }
                // Branch queries
                if (!$this->indexExists('purchase_orders', 'purchase_orders_branch_index')) {
                    $table->index('branch_id', 'purchase_orders_branch_index');
                }
                // Supplier queries
                if (!$this->indexExists('purchase_orders', 'purchase_orders_supplier_index')) {
                    $table->index('supplier_id', 'purchase_orders_supplier_index');
                }
                // Date queries (reports)
                if (!$this->indexExists('purchase_orders', 'purchase_orders_created_at_index')) {
                    $table->index('created_at', 'purchase_orders_created_at_index');
                }
            });
        }

        // purchase_order_items table
        if (Schema::hasTable('purchase_order_items')) {
            Schema::table('purchase_order_items', function (Blueprint $table) {
                // Purchase order queries
                if (!$this->indexExists('purchase_order_items', 'purchase_order_items_po_index')) {
                    $table->index('purchase_order_id', 'purchase_order_items_po_index');
                }
                // Item queries
                if (!$this->indexExists('purchase_order_items', 'purchase_order_items_item_index')) {
                    $table->index('inventory_item_id', 'purchase_order_items_item_index');
                }
            });
        }

        // batch_stocks table
        if (Schema::hasTable('batch_stocks')) {
            Schema::table('batch_stocks', function (Blueprint $table) {
                // Batch + Expiry queries (expiry tracking)
                if (!$this->indexExists('batch_stocks', 'batch_stocks_batch_expiry_index')) {
                    $table->index(['batch_production_id', 'expiry_date'], 'batch_stocks_batch_expiry_index');
                }
                // Expiry queries (expiry alerts)
                if (!$this->indexExists('batch_stocks', 'batch_stocks_expiry_index')) {
                    $table->index('expiry_date', 'batch_stocks_expiry_index');
                }
                // Batch queries
                if (!$this->indexExists('batch_stocks', 'batch_stocks_batch_index')) {
                    $table->index('batch_production_id', 'batch_stocks_batch_index');
                }
            });
        }

        // recipes table
        if (Schema::hasTable('recipes')) {
            Schema::table('recipes', function (Blueprint $table) {
                // Menu item queries
                if (!$this->indexExists('recipes', 'recipes_menu_item_index')) {
                    $table->index('menu_item_id', 'recipes_menu_item_index');
                }
                // Inventory item queries
                if (!$this->indexExists('recipes', 'recipes_inventory_item_index')) {
                    $table->index('inventory_item_id', 'recipes_inventory_item_index');
                }
            });
        }

        // suppliers table
        if (Schema::hasTable('suppliers')) {
            Schema::table('suppliers', function (Blueprint $table) {
                // Restaurant queries (filtering)
                if (!$this->indexExists('suppliers', 'suppliers_restaurant_index')) {
                    $table->index('restaurant_id', 'suppliers_restaurant_index');
                }
                // Note: suppliers table doesn't have branch_id, only restaurant_id
            });
        }

        // ============================================
        // CASH REGISTER MODULE INDEXES
        // ============================================

        // cash_register_sessions table
        if (Schema::hasTable('cash_register_sessions')) {
            Schema::table('cash_register_sessions', function (Blueprint $table) {
                // Branch + Status queries (dashboard, reports)
                if (!$this->indexExists('cash_register_sessions', 'cash_register_sessions_branch_status_index')) {
                    $table->index(['branch_id', 'status'], 'cash_register_sessions_branch_status_index');
                }
                // Register queries
                if (!$this->indexExists('cash_register_sessions', 'cash_register_sessions_register_index')) {
                    $table->index('cash_register_id', 'cash_register_sessions_register_index');
                }
                // Closed date queries (reports)
                if (!$this->indexExists('cash_register_sessions', 'cash_register_sessions_closed_at_index')) {
                    $table->index('closed_at', 'cash_register_sessions_closed_at_index');
                }
                // Opened date queries
                if (!$this->indexExists('cash_register_sessions', 'cash_register_sessions_opened_at_index')) {
                    $table->index('opened_at', 'cash_register_sessions_opened_at_index');
                }
                // Opened by queries (user activity)
                if (!$this->indexExists('cash_register_sessions', 'cash_register_sessions_opened_by_index')) {
                    $table->index('opened_by', 'cash_register_sessions_opened_by_index');
                }
            });
        }

        // cash_register_transactions table
        if (Schema::hasTable('cash_register_transactions')) {
            Schema::table('cash_register_transactions', function (Blueprint $table) {
                // Session + Type queries (reports, calculations)
                if (!$this->indexExists('cash_register_transactions', 'cash_register_transactions_session_type_index')) {
                    $table->index(['cash_register_session_id', 'type'], 'cash_register_transactions_session_type_index');
                }
                // Session queries (most common)
                if (!$this->indexExists('cash_register_transactions', 'cash_register_transactions_session_index')) {
                    $table->index('cash_register_session_id', 'cash_register_transactions_session_index');
                }
                // Happened at queries (date range reports)
                if (!$this->indexExists('cash_register_transactions', 'cash_register_transactions_happened_at_index')) {
                    $table->index('happened_at', 'cash_register_transactions_happened_at_index');
                }
                // Type queries (filtering by transaction type)
                if (!$this->indexExists('cash_register_transactions', 'cash_register_transactions_type_index')) {
                    $table->index('type', 'cash_register_transactions_type_index');
                }
                // Order reference (order tracking)
                if (!$this->indexExists('cash_register_transactions', 'cash_register_transactions_order_index')) {
                    $table->index('order_id', 'cash_register_transactions_order_index');
                }
                // Branch queries
                if (!$this->indexExists('cash_register_transactions', 'cash_register_transactions_branch_index')) {
                    $table->index('branch_id', 'cash_register_transactions_branch_index');
                }
            });
        }

        // cash_register_counts table
        if (Schema::hasTable('cash_register_counts')) {
            Schema::table('cash_register_counts', function (Blueprint $table) {
                // Session queries (denomination counts)
                if (!$this->indexExists('cash_register_counts', 'cash_register_counts_session_index')) {
                    $table->index('cash_register_session_id', 'cash_register_counts_session_index');
                }
                // Denomination queries
                if (!$this->indexExists('cash_register_counts', 'cash_register_counts_denomination_index')) {
                    $table->index('cash_denomination_id', 'cash_register_counts_denomination_index');
                }
            });
        }

        // cash_registers table
        if (Schema::hasTable('cash_registers')) {
            Schema::table('cash_registers', function (Blueprint $table) {
                // Branch queries
                if (!$this->indexExists('cash_registers', 'cash_registers_branch_index')) {
                    $table->index('branch_id', 'cash_registers_branch_index');
                }
                // Restaurant queries
                if (!$this->indexExists('cash_registers', 'cash_registers_restaurant_index')) {
                    $table->index('restaurant_id', 'cash_registers_restaurant_index');
                }
            });
        }

        // ============================================
        // GENERAL/CORE TABLE INDEXES
        // ============================================

        // orders table (if exists and not already indexed)
        if (Schema::hasTable('orders')) {
            Schema::table('orders', function (Blueprint $table) {
                // Only add indexes if columns exist
                if (Schema::hasColumn('orders', 'restaurant_id') && 
                    Schema::hasColumn('orders', 'status') && 
                    Schema::hasColumn('orders', 'created_at') &&
                    !$this->indexExists('orders', 'orders_restaurant_status_date_index')) {
                    $table->index(['restaurant_id', 'status', 'created_at'], 'orders_restaurant_status_date_index');
                }
                // Branch + Status (filtering)
                if (Schema::hasColumn('orders', 'branch_id') && 
                    Schema::hasColumn('orders', 'status') &&
                    !$this->indexExists('orders', 'orders_branch_status_index')) {
                    $table->index(['branch_id', 'status'], 'orders_branch_status_index');
                }
            });
        }

        // kot_items table (if exists)
        if (Schema::hasTable('kot_items')) {
            Schema::table('kot_items', function (Blueprint $table) {
                // Kitchen place + Status (KDS filtering)
                if (Schema::hasColumn('kot_items', 'kitchen_place_id') && 
                    Schema::hasColumn('kot_items', 'status') &&
                    !$this->indexExists('kot_items', 'kot_items_kitchen_status_index')) {
                    $table->index(['kitchen_place_id', 'status'], 'kot_items_kitchen_status_index');
                }
                // KOT queries
                if (Schema::hasColumn('kot_items', 'kot_id') &&
                    !$this->indexExists('kot_items', 'kot_items_kot_index')) {
                    $table->index('kot_id', 'kot_items_kot_index');
                }
            });
        }
    }

    /**
     * Reverse the migrations.
     */
    public function down(): void
    {
        // Drop all indexes created above
        $indexes = [
            // Inventory
            ['table' => 'inventory_items', 'indexes' => [
                'inventory_items_branch_category_index',
                'inventory_items_branch_id_index',
                'inventory_items_category_index',
                'inventory_items_supplier_index',
            ]],
            ['table' => 'inventory_stocks', 'indexes' => [
                'inventory_stocks_item_branch_index',
                'inventory_stocks_item_index',
                'inventory_stocks_branch_index',
            ]],
            ['table' => 'inventory_movements', 'indexes' => [
                'inventory_movements_item_date_index',
                'inventory_movements_item_index',
                'inventory_movements_created_at_index',
                'inventory_movements_order_index',
                'inventory_movements_branch_index',
            ]],
            ['table' => 'purchase_orders', 'indexes' => [
                'purchase_orders_status_date_index',
                'purchase_orders_branch_index',
                'purchase_orders_supplier_index',
                'purchase_orders_created_at_index',
            ]],
            ['table' => 'purchase_order_items', 'indexes' => [
                'purchase_order_items_po_index',
                'purchase_order_items_item_index',
            ]],
            ['table' => 'batch_stocks', 'indexes' => [
                'batch_stocks_batch_expiry_index',
                'batch_stocks_expiry_index',
                'batch_stocks_batch_index',
            ]],
            ['table' => 'recipes', 'indexes' => [
                'recipes_menu_item_index',
                'recipes_inventory_item_index',
            ]],
            ['table' => 'suppliers', 'indexes' => [
                'suppliers_restaurant_index',
            ]],
            // CashRegister
            ['table' => 'cash_register_sessions', 'indexes' => [
                'cash_register_sessions_branch_status_index',
                'cash_register_sessions_register_index',
                'cash_register_sessions_closed_at_index',
                'cash_register_sessions_opened_at_index',
                'cash_register_sessions_opened_by_index',
            ]],
            ['table' => 'cash_register_transactions', 'indexes' => [
                'cash_register_transactions_session_type_index',
                'cash_register_transactions_session_index',
                'cash_register_transactions_happened_at_index',
                'cash_register_transactions_type_index',
                'cash_register_transactions_order_index',
                'cash_register_transactions_branch_index',
            ]],
            ['table' => 'cash_register_counts', 'indexes' => [
                'cash_register_counts_session_index',
                'cash_register_counts_denomination_index',
            ]],
            ['table' => 'cash_registers', 'indexes' => [
                'cash_registers_branch_index',
                'cash_registers_restaurant_index',
            ]],
            // General
            ['table' => 'orders', 'indexes' => [
                'orders_restaurant_status_date_index',
                'orders_branch_status_index',
            ]],
            ['table' => 'kot_items', 'indexes' => [
                'kot_items_kitchen_status_index',
                'kot_items_kot_index',
            ]],
        ];

        foreach ($indexes as $tableData) {
            if (Schema::hasTable($tableData['table'])) {
                Schema::table($tableData['table'], function (Blueprint $table) use ($tableData) {
                    foreach ($tableData['indexes'] as $indexName) {
                        if ($this->indexExists($tableData['table'], $indexName)) {
                            $table->dropIndex($indexName);
                        }
                    }
                });
            }
        }
    }

    /**
     * Check if an index exists on a table
     */
    private function indexExists(string $table, string $index): bool
    {
        $connection = Schema::getConnection();
        $database = $connection->getDatabaseName();
        
        $result = DB::select(
            "SELECT COUNT(*) as count 
             FROM information_schema.statistics 
             WHERE table_schema = ? 
             AND table_name = ? 
             AND index_name = ?",
            [$database, $table, $index]
        );
        
        return $result[0]->count > 0;
    }
};
