-- --------------------------------------------------------------------------------
-- _090_attribute Group Routines
-- --------------------------------------------------------------------------------
DELIMITER $$$


create function __attribute_ValidateId(attribute_id_ int unsigned) returns int unsigned
deterministic
begin
    declare attribute_id int unsigned;
    
    select a.id into attribute_id
    from attributeT a
    join tagT t on a.id = t.id
    join nodeT n on a.id = n.id
    join nodeTypeT nt on n.nodeTypeId = nt.id
    where a.id = attribute_id_
        and nt.internalTypeId = 'attribute';
    
    if attribute_id is null then 
        call raiseError1('attribute', 'validate', attribute_id_, 'attribute does not exist');
    end if;

    return 1;
end$$$


create function __attribute_ValidateIdDataType(
    attribute_id_ int unsigned, 
    type_name_ varchar(10) binary
) returns int unsigned
deterministic
begin
    set @1 = __attribute_ValidateId(attribute_id_);
    if (select a.dataTypeId from attributeT a where a.id = attribute_id_) != type_name_ then
        call raiseError2('attribute', 'typecheck', attribute_id_, type_name_, 'attribute mismatching type');
    end if;
    
    return 1;
end$$$


create function attribute_GetByCode(code_ varchar(30) binary) returns int unsigned
deterministic
return (select ac.id from attributeCodeT ac where ac.attributeCode = code_)$$$


create function __attribute_ClampValueNumeric(attribute_id_ int unsigned, value_ decimal(22, 6)) returns decimal(22, 6)
deterministic
begin
    declare res decimal(22, 6);
    
    select round(
                greatest(
                    least(
                        value_, 
                        coalesce(a.decimalMax, value_)), 
                    coalesce(a.decimalMin, value_)), 
                a.decimalRound)
    into res
    from attributeNumericT a
    where a.id = attribute_id_;
    
    return res;
end$$$


create function __attribute_ClampValueDatetime(attribute_id_ int unsigned, value_ datetime) returns datetime
deterministic
begin
    declare res datetime;
    
    select cast(date_format(value_, adf.storageFormat) as datetime)
    into res
    from attributeDatetimeT a
    join attributeDatetimeFormatT adf on a.datetimeStorageId = adf.id
    where a.id = attribute_id_;
    
    return res;
end$$$


create function __attribute_ClampValuePrice(attribute_id_ int unsigned, value_ decimal(22, 6)) returns decimal(22, 6)
deterministic
begin
    declare res decimal(22, 6);
    
    select round(greatest(value_, 0), a.priceRound)
    into res
    from attributePriceT a
    where a.id = attribute_id_;
    
    return res;
end$$$


create function __attribute_UpdateCache(attribute_id_ int unsigned) returns int unsigned
not deterministic modifies sql data
begin
    if attribute_id_ is null then
        insert attributeCacheT(id, attributeNames, attributeNamesSearch)
        select 
            tl.id, 
            group_concat(concat_ws(0xb, tl.languageId, tl.uofmPre, tl.uofmPost) separator '\0'),
            group_concat(concat_ws(0xb, tl.languageId, string_Normalize(tl.uofmPre), string_Normalize(tl.uofmPost)) separator '\0')
        from attributeT a
        join tagTL tl on a.id = tl.id
        group by tl.id
        on duplicate key update
            attributeCacheT.attributeNames = values(attributeNames),
            attributeCacheT.attributeNamesSearch = values(attributeNamesSearch);
    else
        insert attributeCacheT(id, attributeNames, attributeNamesSearch)
        select 
            tl.id, 
            group_concat(concat_ws(0xb, tl.languageId, tl.uofmPre, tl.uofmPost) separator '\0'),
            group_concat(concat_ws(0xb, tl.languageId, string_Normalize(tl.uofmPre), string_Normalize(tl.uofmPost)) separator '\0')
        from attributeT a
        join tagTL tl on a.id = tl.id
        where tl.id = attribute_id_
        group by tl.id
        on duplicate key update
            attributeCacheT.attributeNames = values(attributeNames),
            attributeCacheT.attributeNamesSearch = values(attributeNamesSearch);
    end if;
    
    return attribute_id_;
end$$$


create function __attribute_Create(
    type_id_ int unsigned,
    type_code_ varchar(30) binary,
    name_ varchar(255),
    sorter_ int,
    can_search_ enum('n', 'y'),
    display_in_object_ enum('n', 'y'),
    display_in_object_list_ enum('n', 'y'),
    factor_ double,
    enabled_ enum('n', 'y'),
    data_type_id_ varchar(10) binary,
    code_ varchar(30) binary,
    required_ enum('n', 'y'),
    uofm_pre_ varchar(255),
    uofm_post_ varchar(255)
) returns int unsigned
not deterministic modifies sql data
begin
    declare attribute_id int unsigned;
    
    set attribute_id = __tag_Create(type_id_, type_code_, 'attribute', name_, sorter_, can_search_, display_in_object_, display_in_object_list_, factor_, null, enabled_, uofm_pre_, uofm_post_);
    
    insert attributeT(id, dataTypeId, required)
    values(attribute_id, data_type_id_, required_);

    if nullif(code_, '') is not null then
        insert attributeCodeT(id, attributeCode)
        values(attribute_id, code_);
    end if;
    
    set @1 = __attribute_UpdateCache(attribute_id);
    
    return attribute_id;
