-- Update invoice status check constraint to support all new statuses
-- SQLite doesn't support ALTER CONSTRAINT directly, so we need to recreate the table

-- Create new invoices table with updated status constraint
CREATE TABLE invoices_new (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    invoice_number TEXT NOT NULL UNIQUE,
    client_id INTEGER NOT NULL,
    proposal_id INTEGER,
    contract_id INTEGER,
    invoice_date TEXT NOT NULL,
    due_date TEXT NOT NULL,
    status TEXT DEFAULT 'draft' CHECK(status IN ('draft', 'created', 'sent', 'void', 'cancelled', 'unpaid', 'partially_paid', 'paid')),
    subtotal REAL NOT NULL DEFAULT 0.00,
    tax_total REAL NOT NULL DEFAULT 0.00,
    total REAL NOT NULL DEFAULT 0.00,
    paid_amount REAL NOT NULL DEFAULT 0.00,
    currency TEXT DEFAULT 'USD',
    payment_terms TEXT,
    notes TEXT,
    company_name TEXT,
    company_address TEXT,
    tax_exempt INTEGER DEFAULT 0,
    tax_exemption_reason TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    invoice_subject TEXT,
    tax_method TEXT,
    invoice_level_tax_bracket_id INTEGER,
    pdf_path TEXT,
    FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE RESTRICT
);

-- Copy data from old table
INSERT INTO invoices_new 
SELECT * FROM invoices;

-- Drop old table
DROP TABLE invoices;

-- Rename new table
ALTER TABLE invoices_new RENAME TO invoices;

-- Recreate indexes
CREATE INDEX idx_invoices_client_id ON invoices(client_id);
CREATE INDEX idx_invoices_status ON invoices(status);
CREATE INDEX idx_invoices_invoice_date ON invoices(invoice_date);
