DROP TABLE IF EXISTS stg_zugeordnet_lv    CASCADE;
DROP TABLE IF EXISTS lehr_haelt_lv        CASCADE;
DROP TABLE IF EXISTS lehr_zugeordnet_stg  CASCADE;
DROP TABLE IF EXISTS fk_beteiligt_stg     CASCADE;
DROP TABLE IF EXISTS amt_stg              CASCADE;
DROP TABLE IF EXISTS amt_fk               CASCADE;

DROP TABLE IF EXISTS amtsbeschreibung     CASCADE;
DROP TABLE IF EXISTS lehrveranstaltung    CASCADE;
DROP TABLE IF EXISTS professor            CASCADE;
DROP TABLE IF EXISTS lehrender            CASCADE;
DROP TABLE IF EXISTS studiengang          CASCADE;
DROP TABLE IF EXISTS fakultaet            CASCADE;

DROP DOMAIN IF EXISTS D_SPRECHSTUNDE      CASCADE;
DROP DOMAIN IF EXISTS D_DIENSTZIMMER      CASCADE;
DROP DOMAIN IF EXISTS D_TELEFON           CASCADE;
DROP DOMAIN IF EXISTS D_EMAIL             CASCADE;
DROP DOMAIN IF EXISTS D_NAME              CASCADE;
DROP DOMAIN IF EXISTS D_TITEL             CASCADE;
DROP DOMAIN IF EXISTS D_KUERZEL           CASCADE;
DROP DOMAIN IF EXISTS D_STATUS_LEHRENDER  CASCADE;
DROP DOMAIN IF EXISTS D_ABSCHLUSS         CASCADE;
DROP DOMAIN IF EXISTS D_ANREDE            CASCADE;


CREATE DOMAIN D_ANREDE AS 
  CHAR(4)     
  CHECK (VALUE IN ('Herr', 'Frau'))
;

CREATE DOMAIN D_ABSCHLUSS AS
  VARCHAR(8)
  CHECK (VALUE IN ('Bachelor', 'Master'))
;

CREATE DOMAIN D_STATUS_LEHRENDER AS
  VARCHAR(16)
  CHECK (VALUE IN ('Professor', 'Lehrbeauftragter'))
;

CREATE DOMAIN D_KUERZEL AS
  VARCHAR(10)
  CHECK (VALUE ~ '\A[\wäöüß_.\-$]+\Z')
;

CREATE DOMAIN D_TITEL AS
  VARCHAR(20)
  CHECK (VALUE ~ '\A[\wäöüß_.@$+\-/()\s]+\Z')
;

CREATE DOMAIN D_NAME AS
  VARCHAR(100)
  CHECK (VALUE ~ '\A[\wäöüß_.@$+\-/()\s]+\Z')
;

CREATE DOMAIN D_EMAIL AS
  VARCHAR(100)
  -- W3C: https://www.regextester.com/97767
  CHECK (VALUE ~* '\A[a-z0-9.!#$%&''*+/=?^_`{|}~-]+@[a-z0-9-]+(?:\.[a-z0-9-]+)*\Z')
;

CREATE DOMAIN D_TELEFON AS
  VARCHAR(100)
  CHECK (VALUE ~ '\A[0-9][0-9\-/()\s]*\Z')
;

CREATE DOMAIN D_DIENSTZIMMER AS
  VARCHAR(6)
  CHECK (VALUE ~ '\A[A-Z][0-9][.][0-9][0-9][a-z]?\Z')
;

CREATE DOMAIN D_SPRECHSTUNDE AS
  VARCHAR(50)
  CHECK (VALUE ~ '\A[\wäöüß_.!:,;+\-/()\s]+\Z')
;

CREATE TABLE fakultaet
  (f_id            INTEGER   NOT NULL,
   f_kuerzel       D_KUERZEL NOT NULL,
   f_name          D_NAME    NOT NULL,
   f_email         D_EMAIL,    
   f_tel           D_TELEFON NOT NULL,

   CONSTRAINT pk_fakultaet
     PRIMARY KEY (f_id),

   CONSTRAINT unique_fakultaet_kuerzel
     UNIQUE (f_kuerzel)
  );

CREATE TABLE studiengang
  (s_id            INTEGER     NOT NULL,
   s_kuerzel       D_KUERZEL   NOT NULL,
   s_name          D_NAME      NOT NULL,
   s_abschluss     D_ABSCHLUSS NOT NULL,
   s_titel         D_NAME      NOT NULL,
   s_zugeordnet_zu INTEGER     NOT NULL,

   CONSTRAINT pk_studiengang
     PRIMARY KEY (s_id),

   CONSTRAINT unique_studiengang_kuerzel
     UNIQUE (s_kuerzel),

   CONSTRAINT fk_studiengang_zugeordnet_zu_fakultaet
     FOREIGN KEY (s_zugeordnet_zu) REFERENCES fakultaet (f_id)
  );

CREATE TABLE lehrender
  (l_id            INTEGER            NOT NULL,
   l_kuerzel       D_KUERZEL          NOT NULL,
   l_anrede        D_ANREDE           NOT NULL,
   l_titel         D_TITEL,    
   l_name          D_NAME             NOT NULL,
   l_email         D_EMAIL,    
   l_zugeordnet_zu INTEGER            NOT NULL,
   l_status        D_STATUS_LEHRENDER NOT NULL DEFAULT 'Lehrbeauftragter',

   CONSTRAINT pk_lehrender
     PRIMARY KEY (l_id),

   CONSTRAINT unique_lehrender_kuerzel
     UNIQUE (l_kuerzel),

   CONSTRAINT fk_lehrender_zugeordnet_zu_fakultaet
     FOREIGN KEY (l_zugeordnet_zu) REFERENCES fakultaet (f_id)
  );

