CREATE TABLE IF NOT EXISTS `action` (
      `action_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
      `name` varchar(100) NOT NULL,
      `constant_string_name` varchar(100) NOT NULL,
      `description` text,
      `default` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'Whether or not to allow this action by default',
      PRIMARY KEY (`action_id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `role` (
      `role_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
      `name` varchar(45) NOT NULL,
      `constant_string_name` char(100) NOT NULL,
      `description` text,
      PRIMARY KEY (`role_id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `role_action` (
      `role_action_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
      `role_id` int(11) unsigned NOT NULL,
      `action_id` int(11) unsigned NOT NULL,
      PRIMARY KEY (`role_action_id`),
      KEY `fk_role_action_1` (`role_id`),
      KEY `fk_role_action_2` (`action_id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

ALTER TABLE `role_action` ADD CONSTRAINT `fk_role_action_1` FOREIGN KEY (`role_id`) REFERENCES `role` (`role_id`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `role_action` ADD CONSTRAINT `fk_role_action_2` FOREIGN KEY (`action_id`) REFERENCES `action` (`action_id`) ON DELETE CASCADE ON UPDATE CASCADE;

CREATE TABLE IF NOT EXISTS `user_role` (
      `user_role_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
      `user_id` int(11) unsigned NOT NULL,
      `role_id` int(11) unsigned NOT NULL,
      PRIMARY KEY (`user_role_id`),
      KEY `fk_user_role_1` (`user_id`),
      KEY `fk_user_role_2` (`role_id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

ALTER TABLE `user_role` ADD CONSTRAINT `fk_user_role_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `user_role` ADD CONSTRAINT `fk_user_role_2` FOREIGN KEY (`role_id`) REFERENCES `role` (`role_id`) ON DELETE CASCADE ON UPDATE CASCADE;
