--
-- Tigase Unified Archive Component - Extension of implementation of Message Archiving component for Tigase XMPP Server
-- Copyright (C) 2015 Tigase, Inc. (office@tigase.com) - All Rights Reserved
-- Unauthorized copying of this file, via any medium is strictly prohibited
-- Proprietary and confidential
--

-- LOAD FILE: database/sqlserver-message-archiving-2.0.0.sql

-- QUERY START:
SET QUOTED_IDENTIFIER ON
-- QUERY END:
GO

-- QUERY START:
IF NOT EXISTS(SELECT * FROM sys.columns WHERE object_id = OBJECT_ID('tig_ma_msgs') and NAME = 'item_type')
ALTER TABLE [dbo].[tig_ma_msgs] ADD [item_type] [nvarchar](50);
-- QUERY END:
GO
-- QUERY START:
UPDATE [dbo].[tig_ma_msgs] SET [item_type] = 'chat' WHERE [item_type] IS NULL;
-- QUERY END:
GO
-- QUERY START:
IF NOT EXISTS(SELECT * FROM sys.indexes WHERE object_id = object_id('dbo.tig_ma_msgs') AND NAME ='IX_tig_ma_msgs_owner_id_item_type_index')
CREATE INDEX IX_tig_ma_msgs_owner_id_item_type_index ON [dbo].[tig_ma_msgs] ([owner_id],[item_type]);
-- QUERY END:
GO

-- QUERY START:
IF NOT EXISTS(SELECT * FROM sys.columns WHERE object_id = OBJECT_ID('tig_ma_msgs') and NAME = 'offline')
ALTER TABLE [dbo].[tig_ma_msgs] ADD [offline] [smallint];
-- QUERY END:
GO
-- QUERY START:
UPDATE [dbo].[tig_ma_msgs] SET [offline] = 0 WHERE [offline] IS NULL;
-- QUERY END:
GO
-- QUERY START:
IF NOT EXISTS(SELECT * FROM sys.indexes WHERE object_id = object_id('dbo.tig_ma_msgs') AND NAME ='IX_tig_ma_msgs_owner_id_offline_index')
CREATE INDEX IX_tig_ma_msgs_owner_id_offline_index ON [dbo].[tig_ma_msgs] ([owner_id],[offline]);
-- QUERY END:
GO

-- QUERY START:
if exists (select * from sys.columns where object_id = object_id('dbo.tig_ma_jids') and name = 'jid_fragment' and max_length > 900)
begin
	IF EXISTS (select 1 from sys.indexes WHERE object_id = object_id('dbo.tig_ma_jids') AND NAME ='IX_tig_ma_jids_jid')
	begin
	    drop index IX_tig_ma_jids_jid on tig_ma_jids;
	end
	alter table tig_ma_jids drop column jid_fragment;
	alter table tig_ma_jids add jid_fragment as left(jid, 255);
end
-- QUERY END:
GO
-- QUERY START:
IF NOT EXISTS ( select 1
	from sys.indexes i
	inner join sys.index_columns ic on i.object_id = ic.object_id and i.index_id = ic.index_id
	inner join sys.columns c on ic.object_id = c.object_id and ic.column_id = c.column_id
	where
		i.object_id = object_id('dbo.tig_ma_jids') and i.name = 'IX_tig_ma_jids_jid'
		and ic.is_included_column = 1 and c.name = 'jid')
	begin
	IF EXISTS (select 1 from sys.indexes WHERE object_id = object_id('dbo.tig_ma_jids') AND NAME ='IX_tig_ma_jids_jid')
	begin
		drop index IX_tig_ma_jids_jid on tig_ma_jids;
	end
	create index IX_tig_ma_jids_jid on tig_ma_jids (jid_fragment) include (jid, jid_id);
	end
-- QUERY END:
GO
-- QUERY START:
if not exists (select * from sys.columns where object_id = object_id('dbo.tig_ma_msgs') and name = 'jingle_sid')
    alter table [dbo].[tig_ma_msgs] add [jingle_sid] nvarchar(128) null;
-- QUERY END:
GO
-- QUERY START:
if not exists (select * from sys.columns where object_id = object_id('dbo.tig_ma_msgs') and name = 'jingle_reason')
alter table [dbo].[tig_ma_msgs] add [jingle_reason] nvarchar(50) null;
-- QUERY END:
GO
-- QUERY START:
if not exists (select * from sys.columns where object_id = object_id('dbo.tig_ma_msgs') and name = 'jingle_action')
alter table [dbo].[tig_ma_msgs] add [jingle_action] nvarchar(50) null;
-- QUERY END:
GO
-- QUERY START:
if not exists (select * from sys.columns where object_id = object_id('dbo.tig_ma_msgs') and name = 'jingle_offline')
alter table [dbo].[tig_ma_msgs] add [jingle_offline] bit default 0;
-- QUERY END:
GO
-- QUERY START:
update tig_ma_msgs set
	[jingle_sid] = cast(msg as xml).value('(iq/*:jingle/@sid)[1]','nvarchar(128)'),
	[jingle_action] = cast(msg as xml).value('(iq/*:jingle/@action)[1]','nvarchar(50)'),
	[jingle_reason] = case cast(cast(msg as xml).query('local-name((iq/*:jingle/*:reason/*)[1]) != "text"') as varchar(100))
		when 'true' then cast(msg as xml).value('local-name((iq/*:jingle/*:reason/*)[1])','nvarchar(50)')
		else cast(msg as xml).value('local-name((iq/*:jingle/*:reason/*)[2])','nvarchar(50)')
	end
where
	item_type = 'call' and jingle_sid is null and jingle_action is null and type = 'set';
-- QUERY END:
GO
-- QUERY START:
update tig_ma_msgs set
	[jingle_offline] = case cast(msg as xml).value('(iq/*:jingle/@offline)[1]', 'varchar(100)')
		when 'true' then 1
		else 0
	end
where item_type = 'call' and jingle_offline is null;
-- QUERY END:
GO
-- QUERY START:
update tig_ma_msgs set
	[jingle_reason] = null
where LEN(jingle_reason) = 0;
-- QUERY END:
GO
-- QUERY START:
if not exists (select * from sys.indexes where object_id = object_id('dbo.tig_ma_msgs') and name = 'IX_tig_ma_msgs_owner_id_buddy_id_ts')
    create index IX_tig_ma_msgs_owner_id_buddy_id_ts on tig_ma_msgs (owner_id, buddy_id, ts);
-- QUERY END:
GO
-- QUERY START:
if not exists (select * from sys.indexes where object_id = object_id('dbo.tig_ma_msgs') and name = 'IX_tig_ma_msgs_owner_id_buddy_id_jingle_sid_ts')
    create index IX_tig_ma_msgs_owner_id_buddy_id_jingle_sid_ts on tig_ma_msgs (owner_id, buddy_id, jingle_sid, ts) include (jingle_action, msg_id);
-- QUERY END:
GO
-- QUERY START:
if not exists (select 1
	from sys.indexes i
	inner join sys.index_columns ic on i.object_id = ic.object_id and i.index_id = ic.index_id
	inner join sys.columns c on ic.object_id = c.object_id and ic.column_id = c.column_id
	where
		i.object_id = object_id('dbo.tig_ma_msgs') and i.name = 'IX_tig_ma_msgs_owner_id_buddy_id_index'
		and ic.is_included_column = 1 and c.name = 'offline')
	begin
	IF EXISTS (select 1 from sys.indexes WHERE object_id = object_id('dbo.tig_ma_msgs') AND NAME ='IX_tig_ma_msgs_owner_id_buddy_id_index')
	    begin
		drop index IX_tig_ma_msgs_owner_id_buddy_id_index on tig_ma_msgs;
		end
	create index IX_tig_ma_msgs_owner_id_buddy_id_index on tig_ma_msgs (owner_id, buddy_id) include (ts, direction, type, item_type, jingle_sid, jingle_action, jingle_reason, jingle_offline, stanza_hash, offline);
	end
-- QUERY END:
GO

-- ---------------------
-- Stored procedures
-- ---------------------

-- QUERY START:
IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'Tig_UA_GetItems')
	DROP PROCEDURE [dbo].[Tig_UA_GetItems]
-- QUERY END:
GO

-- QUERY START:
create procedure [dbo].[Tig_UA_GetItems]
	@_ownerJid nvarchar(2049), 
	@_buddyJid nvarchar(2049), 
	@_from datetime, 
	@_to datetime,
	@_tags nvarchar(max), 
	@_contains nvarchar(max),
	@_itemType nvarchar(50),
	@_offline smallint,
	@_limit int, 
	@_offset int
AS
begin
	SET NOCOUNT ON;
	declare 
		@params_def nvarchar(max),
		@contains_query nvarchar(max),
		@tags_query nvarchar(max),
		@msgs_query nvarchar(max),
		@query_sql nvarchar(max);

	if @_tags is not null or @_contains is not null
		begin
		set @params_def = N'@_ownerJid nvarchar(2049), @_buddyJid nvarchar(2049), @_from datetime, @_to datetime, @_itemType nvarchar(50), @_offline smallint, @_limit int, @_offset int';
		exec Tig_MA_GetHasTagsQuery @_in_str = @_tags, @_out_query = @tags_query output;
		exec Tig_MA_GetBodyContainsQuery @_in_str = @_contains, @_out_query = @contains_query output;
		set @msgs_query = N'select m.msg, m.ts, m.direction, b.jid, m.stanza_hash, m.item_type, row_number() over (order by m.ts) as row_num
		from tig_ma_msgs m 
			inner join tig_ma_jids o on m.owner_id = o.jid_id 
			inner join tig_ma_jids b on b.jid_id = m.buddy_id
		where 
			o.jid_sha1 = HASHBYTES(''SHA1'', LOWER(@_ownerJid))
			and (@_buddyJid is null or b.jid_sha1 = HASHBYTES(''SHA1'', LOWER(@_buddyJid)))
			and (@_from is null or m.ts >= @_from)
			and (@_to is null or m.ts <= @_to)
			and (@_itemType is null or m.item_type = @_itemType)
			and (@_offline is null or m.offline = @_offline)
			and m.offline <> 2';
		set @query_sql = N';with results_cte as (' + @msgs_query + @tags_query + @contains_query + N') select * from results_cte where row_num >= @_offset + 1 and row_num < @_offset + 1 + @_limit order by row_num'
		execute sp_executesql @query_sql, @params_def, @_ownerJid=@_ownerJid, @_buddyJid=@_buddyJid, @_from=@_from, @_to=@_to, @_itemType=@_itemType, @_offline=@_offline, @_limit=@_limit, @_offset=@_offset
		end
	else
		begin
		;with results_cte as (
		select m.msg, m.ts, m.direction, b.jid, m.stanza_hash, m.item_type, row_number() over (order by m.ts) as row_num
		from tig_ma_msgs m 
			inner join tig_ma_jids o on m.owner_id = o.jid_id 
			inner join tig_ma_jids b on b.jid_id = m.buddy_id
		where 
			o.jid_sha1 = HASHBYTES('SHA1', LOWER(@_ownerJid))
			and (@_buddyJid is null or b.jid_sha1 = HASHBYTES('SHA1', LOWER(@_buddyJid)))
			and (@_from is null or m.ts >= @_from)
			and (@_to is null or m.ts <= @_to)
			and (@_itemType is null or m.item_type = @_itemType)
			and (@_offline is null or m.offline = @_offline)
			and m.offline <> 2
		)
		select * from results_cte where row_num >= @_offset + 1 and row_num < @_offset + 1 + @_limit order by row_num;
		end
end
-- QUERY END:
GO

-- QUERY START:
IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'Tig_UA_GetItemsCount')
	DROP PROCEDURE [dbo].[Tig_UA_GetItemsCount]
-- QUERY END:
GO

-- QUERY START:
create procedure [dbo].[Tig_UA_GetItemsCount]
	@_ownerJid nvarchar(2049), 
	@_buddyJid nvarchar(2049), 
	@_from datetime, 
	@_to datetime, 
	@_tags nvarchar(max),
	@_contains nvarchar(max),
	@_itemType nvarchar(50),
	@_offline smallint
AS
begin
	declare 
		@params_def nvarchar(max),
		@tags_query nvarchar(max),
		@contains_query nvarchar(max),
		@msgs_query nvarchar(max),
		@query_sql nvarchar(max);

	if @_tags is not null or @_contains is not null
		begin
		set @params_def = N'@_ownerJid nvarchar(2049), @_buddyJid nvarchar(2049), @_from datetime, @_to datetime, @_itemType nvarchar(50), @_offline smallint';
		exec Tig_MA_GetHasTagsQuery @_in_str = @_tags, @_out_query = @tags_query output;
		exec Tig_MA_GetBodyContainsQuery @_in_str = @_contains, @_out_query = @contains_query output;
		set @msgs_query = N'select count(m.msg_id)
		from tig_ma_msgs m 
			inner join tig_ma_jids o on m.owner_id = o.jid_id 
			inner join tig_ma_jids b on b.jid_id = m.buddy_id
		where 
			o.jid_sha1 = HASHBYTES(''SHA1'', LOWER(@_ownerJid))
			and (@_buddyJid is null or b.jid_sha1 = HASHBYTES(''SHA1'', LOWER(@_buddyJid)))
			and (@_from is null or m.ts >= @_from)
			and (@_to is null or m.ts <= @_to)
			and (@_itemType is null or m.item_type = @_itemType)
			and (@_offline is null or m.offline = @_offline)
			and m.offline <> 2';
		set @query_sql = @msgs_query + @tags_query + @contains_query;
		execute sp_executesql @query_sql, @params_def, @_ownerJid=@_ownerJid, @_buddyJid=@_buddyJid, @_from=@_from, @_to=@_to, @_itemType=@_itemType, @_offline=@_offline
		end
	else
		begin
		select count(m.msg_id)
		from tig_ma_msgs m 
			inner join tig_ma_jids o on m.owner_id = o.jid_id 
			inner join tig_ma_jids b on b.jid_id = m.buddy_id
		where 
			o.jid_sha1 = HASHBYTES('SHA1', LOWER(@_ownerJid))
			and (@_buddyJid is null or b.jid_sha1 = HASHBYTES('SHA1', LOWER(@_buddyJid)))
			and (@_from is null or m.ts >= @_from)
			and (@_to is null or m.ts <= @_to)
			and (@_itemType is null or m.item_type = @_itemType)
			and (@_offline is null or m.offline = @_offline)
			and m.offline <> 2
		end
end
-- QUERY END:
GO

-- QUERY START:
IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'Tig_UA_GetItemPosition')
	DROP PROCEDURE [dbo].[Tig_UA_GetItemPosition]
-- QUERY END:
GO

-- QUERY START:
create procedure [dbo].[Tig_UA_GetItemPosition]
	@_ownerJid nvarchar(2049),
	@_buddyJid nvarchar(2049),
	@_from datetime,
	@_to datetime,
	@_tags nvarchar(max),
	@_contains nvarchar(max),
	@_hash nvarchar(50),
	@_itemType nvarchar(50),
	@_offline smallint
