-- Schemaevolution (Weiterentwicklung eines Datenbankschemas)
-- Definiere zwei neue Attribute "h_name_mit_ortschaft" und "h_anzahl_haendler"
-- für die Tabelle "haendler", sofern diese nicht bereits definiert wurden.

ALTER TABLE haendler
  ADD COLUMN IF NOT EXISTS h_name_mit_ortschaft VARCHAR(100),
  ADD COLUMN IF NOT EXISTS h_anzahl_waren       INTEGER DEFAULT 0
;

/*
ALTER TABLE liefert
  ALTER CONSTRAINT fk_liefert_haendler DEFERRABLE INITIALLY DEFERRED,
  ALTER CONSTRAINT fk_liefert_ware     DEFERRABLE INITIALLY DEFERRED
;
*/

-- Initial müssen die neuen Attribute von allen bereits vorhandenen 
-- Tupeln aktualisiert werden. Bei Tupeln, die künftig hinzugefügt
-- werden, ist dies nicht notwendig, da die Werte der neuen Attribute
-- dann automatisch aktualisiert werden.

SELECT * FROM haendler;

UPDATE haendler h
SET    h_name_mit_ortschaft = COALESCE(h_name || ' in ' || h_ortschaft, h_name),
       h_anzahl_waren       = (SELECT COUNT (DISTINCT w_id)
                               FROM   liefert l
                               WHERE  l.h_id = h.h_id
                              )
;

SELECT * FROM haendler;

-- Sollte die Datenbank noch keine Elemente enthalten, kann man sich
-- diese Update-Operation sparen.    

-- Alle Händler wurden aktualisiert.
-- ALLERDINGS NUR EINMAL.
-- Jedes mal, wenn ein Händler eingefügt oder geändert wird, oder wenn es
-- irgendeine Änderung an der liefert-Tabelle gibt, müssen die beiden neuen
-- Attribute für die zugehörigen Händler erneut berechnet werden. Dies
-- erfolgt künftig automatisch mit Hilfe von Triggern.
    
------------------------------------------------------------------------------------
-- Berechne das haendler-Attribut h_name_mit_ortschaft automatisch.
------------------------------------------------------------------------------------

-- Falls der Trigger (samt zugehöriger Prozedur) schon existiert: Lösche ihn.
DROP TRIGGER  IF EXISTS h_name_mit_ortschaft_trigger ON haendler;
DROP FUNCTION IF EXISTS h_name_mit_ortschaft_function() CASCADE;

-- Erzeuge die Trigger-Prozedur. Diese berechnet den Wert von
-- h_name_mit_ortschaft aus den neuen Werten von h_name und h_ortschaft.
CREATE FUNCTION h_name_mit_ortschaft_function() RETURNS TRIGGER 
LANGUAGE plpgsql
AS
$$
  BEGIN
    NEW.h_name_mit_ortschaft = COALESCE(NEW.h_name || ' in ' || NEW.h_ortschaft, NEW.h_name);  
    -- Gibt das modifizierte NEW-Tupel zurück.
    RETURN NEW;
  END
$$
;

-- Jedes Mal BEVOR ein neues Tupel in haendler eingefügt wird bzw. 
-- den alten Wert ersetzt, wird es modifiziert. Der Wert des Attributs 
-- h_name_mit_ortschaft wird automatisch berechnet.
CREATE TRIGGER h_name_mit_ortschaft_trigger 
BEFORE INSERT OR UPDATE 
ON haendler
FOR EACH ROW 
  EXECUTE PROCEDURE h_name_mit_ortschaft_function()
;


------------------------------------------------------------------------------------
-- Berechne das Attribut h_anzahl_waren automatisch.
------------------------------------------------------------------------------------

-- Falls die Trigger (samt zugehöriger Prozedur) schon existieren: Lösche sie.
DROP TRIGGER  IF EXISTS h_anzahl_waren_trigger_on_insert_delete ON haendler;
DROP TRIGGER  IF EXISTS h_anzahl_waren_trigger_on_update        ON haendler;
DROP FUNCTION IF EXISTS h_anzahl_waren_function() CASCADE;

