DROP TABLE IF EXISTS sitzplatzreservierung   CASCADE;
DROP TABLE IF EXISTS reservierung            CASCADE;
DROP TABLE IF EXISTS sitzplatz               CASCADE;
DROP TABLE IF EXISTS verfuegbarkeit          CASCADE;
DROP TABLE IF EXISTS kalender                CASCADE;
DROP TABLE IF EXISTS veranstaltung           CASCADE;
DROP TABLE IF EXISTS preis                   CASCADE;
DROP TABLE IF EXISTS preisdauer              CASCADE;
DROP TABLE IF EXISTS stueck                  CASCADE;
DROP TABLE IF EXISTS sitzplatzkategorie      CASCADE;
DROP TABLE IF EXISTS veranstaltungskategorie CASCADE;


CREATE TABLE veranstaltungskategorie
( vk_id     INTEGER NOT NULL,
  kategorie VARCHAR NOT NULL,
  PRIMARY KEY (vk_id),
  UNIQUE (kategorie)
)
;

CREATE TABLE sitzplatzkategorie
( sk_id     INTEGER NOT NULL,
  kategorie VARCHAR NOT NULL,
  PRIMARY KEY (sk_id),
  UNIQUE (kategorie)
)
;

CREATE TABLE preisdauer
( beginn DATE NOT NULL,
  ende   DATE NOT NULL,
  PRIMARY KEY (beginn),
  UNIQUE      (ende),
  CHECK       (ende > beginn)  
)
;

CREATE TABLE preis
( vk_id  INTEGER      NOT NULL,
  sk_id  INTEGER      NOT NULL,
  beginn DATE         NOT NULL,
  preis  NUMERIC(7,2) NOT NULL,
  PRIMARY KEY (vk_id, sk_id, beginn),
  FOREIGN KEY (vk_id)  REFERENCES veranstaltungskategorie,
  FOREIGN KEY (sk_id)  REFERENCES sitzplatzkategorie,
  FOREIGN KEY (beginn) REFERENCES preisdauer
)
;

CREATE TABLE stueck
( st_id        INTEGER   NOT NULL,
  titel        VARCHAR   NOT NULL,
  beschreibung TEXT      NOT NULL,
  dauer        INTERVAL  NOT NULL,
  PRIMARY KEY (st_id),
  UNIQUE      (titel)
)
;
  
CREATE TABLE veranstaltung
( v_id        INTEGER   NOT NULL,
  vk_id       INTEGER   NOT NULL,
  st_id       INTEGER   NOT NULL,
  beginn      TIMESTAMP NOT NULL,
  PRIMARY KEY (v_id),
  UNIQUE (beginn),
  FOREIGN KEY (vk_id) REFERENCES veranstaltungskategorie,
  FOREIGN KEY (st_id) REFERENCES stueck
)
;

CREATE TABLE sitzplatz
( s_id   INTEGER NOT NULL,
  sk_id  INTEGER NOT NULL,
  reihe  INTEGER NOT NULL,
  platz  INTEGER NOT NULL,
  PRIMARY KEY (s_id),
  UNIQUE (reihe, platz),
  FOREIGN KEY (sk_id) REFERENCES sitzplatzkategorie
)
;
  
CREATE TABLE reservierung
( r_id     INTEGER NOT NULL,
  v_id     INTEGER NOT NULL,
  passcode VARCHAR DEFAULT encode(gen_random_bytes(32), 'hex'),
  qrcode   VARCHAR,
  PRIMARY KEY (r_id),
  UNIQUE (passcode),
  FOREIGN KEY (v_id) REFERENCES veranstaltung
)
;

CREATE TABLE sitzplatzreservierung
( r_id INTEGER NOT NULL,
  s_id INTEGER NOT NULL,
  PRIMARY KEY (r_id, s_id),
  FOREIGN KEY (r_id) REFERENCES reservierung,
  FOREIGN KEY (s_id) REFERENCES sitzplatz
)
;

INSERT INTO veranstaltungskategorie(vk_id, kategorie)
VALUES 
(1, 'Kindervorstellung'),
(2, 'Erwachsenenvorstellung'),
(3, 'Sondervorstellung Schulen')
;

INSERT INTO sitzplatzkategorie(sk_id, kategorie)
VALUES 
(1, 'rot'),
(2, 'blau'),
(3, 'grün')
;

INSERT INTO preisdauer(beginn, ende)
VALUES 
('2000-01-01', '2023-01-01'),
('2023-01-01', '3000-01-01')
;

INSERT INTO preis(vk_id, sk_id, beginn, preis)
VALUES 
(1, 1, '2000-01-01', 14.00),
(1, 2, '2000-01-01', 12.50),
(1, 3, '2000-01-01',  9.50),
(2, 1, '2000-01-01', 29.00),
(2, 2, '2000-01-01', 25.50),
(2, 3, '2000-01-01', 19.00),
(3, 1, '2000-01-01', 14.00),
(3, 2, '2000-01-01', 12.50),
(3, 3, '2000-01-01',  9.50),

(1, 1, '2023-01-01', 15.50),
(1, 2, '2023-01-01', 13.50),
(1, 3, '2023-01-01',  9.50),
(2, 1, '2023-01-01', 32.00),
(2, 2, '2023-01-01', 28.00),
(2, 3, '2023-01-01', 19.50),
(3, 1, '2023-01-01', 15.50),
(3, 2, '2023-01-01', 13.50),
(3, 3, '2023-01-01',  9.50)
;

