--DROP LANGUAGE IF EXISTS plpgsql CASCADE;
--CREATE LANGUAGE plpgsql;

DROP VIEW  IF EXISTS teilnahme            CASCADE;
DROP VIEW  IF EXISTS wettkampf            CASCADE;
DROP VIEW  IF EXISTS sportler             CASCADE;




DROP TYPE  IF EXISTS ENUM_GESCHLECHT      CASCADE;


/*****************************************************************************
 * Datentypen und Tabellen
 *****************************************************************************/

CREATE TYPE ENUM_GESCHLECHT
AS 
ENUM ('w', 'm');


CREATE TABLE enum_sportart
(
 sa_id       INTEGER     NOT NULL,
 bezeichnung VARCHAR(50) NOT NULL,

 PRIMARY KEY (sa_id),
 UNIQUE (bezeichnung)
);

CREATE TABLE enum_wettkampfart
(
 wa_id       INTEGER     NOT NULL,
 sa_id       INTEGER     NOT NULL,
 bezeichnung VARCHAR(50) NOT NULL,

 PRIMARY KEY (wa_id),
 UNIQUE (sa_id, bezeichnung),
 FOREIGN KEY (sa_id) REFERENCES enum_sportart
);


CREATE TABLE enum_sportstaette
(
 spst_id     INTEGER     NOT NULL,
 bezeichnung VARCHAR(50) NOT NULL,

 PRIMARY KEY (spst_id),
 UNIQUE (bezeichnung)
);


CREATE TABLE enum_land
(
 l_id    INTEGER     NOT NULL,
 kuerzel CHAR(3)     NOT NULL,
 name_de VARCHAR(50) NOT NULL,
 name_en VARCHAR(50) NOT NULL,
 name_fr VARCHAR(50) NOT NULL,

 PRIMARY KEY (l_id),
 UNIQUE (kuerzel),
 UNIQUE (name_de),
 UNIQUE (name_en),
 UNIQUE (name_fr)
);


CREATE TABLE e_sportler
(
 s_id       INTEGER         NOT NULL,
 name       VARCHAR(50)     NOT NULL,
 vorname    VARCHAR(50),
 geburtstag DATE            NOT NULL,
 geschlecht ENUM_GESCHLECHT NOT NULL,
 l_id       INTEGER         NOT NULL,
 PRIMARY KEY (s_id),
 UNIQUE (name, vorname, geburtstag, l_id),
 FOREIGN KEY (l_id)  REFERENCES enum_land
);

CREATE TABLE e_wettkampf
(
 w_id       INTEGER         NOT NULL,
 wa_id      INTEGER         NOT NULL,
 geschlecht ENUM_GESCHLECHT NOT NULL,
 spst_id    INTEGER         NOT NULL,
 beginn     TIMESTAMP       NOT NULL,
 PRIMARY KEY (w_id),
 UNIQUE      (wa_id, geschlecht),
 FOREIGN KEY (wa_id)   REFERENCES enum_wettkampfart,
 FOREIGN KEY (spst_id) REFERENCES enum_sportstaette
);

CREATE TABLE r_teilnahme
(
 s_id            INTEGER NOT NULL,
 w_id            INTEGER NOT NULL,
 platz           INTEGER,
 doping          BOOLEAN,
 disqualifiziert BOOLEAN NOT NULL DEFAULT FALSE,
 PRIMARY KEY (s_id, w_id),
 FOREIGN KEY (s_id) REFERENCES e_sportler,
 FOREIGN KEY (w_id) REFERENCES e_wettkampf,
 CHECK (platz > 0),
 CHECK (NOT (doping AND NOT disqualifiziert)),
 CHECK (NOT (disqualifiziert AND platz IS NOT NULL))
);


/*****************************************************************************
 * Komplexe Check-Constraints.
 *****************************************************************************/

