<?php

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

class AuditIndexing 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. Applies to the ALTERs and to index-creation lock steps.
        DB::statement("SET lock_timeout = '5s'");

        // There's an issue here where ASNs technically could be bigger than the integer size of the asn column. This addresses that.
        if ($this->columnType('asns', 'asn') === 'integer') {
            DB::statement('ALTER TABLE asns ALTER COLUMN asn TYPE bigint');
        }

        // As above.
        if ($this->columnType('prefix_asn_peers', 'peer') === 'integer') {
            DB::statement('ALTER TABLE prefix_asn_peers ALTER COLUMN peer TYPE bigint, ALTER COLUMN owner TYPE bigint');
        }

        // Throw some indexes onto the columns based upon what's actually used. This table is huge though.
        $this->concurrentIndex('goliath_attacks', ['appliance_id', 'destination', 'started_at'], 'goliath_attacks_appliance_id_destination_started_at_index');
        $this->concurrentIndex('goliath_attacks', ['updated_at'], 'goliath_attacks_updated_at_index');
        $this->concurrentIndex('goliath_attacks', ['started_at'], 'goliath_attacks_started_at_index');
        $this->concurrentIndex('goliath_attacks', ['last_at'], 'goliath_attacks_last_at_index');

        // Pivot index as attack_id is used everywhere.
        $this->concurrentIndex('goliath_attack_prefixes', ['attack_id', 'prefix_id'], 'goliath_attack_prefixes_attack_id_prefix_id_index');

        // This will make requests against for the appliances and anything using the API more efficient.
        $this->concurrentIndex('api_tokens', ['identifier'], 'api_tokens_identifier_index');
        $this->concurrentIndex('api_token_bearer', ['bearer', 'expiration_at'], 'api_token_bearer_bearer_expiration_at_index');
        $this->concurrentIndex('api_token_whitelist', ['token_id', 'address'], 'api_token_whitelist_token_id_address_index');

        // Basically everything in Creatia uses an organisation_id - We need these to be indexed.
        foreach ([
            'product_rights',
            'organisation_external_identities',
            'report_organisation_attributes',
            'pending_reports',
            'goliath_history',
            'asns',
            'api_tokens',
            'goliath_appliances',
            'bgp_reroute',
            'goliath_organisation_attributes',
            'mercury_organisation_attributes',
            'geo_ip_organisation_attributes',
            'goliath_organisation_special',
            'email_organisation_attributes',
        ] as $table) {
            $this->concurrentIndex($table, ['organisation_id'], $table . '_organisation_id_index');
        }

        // This is important for anyone who has tenanted prefixes.
        $this->concurrentIndex('goliath_history_visibility', ['history_id', 'organisation_id'], 'goliath_history_visibility_history_id_organisation_id_index');

        // Speeds up subtenancy lookups.
        $this->concurrentIndex('prefix_subtenants', ['prefix_id'], 'prefix_subtenants_prefix_id_index');
        $this->concurrentIndex('prefix_subtenants', ['subtenant_id'], 'prefix_subtenants_subtenant_id_index');

        // Unique constraint for prefixes that aren't deleted.
        if (Schema::hasTable('prefixes') && !$this->hasIndexOn('prefixes', ['address', 'mask'], true)) {
            $this->dropIfInvalid('prefixes_address_mask_unique');

            DB::statement('CREATE UNIQUE INDEX CONCURRENTLY IF NOT EXISTS prefixes_address_mask_unique ON prefixes (address, mask) WHERE deleted_at IS NULL');
        }

        // This will speed up joins
        if (Schema::hasTable('prefix_asn_peers') && !$this->hasIndexOn('prefix_asn_peers', ['prefix_id'], true)) {
            $this->dropIfInvalid('prefix_asn_peers_prefix_id_unique');

            DB::statement('DELETE FROM prefix_asn_peers a USING prefix_asn_peers b WHERE a.prefix_id = b.prefix_id AND a.ctid > b.ctid');
            DB::statement('CREATE UNIQUE INDEX CONCURRENTLY IF NOT EXISTS prefix_asn_peers_prefix_id_unique ON prefix_asn_peers (prefix_id)');
        }

        // When used correctly this will speed up a lot of queries
        if (Schema::hasTable('prefixes')) {
            $this->dropIfInvalid('prefixes_cidr_gist');

            DB::statement('CREATE INDEX CONCURRENTLY IF NOT EXISTS prefixes_cidr_gist ON prefixes USING gist (network(set_masklen(address::inet, mask)) inet_ops)');
        }
    }

    public function down()
    {
        foreach ([
            'goliath_attacks_appliance_id_destination_started_at_index',
            'goliath_attacks_updated_at_index',
            'goliath_attacks_started_at_index',
            'goliath_attacks_last_at_index',
            'goliath_attack_prefixes_attack_id_prefix_id_index',
            'goliath_attack_collected_attack_id_unique',
            'api_tokens_identifier_index',
            'api_token_bearer_bearer_expiration_at_index',
            'api_token_whitelist_token_id_address_index',
            'product_rights_organisation_id_index',
            'organisation_external_identities_organisation_id_index',
            'report_organisation_attributes_organisation_id_index',
            'pending_reports_organisation_id_index',
            'goliath_history_organisation_id_index',
            'asns_organisation_id_index',
            'api_tokens_organisation_id_index',
            'goliath_appliances_organisation_id_index',
            'bgp_reroute_organisation_id_index',
            'goliath_organisation_attributes_organisation_id_index',
            'mercury_organisation_attributes_organisation_id_index',
            'geo_ip_organisation_attributes_organisation_id_index',
            'goliath_organisation_special_organisation_id_index',
            'email_organisation_attributes_organisation_id_index',
            'goliath_history_visibility_history_id_organisation_id_index',
            'prefix_subtenants_prefix_id_index',
            'prefix_subtenants_subtenant_id_index',
            'prefixes_address_mask_unique',
            'prefix_asn_peers_prefix_id_unique',
            'prefixes_cidr_gist',
        ] as $index) {
            DB::statement('DROP INDEX CONCURRENTLY IF EXISTS "' . $index . '"');
        }

        // The bigint type changes are intentionally not reverted - narrowing back to integer would fail (or corrupt) once 4-byte ASNs have been stored.
    }

    /**
     * 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. Non-unique checks accept any valid index whose leading columns
     * match; unique checks require an exact-column unique index.
     */
    private function hasIndexOn(string $_table, array $_columns, bool $_unique = false): 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" . ($_unique ? ' AND i.indisunique' : '') . "
            GROUP BY i.indexrelid
        ", [$_table]);

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

        foreach ($indexes as $index) {
            if ($_unique ? $index->columns === $wanted : 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 . '"');
        }
    }

    private function columnType(string $_table, string $_column): ?string
    {
        $result = DB::select('SELECT data_type FROM information_schema.columns WHERE table_schema = current_schema() AND table_name = ? AND column_name = ?', [$_table, $_column]);

        return $result[0]->data_type ?? null;
    }
}