DELIMITER $$

CREATE FUNCTION SiteDetails() RETURNS VARCHAR(100)
BEGIN
	set @path = concat("$.legal_links[", 0, "].url");

	select json_length(json_extract(data, '$.legal_links[*].url')) into @urlCount
	from site_global
	where label = 'site_details';
	
	set @urlLoopIdx = 0;
	
	urlLoop: loop
		set @path = concat("$.legal_links[", @urlLoopIdx, "].url");
		
		select json_extract(data, @path) into @url
		from site_global
		where label = 'site_details';
		
		/* 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 = 'site_details';
		
		update site_global
		set data = cast(@newJson as JSON)
		where label = 'site_details';		
			
		set @urlLoopIdx = @urlLoopIdx + 1;
			
		if (@urlLoopIdx < @urlCount - 1) then
			iterate urlLoop;
		end if;			
			
		leave urlLoop;
	end loop urlLoop;

	return @urlLoopIdx;
END$$

DELIMITER ;

set @urlsUpdated = SiteDetails();

DROP FUNCTION IF EXISTS SiteDetails;