/******************************************************************************
 * Alten DB-Inhalt löschen
 ******************************************************************************/

DROP TRIGGER  IF EXISTS liefert_trigger ON liefert;
DROP FUNCTION IF EXISTS liefert_function();
DROP TRIGGER  IF EXISTS ware_trigger ON ware;
DROP FUNCTION IF EXISTS ware_function();
DROP TRIGGER  IF EXISTS typ_trigger ON typ;
DROP FUNCTION IF EXISTS typ_function();
DROP TRIGGER  IF EXISTS haendler_trigger ON haendler;
DROP FUNCTION IF EXISTS haendler_function();

DROP TRIGGER  IF EXISTS volltext_update_trigger ON volltext;
DROP FUNCTION IF EXISTS volltext_update_function();

DROP INDEX IF EXISTS volltext_index;
DROP VIEW  IF EXISTS volltext_info;
DROP VIEW  IF EXISTS haendler_liefert_ware_german;
DROP VIEW  IF EXISTS haendler_liefert_ware_phonet1;
DROP VIEW  IF EXISTS haendler_liefert_ware_volltext;
DROP VIEW  IF EXISTS haendler_liefert_ware;

DROP TABLE IF EXISTS volltext;
DROP TABLE IF EXISTS liefert;
DROP TABLE IF EXISTS ware;
DROP TABLE IF EXISTS haendler;
DROP TABLE IF EXISTS typ;

DROP DOMAIN IF EXISTS D_LIEFERZEIT;

/******************************************************************************
 * Domains und Tabellen erstellen
 ******************************************************************************/

CREATE DOMAIN D_LIEFERZEIT
AS
SMALLINT
CHECK (value > 0);

CREATE TABLE typ
  (t_id          INTEGER     NOT NULL,
   t_name        VARCHAR(20) NOT NULL,
   t_supertyp_id INTEGER,

   CONSTRAINT pk_typ
     PRIMARY KEY (t_id),

   CONSTRAINT fk_typ_typ
     FOREIGN KEY (t_supertyp_id) REFERENCES typ (t_id),
     
   CONSTRAINT unique_typ_t_name
     UNIQUE (t_name)
  );

CREATE TABLE haendler
  (h_id        INTEGER     NOT NULL,
   h_name      VARCHAR(30) NOT NULL,
   h_ortschaft VARCHAR(50),

   CONSTRAINT pk_haendler
     PRIMARY KEY (h_id),

   CONSTRAINT unique_haendler_name_ortschaft
     UNIQUE (h_name, h_ortschaft)
  );

CREATE TABLE ware
  (w_id          INTEGER     NOT NULL,
   t_id          INTEGER     NOT NULL DEFAULT 0,
   w_bezeichnung VARCHAR(50) NOT NULL,

   CONSTRAINT pk_ware
     PRIMARY KEY (w_id),

   CONSTRAINT fk_ware_typ
     FOREIGN KEY (t_id) REFERENCES typ (t_id),

   CONSTRAINT unique_ware_t_id_bezeichnung
     UNIQUE (t_id, w_bezeichnung)
  );
                
CREATE TABLE liefert
  (h_id         INTEGER       NOT NULL,
   w_id         INTEGER       NOT NULL,
   l_preis      NUMERIC(8,2)  NOT NULL,
   l_lieferzeit D_LIEFERZEIT,

   CONSTRAINT pk_liefert
     PRIMARY KEY (h_id, w_id, l_preis),

   CONSTRAINT fk_liefert_haendler
     FOREIGN KEY (h_id) REFERENCES haendler (h_id),

   CONSTRAINT fk_liefert_ware
     FOREIGN KEY (w_id) REFERENCES ware (w_id)
  );

