DROP TABLE IF EXISTS verfuegbarkeit CASCADE;
DROP TABLE IF EXISTS preis CASCADE;
DROP TABLE IF EXISTS reservierung CASCADE;
DROP TABLE IF EXISTS sitzplatz CASCADE;
DROP TABLE IF EXISTS veranstaltung CASCADE;
DROP TABLE IF EXISTS veranstaltungs_kategorie CASCADE;
DROP TABLE IF EXISTS sitzplatz_kategorie CASCADE;
DROP TABLE IF EXISTS personen_kategorie CASCADE;


CREATE TABLE personen_kategorie
(
  pk_id INTEGER     NOT NULL,
  p_kat VARCHAR(50) NOT NULL,
  PRIMARY KEY (pk_id),
  UNIQUE (p_kat)
)
;

CREATE TABLE veranstaltungs_kategorie
(
  vk_id INTEGER     NOT NULL,
  v_kat VARCHAR(50) NOT NULL,
  PRIMARY KEY (vk_id),
  UNIQUE (v_kat)
)
;

CREATE TABLE sitzplatz_kategorie
(
  sk_id INTEGER     NOT NULL,
  s_kat VARCHAR(50) NOT NULL,
  PRIMARY KEY (sk_id),
  UNIQUE (s_kat)
)
;

CREATE TABLE preis
(
  vk_id INTEGER      NOT NULL,
  sk_id INTEGER      NOT NULL,
  pk_id INTEGER      NOT NULL,
  preis NUMERIC(7,2) NOT NULL,
  PRIMARY KEY (vk_id, sk_id, pk_id),
  FOREIGN KEY (vk_id) REFERENCES veranstaltungs_kategorie,
  FOREIGN KEY (sk_id) REFERENCES sitzplatz_kategorie,
  FOREIGN KEY (pk_id) REFERENCES personen_kategorie
)
;
  
CREATE TABLE veranstaltung
(
  v_id  INTEGER      NOT NULL,
  vk_id INTEGER      NOT NULL,
  titel VARCHAR(100) NOT NULL,
  start TIMESTAMP    NOT NULL,
  dauer INTERVAL     NOT NULL,
  PRIMARY KEY (v_id),
  UNIQUE (start),
  FOREIGN KEY (vk_id) REFERENCES veranstaltungs_kategorie
)
;
  
CREATE TABLE sitzplatz
(
  s_id   INTEGER NOT NULL,
  sk_id  INTEGER NOT NULL,
  reihe  INTEGER NOT NULL,
  nummer INTEGER NOT NULL,
  PRIMARY KEY (s_id),
  UNIQUE (sk_id, reihe, nummer),
  FOREIGN KEY (sk_id) REFERENCES sitzplatz_kategorie
)
;
  
CREATE TABLE reservierung
(
  v_id         INTEGER NOT NULL,
  s_id         INTEGER NOT NULL,
  pk_id        INTEGER NOT NULL,
  ticketnummer INTEGER NOT NULL,
  PRIMARY KEY (v_id, s_id),
  FOREIGN KEY (v_id)  REFERENCES veranstaltung,
  FOREIGN KEY (s_id)  REFERENCES sitzplatz,
  FOREIGN KEY (pk_id) REFERENCES personen_kategorie
)
;
  

INSERT INTO personen_kategorie(pk_id, p_kat)
VALUES
  (1, 'Kind'),
  (2, 'Schüler/Student'),
  (3, 'Erwachsener')
;

INSERT INTO veranstaltungs_kategorie(vk_id, v_kat)
VALUES 
  (1, 'Kinder-Vorstellung'),
  (2, 'Erwachsenen-Vorstellung'),
  (3, 'Special Event')
;

INSERT INTO sitzplatz_kategorie(sk_id, s_kat)
VALUES 
  (1, 'Rang 1'),
  (2, 'Rang 2'),
  (3, 'Balkon')
;

