-- --------------------------------------------------------------------------------
-- _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_ mediumtext)
modifies sql data
begin
    set @executeSQL_q = sql_query_;
    prepare executeSQL_stmt from @executeSQL_q;
    execute executeSQL_stmt;
    deallocate prepare executeSQL_stmt;
end$$$


-- maintenance

create procedure analyzeAll(db_name_ varchar(100), write_log_ int)
modifies sql data
begin
    declare q mediumtext;
    declare db_name__ varchar(100) default coalesce(db_name_, database());
    declare sess_id bigint unsigned default uuid_short();

    if coalesce(write_log_, 1) then 
        insert _maintenanceLogT(sessId, msg)
        values (sess_id, concat('analyze all for ', db_name__,' started'));
    end if;

    set @@group_concat_max_len = 262144;
    
    select concat('analyze table ', group_concat(t.TABLE_NAME))
    into q
    from information_schema.TABLES t
    where t.TABLE_SCHEMA = db_name__
        and t.TABLE_TYPE = 'BASE TABLE';
        
    call __executeSQL(q);
    
    if coalesce(write_log_, 1) then 
        insert _maintenanceLogT(sessId, msg)
        values (sess_id, concat('analyze all for ', db_name__,' finished'));
    end if;

end$$$


create procedure optimizeAll(db_name_ varchar(100), write_log_ int)
modifies sql data
begin
    declare q mediumtext;
    declare db_name__ varchar(100) default coalesce(db_name_, database());
    declare sess_id bigint unsigned default uuid_short();

    if coalesce(write_log_, 1) then 
        insert _maintenanceLogT(sessId, msg)
        values (sess_id, concat('optimize all for ', db_name__,' started'));
    end if;

    set @@group_concat_max_len = 262144;
    
    select concat('optimize table ', group_concat(t.TABLE_NAME))
    into q
    from information_schema.TABLES t
    where t.TABLE_SCHEMA = db_name__
        and t.TABLE_TYPE = 'BASE TABLE'
        and t.`ENGINE` = 'InnoDB'
        and t.TABLE_NAME != '_maintenanceLogT';
        
    call __executeSQL(q);
    
    if coalesce(write_log_, 1) then 
        insert _maintenanceLogT(sessId, msg)
        values (sess_id, concat('optimize all for ', db_name__,' finished'));
    end if;

end$$$


create function maintenance_GetLockName() returns varchar(50)
deterministic
return concat(database(), '.maintenance')$$$


create function __maintenance_DataStart(queue_limit bigint unsigned) returns bigint unsigned
not deterministic modifies sql data
begin
    declare max_id bigint unsigned default coalesce(
                                                (select id from zzzNodeQueueT order by id limit queue_limit, 1), 
                                                (select max(id) from zzzNodeQueueT));

    drop temporary table if exists __nodeQueueT;
    create temporary table __nodeQueueT(
        flag char(10) binary not null,
        nodeId int unsigned not null,
        primary key(flag, nodeId)
    );
    
    insert __nodeQueueT(flag, nodeId)
    select flag, nodeId
    from zzzNodeQueueT
    where id <= max_id
    group by flag, nodeId;
    
    return max_id;
end$$$


create function __maintenance_DataEnd(n bigint unsigned) returns bigint unsigned
not deterministic modifies sql data
begin
    drop temporary table if exists __nodeQueueT;
    
    delete from zzzNodeQueueT
    where id <= n;
    
    return 1;
end$$$