/*
CREATE ASSERTION platz_nicht_zu_gross
CHECK (NOT EXISTS
        (SELECT * 
         FROM   r_teilnahme t1
         WHERE  platz > (SELECT COUNT(*) 
                         FROM r_teilnahme t2
                         WHERE t1.wid = t2.wid AND
                               t2.platz IS NOT NULL
                        )
        )
      )
;
*/

DROP TRIGGER  IF EXISTS platz_nicht_zu_gross_trigger ON r_teilnahme;
DROP FUNCTION IF EXISTS platz_nicht_zu_gross() CASCADE;

CREATE FUNCTION platz_nicht_zu_gross() RETURNS TRIGGER AS
$code$
  BEGIN
    IF (NEW.platz > (SELECT COUNT(*) 
                     FROM   r_teilnahme t
                     WHERE      t.w_id = NEW.w_id
                            AND t.platz IS NOT NULL
                    )
        )
    THEN
      RAISE EXCEPTION '\n  So viele Plaetze gibt es im Wettkampf\n  %\n  nicht: %',
                      (SELECT   '"' || sportart || ', ' || wettkampfart || ', ' 
                              || CASE WHEN geschlecht = 'w' THEN 'Frauen' ELSE 'Männer' END 
                              || '"'
                       FROM   wettkampf
                       WHERE  w_id = NEW.w_id
                      ),      
                      NEW.platz;
    END IF;
    RETURN NULL;
  END
$code$
LANGUAGE plpgsql;

CREATE CONSTRAINT TRIGGER platz_nicht_zu_gross_trigger
AFTER INSERT OR UPDATE OR DELETE 
ON r_teilnahme
DEFERRABLE INITIALLY DEFERRED
FOR EACH ROW
  EXECUTE PROCEDURE platz_nicht_zu_gross();


DROP TRIGGER  IF EXISTS geschlecht_korrekt ON r_teilnahme;
DROP FUNCTION IF EXISTS geschlecht_korrekt() CASCADE;

CREATE FUNCTION geschlecht_korrekt() RETURNS TRIGGER AS
$code$
  BEGIN
    IF ((SELECT geschlecht FROM e_sportler s
         WHERE s.s_id = NEW.s_id)
        <>
       (SELECT geschlecht FROM e_wettkampf w
        WHERE w.w_id = NEW.w_id)
       )
    THEN
      RAISE EXCEPTION 
      '\n  % darf an\n  %\n  nicht teilnehmen (falsches Geschlecht).',
      (SELECT      COALESCE(vorname || ' ' || name, name) 
                || ' (' 
                || TO_CHAR(geburtstag, 'DD.MM.YYYY')
                || ', '
                || (SELECT kuerzel 
                    FROM   e_sportler s JOIN enum_land l USING (l_id)
                    WHERE  s_id = NEW.s_id
                   )
                || ')'
       FROM   sportler
       WHERE  s_id = NEW.s_id
      ),
      (SELECT   '"' || sportart || ', ' || wettkampfart || ', ' 
                    || CASE WHEN geschlecht = 'w' THEN 'Frauen' ELSE 'Männer' END 
                    || '"'
       FROM   wettkampf
       WHERE  w_id = NEW.w_id
      );
    END IF;
    RETURN NULL;
  END
$code$
LANGUAGE plpgsql;

CREATE CONSTRAINT TRIGGER geschlecht_korrekt
AFTER INSERT OR UPDATE OR DELETE 
ON r_teilnahme
DEFERRABLE INITIALLY DEFERRED
FOR EACH ROW
  EXECUTE PROCEDURE geschlecht_korrekt();  
  
/*****************************************************************************
 * Views, um Tabellen ohne Kategorietabellen zu simulieren.
 *****************************************************************************/

CREATE VIEW sportler (s_id, name, vorname, geburtstag, geschlecht, land)
AS
SELECT s_id, name, vorname, geburtstag, geschlecht, kuerzel
FROM   e_sportler s JOIN enum_land l USING (l_id)
;

