-- --------------------------------------------------------------------------------
-- _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 (select a.sortable = 'y' from attributeT a where a.id = attribute_id_);
    
    if object_id_ is null then
        if sortable then
            insert cacheObjectSortT(objectId, attributeId, sorterAsc, sorterDesc)
            select objectn.id, attribute_id_, - coalesce(value_, avn.value), coalesce(value_, avn.value)
            from attributeT a
            join attributeValueNumericT avn on a.id = avn.attributeId
            join objectT objectn on avn.objectId = objectn.id
            where a.id = attribute_id_
                and a.dataTypeId = 'numeric'
            on duplicate key update 
                cacheObjectSortT.sorterAsc = values(sorterAsc),
                cacheObjectSortT.sorterDesc = values(sorterDesc);
            
            insert cacheObjectSortT(objectId, attributeId, sorterAsc, sorterDesc)
            select objectd.id, attribute_id_, - coalesce(value_, datetime_ToDouble(avd.value)), coalesce(value_, datetime_ToDouble(avd.value))
            from attributeT a
            join attributeValueDatetimeT avd on a.id = avd.attributeId
            join objectT objectd on avd.objectId = objectd.id
            where a.id = attribute_id_
                and a.dataTypeId = 'datetime'
            on duplicate key update 
                cacheObjectSortT.sorterAsc = values(sorterAsc),
                cacheObjectSortT.sorterDesc = values(sorterDesc);
        else
            delete from cacheObjectSortT where attributeId = attribute_id_;
        end if;
    else
        if sortable then 
            insert cacheObjectSortT(objectId, attributeId, sorterAsc, sorterDesc)
            select 
                o.id, 
                a.id, 
                - coalesce(value_, avn.value, datetime_ToDouble(avd.value)), 
                coalesce(value_, avn.value, datetime_ToDouble(avd.value))
            from attributeT a
            join objectT o on 1=1
            left join attributeValueNumericT avn on a.id = avn.attributeId and o.id = avn.objectId
            left join attributeValueDatetimeT avd on a.id = avd.attributeId and o.id = avd.objectId
            where a.id = attribute_id_
                and o.id = object_id_
            on duplicate key update 
                cacheObjectSortT.sorterAsc = values(sorterAsc),
                cacheObjectSortT.sorterDesc = values(sorterDesc);
        else
            delete from cacheObjectSortT where objectId = object_id_ and 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_UpdateAllSortCache() 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_UpdateAllRatings();
    
    -- 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
    where a.sortable = 'y';
    
    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
    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 primary key,
        selectedPoints int not null,
        totalPoints int not null
    ) engine=MEMORY;
    
    insert objectUpdateRatingsData(objectId, selectedPoints, totalPoints)
    select 
        o.id,
        sum(if(nrl.linked = 1, srctag.ratingPoints, 0)),
        sum(srctag.ratingPoints)
    from objectT o
    join zzzNodeWorksetV workset on o.id = workset.nodeId
    join _nodeRecommendLink4V nrl on o.id = nrl.dstNodeId
    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 workset.isMain = 1
        and nodetype.canHaveRatingPoints = 'y'
    group by o.id;
    
    insert zzzDevNullObjectT(n)
    select __object_UpdateRating(objectId, selectedPoints, totalPoints)
    from objectUpdateRatingsData;

    drop temporary table objectUpdateRatingsData;

    return 1;
end$$$


create function __object_UpdateAllRatings() 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 primary key,
        selectedPoints int not null,
        totalPoints int not null
    ) engine=MEMORY;
    
    insert objectUpdateAllRatingsData(objectId, selectedPoints, totalPoints)
    select 
        o.id,
        sum(if(nrl.linked = 1, srctag.ratingPoints, 0)),
        sum(srctag.ratingPoints)
    from objectT o
    join _nodeRecommendLink4V nrl on o.id = nrl.dstNodeId
    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;
    
    insert zzzDevNullObjectT(n)
    select __object_UpdateRating(objectId, selectedPoints, totalPoints)
    from objectUpdateAllRatingsData;

    drop temporary table objectUpdateAllRatingsData;

    return 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
    ) engine=MyISAM;
    
    -- CONST: address field separator
    insert objectUpdateAddressesData(objectId, address)
    select 
        o.id,
        group_concat(
            if(
                nad.srcInternalTypeId = 'attribute',
                coalesce(
                    nad.srcNumericValue,
                    date_format(nad.srcDatetimeValue, nad.srcDatetimeOutputFormat), 
                    nad.srcTextValue, 
                    ''), 
                nad.srcName)
            order by nad.ancestrySorter, nad.srcTypeName, nad.srcName
            separator ', ') addr
    from objectT o
    join zzzNodeWorksetV nws on o.id = nws.nodeId
    left join nodeAncestryDataV nad on o.id = nad.dstNodeId and nad.languageIsDefault = 'y'
    where nws.isMain = 1
    group by o.id;

    -- CONST: address field separator and language id
    insert objectUpdateAddressesData(objectId, alias)
    select 
        o.id,
        concat_ws(
            '-', 
            o.id,
            group_concat(
                if(
                    nad.srcInternalTypeId = 'attribute',
                    coalesce(
                        nad.srcNumericValue,
                        date_format(nad.srcDatetimeValue, nad.srcDatetimeOutputFormat), 
                        nad.srcTextValue, 
                        ''), 
                    nad.srcName)
                order by nad.ancestrySorter, nad.srcTypeName, nad.srcName
                separator '-')) alias_
    from objectT o
    join zzzNodeWorksetV nws on o.id = nws.nodeId
    left join nodeAncestryDataV nad on o.id = nad.dstNodeId and nad.languageId = 'eng'
    where nws.isMain = 1
    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_UpdateAllAddresses() returns int unsigned
not deterministic modifies sql data
comment 'updates all object addresses and aliases; to be called manually'
begin
    drop temporary table if exists objectUpdateAllAddressesData;
    create temporary table objectUpdateAllAddressesData(
        objectId int unsigned not null primary key,
        address text null,
        alias text null
    ) engine=MyISAM;
    
    -- CONST: address field separator
    insert objectUpdateAllAddressesData(objectId, address)
    select 
        o.id,
        group_concat(
            if(
                nad.srcInternalTypeId = 'attribute',
                coalesce(
                    nad.srcNumericValue,
                    date_format(nad.srcDatetimeValue, nad.srcDatetimeOutputFormat), 
                    nad.srcTextValue, 
                    ''), 
                nad.srcName)
            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(
                if(
                    nad.srcInternalTypeId = 'attribute',
                    coalesce(
                        nad.srcNumericValue,
                        date_format(nad.srcDatetimeValue, nad.srcDatetimeOutputFormat), 
                        nad.srcTextValue, 
                        ''), 
                    nad.srcName)
                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);

    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 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_, substring(address_, 1, 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_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
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_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_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$$$


-- 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_);
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_UpdateTypeByUserForObject(
    image_id_ int unsigned,
    image_type_id_ char(10) binary,
    video_type_ varchar(50),
    url_ varchar(255),
    extra_id_ 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_);
end$$$