-- Add amount_paid column to invoices table
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),
    amount_paid DECIMAL(10,2) DEFAULT 0 CHECK(amount_paid >= 0),
    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 
    id,
    subscriber_id,
    consumption_id,
    amount,
    fixed_fees,
    penalties,
    total_amount,
    CASE 
        WHEN status = 'paid' THEN total_amount
        ELSE 0
    END as amount_paid,
    status,
    due_date,
    payment_date,
    payment_method,
    invoice_number,
    created_at,
    updated_at
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),
    amount_paid DECIMAL(10,2) DEFAULT 0 CHECK(amount_paid >= 0),
    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;
