/*
               --------------------
               |TABLE CUSTOMERS:  |
               |------------------|
     ----------|Cust_ID (PK)      |
     |         |Cust_name         |
     |         |Address           |
     |         |Postal+code       |
     |         |City              |
     |         |Country           |
     |         --------------------
     |
     |     
     |          -----------------           --------------------
     | 1:n     |TABLE ORDERS    |           |TABLE ORDERDETAIL |
     |         |----------------|    1:n    |------------------|
     |         |Order_id (PK)   |-------<<--|Order_id (PK/FK)  |
     |---<<----|Cust_id         |           |Product_id (PK/FK |---<>----
               |Order_date      |           |Quantity          |        |
               |Emp_id          |-->>--     |Discount          |        |
               ------------------     |     |                  |        |
                                      |     --------------------        |
                                 1:n  |                             1:1 |
                                      |                                 |
            ------------------        |     --------------------        |
            |TABLE EMPLOYEES |        |     |TABLE PRODUCTS    |        |
            |----------------|        |     |------------------|        |
            |Emp_id (PK)     |--------|     |Product_id        |---------
            |Name            |              |Product_name      |
            |Lastname        |              |No_In_Stock       |
            ------------------              |To_Order          |
                                            |Price             |
                                            --------------------


*/

-- script for mysql

CREATE DATABASE PROJECT;

USE PROJECT;

--MySQL:
CREATE TABLE IF NOT EXISTS Customers (
    Cust_id varchar(10) NOT NULL PRIMARY KEY,
    Cust_name VARCHAR(30) NOT NULL,
    Address VARCHAR(30) NULL,
    City VARCHAR(30) NULL,
    Country VARCHAR(30) NULL,
    Status BOOLEAN NOT NULL DEFAULT True
)  ENGINE=INNODB;

insert into Customers (Cust_id, Cust_name, Address, City, Country) values ('A001', 'KLM', 'Staallaan 320', 'Amsterdam', 'Netherlands');
insert into Customers (Cust_id, Cust_name, Address, City, Country) values ('A002', 'Krupp', 'Diborgstrasse  44', 'Berlin', 'Deutschland');
insert into Customers (Cust_id, Cust_name, Address, City, Country) values ('A003', 'Snip AG', 'Rue de Boela', 'Paris', 'France');

-- MySQL
CREATE TABLE IF NOT EXISTS Employees (
Emp_id      VARCHAR(10) NOT NULL PRIMARY KEY,
First_Name  varchar(20) NOT NULL,
Last_Name   varchar(30) NOT NULL,
Status      BOOLEAN NOT NULL DEFAULT True
)  ENGINE=INNODB;

insert into Employees (Emp_id, First_Name, Last_Name) values ('E001', 'Gert', 'de Boer');
insert into Employees (Emp_id, First_Name, Last_Name) values ('E002', 'Suzanne', 'Kooymann');
insert into Employees (Emp_id, First_Name, Last_Name) values ('E003', 'Doni', 'La Rouche');

CREATE TABLE IF NOT EXISTS Products (
Product_id      VARCHAR(20) NOT NULL PRIMARY KEY,
Product_Name    VARCHAR(30) NOT NULL,
Unit_price      decimal(7,2) NOT NULL,
No_In_Stock     INT NOT NULL,
To_Order        BOOLEAN NOT NULL DEFAULT False
)  ENGINE=INNODB;

insert into Products (Product_id, Product_Name, Unit_Price, No_In_Stock) values ('P001', 'Stalen chassis', 450.00,7);
insert into Products (Product_id, Product_Name, Unit_Price, No_In_Stock) values ('P002', 'Papier per ton', 950.00,10);
insert into Products (Product_id, Product_Name, Unit_Price, No_In_Stock) values ('P003', 'Karton per ton', 35.00,90);

CREATE TABLE IF NOT EXISTS Orders (
Order_id      VARCHAR(10) NOT NULL PRIMARY KEY,
Cust_id       varchar(10) NOT NULL,
Emp_id        VARCHAR(10) NOT NULL,
Order_date    DATE NOT NULL,
CONSTRAINT    FK_CUST FOREIGN KEY (Cust_id) REFERENCES customers(Cust_id),
CONSTRAINT    FK_EMP FOREIGN KEY (Emp_id) REFERENCES employees(Emp_id)
)  ENGINE=INNODB;

insert into orders (order_id, cust_id, emp_id, order_date) values ('O001', 'A002', 'E001', '2002-06-22');
insert into orders (order_id, cust_id, emp_id, order_date) values ('O002', 'A002', 'E002', '2003-03-13');
insert into orders (order_id, cust_id, emp_id, order_date) values ('O003', 'A001', 'E002', '2007-04-09');
insert into orders (order_id, cust_id, emp_id, order_date) values ('O004', 'A003', 'E002', '2007-05-11');

CREATE TABLE IF NOT EXISTS OrderDetail (
Order_id      VARCHAR(10) NOT NULL,
Product_id    VARCHAR(20) NOT NULL,
Quantity      INT NOT NULL,
Discount      decimal(4,2) NULL,
CONSTRAINT pk_detail PRIMARY KEY (order_id,product_id),
CONSTRAINT fk_product FOREIGN KEY (product_id) REFERENCES products(product_id),
CONSTRAINT fk_order FOREIGN KEY (order_id) REFERENCES orders(order_id)
)  ENGINE=INNODB;

insert into orderdetail (order_id, product_id, quantity, discount) values ('O007', 'P002', 3, 45.00);
insert into orderdetail (order_id, product_id, quantity, discount) values ('O001', 'P003', 2, 1.00);

-- end