
-- 
-- Show flag for ShopArticleAttributeItem entries
-- 
ALTER TABLE `ShopArticleAttributeItem` ADD COLUMN `ShopArticleAttributeItem_Show` ENUM('true','false') NOT NULL DEFAULT 'true' AFTER `ShopArticleAttributeItem_Order`;

-- 
-- ShopUser Address update
-- 
CREATE TABLE `ShopUserAddress` (
	`ShopUserAddress_ID` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
	`Person_ID` INT(10) UNSIGNED NOT NULL,
	`Country_Code` CHAR(2) NOT NULL,
	`ShopUserAddress_Sex` ENUM('male','female','unknown') NOT NULL DEFAULT 'unknown',
	`ShopUserAddress_Title` VARCHAR(40) NULL DEFAULT NULL,
	`ShopUserAddress_Firstname` VARCHAR(255) NULL DEFAULT NULL,
	`ShopUserAddress_Lastname` VARCHAR(255) NULL DEFAULT NULL,
	`ShopUserAddress_AdditionalName` VARCHAR(255) NULL DEFAULT NULL,
	`ShopUserAddress_IsCompany` ENUM('true','false') NOT NULL DEFAULT 'false',
	`ShopUserAddress_Company` VARCHAR(255) NULL DEFAULT NULL,
	`ShopUserAddress_UID` VARCHAR(255) NULL DEFAULT NULL,
	`ShopUserAddress_Street` VARCHAR(255) NOT NULL,
	`ShopUserAddress_StreetNumber` VARCHAR(40) NOT NULL,
	`ShopUserAddress_ZipCode` VARCHAR(10) NOT NULL,
	`ShopUserAddress_City` VARCHAR(255) NOT NULL,
	`ShopUserAddress_State` VARCHAR(255) NULL DEFAULT NULL,
	`ShopUserAddress_Phone` VARCHAR(255) NULL DEFAULT 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 (`ShopUserAddress_ID`),
	INDEX `ShopUser_ShopUserAddress_1` (`Person_ID`),
	INDEX `Country_ShopUserAddress_2` (`Country_Code`),
	INDEX `IDX_Person_Person_ID_Edit` (`Person_ID_Edit`),
	INDEX `IDX_Person_Person_ID_Create` (`Person_ID_Create`),
	CONSTRAINT `Country_ShopUserAddress_2` FOREIGN KEY (`Country_Code`) REFERENCES `Country` (`Country_Code`),
	CONSTRAINT `ShopUserAddress_ibfk_1` FOREIGN KEY (`Person_ID_Edit`) REFERENCES `Person` (`Person_ID`) ON UPDATE NO ACTION ON DELETE NO ACTION,
	CONSTRAINT `ShopUserAddress_ibfk_2` FOREIGN KEY (`Person_ID_Create`) REFERENCES `Person` (`Person_ID`) ON UPDATE NO ACTION ON DELETE NO ACTION,
	CONSTRAINT `ShopUser_ShopUserAddress_1` FOREIGN KEY (`Person_ID`) REFERENCES `ShopUser` (`Person_ID`) ON DELETE CASCADE
) COLLATE='utf8_general_ci' ENGINE=InnoDB;

ALTER TABLE `ShopUser` ADD COLUMN `ShopUserAddress_ID_Invoice` INTEGER UNSIGNED AFTER `Person_ID`;
ALTER TABLE `ShopUser` ADD CONSTRAINT `ShopUserAddress_ShopUser_12` 
    FOREIGN KEY (`ShopUserAddress_ID_Invoice`) REFERENCES `ShopUserAddress` (`ShopUserAddress_ID`);

ALTER TABLE `ShopUser` ADD COLUMN `ShopUserAddress_ID_Shipping` INTEGER UNSIGNED AFTER `ShopUserAddress_ID_Invoice`;
ALTER TABLE `ShopUser` ADD CONSTRAINT `ShopUserAddress_ShopUser_13` 
    FOREIGN KEY (`ShopUserAddress_ID_Shipping`) REFERENCES `ShopUserAddress` (`ShopUserAddress_ID`);

-- 
-- ShopCustomer Address Update
-- 
ALTER TABLE `ShopCustomer` ADD COLUMN `ShopCustomer_StreetNumber` VARCHAR(40) NOT NULL AFTER `ShopCustomer_Street`;
ALTER TABLE `ShopCustomer` ADD COLUMN `ShopCustomer_DeliveryStreetNumber` VARCHAR(40) AFTER `ShopCustomer_DeliveryStreet`;

