-- --------------------------------------------------------------------------------
-- _010_utils Group Routines
-- @__sorterValue - current sorter value
-- @__sorterIncrement - the amount the sorter will be incremented by
-- @__stringQuoteOutHelperPrevC - previous char value in character filtering function
-- --------------------------------------------------------------------------------
DELIMITER $$$

create procedure raiseError(msg_ varchar(128))
begin
    declare msg varchar(128) default substring(msg_, 1, 128);

    signal sqlstate '45000'
    set message_text = msg;
end$$$


create procedure raiseError1(
    object_ varchar(30), 
    action_ varchar(30), 
    subject_ varchar(30), 
    msg_ varchar(65))
begin   
    call raiseError(concat_ws('|', 
                    coalesce(object_, '<empty>'), 
                    coalesce(action_, '<empty>'),
                    coalesce(subject_, ''), 
                    coalesce(msg_, '')));
end$$$

create procedure raiseError2(
    object_ varchar(30), 
    action_ varchar(30), 
    subject1_ varchar(30), 
    subject2_ varchar(30), 
    msg_ varchar(65))
begin 
    call raiseError(concat_ws('|', 
                    coalesce(object_, '<empty>'), 
                    coalesce(action_, '<empty>'), 
                    coalesce(subject1_, ''), 
                    coalesce(subject2_, ''), 
                    coalesce(msg_, '')));
end$$$

                    
create function testOddRaiseError(n int) returns int
not deterministic
begin
    if (n % 2 = 1) then 
        call raiseError1('testObject', 'testAction', n, '<-- this is odd!');
    end if;
    return n;
end$$$


-- dynamic sql wrapper

create procedure __executeSQL(sql_query_ varchar(4000))
modifies sql data
begin
    set @executeSQL_q = sql_query_;
    prepare executeSQL_stmt from @executeSQL_q;
    execute executeSQL_stmt;
    deallocate prepare executeSQL_stmt;
end$$$


-- workset functions

create function __nodeWorkset_Cleanup() returns int unsigned
not deterministic
begin
    delete from zzzNodeWorksetT where added < date_sub(now(), interval 1 day);
    return 1;
end$$$


create function __nodeWorkset_GetLevel() returns int unsigned
return coalesce(@__nodeWorksetLevel, 0)$$$


create function __nodeWorkset_Start() returns int unsigned
not deterministic modifies sql data
begin
    set @__nodeWorksetLevel = coalesce(@__nodeWorksetLevel, 0) + 1;
    
    if __nodeWorkset_GetLevel() = 1 then
        delete from zzzNodeWorksetT where connId = connection_id();
    end if;
    return 1;
end$$$


create function __nodeWorkset_Update(node_id int unsigned, direction int) returns int unsigned
comment 'direction=-1 - downwards; direction=1 - upwards; direction=0 - both; others - undefined'
not deterministic modifies sql data
begin
    if __nodeWorkset_GetLevel() > 0 then 
        insert zzzNodeWorksetT(nodeId, connId, isMain) 
        values (node_id, connection_id(), 1)
        on duplicate key update isMain = 1;

        -- downwards from source node
        insert zzzNodeWorksetT(nodeId, connId, isMain) 
        select l.srcNodeId, connection_id(), direction < 1
        from nodeLinkT l 
        where l.dstNodeId = node_id 
            and metric > 0
        on duplicate key update isMain = isMain or (direction < 1);

        -- upwards from source node
        insert ignore zzzNodeWorksetT(nodeId, connId, isMain) 
        select l.dstNodeId, connection_id(), direction > -1
        from nodeLinkT l 
        where l.srcNodeId = node_id 
            and metric > 0
        on duplicate key update isMain = isMain or (direction > -1);
    end if;
    
    return node_id;
end$$$


create function __nodeWorkset_End() returns int unsigned
not deterministic modifies sql data
begin
    if __nodeWorkset_GetLevel() = 1 then
        set @1 = __nodeWorkset_Cleanup();
        delete from zzzNodeWorksetT where connId = connection_id();
    end if;

    set @__nodeWorksetLevel = greatest(coalesce(@__nodeWorksetLevel, 0) - 1, 0);

    return 1;
end$$$


-- node ancestry support, based on workset