INSERT INTO stueck(st_id, titel, dauer, beschreibung)
VALUES
(1, 'Frau Holle', INTERVAL '1 hour 20 minutes',
 'Als arme Magd lebt die fleißige Liese im Hause ihrer Stiefmutter, bis sie eines Tages in das Reich der gütigen »Frau Holle« gerät. Ein Marionettenstück nach dem Märchen der Gebrüder Grimm.'
),
(2, 'Der kleine Wassermann', INTERVAL '1 hour 30 minutes',
 '»Der kleine Wassermann« erlebt im Mühlenweiher so allerhand lustige und spannende Abenteuer. Zum Glück hat er stets einen treuen Freund an seiner Seite - den Karpfen Cyprinus.'
),
(3, 'Kabarett 2023', INTERVAL '2 hours',
 'Von Klassik bis Pop, vom gespielten Witz bis zur politischen Satire, Sinniges und Sinnliches umfasst das Kabarett der Augsburger Puppen. Mal heiter, mal tiefgründig unterhält das Ensemble der Augsburger Puppenkiste seine Besucher jedes Jahr auf´s neue mit einem bunten 2-stündigen Programm. Auch ein Blick hinter die Kulissen darf dabei nicht fehlen.'
),
(4, 'Der gestiefelte Kater', INTERVAL '1 hour 30 minutes',
 'Frei nach den Gebrüdern Grimm: Die Geschichte vom mutigen »Gestiefelten Kater« und dem Müllerssohn, der mit seiner Hilfe zum Prinzen wird. Denn zu zweit kann man alles schaffen!'
)
;


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

( 6, 2, 2,  6), 
( 7, 2, 2,  7), 
( 8, 2, 2,  8), 
( 9, 2, 2,  9), 
(10, 2, 2, 10), 
(11, 2, 2, 11), 
(12, 2, 2, 12), 

(13, 3, 3, 13), 
(14, 2, 3, 14), 
(15, 2, 3, 15), 
(16, 2, 3, 16), 
(17, 2, 3, 17), 
(18, 2, 3, 18), 
(19, 2, 3, 19), 
(20, 2, 3, 20), 
(21, 3, 3, 21), 

(22, 3, 4, 22), 
(23, 3, 4, 23), 
(24, 3, 4, 24), 
(25, 3, 4, 25), 
(26, 3, 4, 26)
; 

INSERT INTO veranstaltung(v_id, vk_id, st_id, beginn)
VALUES
( 1, 1, 1, TIMESTAMP '2023-01-24 16:00'),
( 2, 1, 1, TIMESTAMP '2023-01-25 15:00'),
( 3, 2, 3, TIMESTAMP '2023-01-25 19:30'),
( 4, 1, 1, TIMESTAMP '2023-01-26 15:00'),
( 5, 2, 3, TIMESTAMP '2023-01-26 18:00'),
( 6, 2, 3, TIMESTAMP '2023-01-29 19:30'),
( 7, 1, 2, TIMESTAMP '2023-02-01 15:00'),
( 8, 1, 2, TIMESTAMP '2023-02-02 15:00'),
( 9, 1, 2, TIMESTAMP '2023-02-08 15:00'),
(10, 3, 3, TIMESTAMP '2023-05-05 19:30'),
(11, 3, 4, TIMESTAMP '2023-05-05 10:00')
;

-- Tageskassenreservierungsnummern
INSERT INTO reservierung(r_id, v_id, passcode)
SELECT -v_id, v_id, TO_CHAR(beginn, 'YYYY-MM-DD HH24:MI')
FROM   veranstaltung;

-- Reservierungsnummern
INSERT INTO reservierung(r_id, v_id, passcode)
VALUES
(1,  1, 'a'),
(2,  2, 'abc')
;

INSERT INTO reservierung(r_id, v_id)
VALUES
(3,  2),
(4,  3),
(5,  3),
(6,  4),
(7,  4),
(8, 10),
(9, 11)
;

-- Sitzplatzreservierungen
INSERT INTO sitzplatzreservierung(r_id, s_id)
VALUES
(1, 1),
(1, 2),
(1, 3),
(1, 4),
(1, 5),
(2, 1),
(2, 2),
(2, 3),
(3, 6),
(3, 7),
(4, 1),
(4, 2),
(4, 3),
(4, 6),
(5, 7),
(5, 8),
(5, 9),
(6, 1),
(7, 2),
(7, 3),
(7, 4),
(7, 5),
(8, 4),
(9, 6)
;

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

SELECT * 
FROM   reservierung;
/*
-- https://pgxn.org/dist/pgzint/
SELECT 'data:image/png;base64,' || encode(bc_qrcode(passcode), 'base64')
FROM   reservierung;

SELECT 'data:image/png;base64,' || encode(bc_generate(passcode, 58, NULL, 2, 0, NULL, NULL, NULL, NULL, NULL, NULL, 5, NULL), 'base64')
FROM   reservierung;


-- https://github.com/AbdulYadi/pgqr
SELECT 'data:image/png;base64,' || encode(pgqr(passcode, 0, 0, 4), 'base64')
FROM   reservierung;


-- https://base64.guru/converter/decode/image
-- https://www.site24x7.com/tools/datauri-to-image.html
*/