CREATE DATABASE IF NOT EXISTS `mistox`;
USE `mistox`;

CREATE TABLE IF NOT EXISTS `Product` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Name` varchar(45) DEFAULT NULL,
  `Description` text,
  `Cost` int(11) DEFAULT NULL,
  `URL` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) AUTO_INCREMENT=1;

CREATE TABLE IF NOT EXISTS `ProductImage` (
  `ImageID` int(11) NOT NULL AUTO_INCREMENT,
  `ProductID` int(11) NOT NULL,
  `Image` MEDIUMBLOB,
  `Name` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`ImageID`,`ProductID`)
) AUTO_INCREMENT=1;

CREATE TABLE IF NOT EXISTS `ProductInventory` (
  `AccountID` int(11) NOT NULL,
  `ProductID` int(11) NOT NULL,
  `Key` varchar(45) NOT NULL,
  `Value` text DEFAULT NULL,
  PRIMARY KEY (`AccountID`,`ProductID`,`Key`)
);

CREATE TABLE IF NOT EXISTS `Cart` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `AccountID` int(11) DEFAULT NULL,
  `ProductID` int(11) DEFAULT NULL,
  PRIMARY KEY (`ID`),
  KEY `AccountID` (`AccountID`),
  KEY `ProductID` (`ProductID`),
  CONSTRAINT `Cart_ibfk_1` FOREIGN KEY (`AccountID`) REFERENCES `Account` (`ID`),
  CONSTRAINT `Cart_ibfk_2` FOREIGN KEY (`ProductID`) REFERENCES `Product` (`ID`)
) AUTO_INCREMENT=1;

CREATE TABLE IF NOT EXISTS `Receipt` (
  `AccountID` int(11) NOT NULL,
  `ProductID` int(11) NOT NULL,
  `ReceiptID` varchar(45) NOT NULL,
  `LineItem` int(11) NOT NULL,
  `Time` datetime DEFAULT NULL,
  `TaxAmount` int(11) DEFAULT NULL,
  `TotalCost` int(11) DEFAULT NULL,
  PRIMARY KEY (`AccountID`,`ProductID`,`ReceiptID`)
);