-- --------------------------------------------------------------------------------
-- _110_search Group Routines
-- @__searchId - current search session
-- --------------------------------------------------------------------------------
DELIMITER $$$


create function search_CurrentId() returns bigint unsigned
deterministic
return @__searchId$$$


create function __search_ResetSession(session_id_ bigint unsigned) returns int unsigned
not deterministic modifies sql data
begin
    delete from searchCriterionT where sessionId = session_id_;

    return 1;
end$$$


create function search_Reset() returns int unsigned
not deterministic modifies sql data
begin
    set @1 = __search_ResetSession(search_CurrentId());
    
    -- delete those sessions which don't have corresponding records in searchSavedT
    delete from searchSessionT
    using searchSessionT
    left join searchSavedT ss on searchSessionT.id = ss.sessionId
    where ss.sessionId is null
        and searchSessionT.added < date_sub(now(), interval 1 day);

    return 1;
end$$$


create function search_StartSession(sid_ varchar(100) binary, reset_params_ int unsigned) returns bigint unsigned
not deterministic modifies sql data
begin
    declare session_id bigint unsigned default (select sessionId from searchSessionSidT where sid = sid_);

    if session_id is null then
        insert searchSessionT(added) values(now()); 

        set session_id = last_insert_id();

        insert searchSessionSidT(sessionId, sid) values(session_id, sid_);
    else 
        update searchSessionT set 
            added = now()
        where id = session_id;
    end if;

    set @__searchId = session_id;

    set @1 = coalesce(reset_params_, 1) and search_Reset();

    return session_id;
end$$$


create function __search_SetTag(
    session_id_ bigint unsigned, 
    node_id_ int unsigned, 
    force_node_ int,
    return_node_id_ int
) returns bigint unsigned
not deterministic modifies sql data
begin
    declare node_id, node_type_id int unsigned;
    declare internal_type_id varchar(10) binary;
    declare force_node int default coalesce(force_node_, 0);
    declare criterion_id bigint unsigned;

    select
        n.id, if(nt.searchAnd = 'y', null, nt.id), nt.internalTypeId  -- attributes must have searchAnd = y
    into
        node_id, node_type_id, internal_type_id
    from nodeT n 
    join nodeTypeT nt on n.nodeTypeId = nt.id
    left join tagT t on n.id = t.id
    where n.id = node_id_
        and (force_node or t.canSearch is null or t.canSearch = 'y');

    if node_id is not null then 
        insert ignore searchCriterionT(sessionId, nodeId, nodeTypeId, internalTypeId)
        values (session_id_, node_id, node_type_id, internal_type_id);

        -- cannot use last_insert_id because it won't work if inserting duplicate values for sessionId and nodeId
        select sc.id into criterion_id from searchCriterionT sc where sc.sessionId = session_id_ and sc.nodeId = node_id; 
    end if;
    
    return if(coalesce(return_node_id_, 0), node_id_, criterion_id);
end$$$


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


create function search_SetAttributeNumeric(
    attr_id_ int unsigned, 
    value_low_ decimal(22, 6), 
    value_high_ decimal(22, 6)
) returns int unsigned
not deterministic modifies sql data
begin
    declare criterion_id bigint unsigned default __search_SetTag(search_CurrentId(), attr_id_, null, null);

    insert searchCriterionNumericT(
        searchCriterionId,
        valueLow, 
        valueHigh)
    select 
        criterion_id,
        __attribute_ClampValueNumeric(attr_id_, value_low_), 
        __attribute_ClampValueNumeric(attr_id_, value_high_)
    from attributeT a
    where a.id = attr_id_
        and criterion_id is not null
        and a.dataTypeId = 'numeric'
    on duplicate key update
        searchCriterionNumericT.valueLow = __attribute_ClampValueNumeric(attr_id_, value_low_),
        searchCriterionNumericT.valueHigh = __attribute_ClampValueNumeric(attr_id_, value_high_);

    return 1;
end$$$


