--
-- 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:
SET QUOTED_IDENTIFIER ON
-- QUERY END:
GO

-- QUERY START:
if not exists (select * from sysobjects where name='tig_push_devices' and xtype='U')
	CREATE  TABLE [dbo].[tig_push_devices] (
	    [id] [varbinary](40) not null,
	    [service_jid] [nvarchar](2049) not null,
	    [service_jid_sha1] [varbinary](40) not null,
	    [user_jid] [nvarchar](2049) not null,
	    [user_jid_sha1] [varbinary](40) not null,
        [node] [nvarchar](1024) not null,
        [node_sha1] [varbinary](40) not null,
        [provider] [nvarchar](100) not null,
        [device_id] [nvarchar](1024) not null,

		PRIMARY KEY ( [id] )
	);
-- QUERY END:
GO

-- QUERY START:
IF NOT EXISTS(SELECT * FROM sys.indexes WHERE object_id = object_id('dbo.tig_push_devices') AND NAME ='IX_tig_push_devices_service_jid_sha1_node_sha1')
	CREATE INDEX IX_tig_push_devices_service_jid_sha1_node_sha1 ON [dbo].[tig_push_devices](service_jid_sha1, node_sha1);
-- QUERY END:
GO

-- QUERY START:
IF NOT EXISTS(SELECT * FROM sys.indexes WHERE object_id = object_id('dbo.tig_push_devices') AND NAME ='IX_tig_push_devices_provider_device_id')
	CREATE INDEX IX_tig_push_devices_provider_device_id ON [dbo].[tig_push_devices](provider, device_id);
-- 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)
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 );

    insert into tig_push_devices (id, service_jid, service_jid_sha1, user_jid, user_jid_sha1, node, node_sha1, provider, device_id)
        values (@_id, @_serviceJid, @_serviceJidSha1, @_userJid, @_userJidSha1, @_node, @_nodeSha1, @_provider, @_deviceId)

end
-- QUERY END:
GO

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

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

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

    delete from tig_push_devices where id = @_id;
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
    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
    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:
exec TigSetComponentVersion 'push', '1.0.0';
-- QUERY END:
GO
