drop table author cascade constraints;
create table author (
  author_id                      number(10) primary key,
  first_name                     varchar2(20),
  last_name                      varchar2(30)
);

drop table obituary cascade constraints;
create table obituary (
  birth_date                     date,
  deceased_date                  date,
  obituary_text                  Clob,
  author_id                      number(10) primary key,
  foreign key (author_id) references author (author_id)
);

drop table publisher cascade constraints;
create table publisher (
  publisher_id                   number(10) primary key,
  name                           varchar2(100),
  sales_contact                  varchar2(100)
);

drop table category cascade constraints;
create table category (
  category_id                    number(10) primary key,
  description                    varchar2(100)
);

drop table title cascade constraints;
create table title (
  title_id                       number(10) primary key,
  name                           varchar2(100),
  author_id                      number(10)  not null,
  publisher_id                   number(10)  not null,
  category_id                    number(10)  not null,
  foreign key (author_id) references author (author_id),
  foreign key (publisher_id) references publisher (publisher_id),
  foreign key (category_id) references category (category_id)
);

drop table sale cascade constraints;
create table sale (
  sale_id                        number(10) primary key,
  name                           varchar2(100),
  start_date                     date,
  end_date                       date,
  discount                       number
);



drop table sale_title_xref;
create table sale_title_xref (
  sale_id                        number(10),
  title_id                       number(10),
  foreign key (sale_id) references sale ( sale_id ),
  foreign key (title_id) references title ( title_id ) 
);
  
 drop sequence  author_seq;
 create sequence author_seq
 increment by 1
 start with 1
 nomaxvalue
 nominvalue
 nocache;

 drop sequence  none;
 create sequence none
 increment by 1
 start with 1
 nomaxvalue
 nominvalue
 nocache;

 drop sequence  publisher_seq;
 create sequence publisher_seq
 increment by 1
 start with 1
 nomaxvalue
 nominvalue
 nocache;

 drop sequence  category_seq;
 create sequence category_seq
 increment by 1
 start with 1
 nomaxvalue
 nominvalue
 nocache;

 drop sequence  title_seq;
 create sequence title_seq
 increment by 1
 start with 1
 nomaxvalue
 nominvalue
 nocache;

 drop sequence  sale_seq;
 create sequence sale_seq
 increment by 1
 start with 1
 nomaxvalue
 nominvalue
 nocache;



drop table config;
create table config (
  name  VARCHAR2(250) NOT NULL PRIMARY KEY,
  value VARCHAR2(4000)
);
insert into config ( name, value ) values ( 'consolePassword',  'admin' );
insert into config ( name, value ) values ( 'logDirectory', 'logs' );
insert into config ( name, value ) values ( 'logDirectory2', 'logs' );
insert into config ( name, value ) values ( 'nodeInClusterThatRunsBatchJobs', 'server1' );

drop table application_logs;
create table application_logs (
  logger_name VARCHAR2(250) NOT NULL PRIMARY KEY,
  log_file VARCHAR2(250),
  logging_level VARCHAR2(10),
  rotate_log_file_at number(10),
  log_files_in_rotation number(5)
);

insert into application_logs (
  logger_name,
  log_file,
  logging_level,
  rotate_log_file_at,
  log_files_in_rotation
)
values ( 
 'com.jdonohue.bookstore',
 'com.jdonohue.bookstore.log',
 'FINEST', 
 9999999,
 1
);
insert into application_logs (
  logger_name,
  log_file,
  logging_level,
  rotate_log_file_at,
  log_files_in_rotation
)
values ( 
 'com.jdonohue.bookstore.Author',
 'com.jdonohue.bookstore.Author.log',
 'FINEST', 
 9999999,
 1
);
insert into application_logs (
  logger_name,
  log_file,
  logging_level,
  rotate_log_file_at,
  log_files_in_rotation
)
values ( 
 'com.jdonohue.bookstore.Obituary',
 'com.jdonohue.bookstore.Obituary.log',
 'FINEST', 
 9999999,
 1
);
insert into application_logs (
  logger_name,
  log_file,
  logging_level,
  rotate_log_file_at,
  log_files_in_rotation
)
values ( 
 'com.jdonohue.bookstore.Publisher',
 'com.jdonohue.bookstore.Publisher.log',
 'FINEST', 
 9999999,
 1
);
insert into application_logs (
  logger_name,
  log_file,
  logging_level,
  rotate_log_file_at,
  log_files_in_rotation
)
values ( 
 'com.jdonohue.bookstore.Category',
 'com.jdonohue.bookstore.Category.log',
 'FINEST', 
 9999999,
 1
);
insert into application_logs (
  logger_name,
  log_file,
  logging_level,
  rotate_log_file_at,
  log_files_in_rotation
)
values ( 
 'com.jdonohue.bookstore.Title',
 'com.jdonohue.bookstore.Title.log',
 'FINEST', 
 9999999,
 1
);
insert into application_logs (
  logger_name,
  log_file,
  logging_level,
  rotate_log_file_at,
  log_files_in_rotation
)
values ( 
 'com.jdonohue.bookstore.Sale',
 'com.jdonohue.bookstore.Sale.log',
 'FINEST', 
 9999999,
 1
);