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


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


create function search_Reset() returns int unsigned
not deterministic modifies sql data
begin
    delete from searchCriterionT where sessionId = search_CurrentId();
    delete from searchSessionT where 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
    insert searchSessionT(id, sid)
    values(uuid_short(), sid_)
    on duplicate key update searchSessionT.added = now();

    select id into @__searchId from searchSessionT where sid = sid_;

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

    return search_CurrentId();
end$$$


create function __search_SetTag(tag_id_ int unsigned, force_tag_ int) returns int unsigned
not deterministic modifies sql data
begin
    declare tag_id, node_type_id int unsigned;
    declare internal_type_id varchar(10) binary;
    declare force_tag int default coalesce(force_tag_, 0);

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

    insert ignore searchCriterionT(sessionId, nodeId, nodeTypeId, internalTypeId)
    select search_CurrentId(), tag_id, node_type_id, internal_type_id
    from dual
    where tag_id is not null;

    return tag_id;
end$$$


create function search_SetTag(tag_id_ int unsigned) returns int unsigned
not deterministic modifies sql data
begin
    set @1 = __search_SetTag(tag_id_, 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 attr_id int unsigned default __search_SetTag(attr_id_, null);

    insert searchCriterionNumericT(
        sessionId, 
        nodeId, 
        valueLow, 
        valueHigh)
    select 
        search_CurrentId(), 
        a.id, 
        __attribute_ClampValueNumeric(attr_id, value_low_), 
        __attribute_ClampValueNumeric(attr_id, value_high_)
    from attributeT a
    where a.id = attr_id
        and attr_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 attr_id int unsigned default __search_SetTag(attr_id_, null);

    insert searchCriterionDatetimeT(
        sessionId, 
        nodeId, 
        valueLow, 
        valueHigh)
    select 
        search_CurrentId(), 
        a.id, 
        __attribute_ClampValueDatetime(attr_id, value_low_), 
        __attribute_ClampValueDatetime(attr_id, value_high_)
    from attributeT a
    where a.id = attr_id
        and attr_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_RectifyCriteria(session_id_ bigint unsigned) returns int unsigned
not deterministic modifies sql data
begin
    drop temporary table if exists searchRectifyCriteriaDeletes;
    create temporary table searchRectifyCriteriaDeletes(
        nodeId int unsigned not null primary key
    ) engine=MEMORY;

--  remove tags whose parents are searchable but not included into the list
/*    insert ignore searchRectifyCriteriaDeletes(nodeId)
    select sc.nodeId
    from searchCriterionT sc
    where sc.sessionId = session_id_
        and exists (
            select 1 
            from nodeLinkOwnsV nlo1 
            join tagT t1 on nlo1.srcNodeId = t1.id
            where nlo1.dstNodeId = sc.nodeId
                and nlo1.metric = 1
                and t1.canSearch = 'y')
        and not exists (
            select 1 
            from nodeLinkOwnsV nlo2 
            join tagT t2 on nlo2.srcNodeId = t2.id
            join searchCriterionT absentc2 on t2.id = absentc2.nodeId
            where nlo2.dstNodeId = sc.nodeId
                and nlo2.metric = 1
                and t2.canSearch = 'y'
                and sc.sessionId = absentc2.sessionId);
*/
-- empty attribute criteria
    insert ignore searchRectifyCriteriaDeletes(nodeId)
    select scn.nodeId
    from searchCriterionNumericT scn
    where scn.sessionId = session_id_
        and scn.valueLow is null
        and scn.valueHigh is null;

    insert ignore searchRectifyCriteriaDeletes(nodeId)
    select scd.nodeId
    from searchCriterionDatetimeT scd
    where scd.sessionId = session_id_
        and scd.valueLow is null
        and scd.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_SetDefaultResult(session_id_ bigint unsigned) returns int unsigned
not deterministic modifies sql data
begin 
    insert searchResultT(sessionId, nodeId, collapsed)
    select session_id_, t.id, 'y'
    from tagV t
    join nodeRootLinkV nrl on t.id = nrl.dstNodeId and nrl.metric = 1
    where t.canSearch = 'y'
        and (nrl.typeRequiredForObject = 'y' or t.typeRequiredForObject = 'y')
        and nrl.displayNode = 'y'
        and not exists(
            select 1
            from nodeLinkChainTransitionV nl
            where nl.nodeLinkTypeId = nrl.nodeLinkTypeId
                and nl.srcNodeId = nrl.id
                and nl.dstNodeId = t.id)
    on duplicate key update collapsed = '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 session_id_ bigint unsigned default search_CurrentId();
    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
-- add searchable tags which don't have parents
    insert ignore searchResultT(sessionId, nodeId)
    select session_id_, 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 nodeT nonrootnode on nl.srcNodeId = nonrootnode.id
            join tagT nonroottag on nonrootnode.id = nonroottag.id
            where nl.nodeLinkTypeId = nlt.id
                and nl.dstNodeId = n.id
                and nl.metric = 1
                and nonrootnode.isRoot = 'n');

    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'
    where 1=1
