<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;
use Illuminate\Support\Facades\DB;

class AddEssentialTaskIndexes extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        // Helper function to check if index exists
        $indexExists = function ($table, $indexName) {
            $indexes = DB::select("SHOW INDEX FROM {$table} WHERE Key_name = ?", [$indexName]);
            return count($indexes) > 0;
        };

        // Add essential indexes for tasks table (only the most critical ones)
        Schema::table('tasks', function (Blueprint $table) use ($indexExists) {
            // Only add indexes that are absolutely essential for our optimization
            if (Schema::hasColumn('tasks', 'client_id') && !$indexExists('tasks', 'idx_tasks_client_id')) {
                $table->index('client_id', 'idx_tasks_client_id');
            }
            
            if (Schema::hasColumn('tasks', 'client_type') && !$indexExists('tasks', 'idx_tasks_client_type')) {
                $table->index('client_type', 'idx_tasks_client_type');
            }
            
            if (Schema::hasColumn('tasks', 'assigned_to') && !$indexExists('tasks', 'idx_tasks_assigned_to')) {
                $table->index('assigned_to', 'idx_tasks_assigned_to');
            }
        });

        // Add essential index for assigned_changes table (used in workers query)
        if (Schema::hasTable('assigned_changes')) {
            Schema::table('assigned_changes', function (Blueprint $table) use ($indexExists) {
                if (Schema::hasColumn('assigned_changes', 'related_id') && 
                    Schema::hasColumn('assigned_changes', 'related_type') && 
                    !$indexExists('assigned_changes', 'idx_assigned_changes_related')) {
                    $table->index(['related_id', 'related_type'], 'idx_assigned_changes_related');
                }
            });
        }

        // Add indexes for parts table only if it has the right columns
        if (Schema::hasTable('parts') && Schema::hasColumn('parts', 'related_id')) {
            Schema::table('parts', function (Blueprint $table) use ($indexExists) {
                if (!$indexExists('parts', 'idx_parts_related_id')) {
                    $table->index('related_id', 'idx_parts_related_id');
                }
            });
        }
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::table('tasks', function (Blueprint $table) {
            $table->dropIndex('idx_tasks_client_id');
            $table->dropIndex('idx_tasks_client_type');
            $table->dropIndex('idx_tasks_assigned_to');
        });

        if (Schema::hasTable('assigned_changes')) {
            Schema::table('assigned_changes', function (Blueprint $table) {
                $table->dropIndex('idx_assigned_changes_related');
            });
        }

        if (Schema::hasTable('parts')) {
            Schema::table('parts', function (Blueprint $table) {
                $table->dropIndex('idx_parts_related_id');
            });
        }
    }
}