AS
begin
	declare
		@params_def nvarchar(max),
		@tags_query nvarchar(max),
		@contains_query nvarchar(max),
		@msgs_query nvarchar(max),
		@query_sql nvarchar(max);

	if @_tags is not null or @_contains is not null
		begin
		set @params_def = N'@_ownerJid nvarchar(2049), @_buddyJid nvarchar(2049), @_from datetime, @_to datetime, @_itemType nvarchar(50), @_offline smallint, @_hash nvarchar(50)';
		exec Tig_MA_GetHasTagsQuery @_in_str = @_tags, @_out_query = @tags_query output;
		exec Tig_MA_GetBodyContainsQuery @_in_str = @_contains, @_out_query = @contains_query output;
		set @msgs_query = N'select x.position from (
		select m.stanza_hash, row_number() over (order by m.ts) as position
		from tig_ma_msgs m
			inner join tig_ma_jids o on m.owner_id = o.jid_id
			inner join tig_ma_jids b on b.jid_id = m.buddy_id
		where
			o.jid_sha1 = HASHBYTES(''SHA1'', LOWER(@_ownerJid))
			and (@_buddyJid is null or b.jid_sha1 = HASHBYTES(''SHA1'', LOWER(@_buddyJid)))
			and (@_from is null or m.ts >= @_from)
			and (@_to is null or m.ts <= @_to)
			and (@_itemType is null or m.item_type = @_itemType)
			and (@_offline is null or m.offline = @_offline)
			and m.offline <> 2';
		set @query_sql = @msgs_query + @tags_query + @contains_query + N') x where x.stanza_hash = @_hash';
		execute sp_executesql @query_sql, @params_def, @_ownerJid=@_ownerJid, @_buddyJid=@_buddyJid, @_from=@_from, @_to=@_to, @_itemType=@_itemType, @_offline=@_offline, @_hash = @_hash
		end
	else
		begin
		select x.position from (
		select m.stanza_hash, row_number() over (order by m.ts) as position
		from tig_ma_msgs m
			inner join tig_ma_jids o on m.owner_id = o.jid_id
			inner join tig_ma_jids b on b.jid_id = m.buddy_id
		where
			o.jid_sha1 = HASHBYTES('SHA1', LOWER(@_ownerJid))
			and (@_buddyJid is null or b.jid_sha1 = HASHBYTES('SHA1', LOWER(@_buddyJid)))
			and (@_from is null or m.ts >= @_from)
			and (@_to is null or m.ts <= @_to)
			and (@_itemType is null or m.item_type = @_itemType)
			and (@_offline is null or m.offline = @_offline)
			and m.offline <> 2
		) x where x.stanza_hash = @_hash
		end
end
-- QUERY END:
GO


-- QUERY START:
IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'Tig_UA_GetCollections')
	DROP PROCEDURE [dbo].[Tig_UA_GetCollections]
-- QUERY END:
GO

-- QUERY START:
create procedure [dbo].[Tig_UA_GetCollections]
	@_ownerJid nvarchar(2049), 
	@_buddyJid nvarchar(2049), 
	@_from datetime, 
	@_to datetime, 
	@_tags nvarchar(max),
	@_contains nvarchar(max),
	@_byType smallint,
	@_itemType nvarchar(50),
	@_offline smallint,
	@_limit int, 
	@_offset int
AS
begin
	declare 
		@params_def nvarchar(max),
		@tags_query nvarchar(max),
		@contains_query nvarchar(max),
		@groupby_query nvarchar(max),
		@msgs_query nvarchar(max),
		@query_sql nvarchar(max);

	if @_tags is not null or @_contains is not null
		begin
		set @params_def = N'@_ownerJid nvarchar(2049), @_buddyJid nvarchar(2049), @_from datetime, @_to datetime, @_itemType nvarchar(50), @_offline smallint, @_limit int, @_offset int';
		exec Tig_MA_GetHasTagsQuery @_in_str = @_tags, @_out_query = @tags_query output;
		exec Tig_MA_GetBodyContainsQuery @_in_str = @_contains, @_out_query = @contains_query output;
		set @msgs_query = N'select min(m.ts) as ts, b.jid, ROW_NUMBER() over (order by min(m.ts), b.jid) as row_num';

		if @_byType = 1 
			set @msgs_query = @msgs_query + N', case when m.type = ''groupchat'' then ''groupchat'' else '''' end as type';
		else
			set @msgs_query = @msgs_query + N', null as type';

		set @msgs_query = @msgs_query + N' from tig_ma_msgs m 
			inner join tig_ma_jids o on m.owner_id = o.jid_id 
			inner join tig_ma_jids b on b.jid_id = m.buddy_id
		where 
			o.jid_sha1 = HASHBYTES(''SHA1'', LOWER(@_ownerJid))
			and (@_buddyJid is null or b.jid_sha1 = HASHBYTES(''SHA1'', LOWER(@_buddyJid)))
			and (@_from is null or m.ts >= @_from)
			and (@_to is null or m.ts <= @_to)
			and (@_itemType is null or m.item_type = @_itemType)
			and (@_offline is null or m.offline = @_offline)
			and m.offline <> 2';
		if @_byType = 1
			set @groupby_query = N' group by cast(m.ts as date), m.buddy_id, b.jid, case when m.type = ''groupchat'' then ''groupchat'' else '''' end';
		else
			set @groupby_query = N' group by cast(m.ts as date), m.buddy_id, b.jid';
		
		set @query_sql = N';with results_cte as (' + @msgs_query + @tags_query + @contains_query + @groupby_query + N') select * from results_cte where row_num >= @_offset + 1 and row_num < @_offset + 1 + @_limit order by row_num'
		execute sp_executesql @query_sql, @params_def, @_ownerJid=@_ownerJid, @_buddyJid=@_buddyJid, @_from=@_from, @_to=@_to, @_itemType=@_itemType, @_offline=@_offline, @_limit=@_limit, @_offset=@_offset
		end
	else
		begin
		if @_byType = 1
			begin
			;with results_cte as (
			select min(ts) as ts, b.jid, row_number() over (order by min(m.ts), b.jid) as row_num, case when m.type = 'groupchat' then 'groupchat' else '' end as type
			from tig_ma_msgs m 
				inner join tig_ma_jids o on m.owner_id = o.jid_id 
				inner join tig_ma_jids b on b.jid_id = m.buddy_id
			where 
				o.jid_sha1 = HASHBYTES('SHA1', LOWER(@_ownerJid))
				and (@_buddyJid is null or b.jid_sha1 = HASHBYTES('SHA1', LOWER(@_buddyJid)))
				and (@_from is null or m.ts >= @_from)
				and (@_to is null or m.ts <= @_to)
				and (@_itemType is null or m.item_type = @_itemType)
				and (@_offline is null or m.offline = @_offline)
			    and m.offline <> 2
			group by cast(m.ts as date), m.buddy_id, b.jid, case when m.type = 'groupchat' then 'groupchat' else '' end
			)
			select * from results_cte where row_num >= @_offset + 1 and row_num < @_offset + 1 + @_limit order by row_num;
			end
		else
			begin
			;with results_cte as (
			select min(ts) as ts, b.jid, row_number() over (order by min(m.ts), b.jid) as row_num, null as type
			from tig_ma_msgs m 
				inner join tig_ma_jids o on m.owner_id = o.jid_id 
				inner join tig_ma_jids b on b.jid_id = m.buddy_id
			where 
				o.jid_sha1 = HASHBYTES('SHA1', LOWER(@_ownerJid))
				and (@_buddyJid is null or b.jid_sha1 = HASHBYTES('SHA1', LOWER(@_buddyJid)))
				and (@_from is null or m.ts >= @_from)
				and (@_to is null or m.ts <= @_to)
				and (@_itemType is null or m.item_type = @_itemType)
				and (@_offline is null or m.offline = @_offline)
			    and m.offline <> 2
			group by cast(m.ts as date), m.buddy_id, b.jid
			)
			select * from results_cte where row_num >= @_offset + 1 and row_num < @_offset + 1 + @_limit order by row_num;
			end
		end
