ALTER TABLE `module_settings` ADD INDEX `module_key` (`key`);
ALTER TABLE `sites` ADD IF NOT EXISTS `theme` VARCHAR(255) NULL AFTER `template_id`;
ALTER TABLE `sites` ADD IF NOT EXISTS `font` VARCHAR(255) NULL AFTER `theme`;
ALTER TABLE `module_instances` CHANGE `sort` `sort` INT(3) UNSIGNED NOT NULL DEFAULT 0;
ALTER TABLE `modules_pages` ADD IF NOT EXISTS `sort` INT(3) UNSIGNED NOT NULL DEFAULT 0;
UPDATE `modules_pages` INNER JOIN `module_instances` ON `modules_pages`.`module_instance_id`=`module_instances`.id SET `modules_pages`.sort=`module_instances`.sort;
ALTER TABLE `directory_companies` ADD IF NOT EXISTS `internal_notes` TEXT NULL AFTER `sv_zoom`;
UPDATE `modules` SET `name`="Custom Content" WHERE `name`="Custom HTML";
UPDATE `modules` SET `name`="Blog - Archives" WHERE `name`="Blog Archives";
UPDATE `modules` SET `name`="Blog - Search" WHERE `name`="Blog Search";
UPDATE `modules` SET `name`="Blog - Latest" WHERE `name`="Latest Blog";
UPDATE `modules` SET `name`="Booking" WHERE `name`="Online Booking";
UPDATE `modules` SET `name`="Gallery" WHERE `name`="Image/Video Gallery";
UPDATE `modules` SET `name`="Gallery - Galleria" WHERE `name`="Galleria Gallery";
UPDATE `module_settings` SET `key`=CONCAT("s", `key`) WHERE `module_instance_id` IN(SELECT mi.id FROM `module_instances` AS mi INNER JOIN `modules` AS m ON mi.module_id=m.id WHERE `file_name`='menu_mobile');
UPDATE `module_settings` SET `key`="special01" WHERE `key`="01-special" AND `module_instance_id` IN(SELECT mi.id FROM `module_instances` AS mi INNER JOIN `modules` AS m ON mi.module_id=m.id WHERE `file_name`='monthly_specials');
UPDATE `module_settings` SET `key`="special02" WHERE `key`="02-special" AND `module_instance_id` IN(SELECT mi.id FROM `module_instances` AS mi INNER JOIN `modules` AS m ON mi.module_id=m.id WHERE `file_name`='monthly_specials');
UPDATE `module_settings` SET `key`="special03" WHERE `key`="03-special" AND `module_instance_id` IN(SELECT mi.id FROM `module_instances` AS mi INNER JOIN `modules` AS m ON mi.module_id=m.id WHERE `file_name`='monthly_specials');
UPDATE `module_settings` SET `key`="special04" WHERE `key`="04-special" AND `module_instance_id` IN(SELECT mi.id FROM `module_instances` AS mi INNER JOIN `modules` AS m ON mi.module_id=m.id WHERE `file_name`='monthly_specials');
UPDATE `module_settings` SET `key`="special05" WHERE `key`="05-special" AND `module_instance_id` IN(SELECT mi.id FROM `module_instances` AS mi INNER JOIN `modules` AS m ON mi.module_id=m.id WHERE `file_name`='monthly_specials');
UPDATE `module_settings` SET `key`="special06" WHERE `key`="06-special" AND `module_instance_id` IN(SELECT mi.id FROM `module_instances` AS mi INNER JOIN `modules` AS m ON mi.module_id=m.id WHERE `file_name`='monthly_specials');
UPDATE `module_settings` SET `key`="special07" WHERE `key`="07-special" AND `module_instance_id` IN(SELECT mi.id FROM `module_instances` AS mi INNER JOIN `modules` AS m ON mi.module_id=m.id WHERE `file_name`='monthly_specials');
UPDATE `module_settings` SET `key`="special08" WHERE `key`="08-special" AND `module_instance_id` IN(SELECT mi.id FROM `module_instances` AS mi INNER JOIN `modules` AS m ON mi.module_id=m.id WHERE `file_name`='monthly_specials');
UPDATE `module_settings` SET `key`="special09" WHERE `key`="09-special" AND `module_instance_id` IN(SELECT mi.id FROM `module_instances` AS mi INNER JOIN `modules` AS m ON mi.module_id=m.id WHERE `file_name`='monthly_specials');
UPDATE `module_settings` SET `key`="special10" WHERE `key`="10-special" AND `module_instance_id` IN(SELECT mi.id FROM `module_instances` AS mi INNER JOIN `modules` AS m ON mi.module_id=m.id WHERE `file_name`='monthly_specials');
UPDATE `module_settings` SET `key`="special11" WHERE `key`="11-special" AND `module_instance_id` IN(SELECT mi.id FROM `module_instances` AS mi INNER JOIN `modules` AS m ON mi.module_id=m.id WHERE `file_name`='monthly_specials');
UPDATE `module_settings` SET `key`="special12" WHERE `key`="12-special" AND `module_instance_id` IN(SELECT mi.id FROM `module_instances` AS mi INNER JOIN `modules` AS m ON mi.module_id=m.id WHERE `file_name`='monthly_specials');
DELETE FROM `modules` WHERE `file_name`="video";
UPDATE `modules` SET `name`="Video", `file_name`="video" WHERE `file_name`="video_html5";
DROP TABLE IF EXISTS `module_dependancy`;
DROP TABLE IF EXISTS `template_cake`;
ALTER TABLE `template_cell` DROP FOREIGN KEY `template_cell_ibfk_1`;
ALTER TABLE `template_row` DROP FOREIGN KEY `template_row_ibfk_1`;
ALTER TABLE `template_row` DROP FOREIGN KEY `parent_cell_id`;
DROP TABLE IF EXISTS `template_cell`;
DROP TABLE IF EXISTS `template_layers`;
DROP TABLE IF EXISTS `template_layers_modules`;
DROP TABLE IF EXISTS `template_row`;
DROP TABLE IF EXISTS `template_options`;
INSERT INTO `modules` (`name`, `file_name`, `class_name`) VALUES ('Contact Information', 'contact_info', 'ContactInfoModule');
UPDATE `module_settings` SET `key`="html1" WHERE `key`="module_html" AND `module_instance_id` IN(SELECT mi.id FROM `module_instances` AS mi INNER JOIN `modules` AS m ON mi.module_id=m.id WHERE `file_name`='html');
ALTER TABLE `directory_companies` CHANGE `internal_notes` `internal_notes` VARCHAR(255) NULL AFTER `description`;
ALTER TABLE `users` DROP COLUMN IF EXISTS `birth_day`;
ALTER TABLE `users` DROP COLUMN IF EXISTS `birth_month`;
ALTER TABLE `users` DROP COLUMN IF EXISTS `birth_year`;
ALTER TABLE `users` DROP COLUMN IF EXISTS `facebook_id`;
ALTER TABLE `sites` DROP COLUMN IF EXISTS `facebook_app_id`;
ALTER TABLE `sites` DROP COLUMN IF EXISTS `facebook_app_secret`;
ALTER TABLE `menu_items` CHANGE `parent_id` `parent_id` INT(11) UNSIGNED NULL DEFAULT NULL;
UPDATE `menu_items` SET `parent_id`=NULL WHERE `parent_id`=0;
ALTER TABLE `pages` CHANGE `category_id` `category_id` INT(11) UNSIGNED NULL DEFAULT NULL;
UPDATE `pages` SET `category_id`=NULL WHERE `category_id`=0;
DROP TABLE IF EXISTS `shop_product_packages`;
INSERT INTO `modules` SET `name`='Team', `file_name`='team', `class_name`='TeamModule';
INSERT INTO `modules` SET `name`='Services', `file_name`='services', `class_name`='ServicesModule';
UPDATE `shop_postalcodes` SET `longitude`=`longitude`*-1 WHERE `longitude` > 0;
# Fix generic `id` fields - do this last, after all existing changes which reference the old field IDs.
ALTER TABLE `booking_appointments` CHANGE `id` `booking_appointment_id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `categories` CHANGE `id` `category_id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `directory_companies_categories` CHANGE `id` `directory_company_category_id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `domains` CHANGE `id` `domain_id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `events` DROP FOREIGN KEY `events_ibfk_1`;
ALTER TABLE `events_forms` DROP FOREIGN KEY `events_forms_ibfk_1`;
ALTER TABLE `events` CHANGE `id` `event_id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `events` ADD CONSTRAINT `fk_events_parent_id` FOREIGN KEY (`parent_id`) REFERENCES `events` (`event_id`) ON UPDATE CASCADE;
ALTER TABLE `events_forms` ADD CONSTRAINT `fk_events_forms_event_id` FOREIGN KEY (`event_id`) REFERENCES `events` (`event_id`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `menu_items` CHANGE `id` `menu_item_id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `menus` CHANGE `id` `menu_id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `module_instances` CHANGE `id` `module_instance_id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `module_settings` CHANGE `id` `module_setting_id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `modules` CHANGE `id` `module_id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `modules_pages` CHANGE `id` `module_page_id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `shop_categories` DROP FOREIGN KEY `shop_categories_ibfk_5`;
ALTER TABLE `pages` CHANGE `id` `page_id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `shop_categories` ADD CONSTRAINT `fk_shop_categories_not_authorized_page_id` FOREIGN KEY (`not_authorized_page_id`) REFERENCES `pages` (`page_id`) ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE `portfolio` CHANGE `id` `portfolio_id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `shop_categories_products` CHANGE `id` `shop_category_product_id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `shop_categories_shopper_groups` CHANGE `id` `shop_category_shopper_group_id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `shop_vehicles_products` CHANGE `id` `shop_vehicle_product_id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `shop_routes` DROP FOREIGN KEY `shop_routes_ibfk_1`;
ALTER TABLE `sites` CHANGE `id` `site_id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `shop_routes` ADD CONSTRAINT `fk_shop_routes_site_id` FOREIGN KEY (`site_id`) REFERENCES `sites` (`site_id`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `templates` CHANGE `id` `template_id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `testimonials` CHANGE `id` `testimonial_id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `user_role` DROP FOREIGN KEY `fk_user_role_1`;
ALTER TABLE `users` CHANGE `id` `user_id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `user_role` ADD CONSTRAINT `fk_user_role_user_id` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE;
# RC2
ALTER TABLE `module_settings` CHANGE `value` `value` TEXT NOT NULL;
ALTER TABLE `menu_items` CHANGE `page_id` `page_id` INT(11) UNSIGNED NULL DEFAULT NULL;
ALTER TABLE `shop_routes` DROP KEY `url`;

