PRAGMA foreign_keys = ON;

DROP TABLE IF EXISTS PurchasingOrderItems;
DROP TABLE IF EXISTS CustomerOrderItems;
DROP TABLE IF EXISTS SupplierItems;
DROP TABLE IF EXISTS WarehouseItems;
DROP TABLE IF EXISTS PurchasingOrder;
DROP TABLE IF EXISTS Suppliers;
DROP TABLE IF EXISTS MadeOrders;
DROP TABLE IF EXISTS Items;
DROP TABLE IF EXISTS WarehouseLocation;
DROP TABLE IF EXISTS Customers;
DROP TABLE IF EXISTS CustomerOrders;

-- HERE CREATE TABLES DEFINITIONS -- COMPILE ENTIRE SCRIPT ON https://kripken.github.io/sql.js/GUI/

CREATE TABLE Suppliers (SuppliersCode INTEGER PRIMARY KEY,
SupplierName		TEXT,
Country			TEXT,
City 			TEXT,
Code			INTEGER,
Email			TEXT);

CREATE TABLE Items (ItemsCode INTEGER PRIMARY KEY,
Name		TEXT,
Details		TEXT,
ItemCode	FLOAT);

CREATE TABLE PurchasingOrder (PONumber INTEGER PRIMARY KEY,
SupplierCode INTEGER,
PaidDate	TEXT,
RecivedDate TEXT
);

CREATE TABLE WarehouseLocation (Location INTEGER PRIMARY KEY);

CREATE TABLE CustomerOrders (CustomerOrderNumber INTEGER PRIMARY KEY,
PaidDate	TEXT,
OutgoingDate	TEXT);

CREATE TABLE Customers(CustomerCode INTEGER PRIMARY KEY,
CustomerName		TEXT,
Country		TEXT,
City		TEXT,
Street		TEXT,
PhoneNumber	TEXT,
Email		TEXT);





CREATE TABLE SupplierItems (SupplierCode INTEGER,
ItemCode INTEGER,
ItemPrice FLOAT,
PRIMARY KEY (SupplierCode, ItemCode),
FOREIGN KEY (SupplierCode) REFERENCES Suppliers (SuppliersCode),
FOREIGN KEY (ItemCode) REFERENCES Items (ItemsCode)
);

CREATE TABLE CustomerOrderItems (CustomerOrderItemsCode INTEGER,
CustomerOrderItemsNumber	TEXT,
CustomerOrderItemsQuantity	TEXT,
PRIMARY KEY (CustomerOrderItemsCode)
FOREIGN KEY (CustomerOrderItemsCode) REFERENCES CustomerOrders(CustomerOrderNumber)
);



-- HERE CREATE TABLES DEFINITIONS

--INPUT DATA FROM SESSION 1
INSERT INTO Suppliers VALUES( 1, "Sandvik", "Norway", "Sandviken", 555666, "sandvik@gazeta.pl" );
INSERT INTO Suppliers VALUES( 2, "CocaCola", "USA", "New York", 888333, "coke@gazeta.pl" );
INSERT INTO Suppliers VALUES( 3, "KFC", "USA", "Chicago", 999222, "kfc@gazeta.pl" );

INSERT INTO Items VALUES( 1, "Chicken", "Tasty", 5.5 );
INSERT INTO Items VALUES( 2, "Leg", "Delicious", 4 );
INSERT INTO Items VALUES( 3, "Omelette", "Ordinary", 3 );
INSERT INTO Items VALUES( 4, "Pizza", "Ugly", 12.5 );
INSERT INTO Items VALUES( 5, "Fanta", "When Thirsty", 2.4 );
INSERT INTO Items VALUES( 6, "Cola", "Refreshing", 3 );
INSERT INTO Items VALUES( 7, "Screw", "Decent", 5.5 );
INSERT INTO Items VALUES( 8, "Scissors", "Indecent", 7.5 );
INSERT INTO Items VALUES( 9, "Nail", "Firm", 8.5 );
INSERT INTO Items VALUES(10, "Decanter", "Spacious", 10 );




INSERT INTO Customers VALUES ( 1, 'Mitch Ducanon', 'USA', 'Miami', 'Ocean Street', '+1 8376456', 'mitch@hotmail.com');
INSERT INTO Customers VALUES ( 2, 'Deynn', 'Poland', 'Sosnowiec', 'Na Ostatnim Groszu', '+48 398723', 'deynn@onet.pl');
INSERT INTO Customers VALUES ( 3, 'Angela Merkel', 'Germany', 'Berlin', 'Bullowstrasse', '+49 2134908', 'angie@spiegel.com');
INSERT INTO Customers VALUES ( 4, 'Patrick Bruel', 'France', 'Pari', 'Frog Street', '+33 8376456', 'bruel@hotmail.com');
INSERT INTO Customers VALUES ( 5, 'Gianni Versace', 'Italy', 'Rome', 'Macaroni Street', '+39 8376456', 'gianni@hotmail.com');


INSERT INTO CustomerOrders VALUES ( 1, '4.09.2017', '12.09.2017');
INSERT INTO CustomerOrders VALUES ( 2, '1.09.2017', '14.09.2017');
INSERT INTO CustomerOrders VALUES ( 3, '5.09.2017', '18.09.2017');
INSERT INTO CustomerOrders VALUES ( 4, NULL, NULL);
INSERT INTO CustomerOrders VALUES ( 5, '10.09.2017', '15.09.2017');
INSERT INTO CustomerOrders VALUES ( 6, '8.09.2017', NULL);
INSERT INTO CustomerOrders VALUES ( 7, '4.09.2017', '14.09.2017');
INSERT INTO CustomerOrders VALUES ( 8, '2.09.2017', '13.09.2017');


INSERT INTO WarehouseLocation VALUES( 1 );
INSERT INTO WarehouseLocation VALUES( 2 );
INSERT INTO WarehouseLocation VALUES( 3 );
INSERT INTO WarehouseLocation VALUES( 4 );
INSERT INTO WarehouseLocation VALUES( 5 );


INSERT INTO PurchasingOrder VALUES( 1, 1, NULL, "01.01.2017");
INSERT INTO PurchasingOrder VALUES( 2, 1, "02.01.2017", "02.01.2017");
INSERT INTO PurchasingOrder VALUES( 3, 1, "02.01.2017", "03.01.2017");
INSERT INTO PurchasingOrder VALUES( 4, 2, "02.01.2017", "04.01.2017");
INSERT INTO PurchasingOrder VALUES( 5, 2, "02.01.2017", "05.01.2017");
INSERT INTO PurchasingOrder VALUES( 6, 3, "02.01.2017", "06.01.2017");
INSERT INTO PurchasingOrder VALUES( 7, 3, "02.01.2017", "07.01.2017");
INSERT INTO PurchasingOrder VALUES( 8, 3, "02.01.2017", "08.01.2017");
INSERT INTO PurchasingOrder VALUES( 9, 3, "09.01.2017", NULL);



INSERT INTO SupplierItems VALUES( 1, 7, 3 );
INSERT INTO SupplierItems VALUES( 1, 8, 5 );
INSERT INTO SupplierItems VALUES( 1, 9, 6.5 );
INSERT INTO SupplierItems VALUES( 1,10, 7 );
INSERT INTO SupplierItems VALUES( 2,10, 8 );
INSERT INTO SupplierItems VALUES( 2, 5, 2 );
INSERT INTO SupplierItems VALUES( 2, 6, 2.5 );
INSERT INTO SupplierItems VALUES( 3, 7, 2 );
INSERT INTO SupplierItems VALUES( 3, 8, 4 );
INSERT INTO SupplierItems VALUES( 3, 9, 6 );
INSERT INTO SupplierItems VALUES( 3,10, 5 );
INSERT INTO SupplierItems VALUES( 3, 5, 1 );
INSERT INTO SupplierItems VALUES( 3, 6, 2 );
INSERT INTO SupplierItems VALUES( 3, 1, 3 );
INSERT INTO SupplierItems VALUES( 3, 2, 2 );
INSERT INTO SupplierItems VALUES( 3, 3, 2.2 );
INSERT INTO SupplierItems VALUES( 3, 4, 8 );


INSERT INTO CustomerOrderItems VALUES ( 1, 2,  80 );
INSERT INTO CustomerOrderItems VALUES ( 1, 10, 30 );
INSERT INTO CustomerOrderItems VALUES ( 1, 3, 50 );
INSERT INTO CustomerOrderItems VALUES ( 2, 6, 70 );
INSERT INTO CustomerOrderItems VALUES ( 2, 5, 40 );
INSERT INTO CustomerOrderItems VALUES ( 3, 8, 90 );
INSERT INTO CustomerOrderItems VALUES ( 3, 7, 120 );
INSERT INTO CustomerOrderItems VALUES ( 4, 9, 100 );
INSERT INTO CustomerOrderItems VALUES ( 4, 3, 30 );
INSERT INTO CustomerOrderItems VALUES ( 5, 2, 40 );
INSERT INTO CustomerOrderItems VALUES ( 5, 1, 35 );
INSERT INTO CustomerOrderItems VALUES ( 5, 4, 60 );
INSERT INTO CustomerOrderItems VALUES ( 6, 10, 70 );
INSERT INTO CustomerOrderItems VALUES ( 7, 1, 30 );
INSERT INTO CustomerOrderItems VALUES ( 7, 4, 65 );
INSERT INTO CustomerOrderItems VALUES ( 7, 6, 50 );
INSERT INTO CustomerOrderItems VALUES ( 7, 8, 25 );
INSERT INTO CustomerOrderItems VALUES ( 8, 2, 80 );


--DISPLAY ALL TABLES
SELECT * FROM Suppliers;
SELECT * FROM Items;
SELECT * FROM PurchasingOrder;

SELECT * FROM WarehouseLocation;

SELECT * FROM Customers;
SELECT * FROM CustomerOrders;
SELECT * FROM SupplierItems;
SELECT * FROM CustomerOrderItems;