-- 
-- ShopWAWIStock update
-- 	
CREATE TABLE `ShopWAWIStock` (
	`ShopWAWIStock_ID` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
	`ShopWAWIStock_Code` VARCHAR(40) NULL DEFAULT NULL,
	`ShopWAWIStock_Name` VARCHAR(255) 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 (`ShopWAWIStock_ID`),
	UNIQUE INDEX `IDX_ShopWAWIStock_1` (`ShopWAWIStock_Code`),
	INDEX `IDX_Person_Person_ID_Edit` (`Person_ID_Edit`),
	INDEX `IDX_Person_Person_ID_Create` (`Person_ID_Create`),
	CONSTRAINT `ShopWAWIStock_ibfk_1` FOREIGN KEY (`Person_ID_Edit`) REFERENCES `Person` (`Person_ID`) ON UPDATE NO ACTION ON DELETE NO ACTION,
	CONSTRAINT `ShopWAWIStock_ibfk_2` FOREIGN KEY (`Person_ID_Create`) REFERENCES `Person` (`Person_ID`) ON UPDATE NO ACTION ON DELETE NO ACTION
) COLLATE='utf8_general_ci' ENGINE=InnoDB;

CREATE TABLE `ShopWAWIProduct_ShopWAWIStock` (
	`ShopWAWIProduct_ShopWAWIStock_ID` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
	`ShopWAWIProduct_ID` INT(11) NOT NULL,
	`ShopWAWIStock_ID` INT(10) UNSIGNED NOT NULL,
	`ShopWAWIProduct_ShopWAWIStock_Stock` INT(11) NOT NULL DEFAULT '0',
	`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 (`ShopWAWIProduct_ShopWAWIStock_ID`),
	INDEX `ShopWAWIProduct_ShopWAWIProduct_ShopWAWIStock_1` (`ShopWAWIProduct_ID`),
	INDEX `ShopWAWIStock_ShopWAWIProduct_ShopWAWIStock_2` (`ShopWAWIStock_ID`),
	INDEX `IDX_Person_Person_ID_Edit` (`Person_ID_Edit`),
	INDEX `IDX_Person_Person_ID_Create` (`Person_ID_Create`),
	CONSTRAINT `ShopWAWIProduct_ShopWAWIProduct_ShopWAWIStock_1` FOREIGN KEY (`ShopWAWIProduct_ID`) REFERENCES `ShopWAWIProduct` (`ShopWAWIProduct_ID`),
	CONSTRAINT `ShopWAWIProduct_ShopWAWIStock_ibfk_1` FOREIGN KEY (`Person_ID_Edit`) REFERENCES `Person` (`Person_ID`) ON UPDATE NO ACTION ON DELETE NO ACTION,
	CONSTRAINT `ShopWAWIProduct_ShopWAWIStock_ibfk_2` FOREIGN KEY (`Person_ID_Create`) REFERENCES `Person` (`Person_ID`) ON UPDATE NO ACTION ON DELETE NO ACTION,
	CONSTRAINT `ShopWAWIStock_ShopWAWIProduct_ShopWAWIStock_2` FOREIGN KEY (`ShopWAWIStock_ID`) REFERENCES `ShopWAWIStock` (`ShopWAWIStock_ID`)
) COLLATE='utf8_general_ci' ENGINE=InnoDB;

-- 
-- ShopUser updates (fields moved to table ShopUserAddress)
-- 
ALTER TABLE `ShopUser` DROP INDEX `IDX_ShopUser_4`, DROP FOREIGN KEY `Country_ShopUser_2`;
ALTER TABLE `ShopUser` DROP COLUMN `Country_Code`;
ALTER TABLE `ShopUser` DROP INDEX `IDX_ShopUser_13`, DROP FOREIGN KEY `Country_ShopUser_11`;
ALTER TABLE `ShopUser` DROP COLUMN `Country_Code_Invoice`;
ALTER TABLE `ShopUser` DROP COLUMN `ShopUser_AdditionalName`;
ALTER TABLE `ShopUser` DROP COLUMN `ShopUser_Street`;
ALTER TABLE `ShopUser` DROP COLUMN `ShopUser_ZIPCode`;
ALTER TABLE `ShopUser` DROP COLUMN `ShopUser_City`;
ALTER TABLE `ShopUser` DROP COLUMN `ShopUser_District`;
ALTER TABLE `ShopUser` DROP COLUMN `ShopUser_DeliveryCompany`;
ALTER TABLE `ShopUser` DROP COLUMN `ShopUser_DeliverySex`;
ALTER TABLE `ShopUser` DROP COLUMN `ShopUser_DeliveryTitle`;
ALTER TABLE `ShopUser` DROP COLUMN `ShopUser_DeliveryFirstname`;
ALTER TABLE `ShopUser` DROP COLUMN `ShopUser_DeliveryLastname`;
ALTER TABLE `ShopUser` DROP COLUMN `ShopUser_DeliveryStreet`;
ALTER TABLE `ShopUser` DROP COLUMN `ShopUser_DeliveryZIPCode`;
ALTER TABLE `ShopUser` DROP COLUMN `ShopUser_DeliveryCity`;
ALTER TABLE `ShopUser` DROP COLUMN `ShopUser_DeliveryDistrict`;
ALTER TABLE `ShopUser` DROP COLUMN `ShopUser_UID`;

ALTER TABLE `ShopUser` DROP COLUMN `ShopUser_AddressNumber`;
ALTER TABLE `ShopUser` DROP COLUMN `ShopUser_Hash`;
ALTER TABLE `ShopCustomer` DROP COLUMN `ShopCustomer_AddressNumber`;
ALTER TABLE `ShopCustomer` DROP COLUMN `ShopCustomer_Hash`;

ALTER TABLE `ShopCustomer` DROP COLUMN `ShopCustomer_District`;
ALTER TABLE `ShopCustomer` DROP COLUMN `ShopCustomer_DeliveryDistrict`;

-- 
-- Added external ID fields für user and address
-- 
ALTER TABLE `ShopUser` ADD COLUMN `ShopUser_ExternalID` VARCHAR(255) COMMENT 'Externe Referenz' AFTER `ShopUser_Imported`;
ALTER TABLE `ShopUserAddress` ADD COLUMN `ShopUserAddress_ExternalID` VARCHAR(255) COMMENT 'Externe Referenz' AFTER `ShopUserAddress_Phone`;

-- 
-- Category config for products/page
-- 
ALTER TABLE `ShopCategory` ADD COLUMN `ShopCategory_PerPageCount` INTEGER UNSIGNED AFTER `ShopCategory_QualitycheckContent`;

-- 
-- Linked Categories
-- 
ALTER TABLE `ShopCategory` ADD COLUMN `ShopCategory_ID_Linked` INTEGER(11) COMMENT 'Querverweis zu anderer Kategorie' AFTER `ShopArticleType_ID`;
CREATE INDEX `IDX_ShopCategory_18` ON `ShopCategory` (`ShopCategory_ID_Linked`);
ALTER TABLE `ShopCategory` ADD CONSTRAINT `ShopCategory_ShopCategory_8` FOREIGN KEY (`ShopCategory_ID_Linked`) REFERENCES `ShopCategory` (`ShopCategory_ID`);

-- 
-- Redesign for ShopWAWISupplier_ShopWAWIProduct => ShopWAWISupplierProduct (with own primary key)
-- 
CREATE TABLE `ShopWAWISupplierProduct` (
	`ShopWAWISupplierProduct_ID` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
	`ShopWAWISupplier_ID` INT(11) NOT NULL,
	`ShopWAWIProduct_ID` INT(11) NULL DEFAULT NULL,
	`Currency_Code` VARCHAR(16) NULL DEFAULT NULL,
	`ShopWAWISupplierProduct_ArticleNumber` VARCHAR(255) NOT NULL,
	`ShopWAWISupplierProduct_EAN` VARCHAR(255) NULL DEFAULT NULL,
	`ShopWAWISupplierProduct_Price` DECIMAL(10,2) NOT NULL,
	`ShopWAWISupplierProduct_MSRP` DECIMAL(10,2),
	`ShopWAWISupplierProduct_Stock` INTEGER UNSIGNED,
	`ShopWAWISupplierProduct_InStock` ENUM('true','false') NOT NULL DEFAULT 'false',
	`ShopWAWISupplierProduct_Data` TEXT,
	`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 (`ShopWAWISupplierProduct_ID`),
	UNIQUE INDEX `IDX_ShopWAWISupplierProduct_UniqueArtnr` (`ShopWAWISupplier_ID`, `ShopWAWISupplierProduct_ArticleNumber`),
	INDEX `IDX_ShopWAWISupplierProduct_1` (`ShopWAWISupplier_ID`),
	INDEX `IDX_ShopWAWISupplierProduct_2` (`ShopWAWIProduct_ID`),
	INDEX `IDX_ShopWAWISupplierProduct_3` (`ShopWAWISupplier_ID`, `ShopWAWIProduct_ID`),
	INDEX `Currency_ShopWAWISupplierProduct_3` (`Currency_Code`),
	INDEX `IDX_Person_Person_ID_Edit` (`Person_ID_Edit`),
	INDEX `IDX_Person_Person_ID_Create` (`Person_ID_Create`),
	CONSTRAINT `Currency_ShopWAWISupplierProduct_3` FOREIGN KEY (`Currency_Code`) REFERENCES `Currency` (`Currency_Code`),
	CONSTRAINT `ShopWAWIProduct_ShopWAWISupplierProduct_1` FOREIGN KEY (`ShopWAWIProduct_ID`) REFERENCES `ShopWAWIProduct` (`ShopWAWIProduct_ID`) ON DELETE SET NULL,
	CONSTRAINT `ShopWAWISupplierProduct_ibfk_1` FOREIGN KEY (`Person_ID_Edit`) REFERENCES `Person` (`Person_ID`) ON UPDATE NO ACTION ON DELETE NO ACTION,
	CONSTRAINT `ShopWAWISupplierProduct_ibfk_2` FOREIGN KEY (`Person_ID_Create`) REFERENCES `Person` (`Person_ID`) ON UPDATE NO ACTION ON DELETE NO ACTION,
	CONSTRAINT `ShopWAWISupplier_ShopWAWISupplierProduct_2` FOREIGN KEY (`ShopWAWISupplier_ID`) REFERENCES `ShopWAWISupplier` (`ShopWAWISupplier_ID`) ON DELETE CASCADE
) COLLATE='utf8_general_ci' ENGINE=InnoDB;

