-- --------------------------------------------------------------------------------
-- _140_comments Group Routines
-- --------------------------------------------------------------------------------
DELIMITER $$$


create function __comment_FindParentId(parent_comment_id_ bigint unsigned) returns bigint unsigned
not deterministic
begin
    declare result bigint unsigned default parent_comment_id_;
    declare new_parent_comment_id bigint unsigned;

    if result is not null then
        select parentCommentId into new_parent_comment_id from commentT where id = parent_comment_id_;

        while new_parent_comment_id is not null do
            set result = new_parent_comment_id;
            select parentCommentId into new_parent_comment_id from commentT where id = result;
        end while;
    end if;

    return result;
end$$$


create function __comment_CanCreateSubcomment(comment_id_ bigint unsigned, author_user_id_ int unsigned) returns int unsigned
deterministic
begin
    return author_user_id_ is not null
        and 
        (comment_id_ is null
        or exists (select 1 
                from commentT c 
                join nodeLinkT nl on c.objectNodeId = nl.dstNodeId 
                join nodeLinkTypeT nlt on nlt.code = 'owns' and nl.nodeLinkTypeId = nlt.id
                join userT u on u.id = author_user_id_
                where c.id = comment_id_ 
                    and (c.authorUserId = author_user_id_ or c.privateUserId is null or c.privateUserId = author_user_id_ or nl.srcNodeId = author_user_id_ or u.roleId != 'user')));
end$$$


create function __comment_CanCreateSub(comment_id_ bigint unsigned, author_user_id_ int unsigned) returns int unsigned
not deterministic
begin
    if not __comment_CanCreateSubcomment(comment_id_, author_user_id_) then
        call raiseError1('comment', 'create', comment_id_, 'cannot create subcomment for this parent comment');
    end if;

    return 1;
end$$$


create function comment_Create(
    parent_comment_id_ bigint unsigned,
    object_node_id_ int unsigned,
    author_user_id_ int unsigned,
    is_private_ enum('n', 'y'),
    value_ text
) returns bigint unsigned
not deterministic modifies sql data
begin
    declare result, parent_comment_id bigint unsigned;
    declare object_node_id, private_user_id int unsigned;

    set parent_comment_id = __comment_FindParentId(parent_comment_id_);

    if parent_comment_id is null then
        set object_node_id = object_node_id_;
        set private_user_id = if(is_private_ = 'y', author_user_id_, null); -- if starting comment thread, the users must submit private comments only for themselves
    else
        set @1 = __comment_CanCreateSub(parent_comment_id, author_user_id_);
        
        select objectNodeId, privateUserId
        into object_node_id, private_user_id
        from commentT 
        where id = parent_comment_id;
    end if;

    insert commentT(parentCommentId, objectNodeId, authorUserId, userName, privateUserId, value)
    select parent_comment_id, object_node_id, author_user_id_, u.name, private_user_id, value_
    from userT u
    where u.id = author_user_id_;

    set result = last_insert_id();

    return result;
end$$$


create function comment_Delete(comment_id_ bigint unsigned) returns bigint unsigned
not deterministic modifies sql data
begin
    delete from commentT where id = comment_id_;

    return 1;
end$$$


create function comment_Update(
    comment_id_ bigint unsigned,
    value_ text
) returns bigint unsigned
not deterministic modifies sql data
begin
    update commentT set
        value = coalesce(value_, value)
    where id = comment_id_;

    return comment_id_;
end$$$