DELIMITER $$

CREATE FUNCTION RemoveSlash(tableName varchar(100), path varchar(1000) ) RETURNS VARCHAR(1000)
BEGIN
    select json_extract(data, path) into @url
    from site_global
    where label = tableName;

    /* Remove quotes & trailing slash from url */
    set @url = replace(@url, '"', '');
    set @url = if(length(@url) > 1 and substring(@url, length(@url), 1) = '/', substring(@url, 1, length(@url) - 1), @url);

    select json_set(data, path, @url) into @newJson
    from site_global
    where label = tableName;

    update site_global
    set data = cast(@newJson as JSON)
    where label = tableName;

    return @url;
END$$

CREATE FUNCTION MainMenu() RETURNS INT
BEGIN
    /** Account for item groups within the evergreenPages section **/
    select json_length(json_extract(data, '$.evergreenPages[*]')) into @evergreenCount
    from site_global
    where label = 'main_menu';

    set @evergreenCountIdx = 0;
    evergreenSectionLoop: loop
        /** Parse and update evergreen pages **/
        set @path = concat("$.evergreenPages[", @evergreenCountIdx, "].items[*].slug");
        select json_length(json_extract(data, @path)) into @urlCount
        from site_global
        where label = 'main_menu';

        set @urlLoopIdx = 0;
        urlLoop: loop
            set @path = concat("$.evergreenPages[", @evergreenCountIdx, "].items[", @urlLoopIdx, "].slug");
            set @result = RemoveSlash('main_menu', @path);

            set @urlLoopIdx = @urlLoopIdx + 1;
            if (@urlLoopIdx < @urlCount) then
                iterate urlLoop;
            end if;

            leave urlLoop;
        end loop urlLoop;

        set @evergreenCountIdx = @evergreenCountIdx + 1;
        if (@evergreenCountIdx < @evergreenCount) then
            iterate evergreenSectionLoop;
        end if;

        leave evergreenSectionLoop;
    end loop evergreenSectionLoop;

    /** Parse and update standard menus **/
    select json_length(json_extract(data, '$.menus[*]')) into @menuCount
    from site_global
    where label = 'main_menu';

    set @menuLoopIdx = 0;
    menuLoop: loop
        /** Each menu can have a url **/
        set @path = concat("$.menus[", @menuLoopIdx, "].url");
        select json_extract(data, @path) into @url
        from site_global
        where label = 'main_menu';

        if (@url is not null) then
            set @result = RemoveSlash('main_menu', @path);
        end if;

        /** Each menu can have a number of columns **/
        set @path = concat("$.menus[", @menuLoopIdx, "].columns");

        select json_extract(data, @path) into @columns
        from site_global
        where label = 'main_menu';

        if (@columns is not null) then
            select json_length(json_extract(data, @path)) into @columnCount
            from site_global
            where label = 'main_menu';

            set @columnLoopIdx = 0;
            columnLoop: loop
                /** Each column has a number of items **/
                set @path = concat("$.menus[", @menuLoopIdx, "].columns[", @columnLoopIdx, "].items");
                select json_length(json_extract(data, @path)) into @itemCount
                from site_global
                where label = 'main_menu';

                set @itemLoopIdx = 0;
                itemLoop: loop
                    /** Each item has a slug **/
                    set @path = concat("$.menus[", @menuLoopIdx, "].columns[", @columnLoopIdx, "].items[", @itemLoopIdx,"].slug");
                    set @result = RemoveSlash('main_menu', @path);

                    /** Continue item loop **/
                    set @itemLoopIdx = @itemLoopIdx + 1;
                    if (@itemLoopIdx < @itemCount) then
                        iterate itemLoop;
                    end if;

                    leave itemLoop;
                end loop itemLoop;


                /** Continue column loop **/
                set @columnLoopIdx = @columnLoopIdx + 1;
                if (@columnLoopIdx < @columnCount) then
                    iterate columnLoop;
                end if;

                leave columnLoop;
            end loop columnLoop;
        end if;

        /** Continue menu loop **/
        set @menuLoopIdx = @menuLoopIdx + 1;
        if (@menuLoopIdx <  @menuCount) then
            iterate menuLoop;
        end if;

        leave menuLoop;
    end loop menuLoop;

    return @menuLoopIdx;
END$$

DELIMITER ;

set @urlsUpdated = MainMenu();

DROP FUNCTION IF EXISTS RemoveSlash;
DROP FUNCTION IF EXISTS MainMenu;