CREATE TABLE IF NOT EXISTS payment_reminders (
  id INT AUTO_INCREMENT PRIMARY KEY,
  customer_account_id INT NOT NULL,
  billing_account_no VARCHAR(20) NOT NULL,
  month INT NOT NULL,
  year INT NOT NULL,
  billing_period VARCHAR(7) NOT NULL,
  reminder_type ENUM('email', 'whatsapp', 'both') DEFAULT 'email',
  email_sent TINYINT(1) DEFAULT 0,
  email_sent_at DATETIME NULL,
  email_error TEXT NULL,
  whatsapp_sent TINYINT(1) DEFAULT 0,
  whatsapp_sent_at DATETIME NULL,
  whatsapp_error TEXT NULL,
  days_overdue INT DEFAULT 0,
  sent_by VARCHAR(50),
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_reminder (billing_account_no, billing_period)
);
