CREATE TABLE rbac_permission (
	perm_id INT AUTO_INCREMENT NOT NULL, 
	role_id INT NOT NULL, 
	perm_name VARCHAR(32) NOT NULL, 
	perm_filter VARCHAR(100) NOT NULL, 
	INDEX IDX_70964D43D60322AC (role_id), 
	PRIMARY KEY(perm_id)) 
DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB;

CREATE TABLE resetuserpassword (
	id VARCHAR(8) NOT NULL, 
	userId INT NOT NULL, 
	userEmail VARCHAR(255) NOT NULL, 
	validTo DATETIME DEFAULT NULL, 
	INDEX userId (userId), 
	PRIMARY KEY(id)) 
DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB;

CREATE TABLE rbac_role (
	role_id INT AUTO_INCREMENT NOT NULL, 
	parent_role_id INT DEFAULT NULL, 
	role_name VARCHAR(32) NOT NULL, 
	INDEX IDX_C55D6FF2A44B56EA (parent_role_id), 
	PRIMARY KEY(role_id)) 
DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB;

CREATE TABLE user (
	user_id INT AUTO_INCREMENT NOT NULL, 
	username VARCHAR(255) DEFAULT NULL, 
	email VARCHAR(255) DEFAULT NULL, 
	display_name VARCHAR(50) DEFAULT NULL, 
	password VARCHAR(128) NOT NULL, 
	state SMALLINT DEFAULT NULL, 
	loginFailures INT NOT NULL, 
	UNIQUE INDEX UNIQ_8D93D649F85E0677 (username), 
	--UNIQUE INDEX UNIQ_8D93D649E7927C74 (email), 
	PRIMARY KEY(user_id)) 
DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB;

CREATE TABLE user_rbac_role (
	user_id INT NOT NULL, 
	role_id INT NOT NULL, 
	INDEX IDX_C5BDE216A76ED395 (user_id), 
	INDEX IDX_C5BDE216D60322AC (role_id), 
	PRIMARY KEY(user_id, role_id)) 
DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `user_rbac_permissions` (
  `user_id` int(11) NOT NULL,
  `permission` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `filter` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  KEY `user_id` (`user_id`),
  KEY `permission` (`permission`),
  KEY `filter` (`filter`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

ALTER TABLE `rbac_permission` ADD CONSTRAINT `FK_70964D43D60322AC` FOREIGN KEY (`role_id`) REFERENCES `rbac_role`(`role_id`) ON DELETE RESTRICT ON UPDATE RESTRICT
ALTER TABLE rbac_role ADD CONSTRAINT FK_C55D6FF2A44B56EA FOREIGN KEY (parent_role_id) REFERENCES rbac_role (role_id);
ALTER TABLE user_rbac_role ADD CONSTRAINT FK_C5BDE216A76ED395 FOREIGN KEY (user_id) REFERENCES user (user_id);
ALTER TABLE user_rbac_role ADD CONSTRAINT FK_C5BDE216D60322AC FOREIGN KEY (role_id) REFERENCES rbac_role (role_id);


delimiter $$

CREATE PROCEDURE `build_role_rbac_permissions`(IN `param_role_id` INT)
    NO SQL
BEGIN
	DECLARE finished INT;
	DECLARE role_iteration INT; 
	DECLARE roles_cursor CURSOR FOR SELECT role_id FROM rbac_role  WHERE parent_role_id = param_role_id;
	DECLARE CONTINUE HANDLER 
	FOR NOT FOUND SET finished = 1;

	CREATE TEMPORARY TABLE IF NOT EXISTS build_permissions (permission varchar(100), filter varchar(100));
	
	INSERT INTO build_permissions
	SELECT perm_name, perm_filter FROM rbac_permission
    WHERE  role_id = param_role_id;
	
	set finished = 0;
	SET max_sp_recursion_depth=255;

	OPEN roles_cursor;
		
	processRole: LOOP
		FETCH roles_cursor INTO role_iteration;
		
		IF finished = 1 THEN 
			LEAVE processRole;
		END IF;
			
		CALL build_role_rbac_permissions (role_iteration);
	END LOOP processRole;
	
    CLOSE roles_cursor;
    
END$$

delimiter ;

delimiter $$
CREATE PROCEDURE `build_role_users_rbac_permissions`(IN `param_role_id` INT)
    NO SQL
BEGIN
	DECLARE parent_id INT;
	DECLARE finished INT;
	DECLARE user_iteration INT; 
	DECLARE users_cursor CURSOR FOR SELECT user_id FROM user_rbac_role  WHERE role_id = param_role_id;
	DECLARE CONTINUE HANDLER 
	FOR NOT FOUND SET finished = 1;

	set finished = 0;
	SET max_sp_recursion_depth=255;
    
    OPEN users_cursor;
		
	processRole: LOOP
		FETCH users_cursor INTO user_iteration;
		
		IF finished = 1 THEN 
			LEAVE processRole;
		END IF;
			
		CALL build_user_rbac_permissions (user_iteration);
	END LOOP processRole;
        
    CLOSE users_cursor;
    
    SELECT parent_role_id from rbac_role where role_id = param_role_id
    INTO parent_id;
    
    IF(NOT parent_id IS NULL) THEN
    	CALL build_role_users_rbac_permissions(parent_id);
    END IF;
END$$

delimiter ;

delimiter $$
CREATE PROCEDURE `build_user_rbac_permissions`(IN `param_user_id` INT)
    NO SQL
BEGIN
	DECLARE finished INT;
	DECLARE role_iteration INT; 
	DECLARE roles_cursor CURSOR FOR SELECT role_id FROM user_rbac_role  WHERE user_id = param_user_id;
	DECLARE CONTINUE HANDLER 
	FOR NOT FOUND SET finished = 1;

	set finished = 0;
	SET max_sp_recursion_depth=255;

	DELETE FROM user_rbac_permissions
	WHERE user_id = param_user_id;

	OPEN roles_cursor;
		
	processRole: LOOP
		FETCH roles_cursor INTO role_iteration;
		
		IF finished = 1 THEN 
			LEAVE processRole;
		END IF;
			
		CALL build_role_rbac_permissions (role_iteration);
	END LOOP processRole;
        
    CLOSE roles_cursor;
    
    INSERT INTO user_rbac_permissions (user_id, permission, filter)
    SELECT param_user_id, permission, filter FROM build_permissions;
END$$

delimiter ;

DROP TABLE IF EXISTS `user_rbac_permissions`;
CREATE TABLE IF NOT EXISTS `user_rbac_permissions` (
  `user_id` int(11) NOT NULL,
  `permission` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `filter` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  KEY `user_id` (`user_id`),
  KEY `permission` (`permission`),
  KEY `filter` (`filter`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;