end$$$


create function __attribute_SetNumeric(
    attribute_id_ int unsigned,
    decimal_min_ decimal(22, 6), reset_decimal_min_ enum('n', 'y'),
    decimal_max_ decimal(22, 6), reset_decimal_max_ enum('n', 'y'),
    decimal_round_ int
) returns int unsigned
not deterministic modifies sql data
begin
    insert attributeNumericT(
        id, 
        decimalMin, 
        decimalMax, 
        decimalRound)
    values(
        attribute_id_,
        decimal_min_,
        decimal_max_,
        decimal_round_)
    on duplicate key update
        attributeNumericT.decimalMin = if(reset_decimal_min_ = 'y', null, coalesce(decimal_min_, attributeNumericT.decimalMin)),
        attributeNumericT.decimalMax = if(reset_decimal_max_ = 'y', null, coalesce(decimal_max_, attributeNumericT.decimalMax)),
        attributeNumericT.decimalRound = coalesce(decimal_round_, attributeNumericT.decimalRound);

    return attribute_id_;
end$$$


create function __attribute_SetDatetime(
    attribute_id_ int unsigned,
    datetime_storage_id_ varchar(10) binary
) returns int unsigned
not deterministic modifies sql data
begin
    insert attributeDatetimeT(
        id, 
        datetimeStorageId)
    values(
        attribute_id_,
        datetime_storage_id_)
    on duplicate key update
        attributeDatetimeT.datetimeStorageId = coalesce(datetime_storage_id_, attributeDatetimeT.datetimeStorageId);

    return attribute_id_;
end$$$


create function __attribute_SetText(
    attribute_id_ int unsigned,
    text_is_long_ enum('n', 'y')
) returns int unsigned
not deterministic modifies sql data
begin
    insert attributeTextT(
        id, 
        textIsLong)
    values(
        attribute_id_,
        text_is_long_)
    on duplicate key update
        attributeTextT.textIsLong = coalesce(text_is_long_, attributeTextT.textIsLong);

    return attribute_id_;
end$$$


create function __attribute_SetPrice(
    attribute_id_ int unsigned,
    price_round_ int
) returns int unsigned
not deterministic modifies sql data
begin
    insert attributePriceT(
        id, 
        priceRound)
    values(
        attribute_id_,
        price_round_)
    on duplicate key update
        attributePriceT.priceRound = coalesce(price_round_, attributePriceT.priceRound);

    return attribute_id_;
end$$$


create function __attribute_Duplicate(attribute_id_ int unsigned) returns int unsigned
not deterministic modifies sql data
begin
    declare new_id int unsigned;
    
    set @1 = __attribute_ValidateId(attribute_id_);
    
    set new_id = __tag_DuplicateInternal(attribute_id_);
    
    insert attributeT(id, dataTypeId, required)
    select new_id, a.dataTypeId, a.required
    from attributeT a
    where a.id = attribute_id_;

    -- as attributeCode cannot be duplicate, we deliberately are not duplicating attributeCodeT record for new_id
    
    insert attributeCacheT(id, attributeNames, attributeNamesSearch)
    select new_id, ac.attributeNames, ac.attributeNamesSearch
    from attributeCacheT ac
    where ac.id = attribute_id_;
    
    insert attributeNumericT(id, decimalMin, decimalMax, decimalRound)
    select new_id, an.decimalMin, an.decimalMax, an.decimalRound
    from attributeNumericT an
    where an.id = attribute_id_;

    insert attributeDatetimeT(id, datetimeStorageId)
    select new_id, ad.datetimeStorageId
    from attributeDatetimeT ad
    where ad.id = attribute_id_;
    
    insert attributeTextT(id, textIsLong)
    select new_id, ax.textIsLong
    from attributeTextT ax
    where ax.id = attribute_id_;
    
    insert attributePriceT(id, priceRound)
    select new_id, ap.priceRound
    from attributePriceT ap
    where ap.id = attribute_id_;

    return new_id;
end$$$


create function attribute_CreateNumeric(
    type_id_ int unsigned,
    type_code_ varchar(30) binary,
    name_ varchar(255),
    sorter_ int,
    can_search_ enum('n', 'y'),
    display_in_object_ enum('n', 'y'),
    display_in_object_list_ enum('n', 'y'),
    factor_ double,
    enabled_ enum('n', 'y'),
    code_ varchar(30) binary,
    required_ enum('n', 'y'),
    uofm_pre_ varchar(255),
    uofm_post_ varchar(255),
    decimal_min_ decimal(22, 6),
    decimal_max_ decimal(22, 6),
    decimal_round_ int
) returns int unsigned
not deterministic modifies sql data
begin
    declare attribute_id int unsigned;
    
    set attribute_id = __attribute_Create(
        type_id_, 
        type_code_, 
        name_, 
        sorter_, 
        can_search_, 
        display_in_object_,
        display_in_object_list_,
        factor_,
        enabled_, 
        'numeric', 
        code_,
        required_,
        uofm_pre_, 
        uofm_post_);
    
    set @1 = __attribute_SetNumeric(attribute_id, decimal_min_, null, decimal_max_, null, decimal_round_);
    
    return attribute_id;
end$$$


create function attribute_CreateDatetime(
    type_id_ int unsigned,
    type_code_ varchar(30) binary,
    name_ varchar(255),
    sorter_ int,
    can_search_ enum('n', 'y'),
    display_in_object_ enum('n', 'y'),
    display_in_object_list_ enum('n', 'y'),
    factor_ double,
    enabled_ enum('n', 'y'),
    code_ varchar(30) binary,
    required_ enum('n', 'y'),
    uofm_pre_ varchar(255),
    uofm_post_ varchar(255),
    datetime_storage_id_ varchar(10) binary
) returns int unsigned
not deterministic modifies sql data
begin
    declare attribute_id int unsigned;
    
    set attribute_id = __attribute_Create(
        type_id_, 
        type_code_, 
        name_, 
        sorter_, 
        can_search_, 
        display_in_object_,
        display_in_object_list_,
        factor_,
        enabled_, 
        'datetime', 
        code_,
        required_,
        uofm_pre_, 
        uofm_post_);
    
    set @1 = __attribute_SetDatetime(attribute_id, datetime_storage_id_);
    
    return attribute_id;
end$$$


create function attribute_CreateText(
    type_id_ int unsigned,
    type_code_ varchar(30) binary,
    name_ varchar(255),
    sorter_ int,
    can_search_ enum('n', 'y'),
    display_in_object_ enum('n', 'y'),
    display_in_object_list_ enum('n', 'y'),
    factor_ double,
    enabled_ enum('n', 'y'),
    code_ varchar(30) binary,
    required_ enum('n', 'y'),
    uofm_pre_ varchar(255),
    uofm_post_ varchar(255),
    text_is_long_ enum('n', 'y')
) returns int unsigned
not deterministic modifies sql data
begin
    declare attribute_id int unsigned;
    
    set attribute_id = __attribute_Create(
        type_id_, 
        type_code_, 
        name_, 
        sorter_, 
        can_search_, 
        display_in_object_,
        display_in_object_list_,
        factor_,
        enabled_, 
        'text', 
        code_,
        required_,
        uofm_pre_, 
        uofm_post_);
    
    set @1 = __attribute_SetText(attribute_id, text_is_long_);
    
    return attribute_id;
end$$$


create function attribute_CreatePrice(
    type_id_ int unsigned,
    type_code_ varchar(30) binary,
    name_ varchar(255),
    sorter_ int,
    can_search_ enum('n', 'y'),
    display_in_object_ enum('n', 'y'),
    display_in_object_list_ enum('n', 'y'),
    factor_ double,
    enabled_ enum('n', 'y'),
    code_ varchar(30) binary,
    required_ enum('n', 'y'),
    uofm_pre_ varchar(255),
    uofm_post_ varchar(255),
    price_round_ int
) returns int unsigned
not deterministic modifies sql data
begin
    declare attribute_id int unsigned;
    
    set attribute_id = __attribute_Create(
        type_id_, 
        type_code_, 
        name_, 
        sorter_, 
        can_search_, 
        display_in_object_,
        display_in_object_list_,
        factor_,
        enabled_, 
        'price', 
        code_,
        required_,
        uofm_pre_, 
        uofm_post_);
    
    set @1 = __attribute_SetPrice(attribute_id, price_round_);
    
    return attribute_id;
end$$$


create function attribute_Delete(attribute_id_ int unsigned) returns int unsigned
not deterministic modifies sql data
begin
    set @1 = __attribute_ValidateId(attribute_id_);
    return __tag_Delete(attribute_id_); -- call ancestor function
end$$$


create function __attribute_Update(
    attribute_id_ int unsigned,
    sorter_ int,
    can_search_ enum('n', 'y'), reset_can_search_ enum('n', 'y'), 
    display_in_object_ enum('n', 'y'), reset_display_in_object_ enum('n', 'y'),
    display_in_object_list_ enum('n', 'y'), reset_display_in_object_list_ enum('n', 'y'),
    factor_ double,
    enabled_ enum('n', 'y'),
    code_ varchar(30) binary,
    required_ enum('n', 'y')
) returns int unsigned
not deterministic modifies sql data
begin
    set @1 = __tag_Update(
                    attribute_id_, 
                    sorter_, 
                    can_search_, reset_can_search_, 
                    display_in_object_, reset_display_in_object_,
                    display_in_object_list_, reset_display_in_object_list_,
                    factor_, 
                    null,
                    enabled_);
    
    update attributeT set
        required = coalesce(required_, required)
    where id = attribute_id_;

    if code_ = '' then
        delete from attributeCodeT where id = attribute_id_;
    elseif code_ is not null then
        insert attributeCodeT(id, attributeCode)
        values (attribute_id_, code_)
        on duplicate key update
            attributeCodeT.attributeCode = code_;
    end if; 

    -- as of now - no __attribute_UpdateCache here because cache does not depend on attributeT

    return attribute_id_;
