-- One-shot Postgres aggregation for the Summary page.
-- Without this the app paginates time_entries from the client (7+ round
-- trips), which doesn't scale. This function runs the GROUP BY in Postgres
-- and returns ~80 rows total.

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 (
  project text,
  this_seconds bigint,
  prev_seconds bigint,
  all_seconds bigint
)
language sql
stable
security invoker
set search_path = public
as $$
  select
    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
  from public.time_entries
  where org_id = p_org_id
    and converted_duration_seconds is not null
  group by 1
  order by this_seconds desc
$$;

-- Supporting index: (org_id, project, start_at) lets Postgres do a single
-- ordered scan when grouping by project + filtering by date.
create index if not exists idx_time_entries_org_project_start_proj
  on public.time_entries (org_id, project, start_at)
  where converted_duration_seconds is not null;
