--
-- Tigase Push - Push notifications component for Tigase
-- Copyright (C) 2017 Tigase, Inc. (office@tigase.com) - All Rights Reserved
-- Unauthorized copying of this file, via any medium is strictly prohibited
-- Proprietary and confidential
--

--

-- 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_push_devices' and column_name = 'device_second_id') then
alter table tig_push_devices
    add device_second_id varchar(1024);
end if;
end$$;
-- QUERY END:

-- QUERY START:
do $$
begin
    if exists( select 1 from pg_proc where proname = 'tig_push_registerdevice' and pg_get_function_arguments(oid) = 'TABLE(_servicejid character varying, _userjid character varying, _node character varying, _provider character varying, _deviceid character varying)') then
drop function Tig_Push_RegisterDevice(_servicejid character varying, _userjid character varying, _node character varying, _provider character varying, _deviceid character varying);
end if;
end$$;
-- QUERY END:

-- QUERY START:
create or replace function Tig_Push_RegisterDevice(_serviceJid varchar(2049), _userJid varchar(2049), _node varchar(1024), _provider varchar(100), _deviceId varchar(1024), _deviceSecondId varchar(1024))
              returns void as $$
begin
    if exists (select 1 from tig_push_devices where service_jid = _serviceJid and node = _node) then
update tig_push_devices set device_second_id = _deviceSecondId where service_jid = _serviceJid and node = _node and user_jid= _userJid;
else
insert into tig_push_devices (service_jid, user_jid, node, provider, device_id, device_second_id)
values (_serviceJid, _userJid, _node, _provider, _deviceId, _deviceSecondId);
end if;
end;
$$ LANGUAGE 'plpgsql';
-- QUERY END:

-- QUERY START:
do $$
begin
    if exists( select 1 from pg_proc where proname = 'tig_push_getnodesettingsbynode' and pg_get_function_result(oid) = 'TABLE(user_jid character varying, provider character varying, device_id character varying)') then
drop function Tig_Push_GetNodeSettingsByNode(_serviceJid character varying, _nide character varying);
end if;
end$$;
-- QUERY END:

-- QUERY START:
create or replace function Tig_Push_GetNodeSettingsByNode(_serviceJid varchar(2049), _node varchar(1024))
              returns table(
    "user_jid" varchar(2049),
              "provider" varchar(100),
              "device_id" varchar(1024),
              "device_second_id" varchar(1024)
              ) as $$
begin
    return query select pd.user_jid, pd.provider, pd.device_id, pd.device_second_id
                 from tig_push_devices pd
                 where
                         lower(pd.service_jid) = lower(_serviceJid)
                   and pd.node = _node;
end;
$$ LANGUAGE 'plpgsql';
-- QUERY END:

-- QUERY START:
do $$
begin
    if exists( select 1 from pg_proc where proname = 'tig_push_getnodesettingsbydevice' and pg_get_function_result(oid) = 'TABLE(service_jid character varying, user_jid character varying, node character varying, provider character varying, device_id character varying)') then
drop function Tig_Push_GetNodeSettingsByDevice(_provider character varying, _deviceId character varying);
end if;
end$$;
-- QUERY END:

-- QUERY START:
create or replace function Tig_Push_GetNodeSettingsByDevice(_provider varchar(100), _deviceId varchar(1024))
              returns table(
    "service_jid" varchar(2049),
              "user_jid" varchar(2049),
              "node" varchar(1024),
              "provider" varchar(100),
              "device_id" varchar(1024),
              "device_second_id" varchar(1024)
              ) as $$
begin
    return query select pd.service_jid, pd.user_jid, pd.node, pd.provider, pd.device_id, pd.device_second_id
                 from tig_push_devices pd
                          inner join tig_push_devices x
                                     on lower(pd.service_jid) = lower(x.service_jid)
                                         and pd.node = x.node
                 where
                         x.provider = _provider
                   and x.device_id = _deviceId;
end;
$$ LANGUAGE 'plpgsql';
-- QUERY END:

-- QUERY START:
create or replace function Tig_Push_Stats()
    returns table(
        "type" varchar(100),
        "provider" varchar(100),
        "count" bigint) as '
    select ''all'', provider, count(1) from tig_push_devices group by provider union all select ''accounts'', provider, count(distinct user_jid) from tig_push_devices group by provider, user_jid;
' LANGUAGE 'sql';
-- QUERY END: