--
-- 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:
if not exists (select 1 from sys.columns where object_id = object_id('dbo.tig_push_devices') and name = 'device_second_id')
alter table tig_push_devices add device_second_id nvarchar(1024);
-- QUERY END:
GO

-- QUERY START:
IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'Tig_Push_RegisterDevice')
DROP PROCEDURE Tig_Push_RegisterDevice
-- QUERY END:
    GO

-- QUERY START:
create procedure dbo.Tig_Push_RegisterDevice
    @_serviceJid nvarchar(2049),
	@_userJid nvarchar(2049), @_node nvarchar(1024),
    @_provider nvarchar(100), @_deviceId nvarchar(1024),
    @_deviceSecondId nvarchar(1024)
AS
begin
    declare @_serviceJidSha1 varbinary(20), @_userJidSha1 varbinary(20), @_nodeSha1 varbinary(20), @_id varbinary(20);

set @_serviceJidSha1 = HASHBYTES('SHA1', LOWER(@_serviceJid));
set @_userJidSha1 = HASHBYTES('SHA1', LOWER(@_userJid));
set @_nodeSha1 = HASHBYTES('SHA1', @_node);
set @_id = HASHBYTES('SHA1', LOWER(@_serviceJid) + LOWER(@_userJid) + @_provider + @_deviceId );

if exists (select 1 from tig_push_devices where service_jid_sha1 = @_serviceJidSha1 and node_sha1 = @_nodeSha1)
update tig_push_devices set device_second_id = @_deviceSecondId where service_jid_sha1 = @_serviceJidSha1 and node_sha1 = @_nodeSha1 and user_jid_sha1 = @_userJidSha1;
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, @_deviceSecondId)

    end
-- QUERY END:
GO

-- QUERY START:
IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'Tig_Push_GetNodeSettingsByNode')
DROP PROCEDURE Tig_Push_GetNodeSettingsByNode
-- QUERY END:
    GO

-- QUERY START:
create procedure dbo.Tig_Push_GetNodeSettingsByNode
    @_serviceJid nvarchar(2049), @_node nvarchar(1024)
AS
begin
    declare @_serviceJidSha1 varbinary(20), @_nodeSha1 varbinary(20);

set @_serviceJidSha1 = HASHBYTES('SHA1', LOWER(@_serviceJid));
set @_nodeSha1 = HASHBYTES('SHA1', @_node);

select user_jid, provider, device_id, device_second_id
from tig_push_devices
where
        service_jid_sha1 = @_serviceJidSha1
  and node_sha1 = @_nodeSha1;
end
-- QUERY END:
GO


-- QUERY START:
IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'Tig_Push_GetNodeSettingsByDevice')
DROP PROCEDURE Tig_Push_GetNodeSettingsByDevice
-- QUERY END:
    GO

-- QUERY START:
create procedure dbo.Tig_Push_GetNodeSettingsByDevice
    @_provider nvarchar(100), @_deviceId nvarchar(1024)
AS
begin
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 pd.service_jid_sha1 = x.service_jid_sha1 and pd.node_sha1 = x.node_sha1
where
        x.provider = @_provider
  and x.device_id = @_deviceId;
end
-- QUERY END:
GO

-- QUERY START:
IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'Tig_Push_Stats')
    DROP PROCEDURE Tig_Push_Stats
-- QUERY END:
GO

-- QUERY START:
create procedure dbo.Tig_Push_Stats
AS
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:
GO
