-- Allow images to zoom out while preserving existing framing data. begin; alter table public.community_profile_layout drop constraint if exists community_profile_layout_avatar_zoom_check, drop constraint if exists community_profile_layout_banner_zoom_check; alter table public.community_profile_layout add constraint community_profile_layout_avatar_zoom_check check(avatar_zoom between 0.35 and 3.00), add constraint community_profile_layout_banner_zoom_check check(banner_zoom between 0.35 and 3.00); create or replace function public.community_save_framing( p_avatar_x integer, p_avatar_y integer, p_avatar_zoom numeric, p_banner_x integer, p_banner_y integer, p_banner_zoom numeric) returns void language plpgsql security definer set search_path = '' as $$ begin if auth.uid() is null or not exists (select 1 from public.community_profiles p join public.hub_access a on a.user_id = p.user_id where p.user_id = auth.uid() and a.status = 'approved') or exists (select 1 from private.hub_suspensions s where s.user_id = auth.uid() and s.lifted_at is null and (s.suspended_until is null or s.suspended_until > now())) then raise exception 'Approved profile required' using errcode = '42501'; end if; if p_avatar_x is null or p_avatar_x not between 0 and 100 or p_avatar_y is null or p_avatar_y not between 0 and 100 or p_banner_x is null or p_banner_x not between 0 and 100 or p_banner_y is null or p_banner_y not between 0 and 100 or p_avatar_zoom is null or p_avatar_zoom not between 0.35 and 3 or p_banner_zoom is null or p_banner_zoom not between 0.35 and 3 then raise exception 'Invalid framing values'; end if; insert into public.community_profile_layout( user_id,avatar_x,avatar_y,avatar_zoom,banner_x,banner_y,banner_zoom) values(auth.uid(),p_avatar_x,p_avatar_y,p_avatar_zoom,p_banner_x,p_banner_y,p_banner_zoom) on conflict(user_id) do update set avatar_x=excluded.avatar_x, avatar_y=excluded.avatar_y, avatar_zoom=excluded.avatar_zoom, banner_x=excluded.banner_x, banner_y=excluded.banner_y, banner_zoom=excluded.banner_zoom, updated_at=now(); end; $$; commit;