-- Task #337 — Usina Multi-BM: Monitoramento e Alertas
-- Tabelas de configuração e estado de alertas por e-mail (idempotente).

CREATE TABLE IF NOT EXISTS multibm_alert_config (
  key VARCHAR(32) PRIMARY KEY DEFAULT 'default',
  enabled BOOLEAN NOT NULL DEFAULT false,
  fail_rate_threshold INTEGER NOT NULL DEFAULT 30,
  min_volume INTEGER NOT NULL DEFAULT 20,
  window_minutes INTEGER NOT NULL DEFAULT 60,
  cooldown_minutes INTEGER NOT NULL DEFAULT 360,
  alert_quality_drop BOOLEAN NOT NULL DEFAULT true,
  recipients_csv TEXT NOT NULL DEFAULT '',
  smtp_host VARCHAR(255),
  smtp_port INTEGER,
  smtp_user VARCHAR(255),
  smtp_password TEXT,
  smtp_from VARCHAR(255),
  smtp_secure BOOLEAN NOT NULL DEFAULT false,
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS multibm_alert_state (
  alert_key VARCHAR(160) PRIMARY KEY,
  last_alerted_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  last_value VARCHAR(128)
);

-- Garante o singleton de configuração.
INSERT INTO multibm_alert_config (key) VALUES ('default')
ON CONFLICT (key) DO NOTHING;