--
-- 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 table if not EXISTS tig_tags_jids (
    id binary(32) NOT NULL,
    jid varchar(2049) CHARSET utf8 NOT NULL,
    domain varchar(1024) CHARSET utf8 NOT NULL,
    primary key (id)
)
ENGINE=InnoDB default character set utf8 ROW_FORMAT=DYNAMIC;
-- QUERY END:

-- QUERY START:
create table if not EXISTS tig_tags (
   id binary(16) NOT NULL,
   name varchar(50) CHARSET utf8mb4 collate utf8mb4_bin NOT NULL,
   scope smallint NOT NULL,
   description varchar(255) CHARSET utf8mb4 collate utf8mb4_bin,
   owner binary(32) NOT NULL,
   color varchar(16) CHARSET utf8,
   icon varchar(16) CHARSET utf8mb4 collate utf8mb4_bin,
   primary key (id),
   foreign key (owner) references tig_tags_jids (id),
   unique index tig_tags_owner_name (owner, name)
)
ENGINE=InnoDB default character set utf8 ROW_FORMAT=DYNAMIC;
-- QUERY END:

-- QUERY START:
create table if not EXISTS tig_tags_associations (
    tag_id binary(16) NOT NULL,
    jid_id binary(32) 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)
)
ENGINE=InnoDB default character set utf8 ROW_FORMAT=DYNAMIC;
-- QUERY END:

-- QUERY START:
drop function if exists Tig_Tags_UuidToOrdered;
-- QUERY END:

-- QUERY START:
drop function if exists Tig_Tags_OrderedToUuid;
-- QUERY END:

-- QUERY START:
drop function if exists Tig_Tags_JIDEnsure;
-- QUERY END:

-- QUERY START:
drop procedure if exists Tig_Tags_TagCreate;
-- QUERY END:

-- QUERY START:
drop procedure if exists Tig_Tags_TagUpdate;
-- QUERY END:

-- QUERY START:
drop procedure if exists Tig_Tags_TagDelete;
-- QUERY END:

-- QUERY START:
drop procedure if exists Tig_Tags_TagGet;
-- QUERY END:

-- QUERY START:
drop procedure if exists Tig_Tags_TagAssign;
-- QUERY END:

-- QUERY START:
drop procedure if exists Tig_Tags_TagDissociate;
-- QUERY END:

-- QUERY START:
drop procedure if exists Tig_Tags_TagsQuery;
-- QUERY END:

-- QUERY START:
drop procedure if exists Tig_Tags_TagsQueryAssociated;
-- QUERY END:

-- QUERY START:
drop procedure if exists Tig_Tags_QueryJidsWithTag;
-- QUERY END:

-- QUERY START:
drop procedure if exists Tig_Tags_TagGetAssociationType;
-- QUERY END:

delimiter //

-- QUERY START:
create function Tig_Tags_UuidToOrdered(_uuid varchar(36)) returns binary(16) deterministic
begin
    if _uuid is null then
        return null;
    end if;
    return unhex(concat(substr(_uuid, 15, 4), substr(_uuid, 10, 4), substr(_uuid, 1, 8), substr(_uuid, 20, 4), substr(_uuid, 25)));
end //
-- QUERY END:

-- QUERY START:
create function Tig_Tags_OrderedToUuid(_uuid binary(16)) returns varchar(36) deterministic
begin
    declare hexed varchar(36);
    if _uuid is null then
        return null;
    end if;

    select hex(_uuid) into hexed;

    return concat(substr(hexed, 9, 8), '-', substr(hexed, 5, 4), '-', substr(hexed, 1, 4), '-', substr(hexed, 17, 4), '-', substr(hexed, 21));
end //
-- QUERY END:

-- QUERY START:
create function Tig_Tags_JIDEnsure(_jid varchar(2049) CHARSET utf8) returns binary(32) DETERMINISTIC
begin
    insert into tig_tags_jids (id, jid, domain)
    select unhex(sha2(_jid, 256)), _jid, SUBSTR(_jid, LOCATE('@', _jid) + 1) ON DUPLICATE KEY UPDATE id = id;
    
    return (unhex(sha2(_jid, 256)));
end //
-- QUERY END:

-- QUERY START:
create procedure Tig_Tags_TagCreate(_id varchar(36), _scope smallint, _owner varchar(2049) CHARSET utf8, _name varchar(50) CHARSET utf8mb4 collate utf8mb4_bin, _description varchar(255) CHARSET utf8mb4 collate utf8mb4_bin, _color varchar(16) charset utf8, _icon varchar(16) CHARSET utf8mb4 collate utf8mb4_bin)
begin
    insert into tig_tags (id, name, scope, owner, description, color, icon)
    select Tig_Tags_UuidToOrdered(_id), _name, _scope, Tig_Tags_JIDEnsure(_owner), _description, _color, _icon;
