#create database assignment1;
use assignment1;
#drop table IF EXISTS Customer;
#drop table IF EXISTS Supplier;
#drop table IF EXISTS Product;
#drop table IF EXISTS ShippingCompany;
#drop table IF EXISTS Address;
#drop table IF EXISTS PaymentType;
#drop table IF EXISTS InvoiceDetails;
#drop table IF EXISTS InvoiceItems;
#drop table IF EXISTS OutboundShipping;
#drop table IF EXISTS PurchaseOrderDetails;
#drop table IF EXISTS PurchaseOrderItems;
#drop table IF EXISTS InboundShipping;

create table IF NOT EXISTS Address(
Address_ID int NOT NULL AUTO_INCREMENT,
Address_Recipient_Name varchar(50) NOT NULL,
Address_Line_1 varchar(200) NOT NULL,
Address_Line_2 varchar(200),
Postal_Code char(6) NOT NULL, # no space
City varchar(15) NOT NULL,
Province char(2) NOT NULL,
IsPOBox bool DEFAULT 0,
PRIMARY KEY (Address_ID)
);
create table IF NOT EXISTS Supplier(
Supplier_ID int NOT NULL AUTO_INCREMENT,
Supplier_Co_Name varchar(40) NOT NULL,
Contact_Name varchar(50),
Supplier_Email varchar(50),
Supplier_Phone_No char(12) NOT NULL, # format: xxx-xxx-xxxx
Supplier_Address_ID int NOT NULL,
PRIMARY KEY (Supplier_ID),
FOREIGN KEY (Supplier_Address_ID) REFERENCES Address (Address_ID)
);
create table IF NOT EXISTS ShippingCompany(
Shipping_Company_ID int NOT NULL AUTO_INCREMENT,
Ship_Co_Name varchar(50) NOT NULL,
Ship_Co_Email varchar(50),
Ship_Co_Phone_No char(12), # format: xxx-xxx-xxxx
Ship_Co_Address_ID int NOT NULL,
PRIMARY KEY (Shipping_Company_ID),
FOREIGN KEY (Ship_Co_Address_ID) REFERENCES Address (Address_ID)
);
create table IF NOT EXISTS OutboundShipping(
Outbound_Shipping_ID int NOT NULL AUTO_INCREMENT,
Out_Ship_Co int NOT NULL,
Out_Ship_Address int NOT NULL,
Out_Ship_Cost decimal(4.2) DEFAULT 0.00,
PRIMARY KEY (Outbound_Shipping_ID),
FOREIGN KEY (Out_Ship_Co) REFERENCES ShippingCompany (Shipping_Company_ID),
FOREIGN KEY (Out_Ship_Address) REFERENCES Address (Address_ID)
);
create table IF NOT EXISTS InboundShipping(
Inbound_Shipping_ID int NOT NULL AUTO_INCREMENT,
In_Ship_Co INT NOT NULL,
In_Ship_Address int NOT NULL,
In_Ship_Cost decimal(4.2) DEFAULT 0.00,
PRIMARY KEY (Inbound_Shipping_ID),
FOREIGN KEY (In_Ship_Co) REFERENCES ShippingCompany (Shipping_Company_ID),
FOREIGN KEY (In_Ship_Address) REFERENCES Address (Address_ID)
);
create table IF NOT EXISTS Customer(
Customer_ID int NOT NULL AUTO_INCREMENT,
Cust_First_Name varchar(20),
Cust_Last_Name varchar(30) NOT NULL,
Cust_Email varchar(50),
Cust_Phone_No char(12) NOT NULL, # format: xxx-xxx-xxxx
PRIMARY KEY (Customer_ID)
);
create table IF NOT EXISTS Product(
Product_ID int NOT NULL AUTO_INCREMENT,
Prod_Name varchar(50) NOT NULL DEFAULT "Test",
Prod_Desc varchar(500),
Prod_Price decimal(4,2) DEFAULT 0.00,
Prod_Quant int UNSIGNED DEFAULT 0,
PRIMARY KEY (Product_ID)
);
create table IF NOT EXISTS PaymentType(
Payment_ID int NOT NULL AUTO_INCREMENT,
Card_Type varchar(20), # Ex. Debit, Visa, Mastercard, etc.
Cardholder_Name varchar(50) NOT NULL,
Card_No varchar(20) NOT NULL,
Card_Exp_Date varchar(4) NOT NULL, # format: mmyy
Card_Security varchar(4) NOT NULL, # CCV
Billing_Address_ID int NOT NULL,
PRIMARY KEY (Payment_ID),
FOREIGN KEY (Billing_Address_ID) REFERENCES Address (Address_ID)
);
create table IF NOT EXISTS InvoiceDetails(
Invoice_No int NOT NULL AUTO_INCREMENT,
Inv_Pay_ID int NOT NULL,
Inv_Cust_ID int NOT NULL,
Subtotal_Prods decimal(6,2) DEFAULT 0.00,
Inv_Total decimal(6,2) DEFAULT 0.00,
Inv_Ship_Details int NOT NULL,
Order_Status varchar(20) DEFAULT 'Pending',
PRIMARY KEY (Invoice_No),
FOREIGN KEY (Inv_Pay_ID) REFERENCES PaymentType (Payment_ID),
FOREIGN KEY (Inv_Cust_ID) REFERENCES Customer (Customer_ID),
FOREIGN KEY (Inv_Ship_Details) REFERENCES OutboundShipping (Outbound_Shipping_ID)
);
create table IF NOT EXISTS InvoiceItems(
Inv_No int NOT NULL,
Prod_ID int NOT NULL,
Prod_Quant int UNSIGNED DEFAULT 1,
PRIMARY KEY (Inv_No, Prod_ID),
FOREIGN KEY (Inv_No) REFERENCES InvoiceDetails (Invoice_No),
FOREIGN KEY (Prod_ID) REFERENCES Product (Product_ID)
);
create table IF NOT EXISTS PurchaseOrderDetails(
Purchase_Order_No int NOT NULL AUTO_INCREMENT,
PO_Pay_ID int NOT NULL,
PO_Supplier_ID int NOT NULL,
Subtotal_Prods decimal(6,2) DEFAULT 0.00,
PO_Total decimal(6,2) DEFAULT 0.00,
PO_Ship_Details int NOT NULL,
Order_Received bool DEFAULT 0,
PRIMARY KEY (Purchase_Order_NO),
FOREIGN KEY (PO_Pay_ID) REFERENCES PaymentType (Payment_ID),
FOREIGN KEY (PO_Supplier_ID) REFERENCES Supplier (Supplier_ID),
FOREIGN KEY (PO_Ship_Details) REFERENCES InboundShipping (Inbound_Shipping_ID)
);
drop table PuchaseOrderItems;
create table IF NOT EXISTS PurchaseOrderItems(
PO_No int NOT NULL,
Prod_ID int NOT NULL,
Prod_Quant int UNSIGNED DEFAULT 1,
PRIMARY KEY (PO_No, Prod_ID),
FOREIGN KEY (PO_No) REFERENCES PurchaseOrderDetails (Purchase_Order_No),
FOREIGN KEY (Prod_ID) REFERENCES Product (Product_ID)
);

#Added after while testing
alter table Product modify Prod_Price decimal(6,2) DEFAULT 0.00;
alter table OutboundShipping modify Out_Ship_Cost decimal(6,2) DEFAULT 0.00;
alter table InboundShipping modify In_Ship_Cost decimal(6,2) DEFAULT 0.00;
alter table InvoiceDetails modify Subtotal_Prods decimal(7,2) DEFAULT 0.00, modify Inv_Total decimal(7,2) DEFAULT 0.00;
alter table PurchaseOrderDetails modify Subtotal_Prods decimal(9,2) DEFAULT 0.00, modify PO_Total decimal(9,2) DEFAULT 0.00;

#Checking data
Select * from Address;
Select * from Customer;
Select * from InboundShipping;
Select * from InvoiceDetails;
Select * from InvoiceItems;
Select * from OutboundShipping;
Select * from PaymentType;
Select * from Product;
Select * from PurchaseOrderDetails;
Select * from PurchaseOrderItems;
Select * from ShippingCompany;
Select * from Supplier;