SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

DECLARE @Major int, @Minor int, @Patch int, @Installed DateTime, @Prereqs int

Set @Major = 4;
Set @Minor = 1;
Set @Patch = 5;

Select @Prereqs = isnull(Count(InstallDate),0)  from cs_SchemaVersion where Major=@Major and Minor=@Minor and Patch<@Patch

Select @Installed = InstallDate  from cs_SchemaVersion where Major=@Major and Minor=@Minor and Patch=@Patch

If(@Installed is null AND @Prereqs = @Patch)
	BEGIN
--## Schema Patch ##

exec('
DECLARE @ApplicationID uniqueidentifier
DECLARE @SettingsID int
DECLARE @Perm_RoleID uniqueidentifier

DECLARE settingsCursor CURSOR FOR
select ss.SettingsID, aa.ApplicationId
from cs_SiteSettings ss
inner join aspnet_Applications aa on ss.ApplicationName = aa.ApplicationName

OPEN settingsCursor

FETCH NEXT FROM settingsCursor
INTO @SettingsID, @ApplicationID

WHILE @@FETCH_STATUS = 0
BEGIN
	IF NOT EXISTS (SELECT * FROM [aspnet_Roles] where [ApplicationId] = @ApplicationID and [LoweredRoleName] = ''wikiadministrator'') BEGIN
		INSERT INTO [aspnet_Roles] ([RoleId], [ApplicationId], [RoleName], [LoweredRoleName], [Description]) VALUES (newID(), @ApplicationID, N''WikiAdministrator'', N''wikiadministrator'', N''Administration role for Wikis'')
	END
	
	IF NOT EXISTS (SELECT * FROM [aspnet_Roles] where [ApplicationId] = @ApplicationID and [LoweredRoleName] = ''wikimoderator'') BEGIN
		INSERT INTO [aspnet_Roles] ([RoleId], [ApplicationId], [RoleName], [LoweredRoleName], [Description]) VALUES (newID(), @ApplicationID, N''WikiModerator'', N''wikimoderator'', N''Moderator role for Wikis'')
	END
	
	IF NOT EXISTS (SELECT * FROM [aspnet_Roles] where [ApplicationId] = @ApplicationID and [LoweredRoleName] = ''wikicontributor'') BEGIN
		INSERT INTO [aspnet_Roles] ([RoleId], [ApplicationId], [RoleName], [LoweredRoleName], [Description]) VALUES (newID(), @ApplicationID, N''WikiContributor'', N''wikicontributor'', N''Contributor role for Wikis'')
	END

	Select @Perm_RoleID = RoleId from aspnet_Roles where ApplicationId = @ApplicationID and RoleName = ''Everyone''
	INSERT INTO [cs_ProductPermissions] ([SettingsID], [ApplicationType], [RoleID], [AllowMask], [DenyMask]) values (@SettingsID, 53, @Perm_RoleID, 0x000000000000000B, 0x0000700000000034 )

	Select @Perm_RoleID = RoleId from aspnet_Roles where ApplicationId = @ApplicationID and RoleName = ''Registered Users''
	INSERT INTO [cs_ProductPermissions] ([SettingsID], [ApplicationType], [RoleID], [AllowMask], [DenyMask]) values (@SettingsID, 53, @Perm_RoleID, 0x000020000000001F, 0xFFFFDFFFFFFFFFE0 )

	Select @Perm_RoleID = RoleId from aspnet_Roles where ApplicationId = @ApplicationID and RoleName = ''WikiContributor''
	INSERT INTO [cs_ProductPermissions] ([SettingsID], [ApplicationType], [RoleID], [AllowMask], [DenyMask]) values (@SettingsID, 53, @Perm_RoleID, 0x000020000000001F, 0xFFFFDFFFFFFFFFE0 )
	
	Select @Perm_RoleID = RoleId from aspnet_Roles where ApplicationId = @ApplicationID and RoleName = ''WikiModerator''
	INSERT INTO [cs_ProductPermissions] ([SettingsID], [ApplicationType], [RoleID], [AllowMask], [DenyMask]) values (@SettingsID, 53, @Perm_RoleID, 0x000070000000003F, 0xFFFF8FFFFFFFFFC0 )

	Select @Perm_RoleID = RoleId from aspnet_Roles where ApplicationId = @ApplicationID and RoleName = ''WikiAdministrator''
	INSERT INTO [cs_ProductPermissions] ([SettingsID], [ApplicationType], [RoleID], [AllowMask], [DenyMask]) values (@SettingsID, 53, @Perm_RoleID, 0x010070000000003F, 0 )

	Select @Perm_RoleID = RoleId from aspnet_Roles where ApplicationId = @ApplicationID and RoleName = ''SystemAdministrator''
	INSERT INTO [cs_ProductPermissions] ([SettingsID], [ApplicationType], [RoleID], [AllowMask], [DenyMask]) values (@SettingsID, 53, @Perm_RoleID, 0x010070000000003F, 0 )

	FETCH NEXT FROM settingsCursor
	INTO @SettingsID, @ApplicationID
END

CLOSE settingsCursor
DEALLOCATE settingsCursor
')

--## END Schema Patch ##
Insert into cs_SchemaVersion(Major, Minor, Patch, InstallDate) values (@Major, @Minor, @Patch, GetDate())

Print 'Schema Patch v' + Convert(Varchar(2),@Major) + '.' + Convert(Varchar(2),@Minor) + '.' +  Convert(Varchar(3),@Patch) + ' was applied successfully '

	END
ELSE IF(@Installed is not null)
	BEGIN
Print 'Schema Patch v' + Convert(Varchar(2),@Major) + '.' + Convert(Varchar(2),@Minor) + '.' +  Convert(Varchar(3),@Patch) + ' was already applied on ' + Convert(varchar(50), @Installed)  
	END 
ELSE
	BEGIN
Print 'The patch could not be applied because your current schema is missing previous updates (Schema Patch v' + Convert(Varchar(2),@Major) + '.' + Convert(Varchar(2),@Minor) + '.' +  Convert(Varchar(3),@Patch) + ')' 
	END 