end
-- QUERY END:
GO

-- QUERY START:
IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'Tig_UA_GetCollectionsCount')
	DROP PROCEDURE [dbo].[Tig_UA_GetCollectionsCount]
-- QUERY END:
GO

-- QUERY START:
create procedure [dbo].[Tig_UA_GetCollectionsCount]
	@_ownerJid nvarchar(2049), 
	@_buddyJid nvarchar(2049), 
	@_from datetime, 
	@_to datetime, 
	@_tags nvarchar(max),
	@_contains nvarchar(max),
	@_byType smallint,
	@_itemType nvarchar(50),
	@_offline smallint
AS
begin
	declare 
		@params_def nvarchar(max),
		@tags_query nvarchar(max),
		@contains_query nvarchar(max),
		@groupby_query nvarchar(max),
		@msgs_query nvarchar(max),
		@query_sql nvarchar(max);

	if @_tags is not null or @_contains is not null
		begin
		set @params_def = N'@_ownerJid nvarchar(2049), @_buddyJid nvarchar(2049), @_from datetime, @_to datetime, @_itemType nvarchar(50), @_offline smallint';
		exec Tig_MA_GetHasTagsQuery @_in_str = @_tags, @_out_query = @tags_query output;
		exec Tig_MA_GetBodyContainsQuery @_in_str = @_contains, @_out_query = @contains_query output;
		set @msgs_query = N'select min(m.ts) as ts, b.jid';

		if @_byType = 1 
			set @msgs_query = @msgs_query + N', case when m.type = ''groupchat'' then ''groupchat'' else '''' end as type';
		else
			set @msgs_query = @msgs_query + N', null as type';

		set @msgs_query = @msgs_query + N' from tig_ma_msgs m 
			inner join tig_ma_jids o on m.owner_id = o.jid_id 
			inner join tig_ma_jids b on b.jid_id = m.buddy_id
		where 
			o.jid_sha1 = HASHBYTES(''SHA1'', LOWER(@_ownerJid))
			and (@_buddyJid is null or b.jid_sha1 = HASHBYTES(''SHA1'', LOWER(@_buddyJid)))
			and (@_from is null or m.ts >= @_from)
			and (@_to is null or m.ts <= @_to)
			and (@_itemType is null or m.item_type = @_itemType)
			and (@_offline is null or m.offline = @_offline)
			and m.offline <> 2';
		if @_byType = 1
			set @groupby_query = N' group by cast(m.ts as date), m.buddy_id, b.jid, case when m.type = ''groupchat'' then ''groupchat'' else '''' end';
		else
			set @groupby_query = N' group by cast(m.ts as date), m.buddy_id, b.jid';
		
		set @query_sql = N';with results_cte as (' + @msgs_query + @tags_query + @contains_query + @groupby_query + N') select count(1) from results_cte'
		execute sp_executesql @query_sql, @params_def, @_ownerJid=@_ownerJid, @_buddyJid=@_buddyJid, @_from=@_from, @_to=@_to, @_itemType=@_itemType, @_offline=@_offline
		end
	else
		begin
		if @_byType = 1
			begin
			;with results_cte as (
			select min(ts) as ts, b.jid, case when m.type = 'groupchat' then 'groupchat' else '' end as type
			from tig_ma_msgs m 
				inner join tig_ma_jids o on m.owner_id = o.jid_id 
				inner join tig_ma_jids b on b.jid_id = m.buddy_id
			where 
				o.jid_sha1 = HASHBYTES('SHA1', LOWER(@_ownerJid))
				and (@_buddyJid is null or b.jid_sha1 = HASHBYTES('SHA1', LOWER(@_buddyJid)))
				and (@_from is null or m.ts >= @_from)
				and (@_to is null or m.ts <= @_to)
				and (@_itemType is null or m.item_type = @_itemType)
				and (@_offline is null or m.offline = @_offline)
			    and m.offline <> 2
			group by cast(m.ts as date), m.buddy_id, b.jid, case when m.type = 'groupchat' then 'groupchat' else '' end
			)
			 select count(1) from results_cte;
			end
		else
			begin
			;with results_cte as (
			select min(ts) as ts, b.jid, null as type
			from tig_ma_msgs m 
				inner join tig_ma_jids o on m.owner_id = o.jid_id 
				inner join tig_ma_jids b on b.jid_id = m.buddy_id
			where 
				o.jid_sha1 = HASHBYTES('SHA1', LOWER(@_ownerJid))
				and (@_buddyJid is null or b.jid_sha1 = HASHBYTES('SHA1', LOWER(@_buddyJid)))
				and (@_from is null or m.ts >= @_from)
				and (@_to is null or m.ts <= @_to)
				and (@_itemType is null or m.item_type = @_itemType)
				and (@_offline is null or m.offline = @_offline)
			    and m.offline <> 2
			group by cast(m.ts as date), m.buddy_id, b.jid
			)
			select count(1) from results_cte;
			end
		end
