CREATE TABLE agent_monthly_payments (
id INTEGER PRIMARY KEY AUTOINCREMENT,
agent_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) DEFAULT 0.00,
payment_method TEXT DEFAULT 'cash',
notes TEXT,
status TEXT DEFAULT 'completed' CHECK (status IN ('completed', 'cancelled')),
paid_at DATETIME DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (agent_id) REFERENCES agents(id) ON DELETE CASCADE,
FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE,
FOREIGN KEY (invoice_id) REFERENCES invoices(id) ON DELETE CASCADE
)