-- 
-- CMS Update - Template selection
-- 
ALTER TABLE `ArticleEntry` ADD COLUMN `ArticleEntry_Template` VARCHAR(255) AFTER `ArticleEntry_Rating_Count`;

-- 
-- Add hash ShopWAWISupplierProduct - can be used to detect external changes
-- 
ALTER TABLE `ShopWAWISupplierProduct` ADD COLUMN `ShopWAWISupplierProduct_Hash` VARCHAR(255) AFTER `ShopWAWISupplierProduct_Data`;
ALTER TABLE `ShopWAWISupplierProduct` ADD COLUMN `ShopWAWISupplierProduct_Active` ENUM('true','false') NOT NULL DEFAULT 'false' AFTER `ShopWAWISupplierProduct_Hash`;
ALTER TABLE `ShopWAWISupplierProduct` ADD COLUMN `ShopWAWISupplierProduct_Name` VARCHAR(255) AFTER `ShopWAWISupplierProduct_EAN`;
	
-- removed field ShopPrice_ValidCountStart
ALTER TABLE `ShopPrice` DROP COLUMN `ShopPrice_ValidCountStart`;

-- added sql report auth
INSERT INTO `Authorization` (`Authorization_Code`, `Authorization_Name`) VALUES ('shop_report_sql', 'Shop SQL Auswertungen');

