-- Eliminate rate_proportion from the cost/billout pipeline. Each
-- team_member now has a direct per-source-hour cost rate
-- (cost_rate_usd) and the existing billout_rate_usd is rescaled to
-- mean "per source hour" too. converted_duration_seconds still gets
-- stamped at write time (= source × proportion) and is used ONLY by
-- the Toggl CSV export. teams.base_rate_usd and team_members.rate_proportion
-- stay in the schema but are deprecated for cost/billout math.
--
-- Dollar equivalence:
--   - old billout_cost_usd = converted × team.base_rate
--                         = (source × prop) × base
--                         = source × (base × prop)
--                         = source × cost_rate_usd  (new)
--   - old billout_amount_usd (no override) = converted × billout_rate_old
--                         = (source × prop) × billout_rate_old
--                         = source × (billout_rate_old × prop)
--                         = source × billout_rate_new
--   - old billout_amount_usd (pct override -X%) = converted × billout_rate_old × (1 - X/100)
--                         (similar algebra) = source × billout_rate_new × (1 - X/100)
-- So every existing entry's stored billout_cost / billout_amount stays
-- byte-for-byte the same.

-- ============================================================
-- 1. New column: cost_rate_usd
-- ============================================================
alter table public.team_members
  add column if not exists cost_rate_usd numeric(10, 2);

comment on column public.team_members.cost_rate_usd is
  'USD per source hour that the team owner pays this person. New canonical cost rate (replaces team.base_rate × rate_proportion).';

-- ============================================================
-- 2. Backfill cost_rate_usd from old base × proportion
-- ============================================================
update public.team_members tm
set cost_rate_usd = round(t.base_rate_usd * tm.rate_proportion * 100) / 100
from public.teams t
where tm.team_id = t.id
  and tm.cost_rate_usd is null;

-- Gary is an AI — cost is $0 (per user direction in this migration).
update public.team_members
set cost_rate_usd = 0
where email = 'gary@rian.ca';

-- ============================================================
-- 3. Scale existing billout_rate_usd by proportion (in-place).
--    Old billout_rate was per converted-hour; new is per source hour.
--    Multiply by proportion so source × new_rate == converted × old_rate.
-- ============================================================
update public.team_members
set billout_rate_usd = round(billout_rate_usd * rate_proportion * 100) / 100
where billout_rate_usd is not null
  and rate_proportion is not null;

-- ============================================================
-- 4. Re-stamp billout_cost_usd on every entry: source × cost_rate.
--    Should be a no-op mathematically (same dollar amount) but lets us
--    rip out the team.base_rate dependency on the write path going
--    forward.
-- ============================================================
update public.time_entries te
set billout_cost_usd = round(
  (te.duration_seconds::numeric / 3600) * tm.cost_rate_usd * 100
) / 100
from public.team_members tm
where te.team_member_id = tm.id
  and te.duration_seconds is not null
  and tm.cost_rate_usd is not null;

-- ============================================================
-- 5. Re-stamp billout_amount_usd: source × effective_billout_rate.
--    The function already returns billout_rate_usd (now per-source-hour)
--    for the fallback case, and applies pct correctly. For absolute-rate
--    overrides this CHANGES the math (source × absolute_rate vs old
--    converted × absolute_rate). We don't have any absolute-rate
--    overrides right now (Prompt Victoria is pct), so this is a no-op
--    in practice — but worth noting if you add absolute overrides later
--    they'll behave per-source-hour.
-- ============================================================
update public.time_entries te
set billout_amount_usd = case
  when te.duration_seconds is null then null
  else round(
    (te.duration_seconds::numeric / 3600)
    * coalesce(
        public.effective_billout_rate(te.team_member_id, te.project_id),
        0
      )
    * 100
  ) / 100
end;

-- ============================================================
-- 6. Update restamp helper to use duration_seconds (canonical hours)
--    instead of converted_duration_seconds.
-- ============================================================
create or replace function public.restamp_billout_for_project(
  p_org_id uuid,
  p_project_id uuid,
  p_team_member_id uuid default null
)
returns bigint
language sql
volatile
security invoker
set search_path = public
as $$
  with updated as (
    update public.time_entries te
    set billout_amount_usd = case
      when te.duration_seconds is null then null
      else round(
        (te.duration_seconds::numeric / 3600)
        * coalesce(
            public.effective_billout_rate(te.team_member_id, te.project_id),
            0
          )
        * 100
      ) / 100
    end
    where te.org_id = p_org_id
      and te.project_id = p_project_id
      and (p_team_member_id is null or te.team_member_id = p_team_member_id)
      and te.duration_seconds is not null
    returning 1
  )
  select coalesce(count(*), 0)::bigint from updated
$$;

-- ============================================================
-- 7. Switch the summary RPCs to sum duration_seconds (source) as
--    "billout_seconds", since after this migration "hours billed" =
--    source hours for the user. converted_duration_seconds stays
--    accessible per-row for the Toggl export.
--
--    Only the *_seconds aggregate changes; everything else stays.
-- ============================================================

-- entries_filter_totals
drop function if exists public.entries_filter_totals(
  uuid, timestamp, timestamp, text, uuid, text, text, text, text, text, text, uuid
);

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,
  p_client text default null,
  p_operator text default null,
  p_imported_by uuid default null
)
returns table (
  row_count bigint,
  source_seconds bigint,
  billout_seconds bigint,
  cost_usd numeric,
  billout_amount_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.operator 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.operator 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 || '%'
        or te.client 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 || '%'
      )
      and (p_client is null or te.client ilike '%' || p_client || '%')
      and (p_operator is null or te.operator ilike '%' || p_operator || '%')
      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
        )
      )
  )
  select
    count(*)::bigint as row_count,
    coalesce(sum(duration_seconds), 0)::bigint as source_seconds,
    -- After the proportion-elimination migration, "billout hours" = source hours.
    coalesce(sum(duration_seconds), 0)::bigint as billout_seconds,
    coalesce(sum(billout_cost_usd), 0)::numeric as cost_usd,
    coalesce(sum(billout_amount_usd), 0)::numeric as billout_amount_usd
  from filtered
$$;

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

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,
  p_operator text default null,
  p_client text default null,
  p_user text default null,
  p_source_email text 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,
  this_billout_adj_usd numeric,
  prev_billout_adj_usd numeric,
  all_billout_adj_usd numeric
)
language sql
stable
security invoker
set search_path = public
as $$
  with agg 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.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.duration_seconds end), 0)::bigint as prev_seconds,
      coalesce(sum(te.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.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
        )
      )
      and (p_operator is null or te.operator ilike '%' || p_operator || '%')
      and (p_client is null or te.client ilike '%' || p_client || '%')
      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 || '%'
      )
    group by 1, 2, 3
  )
  select
    agg.operator,
    agg.client,
    agg.project,
    agg.this_seconds,
    agg.prev_seconds,
    agg.all_seconds,
    agg.this_cost_usd,
    agg.prev_cost_usd,
    agg.all_cost_usd,
    agg.this_billout_usd,
    agg.prev_billout_usd,
    agg.all_billout_usd,
    (
      agg.this_billout_usd
        * (1 + coalesce(p.billout_adjustment_pct, 0) / 100)
      + coalesce(p.billout_adjustment_amount, 0)
    )::numeric as this_billout_adj_usd,
    (
      agg.prev_billout_usd
        * (1 + coalesce(p.billout_adjustment_pct, 0) / 100)
      + coalesce(p.billout_adjustment_amount, 0)
    )::numeric as prev_billout_adj_usd,
    (
      agg.all_billout_usd
        * (1 + coalesce(p.billout_adjustment_pct, 0) / 100)
      + coalesce(p.billout_adjustment_amount, 0)
    )::numeric as all_billout_adj_usd
  from agg
  left join public.operators o
    on o.org_id = p_org_id and lower(o.name) = lower(agg.operator)
  left join public.clients c
    on c.operator_id = o.id and lower(c.name) = lower(agg.client)
  left join public.projects p
    on p.client_id = c.id and lower(p.name) = lower(agg.project)
  order by this_seconds desc
$$;

-- project_summary (/projects page)
drop function if exists public.project_summary(
  uuid, timestamp, timestamp, text, uuid, text, text, text, text, text, text, uuid, boolean
);

create or replace function public.project_summary(
  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,
  p_client text default null,
  p_operator text default null,
  p_imported_by uuid default null,
  p_missing_info boolean default false
)
returns table (
  operator text,
  client text,
  project text,
  project_entity_id uuid,
  row_count bigint,
  billout_seconds bigint,
  cost_usd numeric,
  billout_amount_usd numeric,
  billout_adjusted_usd numeric,
  billout_adjustment_pct numeric,
  billout_adjustment_amount numeric,
  income_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.operator 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.operator 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 || '%'
        or te.client 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 || '%'
      )
      and (p_client is null or te.client ilike '%' || p_client || '%')
      and (p_operator is null or te.operator ilike '%' || p_operator || '%')
      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
        )
      )
      and (
        coalesce(p_missing_info, false) = false
        or (te.operator is null or te.client is null or te.project is null)
      )
  ),
  agg as (
    select
      operator,
      client,
      project,
      count(*)::bigint as row_count,
      -- Now uses duration_seconds (source) as the "billable" hours.
      coalesce(sum(duration_seconds), 0)::bigint as billout_seconds,
      coalesce(sum(billout_cost_usd), 0)::numeric as cost_usd,
      coalesce(sum(billout_amount_usd), 0)::numeric as billout_amount_usd
    from filtered
    group by 1, 2, 3
  )
  select
    agg.operator,
    agg.client,
    agg.project,
    p.id as project_entity_id,
    agg.row_count,
    agg.billout_seconds,
    agg.cost_usd,
    agg.billout_amount_usd,
    (
      agg.billout_amount_usd
        * (1 + coalesce(p.billout_adjustment_pct, 0) / 100)
      + coalesce(p.billout_adjustment_amount, 0)
    )::numeric as billout_adjusted_usd,
    p.billout_adjustment_pct,
    p.billout_adjustment_amount,
    p.income_usd
  from agg
  left join public.operators o
    on o.org_id = p_org_id and lower(o.name) = lower(agg.operator)
  left join public.clients c
    on c.operator_id = o.id and lower(c.name) = lower(agg.client)
  left join public.projects p
    on p.client_id = c.id and lower(p.name) = lower(agg.project)
$$;
