--
-- 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
--

source database/mysql-unified-archive-2.1.0.sql;

-- LOAD FILE: database/mysql-unified-archive-2.1.0.sql;

source database/mysql-message-archiving-3.0.0.sql;

-- LOAD FILE: database/mysql-message-archiving-3.0.0.sql;

-- QUERY START:
drop procedure if exists Tig_UA_GetItems;
-- QUERY END:

-- QUERY START:
drop procedure if exists Tig_UA_GetItemsCount;
-- QUERY END:

-- QUERY START:
drop procedure if exists Tig_UA_GetItemPosition;
-- QUERY END:

-- QUERY START:
drop procedure if exists Tig_UA_AddItem;
-- QUERY END:

-- QUERY START:
drop procedure if exists Tig_UA_FOR_AddItemOffline;
-- QUERY END:

-- QUERY START:
drop procedure if exists Tig_UA_AddItemJingle;
-- QUERY END:

-- QUERY START:
drop procedure if exists Tig_UA_GetCollections;
-- QUERY END:

-- QUERY START:
drop procedure if exists Tig_UA_GetCollectionsCount;
-- QUERY END:

-- QUERY START:
drop procedure if exists Tig_UA_QueryRecent;
-- QUERY END:

-- QUERY START:
drop procedure if exists Tig_UA_QueryRecentCount;
-- QUERY END:

-- QUERY START:
drop procedure if exists Tig_UA_Upgrade;
-- QUERY END:

-- QUERY START:
call TigAddColumnIfNotExists('tig_ma_msgs',  'type', 'varchar(20)');
-- QUERY END:

-- QUERY START:
call TigAddColumnIfNotExists('tig_ma_msgs',  'direction', 'smallint');
-- QUERY END:

delimiter //

-- QUERY START:
create table if not exists tig_ua_jingle (
    stable_id binary(16) not null,
    owner_id bigint unsigned not null,
    direction smallint not null,
    sid varchar(128) not null,
    action varchar(50) not null,
    reason varchar(50),
    offline smallint not null default 0,

    primary key (owner_id, stable_id),
    foreign key (owner_id, stable_id) references tig_ma_msgs (owner_id, stable_id) on delete cascade,
    foreign key (owner_id) references tig_ma_jids (jid_id)
)
ENGINE=InnoDB default character set utf8 ROW_FORMAT=DYNAMIC;
-- QUERY END:

-- QUERY START:
create table if not exists tig_ua_for (
    stable_id binary(16) not null,
    owner_id bigint unsigned not null,
    buddy_res varchar(1024),

    primary key (owner_id, stable_id),
    foreign key (owner_id, stable_id) references tig_ma_msgs (owner_id, stable_id) on delete cascade,
    foreign key (owner_id) references tig_ma_jids (jid_id)
)
ENGINE=InnoDB default character set utf8 ROW_FORMAT=DYNAMIC;
-- QUERY END:

-- QUERY START:
create procedure Tig_UA_Upgrade()
begin
    if exists (select 1 from information_schema.columns where table_schema = database() and table_name = 'tig_ma_msgs' and column_name = 'jingle_sid')
        and exists (select 1 from information_schema.columns where table_schema = database() and table_name = 'tig_ma_msgs' and column_name = 'direction') then
        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
            );
    end if;

    if exists (select 1 from information_schema.columns where table_schema = database() and table_name = 'tig_ma_msgs' and column_name = 'jingle_sid') then
        alter table tig_ma_msgs
            drop column jingle_sid,
            drop column jingle_action,
            drop column jingle_reason,
            drop column jingle_offline;
    end if;

    if exists (select 1 from information_schema.columns where table_schema = database() and table_name = 'tig_ma_msgs' and column_name = 'buddy_res') then
        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
            );

        alter table tig_ma_msgs
            drop column buddy_res;
    end if;

    if exists (select 1 from information_schema.columns where table_schema = database() and table_name = 'tig_ma_msgs' and column_name = 'item_type' and data_type = 'varchar') then
        if not exists(select 1 from information_schema.columns where table_schema = database() AND table_name = 'tig_ma_msgs' AND column_name = 'item_type_new') then
            alter table tig_ma_msgs add column item_type_new smallint not null default 1;
        end if;

        if exists(SELECT 1 FROM information_schema.statistics s1 WHERE s1.table_schema = database() AND s1.table_name = 'tig_ma_msgs' AND s1.index_name = 'tig_ma_msgs_owner_id_item_type_index') then
            drop index tig_ma_msgs_owner_id_item_type_index on tig_ma_msgs;
        end if;

        if exists(select 1 from information_schema.columns where table_schema = database() AND table_name = 'tig_ma_msgs' AND column_name = 'type') then
            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';
            alter table tig_ma_msgs drop column type;
        end if;

        alter table tig_ma_msgs
            drop column item_type;
        alter table tig_ma_msgs change item_type_new item_type smallint not null default 1;
    else
        if not exists(select 1 from information_schema.columns where table_schema = database() AND table_name = 'tig_ma_msgs' AND column_name = 'item_type') then
            alter table tig_ma_msgs add column item_type smallint not null default 1;
        end if;
    end if;

    if exists (select 1 from information_schema.columns where table_schema = database() and table_name = 'tig_ma_msgs' and column_name = 'direction') then
        alter table tig_ma_msgs
            drop column direction;
    end if;