-- added product barcode fields (in addition to EAN)
ALTER TABLE `ShopWAWIProduct` ADD COLUMN `ShopWAWIProduct_Barcode` VARCHAR(255) AFTER `ShopWAWIProduct_EAN`;
ALTER TABLE `ShopWAWISupplierProduct` ADD COLUMN `ShopWAWISupplierProduct_Barcode` VARCHAR(255) AFTER `ShopWAWISupplierProduct_EAN`;
UPDATE `ShopWAWIProduct` SET `ShopWAWIProduct_Barcode` = `ShopWAWIProduct_EAN` WHERE TRIM(IFNULL(`ShopWAWIProduct_Barcode`, '')) = '' AND TRIM(IFNULL(`ShopWAWIProduct_EAN`, '')) != '';

-- theme support
INSERT INTO `ShopConfig` (`ShopConfig_Code`, `ShopConfig_Name`, `ShopConfig_Order`, `ShopConfig_Critical`) VALUES ('view_theme', 'Theme', 89, 'true');

-- 
-- Drop-Shipping Update
-- 
ALTER TABLE `ShopWAWISupplier` ADD COLUMN `ShopWAWISupplier_Dropshipping` ENUM('true','false') NOT NULL DEFAULT 'false' AFTER `ShopWAWISupplier_Info`;
ALTER TABLE `ShopOrder` ADD COLUMN `ShopWAWISupplier_ID` INTEGER COMMENT 'Assignment to Drop-Shipper (Lieferant)' AFTER `ShopDeliveryService_Code`;
ALTER TABLE `ShopOrder` ADD CONSTRAINT `ShopWAWISupplier_ShopOrder_10` FOREIGN KEY (`ShopWAWISupplier_ID`) REFERENCES `ShopWAWISupplier` (`ShopWAWISupplier_ID`);
ALTER TABLE `ShopAccountJournal` ADD COLUMN `ShopWAWISupplier_ID` INTEGER COMMENT 'Assignment to Drop-Shipper (Lieferant)' AFTER `ShopAccountJournalGroup_ID`;
ALTER TABLE `ShopAccountJournal` ADD CONSTRAINT `ShopWAWISupplier_ShopAccountJournal_8` FOREIGN KEY (`ShopWAWISupplier_ID`) REFERENCES `ShopWAWISupplier` (`ShopWAWISupplier_ID`);