create function search_SetAttributeDatetime(
    attr_id_ int unsigned, 
    value_low_ datetime, 
    value_high_ datetime
) returns int unsigned
not deterministic modifies sql data
begin
    declare criterion_id bigint unsigned default __search_SetTag(search_CurrentId(), attr_id_, null, null);

    insert searchCriterionDatetimeT(
        searchCriterionId, 
        valueLow, 
        valueHigh)
    select 
        criterion_id,
        __attribute_ClampValueDatetime(attr_id_, value_low_), 
        __attribute_ClampValueDatetime(attr_id_, value_high_)
    from attributeT a
    where a.id = attr_id_
        and criterion_id is not null
        and a.dataTypeId = 'datetime'
    on duplicate key update
        searchCriterionDatetimeT.valueLow = __attribute_ClampValueDatetime(attr_id_, value_low_),
        searchCriterionDatetimeT.valueHigh = __attribute_ClampValueDatetime(attr_id_, value_high_);

    return 1;
end$$$


create function search_SetAttributePrice(
    attr_id_ int unsigned, 
    currency_id_ varchar(4) binary,
    value_low_ decimal(22, 6), 
    value_high_ decimal(22, 6)
) returns int unsigned
not deterministic modifies sql data
begin
    declare criterion_id bigint unsigned default __search_SetTag(search_CurrentId(), attr_id_, null, null);

    insert searchCriterionPriceT(
        searchCriterionId,
        currencyId,
        valueLow, 
        valueHigh)
    select 
        criterion_id,
        currency_id_,
        __attribute_ClampValuePrice(attr_id_, value_low_), 
        __attribute_ClampValuePrice(attr_id_, value_high_)
    from attributeT a
    where a.id = attr_id_
        and criterion_id is not null
        and a.dataTypeId = 'price'
    on duplicate key update
        searchCriterionPriceT.currencyId = currency_id_,
        searchCriterionPriceT.valueLow = __attribute_ClampValuePrice(attr_id_, value_low_),
        searchCriterionPriceT.valueHigh = __attribute_ClampValuePrice(attr_id_, value_high_);

    return 1;
end$$$


create function __search_RectifyCriteria(session_id_ bigint unsigned) returns int unsigned
not deterministic modifies sql data
begin
    -- insert parent nodes into criteria for those nodes, whose linking is singleSrc=y
    drop temporary table if exists searchRectifyCriteriaInserts;
    create temporary table searchRectifyCriteriaInserts(
        nodeId int unsigned not null primary key
    );
    
    insert ignore searchRectifyCriteriaInserts(nodeId)
    select nl.srcNodeId
    from searchCriterionT sc
    join nodeLinkTypeT nlt on nlt.isDefault = 'y'
    join tagT dst_tag on sc.nodeId = dst_tag.id  -- only tags and attributes
    join nodeLinkT nl on nlt.id = nl.nodeLinkTypeId and sc.nodeId = nl.dstNodeId
    join tagT src_tag on nl.srcNodeId = src_tag.id
    join allowedNodeLinkByNodeV anl on nl.srcNodeId = anl.srcNodeId and nl.dstNodeId = anl.dstNodeId
    left join searchCriterionT exicriterion on sc.sessionId = exicriterion.sessionId 
        and nl.srcNodeId = exicriterion.nodeId
    where sc.sessionId = session_id_
        and nl.metric = 1
        and anl.singleSrc = 'y'
        and exicriterion.nodeId is null;
        
    insert ignore zzzDevNullSearchT(n)
    select __search_SetTag(session_id_, srci.nodeId, null, 1)
    from searchRectifyCriteriaInserts srci;

    drop temporary table searchRectifyCriteriaInserts;
    
    -- remove not needed nodes from criteria
    drop temporary table if exists searchRectifyCriteriaDeletes;
    create temporary table searchRectifyCriteriaDeletes(
        nodeId int unsigned not null primary key
    );

    -- empty attribute criteria
    insert ignore searchRectifyCriteriaDeletes(nodeId)
    select sc.nodeId
    from searchCriterionNumericT scn
    join searchCriterionT sc on scn.searchCriterionId = sc.id
    where sc.sessionId = session_id_
        and scn.valueLow is null
        and scn.valueHigh is null;

    insert ignore searchRectifyCriteriaDeletes(nodeId)
    select sc.nodeId
    from searchCriterionDatetimeT scd
    join searchCriterionT sc on scd.searchCriterionId = sc.id
    where sc.sessionId = session_id_
        and scd.valueLow is null
        and scd.valueHigh is null;

    insert ignore searchRectifyCriteriaDeletes(nodeId)
    select sc.nodeId
    from searchCriterionPriceT scp
    join searchCriterionT sc on scp.searchCriterionId = sc.id
    where sc.sessionId = session_id_
        and scp.valueLow is null
        and scp.valueHigh is null;

    -- remove unneeded criteria
    delete from searchCriterionT
    using searchCriterionT
    join searchRectifyCriteriaDeletes srcd on searchCriterionT.nodeId = srcd.nodeId
    where searchCriterionT.sessionId = session_id_;

    drop temporary table searchRectifyCriteriaDeletes;

    return 1;
