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

\i database/postgresql-message-archiving-2.0.0.sql

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

-- QUERY START:
do $$
begin
if not exists(select 1 from information_schema.columns where table_catalog = current_database() AND table_schema = 'public' 
		AND table_name = 'tig_ma_msgs' AND column_name = 'item_type') then
	alter table tig_ma_msgs add item_type varchar(50);
end if;
end$$;
-- QUERY END:
-- QUERY START:
update tig_ma_msgs set item_type = 'chat' where item_type is null;
-- QUERY END:
-- QUERY START:
do $$
begin
if exists (select 1 where (select to_regclass('public.tig_ma_msgs_owner_id_item_type_index')) is null) then
	create index tig_ma_msgs_owner_id_item_type_index on tig_ma_msgs (owner_id, item_type);
end if;
end$$;
-- QUERY END:

-- QUERY START:
do $$
begin
if not exists(select 1 from information_schema.columns where table_catalog = current_database() AND table_schema = 'public' 
		AND table_name = 'tig_ma_msgs' AND column_name = 'offline') then
	alter table tig_ma_msgs add offline smallint;
end if;
end$$;
-- QUERY END:
-- QUERY START:
update tig_ma_msgs set offline = 0 where offline is null;
-- QUERY END:
-- QUERY START:
do $$
begin
if exists (select 1 where (select to_regclass('public.tig_ma_msgs_owner_id_offline_index')) is null) then
	create index tig_ma_msgs_owner_id_offline_index on tig_ma_msgs (owner_id, offline);
end if;
end$$;
-- QUERY END:

-- QUERY START:
do $$
begin
if not exists(select 1 from information_schema.columns where table_catalog = current_database() AND table_schema = 'public'
                AND table_name = 'tig_ma_msgs' AND column_name = 'jingle_sid') then
        alter table tig_ma_msgs add jingle_sid varchar(128);
end if;
end$$;
-- QUERY END:
-- QUERY START:
do $$
begin
if not exists(select 1 from information_schema.columns where table_catalog = current_database() AND table_schema = 'public'
                AND table_name = 'tig_ma_msgs' AND column_name = 'jingle_reason') then
        alter table tig_ma_msgs add jingle_reason varchar(50);
end if;
end$$;
-- QUERY END:
-- QUERY START:
do $$
begin
if not exists(select 1 from information_schema.columns where table_catalog = current_database() AND table_schema = 'public'
                AND table_name = 'tig_ma_msgs' AND column_name = 'jingle_action') then
        alter table tig_ma_msgs add jingle_action varchar(50);
end if;
end$$;
-- QUERY END:
-- QUERY START:
do $$
begin
if not exists(select 1 from information_schema.columns where table_catalog = current_database() AND table_schema = 'public'
                AND table_name = 'tig_ma_msgs' AND column_name = 'jingle_offline') then
        alter table tig_ma_msgs add jingle_offline smallint default 0;
end if;
end$$;
-- QUERY END:

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

-- QUERY START:
do $$
begin
if exists( select 1 from pg_proc where proname = 'tig_ua_getitems' and (
        pg_get_function_result(oid) = 'TABLE(msg text, ts timestamp without time zone, direction smallint, "buddyJid" character varying, item_type character varying)'
        or pg_get_function_result(oid) = 'TABLE(msg text, ts timestamp without time zone, direction smallint, "buddyJid" character varying, "stanza_hash" character varying, item_type character varying)')) then
    drop function Tig_UA_GetItems(_ownerJid varchar(2049), _buddyJid varchar(2049), _from timestamp, _to timestamp, _tags text, _contains text, _itemType varchar(50), _offline smallint, _limit int, _offset int);
end if;
end$$;
-- QUERY END:

