-- Update the invoices table to allow partially_paid status
DROP TRIGGER IF EXISTS update_invoices_timestamp;

CREATE TEMPORARY TABLE invoices_backup(
    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', 'partially_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_backup 
SELECT * FROM invoices;

DROP TABLE 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', 'partially_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 * FROM invoices_backup;

DROP TABLE invoices_backup;

CREATE TRIGGER update_invoices_timestamp
   AFTER UPDATE ON invoices
BEGIN
   UPDATE invoices SET updated_at = CURRENT_TIMESTAMP WHERE id = NEW.id;
END;