create procedure maintenance_5minute(write_log_ int)
modifies sql data
begin
    declare lock_name varchar(50) default maintenance_GetLockName();
    declare sess_id bigint unsigned default uuid_short();
    declare write_log int default coalesce(write_log_, 1);
    declare maintenance_number bigint unsigned;
    declare id_limit int unsigned default greatest(coalesce(constI('maintenance.limit.node'), 2000) - 1, 1);  -- CONST: node limit

    if get_lock(lock_name, 1) then
        if write_log then 
            insert _maintenanceLogT(sessId, msg)
            values (sess_id, concat('maintenance_5minute started'));
        end if;

        
        set maintenance_number = __maintenance_DataStart(id_limit);

        set @1 = __nodeAncestry_Update();
        -- FORWARD: __object_UpdateRatings is defined in _080_object
        set @2 = __object_UpdateRatings();
        -- FORWARD: __object_UpdateAddresses is defined in _080_object
        set @3 = __object_UpdateAddresses();
        -- FORWARD: __tag_UpdateCache is defined in _070_tag
        set @4 = __tag_UpdateCache();
        -- FORWARD: __nodeLink_UpdateRecommendCacheQueue is defined in _020_nodeLink
        set @5 = __nodeLink_UpdateRecommendCacheQueue();

        set @6 = __currency_Calculate();
        
        set @7 = __maintenance_DataEnd(maintenance_number);

        -- FORWARD: viewLog_UpdateStat is defined in _030_node
        set @8 = viewLog_UpdateStat(null);
        
        if coalesce(constI('search.cache.update'), 1) then
            -- FORWARD: __search_UpdateResultCache is defined in _110_search
            set @1 = __search_UpdateResultCache() and const_SetI('search.cache.update', 0);
        end if;

        if write_log then 
            insert _maintenanceLogT(sessId, msg)
            values (sess_id, concat('maintenance_5minute finished'));
        end if;
        set @0 = release_lock(lock_name);
    else
        if write_log then 
            insert _maintenanceLogT(sessId, msg)
            values (sess_id, concat('maintenance_5minute could not start - another instance running'));
        end if;
    end if;
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, only_direction int, linked int) returns int unsigned
comment 'direction=-1 - downwards; direction=1 - upwards; direction=0 - both; others - undefined; only_direction is the same, but controls if records added at all'
not deterministic modifies sql data
begin
    if __nodeWorkset_GetLevel() > 0 then 
        insert zzzNodeWorksetT(nodeId, connId, isMain, isExact, isLinked) 
        values (node_id, connection_id(), 1, if(direction < 0, 2, 1), linked)
        on duplicate key update 
            isMain = 1,
            isExact = if(isExact = 1, isExact, values(isExact)),
            isLinked = isLinked or linked;

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

        -- upwards from source node
        insert zzzNodeWorksetT(nodeId, connId, isMain, isLinked) 
        select l.dstNodeId, connection_id(), direction > -1, linked and direction > -1
        from nodeLinkT l 
        where l.srcNodeId = node_id 
            and metric > 0
            and only_direction > -1
        on duplicate key update 
            isMain = isMain or (direction > -1),
            isLinked = isLinked or values(isLinked);
    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,
                string_ConcatSmart(
                    ' ',
                    nad.srcUofmPre,
                    if(
                        nad.srcInternalTypeId = 'attribute',
                        coalesce(
                            nad.srcNumericValue,
                            date_format(nad.srcDatetimeValue, nad.srcDatetimeOutputFormat), 
                            string_Replace0xbZero(nad.srcTextValue),
                            ''), 
                        nad.srcName),
                    nad.srcUofmPost))
            order by nad.ancestrySorter, nad.srcTypeName, nad.srcName
            separator '\0') tag_names
    from nodeAncestryDataV nad
    join __nodeQueueT nq on nad.dstNodeId = nq.nodeId
    where nq.flag = ' '
    group by nad.dstNodeId, nad.languageId
    on duplicate key update
        nodeAncestryT.tagNames = values(tagNames);

    insert nodeAncestryAllT(id, tagNames)
    select 
        na.id,
        group_concat(concat_ws(0x1e, na.languageId, na.tagNames) separator '\Z')
    from nodeAncestryT na
    join __nodeQueueT nq on na.id = nq.nodeId
    where nq.flag = ' '
    group by na.id
    on duplicate key update 
        nodeAncestryAllT.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);

    delete from nodeAncestryAllT
    where coalesce(reset_data_, 0);
    
    insert nodeAncestryT(id, languageId, tagNames)
    select 
        nad.dstNodeId,
        nad.languageId,
        group_concat(
            concat_ws(
                0xb, 
                nad.srcTypeId,
                nad.srcTypeName,
                string_ConcatSmart(
                    ' ',
                    nad.srcUofmPre,
                    if(
                        nad.srcInternalTypeId = 'attribute',
                        coalesce(
                            nad.srcNumericValue,
                            date_format(nad.srcDatetimeValue, nad.srcDatetimeOutputFormat), 
                            string_Replace0xbZero(nad.srcTextValue), 
                            ''), 
                        nad.srcName),
                    nad.srcUofmPost))
            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);

    insert nodeAncestryAllT(id, tagNames)
    select 
        na.id,
        group_concat(concat_ws(0x1e, na.languageId, na.tagNames) separator '\Z')
    from nodeAncestryT na
    group by na.id
    on duplicate key update 
        nodeAncestryAllT.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 l int default char_length(s);
    
    set @__stringQuoteOutHelperPrevC = null; -- init
    
    return (
        select
            group_concat(
                __string_QuoteOutHelper(
                    coalesce(vc.replacement, vc.id, rpl), 
                    coalesce(vc.forceSingle, 'y') = 'y')
                order by i.n 
                separator '')
        from zzzInts10000V i
        left join zzzValidCharT vc on substring(s, i.n + 1, 1) = vc.id and vc.purpose = purp
        where i.n < l);
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', '')$$$


