SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[cs_system_UpdateUserSubscriptions]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[cs_system_UpdateUserSubscriptions]
GO

CREATE PROCEDURE [dbo].cs_system_UpdateUserSubscriptions
(
	@UserID int,
	@SettingsID int
)
AS
SET Transaction Isolation Level Read UNCOMMITTED
BEGIN

	DECLARE @SectionID int
	DECLARE @HasAccess bit
	
	DECLARE sections_cursor CURSOR FOR
	SELECT T.SectionID
	FROM cs_TrackedThreads TT INNER JOIN cs_Threads T ON TT.ThreadID = T.ThreadID
	WHERE TT.UserID = @UserID AND TT.SettingsID = @SettingsID
	UNION
	SELECT TS.SectionID
	FROM cs_TrackedSections TS
	WHERE TS.UserID = @UserID AND TS.SettingsID = @SettingsID
	
	OPEN sections_cursor

	FETCH NEXT FROM sections_cursor 
	INTO @SectionID

	WHILE @@FETCH_STATUS = 0
	BEGIN
		-- Get user's view & read access permissions status
		EXEC @HasAccess = cs_system_UserCanReadSection @UserID, @SectionID, @SettingsID
		
		-- If no access then clean up subscriptions
		IF @HasAccess = 0
		BEGIN
			-- Remove forum subscriptions
			EXEC cs_RemoveAllSectionTracking @UserID, @SectionID, @SettingsID

			-- Remove thread subscriptions
			EXEC cs_RemoveAllPostTracking @UserID, @SectionID, @SettingsID
		END

		FETCH NEXT FROM sections_cursor
		INTO @SectionID
	END

	CLOSE sections_cursor
	DEALLOCATE sections_cursor

END

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

grant execute on [dbo].[cs_system_UpdateUserSubscriptions] to public

