DROP DATABASE DBWT;
CREATE DATABASE DBWT;

USE DBWT;

DROP TABLE IF EXISTS DBWT.Benutzer;
CREATE TABLE Benutzer(
    `Nummer` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `Nutzername` VARCHAR(30) NOT NULL UNIQUE,
    `Salt` VARCHAR(32) NOT NULL,
    `Hash` VARCHAR(24) NOT NULL,
    `Anlegedatum` DATE  NOT NULL DEFAULT CURRENT_DATE,
    `Aktiv` BOOL DEFAULT FALSE NOT NULL,
    `Vorname` VARCHAR(30) NOT NULL,
    `Nachname` VARCHAR(30) NOT NULL,
    `Email` VARCHAR(40) NOT NULL UNIQUE,
    `Geburtsdatum` DATE DEFAULT CURRENT_DATE,
    `Alter` INT DEFAULT ((DATEDIFF(CURRENT_DATE,`Geburtsdatum`))/365),
    `Letzter Login` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY(Nummer)

);

DROP TABLE IF EXISTS DBWT.Gaeste;
CREATE TABLE Gaeste(
    `Nummer` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `Grund` VARCHAR(255) NOT NULL DEFAULT('Keine Angabe'),
    `Ablaufdatum` DATE DEFAULT  (ADDDATE(CURRENT_DATE, INTERVAL 7 DAY)) NOT NULL,

    PRIMARY KEY(Nummer),
    FOREIGN KEY (Nummer) REFERENCES Benutzer(Nummer) ON DELETE CASCADE

);

DROP TABLE IF EXISTS DBWT.FhAngehoerige;
CREATE TABLE FhAngehoerige(
    `Nummer` INT UNSIGNED PRIMARY KEY NOT NULL AUTO_INCREMENT,
    FOREIGN KEY (Nummer) REFERENCES Benutzer(Nummer) ON DELETE CASCADE
);


DROP TABLE IF EXISTS DBWT.Studenten;
CREATE TABLE Studenten(
    `Nummer` INT UNSIGNED  PRIMARY KEY NOT NULL AUTO_INCREMENT UNIQUE,
    `Matrikelnummer` INT UNIQUE,
    `Studiengang` ENUM('ET','INF','ISE','MCD','WI'),
    CONSTRAINT CheckMaNumber  CHECK (1000000000 > `Matrikelnummer` > 9999999),
   FOREIGN KEY(Nummer) REFERENCES FhAngehoerige(Nummer) ON DELETE CASCADE
);

DROP TABLE IF EXISTS DBWT.Mitarbeiter;
CREATE TABLE Mitarbeiter(
    `Nummer` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `Büro` VARCHAR(20),
    `Telefon` VARCHAR(20),
    PRIMARY KEY(Nummer),
    FOREIGN KEY(Nummer) REFERENCES FhAngehoerige(Nummer) ON DELETE CASCADE
);

DROP TABLE IF EXISTS DBWT.Fachbereiche;
CREATE TABLE Fachbereiche(
    `ID` INT UNSIGNED PRIMARY KEY NOT NULL AUTO_INCREMENT,
    `Website` VARCHAR(100) DEFAULT('https://www.fh-aachen.de/fachbereiche/') NOT NULL,
    `Name` VARCHAR(40) NOT NULL
);

DROP TABLE IF EXISTS DBWT.Bilder;
CREATE TABLE Bilder(
    `ID` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `Alt-Text` VARCHAR(50) NOT NULL,
    `Titel` VARCHAR(50),
    `Binärdaten` BLOB NOT NULL

);

DROP TABLE IF EXISTS DBWT.Kategorien;
CREATE TABLE Kategorien(
    `ID` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `Bezeichnung` VARCHAR(255) NOT NULL,
    `bilder_ID` INT UNSIGNED NOT NULL,
    `unterkat_ID` INT UNSIGNED NOT NULL,
    FOREIGN KEY (ID) REFERENCES Bilder(ID),
    FOREIGN KEY (unterkat_ID) REFERENCES Kategorien(ID)
);

DROP TABLE IF EXISTS DBWT.Mahlzeiten;
CREATE TABLE Mahlzeiten(
    `ID` INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
    `Beschreibung` VARCHAR(255) NOT NULL,
    `Vorrat` INT UNSIGNED NOT NULL DEFAULT 0,
    `kategorie_id` INT UNSIGNED NOT NULL,
    `Verfügbar` BOOL NOT NULL DEFAULT (Vorrat > 0),
    FOREIGN KEY (kategorie_id) REFERENCES Kategorien(ID)
);

DROP TABLE IF EXISTS DBWT.Kommentare;
CREATE TABLE Kommentare(
    `ID` INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
    `Bemerkung` VARCHAR(150),
    `Bewertung` VARCHAR(150) NOT NULL DEFAULT('Keine Angabe'),
    `benutzer_nummer` INT UNSIGNED NOT NULL UNIQUE,
    `mahlzeiten_id` INT UNSIGNED NOT NULL UNIQUE,
    FOREIGN KEY (benutzer_nummer) REFERENCES Studenten(Nummer),
    FOREIGN KEY (mahlzeiten_id) REFERENCES Mahlzeiten(ID)
);

