-- -----------------------------------------------------
-- Schema toprast
-- -----------------------------------------------------
DROP TABLE IF EXISTS "allerg_prod" ;
DROP TABLE IF EXISTS "allergene" ;
DROP TABLE IF EXISTS "rechn_prod" ;
DROP TABLE IF EXISTS "produkte" ;
DROP TABLE IF EXISTS "rechnungen" ;
DROP TABLE IF EXISTS "filialen" ;
DROP TABLE IF EXISTS "kunden" ;
DROP TABLE IF EXISTS "mitarbeiter" ;
DROP TABLE IF EXISTS "personen" ;
DROP TABLE IF EXISTS "adressen" ;

-- -----------------------------------------------------
-- Table "adressen"
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS "adressen" (
  "addr_id" SERIAL NOT NULL,
  "strasse" VARCHAR(64) NOT NULL,
  "plz" VARCHAR(8) NOT NULL,
  "ort" VARCHAR(64) NOT NULL,
  "hausnummer" VARCHAR(10) NULL,
  PRIMARY KEY ("addr_id"))
;


-- -----------------------------------------------------
-- Table "personen"
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS "personen" (
  "pers_id" SERIAL NOT NULL,
  "vorname" VARCHAR(45) NULL,
  "nachname" VARCHAR(45) NULL,
  "geschlecht" CHAR(1) CHECK (geschlecht IN('m','w')),
  "fk_addr_id" INT NULL,
  PRIMARY KEY ("pers_id"),
  CONSTRAINT "fk_personen_adressen"
    FOREIGN KEY ("fk_addr_id")
    REFERENCES "adressen" ("addr_id")
    ON DELETE SET NULL
    ON UPDATE SET NULL)
;


-- -----------------------------------------------------
-- Table "kunden"
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS "kunden" (
  "fk_pers_id" INT NOT NULL,
  PRIMARY KEY ("fk_pers_id"),
  CONSTRAINT "fk_kunden_personen1"
    FOREIGN KEY ("fk_pers_id")
    REFERENCES "personen" ("pers_id")
    ON DELETE CASCADE
    ON UPDATE CASCADE)
;


