-- ============================================================
-- Real RLS for invoices + qpi_qualifications.
--
-- Scope for this migration (confirmed 2026-07-22 — see
-- apps-script-1P0T/SUPABASE_RLS_PLAN.md for the full 19-table plan):
--   1. invoices-only for now. The other tables in the plan (agents,
--      admin_accounts, schedule, handovers, agent_notes, daily_reports,
--      scoring_settings, invoice_profiles, invoice_settings/rate_history,
--      login_log, etc.) do not exist in this project yet and are
--      deferred to a later migration.
--   2. qpi_qualifications gets kept as its own table (not folded into
--      invoices), even though standalone QPI invoices themselves were
--      already removed in 0006 — QPI is a line item inside
--      invoices.items[], but *eligibility* to add one is still tracked
--      per agent/quarter here.
--   3. Real auth.uid()-based RLS is written now. 0005's temporary
--      anon-read policy is LEFT IN PLACE on purpose — the Next.js app
--      has no Supabase Auth flow yet, so today literally nobody can
--      satisfy the new "own row" / role-flag policies below. Once real
--      sign-in ships, drop the 0005 policy; until then both coexist.
--
-- Reconciliation note: the confirmed plan said invoice content edits
-- (hour overrides etc.) should be blocked once submitted/approved.
-- Migration 0002 (already shipped, predates this file) documents that
-- a 'rejected' invoice is intentionally still editable/resubmittable —
-- not a bug to close. This migration honors 0002: content edits are
-- blocked while status = 'submitted' or beyond ('approved'/'paid'),
-- but allowed again once status = 'rejected'. Flag this if that's not
-- what you meant.
-- ============================================================


-- ============================================================
-- Minimal identity/role scaffolding
--
-- There is no agents/admin_accounts table yet, so this is
-- intentionally small: just enough to let "own invoice" and
-- "invoice reviewer/approver" mean something once auth exists.
-- Rows are provisioned by a service-role script/seed for now — no
-- client-facing insert/update policy exists (that arrives with the
-- fuller role/admin migration).
-- ============================================================

create table if not exists public.profiles (
  id                    uuid primary key references auth.users(id) on delete cascade,
  agent_id              text,      -- matches invoices.agent_id's free-text id; null for non-agent accounts
  is_invoice_reviewer   boolean not null default false,  -- can view all invoices (legacy INVOICE_REVIEWERS)
  is_invoice_approver   boolean not null default false,  -- can approve/reject/pay, edit QPI qualifications (legacy INVOICE_APPROVERS)
  created_at            timestamptz not null default now()
);

alter table public.profiles enable row level security;

create policy "Users can read their own profile"
on public.profiles
for select
to authenticated
using (id = auth.uid());

-- No insert/update/delete policy on profiles yet — provisioned via
-- service role until a real admin-management flow exists.

create or replace function public.current_agent_id()
returns text
language sql stable security definer set search_path = public as $$
  select agent_id from public.profiles where id = auth.uid();
$$;

create or replace function public.is_invoice_reviewer()
returns boolean
language sql stable security definer set search_path = public as $$
  select coalesce((select is_invoice_reviewer from public.profiles where id = auth.uid()), false);
$$;

create or replace function public.is_invoice_approver()
returns boolean
language sql stable security definer set search_path = public as $$
  select coalesce((select is_invoice_approver from public.profiles where id = auth.uid()), false);
$$;

grant execute on function public.current_agent_id() to authenticated;
grant execute on function public.is_invoice_reviewer() to authenticated;
grant execute on function public.is_invoice_approver() to authenticated;


-- ============================================================
-- invoices — replace the 0001 interim policy with real, granular ones
-- ============================================================

drop policy if exists "Authenticated users can view invoices" on public.invoices;

-- Note: 0005's "TEMP: allow anon read access" policy is untouched and
-- still applies to the `anon` role. It has nothing to do with the
-- policies below, which only apply to `authenticated`.

create policy "invoices_select_own"
on public.invoices
for select
to authenticated
using (agent_id = public.current_agent_id());

create policy "invoices_select_reviewer_or_approver"
on public.invoices
for select
to authenticated
using (public.is_invoice_reviewer() or public.is_invoice_approver());

create policy "invoices_insert_own"
on public.invoices
for insert
to authenticated
with check (
  agent_id = public.current_agent_id()
  and status = 'draft'
);

-- Owner can update/transition their own invoice while it's in an
-- editable-by-them state: draft (not yet sent), submitted (can still
-- retract back to draft), or rejected (per 0002, meant to be edited
-- and resubmitted). Never while approved or paid.
create policy "invoices_update_owner"
on public.invoices
for update
to authenticated
using (
  agent_id = public.current_agent_id()
  and status in ('draft', 'submitted', 'rejected')
)
with check (
  agent_id = public.current_agent_id()
  and status in ('draft', 'submitted', 'rejected')
);

-- Approvers have full update rights: approve, reject, unapprove/reverse,
-- and mark paid. (Marking paid rides on this same flag for now, pending
-- a dedicated billing/finance role in a later migration.)
create policy "invoices_update_approver"
on public.invoices
for update
to authenticated
using (public.is_invoice_approver())
with check (public.is_invoice_approver());

create policy "invoices_delete_own_draft"
on public.invoices
for delete
to authenticated
using (
  agent_id = public.current_agent_id()
  and status = 'draft'
);

-- RLS is row-level and can't see "which column changed" on its own —
-- this trigger blocks content edits (line items / totals) once an
-- invoice is submitted-and-awaiting-review or beyond, while still
-- allowing pure status-only transitions (submit/retract) and allowing
-- edits again once rejected. Approvers are exempt (they may need to
-- adjust before/at approval).
create or replace function public.invoices_restrict_content_edit_when_submitted()
returns trigger language plpgsql as $$
begin
  if not public.is_invoice_approver()
     and old.status = 'submitted'
     and (
       new.items is distinct from old.items
       or new.subtotal is distinct from old.subtotal
       or new.tax is distinct from old.tax
       or new.total is distinct from old.total
       or new.monthly_base is distinct from old.monthly_base
       or new.rate_snapshot is distinct from old.rate_snapshot
       or new.weeks is distinct from old.weeks
     ) then
    raise exception 'Cannot edit invoice content while awaiting review — retract to draft first';
  end if;
  return new;
end;
$$;

create trigger trg_invoices_restrict_content_edit_when_submitted
  before update on public.invoices
  for each row execute function public.invoices_restrict_content_edit_when_submitted();


-- ============================================================
-- qpi_qualifications — new table
-- agent_id is free text (matching invoices.agent_id), not a foreign
-- key — there is no agents table yet in this project.
-- ============================================================

create table if not exists public.qpi_qualifications (
  id           uuid primary key default gen_random_uuid(),
  agent_id     text not null,
  quarter_key  text not null,   -- e.g. "2026-Q2"
  trust_score  boolean not null default false,
  sla          boolean not null default false,
  continuity   boolean not null default false,
  updated_at   timestamptz not null default now(),
  unique (agent_id, quarter_key)
);

alter table public.qpi_qualifications enable row level security;

create policy "qpi_qualifications_select_own"
on public.qpi_qualifications
for select
to authenticated
using (agent_id = public.current_agent_id());

create policy "qpi_qualifications_select_approver"
on public.qpi_qualifications
for select
to authenticated
using (public.is_invoice_approver());

create policy "qpi_qualifications_insert_approver"
on public.qpi_qualifications
for insert
to authenticated
with check (public.is_invoice_approver());

create policy "qpi_qualifications_update_approver"
on public.qpi_qualifications
for update
to authenticated
using (public.is_invoice_approver())
with check (public.is_invoice_approver());

create policy "qpi_qualifications_delete_approver"
on public.qpi_qualifications
for delete
to authenticated
using (public.is_invoice_approver());