-- ============================================================
--  CareBill — bounce handling, suppression and send pacing.
--  Existing database:  mysql carebill < sql/deliverability.sql
-- ============================================================

-- addresses we must stop writing to. A hard bounce or a spam complaint that we
-- keep sending to is what gets a sending account suspended.
CREATE TABLE suppressions (
  id            BIGINT AUTO_INCREMENT PRIMARY KEY,
  email         VARCHAR(160) NOT NULL,
  reason        ENUM('hard_bounce','complaint','soft_repeat','manual','invalid') NOT NULL,
  detail        VARCHAR(255),
  source        VARCHAR(20) DEFAULT 'webhook',
  client_id     INT NULL,
  released_at   DATETIME NULL,
  released_by   INT NULL,
  created_at    DATETIME DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_supp (email),
  INDEX ix_supp_reason (reason)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE delivery_events (
  id            BIGINT AUTO_INCREMENT PRIMARY KEY,
  email         VARCHAR(160) NOT NULL,
  event         ENUM('sent','delivered','soft_bounce','hard_bounce','complaint','rejected','open','click') NOT NULL,
  outbox_id     BIGINT NULL,
  message_id    VARCHAR(200) NULL,
  diagnostic    VARCHAR(400),
  provider      VARCHAR(20),
  created_at    DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX ix_de_email (email, created_at), INDEX ix_de_event (event, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE outbox ADD COLUMN message_id VARCHAR(200) NULL AFTER last_error;

INSERT INTO settings (k,v) VALUES
 ('deliver_warmup_start',''), ('deliver_domain_hourly','30'),
 ('deliver_soft_limit','4'), ('mailhook_key','')
ON DUPLICATE KEY UPDATE k=k;