end$$$


create function __search_RectifyCriteriaPost(session_id_ bigint unsigned) returns int unsigned
not deterministic modifies sql data
begin
    delete from searchCriterionT
    using searchCriterionT
    join tagT tag on searchCriterionT.nodeId = tag.id
    left join searchResultT sr on searchCriterionT.nodeId = sr.nodeId and searchCriterionT.sessionId = sr.sessionId
    where searchCriterionT.sessionId = session_id_
        and sr.nodeId is null;

    return 1;
end$$$


create function __search_SetCachedResult(session_id_ bigint unsigned) returns int unsigned
not deterministic modifies sql data
begin
    insert ignore searchResultT(sessionId, nodeId)
    select session_id_, nodeId
    from cacheSearchDefaultResultT;

    return 1;
end$$$


create function __search_SetDefaultResult(session_id_ bigint unsigned) returns int unsigned
not deterministic modifies sql data
begin 
    insert ignore searchResultT(sessionId, nodeId, collapsed)
    select session_id_, o.id, 'n'
    from objectT o
    join searchCriterionT sc on o.id = sc.nodeId
    join objectStatusT os on o.statusId = os.id
    where sc.sessionId = session_id_
        and os.public = 'y';

    return 1;
end$$$


-- must be called after __search_RectifyCriteria
create function __search_CheckRequiredForObject(session_id_ bigint unsigned) returns int unsigned
comment 'must be called after __search_RectifyCriteria'
not deterministic
begin
    declare total_required, criterion_required, parent_required int unsigned;

    select count(*) into total_required
    from nodeTypeT
    where requiredForObject = 'y';

    select count(distinct nt.id) into criterion_required
    from searchCriterionT sc
    join nodeT n on sc.nodeId = n.id
    join nodeTypeT nt on n.nodeTypeId = nt.id
    where sc.sessionId = session_id_
        and nt.canSearch = 'y'
        and nt.requiredForObject = 'y';

    select count(distinct nt.id) into parent_required
    from searchCriterionT sc
    join nodeLinkOwnsV nlo on sc.nodeId = nlo.dstNodeId
    join nodeT n on nlo.srcNodeId = n.id
    join nodeTypeT nt on n.nodeTypeId = nt.id
    where sc.sessionId = session_id_
        and nlo.metric = 1
        and nt.canSearch = 'n'
        and nt.requiredForObject = 'y';

    return (criterion_required + parent_required) = total_required;
end$$$


create procedure __search_SearchTags(
    session_id_ bigint unsigned, 
    count_notnull_ int, 
    count_null_ int)
modifies sql data
begin
    insert ignore searchResultT(sessionId, nodeId)
    select session_id_, n.id
    from nodeT n
    join nodeLinkTypeT nlt on nlt.isDefault = 'y'
    join tagT t on n.id = t.id and t.canSearch = 'y'
    join nodeLinkT nl1 on nl1.nodeLinkTypeId = nlt.id
        and nl1.dstNodeId = n.id
        and nl1.metric = 1
    join searchCriterionT sc1 on nl1.srcNodeId = sc1.nodeId and sc1.sessionId = session_id_
    left join nodeLinkT nl2 on nl2.nodeLinkTypeId = nlt.id
        and nl2.dstNodeId = n.id
        and nl2.metric = 1
    left join tagT t2 on nl2.srcNodeId = t2.id
        and t2.canSearch = 'y'
    left join nodeT n2 on t2.id = n2.id
    group by n.id
    having count(distinct n2.nodeTypeId) = count(distinct sc1.nodeTypeId);
end$$$


create procedure __search_Prepare(session_id_ bigint unsigned)
modifies sql data
begin
    drop temporary table if exists searchUpdateExtraObjectsData;
    create temporary table searchUpdateExtraObjectsData(
        tagId int unsigned not null primary key,
        cnt int unsigned not null default 0
    );

    drop temporary table if exists searchObjectTemp;
    create temporary table searchObjectTemp(objectId int unsigned not null primary key);
    
    insert searchUpdateExtraObjectsData(tagId, cnt)
    select tagresult.nodeId, 0
    from searchResultT tagresult
    where tagresult.sessionId = session_id_;
