-- Per-project rate overrides. Each row is a rule:
--   - team_member_id IS NULL → applies to ALL entries on this project
--   - team_member_id IS SET  → applies only to this team_member's entries
-- More-specific (user) wins over project-wide. Both fall back to
-- team_members.billout_rate_usd if no rule matches.
--
-- Owner-only. The app re-stamps entries' billout_amount_usd when a
-- rule is added / changed / deleted (lock-at-write semantic as cost,
-- ADR #014).

create table if not exists public.project_rate_overrides (
  id uuid primary key default gen_random_uuid(),
  org_id uuid not null references public.organizations(id) on delete cascade,
  project_id uuid not null references public.projects(id) on delete cascade,
  team_member_id uuid references public.team_members(id) on delete cascade,
  override_rate_usd numeric(10, 2) not null check (override_rate_usd >= 0),
  notes text,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

-- One rule per (project, user) — NULL team_member_id is the "all" default,
-- limited to one default per project.
create unique index if not exists project_rate_overrides_unique
  on public.project_rate_overrides (
    project_id,
    coalesce(team_member_id, '00000000-0000-0000-0000-000000000000')
  );

create index if not exists project_rate_overrides_project_idx
  on public.project_rate_overrides(project_id);
create index if not exists project_rate_overrides_team_member_idx
  on public.project_rate_overrides(team_member_id);

drop trigger if exists project_rate_overrides_set_updated_at on public.project_rate_overrides;
create trigger project_rate_overrides_set_updated_at
before update on public.project_rate_overrides
for each row execute function public.set_updated_at();

alter table public.project_rate_overrides enable row level security;

-- Owner-only: same gate as the projects table itself.
drop policy if exists "project_rate_overrides_select" on public.project_rate_overrides;
create policy "project_rate_overrides_select"
  on public.project_rate_overrides for select
  to authenticated
  using (
    public.is_org_owner(org_id)
    or public.is_super_admin_caller()
  );

drop policy if exists "project_rate_overrides_modify" on public.project_rate_overrides;
create policy "project_rate_overrides_modify"
  on public.project_rate_overrides for all
  to authenticated
  using (
    public.is_org_owner(org_id)
    or public.is_super_admin_caller()
  )
  with check (
    public.is_org_owner(org_id)
    or public.is_super_admin_caller()
  );

comment on table public.project_rate_overrides is
  'Per-project rate rules. team_member_id NULL = applies to all hours on the project; SET = user-specific. Owner-only.';

-- ============================================================
-- Helper: effective_billout_rate(team_member_id, project_id)
-- ============================================================
-- Lookup order:
--   1. user-specific override on this project
--   2. project-wide override (team_member_id IS NULL)
--   3. team_member.billout_rate_usd
--
-- Returns NULL if nothing is set. Pure SQL, stable.
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 $$
  select rate from (
    -- 1. user-specific override on this project
    select 1 as priority, override_rate_usd as rate
    from public.project_rate_overrides
    where project_id = p_project_id
      and team_member_id = p_team_member_id
      and p_project_id is not null and p_team_member_id is not null

    union all

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

    union all

    -- 3. team_member.billout_rate_usd
    select 3 as priority, billout_rate_usd as rate
    from public.team_members
    where id = p_team_member_id
      and p_team_member_id is not null
  ) candidates
  where rate is not null
  order by priority
  limit 1
$$;

comment on function public.effective_billout_rate is
  'Returns the effective billout rate for a (team_member, project) pair. User-specific override wins over project-wide override wins over team_members.billout_rate_usd. NULL if nothing is set.';
