-- SMTP auto-warmup engine (replaces manual tier caps)

ALTER TABLE company_smtp_accounts
  ADD COLUMN warmup_enabled TINYINT(1) NOT NULL DEFAULT 1 AFTER tier,
  ADD COLUMN warmup_started_at DATETIME NULL AFTER warmup_enabled,
  ADD COLUMN warmup_phase ENUM('bootstrap','ramping','stable','mature','recovery','paused') NOT NULL DEFAULT 'bootstrap' AFTER warmup_started_at,
  ADD COLUMN warmup_day INT UNSIGNED NOT NULL DEFAULT 1 AFTER warmup_phase,
  ADD COLUMN warmup_health_score TINYINT UNSIGNED NOT NULL DEFAULT 0 AFTER warmup_day,
  ADD COLUMN sent_today_warmup INT UNSIGNED NOT NULL DEFAULT 0 AFTER sent_today,
  ADD COLUMN sent_today_campaign INT UNSIGNED NOT NULL DEFAULT 0 AFTER sent_today_warmup,
  ADD COLUMN campaigns_unlocked_at DATETIME NULL AFTER sent_this_hour,
  ADD COLUMN max_daily_ceiling INT UNSIGNED NULL COMMENT 'Optional admin override ceiling' AFTER hourly_cap,
  ADD COLUMN disable_slow_ramp TINYINT(1) NOT NULL DEFAULT 0 AFTER max_daily_ceiling;

UPDATE company_smtp_accounts
SET warmup_started_at = COALESCE(warmup_started_at, created_at),
    warmup_day = GREATEST(1, DATEDIFF(CURDATE(), DATE(COALESCE(warmup_started_at, created_at))) + 1),
    warmup_phase = CASE
      WHEN status = 'paused' THEN 'paused'
      WHEN tier = 'established' THEN 'stable'
      WHEN tier = 'warming' THEN 'ramping'
      ELSE 'bootstrap'
    END
WHERE deleted_at IS NULL;

ALTER TABLE sending_limit_profiles
  ADD COLUMN warmup_global_daily_ceiling INT UNSIGNED NOT NULL DEFAULT 50 AFTER tier_established_daily_cap,
  ADD COLUMN warmup_min_reserve_pct TINYINT UNSIGNED NOT NULL DEFAULT 25 AFTER warmup_global_daily_ceiling,
  ADD COLUMN warmup_campaign_unlock_day INT UNSIGNED NOT NULL DEFAULT 14 AFTER warmup_min_reserve_pct,
  ADD COLUMN warmup_campaign_unlock_health TINYINT UNSIGNED NOT NULL DEFAULT 90 AFTER warmup_campaign_unlock_day;

CREATE TABLE IF NOT EXISTS smtp_warmup_events (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  account_id INT NOT NULL,
  direction ENUM('outbound','inbound') NOT NULL DEFAULT 'outbound',
  peer_account_id INT NULL,
  peer_email VARCHAR(255) NULL,
  subject VARCHAR(255) NULL,
  placement ENUM('inbox','spam','unknown') NOT NULL DEFAULT 'unknown',
  opened TINYINT(1) NOT NULL DEFAULT 0,
  replied TINYINT(1) NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_warmup_events_account (account_id, created_at),
  CONSTRAINT fk_warmup_events_account FOREIGN KEY (account_id) REFERENCES company_smtp_accounts(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS smtp_warmup_state_log (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  account_id INT NOT NULL,
  from_phase VARCHAR(32) NOT NULL,
  to_phase VARCHAR(32) NOT NULL,
  reason VARCHAR(500) NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_warmup_state_account (account_id, created_at),
  CONSTRAINT fk_warmup_state_account FOREIGN KEY (account_id) REFERENCES company_smtp_accounts(id) ON DELETE CASCADE
);