-- --------------------------------------------------------------------------------
-- _070_tag Group Routines
-- --------------------------------------------------------------------------------
DELIMITER $$$

create function __tag_ValidateId(tag_id_ int unsigned) returns int unsigned
deterministic
begin
    declare tag_id int unsigned;
    
    select t.id into tag_id 
    from tagT t
    join nodeT n on t.id = n.id
    join nodeTypeT nt on n.nodeTypeId = nt.id
    where t.id = tag_id_ 
        and nt.internalTypeId = 'tag';
    
    if tag_id is null then 
        call raiseError1('tag', 'validate', tag_id_, 'tag does not exist');
    end if;

    return 1;
end$$$


create function __tag_CalcDefaults(
    type_field_ enum('n', 'y'),         -- not nullable
    type_display_node_ enum('n', 'y'),  -- not nullable
    field_raw_ enum('n', 'y'),          -- nullable
    enabled_ enum('n', 'y')             -- not nullable (hopefully)
) returns enum('n', 'y')
deterministic
begin
    return if(enabled_ = 'y' and type_display_node_ = 'y', coalesce(field_raw_, type_field_, 'n'), 'n');
end$$$


create function __tag_UpdateDefaults(tag_id_ int unsigned, type_id_ int unsigned) returns int unsigned
not deterministic modifies sql data
begin
    if tag_id_ is null and type_id_ is null then
        return 1;
    end if;
    
    update tagT t join nodeT n on t.id = n.id join nodeTypeT nt on n.nodeTypeId = nt.id set
        t.canSearch = __tag_CalcDefaults(nt.canSearch, n.displayNode, t.canSearchRaw, t.enabled),
        t.displayInObject = __tag_CalcDefaults(nt.displayInObject, n.displayNode, t.displayInObjectRaw, t.enabled),
        t.displayInObjectList = __tag_CalcDefaults(nt.displayInObjectList, n.displayNode, t.displayInObjectListRaw, t.enabled)
    where t.id = coalesce(tag_id_, t.id)
        and nt.id = coalesce(type_id_, nt.id);

    return coalesce(tag_id_, 1);
end$$$


create function __tag_ClampFactor(factor_ double) returns double
deterministic
-- CONST: tag factor min/max limits
return greatest(0.1, factor_)$$$


create function __tag_ClampRatingPoints(rating_points_ int unsigned) returns int unsigned
deterministic
-- CONST: rating points min limit
return greatest(0, rating_points_)$$$


create function __tag_DefaultSorter() returns int
not deterministic
begin
    -- CONST: sorter step
    return coalesce(sorter_Get(), sorter_Set(coalesce((select max(sorter) from tagT), 0) + 10, 10));
end$$$


create function __tag_UpdateWorksetAncestry(node_id int unsigned) returns int unsigned
not deterministic modifies sql data
begin
    if __nodeWorkset_GetLevel() > 0 then 
        insert zzzNodeWorksetT(nodeId, connId, isMain, isExact) 
        values (node_id, connection_id(), 1, 1)
        on duplicate key update 
            isMain = 1,
            isExact = 1;

        -- upwards from source node
        insert zzzNodeWorksetT(nodeId, connId, isMain) 
        select nad.dstNodeId, connection_id(), 1
        from nodeAncestryDataV nad
        where nad.srcNodeId = node_id
        on duplicate key update isMain = 1;
    end if;
    
    return node_id;
end$$$


create function tag_UpdateWorksetAncestry(node_id int unsigned) returns int unsigned
not deterministic modifies sql data
begin
    set @0 = node_id is not null and __tag_UpdateCacheAll(node_id);
    
    set @1 = nodeLink_UpdateStart();
    set @2 = __tag_UpdateWorksetAncestry(node_id);
    set @3 = nodeLink_UpdateEnd();

    return node_id;
end$$$


create function __tag_UpdateCache() returns int unsigned
not deterministic modifies sql data
begin
    insert tagCacheT(id, tagNames, tagNamesSearch, tagNamesFullSearch)
    select 
        tl.id, 
        group_concat(concat_ws(0xb, tl.languageId, tl.name) separator '\0'),
        group_concat(concat_ws(0xb, tl.languageId, string_Normalize(tl.name)) separator '\0'),
        group_concat(string_Normalize(tl.name) separator ' ')
    from tagTL tl
    join __nodeQueueT nq on tl.id = nq.nodeId
    where nq.flag = ' '
    group by tl.id
    on duplicate key update
        tagCacheT.tagNames = values(tagNames),
        tagCacheT.tagNamesSearch = values(tagNamesSearch),
        tagCacheT.tagNamesFullSearch = values(tagNamesFullSearch);

    return 1;
