-- --------------------------------------------------------------------------------
-- _080_object Group Routines
-- --------------------------------------------------------------------------------
DELIMITER $$$

-- validators

create function __object_ValidateId(object_id_ int unsigned) returns int unsigned
deterministic
begin
    declare object_id int unsigned;

    select o.id into object_id
    from objectT o
    join nodeT n on o.id = n.id
    join nodeTypeT nt on n.nodeTypeId = nt.id
    where o.id = object_id_
        and nt.internalTypeId = 'object';

    if object_id is null then 
        call raiseError1('object', 'validate', object_id_, 'object does not exist');
    end if;

    return 1;
end$$$


create function __object_AccessObjectByUser(
    object_id_ int unsigned,
    action_ enum('update', 'delete', 'status'),
    new_status_id_ char(10) binary
) returns int unsigned
not deterministic 
begin
    declare editable, draftable enum('n', 'y');
    
    select os.userEditable, os.userDraftable
    into editable, draftable
    from objectStatusT os
    join objectT o on os.id = o.statusId
    where o.id = object_id_;

    return 
        case action_ 
            when 'update' then editable = 'y'
            when 'delete' then editable = 'y' or draftable = 'y'
            when 'status' then 
                case new_status_id_ 
                    when 'draft' then draftable = 'y'
                    when 'review' then editable = 'y'
                    else 0
                end
            else 0
        end;
end$$$


create function __object_AccessByLoggedUser(object_id_ int unsigned) returns int unsigned
not deterministic 
return exists (
            select 1 
            from nodeLinkT nl 
            join nodeLinkTypeT nlt on nl.nodeLinkTypeId = nlt.id 
            where nlt.code = 'owns'
                and nl.srcNodeId = user_CurrentId()
                and nl.dstNodeId = object_id_)$$$


create function __object_AccessByUser(
    object_id_ int unsigned,
    action_ enum('update', 'delete', 'status'),
    new_status_id_ char(10) binary
) returns int unsigned
not deterministic 
begin
    if not (
            __object_ValidateId(object_id_)
            and __object_AccessObjectByUser(object_id_, action_, new_status_id_)
            and __object_AccessByLoggedUser(object_id_))
    then
        call raiseError1('object', action_, object_id_, concat('cannot perform "', action_, '" on object'));
    end if;
    
    return 1;
end$$$


-- sort cache maintenance

create function __object_UpdateSortCache(
    object_id_ int unsigned,
    attribute_id_ int unsigned,
    value_ double
) returns int unsigned
not deterministic modifies sql data
comment 'using not documented trick of "order by -field desc" to put nulls last'
begin
    declare sortable int unsigned default exists (select id from sortOptionT so where so.attributeId = attribute_id_);
    
    if object_id_ is null then
        if sortable then
            insert cacheObjectSortT(objectId, sortOptionId, sorter)
            select objectn.id, so.id, - coalesce(value_, so.sortDirection * avn.value)
            from sortOptionT so
            join attributeValueNumericT avn on so.attributeId = avn.attributeId
            join objectT objectn on avn.objectId = objectn.id
            where so.attributeId = attribute_id_
            on duplicate key update 
                cacheObjectSortT.sorter = values(sorter);
            
            insert cacheObjectSortT(objectId, sortOptionId, sorter)
            select objectd.id, so.id, - coalesce(value_, so.sortDirection * datetime_ToDouble(avd.value))
            from sortOptionT so
            join attributeValueDatetimeT avd on so.attributeId = avd.attributeId
            join objectT objectd on avd.objectId = objectd.id
            where so.attributeId = attribute_id_
            on duplicate key update 
                cacheObjectSortT.sorter = values(sorter);

            insert cacheObjectSortT(objectId, sortOptionId, sorter)
            select objectp.id, so.id, - coalesce(value_, so.sortDirection * avp.value)
            from sortOptionT so
            join attributeValuePriceT avp on so.attributeId = avp.attributeId
            join objectT objectp on avp.objectId = objectp.id
            join currencyT c on avp.currencyId = c.id
            where so.attributeId = attribute_id_
                and c.isDefault = 'y'
            on duplicate key update 
                cacheObjectSortT.sorter = values(sorter);
        else
            delete from cacheObjectSortT 
            using cacheObjectSortT
            join sortOptionT so on cacheObjectSortT.sortOptionId = so.id
            where so.attributeId = attribute_id_;
        end if;
    else
        if sortable then 
            insert cacheObjectSortT(objectId, sortOptionId, sorter)
            select 
                o.id, 
                so.id, 
                - coalesce(value_, so.sortDirection * avn.value, so.sortDirection * datetime_ToDouble(avd.value), so.sortDirection * avp.value, 0.0)
            from sortOptionT so
            join objectT o on 1=1
            left join attributeValueNumericT avn on so.attributeId = avn.attributeId and o.id = avn.objectId
            left join attributeValueDatetimeT avd on so.attributeId = avd.attributeId and o.id = avd.objectId
            left join currencyT c on c.isDefault = 'y'
            left join attributeValuePriceT avp on so.attributeId = avp.attributeId and o.id = avp.objectId and avp.currencyId = c.id
            where so.attributeId = attribute_id_
                and o.id = object_id_
            on duplicate key update 
                cacheObjectSortT.sorter = values(sorter);
        else
            delete from cacheObjectSortT 
            using cacheObjectSortT
            join sortOptionT so on cacheObjectSortT.sortOptionId = so.id
            where cacheObjectSortT.objectId = object_id_
                and so.attributeId = attribute_id_;
        end if;
    end if;
    
    return object_id_;
