
-- new feature: ShopCategory / ShopArticleAttributeItem pages
ALTER TABLE `ShopArticleAttributeType` ADD COLUMN `ShopArticleAttributeType_HasCategoryPages` ENUM('true','false') NOT NULL DEFAULT 'false' AFTER `ShopArticleAttributeType_Tracking`;

CREATE TABLE `ShopCategory_ShopArticleAttributeItem` (
	`ShopCategory_ShopArticleAttributeItem_ID` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
	`ShopCategory_ID` INT(11) NOT NULL,
	`ShopArticleAttributeItem_ID` INT(11) NOT NULL,
	`ArticleEntry_ID` INT(10) UNSIGNED NOT NULL,
	`Person_ID_Edit` INT(10) UNSIGNED NULL DEFAULT NULL,
	`Table_Edit` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
	`Person_ID_Create` INT(10) UNSIGNED NULL DEFAULT NULL,
	`Table_Create` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
	PRIMARY KEY (`ShopCategory_ShopArticleAttributeItem_ID`),
	INDEX `ShopCategory_ShopCategory_ShopArticleAttributeItem_1` (`ShopCategory_ID`),
	INDEX `ShopArticleAttributeItem_ShopCategory_ShopArticleAttributeItem_2` (`ShopArticleAttributeItem_ID`),
	INDEX `ArticleEntry_ShopCategory_ShopArticleAttributeItem_3` (`ArticleEntry_ID`),
	INDEX `IDX_Person_Person_ID_Edit` (`Person_ID_Edit`),
	INDEX `IDX_Person_Person_ID_Create` (`Person_ID_Create`),
	CONSTRAINT `ArticleEntry_ShopCategory_ShopArticleAttributeItem_3` FOREIGN KEY (`ArticleEntry_ID`) REFERENCES `ArticleEntry` (`ArticleEntry_ID`),
	CONSTRAINT `ShopArticleAttributeItem_ShopCategory_ShopArticleAttributeItem_2` FOREIGN KEY (`ShopArticleAttributeItem_ID`) REFERENCES `ShopArticleAttributeItem` (`ShopArticleAttributeItem_ID`),
	CONSTRAINT `ShopCategory_ShopArticleAttributeItem_ibfk_1` FOREIGN KEY (`Person_ID_Edit`) REFERENCES `Person` (`Person_ID`) ON UPDATE NO ACTION ON DELETE NO ACTION,
	CONSTRAINT `ShopCategory_ShopArticleAttributeItem_ibfk_2` FOREIGN KEY (`Person_ID_Create`) REFERENCES `Person` (`Person_ID`) ON UPDATE NO ACTION ON DELETE NO ACTION,
	CONSTRAINT `ShopCategory_ShopCategory_ShopArticleAttributeItem_1` FOREIGN KEY (`ShopCategory_ID`) REFERENCES `ShopCategory` (`ShopCategory_ID`)
) COLLATE='utf8_general_ci' ENGINE=InnoDB;

-- new shop config variable: Startseiten Titel
INSERT INTO `ShopConfig` (`ShopConfig_Code`, `ShopConfig_Name`, `ShopConfig_Order`, `ShopConfig_Required`, `Person_ID_Edit`, `Table_Edit`, `Person_ID_Create`, `Table_Create`) VALUES
('home_title', 'Startseiten Titel', 0, 'true', 1, '2013-10-28 14:53:42', 1, '2013-10-28 14:53:42');
UPDATE ShopConfig SET ShopConfig_Order=1 WHERE ShopConfig_Code IN ('company_fax', 'company_phone', 'company_name', 'company_hotline', 'company_address');

-- extended rating information (IP and User-Agent)
ALTER TABLE `Rating` ADD COLUMN `Rating_IP` VARCHAR(40) AFTER `Rating_Text`;
ALTER TABLE `Rating` ADD COLUMN `Rating_UserAgent` VARCHAR(255) AFTER `Rating_IP`;

