create

--Create sequences and type
CREATE SEQUENCE seq_produkttt;
CREATE SEQUENCE seq_kaffeekapseltyp INCREMENT BY 3 MINVALUE 7000 NO CYCLE;
CREATE TYPE KaffeeAroma AS ENUM ('Mild', 'Normal', 'Stark');

CREATE TABLE Kaffee(
        name VARCHAR(100) PRIMARY KEY,
        aroma VARCHAR(100) NOT NULL,
        saeure VARCHAR(100) NOT NULL,
        koerper VARCHAR(100) NOT NULL,
        referenzkapsel INTEGER NOT NULL

);

CREATE TABLE Lungo(
        name VARCHAR(100) NOT NULL,
        FOREIGN KEY(name) REFERENCES Kaffee(name),
        PRIMARY KEY(name)
);

CREATE TABLE Ristretto(
        name VARCHAR(100) NOT NULL,
        roetemp INTEGER,
        FOREIGN KEY(name) REFERENCES Kaffee(name),
        PRIMARY KEY(name)
);

CREATE TABLE Espresso(
        name VARCHAR(100) NOT NULL,
        druck INTEGER NOT NULL CHECK (druck >= 7),
        FOREIGN KEY(name) REFERENCES Kaffee(name),
        PRIMARY KEY(name)
);

CREATE TABLE Produkt(
        modelnr INTEGER DEFAULT nextval('seq_produkt'),
        preis NUMERIC(5,2) NOT NULL,
        produziertVon VARCHAR(100) NOT NULL REFERENCES Hersteller(marke),
        PRIMARY KEY (modelnr)
);

CREATE TABLE Hersteller(
        marke VARCHAR(100) PRIMARY KEY,
        stl INTEGER
);

CREATE TABLE Kaffeemaschine(
        modelnr INTEGER PRIMARY KEY REFERENCES Produkt(modelnr),
        name VARCHAR(100) NOT NULL,
        druck NUMERIC(6, 2) NOT NULL,
        anzkaps INTEGER NOT NULL CHECK (anzkaps BETWEEN 1 AND 20),

);

ALTER TABLE Hersteller ADD CONSTRAINT fk_hauptprodukt
    FOREIGN KEY(stl) REFERENCES Produkt(modelnr)
     DEFERRABLE INITIALLY DEFERRED;

CREATE TABLE Kaffeekapseltyp(
        ktid INTEGER DEFAULT nextval('seq_kaffeekapseltyp'),
        groesse INTEGER,
        volumen REAL NOT NULL,
        PRIMARY KEY (ktid, groesse)
);

CREATE TABLE BasiertAuf(
        alt INTEGER REFERENCES Kaffeemaschine(modelnr),
        neu INTEGER REFERENCES Kaffeemaschine(modelnr),
        PRIMARY KEY(alt, neu)
);

CREATE TABLE Unterstuezt(
        maschine INTEGER REFERENCES Kaffeemaschine(modelnr),
        typID INTEGER,
        typGroesse INTEGER,
        FOREIGN KEY (typID, typGroesse) REFERENCES Kaffeekapseltyp(ktid, groesse),
        PRIMARY KEY (maschine, typID, typGroesse)
);

CREATE TABLE KannZubereiten(
        maschine INTEGER REFERENCES Kaffeemaschine(modelnr),
        kaffee VARCHAR(100) REFERENCES Kaffee(name),
        PRIMARY KEY (maschine, kaffee)
);

CREATE TABLE KompatibelMit(
        vonID INTEGER,
        vonGroesse INTEGER,
        zuID INTEGER,
        zuGroesse INTEGER,
        FOREIGN KEY (vonID, vonGroesse) REFERENCES Kaffeekapseltyp (ktid, groesse),
        FOREIGN KEY (zuID, zuGroesse) REFERENCES Kaffeekapseltyp (ktid, groesse),
        PRIMARY KEY (vonID, vonGroesse, zuID, zuGroesse)
);

CREATE TABLE Kaffeekapsel(
        modelnr INTEGER PRIMARY KEY REFERENCES Produkt (modelnr),
        material VARCHAR(100) NOT NULL,
        typID INTEGER NOT NULL,
        typGroesse INTEGER NOT NULL,
        enthaelt VARCHAR(100) NOT NULL REFERENCES Kaffee (name),
        FOREIGN KEY (typID, typGroesse) REFERENCES Kaffeekapseltyp (ktid, groesse)
);

CREATE TABLE Review(
        hersteller VARCHAR(100) REFERENCES Hersteller (marke),
        id INTEGER,
        txt TEXT NOT NULL,
        PRIMARY KEY (hersteller, id)
);

CREATE TABLE Lizenz(
        maschine INTEGER REFERENCES Kaffeemaschine (modelnr),
        typID INTEGER,
        typGroesse INTEGER,
        hersteller VARCHAR(100) NOT NULL REFERENCES Hersteller (marke),
        gebuehr REAL NOT NULL,
        FOREIGN KEY (typID, typGroesse) REFERENCES Kaffeekapseltyp (ktid, groesse),
        PRIMARY KEY (maschine, typID, typGroesse, hersteller)
);

CREATE TABLE Haendler(
        name VARCHAR(100) PRIMARY KEY,
        webseite TEXT NOT NULL
);

CREATE TABLE Verkauft(
        haendler VARCHAR(100) REFERENCES Haendler (name),
        produkt INTEGER REFERENCES Produkt (modelnr),
        PRIMARY KEY (haendler, produkt)
);

CREATE TABLE Mengenrabatt(
        angebotenVon VARCHAR(100) REFERENCES Haendler (name),
        label BYTEA NOT NULL,
        rabatt REAL,
        PRIMARY KEY (angebotenVon, label, rabatt)
);

CREATE TABLE BestehtAus(
        rabattVon VARCHAR(100),
        rabattLabel BYTEA NOT NULL,
        rabattRabatt REAL NOT NULL,
        produkt INTEGER REFERENCES Produkt (modelnr),
        menge VARCHAR(100),
        FOREIGN KEY (rabattVon, rabattLabel, rabattRabatt) REFERENCES Mengenrabatt (angebotenVon, label, rabatt),
        PRIMARY KEY (rabattVon, rabattLabel, rabattRabatt, produkt)
);

CREATE TABLE GutscheinCode(
        rabattVon VARCHAR(100) NOT NULL,
        rabattLabel BYTEA NOT NULL,
        rabattRabatt REAL,
        code VARCHAR(100) NOT NULL CHECK (char_length(code) > 9),
        FOREIGN KEY (rabattVon, rabattLabel, rabattRabatt) REFERENCES Mengenrabatt (angebotenVon, label, rabatt),
        PRIMARY KEY (rabattVon, rabattLabel, rabattRabatt, code)
);