CREATE TABLE Product(ID BIGINT NOT NULL AUTO_INCREMENT,
					ProductTitle VARCHAR(75) NOT NULL,
					ProductPrice FLOAT,
					ProductSummary TINYTEXT,
					ProductDescription TEXT DEFAULT NULL,
					ProductCreatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
					ProductModifiedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
					PRIMARY KEY (ID));
CREATE TABLE Inventory(ID BIGINT NOT NULL AUTO_INCREMENT,
					Stock INT NOT NULL,
					ProductID BIGINT NOT NULL UNIQUE,
					InventoryCreatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
					InventoryModifiedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                    PRIMARY KEY (ID),
                    CHECK (Stock>=0));
CREATE TABLE Category(ID SMALLINT NOT NULL AUTO_INCREMENT,
					ParentID SMALLINT,
					CategoryTitle VARCHAR(75) NOT NULL,
					CategoryDescription TEXT DEFAULT NULL,
					CategoryCreatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
					CategoryModifiedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
					PRIMARY KEY (ID));
CREATE TABLE Product_Category(ProductID BIGINT NOT NULL,
					CategoryID SMALLINT NOT NULL);
CREATE TABLE Order_Table(ID BIGINT NOT NULL AUTO_INCREMENT,
					UserID BIGINT NOT NULL,
					OrderStatus TINYINT NOT NULL DEFAULT 0,
					Subtotal FLOAT DEFAULT 0,
					Tax FLOAT DEFAULT 0,
					GrandTotal FLOAT DEFAULT 0,
					OrderCompanyName VARCHAR(50),
					OrderFirstName VARCHAR(50),
					OrderLastName VARCHAR(50),
					OrderMobile VARCHAR(15) NOT NULL,
					OrderEmail VARCHAR(50) NOT NULL,
					OrderLine1 VARCHAR(50) NOT NULL,
					OrderLine2 VARCHAR(50),
					OrderPostalCode VARCHAR(6) NOT NULL,
					OrderCity VARCHAR(50) NOT NULL,
					OrderCountry VARCHAR(50) NOT NULL,
					OrderDescription TEXT DEFAULT NULL,
					OrderCreatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
					OrderModifiedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
					PRIMARY KEY (ID));
CREATE TABLE Order_Item(ID BIGINT NOT NULL AUTO_INCREMENT,
					ProductID BIGINT NOT NULL,
					OrderID BIGINT NOT NULL,
					OrderQuantity SMALLINT NOT NULL,
					ItemPrice FLOAT DEFAULT 0,
					ItemDescription TEXT DEFAULT NULL,
					ItemCreatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
					ItemModifiedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
					PRIMARY KEY (ID));
CREATE TABLE User(ID BIGINT NOT NULL AUTO_INCREMENT,
					UserFirstName VARCHAR(50),
					UserLastName VARCHAR(50),
					UserPhone VARCHAR(15) NOT NULL,
					UserEmail VARCHAR(50) NOT NULL,
					PassHash VARCHAR(50) NOT NULL,
					UserCompanyName VARCHAR(50),
					UserDescription TEXT DEFAULT NULL,
					UserRegisteredAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
					UserLastModified TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
					PRIMARY KEY (ID));
CREATE TABLE User_Paydetails(ID BIGINT NOT NULL AUTO_INCREMENT,
					UserID BIGINT NOT NULL,
					UserPaymentType VARCHAR(10) NOT NULL,
					UserAccount VARCHAR(17) NOT NULL UNIQUE,
					Expiry DATE,
					UserProvider VARCHAR(30) NOT NULL,
					PRIMARY KEY (ID));
CREATE TABLE User_Address(ID BIGINT NOT NULL AUTO_INCREMENT,
					UserID BIGINT NOT NULL,
					AddressLine1 VARCHAR(50) NOT NULL,
					AddressLine2 VARCHAR(50),
					AddressCity VARCHAR(50) NOT NULL,
					AddressPostalCode VARCHAR(6) NOT NULL,
					AddressCountry VARCHAR(50) NOT NULL,
					AddressLandline VARCHAR(15),
					PRIMARY KEY (ID));
