DROP TABLE IF EXISTS r_teilnahme       CASCADE;
DROP TABLE IF EXISTS e_wettkampf       CASCADE;
DROP TABLE IF EXISTS e_sportler        CASCADE;

DROP TABLE IF EXISTS enum_land         CASCADE;

DROP TABLE IF EXISTS enum_sportstaette CASCADE;
DROP TABLE IF EXISTS enum_wettkampfart CASCADE;
DROP TABLE IF EXISTS enum_sportart     CASCADE;

DROP DOMAIN IF EXISTS D_GESCHLECHT     CASCADE;

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

-- CREATE DOMAIN IF NOT EXISTS D_UNTAINTED
-- AS
-- VARCHAR CHECK (value !~ '[<>"'';]|--|/\*');
DO $$ BEGIN
  CREATE DOMAIN D_UNTAINTED
  AS
  VARCHAR CHECK (value !~ '[<>"'';]|--|/\*');
EXCEPTION
  WHEN duplicate_object THEN null;
END $$;

CREATE DOMAIN D_GESCHLECHT
AS 
CHAR(1) CHECK (VALUE IN ('w', 'm', 'x'));


CREATE TABLE enum_sportart
(id   INTEGER     NOT NULL,
 name D_UNTAINTED NOT NULL,

 CONSTRAINT enum_sportart_pk
   PRIMARY KEY (id),

 CONSTRAINT enum_sportart_unique_name
   UNIQUE (name)
);

CREATE TABLE enum_wettkampfart
(id    INTEGER     NOT NULL,
 sa_id INTEGER     NOT NULL,
 name  D_UNTAINTED NOT NULL,

 CONSTRAINT enum_wettkampfart_pk
   PRIMARY KEY (id),
   
 CONSTRAINT enum_wettkampfart_unique_sa_id_name
   UNIQUE (sa_id, name),
 
 CONSTRAINT enum_wettkampfart_fk_enum_sportart_id
   FOREIGN KEY (sa_id) REFERENCES enum_sportart(id)
);

CREATE TABLE enum_sportstaette
(id   INTEGER     NOT NULL,
 name D_UNTAINTED NOT NULL,

 CONSTRAINT enum_sportstaette_pk
   PRIMARY KEY (id),
 
 CONSTRAINT enum_sportstaette_unique_name
   UNIQUE (name)
);

CREATE TABLE enum_land
(id      INTEGER     NOT NULL,
 kuerzel CHAR(3)     NOT NULL,
 name_de D_UNTAINTED NOT NULL,
 name_en D_UNTAINTED NOT NULL,
 name_fr D_UNTAINTED NOT NULL,

 CONSTRAINT enum_land_pk
   PRIMARY KEY (id),
 
 CONSTRAINT enum_land_unique_kuerzel
   UNIQUE (kuerzel),
  
 CONSTRAINT enum_land_unique_name_de
   UNIQUE (name_de),
  
 CONSTRAINT enum_land_unique_name_en
   UNIQUE (name_en),
  
 CONSTRAINT enum_land_unique_name_fr
   UNIQUE (name_fr)
);

CREATE TABLE e_sportler
(id         INTEGER      NOT NULL,
 name       D_UNTAINTED  NOT NULL,
 vorname    D_UNTAINTED,
 geburtstag DATE         NOT NULL,
 l_id       INTEGER      NOT NULL,
 geschlecht D_GESCHLECHT NOT NULL,
 
 CONSTRAINT e_sportler_pk
   PRIMARY KEY (id),
 
 CONSTRAINT e_sportler_unique_name_vorname_geburtstag_land
   UNIQUE (name, vorname, geburtstag, l_id),
 
 CONSTRAINT e_sportler_fk_enum_land_id
   FOREIGN KEY (l_id) REFERENCES enum_land(id),
 
 CONSTRAINT sportler_check_geschlecht
   CHECK (geschlecht <> 'x')
);

CREATE TABLE e_wettkampf
(id         INTEGER      NOT NULL,
 wa_id      INTEGER      NOT NULL,
 geschlecht D_GESCHLECHT NOT NULL,
 spst_id    INTEGER      NOT NULL,
 beginn     TIMESTAMP    NOT NULL,
 
 CONSTRAINT wettkampf_pk
    PRIMARY KEY (id),

 CONSTRAINT wettkampf_unique_sportart_wettkampfart_geschlecht
   UNIQUE      (wa_id, geschlecht),
 
 CONSTRAINT wettkampf_fk_enum_wettkampfart_id
   FOREIGN KEY (wa_id)   REFERENCES enum_wettkampfart(id),

 CONSTRAINT wettkampf_fk_enum_sportstaette_id
   FOREIGN KEY (spst_id) REFERENCES enum_sportstaette(id)
);

CREATE TABLE r_teilnahme
(s_id            INTEGER NOT NULL,
 w_id            INTEGER NOT NULL,
 platz           INTEGER,
 doping          BOOLEAN,
 disqualifiziert BOOLEAN NOT NULL DEFAULT FALSE,
 
 CONSTRAINT teilnahme_pk
   PRIMARY KEY (s_id, w_id),

 CONSTRAINT teilnahme_fk_sportler_id
   FOREIGN KEY (s_id) REFERENCES e_sportler(id),

 CONSTRAINT teilnahme_fk_wettkampf_id
   FOREIGN KEY (w_id) REFERENCES e_wettkampf(id),
 
 CONSTRAINT t_teilnahme_platz_groesser_0 
   CHECK (platz > 0),
 
 CONSTRAINT t_teilnahme_doper_werden_disqualifiziert 
   CHECK (NOT (doping IS TRUE AND disqualifiziert IS FALSE)),

 CONSTRAINT t_teilnahme_disqualifizierte_belegen_keinen_platz 
   CHECK (NOT (disqualifiziert IS TRUE AND platz IS NOT NULL))
);


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

