--
-- 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-message-archiving-2.0.0.sql;

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

-- QUERY START:
call TigAddColumnIfNotExists('tig_ma_msgs',  'item_type', 'varchar(50)');
-- QUERY END:
-- QUERY START:
update tig_ma_msgs set item_type = 'chat' where item_type is null;
-- QUERY END:
-- QUERY START:
call TigAddIndexIfNotExists('tig_ma_msgs', 'tig_ma_msgs_owner_id_item_type_index', 0, '(owner_id, item_type)');
-- QUERY END:

-- QUERY START:
call TigAddColumnIfNotExists('tig_ma_msgs',  'offline', 'smallint');
-- QUERY END:
-- QUERY START:
update tig_ma_msgs set offline = 0 where offline is null;
-- QUERY END:
-- QUERY START:
call TigAddIndexIfNotExists('tig_ma_msgs', 'tig_ma_msgs_owner_id_offline_index', 0, '(owner_id, offline)');
-- QUERY END:

-- QUERY START:
call TigAddColumnIfNotExists('tig_ma_msgs',  'jingle_sid', 'varchar(128)');
-- QUERY END:
-- QUERY START:
call TigAddColumnIfNotExists('tig_ma_msgs',  'jingle_reason', 'varchar(50)');
-- QUERY END:
-- QUERY START:
call TigAddColumnIfNotExists('tig_ma_msgs',  'jingle_action', 'varchar(50)');
-- QUERY END:
-- QUERY START:
call TigAddColumnIfNotExists('tig_ma_msgs',  'jingle_offline', 'smallint default 0');
-- QUERY END:

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

-- 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_GetCollections;
-- QUERY END:

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

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

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

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

delimiter //


-- QUERY START:
create procedure Tig_UA_GetItems( _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, _itemType varchar(50) CHARSET utf8, _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, m.direction, b.jid, m.stanza_hash, 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 (? is null or m.ts >= ?)
			and (? is null or m.ts <= ?)
			and (? is null or m.item_type = ?)
			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, @offline, @offline, @limit, @offset;
		deallocate prepare stmt;
	else
		select m.msg, m.ts, m.direction, b.jid, m.stanza_hash, 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 (_from is null or m.ts >= _from)
			and (_to is null or m.ts <= _to)
			and (_itemType is null or m.item_type = _itemType)
			and (_offline is null or m.offline = _offline)
			and m.offline <> 2
		order by m.ts
		limit _limit offset _offset;
	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), _tags text CHARSET utf8mb4 collate utf8mb4_bin, _contains text CHARSET utf8mb4 collate utf8mb4_bin, _itemType varchar(50) CHARSET utf8, _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(m.msg_id)
		from tig_ma_msgs m 
			inner join tig_ma_jids o on m.owner_id = o.jid_id 
			inner join tig_ma_jids b on b.jid_id = m.buddy_id
		where 
			o.jid_sha1 = 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 m.item_type = ?)
			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, @offline, @offline;
		deallocate prepare stmt;
	else
		select count(m.msg_id)
		from tig_ma_msgs m 
			inner join tig_ma_jids o on m.owner_id = o.jid_id 
			inner join tig_ma_jids b on b.jid_id = m.buddy_id
		where 
			o.jid_sha1 = 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 m.item_type = _itemType)
			and (_offline is null or m.offline = _offline)
			and m.offline <> 2;
	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), _tags text CHARSET utf8mb4 collate utf8mb4_bin, _contains text CHARSET utf8mb4 collate utf8mb4_bin, _hash varchar(50) CHARSET utf8, _itemType varchar(50) CHARSET utf8, _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 @hash = _hash;
		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.stanza_hash, @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 (? is null or m.ts >= ?)
			and (? is null or m.ts <= ?)
			and (? is null or m.item_type = ?)
			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.stanza_hash = ?');
		prepare stmt from @query;
		execute stmt using @ownerJid, @buddyJid, @buddyJid, @from, @from, @to, @to, @itemType, @itemType, @offline, @offline, @hash;
		deallocate prepare stmt;
	else
		select x.position from (
		select m.stanza_hash, @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 (_from is null or m.ts >= _from)
			and (_to is null or m.ts <= _to)
			and (_itemType is null or m.item_type = _itemType)
			and (_offline is null or m.offline = _offline)
			and m.offline <> 2
		order by m.ts) x where x.stanza_hash  = _hash;
	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 varchar(50) CHARSET utf8, _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.type = ''groupchat'' then ''groupchat'' else '''' end as `type`');
		else
			set @msgs_query = CONCAT( @msgs_query, ', null 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 m.item_type = ?)
			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.type = ''groupchat'' then ''groupchat'' else '''' 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, @offline, @offline, @limit, @offset;
		deallocate prepare stmt;
	else
		if _byType = 1 then
			select min(m.ts), b.jid, case when m.type = 'groupchat' then 'groupchat' else '' end as `type`
			from tig_ma_msgs m 
				inner join tig_ma_jids o on m.owner_id = o.jid_id 
				inner join tig_ma_jids b on b.jid_id = m.buddy_id
			where 
				o.jid_sha1 = 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 m.item_type = _itemType)
				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.type = 'groupchat' then 'groupchat' else '' end 
			order by min(m.ts), b.jid
			limit _limit offset _offset;
		else
			select min(m.ts), b.jid, null as `type`
			from tig_ma_msgs m 
				inner join tig_ma_jids o on m.owner_id = o.jid_id 
				inner join tig_ma_jids b on b.jid_id = m.buddy_id
			where 
				o.jid_sha1 = 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 m.item_type = _itemType)
				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 varchar(50) CHARSET utf8, _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.type = ''groupchat'' then ''groupchat'' else '''' 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 m.item_type = ?)
			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.type = ''groupchat'' then ''groupchat'' else '''' 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, @offline, @offline;
		deallocate prepare stmt;
	else
		if _byType = 1 then
			select count(1) from (
				select min(m.ts), b.jid, case when m.type = 'groupchat' then 'groupchat' else '' end as `type`
				from tig_ma_msgs m 
					inner join tig_ma_jids o on m.owner_id = o.jid_id 
					inner join tig_ma_jids b on b.jid_id = m.buddy_id
				where 
					o.jid_sha1 = 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 m.item_type = _itemType)
					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.type = 'groupchat' then 'groupchat' else '' 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 m.item_type = _itemType)
					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_AddItem(_ownerJid varchar(2049) CHARSET utf8, _buddyJid varchar(2049) CHARSET utf8,
	 _buddyRes varchar(1024)  CHARSET utf8, _ts timestamp(6), _direction smallint, _type varchar(20) CHARSET utf8,
	 _body text CHARSET utf8mb4 collate utf8mb4_bin, _msg text CHARSET utf8mb4 collate utf8mb4_bin, _hash varchar(50) CHARSET utf8,
	 _itemType varchar(50) CHARSET utf8, _offline smallint, _jingle_sid varchar(128), _jingle_action varchar(50),
	 _jingle_reason varchar(50), _jingle_offline smallint)
begin
	declare _owner_id bigint;
	declare _buddy_id bigint;
	declare _msg_id bigint;
	declare x bigint;

	START TRANSACTION;
	select Tig_MA_EnsureJid(_ownerJid) into _owner_id;
	select Tig_MA_EnsureJid(_buddyJid) into _buddy_id;

    set x = LAST_INSERT_ID();
	insert into tig_ma_msgs (owner_id, buddy_id, buddy_res, ts, direction, `type`, body, msg, stanza_hash, item_type, offline,
	        jingle_sid, jingle_action, jingle_reason, jingle_offline)
		values (_owner_id, _buddy_id, _buddyRes, _ts, _direction, _type, _body, _msg, _hash, _itemType, _offline,
		    _jingle_sid, _jingle_action, _jingle_reason, _jingle_offline)
		on duplicate key update direction = direction;

	select LAST_INSERT_ID() into _msg_id;
	COMMIT;

    if x <> _msg_id then
	    select _msg_id as msg_id;
    else
        select null as msg_id;
	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
    select b2.jid, m2.ts, m2.direction,m2.msg,
		case m2.item_type
			when 'chat' then case m2.type
				when 'groupchat' then 'groupchat'
				else 'chat'
			end
			when 'call' then 'call'
		end as item_type,
		case
			when m2.item_type <> 'call' then null
			when m2.jingle_action='session-accept' or (m2.jingle_action='session-terminate' and m2.jingle_reason = 'success') then 'success'
			when m2.jingle_offline = 1
				or (m2.jingle_action = 'session-terminate' and (
					m2.jingle_reason = 'busy' or m2.jingle_reason = 'timeout'
					or m2.jingle_reason = 'failed-application'
					or (m2.jingle_reason = 'cancel' and m2.direction = 1 and ac.msg_id is null)
				))
				or m2.jingle_action = 'session-initiate' then 'missed'
			else 'canceled'
		end as "condition",
		m2.stanza_hash,
		(unix_timestamp(m2.ts) - unix_timestamp(ac.ts)) as duration
    from (
    select max(x2.msg_id) as msg_id from (
    select x1.owner_id, x1.buddy_id, max(x1.ts) as ts
    from
	    (select
		    m1.msg_id, m1.owner_id, m1.buddy_id, m1.ts,
		    case m1.item_type
			    when 'chat' then case m1.type
				    when 'groupchat' then 'groupchat'
				    else 'chat'
			    end
			    when 'call' then 'call'
		    end as item_type,
		    case
			    when m1.item_type <> 'call' then null
			    when m1.jingle_action='session-accept' or (m1.jingle_action='session-terminate' and m1.jingle_reason = 'success') then 'success'
			    when m1.jingle_offline = 1
				    or (m1.jingle_action = 'session-terminate' and (
					    m1.jingle_reason = 'busy' or m1.jingle_reason = 'timeout'
					    or m1.jingle_reason = 'failed-application'
					    or (m1.jingle_reason = 'cancel' and m1.direction = 1 and
					    not exists(select 1 from tig_ma_msgs m0 where m0.owner_id = m1.owner_id and m0.buddy_id = m1.buddy_id and m0.jingle_sid = m1.jingle_sid and m0.jingle_action = 'session-accept' and m0.ts < m1.ts))
				    ))
				    or m1.jingle_action = 'session-initiate' then 'missed'
			    else 'canceled'
		    end as "condition"
	    from tig_ma_msgs m1
	    where m1.owner_id = (select jid_id from tig_ma_jids where jid = _ownerJid)
	        and (_from is null or ts >= _from)
	        and (_to is null or ts <= _to)
	        and m1.offline <> 2
	    ) as x1
    where
	    (1=_chat and x1.item_type = 'chat')
	    or (1=_groupchat and x1.item_type = 'groupchat')
	    or (1=_call and x1.item_type = 'call' and (
    		(1=_call_success and x1.condition = 'success')
	    	or (1=_call_missed and x1.condition = 'missed')
		    or (1=_call_canceled and x1.condition = 'canceled')
	    ))
	group by x1.owner_id, x1.buddy_id
    ) as x
    inner join tig_ma_msgs x2 on x2.owner_id = x.owner_id and x2.buddy_id = x.buddy_id and x2.ts = x.ts
    group by x.owner_id, x.buddy_id
) as y
inner join tig_ma_msgs m2 on m2.msg_id = y.msg_id
inner join tig_ma_jids b2 on b2.jid_id = m2.buddy_id
left outer join tig_ma_msgs ac on ac.owner_id = m2.owner_id and ac.buddy_id = m2.buddy_id and ac.jingle_sid = m2.jingle_sid and ac.jingle_action = 'session-accept'
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.msg_id) as msg_id from (
    select x1.owner_id, x1.buddy_id, max(x1.ts) as ts
    from
        (select
            m1.msg_id, m1.owner_id, m1.buddy_id, m1.ts,
            case m1.item_type
                when 'chat' then case m1.type
                    when 'groupchat' then 'groupchat'
                    else 'chat'
                end
                when 'call' then 'call'
            end as item_type,
            case
                when m1.item_type <> 'call' then null
                when m1.jingle_action='session-accept' or (m1.jingle_action='session-terminate' and m1.jingle_reason = 'success') then 'success'
                when m1.jingle_offline = 1
                    or (m1.jingle_action = 'session-terminate' and (
                        m1.jingle_reason = 'busy' or m1.jingle_reason = 'timeout'
                        or m1.jingle_reason = 'failed-application'
                        or (m1.jingle_reason = 'cancel' and m1.direction = 1 and not exists(select 1 from tig_ma_msgs m0 where m0.owner_id = m1.owner_id and m0.buddy_id = m1.buddy_id and m0.jingle_sid = m1.jingle_sid and m0.jingle_action = 'session-accept' and m0.ts < m1.ts))
                    ))
                    or m1.jingle_action = 'session-initiate' then 'missed'
                else 'canceled'
            end as "condition"
        from tig_ma_msgs m1
        where m1.owner_id = (select jid_id from tig_ma_jids where jid = _ownerJid)
        and (_from is null or ts >= _from)
        and (_to is null or ts <= _to)
        and m1.offline <> 2
    ) as x1
    where
        (1=_chat and x1.item_type = 'chat')
        or (1=_groupchat and x1.item_type = 'groupchat')
        or (1=_call and x1.item_type = 'call' and (
            (1=_call_success and x1.condition = 'success')
            or (1=_call_missed and x1.condition = 'missed')
            or (1=_call_canceled and x1.condition = 'canceled')
        ))
    group by x1.owner_id, x1.buddy_id
    ) as x
    inner join tig_ma_msgs x2 on x2.owner_id = x.owner_id and x2.buddy_id = x.buddy_id and x2.ts = x.ts
    group by x.owner_id, x.buddy_id
    ) as y;
end //
-- QUERY END:

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(msg_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(_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, m.buddy_res, m.msg_id, m.item_type
		from tig_ma_msgs m inner join tig_ma_jids o on o.jid_id = m.owner_id
		inner join tig_ma_jids b on b.jid_id = m.buddy_id
		where o.jid_sha1 = 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, m.msg_id
		from tig_ma_msgs m inner join tig_ma_jids o on o.jid_id = m.owner_id
		where o.jid_sha1 = SHA1(LOWER(?)) and o.jid = ? and m.offline >= 1';
	if _msgIds is not null then
		set @msg_query = CONCAT(@msg_query, ' and m.msg_id in (' , _msgIds, ')');
	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 msg_id in (' , _msgIds, ')');
	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 msg_id in (' , _msgIds, ')');
	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 msg_id in (' , _msgIds, ')');
	end if;
	prepare stmt from @msg_query;
	execute stmt using @ownerId;
	deallocate prepare stmt;
end //
-- QUERY END:

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

delimiter ;
