<?php

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

return new class extends Migration
{
    /**
     * Run the migrations.
     * 
     * This migration adds targeted indexes to optimize ProjectsDashboard queries.
     * Based on analysis of widget queries:
     * - ProjectCharterStatusChart: filters by status + year + relationships
     * - ProjectStatusChart: filters by status groups + year
     * - ProjectCostChart: filters by total_cost + year
     * - ProjectDetailsTable: sorts by start_date, filters by year
     * - ProjectCostOverview: aggregates total_cost
     * - All widgets filter through initiativeProject relationship
     */
    public function up(): void
    {
        Schema::table('project_charters', function (Blueprint $table) {
            // Critical for year-based filtering (used in ALL widgets)
            $table->index(['start_date', 'expected_end_date'], 'pc_year_filter_idx');
            
            // For status-based grouping and filtering (StatusChart, CostChart)
            $table->index('status', 'pc_status_idx');
            
            // For default sorting in ProjectDetailsTable
            $table->index('start_date', 'pc_start_date_idx');
            
            // For cost aggregations and filtering (CostOverview, CostChart)
            $table->index('total_cost', 'pc_total_cost_idx');
            
            // Composite index for status + year queries (most common pattern)
            $table->index(['status', 'start_date'], 'pc_status_year_idx');
        });
    }

    /**
     * Reverse the migrations.
     */
    public function down(): void
    {
        Schema::table('project_charters', function (Blueprint $table) {
            $table->dropIndex('pc_year_filter_idx');
            $table->dropIndex('pc_status_idx');
            $table->dropIndex('pc_start_date_idx');
            $table->dropIndex('pc_total_cost_idx');
            $table->dropIndex('pc_status_year_idx');
        });
    }
};
