-- The Summary page's "By team member" section is replaced by a filter-aware
-- totals card on /entries. This migration adds the totals RPC and drops the
-- now-unused per-team-member RPC.

drop function if exists public.summary_by_team_member(
  uuid, timestamp, timestamp, timestamp, timestamp
);

-- Returns row count, source hours, billout hours, and cost for the entries
-- that match the same filter the /entries page applies. Status filter
-- mirrors what the page does (pending excludes blocked; blocked is the
-- derived "missing-required-field" predicate).
create or replace function public.entries_filter_totals(
  p_org_id uuid,
  p_start timestamp default null,
  p_end timestamp default null,
  p_status text default 'all',
  p_batch uuid default null,
  p_q text default null,
  p_project text default null,
  p_user text default null,
  p_source_email text default null
)
returns table (
  row_count bigint,
  source_seconds bigint,
  billout_seconds bigint,
  cost_usd numeric
)
language sql
stable
security invoker
set search_path = public
as $$
  with filtered as (
    select te.*
    from public.time_entries te
    where te.org_id = p_org_id
      and (p_start is null or te.start_at >= p_start)
      and (p_end is null or te.start_at < p_end)
      and (p_batch is null or te.import_id = p_batch)
      and (
        p_status = 'all'
        or (p_status = 'pending'
            and te.transferred_at is null
            and te.team_member_id is not null
            and te.converted_duration_seconds is not null
            and te.client is not null
            and te.project is not null
            and te.description is not null
            and te.end_at is not null
            and te.source_user_email is not null
            and te.converted_user is not null)
        or (p_status = 'transferred' and te.transferred_at is not null)
        or (p_status = 'blocked'
            and te.transferred_at is null
            and (te.team_member_id is null
                 or te.converted_duration_seconds is null
                 or te.client is null
                 or te.project is null
                 or te.description is null
                 or te.end_at is null
                 or te.source_user_email is null
                 or te.converted_user is null))
      )
      and (
        p_q is null
        or te.project ilike '%' || p_q || '%'
        or te.description ilike '%' || p_q || '%'
        or te.source_user_email ilike '%' || p_q || '%'
      )
      and (p_project is null or te.project ilike '%' || p_project || '%')
      and (p_user is null or te.converted_user ilike '%' || p_user || '%')
      and (
        p_source_email is null
        or te.source_user_email ilike '%' || p_source_email || '%'
      )
  )
  select
    count(*)::bigint as row_count,
    coalesce(sum(duration_seconds), 0)::bigint as source_seconds,
    coalesce(sum(converted_duration_seconds), 0)::bigint as billout_seconds,
    coalesce(sum(billout_cost_usd), 0)::numeric as cost_usd
  from filtered
$$;
