-- =====================================================================
-- Tabela: tb_automation
-- Projeto: f19 — banco w19_clikil41_eleicao
--
-- Template editável de automações de disparo (Tipo 1=lista, 2=grupo,
-- 3=status, 4=message, 5=meta). Substitui hook.f19/zapi/_automacoes/*.json.
--
-- Scope:
--   per_campaign     — template GENÉRICO, resolve tokens {campaign_*} em
--                      runtime contra TODAS as campanhas que o apoiador
--                      participa (1 template serve N campanhas).
--   single_campaign  — vinculada exclusivamente a id_campaign != NULL.
--
-- Flow:
--   simple         — apoiador dispara, f19 só avalia resposta
--   simple_reply   — apoiador dispara msg 1, f19 dispara msgs 2+ se respondem
--   complex        — multi-etapa com avaliação intermediária
--
-- JSON columns:
--   automation_match — { from_me, contains_all_tokens[], contains_any[], not_contains[] }
--   automation_steps — [{ step:int, via:'supporter_list'|'f19_send', text_template:str, delay_secs:int }]
--   automation_evaluation — { enabled, via:'chatgpt'|'claude', delay_secs_after_last_msg, categories[] }
-- =====================================================================

CREATE TABLE IF NOT EXISTS `tb_automation` (
  `id_automation`         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `id_campaign`           BIGINT UNSIGNED DEFAULT NULL,                -- NULL = global (per_campaign)
  `automation_name`       VARCHAR(150)    NOT NULL,
  `automation_type`       ENUM('broadcast','group','status','message','meta') NOT NULL,
  `automation_scope`      ENUM('per_campaign','single_campaign') NOT NULL DEFAULT 'per_campaign',
  `automation_flow`       ENUM('simple','simple_reply','complex') NOT NULL DEFAULT 'simple',
  `automation_status`     TINYINT(1)      NOT NULL DEFAULT 1,           -- 1 ativo · 0 pausado · -1 arquivado
  `automation_match`      JSON            DEFAULT NULL,
  `automation_steps`      JSON            DEFAULT NULL,
  `automation_evaluation` JSON            DEFAULT NULL,
  `automation_created_at` DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `automation_updated_at` DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id_automation`),
  KEY `ix_aut_camp_status` (`id_campaign`, `automation_status`),
  KEY `ix_aut_type_status` (`automation_type`, `automation_status`),
  CONSTRAINT `fk_aut_campaign` FOREIGN KEY (`id_campaign`)
    REFERENCES `tb_campaign` (`id_campaign`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;