end$$$


create procedure __search_Finalize(session_id_ bigint unsigned)
modifies sql data
begin
    update searchResultT sr join searchUpdateExtraObjectsData sueod on sr.nodeId = sueod.tagId set
        sr.extraObjects = sueod.cnt
    where sr.sessionId = session_id_;

    drop temporary table searchObjectTemp;

    drop temporary table searchUpdateExtraObjectsData;
end$$$


create procedure __search_SearchObjects(
    session_id_ bigint unsigned, 
    count_notnull_ int, 
    sum_null_ double, 
    sum_null_tolerance_ double,
    count_numerics_ int, 
    count_datetimes_ int,
    count_prices_ int)
modifies sql data
begin
    declare limit_dataset int unsigned default 
        if(
            count_notnull_ > 0, 
            constI('search.limit.criteria.notempty'), 
            constI('search.limit.criteria.empty'));

    declare count_numerics int default greatest(count_numerics_, 1);
    declare count_datetimes int default greatest(count_datetimes_, 1);
    declare count_prices int default greatest(count_prices_, 1);

    if count_notnull_ > 0 then

        -- NOTE: searchObjectTemp created in __search_Prepare
        insert searchObjectTemp(objectId)
        select n.id
        from nodeT n
        join nodeLinkTypeT nlt on nlt.isDefault = 'y'
        join objectT o on n.id = o.id
        join objectStatusT os on o.statusId = os.id

        join nodeLinkT strict_criterion_link on nlt.id = strict_criterion_link.nodeLinkTypeId
            and n.id = strict_criterion_link.dstNodeId
            and strict_criterion_link.metric = 1
        join searchCriterionT strict_criterion on strict_criterion_link.srcNodeId = strict_criterion.nodeId                        
            and strict_criterion.nodeTypeId is not null
            and strict_criterion.internalTypeId = 'tag'

        where strict_criterion.sessionId = session_id_
            and os.public = 'y'

        group by n.id

        having count(distinct strict_criterion.nodeTypeId) = count_notnull_
        order by o.rating desc, n.changed desc
        limit limit_dataset;

    else

        -- NOTE: searchObjectTemp created in __search_Prepare
        insert searchObjectTemp(objectId)
        select n.id
        from nodeT n
        join nodeLinkTypeT nlt on nlt.isDefault = 'y'
        join objectT o on n.id = o.id
        join objectStatusT os on o.statusId = os.id

        where os.public = 'y'

        group by n.id

        order by o.rating desc, n.changed desc
        limit limit_dataset;

    end if;

    insert ignore searchResultT(sessionId, nodeId, relevancy)
    select 
        session_id_, 
        sot.objectId, 
        (
            (coalesce(
                sum(nonstrict_tag.factor) / greatest(count(nonstrict_tag.id) / greatest(count(distinct nonstrict_tag.id), 1.0), 1.0), 
                0) / sum_null_ -- sum_null_ must be sanitized to be != 0
            + (count(distinct scn.searchCriterionId) / count_numerics)
            + (count(distinct scd.searchCriterionId) / count_datetimes)
            + (count(distinct scp.searchCriterionId) / count_datetimes))
        / 4) relevancy
    from searchObjectTemp sot
    join nodeLinkTypeT nlt on nlt.isDefault = 'y'

    left join nodeLinkT nonstrict_criterion_link on nlt.id = nonstrict_criterion_link.nodeLinkTypeId 
        and sot.objectId  = nonstrict_criterion_link.dstNodeId 
        and nonstrict_criterion_link.metric = 1
    left join searchCriterionT nonstrict_criterion on nonstrict_criterion_link.srcNodeId = nonstrict_criterion.nodeId 
        and nonstrict_criterion.sessionId = session_id_
        and nonstrict_criterion.nodeTypeId is null
        and nonstrict_criterion.internalTypeId = 'tag'
    left join tagT nonstrict_tag on nonstrict_criterion.nodeId = nonstrict_tag.id

    left join attributeValueNumericT avn on sot.objectId = avn.objectId
    left join searchCriterionT scn_ on avn.attributeId = scn_.nodeId and scn_.sessionId = session_id_
    left join searchCriterionNumericT scn on scn_.id = scn.searchCriterionId
        and avn.value >= coalesce(scn.valueLow, avn.value)
        and avn.value <= coalesce(scn.valueHigh, avn.value)

    left join attributeValueDatetimeT avd on sot.objectId = avd.objectId
    left join searchCriterionT scd_ on avd.attributeId = scd_.nodeId and scd_.sessionId = session_id_
    left join searchCriterionDatetimeT scd on scd_.id = scd.searchCriterionId
        and avd.value >= coalesce(scd.valueLow, avd.value)
        and avd.value <= coalesce(scd.valueHigh, avd.value)

    left join attributeValuePriceT avp on sot.objectId = avp.objectId
    left join searchCriterionT scp_ on avp.attributeId = scp_.nodeId and scp_.sessionId = session_id_
    left join searchCriterionPriceT scp on scp_.id = scp.searchCriterionId and avp.currencyId = scp.currencyId
        and avp.value >= coalesce(scp.valueLow, avp.value)
        and avp.value <= coalesce(scp.valueHigh, avp.value)

    group by sot.objectId;
