ALTER PROCEDURE [dbo].[NBAddOrUpdateItem] 
	@principal int,
	@isWizdomOrNoticeboardAdmin bit,
	@targetChannels nvarchar(max),
	@itemId int null = null,
	@heading nvarchar(max),
	@summary nvarchar(max) = null,
	@content nvarchar(max),
	@contentTypeId int,
	@translatedValues nvarchar(max),
	@extendedProperties nvarchar(max),
	@externalId nvarchar(max) = null,
	@authorPrincipalId int,
	@alternateAuthorPrincipalId int null = null,
	@startDate datetimeoffset,
	@endDate datetimeoffset,
	@modifiedDate datetimeoffset,
	@createdDate datetimeoffset null = null,
	@resetReadstatus bit,
	@isFuture bit
AS
BEGIN
	select Groups_PrincipalID into #mygroups  from PrincipalGrouping where Members_PrincipalID = @principal
	insert into #mygroups values(@principal)
	insert into #mygroups values(1) -- everyone

	-- Prepare the XML doc, so we can use it with OPENXML
	DECLARE @targetChannelsDoc int
	EXEC sp_xml_preparedocument @targetChannelsDoc OUTPUT, @targetChannels;  

;WITH ChannelRightsMap as (
	SELECT 
		ChannelID, 
		ParentChannelId,
		ChannelID as RightsChannel, 
		NB_Channel.CanContainItems,
		NB_Channel.CanOnlyEditItsOwn,
		NB_Channel.BreakRightsInherit
	from NB_Channel where BreakRightsInherit = 1
	
	UNION ALL
	
	SELECT 
		NB_Channel.ChannelID,
		NB_Channel.ParentChannelId,
		ChannelRightsMap.RightsChannel,
		NB_Channel.CanContainItems,
		NB_Channel.CanOnlyEditItsOwn,
		NB_Channel.BreakRightsInherit
	from NB_Channel
	inner join ChannelRightsMap on ChannelRightsMap.ChannelID = NB_Channel.ParentChannelId
	where NB_Channel.BreakRightsInherit = 0 

),
AllowedChannels as ( -- filtered by read rights for the current @principal
	select 
		ChannelRightsMap.ChannelID, 
		case -- if user is admin, he'll get write access
			when @isWizdomOrNoticeboardAdmin = 1 then 2  
			else MAX(NB_ChannelRights.Level)
		end as RightsLevel, 
		ChannelRightsMap.CanContainItems,
		ChannelRightsMap.BreakRightsInherit,
		ISNULL(RightsChannel.CanOnlyEditItsOwn,0) as CanOnlyEditItsOwn
	from 
		ChannelRightsMap
		LEFT OUTER JOIN NB_ChannelRights on NB_ChannelRights.ChannelID = ChannelRightsMap.RightsChannel AND BreakRightsInherit = 1
		INNER JOIN NB_Channel as RightsChannel on RightsChannel.ChannelID = ChannelRightsMap.RightsChannel 
		LEFT OUTER JOIN #mygroups on NB_ChannelRights.PrincipalID = #mygroups.Groups_PrincipalID
	where
		(
			@isWizdomOrNoticeboardAdmin = 1 or -- is admin
			#mygroups.Groups_PrincipalID is not null
		)
	group by 
		ChannelRightsMap.ChannelID, 
		ChannelRightsMap.CanContainItems,
		ChannelRightsMap.BreakRightsInherit,
		RightsChannel.CanOnlyEditItsOwn
)
SELECT 
	TargetChannelId,
	CanContainItems,
	CanOnlyEditItsOwn,
	CASE WHEN  ChannelID IS NOT NULL THEN 1
	ELSE 0 END as HaveWriteAccess
INTO 
	#TargetChannels
FROM 
	OPENXML (@targetChannelsDoc, '/Cs/C') WITH(TargetChannelId int '.')
	--OPENJSON(@targetChannels) WITH(TargetChannelId int '$') 
	LEFT OUTER JOIN AllowedChannels on AllowedChannels.ChannelID = TargetChannelId AND AllowedChannels.RightsLevel = 2

