insert objectPermissionsTbl values(190,12,'Global Comment Editing' ,'<Values xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<Value>
<display>Enable</display>
<value>2</value>
</Value>
<Value>
<display>Disable</display>
<value>0</value>
</Value>
</Values>',1,'This permission will enable the user to edit interaction''s comments.')
go
insert into rolePermissionsTbl values(190,5,2)
go
          
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[eventUpdates](
	[entryId] [int] IDENTITY(1,1) NOT NULL,
	[topicId] [int] NOT NULL,
	[newName] [varchar](256) NULL,
	[oldThreshold] [int] NULL,
	[newThreshold] [int] NULL,
	[fromCallId] [int] NOT NULL,
	[toCallId] [int] NOT NULL,
	[startTime] [int] NULL,
	[handledBy] [varchar](512) NULL,
	[nChanged] [int] NULL,
 CONSTRAINT [PK_eventUpdates] PRIMARY KEY CLUSTERED 
(
	[entryId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[eventUpdates] ADD  CONSTRAINT [DF_eventUpdates_nChanged]  DEFAULT ((0)) FOR [nChanged]
GO


SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[eventTermUpdates](
	[eventEntryId] [int] NOT NULL,
	[term] [varchar](300) NOT NULL,
	[changed] [bit] NOT NULL,
	[newThreshold] [int] NULL,
 CONSTRAINT [PK_eventTermUpdates] PRIMARY KEY CLUSTERED 
(
	[eventEntryId] ASC,
	[term] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[eventTermUpdates]  WITH CHECK ADD  CONSTRAINT [FK_eventTermUpdates_eventUpdates] FOREIGN KEY([eventEntryId])
REFERENCES [dbo].[eventUpdates] ([entryId])
GO

ALTER TABLE [dbo].[eventTermUpdates] CHECK CONSTRAINT [FK_eventTermUpdates_eventUpdates]
GO


GRANT INSERT ON [eventUpdates] TO SMART
GRANT INSERT ON [eventTermUpdates] TO SMART

GRANT UPDATE(startTime,handledBy,fromCallId,nChanged) ON [eventUpdates] TO Platform
GRANT SELECT,DELETE ON [eventUpdates] TO Platform
GRANT SELECT,DELETE ON [eventTermUpdates] TO Platform

GRANT UPDATE(resourceName,term,found) ON [callEventsTbl] TO Platform
GRANT UPDATE(resourceName,term,found) ON [callEventsAuditTbl] TO Platform

GRANT EXECUTE ON sp_synchCallEventsAuditTbl TO Platform

REVOKE EXECUTE ON sp_synchCallEventsAuditTbl FROM SMART
REVOKE UPDATE ON [callEventsTbl] FROM SMART
REVOKE UPDATE ON [callEventsAuditTbl] FROM SMART


          
GO

IF EXISTS(SELECT * FROM sys.columns 
            WHERE Name = N'originalBody' AND Object_ID = Object_ID(N'TextData'))
BEGIN
    ALTER TABLE TextData DROP COLUMN originalBody
END

GO
IF EXISTS(SELECT * FROM sys.columns 
            WHERE Name = N'body' AND Object_ID = Object_ID(N'TextData'))
BEGIN
    ALTER TABLE TextData DROP COLUMN body
END

GO
IF NOT EXISTS(SELECT * FROM sys.columns  WHERE Name = N'folder' AND Object_ID = Object_ID(N'TextData'))        
BEGIN
    ALTER TABLE TextData ADD folder nvarchar(max) NULL
END

IF NOT EXISTS(SELECT * FROM sys.columns WHERE Name = N'encryptionKey' AND Object_ID = Object_ID(N'TextData'))           
BEGIN
    ALTER TABLE TextData ADD encryptionKey int NOT NULL DEFAULT (0)
END

GO
IF NOT EXISTS(SELECT * FROM sys.columns WHERE Name = N'siteId' AND Object_ID = Object_ID(N'TextData'))           
BEGIN
    ALTER TABLE TextData ADD siteId int NOT NULL DEFAULT (0)
	ALTER TABLE TextData WITH NOCHECK ADD CONSTRAINT [FK_TextData_siteTbl] FOREIGN KEY([siteId]) REFERENCES [dbo].[siteTbl] ([siteId])
	ALTER TABLE [dbo].[TextData]  NOCHECK CONSTRAINT FK_TextData_siteTbl
END


GO


IF OBJECT_ID('siteTextFormatsTbl', 'U') IS NULL
BEGIN
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON

CREATE TABLE [dbo].[siteTextFormatsTbl](
	[textFormatId] [int] NOT NULL,
	[siteId] [int] NOT NULL,
	[retentionPeriod] [int] NOT NULL,
 CONSTRAINT [PK_siteTextFormatsTbl] PRIMARY KEY CLUSTERED 
(
	[textFormatId] ASC,
	[siteId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]


ALTER TABLE [dbo].[siteTextFormatsTbl]  WITH CHECK ADD  CONSTRAINT [FK_sitesTextFormatsTbl_textFormatsTbl] FOREIGN KEY([textFormatId])
REFERENCES [dbo].[ResourceType] ([resourceTypeId])


ALTER TABLE [dbo].[siteTextFormatsTbl] CHECK CONSTRAINT [FK_sitesTextFormatsTbl_textFormatsTbl]


ALTER TABLE [dbo].[siteTextFormatsTbl]  WITH CHECK ADD CONSTRAINT [FK_sitesTextFormatsTbl_siteTbl] FOREIGN KEY([siteId])
REFERENCES [dbo].[siteTbl] ([siteId])


ALTER TABLE [dbo].[siteTextFormatsTbl] CHECK CONSTRAINT [FK_sitesTextFormatsTbl_siteTbl]

END
  

update objectPermissionsTbl 
set description = 'Edit Call Comments',
[values]='<Values xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <Value>
    <display>Allow</display>
    <value>2</value>
  </Value>
  <Value>
    <display>Deny</display>
    <value>0</value>
  </Value>
</Values>'
where objectId = 190
go
update objectPermissionsTbl 
set [description] = 'Edit others'' comments',
[values]='<Values xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <Value>
    <display>Allow</display>
    <value>2</value>
  </Value>
  <Value>
    <display>Deny</display>
    <value>0</value>
  </Value>
</Values>'
where objectId = 163
go
          
GO


Update ResourceType set description = 'Text' where resourceTypeId = 4 
GO

IF NOT EXISTS(SELECT * FROM sys.columns  WHERE Name = N'fileName' AND Object_ID = Object_ID(N'TextData'))        
BEGIN
    ALTER TABLE TextData ADD fileName nvarchar(max) NULL
END

GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[TextAttachmentData](
	[textId] [int] NOT NULL,
	[name] [nvarchar](max) NOT NULL,
	[folder] [nvarchar](max) NULL,
	[fileName] [nvarchar](max) NULL,
	[encryptionKey] [int] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

ALTER TABLE [dbo].[TextAttachmentData]  WITH CHECK ADD  CONSTRAINT [FK_TextAttachmentData] FOREIGN KEY([textId])
REFERENCES [dbo].[TextData] ([textId])
GO

ALTER TABLE [dbo].[TextAttachmentData] CHECK CONSTRAINT [FK_TextAttachmentData]
GO

ALTER TABLE [dbo].[TextAttachmentData] ADD  DEFAULT ((0)) FOR [encryptionKey]
GO


IF OBJECT_ID('sp_deleteTexts', 'P') IS NOT NULL
	DROP PROCEDURE [dbo].[sp_deleteTexts]
GO
IF OBJECT_ID('sp_deleteTextsAndMoveToPurge', 'P') IS NOT NULL
	DROP PROCEDURE [dbo].[sp_deleteTextsAndMoveToPurge]
GO

CREATE PROCEDURE [dbo].[sp_deleteTextsAndMoveToPurge](@TextIDs varchar(max),@purgeId int,@deletedTextsNum int OUTPUT)
WITH EXECUTE AS SELF
AS
BEGIN
	SET NOCOUNT ON;

	create table #tmpTextTbl (path nvarchar(max), siteId int, purgeId int NULL)  
	declare @purgeIdStr as nvarchar (max)
	set @purgeIdStr =  CAST(@purgeId as nvarchar (max))
    declare @getPaths as nvarchar (max)
	set @getPaths =	'select folder + ''\'' + fileName as path, siteId, ' + @purgeIdStr + ' as purgeId from textData where folder is not null and fileName is not null and textid in (' + @TextIDs + ')'  
	set @getPaths = @getPaths + ' union select i.folder + ''\'' + i.fileName as path, t.siteId, ' + @purgeIdStr + ' as purgeId from textData t inner join TextAttachmentData i on t.textId = i.TextId where i.folder is not null and i.fileName is not null and t.textid in (' + @TextIDs + ')'
	insert into #tmpTextTbl exec sp_executesql @getPaths

	DECLARE @ParmDefinition nvarchar(500);
	set @deletedTextsNum ='-1'
	declare @SelectCounter nvarchar(max)
	set @SelectCounter = N'select @sOUT = count(*) from textData where textId in (' + @TextIDs + ')'
	SET @ParmDefinition = N'@sOUT int OUTPUT';
    exec sp_executesql @SelectCounter,@ParmDefinition,@sOUT=@deletedTextsNum OUTPUT;
	declare @deleteTexts as nvarchar (max)
	set @deleteTexts = ''
	---------
	set @deleteTexts = @deleteTexts + 'DELETE FROM callMetaExTbl where callid in (' + @TextIDs + ')'
	set @deleteTexts = @deleteTexts + 'DELETE FROM callcategoryTbl where callid in (' + @TextIDs + ')'
	set @deleteTexts = @deleteTexts + 'DELETE FROM callEventsTbl where callid in (' + @TextIDs + ')'
	set @deleteTexts = @deleteTexts + 'DELETE FROM callAgentTbl where callid in (' + @TextIDs + ')'
	set @deleteTexts = @deleteTexts + 'DELETE FROM callPartitionTbl where callid in (' + @TextIDs + ')'
	set @deleteTexts = @deleteTexts + 'DELETE FROM textparties where textId in (' + @TextIDs + ')'
	set @deleteTexts = @deleteTexts + 'DELETE FROM textStatus where textId in (' + @TextIDs + ')'
	set @deleteTexts = @deleteTexts + 'DELETE FROM TextAttachmentData where textId in (' + @TextIDs + ')'
	set @deleteTexts = @deleteTexts + 'DELETE FROM textData where textId in (' + @TextIDs + ')'
	set @deleteTexts = @deleteTexts + 'DELETE FROM repCategoryMetaTbl where callid in (' + @TextIDs + ')'
	set @deleteTexts = @deleteTexts + 'DELETE FROM callSpeakersTbl where callid in (' + @TextIDs + ')'
	---------
	exec sp_executesql @deleteTexts

	insert into purgeCallsTbl select path, siteId, purgeId from #tmpTextTbl where path not in (select path from purgeCallsTbl)
	drop table #tmpTextTbl
END

GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF OBJECT_ID('sp_get_old_texts', 'P') IS NOT NULL
	DROP PROCEDURE [dbo].[sp_get_old_texts]
GO

CREATE PROCEDURE [dbo].[sp_get_old_texts] (@textFormat as int,@siteId as int)
AS 
  begin
      select 0 as isAttachment, td.textId, td.folder, td.fileName, td.resourceTypeId
      from TextData td with (nolock)
	  join siteTextFormatsTbl as stf with (nolock) on td.resourceTypeId = stf.textFormatId and td.siteId = stf.siteId
	  join TextStatus as ts with (nolock) on td.textId =ts.textId
      where td.siteId =  @siteId
        and td.resourceTypeId = @textFormat
        and ts.endRecTime <> 0
        and stf.retentionPeriod >= 0
		and td.folder IS NOT NULL
		and td.fileName IS NOT NULL
        and DATEDIFF (hour,DATEADD(second,ts.arrivalTime,'1-1-1970'),GETUTCDATE()) >= stf.retentionPeriod
        and td.textId not in (select callid from callcategorytbl with (nolock) where categoryid in (select categoryid from categoryinfotbl with (nolock) where isManual = 1))

	Union 

	  select 1 as isAttachment, td.textId, ta.folder, ta.fileName, td.resourceTypeId
      from TextData td with (nolock)
	  join TextAttachmentData ta with (nolock) on td.textId = ta.textId
	  join siteTextFormatsTbl as stf with (nolock) on td.resourceTypeId = stf.textFormatId and td.siteId = stf.siteId
	  join TextStatus as ts with (nolock) on td.textId =ts.textId
      where td.siteId =  @siteId
        and td.resourceTypeId = @textFormat
        and ts.endRecTime <> 0
        and stf.retentionPeriod >= 0
		and ta.folder IS NOT NULL
		and ta.fileName IS NOT NULL
        and DATEDIFF (hour,DATEADD(second,ts.arrivalTime,'1-1-1970'),GETUTCDATE()) >= stf.retentionPeriod
        and td.textId not in (select callid from callcategorytbl with (nolock) where categoryid in (select categoryid from categoryinfotbl with (nolock) where isManual = 1))

end

GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER PROCEDURE [dbo].[sp_deleteTextsByParams](@purgeId int,@fromDate int,@toDate int, @progList varchar(1024),@toPurgeEmail bit,@toPurgeChat bit,@toPurgeText bit,@toPurgeSocial bit,@deletedTextsNum int OUTPUT,@inStaticList int OUTPUT)

WITH EXECUTE AS SELF

  AS 
  declare @command as nvarchar(3024)
  DECLARE @ParmDefinition nvarchar(500);
  declare @s nvarchar(max)
  declare @s2 int
  SET NOCOUNT ON --Suppress "rows affected" messages

  set @s = '' 
  set @s2 = 0 
  set @command =''
  set @command = @command + 'create table #textIds (textid int); insert into #textIds select top 2000 td.textid '
  set @command = @command + 'from textData as td with (nolock) '
  set @command = @command + 'where td.textid not in (select callid from callcategorytbl with (nolock) where categoryid in (select categoryid from categoryinfotbl with (nolock) where isManual = 1))  '
  set @command = @command + 'and td.textid not in (select distinct callId from coachingStaticCallListCalls with (nolock)) and ( 1=1 '
  if @progList <> '' set @command = @command + ' and programId in (' + @progList + ')'
  if @fromDate > 0 set @command = @command + ' and originalTime >= ' + cast(@fromDate as varchar(16))
  if @toDate > 0 set @command = @command + ' and originalTime <= ' + cast(@toDate as varchar(16))
  set @command = @command + ' and ('
  if @toPurgeEmail = 1 set @command = @command + 'resourceTypeId=2'
  if @toPurgeEmail = 1 and @toPurgeChat = 1 set @command = @command + ' or '
  if @toPurgeChat = 1 set @command = @command + 'resourceTypeId=3'
  if (@toPurgeEmail = 1 or @toPurgeChat = 1) and @toPurgeText = 1 set @command = @command + ' or '
  if @toPurgeText = 1 set @command = @command + 'resourceTypeId=4'
  if (@toPurgeEmail = 1 or @toPurgeChat = 1 or @toPurgeText = 1) and @toPurgeSocial = 1 set @command = @command + ' or '
  if @toPurgeSocial = 1 set @command = @command + 'resourceTypeId=5'
  set @command = @command + ') '
  set @command = @command + '); select @sOUT = @sOUT + cast(textid as varchar(64)) +  '','' '
  set @command = @command + 'from  #textIds ;'
  set @command = @command + 'create table #textIds2 (textid int); insert into #textIds2 select top 2000 td.textid '
  set @command = @command + 'from textData as td with (nolock) '
  set @command = @command + 'where td.textid not in (select callid from callcategorytbl with (nolock) where categoryid in (select categoryid from categoryinfotbl with (nolock) where isManual = 1))  '
  set @command = @command + 'and td.textid  in (select distinct callId from coachingStaticCallListCalls with (nolock)) and ( 1=1 '
  if @progList <> '' set @command = @command + ' and programId in (' + @progList + ')'
  if @fromDate > 0 set @command = @command + ' and originalTime >= ' + cast(@fromDate as varchar(16))
  if @toDate > 0 set @command = @command + ' and originalTime <= ' + cast(@toDate as varchar(16))
  set @command = @command + '); select @sOUT2 = count(*) from  #textIds2 ;'
  set @command = @command + 'delete from indexq where resourceId in (select textid from #textIds) and resourceTypeId<>1; '
  set @command = @command + 'insert into indexq(resourceId,resourceTypeId,callTime,status,priority,purgeId) select td.textid,1,td.originalTime,2,1,' 
  set @command = @command + cast(@purgeId as varchar(16)) + ' from #textIds join textData td with (nolock) on #textIds.textid=td.textid '  
  set @command = @command + 'where td.textid not in (select resourceId from indexq where status=2 and resourceTypeId<>1); '

  SET @ParmDefinition = N'@sOUT nvarchar(max) OUTPUT, @sOUT2 int OUTPUT';
  exec sp_executesql @command,@ParmDefinition,@sOUT=@s OUTPUT,@sOUT2=@s2 OUTPUT;
  set @s = Substring(@s,0,Len(@s))
  set @inStaticList= @s2
  set @deletedTextsNum=0
  if len(@s) > 0
  begin
    exec sp_deleteTextsAndMoveToPurge @s,@purgeId,@deletedTextsNum OUTPUT
  end

  SET NOCOUNT OFF
        
GO
update dbo.versionTbl set version= '8.5.7169' where resource in ('SM', 'SMART') 
go