end$$$


create function __tag_UpdateCacheAll(tag_id int unsigned) returns int unsigned
not deterministic modifies sql data
comment 'updates cache for either specific tag or all tags'
begin
    insert tagCacheT(id, tagNames, tagNamesSearch, tagNamesFullSearch)
    select 
        tl.id, 
        group_concat(concat_ws(0xb, tl.languageId, tl.name) separator '\0'),
        group_concat(concat_ws(0xb, tl.languageId, string_Normalize(tl.name)) separator '\0'),
        group_concat(string_Normalize(tl.name) separator ' ')
    from tagTL tl
    where tl.id = coalesce(tag_id, tl.id)
    group by tl.id
    on duplicate key update
        tagCacheT.tagNames = values(tagNames),
        tagCacheT.tagNamesSearch = values(tagNamesSearch),
        tagCacheT.tagNamesFullSearch = values(tagNamesFullSearch);

    return coalesce(tag_id, 1);
end$$$


create function __tag_Create(
    type_id_ int unsigned,
    type_code_ varchar(30) binary,
    internal_type_id_ varchar(10) binary,
    name_ varchar(255),
    sorter_ int,
    can_search_ enum('n', 'y'),
    display_in_object_ enum('n', 'y'),
    display_in_object_list_ enum('n', 'y'),
    factor_ double,
    rating_points_ int unsigned,
    enabled_ enum('n', 'y'),
    uofm_pre_ varchar(255),
    uofm_post_ varchar(255)
) returns int unsigned
not deterministic modifies sql data
begin
    declare node_id, type_id int unsigned;
    declare name__ varchar(255);
    declare can_search__, display_in_object__, display_in_object_list__ enum('n', 'y');
    declare sorter__ int default coalesce(sorter_, __tag_DefaultSorter());
    declare enabled__ enum('n', 'y') default coalesce(enabled_, 'y');
    declare real_type_id int unsigned default coalesce(type_id_, nodeType_GetByCode(type_code_));
    
    select 
        nt.id, 
        __tag_CalcDefaults(nt.canSearch, nt.displayNode, can_search_, enabled__), 
        __tag_CalcDefaults(nt.displayInObject, nt.displayNode, display_in_object_, enabled__), 
        __tag_CalcDefaults(nt.displayInObjectList, nt.displayNode, display_in_object_list_, enabled__), 
        coalesce(name_, ntl.name)
    into 
        type_id, 
        can_search__, 
        display_in_object__,
        display_in_object_list__,
        name__
    from nodeTypeT nt
    join nodeTypeTL ntl on nt.id = ntl.id
    join languageT l on ntl.languageId = l.id and l.isDefault = 'y'
    where nt.id = real_type_id 
        and nt.internalTypeId = internal_type_id_;
    
    set node_id = node_LinkDefaults(__node_Create(type_id, null, name__));
    
    insert tagT(
        id, 
        sorter, 
        canSearch, 
        canSearchRaw, 
        displayInObject,
        displayInObjectRaw,
        displayInObjectList,
        displayInObjectListRaw,
        factor, 
        ratingPoints,
        enabled)
    values(
        node_id, 
        sorter__, 
        can_search__, 
        can_search_,
        display_in_object__,
        display_in_object_,
        display_in_object_list__,
        display_in_object_list_,
        __tag_ClampFactor(coalesce(factor_, 1.0)), 
        __tag_ClampRatingPoints(coalesce(rating_points_, 0)),
        enabled__);
    
    insert tagTL(id, languageId, name, uofmPre, uofmPost)
    select node_id, l.id, coalesce(name_, ntl.name), coalesce(uofm_pre_, ''), coalesce(uofm_post_, '')
    from languageT l
    join nodeTypeTL ntl on ntl.id = type_id and ntl.languageId = l.id;
    
    set @1 = tag_UpdateWorksetAncestry(node_id);
    
    return node_id;
end$$$