end$$$


create function __object_UpdateSortCache2(
    object_id_ int unsigned,
    attribute_id_ int unsigned,
    value_ double
) returns double
not deterministic modifies sql data
comment 'calls __object_UpdateSortCache and returns value_'
begin
    set @1 = __object_UpdateSortCache(object_id_, attribute_id_, value_);
    
    return value_;
end$$$


create function __object_UpdateSortCacheRating2(
    object_id_ int unsigned,
    value_ double
) returns double
not deterministic modifies sql data
begin
    -- NOTE: descending sort by default - note minus sign
    return - __object_UpdateSortCache2(
                                    object_id_, 
                                    attribute_GetByCode('__byrating'), 
                                    - coalesce(
                                            value_, 
                                            (select o.rating from objectT o where o.id = object_id_)));
end$$$


create function __object_UpdateSortCacheChanged2(
    object_id_ int unsigned,
    changed_ timestamp
) returns double
not deterministic modifies sql data
begin
    -- NOTE: descending sort by default - note minus sign
    return - __object_UpdateSortCache2(
                                    object_id_, 
                                    attribute_GetByCode('__bychanged'), 
                                    - datetime_ToDouble(
                                                coalesce(
                                                        changed_, 
                                                        (select n.changed from nodeT n where n.id = object_id_))));
end$$$


create function __object_UpdateSortCacheAll() returns int unsigned
not deterministic modifies sql data
comment 'updates all sort caches; to be called manually'
begin
    -- special attribute - rating; update all ratings as a side effect
    set @1 = __object_UpdateRatingsAll(null);
    
    -- special attribute - last changed date
    insert zzzDevNullObjectT(n)
    select coalesce(__object_UpdateSortCacheChanged2(o.id, null), 0)
    from objectT o;
    
    -- all other attributes
    -- this query will select special attributes as well, 
    -- but won't update anything because special attributes have no attributeValueNumericT and attributeValueDatetimeT
    insert zzzDevNullObjectT(n)
    select coalesce(__object_UpdateSortCache(null, a.id, null), 0)
    from attributeT a
    join sortOptionT so on so.id = (select min(eso.id) from sortOptionT eso where eso.attributeId = a.id);
    
    return 1;
end$$$


-- rating maintenance

create function __object_ClampRating(rating_ double) returns double
deterministic
-- CONST: object rating min/max limits
return greatest(0.0, least(1.0, rating_))$$$


create function __object_UpdateRating(
    object_id_ int unsigned, 
    selected_ int unsigned, 
    total_ int unsigned
) returns int unsigned
not deterministic modifies sql data
begin
    -- TODO: add ratings for paid objects
    update objectT o set
        o.rating = __object_UpdateSortCacheRating2(
                                            o.id, 
                                            coalesce(__object_ClampRating(selected_ / greatest(total_, 1)), 0))
    where o.id = object_id_;

    return object_id_;
end$$$


