-- 1. Add a locked `billout_cost_usd` column to time_entries so cost is
--    captured at import time and isn't retroactively affected by later
--    base_rate or rate_proportion changes.
-- 2. Extend `summary_by_project` to also return per-bucket cost (sum of
--    the locked column).
-- 3. Add `summary_by_team_member` for the new "by team member" report on
--    the Summary page.

alter table public.time_entries
  add column if not exists billout_cost_usd numeric(10, 4);

-- Populate existing rows using the current base rate. This is the best
-- approximation for the historical backfill — going forward, the value
-- is captured by the import / re-resolve / edit code paths.
update public.time_entries te
set billout_cost_usd = round(
  (te.converted_duration_seconds::numeric / 3600) * o.default_hourly_rate_usd,
  4
)
from public.organizations o
where te.org_id = o.id
  and te.billout_cost_usd is null
  and te.converted_duration_seconds is not null;

create index if not exists idx_time_entries_org_project_start_billout
  on public.time_entries (org_id, project, start_at)
  where billout_cost_usd is not null;

-- Replace summary_by_project to include per-bucket cost.
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 (
  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(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
  order by this_seconds desc
$$;

-- New RPC: same shape but bucketed by team member (with a fallback
-- label of the source email for unmatched entries).
create or replace function public.summary_by_team_member(
  p_org_id uuid,
  p_this_start timestamp,
  p_this_end timestamp,
  p_prev_start timestamp,
  p_prev_end timestamp
)
returns table (
  label text,
  this_source_seconds bigint,
  this_billout_seconds bigint,
  this_cost_usd numeric,
  prev_source_seconds bigint,
  prev_billout_seconds bigint,
  prev_cost_usd numeric,
  all_source_seconds bigint,
  all_billout_seconds bigint,
  all_cost_usd numeric
)
language sql
stable
security invoker
set search_path = public
as $$
  select
    coalesce(tm.display_name, te.source_user_email, '(unknown)') as label,
    coalesce(sum(case when te.start_at >= p_this_start and te.start_at < p_this_end
      then te.duration_seconds end), 0)::bigint as this_source_seconds,
    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_billout_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.duration_seconds end), 0)::bigint as prev_source_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_billout_seconds,
    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.duration_seconds), 0)::bigint as all_source_seconds,
    coalesce(sum(te.converted_duration_seconds), 0)::bigint as all_billout_seconds,
    coalesce(sum(te.billout_cost_usd), 0)::numeric as all_cost_usd
  from public.time_entries te
  left join public.team_members tm on tm.id = te.team_member_id
  where te.org_id = p_org_id
    and te.converted_duration_seconds is not null
  group by coalesce(tm.display_name, te.source_user_email, '(unknown)')
  order by this_billout_seconds desc
$$;