end //
-- QUERY END:

delimiter ;

-- QUERY START:
call Tig_UA_Upgrade();
-- QUERY END:

-- QUERY START:
drop procedure if exists Tig_UA_Upgrade;
-- QUERY END:


delimiter //

-- QUERY START:
create procedure Tig_UA_AddItem(_ownerJid varchar(2049) CHARSET utf8, _buddyJid varchar(2049) CHARSET utf8, _ts timestamp(6),
    _stableId varchar(36) CHARSET utf8, _stanzaId varchar(64) CHARSET utf8, _refStableId varchar(36) CHARSET utf8,
	_body mediumtext CHARSET utf8mb4 collate utf8mb4_bin, _msg mediumtext CHARSET utf8mb4 collate utf8mb4_bin,
	_itemType smallint, _offline smallint)
begin
    declare _owner_id bigint;
	declare _buddy_id bigint;

	START TRANSACTION;
	call Tig_MA_EnsureJid(_ownerJid, _owner_id);
	COMMIT;
    START TRANSACTION;
	call Tig_MA_EnsureJid(_buddyJid, _buddy_id);
    COMMIT;

    START TRANSACTION;
    if _itemType = 2  then
        insert into tig_ma_msgs (owner_id, stable_id, buddy_id, ts, stanza_id, is_ref, ref_stable_id, body, msg,
                                 item_type, offline)
        select _owner_id, Tig_MA_UuidToOrdered(_stableId), _buddy_id, _ts, _stanzaId, case when _refStableId is null then 0 else 1 end, Tig_MA_UuidToOrdered(_refStableId), _body, _msg,
               _itemType, _offline
        where not exists (
                select 1 from tig_ma_msgs where owner_id = _owner_id and stable_id = Tig_MA_UuidToOrdered(_stableId)
        ) and not exists (
                select 1 from tig_ma_msgs where owner_id = _owner_id and buddy_id = _buddy_id and ts between TIMESTAMPADD(MINUTE, -30,_ts) and TIMESTAMPADD(MINUTE, 30, _ts) and stanza_id = _stanzaId
        );
    else
        insert into tig_ma_msgs (owner_id, stable_id, buddy_id, ts, stanza_id, is_ref, ref_stable_id, body, msg,
                                 item_type, offline)
        select _owner_id, Tig_MA_UuidToOrdered(_stableId), _buddy_id, _ts, _stanzaId, case when _refStableId is null then 0 else 1 end, Tig_MA_UuidToOrdered(_refStableId), _body, _msg,
               _itemType, _offline
        where not exists (
                select 1 from tig_ma_msgs where owner_id = _owner_id and stable_id = Tig_MA_UuidToOrdered(_stableId)
        );
    end if;
	COMMIT;
end //
-- QUERY END:

-- QUERY START:
create procedure Tig_UA_FOR_AddItemOffline(_ownerJid varchar(2049) CHARSET utf8, _stableId varchar(36) CHARSET utf8, _buddyRes varchar(1024))
begin
    declare _owner_id bigint;

    call Tig_MA_EnsureJid(_ownerJid, _owner_id);

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

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

    call Tig_MA_EnsureJid(_ownerJid, _owner_id);

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

