-- --------------------------------------------------------------------------------
-- _020_nodeLink Group Routines
-- --------------------------------------------------------------------------------
DELIMITER $$$

-- intermediate caching 

-- drop function if exists __nodeLink_UpdateCache$$$
create function __nodeLink_UpdateCache() returns int unsigned
not deterministic modifies sql data
begin
    insert nodeLinkT(_id, nodeLinkTypeId, srcNodeId, dstNodeId, intermediate, intermediateRecommendSingle, intermediateRecommendMultiple, added)
    select
        nl._id,
        nl.nodeLinkTypeId,
        nl.srcNodeId,
        nl.dstNodeId,
        (nl.metric > 1 
            or exists(
                select 1 
                from nodeLinkChainV chain 
                where nl.nodeLinkTypeId = chain.nodeLinkTypeId
                    and nl.srcNodeId = chain.srcNodeId
                    and nl.dstNodeId = chain.dstNodeId)),
        if(
            anl.singleSrc = 'n', 
            nl.metric > 1, 
            nl.metric > 1 
            or (anl.essential = 'n' 
                and exists(
                    select 1 
                    from nodeLinkChainV chain 
                    where nl.nodeLinkTypeId = chain.nodeLinkTypeId
                        and nl.srcNodeId = chain.srcNodeId
                        and nl.dstNodeId = chain.dstNodeId))),
        if(
            anl.singleSrc = 'y', 
            nl.metric > 1
            or (exists(
                select 1 
                from nodeLinkChainV chain 
                where nl.nodeLinkTypeId = chain.nodeLinkTypeId
                    and nl.srcNodeId = chain.srcNodeId
                    and nl.dstNodeId = chain.dstNodeId)), 
            0),
        nl.added
    from nodeLinkT nl
    join zzzNodeWorksetT ws1 on nl.srcNodeId = ws1.nodeId
    join zzzNodeWorksetT ws2 on nl.dstNodeId = ws2.nodeId
    join allowedNodeLinkByNodeV anl on nl.srcNodeId = anl.srcNodeId and nl.dstNodeId = anl.dstNodeId
    where ws1.connId = connection_id()
        and ws1.isLinked = 1
        and ws2.connId = connection_id()
        and ws2.isLinked = 1
    on duplicate key update
        nodeLinkT.intermediate = values(intermediate),
        nodeLinkT.intermediateRecommendSingle = values(intermediateRecommendSingle),
        nodeLinkT.intermediateRecommendMultiple = values(intermediateRecommendMultiple),
        nodeLinkT.added = values(added);

    return 1;
end$$$


-- drop function if exists __nodeLink_UpdateCacheAll$$$
create function __nodeLink_UpdateCacheAll(src_node_id int unsigned, dst_node_id int unsigned) returns int unsigned
not deterministic modifies sql data
begin
    if src_node_id is null and dst_node_id is null then
        insert nodeLinkT(_id, nodeLinkTypeId, srcNodeId, dstNodeId, intermediate, intermediateRecommendSingle, intermediateRecommendMultiple, added)
        select
            nl._id,
            nl.nodeLinkTypeId,
            nl.srcNodeId,
            nl.dstNodeId,
            (nl.metric > 1 
                or exists(
                    select 1 
                    from nodeLinkChainV chain 
                    where nl.nodeLinkTypeId = chain.nodeLinkTypeId
                        and nl.srcNodeId = chain.srcNodeId
                        and nl.dstNodeId = chain.dstNodeId)),
            if(
                anl.singleSrc = 'n', 
                nl.metric > 1, 
                nl.metric > 1 
                or (anl.essential = 'n' 
                    and exists(
                        select 1 
                        from nodeLinkChainV chain 
                        where nl.nodeLinkTypeId = chain.nodeLinkTypeId
                            and nl.srcNodeId = chain.srcNodeId
                            and nl.dstNodeId = chain.dstNodeId))),
            if(
                anl.singleSrc = 'y', 
                nl.metric > 1
                or (exists(
                    select 1 
                    from nodeLinkChainV chain 
                    where nl.nodeLinkTypeId = chain.nodeLinkTypeId
                        and nl.srcNodeId = chain.srcNodeId
                        and nl.dstNodeId = chain.dstNodeId)), 
                0),
            nl.added
        from nodeLinkT nl
        join allowedNodeLinkByNodeV anl on nl.srcNodeId = anl.srcNodeId and nl.dstNodeId = anl.dstNodeId
        on duplicate key update
            nodeLinkT.intermediate = values(intermediate),
            nodeLinkT.intermediateRecommendSingle = values(intermediateRecommendSingle),
            nodeLinkT.intermediateRecommendMultiple = values(intermediateRecommendMultiple),
            nodeLinkT.added = values(added);
    else
        insert nodeLinkT(_id, nodeLinkTypeId, srcNodeId, dstNodeId, intermediate, intermediateRecommendSingle, intermediateRecommendMultiple, added)
        select
            nl._id,
            nl.nodeLinkTypeId,
            nl.srcNodeId,
            nl.dstNodeId,
            (nl.metric > 1 
                or exists(
                    select 1 
                    from nodeLinkChainV chain 
                    where nl.nodeLinkTypeId = chain.nodeLinkTypeId
                        and nl.srcNodeId = chain.srcNodeId
                        and nl.dstNodeId = chain.dstNodeId)),
            if(
                anl.singleSrc = 'n', 
                nl.metric > 1, 
                nl.metric > 1 
                or (anl.essential = 'n' 
                    and exists(
                        select 1 
                        from nodeLinkChainV chain 
                        where nl.nodeLinkTypeId = chain.nodeLinkTypeId
                            and nl.srcNodeId = chain.srcNodeId
                            and nl.dstNodeId = chain.dstNodeId))),
            if(
                anl.singleSrc = 'y', 
                nl.metric > 1
                or (exists(
                    select 1 
                    from nodeLinkChainV chain 
                    where nl.nodeLinkTypeId = chain.nodeLinkTypeId
                        and nl.srcNodeId = chain.srcNodeId
                        and nl.dstNodeId = chain.dstNodeId)), 
                0),
            nl.added
        from nodeLinkT nl
        join allowedNodeLinkByNodeV anl on nl.srcNodeId = anl.srcNodeId and nl.dstNodeId = anl.dstNodeId
        where nl.srcNodeId = coalesce(src_node_id, nl.srcNodeId)
            and nl.dstNodeId = coalesce(dst_node_id, nl.dstNodeId)
        on duplicate key update
            nodeLinkT.intermediate = values(intermediate),
            nodeLinkT.intermediateRecommendSingle = values(intermediateRecommendSingle),
            nodeLinkT.intermediateRecommendMultiple = values(intermediateRecommendMultiple),
            nodeLinkT.added = values(added);
    end if;

    return 1;
end$$$


-- caching of distinct count of type ids

create function __nodeLink_UpdateRecommendCache1() returns int unsigned
not deterministic
begin
    /*insert cacheNodeRecommendSum T(srcNodeId, dstNodeId, nodeTypeIdDistinctCount)
    select 
        srcnode.id, 
        dstnode.id, 
        count(distinct node.nodeTypeId)
    from nodeT srcnode
    join nodeLinkTypeT nlt on nlt.isDefault = 'y'
    join allowedNodeLinkT anl on srcnode.nodeTypeId = anl.srcNodeTypeId
    join nodeT dstnode on anl.dstNodeTypeId = dstnode.nodeTypeId and srcnode.id != dstnode.id

    join nodeLinkForkSrcV links on links.nodeLinkTypeId = nlt.id
        and links.dstNodeId1 = srcnode.id
        and links.dstNodeId2 = dstnode.id
        and links.metric1 = 1
        and links.metric2 = 1
    join nodeT node on links.srcNodeId = node.id
    
    join zzzNodeWorksetT ws on srcnode.id = ws.nodeId

    where anl.singleSrc = 'n'
        and ws.connId = connection_id()
        and ws.isExact = 1
    group by srcnode.id, dstnode.id
    on duplicate key update
        cacheNodeRecommendSum T.nodeTypeIdDistinctCount = values(nodeTypeIdDistinctCount);*/

    /*insert cacheNodeRecommendSum T(srcNodeId, dstNodeId, nodeTypeIdDistinctCount)
    select 
        srcnode.id, 
        dstnode.id, 
        count(distinct node.nodeTypeId)
    from nodeT srcnode
    join nodeLinkTypeT nlt on nlt.isDefault = 'y'
    join allowedNodeLinkT anl on srcnode.nodeTypeId = anl.srcNodeTypeId
    join nodeT dstnode on anl.dstNodeTypeId = dstnode.nodeTypeId and srcnode.id != dstnode.id

    join nodeLinkForkSrcV links on links.nodeLinkTypeId = nlt.id
        and links.dstNodeId1 = srcnode.id
        and links.dstNodeId2 = dstnode.id
        and links.metric1 = 1
        and links.metric2 = 1
    join nodeT node on links.srcNodeId = node.id
    
    join zzzNodeWorksetT ws on dstnode.id = ws.nodeId

    where anl.singleSrc = 'n'
        and ws.connId = connection_id()
        and ws.isExact = 1
    group by srcnode.id, dstnode.id
    on duplicate key update
        cacheNodeRecommendSum T.nodeTypeIdDistinctCount = values(nodeTypeIdDistinctCount);*/

    return 1;
end$$$


create function __nodeLink_UpdateRecommendCacheQueue() returns int unsigned
not deterministic
begin
    /*insert cacheNodeRecommendSum T(srcNodeId, dstNodeId, nodeTypeIdDistinctCount)
    select 
        srcnode.id, 
        dstnode.id, 
        count(distinct node.nodeTypeId)
    from nodeT srcnode
    join nodeLinkTypeT nlt on nlt.isDefault = 'y'
    join allowedNodeLinkT anl on srcnode.nodeTypeId = anl.srcNodeTypeId
    join nodeT dstnode on anl.dstNodeTypeId = dstnode.nodeTypeId and srcnode.id != dstnode.id

    join nodeLinkForkSrcV links on links.nodeLinkTypeId = nlt.id
        and links.dstNodeId1 = srcnode.id
        and links.dstNodeId2 = dstnode.id
        and links.metric1 = 1
        and links.metric2 = 1
    join nodeT node on links.srcNodeId = node.id
    
    join __nodeQueue T nq on srcnode.id = nq.nodeId

    where anl.singleSrc = 'n'
        and nq.isLinked = 1
    group by srcnode.id, dstnode.id
    on duplicate key update
        cacheNodeRecommendSum T.nodeTypeIdDistinctCount = values(nodeTypeIdDistinctCount);

    insert cacheNodeRecommendSum T(srcNodeId, dstNodeId, nodeTypeIdDistinctCount)
    select 
        srcnode.id, 
        dstnode.id, 
        count(distinct node.nodeTypeId)
    from nodeT srcnode
    join nodeLinkTypeT nlt on nlt.isDefault = 'y'
    join allowedNodeLinkT anl on srcnode.nodeTypeId = anl.srcNodeTypeId
    join nodeT dstnode on anl.dstNodeTypeId = dstnode.nodeTypeId and srcnode.id != dstnode.id

    join nodeLinkForkSrcV links on links.nodeLinkTypeId = nlt.id
        and links.dstNodeId1 = srcnode.id
        and links.dstNodeId2 = dstnode.id
        and links.metric1 = 1
        and links.metric2 = 1
    join nodeT node on links.srcNodeId = node.id
    
    join __nodeQueue T nq on dstnode.id = nq.nodeId

    where anl.singleSrc = 'n'
        and nq.isLinked = 1
    group by srcnode.id, dstnode.id
    on duplicate key update
        cacheNodeRecommendSum T.nodeTypeIdDistinctCount = values(nodeTypeIdDistinctCount);*/
    
    return 1;
end$$$


create function __nodeLink_UpdateRecommendCacheAll(src_node_id int unsigned, dst_node_id int unsigned) returns int unsigned
not deterministic
begin
    /*if src_node_id is not null and dst_node_id is not null then
        insert cacheNodeRecommendSum T(srcNodeId, dstNodeId, nodeTypeIdDistinctCount)
        select 
            srcnode.id, 
            dstnode.id, 
            count(distinct node.nodeTypeId)
        from nodeT srcnode
        join nodeLinkTypeT nlt on nlt.isDefault = 'y'
        join allowedNodeLinkT anl on srcnode.nodeTypeId = anl.srcNodeTypeId
        join nodeT dstnode on anl.dstNodeTypeId = dstnode.nodeTypeId and srcnode.id != dstnode.id

        join nodeLinkForkSrcV links on links.nodeLinkTypeId = nlt.id
            and links.dstNodeId1 = srcnode.id
            and links.dstNodeId2 = dstnode.id
            and links.metric1 = 1
            and links.metric2 = 1
        join nodeT node on links.srcNodeId = node.id
        
        where anl.singleSrc = 'n'
            and srcnode.id = src_node_id
            and dstnode.id = dst_node_id
        group by srcnode.id, dstnode.id
        on duplicate key update
            cacheNodeRecommendSum T.nodeTypeIdDistinctCount = values(nodeTypeIdDistinctCount);
    elseif src_node_id is not null then
        insert cacheNodeRecommendSum T(srcNodeId, dstNodeId, nodeTypeIdDistinctCount)
        select 
            srcnode.id, 
            dstnode.id, 
            count(distinct node.nodeTypeId)
        from nodeT srcnode
        join nodeLinkTypeT nlt on nlt.isDefault = 'y'
        join allowedNodeLinkT anl on srcnode.nodeTypeId = anl.srcNodeTypeId
        join nodeT dstnode on anl.dstNodeTypeId = dstnode.nodeTypeId and srcnode.id != dstnode.id

        join nodeLinkForkSrcV links on links.nodeLinkTypeId = nlt.id
            and links.dstNodeId1 = srcnode.id
            and links.dstNodeId2 = dstnode.id
            and links.metric1 = 1
            and links.metric2 = 1
        join nodeT node on links.srcNodeId = node.id
        
        where anl.singleSrc = 'n'
            and srcnode.id = src_node_id
        group by srcnode.id, dstnode.id
        on duplicate key update
            cacheNodeRecommendSum T.nodeTypeIdDistinctCount = values(nodeTypeIdDistinctCount);
    elseif dst_node_id is not null then
        insert cacheNodeRecommendSum T(srcNodeId, dstNodeId, nodeTypeIdDistinctCount)
        select 
            srcnode.id, 
            dstnode.id, 
            count(distinct node.nodeTypeId)
        from nodeT srcnode
        join nodeLinkTypeT nlt on nlt.isDefault = 'y'
        join allowedNodeLinkT anl on srcnode.nodeTypeId = anl.srcNodeTypeId
        join nodeT dstnode on anl.dstNodeTypeId = dstnode.nodeTypeId and srcnode.id != dstnode.id

        join nodeLinkForkSrcV links on links.nodeLinkTypeId = nlt.id
            and links.dstNodeId1 = srcnode.id
            and links.dstNodeId2 = dstnode.id
            and links.metric1 = 1
            and links.metric2 = 1
        join nodeT node on links.srcNodeId = node.id
        
        where anl.singleSrc = 'n'
            and dstnode.id = dst_node_id
        group by srcnode.id, dstnode.id
        on duplicate key update
            cacheNodeRecommendSum T.nodeTypeIdDistinctCount = values(nodeTypeIdDistinctCount);
    end if;*/
    
    return 1;
end$$$


-- nodelink main routines

create function nodeLink_UpdateStart() returns int unsigned
not deterministic modifies sql data
begin
    return __nodeWorkset_Start();
end$$$


create function __nodeLink_UpdateCreateExtraLinks() returns int unsigned
not deterministic modifies sql data
begin
    repeat
        insert nodeLinkT(nodeLinkTypeId, srcNodeId, dstNodeId, metric)
        select l1.nodeLinkTypeId, l1.srcNodeId, l2.dstNodeId, (l1.metric + l2.metric)
        from nodeLinkT l1
        join nodeLinkT l2 on l1.nodeLinkTypeId = l2.nodeLinkTypeId and l1.dstNodeId = l2.srcNodeId
        join zzzNodeWorksetT ws1 on l1.srcNodeId = ws1.nodeId
        join zzzNodeWorksetT ws2 on l2.dstNodeId = ws2.nodeId
        left join nodeLinkT l3 on l1.nodeLinkTypeId = l3.nodeLinkTypeId
            and l3.srcNodeId = l1.srcNodeId
            and l3.dstNodeId = l2.dstNodeId
            and l3.metric <= l1.metric + l2.metric
        where l1.srcNodeId != l2.dstNodeId
            and l1.metric > 0
            and l2.metric > 0
            and ws1.connId = connection_id()
            and ws2.connId = connection_id()
            and l3.srcNodeId is null
        on duplicate key update
            nodeLinkT.metric = least(nodeLinkT.metric, l1.metric + l2.metric);
    until row_count() < 1 end repeat;

    return 1;
end$$$


create function __nodeLink_UpdateDeleteExtraLinks() returns int unsigned
not deterministic modifies sql data
begin
    declare row_cnt int unsigned default 0;

    create temporary table if not exists zzNodeLinkDelete (
        nodeLinkTypeId int unsigned not null,
        srcNodeId int unsigned not null,
        dstNodeId int unsigned not null,
        metric int not null default 1,
        primary key(nodeLinkTypeId, srcNodeId, dstNodeId),
        unique(nodeLinkTypeId, dstNodeId, srcNodeId)
    );

    create temporary table if not exists zzNodeLinkDelete2 like zzNodeLinkDelete;
    delete from zzNodeLinkDelete;
    delete from zzNodeLinkDelete2;

    insert zzNodeLinkDelete(nodeLinkTypeId, srcNodeId, dstNodeId, metric)
    select l.nodeLinkTypeId, l.srcNodeId, l.dstNodeId, l.metric
    from nodeLinkT l
    join zzzNodeWorksetT ws1 on l.srcNodeId = ws1.nodeId
    join zzzNodeWorksetT ws2 on l.dstNodeId = ws2.nodeId
    where l.metric > 1
        and ws1.connId = connection_id()
        and ws2.connId = connection_id()
        and not exists (
            select 1
            from nodeLinkT l1
            join nodeLinkT l2 on l1.nodeLinkTypeId = l2.nodeLinkTypeId and l1.dstNodeId = l2.srcNodeId
            where l1.nodeLinkTypeId = l.nodeLinkTypeId
                and l1.srcNodeId = l.srcNodeId
                and l2.dstNodeId = l.dstNodeId
                and l1.metric > 0
                and l2.metric > 0
                and l.metric = l1.metric + l2.metric);
    set row_cnt = row_count();

    while row_cnt > 0 do
        insert ignore zzNodeLinkDelete2
        select * from zzNodeLinkDelete ld;

        insert ignore zzNodeLinkDelete(nodeLinkTypeId, srcNodeId, dstNodeId, metric)
        select l.nodeLinkTypeId, l.srcNodeId, l.dstNodeId, l.metric
        from nodeLinkT l
        join zzzNodeWorksetT ws1 on l.srcNodeId = ws1.nodeId
        join zzzNodeWorksetT ws2 on l.dstNodeId = ws2.nodeId
        join zzNodeLinkDelete2 l1 on l.nodeLinkTypeId = l1.nodeLinkTypeId and l.srcNodeId = l1.srcNodeId
        join nodeLinkT l2 on l.nodeLinkTypeId = l2.nodeLinkTypeId and l1.dstNodeId = l2.srcNodeId and l.dstNodeId = l2.dstNodeId
        where l.metric > 1
            and l2.metric > 0
            and l.metric = l1.metric + l2.metric
            and ws1.connId = connection_id()
            and ws2.connId = connection_id();

        set row_cnt = row_count();

        insert ignore zzNodeLinkDelete2
        select * from zzNodeLinkDelete ld;

        insert ignore zzNodeLinkDelete(nodeLinkTypeId, srcNodeId, dstNodeId, metric)
        select l.nodeLinkTypeId, l.srcNodeId, l.dstNodeId, l.metric
        from nodeLinkT l
        join zzzNodeWorksetT ws1 on l.srcNodeId = ws1.nodeId
        join zzzNodeWorksetT ws2 on l.dstNodeId = ws2.nodeId
        join nodeLinkT l1 on l.nodeLinkTypeId = l1.nodeLinkTypeId and l.srcNodeId = l1.srcNodeId
        join zzNodeLinkDelete2 l2 on l.nodeLinkTypeId = l2.nodeLinkTypeId and l1.dstNodeId = l2.srcNodeId and l.dstNodeId = l2.dstNodeId
        where l.metric > 1
            and l1.metric > 0
            and l.metric = l1.metric + l2.metric
            and ws1.connId = connection_id()
            and ws2.connId = connection_id();

        set row_cnt = row_cnt + row_count();
    end while;

    delete from nodeLinkT
    using nodeLinkT
    join zzNodeLinkDelete l on nodeLinkT.nodeLinkTypeId = l.nodeLinkTypeId
        and nodeLinkT.srcNodeId = l.srcNodeId
        and nodeLinkT.dstNodeId = l.dstNodeId
        and nodeLinkT.metric = l.metric;

    return 1;
end$$$


create function __nodeLink_UpdateRefreshExtraLinks() returns int unsigned
not deterministic modifies sql data
begin
    declare deleted, added int unsigned;
    set deleted = __nodeLink_UpdateDeleteExtraLinks();
    set added = __nodeLink_UpdateCreateExtraLinks();
    return deleted + added;
end$$$


create function nodeLink_UpdateEnd() returns int unsigned
not deterministic modifies sql data
begin
    declare node_id int unsigned;

    if __nodeWorkset_GetLevel() = 1 then
        set @1 = __nodeLink_UpdateRefreshExtraLinks();
        
        -- FORWARD: __node_UpdateCache is defined in _030_node
        set @2 = __node_UpdateCache();
        
        set @3 = __nodeLink_UpdateCache();
        
        set @4 = __nodeLink_UpdateRecommendCache1();

        -- make an exception for edited object - if there is 1 object, update its address and ratings right away
        if (select count(*) from zzzNodeWorksetV nw join objectT o on nw.nodeId = o.id where nw.isMain = 1) = 1 then
            select o.id into node_id from zzzNodeWorksetV nw join objectT o on nw.nodeId = o.id where nw.isMain = 1 limit 1;

            -- FORWARD: __object_UpdateAddressesAll and __object_UpdateRatingsAll are defined in _080_object
            set @1 = node_id is not null and __object_UpdateAddressesAll(node_id); -- and __object_UpdateRatingsAll(node_id);
        end if;
        
        -- put into the queue for the maintenance routine to work with
        insert zzzDevNullBigintLinkT(n)
        select nodeQueue_Add(nw.nodeId, null)
        from zzzNodeWorksetV nw
        where nw.isMain = 1;

        -- set flag for maintenance routine
        set @5 = const_SetI('search.cache.update', 1);
    end if;
    set @6 = __nodeWorkset_End();
    return 1;
end$$$


create function nodeLink_IsUpdateOn() returns int unsigned
not deterministic
begin 
    return (__nodeWorkset_GetLevel() > 0);
end$$$


create function __nodeLink_UpdateWorkSet(node_id int unsigned, direction int, only_direction int, linked int) returns int unsigned
comment 'direction=-1 - downwards; direction=1 - upwards; direction=0 - both; others - undefined'
not deterministic
begin
    return __nodeWorkset_Update(node_id, direction, only_direction, linked);
end$$$


create function __nodeLink_Create(
    link_code varchar(30) binary, 
    src_id int unsigned, 
    dst_id int unsigned,
    force_workset_ int
) returns int unsigned
not deterministic modifies sql data
begin
    declare res int unsigned;
    declare force_workset int default coalesce(force_workset_, 1);

    if not exists (select 1 
                    from allowedNodeLinkT anl 
                    join nodeT src on anl.srcNodeTypeId = src.nodeTypeId 
                    join nodeT dst on anl.dstNodeTypeId = dst.nodeTypeId
                    where src.id = src_id and dst.id = dst_id) 
    then
        call raiseError2('nodeLink', 'create', src_id, dst_id, 'cannot create link between');
    end if;
    
    insert nodeLinkT(nodeLinkTypeId, srcNodeId, dstNodeId, metric)
    select nlt.id, src_id, dst_id, 1
    from nodeLinkTypeT nlt
    join nodeLinkTypeT nltdef on nltdef.isDefault = 'y'
    where nlt.code = coalesce(link_code, nltdef.code)
        and not exists (select 1 
                        from nodeLinkT l 
                        where l.nodeLinkTypeId = nlt.id 
                            and l.srcNodeId = src_id 
                            and l.dstNodeId = dst_id 
                            and l.metric <= 1)
    on duplicate key update nodeLinkT.metric = 1;
    
    set res = row_count();
    if nodeLink_IsUpdateOn() = 1 and res > 0 then
        set @1 = __nodeLink_UpdateWorkSet(src_id, -1, if(force_workset, 0, -1), 1) and __nodeLink_UpdateWorkSet(dst_id, 1, if(force_workset, 0, 1), 1);
    end if;

    return res;
end$$$


create function __nodeLink_Delete(
    link_code varchar(30) binary, 
    src_id int unsigned, 
    dst_id int unsigned,
    force_workset_ int
) returns int unsigned
not deterministic modifies sql data
begin
    declare res int unsigned;
    declare force_workset int default coalesce(force_workset_, 1);

    delete from nodeLinkT
    using nodeLinkT
    join nodeLinkTypeT nlt on nodeLinkT.nodeLinkTypeId = nlt.id
    join nodeLinkTypeT nltdef on nltdef.isDefault = 'y'
    where nlt.code = coalesce(link_code, nltdef.code)
        and nodeLinkT.srcNodeId = src_id 
        and nodeLinkT.dstNodeId = dst_id 
        and nodeLinkT.metric = 1;

    set res = row_count();
    if nodeLink_IsUpdateOn() = 1 and res > 0 then
        set @1 = __nodeLink_UpdateWorkSet(src_id, -1, if(force_workset, 0, -1), 1) and __nodeLink_UpdateWorkSet(dst_id, 1, if(force_workset, 0, 1), 1);
    end if;

    return res;
end$$$


create function __nodeLink_CreateExtraLinks() returns int unsigned
not deterministic modifies sql data
begin
    declare row_cnt int unsigned default 0;
    repeat
        insert nodeLinkT(nodeLinkTypeId, srcNodeId, dstNodeId, metric)
        select l1.nodeLinkTypeId, l1.srcNodeId, l2.dstNodeId, (l1.metric + l2.metric)
        from nodeLinkT l1
        join nodeLinkT l2 on l1.nodeLinkTypeId = l2.nodeLinkTypeId and l1.dstNodeId = l2.srcNodeId
        left join nodeLinkT l3 on l3.nodeLinkTypeId = l1.nodeLinkTypeId 
            and l3.srcNodeId = l1.srcNodeId
            and l3.dstNodeId = l2.dstNodeId
            and l3.metric <= l1.metric + l2.metric
        where l1.srcNodeId != l2.dstNodeId
            and l1.metric > 0
            and l2.metric > 0
            and l3.srcNodeId is null
        on duplicate key update 
            nodeLinkT.metric = least(nodeLinkT.metric, l1.metric + l2.metric);

        set row_cnt = row_cnt + row_count();
    until row_count() < 1 end repeat;
    return row_cnt;
end$$$


create function __nodeLink_DeleteExtraLinks() returns int unsigned
not deterministic modifies sql data
begin
    declare row_cnt int unsigned default 0;
    create temporary table if not exists zzNodeLinkDelete (
        nodeLinkTypeId int unsigned not null,
        srcNodeId int unsigned not null, 
        dstNodeId int unsigned not null, 
        metric int not null default 1,
        primary key(nodeLinkTypeId, srcNodeId, dstNodeId),
        unique(nodeLinkTypeId, dstNodeId, srcNodeId)
    );
    
    repeat
        delete from zzNodeLinkDelete;
        
        insert zzNodeLinkDelete(nodeLinkTypeId, srcNodeId, dstNodeId, metric)
        select l.nodeLinkTypeId, l.srcNodeId, l.dstNodeId, l.metric
        from nodeLinkT l
        where l.metric > 1
            and not exists (
                select 1 
                from nodeLinkT l1 
                join nodeLinkT l2 on l1.nodeLinkTypeId = l2.nodeLinkTypeId and l1.dstNodeId = l2.srcNodeId 
                where l1.nodeLinkTypeId = l.nodeLinkTypeId
                    and l1.srcNodeId = l.srcNodeId 
                    and l2.dstNodeId = l.dstNodeId
                    and l1.metric > 0
                    and l2.metric > 0
                    and l.metric = l1.metric + l2.metric);

        insert ignore zzNodeLinkDelete(nodeLinkTypeId, srcNodeId, dstNodeId, metric)
        select l.nodeLinkTypeId, l.srcNodeId, l.dstNodeId, l.metric
        from nodeLinkT l
        join nodeLinkT l2 on l.nodeLinkTypeId = l2.nodeLinkTypeId 
            and l.srcNodeId = l2.srcNodeId 
            and l.dstNodeId = l2.dstNodeId 
            and l.metric > l2.metric
        where l.metric > 1;
        
        delete from nodeLinkT
        using nodeLinkT 
        join zzNodeLinkDelete l on nodeLinkT.nodeLinkTypeId = l.nodeLinkTypeId 
            and nodeLinkT.srcNodeId = l.srcNodeId 
            and nodeLinkT.dstNodeId = l.dstNodeId 
            and nodeLinkT.metric = l.metric;

        set row_cnt = row_cnt + row_count();
    until row_count() < 1 end repeat;
    return row_cnt;
end$$$


create function __nodeLink_RefreshExtraLinks() returns int unsigned
not deterministic modifies sql data
begin
    declare deleted, added int unsigned;
    set deleted = __nodeLink_DeleteExtraLinks();
    set added = __nodeLink_CreateExtraLinks();
    return deleted + added;
end$$$


create function nodeLink_Create(
    link_code varchar(30) binary, 
    src_id int unsigned, 
    dst_id int unsigned
) returns int unsigned
not deterministic modifies sql data
begin
    declare res int unsigned;
    set @1 = nodeLink_UpdateStart();
    set res = __nodeLink_Create(link_code, src_id, dst_id, null);
    set @2 = nodeLink_UpdateEnd();
    return 1;
end$$$


create function nodeLink_Create2(
    link_code varchar(30) binary, 
    src_id int unsigned, 
    dst_id int unsigned
) returns int unsigned
not deterministic modifies sql data
begin
    set @1 = nodeLink_Create(link_code, src_id, dst_id);
    return dst_id;
end$$$


create function nodeLink_Delete(
    link_code varchar(30) binary, 
    src_id int unsigned, 
    dst_id int unsigned
) returns int unsigned
not deterministic modifies sql data
begin
    declare res int unsigned;
    set @1 = nodeLink_UpdateStart();
    set res = __nodeLink_Delete(link_code, src_id, dst_id, null);
    set @2 = nodeLink_UpdateEnd();
    return 1;
end$$$


-- ---------------------------------
-- nodeLink smart linking
-- ---------------------------------

create function __nodeLink_DestinationStart() returns int unsigned
not deterministic modifies sql data
begin
    drop temporary table if exists __nodeLinkDestinationT;
    create temporary table __nodeLinkDestinationT(nodeId int unsigned not null primary key);

    return 1;
end$$$


create function __nodeLink_DestinationAdd(node_id int unsigned) returns int unsigned
not deterministic modifies sql data
begin
    insert ignore __nodeLinkDestinationT(nodeId) values (node_id);

    return 1;
end$$$


create function __nodeLink_DestinationEnd() returns int unsigned
not deterministic modifies sql data
begin
    drop temporary table if exists __nodeLinkDestinationT;

    return 1;
end$$$


create function __nodeLink_Relink(
    link_code_ varchar(30) binary, 
    src_id_ int unsigned
) returns int unsigned
comment 'links src_id and all nodes in __nodeLinkDestinationT, detects and removes unnecessary linking'
not deterministic modifies sql data
begin
    -- NOTE: __nodeLinkDestinationT table must be created and filled in by caller

    declare link_code varchar(30) binary;
    declare link_id int unsigned;
    
    select nlt.id, nlt.code into link_id, link_code
    from nodeLinkTypeT nlt join nodeLinkTypeT nltdef on nltdef.isDefault = 'y'
    where nlt.code = coalesce(link_code_, nltdef.code);
    
    drop temporary table if exists nodeLinkRelinkInserts;
    drop temporary table if exists nodeLinkRelinkSrc;
    drop temporary table if exists nodeLinkRelinkDst;

    create temporary table nodeLinkRelinkInserts(
        srcNodeId int unsigned not null,
        dstNodeId int unsigned not null,
        primary key(srcNodeId, dstNodeId)
    );

    create temporary table nodeLinkRelinkSrc(
        nodeId int unsigned not null primary key,
        nodeTypeId int unsigned not null,
        index(nodeTypeId)
    );
    
    create temporary table nodeLinkRelinkDst(
        nodeId int unsigned not null primary key,
        nodeTypeId int unsigned not null,
        index(nodeTypeId)
    );

    -- fill in table with all parents
    insert nodeLinkRelinkSrc(nodeId, nodeTypeId) 
    select n.id, n.nodeTypeId
    from nodeT n
    where n.id = src_id_;
    
    insert ignore nodeLinkRelinkSrc(nodeId, nodeTypeId)
    select nl.srcNodeId, anl.srcNodeTypeId
    from nodeLinkT nl
    join allowedNodeLinkByNodeV anl on nl.srcNodeId = anl.srcNodeId and nl.dstNodeId = anl.dstNodeId
    where nl.nodeLinkTypeId = link_id
        and nl.dstNodeId = src_id_
        and nl.metric = 1
        and anl.singleSrc = 'y';

    -- fill in table with all children
    insert nodeLinkRelinkDst(nodeId, nodeTypeId) 
    select n.id, n.nodeTypeId
    from nodeT n
    join __nodeLinkDestinationT nld on n.id = nld.nodeId;
    
    insert ignore nodeLinkRelinkDst(nodeId, nodeTypeId)
    select nl.dstNodeId, anl.dstNodeTypeId
    from nodeLinkT nl
    join allowedNodeLinkByNodeV anl on nl.srcNodeId = anl.srcNodeId and nl.dstNodeId = anl.dstNodeId
    join __nodeLinkDestinationT nld on nl.srcNodeId = nld.nodeId
    where nl.nodeLinkTypeId = link_id
        and nl.metric = 1
        and anl.singleSrc = 'y';

    -- add src and dst nodes to the list
    insert nodeLinkRelinkInserts(srcNodeId, dstNodeId)
    select anl.srcNodeId, anl.dstNodeId
    from allowedNodeLinkByNodeV anl
    join __nodeLinkDestinationT nld on anl.dstNodeId = nld.nodeId
    where anl.srcNodeId = src_id_;

    -- fill all valid combinations from src and dst sets, so all singleSrc='y' nodes will be linked
    insert ignore nodeLinkRelinkInserts(srcNodeId, dstNodeId)
    select src.nodeId, dst.nodeId
    from nodeLinkRelinkSrc src
    join allowedNodeLinkT anl on src.nodeTypeId = anl.srcNodeTypeId
    join nodeLinkRelinkDst dst on anl.dstNodeTypeId = dst.nodeTypeId
    left join nodeLinkT exilink on exilink.nodeLinkTypeId = link_id
        and src.nodeId = exilink.srcNodeId
        and dst.nodeId = exilink.dstNodeId
        and exilink.metric = 1
    where anl.singleSrc = 'y'
        and exilink.metric is null;
    
    -- go and link
    set @1 = nodeLink_UpdateStart();
    
    insert zzzDevNullLinkT(n)
    select __nodeLink_Create(link_code, nlri.srcNodeId, nlri.dstNodeId, null)
    from nodeLinkRelinkInserts nlri;

    set @2 = nodeLink_UpdateEnd();
    
    drop temporary table nodeLinkRelinkInserts;
    drop temporary table nodeLinkRelinkSrc;
    drop temporary table nodeLinkRelinkDst;

    return 1; 
end$$$


create function nodeLink_Relink(
    link_code_ varchar(30) binary, 
    src_id_ int unsigned, 
    dst_id_ int unsigned
) returns int unsigned
comment 'links src_id and dst_id, detects and removes unnecessary linking'
not deterministic modifies sql data
begin
    declare res int unsigned default 1;

    if src_id_ = dst_id_ then
        return 1;
    end if;

    set @1 = __nodeLink_DestinationStart();
    set @2 = __nodeLink_DestinationAdd(dst_id_);
    set res = __nodeLink_Relink(link_code_, src_id_);
    set @3 = __nodeLink_DestinationEnd();

    return res;
end$$$


create function nodeLink_Relink2(
    link_code_ varchar(30) binary, 
    src_id_ int unsigned, 
    dst_id_ int unsigned
) returns int unsigned
comment 'calls nodeLink_Relink and returns dst_id'
not deterministic modifies sql data
begin
    set @1 = nodeLink_Relink(link_code_, src_id_, dst_id_);
    return dst_id_;
end$$$


create function __nodeLink_Unlink(
    link_code_ varchar(30) binary, 
    src_id_ int unsigned, 
    unlink_from_src_id_ int unsigned  -- if true - unlink from src_id as well; otherwise - will keep the link to src_id_
) returns int unsigned
comment 'unlinks src_id and all nodes in __nodeLinkDestinationT'
not deterministic modifies sql data
begin
    declare link_code varchar(30) binary;
    declare link_id int unsigned;
    declare min_metric, link_to_parent int;
    declare unlink_from_src_id int unsigned default coalesce(unlink_from_src_id_, 1);
    
    select nlt.id, nlt.code into link_id, link_code
    from nodeLinkTypeT nlt join nodeLinkTypeT nltdef on nltdef.isDefault = 'y'
    where nlt.code = coalesce(link_code_, nltdef.code);
    
    drop temporary table if exists nodeLinkUnlinkDeletes;
    drop temporary table if exists nodeLinkUnlinkSrc;
    drop temporary table if exists nodeLinkUnlinkDst;

    drop temporary table if exists __nodeLinkDestination2T;

    create temporary table nodeLinkUnlinkDeletes(
        srcNodeId int unsigned not null,
        dstNodeId int unsigned not null,
        primary key(srcNodeId, dstNodeId)
    );

    create temporary table nodeLinkUnlinkSrc(
        nodeId int unsigned not null primary key,
        nodeTypeId int unsigned not null,
        index(nodeTypeId)
    );
    
    create temporary table nodeLinkUnlinkDst(
        nodeId int unsigned not null primary key,
        nodeTypeId int unsigned not null,
        index(nodeTypeId)
    );

    create temporary table __nodeLinkDestination2T like __nodeLinkDestinationT;
    insert __nodeLinkDestination2T(nodeId) select nodeId from __nodeLinkDestinationT;

    -- add nodes to remove list - child for src_id and parent for dst_id, but with special condition
    insert nodeLinkUnlinkDeletes(srcNodeId, dstNodeId)
    select nl.srcNodeId, nl.dstNodeId
    from nodeLinkT nl
    join nodeT n on nl.srcNodeId = n.id
    join __nodeLinkDestinationT nld on nl.dstNodeId = nld.nodeId
    left join nodeRootT nr on n.id = nr.id
    where nl.nodeLinkTypeId = link_id
        and nl.metric = 1
        and nr.isRoot is null
        and (unlink_from_src_id or n.id != src_id_)
        and exists (
            select 1 
            from nodeLinkT nl1
            where nl1.nodeLinkTypeId = link_id
                and nl1.srcNodeId = src_id_
                and nl1.dstNodeId = nl.srcNodeId
                and nl1.metric > 0)
    -- check if node in question is linked to other nodes of different types
        and (select count(distinct n21.nodeTypeId) 
            from nodeLinkT nl21
            join nodeT n21 on nl21.srcNodeId = n21.id
            where nl21.nodeLinkTypeId = link_id
                and nl21.dstNodeId = nl.srcNodeId
                and nl21.metric = 1) > 
            (select count(distinct n22.nodeTypeId)
            from nodeLinkForkSrcV nl22
            join nodeT n22 on nl22.srcNodeId = n22.id
            join __nodeLinkDestination2T nld2 on nl22.dstNodeId2 = nld2.nodeId
            where nl22.nodeLinkTypeId = link_id
                and nl22.srcNodeId != src_id_
                and nl22.dstNodeId1 = nl.srcNodeId
                and nl22.metric1 = 1
                and nl22.metric2 = 1);

    -- fill in table with all parents
    insert nodeLinkUnlinkSrc(nodeId, nodeTypeId) 
    select n.id, n.nodeTypeId
    from nodeT n
    left join nodeRootT nr on n.id = nr.id
    where n.id = src_id_
        and nr.isRoot is null
        and unlink_from_src_id;
    
    -- insert only intermediate nodes - children for src and parents for dst
    insert nodeLinkUnlinkSrc(nodeId, nodeTypeId)
    select nl.midNodeId, anl.srcNodeTypeId
    from nodeLinkChainTransitionV nl
    join allowedNodeLinkByNodeV anl on nl.midNodeId = anl.srcNodeId and nl.dstNodeId = anl.dstNodeId
    join nodeT n on nl.midNodeId = n.id
    join __nodeLinkDestinationT nld on nl.dstNodeId = nld.nodeId
    left join nodeRootT nr on n.id = nr.id
    where nl.nodeLinkTypeId = link_id
        and nl.srcNodeId = src_id_
        and anl.singleSrc = 'y'
        and nr.isRoot is null;

    -- fill in table with all children
    insert nodeLinkUnlinkDst(nodeId, nodeTypeId) 
    select n.id, n.nodeTypeId
    from nodeT n
    join __nodeLinkDestinationT nld on n.id = nld.nodeId;
    
    insert nodeLinkUnlinkDst(nodeId, nodeTypeId)
    select nl.dstNodeId, anl.dstNodeTypeId
    from nodeLinkT nl
    join allowedNodeLinkByNodeV anl on nl.srcNodeId = anl.srcNodeId and nl.dstNodeId = anl.dstNodeId
    join __nodeLinkDestinationT nld on nl.srcNodeId = nld.nodeId
    where nl.nodeLinkTypeId = link_id
        and nl.metric = 1
        and anl.singleSrc = 'y';

    -- add src to dst link if allowed
    insert ignore nodeLinkUnlinkDeletes(srcNodeId, dstNodeId)
    select nl.srcNodeId, nl.dstNodeId
    from nodeLinkT nl 
    join __nodeLinkDestinationT nld on nl.dstNodeId = nld.nodeId
    where nl.nodeLinkTypeId = link_id
        and nl.srcNodeId = src_id_
        and unlink_from_src_id;

    -- fill all valid combinations from src and dst sets, so all singleSrc='y' nodes will be unlinked
    insert ignore nodeLinkUnlinkDeletes(srcNodeId, dstNodeId)
    select nl.srcNodeId, nl.dstNodeId
    from nodeLinkT nl 
    join nodeLinkUnlinkSrc src on nl.srcNodeId = src.nodeId
    join nodeLinkUnlinkDst dst on nl.dstNodeId = dst.nodeId
    join allowedNodeLinkT anl on src.nodeTypeId = anl.srcNodeTypeId and dst.nodeTypeId = anl.dstNodeTypeId
    where nl.nodeLinkTypeId = link_id
        and nl.metric = 1
        and anl.singleSrc = 'y';

    -- go and unlink
    set @1 = nodeLink_UpdateStart();
    
    insert zzzDevNullLinkT(n)
    select __nodeLink_Delete(link_code, nlud.srcNodeId, nlud.dstNodeId, null)
    from nodeLinkUnlinkDeletes nlud;

    -- FORWARD: __node_LinkDefaults is defined in _03_node
    -- set @2 = __node_LinkDefaults(link_code, dst_ id_);
    
    set @3 = nodeLink_UpdateEnd();
    
    drop temporary table nodeLinkUnlinkDeletes;
    drop temporary table nodeLinkUnlinkSrc;
    drop temporary table nodeLinkUnlinkDst;

    drop temporary table __nodeLinkDestination2T;
    
    return 1;
end$$$


create function nodeLink_Unlink(
    link_code_ varchar(30) binary, 
    src_id_ int unsigned, 
    dst_id_ int unsigned
) returns int unsigned
comment 'unlinks src_id and dst_id, links dst_id to parents of src_id'
not deterministic modifies sql data
begin
    declare res int unsigned default 1;

    if src_id_ = dst_id_ then
        return 1;
    end if;

    set @1 = __nodeLink_DestinationStart();
    set @2 = __nodeLink_DestinationAdd(dst_id_);
    set res = __nodeLink_Unlink(link_code_, src_id_, 1);
    set @3 = __nodeLink_DestinationEnd();

    return res;
end$$$


create function nodeLink_Unlink2(
    link_code_ varchar(30) binary, 
    src_id_ int unsigned, 
    dst_id_ int unsigned
) returns int unsigned
comment 'calls nodeLink_Unlink and returns dst_id'
not deterministic modifies sql data
begin
    set @1 = nodeLink_Unlink(link_code_, src_id_, dst_id_);
    return dst_id_;
end$$$

-- ---------------------------------
-- nodeLink relocating nodes in tree
-- ---------------------------------

create function nodeLink_RelocateStart() returns int unsigned
not deterministic modifies sql data
begin
    set @1 = __nodeLink_DestinationStart();
    return 1;
end$$$


create function nodeLink_RelocateAddNode(node_id_ int unsigned) returns int unsigned
not deterministic modifies sql data
begin
    set @1 = __nodeLink_DestinationAdd(node_id_);
    return 1;
end$$$


create function nodeLink_RelocateEnd(new_parent_node_id_ int unsigned) returns int unsigned
not deterministic modifies sql data
begin
    -- __nodeLink_DestinationStart is called in nodeLink_RelocateStart

    insert zzzDevNullT(n)
    select __nodeLink_Unlink(null, nr.id, 0)
    from nodeRootT nr;

    set @1 = __nodeLink_Relink(null, new_parent_node_id_);
    set @2 = __nodeLink_DestinationEnd();
    return 1;
end$$$