CREATE TABLE `shop_product_variant` (
  `product_variant_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `product_id` int(11) unsigned NOT NULL,
  `variant` varchar(255) NOT NULL DEFAULT '',
  `sort` int(11) unsigned NOT NULL DEFAULT '1',
  PRIMARY KEY (`product_variant_id`),
  KEY `product_id` (`product_id`),
  CONSTRAINT `shop_product_variant_ibfk_1` FOREIGN KEY (`product_id`) REFERENCES `shop_products` (`product_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ALTER TABLE `shop_prices` CHANGE `shopper_group_id` `shopper_group_id` INT(11) UNSIGNED NULL DEFAULT NULL;
ALTER TABLE `shop_prices` ADD IF NOT EXISTS `product_variant_id` INT(11) UNSIGNED NULL DEFAULT NULL;
ALTER TABLE `shop_prices` CHANGE `price` `price` DECIMAL(10,2) UNSIGNED NULL DEFAULT NULL;

ALTER TABLE `shop_prices` DROP FOREIGN KEY `fk_shop_prices_product_id`;
ALTER TABLE `shop_prices` DROP KEY `fk_shop_prices_product_id`;
ALTER TABLE `shop_prices` DROP FOREIGN KEY `fk_shop_prices_shopper_group_id`;
ALTER TABLE `shop_prices` DROP KEY `fk_shop_prices_shopper_group_id`;
ALTER TABLE `shop_prices` DROP FOREIGN KEY `shop_prices_ibfk_1`;
ALTER TABLE `shop_prices` DROP KEY `shop_prices_ibfk_1`;
ALTER TABLE `shop_prices` DROP FOREIGN KEY `shop_prices_ibfk_2`;
ALTER TABLE `shop_prices` DROP KEY `shop_prices_ibfk_2`;
ALTER TABLE `shop_prices` ADD UNIQUE KEY `product_id_variant_id_shopper_group_id` (`product_id`,`product_variant_id`,`shopper_group_id`);
ALTER TABLE `shop_prices` ADD CONSTRAINT `shop_prices_ibfk_1` FOREIGN KEY (`product_id`) REFERENCES `shop_products` (`product_id`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `shop_prices` ADD CONSTRAINT `shop_prices_ibfk_2` FOREIGN KEY (`product_variant_id`) REFERENCES `shop_product_variant` (`product_variant_id`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `shop_prices` ADD CONSTRAINT `shop_prices_ibfk_3` FOREIGN KEY (`shopper_group_id`) REFERENCES `shop_shopper_groups` (`shopper_group_id`) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE `shop_cart` ADD IF NOT EXISTS `product_variant_id` INT(11) UNSIGNED NULL DEFAULT NULL AFTER `product_id`;
ALTER TABLE `shop_cart` ADD CONSTRAINT `shop_cart_product_variant_id` FOREIGN KEY (`product_variant_id`) REFERENCES `shop_product_variant` (`product_variant_id`) ON DELETE CASCADE ON UPDATE CASCADE;

INSERT INTO `shop_config` (`site_id`, `config_label`, `config_key`, `config_value`, `input_type`, `sort`) VALUES (1, 'Cart View Filename', 'cart_view_filename', 'trebilcock', 'text', 12);

ALTER TABLE `users` ADD IF NOT EXISTS `phone` VARCHAR(25) NULL DEFAULT NULL AFTER `email`;
ALTER TABLE `users` ADD IF NOT EXISTS `shipping_address` VARCHAR(25) NULL DEFAULT NULL AFTER `phone`;
ALTER TABLE `users` ADD IF NOT EXISTS `shipping_city` VARCHAR(25) NULL DEFAULT NULL AFTER `shipping_address`;
ALTER TABLE `users` ADD IF NOT EXISTS `shipping_postalcode` VARCHAR(25) NULL DEFAULT NULL AFTER `shipping_address`;
ALTER TABLE `users` ADD IF NOT EXISTS `shipping_province` VARCHAR(25) NULL DEFAULT NULL AFTER `shipping_postalcode`;
ALTER TABLE `users` ADD IF NOT EXISTS `shipping_country` VARCHAR(25) NULL DEFAULT NULL AFTER `shipping_province`;

INSERT INTO `shop_prices` (`product_id`, `shopper_group_id`, `price`)
	SELECT `product_id`, (1) AS `shopper_group_id`, `price` FROM `shop_products`;
ALTER TABLE `shop_products` DROP COLUMN IF EXISTS `price`;
ALTER TABLE `shop_orders` ADD IF NOT EXISTS `paid` TINYINT(1) UNSIGNED NOT NULL DEFAULT 0;
UPDATE `shop_orders` SET `paid`=1;
