drop schema if exists "postspec" cascade;
create schema postspec;

set search_path to postspec;

-- Return a map from table UID to last value of sequence. All user tables are
-- included but tables without sequences (subtables) have last value set to
-- null
create or replace function table_sequence_ids(ignore_schemas varchar[])
  returns table(table_uid text, record_id bigint)
as $$
  declare
    name varchar(255);
    tuple record;
  begin
    ignore_schemas := ignore_schemas || array['information_schema', 'postspec']::varchar[];
    for tuple in
      select  tc.relnamespace::regnamespace::text || '.' || tc.relname as table_uid,
              s.relnamespace::regnamespace::text || '.' || s.relname as sequence_uid
      from    pg_class tc
      left join (
              select  d.refobjid,
                      sc.relname,
                      sc.relkind,
                      sc.relnamespace::regnamespace::text as relnamespace
              from    pg_depend d
              join    pg_class sc on sc.oid = d.objid
              where   coalesce(sc.relkind = 'S', true)
              and     coalesce(sc.relnamespace::regnamespace::text != 'postspec', true)
      ) s on s.refobjid = tc.oid
      where   tc.relkind = 'r'
      and     tc.relnamespace::regnamespace::text not like 'pg_%'
      and     tc.relnamespace::regnamespace::text != all(ignore_schemas)
    loop
      if tuple.sequence_uid is null then
        table_uid := tuple.table_uid;
        record_id := null;
        return next;
      else
        return query execute
            'select ' || quote_literal(tuple.table_uid) || '::text as table_uid, ' ||
                    'case is_called when true then last_value else last_value - 1 end as last_value ' ||
            'from ' || tuple.sequence_uid;
      end if;
    end loop;
    return;
  end
$$ language plpgsql;

create or replace function readonly_failure() returns trigger as $$
  begin
    raise 'Postspec: Can''t modify seed data in %', TG_TABLE_NAME::regclass::text;
    return null;
  end;
$$ language plpgsql immutable leakproof;

-- :call-seq:
--    register_insert(record_id)
--
create or replace function register_insert() returns trigger as $$
  begin
    insert into postspec.inserts (table_uid, record_id) values
      (TG_TABLE_SCHEMA || '.' || TG_TABLE_NAME, new.id);
    return new;
  end;
$$ language plpgsql;

-- :call-seq:
--    register_update(record_id)
--
create or replace function register_update() returns trigger as $$
  begin
    insert into postspec.updates (table_uid, record_id) values
      (TG_TABLE_SCHEMA || '.' || TG_TABLE_NAME, old.id);
    return new;
  end;
$$ language plpgsql;

-- :call-seq:
--    register_delete(record_id)
--
create or replace function register_delete() returns trigger as $$
  begin
    insert into postspec.deletes (table_uid, record_id) values
      (TG_TABLE_SCHEMA || '.' || TG_TABLE_NAME, old.id);
--      ('postspec.' || TG_TABLE_NAME, old.id);
    return old;
  end;
$$ language plpgsql;

create table runs (
  id integer generated by default as identity primary key,
  mode text not null,
  ready boolean not null default false,
  clean boolean not null default false,
  status boolean,
  duration numeric generated always as (
    round(extract(epoch from updated_at - created_at)::numeric, 3)
  ) stored,
  created_at timestamp without time zone not null default (now() at time zone 'UTC'),
  updated_at timestamp without time zone
);

create table seeds (
  id integer generated by default as identity primary key,
  table_uid text,
  record_id integer
);

create table inserts (
  id integer generated by default as identity primary key,
  table_uid text,
  record_id integer
);

create table updates (
  id integer generated by default as identity primary key,
  table_uid text,
  record_id integer
);

create table deletes (
  id integer generated by default as identity primary key,
  table_uid text,
  record_id integer
);

set search_path to public;

grant all on schema postspec to public;
grant all on postspec.runs to public;
grant all on postspec.seeds to public;
grant all on postspec.inserts to public;
grant all on postspec.updates to public;
grant all on postspec.deletes to public;