-- Section 3 of docs/0014_permissions_system_spec.md: has_permission(), and
-- rewriting the write policies on schedule/invoice_settings/rate_history
-- to use it instead of the `using (true)` / `with check (true)` TODO
-- policies from 0011/0013.
--
-- Note on scope vs. the spec's own Section 3 snippet: that snippet only
-- showed one illustrative policy per table (invoice_settings' update,
-- rate_history's insert, schedule's update). It did not mention
-- schedule's INSERT policy or rate_history's UPDATE/DELETE policies.
-- Replacing only the three shown would leave half the write surface on
-- these tables still wide open on `using (true)`, defeating the point of
-- this migration — so all six existing *_admin_TODO write policies from
-- 0011/0013 are replaced here, not just the three the spec illustrated.
--
-- Numbering note: the spec's own comments (0014, Section 0/2) refer to
-- the eventual is_invoice_reviewer/is_invoice_approver column drop as
-- "0015+" — that was written before this migration existed and just
-- meant "sometime after 0014", not a hard reservation of the number 15.
-- This migration is the next one chronologically, so it takes 0015; the
-- column drop becomes whatever number is next when that work starts.

-- ============================================================
-- has_permission() — per spec Section 3, verbatim.
-- ============================================================

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
  );
$$;

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

-- ============================================================
-- schedule (0011) — replace both write policies.
-- ============================================================

drop policy if exists "schedule_insert_admin_TODO" on public.schedule;
create policy "schedule_insert"
on public.schedule
for insert
to authenticated
with check (public.has_permission('invoicing', need_write => true));

drop policy if exists "schedule_update_admin_TODO" on public.schedule;
create policy "schedule_update"
on public.schedule
for update
to authenticated
using (public.has_permission('invoicing', need_write => true))
with check (public.has_permission('invoicing', need_write => true));

-- ============================================================
-- invoice_settings (0013) — replace the one write policy.
-- ============================================================

drop policy if exists "invoice_settings_update_admin_TODO" on public.invoice_settings;
create policy "invoice_settings_update"
on public.invoice_settings
for update
to authenticated
using (public.has_permission('invoicing', need_write => true))
with check (public.has_permission('invoicing', need_write => true));

-- ============================================================
-- rate_history (0013) — replace all three write policies.
-- ============================================================

drop policy if exists "rate_history_insert_admin_TODO" on public.rate_history;
create policy "rate_history_insert"
on public.rate_history
for insert
to authenticated
with check (public.has_permission('invoicing', need_write => true));

drop policy if exists "rate_history_update_admin_TODO" on public.rate_history;
create policy "rate_history_update"
on public.rate_history
for update
to authenticated
using (public.has_permission('invoicing', need_write => true))
with check (public.has_permission('invoicing', need_write => true));

drop policy if exists "rate_history_delete_admin_TODO" on public.rate_history;
create policy "rate_history_delete"
on public.rate_history
for delete
to authenticated
using (public.has_permission('invoicing', need_write => true));