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

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)
    ) engine=MEMORY;

    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
    if __nodeWorkset_GetLevel() = 1 then
        set @1 = __nodeLink_UpdateRefreshExtraLinks();
        set @2 = __nodeAncestry_Update();
        -- FORWARD: __object_UpdateRatings is defined in _080_object
        set @3 = __object_UpdateRatings();
        -- FORWARD: __object_UpdateAddresses is defined in _080_object
        set @4 = __object_UpdateAddresses();
    end if;
    set @5 = __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) returns int unsigned
comment 'direction=-1 - downwards; direction=1 - upwards; direction=0 - both; others - undefined'
not deterministic
begin
    return __nodeWorkset_Update(node_id, direction);
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;

    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) and __nodeLink_UpdateWorkSet(dst_id, 1);
    end if;

    return res;
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;

    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) and __nodeLink_UpdateWorkSet(dst_id, 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)
    ) engine=MEMORY;
    
    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);
    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);
    set @2 = nodeLink_UpdateEnd();
    return 1;
end$$$


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

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 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);
    
    if src_id_ = dst_id_ 
        or exists(
            select 1 
            from nodeLinkT nl 
            where nl.nodeLinkTypeId = link_id
                and nl.srcNodeId = src_id_
                and nl.dstNodeId = dst_id_
                and nl.metric = 1)
    then
        return 1;
    end if;

    drop temporary table if exists nodeLinkRelinkInserts;

-- link to nodes, which are parents for src_id and children for parents of dst_id
    create temporary table nodeLinkRelinkInserts(srcNodeId int unsigned not null primary key) engine=MEMORY;

    insert nodeLinkRelinkInserts(srcNodeId)
    select distinct trglink.dstNodeId
    from nodeLinkT dstlink 
    join nodeLinkT trglink on dstlink.nodeLinkTypeId = trglink.nodeLinkTypeId
        and dstlink.srcNodeId = trglink.srcNodeId
    join allowedNodeLinkByNodeV dstallow on dstlink.srcNodeId = dstallow.srcNodeId 
        and dstlink.dstNodeId = dstallow.dstNodeId
    join allowedNodeLinkByNodeV trgallow on trglink.srcNodeId = trgallow.srcNodeId 
        and trglink.dstNodeId = trgallow.dstNodeId
    left join nodeLinkT exilink on dstlink.nodeLinkTypeId = exilink.nodeLinkTypeId
        and trglink.dstNodeId = exilink.srcNodeId
        and dst_id_ = exilink.dstNodeId
        and exilink.metric = 1
    where dstlink.nodeLinkTypeId = link_id
        and dstlink.dstNodeId = dst_id_
        and dstlink.metric = 1
        and trglink.dstNodeId != src_id_
        and trglink.metric = 1
        and dstallow.singleSrc = 'y'
        and trgallow.singleSrc = 'y'
        and exists (
                select 1
                from nodeLinkT srclink
                join allowedNodeLinkByNodeV srcallow on srclink.srcNodeId = srcallow.srcNodeId
                    and srclink.dstNodeId = srcallow.dstNodeId
                where srclink.nodeLinkTypeId = dstlink.nodeLinkTypeId
                    and srclink.srcNodeId = trglink.dstNodeId
                    and srclink.dstNodeId = src_id_
                    and srclink.metric = 1
                    and srcallow.singleSrc = 'y')
        and exilink.metric is null;
    
    set @1 = nodeLink_UpdateStart();
    
    insert zzzDevNullLinkT(n)
    select __nodeLink_Create(link_code, nlri.srcNodeId, dst_id_)
    from nodeLinkRelinkInserts nlri;

    set @2 = __nodeLink_Create(link_code, src_id_, dst_id_);
    
    set @3 = nodeLink_UpdateEnd();
    
    drop temporary table nodeLinkRelinkInserts;

    return 1; 
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, 
    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 link_code varchar(30) binary;
    declare link_id int unsigned;
    declare min_metric, link_to_parent int;
    
    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);
    
    if src_id_ = dst_id_ 
        or not exists(
            select 1 
            from nodeLinkT nl 
            where nl.nodeLinkTypeId = link_id
                and nl.srcNodeId = src_id_
                and nl.dstNodeId = dst_id_)
    then
        return 1;
    end if;

    drop temporary table if exists nodeLinkUnlinkDeletes;
    create temporary table nodeLinkUnlinkDeletes(srcNodeId int unsigned not null primary key) engine=MEMORY;

-- add nodes to remove list - child for src_id and parent for dst_id, but with special condition
    insert nodeLinkUnlinkDeletes(srcNodeId)
    select nl.srcNodeId
    from nodeLinkT nl
    where nl.nodeLinkTypeId = link_id
        and nl.dstNodeId = dst_id_
        and nl.metric = 1
        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 not linked to other nodes of different types - if linked to more than 1 we cannot remove it
        and (select count(distinct n2.nodeTypeId)
            from nodeLinkT nl21
            join nodeLinkT nl22 on nl21.nodeLinkTypeId = nl22.nodeLinkTypeId and nl21.srcNodeId = nl22.srcNodeId
            join nodeT n2 on nl21.srcNodeId = n2.id
            where nl21.nodeLinkTypeId = link_id
                and nl21.srcNodeId != src_id_
                and nl21.dstNodeId = dst_id_
                and nl21.metric = 1
                and nl22.dstNodeId = nl.srcNodeId
                and nl22.metric = 1
                and n2.isRoot = 'n') < 2; -- 0 or 1

    set @1 = nodeLink_UpdateStart();
    
    set @2 = __nodeLink_Delete(link_code, src_id_, dst_id_);
    
    insert zzzDevNullLinkT(n)
    select __nodeLink_Delete(link_code, nlud.srcNodeId, dst_id_)
    from nodeLinkUnlinkDeletes nlud;

    -- FORWARD: __node_LinkDefaults is defined in _03_node
    set @3 = __node_LinkDefaults(link_code, dst_id_);
    
    set @4 = nodeLink_UpdateEnd();
    
    drop temporary table nodeLinkUnlinkDeletes;
    
    return 1;
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$$$