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 amtbeschreibung      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 TYPE  IF EXISTS D_STATUS_LEHRENDER   CASCADE;
DROP TYPE  IF EXISTS D_ABSCHLUSS          CASCADE;
DROP TYPE  IF EXISTS D_ANREDE             CASCADE;


CREATE TYPE D_ANREDE AS ENUM('Herr', 'Frau');
CREATE TYPE D_ABSCHLUSS AS ENUM('Diplom', 'Bachelor', 'Master');
CREATE TYPE D_STATUS_LEHRENDER AS ENUM('Professor', 'Lehrbeauftragter');


CREATE TABLE fakultaet
  (f_id            INTEGER     NOT NULL,
   f_kuerzel       VARCHAR(4)  NOT NULL,
   f_name          VARCHAR(50) NOT NULL,
   f_email         VARCHAR(50),
   f_tel           VARCHAR(50) 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       VARCHAR(4)  NOT NULL,
   s_name          VARCHAR(50) NOT NULL,
   s_abschluss     D_ABSCHLUSS NOT NULL,
   s_titel         VARCHAR(10) 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       VARCHAR(20)        NOT NULL,
   l_anrede        D_ANREDE           NOT NULL,
   l_titel         VARCHAR(10),
   l_name          VARCHAR(50)        NOT NULL,
   l_email         VARCHAR(50),
   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  VARCHAR(10),
   p_sprechstunde  VARCHAR(50)        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       VARCHAR(10) NOT NULL,
   lv_titel         VARCHAR(50) NOT NULL,
   lv_zugeordnet_zu INTEGER     NOT NULL,

   CONSTRAINT pk_lehrveranstaltung
     PRIMARY KEY (lv_id),

   CONSTRAINT unique_lehrveranstaltung_kuezel
     UNIQUE (lv_kuerzel),

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

CREATE TABLE amtbeschreibung
  (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_amtbeschreibung
     PRIMARY KEY (ab_id),

   CONSTRAINT unique_amtbeschreibung_bez_weiblich
     UNIQUE (ab_bez_weiblich),

   CONSTRAINT unique_amtbeschreibung_bez_maennlich
     UNIQUE (ab_bez_maennlich),

   CONSTRAINT unique_amtbeschreibung_bez_plural
     UNIQUE (ab_bez_plural)
  );




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

   CONSTRAINT pk_amkt_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_amtbeschreibung
     FOREIGN KEY (ab_id) REFERENCES amtbeschreibung (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_amtbeschreibung
     FOREIGN KEY (ab_id) REFERENCES amtbeschreibung (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)
  );

