<?php

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

/**
 * Indexes that speed up the date-range analytics queries (grouping/filtering
 * leads and withdrawals by date, status, company and country) at scale.
 */
return new class extends Migration
{
    public function up()
    {
        Schema::table('leads', function (Blueprint $table) {
            $this->addIndex($table, 'leads', ['created_at'], 'leads_created_at_index');
            $this->addIndex($table, 'leads', ['status', 'created_at'], 'leads_status_created_at_index');
            $this->addIndex($table, 'leads', ['company', 'created_at'], 'leads_company_created_at_index');
            $this->addIndex($table, 'leads', ['country'], 'leads_country_index');
            $this->addIndex($table, 'leads', ['user_id', 'status'], 'leads_user_status_index');
        });

        Schema::table('withdrawals', function (Blueprint $table) {
            $this->addIndex($table, 'withdrawals', ['created_at'], 'withdrawals_created_at_index');
            $this->addIndex($table, 'withdrawals', ['status', 'created_at'], 'withdrawals_status_created_at_index');
        });

        Schema::table('users', function (Blueprint $table) {
            $this->addIndex($table, 'users', ['created_at'], 'users_created_at_index');
            $this->addIndex($table, 'users', ['last_seen_at'], 'users_last_seen_at_index');
        });
    }

    public function down()
    {
        $drop = function (string $table, string $index) {
            Schema::table($table, function (Blueprint $t) use ($index) {
                try { $t->dropIndex($index); } catch (\Throwable $e) {}
            });
        };

        foreach ([
            'leads' => ['leads_created_at_index', 'leads_status_created_at_index', 'leads_company_created_at_index', 'leads_country_index', 'leads_user_status_index'],
            'withdrawals' => ['withdrawals_created_at_index', 'withdrawals_status_created_at_index'],
            'users' => ['users_created_at_index', 'users_last_seen_at_index'],
        ] as $table => $indexes) {
            foreach ($indexes as $index) {
                $drop($table, $index);
            }
        }
    }

    private function addIndex(Blueprint $table, string $tableName, array $columns, string $name): void
    {
        // Skip if all columns exist and the index isn't already there.
        foreach ($columns as $column) {
            if (!Schema::hasColumn($tableName, $column)) {
                return;
            }
        }

        try {
            $table->index($columns, $name);
        } catch (\Throwable $e) {
            // Index already exists — ignore.
        }
    }
};
