\pset tuples_only on
\pset format unaligned
select 'total|' || count(*) from public.request_audit_logs where created_at >= timezone('Asia/Shanghai', date_trunc('day', now() at time zone 'Asia/Shanghai'));
select 'valid_json_candidate|' || count(*) from public.request_audit_logs where created_at >= timezone('Asia/Shanghai', date_trunc('day', now() at time zone 'Asia/Shanghai')) and request_body not like '%(truncated)%';
select 'input_type|' || t || '|' || count(*) from (
  select coalesce(jsonb_typeof(request_body::jsonb->'input'),'NULL') t
  from public.request_audit_logs
  where created_at >= timezone('Asia/Shanghai', date_trunc('day', now() at time zone 'Asia/Shanghai')) and request_body not like '%(truncated)%'
) s group by t order by t;
select 'roles|' || r || '|' || count(*) from (
  select coalesce(elem->>'role','NULL') r
  from public.request_audit_logs l
  cross join lateral jsonb_array_elements(case when jsonb_typeof(l.request_body::jsonb->'input')='array' then l.request_body::jsonb->'input' else '[]'::jsonb end) elem
  where l.created_at >= timezone('Asia/Shanghai', date_trunc('day', now() at time zone 'Asia/Shanghai')) and l.request_body not like '%(truncated)%'
) s group by r order by r;
COPY (
with user_msgs as (
  select l.id,
         to_char(l.created_at at time zone 'Asia/Shanghai','YYYY-MM-DD HH24:MI:SS') as created_at,
         l.path,l.model,l.account_id,l.has_tools,l.has_thinking,l.has_stream,
         ord,
         coalesce(
           string_agg(coalesce(c->>'text', c->>'input_text', c->>'output_text', c::text), E'\n' order by cord),
           elem->>'content',
           elem->>'text'
         ) as user_text
  from public.request_audit_logs l
  cross join lateral jsonb_array_elements(case when jsonb_typeof(l.request_body::jsonb->'input')='array' then l.request_body::jsonb->'input' else '[]'::jsonb end) with ordinality as e(elem,ord)
  left join lateral jsonb_array_elements(case when jsonb_typeof(elem->'content')='array' then elem->'content' else '[]'::jsonb end) with ordinality as cc(c,cord) on true
  where l.created_at >= timezone('Asia/Shanghai', date_trunc('day', now() at time zone 'Asia/Shanghai'))
    and l.request_body not like '%(truncated)%'
    and elem->>'role'='user'
  group by l.id,l.created_at,l.path,l.model,l.account_id,l.has_tools,l.has_thinking,l.has_stream,ord,elem
), last_user as (
  select distinct on (id) * from user_msgs where nullif(btrim(user_text),'') is not null order by id, ord desc
)
select json_build_object('id',id,'created_at',created_at,'path',path,'model',model,'account_id',account_id,'has_tools',has_tools,'has_thinking',has_thinking,'has_stream',has_stream,'user_text',left(user_text,2000))::text
from last_user
order by id asc
) TO STDOUT WITH CSV;