-- -----------------------------------------------------
-- Table "filialen"
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS "filialen" (
  "f_id" SERIAL NOT NULL,
  "uid" VARCHAR(12) NULL,
  "name" VARCHAR(45) NOT NULL,
  "telefon" VARCHAR(45) NULL,
  "fax" VARCHAR(45) NULL,
  "fk_addr_id" INT NOT NULL,
  "geschlossen" BOOLEAN NOT NULL DEFAULT FALSE,
  PRIMARY KEY ("f_id"),
  CONSTRAINT "fk_filialen_adressen1"
    FOREIGN KEY ("fk_addr_id")
    REFERENCES "adressen" ("addr_id")
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
;


-- -----------------------------------------------------
-- Table "mitarbeiter"
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS "mitarbeiter" (
  "fk_pers_id" INT NOT NULL,
  "eintrittsdatum" DATE NOT NULL,
  "austrittsdatum" DATE NULL,
  PRIMARY KEY ("fk_pers_id"),
  CONSTRAINT "fk_mitarbeiter_personen1"
    FOREIGN KEY ("fk_pers_id")
    REFERENCES "personen" ("pers_id")
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
;


-- -----------------------------------------------------
-- Table "rechnungen"
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS "rechnungen" (
  "rechn_nr" SERIAL NOT NULL,
  "datum" DATE NOT NULL,
  "uhrzeit" TIME NOT NULL,
  "fk_f_id" INT NOT NULL,
  "fk_kunden_pers_id" INT NULL,
  "fk_mitarbeiter_pers_id" INT NULL,
  PRIMARY KEY ("rechn_nr"),
  CONSTRAINT "fk_rechnungen_filialen1"
    FOREIGN KEY ("fk_f_id")
    REFERENCES "filialen" ("f_id")
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT "fk_rechnungen_kunden1"
    FOREIGN KEY ("fk_kunden_pers_id")
    REFERENCES "kunden" ("fk_pers_id")
    ON DELETE SET NULL
    ON UPDATE SET NULL,
  CONSTRAINT "fk_rechnungen_mitarbeiter1"
    FOREIGN KEY ("fk_mitarbeiter_pers_id")
    REFERENCES "mitarbeiter" ("fk_pers_id")
    ON DELETE SET NULL
    ON UPDATE SET NULL)
;


-- -----------------------------------------------------
-- Table "produkte"
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS "produkte" (
  "produkt_id" SERIAL NOT NULL,
  "name" VARCHAR(64) NOT NULL,
  "preis" REAL NOT NULL,
  "mwst" REAL NOT NULL,
  "ausgelaufen" BOOLEAN NOT NULL DEFAULT FALSE,
  PRIMARY KEY ("produkt_id"))
;


-- -----------------------------------------------------
-- Table "rechn_prod"
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS "rechn_prod" (
  "id" SERIAL NOT NULL,
  "preis" REAL NOT NULL,
  "mwst" REAL NOT NULL,
  "fk_prod_id" INT NULL,
  "fk_rechn_nr" INT NOT NULL,
  PRIMARY KEY ("id"),
  CONSTRAINT "fk_rechn_prod_rechnungen1"
    FOREIGN KEY ("fk_rechn_nr")
    REFERENCES "rechnungen" ("rechn_nr")
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT "fk_rechn_prod_produkte1"
    FOREIGN KEY ("fk_prod_id")
    REFERENCES "produkte" ("produkt_id")
    ON DELETE SET NULL
    ON UPDATE SET NULL)
;


-- -----------------------------------------------------
-- Table "allergene"
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS "allergene" (
  "kuerzel" CHAR(1) NOT NULL,
  "bezeichnung" VARCHAR(64) NOT NULL,
  PRIMARY KEY ("kuerzel"))
;


-- -----------------------------------------------------
-- Table "allerg_prod"
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS "allerg_prod" (
  "fk_kuerzel" CHAR(1) NOT NULL,
  "fk_produkt_id" INT NOT NULL,
  PRIMARY KEY ("fk_kuerzel", "fk_produkt_id"),
  CONSTRAINT "fk_allerg_prod_allergene1"
    FOREIGN KEY ("fk_kuerzel")
    REFERENCES "allergene" ("kuerzel")
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT "fk_allerg_prod_produkte1"
    FOREIGN KEY ("fk_produkt_id")
    REFERENCES "produkte" ("produkt_id")
    ON DELETE CASCADE
    ON UPDATE CASCADE)
;

-- -----------------------------------------------------
-- Table "vornamen"
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS "vornamen" (
  "vn_id" serial, 
  "vorname" text NOT NULL,
  "geschlecht" CHAR(1) CHECK (geschlecht IN('m','w')),
  PRIMARY KEY ("vn_id")
);

-- -----------------------------------------------------
-- Table "nachnamen"
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS "nachnamen" (
  "nn_id" serial, 
  "nachname" text NOT NULL,
  PRIMARY KEY ("nn_id")
);

-- -------------------------------------------------------------------------------------------------------
-- Inserts
-- -------------------------------------------------------------------------------------------------------

-- insert nachnamen

insert into vornamen ("vn_id", "vorname", "geschlecht") values
(default, 'Liam', 'm'),
(default, 'Milan', 'm'),
(default, 'Elias', 'm'),
(default, 'Julian', 'm'),
(default, 'Levi', 'm'),
(default, 'Elias', 'm'),
(default, 'Henry', 'm'),
(default, 'Oskar', 'm'),
(default, 'Sven', 'm'),
(default, 'Levi', 'm'),
(default, 'Hannes', 'm'),
(default, 'Artur', 'm'),
(default, 'Noel', 'm'),
(default, 'Pascal', 'm'),
(default, 'Maurice', 'm'),
(default, 'David', 'm'),
(default, 'Jan', 'm'),
(default, 'Anton', 'm'),
(default, 'Lars', 'm'),
(default, 'Lias', 'm'),
(default, 'Jeremy', 'm'),
(default, 'Jonathan', 'm'),
(default, 'Tim', 'm'),
(default, 'Liam', 'm'),
(default, 'Tjark', 'm'),
(default, 'Carl', 'm'),
(default, 'Finn', 'm'),
(default, 'Luca', 'm'),
(default, 'Jerome', 'm'),
(default, 'Joey', 'm'),
(default, 'Felix', 'm'),
(default, 'Mika', 'm'),
(default, 'Ben', 'm'),
(default, 'Mauricio', 'm'),
(default, 'Emil', 'm'),
(default, 'Johannes', 'm'),
(default, 'Mattis', 'm'),
(default, 'Liam', 'm'),
(default, 'Fabian', 'm'),
(default, 'Jaden', 'm'),
(default, 'Vincent', 'm'),
(default, 'Nils', 'm'),
(default, 'Daniel', 'm'),
(default, 'Jonas', 'm'),
(default, 'Benjamin', 'm'),
(default, 'Jacob', 'm'),
(default, 'Michael', 'm'),
(default, 'Timo', 'm'),
(default, 'Julien', 'm'),
(default, 'Alexander', 'm'),
(default, 'Jette', 'w'),
(default, 'Fiona', 'w'),
(default, 'Lena', 'w'),
(default, 'Stella', 'w'),
(default, 'Ida', 'w'),
(default, 'Lotta', 'w'),
(default, 'Alina', 'w'),
(default, 'Julia', 'w'),
(default, 'Marie', 'w'),
(default, 'Finja', 'w'),
(default, 'Anna', 'w'),
(default, 'Amalia', 'w'),
(default, 'Lucia', 'w'),
(default, 'Chiara', 'w'),
(default, 'Merle', 'w'),
(default, 'Fabienne', 'w'),
(default, 'Jael', 'w'),
(default, 'Elin', 'w'),
(default, 'Alicia', 'w'),
(default, 'Leona', 'w'),
(default, 'Dior', 'w'),
(default, 'Dariah', 'w'),
(default, 'Johanna', 'w'),
(default, 'Angelina', 'w'),
(default, 'Joenna', 'w'),
(default, 'Laura', 'w'),
(default, 'Celina', 'w'),
(default, 'Joy', 'w'),
(default, 'Malina', 'w'),
(default, 'Charlott', 'w'),
(default, 'Paula', 'w'),
(default, 'Annika', 'w'),
(default, 'Paulina', 'w'),
(default, 'Kyara', 'w'),
(default, 'Carla', 'w'),
(default, 'Emilia', 'w'),
(default, 'Jana', 'w'),
(default, 'Emily ', 'w'),
(default, 'Mira', 'w'),
(default, 'Nele', 'w'),
(default, 'Leana', 'w'),
(default, 'Aurelia', 'w'),
(default, 'Sandra', 'w'),
(default, 'Elisa', 'w'),
(default, 'Aurelija', 'w'),
(default, 'Noemi', 'w'),
(default, 'Sophia', 'w'),
(default, 'Maxima', 'w'),
(default, 'Allegra', 'w'),
(default, 'Carolina', 'w');


-- insert vornamen

insert into nachnamen ("nn_id", "nachname") values
(default, 'Müller'),
(default, 'Schmidt'),
(default, 'Schneider'),
(default, 'Fischer'),
(default, 'Meyer'),
(default, 'Weber'),
(default, 'Wagner'),
(default, 'Becker'),
(default, 'Schulz'),
(default, 'Hoffmann'),
(default, 'Schäfer'),
(default, 'Koch'),
(default, 'Bauer'),
(default, 'Richter'),
(default, 'Klein'),
(default, 'Schröder'),
(default, 'Wolf'),
(default, 'Neumann'),
(default, 'Schwarz'),
(default, 'Zimmermann'),
(default, 'Krüger'),
(default, 'Braun'),
(default, 'Hofmann'),
(default, 'Schmitz'),
(default, 'Hartmann'),
(default, 'Lange'),
(default, 'Schmitt'),
(default, 'Werner'),
(default, 'Krause'),
(default, 'Meier'),
(default, 'Schmid'),
(default, 'Lehmann'),
(default, 'Schulze'),
(default, 'Maier'),
(default, 'Köhler'),
(default, 'Herrmann'),
(default, 'Walter'),
(default, 'Körtig'),
(default, 'Mayer'),
(default, 'Huber'),
(default, 'Kaiser'),
(default, 'Fuchs'),
(default, 'Peters'),
(default, 'Möller'),
(default, 'Scholz'),
(default, 'Lang'),
(default, 'Weiß'),
(default, 'Jung'),
(default, 'Hahn'),
(default, 'Vogel'),
(default, 'Friedrich'),
(default, 'Günther'),
(default, 'Keller'),
(default, 'Schubert'),
(default, 'Berger'),
(default, 'Frank'),
(default, 'Roth'),
(default, 'Beck'),
(default, 'Winkler'),
(default, 'Lorenz'),
(default, 'Baumann'),
(default, 'Albrecht'),
(default, 'Ludwig'),
(default, 'Franke'),
(default, 'Simon'),
(default, 'Böhm'),
(default, 'Schuster'),
(default, 'Schumacher'),
(default, 'Kraus'),
(default, 'Winter'),
(default, 'Otto'),
(default, 'Krämer'),
(default, 'Stein'),
(default, 'Vogt'),
(default, 'Martin'),
(default, 'Jäger'),
(default, 'Groß'),
(default, 'Sommer'),
(default, 'Brandt'),
(default, 'Haas'),
(default, 'Heinrich'),
(default, 'Seidel'),
(default, 'Schreiber'),
(default, 'Schulte'),
(default, 'Graf'),
(default, 'Dietrich'),
(default, 'Ziegler'),
(default, 'Engel'),
(default, 'Kühn'),
(default, 'Kuhn'),
(default, 'Pohl'),
(default, 'Horn'),
(default, 'Thomas'),
(default, 'Busch'),
(default, 'Wolff'),
(default, 'Sauer'),
(default, 'Bergmann'),
(default, 'Pfeiffer'),
(default, 'Voigt'),
(default, 'Ernst');


-- insert Produkte

INSERT INTO produkte ("produkt_id", "name", "preis", "mwst", "ausgelaufen") VALUES 
(default, 'Schnitzel', 9.50, 10, FALSE),
(default, 'Schweinsbraten', 13.50, 10, FALSE),
(default, 'Deo', 5.30, 20, FALSE),
(default, 'Shampoo', 2.50, 20, FALSE),
(default, 'Magazin', 4.50, 10, FALSE),
(default, 'Cola', 3.50, 10, FALSE),
(default, 'Kartoffeln', 2.50, 10, FALSE),
(default, 'Autobahnvignette', 89.90, 20, FALSE),
(default, 'Scheibenwischer', 12.50, 20, FALSE),
(default, 'Duftbaum', 5.00, 20, FALSE);


-- insert allergene

insert into allergene ("kuerzel", "bezeichnung") values
('A','Glutenhältige Getreide'),
('B','Krebstiere (Krustentiere bzw. Crustaceae)'),
('C','Aus Ei hergestellte Produkte'),
('D','Fisch'),
('E','Erdnüsse'),
('F','Soja'),
('G','Milch (einschließlich Laktose)'),
('H','Schalenfrüchte (Nüsse)'),
('L','Sellerie'),
('M','Senf'),
('N','Sesam'),
('O','Schwefeldioxid und Sulphit'),
('P','Lupinen'),
('R','Weichtiere (Mollusken)');


-- insert allerg_prod

insert into allerg_prod ("fk_kuerzel", "fk_produkt_id") values
('A', 3),
('A', 6),
('A', 7),
('C', 3),
('C', 5),
('C', 9),
('E', 9),
('G', 9),
('H', 9),
('C', 6),
('M', 6);


-- insert adressen 

INSERT INTO adressen ("strasse", "plz", "ort", "hausnummer") VALUES 
('Hauptstrasse', 2151, 'Asparn an der Zaya', 24),
('Breunerstrasse', 2130, 'Mistelbach', 43),
('Obere Landstrasse', 2130, 'Mistelbach', 5),
('Krauthof', 2130, 'Mistelbach', 45),
('Hauptplatz', 2130, 'Mistelbach', 75),
('Krankenhaus', 1200, 'Wien', 24),
('Kopfgasse', 1230, 'Wien', 87),
('Krautstrasse', 1010, 'Wien', 7),
('Kartoffelweg', 1010, 'Wien', 66),
('Nasenhaarstrasse', 1030, 'Wien', 57),
('Chinakohl', 1100, 'Wien', 12),
('Brandweingasse', 1020, 'Wien', 15);


-- insert filialen
INSERT INTO filialen ("uid", "name", "telefon", "fax", "geschlossen", "fk_addr_id")
	VALUES (24324, 'Hauptstrasse', 4363456, 9741781, FALSE, (SELECT addr_id FROM adressen WHERE strasse='Hauptstrasse'));
INSERT INTO filialen ("uid", "name", "telefon", "fax", "geschlossen", "fk_addr_id")
	VALUES (23413, 'Krankenhaus', 6346463, 6456456, FALSE, (SELECT addr_id FROM adressen WHERE strasse='Krankenhaus'));
INSERT INTO filialen ("uid", "name", "telefon", "fax", "geschlossen", "fk_addr_id")
	VALUES (23425, 'Kopfgasse', 7454536, 34674345, FALSE, (SELECT addr_id FROM adressen WHERE strasse='Kopfgasse'));


-- -------------------------------------------------------------------
-- Insert selects:
-- -------------------------------------------------------------------

insert into personen (vorname, nachname, geschlecht)
select v.vorname, n.nachname, v.geschlecht
from vornamen v, nachnamen n;

insert into mitarbeiter (fk_pers_id , eintrittsdatum, austrittsdatum)
select per.pers_id, 
	to_date(concat('201', round(random()*7), '-', to_char(round(random()*12), 'fm00'), '-' , to_char(round(random()*28), 'fm00')), 'YYYY-MM-DD'),
	NULLIF (to_date(concat('2017-01-2' , round(random()*0.57)), 'YYYY-MM-DD'), to_date('2017-01-20', 'YYYY-MM-DD'))
FROM personen per
where pers_id % 201 = 0;

insert into kunden (fk_pers_id)
select per.pers_id
FROM personen per
where pers_id % 3 != 0 or pers_id % 5 != 0 or pers_id % 7 != 0;

insert into rechnungen (datum, uhrzeit, fk_f_id, fk_kunden_pers_id, fk_mitarbeiter_pers_id)
select to_date(concat('201', floor((random()*7.5)), '-', to_char(round(random()*12), 'fm00'), '-' , to_char(round(random()*28), 'fm00')), 'YYYY-MM-DD'),
'23:59:59',
1, -- Dummyfiliale
kun.fk_pers_id, -- kundennummer
(SELECT mi.fk_pers_id FROM mitarbeiter mi ORDER BY random() LIMIT 1)
FROM kunden kun;

insert into  rechn_prod (preis, mwst, fk_prod_id, fk_rechn_nr)
select pr.preis * round(random()*10), pr.mwst, pr.produkt_id, r.rechn_nr
from produkte pr, rechnungen r;

-- -------------------------------------------------------------------
-- drop temporary tables
-- -------------------------------------------------------------------

DROP TABLE IF EXISTS "vornamen" ;
DROP TABLE IF EXISTS "nachnamen" ;