CREATE VIEW wettkampf (w_id, sportart, wettkampfart, geschlecht, sportstaette, beginn)
AS
SELECT w_id, sa.bezeichnung, wa.bezeichnung, geschlecht, spst.bezeichnung, beginn
FROM   e_wettkampf w JOIN enum_wettkampfart wa   USING (wa_id)
                     JOIN enum_sportart sa       USING (sa_id)
                     JOIN enum_sportstaette spst USING (spst_id)
;

CREATE VIEW teilnahme (s_id, w_id, platz, doping, disqualifiziert)
AS
SELECT s_id, w_id, platz, doping, disqualifiziert
FROM   r_teilnahme
;


DROP RULE IF EXISTS insert_into_sportler ON sportler CASCADE;
DROP RULE IF EXISTS delete_from_sportler ON sportler CASCADE;
DROP RULE IF EXISTS update_sportler      ON sportler CASCADE;

CREATE RULE insert_into_sportler AS ON INSERT TO sportler
DO INSTEAD
INSERT INTO e_sportler (s_id, name, vorname, geburtstag, geschlecht, l_id)
VALUES (NEW.s_id,
        NEW.name,
        NEW.vorname,
        NEW.geburtstag,
        NEW.geschlecht,
        (SELECT l_id  FROM enum_land WHERE kuerzel = NEW.land)
       );

CREATE RULE delete_from_sportler AS ON DELETE TO sportler
DO INSTEAD
DELETE FROM e_sportler
WHERE  s_id = OLD.s_id;

CREATE RULE update_sportler AS ON UPDATE TO sportler
DO INSTEAD
UPDATE e_sportler
SET s_id       = NEW.s_id,
    name       = NEW.name,
    vorname    = NEW.vorname,
    geburtstag = NEW.geburtstag,
    geschlecht = NEW.geschlecht,
    l_id       = (SELECT l_id  FROM enum_land WHERE kuerzel = NEW.land)
WHERE  s_id = OLD.s_id;


DROP RULE IF EXISTS insert_into_wettkampf ON wettkampf CASCADE;
DROP RULE IF EXISTS delete_from_wettkampf ON wettkampf CASCADE;
DROP RULE IF EXISTS update_wettkampf      ON wettkampf CASCADE;

CREATE RULE insert_into_wettkampf AS ON INSERT TO wettkampf
DO INSTEAD
INSERT INTO e_wettkampf (w_id, wa_id, geschlecht, spst_id, beginn)
VALUES (NEW.w_id,
        (SELECT wa_id   
         FROM   enum_wettkampfart w JOIN enum_sportart s USING (sa_id)
         WHERE  w.bezeichnung = NEW.wettkampfart AND s.bezeichnung = NEW.sportart
        ),
        NEW.geschlecht,
        (SELECT spst_id FROM enum_sportstaette WHERE bezeichnung = NEW.sportstaette),
        NEW.beginn
       );

CREATE RULE delete_from_wettkampf AS ON DELETE TO wettkampf
DO INSTEAD
DELETE FROM e_wettkampf
WHERE  w_id = OLD.w_id;

CREATE RULE update_wettkampf AS ON UPDATE TO wettkampf
DO INSTEAD
UPDATE e_wettkampf
SET w_id       = NEW.w_id,
    wa_id      = (SELECT wa_id
                  FROM   enum_wettkampfart w JOIN enum_sportart s USING (sa_id)
                  WHERE  w.bezeichnung = NEW.wettkampfart AND s.bezeichnung = NEW.sportart
                 ),
    geschlecht = NEW.geschlecht,
    spst_id    = (SELECT spst_id FROM enum_sportstaette WHERE bezeichnung = NEW.sportstaette),
    beginn     = NEW.beginn
WHERE  w_id = OLD.w_id;



DROP RULE IF EXISTS insert_into_teilnahme ON teilnahme CASCADE;
DROP RULE IF EXISTS delete_from_teilnahme ON teilnahme CASCADE;
DROP RULE IF EXISTS update_teilnahme      ON teilnahme CASCADE;