INSERT INTO enum_sportart(id, name)
VALUES
  (1, 'Judo'),
  (2, 'Schwimmen'),
  (3, 'Rudern'),
  (4, 'Boxen'),
  (5, 'Tennis')
;

INSERT INTO enum_wettkampfart(id, sa_id, name)
VALUES
  (1,  1, 'bis 57kg'),
  (2,  1, 'bis 80kg'),
  (3,  2, 'Lagen 400m'),
  (4,  2, 'Schmetterling 100m'),
  (5,  2, 'Brust 100m'),
  (6,  2, 'Brust 200m'),
  (7,  3, 'Kajak-Einer'),
  (8,  4, 'bis 57kg'),
  (9,  4, 'bis 80kg'),
  (10, 5, 'Einzel Finale'),
  (11, 5, 'Mixed Finale')
;

INSERT INTO enum_sportstaette(id, name)
VALUES
  (1, 'Halle 1'),
  (2, 'Halle 2'),
  (3, 'Schwimmhalle 1'),
  (4, 'Schwimmhalle 2'),
  (5, 'Ruderstrecke'),
  (6, 'Center Court')
;

INSERT INTO enum_land(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 e_sportler(id, name, vorname, geburtstag, geschlecht, l_id)
VALUES
  (1,  'Völker',                'Sandra',     DATE '1974-04-01', 'w', 4),
  (2,  'van Almsick',           'Franziska',  DATE '1978-04-05', 'w', 4),
  (3,  'Harstick',              'Sara',       DATE '1981-01-01', 'w', 4),
  (4,  'Buschschulte',          'Antje',      DATE '1978-12-27', 'w', 4),
  (5,  'Filippi',               'Alessia',    DATE '1987-06-23', 'w', 6),
  (6,  'Angelopoulou',          'Vasiliki',   DATE '1987-05-30', 'w', 5),
  (7,  'Rutschow-Stomporowski', 'Katrin',     DATE '1975-04-02', 'w', 4),
  (8,  'Karsten',               'Ekaterina',  DATE '1972-06-02', 'w', 1),
  (9,  'Fedotova',              'Irina',      DATE '1970-01-01', 'w', 7),
  (10, 'Boenisch',              'Yvonne',     DATE '1980-12-29', 'w', 4),
  (11, 'Yukhareva',             'Natalya',    DATE '1970-01-01', 'w', 7),
  (12, 'Cavazzuti',             'Cinzia',     DATE '1970-01-01', 'w', 6),
  (13, 'Harel',                 'Barbara',    DATE '1970-01-01', 'w', 3),
  (14, 'Xeteylu',               'Leidisyuri', DATE '1970-01-01', 'w', 2),
  (15, 'Tuuxaru',               'Iliisquix',  DATE '1970-01-01', 'm', 2),
  (16, 'Maier',                 'Josef',      DATE '1980-01-01', 'm', 4),
  (17, 'Maier',                 'Josef',      DATE '1982-01-01', 'm', 4),
  (18, 'Dede',                  NULL,         DATE '1970-01-01', 'm', 4),
  (19, 'Graf',                  'Steffi',     DATE '1969-06-14', 'w', 4),
  (20, 'Becker',                'Boris',      DATE '1967-11-22', 'm', 4)
;

INSERT INTO e_wettkampf (id, wa_id, geschlecht, spst_id, beginn)
VALUES
  ( 1,  1, 'w', 1, TIMESTAMP '2004-08-14 16:30'),
  ( 2,  2, 'm', 2, TIMESTAMP '2004-08-14 16:30'),
  ( 3,  3, 'w', 3, TIMESTAMP '2004-08-14 17:30'),
  ( 4,  4, 'w', 3, TIMESTAMP '2004-08-15 15:30'),
  ( 5,  5, 'w', 4, TIMESTAMP '2004-08-16 12:15'),
  ( 6,  5, 'm', 4, TIMESTAMP '2004-08-16 14:15'),
  ( 7,  6, 'w', 3, TIMESTAMP '2004-08-17 12:15'),
  ( 8,  6, 'm', 3, TIMESTAMP '2004-08-17 14:15'),
  ( 9,  7, 'w', 5, TIMESTAMP '2004-08-19 19:30'),
  (10,  7, 'm', 5, TIMESTAMP '2004-08-19 19:30'),
  (11,  8, 'w', 1, TIMESTAMP '2004-08-17 16:30'),
  (12,  9, 'm', 2, TIMESTAMP '2004-08-17 16:30'),
  (13, 10, 'w', 6, TIMESTAMP '2004-08-20 20:00'),
  (14, 10, 'm', 6, TIMESTAMP '2004-08-21 20:00'),
  (15, 11, 'x', 6, TIMESTAMP '2004-08-22 20:00')
;

INSERT INTO r_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, NULL),
  ( 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),
  (19, 13, 1, FALSE),
  (20, 14, 1, FALSE),
  (19, 15, 1, FALSE),
  (20, 15, 1, FALSE)
;

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