\pset format aligned
\pset pager off
with bad as (
  select id, created_at, user_id, api_key_id, account_id, group_id, subscription_id, model, coalesce(billing_model,'') billing_model,
         input_tokens, output_tokens, cache_read_tokens,
         input_cost, output_cost, cache_read_cost, total_cost,
         (input_tokens*0.75/1000000.0 + output_tokens*4.5/1000000.0 + cache_read_tokens*0.075/1000000.0) as expected_mini_cost
  from usage_logs
  where model='gpt-5.4-mini'
    and coalesce(billing_model,'')=''
    and created_at >= timestamp with time zone '2026-05-01 00:00:00+00'
)
select user_id, api_key_id, account_id, group_id, subscription_id,
       count(*) rows,
       round(sum(total_cost)::numeric,10) charged,
       round(sum(expected_mini_cost)::numeric,10) expected,
       round(sum(total_cost-expected_mini_cost)::numeric,10) overcharged,
       min(created_at at time zone 'Asia/Shanghai') min_bj,
       max(created_at at time zone 'Asia/Shanghai') max_bj
from bad
group by user_id, api_key_id, account_id, group_id, subscription_id
order by overcharged desc;

with bad as (
  select *, (input_tokens*0.75/1000000.0 + output_tokens*4.5/1000000.0 + cache_read_tokens*0.075/1000000.0) as expected_mini_cost
  from usage_logs
  where model='gpt-5.4-mini' and coalesce(billing_model,'')='' and created_at >= timestamp with time zone '2026-05-01 00:00:00+00'
)
select count(*) rows, round(sum(total_cost)::numeric,10) charged, round(sum(expected_mini_cost)::numeric,10) expected, round(sum(total_cost-expected_mini_cost)::numeric,10) overcharged
from bad;

select id, created_at at time zone 'Asia/Shanghai' bj, request_id, user_id, api_key_id, account_id, group_id, subscription_id,
       input_tokens, output_tokens, cache_read_tokens, input_cost, output_cost, cache_read_cost, total_cost,
       round((input_tokens*0.75/1000000.0 + output_tokens*4.5/1000000.0 + cache_read_tokens*0.075/1000000.0)::numeric,10) expected,
       round((total_cost-(input_tokens*0.75/1000000.0 + output_tokens*4.5/1000000.0 + cache_read_tokens*0.075/1000000.0))::numeric,10) overcharged
from bad
order by created_at;
