/*
 * Definieren Sie eine History-Tabelle "lehrperson_history" für
 * die Tabelle "lehrperson". Die Tabelle "lehrperson_history" soll 
 * dieselben Attribute enthalten, wie die Tabelle "lehrperson".
 * Darüber hinaus soll sie zwei zusätzlich Attribute enthalten:
 *   - h_modifikation (ENUM mit vier Werten: 'INSERT', 'DELETE', 'UPDATE-OLD', 'UPDATE-NEW')
 *   - h_zeitstempel  (Zeitpunkt der Änderung)
 *
 * UNIQUE Contraints und FOREIGN KEYs sollten Sie für diese Tabelle
 * nicht definieren, wohl aber einen geeigneten PRIMARY KEY.
 *
 * Schreiben Sie drei geeignete Trigger für die Tabelle "lehrperson",
 * die ZUSÄTZLICH folgende Operationen ausführen:
 *
 * DELETE: Das gelöschte Tupel wird in die History-Tabelle eingetragen
 *         und mit "DELETE" markiert.
 * INSERT: Das neue Tupel wird in die History-Tabelle eingetragen
 *         und mit "INSERT" markiert.
 * UPDATE: Das alte Tupel wird in die History-Tabelle eingetragen
 *         und mit "UPDATE-OLD" markiert.
 *         Das neue Tupel wird in die History-Tabelle eingetragen
 *         und mit "UPDATE-NEW" markiert.
 *         Beide Einträge erhalten einen identischen Zeitstempel.
 *
 * Führen Sie anschließend nacheinander die nachfolgenden SQL-Befehle 
 * aus (in der vorgegebenen Reihenfolge).
 * Das Ergebnis der letzten SELECT-Anweisung sollte folgendermaßen aussehen:
 *
 * lp_id  lp_kuerzel  lp_anrede  lp_titel   lp_name       lp_email                      lp_zugeordnet_zu  lp_status   h_modifikation  h_timestamp
 * =============================================================================================================================================================
 * 1300   ce          Herr       Prof. Dr.  Clemens Espe  norbert.gerth@hs-augsburg.de  3                 Professor   INSERT          2019-12-12 11:11:20.623988
 * 1300   ce          Herr       Prof. Dr.  Clemens Espe  norbert.gerth@hs-augsburg.de  3                 Professor   UPDATE-OLD      2019-12-12 11:13:34.467547
 * 1300   ce          Herr       Prof. Dr.  Clemens Espe  clemens.espe@hs-augsburg.de   3                 Professor   UPDATE-NEW      2019-12-12 11:13:34.467547
 * 1300   ce          Herr       Prof. Dr.  Clemens Espe  clemens.espe@hs-augsburg.de   3                 Professor   DELETE          2019-12-12 11:13:59.176544
 */
 
 -- Lösung der Aufgabe 
 
 DROP TABLE  IF EXISTS lehrperson_history CASCADE;
 DROP DOMAIN IF EXISTS D_MODIFICATION     CASCADE;
 
 CREATE DOMAIN D_MODIFICATION 
 AS     VARCHAR(10) CHECK (VALUE IN('INSERT', 'DELETE', 'UPDATE-OLD', 'UPDATE-NEW'));
 
 CREATE TABLE lehrperson_history
 (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,
  h_modifikation   D_MODIFICATION      NOT NULL,
  h_zeitsempel     TIMESTAMP           NOT NULL,
   
  CONSTRAINT pk_lehrperson_history
    PRIMARY KEY (lp_id, h_modifikation, h_zeitsempel)
 );

  
-- After-Trigger für das Logging von INSERT-Befehlen
-- der Tabelle lehrperson.

DROP TRIGGER  IF EXISTS lehrperson_insert_trigger ON lehrperson;
DROP FUNCTION IF EXISTS lehrperson_history_insert() CASCADE;