CREATE RULE insert_into_teilnahme AS ON INSERT TO teilnahme
DO INSTEAD
INSERT INTO r_teilnahme (s_id, w_id, platz, doping, disqualifiziert)
VALUES (NEW.s_id, NEW.w_id, NEW.platz, NEW.doping, COALESCE(NEW.disqualifiziert, FALSE))
;

CREATE RULE delete_from_teilnahme AS ON DELETE TO teilnahme
DO INSTEAD
DELETE FROM r_teilnahme
WHERE  s_id = OLD.s_id AND w_id = OLD.w_id;

CREATE RULE update_teilnahme AS ON UPDATE TO teilnahme
DO INSTEAD
UPDATE r_teilnahme
SET s_id            = NEW.s_id,
    w_id            = NEW.w_id,
    platz           = NEW.platz,
    doping          = NEW.doping,
    disqualifiziert = COALESCE(NEW.disqualifiziert, FALSE)
WHERE  s_id = OLD.s_id AND w_id = OLD.w_id;


/*****************************************************************************
 * Beispielsdaten
 *****************************************************************************/

INSERT INTO enum_sportart(sa_id, bezeichnung)
VALUES
  (1, 'Judo'),
  (2, 'Schwimmen'),
  (3, 'Rudern'),
  (4, 'Boxen')
;

INSERT INTO enum_wettkampfart(wa_id, sa_id, bezeichnung)
VALUES
  (1, 1, 'bis 57kg'),
  (2, 1, 'bis 80kg'),
  (3, 2, '400m Lagen'),
  (4, 2, '100m Schmetterling'),
  (5, 2, '100m Brust'),
  (6, 2, '200m Brust'),
  (7, 3, 'Einer'),
  (8, 4, 'bis 57kg'),
  (9, 4, 'bis 80kg')
;

INSERT INTO enum_sportstaette(spst_id, bezeichnung)
VALUES
  (1, 'Halle 1'),
  (2, 'Halle 2'),
  (3, 'Schwimmhalle 1'),
  (4, 'Schwimmhalle 2'),
  (5, 'Ruderstrecke')
;

INSERT INTO enum_land(l_id, kuerzel, name_de, name_en, name_fr)
VALUES
  (1, 'BLR', 'Weißrußland', 'Belarus', 'Biélorussie'),
  (2, 'CUB', 'Kuba', 'Cuba', 'Cuba'),
  (3, 'FRA', 'Frankreich', 'France', 'France'),
  (4, 'GER', 'Bundesrepublik Deutschland', 'Federal Republic of Germany', 'République fédérale d’Allemagne'),
  (5, 'GRE', 'Griechenland', 'Greece', 'Grèce'),
  (6, 'ITA', 'Italien', 'Italy', 'Italie'),
  (7, 'RUS', 'Russische Föderation', 'Russian Federation', 'Fédération de Russie ')
;


INSERT INTO sportler(s_id, name, vorname, geburtstag, geschlecht, land)
VALUES
  (1,  'Völker',                'Sandra',     DATE '1974-04-01', 'w', 'GER'),
  (2,  'van Almsick',           'Franziska',  DATE '1978-04-05', 'w', 'GER'),
  (3,  'Harstick',              'Sara',       DATE '1981-01-01', 'w', 'GER'),
  (4,  'Buschschulte',          'Antje',      DATE '1978-12-27', 'w', 'GER'),
  (5,  'Filippi',               'Alessia',    DATE '1987-06-23', 'w', 'ITA'),
  (6,  'Angelopoulou',          'Vasiliki',   DATE '1987-05-30', 'w', 'GRE'),
  (7,  'Rutschow-Stomporowski', 'Katrin',     DATE '1975-04-02', 'w', 'GER'),
  (8,  'Karsten',               'Ekaterina',  DATE '1972-06-02', 'w', 'BLR'),
  (9,  'Fedotova',              'Irina',      DATE '1970-01-01', 'w', 'RUS'),
  (10, 'Boenisch',              'Yvonne',     DATE '1980-12-29', 'w', 'GER'),
  (11, 'Yukhareva',             'Natalya',    DATE '1970-01-01', 'w', 'RUS'),
  (12, 'Cavazzuti',             'Cinzia',     DATE '1970-01-01', 'w', 'ITA'),
  (13, 'Harel',                 'Barbara',    DATE '1970-01-01', 'w', 'FRA'),
  (14, 'Xeteylu',               'Leidisyuri', DATE '1970-01-01', 'w', 'CUB'),
  (15, 'Tuuxaru',               'Iliisquix',  DATE '1970-01-01', 'm', 'CUB'),
  (16, 'Maier',                 'Josef',      DATE '1980-01-01', 'm', 'GER'),
  (17, 'Maier',                 'Josef',      DATE '1982-01-01', 'm', 'GER'),
  (18, 'Dede',                  null,         DATE '1970-01-01', 'm', 'GER')
