-------------------------------------------------------------------------------
-- Tables
-------------------------------------------------------------------------------

create table SALUTATIONS
(
  ID         INTEGER IDENTITY,
  SALUTATION VARCHAR(200),
  constraint SALU_UK unique (SALUTATION)
)

create table ACADEMICTITLES
(
  ID             INTEGER IDENTITY,
  ACADEMIC_TITLE VARCHAR(200) not null,
  constraint ACTI_UK unique (ACADEMIC_TITLE)
)

create table COUNTRIES
(
  ID      INTEGER IDENTITY,
  COUNTRY VARCHAR(200) not null,
  EU      CHAR(1) default 'N' not null,
  constraint CTRY_UK unique (COUNTRY)
)

create table STATES
(
  ID      INTEGER IDENTITY,
  CTRY_ID INTEGER not null,
  STATE   VARCHAR(200) not null,
  constraint STAT_UK unique (STATE),
  constraint STAT_CTRY_ID_FK foreign key (CTRY_ID) references COUNTRIES (ID)
)

create table DISTRICTS
(
  ID       INTEGER IDENTITY,
  STAT_ID  INTEGER not null,
  DISTRICT VARCHAR(200),
  constraint DIST_UK unique (DISTRICT),
  constraint DIST_STAT_ID_FK foreign key (STAT_ID) references STATES (ID)
)

create table HEALTHINSURANCES
(
  ID               INTEGER IDENTITY,
  HEALTH_INSURANCE VARCHAR(200) not null,
  constraint HEIN_UK unique (HEALTH_INSURANCE)
)

create table EDUCATIONS
(
  ID        INTEGER IDENTITY,
  EDUCATION VARCHAR(200),
  constraint EDUC_UK unique (EDUCATION)
)

create table CONTACTS
(
  ID            INTEGER IDENTITY,
  SALU_ID       INTEGER,
  ACTI_ID       INTEGER,
  FIRSTNAME     VARCHAR(200) not null,
  LASTNAME      VARCHAR(200) not null,
  STREET        VARCHAR(200),
  NR            VARCHAR(200),
  ZIP           VARCHAR(4),
  TOWN          VARCHAR(200),
  CTRY_ID       INTEGER,
  BIRTHDAY      DATE,
  SOCIALSECNR   DECIMAL(4),
  HEIN_ID       INTEGER,
  FILENAME      VARCHAR(200),
  IMAGE         BINARY,
  constraint CONT_SALU_ID_FK foreign key (SALU_ID) references SALUTATIONS (ID),
  constraint CONT_CTRY_ID_FK foreign key (CTRY_ID) references COUNTRIES (ID),
  constraint CONT_ACTI_ID_FK foreign key (ACTI_ID) references ACADEMICTITLES (ID),
  constraint CONT_HEIN_ID_FK foreign key (HEIN_ID) references HEALTHINSURANCES (ID)
)

create table CONT_EDUC
(
  ID      INTEGER IDENTITY,
  CONT_ID INTEGER not null,
  EDUC_ID INTEGER not null,
  constraint COED_UK unique (CONT_ID, EDUC_ID),
  constraint COED_CONT_ID_FK foreign key (CONT_ID) references CONTACTS (ID),
  constraint COED_EDUC_ID_FK foreign key (EDUC_ID) references EDUCATIONS (ID)
)

create table FOLDERS
(
  ID        INTEGER IDENTITY,
  FOLDER    VARCHAR(256) not null,
  FOLD_ID   INTEGER,
  constraint FOLD_UK unique (FOLD_ID, FOLDER),
  constraint FOLD_FOLD_ID_FK foreign key (FOLD_ID) references FOLDERS (ID) ON DELETE CASCADE
)

create table FILES
(
  ID         INTEGER IDENTITY,
  TYPE       VARCHAR(50) not null,
  FILENAME   VARCHAR(256) not null,
  FILESIZE   INTEGER not null,
  CREATED    DATE not null,
  CREATED_BY VARCHAR(64),
  CHANGED    DATE not null,
  CHANGED_BY VARCHAR(64),
  FOLD_ID    INTEGER,
  constraint FILE_UK unique (FOLD_ID, FILENAME),
  constraint FILE_FOLD_ID_FK foreign key (FOLD_ID) references FOLDERS (ID) ON DELETE CASCADE
)

-------------------------------------------------------------------------------
-- Views
-------------------------------------------------------------------------------

CREATE VIEW V_COUNTRIES AS
select c.ID
      ,c.COUNTRY
      ,c.EU
  from COUNTRIES c
 order by c.COUNTRY

CREATE VIEW V_STATES AS
select s.ID
      ,s.CTRY_ID
      ,s.STATE
  from STATES s
 order by s.STATE
 
CREATE VIEW V_DISTRICTS AS
select d.ID
      ,d.STAT_ID
      ,d.DISTRICT
  from DISTRICTS d
 order by d.DISTRICT

CREATE VIEW V_EDUCATIONS AS
select e.ID
      ,e.EDUCATION
  from EDUCATIONS e
 order by e.EDUCATION

CREATE VIEW V_CONTACTS AS
select C.ID
      ,C.SALU_ID
      ,(select S.SALUTATION from SALUTATIONS S where S.ID = C.SALU_ID) SALUTATION
      ,C.ACTI_ID
      ,(select T.ACADEMIC_TITLE from ACADEMICTITLES T where T.ID = C.ACTI_ID) ACADEMIC_TITLE
      ,C.FIRSTNAME
      ,C.LASTNAME
      ,C.STREET
      ,C.NR
      ,C.ZIP
      ,C.TOWN
      ,C.CTRY_ID
      ,(select CO.COUNTRY from COUNTRIES CO where CO.ID = C.CTRY_ID) COUNTRY
      ,C.BIRTHDAY
      ,C.SOCIALSECNR
      ,C.HEIN_ID
      ,(select I.HEALTH_INSURANCE from HEALTHINSURANCES I where I.ID = C.HEIN_ID) HEALTH_INSURANCE
      ,C.FILENAME
      ,C.IMAGE
  from CONTACTS C
   
CREATE VIEW V_CONT_EDUC AS
select CE.ID
      ,CE.CONT_ID
      ,CE.EDUC_ID
      ,(select E.EDUCATION from EDUCATIONS E where E.ID = CE.EDUC_ID) EDUCATION
  from CONT_EDUC CE