DO $$
DECLARE
    t RECORD;
    idx RECORD;
    trig RECORD;
    func RECORD;
    old_table TEXT;
    new_table TEXT;
    old_index TEXT;
    new_index TEXT;
    schema_name TEXT := 'public';
BEGIN
    RAISE NOTICE '=== Starting full migration: drop triggers/functions, rename tables and indexes ===';

    -- Step 1: Drop all non-internal triggers in the schema
    FOR trig IN
        SELECT tgname, tgrelid::regclass::text AS relname, tgfoid
        FROM pg_trigger
        WHERE NOT tgisinternal
          AND tgrelid IN (
              SELECT c.oid
              FROM pg_class c
              JOIN pg_namespace n ON c.relnamespace = n.oid
              WHERE n.nspname = schema_name
          )
    LOOP
        RAISE NOTICE 'Dropping trigger "%" on table "%"', trig.tgname, trig.relname;

        BEGIN
            EXECUTE format('DROP TRIGGER IF EXISTS %I ON %I.%I;', trig.tgname, schema_name, trig.relname);
        EXCEPTION WHEN OTHERS THEN
            RAISE WARNING '  !! Failed to drop trigger "%": %', trig.tgname, SQLERRM;
        END;
    END LOOP;

    -- Step 2: Drop all functions used by those triggers
    FOR func IN
        SELECT
            n.nspname AS schema_name,
            p.proname AS function_name,
            pg_get_function_identity_arguments(p.oid) AS args,
            p.oid
        FROM pg_proc p
        JOIN pg_namespace n ON p.pronamespace = n.oid
        WHERE p.prorettype = 'pg_catalog.trigger'::regtype
          AND n.nspname = schema_name
          AND NOT EXISTS (
              SELECT 1
              FROM pg_trigger trg
              WHERE trg.tgfoid = p.oid
                AND NOT trg.tgisinternal
          )
    LOOP
        RAISE NOTICE 'Dropping orphaned trigger function %.%(%)', func.schema_name, func.function_name, func.args;
        BEGIN
            EXECUTE format('DROP FUNCTION IF EXISTS %I.%I(%s);', func.schema_name, func.function_name, func.args);
        EXCEPTION WHEN OTHERS THEN
            RAISE WARNING 'Failed to drop function %.%(%): %', func.schema_name, func.function_name, func.args, SQLERRM;
        END;
    END LOOP;

    -- Step 3: Rename tables ending with _1
    FOR t IN
        SELECT tablename
        FROM pg_tables
        WHERE schemaname = schema_name
          AND tablename ~ '^[a-zA-Z0-9_]+__[a-zA-Z0-9_]+_1$'
    LOOP
        old_table := t.tablename;
        new_table := regexp_replace(old_table, '_1$', '');

        RAISE NOTICE 'Renaming table: "%" -> "%"', old_table, new_table;

        BEGIN
            EXECUTE format('ALTER TABLE %I.%I RENAME TO %I;', schema_name, old_table, new_table);
        EXCEPTION WHEN OTHERS THEN
            RAISE WARNING '  !! Could not rename table "%": %', old_table, SQLERRM;
        END;

        -- Step 4: Rename indexes that include old table name
        FOR idx IN
            SELECT indexname
            FROM pg_indexes
            WHERE schemaname = schema_name
              AND indexname LIKE '%' || old_table || '%'
        LOOP
            old_index := idx.indexname;
            new_index := replace(old_index, old_table, new_table);

            IF old_index <> new_index THEN
                RAISE NOTICE '  -> Renaming index: "%" -> "%"', old_index, new_index;
                BEGIN
                    EXECUTE format('ALTER INDEX %I.%I RENAME TO %I;', schema_name, old_index, new_index);
                EXCEPTION WHEN OTHERS THEN
                    RAISE WARNING '    !! Failed to rename index "%": %', old_index, SQLERRM;
                END;
            END IF;
        END LOOP;
    END LOOP;

    RAISE NOTICE '=== Migration complete ===';
END $$;