create function __tag_DuplicateInternal(tag_id_ int unsigned) returns int unsigned
not deterministic modifies sql data
begin
    declare new_id int unsigned;
    
    set new_id = node_LinkDefaults(__node_Duplicate(tag_id_));
    
    insert tagT(
        id, 
        sorter, 
        canSearch, 
        canSearchRaw, 
        displayInObject,
        displayInObjectRaw,
        displayInObjectList,
        displayInObjectListRaw,
        factor, 
        ratingPoints,
        enabled)
    select 
        new_id, 
        t.sorter, 
        t.canSearch, 
        t.canSearchRaw, 
        t.displayInObject,
        t.displayInObjectRaw,
        t.displayInObjectList,
        t.displayInObjectListRaw,
        t.factor, 
        t.ratingPoints,
        t.enabled
    from tagT t
    where t.id = tag_id_;
    
    insert tagTL(id, languageId, name, uofmPre, uofmPost)
    select new_id, tl.languageId, tl.name, tl.uofmPre, tl.uofmPost
    from tagTL tl
    where tl.id = tag_id_;
    
    insert tagCacheT(id, tagNames, tagNamesSearch, tagNamesFullSearch)
    select new_id, tc.tagNames, tc.tagNamesSearch, tc.tagNamesFullSearch
    from tagCacheT tc
    where tc.id = tag_id_;
    
    return new_id;
end$$$


create function __tag_Duplicate(tag_id_ int unsigned) returns int unsigned
not deterministic modifies sql data
begin
    set @1 = __tag_ValidateId(tag_id_);
    
    return __tag_DuplicateInternal(tag_id_);
end$$$


create function tag_Create2(
    type_id_ int unsigned,
    type_code_ varchar(30) binary,
    name_ varchar(255),
    sorter_ int,
    can_search_ enum('n', 'y'),
    display_in_object_ enum('n', 'y'),
    display_in_object_list_ enum('n', 'y'),
    factor_ double,
    rating_points_ int unsigned,
    enabled_ enum('n', 'y'),
    uofm_pre_ varchar(255),
    uofm_post_ varchar(255)
) returns int unsigned
not deterministic modifies sql data
begin
    declare tag_id int unsigned;
    
    set tag_id = __tag_Create(
                        type_id_, 
                        type_code_, 
                        'tag', 
                        name_, 
                        sorter_, 
                        can_search_, 
                        display_in_object_, 
                        display_in_object_list_, 
                        factor_, 
                        rating_points_,
                        enabled_,
                        uofm_pre_,
                        uofm_post_);
    
    -- FORWARD: template_CreateSubs is defined in _12_template
    set @1 = template_CreateSubs(tag_id);
    
    return tag_id;
end$$$


create function tag_Create(
    type_id_ int unsigned,
    type_code_ varchar(30) binary,
    name_ varchar(255),
    sorter_ int,
    can_search_ enum('n', 'y'),
    display_in_object_ enum('n', 'y'),
    display_in_object_list_ enum('n', 'y'),
    factor_ double,
    rating_points_ int unsigned,
    enabled_ enum('n', 'y')
) returns int unsigned
not deterministic modifies sql data
return tag_Create2(type_id_, type_code_, name_, sorter_, can_search_, display_in_object_, display_in_object_list_, factor_, rating_points_, enabled_, null, null)$$$


create function tag_CreateDef(
    type_id_ int unsigned,
    type_code_ varchar(30) binary,
    name_ varchar(255)
) returns int unsigned
not deterministic modifies sql data
return tag_Create(type_id_, type_code_, name_, null, null, null, null, null, null, null)$$$


create function __tag_Delete(tag_id_ int unsigned) returns int unsigned
not deterministic modifies sql data
return __node_Delete(tag_id_)$$$


create function tag_Delete(tag_id_ int unsigned) returns int unsigned
not deterministic modifies sql data
begin
    set @1 = __tag_ValidateId(tag_id_);
    return __tag_Delete(tag_id_);
end$$$


create function __tag_Update(
    tag_id_ int unsigned,
    sorter_ int,
    can_search_ enum('n', 'y'), reset_can_search_ enum('n', 'y'), 
    display_in_object_ enum('n', 'y'), reset_display_in_object_ enum('n', 'y'),
    display_in_object_list_ enum('n', 'y'), reset_display_in_object_list_ enum('n', 'y'),
    factor_ double,
    rating_points_ int unsigned,
    enabled_ enum('n', 'y')
) returns int unsigned
not deterministic modifies sql data
begin
    set @1 = __node_Update(tag_id_, null);
    
    update tagT set
        sorter = coalesce(sorter_, sorter),
        canSearchRaw = if(reset_can_search_ = 'y', null, coalesce(can_search_, canSearchRaw)),
        displayInObjectRaw = if(reset_display_in_object_ = 'y', null, coalesce(display_in_object_, displayInObjectRaw)),
        displayInObjectListRaw = if(reset_display_in_object_list_ = 'y', null, coalesce(display_in_object_list_, displayInObjectListRaw)),
        factor = __tag_ClampFactor(coalesce(factor_, factor)),
        ratingPoints = __tag_ClampRatingPoints(coalesce(rating_points_, ratingPoints)),
        enabled = coalesce(enabled_, enabled)
    where id = tag_id_;
    
    set @2 = __tag_UpdateDefaults(tag_id_, null);
    
    -- as of now - no tag_UpdateWorksetAncestry here because cache does not depend on tagT
    
    return tag_id_;
end$$$


create function tag_Update(
    tag_id_ int unsigned,
    sorter_ int,
    can_search_ enum('n', 'y'), reset_can_search_ enum('n', 'y'), 
    display_in_object_ enum('n', 'y'), reset_display_in_object_ enum('n', 'y'),
    display_in_object_list_ enum('n', 'y'), reset_display_in_object_list_ enum('n', 'y'),
    factor_ double,
    rating_points_ int unsigned,
    enabled_ enum('n', 'y')
) returns int unsigned
not deterministic modifies sql data
begin
    declare result int unsigned;

    set @1 = __tag_ValidateId(tag_id_);

    set result = __tag_Update(
                    tag_id_, 
                    sorter_, 
                    can_search_, reset_can_search_, 
                    display_in_object_, reset_display_in_object_, 
                    display_in_object_list_, reset_display_in_object_list_, 
                    factor_, 
                    rating_points_,
                    enabled_);

    set @2 = nodeLink_UpdateStart();
    set @3 =  __nodeLink_UpdateWorkSet(tag_id_, 1, 1, 0); -- give a chance to update cache of objects
    set @4 = nodeLink_UpdateEnd();

    return result;
end$$$


create function __tag_UpdateLocal(
    tag_id_ int unsigned,
    lang_id_ char(3),
    name_ varchar(255),
    uofm_pre_ varchar(255),
    uofm_post_ varchar(255)
) returns int unsigned
not deterministic modifies sql data
begin
    set @1 = __node_Update(tag_id_, left((select name_ from languageT where id = coalesce(lang_id_, id) and isDefault = 'y'), 50));
    
    insert tagTL(id, languageId, name, uofmPre, uofmPost)
    select tag_id_, l.id, coalesce(name_, ''), coalesce(uofm_pre_, ''), coalesce(uofm_post_, '')
    from languageT l
    where l.id = coalesce(lang_id_, l.id)
    on duplicate key update 
        tagTL.name = coalesce(name_, tagTL.name),
        tagTL.uofmPre = coalesce(uofm_pre_, tagTL.uofmPre),
        tagTL.uofmPost = coalesce(uofm_post_, tagTL.uofmPost);
    
    set @2 = tag_UpdateWorksetAncestry(tag_id_);
    
    return tag_id_;
end$$$


create function tag_UpdateLocal2(
    tag_id_ int unsigned,
    lang_id_ char(3),
    name_ varchar(255),
    uofm_pre_ varchar(255),
    uofm_post_ varchar(255)
) returns int unsigned
not deterministic modifies sql data
begin
    set @1 = __tag_ValidateId(tag_id_);
    return __tag_UpdateLocal(tag_id_, lang_id_, name_, uofm_pre_, uofm_post_);
end$$$


create function tag_UpdateLocal(
    tag_id_ int unsigned,
    lang_id_ char(3),
    name_ varchar(255)
) returns int unsigned
not deterministic modifies sql data
return tag_UpdateLocal2(tag_id_, lang_id_, name_, null, null)$$$

/*
create function tag_UpdateExtraName(tag_id_ int unsigned, extra_name_ text) returns int unsigned
not deterministic modifies sql data
begin
    set @1 = __tag_ValidateId(tag_id_);

    if extra_name_ is null then
        delete from tagE xtraNameT where id = tag_id_;
    else
        insert tagE xtraNameT(id, extraName)
        values(tag_id_, extra_name_)
        on duplicate key update
            tagE xtraNameT.extraName = extra_name_;
    end if;

    set @2 = tag_UpdateWorksetAncestry(tag_id_);

    return tag_id_;
end$$$
*/