end$$$


create procedure __search_UpdateExtraObjectsCriteria(session_id_ bigint unsigned)
modifies sql data
begin
    insert searchUpdateExtraObjectsData(tagId, cnt)
    select sr.nodeId, 1
    from searchResultT sr
    join searchCriterionT criterion on sr.sessionId = criterion.sessionId and sr.nodeId = criterion.nodeId
    where sr.sessionId = session_id_
    on duplicate key update
        searchUpdateExtraObjectsData.cnt = 1;
end$$$


create procedure __search_UpdateExtraObjectsSimple(session_id_ bigint unsigned)
modifies sql data
begin
    insert searchUpdateExtraObjectsData(tagId, cnt)
    select result_node.id, 1
    from searchResultT sr
    join tagT result_tag on sr.nodeId = result_tag.id and sr.sessionId = session_id_
    join nodeT result_node on sr.nodeId = result_node.id
    join nodeLinkTypeT nlt on nlt.isDefault = 'y'

    join nodeLinkT result_link on nlt.id = result_link.nodeLinkTypeId
        and result_node.id = result_link.srcNodeId
        and result_link.metric = 1

    join nodeT object_node on result_link.dstNodeId = object_node.id
    join searchResultT object_result on sr.sessionId = object_result.sessionId and object_node.id = object_result.nodeId
    join objectT object on object_node.id = object.id
    join objectStatusT os on object.statusId = os.id and os.public = 'y'

    left join searchCriterionT no_criterion on sr.sessionId = no_criterion.sessionId and sr.nodeId = no_criterion.nodeId

    where no_criterion.nodeId is null

    group by result_node.id
    on duplicate key update
        searchUpdateExtraObjectsData.cnt = 1;
end$$$


create procedure __search_UpdateExtraObjectsComplex(session_id_ bigint unsigned)
modifies sql data
begin
    insert searchUpdateExtraObjectsData(tagId, cnt)
    select result_node.id, 1
    from searchResultT sr
    join tagT result_tag on sr.nodeId = result_tag.id and sr.sessionId = session_id_
    join nodeT result_node on sr.nodeId = result_node.id
    join nodeTypeT result_node_type on result_node.nodeTypeId = result_node_type.id and result_node_type.searchAnd = 'n'
    join nodeLinkTypeT nlt on nlt.isDefault = 'y'

    join nodeLinkT result_link on nlt.id = result_link.nodeLinkTypeId
        and result_node.id = result_link.srcNodeId
        and result_link.metric = 1

    join nodeT object_node on result_link.dstNodeId = object_node.id
    join objectT object on object_node.id = object.id
    join objectStatusT os on object.statusId = os.id and os.public = 'y'

    join nodeLinkT criterion_link on nlt.id = criterion_link.nodeLinkTypeId
        and object_node.id = criterion_link.dstNodeId
        and criterion_link.metric = 1
    join searchCriterionT criterion on sr.sessionId = criterion.sessionId
        and criterion_link.srcNodeId = criterion.nodeId
        and criterion.nodeTypeId != result_node.nodeTypeId

    join searchCriterionT source_criterion on sr.sessionId = source_criterion.sessionId
        and source_criterion.nodeTypeId is not null
        and source_criterion.internalTypeId = 'tag'
        and source_criterion.nodeTypeId != result_node.nodeTypeId

    join searchCriterionT type_criterion on sr.sessionId = type_criterion.sessionId and result_node.nodeTypeId = type_criterion.nodeTypeId

    left join searchCriterionT no_criterion on sr.sessionId = no_criterion.sessionId and sr.nodeId = no_criterion.nodeId

    where no_criterion.nodeId is null

    group by result_node.id
    having count(distinct source_criterion.nodeTypeId) = count(distinct criterion.nodeTypeId)
    on duplicate key update
        searchUpdateExtraObjectsData.cnt = 1;