CREATE TABLE volltext
  (v_id       INTEGER NOT NULL,
   v_volltext TSVECTOR NOT NULL,
   h_id       INTEGER,
   w_id       INTEGER,
   t_id       INTEGER,
   l_preis    NUMERIC(8,2),
  

   CONSTRAINT pk_volltext
     PRIMARY KEY (v_id),

   CONSTRAINT fk_volltext_haendler
     FOREIGN KEY (h_id) REFERENCES haendler(h_id),

   CONSTRAINT fk_volltext_ware
     FOREIGN KEY (w_id) REFERENCES ware(w_id),

   CONSTRAINT fk_volltext_typ
     FOREIGN KEY (t_id) REFERENCES typ(t_id)     
  );

/******************************************************************************
 * Indexe
 ******************************************************************************/

/* Volltextindex */
CREATE INDEX volltext_index ON volltext USING gin(v_volltext);

/* Sekundärindexe */
CREATE INDEX heandler_volltext ON volltext USING btree (h_id, w_id, l_preis);
CREATE INDEX ware_volltext ON volltext USING btree (w_id, h_id, l_preis);
CREATE INDEX liefert_volltext ON volltext USING btree (l_preis,w_id, h_id);
CREATE INDEX typ_volltext ON volltext USING btree (t_id, w_id);

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

DROP VIEW IF EXISTS haendler_liefert_ware;
CREATE VIEW haendler_liefert_ware   
            (h_id, h_name, h_ortschaft,
             l_preis, l_lieferzeit, 
             w_id, w_bezeichnung,
             t_id, t_name
            )
AS
SELECT h_id, h_name, h_ortschaft,
       l_preis, l_lieferzeit, 
       w_id, w_bezeichnung,
       t_id, t_name
FROM             haendler 
       FULL JOIN liefert  USING (h_id)
       FULL JOIN ware     USING (w_id)
       FULL JOIN typ      USING (t_id)
UNION DISTINCT       
SELECT h_id, h_name, h_ortschaft,
       NULL, NULL, 
       NULL, NULL, 
       NULL, NULL
FROM   haendler
UNION DISTINCT
SELECT NULL, NULL, NULL,
       NULL, NULL, 
       w_id, w_bezeichnung, 
       t_id, t_name
FROM   ware JOIN typ USING (t_id)
UNION DISTINCT
SELECT NULL, NULL, NULL,
       NULL, NULL, 
       NULL, NULL,
       t_id, t_name
FROM   typ
;

DROP VIEW IF EXISTS haendler_liefert_ware_volltext;
CREATE VIEW haendler_liefert_ware_volltext
            (volltext, 
             h_id, h_name, h_ortschaft,
             l_preis, l_lieferzeit, 
             w_id, w_bezeichnung,
             t_id, t_name
            )
AS
SELECT           COALESCE(h_name, '')
       || ' ' || COALESCE(h_ortschaft, '')
       || ' ' || COALESCE(CAST(l_preis AS VARCHAR), '')
       || ' ' || COALESCE(CAST(l_lieferzeit AS VARCHAR), '')
       || ' ' || COALESCE(w_bezeichnung, '')
       || ' ' || COALESCE(t_name, ''),
       h_id, h_name, h_ortschaft,
       l_preis, l_lieferzeit, 
       w_id, w_bezeichnung,
       t_id, t_name
FROM   haendler_liefert_ware    
;

DROP VIEW IF EXISTS haendler_liefert_ware_german;
CREATE VIEW haendler_liefert_ware_german
            (v_volltext, 
             h_id, h_name, h_ortschaft,
             l_preis, l_lieferzeit, 
             w_id, w_bezeichnung,
             t_id, t_name
            )
AS
SELECT to_tsvector('german', volltext),
       h_id, h_name, h_ortschaft,
       l_preis, l_lieferzeit, 
       w_id, w_bezeichnung,
       t_id, t_name
FROM   haendler_liefert_ware_volltext
;

-- Wenn die Extension dict_phone existiert, können noch
-- weitere Volltext-Indexe definiert werden:

DO --TBD: Make it DRY!
$_plpgsql_$
BEGIN
  IF (SELECT EXISTS(SELECT * FROM pg_extension WHERE extname = 'dict_phonet'))
  THEN
  
    DROP TEXT SEARCH DICTIONARY IF EXISTS phonet1 CASCADE;

    CREATE TEXT SEARCH DICTIONARY phonet1  
    ( TEMPLATE = phonet_template,
      ENCODING = 'utf8',    -- 'utf8', 'latin1'
      LANGUAGE = 'german',  -- 'german', 'no language'
      RULES    = '1',       -- '1', '2'
      MAXLEN   = '100'      -- '100', '<integer>'
    );

    CREATE TEXT SEARCH CONFIGURATION phonet1
    ( PARSER = phonet_parser );

    ALTER TEXT SEARCH CONFIGURATION phonet1
    ALTER MAPPING FOR word
    WITH phonet1;

    DROP VIEW IF EXISTS haendler_liefert_ware_phonet1;
    CREATE VIEW haendler_liefert_ware_phonet1
                (v_volltext, 
                 h_id, h_name, h_ortschaft,
                 l_preis, l_lieferzeit, 
                 w_id, w_bezeichnung,
                 t_id, t_name
                )
    AS
    SELECT to_tsvector('phonet1', volltext),
           h_id, h_name, h_ortschaft,
           l_preis, l_lieferzeit, 
           w_id, w_bezeichnung,
           t_id, t_name
    FROM   haendler_liefert_ware_volltext
    ;

  END IF;
END 
$_plpgsql_$;


DROP VIEW IF EXISTS volltext_info;
CREATE VIEW volltext_info
            (v_volltext, 
             h_id, h_name, h_ortschaft,
             l_preis, l_lieferzeit, 
             w_id, w_bezeichnung,
             t_id, t_name
            )
AS
SELECT v_volltext, 
       h.h_id, h_name, h_ortschaft,
       l.l_preis, l_lieferzeit, 
       w.w_id, w_bezeichnung,
       t.t_id, t_name
FROM   volltext v LEFT JOIN haendler h ON v.h_id = h.h_id
                  LEFT JOIN ware     w ON v.w_id = w.w_id
                  LEFT JOIN typ      t ON v.t_id = t.t_id
                  LEFT JOIN liefert  l ON     v.h_id    = l.h_id 
                                          AND v.w_id    = l.w_id
                                          AND v.l_preis = l.l_preis
;

/******************************************************************************
 * Trigger und Rules (= PostgreSQL-spezifische Trigger)
 ******************************************************************************/

DROP TRIGGER  IF EXISTS volltext_update_trigger ON volltext;
DROP FUNCTION IF EXISTS volltext_update_function();

CREATE FUNCTION volltext_update_function() RETURNS TRIGGER
AS
$_plpgsql_$
  BEGIN
    NEW.v_id = COALESCE((SELECT MAX(v_id)+1 FROM volltext), 1);
    RETURN NEW;
  END
$_plpgsql_$
LANGUAGE plpgsql;

CREATE TRIGGER volltext_update_trigger BEFORE INSERT 
ON volltext
FOR EACH ROW 
EXECUTE PROCEDURE
  volltext_update_function();
;


DROP FUNCTION IF EXISTS haendler_function();
CREATE FUNCTION haendler_function() RETURNS TRIGGER
AS
$_plpgsql_$
  BEGIN
    IF (TG_OP IN ('UPDATE', 'DELETE')) THEN
      DELETE FROM volltext
      WHERE  h_id = OLD.h_id
      ;
    END IF;  
    
    IF (TG_OP IN ('INSERT', 'UPDATE')) THEN
      DELETE FROM volltext
      WHERE  h_id = NEW.h_id
      ;
    
      INSERT INTO volltext(v_volltext, h_id, w_id, t_id, l_preis)
      SELECT v_volltext, h_id, w_id, t_id, l_preis
      FROM   haendler_liefert_ware_german
      WHERE  h_id = NEW.h_id
      ;
    END IF;
    RETURN NULL;    
  END