end$$$


create function attribute_Update(
    attribute_id_ int unsigned,
    sorter_ int,
    can_search_ enum('n', 'y'), reset_can_search_ enum('n', 'y'), 
    display_in_object_ enum('n', 'y'), reset_display_in_object_ enum('n', 'y'),
    display_in_object_list_ enum('n', 'y'), reset_display_in_object_list_ enum('n', 'y'),
    factor_ double,
    enabled_ enum('n', 'y'),
    code_ varchar(30) binary,
    required_ enum('n', 'y')
) returns int unsigned
not deterministic modifies sql data
begin
    set @1 = __attribute_ValidateId(attribute_id_);

    set @2 = __attribute_Update(
                        attribute_id_, 
                        sorter_, 
                        can_search_, reset_can_search_, 
                        display_in_object_, reset_display_in_object_,
                        display_in_object_list_, reset_display_in_object_list_,
                        factor_, 
                        enabled_, 
                        code_,
                        required_);

    set @3 = __object_UpdateSortCache(null, attribute_id_, null);

    return attribute_id_;
end$$$


create function __attribute_UpdateNumericValues(attribute_id_ int unsigned) returns int unsigned
not deterministic modifies sql data
begin
    update attributeValueNumericT set
        value = __attribute_ClampValueNumeric(attribute_id_, valueRaw)
    where attributeId = attribute_id_;

    return attribute_id_;
end$$$


create function attribute_UpdateNumeric(
    attribute_id_ int unsigned,
    sorter_ int,
    can_search_ enum('n', 'y'), reset_can_search_ enum('n', 'y'), 
    display_in_object_ enum('n', 'y'), reset_display_in_object_ enum('n', 'y'),
    display_in_object_list_ enum('n', 'y'), reset_display_in_object_list_ enum('n', 'y'),
    factor_ double,
    enabled_ enum('n', 'y'),
    code_ varchar(30) binary,
    required_ enum('n', 'y'),
    decimal_min_ decimal(22, 6), reset_decimal_min_ enum('n', 'y'),
    decimal_max_ decimal(22, 6), reset_decimal_max_ enum('n', 'y'),
    decimal_round_ int
) returns int unsigned
not deterministic modifies sql data
begin
    set @1 = __attribute_ValidateIdDataType(attribute_id_, 'numeric');
    
    set @2 = __attribute_Update(
                        attribute_id_, 
                        sorter_, 
                        can_search_, reset_can_search_, 
                        display_in_object_, reset_display_in_object_,
                        display_in_object_list_, reset_display_in_object_list_,
                        factor_, 
                        enabled_, 
                        code_,
                        required_);
    
    set @3 = __attribute_SetNumeric(attribute_id_, decimal_min_, reset_decimal_min_, decimal_max_, reset_decimal_max_, decimal_round_);
    
    set @4 = __attribute_UpdateNumericValues(attribute_id_);

    set @5 = __object_UpdateSortCache(null, attribute_id_, null);

    return attribute_id_;
end$$$


create function __attribute_UpdateDatetimeValues(attribute_id_ int unsigned) returns int unsigned
not deterministic modifies sql data
begin
    update attributeValueDatetimeT set
        value = __attribute_ClampValueDatetime(attribute_id_, valueRaw)
    where attributeId = attribute_id_;

    return attribute_id_;
end$$$


create function attribute_UpdateDatetime(
    attribute_id_ int unsigned,
    sorter_ int,
    can_search_ enum('n', 'y'), reset_can_search_ enum('n', 'y'), 
    display_in_object_ enum('n', 'y'), reset_display_in_object_ enum('n', 'y'),
    display_in_object_list_ enum('n', 'y'), reset_display_in_object_list_ enum('n', 'y'),
    factor_ double,
    enabled_ enum('n', 'y'),
    code_ varchar(30) binary,
    required_ enum('n', 'y'),
    datetime_storage_id_ varchar(10) binary
) returns int unsigned
not deterministic modifies sql data
begin
    set @1 = __attribute_ValidateIdDataType(attribute_id_, 'datetime');
    
    set @2 = __attribute_Update(
                        attribute_id_, 
                        sorter_, 
                        can_search_, reset_can_search_, 
                        display_in_object_, reset_display_in_object_,
                        display_in_object_list_, reset_display_in_object_list_,
                        factor_, 
                        enabled_, 
                        code_,
                        required_);
    
    set @3 = __attribute_SetDatetime(attribute_id_, datetime_storage_id_);
    
    set @4 = __attribute_UpdateDatetimeValues(attribute_id_);
    
    set @5 = __object_UpdateSortCache(null, attribute_id_, null);
    
    return attribute_id_;