end$$$


create procedure __search_UpdateExtraObjects(session_id_ bigint unsigned)
modifies sql data
begin
    call __search_UpdateExtraObjectsCriteria(session_id_);

    call __search_UpdateExtraObjectsSimple(session_id_);

    call __search_UpdateExtraObjectsComplex(session_id_);
end$$$


create procedure __search_UpdateSortCache(session_id_ bigint unsigned)
modifies sql data
begin
    drop temporary table if exists searchUpdateSortCacheData;
    create temporary table searchUpdateSortCacheData(
        objectId int unsigned not null,
        attributeId int unsigned not null,
        primary key(objectId, attributeId)
    );

    insert ignore searchUpdateSortCacheData(objectId, attributeId)
    select o.id, sas.id
    from searchResultV sr
    join objectT o on sr.nodeId = o.id
    join searchAttributeSortV sas on 1=1
    left join cacheObjectSortT os on o.id = os.objectId and sas.sortId = os.sortOptionId
    where os.objectId is null;
    
    insert zzzDevNullSearchT(n)
    select __object_UpdateSortCache(suscd.objectId, suscd.attributeId, null)
    from searchUpdateSortCacheData suscd;

    drop temporary table searchUpdateSortCacheData;
end$$$


create procedure search_Search()
modifies sql data
begin
    declare session_id bigint unsigned default search_CurrentId();
    declare 
        count_notnull, 
        count_null, 
        count_numerics, 
        count_datetimes,
        count_prices int; 
        -- search_objects int;
    declare sum_null double;

    start transaction;
    
    set @1 = __search_RectifyCriteria(session_id);

    select count(distinct sc.nodeTypeId) into count_notnull
    from searchCriterionT sc
    where sc.sessionId = session_id
        and sc.nodeTypeId is not null
        and sc.internalTypeId = 'tag';

    select count(*), greatest(coalesce(sum(t.factor), 0.001), 0.001) into count_null, sum_null
    from searchCriterionT sc
    join tagT t on sc.nodeId = t.id
    where sc.sessionId = session_id
        and sc.nodeTypeId is null
        and sc.internalTypeId = 'tag';

    select count(*) into count_numerics
    from searchCriterionT sc
    join searchCriterionNumericT scn on sc.id = scn.searchCriterionId
    where sc.sessionId = session_id;

    select count(*) into count_datetimes
    from searchCriterionT sc
    join searchCriterionDatetimeT scd on sc.id = scd.searchCriterionId
    where sc.sessionId = session_id;

    select count(*) into count_prices
    from searchCriterionT sc
    join searchCriterionPriceT scp on sc.id = scp.searchCriterionId
    where sc.sessionId = session_id;

    -- set search_objects = __search_CheckRequiredForObject(session_id);

    delete from searchResultT where sessionId = session_id;

    set @1 = __search_SetCachedResult(session_id);

    call __search_SearchTags(session_id, count_notnull, count_null);

    set @2 = __search_SetDefaultResult(session_id);
    
    set @3 = __search_RectifyCriteriaPost(session_id);
    
    call __search_Prepare(session_id);

    -- CONST: sum_null_tolerance_ is 0.001
    call __search_SearchObjects(session_id, count_notnull, sum_null, 0.001, count_numerics, count_datetimes, count_prices);

    call __search_UpdateExtraObjects(session_id);

    call __search_UpdateSortCache(session_id);

    call __search_Finalize(session_id);

    commit;

end$$$


