create table beverages
(
    id       int          not null auto_increment,
    label    varchar(50)  not null,
    producer varchar(100) not null,
    PRIMARY KEY (id)
);

insert into beverages (label, producer) values ('coca cola', 'Coca Cola INC.');

insert into beverages (label, producer)  values ('Grüner Vetliner', 'Weingut Wandl');

create table softdrinks (
    id int not null,
    sugar_content int not null,
    PRIMARY KEY (id),
    constraint fk_softdrink_beverage_id
                        FOREIGN KEY (id) references beverages (id)
);

insert into softdrinks (id, sugar_content) values (1, 1);

create table e_wine_quality (
    type varchar(20) not null,
    PRIMARY KEY (type)
);

insert into e_wine_quality (type) values ('TAFELWEIN'),
                                         ('QUALITÄTSWEIN'),
                                         ('KABINETTSWEIN'),
                                         ('HECKENKLESCHER');

create table wines (
    id int not null,
    wine_quality varchar(20) not null,
    vintage date not null,
    alcohol_level int not null,
    PRIMARY KEY (id),
    constraint fk_wines_beverage_id FOREIGN KEY (id) references beverages (id),
    constraint fk_wines_quality FOREIGN KEY (wine_quality) references  e_wine_quality (type)
);

insert into wines (id, wine_quality, vintage, alcohol_level) values
(2, 'TAFELWEIN', str_to_date('10.02.2020', '%d.%m.%Y'), 14);

create table customers (
    id int not null auto_increment,
    first_name varchar(30) not null,
    last_name varchar(30) not null,
    phone_nr varchar(16) not null,
    PRIMARY KEY (id)
);

insert into customers (first_name, last_name, phone_nr) values ('Dominik', 'Hetzi', '133');

create table orders (
    id int not null auto_increment,
    order_date date not null,
    delivery_date date not null,
    amount int not null,
    PRIMARY KEY (id)
);

insert into orders (order_date, delivery_date, amount)  values (str_to_date('10.01.2018', '%d.%m.%Y'), str_to_date('10.01.2018', '%d.%m.%Y'), 20);

create table customer_orders (
    customer_id int not null,
    order_id int not null,
    beverage_id int not null,
    amount int not null,
    PRIMARY KEY (customer_id, order_id, beverage_id),
    constraint fk_customer_order_customer_id FOREIGN KEY  (customer_id) references  customers (id),
    constraint fk_customer_order_order_id FOREIGN KEY  (order_id) references  orders (id),
    constraint fk_customer_order_beverage_id FOREIGN KEY  (beverage_id) references  beverages (id)
 );

insert into customer_orders (customer_id, order_id, beverage_id, amount) values
(1, 1, 1, 20);