create function __object_UpdateRatings() returns int unsigned
not deterministic modifies sql data
begin
    drop temporary table if exists objectUpdateRatingsData;
    create temporary table objectUpdateRatingsData(
        objectId int unsigned not null,
        tagId int unsigned not null,
        linked int unsigned null,
        primary key(objectId, tagId)
    );
    
    insert objectUpdateRatingsData(objectId, tagId, linked)
    select 
        o.id,
        srctag.id,
        null
    from objectT o
    join __nodeQueueT nq on o.id = nq.nodeId
    join nodeRecommendLinkV nrl on o.id = nrl.dstNodeId -- nodeRecommendLinkV may have duplicate records
    join tagT srctag on nrl.srcNodeId = srctag.id
    join nodeT srcnode on srctag.id = srcnode.id
    join nodeTypeT nodetype on srcnode.nodeTypeId = nodetype.id
    where nodetype.canHaveRatingPoints = 'y'
        and nq.flag = ' '
    group by o.id, srctag.id;

    insert objectUpdateRatingsData(objectId, tagId, linked)
    select 
        o.id,
        srctag.id,
        nl.metric
    from objectT o
    join __nodeQueueT nq on o.id = nq.nodeId
    join nodeLinkTypeT nlt on nlt.isDefault = 'y'
    join nodeLinkT nl on nlt.id = nl.nodeLinkTypeId and o.id = nl.dstNodeId and nl.metric = 1
    join tagT srctag on nl.srcNodeId = srctag.id
    join nodeT srcnode on srctag.id = srcnode.id
    join nodeTypeT nodetype on srcnode.nodeTypeId = nodetype.id
    where nodetype.canHaveRatingPoints = 'y'
        and nq.flag = ' '
    group by o.id, srctag.id
    on duplicate key update
        objectUpdateRatingsData.linked = 1;

    insert zzzDevNullObjectT(n)
    select __object_UpdateRating(ourd.objectId, sum(if(ourd.linked = 1, t.ratingPoints, 0)), sum(t.ratingPoints))
    from objectUpdateRatingsData ourd
    join tagT t on ourd.tagId = t.id
    group by ourd.objectId;

    drop temporary table objectUpdateRatingsData;

    return 1;
end$$$


create function __object_UpdateRatingsAll(object_id int unsigned) returns int unsigned
not deterministic modifies sql data
comment 'updates all object ratings; to be called manually'
begin
    drop temporary table if exists objectUpdateAllRatingsData;
    create temporary table objectUpdateAllRatingsData(
        objectId int unsigned not null,
        tagId int unsigned not null,
        linked int unsigned null,
        primary key(objectId, tagId)
    );
    
    if object_id is null then
        insert objectUpdateAllRatingsData(objectId, tagId, linked)
        select 
            o.id,
            srctag.id,
            null
        from objectT o
        join nodeRecommendLinkV nrl on o.id = nrl.dstNodeId -- nodeRecommendLinkV may have duplicate records
        join tagT srctag on nrl.srcNodeId = srctag.id
        join nodeT srcnode on srctag.id = srcnode.id
        join nodeTypeT nodetype on srcnode.nodeTypeId = nodetype.id
        where nodetype.canHaveRatingPoints = 'y'
        group by o.id, srctag.id;
        
        insert objectUpdateAllRatingsData(objectId, tagId, linked)
        select 
            o.id,
            srctag.id,
            nl.metric
        from objectT o
        join nodeLinkTypeT nlt on nlt.isDefault = 'y'
        join nodeLinkT nl on nlt.id = nl.nodeLinkTypeId and o.id = nl.dstNodeId and nl.metric = 1
        join tagT srctag on nl.srcNodeId = srctag.id
        join nodeT srcnode on srctag.id = srcnode.id
        join nodeTypeT nodetype on srcnode.nodeTypeId = nodetype.id
        where nodetype.canHaveRatingPoints = 'y'
        group by o.id, srctag.id
        on duplicate key update
            objectUpdateAllRatingsData.linked = 1;
    else
        insert objectUpdateAllRatingsData(objectId, tagId, linked)
        select 
            o.id,
            srctag.id,
            null
        from objectT o
        join nodeRecommendLinkV nrl on o.id = nrl.dstNodeId -- nodeRecommendLinkV may have duplicate records
        join tagT srctag on nrl.srcNodeId = srctag.id
        join nodeT srcnode on srctag.id = srcnode.id
        join nodeTypeT nodetype on srcnode.nodeTypeId = nodetype.id
        where o.id = object_id
            and nodetype.canHaveRatingPoints = 'y'
        group by o.id, srctag.id;
        
        insert objectUpdateAllRatingsData(objectId, tagId, linked)
        select 
            o.id,
            srctag.id,
            nl.metric
        from objectT o
        join nodeLinkTypeT nlt on nlt.isDefault = 'y'
        join nodeLinkT nl on nlt.id = nl.nodeLinkTypeId and o.id = nl.dstNodeId and nl.metric = 1
        join tagT srctag on nl.srcNodeId = srctag.id
        join nodeT srcnode on srctag.id = srcnode.id
        join nodeTypeT nodetype on srcnode.nodeTypeId = nodetype.id
        where o.id = object_id
            and nodetype.canHaveRatingPoints = 'y'
        group by o.id, srctag.id
        on duplicate key update
            objectUpdateAllRatingsData.linked = 1;
    end if;

    insert zzzDevNullObjectT(n)
    select __object_UpdateRating(ourd.objectId, sum(if(ourd.linked = 1, t.ratingPoints, 0)), sum(t.ratingPoints))
    from objectUpdateAllRatingsData ourd
    join tagT t on ourd.tagId = t.id
    group by ourd.objectId;

    drop temporary table objectUpdateAllRatingsData;

    return coalesce(object_id, 1);
end$$$