create function __search_UpdateResultCache() returns int unsigned
not deterministic modifies sql data
begin
    drop temporary table if exists searchUpdateResultCacheData;
    create temporary table searchUpdateResultCacheData(nodeId int unsigned not null primary key);
    
    -- from __search_SetDefaultResult
    insert ignore searchUpdateResultCacheData(nodeId)
    select t.id
    from tagT t
    join nodeT n on t.id = n.id
    join nodeTypeT nt on n.nodeTypeId = nt.id
    join nodeRootLinkV nrl on t.id = nrl.dstNodeId and nrl.metric = 1 and nrl.intermediate = 0
    where t.canSearch = 'y'
        and (nrl.typeRequiredForObject = 'y' or nt.requiredForObject = 'y')
        and nrl.displayNode = 'y';

    -- from __search_SearchTags
    if constS('search.server.version') = '5.5' then
        insert ignore searchUpdateResultCacheData(nodeId)
        select n.id
        from nodeT n
        join nodeLinkTypeT nlt on nlt.isDefault = 'y'
        join nodeTypeT nt on n.nodeTypeId = nt.id
        join tagT t on n.id = t.id and t.canSearch = 'y'
        where nt.requiredForObject = 'n'
            and not exists(
                select 1
                from nodeLinkT nl
                join tagT nonroottag on nl.srcNodeId = nonroottag.id
                left join nodeRootT nr on nonroottag.id = nr.id
                where nl.nodeLinkTypeId = nlt.id
                    and nl.dstNodeId = n.id
                    and nl.metric = 1
                    and nr.isRoot is null);
    else 
        insert ignore searchUpdateResultCacheData(nodeId)
        select n.id
        from nodeT n
        join nodeLinkTypeT nlt on nlt.isDefault = 'y'
        join nodeTypeT nt on n.nodeTypeId = nt.id
        join tagT t on n.id = t.id and t.canSearch = 'y'
        left join (
            select nl.nodeLinkTypeId, nl.dstNodeId
            from nodeLinkT nl 
            join tagT nonroottag on nl.srcNodeId = nonroottag.id
            left join nodeRootT nr on nonroottag.id = nr.id
            where nl.metric = 1
                and nr.isRoot is null
        ) as ne on ne.nodeLinkTypeId = nlt.id and ne.dstNodeId = n.id
        where nt.requiredForObject = 'n'
            and ne.dstNodeId is null;
    end if;

    -- update search result cache table
    delete from cacheSearchDefaultResultT
    using cacheSearchDefaultResultT
    left join searchUpdateResultCacheData surcd on cacheSearchDefaultResultT.nodeId = surcd.nodeId
    where surcd.nodeId is null;

    insert ignore cacheSearchDefaultResultT(nodeId)
    select surcd.nodeId
    from searchUpdateResultCacheData surcd;

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


-- search session duplicate, save, restore and delete

create function __search_DuplicateCriterion(id_ bigint unsigned, new_session_id_ bigint unsigned) returns bigint unsigned
not deterministic modifies sql data
begin
    declare result bigint unsigned;

    insert searchCriterionT(sessionId, nodeId, nodeTypeId, internalTypeId)
    select new_session_id_, sc.nodeId, sc.nodeTypeId, sc.internalTypeId
    from searchCriterionT sc
    where sc.id = id_;

    set result = last_insert_id();

    insert searchCriterionNumericT(searchCriterionId, valueLow, valueHigh)
    select result, scn.valueLow, scn.valueHigh
    from searchCriterionNumericT scn
    where scn.searchCriterionId = id_;

    insert searchCriterionDatetimeT(searchCriterionId, valueLow, valueHigh)
    select result, scd.valueLow, scd.valueHigh
    from searchCriterionDatetimeT scd
    where scd.searchCriterionId = id_;

    insert searchCriterionPriceT(searchCriterionId, currencyId, valueLow, valueHigh)
    select result, scp.currencyId, scp.valueLow, scp.valueHigh
    from searchCriterionPriceT scp
    where scp.searchCriterionId = id_;

    return result;
end$$$


create function __search_DuplicateSession(session_id_ bigint unsigned, dest_session_id_ bigint unsigned) returns bigint unsigned
not deterministic modifies sql data
begin
    declare result bigint unsigned;

    if dest_session_id_ is null then
        insert searchSessionT(added) values(now());

        set result = last_insert_id();
    else
        set result = dest_session_id_;

        set @1 = __search_ResetSession(result);
    end if;

    drop temporary table if exists duplicateSessionCriteria;
    create temporary table duplicateSessionCriteria(id bigint unsigned not null primary key)
    select sc.id
    from searchCriterionT sc
    where sc.sessionId = session_id_;

    insert zzzDevNullBigintSearchT(n)
    select __search_DuplicateCriterion(dsc.id, result)
    from duplicateSessionCriteria dsc;

    drop temporary table duplicateSessionCriteria;

    return result;
end$$$


