-- note that actually distinct field names need to be relatively unique for
-- natural join to work correctly
set role "speaker-to-postgres";

-- uncomment the following if you are doing this for real; having a
-- script around that drops the database makes me nervous.

-- delete from book;
-- drop table book cascade;
-- delete from shelfcode;
-- drop table shelfcode cascade;
-- delete from series;
-- drop table series cascade;
-- delete from title_series;
-- drop table title_series cascade;
-- delete from title_responsibility;
-- drop table title_responsibility cascade;
-- delete from title_responsibility_type;
-- drop table title_responsibility_type cascade;
-- delete from title_title;
-- drop table title_title cascade;
-- delete from title_title_type;
-- drop table title_title_type cascade;
-- delete from title;
-- drop table title cascade;
-- delete from entity;
-- drop table entity cascade;
-- delete from entity_type;
-- drop table entity_type cascade;
-- drop sequence id_seq cascade;
-- drop table log cascade;

create sequence id_seq;
grant select on id_seq to public;
grant update on id_seq to keyholders;

create table log (
       generation bigserial primary key,
       obj_id integer not null,
       stamp timestamp with time zone default current_timestamp not null,
       username varchar(64) default current_user not null,
       ip inet default inet_client_addr(),
       client text not null,
       relid oid not null,
       change text not null
);
create index log_id_relid_idx on log(obj_id, relid);

grant select, insert on log to keyholders;

select nextval('log_generation_seq');
-- so the pump is primed for exports

grant select, update on log_generation_seq to keyholders;

create table title (
       title_id integer default nextval('id_seq') not null primary key,
       title_comment text,
       title_created timestamp with time zone default current_timestamp not null,
       title_created_by varchar(64) default current_user not null,
       title_created_with varchar(64) default 'SQL' not null,
       title_modified timestamp with time zone default current_timestamp not null,
       title_modified_by varchar(64) default current_user not null,
       title_modified_with varchar(64) default 'SQL' not null,
       title_lost boolean not null default false);

grant select on title to public;
grant insert, update, delete on title to panthercomm;

create table entity_type (
       entity_type char(1) not null primary key,
       description text);

grant select on entity_type to public;
grant insert, update, delete on entity_type to libcomm;

insert into entity_type values ('C', 'Corporation');
insert into entity_type values ('H', 'Human');
insert into entity_type values ('S', 'Pseudonym');
insert into entity_type values ('?', 'Unspecified');

create table entity (
       entity_id integer default nextval('id_seq') not null primary key,
       -- aka integer references entity(entity_id),
       entity_name text not null,
       entity_type char(1) default 'H' not null references entity_type,
       aka integer references entity(entity_id),
       entity_note text,
       entity_comment text,
       entity_created timestamp with time zone default current_timestamp not null,
       entity_created_by varchar(64) default current_user not null,
       entity_created_with varchar(64) default 'SQL' not null,
       entity_modified timestamp with time zone default current_timestamp not null,
       entity_modified_by varchar(64) default current_user not null,
       entity_modified_with varchar(64) default 'SQL' not null,
       unique(entity_name, entity_note));
--alter table entity add foreign key (aka)
create index entity_name_idx on entity(entity_name);

grant select on entity to public;
grant insert, update, delete on entity to panthercomm;

create table title_responsibility_type (
       responsibility_type char(1) not null primary key,
       description text);

grant select on title_responsibility_type to public;
grant insert, update, delete on title_responsibility_type to libcomm;

insert into title_responsibility_type values ('?', 'Unspecified');
insert into title_responsibility_type values ('=', 'Sort As');
insert into title_responsibility_type values ('A', 'Author');
insert into title_responsibility_type values ('E', 'Editor');
insert into title_responsibility_type values ('P', 'Publisher');

create table title_responsibility (
       title_id integer not null references title,
       entity_id integer not null references entity,
       order_responsibility_by integer not null default 0,
       responsibility_type char(1) default 'A' not null references title_responsibility_type,
       unique (title_id, order_responsibility_by),
       check (responsibility_type != '=' or order_responsibility_by = 0));
create index title_responsibility_id_idx on title_responsibility(title_id);
create index title_responsibility_entity_id_idx on title_responsibility(entity_id);

grant select on title_responsibility to public;
grant insert, update, delete on title_responsibility to panthercomm;

create table title_title_type (
       title_type char(1) not null primary key,
       tityle_type_description text);

grant select on title_title_type to public;
grant insert, update, delete on title_title_type to libcomm;

insert into title_title_type values ('=', 'Sort As');
insert into title_title_type values ('T', 'Title');
insert into title_title_type values ('N', 'Note');

create table title_title (
       title_id integer not null references title,
       title_name text not null,
       order_title_by integer not null default 0,
       title_type char(1) default 'T' not null references title_title_type,
       unique (title_id, order_title_by),
       check (title_type != '=' or order_title_by = 0));

grant select on title_title to public;
grant insert, update, delete on title_title to panthercomm;

create table series (
       series_id integer default nextval('id_seq') not null primary key,
       series_name text not null,
       series_comment text,
       series_created timestamp with time zone default current_timestamp not null,
       series_created_by varchar(64) default current_user not null,
       series_created_with varchar(64) default 'SQL' not null,
       series_modified timestamp with time zone default current_timestamp not null,
       series_modified_by varchar(64) default current_user not null,
       series_modified_with varchar(64) default 'SQL' not null,
       unique(series_name));