create function __string_ConcatSmart(s1 varchar(255), s varchar(255), s2 varchar(255)) returns varchar(1024)
deterministic
return if(s != '', concat(coalesce(s1, ''), s, coalesce(s2, '')), '')$$$


create function string_ConcatSmart(sep varchar(50), s1 varchar(255), s varchar(255), s2 varchar(255)) returns varchar(1024)
deterministic
return __string_ConcatSmart(__string_ConcatSmart('', s1, sep), s, __string_ConcatSmart(sep, s2, ''))$$$


create function string_Replace0xbZero(v varchar(2048)) returns varchar(2048)
deterministic
return replace(replace(v, 0xb, ''), '\0', '')$$$


-- datetime manipulation

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

-- consts

-- const get functions break from naming rules (would be const_GetS/GetI/GetF) for brevity
create function constS(name_ varchar(60)) returns varchar(255)
deterministic
return (select valueString from constT where id = name_)$$$

create function constI(name_ varchar(60)) returns int
deterministic
return (select valueInt from constT where id = name_)$$$

create function constF(name_ varchar(60)) returns double
deterministic
return (select valueFloat from constT where id = name_)$$$


create function const_SetS(name_ varchar(60), value_ varchar(255)) returns varchar(255)
not deterministic modifies sql data
begin
    declare data_type ENUM('string', 'int', 'float') default (select dataType from constT where id = name_);

    if (data_type is null) or (data_type = 'string') then
        insert constT(id, dataType, valueString)
        values (name_, 'string', value_)
        on duplicate key update 
            dataType = 'string',
            valueString = value_;
    else
        call raiseError2('const', 'update', substring(name_, 1, 30), 'string', 'cannot update const - mismatching type');
    end if;

    return value_;
end$$$

create function const_SetI(name_ varchar(60), value_ int) returns int
not deterministic modifies sql data
begin
    declare data_type ENUM('string', 'int', 'float') default (select dataType from constT where id = name_);

    if (data_type is null) or (data_type = 'int') then
        insert constT(id, dataType, valueInt)
        values (name_, 'int', value_)
        on duplicate key update 
            dataType = 'int',
            valueInt = value_;
    else
        call raiseError2('const', 'update', substring(name_, 1, 30), 'int', 'cannot update const - mismatching type');
    end if;

    return value_;
end$$$

create function const_SetF(name_ varchar(60), value_ double) returns double
not deterministic modifies sql data
begin
    declare data_type ENUM('string', 'int', 'float') default (select dataType from constT where id = name_);

    if (data_type is null) or (data_type = 'float') then
        insert constT(id, dataType, valueFloat)
        values (name_, 'float', value_)
        on duplicate key update
            dataType = 'float',
            valueFloat = value_;
    else
        call raiseError2('const', 'update', substring(name_, 1, 30), 'float', 'cannot update const - mismatching type');
    end if;

    return value_;