-- if link exists at all
        and exists(
            select 1
            from nodeLinkT nl1
            join searchCriterionT sc1 on nl1.srcNodeId = sc1.nodeId
            where sc1.sessionId = session_id_
                and nl1.nodeLinkTypeId = nlt.id
                and nl1.dstNodeId = n.id
                and nl1.metric = 1)
-- if properly linked to all non "searchAnd" tags
        and (select count(distinct n2.nodeTypeId)
                from nodeLinkT nl2
                join tagT t2 on nl2.srcNodeId = t2.id
                join nodeT n2 on t2.id = n2.id
                where nl2.nodeLinkTypeId = nlt.id
                    and nl2.dstNodeId = n.id
                    and nl2.metric = 1
                    and t2.canSearch = 'y') 
            =
            (select count(distinct sc3.nodeTypeId)
                from nodeLinkT nl3
                join searchCriterionT sc3 on nl3.srcNodeId = sc3.nodeId
                where sc3.sessionId = session_id_
                    and sc3.nodeTypeId is not null
                    and nl3.nodeLinkTypeId = nlt.id
                    and nl3.dstNodeId = n.id
                    and nl3.metric = 1);
-- if properly linked to all "searchAnd" tags
/*        and (select count(*)
                from nodeLinkDirectV nl5
                join searchCriterionT sc5 on nl5.srcNodeId = sc5.nodeId
                where sc5.sessionId = session_id_
                    and sc5.nodeTypeId is null
                    and nl5.nodeLinkTypeId = nlt.id
                    and nl5.dstNodeId = n.id
                    and nl5.metric = 1)
            = count_null_;*/
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)
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 objectT o on n.id = o.id
    join objectStatusT os on o.statusId = os.id
    where os.public = 'y'
-- if link exists at all
--        and exists(
--            select 1
--            from nodeLinkT nl1
--            join searchCriterionT sc1 on nl1.srcNodeId = sc1.nodeId
--            where sc1.sessionId = session_id_
--                and nl1.nodeLinkTypeId = nlt.id
--                and nl1.dstNodeId = n.id)
-- if properly linked to all non "searchAnd" tags
        and count_notnull_ =
            (select count(distinct sc3.nodeTypeId)
                from nodeLinkT nl3
                join searchCriterionT sc3 on nl3.srcNodeId = sc3.nodeId
                where sc3.sessionId = session_id_
                    and sc3.nodeTypeId is not null
                    and sc3.internalTypeId = 'tag'
                    and nl3.nodeLinkTypeId = nlt.id
                    and nl3.dstNodeId = n.id
                    and nl3.metric = 1)
-- if properly linked to all "searchAnd" tags and the sum of all factors greater than some threshold
        and (sum_null_ - sum_null_tolerance_) <
            coalesce(
                (select sum(t5.factor)
                from nodeLinkT nl5
                join searchCriterionT sc5 on nl5.srcNodeId = sc5.nodeId
                join tagT t5 on sc5.nodeId = t5.id
                where sc5.sessionId = session_id_
                    and sc5.nodeTypeId is null
                    and sc5.internalTypeId = 'tag'
                    and nl5.nodeLinkTypeId = nlt.id
                    and nl5.dstNodeId = n.id
                    and nl5.metric = 1),
                0)
-- if properly linked to all numeric tags
        and count_numerics_ = 
            (select count(*)
                from nodeLinkT nl7
                join searchCriterionNumericT scn7 on nl7.srcNodeId = scn7.nodeId
                join attributeValueNumericT avn7 on scn7.nodeId = avn7.attributeId
                where scn7.sessionId = session_id_
                    and nl7.nodeLinkTypeId = nlt.id
                    and nl7.dstNodeId = n.id
                    and nl7.metric = 1
                    and avn7.objectId = n.id
                    and avn7.value >= coalesce(scn7.valueLow, avn7.value)
                    and avn7.value <= coalesce(scn7.valueHigh, avn7.value))
