CREATE TABLE IF NOT EXISTS `booking_appointments` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  `phone` varchar(255) NOT NULL,
  `appointment_date` datetime NOT NULL,
  `appointment_for` varchar(255) NOT NULL,
  `appointment_with` varchar(255) NOT NULL,
  `notes` text NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `template_cell` (
  `cell_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `row_id` int(11) unsigned NOT NULL,
  `position_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `size_mapping` text COLLATE utf8_unicode_ci NOT NULL,
  `semantic` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'div',
  `sort` int(11) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`cell_id`),
  KEY `row_id_idx` (`row_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE IF NOT EXISTS `template_options` (
  `option_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `template_id` int(11) unsigned NOT NULL,
  `option_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `option_key` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `option_value` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  PRIMARY KEY (`option_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE IF NOT EXISTS `template_row` (
  `row_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `template_id` int(11) unsigned NOT NULL,
  `parent_cell_id` int(11) unsigned DEFAULT NULL,
  `semantic` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'div',
  `sort` int(11) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`row_id`),
  KEY `parent_cell_id_idx` (`parent_cell_id`),
  KEY `template_id` (`template_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

ALTER TABLE `template_options` ADD CONSTRAINT `template_options_ibfk_1` FOREIGN KEY (`template_id`) REFERENCES `templates` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `template_row` ADD CONSTRAINT `template_row_ibfk_1` FOREIGN KEY (`template_id`) REFERENCES `templates` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `template_row` ADD CONSTRAINT `parent_cell_id` FOREIGN KEY (`parent_cell_id`) REFERENCES `template_cell` (`cell_id`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `template_cell` ADD CONSTRAINT `template_cell_ibfk_1` FOREIGN KEY (`row_id`) REFERENCES `template_row` (`row_id`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `shop_categories` ADD IF NOT EXISTS `not_authorized_page_id` INT(11) unsigned NULL DEFAULT NULL AFTER `category_image_id`;
ALTER TABLE `shop_categories` ADD CONSTRAINT `shop_categories_ibfk_5` FOREIGN KEY (`not_authorized_page_id`) REFERENCES `pages` (`id`) ON DELETE SET NULL ON UPDATE CASCADE;
UPDATE `templates` SET `name`="NCMS Responsive Grid" WHERE `directory`="default";

CREATE TABLE IF NOT EXISTS `shop_shopper_groups` (
	  `shopper_group_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
	  `site_id` int(11) unsigned NOT NULL DEFAULT '1',
	  `group_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
	  `global_discount` float NOT NULL DEFAULT '0',
	  `payment_gateway_id` int(11) unsigned DEFAULT NULL,
	  `shipping_gateway_id` int(11) unsigned DEFAULT NULL,
	  PRIMARY KEY (`shopper_group_id`),
	  KEY `payment_gateway_id` (`payment_gateway_id`),
	  KEY `shipping_gateway_id` (`shipping_gateway_id`),
	  CONSTRAINT `shop_shopper_groups_ibfk_1` FOREIGN KEY (`payment_gateway_id`) REFERENCES `shop_payment_gateways` (`payment_gateway_id`) ON DELETE SET NULL ON UPDATE CASCADE,
	  CONSTRAINT `shop_shopper_groups_ibfk_2` FOREIGN KEY (`shipping_gateway_id`) REFERENCES `shop_shipping_gateways` (`shipping_gateway_id`) ON DELETE SET NULL ON UPDATE CASCADE
	) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

ALTER TABLE `users` ADD IF NOT EXISTS `shopper_group_id` int(11) unsigned DEFAULT NULL AFTER `last_login`;

CREATE TABLE IF NOT EXISTS `shop_prices` (
	  `price_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
	  `product_id` int(11) unsigned NOT NULL,
	  `shopper_group_id` int(11) unsigned NOT NULL,
	  `price` decimal(10,2) unsigned NOT NULL,
	  PRIMARY KEY (`price_id`),
	  UNIQUE KEY `nodupes` (`product_id`,`shopper_group_id`),
	  KEY `shopper_group_id` (`shopper_group_id`),
	  CONSTRAINT `shop_prices_ibfk_1` FOREIGN KEY (`product_id`) REFERENCES `shop_products` (`product_id`) ON DELETE CASCADE ON UPDATE CASCADE,
	  CONSTRAINT `shop_prices_ibfk_2` FOREIGN KEY (`shopper_group_id`) REFERENCES `shop_shopper_groups` (`shopper_group_id`) ON DELETE CASCADE ON UPDATE CASCADE
	) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE IF NOT EXISTS `shop_categories_shopper_groups` (
	  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
	  `category_id` int(11) unsigned NOT NULL,
	  `shopper_group_id` int(11) unsigned NOT NULL,
	  PRIMARY KEY (`id`),
	  UNIQUE KEY `category_id` (`category_id`,`shopper_group_id`),
	  KEY `shopper_group_id` (`shopper_group_id`),
	  CONSTRAINT `shop_categories_shopper_groups_ibfk_1` FOREIGN KEY (`category_id`) REFERENCES `shop_categories` (`category_id`) ON DELETE CASCADE ON UPDATE CASCADE,
	  CONSTRAINT `shop_categories_shopper_groups_ibfk_2` FOREIGN KEY (`shopper_group_id`) REFERENCES `shop_shopper_groups` (`shopper_group_id`) ON DELETE CASCADE ON UPDATE CASCADE
	) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

REPLACE INTO `shop_shopper_groups` (`shopper_group_id`, `site_id`, `group_name`, `global_discount`, `payment_gateway_id`, `shipping_gateway_id`) VALUES (1, 1, 'Default', 0, NULL, NULL);

CREATE TABLE IF NOT EXISTS `crawler_issues` (
	  `issue_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
	  `crawltime` datetime NOT NULL,
	  `page_url` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
	  `severity` int(11) NOT NULL DEFAULT '1',
	  `notes` text COLLATE utf8_unicode_ci NOT NULL,
	  PRIMARY KEY (`issue_id`)
	) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE IF NOT EXISTS `shop_vehicles` (
	  `vehicle_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
	  `datasource_vehicle_id` varchar(255) DEFAULT NULL,
	  `year` float NOT NULL,
	  `make` varchar(255) NOT NULL DEFAULT '',
	  `model` varchar(255) NOT NULL DEFAULT '',
	  `style` varchar(255) NOT NULL DEFAULT '',
	  PRIMARY KEY (`vehicle_id`),
	  UNIQUE KEY `year` (`year`,`make`,`model`,`style`)
	) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `shop_vehicles_products` (
	  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
	  `vehicle_id` int(11) unsigned NOT NULL,
	  `product_id` int(11) unsigned NOT NULL,
	  PRIMARY KEY (`id`),
	  UNIQUE KEY `vehicle_id_product_id` (`vehicle_id`,`product_id`),
	  KEY `product_id` (`product_id`),
	  CONSTRAINT `shop_vehicles_products_ibfk_1` FOREIGN KEY (`vehicle_id`) REFERENCES `shop_vehicles` (`vehicle_id`) ON DELETE CASCADE ON UPDATE CASCADE,
	  CONSTRAINT `shop_vehicles_products_ibfk_2` FOREIGN KEY (`product_id`) REFERENCES `shop_products` (`product_id`) ON DELETE CASCADE ON UPDATE CASCADE
	) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;
