--
-- 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-3.0.0.sql

-- QUERY START:
IF NOT EXISTS (select * from sys.sysobjects where name='tig_ua_jingle' and xtype='U')
	CREATE  TABLE [dbo].[tig_ua_jingle] (
		[stable_id] [uniqueidentifier] NOT NULL,
		[owner_id] [bigint] NOT NULL,
		[direction] [smallint] NOT NULL,
		[sid] [nvarchar](128) NOT NULL,
		[action] [nvarchar](50) NOT NULL,
		[reason] [nvarchar](50),
		[offline] [smallint] not null default 0,

		PRIMARY KEY ( [owner_id], [stable_id] ),
        CONSTRAINT [FK_tig_ua_jingle_owner_id_stable_id] FOREIGN KEY ([owner_id], [stable_id])
            REFERENCES [dbo].[tig_ma_msgs] ([owner_id], [stable_id]) ON DELETE CASCADE,
		CONSTRAINT [FK_tig_ua_jingle_owner_id] FOREIGN KEY ([owner_id]) REFERENCES [dbo].[tig_ma_jids]([jid_id])
	);
-- QUERY END:
GO

-- QUERY START:
IF NOT EXISTS (select * from sys.sysobjects where name='tig_ua_for' and xtype='U')
	CREATE  TABLE [dbo].[tig_ua_for] (
		[stable_id] [uniqueidentifier] NOT NULL,
		[owner_id] [bigint] NOT NULL,
		[buddy_res] [nvarchar](1024),

		PRIMARY KEY ( [owner_id], [stable_id] ),
        CONSTRAINT [FK_tig_ua_for_owner_id_stable_id] FOREIGN KEY ([owner_id], [stable_id])
            REFERENCES [dbo].[tig_ma_msgs] ([owner_id], [stable_id]) ON DELETE CASCADE,
		CONSTRAINT [FK_tig_ua_for_owner_id] FOREIGN KEY ([owner_id]) REFERENCES [dbo].[tig_ma_jids]([jid_id])
	);
-- QUERY END:
GO

-- QUERY START:
IF EXISTS (SELECT * FROM sys.columns WHERE object_id = object_id('dbo.tig_ma_msgs') AND name = 'jingle_sid') AND EXISTS (SELECT * FROM sys.columns WHERE object_id = object_id('dbo.tig_ma_msgs') AND name = 'direction')
    BEGIN
        declare @query_sql nvarchar(max);
        set @query_sql = N'insert into tig_ua_jingle (stable_id, owner_id, direction, sid, action, reason, offline)
        select m.stable_id, m.owner_id, m.direction, m.jingle_sid, m.jingle_action, m.jingle_reason, m.jingle_offline
        from tig_ma_msgs m
        where
            m.jingle_sid is not null
          and not exists (
                select 1 from tig_ua_jingle j where j.owner_id = m.owner_id and j.stable_id = m.stable_id
            )';
        execute sp_executesql @query_sql
    END
-- QUERY END:
GO

-- QUERY START:
declare @sql NVARCHAR(MAX);
select @sql = 'alter table tig_ma_msgs drop constraint ' + con.name + ';'
from sys.default_constraints con
inner join sys.columns col on con.parent_column_id = col.column_id
where col.object_id = object_id('dbo.tig_ma_msgs') and col.name = 'jingle_offline';
if @sql is not null
begin
    exec sp_executeSQL @sql;
    select @sql = null;
end
-- QUERY END:
GO

-- QUERY START:
IF EXISTS (SELECT * FROM sys.columns WHERE object_id = object_id('dbo.tig_ma_msgs') AND name = 'jingle_sid')
    BEGIN
        alter table [tig_ma_msgs] drop column [jingle_sid];
        alter table [tig_ma_msgs] drop column [jingle_action];
        alter table [tig_ma_msgs] drop column [jingle_reason];
        alter table [tig_ma_msgs] drop column [jingle_offline];
    END
-- QUERY END:
GO

