-- Migration 008: Add support for credit notes
-- Add invoice_type column to distinguish between invoices and credit notes

ALTER TABLE supplier_invoices 
ADD COLUMN IF NOT EXISTS invoice_type VARCHAR(20) DEFAULT 'invoice' CHECK (invoice_type IN ('invoice', 'credit_note'));

-- Update existing records to be 'invoice' type
UPDATE supplier_invoices SET invoice_type = 'invoice' WHERE invoice_type IS NULL;

-- Add index for filtering by type
CREATE INDEX IF NOT EXISTS idx_supplier_invoices_type ON supplier_invoices(invoice_type);

-- Add document_type to extractions table
ALTER TABLE extractions
ADD COLUMN IF NOT EXISTS document_type_detected VARCHAR(20) CHECK (document_type_detected IN ('invoice', 'credit_note', 'receipt', 'other'));

-- Update system prompt context
COMMENT ON COLUMN supplier_invoices.invoice_type IS 'Type of document: invoice or credit_note';
COMMENT ON COLUMN extractions.document_type_detected IS 'AI-detected document type from extraction';