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 null = null,
	@alternateAuthorPrincipalId int null = null,
	@startDate datetimeoffset,
	@endDate datetimeoffset,
	@modifiedDate datetimeoffset,
	@createdDate datetimeoffset null = null,
	@searchableText nvarchar(max),
	@resetReadstatus bit,
	@isFuture bit
AS
BEGIN

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



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 NB_GetMyChannels(@principal,@isWizdomOrNoticeboardAdmin,null,null) as 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(@itemId is null and EXISTS(SELECT PrincipalID FROM Principal where PrincipalID = @authorPrincipalId AND (Type <> 1 OR PrincipalID < 2)))
BEGIN
	SELECT 'Author should be a user' as ErrorMessage
	RETURN
END

-- if its a new item, we need an author
IF(@itemId is null AND NOT EXISTS(SELECT PrincipalID FROM Principal where PrincipalID = @authorPrincipalId))
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 < 2)))
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,
		SearchableText = @searchableText,
		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,
		SearchableText
	)
	VALUES(
		@startDate, 
		@endDate, 
		@heading,
		@content,
		@authorPrincipalId,
		@alternateAuthorPrincipalId,
		@extendedProperties,
		@contentTypeId,
		@externalId,
		CASE 
			WHEN @isFuture = 1 THEN 1
			ELSE 2
		END,
		@createdDate,
		@modifiedDate,
		@summary,
		@translatedValues,
		@searchableText
	)
	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