-- Staff privileges are separate from cosmetic community badges. -- Apply ONLY to ZeyadsStuff-Test until validated. begin; create table private.staff_roles ( user_id uuid primary key references auth.users(id) on delete cascade, role text not null check(role in ('administrator','moderator')), assigned_by uuid not null references auth.users(id), assigned_at timestamptz not null default now() ); alter table private.staff_roles enable row level security; revoke all on private.staff_roles from public,anon,authenticated; create function private.staff_rank() returns integer language sql stable security definer set search_path = '' as $$ select case when auth.uid() is null then 0 when not exists(select 1 from public.hub_access a where a.user_id=auth.uid() and a.status='approved') then 0 when 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 0 when exists(select 1 from private.hub_owners o where o.user_id=auth.uid()) then 3 when exists(select 1 from private.staff_roles r where r.user_id=auth.uid() and r.role='administrator') then 2 when exists(select 1 from private.staff_roles r where r.user_id=auth.uid() and r.role='moderator') then 1 else 0 end; $$; revoke all on function private.staff_rank() from public,anon,authenticated; create function public.staff_my_role() returns text language sql stable security definer set search_path = '' as $$ select case private.staff_rank() when 3 then 'owner' when 2 then 'administrator' when 1 then 'moderator' else 'member' end; $$; create function public.staff_list_roles() returns table(user_id uuid,email text,role text,assigned_at timestamptz) language plpgsql stable security definer set search_path = '' as $$ begin if private.staff_rank()<>3 then raise exception 'Owner access required' using errcode='42501'; end if; return query select r.user_id,u.email::text,r.role,r.assigned_at from private.staff_roles r join auth.users u on u.id=r.user_id order by r.assigned_at desc; end $$; create 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 $$; revoke all on function public.staff_my_role(),public.staff_list_roles(),public.staff_set_role(uuid,text) from public,anon,authenticated; grant execute on function public.staff_my_role(),public.staff_list_roles(),public.staff_set_role(uuid,text) to authenticated; create or replace function public.review_access(target_user uuid, next_status text) returns void language plpgsql security definer set search_path = '' as $$ declare previous text; begin if private.staff_rank() < 2 then raise exception 'Staff permission denied' using errcode='42501'; end if; if private.staff_rank()<3 and exists(select 1 from private.staff_roles r where r.user_id=target_user) then raise exception 'Staff accounts are protected' using errcode='42501'; end if; if next_status not in ('pending','approved','blocked') or next_status is null then raise exception 'Invalid access status'; end if; if exists(select 1 from private.hub_owners o where o.user_id = target_user) then raise exception 'Owner access must be managed outside the app'; end if; select status into strict previous from public.hub_access where user_id = target_user for update; update public.hub_access set status = next_status, updated_at = now() where user_id = target_user; insert into private.access_audit(actor,target,previous_status,next_status) values(auth.uid(),target_user,previous,next_status); end $$; create or replace function public.admin_global_stats() returns table(total_accounts bigint,approved_accounts bigint,pending_accounts bigint, blocked_accounts bigint,active_suspensions bigint) language plpgsql stable security definer set search_path = '' as $$ begin if private.staff_rank() < 2 then raise exception 'Staff permission denied' using errcode='42501'; end if; return query select count(*)::bigint, count(*) filter(where a.status='approved')::bigint, count(*) filter(where a.status='pending')::bigint, count(*) filter(where a.status='blocked')::bigint, count(*) filter(where s.user_id is not null and s.lifted_at is null and (s.suspended_until is null or s.suspended_until>now()))::bigint from public.hub_access a left join private.hub_suspensions s on s.user_id=a.user_id; end $$; create or replace function public.admin_list_accounts( p_search text default '', p_filter text default 'all', p_offset integer default 0, p_page_size integer default 30 ) returns table( user_id uuid, email text, status text, is_owner boolean, is_suspended boolean, suspension_reason text, suspended_until timestamptz, created_at timestamptz, total_count bigint ) language plpgsql stable security definer set search_path = '' as $$ begin if private.staff_rank() < 2 then raise exception 'Staff permission denied' using errcode='42501'; end if; if char_length(coalesce(p_search,'')) > 120 or p_filter not in ('all','pending','approved','blocked','suspended') or p_filter is null or p_offset is null or p_offset < 0 or p_page_size is null or p_page_size < 1 or p_page_size > 100 then raise exception 'Invalid account search'; end if; return query with people as ( select a.user_id, u.email::text as email, a.status, exists(select 1 from private.hub_owners o where o.user_id = a.user_id) as is_owner, (s.user_id is not null and s.lifted_at is null and (s.suspended_until is null or s.suspended_until > now())) as is_suspended, s.reason as suspension_reason, s.suspended_until, a.created_at from public.hub_access a join auth.users u on u.id = a.user_id left join private.hub_suspensions s on s.user_id = a.user_id where position(lower(btrim(coalesce(p_search,''))) in lower(coalesce(u.email,''))) > 0 ) select p.user_id, p.email, p.status, p.is_owner, p.is_suspended, case when p.is_suspended then p.suspension_reason else null::text end, case when p.is_suspended then p.suspended_until else null::timestamptz end, p.created_at, count(*) over()::bigint from people p where p_filter = 'all' or (p_filter = 'pending' and p.status = 'pending') or (p_filter = 'approved' and p.status = 'approved' and not p.is_suspended) or (p_filter = 'blocked' and p.status = 'blocked') or (p_filter = 'suspended' and p.is_suspended) order by p.created_at desc, p.user_id limit p_page_size offset p_offset; end $$; create or replace function public.admin_suspend( p_target uuid, p_reason text, p_hours integer default null ) returns void language plpgsql security definer set search_path = '' as $$ declare target_status text; until_at timestamptz; begin if private.staff_rank() < 2 then raise exception 'Staff permission denied' using errcode='42501'; end if; if private.staff_rank()<3 and exists(select 1 from private.staff_roles r where r.user_id=p_target) then raise exception 'Staff accounts are protected' using errcode='42501'; end if; if p_target is null or exists ( select 1 from private.hub_owners o where o.user_id = p_target ) then raise exception 'Owner accounts cannot be suspended' using errcode = '42501'; end if; if p_reason is null or char_length(btrim(p_reason)) not between 5 and 500 or (p_hours is not null and p_hours not in (24, 168, 720)) then raise exception 'Provide a reason and a supported suspension duration'; end if; select a.status into target_status from public.hub_access a where a.user_id = p_target for update; if target_status is distinct from 'approved' then raise exception 'Only approved accounts can be suspended'; end if; until_at := case when p_hours is null then null else now() + make_interval(hours => p_hours) end; insert into private.hub_suspensions (user_id, suspended_by, reason, suspended_at, suspended_until, lifted_at) values (p_target, auth.uid(), btrim(p_reason), now(), until_at, null) on conflict (user_id) do update set suspended_by = excluded.suspended_by, reason = excluded.reason, suspended_at = excluded.suspended_at, suspended_until = excluded.suspended_until, lifted_at = null; insert into private.moderation_audit (actor, target, action, reason, suspended_until) values (auth.uid(), p_target, 'suspend', btrim(p_reason), until_at); end $$; create or replace function public.admin_unsuspend(p_target uuid) returns void language plpgsql security definer set search_path = '' as $$ begin if private.staff_rank() < 2 then raise exception 'Staff permission denied' using errcode='42501'; end if; if private.staff_rank()<3 and exists(select 1 from private.staff_roles r where r.user_id=p_target) then raise exception 'Staff accounts are protected' using errcode='42501'; end if; if p_target is null or exists ( select 1 from private.hub_owners o where o.user_id = p_target ) then raise exception 'Owner accounts cannot be modified' using errcode = '42501'; end if; update private.hub_suspensions s set lifted_at = now() where s.user_id = p_target and s.lifted_at is null and (s.suspended_until is null or s.suspended_until > now()); if not found then raise exception 'No active suspension was found'; end if; insert into private.moderation_audit(actor, target, action) values(auth.uid(), p_target, 'unsuspend'); end $$; create or replace function public.admin_moderation_history(p_limit integer default 50) returns table( event_id text, action text, target_user uuid, target_email text, reason text, suspended_until timestamptz, changed_at timestamptz ) language plpgsql stable security definer set search_path = '' as $$ begin if private.staff_rank() < 2 then raise exception 'Staff permission denied' using errcode='42501'; end if; if p_limit is null or p_limit < 1 or p_limit > 200 then raise exception 'Invalid history limit'; end if; return query select events.event_id, events.action, events.target_user, events.target_email, events.reason, events.suspended_until, events.changed_at from ( select 'access-' || a.id::text as event_id, 'set-' || a.next_status as action, a.target as target_user, u.email::text as target_email, null::text as reason, null::timestamptz as suspended_until, a.changed_at from private.access_audit a left join auth.users u on u.id = a.target union all select 'moderation-' || m.id::text, m.action, m.target, u.email::text, m.reason, m.suspended_until, m.changed_at from private.moderation_audit m left join auth.users u on u.id = m.target ) events order by events.changed_at desc, events.event_id desc limit p_limit; end $$; create or replace function public.community_admin_reports(p_limit integer default 50) returns table(id bigint,reporter uuid,target uuid,category text,details text,state text,staff_note text,created_at timestamptz) language plpgsql stable security definer set search_path = '' as $$ begin if private.staff_rank() < 1 then raise exception 'Staff permission denied' using errcode='42501'; end if; if p_limit is null or p_limit<1 or p_limit>100 then raise exception 'Invalid limit'; end if; return query select r.id,r.reporter,r.target,r.category,r.details,r.state,r.staff_note,r.created_at from private.community_reports r order by r.created_at desc limit p_limit; end $$; create or replace function public.community_admin_audit(p_limit integer default 50) returns table(actor uuid,action text,subject text,created_at timestamptz) language plpgsql stable security definer set search_path = '' as $$ begin if private.staff_rank() < 2 then raise exception 'Staff permission denied' using errcode='42501'; end if; if p_limit is null or p_limit<1 or p_limit>100 then raise exception 'Invalid limit'; end if; return query select a.actor,a.action,a.subject,a.created_at from private.community_audit a order by a.created_at desc limit p_limit; end $$; 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 private.staff_rank() < 1 then raise exception 'Staff permission denied' 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; 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.admin_suspension_appeals(p_limit integer default 50) returns table(id bigint,user_id uuid,email text,message text,state text,staff_note text, suspension_until timestamptz,created_at timestamptz) language plpgsql stable security definer set search_path = '' as $$ begin if private.staff_rank() < 1 then raise exception 'Staff permission denied' using errcode='42501'; end if; if p_limit is null or p_limit not between 1 and 100 then raise exception 'Invalid limit'; end if; return query select a.id,a.user_id,u.email::text,a.message,a.state,a.staff_note, s.suspended_until,a.created_at from private.suspension_appeals a join auth.users u on u.id=a.user_id left join private.hub_suspensions s on s.user_id=a.user_id order by a.created_at desc limit p_limit; 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; 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 $$; 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 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 $$; commit;