# Add constraints.
ALTER TABLE `modules` ADD CONSTRAINT file_name UNIQUE (file_name);
ALTER TABLE `modules` ADD CONSTRAINT class_name UNIQUE (class_name);

DELETE `modules_pages` FROM `modules_pages` LEFT JOIN `module_instances` ON `modules_pages`.`module_instance_id`=`module_instances`.`module_instance_id` WHERE `module_instances`.`module_instance_id` IS NULL;
DELETE `modules_pages` FROM `modules_pages` LEFT JOIN `pages` ON `modules_pages`.`page_id`=`pages`.`page_id` WHERE `pages`.`page_id` IS NULL;
ALTER TABLE `modules_pages` ADD CONSTRAINT `fk_modules_pages_module_instance_id` FOREIGN KEY (`module_instance_id`) REFERENCES `module_instances` (`module_instance_id`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `modules_pages` ADD CONSTRAINT `fk_modules_pages_page_id` FOREIGN KEY (`page_id`) REFERENCES `pages` (`page_id`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `categories` ADD CONSTRAINT `fk_categories_site_id` FOREIGN KEY (`site_id`) REFERENCES `sites` (`site_id`) ON DELETE RESTRICT ON UPDATE CASCADE;
ALTER TABLE `directory_categories` ADD CONSTRAINT `fk_directory_categories_site_id` FOREIGN KEY (`site_id`) REFERENCES `sites` (`site_id`) ON DELETE RESTRICT ON UPDATE CASCADE;
ALTER TABLE `directory_categories` ADD CONSTRAINT `fk_directory_categories_parent_id` FOREIGN KEY (`parent_id`) REFERENCES `directory_categories` (`category_id`) ON DELETE RESTRICT ON UPDATE CASCADE;
ALTER TABLE `directory_companies` ADD CONSTRAINT `fk_directory_companies_site_id` FOREIGN KEY (`site_id`) REFERENCES `sites` (`site_id`) ON DELETE RESTRICT ON UPDATE CASCADE;
ALTER TABLE `directory_coupons` ADD CONSTRAINT `fk_directory_coupons_site_id` FOREIGN KEY (`site_id`) REFERENCES `sites` (`site_id`) ON DELETE RESTRICT ON UPDATE CASCADE;
ALTER TABLE `domains` ADD CONSTRAINT `fk_domains_site_id` FOREIGN KEY (`site_id`) REFERENCES `sites` (`site_id`) ON DELETE RESTRICT ON UPDATE CASCADE;
ALTER TABLE `events` ADD CONSTRAINT `fk_events_site_id` FOREIGN KEY (`site_id`) REFERENCES `sites` (`site_id`) ON DELETE RESTRICT ON UPDATE CASCADE;

UPDATE `menu_items` SET `page_id`=NULL WHERE `page_id`=0;
ALTER TABLE `menu_items` ADD CONSTRAINT `fk_menu_items_menu_id` FOREIGN KEY (`menu_id`) REFERENCES `menus` (`menu_id`) ON DELETE RESTRICT ON UPDATE CASCADE;
ALTER TABLE `menu_items` ADD CONSTRAINT `fk_menu_items_parent_id` FOREIGN KEY (`parent_id`) REFERENCES `menu_items` (`menu_item_id`) ON DELETE RESTRICT ON UPDATE CASCADE;
ALTER TABLE `menu_items` ADD CONSTRAINT `fk_menu_items_page_id` FOREIGN KEY (`page_id`) REFERENCES `pages` (`page_id`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `menus` ADD CONSTRAINT `fk_menus_site_id` FOREIGN KEY (`site_id`) REFERENCES `sites` (`site_id`) ON DELETE RESTRICT ON UPDATE CASCADE;
ALTER TABLE `metadata_pages` ADD CONSTRAINT `fk_metadata_pages_site_id` FOREIGN KEY (`site_id`) REFERENCES `sites` (`site_id`) ON DELETE RESTRICT ON UPDATE CASCADE;
ALTER TABLE `metadata_pages` ADD CONSTRAINT `fk_metadata_pages_page_id` FOREIGN KEY (`page_id`) REFERENCES `pages` (`page_id`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `module_instances` ADD CONSTRAINT `fk_module_instances_module_id` FOREIGN KEY (`module_id`) REFERENCES `modules` (`module_id`) ON DELETE RESTRICT ON UPDATE CASCADE;
ALTER TABLE `pages` ADD CONSTRAINT `fk_pages_site_id` FOREIGN KEY (`site_id`) REFERENCES `sites` (`site_id`) ON DELETE RESTRICT ON UPDATE CASCADE;

ALTER TABLE `pages` ADD CONSTRAINT `fk_pages_category_id` FOREIGN KEY (`category_id`) REFERENCES `categories` (`category_id`) ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE `portfolio` ADD CONSTRAINT `fk_portfolio_site_id` FOREIGN KEY (`site_id`) REFERENCES `sites` (`site_id`) ON DELETE RESTRICT ON UPDATE CASCADE;
ALTER TABLE `shop_config` ADD CONSTRAINT `fk_shop_config_site_id` FOREIGN KEY (`site_id`) REFERENCES `sites` (`site_id`) ON DELETE RESTRICT ON UPDATE CASCADE;
ALTER TABLE `shop_datasources` ADD CONSTRAINT `fk_shop_datasources_site_id` FOREIGN KEY (`site_id`) REFERENCES `sites` (`site_id`) ON DELETE RESTRICT ON UPDATE CASCADE;
ALTER TABLE `shop_categories` ADD CONSTRAINT `fk_shop_categories_site_id` FOREIGN KEY (`site_id`) REFERENCES `sites` (`site_id`) ON DELETE RESTRICT ON UPDATE CASCADE;
ALTER TABLE `shop_orders` ADD CONSTRAINT `fk_shop_orders_site_id` FOREIGN KEY (`site_id`) REFERENCES `sites` (`site_id`) ON DELETE RESTRICT ON UPDATE CASCADE;
ALTER TABLE `shop_payment_gateways` ADD CONSTRAINT `fk_shop_payment_gateways_site_id` FOREIGN KEY (`site_id`) REFERENCES `sites` (`site_id`) ON DELETE RESTRICT ON UPDATE CASCADE;
ALTER TABLE `shop_product_options` ADD CONSTRAINT `fk_shop_product_options_site_id` FOREIGN KEY (`site_id`) REFERENCES `sites` (`site_id`) ON DELETE RESTRICT ON UPDATE CASCADE;

ALTER TABLE `shop_products` ADD CONSTRAINT `fk_shop_products_site_id` FOREIGN KEY (`site_id`) REFERENCES `sites` (`site_id`) ON DELETE RESTRICT ON UPDATE CASCADE;
ALTER TABLE `shop_shipping_gateways` ADD CONSTRAINT `fk_shop_shipping_gateways_site_id` FOREIGN KEY (`site_id`) REFERENCES `sites` (`site_id`) ON DELETE RESTRICT ON UPDATE CASCADE;
ALTER TABLE `shop_shopper_groups` ADD CONSTRAINT `fk_shop_shopper_groups_site_id` FOREIGN KEY (`site_id`) REFERENCES `sites` (`site_id`) ON DELETE RESTRICT ON UPDATE CASCADE;
ALTER TABLE `shop_taxes` ADD CONSTRAINT `fk_shop_taxes_site_id` FOREIGN KEY (`site_id`) REFERENCES `sites` (`site_id`) ON DELETE RESTRICT ON UPDATE CASCADE;
ALTER TABLE `sites` ADD CONSTRAINT `fk_sites_template_id` FOREIGN KEY (`template_id`) REFERENCES `templates` (`template_id`) ON DELETE RESTRICT ON UPDATE CASCADE;
ALTER TABLE `sites` CHANGE `login_page_id` `login_page_id` int(11) unsigned DEFAULT NULL;
ALTER TABLE `sites` CHANGE `registration_page_id` `registration_page_id` int(11) unsigned DEFAULT NULL;
UPDATE `sites` SET `login_page_id`=NULL WHERE `login_page_id`=0;
UPDATE `sites` SET `registration_page_id`=NULL WHERE `registration_page_id`=0;
ALTER TABLE `sites` ADD CONSTRAINT `fk_sites_login_page_id` FOREIGN KEY (`login_page_id`) REFERENCES `pages` (`page_id`) ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE `sites` ADD CONSTRAINT `fk_sites_registration_page_id` FOREIGN KEY (`registration_page_id`) REFERENCES `pages` (`page_id`) ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE `testimonials` ADD CONSTRAINT `fk_testimonials_site_id` FOREIGN KEY (`site_id`) REFERENCES `sites` (`site_id`) ON DELETE RESTRICT ON UPDATE CASCADE;
UPDATE `users` SET `joined_site_id`=NULL WHERE `joined_site_id`=0;
UPDATE `users` SET `shopper_group_id`=NULL WHERE `shopper_group_id`=0;
ALTER TABLE `users` ADD CONSTRAINT `fk_users_joined_site_id` FOREIGN KEY (`joined_site_id`) REFERENCES `sites` (`site_id`) ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE `users` ADD CONSTRAINT `fk_users_shopper_group_id` FOREIGN KEY (`shopper_group_id`) REFERENCES `shop_shopper_groups` (`shopper_group_id`) ON DELETE SET NULL ON UPDATE CASCADE;
