-- Follow-up to 0001_create_invoices.sql: adds a "type" column so QPI
-- invoices can live in the same table as regular invoices instead of a
-- separate parallel system, and documents an intentional status change.

-- 1. "type" column: regular bi-monthly invoices vs QPI (Quarterly
-- Performance Incentive) invoices. These share ~90% of fields; QPI
-- invoices previously lived in their own state.quarterlyInvoices blob
-- in the legacy Apps Script app.
alter table invoices
  add column type text not null default 'regular'
    check (type in ('regular', 'qpi'));

-- 2. "rejected" status: intentional behavior change from the legacy app.
-- The legacy Apps Script app never actually set status to 'rejected' --
-- on reject it bounced the invoice back to 'draft' and recorded
-- rejected_at/rejected_by/reject_reason as separate fields, leaving
-- "rejected" indistinguishable from "never submitted." 'rejected' was
-- already a valid value in the 0001 status check constraint; this is
-- not a schema change, just a decision that the new system will
-- actually use it as a real, reachable terminal-ish state (an invoice
-- can still be edited and resubmitted from there) rather than mirroring
-- the legacy ambiguity.
comment on column invoices.status is
  'draft, submitted, approved, rejected, paid. Unlike the legacy Apps '
  'Script app, "rejected" is a real reachable state here (not bounced '
  'back to draft) -- intentional behavior change, not a bug.';