#basic patches for the ecommerce system

CREATE TABLE `eCommerce_contactInfomation` (
  `contactID` int(16) NOT NULL auto_increment,
  `firstname` varchar(255) default NULL,
  `lastname` varchar(255) default NULL,
  `addressline1` varchar(255) default NULL,
  `addressline2` varchar(255) default NULL,
  `country` varchar(20) default NULL,
  `province` varchar(20) default NULL,
  `city` varchar(20) default NULL,
  `code` varchar(20) default NULL,
  `phone` varchar(20) default NULL,
  `email` varchar(255) NOT NULL,
  PRIMARY KEY  (`contactID`)
) ;

CREATE TABLE `eCommerce_contactInfomation_Shopper` (
  `id` int(13) NOT NULL auto_increment,
  `contactID` varchar(13) NOT NULL default '',
  `shopperID` int(13) NOT NULL,
  `type` varchar(20) NOT NULL,
  `active` int(1) NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `contactID` (`contactID`),
  KEY `shopperID` (`shopperID`)
) ;

ALTER TABLE eCommerce_contactInfomation_Shopper
       ADD  ( CONSTRAINT FK_entity_eCommerce_contactInfomation_Shopper FOREIGN KEY (shopperID)
                             REFERENCES entity(id));


ALTER TABLE `quote_item` ADD `invoiceID` int(11) UNSIGNED NOT NULL DEFAULT '0' ;
ALTER TABLE `quote_item` ADD `orderID` int(11) UNSIGNED NOT NULL DEFAULT '0' ;
ALTER TABLE `quote_item` ADD `lastUpdate` timestamp ;
ALTER TABLE `quote_item` ADD `status` int(5) DEFAULT NULL ;
ALTER TABLE `quote_item` ADD `trackingNum` VARCHAR( 255 ) NOT NULL ;

CREATE TABLE `order` (
     orderID           INT(16)   NOT NULL auto_increment,
	 shipToContactID   int(16)   NOT NULL,
     billToContactID   int(16)   NOT NULL,
     dateOrdered       DATETIME  NOT NULL,
     dateFilfilled     DATETIME  NOT NULL,
     shopperID         INT(16)   NULL,
     status            INT(2)    NULL,
     tax               FLOAT     NULL,
     subtotalBeforeTax FLOAT     NULL,
    
     lastUpdate        TIMESTAMP NULL,
	 ref varchar(32)  NULL,
	 company int(11) NULL,
	 quoteID int(11) NULL,
	 invoiceID int(11) NULL,
     PRIMARY KEY (orderID),
	KEY `FK_eCommerce_ContactInformation_order` (`shipToContactID`),
  	KEY `FK_eCommerce_ContactInformation_order_2` (`billToContactID`)
);

ALTER TABLE `order` 		 ADD `trackingNum` VARCHAR( 255 ) NOT NULL ;

#create order status table
CREATE TABLE `eCommerce_orderStatus` (
  `id` int(11) default NULL,
  `code` int(2) default NULL,
  `name` varchar(30) default NULL,
  UNIQUE KEY `code` (`code`)
) ;

INSERT INTO `eCommerce_orderStatus` (`id`,`code`,`name`) VALUES ('1','0','Awaiting Payment');
INSERT INTO `eCommerce_orderStatus` (`id`,`code`,`name`) VALUES ('2','1','Pending');
INSERT INTO `eCommerce_orderStatus` (`id`,`code`,`name`) VALUES ('3','2','Processing');
INSERT INTO `eCommerce_orderStatus` (`id`,`code`,`name`) VALUES ('4','3','Shipped');
INSERT INTO `eCommerce_orderStatus` (`id`,`code`,`name`) VALUES ('5','4','Cancelled');
INSERT INTO `eCommerce_orderStatus` (`id`,`code`,`name`) VALUES ('6','5','On Hold');
INSERT INTO `eCommerce_orderStatus` (`id`,`code`,`name`) VALUES ('7','6','Denied');

#changes for the product groups
CREATE TABLE `productGroupsData` (
  `description` TEXT NULL,
  `imageID` int(32) NOT NULL,
  `thumbID` int(32) NOT NULL,
  `productGroupsID` int(13) default NULL,
  UNIQUE KEY `productGroupsID` (`productGroupsID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;


CREATE TABLE `directoryGroupsData` (
  `description` TEXT NULL,
  `imageID` int(32) NOT NULL,
  `thumbID` int(32) NOT NULL,
  `directoryGroupsID` int(13) default NULL,
  UNIQUE KEY `directoryGroupsID` (`directoryGroupsID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;


CREATE TABLE `groupsData` (
  `description` TEXT NULL,
  `imageID` int(32) NOT NULL,
  `thumbID` int(32) NOT NULL,
  `groupsID` int(13) default NULL,
  UNIQUE KEY `groupsID` (`groupsID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--Some things that seem to have been skipped by mistake - may need to be run on some of the databases still
INSERT INTO `property` (`column`,`name`,`datatype`,`description`,`locked`,`hidden`,`isunique`,`seq`,`canView`,`canEdit`,`entityQuoteLink`,`searchResults`) VALUES ('productdescription','Product Description','Paragraph','','1','0','0','9999','1','1','0','0');
INSERT INTO `property_set` (`setID`,`property`,`propertyID`) VALUES ((SELECT `id` FROM `sets` WHERE `name` = 'Product'),'productdescription',(SELECT `propertyID` FROM `property` WHERE `column` = 'productdescription'));