<?php
// database/migrations/2025_09_19_000002_shrink_status_type_on_payments_for_index.php

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

return new class extends Migration
{
    public function up(): void
    {
        if (! Schema::hasTable('payments')) {
            return;
        }

        // 1) Drop the oversized composite index if it exists
        $this->dropIndexIfExists('payments', 'payments_job_id_status_type_index');

        // 2) Ensure columns exist before changing them
        $columns = $this->getTableColumns('payments');
        $hasStatus = in_array('status', $columns, true);
        $hasType   = in_array('type', $columns, true);

        // 3) Shrink string columns so composite index fits MySQL limits (utf8mb4)
        // Requires doctrine/dbal to use ->change()
        Schema::table('payments', function (Blueprint $table) use ($hasStatus, $hasType) {
            if ($hasStatus) {
                $table->string('status', 50)->change();
            }
            if ($hasType) {
                $table->string('type', 50)->change();
            }
        });

        // 4) Recreate the composite index (job_id BIGINT + 2x VARCHAR(50) is safe)
        // Only add if both columns are present
        if ($hasStatus && $hasType && ! $this->indexExists('payments', 'payments_job_id_status_type_index')) {
            Schema::table('payments', function (Blueprint $table) {
                $table->index(['job_id', 'status', 'type'], 'payments_job_id_status_type_index');
            });
        }
    }

    public function down(): void
    {
        if (! Schema::hasTable('payments')) {
            return;
        }

        // Best-effort: drop our index; we won't expand column lengths back (data might exceed).
        $this->dropIndexIfExists('payments', 'payments_job_id_status_type_index');
    }

    // --- helpers ---

    protected function dropIndexIfExists(string $table, string $indexName): void
    {
        try {
            // Check if index exists first
            if ($this->indexExists($table, $indexName)) {
                Schema::table($table, function (Blueprint $t) use ($indexName) {
                    $t->dropIndex($indexName);
                });
            }
        } catch (\Throwable $e) {
            // No-op if it doesn't exist or was already dropped
        }
    }

    protected function indexExists(string $table, string $indexName): bool
    {
        $db = DB::getDatabaseName();

        $row = DB::selectOne(
            'SELECT 1 FROM information_schema.STATISTICS WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ? AND INDEX_NAME = ? LIMIT 1',
            [$db, $table, $indexName]
        );

        return (bool) $row;
    }

    protected function getTableColumns(string $table): array
    {
        $rows = DB::select('SHOW COLUMNS FROM `' . str_replace('`', '``', $table) . '`');
        return array_map(fn($r) => $r->Field, $rows);
    }
};