-- ##### REPORT EXCEPTIONS #####
-- Check write access
IF(EXISTS(SELECT TargetChannelId from #TargetChannels WHERE HaveWriteAccess != 1))
BEGIN
	SELECT
		'Missing write access' as ErrorMessage,
		TargetChannelId as ChannelId 
	from #TargetChannels WHERE HaveWriteAccess != 1
	RETURN
END

-- check if channels can contain items
IF(EXISTS(SELECT TargetChannelId from #TargetChannels WHERE CanContainItems != 1))
BEGIN
	SELECT
		'Channel cannot contain items' as ErrorMessage,
		TargetChannelId as ChannelId 
	from #TargetChannels WHERE CanContainItems != 1
	RETURN
END

-- check if author is a user type
IF(EXISTS(SELECT PrincipalID FROM Principal where PrincipalID = @authorPrincipalId AND (Type <> 1 OR PrincipalID < 4)))
BEGIN
	SELECT 'Author should be a user' as ErrorMessage
	RETURN
END

-- check if alternate author is a user type
IF(EXISTS(SELECT PrincipalID FROM Principal where PrincipalID = @alternateAuthorPrincipalId AND (Type <> 1 OR PrincipalID < 4)))
BEGIN
	SELECT 'Alternate Author should be a user' as ErrorMessage
	RETURN
END

-- check if canOnlyEditItsOwn
IF(@itemId is not null AND @isWizdomOrNoticeboardAdmin = 0 AND
	EXISTS(select TargetChannelID from #TargetChannels WHERE CanOnlyEditItsOwn = 1) AND
	NOT EXISTS(select ItemId from NB_Item WHERE ItemID = @itemId AND (AuthorID = @principal or AlternateAuthorID = @principal))
)
BEGIN
	select 
		'Can only edit own items' as ErrorMessage,
		TargetChannelID 
	from #TargetChannels WHERE CanOnlyEditItsOwn = 1
	RETURN
END

-- check if the selected contenttype is allowed in all target channels
IF(EXISTS(select *
	from #TargetChannels
	where
		exists(SELECT * from NB_Channel_ContentType where NB_Channel_ContentType.NB_Channel_ChannelID = #TargetChannels.TargetChannelId) and
		not exists(SELECT * from NB_Channel_ContentType where NB_Channel_ContentType.NB_Channel_ChannelID = #TargetChannels.TargetChannelId and NB_Channel_ContentType.NB_ContentType_ContentTypeID = @contentTypeId)))
BEGIN
	SELECT 'Contenttype is not allowed in one or more channels' as ErrorMessage
	RETURN
END

-- All good
BEGIN
	SELECT 'No exceptions' as ErrorMessage
END


-- ##### handle updates #####
IF(@itemId is not null)
BEGIN
	-- IF this is an update, we should remove bindings to channels, that the item should no longer belong to.
	DELETE FROM NB_Channel_Item 
	WHERE 
		NB_Item_ItemID = @itemId
		AND NB_Channel_ChannelID not in (SELECT TargetChannelId FROM #TargetChannels)

	-- ADD new channel bindings
	INSERT INTO NB_Channel_Item (NB_Channel_ChannelID, NB_Item_ItemID)
	SELECT 
		TargetChannelId, 
		@itemId 
	FROM #TargetChannels
	WHERE
		TargetChannelId not in (SELECT NB_Channel_ChannelID FROM NB_Channel_Item WHERE NB_Channel_Item.NB_Item_ItemID = @itemId)

	-- Update the item
	UPDATE NB_Item
	SET 
		Heading = @heading,
		Summary = @summary,
		Content = @content,
		ContentTypeID = @contentTypeId,
		TranslatedValues = @translatedValues,
		ExtendedProperties = @extendedProperties,
		ExternalID = @externalId,
		AuthorID = @authorPrincipalId,
		AlternateAuthorID = @alternateAuthorPrincipalId,
		Modified = @modifiedDate,
		Startdate = @startDate,
		Enddate = @endDate
	WHERE NB_Item.ItemID = @itemId
	
END
-- ##### handle created #####
IF(@itemId is null)
BEGIN
	INSERT INTO NB_Item(
		Startdate,
		Enddate,
		Heading,
		Content,
		AuthorID,
		AlternateAuthorID,
		ExtendedProperties,
		ContentTypeID,
		ExternalID,
		NotificationStatus,
		Created,
		Modified,
		Summary,
		TranslatedValues
	)
	VALUES(
		@startDate, 
		@endDate, 
		@heading,
		@content,
		@authorPrincipalId,
		@alternateAuthorPrincipalId,
		@extendedProperties,
		@contentTypeId,
		@externalId,
		CASE 
			WHEN @isFuture = 1 THEN 1
			ELSE 2
		END,
		@createdDate,
		@modifiedDate,
		@summary,
		@translatedValues
	)
	SELECT @itemId = @@IDENTITY
	
	-- ADD channel bindings
	INSERT INTO NB_Channel_Item (NB_Channel_ChannelID, NB_Item_ItemID)
	SELECT 
		TargetChannelId, 
		@itemId 
	FROM #TargetChannels
END

IF(@resetReadstatus = 1 AND @itemId is not null)
BEGIN
	DELETE FROM NB_Read WHERE ItemID = @itemId
END
SELECT @itemId as ItemId
END