Stored procedures

delimiter $$
drop procedure if exists `debug_msg`$$
create procedure debug_msg(message varchar(255))
begin
    create table if not exists `debug_logs` (
        created_at datetime DEFAULT CURRENT_TIMESTAMP,
        msg text
    );
    insert into `debug_logs` (msg) value (message);
END $$

delimiter $$
drop procedure if exists createOrUpdateOrderStatus$$
create procedure createOrUpdateOrderStatus(
    in lastCompanyId bigint(20),
    in lastOrderNumber int(11),
    in isCanceledExists tinyint,
    in isReceivedExists tinyint
)
begin
    # Common data
    set @update_id := 0;

    # Update canceled status
    if (isCanceledExists) then
        update order_status_test1 os
        set os.title = 'Отменён', os.general_status = 'FAILED', os.removable = false, id = (select @update_id := id)
        where os.company_id = lastCompanyId and os.title = 'Отменен';
        call debug_msg((
            select concat_ws('',
                'company_id ', lastCompanyId,
                ' order_status id ', @update_id,
                ' title updated to Отменён')
        ));
    # Create canceled status
    else
        insert into order_status_test1 (color, order_number, title, company_id, general_status, removable)
            value ('#f6686b', lastOrderNumber + 1, 'Отменён', lastCompanyId, 'FAILED', false);
        call debug_msg((
            select concat_ws('',
                'company_id ', lastCompanyId,
                ' order_status id ', LAST_INSERT_ID(),
                ' inserted a new status with title Отменён')
        ));
    end if;

    # Update received status
    if (isReceivedExists) then
        update order_status_test1 os
        set os.title = 'Успешный', os.general_status = 'SUCCESSFUL', os.removable = false,
            id = (select @update_id := id)
        where os.company_id = lastCompanyId and os.title = 'Получен';
        call debug_msg((
            select concat_ws('',
                'company_id ', lastCompanyId,
                ' order_status id ', @update_id,
                ' title updated to Успешный')
        ));
    # Create received status
    else
        insert into order_status_test1 (color, order_number, title, company_id, general_status, removable)
            value ('#eaa43eeb', lastOrderNumber + 1, 'Успешный', lastCompanyId, 'SUCCESSFUL', false);
        call debug_msg((
            select concat_ws('',
                'company_id ', lastCompanyId,
                ' order_status id ', LAST_INSERT_ID(),
                ' inserted a new status with title Успешный')
        ));
    end if;
end$$

delimiter $$
drop procedure if exists orderStatusFix$$
create procedure orderStatusFix()
begin
    # Loop data
    declare lastCompanyId bigint(20) default -1;
    declare lastOrderNumber int(11) default 0;
    declare isCanceledExists tinyint default false;
    declare isReceivedExists tinyint default false;

    # Cursor data
    declare id_ bigint(20);
    declare companyId bigint(20);
    declare title varchar(255);
    declare orderNumber int(11);

    declare cursorIsDone tinyint default false;
    declare orderStatusCursor cursor for
        select os.id, os.company_id, os.title, os.order_number
        from order_status_test1 os
        order by os.company_id, os.order_number;
    # Fires when all data was fetched
    declare continue handler for not found set cursorIsDone = true;

    # Loop through all order_status rows ordered by company_id
    open orderStatusCursor;
    loopLink:
        loop
            fetch orderStatusCursor into id_, companyId, title, orderNumber;
            if cursorIsDone then
                leave loopLink;
            else
                # If all previous company rows are processed
                if ((companyId != lastCompanyId) and (lastCompanyId != -1)) then
                    # Do create/update for the last company
                    call createOrUpdateOrderStatus(
                            lastCompanyId,
                            lastOrderNumber,
                            isCanceledExists,
                            isReceivedExists
                    );
                    # Reset flags
                    set isCanceledExists = false;
                    set isReceivedExists = false;
                # Do order number correction only within the same company_id
                else
                    if ((orderNumber - lastOrderNumber) != 1) then
                        set @temp := orderNumber;
                        set orderNumber = lastOrderNumber + 1;
                        update order_status_test1 os
                        set os.order_number = orderNumber
                        where os.id = id_;
                        call debug_msg((
                            select concat_ws('',
                                'company_id ', companyId,
                                ' order_status id ', id_,
                                ' updated order_number from ', @temp, ' to ', orderNumber)
                        ));
                    end if;
                end if;

                # Remember last cursor row data
                set lastCompanyId = companyId;
                set lastOrderNumber = orderNumber;
                if (title = 'Отменен') then
                    set isCanceledExists = true;
                end if;
                if (title = 'Получен') then
                    set isReceivedExists = true;
                end if;
            end if;
        end loop;
    # Do create/update for the last company
    call createOrUpdateOrderStatus(
            lastCompanyId,
            lastOrderNumber,
            isCanceledExists,
            isReceivedExists
    );
    close orderStatusCursor;
end$$

delimiter ;
call orderStatusFix();

# Cleanup
drop procedure orderStatusFix;
drop procedure createOrUpdateOrderStatus;
drop procedure debug_msg;
# Don't forget to drop debug_logs table after checking it:
# drop table if exists `debug_logs`;