-- Each rate override is either an absolute rate OR a percentage
-- adjustment on top of the user's fallback billout rate. Existing rows
-- have override_rate_usd set; new rows can use override_pct instead.

-- Drop the NOT NULL on override_rate_usd so a row can be pct-only.
alter table public.project_rate_overrides
  alter column override_rate_usd drop not null;

-- New column: signed percentage. Positive = markup, negative = discount.
alter table public.project_rate_overrides
  add column if not exists override_pct numeric(7, 4);

-- Exactly-one-or-the-other: rate XOR pct must be set. (Existing rows
-- have rate-only, so the constraint validates trivially.)
alter table public.project_rate_overrides
  drop constraint if exists project_rate_overrides_one_kind;
alter table public.project_rate_overrides
  add constraint project_rate_overrides_one_kind check (
    (override_rate_usd is not null and override_pct is null)
    or (override_rate_usd is null and override_pct is not null)
  );

comment on column public.project_rate_overrides.override_rate_usd is
  'Absolute rate (USD per billout hour). Mutually exclusive with override_pct.';
comment on column public.project_rate_overrides.override_pct is
  'Signed percentage adjustment to the fallback rate (per-user billout_rate_usd). E.g. -20 = 20% discount on the user''s normal rate. Mutually exclusive with override_rate_usd.';

-- ============================================================
-- Rewrite effective_billout_rate to honor pct rules.
--
-- Lookup order is unchanged (user-specific > project-wide > member
-- default). At each level the rule may be absolute (use it as-is) OR
-- percentage (apply to the NEXT-fallback rate — usually the
-- team_member's billout_rate_usd).
-- ============================================================
create or replace function public.effective_billout_rate(
  p_team_member_id uuid,
  p_project_id uuid
)
returns numeric
language sql
stable
security invoker
set search_path = public
as $$
  with chosen as (
    -- user-specific override on this project (highest priority)
    select
      1 as priority,
      override_rate_usd,
      override_pct
    from public.project_rate_overrides
    where p_project_id is not null
      and p_team_member_id is not null
      and project_id = p_project_id
      and team_member_id = p_team_member_id

    union all

    -- project-wide override
    select
      2 as priority,
      override_rate_usd,
      override_pct
    from public.project_rate_overrides
    where p_project_id is not null
      and project_id = p_project_id
      and team_member_id is null

    order by priority
    limit 1
  ),
  member_rate as (
    select billout_rate_usd
    from public.team_members
    where p_team_member_id is not null
      and id = p_team_member_id
  )
  select case
    -- Absolute override wins outright.
    when (select override_rate_usd from chosen) is not null
      then (select override_rate_usd from chosen)
    -- Percentage override: apply to the member's fallback rate.
    -- NULL fallback × any pct = NULL (no rate available).
    when (select override_pct from chosen) is not null
      then (select billout_rate_usd from member_rate)
           * (1 + (select override_pct from chosen) / 100)
    -- No override: just the member's default.
    else (select billout_rate_usd from member_rate)
  end
$$;

comment on function public.effective_billout_rate is
  'Returns the effective billout rate per billout-hour for a (team_member, project) pair. Precedence: user-specific override → project-wide override → team_member.billout_rate_usd. Overrides can be absolute (override_rate_usd) or percentage (override_pct applied to the member default).';
