<?php

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

class AttackCreatedIndexing extends Migration
{
    /**
     * CREATE/DROP INDEX CONCURRENTLY cannot run inside a transaction block, so this
     * migration runs unwrapped. Every statement is guarded and idempotent, so a
     * partial failure can simply be re-run. Note: a CONCURRENTLY build that fails
     * midway leaves an INVALID index behind - drop it and re-run the migration.
     */
    public $withinTransaction = false;

    public function up()
    {
        // Abort rather than queue behind production traffic if a lock cannot be
        // acquired promptly.
        DB::statement("SET lock_timeout = '5s'");

        // created_at was the one unindexed timestamp on goliath_attacks - the attack monitor
        // selects on it constantly and the attack log tables sort by it, each at the cost of
        // a full sequential scan.
        $this->concurrentIndex('goliath_attacks', ['created_at'], 'goliath_attacks_created_at_index');
    }

    public function down()
    {
        DB::statement('DROP INDEX CONCURRENTLY IF EXISTS "goliath_attacks_created_at_index"');
    }

    /**
     * Whether the table already has a usable index for the given columns, regardless
     * of the index name - production indexes were hand-created and may not follow
     * Laravel naming. Accepts any valid index whose leading columns match.
     */
    private function hasIndexOn(string $_table, array $_columns): bool
    {
        if (!Schema::hasTable($_table)) {
            return false;
        }

        $indexes = DB::select("
            SELECT array_to_string(array_agg(coalesce(a.attname, '(expression)') ORDER BY k.ordinality), ',') AS columns
            FROM pg_index i
            JOIN pg_class t ON t.oid = i.indrelid
            JOIN pg_namespace n ON n.oid = t.relnamespace
            JOIN LATERAL unnest(i.indkey) WITH ORDINALITY AS k(attnum, ordinality) ON true
            LEFT JOIN pg_attribute a ON a.attrelid = t.oid AND a.attnum = k.attnum
            WHERE t.relname = ? AND n.nspname = current_schema() AND i.indisvalid
            GROUP BY i.indexrelid
        ", [$_table]);

        $wanted = implode(',', $_columns);

        foreach ($indexes as $index) {
            if (strpos($index->columns . ',', $wanted . ',') === 0) {
                return true;
            }
        }

        return false;
    }

    /**
     * Create a plain b-tree index without blocking writes, skipping tables/columns
     * that no longer exist and columns already covered by any existing index.
     */
    private function concurrentIndex(string $_table, array $_columns, string $_name): void
    {
        if (!Schema::hasTable($_table)) {
            return;
        }

        foreach ($_columns as $column) {
            if (!Schema::hasColumn($_table, $column)) {
                return;
            }
        }

        if ($this->hasIndexOn($_table, $_columns)) {
            return;
        }

        $this->dropIfInvalid($_name);

        DB::statement('CREATE INDEX CONCURRENTLY IF NOT EXISTS "' . $_name . '" ON "' . $_table . '" ("' . implode('", "', $_columns) . '")');
    }

    /**
     * A failed CONCURRENTLY build leaves an INVALID index behind under the target
     * name, which would make CREATE INDEX IF NOT EXISTS silently skip the retry -
     * drop it so a re-run of this migration rebuilds it.
     */
    private function dropIfInvalid(string $_name): void
    {
        $invalid = DB::select("
            SELECT 1
            FROM pg_index i
            JOIN pg_class c ON c.oid = i.indexrelid
            JOIN pg_namespace n ON n.oid = c.relnamespace
            WHERE c.relname = ? AND n.nspname = current_schema() AND NOT i.indisvalid
        ", [$_name]);

        if ($invalid !== []) {
            DB::statement('DROP INDEX CONCURRENTLY IF EXISTS "' . $_name . '"');
        }
    }
}