end //
-- QUERY END:

-- QUERY START:
create procedure Tig_Tags_TagUpdate(_id varchar(36), _scope smallint, _owner varchar(2049) CHARSET utf8, _name varchar(50) CHARSET utf8mb4 collate utf8mb4_bin, _description varchar(255) CHARSET utf8mb4 collate utf8mb4_bin, _color varchar(16) charset utf8, _icon varchar(16) CHARSET utf8mb4 collate utf8mb4_bin)
begin
    update tig_tags
        set name = _name, scope = _scope, owner = Tig_Tags_JIDEnsure(_owner), description = _description, color = _color, icon = _icon
    where id = Tig_Tags_UuidToOrdered(_id);
end //
-- QUERY END:

-- QUERY START:
create procedure Tig_Tags_TagDelete(_id varchar(36))
begin
    delete from tig_tags_associations where tag_id = Tig_Tags_UuidToOrdered(_id);
    delete from tig_tags where id = Tig_Tags_UuidToOrdered(_id);
end //
-- QUERY END:

-- QUERY START:
create procedure Tig_Tags_TagGet(_id varchar(36))
begin
    select Tig_Tags_OrderedToUuid(t.id), 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 = Tig_Tags_UuidToOrdered(_id);
end //
-- QUERY END:

-- QUERY START:
create procedure Tig_Tags_TagAssign(_tag_id varchar(36), _jid varchar(2049) CHARSET utf8, _type smallint, _join_required smallint)
begin
    insert into tig_tags_associations (tag_id, jid_id, type, join_required)
        select Tig_Tags_UuidToOrdered(_tag_id), Tig_Tags_JIDEnsure(_jid), _type, _join_required
    on duplicate key update type = _type, join_required = _join_required;
end //
-- QUERY END:

-- QUERY START:
create procedure Tig_Tags_TagDissociate(_tag_id varchar(36), _jid varchar(2049) CHARSET utf8)
begin
    delete from tig_tags_associations where tag_id = Tig_Tags_UuidToOrdered(_tag_id) and jid_id = unhex(sha2(_jid, 256));
end //
-- QUERY END:

-- QUERY START:
create procedure Tig_Tags_TagsQuery(_domain varchar(1024) CHARSET utf8, _scope smallint, _requestor varchar(2049) CHARSET utf8)
begin
    if _scope is null then
        select Tig_Tags_OrderedToUuid(t.id), 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 = unhex(sha2(_domain, 256)) AND
                t.scope in (1, 3)
            )
           OR (
                t.scope = 4 AND
                t.owner = unhex(sha2(_requestor, 256))
            );
    elseif _scope <= 3 then
        select Tig_Tags_OrderedToUuid(t.id), 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 = unhex(sha2(_domain, 256))
            );
    elseif _scope = 4 then
        select Tig_Tags_OrderedToUuid(t.id), 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 = unhex(sha2(_requestor, 256))
            );
    else
        select Tig_Tags_OrderedToUuid(t.id), name, scope, description, color, icon
                 from tig_tags t where t.scope is null;
    end if;
end //
-- QUERY END:

-- QUERY START:
create procedure Tig_Tags_TagsQueryAssociated(_domain varchar(1024) CHARSET utf8, _jid varchar(2049) CHARSET utf8, _requestor varchar(2049) CHARSET utf8)
begin
    select Tig_Tags_OrderedToUuid(t.id), 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 = unhex(sha2(_jid, 256))
        AND (
            t.scope = 4 AND t.owner = unhex(sha2(_requestor, 256))
        ) OR (
            t.scope = 3 AND t.owner = unhex(sha2(_domain, 256))
        );
end //
-- QUERY END:

-- QUERY START:
create procedure Tig_Tags_QueryJidsWithTag(_id varchar(36), _type smallint, _join_required smallint)
begin
    select j.jid
        from tig_tags_jids j
        inner join tig_tags_associations tkta on j.id = tkta.jid_id
        where
            tkta.tag_id = Tig_Tags_UuidToOrdered(_id)
            and tkta.type = _type
            and tkta.join_required = _join_required;
end //
-- QUERY END:

-- QUERY START:
create procedure Tig_Tags_TagGetAssociationType(_id varchar(36), _jid varchar(2049))
begin
    select tkta.type
        from tig_tags_associations tkta
        where
            tkta.jid_id = unhex(sha2(_jid, 256))
            and tkta.tag_id = Tig_Tags_UuidToOrdered(_id);
end //
-- QUERY END:

delimiter ;