
create extension "uuid-ossp" cascade;
create extension "hstore" cascade;

-- order matters
\ir utility.sql
\ir claims.sql

create table queue (
  id uuid not null default uuid_generate_v4(),
    constraint _p_queue primary key (id),
  created timestamptz not null default clock_timestamp(),
  target text not null default 'default',
  attempts int2 not null default 0,
    constraint _c_queue_attempts check (attempts between 0 and 3),
  payload json not null
);

create table trace (
  rel regclass not null,
  att name not null,
  aud uuid not null,
  new text,
  old text,

  claims claims not null default get_claims()
) partition by list (rel);

create table trash (
  rel regclass not null,
  dat hstore not null,

  claims claims not null default get_claims()
);

create type aud_ctx as (
  _aud uuid,
  _new hstore,
  _old hstore,
  _dif hstore
);

create or replace function track ( anyelement, anyelement, text[], text[], json ) returns aud_ctx as $$ declare
  _aud uuid := coalesce($1.id, $2.id);
  _new hstore := case when $1 is null then null else hstore($1) - $4 - 'id' end;
  _old hstore := case when $2 is null then null else hstore($2) - $4 - 'id' end;
  _dif hstore := hstore_take(case
    when $1 is null then _old -- delete
    when $2 is null then _new -- insert
    else _new - _old          -- update
  end, $3);
begin
  if array_length(akeys(_dif), 1) > 0 then
    insert into queue (pl) values (
      $4 || json_build_object(
        'typ', 'track',
        'aud', _aud,
        'new', hstore_to_json(_new),
        'old', hstore_to_json(_old),
        'dif', hstore_to_json(_dif)
      )
    );

    return (_aud, _new, _old, _dif);
  else
    return null;
  end if;
end $$ language plpgsql;

create or replace function do_track ( ) returns trigger as $$ begin
  perform track(new, old, tg_argv[0]::text[], tg_argv[1]::text[], json_build_object(
    'rel', tg_relid::regclass::text,
    'opr', tg_op
  ));
end $$ language plpgsql security definer;

create or replace function do_trace ( ) returns trigger as $$ declare
  _ctx aud_ctx := track(new, old, tg_argv[0]::text[], tg_argv[1]::text[], json_build_object(
    'rel', tg_relid::regclass::text,
    'opr', tg_op
  ));

  _part_rel text := 'trace__' || tg_relname;
  _part_att text;

  _att text;
begin
  if _ctx is null then return null; end if;

  if to_regclass(_part_rel) is null then
    execute format($x$
      create table %1$I
      partition of "trace" for values in (%2$L::regclass)
      partition by list (att)
    $x$, _part_rel, tg_relid::regclass::text);
  end if;

  for _att in (select key from each(_ctx._dif)) loop
    _part_att := _part_rel || '__' || _att;

    if to_regclass(_part_att) is null then
      execute format($x$
        create table %2$I
        partition of %1$I for values in (%3$L)
      $x$, _part_rel, _part_att, _att);
    end if;

    insert into "trace" (rel, att, aud, new, old)
    values (tg_relid, _att, _ctx._aud, _ctx._new->_att, _ctx._old->_att);
  end loop;

  return null;
end $$ language plpgsql security definer;