CREATE FUNCTION lehrperson_history_insert() RETURNS TRIGGER 
LANGUAGE plpgsql
AS
$$
  BEGIN
    -- Füge in die History-Tabelle lehrperson_history das neue Tupel
    -- versehen mit dem Tag 'INSERT' und dem Zeitpunkt der 
    -- Ausführung des INSERT-Befehls ein.
    INSERT INTO lehrperson_history(lp_id, lp_kuerzel, lp_anrede, lp_titel, lp_name, lp_email, lp_zugeordnet_zu, lp_status, h_modifikation, h_zeitsempel)
    VALUES (NEW.lp_id, NEW.lp_kuerzel, NEW.lp_anrede, NEW.lp_titel, NEW.lp_name, NEW.lp_email, NEW.lp_zugeordnet_zu, NEW.lp_status,
            'INSERT', CURRENT_TIMESTAMP
           );
    RETURN NULL;
  END
$$
;

CREATE TRIGGER lehrperson_insert_trigger
AFTER INSERT
ON lehrperson
FOR EACH ROW
  EXECUTE PROCEDURE lehrperson_history_insert();

  
-- After-Trigger für das Logging von DELETE-Befehlen
-- der Tabelle lehrperson.

DROP TRIGGER  IF EXISTS lehrperson_delete_trigger ON lehrperson;
DROP FUNCTION IF EXISTS lehrperson_history_delete() CASCADE;

CREATE FUNCTION lehrperson_history_delete() RETURNS TRIGGER  
LANGUAGE plpgsql
AS
$$
  BEGIN
    -- Füge in die History-Tabelle lehrperson_history das gelöschte Tupel
    -- versehen mit dem Tag 'DELETE' und dem Zeitpunkt der 
    -- Ausführung des DELETE-Befehls ein.
    INSERT INTO lehrperson_history(lp_id, lp_kuerzel, lp_anrede, lp_titel, lp_name, lp_email, lp_zugeordnet_zu, lp_status, h_modifikation, h_zeitsempel)
    VALUES (OLD.lp_id, OLD.lp_kuerzel, OLD.lp_anrede, OLD.lp_titel, OLD.lp_name, OLD.lp_email, OLD.lp_zugeordnet_zu, OLD.lp_status,
            'DELETE', CURRENT_TIMESTAMP
           );
    RETURN NULL;
  END
$$
;

CREATE TRIGGER lehrperson_delete_trigger
AFTER DELETE
ON lehrperson
FOR EACH ROW
  EXECUTE PROCEDURE lehrperson_history_delete();

  
  
-- After-Trigger für das Logging von UPDATE-Befehlen
-- der Tabelle lehrperson.

DROP TRIGGER  IF EXISTS lehrperson_update_trigger ON lehrperson;
DROP FUNCTION IF EXISTS lehrperson_history_update() CASCADE;

CREATE FUNCTION lehrperson_history_update() RETURNS TRIGGER 
LANGUAGE plpgsql
AS
$$
  BEGIN
    -- BEGIN TRANSACTION;
  
    -- Die aktuelle Uhrzeit wird nur einmal ermittelt, da beide
    -- Insert-Befehle formal zum selben Zeitpunkt (das heißt
    -- innerhalb einer Transaktion) stattfinden.
    -- SQL-konforme Definition einer temporären "Variablen".
    -- Man könnte hier auch einfach eine PLPGSQL-Variable verwenden.
    CREATE TEMPORARY TABLE ctime
    AS
    SELECT CURRENT_TIMESTAMP AS cts;
    
    -- Füge in die History-Tabelle lehrperson_history das ursprüngliche Tupel
    -- versehen mit dem Tag 'UPDATE-OLD' und dem Zeitpunkt der 
    -- Ausführung des UPDATE-Befehls ein.
    INSERT INTO lehrperson_history(lp_id, lp_kuerzel, lp_anrede, lp_titel, lp_name, lp_email, lp_zugeordnet_zu, lp_status, h_modifikation, h_zeitsempel)
    VALUES (OLD.lp_id, OLD.lp_kuerzel, OLD.lp_anrede, OLD.lp_titel, OLD.lp_name, OLD.lp_email, OLD.lp_zugeordnet_zu, OLD.lp_status,
            'UPDATE-OLD', (SELECT cts FROM ctime)
           );

    -- Füge in die History-Tabelle lehrperson_history das modifizierte Tupel
    -- versehen mit dem Tag 'UPDATE-NEW' und dem Zeitpunkt der 
    -- Ausführung des UPDATE-Befehls ein.    
    INSERT INTO lehrperson_history(lp_id, lp_kuerzel, lp_anrede, lp_titel, lp_name, lp_email, lp_zugeordnet_zu, lp_status, h_modifikation, h_zeitsempel)
    VALUES (NEW.lp_id, NEW.lp_kuerzel, NEW.lp_anrede, NEW.lp_titel, NEW.lp_name, NEW.lp_email, NEW.lp_zugeordnet_zu, NEW.lp_status,
            'UPDATE-NEW', (SELECT cts FROM ctime)
           );

    -- COMMIT WORK;
    RETURN NULL;
  END