-- if properly linked to all datetime tags
        and count_datetimes_ = 
            (select count(*)
                from nodeLinkT nl9
                join searchCriterionDatetimeT scd9 on nl9.srcNodeId = scd9.nodeId
                join attributeValueDatetimeT avd9 on scd9.nodeId = avd9.attributeId
                where scd9.sessionId = session_id_
                    and nl9.nodeLinkTypeId = nlt.id
                    and nl9.dstNodeId = n.id
                    and nl9.metric = 1
                    and avd9.objectId = n.id
                    and avd9.value >= coalesce(scd9.valueLow, avd9.value)
                    and avd9.value <= coalesce(scd9.valueHigh, avd9.value));
end$$$


create procedure __search_UpdateExtraObjects(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
    ) engine=MEMORY;
    
    insert searchUpdateExtraObjectsData(tagId, cnt)
    select tagresult.nodeId, 0
    from searchResultT tagresult
    where tagresult.sessionId = session_id_;
    
-- all searchAnd tags
    insert searchUpdateExtraObjectsData(tagId, cnt)
    select tagresult.nodeId, 1
    from searchResultT tagresult
    join nodeLinkTypeT nlt on nlt.isDefault = 'y'
    join tagT t on tagresult.nodeId = t.id
    join nodeT n on t.id = n.id
    join nodeTypeT nt on n.nodeTypeId = nt.id
    where tagresult.sessionId = session_id_
        and nt.searchAnd = 'y'
        and exists (select 1 
                from nodeLinkT nl 
                join searchResultT objectresult on nl.dstNodeId = objectresult.nodeId
                join objectT o on objectresult.nodeId = o.id
                where nl.nodeLinkTypeId = nlt.id 
                    and nl.srcNodeId = t.id 
                    and nl.metric = 1
                    and objectresult.sessionId = tagresult.sessionId)
    on duplicate key update cnt = 1;

-- all non searchAnd tags, whose type is not selected
    insert searchUpdateExtraObjectsData(tagId, cnt)
    select tagresult.nodeId, 1
    from searchResultT tagresult
    join nodeLinkTypeT nlt on nlt.isDefault = 'y'
    join tagT t on tagresult.nodeId = t.id
    join nodeT n on t.id = n.id
    join nodeTypeT nt on n.nodeTypeId = nt.id
    where tagresult.sessionId = session_id_
        and nt.searchAnd = 'n'
        and exists (select 1 
                    from nodeLinkT nl 
                    join searchResultT objectresult on nl.dstNodeId = objectresult.nodeId
                    join objectT o on objectresult.nodeId = o.id
                    where objectresult.sessionId = tagresult.sessionId
                        and nl.nodeLinkTypeId = nlt.id 
                        and nl.srcNodeId = t.id 
                        and nl.metric = 1)
        and not exists (select 1
                    from searchCriterionT criteria
                    where criteria.sessionId = tagresult.sessionId
                        and criteria.nodeTypeId = nt.id)
    on duplicate key update cnt = 1;
    
-- all non searchAnd tags, whose type is selected, regardless if there are any objects
    insert searchUpdateExtraObjectsData(tagId, cnt)
    select tagresult.nodeId, 1
    from searchResultT tagresult
    join nodeLinkTypeT nlt on nlt.isDefault = 'y'
    join tagT t on tagresult.nodeId = t.id
    join nodeT n on t.id = n.id
    join nodeTypeT nt on n.nodeTypeId = nt.id
    where tagresult.sessionId = session_id_
        and nt.searchAnd = 'n'
        and exists (select 1
                    from searchCriterionT criteria
                    where criteria.sessionId = tagresult.sessionId
                        and criteria.nodeTypeId = nt.id)
    on duplicate key update cnt = 1;

    update searchResultT sr join searchUpdateExtraObjectsData sueod on sr.nodeId = sueod.tagId set
        sr.extraObjects = sueod.cnt
    where sr.sessionId = session_id_;

    drop temporary table searchUpdateExtraObjectsData;
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)
    ) engine=MEMORY;

    insert 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.id = os.attributeId
    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 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(*), coalesce(sum(t.factor), 0) 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.sessionId = scn.sessionId and sc.nodeId = scn.nodeId
    where sc.sessionId = session_id;

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

--    set search_objects = __search_CheckRequiredForObject(session_id);

    delete from searchResultT where sessionId = session_id;

    call __search_SearchTags(session_id, count_notnull, count_null);

    set @2 = __search_SetDefaultResult(session_id);
    
    set @3 = __search_RectifyCriteriaPost(session_id);
    
    -- CONST: sum_null_tolerance_ is 1
    call __search_SearchObjects(session_id, count_notnull, sum_null, 1, count_numerics, count_datetimes);
    
    call __search_UpdateExtraObjects(session_id);
    
    call __search_UpdateSortCache(session_id);
    
    commit;

end$$$