end$$$


-- ---------------------------------
-- currency
-- ---------------------------------

create function currency_SetExchangeRate(currency_id_ varchar(4) binary, effective_date_ datetime, rate_ decimal(22, 7)) returns int unsigned
not deterministic modifies sql data
begin
    insert currencyExchangeRateT(currencyId, effectiveDate, rate)
    values (currency_id_, coalesce(effective_date_, now()), rate_);

    return 1;
end$$$


create function currency_EnqueueAll() returns int unsigned
not deterministic modifies sql data
begin
    -- FORWARD: __attribute_EnqueueObjects is defined in _090_attribute
    insert zzzDevNullObjectT(n)
    select __attribute_EnqueueObjects(ap.id)
    from attributePriceT ap;

    return 1;
end$$$


-- to be called from maintenance routine
create function __currency_Calculate() returns int unsigned
not deterministic modifies sql data
begin
    set @1 = intlist_Start1();

    insert intlist1T(n)
    select nq.nodeId
    from __nodeQueueT nq
    where nq.flag = 'currency';

    -- FORWARD: __attribute_PriceCalculateList is defined in _090_attribute
    set @2 = __attribute_PriceCalculateList(null);
    -- FORWARD: __attribute_PriceUpdateCacheList is defined in _090_attribute
    set @3 = __attribute_PriceUpdateCacheList(null);

    set @4 = intlist_End1();

    return 1;
end$$$


create function __currency_CalculateAll() returns int unsigned
comment 'to be called manually'
not deterministic modifies sql data
begin
    set @1 = intlist_Start1();

    insert intlist1T(n)
    select o.id
    from objectT o;

    -- FORWARD: __attribute_PriceCalculateList is defined in _090_attribute
    set @2 = __attribute_PriceCalculateList(null);
    -- FORWARD: __attribute_PriceUpdateCacheList is defined in _090_attribute
    set @3 = __attribute_PriceUpdateCacheList(null);

    set @4 = intlist_End1();

    return 1;
end$$$


-- ---------------------------------
-- node queue
-- ---------------------------------

create function nodeQueue_Add(node_id_ int unsigned, flag_ char(10) binary) returns bigint unsigned
not deterministic modifies sql data
begin
    insert zzzNodeQueueT(nodeId, flag) values (node_id_, coalesce(flag_, ' '));

    return last_insert_id();
end$$$


-- ---------------------------------
-- int list temp tables
--
-- as we cannot use the same temp table for 2 times in 1 query, nor we can 
-- use the same table in re-entrant manner, we create independent exact copies 
-- of the same table, hence several copies of the same routines
-- ---------------------------------

create function intlist_Start1() returns int unsigned
not deterministic modifies sql data
begin
    drop temporary table if exists intlist1T;
    create temporary table intlist1T(n int unsigned not null primary key);

    return 1;
end$$$


create function intlist_StartNoReset1() returns int unsigned
not deterministic modifies sql data
begin
    create temporary table if not exists intlist1T(n int unsigned not null primary key);

    return 1;
end$$$


create function intlist_Add1(n_ int unsigned) returns int unsigned
not deterministic modifies sql data
begin
    insert ignore intlist1T(n) values (n_);

    return n_;
end$$$


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

    return 1;
end$$$

--
create function intlist_Start2() returns int unsigned
not deterministic modifies sql data
begin
    drop temporary table if exists intlist2T;
    create temporary table intlist2T(n int unsigned not null primary key);

    return 1;
end$$$


create function intlist_StartNoReset2() returns int unsigned
not deterministic modifies sql data
begin
    create temporary table if not exists intlist2T(n int unsigned not null primary key);

    return 1;
end$$$


create function intlist_Add2(n_ int unsigned) returns int unsigned
not deterministic modifies sql data
begin
    insert ignore intlist2T(n) values (n_);

    return n_;
end$$$


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

    return 1;
end$$$

-- and so on - create intlist3T routines if necessary