/*************************************************************************************
 *************************************************************************************
 * Beispiel aus der Vorlesung "Multimedia-Datenbanken" von Wolfgang Kowarschick
 *************************************************************************************
 *************************************************************************************/

DROP FUNCTION IF EXISTS singleton()                         CASCADE;
DROP FUNCTION IF EXISTS basispreis_berechne_gesamtpreis()   CASCADE;
DROP FUNCTION IF EXISTS komponente_berechne_gesamtpreis()   CASCADE;													
DROP FUNCTION IF EXISTS bestandteil_berechne_gesamtpreis()  CASCADE;
DROP FUNCTION IF EXISTS rechner_berechne_gesamtpreis()      CASCADE;

DROP VIEW  IF EXISTS rechner_mit_gesamtpreis CASCADE;

DROP TABLE IF EXISTS bestandteil CASCADE;
DROP TABLE IF EXISTS komponente  CASCADE;
DROP TABLE IF EXISTS rechner     CASCADE;
DROP TABLE IF EXISTS basispreis  CASCADE;

/*************************************************************************************
 * Tabellen
 *************************************************************************************/

CREATE TABLE basispreis
(grundpreis NUMERIC(8,2) NOT NULL,
 gewinn     FLOAT NOT NULL
);

CREATE TABLE rechner
(rnr         INTEGER      NOT NULL,
 name        VARCHAR(30)  NOT NULL,
 gesamtpreis NUMERIC(8,2) NOT NULL DEFAULT 0.00,

 CONSTRAINT pk_rechner
 PRIMARY KEY (rnr),

 CONSTRAINT unique_name
 UNIQUE (name)
);

CREATE TABLE komponente
(knr          INTEGER      NOT NULL,
 bezeichnung  VARCHAR(50)  NOT NULL,
 preis        NUMERIC(8,2) NOT NULL,

 CONSTRAINT pk_komponente
 PRIMARY KEY (knr),

 CONSTRAINT unique_bezeichnung
 UNIQUE (bezeichnung)
);
                
CREATE TABLE bestandteil
(rnr    INTEGER NOT NULL,
 knr    INTEGER NOT NULL,
 anzahl INTEGER NOT NULL CHECK (anzahl > 0),

 CONSTRAINT pk_bestandteil
 PRIMARY KEY (rnr,knr),

 CONSTRAINT fk_bestandteil_rechner
 FOREIGN KEY (rnr) REFERENCES rechner (rnr),

 CONSTRAINT fk_bestandteil_komponente
 FOREIGN KEY (knr) REFERENCES komponente (knr)
 ON DELETE CASCADE -- um Komponenten direkt löschen zu können
);

/*************************************************************************************
 * Views
 *************************************************************************************/

-- Achtung: Der Gesamtpreis eines Rechners darf nicht NULL sein.
--          Da der Gesamtpreis für alle Rechner mit Hilfe von Triggern
--          und folgender View ermittelt wird, muss diese für *ALLE* 
--          Rechner ein Ergebnis liefern. Dies wird mit LEFT JOINs und
--          COALESCE erreicht.  

CREATE VIEW rechner_mit_gesamtpreis(rnr, name, gesamtpreis)
AS
SELECT   r.rnr, name, grundpreis+COALESCE(SUM(preis*anzahl),0.0)*(1.0+gewinn)
FROM     rechner r LEFT JOIN bestandteil b ON r.rnr = b.rnr
                   LEFT JOIN komponente  k ON b.knr = k.knr
         CROSS JOIN
         basispreis
GROUP BY r.rnr, grundpreis, gewinn
;


/*************************************************************************************
 * Trigger
 *
 * http://www.postgresql.org/docs/current/static/sql-createtrigger.html
 * http://www.postgresql.org/docs/current/static/plpgsql-trigger.html
 *************************************************************************************/

CREATE FUNCTION singleton() RETURNS TRIGGER
LANGUAGE plpgsql
AS
$$
  DECLARE
    one_element_only BOOLEAN;
  BEGIN
    -- Erstelle die SQL-Anfrage dynamisch zur Laufzeit. 
    -- TG_TABLE_NAME enthält den Namen der Singleton-Tabelle. 
    IF TG_WHEN = 'BEFORE'
    THEN
      EXECUTE 'SELECT COUNT(*)<>1 FROM ' || TG_TABLE_NAME INTO one_element_only;
    ELSE
      EXECUTE 'SELECT COUNT(*)=1 FROM ' || TG_TABLE_NAME INTO one_element_only;
    END IF;
    IF NOT one_element_only
    THEN
      RAISE EXCEPTION '"%" ist eine Singleton-Tabelle', TG_TABLE_NAME;
    END IF;
    RETURN NULL;
  END
$$
;

-- Damit andere Trigger, die auf die Singleton-Eigenschaft vertrauen,
-- nicht vorher anschlagen, muss der Singleton-Test so früh wie möglich stattfinden.
CREATE TRIGGER basispreis_singleton_before 
BEFORE INSERT OR DELETE ON basispreis 
FOR STATEMENT 
EXECUTE PROCEDURE singleton();

-- Auf den AFTER-Test kann dennoch nicht verzichtet werden, wenn auch beim
-- allerersten Insert sichergestellt werden soll, dass genau ein Element
-- eingefügt wird.
CREATE TRIGGER basispreis_singleton_after 
AFTER INSERT ON basispreis 
FOR STATEMENT 
EXECUTE PROCEDURE singleton();



-- Initialisiere einen neuen Computer ohne Komponenten. 
CREATE FUNCTION rechner_berechne_gesamtpreis() RETURNS TRIGGER 
LANGUAGE plpgsql
AS
$$
  BEGIN
    UPDATE rechner AS r
    SET    gesamtpreis =
           (SELECT gesamtpreis 
            FROM   rechner_mit_gesamtpreis rg
            WHERE  rg.rnr = r.rnr
           )
    WHERE  r.rnr = NEW.rnr;
    RETURN NULL;
  END
$$
;

CREATE TRIGGER  rechner_berechne_gesamtpreis 
AFTER INSERT ON rechner
FOR EACH ROW 
EXECUTE PROCEDURE rechner_berechne_gesamtpreis()
;




CREATE FUNCTION bestandteil_berechne_gesamtpreis() RETURNS TRIGGER 
LANGUAGE plpgsql
AS
$$
  BEGIN
    IF (TG_OP = 'INSERT') THEN
      UPDATE rechner AS r
      SET    gesamtpreis =
             (SELECT gesamtpreis 
              FROM   rechner_mit_gesamtpreis rg
              WHERE  rg.rnr = r.rnr
             )
      WHERE  r.rnr = NEW.rnr;
    ELSIF (TG_OP = 'UPDATE') THEN
      UPDATE rechner AS r
      SET    gesamtpreis =
             (SELECT gesamtpreis 
              FROM   rechner_mit_gesamtpreis rg
              WHERE  rg.rnr = r.rnr
             )
      WHERE  r.rnr IN (OLD.rnr, NEW.rnr);
    ELSIF (TG_OP = 'DELETE') THEN
      UPDATE rechner AS r
      SET    gesamtpreis =
             (SELECT gesamtpreis 
              FROM   rechner_mit_gesamtpreis rg
              WHERE  rg.rnr = r.rnr
             )
      WHERE  r.rnr = OLD.rnr;
    END IF;
    RETURN NULL;
  END
$$
;

CREATE TRIGGER bestandteil_berechne_gesamtpreis 
AFTER INSERT OR UPDATE OR DELETE ON bestandteil
FOR EACH ROW 
EXECUTE PROCEDURE bestandteil_berechne_gesamtpreis();



CREATE FUNCTION basispreis_berechne_gesamtpreis() RETURNS TRIGGER 
LANGUAGE plpgsql
AS
$$
  BEGIN
    UPDATE rechner AS r
    SET    gesamtpreis =
           (SELECT gesamtpreis
            FROM   rechner_mit_gesamtpreis rg
            WHERE  rg.rnr = r.rnr
           )
    ;
    RETURN NULL;
  END
$$
;

CREATE TRIGGER basispreis_berechne_gesamtpreis
AFTER INSERT OR UPDATE ON basispreis
FOR STATEMENT
EXECUTE PROCEDURE basispreis_berechne_gesamtpreis();  


-- Bei INSERT oder DELETE einer Komponente ändert sich der Gesamtpreis 
-- keines einzigen Rechners, da eine Beziehung "bestandteil" 
-- bei INSERT erst danach erstellt werden kann und bei DELETE 
-- zuvor aufgelöst worden sein muss(wg. des zugehörigen Foreign Keys).
CREATE FUNCTION komponente_berechne_gesamtpreis() RETURNS TRIGGER
LANGUAGE plpgsql
AS
$$
  BEGIN
    UPDATE rechner AS r
    SET    gesamtpreis
             = (SELECT gesamtpreis
                FROM   rechner_mit_gesamtpreis rg 
                WHERE  rg.rnr = r.rnr)
    WHERE  r.rnr IN
           (SELECT r.rnr 
            FROM   rechner r JOIN bestandteil b ON r.rnr = b.rnr  
            WHERE  knr IN (OLD.knr, NEW.knr)
           )
    ;
    RETURN NULL;
  END
$$
;

CREATE TRIGGER komponente_berechne_gesamtpreis
AFTER UPDATE ON komponente
FOR EACH ROW 
EXECUTE PROCEDURE komponente_berechne_gesamtpreis();  



/*************************************************************************************
 * Daten
 *************************************************************************************/

INSERT INTO basispreis(grundpreis, gewinn)
VALUES
  (100, 0.1)
;

INSERT INTO rechner(rnr, name)
VALUES 
  (1, 'Computer Low-End')
, (2, 'Computer High-End')
;

INSERT INTO komponente(knr, bezeichnung, preis)
VALUES
  (1, 'Gehäuse',     100.00)
, (2, 'Mainboard',   150.00)
, (3, 'CPU',          50.00)
, (4, 'RAM',          20.00)
, (5, 'SSD-Platte',  200.00)
;

INSERT INTO bestandteil(rnr, knr, anzahl)
VALUES 
  (1, 1, 1)
, (1, 2, 1)
, (1, 3, 1)
, (1, 4, 1)
, (1, 5, 1)
, (2, 1, 1)
, (2, 2, 1)
, (2, 3, 2)
, (2, 4, 4)
, (2, 5, 3)
;

/*************************************************************************************
 * Komplexere View zum Ausgeben von Test-Ergebnissen
 *************************************************************************************/

DROP VIEW IF EXISTS rechner2;

CREATE VIEW rechner2(rnr, name, gesamtpreis, selbstkosten, gewinn, komponenten)
AS
SELECT   r.rnr, name, gesamtpreis, grundpreis+COALESCE(SUM(preis*anzahl),0.0),
         gesamtpreis-(grundpreis+COALESCE(SUM(preis*anzahl),0.0)),
         JSON_OBJECT_AGG(COALESCE(bezeichnung,''), COALESCE(anzahl,0))
FROM     rechner r LEFT JOIN bestandteil b ON r.rnr = b.rnr
                   LEFT JOIN komponente  k ON b.knr = k.knr
         CROSS JOIN
         basispreis
GROUP BY r.rnr, grundpreis
;

SELECT * FROM rechner2;