grant select on series to public;
grant insert, update, delete on series to panthercomm;

create table title_series (
       title_id integer not null references title,
       series_id integer not null,
       series_index text,
       order_series_by integer not null default 0,
       series_visible boolean not null default false,
       number_visible boolean not null default false,
       unique (title_id, order_series_by));

grant select on title_series to public;
grant insert, update, delete on title_series to panthercomm;

create table shelfcode (
       shelfcode_id integer default nextval('id_seq') not null primary key,
       shelfcode text unique not null,
       shelfcode_type char(1) default 'R' not null,
       shelfcode_description text not null);

grant select on shelfcode to public;
grant insert, update, delete on shelfcode to libcomm;
create index shelfcode_shelfcode_type on shelfcode(shelfcode_type);

create table book (
       book_id integer default nextval('id_seq') not null primary key,
       title_id integer not null references title,
       book_series_visible boolean not null default false,
       shelfcode_id integer not null references shelfcode,
       doublecrap text,
       book_comment text,
       book_created timestamp with time zone default current_timestamp not null,
       book_created_by varchar(64) default current_user not null,
       book_created_with varchar(64) default 'SQL' not null,
       book_modified timestamp with time zone default current_timestamp not null,
       book_modified_by varchar(64) default current_user not null,
       book_modified_with varchar(64) default 'SQL' not null,
       withdrawn boolean not null default false,
       review boolean not null default false);
create index book_title_idx on book(title_id);

grant select on book to public;
grant insert, update, delete on book to panthercomm;

create table barcode (
       book_id integer not null references book,
       barcode text unique primary key not null);
       barcode_created timestamp with time zone default current_timestamp not null,
       barcode_created_by varchar(64) default current_user not null,
       barcode_created_with varchar(64) default 'SQL' not null,
       barcode_modified timestamp with time zone default current_timestamp not null,
       barcode_modified_by varchar(64) default current_user not null,
       barcode_modified_with varchar(64) default 'SQL' not null,
create index barcode_book_id_idx on barcode(book_id);

grant select on barcode to public;
grant insert on barcode to keyholders;
grant update, delete on barcode to panthercomm;

create or replace view shelf_count as
 select title_id, shelfcode, count(shelfcode) as bookcount
  from book natural join shelfcode
  where not withdrawn
  group by title_id, shelfcode;

grant select on shelf_count to public;

create view atdex as
 select title_id,
  array_to_string(array(select entity_name
                         from title_responsibility natural join entity
                         where title_responsibility.title_id = title.title_id
                         order by order_responsibility_by),'|') as author,
  array_to_string(array(select title_name
                          from title_title
                          where title_title.title_id = title.title_id
                          order by order_title_by),'|') as title
 from title
 order by author, title;
  

-- create view pinkdex as select 
--   title_id, author, title,
--   array_to_string(array(select shelfcode
--                          from shelf_count
--                          where shelf_count.title_id = atdex.title_id and bookcount = 1
--                         union all
--                         select shelfcode||':'||bookcount
--                          from shelf_count
--                          where shelf_count.title_id = atdex.title_id and bookcount > 1
--                         order by shelfcode),',') as codes
--  from atdex
--  order by author, title;

create or replace view pinkdex as select * from
  (select title_id,
     array_agg(entity_name) as authors,
     array_agg(entity_id) as entity_ids,
     array_agg(responsibility_type) as responsibility_types,
     array_agg(order_responsibility_by) as order_responsibility_bys
   from title_responsibility natural join entity
   group by title_id) as authors
  natural join
  (select title_id,
   array_agg(title_name) as titles,
   array_agg(title_type) as title_types,
   array_agg(order_title_by) as order_title_bys
   from title_title
   group by title_id) as titles
  natural join
  (select title_id,
   array_agg((case when book_series_visible then '@' else '' end) ||
             shelfcode || coalesce(doublecrap,'')) as shelfcodes,
   array_agg(shelfcode_id) as shelfcode_ids,
   array_agg(shelfcode_type) as shelfcode_types
   from book natural join shelfcode
   where not withdrawn
   group by title_id) as shelfcodes
  natural left join
  (select title_id,
     array_agg(series_name) as series,
     array_agg(series_index) as series_indexes,
     array_agg(series_visible) as series_visibles,
     array_agg(number_visible) as number_visibles,
     array_agg(series_id) as series_ids,
     array_agg(order_series_by) as order_series_bys
   from title_series natural join series
   group by title_id) as series;

grant select on pinkdex to public;

create table member (
       member_id integer default nextval('id_seq') not null primary key,
       member_pseudo boolean default false not null,
       rolname name, -- references pg_authid(rolname) -- cannot reference a system catalog, sadly
       
       member_created timestamp with time zone default current_timestamp not null,
       member_created_by varchar(64) default current_user not null,
       member_created_with varchar(64) default current_client() not null,
       member_modified timestamp with time zone default current_timestamp not null,
       member_modified_by varchar(64) default current_user not null,
       member_modified_with varchar(64) default current_client() not null);

create trigger member_insert
       before insert on member for each row execute procedure insert_row_created_with();
create trigger member_update
       before update on member for each row execute procedure update_row_modified();
create trigger member_log
       before insert or update or delete on member for each row execute procedure log_row();

grant insert, update, select on member to keyholders;

reset role;