-- address and alias maintenance

create function __object_UpdateAddresses() returns int unsigned
not deterministic modifies sql data
comment 'updates both address and alias'
begin
    drop temporary table if exists objectUpdateAddressesData;
    create temporary table objectUpdateAddressesData(
        objectId int unsigned not null primary key,
        address text null,
        alias text null
    );
    
    -- CONST: address field separator
    insert objectUpdateAddressesData(objectId, address)
    select 
        o.id,
        group_concat(
            string_ConcatSmart(
                ' ',
                nad.srcUofmPre,
                if(
                    nad.srcInternalTypeId = 'attribute',
                    coalesce(
                        nad.srcNumericValue,
                        date_format(nad.srcDatetimeValue, nad.srcDatetimeOutputFormat), 
                        nad.srcTextValue, 
                        ''), 
                    nad.srcName),
                nad.srcUofmPost)
            order by nad.ancestrySorter, nad.srcTypeName, nad.srcName
            separator ', ') addr
    from objectT o
    join __nodeQueueT nq on o.id = nq.nodeId
    left join nodeAncestryDataV nad on o.id = nad.dstNodeId and nad.languageIsDefault = 'y'
    where nq.flag = ' '
    group by o.id;

    -- CONST: address field separator and language id
    insert objectUpdateAddressesData(objectId, alias)
    select 
        o.id,
        concat_ws(
            '-', 
            o.id,
            group_concat(
                string_ConcatSmart(
                    ' ',
                    nad.srcUofmPre,
                    if(
                        nad.srcInternalTypeId = 'attribute',
                        coalesce(
                            nad.srcNumericValue,
                            date_format(nad.srcDatetimeValue, nad.srcDatetimeOutputFormat), 
                            nad.srcTextValue, 
                            ''), 
                        nad.srcName),
                    nad.srcUofmPost)
                order by nad.ancestrySorter, nad.srcTypeName, nad.srcName
                separator '-')) alias_
    from objectT o
    join __nodeQueueT nq on o.id = nq.nodeId
    left join nodeAncestryDataV nad on o.id = nad.dstNodeId and nad.languageId = 'eng'
    where nq.flag = ' '
    group by o.id
    on duplicate key update 
        objectUpdateAddressesData.alias = values(alias);
    
    update objectT o join objectUpdateAddressesData ouad on o.id = ouad.objectId set
        o.address = coalesce(ouad.address, ''),
        o.alias = string_Alphanumeric(coalesce(ouad.alias, ''));

    drop temporary table objectUpdateAddressesData;

    return 1;
end$$$


create function __object_UpdateAddressesAll(object_id int unsigned) returns int unsigned
not deterministic modifies sql data
comment 'updates either specific object address and alias or all object addresses and aliases'
begin
    drop temporary table if exists objectUpdateAllAddressesData;
    create temporary table objectUpdateAllAddressesData(
        objectId int unsigned not null primary key,
        address text null,
        alias text null
    );
    
    if object_id is null then
        -- CONST: address field separator
        insert objectUpdateAllAddressesData(objectId, address)
        select 
            o.id,
            group_concat(
                string_ConcatSmart(
                    ' ',
                    nad.srcUofmPre,
                    if(
                        nad.srcInternalTypeId = 'attribute',
                        coalesce(
                            nad.srcNumericValue,
                            date_format(nad.srcDatetimeValue, nad.srcDatetimeOutputFormat), 
                            nad.srcTextValue, 
                            ''), 
                        nad.srcName),
                    nad.srcUofmPost)
                order by nad.ancestrySorter, nad.srcTypeName, nad.srcName
                separator ', ') addr
        from objectT o 
        left join nodeAncestryDataV nad on o.id = nad.dstNodeId and nad.languageIsDefault = 'y'
        group by o.id;
        
        -- CONST: address field separator and language id
        insert objectUpdateAllAddressesData(objectId, alias)
        select 
            o.id,
            concat_ws(
                '-', 
                o.id,
                group_concat(
                    string_ConcatSmart(
                        ' ',
                        nad.srcUofmPre,
                        if(
                            nad.srcInternalTypeId = 'attribute',
                            coalesce(
                                nad.srcNumericValue,
                                date_format(nad.srcDatetimeValue, nad.srcDatetimeOutputFormat), 
                                nad.srcTextValue, 
                                ''), 
                            nad.srcName),
                        nad.srcUofmPost)
                    order by nad.ancestrySorter, nad.srcTypeName, nad.srcName
                    separator '-')) alias_
        from objectT o
        left join nodeAncestryDataV nad on o.id = nad.dstNodeId and nad.languageId = 'eng'
        group by o.id
        on duplicate key update 
            objectUpdateAllAddressesData.alias = values(alias);
    else
        -- CONST: address field separator
        insert objectUpdateAllAddressesData(objectId, address)
        select 
            o.id,
            group_concat(
                string_ConcatSmart(
                    ' ',
                    nad.srcUofmPre,
                    if(
                        nad.srcInternalTypeId = 'attribute',
                        coalesce(
                            nad.srcNumericValue,
                            date_format(nad.srcDatetimeValue, nad.srcDatetimeOutputFormat), 
                            nad.srcTextValue, 
                            ''), 
                        nad.srcName),
                    nad.srcUofmPost)
                order by nad.ancestrySorter, nad.srcTypeName, nad.srcName
                separator ', ') addr
        from objectT o 
        left join nodeAncestryDataV nad on o.id = nad.dstNodeId and nad.languageIsDefault = 'y'
        where o.id = object_id
        group by o.id;
        
        -- CONST: address field separator and language id
        insert objectUpdateAllAddressesData(objectId, alias)
        select 
            o.id,
            concat_ws(
                '-', 
                o.id,
                group_concat(
                    string_ConcatSmart(
                        ' ',
                        nad.srcUofmPre,
                        if(
                            nad.srcInternalTypeId = 'attribute',
                            coalesce(
                                nad.srcNumericValue,
                                date_format(nad.srcDatetimeValue, nad.srcDatetimeOutputFormat), 
                                nad.srcTextValue, 
                                ''), 
                            nad.srcName),
                        nad.srcUofmPost)
                    order by nad.ancestrySorter, nad.srcTypeName, nad.srcName
                    separator '-')) alias_
        from objectT o
        left join nodeAncestryDataV nad on o.id = nad.dstNodeId and nad.languageId = 'eng'
        where o.id = object_id
        group by o.id
        on duplicate key update 
            objectUpdateAllAddressesData.alias = values(alias);
    end if;

    update objectT o join objectUpdateAllAddressesData ouaad on o.id = ouaad.objectId set
        o.address = coalesce(ouaad.address, ''),
        o.alias = string_Alphanumeric(coalesce(ouaad.alias, ''));

    drop temporary table objectUpdateAllAddressesData;

    return coalesce(object_id, 1);
