IF EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID('METADATA_DROP_PROCEDURE') AND TYPE = 'P')
BEGIN
	DROP PROCEDURE METADATA_DROP_PROCEDURE
END
GO
CREATE PROCEDURE METADATA_DROP_PROCEDURE
(
	@procedureName NVARCHAR(255)
)
AS
BEGIN		
	IF  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(@procedureName) AND TYPE ='P')
	BEGIN    
		EXEC('DROP PROCEDURE ' + @procedureName)
	END
END
GO

EXEC METADATA_DROP_PROCEDURE @procedureName	= 'METADATA_ROLE_PERMISSION'
GO
CREATE PROCEDURE [METADATA_ROLE_PERMISSION]
	@perm_id		INT, 
	@role_id		INT,
	@is_audit		INT	
AS
BEGIN
	IF EXISTS (SELECT 1 FROM [dbo].[AFW_ROLE_PERMISSION] WHERE [perm_id] = @perm_id AND [role_id] = @role_id)
		UPDATE [dbo].[AFW_ROLE_PERMISSION]
		SET [perm_id] = @perm_id,
			[role_id] = @role_id,
			[is_audit] = @is_audit			
		WHERE [perm_id] = @perm_id AND [role_id] = @role_id
	ELSE
		INSERT INTO [dbo].[AFW_ROLE_PERMISSION](
			[perm_id], 
			[role_id],
			[is_audit]
		)VALUES(
			@perm_id,
			@role_id, 
			@is_audit
		)
END
GO


EXEC METADATA_ROLE_PERMISSION 11, 1, 1
EXEC METADATA_ROLE_PERMISSION 59, 1, 1
EXEC METADATA_ROLE_PERMISSION 21, 1, 1
EXEC METADATA_ROLE_PERMISSION 65, 1, 1
EXEC METADATA_ROLE_PERMISSION 70, 1, 1
EXEC METADATA_ROLE_PERMISSION 80, 1, 1
EXEC METADATA_ROLE_PERMISSION 321200, 1, 1
EXEC METADATA_ROLE_PERMISSION 321500, 1, 1
EXEC METADATA_ROLE_PERMISSION 321600, 1, 1
EXEC METADATA_ROLE_PERMISSION 321800, 1, 1
EXEC METADATA_ROLE_PERMISSION 322200, 1, 1
EXEC METADATA_ROLE_PERMISSION 322400, 1, 1
EXEC METADATA_ROLE_PERMISSION 31000, 1, 1
EXEC METADATA_ROLE_PERMISSION 323100, 1, 1
EXEC METADATA_ROLE_PERMISSION 30000, 1, 1
EXEC METADATA_ROLE_PERMISSION 30002, 1, 1
EXEC METADATA_ROLE_PERMISSION 30004, 1, 1
EXEC METADATA_ROLE_PERMISSION 6000, 1, 1
EXEC METADATA_ROLE_PERMISSION 323100, 1, 1
EXEC METADATA_ROLE_PERMISSION 323110, 1, 1
EXEC METADATA_ROLE_PERMISSION 323120, 1, 1
EXEC METADATA_ROLE_PERMISSION 323130, 1, 1
EXEC METADATA_ROLE_PERMISSION 323140, 1, 1
EXEC METADATA_ROLE_PERMISSION 323150, 1, 1
EXEC METADATA_ROLE_PERMISSION 323160, 1, 1
EXEC METADATA_ROLE_PERMISSION 323170, 1, 1
EXEC METADATA_ROLE_PERMISSION 323180, 1, 1
EXEC METADATA_ROLE_PERMISSION 323190, 1, 1
EXEC METADATA_ROLE_PERMISSION 323200, 1, 1
EXEC METADATA_ROLE_PERMISSION 323210, 1, 1
EXEC METADATA_ROLE_PERMISSION 323220, 1, 1
GO

DECLARE @idRoleAdministrator INT
SELECT @idRoleAdministrator = role_id 
FROM [dbo].[AFW_ROLES] 
WHERE 
	[role_name] = 'Administrator' AND
	[role_application_id] = 0
	
