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

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

-- QUERY START:
create table if not exists tig_ua_jingle (
	stable_id uuid not null,
	owner_id bigint 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)
);
-- QUERY END:

-- QUERY START:
do $$
begin
if 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')
       and 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 = '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_catalog = current_database() AND table_schema = 'public' 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;
end$$;
-- QUERY END:

-- QUERY START:
create table if not exists tig_ua_for (
	stable_id uuid not null,
	owner_id bigint 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)
);
-- QUERY END:

-- QUERY START:
do $$
begin
if 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 = '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;
end$$;
-- QUERY END:

-- QUERY START:
do $$
begin
if 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' AND data_type = 'character varying') then
    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_new') then
        alter table tig_ma_msgs add column item_type_new smallint not null default 1;
    end if;

    if not exists (select 1 where (select to_regclass('public.tig_ma_msgs_owner_id_item_type_index')) is null) then
	    drop index tig_ma_msgs_owner_id_item_type_index;
    end if;

    if 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 = '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,
        drop column stanza_hash;
    alter table tig_ma_msgs rename column item_type_new to item_type;
else
    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 column item_type smallint not null default 1;
    end if;
end if;
if 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 = 'direction') then
    alter table tig_ma_msgs drop column direction;
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 with time zone, direction smallint, "buddyJid" character varying, item_type character varying)'
        or pg_get_function_result(oid) = 'TABLE(msg text, ts timestamp with 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 with time zone, _to timestamp with time zone, _tags text, _contains text, _itemType varchar(50), _offline smallint, _limit int, _offset int);
end if;
end$$;
-- QUERY END:

-- 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 with time zone, direction smallint, "buddyJid" character varying, stable_id character varying, item_type character varying)' and
    pg_get_function_arguments(oid) = '_ownerjid character varying, _buddyjid character varying, _from timestamp with time zone, _to timestamp with time zone, _tags text, _contains text, _itemtype character varying, _offline smallint, _limit integer, _offset integer') then
    drop function Tig_UA_GetItems(_ownerjid character varying, _buddyjid character varying, _from timestamp with time zone, _to timestamp with time zone, _tags text, _contains text, _itemtype character varying, _offline smallint, _limit integer, _offset integer);
end if;
end$$;
-- QUERY END:

-- 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 with time zone, direction smallint, "buddyJid" character varying, stable_id character varying, ref_stable_id character varying, item_type character varying)' and
    pg_get_function_arguments(oid) = '_ownerjid character varying, _buddyjid character varying, _from timestamp with time zone, _to timestamp with time zone, _tags text, _contains text, _itemtype character varying, _offline smallint, _limit integer, _offset integer') then
    drop function Tig_UA_GetItems(_ownerjid character varying, _buddyjid character varying, _from timestamp with time zone, _to timestamp with time zone, _refType smallint, _tags text, _contains text, _itemtype character varying, _offline smallint, _limit integer, _offset integer);
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,  _refType smallint, _tags text, _contains text, _itemType smallint, _offline smallint, _limit int, _offset int) returns table(
	"msg" text, "ts" timestamp with time zone, "buddyJid" varchar(2049), "stable_id" varchar(36), "ref_stable_id" varchar(36), item_type smallint
) as $$
declare 
	tags_query text;
	contains_query text;
	msgs_query text;
	pagination_query text;
	query_sql text;
    startTs timestamp with time zone;
    endTs timestamp with time zone;
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, b.jid, cast(m.stable_id as varchar(36)) as stable_id, cast(m.ref_stable_id as varchar(36)) 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 
			lower(o.jid) = lower(%L)
			and (%L is null or lower(b.jid) = lower(%L))
            and (m.is_ref = 0 or m.is_ref = 1)
			and (%L is null or m.ts >= %L)
			and (%L is null or m.ts <= %L)
		    and (%L is null or (%L <> 0 and m.item_type = %L) or (%L = 0 and m.item_type in (0,1,2)))
            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, _itemType, _itemType, _offline, _offline, _limit, _offset);
	else
        case _refType
            when 0 then
		        return query select m.msg, m.ts,b.jid, cast(m.stable_id as varchar(36)) as stable_id, cast(m.ref_stable_id as varchar(36)) 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
                        lower(o.jid) = lower(_ownerJid)
                        and (_buddyJid is null or lower(b.jid) = 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
		        return query select m.msg, m.ts, b.jid, cast(m.stable_id as varchar(36)) as stable_id, cast(m.ref_stable_id as varchar(36)) 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
			            lower(o.jid) = lower(_ownerJid)
			            and (_buddyJid is null or lower(b.jid) = 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
                select into endTs, startTs max(x.ts), min(x.ts)
                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
                        lower(o.jid) = lower(_ownerJid)
                        and (_buddyJid is null or lower(b.jid) = 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;
                return query select ref.msg, ref.ts, b.jid, cast(ref.stable_id as varchar(36)) as stable_id, cast(ref.ref_stable_id as varchar(36)) 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
                        lower(o.jid) = lower(_ownerJid)
                        and (_buddyJid is null or lower(b.jid) = 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 case;
	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 with time zone, _to timestamp with 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 with time zone, _to timestamp with time zone, _tags text, _contains text, _itemtype character varying, _offline smallint);
end if;
end$$;
-- 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 with time zone, _to timestamp with time zone, _reftype smallint, _tags text, _contains text, _itemtype character varying, _offline smallint') then
    drop function Tig_UA_GetItemsCount(_ownerjid character varying, _buddyjid character varying, _from timestamp with time zone, _to timestamp with time zone, _refType smallint, _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, _refType smallint, _tags text, _contains text, _itemType smallint, _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(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
			lower(o.jid) = lower(%L)
			and (%L is null or lower(b.jid) = lower(%L))
            and (m.is_ref = 0 or m.is_ref = 1)
			and (%L is null or m.ts >= %L)
			and (%L is null or m.ts <= %L)
		    and (%L is null or (%L <> 0 and m.item_type = %L) or (%L = 0 and m.item_type in (0,1,2)))
            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, _itemType, _itemType, _offline, _offline);
    else
        case _refType
            when 1 then
		        return 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
                        lower(o.jid) = lower(_ownerJid)
                        and (_buddyJid is null or lower(b.jid) = 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
		        return 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
                        lower(o.jid) = lower(_ownerJid)
                        and (_buddyJid is null or lower(b.jid) = 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;
$$ 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 with time zone, _to timestamp with 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 with time zone, _to timestamp with time zone, _tags text, _contains text, _hash character varying, _itemtype character varying, _offline smallint);
end if;
end$$;
-- 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 with time zone, _to timestamp with time zone, _tags text, _contains text, _stableId character varying, _itemtype character varying, _offline smallint') then
    drop function Tig_UA_GetItemPosition(_ownerjid character varying, _buddyjid character varying, _from timestamp with time zone, _to timestamp with time zone, _tags text, _contains text, _stableId character varying, _itemtype character varying, _offline smallint);
end if;
end$$;
-- 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 with time zone, _to timestamp with time zone, _reftype smallint, _tags text, _contains text, _stableId character varying, _itemtype character varying, _offline smallint') then
    drop function Tig_UA_GetItemPosition(_ownerjid character varying, _buddyjid character varying, _from timestamp with time zone, _to timestamp with time zone, _refType smallint, _tags text, _contains text, _stableId 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, _refType smallint, _tags text, _contains text, _stableId 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.stable_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 (m.is_ref = 0 or m.is_ref = 1)
			and (%L is null or m.ts >= %L)
			and (%L is null or m.ts <= %L)
		    and (%L is null or (%L <> 0 and m.item_type = %L) or (%L = 0 and m.item_type in (0,1,2)))
            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.stable_id = uuid(%L)';
		return query execute format(query_sql, _ownerJid, _buddyJid, _buddyJid, _from, _from, _to, _to, _itemType, _itemType, _itemType, _itemType, _offline, _offline, _stableId);
	else
        case _refType
            when 1 then
		        return query select x.position from (
                    select row_number() over (w) as position, m.stable_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 (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
                    window w as (order by ts) ) x where x.stable_id = _stableId::uuid;
            else
		        return query select x.position from (
		            select row_number() over (w) as position, m.stable_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 (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
		            window w as (order by ts) ) x where x.stable_id = _stableId::uuid;
        end case;
	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 with time zone, _to timestamp with 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 with time zone, _to timestamp with 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 smallint, _offline smallint, _limit int, _offset int) returns table(
	"ts" timestamp with time zone, "with" varchar(2049), "type" integer
) 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.item_type = 2 then 2 else 0 end as "type"';
		else
			msgs_query := msgs_query || ', 0 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 (%L <> 0 and m.item_type = %L) or (%L = 0 and m.item_type in (0,1,2)))
			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.item_type = 2 then 2 else 0 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, _itemType, _itemType, _offline, _offline, _limit, _offset);
	else
		if byType = 1 then
			return query 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
				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 (_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
			return query 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
				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 (_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;
$$ 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 with time zone, _to timestamp with 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 with time zone, _to timestamp with 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 smallint, _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.item_type = 2 then 2 else 0 end as "type"';
		else
			msgs_query := msgs_query || ', 0 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 (%L <> 0 and m.item_type = %L) or (%L = 0 and m.item_type in (0,1,2)))
			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.item_type = 2 then 2 else 0 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, _itemType, _itemType, _offline, _offline);
	else
		if byType = 1 then
			return query 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
				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 (_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
			return query select count(1) from (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
				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 (_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;
$$ 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 with 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 with 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:
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 with time zone, _direction smallint, _type character varying, _body text, _msg text, _stableid 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 with time zone, _direction smallint, _type character varying, _body text, _msg text, _stableid 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:
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, _ts timestamp with time zone, _stableId character varying, _stanzaId character varying, _refStableId character varying, _body text, _msg text, _buddyRes character varying, _direction smallint, _type 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, _ts timestamp with time zone, _stableId character varying, _stanzaId character varying, _refStableId character varying, _body text, _msg text, _buddyRes character varying, _direction smallint, _type 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), _ts timestamp with time zone,
    _stableId varchar(36), _stanzaId varchar(64), _refStableId varchar(36),
    _body text, _msg text,
    _itemType smallint, _offline smallint) returns void as $$
declare
	_owner_id bigint;
	_buddy_id bigint;
	_tsFrom timestamp with time zone;
	_tsTo timestamp with time zone;
begin
	select Tig_MA_EnsureJid(_ownerJid) into _owner_id;
	select Tig_MA_EnsureJid(_buddyJid) into _buddy_id;

    begin
        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, uuid(_stableId), _buddy_id, _ts, _stanzaId, case when _refStableId is null then 0 else 1 end, uuid(_refStableId), _body, _msg,
                    _itemType, _offline
            where not exists (
                    select 1 from tig_ma_msgs where owner_id = _owner_id and stable_id = uuid(_stableId)
            ) and not exists (
                    select 1 from tig_ma_msgs where owner_id = _owner_id and buddy_id = _buddy_id and ts between _ts - interval '30 minutes' and _ts + interval '30 minutes' 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, uuid(_stableId), _buddy_id, _ts, _stanzaId, case when _refStableId is null then 0 else 1 end, uuid(_refStableId), _body, _msg,
                    _itemType, _offline
            where not exists (
                select 1 from tig_ma_msgs where owner_id = _owner_id and stable_id = uuid(_stableId)
            );
        end if;
    exception when unique_violation then
    end;
end;
$$ LANGUAGE 'plpgsql';
-- QUERY END:

-- QUERY START:
create or replace function Tig_UA_FOR_AddItemOffline(_ownerJid varchar(2049),
    _stableId varchar(36), _buddyRes varchar(1024)) returns void as $$
declare
	_owner_id bigint;
begin
	select Tig_MA_EnsureJid(_ownerJid) into _owner_id;

    begin
        insert into tig_ua_for (stable_id, owner_id, buddy_res) values (_stableId::uuid, _owner_id, _buddyRes);
    exception when unique_violation then
    end;
end;
$$ LANGUAGE 'plpgsql';
-- QUERY END:

-- QUERY START:
create or replace function Tig_UA_AddItemJingle(_ownerJid varchar(2049),
    _stableId varchar(36), _sid character varying, _action character varying, _reason character varying, _offline smallint, _direction smallint) returns void as $$
declare
	_owner_id bigint;
begin
	select Tig_MA_EnsureJid(_ownerJid) into _owner_id;

    begin
        insert into tig_ua_jingle (stable_id, owner_id, sid, action, reason, offline, direction)
        values (_stableId::uuid, _owner_id, _sid, _action, _reason, _offline, _direction);
    exception when unique_violation then
    end;
end;
$$ LANGUAGE 'plpgsql';
-- QUERY END:

-- QUERY START:
drop function if exists Tig_UA_QueryRecent(varchar, timestamp with time zone, timestamp with time zone, 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, msg text, item_type smallint, "condition" varchar(50), stable_id varchar(36), "duration" bigint
) as $$
begin
    return query select b2.jid, m2.ts, m2.msg,m2.item_type,
		cast((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 varchar(50)) as "condition",
		cast(m2.stable_id as varchar(36)) as stable_id,
		cast(FLOOR(EXTRACT(EPOCH FROM(m2.ts - ac.ts))) as bigint) as duration
    from (
        select x.owner_id as owner_id, x.stable_id as stable_id, row_number() over (partition by x.owner_id, x.buddy_id order by x.ts desc, x.stable_id desc) as rn
        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 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 = 1)
	        or (1=_groupchat and x.item_type = 2)
	        or (1=_call and x.item_type = 132 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.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;
$$ 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 with time zone, _to timestamp with 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 with time zone, _to timestamp with 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.stable_id as msg_id, row_number() over (partition by x.owner_id, x.buddy_id order by x.ts desc, x.stable_id desc) as rn
        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 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 = 1)
	    or (1=_groupchat and x.item_type = 2)
	    or (1=_call and x.item_type = 132 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:
drop function if exists Tig_UA_FOR_GetMessagesCount(varchar);
-- QUERY END:

-- QUERY START:
create or replace function Tig_UA_FOR_GetMessagesCount(_ownerJid varchar(2049)) returns table (
	item_type smallint, "count" bigint
) as $$
begin
	return query select m.item_type, count(1)
		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:
drop function if exists Tig_UA_FOR_GetMessagesList(varchar);
-- 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), stable_id varchar(36), item_type smallint
) as $$
begin
	return query select b.jid, f.buddy_res, cast(m.stable_id as varchar(36)) as stable_id, m.item_type
		from tig_ma_msgs m
		inner join tig_ma_jids o on o.jid_id = m.owner_id
		inner join tig_ma_jids b on b.jid_id = m.buddy_id
	    left join tig_ua_for f on f.owner_id = m.owner_id and f.stable_id = m.stable_id
		where lower(o.jid) = lower(_ownerJid) and m.offline >= 1;
end;
$$ LANGUAGE 'plpgsql';
-- QUERY END:

-- QUERY START:
drop function if exists Tig_UA_FOR_GetMessages(varchar, text);
-- QUERY END:

-- QUERY START:
create or replace function Tig_UA_FOR_GetMessages(_ownerJid varchar(2049), _msgIds text) returns table (
	message text, stable_id bigint
) as $$
declare
	msg_query text;
begin
	msg_query := 'select m.msg, case(m.stable_id as varchar(36))
		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.stable_id in (uuid(' || replace(_msgIds, ',', '),uuid(') || '))';
	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 stable_id in (uuid(' || replace(_msgIds, ',', '),uuid(') || '))';
	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 stable_id in (uuid(' || replace(_msgIds, ',', '),uuid(') || '))';
	end if;
	execute format(msg_query, ownerId);

	msg_query := 'delete from tig_ua_for
		where owner_id = %L';
    if _msgIds is not null then
        msg_query := msg_query || ' and stable_id in (uuid(' || replace(_msgIds, ',', '),uuid(') || '))';
    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 stable_id in (uuid(' || replace(_msgIds, ',', '),uuid(') || '))';
	end if;
	execute format(msg_query, ownerId);
end;
$$ LANGUAGE 'plpgsql';
-- QUERY END:

-- QUERY START:
create table if not exists tig_ua_file_metadata (
	stable_id uuid not null,
	owner_id bigint 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)
);
-- QUERY END:

-- QUERY START:
create or replace function Tig_UA_FileMetadata_AddItem(_ownerJid varchar(2049),
                                                _stableId varchar(36), _urlHash varchar(40), _url varchar(2048), _media_type varchar(50), _name varchar(255), _description varchar(1024), _size bigint) returns void as $$
declare
    _owner_id bigint;
begin
    select Tig_MA_EnsureJid(_ownerJid) into _owner_id;

    begin
        insert into tig_ua_file_metadata (stable_id, owner_id, url_hash, url, name, description, size, media_type)
        values (_stableId::uuid, _owner_id, _urlHash, _url, _name, _description, _size, _media_type);
    exception when unique_violation then
    end;
end;
$$ LANGUAGE 'plpgsql';
-- QUERY END:

-- QUERY START:
create or replace function Tig_UA_FileMetadata_QueryItems(_domain varchar(1024), _ownerJid varchar(2049), _buddyJid varchar(2049), _after timestamp with time zone, _before timestamp with time zone, _contains varchar(100), _mediaTypeContains varchar(50), _smallerThan bigint, _biggerThan bigint, _limit int, _offset int) returns table (
	owner_jid varchar(2049), buddy_jid varchar(2049), ts timestamp with time zone, stable_id varchar(36), url varchar(2048), media_type varchar(50), name varchar(255), description varchar(1024), size bigint
) as $$
begin
	return query select o.jid, b.jid, m.ts, cast(m.stable_id as varchar(36)) 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 lower(o.jid) = lower(_ownerJid))
		    and (_domain is null or lower(o.domain) = lower(_domain))
            and (_buddyJid is null or lower(b.jid) = 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 '%' || _mediaTypeContains || '%'))
            and (_contains is null or (fm.name like '%' || _contains || '%') or (fm.description like '%' || _contains || '%') or (fm.url like '%' || _contains || '%'))
        order by m.ts asc 
        limit _limit offset _offset;
end;
$$ LANGUAGE 'plpgsql';
-- QUERY END:

-- QUERY START:
create or replace function Tig_UA_FileMetadata_QueryItemsCount(_domain varchar(1024), _ownerJid varchar(2049), _buddyJid varchar(2049), _after timestamp with time zone, _before timestamp with time zone, _contains varchar(100), _mediaTypeContains varchar(50), _smallerThan bigint, _biggerThan bigint, _beforeStableId varchar(36)) returns table (
	"count" bigint
) as $$
begin
	return query 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 lower(o.jid) = lower(_ownerJid))
            and (_domain is null or lower(o.domain) = lower(_domain))
            and (_buddyJid is null or lower(b.jid) = 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 '%' || _mediaTypeContains || '%'))
            and (_contains is null or (fm.name like '%' || _contains || '%') or (fm.description like '%' || _contains || '%') or (fm.url like '%' || _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 lower(o1.jid) = lower(_ownerJid))
                        and m1.stable_id = _beforeStableId::uuid
                )
            ));
end;
$$ LANGUAGE 'plpgsql';
-- QUERY END: