RENAME TABLE `action` TO `acl_action`;
RENAME TABLE `role` TO `acl_role`;
RENAME TABLE `role_action` TO `acl_role_action`;
RENAME TABLE `user_role` TO `acl_user_role`;

ALTER TABLE `acl_role_action` DROP FOREIGN KEY `fk_role_action_action_id`;
ALTER TABLE `acl_role_action` DROP FOREIGN KEY `fk_role_action_role_id`;
ALTER TABLE `acl_user_role` DROP FOREIGN KEY `fk_user_role_user_id`;
ALTER TABLE `acl_user_role` DROP FOREIGN KEY `fk_user_role_role_id`;

ALTER TABLE `acl_action` CHANGE `action_id` `acl_action_id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `acl_role` CHANGE `role_id` `acl_role_id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `acl_role_action` CHANGE `role_action_id` `acl_role_action_id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `acl_role_action` CHANGE `role_id` `acl_role_id` INT(11) UNSIGNED NOT NULL;
ALTER TABLE `acl_role_action` CHANGE `action_id` `acl_action_id` INT(11) UNSIGNED NOT NULL;
ALTER TABLE `acl_user_role` CHANGE `user_role_id` `acl_user_role_id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `acl_user_role` CHANGE `role_id` `acl_role_id` INT(11) UNSIGNED NOT NULL;
ALTER TABLE `acl_action` DROP COLUMN IF EXISTS `default`;
ALTER TABLE `acl_action` DROP COLUMN IF EXISTS `description`;
ALTER TABLE `acl_action` CHANGE `name` `action` VARCHAR(255) NOT NULL;
ALTER TABLE `acl_role` CHANGE `name` `role` VARCHAR(255) NOT NULL;
ALTER TABLE `acl_action` CHANGE `constant_string_name` `constant` VARCHAR(255) NOT NULL;
ALTER TABLE `acl_role` DROP COLUMN IF EXISTS `constant_string_name`;

