create sequence auth_batch_jobs_job_id_seq;

create table auth_batch_jobs (
  job_id                     integer 
                             constraint auth_batch_jobs_pk
                             primary key,
  job_start_time             timestamptz default current_timestamp,
  job_end_time               timestamptz,
  interactive_p              boolean
                             constraint auth_batch_jobs_interactive_nn
                             not null,
  snapshot_p                 boolean
                             constraint auth_batch_jobs_snapshot_nn
                             not null,
  authority_id               integer
                             constraint auth_batch_jobs_auth_fk
                             references auth_authorities(authority_id),
  message                    text,
  -- if interactive, by which user
  creation_user              integer 
                             constraint auth_batch_job_user_fk
                             references users(user_id)
                             on delete set null,
  -- status information for the GetDocument operation
  doc_start_time             timestamptz,
  doc_end_time               timestamptz,
  doc_status                 text,
  doc_message                text,
  document_id                integer 
                             constraint auth_batch_jobs_doc_fk
                             references cr_items(item_id)
                             on delete set null
);

create index auth_batch_jobs_user_idx on auth_batch_jobs(creation_user);
create index auth_batch_jobs_document_idx on auth_batch_jobs(document_id);


create sequence auth_batch_job_entry_id_seq;

create table auth_batch_job_entries (
  entry_id                   integer   
                             constraint auth_batch_job_entries_pk 
                             primary key,
  job_id                     integer 
                             constraint auth_batch_job_entries_job_fk
                             references auth_batch_jobs(job_id)
                             on delete cascade,
  entry_time                 timestamptz default current_timestamp,
  operation                  varchar(100) 
                             constraint auth_batch_jobs_entries_op_ck
                             check (operation in ('insert', 'update', 'delete')),
  authority_id               integer 
                             constraint auth_batch_job_entries_auth_fk
                             references auth_authorities(authority_id)
                             on delete cascade,
  username                   varchar(100),
  user_id                    integer 
                             constraint auth_batch_job_entries_user_fk
                             references users(user_id) on delete set null,
  success_p                  boolean not null,
  message                    text,
  element_messages           text
);

create index auth_batch_job_ent_job_idx on auth_batch_job_entries(job_id);
create index auth_batch_job_ent_auth_idx on auth_batch_job_entries(authority_id);
create index auth_batch_job_ent_user_idx on auth_batch_job_entries(user_id);