-- +goose Up
-- +goose StatementBegin

-- +goose NO TRANSACTION

DO $$
DECLARE
    snapshot_xmin BIGINT;
    partition_record RECORD;
    create_sql TEXT;
    rename_sql TEXT;
    old_name TEXT;
    new_name TEXT;
BEGIN
    SELECT txid_current() INTO snapshot_xmin;

    CREATE TABLE v1_events_olap_new (
        tenant_id UUID NOT NULL,
        id BIGINT NOT NULL GENERATED BY DEFAULT AS IDENTITY,
        external_id UUID NOT NULL DEFAULT gen_random_uuid(),
        seen_at TIMESTAMPTZ NOT NULL,
        key TEXT NOT NULL,
        payload JSONB NOT NULL,
        additional_metadata JSONB,
        PRIMARY KEY (tenant_id, seen_at, id)
    ) PARTITION BY RANGE(seen_at);

    CREATE INDEX v1_events_olap_key_idx_new ON v1_events_olap_new (tenant_id, key);

    FOR partition_record IN
        SELECT
            c.relname as partition_name,
            pg_get_expr(c.relpartbound, c.oid) as partition_bound
        FROM pg_class c
        JOIN pg_inherits i ON c.oid = i.inhrelid
        JOIN pg_class parent ON i.inhparent = parent.oid
        WHERE parent.relname = 'v1_events_olap'
        ORDER BY c.relname
    LOOP
        create_sql := format(
            'CREATE TABLE %I PARTITION OF v1_events_olap_new %s',
            partition_record.partition_name || '_new',
            partition_record.partition_bound
        );
        EXECUTE create_sql;
    END LOOP;

    INSERT INTO v1_events_olap_new (tenant_id, external_id, seen_at, key, payload, additional_metadata)
    SELECT tenant_id, external_id, seen_at, key, payload, additional_metadata
    FROM v1_events_olap
    WHERE xmin::text::bigint < snapshot_xmin;

    LOCK TABLE v1_events_olap IN ACCESS EXCLUSIVE MODE;

    INSERT INTO v1_events_olap_new (tenant_id, external_id, seen_at, key, payload, additional_metadata)
    SELECT tenant_id, external_id, seen_at, key, payload, additional_metadata
    FROM v1_events_olap
    WHERE xmin::text::bigint >= snapshot_xmin;

    DROP TABLE v1_events_olap;
    ALTER TABLE v1_events_olap_new RENAME TO v1_events_olap;

    FOR partition_record IN
        SELECT c.relname as partition_name
        FROM pg_class c
        JOIN pg_inherits i ON c.oid = i.inhrelid
        JOIN pg_class parent ON i.inhparent = parent.oid
        WHERE parent.relname = 'v1_events_olap'
          AND c.relname LIKE '%_new'
        ORDER BY c.relname
    LOOP
        old_name := partition_record.partition_name;
        new_name := regexp_replace(old_name, '_new$', '');
        rename_sql := format('ALTER TABLE %I RENAME TO %I', old_name, new_name);
        EXECUTE rename_sql;
    END LOOP;

    FOR partition_record IN
        SELECT
            c.relname as partition_name,
            i.relname as index_name
        FROM pg_class c
        JOIN pg_inherits inh ON c.oid = inh.inhrelid
        JOIN pg_class parent ON inh.inhparent = parent.oid
        JOIN pg_index idx ON c.oid = idx.indrelid
        JOIN pg_class i ON idx.indexrelid = i.oid
        WHERE parent.relname = 'v1_events_olap'
        AND i.relname LIKE '%_new_%'
        ORDER BY c.relname, i.relname
    LOOP
        old_name := partition_record.index_name;
        new_name := regexp_replace(old_name, '_new_', '_');
        rename_sql := format('ALTER INDEX %I RENAME TO %I', old_name, new_name);
        EXECUTE rename_sql;
    END LOOP;

    ALTER INDEX v1_events_olap_key_idx_new RENAME TO v1_events_olap_key_idx;

    CREATE OR REPLACE FUNCTION v1_events_lookup_table_olap_insert_function()
    RETURNS TRIGGER AS
    $BODY$
    BEGIN
        INSERT INTO v1_event_lookup_table_olap (
            tenant_id,
            external_id,
            event_id,
            event_seen_at
        )
        SELECT
            tenant_id,
            external_id,
            id,
            seen_at
        FROM new_rows
        ON CONFLICT (tenant_id, external_id, event_seen_at) DO NOTHING;

        RETURN NULL;
    END;
    $BODY$
    LANGUAGE plpgsql;

    CREATE TRIGGER v1_event_lookup_table_olap_insert_trigger
    AFTER INSERT ON v1_events_olap
    REFERENCING NEW TABLE AS new_rows
    FOR EACH STATEMENT
    EXECUTE FUNCTION v1_events_lookup_table_olap_insert_function();
END $$;

-- +goose StatementEnd

-- +goose Down
-- +goose StatementBegin
-- +goose StatementEnd
