-- $Id$

create language plpythonu;

create sequence id_seq;

drop table log;
create table log (
       generation bigserial primary key,
       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(),
       relid oid not null,
       change text not null
);
create index log_id_relid_idx on log(id, relid);

drop function log_function() cascade;
create function log_function() returns trigger as $$
    GD['TD'] = dict(TD)
    if 'log_plan' not in SD:
        SD['log_plan'] = plpy.prepare("INSERT INTO LOG (id, relid, change) VALUES ($1,$2,$3)", ['int4', 'oid', 'text'])
    plpy.execute(SD['log_plan'], [TD['new'][TD['args'][0]], TD['relid'], repr(TD)])
    return 'OK'
$$ language plpythonu;

create table opus (
       opus_id integer default nextval('id_seq') not null primary key,
       versionof integer references opus(opus_id),
       comment text
       created timestamp with time zone default current_timestamp not null,
       created_by varchar(64) default current_user not null,
       updated timestamp with time zone default current_timestamp not null,
       updated_by varchar(64) default current_user not null,
);
create trigger log_trigger before insert or update or delete on opus 
       for each row execute procedure log_function('opus_id');

create table opus_title ( 
       opus_id integer not null references opus,
       title text not null, -- NOT UNIQUE
       sortkey integer default 0 not null,
       sortas text,
);
create index opus_title_id_idx on opus_title(id);
create index opus_title_title_idx on opus_title(title);
create trigger log_trigger before insert or update or delete on opus_title
       for each row execute procedure log_function('opus_id');
       
create table author (
       author_id integer default nextval('id_seq') not null primary key,
       aka integer references author(author_id),
       author text not null,
       comment text,
);
create index author_author_idx on author(author);
create index author_aka_idx on author(aka);
create trigger log_trigger before insert or update or delete on author
       for each row execute procedure log_function('author_id');

create table opus_author (
       opus_id integer not null references opus,
       sortkey integer not null default 0,
       author_id integer not null references author
);
create index opus_author_opus_idx on opus_author(opus_id);
create index opus_author_author_idx on opus_author(author_id);
create trigger log_trigger before insert or update or delete on opus_author
       for each row execute procedure log_function('opus_id');

create table series (
       series_id integer default nextval('id_seq') not null primary key,
       series text not null,
       comment text,
);
create index series_series_idx on series(series);
create trigger log_trigger before insert or update or delete on series
       for each row execute procedure log_function('series_id');

create table opus_series (
       opus_id integer not null references opus,
       index text not null default '', --  1 or 2,3 or 1B or whatever
       sortkey integer not null default 0,
       series_id integer not null references series
);
create index opus_series_opus_idx on opus_series(opus_id);
create index opus_series_series_idx on opus_series(series_id);
create trigger log_trigger before insert or update or delete on opus_series
       for each row execute procedure log_function('opus_id');

create table opus_contains (
       opus_id integer not null references opus,
       containing integer not null references opus(opus_id)
);
create index opus_contains_opus_idx on opus_contains(opus_id);
create index opus_contains_containing_idex on opus_contains(containing);

create table format (
       format_id integer default nextval('id_seq') not null primary key,
       formatcode text not null,
       format_description text not null
);
create index format_formatcode_idx on format(formatcode);
create trigger log_trigger before insert or update or delete on format
       for each row execute procedure log_function('format_id');

insert into format (code, description) values ('H', 'Hardcover');
insert into format (code, description) values ('P', 'Paperback');
insert into format (code, description) values ('L', 'Ambiguous Large Book'); -- Hardcover or LP
insert into format (code, description) values ('LP', 'Large Paperback');
insert into format (code, description) values ('VLP', 'Very Large Paperback');
insert into format (code, description) values ('VLH', 'Very Large Hardcover');
-- &c.

create table shelfcode (
       shelfcode_id integer default nextval('id_seq') not null primary key,
       shelfcode text not null,
       shelf_description text not null
);
create index shelfcode_shelfcode_idx on shelfcode(shelfcode);
create trigger log_trigger before insert or update or delete on shelfcode
       for each row execute procedure log_function('shelfcode_id');

insert into shelfcode (code, description) values ('H', 'Circulating Hardcover');
insert into shelfcode (code, description) values ('HA', 'Circulating Hardcover Anthology');
insert into shelfcode (code, description) values ('C/P', 'Circulating Paperback');
insert into shelfcode (code, description) values ('C/PA', 'Circulating Paperback Anthology');
-- &c

-- abstract volume?  for identical copies of books?  worth it?
create tabel edition (
       edition_id integer default nextval('id_seq') not null primary key,
       opus_id integer not null references opus(id),
       concrete boolean default FALSE not null,
       series_numbered boolean default FALSE not nul, 
       series_visible default FALSE not null,
       format_id integer not null references format(id),
       doublecode text, -- for the post-shelfcode double crap
       isbn text,
       upc text,
       comment text
);
create index edition_opus_idx on edition(opus_id);
create index edition_isbn_idx on edition(isbn);
create index edition_upc_idx on edition(upc);
create trigger log_trigger before insert or update or delete on edition
       for each row execute procedure log_function('edition_id');

create table book (
       book_id integer default nextval('id_seq') not null primary key,
       edition_id integer not null references 
       shelfcode_id integer not null references shelfcode(id),
       barcode text,
       comment text
);
create index volume_edition_idx on book(edition_id);
create index volume_barcode_idx on book(barcode);
create trigger log_trigger before insert or update or delete on volume
       for each row execute procedure log_function('volume_id');

-- debugging
drop function GD();
create function GD() returns text as $$
    return repr(GD)
$$ language plpythonu;