end$$$


create function attribute_UpdateText(
    attribute_id_ int unsigned,
    sorter_ int,
    can_search_ enum('n', 'y'), reset_can_search_ enum('n', 'y'), 
    display_in_object_ enum('n', 'y'), reset_display_in_object_ enum('n', 'y'),
    display_in_object_list_ enum('n', 'y'), reset_display_in_object_list_ enum('n', 'y'),
    factor_ double,
    enabled_ enum('n', 'y'),
    code_ varchar(30) binary,
    required_ enum('n', 'y'),
    text_is_long_ enum('n', 'y')
) returns int unsigned
not deterministic modifies sql data
begin
    set @1 = __attribute_ValidateIdDataType(attribute_id_, 'text');
    
    set @2 = __attribute_Update(
                        attribute_id_, 
                        sorter_, 
                        can_search_, reset_can_search_, 
                        display_in_object_, reset_display_in_object_,
                        display_in_object_list_, reset_display_in_object_list_,
                        factor_, 
                        enabled_, 
                        code_,
                        required_);
    
    set @3 = __attribute_SetText(attribute_id_, text_is_long_);
    
    return attribute_id_;
end$$$


create function __attribute_UpdatePriceValues(attribute_id_ int unsigned) returns int unsigned
not deterministic modifies sql data
begin
    update attributeValuePriceT set
        value = __attribute_ClampValuePrice(attribute_id_, valueRaw)
    where attributeId = attribute_id_
        and valueRaw is not null;

    return attribute_id_;
end$$$


create function __attribute_EnqueueObjects(attribute_id_ int unsigned) returns int unsigned
not deterministic modifies sql data
begin
    insert zzzDevNullBigintObjectT(n)
    select nodeQueue_Add(avp.objectId, 'currency')
    from attributeValuePriceT avp
    where avp.attributeId = attribute_id_
    group by avp.objectId;

    return attribute_id_;
end$$$


create function attribute_UpdatePrice(
    attribute_id_ int unsigned,
    sorter_ int,
    can_search_ enum('n', 'y'), reset_can_search_ enum('n', 'y'), 
    display_in_object_ enum('n', 'y'), reset_display_in_object_ enum('n', 'y'),
    display_in_object_list_ enum('n', 'y'), reset_display_in_object_list_ enum('n', 'y'),
    factor_ double,
    enabled_ enum('n', 'y'),
    code_ varchar(30) binary,
    required_ enum('n', 'y'),
    price_round_ int
) returns int unsigned
not deterministic modifies sql data
begin
    set @1 = __attribute_ValidateIdDataType(attribute_id_, 'price');
    
    set @2 = __attribute_Update(
                        attribute_id_, 
                        sorter_, 
                        can_search_, reset_can_search_, 
                        display_in_object_, reset_display_in_object_,
                        display_in_object_list_, reset_display_in_object_list_,
                        factor_, 
                        enabled_, 
                        code_,
                        required_);
    
    set @3 = __attribute_SetPrice(attribute_id_, price_round_);
    
    set @4 = __attribute_UpdatePriceValues(attribute_id_);

    set @5 = __object_UpdateSortCache(null, attribute_id_, null);

    set @6 = __attribute_EnqueueObjects(attribute_id_);

    return attribute_id_;
end$$$


create function attribute_UpdateLocal(
    attribute_id_ int unsigned,
    lang_id_ char(3),
    name_ varchar(255),
    uofm_pre_ varchar(255),
    uofm_post_ varchar(255)
) returns int unsigned
not deterministic modifies sql data
begin
    set @1 = __attribute_ValidateId(attribute_id_);
    
    set @2 = __tag_UpdateLocal(attribute_id_, lang_id_, name_, uofm_pre_, uofm_post_);
        
    set @3 = __attribute_UpdateCache(attribute_id_);

    return attribute_id_;
end$$$


-- --------------------------------------------------------------------------------
-- attribute values
-- --------------------------------------------------------------------------------

create function __attribute_ValidateValueByType(
    data_type_ varchar(10) binary,
    value_numeric_ decimal(22, 6),
    value_datetime_ datetime,
    value_text_ text
) returns int unsigned
deterministic
begin
    if (data_type_ = 'numeric' and value_numeric_ is null) then
        call raiseError2('attribute', 'validateValue', attribute_id_, 'numeric', 'no numeric value');
    elseif (data_type_ = 'datetime' and value_datetime_ is null) then
        call raiseError2('attribute', 'validateValue', attribute_id_, 'datetime', 'no datetime value');
    elseif (data_type_ = 'text' and value_text_ is null) then
        call raiseError2('attribute', 'validateValue', attribute_id_, 'text', 'no text value');
    elseif (data_type_ = 'price' and value_numeric_ is null) then
        call raiseError2('attribute', 'validateValue', attribute_id_, 'price', 'no price value');
    end if;

    return 1;