end
-- QUERY END:
GO

-- QUERY START:
IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'Tig_UA_AddItem')
	DROP PROCEDURE Tig_UA_AddItem
-- QUERY END:
GO

-- QUERY START:
create procedure Tig_UA_AddItem
	@_ownerJid nvarchar(2049), 
	@_buddyJid nvarchar(2049),
	@_buddyRes nvarchar(1024), 
	@_ts datetime, 
	@_direction smallint,
	@_type varchar(20),
	@_body nvarchar(max),
	@_msg nvarchar(max),
	@_hash nvarchar(50),
	@_itemType nvarchar(50),
	@_offline smallint,
	@_jingleSid nvarchar(128),
	@_jingleAction nvarchar(50),
	@_jingleReason nvarchar(50),
	@_jingleOffline smallint
AS
begin
	declare @_owner_id bigint;
	declare @_buddy_id bigint;
	declare @_tsFrom datetime;
	declare @_tsTo datetime;

	if @_type = 'groupchat'
	    select @_tsFrom = DATEADD(minute, -30, @_ts), @_tsTo = DATEADD(minute, 30, @_ts);
	else
	    select @_tsFrom = @_ts, @_tsTo = @_ts;


	exec Tig_MA_EnsureJid @_jid=@_ownerJid, @_jid_id=@_owner_id output;
	exec Tig_MA_EnsureJid @_jid=@_buddyJid, @_jid_id=@_buddy_id output;

	insert into tig_ma_msgs (owner_id, buddy_id, buddy_res, ts, direction, type, body, msg, stanza_hash, item_type, offline,
	        jingle_sid, jingle_action, jingle_reason, jingle_offline)
		select @_owner_id, @_buddy_id, @_buddyRes, @_ts, @_direction, @_type, @_body, @_msg, @_hash, @_itemType, @_offline,
		    @_jingleSid, @_jingleAction, @_jingleReason, @_jingleOffline
		where not exists (
			select 1 from tig_ma_msgs
			    where owner_id = @_owner_id
			        and buddy_id = @_buddy_id
			        and stanza_hash = @_hash
			        and ts between @_tsFrom and @_tsTo
		);
	select @@IDENTITY as msg_id
end
-- QUERY END:
GO

-- QUERY START:
IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'Tig_UA_QueryRecent')
	DROP PROCEDURE [dbo].[Tig_UA_QueryRecent]
-- QUERY END:
GO

-- QUERY START:
create procedure [dbo].[Tig_UA_QueryRecent]
	@_ownerJid nvarchar(2049),
	@_from datetime,
	@_to datetime,
	@_chat smallint,
	@_groupchat smallint,
	@_call smallint,
	@_call_success smallint,
	@_call_missed smallint,
	@_call_canceled smallint,
	@_limit int,
	@_offset int