-- QUERY START:
IF EXISTS (SELECT * FROM sys.columns WHERE object_id = object_id('dbo.tig_ma_msgs') AND name = 'buddy_res')
    BEGIN
        declare @query_sql nvarchar(max);
        set @query_sql = N'insert into tig_ua_for (stable_id, owner_id, buddy_res)
        select m.stable_id, m.owner_id, m.buddy_res
        from tig_ma_msgs m
        where
                m.offline <> 0
          and not exists (
                select 1 from tig_ua_for f where f.owner_id = m.owner_id and f.stable_id = m.stable_id
            )';
        execute sp_executesql @query_sql
    END
-- QUERY END:
GO

-- QUERY START:
IF EXISTS (SELECT * FROM sys.columns WHERE object_id = object_id('dbo.tig_ma_msgs') AND name = 'buddy_res')
    alter table [tig_ma_msgs] drop column [buddy_res];
-- QUERY END:
GO

-- QUERY START:
IF EXISTS (SELECT * FROM sys.columns WHERE object_id = object_id('dbo.tig_ma_msgs') AND name = 'item_type' AND TYPE_NAME(system_type_id) = 'nvarchar')
BEGIN
    IF NOT EXISTS (SELECT * FROM sys.columns WHERE object_id = object_id('dbo.tig_ma_msgs') AND name = 'item_type_new')
        BEGIN
        alter table [tig_ma_msgs] add [item_type_new] [smallint] not null default 1;
        END
    IF EXISTS (select 1 from sys.indexes WHERE object_id = object_id('dbo.tig_ma_msgs') AND NAME ='IX_tig_ma_msgs_owner_id_item_type_index')
        BEGIN
        drop index IX_tig_ma_msgs_owner_id_item_type_index on [tig_ma_jids];
        END
    IF EXISTS (SELECT * FROM sys.columns WHERE object_id = object_id('dbo.tig_ma_msgs') AND name = 'type')
        BEGIN
        declare @query_sql nvarchar(max);
        set @query_sql = N'update tig_ma_msgs
        set item_type_new = case item_type
                                when ''chat'' then
                                    case type
                                        when ''groupchat'' then 2
                                        else 1
                                        end
                                when ''presence'' then 10
                                when ''login'' then 128
                                when ''logout'' then 129
                                when ''call'' then 132
                                else 0
            end where item_type <> ''chat'' and type <> ''groupchat''';
        execute sp_executesql @query_sql
        alter table [tig_ma_msgs] drop column [type];
        END
    ALTER TABLE [tig_ma_msgs] DROP COLUMN [item_type];
    EXEC sp_rename '[tig_ma_msgs].[item_type_new]', 'item_type', 'COLUMN';
END
-- QUERY END:
GO

-- QUERY START:
IF EXISTS (SELECT * FROM sys.columns WHERE object_id = object_id('dbo.tig_ma_msgs') AND name = 'direction')
    BEGIN
        alter table [tig_ma_msgs] drop column [direction];
    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),
	@_ts datetime,
	@_stableId nvarchar(36),
	@_stanzaId nvarchar(64),
	@_refStableId nvarchar(36),
	@_body nvarchar(max),
	@_msg nvarchar(max),
	@_itemType smallint,
	@_offline smallint
AS
begin
	declare @_owner_id bigint;
	declare @_buddy_id bigint;

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

	if @_itemType = 2
        insert into tig_ma_msgs (owner_id, buddy_id, ts, stable_id, stanza_id, ref_stable_id, body, msg, item_type, offline)
        select @_owner_id, @_buddy_id, @_ts, convert(uniqueidentifier,@_stableId), @_stanzaId, convert(uniqueidentifier,@_refStableId), @_body, @_msg, @_itemType, @_offline
        where not exists (
                select 1 from tig_ma_msgs
                where owner_id = @_owner_id
                  and buddy_id = @_buddy_id
                  and stable_id = convert(uniqueidentifier,@_stableId)
            ) and not exists (
                select 1 from tig_ma_msgs where owner_id = @_owner_id and buddy_id = @_buddy_id and ts between DATEADD(MINUTE, -30, @_ts) and DATEADD(MINUTE, 30, @_ts) and stanza_id = @_stanzaId
            );
	else
	    insert into tig_ma_msgs (owner_id, buddy_id, ts, stable_id, stanza_id, ref_stable_id, body, msg, item_type, offline)
		    select @_owner_id, @_buddy_id, @_ts, convert(uniqueidentifier,@_stableId), @_stanzaId, convert(uniqueidentifier,@_refStableId), @_body, @_msg, @_itemType, @_offline
		    where not exists (
			    select 1 from tig_ma_msgs
			        where owner_id = @_owner_id
			            and buddy_id = @_buddy_id
			            and stable_id = convert(uniqueidentifier,@_stableId)
		    );
end
-- QUERY END:
GO

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

-- QUERY START:
create procedure Tig_UA_FOR_AddItemOffline
	@_ownerJid nvarchar(2049),
	@_stableId nvarchar(36),
	@_buddyRes nvarchar(1024)
AS
begin
	declare @_owner_id bigint;

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

    insert into tig_ua_for (stable_id, owner_id, buddy_res) values (@_stableId, @_owner_id, @_buddyRes);
end
-- QUERY END:
GO

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

-- QUERY START:
create procedure Tig_UA_AddItemJingle
    @_ownerJid nvarchar(2049),
    @_stableId nvarchar(36),
    @_sid nvarchar(128),
    @_action nvarchar(50),
    @_reason nvarchar(50),
    @_offline smallint,
    @_direction smallint
AS
begin
    declare @_owner_id bigint;

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

    insert into tig_ua_jingle (stable_id, owner_id, sid, action, reason, offline, direction)
    values (@_stableId, @_owner_id, @_sid, @_action, @_reason, @_offline, @_direction);
end
-- QUERY END:
GO

-- 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,
	@_refType smallint,
	@_tags nvarchar(max),
	@_contains nvarchar(max),
	@_itemType smallint,
	@_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 smallint, @_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,  b.jid, convert(nvarchar(36),m.stable_id) as stable_id, convert(nvarchar(36),m.ref_stable_id) as ref_stable_id, 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 (m.is_ref = 0 or m.is_ref = 1)
			and (@_from is null or m.ts >= @_from)
			and (@_to is null or m.ts <= @_to)
			and (@_itemType is null or (@_itemType <> 0 and m.item_type = @_itemType) or (@_itemType = 0 and m.item_type in (0,1,2)))
			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
		if @_refType = 0
		    begin
        		;with results_cte as (
		        select m.msg, m.ts, b.jid, convert(nvarchar(36),m.stable_id) as stable_id, convert(nvarchar(36),m.ref_stable_id) as ref_stable_id, 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 m.is_ref = 0
			        and (@_from is null or m.ts >= @_from)
			        and (@_to is null or m.ts <= @_to)
			        and (@_itemType is null or (@_itemType <> 0 and m.item_type = @_itemType) or (@_itemType = 0 and m.item_type in (0,1,2)))
			        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
		else
		    begin
		    if @_refType = 1
		        begin
		            ;with results_cte as (
		            select m.msg, m.ts, b.jid, convert(nvarchar(36),m.stable_id) as stable_id, convert(nvarchar(36),m.ref_stable_id) as ref_stable_id, 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 (m.is_ref = 0 or m.is_ref = 1)
			            and (@_from is null or m.ts >= @_from)
			            and (@_to is null or m.ts <= @_to)
			            and (@_itemType is null or (@_itemType <> 0 and m.item_type = @_itemType) or (@_itemType = 0 and m.item_type in (0,1,2)))
			            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
		    else
		        begin
		            ;with results_cte as (
		                select m.owner_id, m.stable_id, 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 (m.is_ref = 0)
			                and (@_from is null or m.ts >= @_from)
			                and (@_to is null or m.ts <= @_to)
			                and (@_itemType is null or (@_itemType <> 0 and m.item_type = @_itemType) or (@_itemType = 0 and m.item_type in (0,1,2)))
			                and (@_offline is null or m.offline = @_offline)
			                and m.offline <> 2
		            )
		            select m.msg, m.ts, b.jid, convert(nvarchar(36),m.stable_id) as stable_id, convert(nvarchar(36),m.ref_stable_id) as ref_stable_id, m.item_type
		            from results_cte cte
		                inner join tig_ma_msgs m on m.owner_id = cte.owner_id and m.ref_stable_id = cte.stable_id
		                inner join tig_ma_jids b on b.jid_id = m.buddy_id
		                where cte.row_num >= @_offset + 1 and cte.row_num < @_offset + 1 + @_limit order by m.ts;
		        end
            end
		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,
	@_refType smallint,
	@_tags nvarchar(max),
	@_contains nvarchar(max),
	@_itemType smallint,
	@_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 smallint, @_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(1)
		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 (m.is_ref = 0 or m.is_ref = 1)
			and (@_from is null or m.ts >= @_from)
			and (@_to is null or m.ts <= @_to)
			and (@_itemType is null or (@_itemType <> 0 and m.item_type = @_itemType) or (@_itemType = 0 and m.item_type in (0,1,2)))
			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
		if @_refType = 1
		    begin
		        select count(1)
		        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 (m.is_ref = 0 or m.is_ref = 1)
			        and (@_from is null or m.ts >= @_from)
			        and (@_to is null or m.ts <= @_to)
			        and (@_itemType is null or (@_itemType <> 0 and m.item_type = @_itemType) or (@_itemType = 0 and m.item_type in (0,1,2)))
			        and (@_offline is null or m.offline = @_offline)
			        and m.offline <> 2;
			end
	    else
	        begin
	            select count(1)
		        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 (m.is_ref = 0)
			        and (@_from is null or m.ts >= @_from)
			        and (@_to is null or m.ts <= @_to)
			        and (@_itemType is null or (@_itemType <> 0 and m.item_type = @_itemType) or (@_itemType = 0 and m.item_type in (0,1,2)))
			        and (@_offline is null or m.offline = @_offline)
			        and m.offline <> 2
			end
		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,
	@_refType smallint,
	@_tags nvarchar(max),
	@_contains nvarchar(max),
	@_stableId nvarchar(36),
	@_itemType smallint,
	@_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 smallint, @_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 x.position from (
		select m.stable_id, 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 (m.is_ref = 0 or m.is_ref = 1)
			and (@_from is null or m.ts >= @_from)
			and (@_to is null or m.ts <= @_to)
			and (@_itemType is null or (@_itemType <> 0 and m.item_type = @_itemType) or (@_itemType = 0 and m.item_type in (0,1,2)))
			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.stable_id = convert(uniqueidentifier,@_stableId)';
		execute sp_executesql @query_sql, @params_def, @_ownerJid=@_ownerJid, @_buddyJid=@_buddyJid, @_from=@_from, @_to=@_to, @_itemType=@_itemType, @_offline=@_offline, @_stableId = @_stableId
		end
	else
		begin
		if @_refType = 1
		    begin
                select x.position from (
		            select m.stable_id, 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 (m.is_ref = 0 or m.is_ref = 1)
			            and (@_from is null or m.ts >= @_from)
			            and (@_to is null or m.ts <= @_to)
			            and (@_itemType is null or (@_itemType <> 0 and m.item_type = @_itemType) or (@_itemType = 0 and m.item_type in (0,1,2)))
			            and (@_offline is null or m.offline = @_offline)
			            and m.offline <> 2
		        ) x where x.stable_id = convert(uniqueidentifier,@_stableId);
		    end
		else
		    begin
                select x.position from (
		            select m.stable_id, 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 m.is_ref = 0
			            and (@_from is null or m.ts >= @_from)
			            and (@_to is null or m.ts <= @_to)
			            and (@_itemType is null or (@_itemType <> 0 and m.item_type = @_itemType) or (@_itemType = 0 and m.item_type in (0,1,2)))
			            and (@_offline is null or m.offline = @_offline)
			            and m.offline <> 2
		        ) x where x.stable_id = convert(uniqueidentifier,@_stableId)
		    end
		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 smallint,
    @_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 smallint, @_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.item_type = 2 then 2 else 0 end as type';
            else
                set @msgs_query = @msgs_query + N', 0 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 (@_itemType <> 0 and m.item_type = @_itemType) or (@_itemType = 0 and m.item_type in (0,1,2)))
			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.item_type = 2 then 2 else 0 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.item_type = 2 then 2 else 0 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 (@_itemType <> 0 and m.item_type = @_itemType) or (@_itemType = 0 and m.item_type in (0,1,2)))
                          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.item_type = 2 then 2 else 0 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, 0 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 (@_itemType <> 0 and m.item_type = @_itemType) or (@_itemType = 0 and m.item_type in (0,1,2)))
                          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.item_type = 2 then 2 else 0 end as type';
            else
                set @msgs_query = @msgs_query + N', 0 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 (@_itemType <> 0 and m.item_type = @_itemType) or (@_itemType = 0 and m.item_type in (0,1,2)))
			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.item_type = 2 then 2 else 0 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.item_type = 2 then 2 else 0 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 (@_itemType <> 0 and m.item_type = @_itemType) or (@_itemType = 0 and m.item_type in (0,1,2)))
                          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.item_type = 2 then 2 else 0 end
                    )
                     select count(1) from results_cte;
                end
            else
                begin
                    ;with results_cte as (
                        select min(ts) as ts, b.jid, 0 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 (@_itemType <> 0 and m.item_type = @_itemType) or (@_itemType = 0 and m.item_type in (0,1,2)))
                          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_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.msg, m2.item_type,
		case
			when m2.item_type <> 132 then null
			when j2.action='session-accept' or (j2.action='session-terminate' and j2.reason = 'success') then 'success'
			when j2.offline = 1
				or (j2.action = 'session-terminate' and (
					j2.reason = 'busy' or j2.reason = 'timeout'
					or j2.reason = 'failed-application'
					or (j2.reason = 'cancel' and j2.direction = 1 and ac.stable_id is null)
				))
				or j2.action = 'session-initiate' then 'missed'
			else 'canceled'
		end as condition,
		convert(varchar(36), m2.stable_id) as stable_id,
		datediff(s, ac.ts, m2.ts) as duration
    from (
        select x.owner_id, x2.stable_id, ROW_NUMBER() over (order by x2.ts desc) as rn from (
        select x1.owner_id, x1.stable_id, ROW_NUMBER() over (partition by x1.owner_id, x1.buddy_id order by x1.ts desc, x1.stable_id desc) as rn1
        from
	        (select m1.owner_id, m1.buddy_id, m1.ts, m1.stable_id, m1.item_type,
		        case
			        when m1.item_type <> 132 then null
        			when j1.action='session-accept' or (j1.action='session-terminate' and j1.reason = 'success') then 'success'
		        	when j1.offline = 1
				        or (j1.action = 'session-terminate' and (
					        j1.reason = 'busy' or j1.reason = 'timeout'
					        or j1.reason = 'failed-application'
					        or (j1.reason = 'cancel' and j1.direction = 1 and
					        not exists (select 1 from tig_ma_msgs m0 inner join tig_ua_jingle j0 on j0.owner_id = m0.owner_id and j0.stable_id = m0.stable_id where m0.owner_id = m1.owner_id and m0.buddy_id = m1.buddy_id and j0.sid = j1.sid and j0.action = 'session-accept' and m0.ts < m1.ts))
				        ))
				        or j1.action = 'session-initiate' then 'missed'
			        else 'canceled'
		        end as condition
	        from tig_ma_msgs m1
            left join tig_ua_jingle j1 on j1.owner_id = m1.owner_id and j1.stable_id = m1.stable_id
	        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 = 1)
	        or (1=@_groupchat and x1.item_type = 2)
	        or (1=@_call and x1.item_type = 132 and (
		        (1=@_call_success and x1.condition = 'success')
		        or (1=@_call_missed and x1.condition = 'missed')
		        or (1=@_call_canceled and x1.condition = 'canceled')
	        ))
        ) as x
        inner join tig_ma_msgs x2 on x2.owner_id = x.owner_id and x2.stable_id = x.stable_id
        where rn1 = 1
    ) as y
    inner join tig_ma_msgs m2 on m2.owner_id = y.owner_id and m2.stable_id = y.stable_id
    inner join tig_ma_jids b2 on b2.jid_id = m2.buddy_id
    left join tig_ua_jingle j2 on j2.owner_id = m2.owner_id and j2.stable_id = m2.stable_id
    left join tig_ua_jingle acj on acj.owner_id = m2.owner_id and acj.sid = j2.sid and j2.action = 'session-accept'
    left outer join tig_ma_msgs ac on ac.owner_id = m2.owner_id and ac.stable_id = acj.stable_id and ac.buddy_id = m2.buddy_id
    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.ts) as msg_id from (
        select x1.owner_id, x1.buddy_id, max(x1.ts) as ts
        from (select m1.stable_id, m1.owner_id, m1.buddy_id, m1.ts, m1.item_type,
		    case
			    when m1.item_type <> 132 then null
			    when j1.action='session-accept' or (j1.action='session-terminate' and j1.reason = 'success') then 'success'
			    when j1.offline = 1
				    or (j1.action = 'session-terminate' and (
					    j1.reason = 'busy' or j1.reason = 'timeout'
					    or j1.reason = 'failed-application'
					    or (j1.reason = 'cancel' and j1.direction = 1 and not exists(select 1 from tig_ma_msgs m0 inner join tig_ua_jingle j0 on j0.owner_id = m0.owner_id and j0.stable_id = m0.stable_id where m0.owner_id = m1.owner_id and m0.buddy_id = m1.buddy_id and j0.sid = j1.sid and j0.action = 'session-accept' and m0.ts < m1.ts))
				    ))
				    or j1.action = 'session-initiate' then 'missed'
			    else 'canceled'
		    end as condition
	        from tig_ma_msgs m1
            left join tig_ua_jingle j1 on j1.owner_id = m1.owner_id and j1.stable_id = m1.stable_id
	        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 = 1)
	        or (1=@_groupchat and x1.item_type = 2)
	        or (1=@_call and x1.item_type = 132 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(1)
		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, f.buddy_res, convert(nvarchar(36),m.stable_id) as stable_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
        left join tig_ua_for f on f.owner_id = m.owner_id and f.stable_id = m.stable_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, convert(nvarchar(36),m.stable_id) as stable_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.stable_id in (convert(uniqueidentifier,' + replace(@_msgIds, ',', N'),convert(uniqueidentifier,') + 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 stable_id in (convert(uniqueidentifier,' + replace(@_msgIds, ',', N'),convert(uniqueidentifier,') + 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 stable_id in (convert(uniqueidentifier,' + replace(@_msgIds, ',', N'),convert(uniqueidentifier,') + N'))';
	execute sp_executesql @msg_query, @params_def, @ownerId=@ownerId

    set @msg_query = N'delete from tig_ua_for
		where owner_id = @ownerId';
    if @_msgIds is not null
        set @msg_query = @msg_query + N' and stable_id in (convert(uniqueidentifier,' + replace(@_msgIds, ',', N'),convert(uniqueidentifier,') + 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 stable_id in (convert(uniqueidentifier,' + replace(@_msgIds, ',', N'),convert(uniqueidentifier,') + N'))';
	execute sp_executesql @msg_query, @params_def, @ownerId=@ownerId
end
-- QUERY END:
GO

-- QUERY START:
IF NOT EXISTS (select * from sys.sysobjects where name='tig_ua_file_metadata' and xtype='U')
	CREATE  TABLE [dbo].[tig_ua_file_metadata] (
		[stable_id] [uniqueidentifier] NOT NULL,
		[owner_id] [bigint] NOT NULL,
		[url_hash] [nvarchar](40) NOT NULL,
		[url] [nvarchar](2048) NOT NULL,
		[name] [nvarchar](255),
		[description] [nvarchar](1024),
		[size] [bigint],
		[media_type] [nvarchar](50),

		PRIMARY KEY ( [owner_id], [stable_id], [url_hash] ),
        CONSTRAINT [FK_tig_ua_file_metadata_owner_id_stable_id] FOREIGN KEY ([owner_id], [stable_id])
            REFERENCES [dbo].[tig_ma_msgs] ([owner_id], [stable_id]) ON DELETE CASCADE,
		CONSTRAINT [FK_tig_ua_file_metadata_owner_id] FOREIGN KEY ([owner_id]) REFERENCES [dbo].[tig_ma_jids]([jid_id])
	);
-- QUERY END:
GO

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

-- QUERY START:
create procedure [dbo].[Tig_UA_FileMetadata_AddItem]
	@_ownerJid nvarchar(2049),
	@_stableId nvarchar(36),
	@_urlHash nvarchar(40),
	@_url nvarchar(2048),
	@_media_type nvarchar(50),
	@_name nvarchar(255),
	@_description nvarchar(1024),
	@_size bigint
AS
begin
    declare @_owner_id bigint;

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

    insert into tig_ua_file_metadata (stable_id, owner_id, url_hash, url, name, description, size, media_type)
    values (convert(uniqueidentifier,@_stableId), @_owner_id, @_urlHash, @_url, @_name, @_description, @_size, @_media_type);
end
-- QUERY END:
GO

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

-- QUERY START:
create procedure [dbo].[Tig_UA_FileMetadata_QueryItems]
    @_domain nvarchar(1024),
	@_ownerJid nvarchar(2049),
	@_buddyJid nvarchar(2049),
	@_after datetime,
	@_before datetime,
	@_contains nvarchar(100),
	@_mediaTypeContains nvarchar(50),
	@_smallerThan bigint,
	@_biggerThan bigint,
	@_limit int,
	@_offset int
AS
begin
    ;with results_cte as (
    select o.jid as owner, b.jid as buddy, m.ts, convert(nvarchar(36),m.stable_id) as stable_id, fm.url, fm.media_type, fm.name, fm.description, fm.size, row_number() over (order by m.ts) as row_num
		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
		inner join tig_ua_file_metadata fm on fm.owner_id = m.owner_id and fm.stable_id = m.stable_id
		where
            (@_ownerJid is null or o.jid_sha1 = HASHBYTES('SHA1', LOWER(@_ownerJid)))
		    and (@_domain is null or o.domain_sha1 = HASHBYTES('SHA1', LOWER(@_domain)))
            and (@_buddyJid is null or o.jid_sha1 = HASHBYTES('SHA1', LOWER(@_buddyJid)))
            and (@_after is null or m.ts >= @_after)
            and (@_before is null or m.ts <= @_before)
            and (@_smallerThan is null or (fm.size < @_smallerThan))
		    and (@_biggerThan is null or (fm.size > @_biggerThan))
		    and (@_mediaTypeContains is null or (fm.media_type like N'%' + @_mediaTypeContains + N'%'))
            and (@_contains is null or (fm.name like N'%' + @_contains + N'%') or (fm.description like N'%' + @_contains + N'%') or (fm.url like N'%' + @_contains + N'%'))
    )
    select * from results_cte where row_num >= @_offset + 1 and row_num < @_offset + 1 + @_limit order by row_num;
end
-- QUERY END:
GO

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

-- QUERY START:
create procedure [dbo].[Tig_UA_FileMetadata_QueryItemsCount]
    @_domain nvarchar(1024),
	@_ownerJid nvarchar(2049),
	@_buddyJid nvarchar(2049),
	@_after datetime,
	@_before datetime,
	@_contains nvarchar(100),
	@_mediaTypeContains nvarchar(50),
	@_smallerThan bigint,
	@_biggerThan bigint,
	@_beforeStableId varchar(36)
AS
begin
    select count(1)
		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
		inner join tig_ua_file_metadata fm on fm.owner_id = m.owner_id and fm.stable_id = m.stable_id
		where
            (@_ownerJid is null or o.jid_sha1 = HASHBYTES('SHA1', LOWER(@_ownerJid)))
            and (@_domain is null or o.domain_sha1 = HASHBYTES('SHA1', LOWER(@_domain)))
            and (@_buddyJid is null or o.jid_sha1 = HASHBYTES('SHA1', LOWER(@_buddyJid)))
            and (@_after is null or m.ts >= @_after)
            and (@_before is null or m.ts <= @_before)
            and (@_smallerThan is null or (fm.size < @_smallerThan))
		    and (@_biggerThan is null or (fm.size > @_biggerThan))
		    and (@_mediaTypeContains is null or (fm.media_type like N'%' + @_mediaTypeContains + N'%'))
            and (@_contains is null or (fm.name like N'%' + @_contains + N'%') or (fm.description like N'%' + @_contains + N'%') or (fm.url like N'%' + @_contains + N'%'))
            and (@_beforeStableId is null or (
                m.ts < (select m1.ts
                    from tig_ma_msgs m1
                    inner join tig_ma_jids o1 on m1.owner_id = o1.jid_id
                    where
                        (@_ownerJid is null or o1.jid_sha1 = HASHBYTES('SHA1', LOWER(@_ownerJid)))
                        and m1.stable_id = convert(uniqueidentifier,@_beforeStableId)
                )
            ));
end
-- QUERY END:
GO