--
-- 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
--

-- -----------------------------------------------------------------------------
-- Tables
-- -----------------------------------------------------------------------------

-- Table to store service jids
-- QUERY START:
create table if not exists tig_push_devices (
	service_jid varchar(2049) not null,
	user_jid varchar(2049) not null,
    node varchar(1024) not null,
    provider varchar(100) not null,
    device_id varchar(1024) not null,

	primary key ( service_jid, user_jid, provider, device_id )
);
-- QUERY END:

-- QUERY START:
do $$
begin
if exists (select 1 where (select to_regclass('public.tig_push_devices_service_jid_user_jid_provider_device_id')) is null) then
    create unique index tig_push_devices_service_jid_user_jid_provider_device_id on tig_push_devices ( lower(service_jid), lower(user_jid), provider, device_id );
end if;
end$$;
-- QUERY END:

-- QUERY START:
do $$
begin
if exists (select 1 where (select to_regclass('public.tig_push_devices_service_jid_node')) is null) then
    create index tig_push_devices_service_jid_node on tig_push_devices ( lower(service_jid), node);
end if;
end$$;
-- QUERY END:

-- QUERY START:
do $$
begin
if exists (select 1 where (select to_regclass('public.tig_push_devices_provider_device_id')) is null) then
    create index tig_push_devices_provider_device_id on tig_push_devices ( provider, device_id );
end if;
end$$;
-- QUERY END:

-- -----------------------------------------------------------------------------
-- Functions
-- -----------------------------------------------------------------------------

-- QUERY START:
create or replace function Tig_Push_RegisterDevice(_serviceJid varchar(2049), _userJid varchar(2049), _node varchar(1024), _provider varchar(100), _deviceId varchar(1024))
returns void as $$
begin
    insert into tig_push_devices (service_jid, user_jid, node, provider, device_id)
        values (_serviceJid, _userJid, _node, _provider, _deviceId);
end;
$$ LANGUAGE 'plpgsql';
-- QUERY END:

-- QUERY START:
create or replace function Tig_Push_UnregisterDevice(_serviceJid varchar(2049), _userJid varchar(2049), _node varchar(1024), _provider varchar(100), _deviceId varchar(1024))
returns void as $$
begin
    delete from tig_push_devices
        where
            lower(service_jid) = lower(_serviceJid)
            and lower(user_jid) = lower(_userJid)
            and node = _node
            and provider = _provider
            and device_id = _deviceId;
end;
$$ LANGUAGE 'plpgsql';
-- 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)
) as $$
begin
    return query select pd.user_jid, pd.provider, pd.device_id
        from tig_push_devices pd
        where
            lower(pd.service_jid) = lower(_serviceJid)
            and pd.node = _node;
end;
$$ LANGUAGE 'plpgsql';
-- 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)
) as $$
begin
    return query 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 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:
select TigSetComponentVersion('push', '1.0.0');
-- QUERY END:
