CREATE TABLE collector_payments (
id INTEGER PRIMARY KEY AUTOINCREMENT,
collector_id INTEGER NOT NULL,
customer_id INTEGER NOT NULL,
invoice_id INTEGER NOT NULL,
payment_amount DECIMAL(15,2) NOT NULL,
commission_amount DECIMAL(15,2) NOT NULL,
payment_method TEXT DEFAULT 'cash' CHECK(payment_method IN ('cash', 'transfer', 'other')),
payment_date DATETIME DEFAULT CURRENT_TIMESTAMP,
notes TEXT,
status TEXT DEFAULT 'completed' CHECK(status IN ('completed', 'pending', 'cancelled')),
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
collected_at DATETIME,
FOREIGN KEY (collector_id) REFERENCES collectors(id),
FOREIGN KEY (customer_id) REFERENCES customers(id),
FOREIGN KEY (invoice_id) REFERENCES invoices(id)
)