CREATE TABLE IF NOT EXISTS `shop_cart` (
  `cart_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `session_id` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `product_id` int(11) unsigned NOT NULL,
  `quantity` int(11) NOT NULL DEFAULT '1',
  `product_options` text COLLATE utf8_unicode_ci,
  PRIMARY KEY (`cart_id`),
  KEY `product_id` (`product_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE IF NOT EXISTS `shop_categories` (
  `category_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `parent_category_id` int(11) unsigned DEFAULT NULL,
  `datasource_category_id` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `site_id` int(11) unsigned NOT NULL DEFAULT '1',
  `category_layout_id` int(11) unsigned DEFAULT '1',
  `default_product_layout_id` int(11) unsigned NOT NULL DEFAULT '1',
  `category_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `seo_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `meta_keywords` varchar(255) COLLATE utf8_unicode_ci DEFAULT '',
  `meta_description` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `category_description` text COLLATE utf8_unicode_ci,
  `category_image_id` int(11) DEFAULT NULL,
  `sort` int(11) NOT NULL DEFAULT '1',
  `published` tinyint(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`category_id`),
  KEY `layout_id_idx` (`category_layout_id`),
  KEY `datasource_category_id` (`datasource_category_id`),
  KEY `published` (`published`),
  KEY `parent_category_id` (`parent_category_id`),
  KEY `site_id` (`site_id`),
  KEY `seo_name` (`seo_name`),
  KEY `default_product_layout_id` (`default_product_layout_id`),
  KEY `category_image_id` (`category_image_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE IF NOT EXISTS `shop_categories_products` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `category_id` int(11) unsigned NOT NULL,
  `product_id` int(11) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `product_id` (`product_id`,`category_id`),
  KEY `category_id_idx` (`category_id`),
  KEY `product_id_idx` (`product_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE IF NOT EXISTS `shop_category_layout` (
  `category_layout_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `layout_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `view_filename` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `sort_by` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'product_name ASC',
  `show_subcategories` tinyint(4) NOT NULL DEFAULT '1',
  `show_products` tinyint(1) NOT NULL DEFAULT '1',
  `show_category_image` tinyint(1) NOT NULL DEFAULT '1',
  `category_image_width` int(11) NOT NULL DEFAULT '250',
  `category_image_height` int(11) NOT NULL DEFAULT '250',
  `product_image_width` int(11) NOT NULL DEFAULT '250',
  `product_image_height` int(11) NOT NULL DEFAULT '250',
  PRIMARY KEY (`category_layout_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE IF NOT EXISTS `shop_config` (
  `config_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `site_id` int(11) unsigned NOT NULL,
  `config_label` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `config_key` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `config_value` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `input_type` enum('text','textarea','tel','email','number','checkbox','pagelist','categorylayoutlist') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'text',
  `sort` int(11) NOT NULL,
  PRIMARY KEY (`config_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE IF NOT EXISTS `shop_currency` (
  `currency_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `currency_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `currency_code` char(3) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `exchange_rate` decimal(20,15) NOT NULL,
  PRIMARY KEY (`currency_id`),
  KEY `currency_code` (`currency_code`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE IF NOT EXISTS `shop_datasources` (
  `datasource_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `site_id` int(11) unsigned NOT NULL,
  `datasource_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `controller_filename` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `default_layout_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`datasource_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE IF NOT EXISTS `shop_images` (
  `image_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `site_id` int(11) unsigned NOT NULL DEFAULT '1',
  `product_id` int(11) unsigned DEFAULT NULL,
  `filename` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  PRIMARY KEY (`image_id`),
  KEY `site_id` (`site_id`),
  KEY `product_id` (`product_id`),
  KEY `filename` (`filename`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE IF NOT EXISTS `shop_layout_specific_data` (
  `layout_specific_data_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `layout_id` int(11) unsigned NOT NULL,
  `data_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `key` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `datatype` enum('text','textarea','number','email','tel','file','url','video') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'text',
  `search_filter` tinyint(1) NOT NULL DEFAULT '0',
  `sort` int(11) NOT NULL DEFAULT '1',
  PRIMARY KEY (`layout_specific_data_id`),
  UNIQUE KEY `layout_id` (`layout_id`,`key`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE IF NOT EXISTS `shop_layout_specific_data_values` (
  `layout_specific_data_value_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `layout_specific_data_id` int(11) unsigned NOT NULL,
  `product_id` int(11) unsigned NOT NULL,
  `data_value` text COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`layout_specific_data_value_id`),
  UNIQUE KEY `layout_specific_data_id` (`layout_specific_data_id`,`product_id`),
  KEY `product_id` (`product_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE IF NOT EXISTS `shop_order_status` (
  `order_status_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `order_status_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  PRIMARY KEY (`order_status_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE IF NOT EXISTS `shop_orders` (
  `order_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `site_id` int(11) unsigned NOT NULL,
  `order_number` char(13) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `order_date` datetime NOT NULL,
  `ship_date` datetime NOT NULL,
  `user_id` int(11) unsigned DEFAULT NULL,
  `order_status_id` int(11) unsigned NOT NULL,
  `payment_gateway_id` int(11) unsigned NOT NULL,
  `shipping_gateway_id` int(11) unsigned NOT NULL,
  `currency_id` int(11) unsigned NOT NULL DEFAULT '1',
  `customer_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `customer_phone` varchar(15) COLLATE utf8_unicode_ci NOT NULL,
  `customer_email` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `customer_address` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `customer_city` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `customer_province` char(2) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `customer_postalcode` char(6) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `customer_country` char(2) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `products` text COLLATE utf8_unicode_ci NOT NULL,
  `taxes` text COLLATE utf8_unicode_ci NOT NULL,
  `shipping_total` decimal(10,2) NOT NULL DEFAULT '0.00',
  `tax_total` decimal(10,2) NOT NULL DEFAULT '0.00',
  `order_subtotal` decimal(10,2) NOT NULL DEFAULT '0.00',
  `order_total` decimal(10,2) NOT NULL DEFAULT '0.00',
  `txn_id` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `tracking_number1` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `tracking_number2` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `tracking_number3` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  PRIMARY KEY (`order_id`),
  KEY `payment_gateway_id` (`payment_gateway_id`),
  KEY `shipping_gateway_id` (`shipping_gateway_id`),
  KEY `order_status_id` (`order_status_id`),
  KEY `currency_id` (`currency_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE IF NOT EXISTS `shop_payment_gateways` (
  `payment_gateway_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `site_id` int(11) unsigned NOT NULL,
  `enabled` tinyint(4) NOT NULL DEFAULT '1',
  `gateway_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `filename` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `sort` int(11) NOT NULL,
  PRIMARY KEY (`payment_gateway_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE IF NOT EXISTS `shop_product_layout` (
  `product_layout_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `layout_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `view_filename` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `show_price` tinyint(4) NOT NULL DEFAULT '1',
  `allow_purchase` tinyint(4) NOT NULL DEFAULT '1',
  `product_image_width` int(11) NOT NULL DEFAULT '400',
  `product_image_height` int(11) NOT NULL DEFAULT '400',
  `extra_image_width` int(11) NOT NULL DEFAULT '250',
  `extra_image_height` int(11) NOT NULL DEFAULT '250',
  PRIMARY KEY (`product_layout_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE IF NOT EXISTS `shop_product_options` (
  `product_option_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `site_id` int(11) unsigned DEFAULT NULL,
  `product_id` int(11) unsigned DEFAULT NULL,
  `category_id` int(11) unsigned DEFAULT NULL,
  `include_child_categories` tinyint(1) NOT NULL DEFAULT '1',
  `option_type` enum('text','textarea','number','checkbox','radio','select') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'text',
  `option_label` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `option_description` text COLLATE utf8_unicode_ci NOT NULL,
  `option_values` text COLLATE utf8_unicode_ci NOT NULL,
  `option_required` tinyint(1) NOT NULL DEFAULT '0',
  `price_change` decimal(10,2) NOT NULL DEFAULT '0.00',
  PRIMARY KEY (`product_option_id`),
  KEY `product_id` (`product_id`),
  KEY `category_id` (`category_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE IF NOT EXISTS `shop_product_packages` (
  `product_package_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `site_id` int(11) unsigned DEFAULT NULL,
  `product_id` int(11) unsigned DEFAULT NULL,
  `category_id` int(11) unsigned DEFAULT NULL,
  `include_child_categories` tinyint(1) NOT NULL DEFAULT '1',
  `package_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `package_description` text COLLATE utf8_unicode_ci NOT NULL,
  `package_price` decimal(10,2) NOT NULL DEFAULT '0.00',
  PRIMARY KEY (`product_package_id`),
  KEY `product_id` (`product_id`),
  KEY `category_id` (`category_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE IF NOT EXISTS `shop_products` (
  `product_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `site_id` int(11) unsigned NOT NULL DEFAULT '1',
  `main_category_id` int(11) unsigned DEFAULT NULL,
  `product_layout_id` int(11) unsigned NOT NULL DEFAULT '1',
  `datasource_id` int(11) unsigned DEFAULT NULL,
  `datasource_product_id` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `datasource_hash` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `sku` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `product_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `seo_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `meta_keywords` varchar(255) COLLATE utf8_unicode_ci DEFAULT '',
  `meta_description` varchar(255) COLLATE utf8_unicode_ci DEFAULT '',
  `product_short_description` text COLLATE utf8_unicode_ci,
  `product_description` text COLLATE utf8_unicode_ci,
  `cost` decimal(10,2) NOT NULL,
  `price` decimal(10,2) NOT NULL,
  `price_lock` tinyint(1) NOT NULL DEFAULT '0',
  `deposit_price` decimal(10,2) NOT NULL DEFAULT '0.00',
  `call_for_price` tinyint(1) NOT NULL DEFAULT '0',
  `by_order_only` tinyint(1) NOT NULL DEFAULT '0',
  `shipping_length` decimal(10,2) NOT NULL DEFAULT '0.00',
  `shipping_width` decimal(10,2) NOT NULL DEFAULT '0.00',
  `shipping_height` decimal(10,2) NOT NULL DEFAULT '0.00',
  `shipping_weight` decimal(10,2) NOT NULL DEFAULT '0.00',
  `last_modified` datetime NOT NULL,
  `last_datasource_update` datetime NOT NULL,
  `product_image_id` int(11) DEFAULT NULL,
  `sort_priority` tinyint(1) NOT NULL DEFAULT '1',
  `published` tinyint(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`product_id`),
  KEY `layout_id_idx` (`product_layout_id`),
  KEY `datasource_product_id` (`datasource_product_id`),
  KEY `published` (`published`),
  KEY `site_id` (`site_id`),
  KEY `seo_name` (`seo_name`),
  KEY `main_category_id` (`main_category_id`),
  KEY `datasource_id` (`datasource_id`),
  KEY `product_image_id` (`product_image_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE IF NOT EXISTS `shop_products_related` (
  `products_related_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `product_id` int(11) unsigned NOT NULL,
  `related_product_id` int(11) unsigned NOT NULL,
  PRIMARY KEY (`products_related_id`),
  UNIQUE KEY `product_id` (`product_id`,`products_related_id`),
  KEY `related_product_id` (`related_product_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE IF NOT EXISTS `shop_shipping_gateways` (
  `shipping_gateway_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `site_id` int(11) unsigned NOT NULL,
  `enabled` tinyint(4) NOT NULL DEFAULT '1',
  `gateway_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `filename` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `sort` int(11) NOT NULL,
  PRIMARY KEY (`shipping_gateway_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE IF NOT EXISTS `shop_taxes` (
  `tax_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `site_id` int(11) unsigned NOT NULL,
  `tax_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `tax_percentage` decimal(6,5) NOT NULL,
  `tax_country` char(2) COLLATE utf8_unicode_ci NOT NULL,
  `tax_province` char(2) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`tax_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

ALTER TABLE `shop_cart` ADD CONSTRAINT `shop_cart_ibfk_1` FOREIGN KEY (`product_id`) REFERENCES `shop_products` (`product_id`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `shop_categories` ADD CONSTRAINT `shop_categories_ibfk_1` FOREIGN KEY (`category_layout_id`) REFERENCES `shop_category_layout` (`category_layout_id`) ON UPDATE CASCADE;
ALTER TABLE `shop_categories` ADD CONSTRAINT `shop_categories_ibfk_2` FOREIGN KEY (`default_product_layout_id`) REFERENCES `shop_product_layout` (`product_layout_id`) ON UPDATE CASCADE;
ALTER TABLE `shop_categories` ADD CONSTRAINT `shop_categories_ibfk_3` FOREIGN KEY (`category_image_id`) REFERENCES `shop_images` (`image_id`) ON UPDATE CASCADE;
ALTER TABLE `shop_categories` ADD CONSTRAINT `shop_categories_ibfk_4` FOREIGN KEY (`parent_category_id`) REFERENCES `shop_categories` (`category_id`) ON UPDATE CASCADE;
ALTER TABLE `shop_categories_products` ADD CONSTRAINT `shop_categories_products_ibfk_1` FOREIGN KEY (`category_id`) REFERENCES `shop_categories` (`category_id`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `shop_categories_products` ADD CONSTRAINT `shop_categories_products_ibfk_2` FOREIGN KEY (`product_id`) REFERENCES `shop_products` (`product_id`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `shop_images` ADD CONSTRAINT `shop_images_ibfk_1` FOREIGN KEY (`product_id`) REFERENCES `shop_products` (`product_id`) ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE `shop_layout_specific_data` ADD CONSTRAINT `shop_layout_specific_data_ibfk_1` FOREIGN KEY (`layout_id`) REFERENCES `shop_product_layout` (`product_layout_id`) ON UPDATE CASCADE;
ALTER TABLE `shop_layout_specific_data_values` ADD CONSTRAINT `shop_layout_specific_data_values_ibfk_1` FOREIGN KEY (`layout_specific_data_id`) REFERENCES `shop_layout_specific_data` (`layout_specific_data_id`) ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE `shop_layout_specific_data_values` ADD CONSTRAINT `shop_layout_specific_data_values_ibfk_2` FOREIGN KEY (`product_id`) REFERENCES `shop_products` (`product_id`) ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE `shop_orders` ADD CONSTRAINT `shop_orders_ibfk_1` FOREIGN KEY (`payment_gateway_id`) REFERENCES `shop_payment_gateways` (`payment_gateway_id`) ON UPDATE CASCADE;
ALTER TABLE `shop_orders` ADD CONSTRAINT `shop_orders_ibfk_2` FOREIGN KEY (`shipping_gateway_id`) REFERENCES `shop_shipping_gateways` (`shipping_gateway_id`) ON UPDATE CASCADE;
ALTER TABLE `shop_orders` ADD CONSTRAINT `shop_orders_ibfk_3` FOREIGN KEY (`order_status_id`) REFERENCES `shop_order_status` (`order_status_id`) ON UPDATE CASCADE;
ALTER TABLE `shop_orders` ADD CONSTRAINT `shop_orders_ibfk_4` FOREIGN KEY (`currency_id`) REFERENCES `shop_currency` (`currency_id`) ON UPDATE CASCADE;
ALTER TABLE `shop_product_options` ADD CONSTRAINT `shop_product_options_ibfk_1` FOREIGN KEY (`product_id`) REFERENCES `shop_products` (`product_id`) ON UPDATE CASCADE;
ALTER TABLE `shop_product_options` ADD CONSTRAINT `shop_product_options_ibfk_2` FOREIGN KEY (`category_id`) REFERENCES `shop_categories` (`category_id`) ON UPDATE CASCADE;
ALTER TABLE `shop_product_packages` ADD CONSTRAINT `shop_product_packages_ibfk_1` FOREIGN KEY (`product_id`) REFERENCES `shop_products` (`product_id`) ON UPDATE CASCADE;
ALTER TABLE `shop_product_packages` ADD CONSTRAINT `shop_product_packages_ibfk_2` FOREIGN KEY (`category_id`) REFERENCES `shop_categories` (`category_id`) ON UPDATE CASCADE;
ALTER TABLE `shop_products` ADD CONSTRAINT `shop_products_ibfk_1` FOREIGN KEY (`main_category_id`) REFERENCES `shop_categories` (`category_id`) ON UPDATE CASCADE;
ALTER TABLE `shop_products` ADD CONSTRAINT `shop_products_ibfk_2` FOREIGN KEY (`product_layout_id`) REFERENCES `shop_product_layout` (`product_layout_id`) ON UPDATE CASCADE;
ALTER TABLE `shop_products` ADD CONSTRAINT `shop_products_ibfk_3` FOREIGN KEY (`datasource_id`) REFERENCES `shop_datasources` (`datasource_id`) ON UPDATE CASCADE;
ALTER TABLE `shop_products` ADD CONSTRAINT `shop_products_ibfk_5` FOREIGN KEY (`product_image_id`) REFERENCES `shop_images` (`image_id`) ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE `shop_products_related` ADD CONSTRAINT `shop_products_related_ibfk_2` FOREIGN KEY (`related_product_id`) REFERENCES `shop_products` (`product_id`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `shop_products_related` ADD CONSTRAINT `shop_products_related_ibfk_3` FOREIGN KEY (`product_id`) REFERENCES `shop_products` (`product_id`) ON DELETE CASCADE ON UPDATE CASCADE;

REPLACE INTO `shop_category_layout` (`category_layout_id`, `layout_name`, `view_filename`, `sort_by`, `show_subcategories`, `show_products`, `show_category_image`, `category_image_width`, `category_image_height`, `product_image_width`, `product_image_height`) VALUES (1, 'Default Category', 'category_default', 'product_name ASC', 1, 1, 1, 250, 250, 250, 250);
REPLACE INTO `shop_product_layout` (`product_layout_id`, `layout_name`, `view_filename`, `show_price`, `allow_purchase`, `product_image_width`, `product_image_height`, `extra_image_width`, `extra_image_height`) VALUES (1, 'Default Layout', 'product_default', 1, 1, 400, 300, 250, 250);

REPLACE INTO `shop_currency` (`currency_id`, `currency_name`, `currency_code`, `exchange_rate`)
  VALUES
    (1, 'Canadian Dollars', 'CAD', 1.000000000000000),
    (2, 'US Dollars', 'USD', 0.918726681568410);

REPLACE INTO `shop_order_status` (`order_status_id`, `order_status_name`)
  VALUES
    (1, 'Processing'),
    (2, 'Shipped'),
    (3, 'Complete'),
    (4, 'Rejected');

REPLACE INTO `shop_payment_gateways` (`payment_gateway_id`, `site_id`, `enabled`, `gateway_name`, `filename`, `sort`)
  VALUES
    (1, 1, 1, 'Credit Card', 'creditcard', 1);

REPLACE INTO `shop_shipping_gateways` (`shipping_gateway_id`, `site_id`, `enabled`, `gateway_name`, `filename`, `sort`)
  VALUES
    (2, 1, 0, 'Pickup In Store', 'pickup', 2);

REPLACE INTO `shop_taxes` (`tax_id`, `site_id`, `tax_name`, `tax_percentage`, `tax_country`, `tax_province`)
  VALUES
    (1, 1, 'GST', 0.05000, 'CA', 'AB'),
    (2, 1, 'GST', 0.05000, 'CA', 'BC'),
    (3, 1, 'PST', 0.07000, 'CA', 'BC'),
    (4, 1, 'GST', 0.05000, 'CA', 'MB'),
    (5, 1, 'PST', 0.08000, 'CA', 'MB'),
    (6, 1, 'HST', 0.13000, 'CA', 'NB'),
    (7, 1, 'HST', 0.13000, 'CA', 'NL'),
    (8, 1, 'GST', 0.05000, 'CA', 'NT'),
    (9, 1, 'HST', 0.15000, 'CA', 'NS'),
    (10, 1, 'GST', 0.05000, 'CA', 'NU'),
    (11, 1, 'HST', 0.13000, 'CA', 'ON'),
    (12, 1, 'HST', 0.14000, 'CA', 'PE'),
    (13, 1, 'GST', 0.05000, 'CA', 'QC'),
    (14, 1, 'QST', 0.09975, 'CA', 'QC'),
    (15, 1, 'GST', 0.05000, 'CA', 'SK'),
    (16, 1, 'PST', 0.05000, 'CA', 'SK'),
    (17, 1, 'GST', 0.05000, 'CA', 'YT');

REPLACE INTO `shop_config` (`config_id`, `site_id`, `config_label`, `config_key`, `config_value`, `input_type`, `sort`)
	VALUES
	  (1, 1, 'Shop Name', 'shop_name', 'My Shop', 'text', 1),
	  (2, 1, 'Shop Page', 'page_id', '5', 'pagelist', 2),
	  (3, 1, 'Add Shop Name to Title', 'add_shop_name_to_title', '1', 'checkbox', 3),
	  (4, 1, 'Index View Filename', 'index_view_filename', 'default', 'text', 4),
	  (5, 1, 'Shop Search Category Layout', 'search_category_layout_id', '1', 'categorylayoutlist', 5),
	  (6, 1, 'Products Per Page', 'products_per_page', '12', 'number', 6),
	  (7, 1, 'Product Link Text', 'product_link_text', 'More Info <i class=\'fa fa-info-circle\'></i>', 'text', 7),
	  (8, 1, 'Successful Transaction Link', 'successful_transaction_link', '/account?task=orders', 'text', 8),
	  (9, 1, 'Send Order Notifications To', 'order_notifications', 'ecommerce@jensentrailers.com', 'text', 9),
	  (10, 1, 'Category Link Text', 'category_link_text', 'View Category <i class=\'fa fa-arrow-right\'></i>', 'text', 7);
