-- ── Invoice Credit Notes (wave-12) ───────────────────────────────────────────
-- Adds credit-note sequence type, partial indexes for DRAFT uniqueness and
-- parent_invoice_id lookup, and seeds credit_note sequence rows for existing tenants.
--
-- Note: invoices.source already includes 'credit_note' (from previous migration).
--       invoices_parent_invoice_id_invoices_id_fk already exists (from previous migration).
--
--> statement-breakpoint

-- 1. Widen invoice_sequences_type_check to admit 'credit_note'
ALTER TABLE invoice_sequences DROP CONSTRAINT IF EXISTS invoice_sequences_type_check;
--> statement-breakpoint
ALTER TABLE invoice_sequences ADD CONSTRAINT invoice_sequences_type_check
  CHECK (type IN ('invoice', 'proforma', 'credit_note'));
--> statement-breakpoint

-- 2. Partial index for fast parent→credit-note lookups
CREATE INDEX IF NOT EXISTS invoices_parent_invoice_id_idx
  ON invoices (parent_invoice_id)
  WHERE parent_invoice_id IS NOT NULL;
--> statement-breakpoint

-- 3. Enforce at most one DRAFT credit note per parent invoice (per tenant)
CREATE UNIQUE INDEX IF NOT EXISTS invoices_one_draft_credit_note_per_parent
  ON invoices (tenant_id, parent_invoice_id)
  WHERE source = 'credit_note' AND status = 'DRAFT';
--> statement-breakpoint

-- 4. Seed credit_note sequence rows for every existing tenant (idempotent)
INSERT INTO invoice_sequences (tenant_id, type, last_number, prefix)
SELECT id, 'credit_note', 0, 'CN-'
FROM tenants
ON CONFLICT (tenant_id, type) DO NOTHING;