ALTER TABLE `acl_role_action` ADD CONSTRAINT `fk_role_action_action_id` FOREIGN KEY (`acl_action_id`) REFERENCES `acl_action` (`acl_action_id`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `acl_role_action` ADD CONSTRAINT `fk_role_action_role_id` FOREIGN KEY (`acl_role_id`) REFERENCES `acl_role` (`acl_role_id`) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE `acl_user_role` ADD CONSTRAINT `fk_user_role_role_id` FOREIGN KEY (`acl_role_id`) REFERENCES `acl_role` (`acl_role_id`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `acl_user_role` ADD CONSTRAINT `fk_user_role_user_id` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE;

CREATE TABLE `acl` (
  `acl_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `acl_role_id` int(11) unsigned NOT NULL,
  `page_id` int(11) unsigned DEFAULT NULL,
  `category_id` int(11) unsigned DEFAULT NULL,
  `document_id` int(11) unsigned DEFAULT NULL,
  `module_instance_id` int(11) unsigned DEFAULT NULL,
  `menu_item_id` int(11) unsigned DEFAULT NULL,
  `read` tinyint(1) unsigned NOT NULL DEFAULT '1',
  `write` tinyint(1) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`acl_id`),
  KEY `role_id` (`acl_role_id`),
  KEY `page_id` (`page_id`),
  KEY `category_id` (`category_id`),
  KEY `document_id` (`document_id`),
  KEY `module_instance_id` (`module_instance_id`),
  KEY `menu_item_id` (`menu_item_id`),
  CONSTRAINT `acl_ibfk_1` FOREIGN KEY (`acl_role_id`) REFERENCES `acl_role` (`acl_role_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `acl_ibfk_2` FOREIGN KEY (`page_id`) REFERENCES `pages` (`page_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `acl_ibfk_3` FOREIGN KEY (`category_id`) REFERENCES `categories` (`category_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `acl_ibfk_4` FOREIGN KEY (`document_id`) REFERENCES `document` (`document_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `acl_ibfk_5` FOREIGN KEY (`module_instance_id`) REFERENCES `module_instances` (`module_instance_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `acl_ibfk_6` FOREIGN KEY (`menu_item_id`) REFERENCES `menu_items` (`menu_item_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

# Create basic roles.
INSERT INTO `acl_role` (`acl_role_id`, `role`, `description`) VALUES
	(1, 'Public', "Automatically used for visitors who are not logged in."),
	(2, 'Registered Users', "Intended for registered users who need access to front-end features."),
	(3, 'Administrators', "Full access to everything."),
	(4, 'Editors', "Manage general content (pages, modules, etc)."),
	(5, 'SEO Specialists', "Access search engine optimization tools."),
	(6, 'Business Directory Managers', "Manage the business directory."),
	(7, 'Event Coordinators', "Manage events and event forms."),
	(8, 'Shop Managers', "Manage shop products, categories, and orders"),
	(9, 'Shop Administrators', "Manage shop products, categories, orders, and configuration."),
	(10, 'Appointment Schedulers', "Manage appointments from the online booking module.");

# Create actions.
INSERT INTO `acl_action` SET `acl_action_id`=1, `action`="Public Login", `constant`="LOGIN";
INSERT INTO `acl_action` SET `acl_action_id`=2, `action`="Admin: Login", `constant`="ADMIN_LOGIN";
INSERT INTO `acl_action` SET `acl_action_id`=3, `action`="Admin: Access website configuration", `constant`="CONFIG";
INSERT INTO `acl_action` SET `acl_action_id`=4, `action`="Admin: Access users", `constant`="USERS";
INSERT INTO `acl_action` SET `acl_action_id`=5, `action`="Admin: Install updates", `constant`="UPDATES";
INSERT INTO `acl_action` SET `acl_action_id`=6, `action`="Admin: Access pages", `constant`="PAGES";
INSERT INTO `acl_action` SET `acl_action_id`=7, `action`="Admin: Access categories", `constant`="CATEGORIES";
INSERT INTO `acl_action` SET `acl_action_id`=8, `action`="Admin: Access menus", `constant`="MENUS";
INSERT INTO `acl_action` SET `acl_action_id`=9, `action`="Admin: Access modules", `constant`="MODULES";
INSERT INTO `acl_action` SET `acl_action_id`=10, `action`="Admin: Access media manager", `constant`="MEDIA";
INSERT INTO `acl_action` SET `acl_action_id`=11, `action`="Admin: Access documents", `constant`="DOCUMENTS";
INSERT INTO `acl_action` SET `acl_action_id`=13, `action`="Admin: Access SEO tools", `constant`="SEO";
INSERT INTO `acl_action` SET `acl_action_id`=14, `action`="Admin: Access user roles", `constant`="ROLES";
INSERT INTO `acl_action` SET `acl_action_id`=15, `action`="Admin: Access directory companies", `constant`="DIRECTORY_COMPANIES";
INSERT INTO `acl_action` SET `acl_action_id`=16, `action`="Admin: Access directory categories", `constant`="DIRECTORY_CATEGORIES";
INSERT INTO `acl_action` SET `acl_action_id`=17, `action`="Admin: Access directory export", `constant`="DIRECTORY_EXPORT";
INSERT INTO `acl_action` SET `acl_action_id`=18, `action`="Admin: Access events", `constant`="EVENTS";
INSERT INTO `acl_action` SET `acl_action_id`=19, `action`="Admin: Access event forms", `constant`="EVENT_FORMS";
INSERT INTO `acl_action` SET `acl_action_id`=20, `action`="Admin: Access appointments", `constant`="APPOINTMENTS";
INSERT INTO `acl_action` SET `acl_action_id`=21, `action`="Admin: Access shop products", `constant`="SHOP_PRODUCTS";
INSERT INTO `acl_action` SET `acl_action_id`=22, `action`="Admin: Access shop categories", `constant`="SHOP_CATEGORIES";
INSERT INTO `acl_action` SET `acl_action_id`=23, `action`="Admin: Access shop orders", `constant`="SHOP_ORDERS";
INSERT INTO `acl_action` SET `acl_action_id`=24, `action`="Admin: Access shop data fields", `constant`="SHOP_DATA";
INSERT INTO `acl_action` SET `acl_action_id`=25, `action`="Admin: Access shop shopper groups", `constant`="SHOP_SHOPPER_GROUPS";
INSERT INTO `acl_action` SET `acl_action_id`=26, `action`="Admin: Access shop configuration", `constant`="SHOP_CONFIG";

# Configure role actions.
INSERT INTO `acl_role_action` (`acl_role_id`, `acl_action_id`) VALUES
	(2, 1),
	(3, 1),
	(3, 2),
	(3, 3),
	(3, 4),
	(3, 5),
	(3, 6),
	(3, 7),
	(3, 8),
	(3, 9),
	(3, 10),
	(3, 11),
	(3, 13),
	(3, 14),
	(4, 1),
	(4, 2),
	(4, 6),
	(4, 7),
	(4, 8),
	(4, 9),
	(4, 10),
	(4, 11),
	(5, 1),
	(5, 2),
	(5, 13),
	(6, 1),
	(6, 2),
	(6, 15),
	(6, 16),
	(6, 17),
	(7, 1),
	(7, 2),
	(7, 18),
	(7, 19),
	(8, 1),
	(8, 2),
	(8, 21),
	(8, 22),
	(8, 23),
	(9, 1),
	(9, 2),
	(9, 21),
	(9, 22),
	(9, 23),
	(9, 24),
	(9, 25),
	(9, 26),
	(10, 1),
	(10, 2),
	(10, 20);

# Make admin users admins.
INSERT INTO `acl_user_role` (`user_id`, `acl_role_id`)
	SELECT `user_id` AS `user_id`, ('3') AS `acl_role_id` FROM `users` WHERE `admin`=1;

# Make non-admin users registered.
INSERT INTO `acl_user_role` (`user_id`, `acl_role_id`)
	SELECT `user_id` AS `user_id`, ('2') AS `acl_role_id` FROM `users` WHERE `admin`=0;

# Configure ACL for public pages.
INSERT INTO `acl` (`acl_role_id`, `page_id`, `read`, `write`)
	SELECT ('1') AS `acl_role_id`, `page_id` AS `page_id`, ('1') AS `read`, ('0') AS `write`
	FROM `pages` WHERE `requires_login`=0
	UNION SELECT ('2') AS `acl_role_id`, `page_id` AS `page_id`, ('1') AS `read`, ('0') AS `write`
	FROM `pages` WHERE `requires_login`=0
	UNION SELECT ('3') AS `acl_role_id`, `page_id` AS `page_id`, ('1') AS `read`, ('1') AS `write`
	FROM `pages` WHERE `requires_login`=0;

# Configure ACL for private pages.
INSERT INTO `acl` (`acl_role_id`, `page_id`, `read`, `write`)
	SELECT ('1') AS `acl_role_id`, `page_id` AS `page_id`, ('0') AS `read`, ('0') AS `write`
	FROM `pages`
	WHERE `requires_login`=1
	UNION SELECT ('2') AS `acl_role_id`, `page_id` AS `page_id`, ('1') AS `read`, ('0') AS `write`
	FROM `pages`
	WHERE `requires_login`=1
	UNION SELECT ('3') AS `acl_role_id`, `page_id` AS `page_id`, ('1') AS `read`, ('1') AS `write`
	FROM `pages`
	WHERE `requires_login`=1;

# Configure ACL for public modules.
INSERT INTO `acl` (`acl_role_id`, `module_instance_id`, `read`, `write`)
	SELECT ('1') AS `acl_role_id`, `module_instance_id` AS `module_instance_id`, ('1') AS `read`, ('0') AS `write`
	FROM `module_instances`
	WHERE `requires_login`=0
	UNION SELECT ('2') AS `acl_role_id`, `module_instance_id` AS `module_instance_id`, ('1') AS `read`, ('0') AS `write`
	FROM `module_instances`
	WHERE `requires_login`=0
	UNION SELECT ('3') AS `acl_role_id`, `module_instance_id` AS `module_instance_id`, ('1') AS `read`, ('1') AS `write`
	FROM `module_instances`
	WHERE `requires_login`=0;

# Configure ACL for private modules.
INSERT INTO `acl` (`acl_role_id`, `module_instance_id`, `read`, `write`)
	SELECT ('1') AS `acl_role_id`, `module_instance_id` AS `module_instance_id`, ('0') AS `read`, ('0') AS `write`
	FROM `module_instances`
	WHERE `requires_login`=1
	UNION SELECT ('2') AS `acl_role_id`, `module_instance_id` AS `module_instance_id`, ('1') AS `read`, ('0') AS `write`
	FROM `module_instances`
	WHERE `requires_login`=1
	UNION SELECT ('3') AS `acl_role_id`, `module_instance_id` AS `module_instance_id`, ('1') AS `read`, ('1') AS `write`
	FROM `module_instances`
	WHERE `requires_login`=1;

# Configure ACL for public documents.
INSERT INTO `acl` (`acl_role_id`, `document_id`, `read`, `write`)
	SELECT ('1') AS `acl_role_id`, `document_id` AS `document_id`, ('1') AS `read`, ('0') AS `write`
	FROM `document`
	WHERE `access`=1
	UNION SELECT ('2') AS `acl_role_id`, `document_id` AS `document_id`, ('1') AS `read`, ('0') AS `write`
	FROM `document`
	WHERE `access`=1
	UNION SELECT ('3') AS `acl_role_id`, `document_id` AS `document_id`, ('1') AS `read`, ('1') AS `write`
	FROM `document`
	WHERE `access`=1;

# Configure ACL for semi-private documents.
INSERT INTO `acl` (`acl_role_id`, `document_id`, `read`, `write`)
	SELECT ('1') AS `acl_role_id`, `document_id` AS `document_id`, ('0') AS `read`, ('0') AS `write`
	FROM `document`
	WHERE `access`=2
	UNION SELECT ('2') AS `acl_role_id`, `document_id` AS `document_id`, ('1') AS `read`, ('0') AS `write`
	FROM `document`
	WHERE `access`=2
	UNION SELECT ('3') AS `acl_role_id`, `document_id` AS `document_id`, ('1') AS `read`, ('1') AS `write`
	FROM `document`
	WHERE `access`=2;

# Configure ACL for private documents.
INSERT INTO `acl` (`acl_role_id`, `document_id`, `read`, `write`)
	SELECT ('1') AS `acl_role_id`, `document_id` AS `document_id`, ('0') AS `read`, ('0') AS `write`
	FROM `document`
	WHERE `access`=3
	UNION SELECT ('2') AS `acl_role_id`, `document_id` AS `document_id`, ('0') AS `read`, ('0') AS `write`
	FROM `document`
	WHERE `access`=3
	UNION SELECT ('3') AS `acl_role_id`, `document_id` AS `document_id`, ('1') AS `read`, ('1') AS `write`
	FROM `document`
	WHERE `access`=3;

# Configure ACL for menu items.
INSERT INTO `acl` (`acl_role_id`, `menu_item_id`, `read`, `write`)
	SELECT ('1') AS `acl_role_id`, `menu_item_id` AS `menu_item_id`, ('1') AS `read`, ('0') AS `write`
	FROM `menu_items`
	UNION SELECT ('2') AS `acl_role_id`, `menu_item_id` AS `menu_item_id`, ('1') AS `read`, ('0') AS `write`
	FROM `menu_items`
	UNION SELECT ('3') AS `acl_role_id`, `menu_item_id` AS `menu_item_id`, ('1') AS `read`, ('1') AS `write`
	FROM `menu_items`;

DELETE FROM `modules` WHERE `file_name`="image_crop";

ALTER TABLE `pages` DROP COLUMN IF EXISTS `requires_login`;
ALTER TABLE `module_instances` DROP COLUMN IF EXISTS `requires_login`;
ALTER TABLE `pages` DROP COLUMN IF EXISTS `social`;
ALTER TABLE `sites` DROP COLUMN IF EXISTS `addthis_widget`;
ALTER TABLE `document` DROP COLUMN IF EXISTS `access`;
