-- Allow the owner to revoke roles even if a staff account has since been blocked. begin; create or replace function public.staff_set_role(p_user uuid,p_role text) returns void language plpgsql security definer set search_path = '' as $$ begin if private.staff_rank()<>3 then raise exception 'Owner access required' using errcode='42501'; end if; if p_user is null or p_user=auth.uid() or exists(select 1 from private.hub_owners o where o.user_id=p_user) then raise exception 'Cannot change owner role' using errcode='42501'; end if; if p_role is not null and p_role not in ('administrator','moderator') then raise exception 'Invalid role'; end if; if p_role is not null and not exists(select 1 from public.hub_access a where a.user_id=p_user and a.status='approved') then raise exception 'Target must have an approved account'; end if; if p_role is null then delete from private.staff_roles where user_id=p_user; else insert into private.staff_roles(user_id,role,assigned_by) values(p_user,p_role,auth.uid()) on conflict(user_id) do update set role=excluded.role,assigned_by=excluded.assigned_by,assigned_at=now(); end if; insert into private.community_audit(actor,action,subject,detail) values(auth.uid(),'set_staff_role',pg_catalog.jsonb_build_object('user_id',p_user,'role',p_role)::text,null); end $$; commit;