-- Add invoice_number column to invoices table if it doesn't exist
CREATE TABLE IF NOT EXISTS temp_table AS SELECT * FROM invoices;
DROP TABLE IF EXISTS invoices;
CREATE TABLE invoices (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    subscriber_id INTEGER NOT NULL,
    consumption_id INTEGER NOT NULL UNIQUE,
    amount DECIMAL(10,2) NOT NULL CHECK(amount >= 0),
    fixed_fees DECIMAL(10,2) NOT NULL CHECK(fixed_fees >= 0),
    penalties DECIMAL(10,2) DEFAULT 0 CHECK(penalties >= 0),
    total_amount DECIMAL(10,2) NOT NULL CHECK(total_amount = amount + fixed_fees + penalties),
    status TEXT CHECK(status IN ('paid', 'unpaid', 'cancelled')) NOT NULL DEFAULT 'unpaid',
    due_date DATE NOT NULL,
    payment_date DATE,
    payment_method TEXT CHECK(payment_method IN ('cash', 'check', 'transfer') OR payment_method IS NULL),
    invoice_number TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (subscriber_id) REFERENCES subscribers(id) ON DELETE RESTRICT,
    FOREIGN KEY (consumption_id) REFERENCES consumption(id) ON DELETE RESTRICT
);
INSERT INTO invoices 
SELECT 
    id, subscriber_id, consumption_id, amount, fixed_fees, penalties, 
    total_amount, status, due_date, payment_date, payment_method,
    NULL as invoice_number,
    created_at, updated_at
FROM temp_table;
DROP TABLE temp_table;

CREATE INDEX IF NOT EXISTS idx_invoices_subscriber ON invoices(subscriber_id);
CREATE INDEX IF NOT EXISTS idx_invoices_status ON invoices(status);
CREATE INDEX IF NOT EXISTS idx_invoices_due_date ON invoices(due_date);