$_plpgsql_$
LANGUAGE plpgsql;

DROP TRIGGER  IF EXISTS haendler_trigger ON haendler;
CREATE TRIGGER haendler_trigger AFTER INSERT OR UPDATE OR DELETE 
ON haendler
FOR EACH ROW 
EXECUTE PROCEDURE
  haendler_function();
;


DROP FUNCTION IF EXISTS typ_function();
CREATE FUNCTION typ_function() RETURNS TRIGGER
AS
$_plpgsql_$
  BEGIN
    IF (TG_OP IN ('UPDATE', 'DELETE')) THEN
      DELETE FROM volltext
      WHERE  t_id = OLD.t_id
      ;
    END IF;  
    
    IF (TG_OP IN ('INSERT', 'UPDATE')) THEN
      DELETE FROM volltext
      WHERE  t_id = NEW.t_id
      ;
      
      INSERT INTO volltext(v_volltext, h_id, w_id, t_id, l_preis)
      SELECT v_volltext, h_id, w_id, t_id, l_preis
      FROM   haendler_liefert_ware_german
      WHERE  t_id = NEW.t_id
      ;
    END IF;
    RETURN NULL;    
  END
$_plpgsql_$
LANGUAGE plpgsql;

DROP TRIGGER  IF EXISTS typ_trigger ON typ;
CREATE TRIGGER typ_trigger AFTER INSERT OR UPDATE OR DELETE 
ON typ
FOR EACH ROW 
EXECUTE PROCEDURE
  typ_function();
;


DROP FUNCTION IF EXISTS ware_function();
CREATE FUNCTION ware_function() RETURNS TRIGGER
AS
$_plpgsql_$
  BEGIN
    IF (TG_OP IN ('UPDATE', 'DELETE')) THEN
      DELETE FROM volltext
      WHERE  w_id = OLD.w_id
      ;
    END IF;  
    
    IF (TG_OP IN ('INSERT', 'UPDATE')) THEN
      DELETE FROM volltext
      WHERE  w_id = NEW.w_id
      ;
      
      INSERT INTO volltext(v_volltext, h_id, w_id, t_id, l_preis)
      SELECT v_volltext, h_id, w_id, t_id, l_preis
      FROM   haendler_liefert_ware_german
      WHERE  w_id = NEW.w_id
      ;
    END IF;
    RETURN NULL;    
  END
$_plpgsql_$
LANGUAGE plpgsql;

DROP TRIGGER  IF EXISTS ware_trigger ON ware;
CREATE TRIGGER ware_trigger AFTER INSERT OR UPDATE OR DELETE 
ON ware
FOR EACH ROW 
EXECUTE PROCEDURE
  ware_function();
;


DROP FUNCTION IF EXISTS liefert_function();
CREATE FUNCTION liefert_function() RETURNS TRIGGER
AS
$_plpgsql_$
  BEGIN
    IF (TG_OP IN ('UPDATE', 'DELETE')) THEN
      DELETE FROM volltext
      WHERE  h_id = OLD.h_id AND w_id = OLD.w_id AND l_preis = OLD.l_preis
      ;
    END IF;  
    
    IF (TG_OP IN ('INSERT', 'UPDATE')) THEN
      DELETE FROM volltext
      WHERE  h_id = NEW.h_id AND w_id = NEW.w_id AND l_preis = NEW.l_preis
      ;
    
      INSERT INTO volltext(v_volltext, h_id, w_id, t_id, l_preis)
      SELECT v_volltext, h_id, w_id, t_id, l_preis
      FROM   haendler_liefert_ware_german
      WHERE  h_id = NEW.h_id AND w_id = NEW.w_id AND l_preis = NEW.l_preis
      ;
    END IF;
    RETURN NULL;    
  END