create function __search_FormatAttributeCriterion(
    attribute_id_ int unsigned, 
    search_criterion_id_ bigint unsigned,
    language_id_ char(3)
) returns varchar(255)
deterministic
begin
    declare result varchar(255) default '-';
    declare decimal_round, price_round int;
    declare dt_format varchar(255);

    set decimal_round = (select decimalRound from attributeNumericT where id = attribute_id_);
    if decimal_round is not null then
        select 
            concat_ws('-', 
                coalesce(round(valueLow, decimal_round), '-'),
                coalesce(round(valueHigh, decimal_round), '-')) 
        into result
        from searchCriterionNumericT 
        where searchCriterionId = search_criterion_id_;
    else
        set price_round = (select priceRound from attributePriceT where id = attribute_id_);
        if price_round is not null then 
            select 
                concat_ws('-', 
                    currencyId,
                    coalesce(round(valueLow, price_round), '-'),
                    coalesce(round(valueHigh, price_round), '-'))
            into result
            from searchCriterionPriceT
            where searchCriterionId = search_criterion_id_;
        else
            select outputFormat 
            into dt_format
            from attributeDatetimeT ad 
            join attributeDatetimeFormatTL adfl on ad.datetimeStorageId = adfl.id and adfl.languageId = language_id_
            where ad.id = attribute_id_;

            if dt_format is not null then
                select 
                    concat_ws('-', 
                        coalesce(date_format(valueLow, dt_format), '-'),
                        coalesce(date_format(valueHigh, dt_format), '-')) 
                into result
                from searchCriterionDatetimeT
                where searchCriterionId = search_criterion_id_;
            end if;
        end if;
    end if;

    return result;
end$$$


create function __search_CreateSessionName(session_id_ bigint unsigned, language_id_ char(3)) returns varchar(255)
deterministic
begin
    declare result varchar(255);
    select 
        substring(
            group_concat(
                if(
                    a.id is null, 
                    string_ConcatSmart(' ', tl.uofmPre, tl.name, tl.uofmPost),
                    concat(
                        tl.name, ' = ',
                        tl.uofmPre, 
                        ' (', __search_FormatAttributeCriterion(a.id, sc.id, language_id_), ') ', 
                        tl.uofmPost))
                order by nt.sorter, t.sorter, tl.name
                separator ', '),
            1,
            255) into result
    from searchCriterionT sc
    join nodeT n on sc.nodeId = n.id
    join nodeTypeT nt on n.nodeTypeId = nt.id
    join tagT t on n.id = t.id
    join tagTL tl on t.id = tl.id and tl.languageId = language_id_
    left join attributeT a on t.id = a.id
    where sc.sessionId = session_id_;

    return result;
end$$$


-- saves current search session for the current logged in user
-- if name_ is null, it is automatically created from search criterions
create function search_Save(name_ varchar(255)) returns bigint unsigned
not deterministic modifies sql data
begin
    declare session_id bigint unsigned;
    declare name varchar(255);

    if search_CurrentId() is null or user_CurrentId() is null then
        return 0;
    end if;

    set session_id = __search_DuplicateSession(search_CurrentId(), null);

    set name = coalesce(name_, __search_CreateSessionName(session_id, user_CurrentLanguage()));

    insert searchSavedT(sessionId, userId, name)
    values (session_id, user_CurrentId(), name);

    return session_id;
end$$$


-- restores saved session into the current one, call search_Search after this function
create function search_RestoreSaved(session_id_ bigint unsigned) returns bigint unsigned
not deterministic modifies sql data
begin 
    declare current_session_id bigint unsigned default search_CurrentId();

    if (current_session_id is null) or not exists(select 1 from searchSavedT where sessionId = session_id_) then 
        return 0;
    else
        set @1 = __search_DuplicateSession(session_id_, current_session_id);

        return session_id_;
    end if;
end$$$


create function search_DeleteSaved(session_id_ bigint unsigned) returns int unsigned
not deterministic modifies sql data
begin
    if not exists (select 1 from searchSavedT where sessionId = session_id_ and userId = user_CurrentId()) then
        return 0;
    end if;

    -- deleting from searchSessionT deletes from searchSavedT as well
    delete from searchSessionT where id = session_id_;

    return 1;
end$$$


create function search_UpdateSaved(session_id_ bigint unsigned, name_ varchar(255)) returns bigint unsigned
not deterministic modifies sql data
begin
    update searchSavedT set
        name = coalesce(name_, name)
    where sessionId = session_id_ and userId = user_CurrentId();

    return if(row_count() > 0, session_id_, 0);
end$$$