DO $$
DECLARE
	_path character varying(256) := 'patch-2026_02_17-RepairEventInviteBodyHtml';
	_build integer := 16616;
BEGIN
	IF EXISTS (SELECT 1 FROM public."Versions" WHERE "Path" = _path) THEN
		RETURN;
	END IF;

	IF EXISTS (SELECT 1 FROM public."Versions" WHERE "Build" >= _build) THEN
		RETURN;
	END IF;

	BEGIN
		WITH one_hour AS (
			SELECT "Value" AS base_value
			FROM public."MessageSettings"
			WHERE "Key" = 'Email.OneHour|BodyHtml'
			LIMIT 1
		),
		with_file_links AS (
			SELECT regexp_replace(
				base_value,
				'(?is)(<tr[^>]*>\s*<td[^>]*>\s*<span[^>]*>\s*<a[^>]*@eventview[^>]*>.*?</a>\s*</span>\s*</td>\s*</tr>)',
				E'                     @file_links\n\\1',
				'g'
			) AS value_with_links
			FROM one_hour
		),
		sanitized AS (
			SELECT regexp_replace(
				value_with_links,
				'(?is)<tr[^>]*>\s*<td[^>]*>\s*<span[^>]*>\s*<a[^>]*@eventview[^>]*>.*?</a>\s*</span>\s*</td>\s*</tr>',
				'',
				'g'
			) AS fixed_value
			FROM with_file_links
		)
		UPDATE public."MessageSettings" ms
		SET
			"Value" = s.fixed_value,
			"Timestamp" = timezone('utc'::text, now())
		FROM sanitized s
		WHERE ms."Key" = 'Email.EventInvite|BodyHtml'
		  AND s.fixed_value IS NOT NULL
		  AND (
			ms."Value" IS NULL
			OR ms."Value" !~* '@eventname'
			OR ms."Value" !~* '@eventdate'
			OR ms."Value" !~* '@eventdescription'
			OR ms."Value" !~* '@eventplace'
			OR ms."Value" !~* '@participantsemails'
			OR ms."Value" !~* '@owneremail'
		  );

		INSERT INTO public."Versions" ("Path", "Build")
		VALUES (_path, 0);
	EXCEPTION
		WHEN OTHERS THEN
			RAISE NOTICE '[ERR] %: %', _path, SQLERRM;
	END;
END;
$$;
