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$$

DELIMITER ;

DELIMITER $$

CREATE FUNCTION EventPageBadges() RETURNS INT
BEGIN
	/** Parse and update evergreen pages **/ 
	select json_length(json_extract(data, '$.badges')) into @urlCount
	from site_global
	where label = 'event_page_badges';
	
	set @urlLoopIdx = 0;
	urlLoop: loop
		set @path = concat("$.badges[", @urlLoopIdx, "].url");	
		set @result = RemoveSlash('event_page_badges', @path);
			
		set @urlLoopIdx = @urlLoopIdx + 1;
		if (@urlLoopIdx < @urlCount) then
			iterate urlLoop;
		end if;			
			
		leave urlLoop;
	end loop urlLoop;

	return @urlLoopIdx;
END$$

DELIMITER ;

set @urlsUpdated = EventPageBadges();

DROP FUNCTION IF EXISTS RemoveSlash;
DROP FUNCTION IF EXISTS EventPageBadges;