-- --------------------------------------------------------------------------------
-- _031_image Group Routines
-- --------------------------------------------------------------------------------
DELIMITER $$$
-- object and user specific image code is in _08_object

create function __image_DefaultSorter() returns int
not deterministic
begin
    -- CONST: sorter step
    return coalesce(sorter_Get(), sorter_Set(coalesce((select max(sorter) from imageT), 0) + 10, 10));
end$$$


create function __image_Create(
    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
    declare image_id int unsigned default(select i.id from imageT i where i.nodeId = node_id_ and i.forceSinglePhoto = 'y');
    
    -- if the node_id has the image and the node_id can have only 1 image - detach the current image from the node
    set @1 = image_id is not null and __image_UpdateNodeId(image_id, node_id_, null);
    
    -- FORWARD: user_CurrentId, user_CurrentName and user_CurrentIp are defined in _060_user
    insert imageT(
        nodeId,
        forceSinglePhoto,
        name,
        filenameOriginal,
        filenameFs,
        display,
        sorter,
        imageTypeId,
        addedUserId,
        addedUserName,
        addedIp,
        changedUserId,
        changedUserName,
        changedIp)
    values (
        node_id_,
        (select n.forceSinglePhoto from nodeT n where n.id = node_id_),
        name_,
        filename_original_,
        filename_fs_,
        display_,
        coalesce(sorter_, __image_DefaultSorter()),
        (select it.id from imageTypeT it where it.isDefault = 'y'),
        user_CurrentId(),
        user_CurrentName(),
        user_CurrentIp(),
        user_CurrentId(),
        user_CurrentName(),
        user_CurrentIp());
    
    set image_id = last_insert_id();

    set @2 = node_id_ is not null and __node_Update(node_id_, null);
    
    return image_id;
end$$$


create function image_Create(
    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 = node_id_ is not null and __node_CanUpdate(node_id_, 'imagecreate');
    return __image_Create(node_id_, name_, filename_original_, filename_fs_, display_, sorter_);
end$$$


create function __image_Delete(image_id_ int unsigned, node_id_ int unsigned) returns int unsigned
not deterministic modifies sql data
begin
    delete from imageT where id = image_id_;

    set @1 = (row_count() > 0) and node_id_ is not null and __node_Update(node_id_, null);
    
    return 1;
end$$$


create function image_Delete(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 = node_id is not null and __node_CanUpdate(node_id, 'imagedelete');
    return __image_Delete(image_id_, node_id);
end$$$


create function __image_Update(
    image_id_ int unsigned,
    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)
) returns int unsigned
not deterministic modifies sql data
begin
    set @1 = node_id_ is not null and __node_Update(node_id_, null);
    
    update imageT set
        name = coalesce(name_, name),
        filenameOriginal = coalesce(filename_original_, filenameOriginal),
        filenameFs = coalesce(filename_fs_, filenameFs),
        display = coalesce(display_, display),
        sorter = coalesce(sorter_, sorter),
        imageTypeId = coalesce(image_type_id_, imageTypeId),
        videoType = coalesce(video_type_, videoType),
        url = coalesce(url_, url),
        extraId = coalesce(extra_id_, extraId),
        changed = current_timestamp(),
        changedUserId = user_CurrentId(),
        changedUserName = user_CurrentName(),
        changedIp = user_CurrentIp()
    where id = image_id_;
    
    return image_id_;
end$$$


create function image_Update(
    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 = node_id is not null and __node_CanUpdate(node_id, 'imageupdate');
    return __image_Update(image_id_, node_id, name_, filename_original_, filename_fs_, display_, sorter_, null, null, null, null);
end$$$


create function __image_UpdateNodeId(
    image_id_ int unsigned, 
    previous_node_id_ int unsigned, 
    node_id_ int unsigned
) returns int unsigned
not deterministic modifies sql data
begin
    set @1 = previous_node_id_ is not null and __node_Update(previous_node_id_, null);
    set @2 = node_id_ is not null and __node_Update(node_id_, null);
    
    update imageT set
        nodeId = node_id_,
        forceSinglePhoto = (select n.forceSinglePhoto from nodeT n where n.id = node_id_),
        changed = current_timestamp(),
        changedUserId = user_CurrentId(),
        changedUserName = user_CurrentName(),
        changedIp = user_CurrentIp()
    where id = image_id_;

    return image_id_;
end$$$


create function image_UpdateNodeId(
    image_id_ int unsigned, 
    node_id_ int unsigned
) returns int unsigned
not deterministic modifies sql data
begin
    declare previous_node_id int unsigned default(select i.nodeId from imageT i where i.id = image_id_);
    
    set @1 = previous_node_id is not null and __node_CanUpdate(previous_node_id, 'imageupdate');
    set @2 = node_id_ is not null and __node_CanUpdate(node_id_, 'imageupdate');
    
    return __image_UpdateNodeId(image_id_, previous_node_id, node_id_);
end$$$


create function image_UpdateType(
    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 = node_id is not null and __node_CanUpdate(node_id, 'imageupdate');
    return __image_Update(image_id_, node_id, null, null, null, null, null, image_type_id_, video_type_, url_, extra_id_);
end$$$