CREATE TABLE professor
  (l_id            INTEGER        NOT NULL,
   p_dienstzimmer  D_DIENSTZIMMER,
   p_sprechstunde  D_SPRECHSTUNDE NOT NULL,

   CONSTRAINT pk_professor
     PRIMARY KEY (l_id),

   CONSTRAINT fk_professor_is_a_lehrender
     FOREIGN KEY (l_id) REFERENCES lehrender (l_id)
  );

CREATE TABLE lehrveranstaltung
  (lv_id            INTEGER   NOT NULL,
   lv_kuerzel       D_KUERZEL NOT NULL,
   lv_titel         D_NAME    NOT NULL,
   lv_zugeordnet_zu INTEGER   NOT NULL,

   CONSTRAINT pk_lehrveranstaltung
     PRIMARY KEY (lv_id),

   CONSTRAINT fk_lehrveranstaltung_zugeordnet_zu_fakultaet
     FOREIGN KEY (lv_zugeordnet_zu) REFERENCES fakultaet (f_id)
  );

CREATE TABLE amtsbeschreibung
  (ab_id            INTEGER     NOT NULL,
   ab_bez_weiblich  VARCHAR(50) NOT NULL,
   ab_bez_maennlich VARCHAR(50) NOT NULL,
   ab_bez_plural    VARCHAR(50) NOT NULL,

   CONSTRAINT pk_amtsbeschreibung
     PRIMARY KEY (ab_id)
  );




CREATE TABLE amt_fk
  (f_id           INTEGER     NOT NULL,
   l_id           INTEGER     NOT NULL,
   ab_id          INTEGER     NOT NULL,

   CONSTRAINT pk_amt_fk
     PRIMARY KEY (l_id, ab_id),

   CONSTRAINT fk_amt_fk_fakultaet
     FOREIGN KEY (f_id) REFERENCES fakultaet (f_id),
   CONSTRAINT fk_amt_fk_professor
     FOREIGN KEY (l_id) REFERENCES professor (l_id),
   CONSTRAINT fk_amt_fk_amtsbeschreibung
     FOREIGN KEY (ab_id) REFERENCES amtsbeschreibung (ab_id)
  );

CREATE TABLE amt_stg
  (s_id           INTEGER     NOT NULL,
   l_id           INTEGER     NOT NULL,
   ab_id          INTEGER     NOT NULL,

   CONSTRAINT pk_amt_stg
     PRIMARY KEY (s_id, l_id, ab_id),

   CONSTRAINT fk_amt_stg_studiengang
     FOREIGN KEY (s_id) REFERENCES studiengang (s_id),
   CONSTRAINT fk_amt_stg_professor
     FOREIGN KEY (l_id) REFERENCES professor (l_id),
   CONSTRAINT fk_amt_stg_amtsbeschreibung
     FOREIGN KEY (ab_id) REFERENCES amtsbeschreibung (ab_id)
  );

CREATE TABLE fk_beteiligt_stg
  (f_id           INTEGER     NOT NULL,
   s_id           INTEGER     NOT NULL,

   CONSTRAINT pk_fk_beteiligt_stg
     PRIMARY KEY (f_id, s_id),

   CONSTRAINT fk_fk_beteiligt_stg_fakultaet
     FOREIGN KEY (f_id) REFERENCES fakultaet (f_id),
   CONSTRAINT fk_fk_beteiligt_stg_studiengang
     FOREIGN KEY (s_id) REFERENCES studiengang (s_id)
  );

CREATE TABLE lehr_zugeordnet_stg
  (l_id           INTEGER     NOT NULL,
   s_id           INTEGER     NOT NULL,

   CONSTRAINT pk_lehr_zugeordnet_stg
     PRIMARY KEY (l_id, s_id),

   CONSTRAINT fk_lehr_zugeordnet_stg_lehrender
     FOREIGN KEY (l_id) REFERENCES lehrender (l_id),
   CONSTRAINT fk_lehr_zugeordnet_stg_studiengang
     FOREIGN KEY (s_id) REFERENCES studiengang (s_id)
  );

CREATE TABLE lehr_haelt_lv
  (l_id           INTEGER     NOT NULL,
   lv_id          INTEGER     NOT NULL,

   CONSTRAINT pk_lehr_haelt_lv
     PRIMARY KEY (l_id, lv_id),

   CONSTRAINT fk_lehr_haelt_lv_lehrender
     FOREIGN KEY (l_id) REFERENCES lehrender (l_id),
   CONSTRAINT fk_lehr_haelt_lv_lehrveranstaltung
     FOREIGN KEY (lv_id) REFERENCES lehrveranstaltung (lv_id)
  );

CREATE TABLE stg_zugeordnet_lv
  (s_id           INTEGER     NOT NULL,
   lv_id          INTEGER     NOT NULL,

   CONSTRAINT pk_stg_zugeordnet_lv
     PRIMARY KEY (s_id, lv_id),

   CONSTRAINT fk_stg_zugeordnet_lv_studiengang
     FOREIGN KEY (s_id) REFERENCES studiengang (s_id),
   CONSTRAINT fk_stg_zugeordnet_lv_lehrveranstaltung
     FOREIGN KEY (lv_id) REFERENCES lehrveranstaltung (lv_id)
  );