-- 
-- Multi-Stock UPDATE
-- 
ALTER TABLE `ShopWAWIStock` ADD COLUMN `ShopWAWIStock_Active` ENUM('true','false') DEFAULT 'true' AFTER `ShopWAWIStock_Name`;
ALTER TABLE `ShopOrder` ADD COLUMN `ShopWAWIStock_ID` INTEGER UNSIGNED COMMENT 'Assignment to Stock' AFTER `ShopWAWISupplier_ID`;
ALTER TABLE `ShopOrder` ADD CONSTRAINT `ShopWAWIStock_ShopOrder_11` FOREIGN KEY (`ShopWAWIStock_ID`) REFERENCES `ShopWAWIStock` (`ShopWAWIStock_ID`);
ALTER TABLE `ShopAccountJournal` ADD COLUMN `ShopWAWIStock_ID` INTEGER UNSIGNED COMMENT 'Assignment to Stock' AFTER `ShopWAWISupplier_ID`;
ALTER TABLE `ShopAccountJournal` ADD CONSTRAINT `ShopWAWIStock_ShopAccountJournal_9` FOREIGN KEY (`ShopWAWIStock_ID`) REFERENCES `ShopWAWIStock` (`ShopWAWIStock_ID`);
-- add default stock if missing
INSERT IGNORE INTO `ShopWAWIStock` (`ShopWAWIStock_ID`, `ShopWAWIStock_Code`, `ShopWAWIStock_Name`, `ShopWAWIStock_Active`, `Person_ID_Edit`, `Table_Edit`, `Person_ID_Create`, `Table_Create`) VALUES
(1, 'default', 'Hauptlager', 'true', 1, '2015-05-04 11:41:29', 1, '2015-05-04 11:41:29');

--
-- Theme is not required
--
UPDATE `ShopConfig` SET `ShopConfig_Required`='false' WHERE `ShopConfig_Code`='view_theme';

-- 
-- Vertreter Erweiterung (mit Backend-Zugang: Worker)
-- 
CREATE TABLE `ShopUserManager` (
	`Person_ID` INT(10) UNSIGNED NOT NULL,
	`ShopUserManager_Code` VARCHAR(40) NULL DEFAULT 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 (`Person_ID`),
	UNIQUE INDEX `IDX_ShopUserManager_Code_Unique` (`ShopUserManager_Code`),
	INDEX `IDX_Person_Person_ID_Edit` (`Person_ID_Edit`),
	INDEX `IDX_Person_Person_ID_Create` (`Person_ID_Create`),
	CONSTRAINT `ShopUserManager_ibfk_1` FOREIGN KEY (`Person_ID_Edit`) REFERENCES `Person` (`Person_ID`) ON UPDATE NO ACTION ON DELETE NO ACTION,
	CONSTRAINT `ShopUserManager_ibfk_2` FOREIGN KEY (`Person_ID_Create`) REFERENCES `Person` (`Person_ID`) ON UPDATE NO ACTION ON DELETE NO ACTION,
	CONSTRAINT `Worker_ShopUserManager_1` FOREIGN KEY (`Person_ID`) REFERENCES `Worker` (`Person_ID`) ON UPDATE CASCADE ON DELETE CASCADE
) COLLATE='utf8_general_ci' ENGINE=InnoDB;

