--
-- Library system data model
--
-- ryanlee@arsdigita.com, 8/30/2000
--

create sequence library_id_seq start 1;

create table library (
       -- primary key for books
       book_id	      integer not null primary key,
       -- main title, not including refernces to edition, etc.
       title	      varchar(4000) not null,
       -- which edition is it
       edition	      integer,
       -- unique identifier for book purchasing
       isbn	      varchar(10) not null,
       -- date published      
       pub_date       date,
       -- number of pages
       pages	      integer
);

create sequence library_author_id_seq start 1;

create table library_authors (
       -- primary key for authors
       author_id      integer not null primary key,
       -- name
       author_name    varchar(1000) not null
);

create sequence library_publisher_id_seq start 1;

create table library_publishers (
       -- primary key for publishers
       publisher_id   integer not null primary key,
       -- publisher name
       publisher_name varchar(1000) not null
);

-- map between authors, books, and publishers
create table book_author_map (
       -- the id of the book
       book_id	      integer references library,
       -- the id of the author
       author_id      integer references library_authors,
       constraint bk_at_unique unique(book_id, author_id)
);

create table book_pub_map (
       -- the id of the book
       book_id	      integer references library,
       -- the id of the publisher
       publisher_id   integer references library_publishers,
       constraint bk_pub_unique unique(book_id, publisher_id)
);

create sequence user_book_map_seq start 1;

-- there is no uniqueness constraint here; people can own
-- more than one copy
create table user_book_map (
       -- primary key for this table
       map_id         integer primary key not null,
       -- the id of the book
       book_id	      integer references library,
       -- the owner of the book
       user_id	      integer references users,
       -- the status of their copy
       status	      varchar(30) not null,
       constraint bk_status_chk check (status in ('new','good','used','poor','bad')),
       -- does the rest of the world know they have this book?
       private_p      char(1) default 'f',
       constraint bk_owner_private_chk check (private_p in ('t','f')),
       borrowable_p   char(1) default 't',
       constraint bk_owner_borrowable_chk check (borrowable_p in ('t','f')),
       auth_needed_p  char(1) default 'f',
       constraint bk_owner_auth_chk check (auth_needed_p in ('t','f'))
);

create table book_synopsis (
       -- synopsis id
       book_synopsis_id    integer not null primary key,
       -- reference the book id
       book_id	           integer not null references library,
       -- who wrote this synopsis
       user_id		   integer not null references users,
       creation_date	   timestamp not null default now(),
       last_modified	   timestamp
);

create sequence owner_prefs_id_seq;
-- preferences
create table owner_prefs (
       prefs_id            integer not null primary key,
       owner_id            integer not null references users,
       -- assess fines?
       fines_p             char(1) not null default 'f',
       constraint owner_prefs_fines_p_chk check (fines_p in ('t','f')),
       -- size of fine, if applicable
       fine_size           numeric,
       -- rate of colletion (per day, per week, per month, absolute total)
       fine_rate           varchar(30) default 'total',
       constraint owner_prefs_fine_rate_chk check (fine_rate in ('total','day','week','month')),
       -- limit in days
       borrow_limit        integer not null default 0
);

-- categorize with aD categorization system

-- look up book info on a .com and stuff it here for faster
-- access to information
-- create table library_lookup_cache (
-- );

-- figure out the true first letter of a proper title
CREATE FUNCTION true_first_letter(
       integer
)
RETURNS varchar
AS 'declare
	v_title	  text;
	v_char    varchar;
	v_book_id alias for $1;
begin
	SELECT title INTO v_title
	FROM   library
        WHERE  book_id = v_book_id;

	v_char := substr(v_title, 1, 1);
	IF length(v_title) > 4 THEN
		IF lower(substr(v_title, 1, 4)) = ''the ''::text THEN
			v_char := substr(v_title, 5, 1);
		 ELSE IF lower(substr(v_title, 1, 3)) = ''an ''::text THEN
			v_char := substr(v_title, 4, 1);
		 ELSE IF lower(substr(v_title, 1, 2)) = ''a ''::text THEN
			v_char := substr(v_title, 3, 1);
		 END IF;
		END IF;
               END IF;
	END IF;

	return v_char;
end;'
LANGUAGE 'plpgsql';