DROP TABLE IF EXISTS stg_zugeordnet_lv    CASCADE;
DROP TABLE IF EXISTS lp_haelt_lv          CASCADE;
DROP TABLE IF EXISTS lp_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 amtsbezeichnung      CASCADE;
DROP TABLE IF EXISTS lehrveranstaltung    CASCADE;
DROP TABLE IF EXISTS professor            CASCADE;
DROP TABLE IF EXISTS lehrperson           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_LEHRPERSON 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_LEHRPERSON AS
  VARCHAR(16)
  CHECK (VALUE IN ('Professor', 'Lehrbeauftragter', 'Lehrkraft'))
;

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
  (fk_id            INTEGER   NOT NULL,
   fk_kuerzel       D_KUERZEL NOT NULL,
   fk_name          D_NAME    NOT NULL,
   fk_email         D_EMAIL,    
   fk_tel           D_TELEFON NOT NULL,

   CONSTRAINT pk_fk
     PRIMARY KEY (fk_id),

   CONSTRAINT unique_fk_kuerzel
     UNIQUE (fk_kuerzel)
  );

CREATE TABLE studiengang
  (stg_id            INTEGER     NOT NULL,
   stg_kuerzel       D_KUERZEL   NOT NULL,
   stg_name          D_NAME      NOT NULL,
   stg_abschluss     D_ABSCHLUSS NOT NULL,
   stg_titel         D_NAME      NOT NULL,
   stg_zugeordnet_zu INTEGER     NOT NULL,

   CONSTRAINT pk_studiengang
     PRIMARY KEY (stg_id),

   CONSTRAINT unique_studiengang_kuerzel
     UNIQUE (stg_kuerzel),

   CONSTRAINT fk_stg_zugeordnet_zu_fk
     FOREIGN KEY (stg_zugeordnet_zu) REFERENCES fakultaet (fk_id)
  );

CREATE TABLE lehrperson
  (lp_id            INTEGER             NOT NULL,
   lp_kuerzel       D_KUERZEL           NOT NULL,
   lp_anrede        D_ANREDE            NOT NULL,
   lp_titel         D_TITEL,    
   lp_name          D_NAME              NOT NULL,
   lp_email         D_EMAIL,    
   lp_zugeordnet_zu INTEGER             NOT NULL,
   lp_status        D_STATUS_LEHRPERSON NOT NULL DEFAULT 'Lehrbeauftragter',

   CONSTRAINT pk_lehrperson
     PRIMARY KEY (lp_id),

   CONSTRAINT unique_lp_kuerzel
     UNIQUE (lp_kuerzel),

   CONSTRAINT fk_lp_zugeordnet_zu_fk
     FOREIGN KEY (lp_zugeordnet_zu) REFERENCES fakultaet (fk_id)
  );

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

   CONSTRAINT pk_professor
     PRIMARY KEY (lp_id),

   CONSTRAINT fk_professor_is_a_lehrperson
     FOREIGN KEY (lp_id) REFERENCES lehrperson (lp_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_lv_zugeordnet_zu
     FOREIGN KEY (lv_zugeordnet_zu) REFERENCES fakultaet (fk_id)
  );

CREATE TABLE amtsbezeichnung
  (ab_id        INTEGER     NOT NULL,
   ab_weiblich  VARCHAR(50) NOT NULL,
   ab_maennlich VARCHAR(50) NOT NULL,
   ab_plural    VARCHAR(50) NOT NULL,

   CONSTRAINT pk_amtsbezeichnung
     PRIMARY KEY (ab_id)
  );



CREATE TABLE amt_fk
  (fk_id INTEGER NOT NULL,
   lp_id INTEGER NOT NULL,
   ab_id INTEGER NOT NULL,

   CONSTRAINT pk_amt_fk
     PRIMARY KEY (lp_id, ab_id),

   CONSTRAINT fk_amt_fk_fk
     FOREIGN KEY (fk_id) REFERENCES fakultaet (fk_id),
   CONSTRAINT fk_amt_fk_prof
     FOREIGN KEY (lp_id) REFERENCES professor (lp_id),
   CONSTRAINT fk_amt_fk_ab
     FOREIGN KEY (ab_id) REFERENCES amtsbezeichnung (ab_id)
  );

CREATE TABLE amt_stg
  (stg_id INTEGER NOT NULL,
   lp_id  INTEGER NOT NULL,
   ab_id  INTEGER NOT NULL,

   CONSTRAINT pk_amt_stg
     PRIMARY KEY (stg_id, lp_id, ab_id),

   CONSTRAINT fk_amt_stg
     FOREIGN KEY (stg_id) REFERENCES studiengang (stg_id),
   CONSTRAINT fk_amt_stg_prof
     FOREIGN KEY (lp_id) REFERENCES professor (lp_id),
   CONSTRAINT fk_amt_stg_ab
     FOREIGN KEY (ab_id) REFERENCES amtsbezeichnung (ab_id)
  );

CREATE TABLE fk_beteiligt_stg
  (fk_id  INTEGER NOT NULL,
   stg_id INTEGER NOT NULL,

   CONSTRAINT pk_fk_beteiligt_stg
     PRIMARY KEY (fk_id, stg_id),

   CONSTRAINT fk_fk_beteiligt_stg_fk
     FOREIGN KEY (fk_id) REFERENCES fakultaet (fk_id),
   CONSTRAINT fk_fk_beteiligt_stg_stg
     FOREIGN KEY (stg_id) REFERENCES studiengang (stg_id)
  );

CREATE TABLE lp_zugeordnet_stg
  (lp_id  INTEGER NOT NULL,
   stg_id INTEGER NOT NULL,

   CONSTRAINT pk_lp_zugeordnet_stg
     PRIMARY KEY (lp_id, stg_id),

   CONSTRAINT fk_lp_zugeordnet_stg_lp
     FOREIGN KEY (lp_id) REFERENCES lehrperson (lp_id),
   CONSTRAINT fk_lp_zugeordnet_stg_stg
     FOREIGN KEY (stg_id) REFERENCES studiengang (stg_id)
  );

CREATE TABLE lp_haelt_lv
  (lp_id INTEGER NOT NULL,
   lv_id INTEGER NOT NULL,

   CONSTRAINT pk_lp_haelt_lv
     PRIMARY KEY (lp_id, lv_id),

   CONSTRAINT fk_lp_haelt_lv_lp
     FOREIGN KEY (lp_id) REFERENCES lehrperson (lp_id),
   CONSTRAINT fk_lp_haelt_lv_lv
     FOREIGN KEY (lv_id) REFERENCES lehrveranstaltung (lv_id)
  );

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

   CONSTRAINT pk_stg_zugeordnet_lv
     PRIMARY KEY (stg_id, lv_id),

   CONSTRAINT fk_stg_zugeordnet_lv_stg
     FOREIGN KEY (stg_id) REFERENCES studiengang (stg_id),
   CONSTRAINT fk_stg_zugeordnet_lv_lv
     FOREIGN KEY (lv_id) REFERENCES lehrveranstaltung (lv_id)
  );