-- QUERY START:
create or replace function Tig_UA_GetItems(_ownerJid varchar(2049), _buddyJid varchar(2049), _from timestamp with time zone, _to timestamp with time zone, _tags text, _contains text, _itemType varchar(50), _offline smallint, _limit int, _offset int) returns table(
	"msg" text, "ts" timestamp with time zone, "direction" smallint, "buddyJid" varchar(2049), "stanza_hash" varchar(50), item_type varchar(50)
) as $$
declare 
	tags_query text;
	contains_query text;
	msgs_query text;
	pagination_query text;
	query_sql text;
begin
	if _tags is not null or _contains is not null then
		select Tig_MA_GetHasTagsQuery(_tags) into tags_query;
		select Tig_MA_GetBodyContainsQuery(_contains) into contains_query;
		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 
			lower(o.jid) = lower(%L)
			and (%L is null or lower(b.jid) = lower(%L))
			and (%L is null or m.ts >= %L)
			and (%L is null or m.ts <= %L)
			and (%L is null or m.item_type = %L)
			and (%L is null or m.offline = %L)
			and m.offline <> 2';
		pagination_query := ' limit %s offset %s';
		query_sql = msgs_query || tags_query || contains_query || ' order by m.ts' || pagination_query;
		return query execute format(query_sql, _ownerJid, _buddyJid, _buddyJid, _from, _from, _to, _to, _itemType, _itemType, _offline, _offline, _limit, _offset);
	else
		return 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 
			lower(o.jid) = lower(_ownerJid)
			and (_buddyJid is null or lower(b.jid) = 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;
$$ LANGUAGE 'plpgsql'; 
-- QUERY END:

-- QUERY START:
do $$
begin
if exists( select 1 from pg_proc where proname = lower('Tig_UA_GetItemsCount') and pg_get_function_arguments(oid) = '_ownerjid character varying, _buddyjid character varying, _from timestamp without time zone, _to timestamp without time zone, _tags text, _contains text, _itemtype character varying, _offline smallint') then
    drop function Tig_UA_GetItemsCount(_ownerjid character varying, _buddyjid character varying, _from timestamp without time zone, _to timestamp without time zone, _tags text, _contains text, _itemtype character varying, _offline smallint);
end if;
end$$;
-- QUERY END:

-- QUERY START:
create or replace function Tig_UA_GetItemsCount(_ownerJid varchar(2049), _buddyJid varchar(2049), _from timestamp with time zone, _to timestamp with time zone, _tags text, _contains text, _itemType varchar(50), _offline smallint) returns table(
	"count" bigint
) as $$
declare 
	tags_query text;
	contains_query text;
	msgs_query text;
	query_sql text;
begin
	if _tags is not null or _contains is not null then
		select Tig_MA_GetHasTagsQuery(_tags) into tags_query;
		select Tig_MA_GetBodyContainsQuery(_contains) into contains_query;
		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 
			lower(o.jid) = lower(%L)
			and (%L is null or lower(b.jid) = lower(%L))
			and (%L is null or m.ts >= %L)
			and (%L is null or m.ts <= %L)
			and (%L is null or m.item_type = %L)
			and (%L is null or m.offline = %L)
			and m.offline <> 2';
		query_sql = msgs_query || tags_query || contains_query;
		return query execute format(query_sql, _ownerJid, _buddyJid, _buddyJid, _from, _from, _to, _to, _itemType, _itemType, _offline, _offline);
	else
		return 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 
			lower(o.jid) = lower(_ownerJid)
			and (_buddyJid is null or lower(b.jid) = 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;
$$ LANGUAGE 'plpgsql'; 
-- QUERY END:

-- QUERY START:
do $$
begin
if exists( select 1 from pg_proc where proname = lower('Tig_UA_GetItemPosition') and pg_get_function_arguments(oid) = '_ownerjid character varying, _buddyjid character varying, _from timestamp without time zone, _to timestamp without time zone, _tags text, _contains text, _hash character varying, _itemtype character varying, _offline smallint') then
    drop function Tig_UA_GetItemPosition(_ownerjid character varying, _buddyjid character varying, _from timestamp without time zone, _to timestamp without time zone, _tags text, _contains text, _hash character varying, _itemtype character varying, _offline smallint);
end if;
end$$;
-- QUERY END:

-- QUERY START:
create or replace function Tig_UA_GetItemPosition(_ownerJid varchar(2049), _buddyJid varchar(2049), _from timestamp with time zone, _to timestamp with time zone, _tags text, _contains text, _hash varchar(50), _itemType varchar(50), _offline smallint) returns table(
	"count" bigint
) as $$
declare
	tags_query text;
	contains_query text;
	msgs_query text;
	query_sql text;
begin
	if _tags is not null or _contains is not null then
		select Tig_MA_GetHasTagsQuery(_tags) into tags_query;
		select Tig_MA_GetBodyContainsQuery(_contains) into contains_query;
		msgs_query := 'select x.position from (
		select row_number() over (w) as position, m.stanza_hash
		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
			lower(o.jid_ = lower(%L)
			and (%L is null or lower(b.jid) = lower(%L))
			and (%L is null or m.ts >= %L)
			and (%L is null or m.ts <= %L)
			and (%L is null or m.item_type = %L)
			and (%L is null or m.offline = %L)
			and m.offline <> 2';
		query_sql = msgs_query || tags_query || contains_query || ' window w as (order by ts) ) x where x.stanza_hash = %L';
		return query execute format(query_sql, _ownerJid, _buddyJid, _buddyJid, _from, _from, _to, _to, _itemType, _itemType, _offline, _offline, _hash);
	else
		return query select x.position from (
		select row_number() over (w) as position, m.stanza_hash
		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
			lower(o.jid) = lower(_ownerJid)
			and (_buddyJid is null or lower(b.jid) = 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
		window w as (order by ts) ) x where x.stanza_hash = _hash;
	end if;
end;
$$ LANGUAGE 'plpgsql';
-- QUERY END:

-- QUERY START:
do $$
begin
if exists( select 1 from pg_proc where proname = lower('Tig_UA_GetCollections') and pg_get_function_arguments(oid) = '_ownerjid character varying, _buddyjid character varying, _from timestamp without time zone, _to timestamp without time zone, _tags text, _contains text, bytype smallint, _itemtype character varying, _offline smallint, _limit integer, _offset integer') then
    drop function Tig_UA_GetCollections(_ownerjid character varying, _buddyjid character varying, _from timestamp without time zone, _to timestamp without time zone, _tags text, _contains text, bytype smallint, _itemtype character varying, _offline smallint, _limit integer, _offset integer);
end if;
end$$;
-- QUERY END:

-- QUERY START:
create or replace function Tig_UA_GetCollections(_ownerJid varchar(2049), _buddyJid varchar(2049), _from timestamp with time zone, _to timestamp with time zone, _tags text, _contains text, byType smallint, _itemType varchar(50), _offline smallint, _limit int, _offset int) returns table(
	"ts" timestamp with time zone, "with" varchar(2049), "type" varchar(20)
) as $$
declare 
	tags_query text;
	contains_query text;
	msgs_query text;
	pagination_query text;
	groupby_query text;
	query_sql text;
begin
	if _tags is not null or _contains is not null then
		select Tig_MA_GetHasTagsQuery(_tags) into tags_query;
		select Tig_MA_GetBodyContainsQuery(_contains) into contains_query;
		msgs_query := 'select min(m.ts), b.jid';
		if byType = 1 then
			msgs_query := msgs_query || ', case when m.type = ''groupchat'' then cast(''groupchat'' as varchar(20)) else cast('''' as varchar(20)) end as "type"';
		else
			msgs_query := msgs_query || ', cast(null as varchar(20)) as "type"';
		end if;
		msgs_query := 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 
			lower(o.jid) = lower(%L)
			and (%L is null or lower(b.jid) = lower(%L))
			and (%L is null or m.ts >= %L)
			and (%L is null or m.ts <= %L)
			and (%L is null or m.item_type = %L)
			and (%L is null or m.offline = %L)
			and m.offline <> 2';
		if byType = 1 then
			groupby_query := ' group by date(m.ts), m.buddy_id, b.jid, case when m.type = ''groupchat'' then cast(''groupchat'' as varchar(20)) else cast('''' as varchar(20)) end';
		else
			groupby_query := ' group by date(m.ts), m.buddy_id, b.jid';
		end if;
		pagination_query := ' limit %s offset %s';
		query_sql := msgs_query || tags_query || contains_query || groupby_query || ' order by min(m.ts), b.jid' || pagination_query;
		return query execute format(query_sql, _ownerJid, _buddyJid, _buddyJid, _from, _from, _to, _to, _itemType, _itemType, _offline, _offline, _limit, _offset);
	else
		if byType = 1 then
			return query select min(m.ts), b.jid, case when m.type = 'groupchat' then cast('groupchat' as varchar(20)) else cast('' as varchar(20)) 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 
				lower(o.jid) = lower(_ownerJid)
				and (_buddyJid is null or lower(b.jid) = 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 cast('groupchat' as varchar(20)) else cast('' as varchar(20)) end
			order by min(m.ts), b.jid
			limit _limit offset _offset;
		else
			return query select min(m.ts), b.jid, cast(null as varchar(20)) 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 
				lower(o.jid) = lower(_ownerJid)
				and (_buddyJid is null or lower(b.jid) = 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;
$$ LANGUAGE 'plpgsql'; 
-- QUERY END:

-- QUERY START:
do $$
begin
if exists( select 1 from pg_proc where proname = lower('Tig_UA_GetCollectionsCount') and pg_get_function_arguments(oid) = '_ownerjid character varying, _buddyjid character varying, _from timestamp without time zone, _to timestamp without time zone, _tags text, _contains text, bytype smallint, _itemtype character varying, _offline smallint') then
    drop function Tig_UA_GetCollectionsCount(_ownerjid character varying, _buddyjid character varying, _from timestamp without time zone, _to timestamp without time zone, _tags text, _contains text, bytype smallint, _itemtype character varying, _offline smallint);
end if;
end$$;
-- QUERY END:

-- QUERY START:
create or replace function Tig_UA_GetCollectionsCount(_ownerJid varchar(2049), _buddyJid varchar(2049), _from timestamp with time zone, _to timestamp with time zone, _tags text, _contains text, byType smallint, _itemType varchar(50), _offline smallint) returns table(
	"count" bigint
) as $$
declare 
	tags_query text;
	contains_query text;
	msgs_query text;
	groupby_query text;
	query_sql text;
begin
	if _tags is not null or _contains is not null then
		select Tig_MA_GetHasTagsQuery(_tags) into tags_query;
		select Tig_MA_GetBodyContainsQuery(_contains) into contains_query;
		msgs_query := 'select count(1) from (select min(m.ts), b.jid';
		if byType = 1 then
			msgs_query := msgs_query || ', case when m.type = ''groupchat'' then cast(''groupchat'' as varchar(20)) else cast('''' as varchar(20)) end as "type"';
		else
			msgs_query := msgs_query || ', cast(null as varchar(20)) as "type"';
		end if;
		msgs_query := 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 
			lower(o.jid) = lower(%L)
			and (%L is null or lower(b.jid) = lower(%L))
			and (%L is null or m.ts >= %L)
			and (%L is null or m.ts <= %L)
			and (%L is null or m.item_type = %L)
			and (%L is null or m.offline = %L)
			and m.offline <> 2';
		if byType = 1 then
			groupby_query := ' group by date(m.ts), m.buddy_id, b.jid, case when m.type = ''groupchat'' then cast(''groupchat'' as varchar(20)) else cast('''' as varchar(20)) end';
		else
			groupby_query := ' group by date(m.ts), m.buddy_id, b.jid';
		end if;
		query_sql := msgs_query || tags_query || contains_query || groupby_query || ') x';
		return query execute format(query_sql, _ownerJid, _buddyJid, _buddyJid, _from, _from, _to, _to, _itemType, _itemType, _offline, _offline);
	else
		if byType = 1 then
			return query select count(1) from (select min(m.ts), b.jid, case when m.type = 'groupchat' then cast('groupchat' as varchar(20)) else cast('' as varchar(20)) 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 
				lower(o.jid) = lower(_ownerJid)
				and (_buddyJid is null or lower(b.jid) = 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 cast('groupchat' as varchar(20)) else cast('' as varchar(20)) end) x;
		else
			return query select count(1) from (select min(m.ts), b.jid, cast(null as varchar(20)) 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 
				lower(o.jid) = lower(_ownerJid)
				and (_buddyJid is null or lower(b.jid) = 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;
$$ LANGUAGE 'plpgsql'; 
-- QUERY END:

-- QUERY START:
do $$
begin
if exists( select 1 from pg_proc where proname = lower('Tig_UA_AddItem') and pg_get_function_arguments(oid) = '_ownerjid character varying, _buddyjid character varying, _buddyres character varying, _ts timestamp without time zone, _direction smallint, _type character varying, _body text, _msg text, _hash character varying, _itemtype character varying, _offline smallint, _jinglesid character varying, _jingleaction character varying, _jinglereason character varying, _jingleoffline smallint') then
    drop function Tig_UA_AddItem(_ownerjid character varying, _buddyjid character varying, _buddyres character varying, _ts timestamp without time zone, _direction smallint, _type character varying, _body text, _msg text, _hash character varying, _itemtype character varying, _offline smallint, _jinglesid character varying, _jingleaction character varying, _jinglereason character varying, _jingleoffline smallint);
end if;
end$$;
-- QUERY END:

-- QUERY START:
create or replace function Tig_UA_AddItem(_ownerJid varchar(2049), _buddyJid varchar(2049), _buddyRes varchar(1024), _ts timestamp with time zone,
	_direction smallint, _type varchar(20), _body text, _msg text, _hash varchar(50), _itemType varchar(50), _offline smallint,
	_jingleSid varchar(128), _jingleAction varchar(50), _jingleReason varchar(50), _jingleOffline smallint) returns bigint as $$
declare
	_owner_id bigint;
	_buddy_id bigint;
	_msg_id bigint;
	_tsFrom timestamp with time zone;
	_tsTo timestamp with time zone;
begin
    if _type = 'groupchat' then
        select _ts - interval '30 minutes', _ts + interval '30 minutes' into _tsFrom, _tsTo;
    else
        select _ts, _ts into _tsFrom, _tsTo;
    end if;

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

    begin
    	with inserted_msg as (
	    	insert into tig_ma_msgs (owner_id, buddy_id, buddy_res, ts, direction, "type", body, msg, stanza_hash, item_type, offline,
	    	    jingle_sid, jingle_action, jingle_reason, jingle_offline)
		    select _owner_id, _buddy_id, _buddyRes, _ts, _direction, _type, _body, _msg, _hash, _itemType, _offline,
		        _jingleSid, _jingleAction, _jingleReason, _jingleOffline
    		where not exists (
	    		select 1
		    	from tig_ma_msgs
			    where owner_id = _owner_id
			        and buddy_id = _buddy_id
    			    and stanza_hash = _hash
	    		    and ts between _tsFrom  and _tsTo
		    )
		    returning msg_id
	    )
	    select msg_id into _msg_id from inserted_msg;
	exception when unique_violation then
	end;

	return _msg_id;
end;
$$ LANGUAGE 'plpgsql';
-- QUERY END:

-- QUERY START:
drop function if exists Tig_UA_QueryRecent(varchar, timestamp, timestamp, smallint, smallint, smallint, smallint, smallint, smallint, int, int);
-- QUERY END:

-- QUERY START:
create or replace function Tig_UA_QueryRecent( _ownerJid varchar(2049), _from timestamp with time zone, _to timestamp with time zone, _chat smallint, _groupchat smallint, _call smallint, _call_success smallint, _call_missed smallint, _call_canceled smallint, _limit int, _offset int) returns table (
    jid varchar(2049), ts timestamp with time zone, direction smallint, msg text, item_type varchar(50), "condition" varchar(50), hash varchar(50), "duration" bigint
) as $$
begin
    return query select b2.jid, m2.ts, m2.direction,m2.msg,
		cast((case m2.item_type
			when 'chat' then case m2.type
				when 'groupchat' then 'groupchat'
				else 'chat'
			end
			when 'call' then 'call'
		end) as varchar(50)) as item_type,
		cast((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 varchar(50)) as "condition",
		m2.stanza_hash,
		cast(FLOOR(EXTRACT(EPOCH FROM(m2.ts - ac.ts))) as bigint) as duration
    from (
        select x.msg_id as msg_id, row_number() over (partition by x.owner_id, x.buddy_id order by x.ts desc, x.msg_id desc) as rn
        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 o.jid_id from tig_ma_jids o where lower(o.jid)= lower(_ownerJid))
	            and (_from is null or m1.ts >= _from)
	            and (_to is null or m1.ts <= _to)
                and m1.offline <> 2
	    ) as x
        where
	        (1=_chat and x.item_type = 'chat')
	        or (1=_groupchat and x.item_type = 'groupchat')
	        or (1=_call and x.item_type = 'call' and (
		        (1=_call_success and x.condition = 'success')
		        or (1=_call_missed and x.condition = 'missed')
		        or (1=_call_canceled and x.condition = 'canceled')
	        ))
    ) as y
    inner join tig_ma_msgs m2 on m2.msg_id = y.msg_id
    inner join tig_ma_jids b2 on b2.jid_id = m2.buddy_id
    left outer join tig_ma_msgs ac on ac.owner_id = m2.owner_id and ac.buddy_id = m2.buddy_id and ac.jingle_sid = m2.jingle_sid and ac.jingle_action = 'session-accept'
    where rn = 1
    order by m2.ts desc
    limit _limit offset _offset;
end;
$$ LANGUAGE 'plpgsql';
-- QUERY END:

-- QUERY START:
do $$
begin
if exists( select 1 from pg_proc where proname = lower('Tig_UA_QueryRecentCount') and pg_get_function_arguments(oid) = '_ownerjid character varying, _from timestamp without time zone, _to timestamp without time zone, _chat smallint, _groupchat smallint, _call smallint, _call_success smallint, _call_missed smallint, _call_canceled smallint') then
    drop function Tig_UA_QueryRecentCount(_ownerjid character varying, _from timestamp without time zone, _to timestamp without time zone, _chat smallint, _groupchat smallint, _call smallint, _call_success smallint, _call_missed smallint, _call_canceled smallint);
end if;
end$$;
-- QUERY END:

-- QUERY START:
create or replace function Tig_UA_QueryRecentCount( _ownerJid varchar(2049), _from timestamp with time zone, _to timestamp with time zone, _chat smallint, _groupchat smallint, _call smallint, _call_success smallint, _call_missed smallint, _call_canceled smallint) returns table (
    "count" bigint
) as $$
begin
    return query select count(1) from (
        select x.msg_id as msg_id, row_number() over (partition by x.owner_id, x.buddy_id order by x.ts desc, x.msg_id desc) as rn
        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 o.jid_id from tig_ma_jids o where lower(o.jid) = lower(_ownerJid))
	        and (_from is null or m1.ts >= _from)
	        and (_to is null or m1.ts <= _to)
	        and m1.offline <> 2
	    ) as x
    where
	    (1=_chat and x.item_type = 'chat')
	    or (1=_groupchat and x.item_type = 'groupchat')
	    or (1=_call and x.item_type = 'call' and (
		    (1=_call_success and x.condition = 'success')
		    or (1=_call_missed and x.condition = 'missed')
		    or (1=_call_canceled and x.condition = 'canceled')
	    ))
    ) as y
    where rn = 1;
end;
$$ LANGUAGE 'plpgsql';
-- QUERY END:

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

-- QUERY START:
create or replace function Tig_UA_FOR_GetMessagesCount(_ownerJid varchar(2049)) returns table (
	item_type varchar(50), "count" bigint
) as $$
begin
	return query select m.item_type, count(msg_id)
		from tig_ma_msgs m inner join tig_ma_jids o on o.jid_id = m.owner_id
		where lower(o.jid) = lower(_ownerJid) and m.offline >= 1
		group by m.item_type;
end;
$$ LANGUAGE 'plpgsql';
-- QUERY END:

-- QUERY START:
create or replace function Tig_UA_FOR_GetMessagesList(_ownerJid varchar(2049)) returns table (
	buddy_jid varchar(2049), buddy_res varchar(1024), msg_id bigint, item_type varchar(50)
) as $$
begin
	return query select b.jid, m.buddy_res, m.msg_id, m.item_type
		from tig_ma_msgs m inner join tig_ma_jids o on o.jid_id = m.owner_id
		inner join tig_ma_jids b on b.jid_id = m.buddy_id
		where lower(o.jid) = lower(_ownerJid) and m.offline >= 1;
end;
$$ LANGUAGE 'plpgsql';
-- QUERY END:

-- QUERY START:
create or replace function Tig_UA_FOR_GetMessages(_ownerJid varchar(2049), _msgIds text) returns table (
	message text, msg_id bigint
) as $$
declare
	msg_query text;
begin
	msg_query := 'select m.msg, m.msg_id
		from tig_ma_msgs m inner join tig_ma_jids o on o.jid_id = m.owner_id
		where lower(o.jid) = lower(%L) and m.offline >= 1';
	if _msgIds is not null then
		msg_query := msg_query || ' and m.msg_id in (' || _msgIds || ')';
	end if;
	return query execute format(msg_query, _ownerJid);
end;
$$ LANGUAGE 'plpgsql';
-- QUERY END:

-- QUERY START:
create or replace function Tig_UA_FOR_RemoveMessages(_ownerJid varchar(2049), _msgIds text) returns void as $$
declare
	ownerId bigint;
	msg_query text;
begin
	select jid_id into ownerId from tig_ma_jids where lower(jid) = lower(_ownerJid);
	msg_query := 'delete from tig_ma_msgs
		where owner_id = %L and offline >= 1';
	if _msgIds is not null then
		msg_query := msg_query || ' and msg_id in (' || _msgIds || ')';
	end if;
	execute format(msg_query, ownerId);
end;
$$ LANGUAGE 'plpgsql';
-- QUERY END:

-- QUERY START:
create or replace function Tig_UA_FOR_UnmarkOfflineMessages(_ownerJid varchar(2049), _msgIds text) returns void as $$
declare
	ownerId bigint;
	msg_query text;
begin
	select jid_id into ownerId from tig_ma_jids where lower(jid) = lower(_ownerJid);
	msg_query := 'delete from tig_ma_msgs
		where owner_id = %L and offline = 2';
	if _msgIds is not null then
		msg_query := msg_query || ' and msg_id in (' || _msgIds || ')';
	end if;
	execute format(msg_query, ownerId);

	msg_query := 'update tig_ma_msgs set offline = 0
		where owner_id = %L and offline = 1';
	if _msgIds is not null then
		msg_query := msg_query || ' and msg_id in (' || _msgIds || ')';
	end if;
	execute format(msg_query, ownerId);
end;
$$ LANGUAGE 'plpgsql';
-- QUERY END:

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