end$$$


create function __attribute_SetValueNumeric(
    attribute_id_ int unsigned,
    object_id_ int unsigned,
    value_numeric_ decimal(22, 6)
) returns int unsigned
not deterministic modifies sql data
begin
    if value_numeric_ is null then
        delete from attributeValueNumericT
        where attributeId = attribute_id_
            and objectId = object_id_;
    else
        insert attributeValueNumericT(attributeId, objectId, value, valueRaw)
        values(attribute_id_, object_id_, __attribute_ClampValueNumeric(attribute_id_, value_numeric_), value_numeric_)
        on duplicate key update 
            attributeValueNumericT.value = __attribute_ClampValueNumeric(attribute_id_, value_numeric_),
            attributeValueNumericT.valueRaw = value_numeric_;
    end if;
    
    return attribute_id_;
end$$$


create function __attribute_SetValueDatetime(
    attribute_id_ int unsigned,
    object_id_ int unsigned,
    value_datetime_ datetime
) returns int unsigned
not deterministic modifies sql data
begin
    if value_datetime_ is null then
        delete from attributeValueDatetimeT
        where attributeId = attribute_id_
            and objectId = object_id_;
    else
        insert attributeValueDatetimeT(attributeId, objectId, value, valueRaw)
        values(attribute_id_, object_id_, __attribute_ClampValueDatetime(attribute_id_, value_datetime_), value_datetime_)
        on duplicate key update 
            attributeValueDatetimeT.value = __attribute_ClampValueDatetime(attribute_id_, value_datetime_),
            attributeValueDatetimeT.valueRaw = value_datetime_;
    end if;
        
    return attribute_id_;
end$$$


create function __attribute_SetValueText(
    attribute_id_ int unsigned,
    object_id_ int unsigned,
    lang_id_ char(3),
    value_text_ text
) returns int unsigned
not deterministic modifies sql data
begin
    if value_text_ is null then
        delete from attributeValueTextT
        where attributeId = attribute_id_
            and objectId = object_id_;
    else
        insert attributeValueTextT(attributeId, objectId, languageId, value)
        select attribute_id_, object_id_, l.id, value_text_
        from languageT l
        where l.id = coalesce(lang_id_, l.id)
        on duplicate key update 
            attributeValueTextT.value = value_text_;
    end if;
        
    return attribute_id_;
end$$$


create function __attribute_PriceCalculateList(attribute_id_ int unsigned) returns int unsigned
not deterministic modifies sql data
comment 'uses intlist1T instead of object id'
begin
    drop temporary table if exists attributePriceCalculateDest;
    create temporary table attributePriceCalculateDest like attributeValuePriceT;

    -- calculate price for the default currency from non-default one - if any
    insert attributePriceCalculateDest(attributeId, objectId, currencyId, value, valueRaw, currencyExchangeRateId)
    select 
        avp.attributeId,
        avp.objectId,
        defaultcurrency.id,
        __attribute_ClampValuePrice(avp.attributeId, avp.value * cer.rate),
        null,
        cer.id
    from attributeValuePriceT avp
    join currencyT thiscurrency on avp.currencyId = thiscurrency.id and thiscurrency.isDefault is null
    join currencyT defaultcurrency on defaultcurrency.isDefault = 'y'
    join currencyExchangeRateLatestV cer on avp.currencyId = cer.currencyId
    join intlist1T i on avp.objectId = i.n
    where avp.attributeId = coalesce(attribute_id_, avp.attributeId)
        and avp.valueRaw is not null;

    -- 
    insert attributeValuePriceT(attributeId, objectId, currencyId, value, valueRaw, currencyExchangeRateId)
    select
        dest.attributeId,
        dest.objectId,
        dest.currencyId,
        dest.value,
        dest.valueRaw,
        dest.currencyExchangeRateId
    from attributePriceCalculateDest dest
    on duplicate key update
        attributeValuePriceT.value = values(value),
        attributeValuePriceT.valueRaw = values(valueRaw),
        attributeValuePriceT.currencyExchangeRateId = values(currencyExchangeRateId);

    -- calculate prices for the rest of non-default currencies from default one
    insert attributePriceCalculateDest(attributeId, objectId, currencyId, value, valueRaw, currencyExchangeRateId)
    select
        avp.attributeId,
        avp.objectId,
        newcurrency.id,
        if(cer.rate = 0, 0, __attribute_ClampValuePrice(avp.attributeId, avp.value / cer.rate)),
        null,
        cer.id
    from attributeValuePriceT avp
    join currencyT defaultcurrency on avp.currencyId = defaultcurrency.id and defaultcurrency.isDefault = 'y'
    join currencyT newcurrency on newcurrency.isDefault is null
    join currencyExchangeRateLatestV cer on newcurrency.id = cer.currencyId
    join intlist1T i on avp.objectId = i.n
    left join attributeValuePriceT exiavp on avp.attributeId = exiavp.attributeId 
        and avp.objectId = exiavp.objectId
        and newcurrency.id = exiavp.currencyId
        and exiavp.valueRaw is not null
    where avp.attributeId = coalesce(attribute_id_, avp.attributeId)
        and exiavp.attributeId is null
    on duplicate key update
        attributePriceCalculateDest.value = values(value),
        attributePriceCalculateDest.valueRaw = null,
        attributePriceCalculateDest.currencyExchangeRateId = values(currencyExchangeRateId);

    --
    insert attributeValuePriceT(attributeId, objectId, currencyId, value, valueRaw, currencyExchangeRateId)
    select
        dest.attributeId,
        dest.objectId,
        dest.currencyId,
        dest.value,
        dest.valueRaw,
        dest.currencyExchangeRateId
    from attributePriceCalculateDest dest
    on duplicate key update
        attributeValuePriceT.value = values(value),
        attributeValuePriceT.valueRaw = values(valueRaw),
        attributeValuePriceT.currencyExchangeRateId = values(currencyExchangeRateId);

    drop temporary table attributePriceCalculateDest;

    return coalesce(attribute_id_, 1); -- need to return something which at least evaluates to 'true'
