-- New role: is_broad_reviewer — distinct from is_admin.
--
-- Grants VIEW-only access to every current/future feature except
-- 'invoicing', without granting any settings/permission-management
-- authority. That authority stays is_admin-only and is untouched here —
-- is_broad_reviewer never satisfies any of the is_admin-gated policies
-- on feature_registry/permissions/profiles from 0014.
--
-- 'invoicing' is excluded explicitly because it already has its own
-- finer-grained model (permissions rows, folded in from
-- is_invoice_reviewer/is_invoice_approver in 0014) — broad_reviewer is
-- for everything else, not a shortcut around that.
--
-- Data population (setting is_broad_reviewer=true for Berry) is NOT
-- part of this migration — same blocker as the still-pending
-- Dominic/Andrew permissions grant: his profiles row doesn't exist yet
-- (no auth.users account). This migration only adds the column and the
-- has_permission() clause; the actual grant is a follow-up UPDATE once
-- his account exists.

alter table public.profiles
  add column if not exists is_broad_reviewer boolean not null default false;

create or replace function public.has_permission(feature text, need_write boolean default false)
returns boolean
language sql
security definer
stable
as $$
  select exists (
    select 1 from public.permissions
    where user_id = auth.uid()
      and feature_key = feature
      and (not need_write or can_write)
  )
  or exists (
    select 1 from public.profiles where id = auth.uid() and is_admin = true
  )
  or (
    not need_write
    and feature <> 'invoicing'
    and exists (
      select 1 from public.profiles where id = auth.uid() and is_broad_reviewer = true
    )
  );
$$;

grant execute on function public.has_permission(text, boolean) to authenticated;