CREATE TABLE payments (
id INTEGER PRIMARY KEY AUTOINCREMENT,
invoice_id INTEGER NOT NULL,
amount DECIMAL(10,2) NOT NULL,
payment_date DATETIME DEFAULT CURRENT_TIMESTAMP,
payment_method TEXT NOT NULL,
reference_number TEXT,
notes TEXT,
remittance_status TEXT CHECK(remittance_status IN ('pending', 'remitted', 'cancelled')),
remittance_date DATETIME,
remittance_notes TEXT,
collector_id INTEGER,
commission_amount DECIMAL(15,2) DEFAULT 0,
payment_type TEXT DEFAULT 'direct' CHECK(payment_type IN ('direct', 'collector', 'online', 'manual')),
FOREIGN KEY (invoice_id) REFERENCES invoices (id)
)