-- =====================================================================
-- Tabela: tb_dispatch
-- Projeto: f19 — banco w19_clikil41_eleicao
--
-- Task header de disparos. 1 linha = 1 (automação × apoiador × dia).
-- Substitui o JSON _<tipo>_<id>_<YYYY-MM-DD>.json gerado em hook.f19/zapi/log/<phone>/disparo/.
--
-- O detalhe dos contatos (1 disparo = 1 linha) fica em tb_dispatch_item.
-- As mensagens recebidas ficam em tb_dispatch_message.
--
-- Agregados (qty_*) são denormalizados — atualizados explicitamente pelo
-- código PHP a cada INSERT/UPDATE em tb_dispatch_item. Sem trigger
-- (debuggable, sem lock estranho).
--
-- Unique key (id_automation, id_adm_user, dispatch_date) garante
-- idempotência: 1 task por dia mesmo se hook duplica.
-- =====================================================================

CREATE TABLE IF NOT EXISTS `tb_dispatch` (
  `id_dispatch`              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `id_automation`            BIGINT UNSIGNED NOT NULL,
  `id_campaign`              BIGINT UNSIGNED NOT NULL,
  `id_adm_user`              BIGINT UNSIGNED NOT NULL,                  -- apoiador dono
  `id_api`                   BIGINT UNSIGNED DEFAULT NULL,              -- instância Z-API usada (lookup por connectedPhone)
  `dispatch_type`            ENUM('broadcast','group','status','message','meta') NOT NULL,
  `dispatch_supporter_phone` VARCHAR(20)     NOT NULL,                  -- connectedPhone
  `dispatch_date`            DATE            NOT NULL,                  -- partição lógica por dia
  `dispatch_status`          ENUM('open','running','done','canceled') NOT NULL DEFAULT 'running',

  -- Agregados denormalizados (sempre derivam de tb_dispatch_item)
  `qty_total`                INT UNSIGNED    NOT NULL DEFAULT 0,
  `qty_processed`            INT UNSIGNED    NOT NULL DEFAULT 0,
  `qty_replied`              INT UNSIGNED    NOT NULL DEFAULT 0,
  `qty_evaluated`            INT UNSIGNED    NOT NULL DEFAULT 0,
  `qty_pending`              INT UNSIGNED    NOT NULL DEFAULT 0,
  `qty_positive`             INT UNSIGNED    NOT NULL DEFAULT 0,
  `qty_negative`             INT UNSIGNED    NOT NULL DEFAULT 0,
  `qty_neutral`              INT UNSIGNED    NOT NULL DEFAULT 0,
  `qty_no_reply`             INT UNSIGNED    NOT NULL DEFAULT 0,

  `dispatch_meta`            JSON            DEFAULT NULL,              -- {activator,app,endpoint,action,delay_ms}
  `dispatch_created_at`      DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `dispatch_updated_at`      DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  PRIMARY KEY (`id_dispatch`),
  UNIQUE KEY `ux_disp_per_day` (`id_automation`, `id_adm_user`, `dispatch_date`),
  KEY `ix_disp_status_date`    (`dispatch_status`, `dispatch_date`),
  KEY `ix_disp_camp_date`      (`id_campaign`, `dispatch_date`),
  KEY `ix_disp_user_date`      (`id_adm_user`, `dispatch_date`),
  CONSTRAINT `fk_disp_aut`     FOREIGN KEY (`id_automation`) REFERENCES `tb_automation` (`id_automation`) ON UPDATE CASCADE,
  CONSTRAINT `fk_disp_camp`    FOREIGN KEY (`id_campaign`)   REFERENCES `tb_campaign`   (`id_campaign`)   ON UPDATE CASCADE,
  CONSTRAINT `fk_disp_user`    FOREIGN KEY (`id_adm_user`)   REFERENCES `tb_adm_user`   (`id_adm_user`)   ON UPDATE CASCADE,
  CONSTRAINT `fk_disp_api`     FOREIGN KEY (`id_api`)        REFERENCES `tb_api`        (`id_api`)        ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;