-- Invoices replace the transferred_at lock as the primary "this entry
-- is billed" signal. Each entry can be attached to at most one
-- invoice. Attaching locks the entry the same way transferred_at used
-- to lock it (Adi can't edit; only owners can detach + re-edit).
--
-- The transferred_at / transferred_by / transfer_batch_id columns
-- stay on time_entries as historical audit timestamps (the migration
-- below preserves the values verbatim). New writes don't touch them;
-- canEditEntry's lock check shifts to invoice_id IS NOT NULL.
--
-- Owner-only operation (canTransfer-equivalent). Adi can see invoices
-- attached to entries he can see (via RLS) but can't create / edit.

-- ============================================================
-- 1. invoices table
-- ============================================================
create table if not exists public.invoices (
  id uuid primary key default gen_random_uuid(),
  org_id uuid not null references public.organizations(id) on delete cascade,
  name text not null,
  status text not null default 'open'
    check (status in ('open', 'sent', 'paid')),
  operator_id uuid references public.operators(id) on delete set null,
  client_id uuid references public.clients(id) on delete set null,
  /** Owner override for cases where the printed invoice total differs
   * from sum(billout_amount_usd). Stored separately so per-row stamps
   * stay honest. NULL = use sum. */
  manual_total_usd numeric(10, 2),
  notes text,
  created_by uuid references auth.users(id) on delete set null,
  created_at timestamptz not null default now(),
  sent_at timestamptz,
  paid_at timestamptz
);

-- One invoice name per org (case-insensitive). Lets us spot "2026-04
-- PlusROI" duplicates regardless of casing.
create unique index if not exists invoices_org_name_lower_uq
  on public.invoices (org_id, lower(name));

create index if not exists invoices_org_status_idx
  on public.invoices (org_id, status);
create index if not exists invoices_operator_idx
  on public.invoices (operator_id);
create index if not exists invoices_client_idx
  on public.invoices (client_id);

comment on table public.invoices is
  'Invoice an entry can be attached to. Attaching locks the entry from non-owner edits — replaces the transferred_at lock.';

-- ============================================================
-- 2. RLS — mirror time_entries scoping (org members see, owners write)
-- ============================================================
alter table public.invoices enable row level security;

drop policy if exists "invoices_org_select" on public.invoices;
create policy "invoices_org_select"
  on public.invoices for select
  to authenticated
  using (public.is_org_member(org_id));

drop policy if exists "invoices_org_insert" on public.invoices;
create policy "invoices_org_insert"
  on public.invoices for insert
  to authenticated
  with check (public.is_org_member(org_id));

drop policy if exists "invoices_org_update" on public.invoices;
create policy "invoices_org_update"
  on public.invoices for update
  to authenticated
  using (public.is_org_member(org_id))
  with check (public.is_org_member(org_id));

drop policy if exists "invoices_org_delete" on public.invoices;
create policy "invoices_org_delete"
  on public.invoices for delete
  to authenticated
  using (public.is_org_member(org_id));

-- ============================================================
-- 3. time_entries: add invoice_id + applied audit fields
-- ============================================================
alter table public.time_entries
  add column if not exists invoice_id uuid
    references public.invoices(id) on delete set null,
  add column if not exists invoice_applied_at timestamptz,
  add column if not exists invoice_applied_by uuid
    references auth.users(id) on delete set null;

create index if not exists time_entries_invoice_id_idx
  on public.time_entries (invoice_id)
  where invoice_id is not null;

comment on column public.time_entries.invoice_id is
  'Invoice this entry is attached to. NOT NULL = locked (only owners can detach + edit). Replaces transferred_at as the primary lock signal.';

-- ============================================================
-- 4. Backfill: every transferred entry gets a "Legacy transfers"
--    invoice in its org. One invoice per org, status=paid, holds
--    every entry that was already marked transferred when this
--    migration ran. Preserves applied_at/applied_by from the old
--    columns so audit trail is intact.
-- ============================================================
insert into public.invoices (org_id, name, status, notes)
select distinct te.org_id,
                'Legacy transfers',
                'paid',
                'Auto-created during the transfer→invoice migration. Holds all entries that were marked transferred before invoices existed. transferred_at on each entry preserved as the applied-at timestamp.'
from public.time_entries te
where te.transferred_at is not null
  and not exists (
    select 1 from public.invoices i
    where i.org_id = te.org_id and lower(i.name) = 'legacy transfers'
  );

update public.time_entries te
set invoice_id = i.id,
    invoice_applied_at = te.transferred_at,
    invoice_applied_by = te.transferred_by
from public.invoices i
where i.org_id = te.org_id
  and lower(i.name) = 'legacy transfers'
  and te.transferred_at is not null
  and te.invoice_id is null;