end$$$


-- main data manipulation

create function object_Create(
    address_ text,
    description_ text,
    status_id_ char(10) binary,
    latitude_ decimal(12, 7),
    longitude_ decimal(12, 7),
    rating_ double,
    note_for_moderator_ text,
    note_for_user_ text
) returns int unsigned
not deterministic modifies sql data
begin
    declare object_id int unsigned;
    declare new_address text;
    declare rating__ double default __object_ClampRating(rating_);

    set new_address = coalesce(address_, '');
    set object_id = node_LinkDefaults(__node_Create(null, 'object', substring(new_address, 1, 50)));

    insert objectT(
        id, 
        address, 
        description, 
        statusId,
        rating,
        noteForModerator, 
        noteForUser) 
    select
        object_id,
        new_address,
        description_,
        coalesce(status_id_, os.id),
        rating__,
        note_for_moderator_,
        note_for_user_
    from objectStatusT os
    where os.isDefault = 'y';
    
    set @1 = __object_UpdateSortCacheRating2(object_id, rating__);
    
    set @2 = __object_UpdateSortCacheChanged2(object_id, null);
    
    set @3 = __coord_Update(object_id, latitude_, longitude_);
    
    return object_id;
end$$$


create function object_CreateByUser(
    address_ text,
    description_ text,
    status_id_ char(10) binary,
    latitude_ decimal(12, 7),
    longitude_ decimal(12, 7),
    note_for_moderator_ text,
    note_for_user_ text
) returns int unsigned
not deterministic modifies sql data
begin
    return nodeLink_Create2(
            'owns', 
            user_CurrentId(), 
            object_Create(
                address_,
                description_,
                status_id_,
                latitude_,
                longitude_,
                0,
                note_for_moderator_,
                note_for_user_));
end$$$


create function __object_Delete(object_id_ int unsigned) returns int unsigned
not deterministic modifies sql data
begin
    return __node_Delete(object_id_);
end$$$


create function object_Delete(object_id_ int unsigned) returns int unsigned
not deterministic modifies sql data
begin
    set @1 = __object_ValidateId(object_id_);
    return __object_Delete(object_id_);
end$$$


create function object_DeleteByUser(object_id_ int unsigned) returns int unsigned
not deterministic modifies sql data
begin
    set @1 = __object_AccessByUser(object_id_, 'delete', null);
    return __object_Delete(object_id_);
end$$$