--ManageUser
EXEC [METADATA_ROLE_PERMISSION] 2, @idRoleAdministrator, 1
EXEC [METADATA_ROLE_PERMISSION] 3, @idRoleAdministrator, 1
EXEC [METADATA_ROLE_PERMISSION] 4, @idRoleAdministrator, 1
EXEC [METADATA_ROLE_PERMISSION] 5, @idRoleAdministrator, 1

--ManageGroup
EXEC [METADATA_ROLE_PERMISSION] 7, @idRoleAdministrator, 1
EXEC [METADATA_ROLE_PERMISSION] 8, @idRoleAdministrator, 1
EXEC [METADATA_ROLE_PERMISSION] 9, @idRoleAdministrator, 1
EXEC [METADATA_ROLE_PERMISSION] 10, @idRoleAdministrator, 1

--ManageProject
EXEC [METADATA_ROLE_PERMISSION] 12, @idRoleAdministrator, 1
EXEC [METADATA_ROLE_PERMISSION] 13, @idRoleAdministrator, 1
EXEC [METADATA_ROLE_PERMISSION] 14, @idRoleAdministrator, 1
EXEC [METADATA_ROLE_PERMISSION] 15, @idRoleAdministrator, 1

--ManageRol
EXEC [METADATA_ROLE_PERMISSION] 17, @idRoleAdministrator, 1
EXEC [METADATA_ROLE_PERMISSION] 18, @idRoleAdministrator, 1
EXEC [METADATA_ROLE_PERMISSION] 19, @idRoleAdministrator, 1
EXEC [METADATA_ROLE_PERMISSION] 20, @idRoleAdministrator, 1

--ManageAuthorization
EXEC [METADATA_ROLE_PERMISSION] 22, @idRoleAdministrator, 1
EXEC [METADATA_ROLE_PERMISSION] 23, @idRoleAdministrator, 1
EXEC [METADATA_ROLE_PERMISSION] 24, @idRoleAdministrator, 1	

--ManageAuthProvider
EXEC [METADATA_ROLE_PERMISSION] 27, @idRoleAdministrator, 1
EXEC [METADATA_ROLE_PERMISSION] 28, @idRoleAdministrator, 1
EXEC [METADATA_ROLE_PERMISSION] 29, @idRoleAdministrator, 1
EXEC [METADATA_ROLE_PERMISSION] 30, @idRoleAdministrator, 1

--ManageConfigLDAP
EXEC [METADATA_ROLE_PERMISSION] 32, @idRoleAdministrator, 1
EXEC [METADATA_ROLE_PERMISSION] 33, @idRoleAdministrator, 1
EXEC [METADATA_ROLE_PERMISSION] 34, @idRoleAdministrator, 1
EXEC [METADATA_ROLE_PERMISSION] 35, @idRoleAdministrator, 1

--ManageMail
EXEC [METADATA_ROLE_PERMISSION] 48, @idRoleAdministrator, 1

---ManageMailAccount
EXEC [METADATA_ROLE_PERMISSION] 50, @idRoleAdministrator, 1
EXEC [METADATA_ROLE_PERMISSION] 51, @idRoleAdministrator, 1
EXEC [METADATA_ROLE_PERMISSION] 52, @idRoleAdministrator, 1
EXEC [METADATA_ROLE_PERMISSION] 53, @idRoleAdministrator, 1

--ManageMailTemplate
EXEC [METADATA_ROLE_PERMISSION] 55, @idRoleAdministrator, 1
EXEC [METADATA_ROLE_PERMISSION] 56, @idRoleAdministrator, 1
EXEC [METADATA_ROLE_PERMISSION] 57, @idRoleAdministrator, 1
EXEC [METADATA_ROLE_PERMISSION] 58, @idRoleAdministrator, 1

--ManageReport
EXEC [METADATA_ROLE_PERMISSION] 60, @idRoleAdministrator, 1
EXEC [METADATA_ROLE_PERMISSION] 61, @idRoleAdministrator, 1
EXEC [METADATA_ROLE_PERMISSION] 62, @idRoleAdministrator, 1
EXEC [METADATA_ROLE_PERMISSION] 63, @idRoleAdministrator, 1
EXEC [METADATA_ROLE_PERMISSION] 64, @idRoleAdministrator, 1

