create table bookmark (
  id                        integer not null,
  bookmark_reference        varchar(255) not null,
  user_id                   integer,
  constraint pk_bookmark primary key (id))
;

create table geo_place (
  id                        integer not null,
  esv_name                  varchar(255),
  root                      varchar(255),
  latitude                  double(17),
  longitude                 double(17),
  comment                   varchar(255),
  precision                 integer,
  constraint ck_geo_place_precision check (precision in (0,1,2)),
  constraint pk_geo_place primary key (id))
;

create table history (
  id                        integer not null,
  history_reference         varchar(255) not null,
  user_id                   integer,
  last_updated              timestamp not null,
  constraint pk_history primary key (id))
;

create table hot_spot (
  id                        integer not null,
  description               varchar(255),
  code                      varchar(255),
  scale                     integer,
  timeband_id               integer,
  constraint ck_hot_spot_scale check (scale in (0,1,2,3,4,5,6)),
  constraint pk_hot_spot primary key (id))
;

create table relational_person (
  id                        integer not null,
  name                      varchar(255),
  father_id                 integer,
  mother_id                 integer,
  code                      varchar(255),
  constraint uq_relational_person_code unique (code),
  constraint pk_relational_person primary key (id))
;

create table scripture_reference (
  id                        integer not null,
  geo_place_id              integer,
  timeline_event_id         integer,
  relational_person_id      integer,
  target_type               integer,
  start_verse_id            integer,
  end_verse_id              integer,
  constraint ck_scripture_reference_target_type check (target_type in (0,1)),
  constraint pk_scripture_reference primary key (id))
;

create table session (
  id                        integer not null,
  j_session_id              varchar(255),
  user_id                   integer,
  ip_address                varchar(255),
  expires_on                timestamp,
  constraint pk_session primary key (id))
;

create table timeband (
  id                        integer not null,
  code                      varchar(255),
  scale                     integer,
  description               varchar(255),
  constraint ck_timeband_scale check (scale in (0,1,2,3,4,5,6)),
  constraint pk_timeband primary key (id))
;

create table timeline_event (
  id                        integer not null,
  summary                   varchar(255),
  from_date                 timestamp,
  to_date                   timestamp,
  from_precision            integer,
  to_precision              integer,
  hot_spot_id               integer,
  constraint ck_timeline_event_from_precision check (from_precision in (0,1,2,3)),
  constraint ck_timeline_event_to_precision check (to_precision in (0,1,2,3)),
  constraint pk_timeline_event primary key (id))
;

create table users (
  id                        integer not null,
  name                      varchar(255),
  password                  varbinary(255) not null,
  salt                      varbinary(255) not null,
  email_address             varchar(255) not null,
  country                   varchar(255),
  constraint pk_users primary key (id))
;

create sequence bookmark_seq;

create sequence geo_place_seq;

create sequence history_seq;

create sequence hot_spot_seq;

create sequence relational_person_seq;

create sequence scripture_reference_seq;

create sequence session_seq;

create sequence timeband_seq;

create sequence timeline_event_seq;

create sequence users_seq;

alter table bookmark add constraint fk_bookmark_user_1 foreign key (user_id) references users (id) on delete restrict on update restrict;
create index ix_bookmark_user_1 on bookmark (user_id);
alter table history add constraint fk_history_user_2 foreign key (user_id) references users (id) on delete restrict on update restrict;
create index ix_history_user_2 on history (user_id);
alter table hot_spot add constraint fk_hot_spot_timeband_3 foreign key (timeband_id) references timeband (id) on delete restrict on update restrict;
create index ix_hot_spot_timeband_3 on hot_spot (timeband_id);
alter table relational_person add constraint fk_relational_person_father_4 foreign key (father_id) references relational_person (id) on delete restrict on update restrict;
create index ix_relational_person_father_4 on relational_person (father_id);
alter table relational_person add constraint fk_relational_person_mother_5 foreign key (mother_id) references relational_person (id) on delete restrict on update restrict;
create index ix_relational_person_mother_5 on relational_person (mother_id);
alter table scripture_reference add constraint fk_scripture_reference_geoPlac_6 foreign key (geo_place_id) references geo_place (id) on delete restrict on update restrict;
create index ix_scripture_reference_geoPlac_6 on scripture_reference (geo_place_id);
alter table scripture_reference add constraint fk_scripture_reference_timelin_7 foreign key (timeline_event_id) references timeline_event (id) on delete restrict on update restrict;
create index ix_scripture_reference_timelin_7 on scripture_reference (timeline_event_id);
alter table scripture_reference add constraint fk_scripture_reference_relatio_8 foreign key (relational_person_id) references relational_person (id) on delete restrict on update restrict;
create index ix_scripture_reference_relatio_8 on scripture_reference (relational_person_id);
alter table session add constraint fk_session_user_9 foreign key (user_id) references users (id) on delete restrict on update restrict;
create index ix_session_user_9 on session (user_id);
alter table timeline_event add constraint fk_timeline_event_hotSpot_10 foreign key (hot_spot_id) references hot_spot (id) on delete restrict on update restrict;
create index ix_timeline_event_hotSpot_10 on timeline_event (hot_spot_id);


