CREATE DATABASE JavaStop_Content;


CREATE TABLE CoffeeProduct (
Product_ID int NOT NULL  AUTO_INCREMENT, 
Product_Name varchar(150) NOT NULL, 
Description varchar(255) NOT NULL, 
Price currency NOT NULL, 
Origin varchar(150) NOT NULL, 
Roast varchar(50) NOT NULL,  
Size int NOT NULL, 
Grind varchar(25) NOT NULL, 
SpecialOffer Boolean,
PRIMARY KEY (Product_ID),
UNIQUE KEY (Name),
FOREIGN KEY (CoffeeCategory_ID) REFERENCES CoffeeCategory (CoffeeCategory_ID),
FOREIGN KEY (Image_ID) REFERENCES Images (Image_ID)
);




CREATE TABLE Customer(
Customer_ID int NOT NULL  AUTO_INCREMENT, 
FirstName varchar(30) NOT NULL,
LastName varchar(30) NOT NULL,
Email varchar(150) NOT NULL,
Phone1 number int(10),
Phone2 number int(10),
Membership_Date date DEFAULT GETDATE(),
Username varchar(150) NOT NULL,
Password varchar(20) NOT NULL,
Company varchar(150),
PRIMARY KEY (Customer_ID),
UNIQUE KEY (Customer_ID),
UNIQUE KEY (Username));




CREATE TABLE Order(
Order_ID int NOT NULL  AUTO_INCREMENT,
Order_Number int NOT NULL  AUTO_INCREMENT,
Order_Tax currency NOT NULL,
Order_Shipping currency NOT NULL,
Order_Total currency NOT NULL,
Order_Date date NOT NULL DEFAULT GETDATE(),
PRIMARY KEY (Order_ID),
FOREIGN KEY (Customer_ID) REFERENCES Customer (Customer_ID), 
FOREIGN KEY (Payment_ID) REFERENCES MyAccountPaymentInfo (Payment_ID),
FOREIGN KEY (Address_ID) REFERENCES MyAccountAddressInfo (Address_ID)
);




CREATE TABLE MerchandiseProduct(
Product_ID int NOT NULL  AUTO_INCREMENT,
Manufacturer varchar(150) NOT NULL,
Description varchar(255) NOT NULL,
Price currency NOT NULL, 
SpecialOffer Boolean,
PRIMARY KEY (Product_ID),
FOREIGN KEY (Image_ID) REFERENCES Images (Image_ID));





CREATE TABLE OrderItem(
OrderItem_ID int NOT NULL  AUTO_INCREMENT,
Quantity int NOT NULL,
OrderSubtotal int NOT NULL,
PRIMARY KEY (OrderItem_ID),
FOREIGN KEY (Order_ID) REFERENCES Order (Order_ID),
FOREIGN KEY (MerchandiseProduct_ID) REFERENCES MerchandiseProduct (Product_ID),
FOREIGN KEY (CoffeeProduct_ID) REFERENCES CoffeeProduct (Product_ID));



CREATE TABLE CoffeeCategory(
CoffeeCategory_ID int NOT NULL  AUTO_INCREMENT,
Category_Name varchar(150),
PRIMARY KEY (CoffeeCategory_ID));




CREATE TABLE InquiryType(
InquiryType_ID int NOT NULL  AUTO INCREMENT,
InquiryName varchar(150) NOT NULL,
PRIMARY KEY (InquiryType_ID));




CREATE TABLE Inquiry(
Inquiry_ID int NOT NULL AUTO INCREMENT, 
Inquiry_Detail varchar(255) NOT NULL,
Contact_First varchar(30) NOT NULL,
Contact_Last varchar(30) NOT NULL,
Email varchar(150) NOT NULL,
Phone int(10), 

PRIMARY KEY (Inquiry_ID),
FOREIGN KEY (InquiryType_ID) REFERENCES InquiryType (InquiryType_ID),
FOREIGN KEY (BusinessType_ID) REFERENCES BusinessType (BusinessType_ID));




CREATE TABLE BusinessType(
BusinessType_ID int NOT NULL AUTO_INCREMENT,
BusinessType varchar(30) NOT NULL,
PRIMARY KEY (BusinessType_ID)
);





CREATE TABLE Images(
Image_ID int NOT NULL  AUTO_INCREMENT,
Image Attachment NOT NULL,
PRIMARY KEY (Image_ID));





CREATE TABLE MyAccountPaymentInfo(
Payment_ID int NOT NULL  AUTO_INCREMENT,
CC_Number int NOT NULL, 
CC_Expiration int NOT NULL,
CC_CV_Number int NOT NULL,
PRIMARY KEY (MyAccountPaymentInfo_ID),
FOREIGN KEY (Customer_ID) REFERENCES Customer (Customer_ID);




CREATE MyAccountAddressInfo(
Address_ID int NOT NULL AUTO_INCREMENT,
Address1 varchar(255) NOT NULL,
Address2 varchar(255) NOT NULL,
City varchar(75) NOT NULL,
State varchar(2) NOT NULL,
Zip varchar(5) NOT NULL,
PRIMARY KEY (Address_ID),
FOREIGN KEY (Customer_ID) REFERENCES Customer (Customer_ID));