-- Permissions system — Sections 1-2 of docs/0014_permissions_system_spec.md,
-- plus RLS on the two new tables (added per explicit follow-up — the spec
-- itself never enabled RLS on feature_registry/permissions, which would
-- have left them open to any authenticated client via PostgREST despite
-- every other table in this project having real RLS since 0001/0008).
--
-- Per the spec: is_invoice_reviewer/is_invoice_approver on profiles are
-- FOLDED INTO permissions rows below, not left running in parallel — but
-- the columns themselves are NOT dropped yet. That's a separate 0015,
-- once nothing else (RLS policies, app code) is confirmed to still read
-- them directly. Section 3 (has_permission() + the actual RLS policy
-- rewrite on schedule/invoice_settings/rate_history) is deliberately NOT
-- part of this file — that's a later step per the spec's order of
-- operations, run only after this migration is applied.
--
-- has_permission() does not exist yet in this migration (it's Section 3),
-- so every admin-only write policy below inlines the same check it will
-- eventually wrap: `exists (select 1 from profiles where id = auth.uid()
-- and is_admin = true)`. These policies are written now so they don't
-- need touching later — Section 3 introduces has_permission() for
-- FEATURE-gated tables (schedule/invoice_settings/rate_history), not for
-- these two, which are permission-administration tables gated on
-- is_admin directly, not on any feature_key.

-- ============================================================
-- Section 1: new tables
-- ============================================================

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

create table if not exists public.feature_registry (
  feature_key   text primary key,
  display_name  text not null,
  display_order integer not null default 0,
  created_at    timestamptz not null default now()
);

insert into public.feature_registry (feature_key, display_name, display_order) values
  ('invoicing', 'Invoicing', 1)
on conflict (feature_key) do nothing;

alter table public.feature_registry enable row level security;

-- Everyone needs to see what features exist to render the settings
-- checklist (Section 4).
create policy "feature_registry_select_all"
on public.feature_registry
for select
to authenticated
using (true);

-- Admin-only writes, inline is_admin check (has_permission() doesn't
-- exist until Section 3, and wouldn't apply to this table anyway — this
-- is who-can-administer-features, not a feature itself).
create policy "feature_registry_insert_admin"
on public.feature_registry
for insert
to authenticated
with check (exists (select 1 from public.profiles where id = auth.uid() and is_admin = true));

create policy "feature_registry_update_admin"
on public.feature_registry
for update
to authenticated
using (exists (select 1 from public.profiles where id = auth.uid() and is_admin = true))
with check (exists (select 1 from public.profiles where id = auth.uid() and is_admin = true));

create policy "feature_registry_delete_admin"
on public.feature_registry
for delete
to authenticated
using (exists (select 1 from public.profiles where id = auth.uid() and is_admin = true));

create table if not exists public.permissions (
  user_id     uuid not null references public.profiles(id) on delete cascade,
  feature_key text not null references public.feature_registry(feature_key),
  can_view    boolean not null default false,
  can_write   boolean not null default false,
  granted_by  uuid references public.profiles(id),
  granted_at  timestamptz not null default now(),
  primary key (user_id, feature_key),
  constraint write_implies_view check (not can_write or can_view)
);

create index if not exists idx_permissions_user on public.permissions(user_id);
create index if not exists idx_permissions_feature on public.permissions(feature_key);

alter table public.permissions enable row level security;

-- Self-select own grants, OR admin sees everyone's.
create policy "permissions_select_self_or_admin"
on public.permissions
for select
to authenticated
using (
  user_id = auth.uid()
  or exists (select 1 from public.profiles where id = auth.uid() and is_admin = true)
);

-- Admin-only writes — nobody grants themselves or anyone else
-- permissions except an admin, via the /settings/users UI (Section 4).
create policy "permissions_insert_admin"
on public.permissions
for insert
to authenticated
with check (exists (select 1 from public.profiles where id = auth.uid() and is_admin = true));

create policy "permissions_update_admin"
on public.permissions
for update
to authenticated
using (exists (select 1 from public.profiles where id = auth.uid() and is_admin = true))
with check (exists (select 1 from public.profiles where id = auth.uid() and is_admin = true));

create policy "permissions_delete_admin"
on public.permissions
for delete
to authenticated
using (exists (select 1 from public.profiles where id = auth.uid() and is_admin = true));

-- ============================================================
-- Section 2: data migration — fold is_invoice_reviewer/is_invoice_approver
-- into permissions rows for feature_key='invoicing'. Reviewer-only ->
-- can_view=true/can_write=false. Approver -> can_view=true/can_write=true
-- (via is_invoice_approver feeding can_write directly; approver implies
-- reviewer per the spec, satisfied here since can_view is always true
-- whenever either flag is set).
-- ============================================================

insert into public.permissions (user_id, feature_key, can_view, can_write)
select id, 'invoicing', true, is_invoice_approver
from public.profiles
where is_invoice_reviewer = true or is_invoice_approver = true
on conflict (user_id, feature_key) do update
  set can_write = excluded.can_write or permissions.can_write;

-- Deprecated (not dropped yet — see header comment and spec Section 0):
--   profiles.is_invoice_reviewer
--   profiles.is_invoice_approver
-- Dropping these is 0015+, once confirmed nothing else reads them
-- directly (the existing is_invoice_reviewer()/is_invoice_approver() SQL
-- functions from 0008 still do, and the current invoices_update_approver
-- policy from 0009/0010 still calls is_invoice_approver() — all of that
-- needs to be migrated to has_permission() first).