AS
begin
    select b2.jid, m2.ts, m2.direction,m2.msg,
		case m2.item_type
			when 'chat' then case m2.type
				when 'groupchat' then 'groupchat'
				else 'chat'
			end
			when 'call' then 'call'
		end as item_type,
		case
			when m2.item_type <> 'call' then null
			when m2.jingle_action='session-accept' or (m2.jingle_action='session-terminate' and m2.jingle_reason = 'success') then 'success'
			when m2.jingle_offline = 1
				or (m2.jingle_action = 'session-terminate' and (
					m2.jingle_reason = 'busy' or m2.jingle_reason = 'timeout'
					or m2.jingle_reason = 'failed-application'
					or (m2.jingle_reason = 'cancel' and m2.direction = 1 and ac.msg_id is null)
				))
				or m2.jingle_action = 'session-initiate' then 'missed'
			else 'canceled'
		end as condition,
		m2.stanza_hash,
		datediff(s, ac.ts, m2.ts) as duration
    from (
        select max(x2.msg_id) as msg_id, ROW_NUMBER() over (order by MAX(x2.ts) desc) as rn from (
        select x1.owner_id, x1.buddy_id, max(x1.ts) as ts
        from
	        (select m1.msg_id, m1.owner_id, m1.buddy_id, m1.ts,
		        case m1.item_type
			        when 'chat' then case m1.type
				        when 'groupchat' then 'groupchat'
				        else 'chat'
			        end
			        when 'call' then 'call'
		        end as item_type,
		        case
			        when m1.item_type <> 'call' then null
        			when m1.jingle_action='session-accept' or (m1.jingle_action='session-terminate' and m1.jingle_reason = 'success') then 'success'
		        	when m1.jingle_offline = 1
				        or (m1.jingle_action = 'session-terminate' and (
					        m1.jingle_reason = 'busy' or m1.jingle_reason = 'timeout'
					        or m1.jingle_reason = 'failed-application'
					        or (m1.jingle_reason = 'cancel' and m1.direction = 1 and
					        not exists(select 1 from tig_ma_msgs m0 where m0.owner_id = m1.owner_id and m0.buddy_id = m1.buddy_id and m0.jingle_sid = m1.jingle_sid and m0.jingle_action = 'session-accept' and m0.ts < m1.ts))
				        ))
				        or m1.jingle_action = 'session-initiate' then 'missed'
			        else 'canceled'
		        end as condition
	        from tig_ma_msgs m1
	        where m1.owner_id = (select jid_id from tig_ma_jids where jid_fragment = left(@_ownerJid, 255) and jid = @_ownerJid)
	            and (@_from is null or ts >= @_from)
	            and (@_to is null or ts <= @_to)
	            and m1.offline <> 2
	        ) as x1
        where
	        (1=@_chat and x1.item_type = 'chat')
	        or (1=@_groupchat and x1.item_type = 'groupchat')
	        or (1=@_call and x1.item_type = 'call' and (
		        (1=@_call_success and x1.condition = 'success')
		        or (1=@_call_missed and x1.condition = 'missed')
		        or (1=@_call_canceled and x1.condition = 'canceled')
	        ))
	    group by x1.owner_id, x1.buddy_id
        ) as x
        inner join tig_ma_msgs x2 on x2.owner_id = x.owner_id and x2.buddy_id = x.buddy_id and x2.ts = x.ts
        group by x.owner_id, x.buddy_id
    ) as y
    inner join tig_ma_msgs m2 on m2.msg_id = y.msg_id
    inner join tig_ma_jids b2 on b2.jid_id = m2.buddy_id
    left outer join tig_ma_msgs ac on ac.owner_id = m2.owner_id and ac.buddy_id = m2.buddy_id and ac.jingle_sid = m2.jingle_sid and ac.jingle_action = 'session-accept'
    where rn > @_offset and rn <= @_offset + @_limit
    order by rn asc
end
-- QUERY END:
GO

-- QUERY START:
IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'Tig_UA_QueryRecentCount')
	DROP PROCEDURE [dbo].[Tig_UA_QueryRecentCount]
-- QUERY END:
GO

-- QUERY START:
create procedure [dbo].[Tig_UA_QueryRecentCount]
	@_ownerJid nvarchar(2049),
	@_from datetime,
	@_to datetime,
	@_chat smallint,
	@_groupchat smallint,
	@_call smallint,
	@_call_success smallint,
	@_call_missed smallint,
	@_call_canceled smallint
AS
begin
    select count(1)
    from (
        select max(x2.msg_id) as msg_id from (
        select x1.owner_id, x1.buddy_id, max(x1.ts) as ts
        from (select m1.msg_id, m1.owner_id, m1.buddy_id, m1.ts,
		    case m1.item_type
			    when 'chat' then case m1.type
				    when 'groupchat' then 'groupchat'
				    else 'chat'
			    end
			    when 'call' then 'call'
		    end as item_type,
		    case
			    when m1.item_type <> 'call' then null
			    when m1.jingle_action='session-accept' or (m1.jingle_action='session-terminate' and m1.jingle_reason = 'success') then 'success'
			    when m1.jingle_offline = 1
				    or (m1.jingle_action = 'session-terminate' and (
					    m1.jingle_reason = 'busy' or m1.jingle_reason = 'timeout'
					    or m1.jingle_reason = 'failed-application'
					    or (m1.jingle_reason = 'cancel' and m1.direction = 1 and not exists(select 1 from tig_ma_msgs m0 where m0.owner_id = m1.owner_id and m0.buddy_id = m1.buddy_id and m0.jingle_sid = m1.jingle_sid and m0.jingle_action = 'session-accept' and m0.ts < m1.ts))
				    ))
				    or m1.jingle_action = 'session-initiate' then 'missed'
			    else 'canceled'
		    end as condition
	        from tig_ma_msgs m1
	        where m1.owner_id = (select jid_id from tig_ma_jids where jid_fragment = left(@_ownerJid, 255) and jid = @_ownerJid)
	            and (@_from is null or ts >= @_from)
	            and (@_to is null or ts <= @_to)
	            and m1.offline <> 2
	    ) as x1
        where
	        (1=@_chat and x1.item_type = 'chat')
	        or (1=@_groupchat and x1.item_type = 'groupchat')
	        or (1=@_call and x1.item_type = 'call' and (
		        (1=@_call_success and x1.condition = 'success')
		        or (1=@_call_missed and x1.condition = 'missed')
		        or (1=@_call_canceled and x1.condition = 'canceled')
	        ))
	    group by x1.owner_id, x1.buddy_id
        ) as x
        inner join tig_ma_msgs x2 on x2.owner_id = x.owner_id and x2.buddy_id = x.buddy_id and x2.ts = x.ts
        group by x.owner_id, x.buddy_id
    ) as y
