DO $$
    DECLARE _path character varying(256) = 'patch-2025_12_04-EmailFolders_RemovingDuplicates';
BEGIN
    IF EXISTS (SELECT "Id" FROM public."Versions" WHERE "Path" = _path) THEN
        RETURN;
    END IF;

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

    BEGIN
	update public."EmailFolders" set "OwnerId"=-"OwnerId" where "OwnerId" < 0;

	with duplicates as (
	select ef."Id" as value, 
	ef."OwnerId", 
	ef."UserEmailId", 
	ef."Type",
	ef."UidValidity",
	min(
	CASE 
		WHEN ef."Type" != 2 AND ef."UidValidity" <= 0 THEN NULL 
		ELSE ef."Id" 
	END
	) over (partition by ef."OwnerId", ef."UserEmailId", ef."Type") as min_val
	from public."EmailFolders" AS ef
	where "Status" != 2 and (ef."OwnerId", ef."UserEmailId", ef."Type" )
		in (select ef2."OwnerId", ef2."UserEmailId", ef2."Type"
		from public."EmailFolders" AS ef2
		where ef2."Type" <> 1024 and ef2."ParentId" is null and "Status" != 2
		group by  ef2."OwnerId", ef2."UserEmailId", ef2."Type"
		having count(*) > 1)
),
moved as (
	update public."EmailMessages" 
	set "FolderId" = d.min_val
	from duplicates d
	where "FolderId" = d.value and d.value <> d.min_val and "FolderId" <> d.min_val
	returning 1
)
update public."EmailFolders" 
set "Status" = 2
from duplicates d
where "Id" = d.value and "Id"<> d.min_val and d.value <> d.min_val;

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