-- migrate-v5.sql — Regras de automação de mensagens
-- Executar: wrangler d1 execute cdp-edge-db --file=migrate-v5.sql

CREATE TABLE IF NOT EXISTS automation_rules (
  id               INTEGER PRIMARY KEY AUTOINCREMENT,
  created_at       TEXT    NOT NULL DEFAULT (datetime('now')),
  name             TEXT    NOT NULL,           -- nome descritivo da regra
  trigger_event    TEXT    NOT NULL,           -- Lead | Purchase | InitiateCheckout | HighIntent
  channel          TEXT    NOT NULL,           -- whatsapp | email
  subject_template TEXT,                       -- assunto (só email), suporta {{variáveis}}
  message_template TEXT    NOT NULL,           -- corpo, suporta {{name}} {{email}} {{phone}} {{campaign}} {{intention}}
  delay_minutes    INTEGER NOT NULL DEFAULT 0, -- aguardar X minutos antes de enviar
  is_active        INTEGER NOT NULL DEFAULT 1  -- 1 = ativa, 0 = pausada
);

CREATE INDEX IF NOT EXISTS idx_auto_rules_event  ON automation_rules(trigger_event);
CREATE INDEX IF NOT EXISTS idx_auto_rules_active ON automation_rules(is_active);