end
-- QUERY END:
GO

-- ----------------------------------------------------------------------------
-- Stored procedures for JDBCFlexibleOfflineMessageRetrievalRepository
-- ----------------------------------------------------------------------------

-- QUERY START:
IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'Tig_UA_FOR_GetMessagesCount')
	DROP PROCEDURE [dbo].[Tig_UA_FOR_GetMessagesCount]
-- QUERY END:
GO

-- QUERY START:
create procedure [dbo].[Tig_UA_FOR_GetMessagesCount]
	@_ownerJid nvarchar(2049)
AS
begin
	select item_type, count(msg_id)
		from tig_ma_msgs m inner join tig_ma_jids o on o.jid_id = m.owner_id
		where o.jid_sha1 = HASHBYTES('SHA1', LOWER(@_ownerJid)) and m.offline >= 1
		group by item_type;
end
-- QUERY END:
GO

-- QUERY START:
IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'Tig_UA_FOR_GetMessagesList')
	DROP PROCEDURE [dbo].[Tig_UA_FOR_GetMessagesList]
-- QUERY END:
GO

-- QUERY START:
create procedure [dbo].[Tig_UA_FOR_GetMessagesList]
	@_ownerJid nvarchar(2049)
AS
begin
	select b.jid, m.buddy_res, m.msg_id, m.item_type
		from tig_ma_msgs m inner join tig_ma_jids o on o.jid_id = m.owner_id
		inner join tig_ma_jids b on b.jid_id = m.buddy_id
		where o.jid_sha1 = HASHBYTES('SHA1', LOWER(@_ownerJid)) and m.offline >= 1
end
-- QUERY END:
GO

-- QUERY START:
IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'Tig_UA_FOR_GetMessages')
	DROP PROCEDURE [dbo].[Tig_UA_FOR_GetMessages]
-- QUERY END:
GO

-- QUERY START:
create procedure [dbo].[Tig_UA_FOR_GetMessages]
	@_ownerJid nvarchar(2049),
	@_msgIds nvarchar(max)
AS
begin
	declare
		@params_def nvarchar(max),
		@msg_query nvarchar(max);
	set @params_def = N'@_ownerJid nvarchar(2049)';
	set @msg_query = N'select m.msg as message, m.msg_id
		from tig_ma_msgs m inner join tig_ma_jids o on o.jid_id = m.owner_id
		where o.jid_sha1 = HASHBYTES(''SHA1'', LOWER(@_ownerJid)) and m.offline >= 1';
	if @_msgIds is not null
		set @msg_query = @msg_query + N' and m.msg_id in (' + @_msgIds + N')';
	execute sp_executesql @msg_query, @params_def, @_ownerJid=@_ownerJid
end
-- QUERY END:
GO

-- QUERY START:
IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'Tig_UA_FOR_RemoveMessages')
	DROP PROCEDURE [dbo].[Tig_UA_FOR_RemoveMessages]
-- QUERY END:
GO

-- QUERY START:
create procedure [dbo].[Tig_UA_FOR_RemoveMessages]
	@_ownerJid nvarchar(2049), 
	@_msgIds nvarchar(max)
AS
begin
	declare
		@ownerId bigint,
		@params_def nvarchar(max),
		@msg_query nvarchar(max);
	select @ownerId=jid_id from tig_ma_jids where jid_sha1 = HASHBYTES('SHA1', LOWER(@_ownerJid));
	set @params_def = N'@ownerId bigint';
	set @msg_query = N'delete from tig_ma_msgs
		where owner_id = @ownerId and offline >= 1';
	if @_msgIds is not null
		set @msg_query = @msg_query + N' and msg_id in (' + @_msgIds + N')';
	execute sp_executesql @msg_query, @params_def, @ownerId=@ownerId
end
-- QUERY END:
GO

-- QUERY START:
IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'Tig_UA_FOR_UnmarkOfflineMessages')
	DROP PROCEDURE [dbo].[Tig_UA_FOR_UnmarkOfflineMessages]
-- QUERY END:
GO

-- QUERY START:
create procedure [dbo].[Tig_UA_FOR_UnmarkOfflineMessages]
	@_ownerJid nvarchar(2049), 
	@_msgIds nvarchar(max)
AS
begin
	declare
		@ownerId bigint,
		@params_def nvarchar(max),
		@msg_query nvarchar(max);
	select @ownerId=jid_id from tig_ma_jids where jid_sha1 = HASHBYTES('SHA1', LOWER(@_ownerJid));
	set @params_def = N'@ownerId bigint';
	set @msg_query = N'delete from tig_ma_msgs
		where owner_id = @ownerId and offline = 2';
	if @_msgIds is not null
		set @msg_query = @msg_query + N' and msg_id in (' + @_msgIds + N')';
	execute sp_executesql @msg_query, @params_def, @ownerId=@ownerId

	set @msg_query = N'update tig_ma_msgs set offline = 0
		where owner_id = @ownerId and offline = 1';
	if @_msgIds is not null
		set @msg_query = @msg_query + N' and msg_id in (' + @_msgIds + N')';
	execute sp_executesql @msg_query, @params_def, @ownerId=@ownerId
end
-- QUERY END:
GO

-- QUERY START:
exec TigSetComponentVersion 'unified-archive', '2.0.0';
-- QUERY END:
GO