-- QUERY START:
create procedure Tig_UA_GetItems( _ownerJid varchar(2049) CHARSET utf8, _buddyJid varchar(2049) CHARSET utf8, _from timestamp(6), _to timestamp(6), _refType tinyint,
    _tags text CHARSET utf8mb4 collate utf8mb4_bin, _contains text CHARSET utf8mb4 collate utf8mb4_bin, _itemType smallint, _offline smallint, _limit int, _offset int)
begin
	if _tags is not null or _contains is not null then
		set @ownerJid = _ownerJid;
		set @buddyJid = _buddyJid;
		set @from = _from;
		set @to = _to;
		set @itemType = _itemType;
		set @offline = _offline;
		set @limit = _limit;
		set @offset = _offset;
		select Tig_MA_GetHasTagsQuery(_tags) into @tags_query;
		select Tig_MA_GetBodyContainsQuery(_contains) into @contains_query;
		set @msgs_query = 'select m.msg, m.ts, b.jid, Tig_MA_OrderedToUuid(m.stable_id) as stable_id, Tig_MA_OrderedToUuid(m.ref_stable_id) as ref_stable_id, m.item_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 = SHA1(LOWER(?))
			and (? is null or b.jid_sha1 = SHA1(LOWER(?)))
            and (m.is_ref = 0 or m.is_ref = 1)
			and (? is null or m.ts >= ?)
			and (? is null or m.ts <= ?)
			and (? is null or (? <> 0 and m.item_type = ?) or (? = 0 and m.item_type in (0,1,2)))
			and (? is null or m.offline = ?)
			and m.offline <> 2';
		set @pagination_query = ' limit ? offset ?';
		set @query = CONCAT(@msgs_query, @tags_query, @contains_query, ' order by m.ts', @pagination_query);
		prepare stmt from @query;
		execute stmt using @ownerJid, @buddyJid, @buddyJid, @from, @from, @to, @to, @itemType, @itemType, @itemType, @itemType, @offline, @offline, @limit, @offset;
		deallocate prepare stmt;
	else
        case _refType
            when 0 then
                select m.msg, m.ts, b.jid, Tig_MA_OrderedToUuid(m.stable_id) as stable_id, Tig_MA_OrderedToUuid(m.ref_stable_id) as ref_stable_id, m.item_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 = SHA1(LOWER(_ownerJid))
			        and (_buddyJid is null or b.jid_sha1 = 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
		        order by m.ts
		        limit _limit offset _offset;
            when 1 then
                select m.msg, m.ts, b.jid, Tig_MA_OrderedToUuid(m.stable_id) as stable_id, Tig_MA_OrderedToUuid(m.ref_stable_id) as ref_stable_id, m.item_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 = SHA1(LOWER(_ownerJid))
                  and (_buddyJid is null or b.jid_sha1 = 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
                order by m.ts
                limit _limit offset _offset;
            else
                begin
                    declare _startTs timestamp(6);
                    declare _endTs timestamp(6);
                    select max(ts), min(ts) into _endTs, _startTs
                    from (
                        select m.ts as ts
                        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 = SHA1(LOWER(_ownerJid))
                            and (_buddyJid is null or b.jid_sha1 = 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
                            order by m.ts
                        limit _limit offset _offset
                    ) x;

                    if _buddyJid is null then
                        select ref.msg, ref.ts, b.jid, Tig_MA_OrderedToUuid(ref.stable_id) as stable_id, Tig_MA_OrderedToUuid(ref.ref_stable_id) as ref_stable_id, m.item_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 m.buddy_id = b.jid_id
                            inner join tig_ma_msgs ref on ref.ref_stable_id = m.stable_id and ref.owner_id = o.jid_id and ref.buddy_id = m.buddy_id
                        where
                            o.jid_sha1 = SHA1(LOWER(_ownerJid))
                            and m.is_ref = 0
                            and m.ts >= _startTs
                            and m.ts <= _endTs
                            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
                        order by ref.ts;
                    else
                        select ref.msg, ref.ts, b.jid, Tig_MA_OrderedToUuid(ref.stable_id) as stable_id, Tig_MA_OrderedToUuid(ref.ref_stable_id) as ref_stable_id, m.item_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 m.buddy_id = b.jid_id
                            inner join tig_ma_msgs ref on ref.ref_stable_id = m.stable_id and ref.owner_id = o.jid_id
                        where
                            o.jid_sha1 = SHA1(LOWER(_ownerJid))
                            and b.jid_sha1 = SHA1(LOWER(_buddyJid))
                            and m.is_ref = 0
                            and m.ts >= _startTs
                            and m.ts <= _endTs
                            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
                        order by ref.ts;
                    end if;
                end;
            end case;
	end if;
end //
-- QUERY END:

-- QUERY START:
create procedure Tig_UA_GetItemsCount( _ownerJid varchar(2049) CHARSET utf8, _buddyJid varchar(2049) CHARSET utf8, _from timestamp(6), _to timestamp(6), _refType tinyint,
    _tags text CHARSET utf8mb4 collate utf8mb4_bin, _contains text CHARSET utf8mb4 collate utf8mb4_bin, _itemType smallint, _offline smallint)
begin
    if _tags is not null or _contains is not null then
        set @ownerJid = _ownerJid;
        set @buddyJid = _buddyJid;
        set @from = _from;
        set @to = _to;
        set @itemType = _itemType;
        set @offline = _offline;
        select Tig_MA_GetHasTagsQuery(_tags) into @tags_query;
        select Tig_MA_GetBodyContainsQuery(_contains) into @contains_query;
        set @msgs_query = '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 = SHA1(LOWER(?))
			and (? is null or b.jid_sha1 = SHA1(LOWER(?)))
            and (m.is_ref = 0 or m.is_ref = 1)
			and (? is null or m.ts >= ?)
			and (? is null or m.ts <= ?)
			and (? is null or (? <> 0 and m.item_type = ?) or (? = 0 and m.item_type in (0,1,2)))
			and (? is null or m.offline = ?)
			and m.offline <> 2';
        set @query = CONCAT(@msgs_query, @tags_query, @contains_query);
        prepare stmt from @query;
        execute stmt using @ownerJid, @buddyJid, @buddyJid, @from, @from, @to, @to, @itemType, @itemType, @itemType, @itemType, @offline, @offline;
        deallocate prepare stmt;
    else
        case _refType
            when 1 then
                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 = SHA1(LOWER(_ownerJid))
                    and (_buddyJid is null or b.jid_sha1 = 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;
            else
                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 = SHA1(LOWER(_ownerJid))
                    and (_buddyJid is null or b.jid_sha1 = 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 case;
    end if;
end //
-- QUERY END:

-- QUERY START:
create procedure Tig_UA_GetItemPosition( _ownerJid varchar(2049) CHARSET utf8, _buddyJid varchar(2049) CHARSET utf8, _from timestamp(6), _to timestamp(6), _refType smallint, _tags text CHARSET utf8mb4 collate utf8mb4_bin, _contains text CHARSET utf8mb4 collate utf8mb4_bin, _stableId varchar(36) CHARSET utf8, _itemType smallint, _offline smallint)
begin
	if _tags is not null or _contains is not null then
		set @ownerJid = _ownerJid;
		set @buddyJid = _buddyJid;
		set @from = _from;
		set @to = _to;
		set @itemType = _itemType;
		set @offline = _offline;
		set @stableId = _stableId;
		select Tig_MA_GetHasTagsQuery(_tags) into @tags_query;
		select Tig_MA_GetBodyContainsQuery(_contains) into @contains_query;
		set @msgs_query = 'select x.position from (
		select m.stable_id, @row_number := @row_number + 1 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,
			(select @row_number := 0) as t
		where
			o.jid_sha1 = SHA1(LOWER(?))
			and (? is null or b.jid_sha1 = SHA1(LOWER(?)))
            and (m.is_ref = 0 or m.is_ref = 1)
			and (? is null or m.ts >= ?)
			and (? is null or m.ts <= ?)
			and (? is null or (? <> 0 and m.item_type = ?) or (? = 0 and m.item_type in (0,1,2)))
			and (? is null or m.offline = ?)
			and m.offline <> 2';
		set @query = CONCAT(@msgs_query, @tags_query, @contains_query, ' order by m.ts) x where x.stable_id = Tig_MA_UuidToOrdered(?)');
		prepare stmt from @query;
		execute stmt using @ownerJid, @buddyJid, @buddyJid, @from, @from, @to, @to, @itemType, @itemType, @itemType, @itemType, @offline, @offline, @stableId;
		deallocate prepare stmt;
	else
        case _refType
            when 1 then
		        select x.position from (
		        select m.stable_id, @row_number := @row_number + 1 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,
			        (select @row_number := 0) as t
		        where
			        o.jid_sha1 = SHA1(LOWER(_ownerJid))
			        and (_buddyJid is null or b.jid_sha1 = 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
		        order by m.ts) x where x.stable_id  = Tig_MA_UuidToOrdered(_stableId);
		    else
                select x.position from (
                select m.stable_id, @row_number := @row_number + 1 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,
                    (select @row_number := 0) as t
                where
                    o.jid_sha1 = SHA1(LOWER(_ownerJid))
                    and (_buddyJid is null or b.jid_sha1 = 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
                order by m.ts) x where x.stable_id  = Tig_MA_UuidToOrdered(_stableId);
        end case;
	end if;
end //
-- QUERY END:

-- QUERY START:
create procedure Tig_UA_GetCollections( _ownerJid varchar(2049) CHARSET utf8, _buddyJid varchar(2049) CHARSET utf8, _from timestamp(6), _to timestamp(6), _tags text CHARSET utf8mb4 collate utf8mb4_bin, _contains text CHARSET utf8mb4 collate utf8mb4_bin, _byType smallint, _itemType smallint, _offline smallint, _limit int, _offset int)
begin
    if _tags is not null or _contains is not null then
        set @ownerJid = _ownerJid;
        set @buddyJid = _buddyJid;
        set @from = _from;
        set @to = _to;
        set @itemType = _itemType;
        set @offline = _offline;
        set @limit = _limit;
        set @offset = _offset;
        select Tig_MA_GetHasTagsQuery(_tags) into @tags_query;
        select Tig_MA_GetBodyContainsQuery(_contains) into @contains_query;
        set @msgs_query = 'select min(m.ts), b.jid';
        if _byType = 1 then
            set @msgs_query = CONCAT( @msgs_query, ', case when m.item_type = 2 then 2 else 0 end as `type`');
        else
            set @msgs_query = CONCAT( @msgs_query, ', 0 as `type`');
        end if;
        set @msgs_query = CONCAT( @msgs_query,' 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 = SHA1(LOWER(?))
			and (? is null or b.jid_sha1 = SHA1(LOWER(?)))
			and (? is null or m.ts >= ?)
			and (? is null or m.ts <= ?)
			and (? is null or (? <> 0 and m.item_type = ?) or (? = 0 and m.item_type in (0,1,2)))
			and (? is null or m.offline = ?)
			and m.offline <> 2');
        set @groupby_query = '';
        if _byType = 1 then
            select ' group by date(m.ts), m.buddy_id, b.jid, case when m.item_type = 2 then 2 else 0 end' into @groupby_query;
        else
            select ' group by date(m.ts), m.buddy_id, b.jid' into @groupby_query;
        end if;
        set @pagination_query = ' limit ? offset ?';
        set @query = CONCAT(@msgs_query, @tags_query, @contains_query, @groupby_query, ' order by min(m.ts), b.jid', @pagination_query);
        prepare stmt from @query;
        execute stmt using @ownerJid, @buddyJid, @buddyJid, @from, @from, @to, @to, @itemType, @itemType, @itemType, @itemType, @offline, @offline, @limit, @offset;
        deallocate prepare stmt;
    else
        if _byType = 1 then
            select min(m.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 = SHA1(LOWER(_ownerJid))
              and (_buddyJid is null or b.jid_sha1 = 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 date(m.ts), m.buddy_id, b.jid, case when m.item_type = 2 then 2 else 0 end
            order by min(m.ts), b.jid
            limit _limit offset _offset;
        else
            select min(m.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 = SHA1(LOWER(_ownerJid))
              and (_buddyJid is null or b.jid_sha1 = 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 date(m.ts), m.buddy_id, b.jid
            order by min(m.ts), b.jid
            limit _limit offset _offset;
        end if;
    end if;
end //
-- QUERY END:

-- QUERY START:
create procedure Tig_UA_GetCollectionsCount( _ownerJid varchar(2049) CHARSET utf8, _buddyJid varchar(2049) CHARSET utf8, _from timestamp(6), _to timestamp(6), _tags text CHARSET utf8mb4 collate utf8mb4_bin, _contains text CHARSET utf8mb4 collate utf8mb4_bin, _byType smallint, _itemType smallint, _offline smallint)
begin
	if _tags is not null or _contains is not null then
		set @ownerJid = _ownerJid;
		set @buddyJid = _buddyJid;
		set @from = _from;
		set @to = _to;
		set @itemType = _itemType;
		set @offline = _offline;
		select Tig_MA_GetHasTagsQuery(_tags) into @tags_query;
		select Tig_MA_GetBodyContainsQuery(_contains) into @contains_query;
		set @msgs_query = 'select count(1) from (select min(m.ts), b.jid';
		if _byType = 1 then
			set @msgs_query = CONCAT( @msgs_query, ', case when m.item_type = 2 then 2 else 0 end as `type`');
		end if;
		set @msgs_query = CONCAT( @msgs_query,' 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 = SHA1(LOWER(?))
			and (? is null or b.jid_sha1 = SHA1(LOWER(?)))
			and (? is null or m.ts >= ?)
			and (? is null or m.ts <= ?)
			and (? is null or (? <> 0 and m.item_type = ?) or (? = 0 and m.item_type in (0,1,2)))
			and (? is null or m.offline = ?)
			and m.offline <> 2');
		if _byType = 1 then
			set @groupby_query = ' group by date(m.ts), m.buddy_id, b.jid, case when m.item_type = 2 then 2 else 0 end';
		else
			set @groupby_query = ' group by date(m.ts), m.buddy_id, b.jid';
		end if;
		set @query = CONCAT(@msgs_query, @tags_query, @contains_query, @groupby_query, ' ) x');
		prepare stmt from @query;
		execute stmt using @ownerJid, @buddyJid, @buddyJid, @from, @from, @to, @to, @itemType, @itemType, @itemType, @itemType, @offline, @offline;
		deallocate prepare stmt;
	else
		if _byType = 1 then
			select count(1) from (
				select min(m.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 = SHA1(LOWER(_ownerJid))
					and (_buddyJid is null or b.jid_sha1 = 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 date(m.ts), m.buddy_id, b.jid, case when m.item_type = 2 then 2 else 0 end
			) x;
		else
			select count(1) from (
				select min(m.ts), b.jid
				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 = SHA1(LOWER(_ownerJid))
					and (_buddyJid is null or b.jid_sha1 = 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 date(m.ts), m.buddy_id, b.jid
			) x;
		end if;
	end if;
end //
-- QUERY END:

-- QUERY START:
create procedure Tig_UA_QueryRecent( _ownerJid varchar(2049) CHARSET utf8, _from timestamp(6), _to timestamp(6), _chat smallint, _groupchat smallint, _call smallint, _call_success smallint, _call_missed smallint, _call_canceled smallint, _limit int, _offset int)
begin
    set @rn = 0;
    set @ownerId = 0;
    set @buddyId = 0;
    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",
		Tig_MA_OrderedToUuid(m2.stable_id) as stable_id,
		(unix_timestamp(m2.ts) - unix_timestamp(ac.ts)) as duration
    from (
    select x2.owner_id, x2.stable_id, (@rn := case when @ownerId = x2.owner_id and @buddyId = x2.buddy_id then @rn + 1 else 1 end) as rn, @ownerId := x2.owner_id, @buddyId := x2.buddy_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 = _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
    order by x2.owner_id, x2.buddy_id, x2.ts desc, x2.stable_id desc
) 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 = 1
order by m2.ts desc
limit _limit offset _offset;
end //
-- QUERY END:

-- QUERY START:
create procedure Tig_UA_QueryRecentCount( _ownerJid varchar(2049) CHARSET utf8, _from timestamp(6), _to timestamp(6), _chat smallint, _groupchat smallint, _call smallint, _call_success smallint, _call_missed smallint, _call_canceled smallint)
begin
    select count(1) from (
    select max(x2.ts) as ts 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 = _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:

delimiter ;

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

-- QUERY START:
drop procedure if exists Tig_UA_FOR_GetMessagesCount;
-- QUERY END:

-- QUERY START:
drop procedure if exists Tig_UA_FOR_GetMessagesList;
-- QUERY END:

-- QUERY START:
drop procedure if exists Tig_UA_FOR_GetMessages;
-- QUERY END:

-- QUERY START:
drop procedure if exists Tig_UA_FOR_RemoveMessages;
-- QUERY END:

-- QUERY START:
drop procedure if exists Tig_UA_FOR_UnmarkOfflineMessages;
-- QUERY END:

delimiter //

-- QUERY START:
create procedure Tig_UA_FOR_GetMessagesCount(_ownerJid varchar(2049) CHARSET utf8)
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 = SHA1(LOWER(_ownerJid)) and m.offline >= 1
		group by item_type;
end //
-- QUERY END:

-- QUERY START:
create procedure Tig_UA_FOR_GetMessagesList(_ownerJid varchar(2049) CHARSET utf8)
begin
	select b.jid as buddy_jid, f.buddy_res, Tig_MA_OrderedToUuid(m.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 = SHA1(LOWER(_ownerJid)) and m.offline >= 1;
end //
-- QUERY END:

-- QUERY START:
create procedure Tig_UA_FOR_GetMessages(_ownerJid varchar(2049) CHARSET utf8, _msgIds text CHARSET utf8)
begin
	set @ownerJid = _ownerJid;
	set @msg_query = 'select m.msg as message, Tig_MA_OrderedToUuid(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 = SHA1(LOWER(?)) and o.jid = ? and m.offline >= 1';
	if _msgIds is not null then
		set @msg_query = CONCAT(@msg_query, ' and m.stable_id in (' , CONCAT('Tig_MA_UuidToOrdered(', REPLACE(_msgIds, ',', '),Tig_MA_UuidToOrdered('), ')'), ')');
	end if;
	prepare stmt from @msg_query;
	execute stmt using @ownerJid, @ownerJid;
	deallocate prepare stmt;
end //
-- QUERY END:

-- QUERY START:
create procedure Tig_UA_FOR_RemoveMessages(_ownerJid varchar(2049) CHARSET utf8, _msgIds text CHARSET utf8)
begin
	select jid_id into @ownerId from tig_ma_jids where jid_sha1 = SHA1(LOWER(_ownerJid));
	set @msg_query = 'delete from tig_ma_msgs
		where owner_id = ? and offline >= 1';
	if _msgIds is not null then
		set @msg_query = CONCAT(@msg_query, ' and stable_id in (' , CONCAT('Tig_MA_UuidToOrdered(', REPLACE(_msgIds, ',', '),Tig_MA_UuidToOrdered('), ')'), ')');
	end if;
	prepare stmt from @msg_query;
	execute stmt using @ownerId;
	deallocate prepare stmt;
end //
-- QUERY END:

-- QUERY START:
create procedure Tig_UA_FOR_UnmarkOfflineMessages(_ownerJid varchar(2049) CHARSET utf8, _msgIds text CHARSET utf8)
begin
	select jid_id into @ownerId from tig_ma_jids where jid_sha1 = SHA1(LOWER(_ownerJid));
	set @msg_query = 'delete from tig_ma_msgs
	    where owner_id = ? and offline = 2';
	if _msgIds is not null then
		set @msg_query = CONCAT(@msg_query, ' and stable_id in (' , CONCAT('Tig_MA_UuidToOrdered(', REPLACE(_msgIds, ',', '),Tig_MA_UuidToOrdered('), ')'), ')');
	end if;
	prepare stmt from @msg_query;
	execute stmt using @ownerId;
	deallocate prepare stmt;

    set @msg_query = 'delete from tig_ua_for
	    where owner_id = ?';
    if _msgIds is not null then
        set @msg_query = CONCAT(@msg_query, ' and stable_id in (' , CONCAT('Tig_MA_UuidToOrdered(', REPLACE(_msgIds, ',', '),Tig_MA_UuidToOrdered('), ')'), ')');
    end if;
    prepare stmt from @msg_query;
    execute stmt using @ownerId;
    deallocate prepare stmt;

	set @msg_query = 'update tig_ma_msgs set offline = 0
		where owner_id = ? and offline = 1';
	if _msgIds is not null then
		set @msg_query = CONCAT(@msg_query, ' and stable_id in (' , CONCAT('Tig_MA_UuidToOrdered(', REPLACE(_msgIds, ',', '),Tig_MA_UuidToOrdered('), ')'), ')');
	end if;
	prepare stmt from @msg_query;
	execute stmt using @ownerId;
	deallocate prepare stmt;
end //
-- QUERY END:

-- QUERY START:
-- call TigSetComponentVersion('unified-archive', '3.0.0');
-- QUERY END:


delimiter ;

-- QUERY START:
create table if not exists tig_ua_file_metadata (
	stable_id binary(16) not null,
	owner_id bigint unsigned not null,
	url_hash varchar(40) not null,
	url varchar(2048) not null,
	name varchar(255),
	description varchar(1024),
	size bigint,
	media_type varchar(50),


	primary key (owner_id, stable_id, url_hash),
    foreign key (owner_id, stable_id) references tig_ma_msgs (owner_id, stable_id) on delete cascade,
	foreign key (owner_id) references tig_ma_jids (jid_id)
)
ENGINE=InnoDB default character set utf8 ROW_FORMAT=DYNAMIC;
-- QUERY END:

-- QUERY START:
drop procedure if exists Tig_UA_FileMetadata_AddItem;
-- QUERY END:

-- QUERY START:
drop procedure if exists Tig_UA_FileMetadata_QueryItems;
-- QUERY END:

-- QUERY START:
drop procedure if exists Tig_UA_FileMetadata_QueryItemsCount;
-- QUERY END:

delimiter //

-- QUERY START:
create procedure Tig_UA_FileMetadata_AddItem(_ownerJid varchar(2049) CHARSET utf8, _stableId varchar(36) CHARSET utf8, _urlHash varchar(40) CHARSET utf8, _url varchar(2048) CHARSET utf8, _media_type varchar(50) CHARSET utf8, _name varchar(255) CHARSET utf8, _description varchar(1024) CHARSET utf8, _size bigint)
begin
    declare _owner_id bigint;

    call Tig_MA_EnsureJid(_ownerJid, _owner_id);

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

-- QUERY START:
create procedure Tig_UA_FileMetadata_QueryItems(_domain varchar(2049) CHARSET utf8, _ownerJid varchar(2049) CHARSET utf8, _buddyJid varchar(2049) CHARSET utf8, _after timestamp(6), _before timestamp(6), _contains varchar(100) CHARSET utf8, _mediaTypeContains varchar(50) CHARSET utf8, _smallerThan bigint, _biggerThan bigint, _limit int, _offset int)
begin
    select o.jid, b.jid, m.ts, Tig_MA_OrderedToUuid(m.stable_id) as stable_id, fm.url, fm.media_type, fm.name, fm.description, fm.size
		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 = SHA1(LOWER(_ownerJid)))
		    and (_domain is null or o.domain = _domain)
            and (_buddyJid is null or b.jid_sha1 = 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 CONCAT('%', _mediaTypeContains, '%')))
            and (_contains is null or (fm.name like CONCAT('%', _contains, '%')) or (fm.description like CONCAT('%', _contains, '%')) or (fm.url like CONCAT('%', _contains, '%')))
        order by m.ts asc
        limit _limit offset _offset;
end //
-- QUERY END:

-- QUERY START:
create procedure Tig_UA_FileMetadata_QueryItemsCount(_domain varchar(2049) CHARSET utf8, _ownerJid varchar(2049) CHARSET utf8, _buddyJid varchar(2049) CHARSET utf8, _after timestamp(6), _before timestamp(6), _contains varchar(100) CHARSET utf8, _mediaTypeContains varchar(50) CHARSET utf8, _smallerThan bigint, _biggerThan bigint, _beforeStableId varchar(36) CHARSET utf8)
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 = SHA1(LOWER(_ownerJid)))
            and (_domain is null or o.domain = _domain)
            and (_buddyJid is null or b.jid_sha1 = 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 CONCAT('%', _mediaTypeContains, '%')))
            and (_contains is null or (fm.name like CONCAT('%', _contains, '%')) or (fm.description like CONCAT('%', _contains, '%')) or (fm.url like CONCAT('%', _contains, '%')))
            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 = SHA1(LOWER(_ownerJid)))
                        and m1.stable_id = Tig_MA_UuidToOrdered(_beforeStableId)
                )
            ));
end //
-- QUERY END:

delimiter ;