-- Summary by-project table now shows operator + client + project as
-- separate columns. The RPC groups by all three (was just project).

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

create or replace function public.summary_by_project(
  p_org_id uuid,
  p_this_start timestamp,
  p_this_end timestamp,
  p_prev_start timestamp,
  p_prev_end timestamp
)
returns table (
  operator text,
  client text,
  project text,
  this_seconds bigint,
  prev_seconds bigint,
  all_seconds bigint,
  this_cost_usd numeric,
  prev_cost_usd numeric,
  all_cost_usd numeric
)
language sql
stable
security invoker
set search_path = public
as $$
  select
    coalesce(operator, '(no operator)') as operator,
    coalesce(client, '(no client)') as client,
    coalesce(project, '(no project)') as project,
    coalesce(sum(case
      when start_at >= p_this_start and start_at < p_this_end
        then converted_duration_seconds end), 0)::bigint as this_seconds,
    coalesce(sum(case
      when start_at >= p_prev_start and start_at < p_prev_end
        then converted_duration_seconds end), 0)::bigint as prev_seconds,
    coalesce(sum(converted_duration_seconds), 0)::bigint as all_seconds,
    coalesce(sum(case
      when start_at >= p_this_start and start_at < p_this_end
        then billout_cost_usd end), 0)::numeric as this_cost_usd,
    coalesce(sum(case
      when start_at >= p_prev_start and start_at < p_prev_end
        then billout_cost_usd end), 0)::numeric as prev_cost_usd,
    coalesce(sum(billout_cost_usd), 0)::numeric as all_cost_usd
  from public.time_entries
  where org_id = p_org_id
    and converted_duration_seconds is not null
  group by 1, 2, 3
  order by this_seconds desc
$$;