;

INSERT INTO wettkampf (w_id, sportart, wettkampfart, geschlecht, sportstaette, beginn)
VALUES
  ( 1, 'Judo',      'bis 57kg',           'w', 'Halle 1',        TIMESTAMP '2004-08-14 16:30'),
  ( 2, 'Judo',      'bis 80kg',           'm', 'Halle 2',        TIMESTAMP '2004-08-14 16:30'),
  ( 3, 'Schwimmen', '400m Lagen',         'w', 'Schwimmhalle 1', TIMESTAMP '2004-08-14 17:30'),
  ( 4, 'Schwimmen', '100m Schmetterling', 'w', 'Schwimmhalle 1', TIMESTAMP '2004-08-15 15:30'),
  ( 5, 'Schwimmen', '100m Brust',         'w', 'Schwimmhalle 2', TIMESTAMP '2004-08-16 12:15'),
  ( 6, 'Schwimmen', '100m Brust',         'm', 'Schwimmhalle 2', TIMESTAMP '2004-08-16 14:15'),
  ( 7, 'Schwimmen', '200m Brust',         'w', 'Schwimmhalle 1', TIMESTAMP '2004-08-17 12:15'),
  ( 8, 'Schwimmen', '200m Brust',         'm', 'Schwimmhalle 1', TIMESTAMP '2004-08-17 14:15'),
  ( 9, 'Rudern',    'Einer',              'w', 'Ruderstrecke',   TIMESTAMP '2004-08-19 19:30'),
  (10, 'Rudern',    'Einer',              'm', 'Ruderstrecke',   TIMESTAMP '2004-08-19 19:30'),
  (11, 'Boxen',     'bis 57kg',           'w', 'Halle 1',        TIMESTAMP '2004-08-17 16:30'),
  (12, 'Boxen',     'bis 80kg',           'm', 'Halle 2',        TIMESTAMP '2004-08-17 16:30')
;


INSERT INTO teilnahme (s_id, w_id, platz, doping)
VALUES
  ( 1,  3, 1, FALSE),
  ( 1,  4, 1, FALSE),
  ( 1,  5, 1, FALSE),
  ( 1,  7, 1, FALSE),
  ( 2,  3, 2, NULL),
  ( 2,  5, 8, FALSE),
  ( 2,  9, 1, FALSE),
  ( 3,  3, 1, NULL),
  ( 4,  3, 1, FALSE),
  ( 4,  4, 1, FALSE),
  ( 4,  5, 2, FALSE),
  ( 5,  3, 1, FALSE),
  ( 5,  5, 2, FALSE),
  ( 6,  5, 1, FALSE),
  ( 8,  5, 1, FALSE),
  (11,  5, 1, FALSE),
  (13,  5, 2, FALSE),
  (13,  4, NULL, FALSE),
  (16, 10, NULL, FALSE)
;

INSERT INTO teilnahme (s_id, w_id, platz, doping, disqualifiziert)
VALUES
  (14,  5, NULL, TRUE,  TRUE),
  (15,  6, NULL, TRUE,  TRUE),
  (17,  6, NULL, FALSE, TRUE)
;