--ManageRules
EXEC [METADATA_ROLE_PERMISSION] 66, @idRoleAdministrator, 1
EXEC [METADATA_ROLE_PERMISSION] 67, @idRoleAdministrator, 1
EXEC [METADATA_ROLE_PERMISSION] 68, @idRoleAdministrator, 1
EXEC [METADATA_ROLE_PERMISSION] 69, @idRoleAdministrator, 1

--ManageDashBoard
EXEC [METADATA_ROLE_PERMISSION] 71, @idRoleAdministrator, 1
EXEC [METADATA_ROLE_PERMISSION] 72, @idRoleAdministrator, 1
EXEC [METADATA_ROLE_PERMISSION] 73, @idRoleAdministrator, 1
EXEC [METADATA_ROLE_PERMISSION] 74, @idRoleAdministrator, 1

---ManageCategory
EXEC [METADATA_ROLE_PERMISSION] 76, @idRoleAdministrator, 1
EXEC [METADATA_ROLE_PERMISSION] 77, @idRoleAdministrator, 1
EXEC [METADATA_ROLE_PERMISSION] 78, @idRoleAdministrator, 1
EXEC [METADATA_ROLE_PERMISSION] 79, @idRoleAdministrator, 1

---Manage Alert
EXEC [METADATA_ROLE_PERMISSION] 81, @idRoleAdministrator, 1
EXEC [METADATA_ROLE_PERMISSION] 82, @idRoleAdministrator, 1
EXEC [METADATA_ROLE_PERMISSION] 83, @idRoleAdministrator, 1
EXEC [METADATA_ROLE_PERMISSION] 84, @idRoleAdministrator, 1

---Manage Comment
EXEC [METADATA_ROLE_PERMISSION] 86, @idRoleAdministrator, 1
EXEC [METADATA_ROLE_PERMISSION] 87, @idRoleAdministrator, 1

---Manage Template
EXEC [METADATA_ROLE_PERMISSION] 89, @idRoleAdministrator, 1
EXEC [METADATA_ROLE_PERMISSION] 90, @idRoleAdministrator, 1
EXEC [METADATA_ROLE_PERMISSION] 91, @idRoleAdministrator, 1
EXEC [METADATA_ROLE_PERMISSION] 92, @idRoleAdministrator, 1

---Manage Company
EXEC [METADATA_ROLE_PERMISSION] 94, @idRoleAdministrator, 1
EXEC [METADATA_ROLE_PERMISSION] 95, @idRoleAdministrator, 1
EXEC [METADATA_ROLE_PERMISSION] 96, @idRoleAdministrator, 1
EXEC [METADATA_ROLE_PERMISSION] 97, @idRoleAdministrator, 1

---Manage Location
EXEC [METADATA_ROLE_PERMISSION] 99, @idRoleAdministrator, 1
EXEC [METADATA_ROLE_PERMISSION] 100, @idRoleAdministrator, 1
EXEC [METADATA_ROLE_PERMISSION] 101, @idRoleAdministrator, 1
EXEC [METADATA_ROLE_PERMISSION] 102, @idRoleAdministrator, 1



---Manage Additionals Fields
EXEC [METADATA_ROLE_PERMISSION] 109, @idRoleAdministrator, 1
EXEC [METADATA_ROLE_PERMISSION] 110, @idRoleAdministrator, 1
EXEC [METADATA_ROLE_PERMISSION] 111, @idRoleAdministrator, 1
EXEC [METADATA_ROLE_PERMISSION] 112, @idRoleAdministrator, 1
GO

