-- Profile-specific image framing. Apply after 202611120004_profile_media.sql. -- Framing does not modify uploaded files and does not grant access to private profiles. begin; create table public.community_profile_layout ( user_id uuid primary key references public.community_profiles(user_id) on delete cascade, avatar_x smallint not null default 50 check (avatar_x between 0 and 100), avatar_y smallint not null default 50 check (avatar_y between 0 and 100), avatar_zoom numeric(3,2) not null default 1.00 check (avatar_zoom between 1.00 and 3.00), banner_x smallint not null default 50 check (banner_x between 0 and 100), banner_y smallint not null default 50 check (banner_y between 0 and 100), banner_zoom numeric(3,2) not null default 1.00 check (banner_zoom between 1.00 and 3.00), updated_at timestamptz not null default now() ); alter table public.community_profile_layout enable row level security; revoke all on public.community_profile_layout from public, anon, authenticated; -- Enforce the exact same visibility rules as community_profile() for layout reads. create function public.community_profile_framing(p_username text) returns table (avatar_x smallint, avatar_y smallint, avatar_zoom numeric, banner_x smallint, banner_y smallint, banner_zoom numeric) language sql stable security definer set search_path = '' as $$ select coalesce(l.avatar_x, 50)::smallint, coalesce(l.avatar_y, 50)::smallint, coalesce(l.avatar_zoom, 1.00)::numeric, coalesce(l.banner_x, 50)::smallint, coalesce(l.banner_y, 50)::smallint, coalesce(l.banner_zoom, 1.00)::numeric from public.community_profile(p_username) p left join public.community_profile_layout l on l.user_id = p.user_id; $$; create 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 1 and 3 or p_banner_zoom is null or p_banner_zoom not between 1 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; $$; revoke all on function public.community_profile_framing(text), public.community_save_framing(integer,integer,numeric,integer,integer,numeric) from public, anon, authenticated; grant execute on function public.community_profile_framing(text) to anon, authenticated; grant execute on function public.community_save_framing(integer,integer,numeric,integer,integer,numeric) to authenticated; commit;