$_plpgsql_$
LANGUAGE plpgsql;

DROP TRIGGER  IF EXISTS liefert_trigger ON liefert;
CREATE TRIGGER liefert_trigger AFTER INSERT OR UPDATE OR DELETE 
ON liefert
FOR EACH ROW 
EXECUTE PROCEDURE
  liefert_function();
;


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

INSERT INTO typ(t_id, t_name, t_supertyp_id)
VALUES
  (0, 'Sonstiges',       NULL),
  (1, 'Computerzubehör', NULL),
  (2, 'CPU',             1),
  (3, 'RAM',             1)
;

INSERT INTO haendler(h_id, h_name, h_ortschaft)
VALUES 
  (1, 'Maier',   'Königsbrunn'),
  (2, 'Müller',  'Königsbrunn'),
  (3, 'Maier',   'Augsburg'),
  (4, 'Huber',   NULL),
  (5, 'Schmidt', 'Hamburg')
;

INSERT INTO ware(w_id, t_id, w_bezeichnung)
VALUES
  (1, 2, 'Pentium IV 3,8'),
  (2, 2, 'Celeron 2,6'),
  (3, 2, 'Athlon XP 3000+'),
  (4, 3, 'SDRAM 1GB'),
  (5, 0, 'Eieruhr')
;

INSERT INTO liefert(h_id, w_id, l_preis, l_lieferzeit)
VALUES 
  (1, 1, 200.00, 1),
  (1, 1, 194.00, 6),
  (1, 2, 100.00, NULL),
  (1, 3, 150.00, 7),
  (1, 4,  10.00, 1),
  (1, 5,   5.00, 1),
  (2, 1, 190.00, NULL),
  (2, 3, 170.00, 4),
  (2, 1, 160.00, 1),
  (2, 2, 180.00, NULL),
  (3, 1, 195.00, 2),
  (3, 2, 190.00, 1),
  (4, 1, 150.00, 3),
  (4, 3, 180.00, 5),
  (4, 3, 199.99, 1)
;
  
/******************************************************************************
 * Test
 ******************************************************************************/  

SELECT * FROM volltext_info
;

SELECT 'volltext' AS query , * FROM volltext_info
UNION
SELECT 'view', * FROM haendler_liefert_ware_german
ORDER BY query, h_id, w_id
;

SELECT   'volltext' AS query, vi.*, 'view' AS query, hlwg.* 
FROM     volltext_info vi FULL JOIN haendler_liefert_ware_german hlwg ON vi.v_volltext = hlwg.v_volltext
ORDER BY vi.h_id, vi.w_id
;

/*
UPDATE haendler
SET    h_ortschaft = 'Rosenheim'
WHERE  h_id = 1
;

SELECT * FROM volltext_info
ORDER BY h_id, w_id
;

UPDATE typ
SET    t_name = 'Prozessor'
WHERE  t_id = 2
;

SELECT * FROM volltext_info
ORDER BY h_id, w_id
;
*/

SELECT *
FROM   volltext_info
WHERE  v_volltext @@ to_tsquery('german', 'CPU')
;

SELECT *
FROM   volltext_info
WHERE  v_volltext @@ to_tsquery('german', 'Königsbrunn') AND l_preis IS NULL
;

SELECT *
FROM   volltext_info
WHERE  v_volltext @@ to_tsquery('german', 'Königsbrunn') AND l_preis IS NOT NULL
;

SELECT *
FROM   volltext_info
WHERE  v_volltext @@ to_tsquery('german', 'Königsbrunn & Pentium') 
;

SELECT *
FROM   volltext_info
WHERE  v_volltext @@ to_tsquery('german', 'Königsbrunn & IV & 3,8') 
;

SELECT *
FROM   volltext_info
WHERE  v_volltext @@ to_tsquery('german', '190.00') 
;

SELECT *
FROM   volltext_info
WHERE  v_volltext @@ to_tsquery('german', 'Muller') 
;