-- First tool in the /tools section: Bill Through Credit Card Expenses.
--
-- Workflow: paste raw lines from a credit-card statement into a batch,
-- assign each line to a (client × project) + description + category,
-- then copy a tab-separated paste block into the destination Google
-- Sheet for billing pass-through.
--
-- Phase 1: manual per-line assignment only. Phase 2 will add
-- highlight keywords + auto-assign rules (deferred).

-- ============================================================
-- 1. Batches
-- ============================================================
create table if not exists public.cc_expense_batches (
  id uuid primary key default gen_random_uuid(),
  org_id uuid not null references public.organizations(id) on delete cascade,
  name text not null,
  created_by uuid references auth.users(id) on delete set null,
  created_at timestamptz not null default now()
);

create index if not exists cc_expense_batches_org_idx
  on public.cc_expense_batches(org_id);

comment on table public.cc_expense_batches is
  'Tools / Bill Through Credit Card Expenses: one batch per CC statement (or arbitrary group). Holds parsed expense lines that get assigned to projects and exported to the PlusROI sheet.';

-- ============================================================
-- 2. Lines
-- ============================================================
create table if not exists public.cc_expense_lines (
  id uuid primary key default gen_random_uuid(),
  batch_id uuid not null
    references public.cc_expense_batches(id) on delete cascade,
  /** Order within the batch — mirrors paste order so the UI list
   * matches what the user pasted in. */
  line_index integer not null,
  /** Original raw line text from the paste, for audit / re-parse. */
  raw_line text not null,
  /** Parsed columns. NULL when the parser couldn't extract that field. */
  expense_date date,
  description text,
  amount_usd numeric(12, 2),
  balance_usd numeric(12, 2),
  /** Assignment: which project this expense pass-throughs to.
   * Free until assigned. */
  project_id uuid references public.projects(id) on delete set null,
  assignment_description text,
  category text,
  created_at timestamptz not null default now()
);

create index if not exists cc_expense_lines_batch_idx
  on public.cc_expense_lines(batch_id);
create index if not exists cc_expense_lines_project_idx
  on public.cc_expense_lines(project_id)
  where project_id is not null;

-- ============================================================
-- 3. RLS — org members read/write; owner-only at the UI layer
-- ============================================================
alter table public.cc_expense_batches enable row level security;

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

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

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

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

alter table public.cc_expense_lines enable row level security;

-- Lines inherit visibility via their batch's org_id.
drop policy if exists "cc_lines_via_batch_select" on public.cc_expense_lines;
create policy "cc_lines_via_batch_select"
  on public.cc_expense_lines for select
  to authenticated
  using (
    exists (
      select 1 from public.cc_expense_batches b
      where b.id = batch_id and public.is_org_member(b.org_id)
    )
  );

drop policy if exists "cc_lines_via_batch_insert" on public.cc_expense_lines;
create policy "cc_lines_via_batch_insert"
  on public.cc_expense_lines for insert
  to authenticated
  with check (
    exists (
      select 1 from public.cc_expense_batches b
      where b.id = batch_id and public.is_org_member(b.org_id)
    )
  );

drop policy if exists "cc_lines_via_batch_update" on public.cc_expense_lines;
create policy "cc_lines_via_batch_update"
  on public.cc_expense_lines for update
  to authenticated
  using (
    exists (
      select 1 from public.cc_expense_batches b
      where b.id = batch_id and public.is_org_member(b.org_id)
    )
  )
  with check (
    exists (
      select 1 from public.cc_expense_batches b
      where b.id = batch_id and public.is_org_member(b.org_id)
    )
  );

drop policy if exists "cc_lines_via_batch_delete" on public.cc_expense_lines;
create policy "cc_lines_via_batch_delete"
  on public.cc_expense_lines for delete
  to authenticated
  using (
    exists (
      select 1 from public.cc_expense_batches b
      where b.id = batch_id and public.is_org_member(b.org_id)
    )
  );