create function __object_Update(
    object_id_ int unsigned,
    address_ text,
    description_ text,
    status_id_ char(10) binary,
    latitude_ decimal(12, 7), reset_latitude_ enum('n', 'y'),
    longitude_ decimal(12, 7), reset_longitude_ enum('n', 'y'),
    rating_ double,
    note_for_moderator_ text, reset_note_for_moderator_ enum('n', 'y'),
    note_for_user_ text, reset_note_for_user_ enum('n', 'y'),
    alias_ text
) returns int unsigned
not deterministic modifies sql data
begin
    set @1 = __node_Update(object_id_, left(address_, 50));
    
    update objectT set
        address = coalesce(address_, address),
        description = coalesce(description_, description),
        statusId = coalesce(status_id_, statusId),
        rating = __object_ClampRating(coalesce(rating_, rating)),
        noteForModerator = if(reset_note_for_moderator_ = 'y', null, coalesce(note_for_moderator_, noteForModerator)),
        noteForUser = if(reset_note_for_user_ = 'y', null, coalesce(note_for_user_, noteForUser)),
        alias = if(alias_ = '', null, coalesce(alias_, alias))
    where id = object_id_;
    
    set @2 = rating_ is not null and __object_UpdateSortCacheRating2(object_id_, null); -- assume we already have rating set
    
    if reset_latitude_ = 'y' and reset_longitude_ = 'y' then
        set @3 = __coord_Delete(object_id_);
    else
        set @4 = __coord_Update(object_id_, latitude_, longitude_);
    end if;

    return object_id_;
end$$$


create function object_Update2(
    object_id_ int unsigned,
    address_ text,
    description_ text,
    status_id_ char(10) binary,
    latitude_ decimal(12, 7), reset_latitude_ enum('n', 'y'),
    longitude_ decimal(12, 7), reset_longitude_ enum('n', 'y'),
    rating_ double,
    note_for_moderator_ text, reset_note_for_moderator_ enum('n', 'y'),
    note_for_user_ text, reset_note_for_user_ enum('n', 'y'),
    date_published_ datetime, reset_date_published_ enum('n', 'y'),
    date_expiry_ datetime, reset_date_expiry_ enum('n', 'y')
) returns int unsigned
not deterministic modifies sql data
begin
    declare result int unsigned;
    
    set @1 = __object_ValidateId(object_id_);
    set result = __object_Update(object_id_, address_, description_, status_id_, 
                            latitude_, reset_latitude_, 
                            longitude_, reset_longitude_,
                            rating_,
                            note_for_moderator_, reset_note_for_moderator_,
                            note_for_user_, reset_note_for_user_,
                            null);
    set @2 = __object_UpdateSortCacheChanged2(object_id_, null);
    
    return result;
end$$$


create function object_Update(
    object_id_ int unsigned,
    address_ text,
    description_ text,
    status_id_ char(10) binary,
    latitude_ decimal(12, 7), reset_latitude_ enum('n', 'y'),
    longitude_ decimal(12, 7), reset_longitude_ enum('n', 'y'),
    rating_ double,
    note_for_moderator_ text, reset_note_for_moderator_ enum('n', 'y'),
    note_for_user_ text, reset_note_for_user_ enum('n', 'y')
) returns int unsigned
not deterministic modifies sql data
return __object_Update(
    object_id_,
    address_,
    description_,
    status_id_,
    latitude_, reset_latitude_,
    longitude_, reset_longitude_,
    rating_,
    note_for_moderator_, reset_note_for_moderator_,
    note_for_user_, reset_note_for_user_,
    null)$$$


create function object_UpdateByUser(
    object_id_ int unsigned,
    address_ text,
    description_ text,
    latitude_ decimal(12, 7), reset_latitude_ enum('n', 'y'),
    longitude_ decimal(12, 7), reset_longitude_ enum('n', 'y'),
    note_for_user_ text, reset_note_for_user_ enum('n', 'y')
) returns int unsigned
not deterministic modifies sql data
begin
    declare result int unsigned;

    set @1 = __object_AccessByUser(object_id_, 'update', null);
    set result = __object_Update(object_id_, address_, description_, null, 
                            latitude_, reset_latitude_,
                            longitude_, reset_longitude_,
                            null,
                            null, null,
                            note_for_user_, reset_note_for_user_,
                            null);
    set @2 = __object_UpdateSortCacheChanged2(object_id_, null);
    
    return result;
end$$$


create function __object_SetDate(
    object_id_ int unsigned, 
    date_published_ datetime, 
    date_expiry_ datetime
) returns int unsigned
not deterministic modifies sql data
begin
    update objectDateT set
        isActive = null
    where objectId = object_id_
        and isActive = 'y';

    if date_published_ is not null and date_expiry_ is not null then
        insert objectDateT(objectId, isActive, datePublished, dateExpiry)
        values (object_id_, 'y', date_published_, date_expiry_);
    end if;

    return object_id_;
end$$$