$$
;

CREATE TRIGGER lehrperson_update_trigger
AFTER UPDATE
ON lehrperson
FOR EACH ROW
  EXECUTE PROCEDURE lehrperson_history_update();



-- Alternative von Johannes Dejori

CREATE FUNCTION insert_delete_history_function() RETURNS TRIGGER
LANGUAGE plpgsql
AS
$$
  BEGIN

  IF (TG_OP = 'DELETE' OR TG_OP = 'UPDATE') THEN

    INSERT INTO lehrperson_history (lp_id, lp_kuerzel, lp_anrede, lp_titel, lp_name, lp_email, lp_zugeordnet_zu, lp_status, h_modifikation, h_zeitstempel)
    VALUES 
    ( OLD.lp_id,
      OLD.lp_kuerzel,
      OLD.lp_anrede,
      OLD.lp_titel,
      OLD.lp_name,
      OLD.lp_email,
      OLD.lp_zugeordnet_zu,
      OLD.lp_status,
      CASE WHEN (TG_OP = 'DELETE')
        THEN 'DELETE'
        ELSE 'UPDATE-OLD'
      END,
      NOW()
    );

  END IF;
  IF (TG_OP = 'INSERT' OR TG_OP = 'UPDATE') THEN

    INSERT INTO lehrperson_history (lp_id, lp_kuerzel, lp_anrede, lp_titel, lp_name, lp_email, lp_zugeordnet_zu, lp_status, h_modifikation, h_zeitstempel)
    VALUES 
    ( NEW.lp_id,
      NEW.lp_kuerzel,
      NEW.lp_anrede,
      NEW.lp_titel,
      NEW.lp_name,
      NEW.lp_email,
      NEW.lp_zugeordnet_zu,
      NEW.lp_status,
      CASE WHEN (TG_OP = 'INSERT')
        THEN 'INSERT'
        ELSE 'UPDATE-NEW'
      END,
      NOW()
    );

  END IF;

  RETURN NULL;
  END
$$
;

CREATE TRIGGER insert_history
AFTER INSERT OR DELETE OR UPDATE
ON lehrperson
FOR EACH ROW
  EXECUTE PROCEDURE insert_delete_history_function()
;




-- Anweisungen zum Testen der Lösung

INSERT INTO lehrperson(lp_id, lp_kuerzel, lp_anrede, lp_titel, lp_name, lp_email, lp_zugeordnet_zu, lp_status)
VALUES (1300, 'ce','Herr',  'Prof. Dr.', 'Clemens Espe', 'norbert.gerth@hs-augsburg.de', 3, 'Professor' );

INSERT INTO professor(lp_id, p_dienstzimmer, p_sprechstunde)
VALUES (1300, 'J2.06', 'Freitag, 13:00-14:00 Uhr');

SELECT * FROM lehrperson_history;



UPDATE lehrperson
SET    lp_email = 'clemens.espe@hs-augsburg.de'
WHERE  lp_kuerzel = 'ce'
;

SELECT * FROM lehrperson_history;



DELETE FROM professor
WHERE  lp_id = (SELECT lp_id FROM lehrperson WHERE lp_kuerzel = 'ce')
;

SELECT * FROM lehrperson_history;



DELETE FROM lehrperson
WHERE  lp_kuerzel = 'ce'
;

SELECT * FROM lehrperson_history;