CREATE TABLE Transaction(ID BIGINT NOT NULL AUTO_INCREMENT,
					OrderID BIGINT NOT NULL,
					UserID BIGINT NOT NULL,
					TranProvider VARCHAR(30) NOT NULL,
					Amount FLOAT NOT NULL,
					TranAccount VARCHAR(17),
					TranPaymentType VARCHAR(10) NOT NULL,
					TranStatus TINYINT NOT NULL DEFAULT 0,
					TranCreatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
					TranModifiedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
					PRIMARY KEY (ID));
CREATE TABLE Cart(ID BIGINT NOT NULL AUTO_INCREMENT,
					UserID BIGINT NOT NULL,
					CartSubtotal FLOAT DEFAULT 0,
					CartStatus TINYINT NOT NULL DEFAULT 0,
					CartCompanyName VARCHAR(50),
					CartFirstName VARCHAR(50),
					CartLastName VARCHAR(50),
					CartMobile VARCHAR(15),
					CartEmail VARCHAR(50),
					CartLine1 VARCHAR(50),
					CartLine2 VARCHAR(50),
					CartPostalCode VARCHAR(6),
					CartCity VARCHAR(50),
					CartCountry VARCHAR(50),
					CartCreatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
					CartModifiedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
					CartDescription TEXT DEFAULT NULL,
					PRIMARY KEY (ID));
CREATE TABLE Cart_Item(ID BIGINT NOT NULL AUTO_INCREMENT,
					ProductID BIGINT NOT NULL,
					CartID BIGINT NOT NULL,
					CartQuantity SMALLINT NOT NULL,
					CartPrice FLOAT DEFAULT 0,
					ItemDescription TEXT DEFAULT NULL,
					ItemCreatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
					ItemModifiedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
					PRIMARY KEY (ID));
	
ALTER TABLE Inventory ADD CONSTRAINT FK_InventoryProduct FOREIGN KEY (ProductID) REFERENCES Product(ID);
ALTER TABLE Category ADD CONSTRAINT FK_ParentCategory FOREIGN KEY (ParentID) REFERENCES Category(ID);
ALTER TABLE Cart ADD CONSTRAINT FK_UserCart FOREIGN KEY (UserID) REFERENCES User(ID);
ALTER TABLE Transaction ADD CONSTRAINT FK_UserTransaction FOREIGN KEY (UserID) REFERENCES User(ID);
ALTER TABLE Order_Item ADD CONSTRAINT FK_OrderOrderItem FOREIGN KEY (OrderID) REFERENCES Order_Table(ID);
ALTER TABLE Transaction ADD CONSTRAINT FK_OrderTransaction FOREIGN KEY (OrderID) REFERENCES Order_Table(ID);
ALTER TABLE Order_Table ADD CONSTRAINT FK_UserOrder FOREIGN KEY (UserID) REFERENCES User(ID);
ALTER TABLE User_Paydetails ADD CONSTRAINT FK_UserPayment FOREIGN KEY (UserID) REFERENCES User(ID);
ALTER TABLE User_Address ADD CONSTRAINT FK_UserAddress FOREIGN KEY (UserID) REFERENCES User(ID);
ALTER TABLE Cart_Item ADD CONSTRAINT FK_ProductCartItem FOREIGN KEY (ProductID) REFERENCES Product(ID);
ALTER TABLE Cart_Item ADD CONSTRAINT FK_CartCartItem FOREIGN KEY (CartID) REFERENCES Cart(ID);
ALTER TABLE Product_Category ADD CONSTRAINT FK_ProductCategory FOREIGN KEY (ProductID) REFERENCES Product(ID);
ALTER TABLE Order_Item ADD CONSTRAINT FK_ProductOrderItem FOREIGN KEY (ProductID) REFERENCES Product(ID);
ALTER TABLE Product_Category ADD CONSTRAINT FK_CategoryProduct FOREIGN KEY (CategoryID) REFERENCES Category(ID);