INSERT INTO `Authorization` (`Authorization_Code`, `Authorization_Name`) VALUES ('shop_user_management', 'Vertreter Modul');

-- 
-- Update for Worker Username unique index (removed useless password)
-- 
ALTER TABLE `Worker`
	DROP INDEX `UsernamePasswordUnique`,
	ADD UNIQUE INDEX `IDX_Worker_Username_Unique` (`Worker_Username`);

-- 
-- Order handling cronjog
-- 
INSERT INTO `Queue` (`QueueType_Code`, `QueueState_Code`, `Queue_Title`, `Queue_Action`, `Queue_Priority`, `Queue_Lock`, `Queue_Active`, `Queue_Date`, `Person_ID_Edit`, `Table_Edit`, `Person_ID_Create`, `Table_Create`) VALUES
('task', 'idle', 'Verarbeitung von Bestellungen', 'Shop_Queue_Orderhandler', 20, 'false', 'true', '2015-01-01 00:00:00', 1, '2015-05-20 13:50:44', 1, '2015-05-20 13:50:44');

-- 
-- Shop Config Updates
-- 
UPDATE `ShopConfig` SET `ShopConfig_Required`='false' WHERE `ShopConfig_Code` IN('payment_cod_bank_accountnumber', 'payment_prepayed_accountnumber', 'payment_cod_bank_number', 'payment_prepayed_bank_number');
DELETE FROM `ShopConfig` WHERE `ShopConfig_Code`='cms_prefix';
DELETE FROM `ShopConfig` WHERE  `ShopConfig_Code`='prefix';


-- 
-- Minimum stock (price condition)
-- 
ALTER TABLE `ShopPrice` ADD COLUMN `ShopPrice_MinStock` INTEGER UNSIGNED AFTER `ShopPrice_ValidCount`;
ALTER TABLE `ShopPriceCache` ADD COLUMN `ShopPriceCache_MinStock` INTEGER UNSIGNED NOT NULL DEFAULT 0 AFTER `ShopPriceCache_IsMinPrice`;


-- 
-- Added ShopOrder hash field (more security with external payment provieders)
-- 
ALTER TABLE `ShopOrder` ADD COLUMN `ShopOrder_Hash` VARCHAR(255) AFTER `ShopOrder_TrackedExternal`;

-- 
-- Added new Table: ShopUserGroup
-- 
CREATE TABLE `ShopUserGroup` (
	`ShopUserGroup_ID` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
	`ShopUserGroup_Code` VARCHAR(40) NULL DEFAULT NULL,
	`ShopUserGroup_Name` VARCHAR(255) 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 (`ShopUserGroup_ID`),
	UNIQUE INDEX `IDX_ShopUserGroup_Code_Unique` (`ShopUserGroup_Code`),
	INDEX `IDX_ShopUserGroup_Name` (`ShopUserGroup_Name`),
	INDEX `IDX_Person_Person_ID_Edit` (`Person_ID_Edit`),
	INDEX `IDX_Person_Person_ID_Create` (`Person_ID_Create`),
	CONSTRAINT `ShopUserGroup_ibfk_1` FOREIGN KEY (`Person_ID_Edit`) REFERENCES `Person` (`Person_ID`) ON UPDATE NO ACTION ON DELETE NO ACTION,
	CONSTRAINT `ShopUserGroup_ibfk_2` FOREIGN KEY (`Person_ID_Create`) REFERENCES `Person` (`Person_ID`) ON UPDATE NO ACTION ON DELETE NO ACTION
) COLLATE='utf8_general_ci' ENGINE=InnoDB;

ALTER TABLE `ShopUser` ADD COLUMN `ShopUserGroup_ID` INTEGER UNSIGNED AFTER `Person_ID`;
ALTER TABLE `ShopUser` ADD CONSTRAINT `ShopUserGroup_ShopUser_12` 
    FOREIGN KEY (`ShopUserGroup_ID`) REFERENCES `ShopUserGroup` (`ShopUserGroup_ID`);
    
