--
-- Tigase Tags Extensions - Tags Extensions for Tigase XMPP Server
-- Copyright (C) 2020 Tigase, Inc. (office@tigase.com) - All Rights Reserved
-- Unauthorized copying of this file, via any medium is strictly prohibited
-- Proprietary and confidential
--

-- QUERY START:
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
-- QUERY END:

-- QUERY START:
create table if not EXISTS tig_tags_jids (
    id bytea NOT NULL,
    jid varchar(2049) NOT NULL,
    domain varchar(1024) NOT NULL,
    primary key (id)
);
-- QUERY END:

-- QUERY START:
create table if not EXISTS tig_tags (
   id uuid NOT NULL DEFAULT uuid_generate_v4(),
   name varchar(50) NOT NULL,
   scope smallint NOT NULL,
   description varchar(255),
   owner bytea NOT NULL,
   color varchar(16),
   icon varchar(16),
   primary key (id),
   foreign key (owner) references tig_tags_jids (id)
);
-- QUERY END:

-- QUERY START:
do $$
    begin
        if exists (select 1 where (select to_regclass('public.tig_tags_owner_name')) is null) then
            create unique index tig_tags_owner_name on tig_tags ( owner, name );
        end if;
    end$$;
-- QUERY END:

-- QUERY START:
create table if not EXISTS tig_tags_associations (
    tag_id uuid NOT NULL,
    jid_id bytea NOT NULL,
    type smallint NOT NULL,
    join_required smallint,
    primary key (tag_id, jid_id),
    foreign key (tag_id) references tig_tags (id),
    foreign key (jid_id) references tig_tags_jids (id)
);
-- QUERY END:

-- QUERY START:
create or replace function Tig_Tags_JIDEnsure(_jid varchar(2049)) returns bytea as $$
declare
    _jid_sha bytea;
    _domain varchar(1024);
begin
    _jid_sha := sha256(cast(lower(_jid) as varchar)::bytea);
    _domain := substr(_jid, strpos(_jid, '@') + 1);
    insert into tig_tags_jids (id, jid, domain)
        values (_jid_sha, _jid, _domain) on conflict (id) DO NOTHING;
    return _jid_sha;
end;
$$ LANGUAGE 'plpgsql';
-- QUERY END:

-- QUERY START:
create or replace function Tig_Tags_JIDGetID(_jid varchar(2049)) returns bytea as $$
begin
    return sha256(cast(lower(_jid) as varchar)::bytea);
end;
$$ LANGUAGE 'plpgsql';
-- QUERY END:

-- QUERY START:
create or replace function Tig_Tags_TagCreate(_id varchar(36), _scope smallint, _owner varchar(2049), _name varchar(50), _description varchar(255), _color varchar(16), _icon varchar(16)) returns void as $$
begin
    insert into tig_tags (id, name, scope, owner, description, color, icon)
        select _id::uuid, _name, _scope, Tig_Tags_JIDEnsure(_owner), _description, _color, _icon;
end;
$$ LANGUAGE 'plpgsql';
-- QUERY END:

-- QUERY START:
create or replace function Tig_Tags_TagUpdate(_id varchar(36), _scope smallint, _owner varchar(2049), _name varchar(50), _description varchar(255), _color varchar(16), _icon varchar(16)) returns void as $$
begin
    update tig_tags
        set name = _name, scope = _scope, owner = Tig_Tags_JIDEnsure(_owner), description = _description, color = _color, icon = _icon
        where id = _id::uuid;
end;
$$ LANGUAGE 'plpgsql';
-- QUERY END:

-- QUERY START:
create or replace function Tig_Tags_TagDelete(_id varchar(36)) returns void as $$
begin
    delete from tig_tags_associations where tag_id = _id::uuid;
    delete from tig_tags where id = _id::uuid;
end;
$$ LANGUAGE 'plpgsql';
-- QUERY END:

-- QUERY START:
create or replace function Tig_Tags_TagGet(_id varchar(36)) returns table (
    id varchar(36),
    owner varchar(2049),
    name varchar(50),
    scope smallint,
    description varchar(255),
    color varchar(16),
    icon varchar(16)
) as $$
begin
    return query select t.id::varchar(36), j.jid as owner, t.name, t.scope, t.description, t.color, t.icon
        from tig_tags t
            inner join tig_tags_jids j on j.id = t.owner
        where
            t.id = _id::uuid;
end;
$$ LANGUAGE 'plpgsql';
-- QUERY END:

-- QUERY START:
create or replace function Tig_Tags_TagAssign(_tag_id varchar(36), _jid varchar(2049), _type smallint, _join_required smallint) returns void as $$
begin
    insert into tig_tags_associations (tag_id, jid_id, type, join_required)
        select _tag_id::uuid, Tig_Tags_JIDEnsure(_jid), _type, _join_required
    on conflict (tag_id, jid_id)
        do update set type = _type, join_required = _join_required;
end;
$$ LANGUAGE 'plpgsql';
-- QUERY END:

-- QUERY START:
create or replace function Tig_Tags_TagDissociate(_tag_id varchar(36), _jid varchar(2049)) returns void as $$
begin
    delete from tig_tags_associations where tag_id = _tag_id::uuid and jid_id = Tig_Tags_JIDGetID(_jid);
end;
$$ LANGUAGE 'plpgsql';
-- QUERY END:

-- QUERY START:
create or replace function Tig_Tags_TagsQuery(_domain varchar(1024), _scope smallint, _requestor varchar(2049)) returns table (
    id varchar(36),
    owner varchar(2049),
    name varchar(50),
    scope smallint,
    description varchar(255),
    color varchar(16),
    icon varchar(16)
) as $$
begin
    if _scope is null then
        return query select t.id::varchar(36), j.jid as owner, t.name, t.scope, t.description, t.color, t.icon
        from tig_tags t
        inner join tig_tags_jids j on j.id = t.owner
        where (
                t.owner = Tig_Tags_JIDGetID(_domain) AND
                t.scope in (1, 3)
            )
           OR (
                t.scope = 4 AND
                t.owner = Tig_Tags_JIDGetID(_requestor)
            );
    elsif _scope <= 3 then
        return query select t.id::varchar(36), j.jid as owner, t.name, t.scope, t.description, t.color, t.icon
        from tig_tags t
        inner join tig_tags_jids j on j.id = t.owner
        where (
                      t.scope = _scope AND
                      t.owner = Tig_Tags_JIDGetID(_domain)
                  );
    elsif _scope = 4 then
        return query select t.id::varchar(36), j.jid as owner, t.name, t.scope, t.description, t.color, t.icon
        from tig_tags t
        inner join tig_tags_jids j on j.id = t.owner
        where (
                      t.scope = 4 AND
                      t.owner = Tig_Tags_JIDGetID(_requestor)
                  );
    else
        return query select id::varchar(36), name, scope, description, color, icon
        from tig_tags t where t.scope is null;
    end if;
end;
$$ LANGUAGE 'plpgsql';
-- QUERY END:

-- QUERY START:
create or replace function Tig_Tags_TagsQueryAssociated(_domain varchar(1024), _jid varchar(2049), _requestor varchar(2049)) returns table (
    id varchar(36),
    owner varchar(2049),
    name varchar(50),
    scope smallint,
    description varchar(255),
    color varchar(16),
    icon varchar(16)
) as $$
begin
    return query select t.id::varchar(36), j.jid as owner, t.name, t.scope, t.description, t.color, t.icon
        from tig_tags t
        inner join tig_tags_jids j on j.id = t.owner
        inner join tig_tags_associations a on t.id = a.tag_id
        where
            a.jid_id = Tig_Tags_JIDGetID(_jid)
        AND (
            t.scope = 4 AND t.owner = Tig_Tags_JIDGetID(_requestor)
        ) OR (
            t.scope = 3 AND t.owner = Tig_Tags_JIDGetID(_domain)
        );
end;
$$ LANGUAGE 'plpgsql';
-- QUERY END:

-- QUERY START:
create or replace function Tig_Tags_QueryJidsWithTag(_id varchar(36), _type smallint, _join_required smallint) returns table (
    jid varchar(2049)
) as $$
begin
    return query select j.jid
        from tig_tags_jids j
        inner join tig_tags_associations tkta on j.id = tkta.jid_id
        where
            tkta.tag_id = _id::uuid
            and tkta.type = _type
            and tkta.join_required = _join_required;
end;
$$ LANGUAGE 'plpgsql';
-- QUERY END:

-- QUERY START:
create or replace function Tig_Tags_TagGetAssociationType(_id varchar(36), _jid varchar(2049)) returns table (
    type smallint
) as $$
begin
    return query select tkta.type
        from tig_tags_associations tkta
        where
            tkta.jid_id = Tig_Tags_JIDGetID(_jid)
            and tkta.tag_id = _id::uuid;
end;
$$ LANGUAGE 'plpgsql';
-- QUERY END: