-- Invoices table
-- Mirrors the invoice object built by buildInvoice() in the KHAIZEN CS
-- Command Center Apps Script (JavaScript.html), migrated from the chunked
-- state.invoices JSON blob into a real table.

create table if not exists invoices (
  -- Identity
  -- App-generated, not a uuid: "inv_<agentId>_<monthStr>_c<cutNumber>"
  id text primary key,
  invoice_number text not null,

  -- Who / when this invoice covers
  agent_id text not null,
  month text not null,        -- "YYYY-MM"
  cut_number smallint not null check (cut_number in (1, 2)),
  weeks jsonb not null default '[]'::jsonb,   -- e.g. ["2026-W23", "2026-W24"]

  -- Line items
  -- Standardized shape per item: {type, label, quantity, amount, meta}
  -- meta holds type-specific extras (weekKey, isHoliday, usesCredit,
  -- holidayDate, bonusDays, _qpiKey, _qpiBase, _qpiPct, etc.)
  items jsonb not null default '[]'::jsonb,

  -- Totals
  subtotal numeric(12, 2) not null default 0,
  tax numeric(12, 2) not null default 0,
  total numeric(12, 2) not null default 0,
  monthly_base numeric(12, 2),   -- only set on cut_number = 2

  -- Rate snapshot at generation time
  -- {usd, php, rate, rateFromLog, cutoffRateLabel, creditDaysUsed}
  rate_snapshot jsonb,
  usd_php_rate numeric(12, 4),
  hourly_rate_usd numeric(12, 2),

  -- Snapshots of profile / billing info at generation time
  profile_snapshot jsonb,
  bill_to jsonb,

  -- Approval chain
  -- draft -> submitted -> approved -> paid
  --                    \-> rejected (back to draft in the app)
  -- Approver identities stay hardcoded in application code
  -- (INVOICE_APPROVERS = Quinty, Edwin) -- no roles table.
  status text not null default 'draft'
    check (status in ('draft', 'submitted', 'approved', 'rejected', 'paid')),

  submitted_at timestamptz,
  approved_at timestamptz,
  approved_by text,
  rejected_at timestamptz,
  rejected_by text,
  reject_reason text,
  paid_at timestamptz,
  paid_by text,

  -- Quarterly performance incentive notes (cut 2 only, free text)
  qpi_notes text,

  -- Bookkeeping
  created_at timestamptz not null default now(),
  created_by text
);

create index if not exists invoices_agent_id_idx on invoices (agent_id);
create index if not exists invoices_month_idx on invoices (month);
create index if not exists invoices_status_idx on invoices (status);

-- Row Level Security
-- Interim policy: any authenticated user can read all invoices.
-- Per-role restrictions (agent sees own invoices, approvers see all, etc.)
-- come later once auth/roles are wired up.
alter table invoices enable row level security;

create policy "Authenticated users can view invoices"
on invoices
for select
to authenticated
using (true);