create or replace function generate_secret ()
  returns text
  language plpgsql
  as
  $$
    begin
      return substr(md5(random()::text), 0, 25);
    end;
  $$

create or replace function auth.verify() returns text as $$
  select coalesce(
    nullif(current_setting('request.jwt.claim.secret', true), ''),
    nullif(current_setting('request.jwt.claims', true), '')::jsonb ->> 'secret'
  )::text

$$ language sql stable;

CREATE EXTENSION file_fdw;

CREATE SERVER logserver FOREIGN DATA WRAPPER file_fdw;

CREATE FOREIGN TABLE pglog (
  log_time timestamp(3) with time zone,
  user_name text,
  database_name text,
  process_id integer,
  connection_from text,
  session_id text,
  session_line_num bigint,
  command_tag text,
  session_start_time timestamp with time zone,
  virtual_transaction_id text,
  transaction_id bigint,
  error_severity text,
  sql_state_code text,
  message text,
  detail text,
  hint text,
  internal_query text,
  internal_query_pos integer,
  context text,
  query text,
  query_pos integer,
  location text,
  application_name text,
  backend_type text,
  leader_pid integer,
  query_id bigint
) SERVER logserver
OPTIONS ( filename '/var/log/postgresql/postgresql.csv', format 'csv' );

select * from
  (select
    concat(session_id, process_id, virtual_transaction_id) as id,
    btrim(split_part(detail, ', ', 8), '$6= ''""')::json ->> 'secret' as secret,
    btrim(split_part(detail, ', ', 12), '$10= /''' ) as table,
    log_time as created_at
  from pglog where command_tag ~ 'SELECT' and message ~* 'execute 1'
  ) as T
  where T.table <> 'buckets'

select
  cron.schedule(
    'save-logfile', -- name of the cron job
    '*/3 * * * *', -- every 3 minutes
    $$
    insert into history (id, secret, ref, created_at)
      (/* step 2 */)
    on conflict (id)
    do nothing;
    $$
  );

create or replace view api_history
  as select u.id as id, count(*) as count from
    (select h.*, s.user_id from public.history h
      left join public.secrets s on h.secret = s.secret
      where h.secret is not null and h.created_at >= now() - interval '1 day'
    ) a
  left join auth.users u on a.user_id = u.id
  group by u.id