--
-- 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 Tig_Push_RegisterDevice;
-- QUERY END:
-- QUERY START:
drop procedure if exists Tig_Push_UnregisterDevice;
-- 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:
create table if not exists tig_push_devices (
    id char(40) not null,
	service_jid varchar(2049) not null,
	service_jid_sha1 char(40) not null,
	user_jid varchar(2049) not null,
	user_jid_sha1 char(40) not null,
    node varchar(1024) not null,
    node_sha1 char(40) not null,
    provider varchar(100) not null,
    device_id varchar(1024) not null,

	primary key ( id ),
	unique index using hash ( id ),
    index using hash ( service_jid_sha1, node_sha1 ),
    index using hash ( provider, device_id(255) )
)
ENGINE=InnoDB default character set utf8 ROW_FORMAT=DYNAMIC;
-- 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))
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;

    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:

-- QUERY START:
create procedure Tig_Push_UnregisterDevice(_serviceJid varchar(2049), _userJid varchar(2049), _node varchar(1024),
        _provider varchar(100), _deviceId varchar(1024))
begin
    delete from tig_push_devices
    where id = SHA1(CONCAT(LOWER(_serviceJid),LOWER(_userJid),_provider,_deviceId));
end //
-- QUERY END:

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

delimiter ;

-- 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
    from tig_push_devices
    where
        _serviceJidSha1 is not null
        and service_jid_sha1 = _serviceJidSha1
        and node_sha1 = _nodeSha1;

end //
-- QUERY END:

-- QUERY START:
call TigSetComponentVersion('push', '1.0.0');
-- QUERY END:

delimiter ;