-- mass e-mail to clients (filtered), sent by the cron in batches
CREATE TABLE mass_mails (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  subject VARCHAR(255) NOT NULL,
  body MEDIUMTEXT NOT NULL,
  filters VARCHAR(2000) NOT NULL DEFAULT '',
  important TINYINT(1) NOT NULL DEFAULT 0 COMMENT '1 = also to clients who switched general e-mails off',
  total INT UNSIGNED NOT NULL DEFAULT 0,
  sent INT UNSIGNED NOT NULL DEFAULT 0,
  admin_id INT UNSIGNED NULL,
  created_at DATETIME NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE mail_queue (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  mass_id INT UNSIGNED NOT NULL,
  client_id INT UNSIGNED NOT NULL,
  status VARCHAR(8) NOT NULL DEFAULT 'queued' COMMENT 'queued|sent|failed',
  sent_at DATETIME NULL,
  KEY queue (status, id),
  UNIQUE KEY one (mass_id, client_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;