INSERT INTO preis(vk_id, sk_id, pk_id, preis)
VALUES 
  (1, 1, 1,  5.00),
  (1, 1, 2,  6.00),
  (1, 1, 3, 10.00),
  (1, 2, 1,  3.00),
  (1, 2, 2,  4.00),
  (1, 2, 3,  8.00),
  (1, 3, 1,  1.00),
  (1, 3, 2,  2.00),
  (1, 3, 3,  6.00),
  (2, 1, 1, 10.00),
  (2, 1, 2, 12.00),
  (2, 1, 3, 20.00),
  (2, 2, 1,  9.00),
  (2, 2, 2, 11.00),
  (2, 2, 3, 18.00),
  (2, 3, 1,  8.00),
  (2, 3, 2, 10.00),
  (2, 3, 3, 16.00),
  (3, 1, 1, 15.00),
  (3, 1, 2, 17.00),
  (3, 1, 3, 30.00),
  (3, 2, 1, 14.00),
  (3, 2, 2, 16.00),
  (3, 2, 3, 28.00),
  (3, 3, 1, 13.00),
  (3, 3, 2, 15.00),
  (3, 3, 3, 26.00)
;


INSERT INTO veranstaltung(v_id, vk_id, titel, start, dauer)
VALUES
  (1, 1, 'Peter und der Wolf', TIMESTAMP '2011-12-24 14:00', INTERVAL '1 hour 30 minutes'),
  (2, 2, 'Der Freischütz',     TIMESTAMP '2011-01-05 18:00', INTERVAL '2 hours'),
  (3, 2, 'Der Freischütz',     TIMESTAMP '2012-02-26 20:30', INTERVAL '2 hours'),
  (4, 2, 'Der Freischütz',     TIMESTAMP '2012-01-14 20:00', INTERVAL '2 hours'),
  (5, 3, 'Silvesterball',      TIMESTAMP '2011-12-31 20:00', INTERVAL '6 hours')
;


INSERT INTO sitzplatz(s_id, sk_id, reihe, nummer)
VALUES
  ( 1, 1, 1, 1), 
  ( 2, 1, 1, 2), 
  ( 3, 1, 1, 3), 
  ( 4, 1, 1, 4), 
  ( 5, 1, 1, 5), 

  ( 6, 2, 2, 1), 
  ( 7, 2, 2, 2), 
  ( 8, 2, 2, 3), 
  ( 9, 2, 2, 4), 
  (10, 2, 2, 5), 
  (11, 2, 2, 6), 
  (12, 2, 2, 7), 

  (13, 2, 3, 1), 
  (14, 2, 3, 2), 
  (15, 2, 3, 3), 
  (16, 2, 3, 4), 
  (17, 2, 3, 5), 
  (18, 2, 3, 6), 
  (19, 2, 3, 7), 
  (20, 2, 3, 8), 
  (21, 2, 3, 9), 

  (22, 3, 1, 1), 
  (23, 3, 1, 2), 
  (24, 3, 1, 3), 
  (25, 3, 1, 4), 
  (26, 3, 1, 5)
; 


INSERT INTO reservierung(v_id, s_id, pk_id, ticketnummer)
VALUES
  (1, 1, 1, 1),
  (1, 2, 2, 1),
  (1, 3, 1, 1),
  (1, 4, 3, 1),
  (1, 5, 1, 1),
  (1, 6, 1, -1),
  (1, 7, 3, -1),
  (1, 8, 1, -1),
  (1, 9, 1, -1),
  (1, 10, 3, -1),
  (1, 11, 3, -1),
  (1, 12, 1, -2),
  (1, 13, 1, -2),
  (1, 14, 1, -2),
  (1, 15, 2, -3),
  (1, 16, 1, -4),
  (1, 17, 3, -4),
  (1, 18, 1, -4),
  (1, 19, 2, -4),
  (1, 20, 2, -5),
  (1, 21, 1, -5),
  (1, 22, 3, -6),
  (1, 23, 1, -7),
  (1, 24, 2, -8),
  (1, 25, 1, -9),
  (1, 26, 1, -9),
  (2, 1, 1, 2),
  (2, 2, 1, 2),
  (2, 3, 1, 2),
  (2, 4, 1, -1),
  (2, 5, 3, -1),
  (2, 6, 2, 3),
  (2, 7, 3, 3),
  (3, 1, 2, 4),
  (3, 2, 1, 4),
  (3, 3, 1, 4),
  (3, 4, 1, -2),
  (3, 5, 1, -2),
  (3, 6, 2, 4),
  (3, 7, 3, 5),
  (3, 8, 1, 5),
  (3, 9, 1, 5),
  (4, 1, 1, 5),
  (4, 2, 1, 6),
  (4, 3, 2, 6),
  (4, 4, 1, 6),
  (4, 5, 1, 6) 
;