-- --------------------------------------------------------------------------------
-- _030_node Group Routines
-- --------------------------------------------------------------------------------
DELIMITER $$$

create function __node_CanUpdate(node_id_ int unsigned, action_ varchar(20)) returns int unsigned
deterministic
begin
    if (select nt.canEditNode
        from nodeTypeT nt 
        join nodeT n on nt.id = n.nodeTypeId 
        where n.id = node_id_) != 'y' 
    then
        call raiseError1('node', action_, node_id_, 'cannot change uneditable node');
    end if;
    
    return 1;
end$$$


create function __node_UpdateCache() returns int unsigned
not deterministic modifies sql data
begin
    insert nodeT(id, nodeTypeId, name, parentNodeTypeCount, changed)
    select
        n.id,
        n.nodeTypeId,
        n.name,
        count(distinct srcnode.nodeTypeId),
        n.changed
    from nodeT n
    join zzzNodeWorksetT ws on n.id = ws.nodeId
    join nodeLinkTypeT nlt on nlt.isDefault = 'y'
    left join nodeLinkT nl on nlt.id = nl.nodeLinkTypeId 
        and n.id = nl.dstNodeId 
        and nl.metric = 1
    left join nodeT srcnode on nl.srcNodeId = srcnode.id
    where ws.connId = connection_id()
    group by n.id, n.nodeTypeId, n.name, n.changed
    on duplicate key update
        nodeT.parentNodeTypeCount = values(parentNodeTypeCount),
        nodeT.changed = values(changed);
        
    return 1;
end$$$


create function __node_UpdateCacheAll(node_id int unsigned) returns int unsigned
not deterministic modifies sql data
begin
    if node_id is not null then
        insert nodeT(id, nodeTypeId, name, parentNodeTypeCount, changed)
        select
            n.id,
            n.nodeTypeId,
            n.name,
            count(distinct srcnode.nodeTypeId),
            n.changed
        from nodeT n
        join nodeLinkTypeT nlt on nlt.isDefault = 'y'
        left join nodeLinkT nl on nlt.id = nl.nodeLinkTypeId 
            and n.id = nl.dstNodeId 
            and nl.metric = 1
        left join nodeT srcnode on nl.srcNodeId = srcnode.id
        where n.id = node_id
        group by n.id, n.nodeTypeId, n.name, n.changed
        on duplicate key update
            nodeT.parentNodeTypeCount = values(parentNodeTypeCount),
            nodeT.changed = values(changed);
    else
        insert nodeT(id, nodeTypeId, name, parentNodeTypeCount, changed)
        select
            n.id,
            n.nodeTypeId,
            n.name,
            count(distinct srcnode.nodeTypeId),
            n.changed
        from nodeT n
        join nodeLinkTypeT nlt on nlt.isDefault = 'y'
        left join nodeLinkT nl on nlt.id = nl.nodeLinkTypeId 
            and n.id = nl.dstNodeId 
            and nl.metric = 1
        left join nodeT srcnode on nl.srcNodeId = srcnode.id
        group by n.id, n.nodeTypeId, n.name, n.changed
        on duplicate key update
            nodeT.parentNodeTypeCount = values(parentNodeTypeCount),
            nodeT.changed = values(changed);
    end if;
    
    return 1;
end$$$


create function __node_Create(
    type_id_ int unsigned, 
    type_code_ varchar(30) binary, 
    name_ varchar(50)
) returns int unsigned
not deterministic modifies sql data
begin
    declare node_id int unsigned;
    declare type_id int unsigned default coalesce(type_id_, nodeType_GetByCode(type_code_));

    -- FORWARD: user_CurrentId, user_CurrentName and user_CurrentIp are defined in _060_user
    insert nodeT(
        nodeTypeId, 
        forceSinglePhoto, 
        name, 
        displayNode, 
        addedUserId,
        addedUserName,
        addedIp,
        changedUserId,
        changedUserName,
        changedIp)
    select 
        type_id, 
        nt.forceSinglePhoto, 
        name_, 
        nt.displayNode,
        user_CurrentId(),
        user_CurrentName(),
        user_CurrentIp(),
        user_CurrentId(),
        user_CurrentName(),
        user_CurrentIp()
    from nodeTypeT nt
    where nt.id = type_id;
    
    set node_id = last_insert_id();
    
    insert nodeLinkT(nodeLinkTypeId, srcNodeId, dstNodeId, metric)
    select nlt.id, node_id, node_id, 0
    from nodeLinkTypeT nlt
    where nlt.forceSelfLink = 'y';
    
    set @1 = __node_UpdateCacheAll(node_id);
    
    return node_id;
end$$$


create function __node_Duplicate(node_id_ int unsigned) returns int unsigned
not deterministic modifies sql data
begin
    declare new_id, type_id int unsigned;
    declare display_node enum('n', 'y');
    declare name varchar(50);
    
    select n.nodeTypeId, n.name, n.displayNode
    into type_id, name, display_node
    from nodeT n
    where n.id = node_id_;

    set new_id = __node_Create(type_id, null, name);
    
    update nodeT n set
        n.displayNode = display_node
    where n.id = new_id;
    
    return new_id;
end$$$


create function __node_Delete(node_id_ int unsigned) returns int unsigned
not deterministic modifies sql data
begin
    declare res int unsigned default 0;
    
    set @4 = __node_CanUpdate(node_id_, 'delete');
    
    if node_id_ is not null then
        set @1 = nodeLink_UpdateStart();
        set @2 = __nodeLink_UpdateWorkSet(node_id_, 0, 0, 1);
        delete from nodeT where id = node_id_;
        set res = row_count();
        set @3 = nodeLink_UpdateEnd();
    end if;
    
    return res;
end$$$


create function __node_Update(node_id_ int unsigned, name_ varchar(50)) returns int unsigned
not deterministic modifies sql data
begin
    set @1 = __node_CanUpdate(node_id_, 'update');
    
    update nodeT set
        name = coalesce(name_, name),
        changed = current_timestamp(),
        changedUserId = user_CurrentId(),
        changedUserName = user_CurrentName(),
        changedIp = user_CurrentIp()
    where id = node_id_;
    
    return node_id_;
end$$$


create function __node_LinkDefaults(link_code_ varchar(30) binary, node_id_ int unsigned) returns int unsigned
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);

    drop temporary table if exists nodeLinkDefaultsTmp;
    create temporary table nodeLinkDefaultsTmp(
        src_id int unsigned, 
        dst_id int unsigned, 
        primary key(src_id, dst_id)
    );
    
    insert nodeLinkDefaultsTmp(src_id, dst_id)
    select rootnode.id, n.id
    from nodeT rootnode
    join nodeRootT nr on rootnode.id = nr.id
    join allowedNodeLinkT anl on rootnode.nodeTypeId = anl.srcNodeTypeId
    join nodeT n on anl.dstNodeTypeId = n.nodeTypeId and n.id = node_id_
    left join templateT t on n.id = t.dstNodeId
    where t.dstNodeId is null
        and not exists (
            select 1 
            from nodeLinkT nl 
            where nl.nodeLinkTypeId = link_id
                and nl.srcNodeId = rootnode.id
                and nl.dstNodeId = n.id
                and nl.metric = 1);

    set @1 = nodeLink_UpdateStart();
    
    insert zzzDevNullNodeT(n)
    select __nodeLink_Create(link_code, src_id, dst_id, 0)
    from nodeLinkDefaultsTmp;

    set @2 = nodeLink_UpdateEnd();

    drop temporary table nodeLinkDefaultsTmp;

    return node_id_;
end$$$


create function node_LinkDefaults(node_id_ int unsigned) returns int unsigned
not deterministic modifies sql data
begin
    set @1 = __node_LinkDefaults('owns', node_id_);
    return node_id_;
end$$$


create function node_Touch(node_id_ int unsigned) returns int unsigned
not deterministic modifies sql data
return __node_Update(node_id_, null)$$$


-- coordT code

create function __coord_Update(
    node_id_ int unsigned, 
    latitude_ decimal(12, 7), 
    longitude_ decimal(12, 7)
) returns int unsigned
not deterministic modifies sql data
begin
    insert coordT(nodeId, latitude, longitude)
    select node_id_, latitude_, longitude_
    from zzzIntsT i
    where i.n = 1
        and latitude_ is not null 
        and longitude_ is not null
    on duplicate key update
        coordT.latitude = coalesce(latitude_, coordT.latitude),
        coordT.longitude = coalesce(longitude_, coordT.longitude);
    
    return node_id_;
end$$$


create function coord_Update(
    node_id_ int unsigned, 
    latitude_ decimal(12, 7), 
    longitude_ decimal(12, 7)
) returns int unsigned
not deterministic modifies sql data
begin
    set @1 = __node_Update(node_id_, null);
    return __coord_Update(node_id_, latitude_, longitude_);
end$$$


create function __coord_Delete(
    node_id_ int unsigned
) returns int unsigned
not deterministic modifies sql data
begin
    delete from coordT where nodeId = node_id_;
    
    return node_id_;
end$$$


create function coord_Delete(
    node_id_ int unsigned
) returns int unsigned
not deterministic modifies sql data
begin
    set @1 = __node_Update(node_id_, null);
    return __coord_Delete(node_id_);
end$$$


-- view log support
create function viewLog_Create(node_id_ int unsigned, dt_ date) returns int unsigned
not deterministic modifies sql data
begin
    declare dt date default coalesce(dt_, current_date());

    insert viewLogT(nodeId, addedDate)
    values (node_id_, dt);

    return node_id_;
end$$$


create function viewLog_UpdateStat(max_date_ date) returns int unsigned
not deterministic modifies sql data
begin
    declare max_date date default coalesce(max_date_, current_date());
    declare max_id bigint unsigned default (select max(id) from viewLogT where addedDate <= max_date);

    if max_id is not null then
        insert viewStatDailyT(nodeId, statDate, amount)
        select nodeId, addedDate, count(*)
        from viewLogT
        where id <= max_id
        group by nodeId, addedDate
        on duplicate key update
            viewStatDailyT.amount = viewStatDailyT.amount + values(amount);

        delete from viewLogT 
        where id <= max_id;
    end if;

    return 1;
end$$$