INSERT INTO `Authorization` (`Authorization_Code`, `Authorization_Name`) VALUES ('shop_user_group', 'Kundengruppen');
INSERT IGNORE INTO `PersonGroup_Authorization` (`Authorization_Code`, `PersonGroup_ID`, `Person_ID_Edit`, `Table_Edit`, `Person_ID_Create`, `Table_Create`) VALUES ('shop_user_group', 1, 1, '2015-09-10 13:02:23', 1, '2015-09-10 13:02:23');

-- 
-- Added new Table: ShopUserRelationship
-- 
CREATE TABLE `ShopUserRelationshipType` (
	`ShopUserRelationshipType_Code` VARCHAR(40) NOT NULL,
	`ShopUserRelationshipType_Name` VARCHAR(255) NOT NULL,
	`ShopUserRelationshipType_Order` INT(10) UNSIGNED NOT NULL DEFAULT '0',
	`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 (`ShopUserRelationshipType_Code`),
	INDEX `IDX_ShopUserRelationshipType_Order` (`ShopUserRelationshipType_Order`),
	INDEX `IDX_Person_Person_ID_Edit` (`Person_ID_Edit`),
	INDEX `IDX_Person_Person_ID_Create` (`Person_ID_Create`),
	CONSTRAINT `ShopUserRelationshipType_ibfk_1` FOREIGN KEY (`Person_ID_Edit`) REFERENCES `Person` (`Person_ID`) ON UPDATE NO ACTION ON DELETE NO ACTION,
	CONSTRAINT `ShopUserRelationshipType_ibfk_2` FOREIGN KEY (`Person_ID_Create`) REFERENCES `Person` (`Person_ID`) ON UPDATE NO ACTION ON DELETE NO ACTION
) COLLATE='utf8_general_ci' ENGINE=InnoDB;

CREATE TABLE `ShopUserRelationship` (
	`ShopUserRelationship_ID` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
	`ShopUserRelationshipType_Code` VARCHAR(40) NOT NULL,
	`Person_ID` INT(10) UNSIGNED NOT NULL,
	`Person_ID_Parent` INT(10) UNSIGNED NOT NULL,
	`ShopUserRelationship_Order` INT(10) UNSIGNED NOT NULL DEFAULT '0',
	`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 (`ShopUserRelationship_ID`),
	UNIQUE INDEX `IDX_ShopUserRelationship_Unique` (`Person_ID_Parent`, `Person_ID`, `ShopUserRelationshipType_Code`),
	INDEX `IDX_ShopUserRelationship_Order` (`Person_ID_Parent`, `ShopUserRelationshipType_Code`, `ShopUserRelationship_Order`),
	INDEX `IDX_Person_Person_ID_Edit` (`Person_ID_Edit`),
	INDEX `IDX_Person_Person_ID_Create` (`Person_ID_Create`),
	INDEX `IDX_ShopUserRelationship_Person_ID` (`Person_ID`),
	INDEX `IDX_ShopUserRelationship_Person_ID_Parent` (`Person_ID_Parent`),
	INDEX `IDX_ShopUserRelationship_ShopUserRelationshipType_Code` (`ShopUserRelationshipType_Code`),
	CONSTRAINT `ShopUserRelationshipType_ShopUserRelationship_1` FOREIGN KEY (`ShopUserRelationshipType_Code`) REFERENCES `ShopUserRelationshipType` (`ShopUserRelationshipType_Code`),
	CONSTRAINT `ShopUserRelationship_ibfk_1` FOREIGN KEY (`Person_ID_Edit`) REFERENCES `Person` (`Person_ID`) ON UPDATE NO ACTION ON DELETE NO ACTION,
	CONSTRAINT `ShopUserRelationship_ibfk_2` FOREIGN KEY (`Person_ID_Create`) REFERENCES `Person` (`Person_ID`) ON UPDATE NO ACTION ON DELETE NO ACTION,
	CONSTRAINT `ShopUser_ShopUserRelationship_2` FOREIGN KEY (`Person_ID_Parent`) REFERENCES `ShopUser` (`Person_ID`),
	CONSTRAINT `ShopUser_ShopUserRelationship_3` FOREIGN KEY (`Person_ID`) REFERENCES `ShopUser` (`Person_ID`)
) COLLATE='utf8_general_ci' ENGINE=InnoDB;

INSERT INTO `ShopUserRelationshipType` (`ShopUserRelationshipType_Code`, `ShopUserRelationshipType_Name`, `ShopUserRelationshipType_Order`) VALUES ('default', 'Default', 1);


