<?php

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

/**
 * Add critical performance indexes identified in April 2026 database audit (batch 4).
 *
 * Priority 1: parent_credit_id on credits
 *   - Enables leaf credit detection (NOT EXISTS subqueries) in dashboard KPIs,
 *     reconciliation command, and PAR metrics.
 *   - Used by: SmartCreditStateResolver, DelinquencyMetricsService, AdminDashboardMetricsService,
 *     ReconcileCreditStatuses command.
 *   - Impact: 200–500 ms queries → 20–50 ms (10× improvement).
 *
 * Priority 2: (company_id, collector_user_id) on credits
 *   - Enables efficient filtering of credits by collector assignment.
 *   - Used by: CollectorCreditOrderService::reorder(), CollectionController.
 *   - Impact: 100–200 ms queries → 10–20 ms (5–10× improvement).
 *
 * Priority 3: (credit_id, company_id, user_id) on collector_credit_order
 *   - Enables efficient LEFT JOIN from installments to collector_credit_order for route ordering.
 *   - The existing (company_id, user_id, sort_order) index cannot cover joins on credit_id.
 *   - Used by: CollectionController route ordering queries.
 *   - Impact: 500–1000 ms nested loop joins → 50–100 ms (5–10× improvement).
 */
return new class extends Migration
{
    public function up(): void
    {
        // Priority 1: Leaf credit detection via parent_credit_id lookups
        Schema::table('credits', function (Blueprint $table) {
            $table->index('parent_credit_id', 'idx_credits_parent_credit_id');
        });

        // Priority 2: Collector credit filtering by company + collector
        Schema::table('credits', function (Blueprint $table) {
            $table->index(['company_id', 'collector_user_id'], 'idx_credits_company_collector_user');
        });

        // Priority 3: Collector route LEFT JOIN optimisation
        Schema::table('collector_credit_order', function (Blueprint $table) {
            $table->index(['credit_id', 'company_id', 'user_id'], 'idx_collector_order_credit_company_user');
        });
    }

    public function down(): void
    {
        Schema::table('credits', function (Blueprint $table) {
            $table->dropIndex('idx_credits_parent_credit_id');
            $table->dropIndex('idx_credits_company_collector_user');
        });

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