USE TESVISCOVENTURES;

CREATE TABLE IF NOT EXISTS newsletter_campaigns (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  subject VARCHAR(200) NOT NULL,
  body_html LONGTEXT NOT NULL,
  audience ENUM('confirmed','all','unsubscribed') NOT NULL DEFAULT 'confirmed',
  status ENUM('draft','sending','sent','failed') NOT NULL DEFAULT 'draft',
  recipient_count INT UNSIGNED NOT NULL DEFAULT 0,
  sent_count INT UNSIGNED NOT NULL DEFAULT 0,
  failed_count INT UNSIGNED NOT NULL DEFAULT 0,
  error_message TEXT NULL,
  created_by BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  sent_at DATETIME NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;