<?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
    {
        // Contracts table indexes
        Schema::table('contracts', function (Blueprint $table) {
            if (! $this->indexExists('contracts', 'contracts_client_case_id_active_deleted_at_index')) {
                $table->index(['client_case_id', 'active', 'deleted_at'], 'contracts_client_case_id_active_deleted_at_index');
            }
            if (! $this->indexExists('contracts', 'contracts_start_date_end_date_index')) {
                $table->index(['start_date', 'end_date'], 'contracts_start_date_end_date_index');
            }
        });

        // Contract segment pivot table indexes
        Schema::table('contract_segment', function (Blueprint $table) {
            if (! $this->indexExists('contract_segment', 'contract_segment_contract_id_active_index')) {
                $table->index(['contract_id', 'active'], 'contract_segment_contract_id_active_index');
            }
            if (! $this->indexExists('contract_segment', 'contract_segment_segment_id_active_index')) {
                $table->index(['segment_id', 'active'], 'contract_segment_segment_id_active_index');
            }
        });

        // Activities table indexes
        Schema::table('activities', function (Blueprint $table) {
            if (! $this->indexExists('activities', 'activities_client_case_id_created_at_index')) {
                $table->index(['client_case_id', 'created_at'], 'activities_client_case_id_created_at_index');
            }
            if (! $this->indexExists('activities', 'activities_contract_id_created_at_index')) {
                $table->index(['contract_id', 'created_at'], 'activities_contract_id_created_at_index');
            }
        });

        // Client cases table indexes
        Schema::table('client_cases', function (Blueprint $table) {
            if (! $this->indexExists('client_cases', 'client_cases_client_id_active_index')) {
                $table->index(['client_id', 'active'], 'client_cases_client_id_active_index');
            }
            if (! $this->indexExists('client_cases', 'client_cases_person_id_active_index')) {
                $table->index(['person_id', 'active'], 'client_cases_person_id_active_index');
            }
        });

        // Documents table indexes for polymorphic relations
        Schema::table('documents', function (Blueprint $table) {
            if (! $this->indexExists('documents', 'documents_documentable_type_documentable_id_index')) {
                $table->index(['documentable_type', 'documentable_id'], 'documents_documentable_type_documentable_id_index');
            }
            if (! $this->indexExists('documents', 'documents_created_at_index')) {
                $table->index(['created_at'], 'documents_created_at_index');
            }
        });

        // Field jobs indexes
        Schema::table('field_jobs', function (Blueprint $table) {
            if (! $this->indexExists('field_jobs', 'field_jobs_assigned_user_id_index')) {
                $table->index(['assigned_user_id'], 'field_jobs_assigned_user_id_index');
            }
            if (! $this->indexExists('field_jobs', 'field_jobs_contract_id_index')) {
                $table->index(['contract_id'], 'field_jobs_contract_id_index');
            }
            if (! $this->indexExists('field_jobs', 'field_jobs_completed_at_index')) {
                $table->index(['completed_at'], 'field_jobs_completed_at_index');
            }
        });
    }

    /**
     * Reverse the migrations.
     */
    public function down(): void
    {
        Schema::table('contracts', function (Blueprint $table) {
            $table->dropIndex('contracts_client_case_id_active_deleted_at_index');
            $table->dropIndex('contracts_start_date_end_date_index');
        });

        Schema::table('contract_segment', function (Blueprint $table) {
            $table->dropIndex('contract_segment_contract_id_active_index');
            $table->dropIndex('contract_segment_segment_id_active_index');
        });

        Schema::table('activities', function (Blueprint $table) {
            $table->dropIndex('activities_client_case_id_created_at_index');
            $table->dropIndex('activities_contract_id_created_at_index');
        });

        Schema::table('client_cases', function (Blueprint $table) {
            $table->dropIndex('client_cases_client_id_active_index');
            $table->dropIndex('client_cases_person_id_active_index');
        });

        Schema::table('documents', function (Blueprint $table) {
            $table->dropIndex('documents_documentable_type_documentable_id_index');
            $table->dropIndex('documents_created_at_index');
        });

        Schema::table('field_jobs', function (Blueprint $table) {
            $table->dropIndex('field_jobs_assigned_user_id_index');
            $table->dropIndex('field_jobs_contract_id_index');
            $table->dropIndex('field_jobs_completed_at_index');
        });
    }

    /**
     * Check if an index exists on a table.
     */
    protected function indexExists(string $table, string $index): bool
    {
        $connection = Schema::getConnection();
        $driver = $connection->getDriverName();

        if ($driver === 'pgsql') {
            // PostgreSQL uses pg_indexes system catalog
            $result = $connection->select(
                "SELECT COUNT(*) as count FROM pg_indexes 
                 WHERE schemaname = 'public' AND tablename = ? AND indexname = ?",
                [$table, $index]
            );
        } else {
            // MySQL/MariaDB uses information_schema.statistics
            $databaseName = $connection->getDatabaseName();
            $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;
    }
};

