BEGIN READ ONLY;
\pset tuples_only on
\pset format unaligned
WITH target_user AS (SELECT * FROM public.users WHERE lower(email)=lower('wangg4660@gmail.com') AND deleted_at IS NULL)
SELECT json_build_object(
  'kind','user',
  'id',id,'email',email,'role',role,'balance',balance,'concurrency',concurrency,'status',status,
  'created_at_shanghai',to_char(created_at at time zone 'Asia/Shanghai','YYYY-MM-DD HH24:MI:SS'),
  'updated_at_shanghai',to_char(updated_at at time zone 'Asia/Shanghai','YYYY-MM-DD HH24:MI:SS'),
  'sora_storage_quota_bytes',sora_storage_quota_bytes,'sora_storage_used_bytes',sora_storage_used_bytes,
  'invite_code',invite_code,'inviter_user_id',inviter_user_id
)::text FROM target_user;

WITH target_user AS (SELECT * FROM public.users WHERE lower(email)=lower('wangg4660@gmail.com') AND deleted_at IS NULL), target_keys AS (
  SELECT k.*, g.name AS group_name, g.platform AS group_platform, g.status AS group_status,
         g.subscription_type, g.quota_billing_mode, g.daily_limit_usd, g.weekly_limit_usd, g.monthly_limit_usd,
         g.daily_limit_tokens, g.weekly_limit_tokens, g.monthly_limit_tokens
  FROM public.api_keys k
  LEFT JOIN public.groups g ON g.id=k.group_id
  WHERE k.user_id IN (SELECT id FROM target_user) AND k.deleted_at IS NULL
)
SELECT json_build_object(
  'kind','api_key','id',id,'user_id',user_id,'name',name,'group_id',group_id,'group_name',group_name,'group_platform',group_platform,
  'group_status',group_status,'subscription_type',subscription_type,'quota_billing_mode',quota_billing_mode,
  'status',status,'quota',quota,'quota_used',quota_used,
  'rate_limit_5h',rate_limit_5h,'rate_limit_1d',rate_limit_1d,'rate_limit_7d',rate_limit_7d,
  'usage_5h',usage_5h,'usage_1d',usage_1d,'usage_7d',usage_7d,
  'overage_balance_enabled',overage_balance_enabled,'overage_group_id',overage_group_id,
  'last_used_at_shanghai',to_char(last_used_at at time zone 'Asia/Shanghai','YYYY-MM-DD HH24:MI:SS'),
  'created_at_shanghai',to_char(created_at at time zone 'Asia/Shanghai','YYYY-MM-DD HH24:MI:SS'),
  'expires_at_shanghai',to_char(expires_at at time zone 'Asia/Shanghai','YYYY-MM-DD HH24:MI:SS')
)::text FROM target_keys ORDER BY id;

WITH target_user AS (SELECT * FROM public.users WHERE lower(email)=lower('wangg4660@gmail.com') AND deleted_at IS NULL), target_groups AS (
  SELECT g.* FROM public.user_allowed_groups uag JOIN public.groups g ON g.id=uag.group_id WHERE uag.user_id IN (SELECT id FROM target_user)
)
SELECT json_build_object(
  'kind','allowed_group','id',id,'name',name,'platform',platform,'status',status,'subscription_type',subscription_type,
  'quota_billing_mode',quota_billing_mode,'daily_limit_usd',daily_limit_usd,'weekly_limit_usd',weekly_limit_usd,'monthly_limit_usd',monthly_limit_usd,
  'daily_limit_tokens',daily_limit_tokens,'weekly_limit_tokens',weekly_limit_tokens,'monthly_limit_tokens',monthly_limit_tokens,
  'claude_code_only',claude_code_only,'allow_messages_dispatch',allow_messages_dispatch,
  'supported_model_scopes',supported_model_scopes
)::text FROM target_groups ORDER BY id;

WITH target_user AS (SELECT id FROM public.users WHERE lower(email)=lower('wangg4660@gmail.com') AND deleted_at IS NULL),
base AS (
  SELECT e.*, k.name AS api_key_name, k.status AS api_key_status, g.name AS group_name, g.platform AS group_platform, a.name AS account_name, a.status AS account_status_live
  FROM public.ops_error_logs e
  LEFT JOIN public.api_keys k ON k.id=e.api_key_id
  LEFT JOIN public.groups g ON g.id=e.group_id
  LEFT JOIN public.accounts a ON a.id=e.account_id
  WHERE e.user_id IN (SELECT id FROM target_user) AND e.created_at >= now() - interval '7 days'
)
SELECT json_build_object(
  'kind','ops_summary_7d',
  'count',count(*),
  'min_created_at_shanghai',to_char(min(created_at) at time zone 'Asia/Shanghai','YYYY-MM-DD HH24:MI:SS'),
  'max_created_at_shanghai',to_char(max(created_at) at time zone 'Asia/Shanghai','YYYY-MM-DD HH24:MI:SS')
)::text FROM base;