DROP TABLE IF EXISTS DBWT.Bestellungen;
CREATE TABLE Bestellungen(
    `Nummer` INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
    `Bestellzeitpunkt` TIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `Abholzeitpunkt` TIME DEFAULT Bestellzeitpunkt,
    `benutzer_nummer` INT UNSIGNED NOT NULL,
    CONSTRAINT zeit CHECK(`Bestellzeitpunkt` <= `Abholzeitpunkt`),
    FOREIGN KEY (benutzer_nummer) REFERENCES Benutzer(Nummer)
);

DROP TABLE IF EXISTS DBWT.Deklaration;
CREATE TABLE Deklaration(
    `Zeichen` VARCHAR(2) NOT NULL PRIMARY KEY,
    `Beschriftung` VARCHAR(32) NOT NULL
);

DROP TABLE IF EXISTS DBWT.Zutaten;
CREATE TABLE Zutaten(
    `ID` INT UNSIGNED NOT NULL PRIMARY KEY,
    `Name` VARCHAR(50) NOT NULL,
    `Bio` BOOL NOT NULL,
    `Vegetarisch` BOOL NOT NULL,
    `Vegan` BOOL NOT NULL,
    `Glutenfrei` BOOL NOT NULL,
    CONSTRAINT id_zahl CHECK(`ID` BETWEEN 00000 AND 99999)
);

DROP TABLE IF EXISTS DBWT.Preise;
CREATE TABLE Preise(
    `ID` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `Jahr` INT UNSIGNED NOT NULL,
    `Gastpreis` INT UNSIGNED NOT NULL,
    `Studentpreis` INT UNSIGNED,
    `MA-Preis` INT UNSIGNED,
    CONSTRAINT max_preis CHECK(Gastpreis <= 99.99),
    CONSTRAINT studi_preis CHECK(Studentpreis < `MA-Preis`),
    FOREIGN KEY (ID) REFERENCES Mahlzeiten(ID) ON DELETE CASCADE
);

DROP TABLE IF EXISTS DBWT.befreundet;
CREATE TABLE befreundet(
    `benutzer1_id` INT UNSIGNED NOT NULL,
    `benutzer2_id` INT UNSIGNED NOT NULL,
    CONSTRAINT PK_befreundet PRIMARY KEY (benutzer1_id,benutzer2_id)
);

DROP TABLE IF EXISTS DBWT.gehört_zu;
CREATE TABLE gehört_zu(
    `benutzer_id` INT UNSIGNED NOT NULL,
    `fach_id` INT UNSIGNED NOT NULL,
    FOREIGN KEY (`benutzer_id`) REFERENCES FhAngehoerige(`Nummer`),
    FOREIGN KEY (`fach_id`) REFERENCES Fachbereiche(`ID`),
    CONSTRAINT PK_gehört_zu PRIMARY KEY (`benutzer_id`,`fach_id`)
);

DROP TABLE IF EXISTS DBWT.enthält;
CREATE TABLE enthält(
    `bestellung_id` INT UNSIGNED NOT NULL,
    `mahlzeit_id` INT UNSIGNED NOT NULL,
    `Anzahl` INT UNSIGNED NOT NULL,
    CONSTRAINT PK_enthält PRIMARY KEY (bestellung_id,mahlzeit_id)
);

DROP TABLE IF EXISTS DBWT.braucht;
CREATE TABLE braucht(
    `mahlzeiten_id` INT UNSIGNED NOT NULL,
    `zeichen` VARCHAR(2) NOT NULL,
    CONSTRAINT PK_braucht PRIMARY KEY (mahlzeiten_id,zeichen)
);

DROP TABLE IF EXISTS DBWT.enthält_zutaten;
CREATE TABLE enthält_zutaten(
    `mahlzeiten_id` INT UNSIGNED NOT NULL,
    `zutaten_id` INT UNSIGNED NOT NULL,
    CONSTRAINT PK_enthält PRIMARY KEY (mahlzeiten_id,zutaten_id)
);

DROP TABLE IF EXISTS DBWT.hat;
CREATE TABLE hat(
    `mahlzeiten_id` INT UNSIGNED NOT NULL,
    `bilder_id` INT UNSIGNED NOT NULL,
    CONSTRAINT PK_hat PRIMARY KEY (mahlzeiten_id,bilder_id)
);

INSERT INTO Benutzer(Nutzername, Salt, `Hash`, Vorname, Nachname, Email, Geburtsdatum)
VALUES('gregor96','d','wse','Georgios','Nikas','nikasgeorgios7@gmail.com','1996-12-30');
INSERT INTO Benutzer(Nutzername, Salt, `Hash`, Vorname, Nachname, Email, Geburtsdatum)
VALUES('nico','d','wse','Nico','Gartmann','nico.g@gmail.com','1997-09-08');
INSERT INTO FhAngehoerige(Nummer) VALUES(2);
INSERT INTO Mitarbeiter(Nummer, Telefon) VALUES(2,'02461347233');

INSERT INTO Benutzer(Nutzername, Salt, `Hash`, Vorname, Nachname, Email, Geburtsdatum)
VALUES('pascal','d','wse','Pascal','Truglio','pt@gmail.com','1996-11-05');
INSERT INTO FhAngehoerige(Nummer) VALUES(3);
INSERT INTO Mitarbeiter(Nummer, Telefon) VALUES(3, '024613299992');

DELETE FROM Benutzer WHERE `Nummer`=2;