end$$$


create function __attribute_PriceUpdateCacheList(attribute_id_ int unsigned) returns int unsigned
not deterministic modifies sql data
comment 'uses intlist1T instead of object id'
begin
    insert cacheAttributeValuePriceT(attributeId, objectId, value)
    select
        avp.attributeId,
        avp.objectId,
        group_concat(
            concat_ws(
                0xb,
                c.id,
                cl.languageId,
                avp.value,
                string_Replace0xbZero(cl.name),
                string_Replace0xbZero(cl.textAA),
                string_Replace0xbZero(cl.textAZ),
                string_Replace0xbZero(cl.textZA),
                string_Replace0xbZero(cl.textZZ),
                string_Replace0xbZero(coalesce(date_format(cer.effectiveDate, '%Y-%m-%d-%H-%i-%s'), '')),
                string_Replace0xbZero(coalesce(cer.rate, '')))
            order by avp.valueRaw desc, c.sorter, cl.languageId
            separator '\0')
    from attributeValuePriceT avp
    join intlist1T i on avp.objectId = i.n
    join currencyT c on avp.currencyId = c.id
    join currencyTL cl on c.id = cl.id
    left join currencyExchangeRateT cer on avp.currencyExchangeRateId = cer.id
    where avp.attributeId = coalesce(attribute_id_, avp.attributeId)
    group by avp.attributeId, avp.objectId
    on duplicate key update
        cacheAttributeValuePriceT.value = values(value);

    return coalesce(attribute_id_, 1); -- need to return something which at least evaluates to 'true'
end$$$


create function __attribute_PriceCalculate(attribute_id_ int unsigned, object_id_ int unsigned) returns int unsigned
not deterministic modifies sql data
begin
    declare res int unsigned;

    set @1 = intlist_Start1() and intlist_Add1(object_id_);
    set res = __attribute_PriceCalculateList(attribute_id_);
    set @3 = __attribute_PriceUpdateCacheList(attribute_id_);
    set @2 = intlist_End1();

    return res;
end$$$


create function __attribute_PriceUpdateCache(attribute_id_ int unsigned, object_id_ int unsigned) returns int unsigned
not deterministic modifies sql data
begin
    declare res int unsigned;

    set @1 = intlist_Start1() and intlist_Add1(object_id_);
    set @res = __attribute_PriceUpdateCacheList(attribute_id_);
    set @2 = intlist_End1();

    return res;
end$$$


create function __attribute_SetValuePrice(
    attribute_id_ int unsigned,
    object_id_ int unsigned,
    currency_id_ varchar(4) binary,
    value_price_ decimal(22, 6)
) returns int unsigned
not deterministic modifies sql data
begin
    if value_price_ is null then
        delete from attributeValuePriceT
        where attributeId = attribute_id_
            and objectId = object_id_;

        delete from cacheAttributeValuePriceT
        where attributeId = attribute_id_
            and objectId = object_id_;
    else
        update attributeValuePriceT set
            valueRaw = null
        where attributeId = attribute_id_
            and objectId = object_id_
            and valueRaw is not null;

        insert attributeValuePriceT(attributeId, objectId, currencyId, value, valueRaw, currencyExchangeRateId)
        values(attribute_id_, object_id_, currency_id_, __attribute_ClampValuePrice(attribute_id_, value_price_), value_price_, null)
        on duplicate key update 
            attributeValuePriceT.value = __attribute_ClampValuePrice(attribute_id_, value_price_),
            attributeValuePriceT.valueRaw = value_price_,
            attributeValuePriceT.currencyExchangeRateId = null;

        set @1 = __attribute_PriceCalculate(attribute_id_, object_id_);
    end if;
    
    return attribute_id_;
end$$$