WITH target_user AS (SELECT id FROM public.users WHERE lower(email)=lower('wangg4660@gmail.com') AND deleted_at IS NULL),
base AS (
  SELECT e.*, k.name AS api_key_name, k.status AS api_key_status, g.name AS group_name, g.platform AS group_platform, a.name AS account_name, a.status AS account_status_live,
         regexp_replace(left(coalesce(e.upstream_error_message,e.error_message,e.upstream_error_detail,e.error_body,''),260),'[\n\r\t]+',' ','g') AS sample_message
  FROM public.ops_error_logs e
  LEFT JOIN public.api_keys k ON k.id=e.api_key_id
  LEFT JOIN public.groups g ON g.id=e.group_id
  LEFT JOIN public.accounts a ON a.id=e.account_id
  WHERE e.user_id IN (SELECT id FROM target_user) AND e.created_at >= now() - interval '7 days'
)
SELECT json_build_object(
  'kind','ops_group_7d',
  'count',count(*),'last_24h',count(*) FILTER (WHERE created_at >= now()-interval '24 hours'),
  'api_key_id',api_key_id,'api_key_name',api_key_name,'api_key_status',api_key_status,
  'group_id',group_id,'group_name',group_name,'group_platform',group_platform,
  'account_id',account_id,'account_name',account_name,'account_status_live',account_status_live,
  'model',model,'request_path',request_path,
  'error_phase',error_phase,'error_owner',error_owner,'error_type',error_type,
  'status_code',status_code,'upstream_status_code',upstream_status_code,
  'provider_error_code',provider_error_code,'provider_error_type',provider_error_type,'network_error_type',network_error_type,
  'is_business_limited',bool_or(is_business_limited),
  'min_created_at_shanghai',to_char(min(created_at) at time zone 'Asia/Shanghai','YYYY-MM-DD HH24:MI:SS'),
  'max_created_at_shanghai',to_char(max(created_at) at time zone 'Asia/Shanghai','YYYY-MM-DD HH24:MI:SS'),
  'sample_message',min(sample_message)
)::text
FROM base
GROUP BY api_key_id,api_key_name,api_key_status,group_id,group_name,group_platform,account_id,account_name,account_status_live,model,request_path,error_phase,error_owner,error_type,status_code,upstream_status_code,provider_error_code,provider_error_type,network_error_type
ORDER BY count(*) DESC, max(created_at) DESC
LIMIT 30;

WITH target_user AS (SELECT id FROM public.users WHERE lower(email)=lower('wangg4660@gmail.com') AND deleted_at IS NULL),
base AS (
  SELECT e.*, k.name AS api_key_name, g.name AS group_name, a.name AS account_name,
         regexp_replace(left(coalesce(e.upstream_error_message,e.error_message,e.upstream_error_detail,e.error_body,''),500),'[\n\r\t]+',' ','g') AS sample_message
  FROM public.ops_error_logs e
  LEFT JOIN public.api_keys k ON k.id=e.api_key_id
  LEFT JOIN public.groups g ON g.id=e.group_id
  LEFT JOIN public.accounts a ON a.id=e.account_id
  WHERE e.user_id IN (SELECT id FROM target_user) AND e.created_at >= now() - interval '7 days'
)
SELECT json_build_object(
  'kind','ops_recent',
  'id',id,'request_id',request_id,'created_at_shanghai',to_char(created_at at time zone 'Asia/Shanghai','YYYY-MM-DD HH24:MI:SS'),
  'api_key_id',api_key_id,'api_key_name',api_key_name,'group_id',group_id,'group_name',group_name,'account_id',account_id,'account_name',account_name,
  'platform',platform,'model',model,'request_path',request_path,'stream',stream,
  'error_phase',error_phase,'error_owner',error_owner,'error_type',error_type,'severity',severity,
  'status_code',status_code,'upstream_status_code',upstream_status_code,
  'provider_error_code',provider_error_code,'provider_error_type',provider_error_type,'retry_after_seconds',retry_after_seconds,
  'duration_ms',duration_ms,'sample_message',sample_message,
  'upstream_errors',upstream_errors
)::text FROM base ORDER BY created_at DESC LIMIT 25;
COMMIT;