create function object_SetDate(
    object_id_ int unsigned, 
    date_published_ datetime, 
    date_expiry_ datetime
) returns int unsigned
not deterministic modifies sql data
return __object_SetDate(object_id_, date_published_, date_expiry_)$$$


create function object_SetStatusByUser(
    object_id_ int unsigned,
    status_id_ char(10) binary
) returns int unsigned
not deterministic modifies sql data
begin
    declare result int unsigned;

    set @1 = __object_AccessByUser(object_id_, 'status', status_id_);
    set result = __object_Update(object_id_, null, null, status_id_, 
                            null, null,
                            null, null,
                            null,
                            null, null,
                            null, null,
                            null);
    set @2 = __object_UpdateSortCacheChanged2(object_id_, null);
    
    return result;
end$$$


create function object_SetStatusForExpired(date_expiry_ datetime, status_id_ char(10) binary) returns int unsigned
not deterministic modifies sql data
begin
    declare date_expiry datetime default coalesce(date_expiry_, now());

    drop temporary table if exists setstatusforexpireddata;
    create temporary table setstatusforexpireddata(objectId int unsigned not null primary key)
    select o.id
    from objectV o
    where o.public = 'y'
        and o.dateExpiry <= date_expiry;

    insert zzzDevNullObjectT(n)
    select 
        __object_Update(
            ssed.objectId,
            null,
            null,
            status_id_,
            null, null,
            null, null,
            null,
            null, null,
            null, null,
            null)
    from setstatusforexpireddata ssed;

    insert zzzDevNullObjectT(n)
    select 
        __object_SetDate(ssed.objectId, null, null)
    from setstatusforexpireddata ssed;

    drop temporary table setstatusforexpireddata;

    return 1;
end$$$


-- object specific image code

create function image_CreateByUserForObject(
    node_id_ int unsigned,
    name_ varchar(255),
    filename_original_ varchar(255),
    filename_fs_ varchar(255),
    display_ enum('n', 'y'),
    sorter_ int
) returns int unsigned
not deterministic modifies sql data
begin
    set @1 = __object_AccessByUser(node_id_, 'update', null);
    return __image_Create(node_id_, name_, filename_original_, filename_fs_, display_, sorter_, null, null, null, null);
end$$$


create function image_CreateByUserForObjectEx(
    node_id_ int unsigned,
    name_ varchar(255),
    filename_original_ varchar(255),
    filename_fs_ varchar(255),
    display_ enum('n', 'y'),
    sorter_ int,
    image_type_id_ char(10) binary,
    video_type_ varchar(50),
    url_ varchar(255),
    extra_id_ varchar(255),
    extra_data_ varchar(255)
) returns int unsigned
not deterministic modifies sql data
begin
    set @1 = __object_AccessByUser(node_id_, 'update', null);
    return __image_Create(node_id_, name_, filename_original_, filename_fs_, display_, sorter_, image_type_id_, video_type_, url_, extra_id_, extra_data_);
end$$$


create function image_DeleteByUserForObject(image_id_ int unsigned) returns int unsigned
not deterministic modifies sql data
begin
    declare node_id int unsigned default (select nodeId from imageT where id = image_id_);
    
    set @1 = __object_AccessByUser(node_id, 'update', null);
    return __image_Delete(image_id_, node_id);
end$$$


create function image_UpdateByUserForObject(
    image_id_ int unsigned,
    name_ varchar(255),
    filename_original_ varchar(255),
    filename_fs_ varchar(255),
    display_ enum('n', 'y'),
    sorter_ int
) returns int unsigned
not deterministic modifies sql data
begin
    declare node_id int unsigned default (select nodeId from imageT where id = image_id_);
    
    set @1 = __object_AccessByUser(node_id, 'update', null);
    return __image_Update(image_id_, node_id, name_, filename_original_, filename_fs_, display_, sorter_, null, null, null, null);
end$$$


create function image_UpdateByUserForObjectEx(
    image_id_ int unsigned,
    name_ varchar(255),
    filename_original_ varchar(255),
    filename_fs_ varchar(255),
    display_ enum('n', 'y'),
    sorter_ int,
    image_type_id_ char(10) binary,
    video_type_ varchar(50),
    url_ varchar(255),
    extra_id_ varchar(255),
    extra_data_ varchar(255)
) returns int unsigned
not deterministic modifies sql data
begin
    declare node_id int unsigned default (select nodeId from imageT where id = image_id_);
    
    set @1 = __object_AccessByUser(node_id, 'update', null);
    return __image_Update(image_id_, node_id, name_, filename_original_, filename_fs_, display_, sorter_, image_type_id_, video_type_, url_, extra_id_, extra_data_);
end$$$