-- Erzeuge die Trigger-Prozedur. Diese berechnet für den Händler, für den 
-- die liefert-Tabelle verändert wurde, den Wert von "h_anzahl_waren" aus 
-- den aktuellen Werten in der Tabelle "liefert".
CREATE FUNCTION h_anzahl_waren_function() RETURNS TRIGGER 
LANGUAGE plpgsql
AS
$$
  BEGIN
    -- Diese und die nachfolgende If-Anweisungen ermöglichen,
    -- dass man nur einen Trigger
    --   CREATE TRIGGER h_anzahl_waren_trigger AFTER INSERT OR UPDATE OR DELETE
    -- an Stelle von drei Triggern
    --   CREATE TRIGGER h_anzahl_waren_trigger AFTER INSERT
    --   CREATE TRIGGER h_anzahl_waren_trigger AFTER UPDATE
    --   CREATE TRIGGER h_anzahl_waren_trigger AFTER DELETE
    -- definieren muss.
    IF (TG_OP = 'DELETE' OR TG_OP = 'UPDATE') THEN
      UPDATE haendler
      SET    h_anzahl_waren = (SELECT COUNT (DISTINCT w_id)
                               FROM   liefert
                               WHERE  h_id = OLD.h_id
                              )
      WHERE  h_id = OLD.h_id;
    END IF;
    IF (TG_OP = 'INSERT' OR TG_OP = 'UPDATE') THEN
      UPDATE haendler
      SET    h_anzahl_waren = (SELECT COUNT (DISTINCT w_id)
                               FROM   liefert
                               WHERE  h_id = NEW.h_id
                              )
      WHERE  h_id = NEW.h_id;
    END IF;

    RETURN NULL;
  END
$$
;

-- Jedes Mal NACHDEM die Tabelle "liefert" verändert wurde, aktualisiere die
-- Tabelle "haendler".
CREATE TRIGGER h_anzahl_waren_trigger_on_insert_delete
AFTER INSERT OR DELETE
ON liefert
FOR EACH ROW
  EXECUTE PROCEDURE h_anzahl_waren_function()
;

CREATE TRIGGER h_anzahl_waren_trigger_on_update
AFTER UPDATE
ON liefert
FOR EACH ROW
  WHEN (OLD.h_id <> NEW.h_id OR OLD.w_id <> NEW.w_id)
  EXECUTE PROCEDURE h_anzahl_waren_function()
;


/*
 Hier gibt es noch Optimierungspotenzial:
   - Wenn bei einem Update h_id nicht geändert wird, reicht es,
     einen der beiden Update-Befehle auszuführen.

  Man könnte die teure SELECT-COUNT-Anweisung auch ganz vermeiden,
  indem man jedes Mal, wenn für einen Händler ein NEUER Artikel
  in die liefert-Tabelle eingefügt wird, die Anzahl der gelieferten
  Waren beim zugehörigen Händler um 1 erhöht, und jedes Mal wenn
  eine Ware ENDGÜLTIG für einen Händler aus der liefert-Tabelle
  gelöscht wird, diese Anzahl um 1 verringert. Allerdings muss
  überprüft werden, ob der Händler dieselbe nicht schon bzw. noch 
  zu einem anderen Preis (mit einer anderen Lieferzeit) liefert.
*/ 

DELETE FROM liefert  WHERE h_id > 5;
DELETE FROM haendler WHERE h_id > 5;

INSERT INTO haendler(h_id, h_name, h_ortschaft)
VALUES 
  (6, 'Händler A',  'Augsburg'),
  (7, 'Händler B',  NULL),
  (8, 'Händler C',  NULL)
;

SELECT * FROM haendler ORDER BY h_id;

UPDATE haendler
SET    h_ortschaft = 'Rosenheim'
WHERE  h_id = 7;

SELECT * FROM haendler ORDER BY h_id;

INSERT INTO liefert(h_id, w_id, l_preis, l_lieferzeit)
VALUES 
  (6, 1, 210.00, 1),
  (6, 1, 196.00, 3),
  (6, 2, 120.00, NULL),
  (7, 3, 150.00, 7),
  (7, 4,  11.00, 1),
  (8, 5,   6.00, 1)
;

SELECT * FROM haendler JOIN liefert USING (h_id) ORDER BY h_id, w_id;
SELECT * FROM haendler ORDER BY h_id;

UPDATE liefert
SET    w_id = 4, l_preis = 160
WHERE  h_id = 6 AND w_id = 2
;

SELECT * FROM haendler ORDER BY h_id;

UPDATE liefert
SET    h_id = 7
WHERE  h_id = 6 AND w_id = 1
;

SELECT * FROM haendler ORDER BY h_id;


DELETE FROM liefert
WHERE  h_id = 7 AND w_id = 1 AND l_lieferzeit = 999
;

SELECT * FROM haendler ORDER BY h_id;


DELETE FROM liefert
WHERE  h_id = 7 AND w_id = 1
;

SELECT * FROM haendler ORDER BY h_id
;