create or replace function public.hub_report_content(p_kind text,p_id uuid,p_category text,p_details text) returns bigint language plpgsql security definer set search_path='' as $$ declare u uuid:=auth.uid(); subject uuid; evidence jsonb; report_id bigint; begin if u is null or not public.community_media_upload_allowed() then raise exception 'Approved access required' using errcode='42501'; end if; if p_kind is null or p_kind not in ('profile','game','request','reply') or p_id is null or p_category is null or p_category not in ('bio','avatar','banner','behavior','other') or p_details is null or char_length(btrim(p_details)) not between 10 and 2000 then raise exception 'Invalid report'; end if; if p_kind='profile' then select p.user_id,to_jsonb(p)||jsonb_build_object('theme',to_jsonb(t)) into subject,evidence from public.community_profiles p left join private.hub_profile_themes t on t.user_id=p.user_id where p.user_id=p_id and p.public_profile and exists(select 1 from public.hub_access a where a.user_id=p.user_id and a.status='approved') and not exists(select 1 from private.hub_suspensions z where z.user_id=p.user_id and z.lifted_at is null and (z.suspended_until is null or z.suspended_until>now())); elsif p_kind='game' then select g.created_by,to_jsonb(g) into subject,evidence from private.hub_games g where g.id=p_id and (g.published or g.created_by=u or private.staff_rank()>=1); else select case when p_kind='request' then s.user_id else null end, case when p_kind='request' then jsonb_build_object('title',s.title,'body',s.body,'kind',s.kind,'created_at',s.created_at) else jsonb_build_object('title',s.title,'reply',s.reply,'updated_at',s.updated_at) end into subject,evidence from private.hub_suggestions s where s.id=p_id and (s.user_id=u or private.staff_rank()>=1) and (p_kind<>'reply' or s.reply<>''); end if; if evidence is null then raise exception 'Content unavailable for reporting'; end if; perform pg_catalog.pg_advisory_xact_lock(pg_catalog.hashtext('report:'||u::text)); if exists(select 1 from private.community_reports r where r.reporter=u and r.created_at>now()-interval '60 seconds') then raise exception 'Please wait a minute before sending another report'; end if; if (select count(*) from private.community_reports r where r.reporter=u and r.created_at>now()-interval '24 hours')>=5 then raise exception 'Daily report limit reached'; end if; if exists(select 1 from private.community_reports r where r.reporter=u and r.content_kind=p_kind and coalesce(r.content_id,r.target)=p_id and r.category=p_category and r.state in ('open','reviewing')) then raise exception 'You already have an active report for this content and reason'; end if; insert into private.community_reports(reporter,target,category,details,content_kind,content_id,content_snapshot) values(u,subject,p_category,btrim(p_details),p_kind,p_id,evidence) returning id into report_id; return report_id; end $$; create or replace function public.hub_content_reports(p_staff boolean default false,p_offset integer default 0) returns jsonb language plpgsql stable security definer set search_path='' as $$ begin if auth.uid() is null or not public.community_media_upload_allowed() then raise exception 'Approved access required' using errcode='42501'; end if; if p_staff and private.staff_rank()<1 then raise exception 'Staff permission denied' using errcode='42501'; end if; return coalesce((select jsonb_agg(x.data order by x.created_at desc,x.id desc) from ( select r.id,r.created_at, jsonb_build_object('id',r.id,'category',r.category,'details',r.details,'state',r.state,'created_at',r.created_at,'content_kind',r.content_kind,'content_id',coalesce(r.content_id,r.target),'content_title',coalesce(r.content_snapshot->>'title',r.content_snapshot->>'display_name',case when p.public_profile and exists(select 1 from public.hub_access a where a.user_id=p.user_id and a.status='approved') then p.display_name end,r.content_kind)) || case when p_staff then jsonb_build_object('reporter',r.reporter,'target',r.target,'staff_note',r.staff_note,'snapshot',r.content_snapshot) else '{}'::jsonb end as data from private.community_reports r left join public.community_profiles p on p.user_id=r.target where p_staff or r.reporter=auth.uid() order by r.created_at desc,r.id desc limit 50 offset greatest(0,least(10000,coalesce(p_offset,0))) ) x),'[]'); end $$;