CREATE TABLE collector_assignments (
id INTEGER PRIMARY KEY AUTOINCREMENT,
collector_id INTEGER NOT NULL,
customer_id INTEGER NOT NULL,
assigned_date DATETIME DEFAULT CURRENT_TIMESTAMP,
status TEXT DEFAULT 'active' CHECK(status IN ('active', 'completed', 'cancelled')),
notes TEXT,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (collector_id) REFERENCES collectors(id),
FOREIGN KEY (customer_id) REFERENCES customers(id),
UNIQUE(collector_id, customer_id)
)