<?php

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

return new class extends Migration
{
    /**
     * Run the migrations.
     * 
     * Adds performance indexes to improve invoice API endpoint speed.
     * These indexes target the slow /api/v1/invoices/sales and /api/v1/invoices/invoices endpoints.
     */
    public function up(): void
    {
        Schema::table('invoices', function (Blueprint $table) {
            // Compound index for type filtering with created_at ordering (most important)
            $table->index(['type', 'created_at'], 'idx_type_created_at');
            
            // Compound index for client filtering
            $table->index(['client_id', 'client_type'], 'idx_client_filter');
            
            // Index for created_at date range queries
            $table->index(['created_at'], 'idx_created_at');
            
            // Index for date filtering queries
            $table->index(['invoice_date'], 'idx_invoice_date');
            
            // Compound index for common combined filters
            $table->index(['type', 'paid', 'created_at'], 'idx_type_paid_created');
        });

        // Add indexes to invoice_items table for better relationship performance
        Schema::table('invoice_items', function (Blueprint $table) {
            // Index for invoice relationship queries
            $table->index(['invoice_id', 'deleted_at'], 'idx_invoice_items_lookup');
        });

        // Add indexes to invoice_emails table for email count queries
        Schema::table('invoice_emails', function (Blueprint $table) {
            // The invoice_id index already exists, but let's ensure it's optimal
            // Check if we need to add a compound index for better counting performance
            $table->index(['invoice_id', 'created_at'], 'idx_emails_count');
        });
    }

    /**
     * Reverse the migrations.
     */
    public function down(): void
    {
        Schema::table('invoices', function (Blueprint $table) {
            $table->dropIndex('idx_type_created_at');
            $table->dropIndex('idx_client_filter');
            $table->dropIndex('idx_created_at');
            $table->dropIndex('idx_invoice_date');
            $table->dropIndex('idx_type_paid_created');
        });

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

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