create function image_UpdateTypeByUserForObject(
    image_id_ int unsigned,
    image_type_id_ char(10) binary,
    video_type_ varchar(50),
    url_ varchar(255),
    extra_id_ varchar(255),
    extra_data_ varchar(255)
) returns int unsigned
not deterministic modifies sql data
begin
    declare node_id int unsigned default (select nodeId from imageT where id = image_id_);
    
    set @1 = __object_AccessByUser(node_id, 'update', null);
    return __image_Update(image_id_, node_id, null, null, null, null, null, image_type_id_, video_type_, url_, extra_id_, extra_data_);
end$$$


-- object selection functions

create function object_Select(object_id_ int unsigned) returns int unsigned
not deterministic modifies sql data
begin
    declare action_count int default 0;

    if search_CurrentId() is not null then 
        insert searchResultSelectedT(sessionId, objectId, added)
        values (search_CurrentId(), object_id_, now())
        on duplicate key update
            searchResultSelectedT.added = now();

        set action_count = action_count + 1;
    end if;
    
    if user_CurrentId() is not null then
        set @1 = nodeLink_Create('fav', user_CurrentId(), object_id_);

        update nodeLinkT nl join nodeLinkTypeT nlt on nl.nodeLinkTypeId = nlt.id set
            nl.added = now()
        where nlt.code = 'fav'
            and nl.srcNodeId = user_CurrentId()
            and nl.dstNodeId = object_id_;

        set action_count = action_count + 1;
    end if;

    if action_count > 0 then 
        set @2 = viewLog_Create(object_id_, null);
    end if;
    
    return object_id_;
end$$$


create function object_Deselect(object_id_ int unsigned) returns int unsigned
not deterministic modifies sql data
begin
    declare user_id int unsigned default user_CurrentId();
    
    if search_CurrentId() is not null then 
        if object_id_ is null then
            delete from searchResultSelectedT 
            where sessionId = search_CurrentId();
        else
            delete from searchResultSelectedT 
            where sessionId = search_CurrentId() 
                and objectId = object_id_;
        end if;
    end if;
    
    if user_id is not null then
        drop temporary table if exists objectDeselectData;
        create temporary table objectDeselectData(objectId int unsigned not null primary key);
        
        if object_id_ is null then
            insert objectDeselectData(objectId)
            select nl.dstNodeId
            from nodeLinkT nl
            join nodeLinkTypeT nlt on nl.nodeLinkTypeId = nlt.id
            where nl.srcNodeId = user_id
                and nl.metric = 1
                and nlt.code = 'fav';
        else
            insert objectDeselectData(objectId)
            select nl.dstNodeId
            from nodeLinkT nl
            join nodeLinkTypeT nlt on nl.nodeLinkTypeId = nlt.id
            where nl.srcNodeId = user_id
                and nl.dstNodeId = object_id_
                and nl.metric = 1
                and nlt.code = 'fav';
        end if;
            
        set @1 = nodeLink_UpdateStart();
        
        insert zzzDevNullObjectT(n)
        select __nodeLink_Delete('fav', user_id, oddata.objectId, null)
        from objectDeselectData oddata;

        set @2 = nodeLink_UpdateEnd();

        drop temporary table objectDeselectData;
    end if;
    
    return object_id_;
end$$$


create function object_SelectSync() returns int unsigned
not deterministic modifies sql data
comment 'should be run when user logs in'
begin
    declare user_id int unsigned default user_CurrentId();
    declare session_id bigint unsigned default search_CurrentId();
    
    if user_id is not null and session_id is not null then
        -- session list -> profile list
        drop temporary table if exists objectSelectSyncData;
        create temporary table objectSelectSyncData(objectId int unsigned not null primary key);
        
        insert objectSelectSyncData(objectId)
        select srs.objectId
        from searchResultSelectedT srs
        where srs.sessionId = session_id
            and not exists(
                        select 1
                        from nodeLinkT nl
                        join nodeLinkTypeT nlt on nl.nodeLinkTypeId = nlt.id
                        where nl.srcNodeId = user_id
                            and nl.dstNodeId = srs.objectId
                            and nl.metric = 1
                            and nlt.code = 'fav');
        
        set @1 = nodeLink_UpdateStart();
        
        insert zzzDevNullObjectT(n)
        select __nodeLink_Create('fav', user_id, ossd.objectId)
        from objectSelectSyncData ossd;
        
        set @2 = nodeLink_UpdateEnd();
        
        drop temporary table objectSelectSyncData;
        
        -- profile list -> session list
        insert searchResultSelectedT(sessionId, objectId, added)
        select session_id, nl.dstNodeId, nl.added
        from nodeLinkT nl
        join nodeLinkTypeT nlt on nl.nodeLinkTypeId = nlt.id
        where nl.srcNodeId = user_id
            and nl.metric = 1
            and nlt.code = 'fav'
        on duplicate key update
            searchResultSelectedT.added = nl.added;
        
    end if;
    
    return 1;
end$$$