-- Protect staff accounts from moderation by delegated roles. begin; create or replace function public.community_moderate_profile(p_target uuid,p_action text,p_reason text) returns void language plpgsql security definer set search_path = '' as $$ declare v_username text; begin if auth.uid() is null or not exists(select 1 from private.hub_owners where user_id=auth.uid()) then raise exception 'Owner access required' using errcode='42501'; end if; if p_target is null or p_action is null or p_action not in ('clear_bio','clear_avatar','clear_banner','hide_profile') or p_reason is null or char_length(btrim(p_reason)) not between 10 and 500 then raise exception 'Invalid moderation request'; end if; if private.staff_rank()<3 and (exists(select 1 from private.staff_roles where user_id=p_target) or exists(select 1 from private.hub_owners where user_id=p_target)) then raise exception 'Staff accounts are protected' using errcode='42501'; end if; select username into v_username from public.community_profiles where user_id=p_target for update; if v_username is null then raise exception 'Profile not found'; end if; if p_action='clear_bio' then update public.community_profiles set bio='',updated_at=now() where user_id=p_target; elsif p_action='clear_avatar' then update public.community_profiles set avatar_url=null,updated_at=now() where user_id=p_target; elsif p_action='clear_banner' then update public.community_profiles set banner_url=null,updated_at=now() where user_id=p_target; elsif p_action='hide_profile' then update public.community_profiles set public_profile=false,updated_at=now() where user_id=p_target; end if; insert into private.community_audit(actor,action,subject,detail) values(auth.uid(),p_action,jsonb_build_object('user_id',p_target,'username',v_username)::text,btrim(p_reason)); end $$; create or replace function public.community_admin(p_operation text,p_payload jsonb default '{}'::jsonb) returns jsonb language plpgsql security definer set search_path = '' as $$ declare v_id bigint; v_user uuid; v_code text; v_state text; v_action text; begin if private.staff_rank() < 1 then raise exception 'Staff permission denied' using errcode='42501'; end if; if (p_operation in ('assign_badge','remove_badge','set_flag','announce') and private.staff_rank()<>3) or (p_operation='moderate_bio' and private.staff_rank()<2) then raise exception 'Operation requires a higher staff role' using errcode='42501'; end if; if p_payload is null or jsonb_typeof(p_payload)<>'object' then raise exception 'Invalid payload'; end if; if p_operation='assign_badge' or p_operation='remove_badge' then v_user := (p_payload->>'user_id')::uuid; v_code := p_payload->>'code'; if v_user is null or not exists(select 1 from public.community_badges where code=v_code) then raise exception 'Invalid badge'; end if; if p_operation='assign_badge' then insert into public.community_user_badges(user_id,code,assigned_by) values(v_user,v_code,auth.uid()) on conflict do nothing; else delete from public.community_user_badges where user_id=v_user and code=v_code; end if; v_action:=p_operation; elsif p_operation='moderate_bio' then v_user := (p_payload->>'user_id')::uuid; if private.staff_rank()<3 and (exists(select 1 from private.staff_roles where user_id=v_user) or exists(select 1 from private.hub_owners where user_id=v_user)) then raise exception 'Staff accounts are protected' using errcode='42501'; end if; if v_user is null or char_length(coalesce(p_payload->>'bio',''))>500 then raise exception 'Invalid bio'; end if; update public.community_profiles set bio=coalesce(p_payload->>'bio',''),updated_at=now() where user_id=v_user; if not found then raise exception 'Profile not found'; end if; v_action:=p_operation; elsif p_operation='review_report' then v_id := (p_payload->>'id')::bigint; v_state:=p_payload->>'state'; if v_state is null or v_state not in ('open','reviewing','resolved','dismissed') or char_length(coalesce(p_payload->>'note',''))>2000 then raise exception 'Invalid report update'; end if; update private.community_reports set state=v_state,staff_note=coalesce(p_payload->>'note',''), reviewed_by=auth.uid(),updated_at=now() where id=v_id; if not found then raise exception 'Report not found'; end if; v_action:=p_operation; elsif p_operation='set_flag' then if (p_payload->>'key') !~ '^[a-z0-9_-]{2,64}$' or jsonb_typeof(p_payload->'enabled')<>'boolean' then raise exception 'Invalid flag'; end if; insert into public.community_flags(key,enabled) values(p_payload->>'key',(p_payload->>'enabled')::boolean) on conflict(key) do update set enabled=excluded.enabled,changed_at=now(); v_action:=p_operation; elsif p_operation='announce' then if char_length(coalesce(p_payload->>'title','')) not between 1 and 120 or char_length(coalesce(p_payload->>'body','')) not between 1 and 2000 then raise exception 'Invalid announcement'; end if; insert into public.community_announcements(title,body,created_by) values(p_payload->>'title',p_payload->>'body',auth.uid()) returning id into v_id; v_action:=p_operation; else raise exception 'Unknown operation'; end if; insert into private.community_audit(actor,action,subject,detail) values(auth.uid(),v_action,left(p_payload::text,300),null); return jsonb_build_object('ok',true,'id',v_id); end $$; create or replace function public.admin_review_suspension_appeal(p_id bigint,p_state text,p_note text) returns void language plpgsql security definer set search_path = '' as $$ declare v_user uuid; begin if private.staff_rank() < 1 then raise exception 'Staff permission denied' using errcode='42501'; end if; if p_id is null or p_state is null or p_state not in ('open','reviewing','accepted','denied') or p_note is null or char_length(btrim(p_note)) not between 5 and 2000 then raise exception 'Provide a status and staff note (5–2000 characters)'; end if; if private.staff_rank()<3 and exists( select 1 from private.suspension_appeals a join private.staff_roles r on r.user_id=a.user_id where a.id=p_id) then raise exception 'Staff appeals require owner review' using errcode='42501'; end if; update private.suspension_appeals set state=p_state,staff_note=btrim(p_note), reviewed_by=auth.uid(),updated_at=now() where id=p_id returning user_id into v_user; if v_user is null then raise exception 'Appeal not found'; end if; -- Acceptance records the decision; unsuspension stays an explicit admin action. insert into private.community_audit(actor,action,subject,detail) values(auth.uid(),'review_suspension_appeal', pg_catalog.jsonb_build_object('appeal_id',p_id,'user_id',v_user,'state',p_state)::text, btrim(p_note)); end $$; commit;