-- New table: ShopWAWISupplier_ShopWAWIProduct to allow more than one supplier per product
CREATE TABLE `ShopWAWISupplier_ShopWAWIProduct` (
	`ShopWAWISupplier_ID` INT(11) NOT NULL,
	`ShopWAWIProduct_ID` INT(11) NOT NULL,
	`ShopWAWISupplier_ShopWAWIProduct_ArticleNumber` VARCHAR(255) NULL DEFAULT NULL,
	`ShopWAWISupplier_ShopWAWIProduct_Einkaufspreis` DECIMAL(10,2) NOT NULL,
	`Person_ID_Edit` INT(10) UNSIGNED NULL DEFAULT NULL,
	`Table_Edit` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
	`Person_ID_Create` INT(10) UNSIGNED NULL DEFAULT NULL,
	`Table_Create` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
	PRIMARY KEY (`ShopWAWISupplier_ID`, `ShopWAWIProduct_ID`),
	INDEX `IDX_ShopWAWISupplier_ShopWAWIProduct_1` (`ShopWAWISupplier_ID`),
	INDEX `IDX_ShopWAWISupplier_ShopWAWIProduct_2` (`ShopWAWIProduct_ID`),
	INDEX `IDX_ShopWAWISupplier_ShopWAWIProduct_3` (`ShopWAWISupplier_ID`, `ShopWAWIProduct_ID`),
	INDEX `IDX_Person_Person_ID_Edit` (`Person_ID_Edit`),
	INDEX `IDX_Person_Person_ID_Create` (`Person_ID_Create`),
	CONSTRAINT `ShopWAWIProduct_ShopWAWISupplier_ShopWAWIProduct_1` FOREIGN KEY (`ShopWAWIProduct_ID`) REFERENCES `ShopWAWIProduct` (`ShopWAWIProduct_ID`) ON DELETE CASCADE,
	CONSTRAINT `ShopWAWISupplier_ShopWAWIProduct_ibfk_1` FOREIGN KEY (`Person_ID_Edit`) REFERENCES `Person` (`Person_ID`) ON UPDATE NO ACTION ON DELETE NO ACTION,
	CONSTRAINT `ShopWAWISupplier_ShopWAWIProduct_ibfk_2` FOREIGN KEY (`Person_ID_Create`) REFERENCES `Person` (`Person_ID`) ON UPDATE NO ACTION ON DELETE NO ACTION,
	CONSTRAINT `ShopWAWISupplier_ShopWAWISupplier_ShopWAWIProduct_2` FOREIGN KEY (`ShopWAWISupplier_ID`) REFERENCES `ShopWAWISupplier` (`ShopWAWISupplier_ID`) ON DELETE CASCADE
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB;

-- added amazon payments as new payment type
INSERT INTO `ShopPaymentType` (`ShopPaymentType_Code`, `ShopPaymentType_Order`, `ShopPaymentType_Name`, `ShopPaymentType_Abo`) VALUES ('amazon', 15, 'Amazon Payments', 'false');
INSERT INTO `ShopPaymentType_Language` (`ShopPaymentType_Code`, `Language_Code`, `ShopPaymentType_Language_Title`) VALUES ('amazon', 'de', 'Amazon Payments');

-- added amazon as order source
INSERT INTO `ShopOrderSource` (`ShopOrderSource_Code`, `ShopOrderSource_Name`) VALUES ('amazon', 'Amazon');

-- WAWI unit update - added plural field
ALTER TABLE `ShopWAWIProductUnit_Language` ADD COLUMN `ShopWAWIProductUnit_Language_Plural` VARCHAR(40) NULL AFTER `ShopWAWIProductUnit_Language_Name`;
ALTER TABLE `ShopWAWIProductUnit_Language` CHANGE COLUMN `ShopWAWIProductUnit_Language_Name` `ShopWAWIProductUnit_Language_Name` VARCHAR(40) NOT NULL AFTER `Language_Code`;
ALTER TABLE `ShopWAWIProductUnit_Language` ADD COLUMN `ShopWAWIProductUnit_Language_Dual` VARCHAR(40) NULL AFTER `ShopWAWIProductUnit_Language_Name`;

-- WAWI unit data updates
UPDATE `ShopWAWIProductUnit_Language` SET `ShopWAWIProductUnit_Language_Plural`=`ShopWAWIProductUnit_Language_Name` WHERE `ShopWAWIProductUnit_Language_Plural` IS NULL AND `ShopWAWIProductUnit_Code` IN ('Pkg', 'ml', 'g', 'Stk');
UPDATE `ShopWAWIProductUnit_Language` SET `ShopWAWIProductUnit_Language_Dual`=`ShopWAWIProductUnit_Language_Plural` WHERE `ShopWAWIProductUnit_Language_Dual` IS NULL AND `ShopWAWIProductUnit_Language_Plural` IS NOT NULL;

UPDATE `ShopWAWIProductUnit_Language` SET `ShopWAWIProductUnit_Language_Name`='item', `ShopWAWIProductUnit_Language_Dual`='items', `ShopWAWIProductUnit_Language_Plural`='items' WHERE `ShopWAWIProductUnit_Code`='Stk' AND `Language_Code`='en';

-- Quality criteria
ALTER TABLE `ShopCategory` ADD COLUMN `ShopCategory_QualitycheckContent` ENUM('true','false') NOT NULL DEFAULT 'false' AFTER `ShopCategory_CssClass`;

-- add ShopArticleItem search field (autofill)
ALTER TABLE `ShopArticleItem_Language` ADD COLUMN `ShopArticleItem_Language_Search` VARCHAR(255) NULL DEFAULT NULL AFTER `ShopArticleItem_Language_Title`;

-- add currency updater cronjob
INSERT IGNORE INTO `Queue` (`QueueType_Code`, `QueueState_Code`, `Queue_Priority`, `Queue_Title`, `Queue_Action`, `Queue_Lock`, `Queue_Active`, `Queue_Date`, `Person_ID_Edit`, `Table_Edit`, `Person_ID_Create`, `Table_Create`) VALUES
('task', 'idle', 10, 'Währungsupdater', 'Datenkraft_Service_Queue_Task_Currency', 'false', 'false', NULL, 1, '2014-06-18 11:43:01', 1, '2014-06-18 11:43:01');

-- add new shipping service: TNT
INSERT INTO ShopDeliveryService (`ShopDeliveryService_Code`, `ShopDeliveryService_Internal`, `ShopDeliveryService_Selectable`, `ShopDeliveryService_URL`, `ShopDeliveryService_ShowTrackingNumber`, `ShopDeliveryService_Order`, `Table_Edit`, `Table_Create`) VALUES
('tnt', 'TNT', 'false', 'http://www.tnt.com', 'true', 15, '2014-06-23 13:08:11', '2014-06-23 13:08:11');

-- index fix (wrong column order)
ALTER TABLE `ShopArticle_ShopCategory` DROP INDEX `IDX_ShopArticle_ShopCategory_Order`, ADD INDEX `IDX_ShopArticle_ShopCategory_Order` (`ShopCategory_ID`, `ShopArticle_ShopCategory_Order`);

-- added external reference and cost field to ShopAccountJournal (invoice or delivery, ...)
ALTER TABLE `ShopAccountJournal` ADD COLUMN `ShopAccountJournal_ExternalCostsExcl` DECIMAL(10,2) NULL DEFAULT NULL AFTER `ShopAccountJournal_GrandTotalIncl`;
ALTER TABLE `ShopAccountJournal` ADD COLUMN `ShopAccountJournal_ExternalRef` VARCHAR(255) NULL DEFAULT NULL AFTER `ShopAccountJournal_GrandTotalIncl`;

-- price config updates
ALTER TABLE `ShopPrice` CHANGE COLUMN `ShopPrice_Count` `ShopPrice_Count` INT(11) NOT NULL DEFAULT '1' COMMENT 'Preis gültig AB einer bestimmten Anzahl von Produkten im Warenkorb. (Ab/Staffel-Preis)' AFTER `ShopPricelist_Code`;
ALTER TABLE `ShopPrice` ADD COLUMN `ShopPrice_ValidFrom` DATE NULL DEFAULT NULL AFTER `ShopPrice_DeliveryInterval`;
ALTER TABLE `ShopPrice` ADD COLUMN `ShopPrice_ValidUntil` DATE NULL DEFAULT NULL AFTER `ShopPrice_ValidFrom`;
ALTER TABLE `ShopPrice` ADD COLUMN `ShopPrice_ValidCountStart` INTEGER COMMENT 'Startwert für die Anzahl der Produkte für die dieser Preis gilt. (ab ValidFrom Datum)' AFTER `ShopPrice_ValidUntil`;
ALTER TABLE `ShopPrice` ADD COLUMN `ShopPrice_ValidCount` INTEGER COMMENT 'Aktuelle Anzahl der Produkte für die der Preis noch gültig ist. (max. ValidCountStart)' AFTER `ShopPrice_ValidCountStart`;