EXEC METADATA_DROP_PROCEDURE @procedureName	= 'METADATA_POPULATE_ROLE_PERMISION'
GO
CREATE PROCEDURE METADATA_POPULATE_ROLE_PERMISION
AS
BEGIN
    DECLARE @ParentId as INT;
    DECLARE @ChildId as INT;
    DECLARE @ParentRole as INT;

    DECLARE @ParentRoleCursor as CURSOR;
    DECLARE @PerChildCursor as CURSOR;
    
    SET @ParentRoleCursor = CURSOR FOR SELECT aper.perm_id,  arpe.role_id
                                         FROM dbo.AFW_PERMISSIONS aper JOIN dbo.AFW_ROLE_PERMISSION arpe 
                                                  ON aper.perm_id = arpe.perm_id
                                        WHERE perm_parent IS NULL;
    OPEN @ParentRoleCursor;
    FETCH NEXT FROM @ParentRoleCursor INTO @ParentId, @ParentRole;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @PerChildCursor = CURSOR FOR SELECT perm_id 
											FROM dbo.AFW_PERMISSIONS 
                                          	WHERE perm_parent = @ParentId 
											AND perm_id NOT IN (30002, 30004);
        OPEN @PerChildCursor;
        FETCH NEXT FROM @PerChildCursor INTO @ChildId;

		WHILE @@FETCH_STATUS = 0
		BEGIN
			EXEC METADATA_ROLE_PERMISSION @ChildId, @ParentRole, 1;
			FETCH NEXT FROM @PerChildCursor INTO @ChildId;
		END

		FETCH NEXT FROM @ParentRoleCursor INTO @ParentId, @ParentRole;
	END
END
GO

EXEC METADATA_POPULATE_ROLE_PERMISION
GO

EXEC METADATA_DROP_PROCEDURE @procedureName	= 'METADATA_ROLE_ACCESS'
GO

CREATE PROCEDURE METADATA_ROLE_ACCESS
	@grou_id	INT,
	@role_id	INT,
	@proj_id	INT,
	@user_id	INT,
	@roac_proj_deny	INT,
	@roac_id        INT OUTPUT
AS
BEGIN
	
	IF NOT EXISTS (
		SELECT	1 
		FROM	[dbo].[AFW_ROLE_ACCESS] 
		WHERE	([roac_proj_id] = @proj_id or [roac_proj_id] is null)
				AND ([roac_grou_id] = @grou_id  or [roac_grou_id] is  null)
				AND ([roac_role_id] = @role_id  or [roac_role_id] is null)
				AND ([roac_user_id] = @user_id or [roac_user_id] is null)
				AND [roac_proj_deny] = @roac_proj_deny)
		
		INSERT INTO [dbo].[AFW_ROLE_ACCESS](
			[roac_proj_id],
			[roac_grou_id],
			[roac_role_id],
			[roac_user_id],
			[roac_proj_deny]
		)VALUES(
			@proj_id,
			@grou_id,
			@role_id,
			@user_id,
			0
		)
		
		SET @roac_id = SCOPE_IDENTITY();
END
GO

DECLARE @idGroupAdministrator INT
SELECT @idGroupAdministrator = grou_id 
FROM [dbo].[AFW_GROUPS] 
WHERE [grou_name] = 'Administrators'

DECLARE @idRoleAdministrator INT
SELECT @idRoleAdministrator = role_id 
FROM [dbo].[AFW_ROLES] 
WHERE 
	[role_name] = 'Administrator' AND
	[role_application_id] = 0


DECLARE @idServiceUser INT
SELECT @idServiceUser = user_id 
FROM [dbo].[AFW_USERS] 
WHERE [user_name] = 'ARANDA SERVICES USER'
	
EXEC [METADATA_ROLE_ACCESS] @idGroupAdministrator, @idRoleAdministrator, NULL, NULL,0, NULL
EXEC [METADATA_ROLE_ACCESS] NULL, @idRoleAdministrator, NULL, @idServiceUser,0, NULL
GO

EXEC METADATA_DROP_PROCEDURE @procedureName	= 'METADATA_POPULATE_ROLE_PERMISION'
GO
EXEC METADATA_DROP_PROCEDURE @procedureName	= 'METADATA_ROLE_PERMISSION'
GO
EXEC METADATA_DROP_PROCEDURE @procedureName	= 'METADATA_ROLE_ACCESS'
GO
EXEC METADATA_DROP_PROCEDURE @procedureName	= 'METADATA_DROP_PROCEDURE'
GO