set search_path = public;

create extension if not exists plpgsql;
create extension if not exists pgcrypto;
create extension if not exists "uuid-ossp";

create table log (
    id uuid primary key default gen_random_uuid(),
    target text not null,
    message text not null,
    host text,
    source text not null,
    "user" text,
    received_at timestamp without time zone not null default (now() at time zone 'utc')
);

create index log_received_at on log (received_at);
create index log_target on log (target);
create index log_source on log (source);

create or replace function log_insert_notify () returns trigger as $$
    begin
        perform pg_notify('log_new_message', NEW.id::text);
        return NEW;
    end;
$$ language plpgsql;

create trigger log_insert_notify_trigger
after insert on log
for each row execute procedure log_insert_notify ();
    
create table outbox (
    id serial primary key,
    target text not null,
    message text not null,
    queued_at timestamp without time zone not null default (now() at time zone 'utc')
);

create or replace function outbox_insert_notify () returns trigger as $$
    begin
        perform pg_notify('outbox_new_message', NEW.id::text);
        return NEW;
    end;
$$ language plpgsql;

create trigger outbox_insert_notify_trigger
after insert on outbox
for each row execute procedure outbox_insert_notify ();