--
-- 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:
drop procedure if exists TigUpgradePush;
-- QUERY END:

delimiter //

-- QUERY START:
create procedure TigUpgradePush()
begin
    if not exists (select 1 from information_schema.columns where table_schema = database() 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:

delimiter ;

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

delimiter ;

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

-- QUERY START:
drop procedure if exists Tig_Push_RegisterDevice;
-- QUERY END:
-- QUERY START:
drop procedure if exists Tig_Push_GetNodeSettingsByDevice;
-- QUERY END:
-- QUERY START:
drop procedure if exists Tig_Push_GetNodeSettingsByNode;
-- QUERY END:
-- QUERY START:
drop procedure if exists Tig_Push_Stats;
-- QUERY END:

delimiter //

-- QUERY START:
create procedure Tig_Push_RegisterDevice(_serviceJid varchar(2049), _userJid varchar(2049), _node varchar(1024),
                                         _provider varchar(100), _deviceId varchar(1024), _device_second_id varchar(1024))
begin
    declare _id char(40);
    declare _serviceJidSha1 char(40);
    declare _userJidSha1 char(40);
    declare _nodeSha1 char(40);

    select SHA1(LOWER(_serviceJid)), SHA1(LOWER(_userJid)), SHA1(_node),
           SHA1( CONCAT( LOWER(_serviceJid), LOWER(_userJid), _provider, _deviceId ) )
    into _serviceJidSha1, _userJidSha1, _nodeSha1, _id;

    if exists (select 1 from tig_push_devices where service_jid_sha1 = SHA1(LOWER(_serviceJid)) and node_sha1 = SHA1(_node)) then
        update tig_push_devices set device_second_id = _device_second_id where service_jid_sha1 = SHA1(LOWER(_serviceJid)) and node_sha1 = SHA1(_node) and user_jid_sha1 = SHA1(LOWER(_userJid));
    else
        insert into tig_push_devices (id, service_jid, service_jid_sha1, user_jid, user_jid_sha1, node, node_sha1, provider, device_id, device_second_id)
        values (_id, _serviceJid, _serviceJidSha1, _userJid, _userJidSha1, _node, _nodeSha1, _provider, _deviceId, _device_second_id);
    end if;
end //
-- QUERY END:

-- QUERY START:
create procedure Tig_Push_GetNodeSettingsByNode(_serviceJid varchar(2049), _node varchar(1024))
begin
    select user_jid, provider, device_id, device_second_id
    from tig_push_devices
    where
            service_jid_sha1 = SHA1(LOWER(_serviceJid))
      and node_sha1 = SHA1(_node);
end //
-- QUERY END:

-- QUERY START:
create procedure Tig_Push_GetNodeSettingsByDevice(_provider varchar(100), _deviceId varchar(1024))
begin
    declare _serviceJidSha1 char(40);
    declare _nodeSha1 char(40);
    select service_jid_sha1, node_sha1 into _serviceJidSha1, _nodeSha1
    from tig_push_devices
    where
            provider = _provider
      and device_id = _deviceId;

    select service_jid, user_jid, node, provider, device_id, device_second_id
    from tig_push_devices
    where
        _serviceJidSha1 is not null
      and service_jid_sha1 = _serviceJidSha1
      and node_sha1 = _nodeSha1;

end //
-- QUERY END:

-- QUERY START:
create procedure Tig_Push_Stats()
begin
    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;
end //
-- QUERY END:
delimiter ;