create function __nodeAncestry_Update() returns int unsigned
not deterministic modifies sql data
begin
    insert nodeAncestryT(id, languageId, tagNames)
    select 
        nad.dstNodeId,
        nad.languageId,
        group_concat(
            concat_ws(
                0xb, 
                nad.srcTypeId,
                nad.srcTypeName,
                if(
                    nad.srcInternalTypeId = 'attribute',
                    coalesce(
                        nad.srcNumericValue,
                        date_format(nad.srcDatetimeValue, nad.srcDatetimeOutputFormat), 
                        replace(replace(nad.srcTextValue, 0xb, ''), '\0', ''), 
                        ''), 
                    nad.srcName))
            order by nad.ancestrySorter, nad.srcTypeName, nad.srcName
            separator '\0') tag_names
    from nodeAncestryDataV nad
    join zzzNodeWorksetV nws on nad.dstNodeId = nws.nodeId
    where nws.isMain = 1
    group by nad.dstNodeId, nad.languageId
    on duplicate key update
        nodeAncestryT.tagNames = values(tagNames);

    return 1;
end$$$


create function __nodeAncestry_UpdateAll(reset_data_ int) returns int unsigned
not deterministic modifies sql data
comment 'updates all nodes ancestry; to be called by hand when needed'
begin
    delete from nodeAncestryT
    where coalesce(reset_data_, 0);
    
    insert nodeAncestryT(id, languageId, tagNames)
    select 
        nad.dstNodeId,
        nad.languageId,
        group_concat(
            concat_ws(
                0xb, 
                nad.srcTypeId,
                nad.srcTypeName,
                if(
                    nad.srcInternalTypeId = 'attribute',
                    coalesce(
                        nad.srcNumericValue,
                        date_format(nad.srcDatetimeValue, nad.srcDatetimeOutputFormat), 
                        replace(replace(nad.srcTextValue, 0xb, ''), '\0', ''), 
                        ''), 
                    nad.srcName))
            order by nad.ancestrySorter, nad.srcTypeName, nad.srcName
            separator '\0') tag_names
    from nodeAncestryDataV nad
    group by nad.dstNodeId, nad.languageId
    on duplicate key update
        nodeAncestryT.tagNames = values(tagNames);

    return 1;
end$$$


-- automatic sorter numbering

create function sorter_Set(sorter_value_ int, sorter_increment_ int) returns int
not deterministic 
begin
    set @__sorterValue = coalesce(sorter_value_, @__sorterValue);
    set @__sorterIncrement = coalesce(sorter_increment_, @__sorterIncrement);
    return @__sorterValue;
end$$$


create function sorter_Get() returns int
not deterministic
begin
    declare result int default @__sorterValue;
    set @__sorterValue = @__sorterValue + @__sorterIncrement;
    return result;
end$$$


create function sorter_GetIncrement() returns int
deterministic
return @__sorterIncrement$$$


-- string manipulation

create function __string_QuoteOutHelper(c_ varchar(1) binary, single_ int) returns varchar(1)
not deterministic
begin
    if single_ then
        if coalesce(@__stringQuoteOutHelperPrevC, '') = c_ then
            return '';
        else
            set @__stringQuoteOutHelperPrevC = c_;
            return c_;
        end if;
    else
        set @__stringQuoteOutHelperPrevC = c_;
        return c_;
    end if;
end$$$


create function __string_QuoteOut(
    s varchar(2048), 
    purp varchar(10),
    rpl varchar(1)
) returns varchar(2048)
deterministic
begin
    declare result varchar(2048) default '';
    declare l int default char_length(s);
    
    set @__stringQuoteOutHelperPrevC = null; -- init
    
    select
        group_concat(__string_QuoteOutHelper(coalesce(vc.replacement, vc.id, rpl), coalesce(vc.forceSingle, 'y') = 'y') order by i.n separator '')
    into 
        result
    from zzzInts10000V i
    left join zzzValidCharT vc on substring(s, i.n + 1, 1) = vc.id and vc.purpose = purp
    where i.n < l;
    
    return result;
end$$$


create function string_Alphanumeric(s varchar(2048)) returns varchar(2048)
deterministic
return __string_QuoteOut(s, 'alias', '-')$$$


create function string_Normalize(s varchar(2048)) returns varchar(2048)
deterministic
return __string_QuoteOut(s, 'norm', '')$$$


-- datetime manipulation

create function datetime_ToDouble(t datetime) returns double
deterministic
return to_days(t) + (time_to_sec(t) + 0.0) / 86400.0$$$