-- Extend summary_by_project to surface billout_amount sums for the
-- prev / this / all-time windows alongside the existing cost sums.
-- The homepage Summary table renders billout above cost for owners.
-- Adi (manager) doesn't see the billout cells — UI gate. The RPC
-- still returns them but the rows are scoped by RLS anyway.

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

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,
  p_imported_by uuid default null
)
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,
  this_billout_usd numeric,
  prev_billout_usd numeric,
  all_billout_usd numeric
)
language sql
stable
security invoker
set search_path = public
as $$
  select
    coalesce(te.operator, '(no operator)') as operator,
    coalesce(te.client, '(no client)') as client,
    coalesce(te.project, '(no project)') as project,
    coalesce(sum(case
      when te.start_at >= p_this_start and te.start_at < p_this_end
        then te.converted_duration_seconds end), 0)::bigint as this_seconds,
    coalesce(sum(case
      when te.start_at >= p_prev_start and te.start_at < p_prev_end
        then te.converted_duration_seconds end), 0)::bigint as prev_seconds,
    coalesce(sum(te.converted_duration_seconds), 0)::bigint as all_seconds,
    coalesce(sum(case
      when te.start_at >= p_this_start and te.start_at < p_this_end
        then te.billout_cost_usd end), 0)::numeric as this_cost_usd,
    coalesce(sum(case
      when te.start_at >= p_prev_start and te.start_at < p_prev_end
        then te.billout_cost_usd end), 0)::numeric as prev_cost_usd,
    coalesce(sum(te.billout_cost_usd), 0)::numeric as all_cost_usd,
    coalesce(sum(case
      when te.start_at >= p_this_start and te.start_at < p_this_end
        then te.billout_amount_usd end), 0)::numeric as this_billout_usd,
    coalesce(sum(case
      when te.start_at >= p_prev_start and te.start_at < p_prev_end
        then te.billout_amount_usd end), 0)::numeric as prev_billout_usd,
    coalesce(sum(te.billout_amount_usd), 0)::numeric as all_billout_usd
  from public.time_entries te
  where te.org_id = p_org_id
    and te.converted_duration_seconds is not null
    and (
      p_imported_by is null
      or exists (
        select 1 from public.clockify_imports ci
        where ci.id = te.import_id and ci.imported_by = p_imported_by
      )
    )
  group by 1, 2, 3
  order by this_seconds desc
$$;
