-- Company SMTP accounts (multi-account email platform)

CREATE TABLE IF NOT EXISTS company_smtp_accounts (
  id INT AUTO_INCREMENT PRIMARY KEY,
  label VARCHAR(120) NOT NULL,
  provider_type ENUM('gmail', 'workspace', 'yahoo', 'outlook', 'custom') NOT NULL DEFAULT 'custom',
  from_name VARCHAR(120) NOT NULL,
  from_email VARCHAR(255) NOT NULL,
  reply_to_email VARCHAR(255) NULL,
  host VARCHAR(255) NOT NULL,
  port INT NOT NULL DEFAULT 587,
  encryption ENUM('none', 'tls', 'ssl') NOT NULL DEFAULT 'tls',
  username VARCHAR(255) NOT NULL,
  password_encrypted TEXT NOT NULL,
  status ENUM('active', 'paused', 'disabled') NOT NULL DEFAULT 'active',
  tier ENUM('fresh', 'warming', 'established') NOT NULL DEFAULT 'fresh',
  health_status ENUM('healthy', 'warning', 'critical', 'unknown') NOT NULL DEFAULT 'unknown',
  health_score TINYINT UNSIGNED NOT NULL DEFAULT 0,
  daily_cap INT UNSIGNED NOT NULL DEFAULT 15,
  hourly_cap INT UNSIGNED NOT NULL DEFAULT 10,
  sent_today INT UNSIGNED NOT NULL DEFAULT 0,
  sent_this_hour INT UNSIGNED NOT NULL DEFAULT 0,
  last_send_at DATETIME NULL,
  last_health_at DATETIME NULL,
  sending_profile_id INT NULL,
  notes TEXT NULL,
  created_by INT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted_at DATETIME NULL,
  INDEX idx_smtp_status (status),
  INDEX idx_smtp_deleted (deleted_at)
);

CREATE TABLE IF NOT EXISTS company_smtp_dns_checks (
  id INT AUTO_INCREMENT PRIMARY KEY,
  account_id INT NOT NULL,
  spf_ok TINYINT(1) NOT NULL DEFAULT 0,
  dkim_ok TINYINT(1) NOT NULL DEFAULT 0,
  dmarc_ok TINYINT(1) NOT NULL DEFAULT 0,
  details JSON NULL,
  checked_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_dns_account FOREIGN KEY (account_id) REFERENCES company_smtp_accounts(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS company_smtp_health_snapshots (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  account_id INT NOT NULL,
  snapshot_date DATE NOT NULL,
  sent INT UNSIGNED NOT NULL DEFAULT 0,
  delivered INT UNSIGNED NOT NULL DEFAULT 0,
  bounced INT UNSIGNED NOT NULL DEFAULT 0,
  complained INT UNSIGNED NOT NULL DEFAULT 0,
  deferred INT UNSIGNED NOT NULL DEFAULT 0,
  delivery_rate DECIMAL(5,2) NULL,
  bounce_rate DECIMAL(5,2) NULL,
  complaint_rate DECIMAL(5,2) NULL,
  inbox_rate_estimated DECIMAL(5,2) NULL COMMENT 'Requires ESP feedback; nullable',
  spam_rate_estimated DECIMAL(5,2) NULL COMMENT 'Requires ESP feedback; nullable',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_snapshot_account_date (account_id, snapshot_date),
  CONSTRAINT fk_snapshot_account FOREIGN KEY (account_id) REFERENCES company_smtp_accounts(id) ON DELETE CASCADE
);