create function __attribute_SetLink(
    attribute_id_ int unsigned,
    object_id_ int unsigned,
    do_link_setup_ int unsigned
) returns int unsigned
not deterministic modifies sql data
begin
    if do_link_setup_ then
        set @1 = nodeLink_UpdateStart();

        set @2 = nodeLink_Relink(null, attribute_id_, object_id_);

        set @3 = __nodeLink_UpdateWorkSet(attribute_id_, -1, -1, 0) and __nodeLink_UpdateWorkSet(object_id_, 1, 1, 0);

        set @4 = nodeLink_UpdateEnd();
    else
        set @1 = nodeLink_Unlink(null, attribute_id_, object_id_);
    end if;
    
    return attribute_id_;
end$$$


create function attribute_SetValueNumeric(
    attribute_id_ int unsigned,
    object_id_ int unsigned,
    value_numeric_ decimal(22, 6) 
) returns int unsigned
not deterministic modifies sql data
begin
    set @1 = __attribute_ValidateIdDataType(attribute_id_, 'numeric') and __object_ValidateId(object_id_);
    
    set @3 = __attribute_SetValueNumeric(attribute_id_, object_id_, value_numeric_);

    set @2 = __attribute_SetLink(attribute_id_, object_id_, value_numeric_ is not null);
    
    set @4 = __object_UpdateSortCache(object_id_, attribute_id_, value_numeric_);

    return attribute_id_;
end$$$


create function attribute_SetValueDatetime(
    attribute_id_ int unsigned,
    object_id_ int unsigned,
    value_datetime_ datetime
) returns int unsigned
not deterministic modifies sql data
begin
    set @1 = __attribute_ValidateIdDataType(attribute_id_, 'datetime') and __object_ValidateId(object_id_);
    
    set @3 = __attribute_SetValueDatetime(attribute_id_, object_id_, value_datetime_);

    set @2 = __attribute_SetLink(attribute_id_, object_id_, value_datetime_ is not null);

    set @4 = __object_UpdateSortCache(object_id_, attribute_id_, datetime_ToDouble(value_datetime_));

    return attribute_id_;
end$$$


create function attribute_SetValueText(
    attribute_id_ int unsigned,
    object_id_ int unsigned,
    lang_id_ char(3),
    value_text_ text
) returns int unsigned
not deterministic modifies sql data
begin
    declare val text default if(value_text_ = '', null, value_text_);
    
    set @1 = __attribute_ValidateIdDataType(attribute_id_, 'text') and __object_ValidateId(object_id_);
    
    set @3 = __attribute_SetValueText(attribute_id_, object_id_, lang_id_, val);

    set @2 = __attribute_SetLink(attribute_id_, object_id_, val is not null);

    return attribute_id_;
end$$$


create function attribute_SetValuePrice(
    attribute_id_ int unsigned,
    object_id_ int unsigned,
    currency_id_ varchar(4) binary,
    value_price_ decimal(22, 6) 
) returns int unsigned
not deterministic modifies sql data
begin
    set @1 = __attribute_ValidateIdDataType(attribute_id_, 'price') and __object_ValidateId(object_id_);
    
    set @3 = __attribute_SetValuePrice(attribute_id_, object_id_, currency_id_, value_price_);

    set @2 = __attribute_SetLink(attribute_id_, object_id_, value_price_ is not null);
    
    set @4 = __object_UpdateSortCache(object_id_, attribute_id_, value_price_);

    return attribute_id_;
end$$$


create function attribute_SetValue(
    attribute_id_ int unsigned,
    object_id_ int unsigned,
    extra_char_id_ varchar(10) binary,
    value_ text
) returns int unsigned
not deterministic modifies sql data
begin
    declare data_type varchar(10) default (select dataTypeId from attributeT where id = attribute_id_);
    declare val text default if(value_ = '', null, value_);
    
    set @1 = __attribute_ValidateId(attribute_id_) and __object_ValidateId(object_id_);
    
    case data_type
        when 'numeric' then
            set @3 = __attribute_SetValueNumeric(attribute_id_, object_id_, cast(val as decimal(22, 6)));
            set @4 = __object_UpdateSortCache(object_id_, attribute_id_, cast(val as decimal(22, 6)));
        when 'datetime' then
            set @3 = __attribute_SetValueDatetime(attribute_id_, object_id_, cast(val as datetime));
            set @4 = __object_UpdateSortCache(object_id_, attribute_id_, datetime_ToDouble(cast(val as datetime)));
        when 'price' then
            set @3 = __attribute_SetValuePrice(attribute_id_, object_id_, extra_char_id_, cast(val as decimal(22, 6)));
            set @4 = __object_UpdateSortCache(object_id_, attribute_id_, cast(val as decimal(22, 6)));
        else
            set @3 = __attribute_SetValueText(attribute_id_, object_id_, extra_char_id_, val);
    end case;

    set @2 = __attribute_SetLink(attribute_id_, object_id_, val is not null);

    return attribute_id_;
end$$$