/**************************************************************************************

    Simple Library System
    Copyright (C) 2002 John Mark Matthews

    This program is free software; you can redistribute it and/or modify
    it under the terms of the GNU General Public License as published by
    the Free Software Foundation; either version 2 of the License, or
    (at your option) any later version.

    This program is distributed in the hope that it will be useful,
    but WITHOUT ANY WARRANTY; without even the implied warranty of
    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
    GNU General Public License for more details.

    You should have received a copy of the GNU General Public License
    along with this program; if not, write to the Free Software
    Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
    
    For further information, contact:
    	John Matthews
    	jmatthews@exostrategy.com

***************************************************************************************/
drop table if exists tbl_login;
create table tbl_login
     (login_id       integer auto_increment primary key, 
      login          varchar(68) unique, 
      pwd	     varchar(68),
      admin_flag     integer,
      email          varchar(68), 
      create_td timestamp);

drop table if exists tbl_login_status;
create table tbl_login_status
     (login_id integer, 
      login_td timestamp, 
      session_seq varchar(100), 
      ip_addy varchar(100));

drop table if exists tbl_person; 
create table tbl_person 
     (person_id   integer auto_increment primary key, 
      login_id    integer,
      last_name   varchar(64), 
      first_name  varchar(64), 
      address_info varchar(255),
      create_td   timestamp);
      

drop table if exists tbl_item;
create table tbl_item
     (item_id     integer auto_increment primary key,
      item_type   integer,
      title	  text,
      subject	  text,
      abstract	  text,
      author	  text,
      status	  integer,
      login_id    integer,
      due_date    date,
      FULLTEXT(title,author,abstract));

drop table if exists tbl_item_history;
create table tbl_item_history
     (item_id     integer,
      status	  integer,
      login_id    integer,
      create_td   timestamp);

drop table if exists tbl_item_request;
create table tbl_item_request
     (request_id  integer auto_increment primary key,
      item_id     integer,
      login_id    integer,
      request     integer,
      create_td   timestamp);
      
drop table if exists tbl_status;
create table tbl_status
     (status integer,
      status_desc varchar(255));
      
drop table if exists tbl_request;
create table tbl_request
     (request integer,
      request_desc varchar(255));
      
drop table if exists tbl_item_type;
create table tbl_item_type
     (item_type integer,
      item_type_desc varchar(255));
      
drop table if exists tbl_subject;
create table tbl_subject
     (subject text);
      
drop table if exists tbl_review;
create table tbl_review
     (item_id     integer,
      login_id    integer,
      grade       integer,
      review      text,
      create_td   timestamp);
      
drop table if exists tbl_review_summary;
create table tbl_review_summary
     (item_id      integer,
      avg_grade    integer,
      total_review integer);

drop table if exists tbl_grade;
create table tbl_grade
     (grade      integer,
      grade_desc varchar(255));

insert into tbl_status (status, status_desc) values (1, 'Available');
insert into tbl_status (status, status_desc) values (2, 'Checked Out');
insert into tbl_status (status, status_desc) values (4, 'Removed');

insert into tbl_request (request, request_desc) values (1, 'Check Out');

insert into tbl_item_type (item_type, item_type_desc) values (1, 'Book');
insert into tbl_item_type (item_type, item_type_desc) values (2, 'Periodical');
insert into tbl_item_type (item_type, item_type_desc) values (4, 'Video');
insert into tbl_item_type (item_type, item_type_desc) values (8, 'Audio');
insert into tbl_item_type (item_type, item_type_desc) values (16, 'Binder');
insert into tbl_item_type (item_type, item_type_desc) values (32, 'Pamphlet');

insert into tbl_login(login,pwd,admin_flag)values('admin','nimda',1);
insert into tbl_person(login_id,last_name,first_name,address_info)values(1,'Admin','Default','');

insert into tbl_subject(subject)values('Attitudes');
insert into tbl_subject(subject)values('Business');
insert into tbl_subject(subject)values('Communication');
insert into tbl_subject(subject)values('Customer Service / Telephone Skills');
insert into tbl_subject(subject)values('Employment');
insert into tbl_subject(subject)values('Management / Supervisory');
insert into tbl_subject(subject)values('Motivation / Success');
insert into tbl_subject(subject)values('Personal');
insert into tbl_subject(subject)values('Professional Development');
insert into tbl_subject(subject)values('Sales & Marketing');
insert into tbl_subject(subject)values('Teamwork');
insert into tbl_subject(subject)values('Training');

truncate table tbl_grade;
insert into tbl_grade(grade,grade_desc)values(0,'No Stars');
insert into tbl_grade(grade,grade_desc)values(1,'1 Star');
insert into tbl_grade(grade,grade_desc)values(2,'2 Stars');
insert into tbl_grade(grade,grade_desc)values(3,'3 Stars');
insert into tbl_grade(grade,grade_desc)values(4,'4 Stars');
insert into tbl_grade(grade,grade_desc)values(5,'5 Stars');
commit;

truncate tbl_review;
truncate tbl_review_summary;
insert into tbl_review_summary(item_id,avg_grade,total_review) select item_id,0,0 from tbl_item;