

CREATE TABLE IF NOT EXISTS whatsapp_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    direction ENUM('IN','OUT') NOT NULL DEFAULT 'OUT',
    evento VARCHAR(50) NOT NULL,
    telefone VARCHAR(30) NULL,
    protocolo VARCHAR(30) NULL,
    payload_json LONGTEXT NULL,
    response_json LONGTEXT NULL,
    status_code INT NOT NULL DEFAULT 0,
    sucesso TINYINT(1) NOT NULL DEFAULT 0,
    mensagem VARCHAR(255) NULL,
    criado_em DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_whatsapp_logs_evento (evento),
    INDEX idx_whatsapp_logs_protocolo (protocolo)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS whatsapp_dispatches (
    id INT AUTO_INCREMENT PRIMARY KEY,
    ocorrencia_id INT NOT NULL,
    gatilho VARCHAR(50) NOT NULL,
    referencia VARCHAR(120) NOT NULL,
    telefone VARCHAR(30) NULL,
    mensagem TEXT NULL,
    payload_json LONGTEXT NULL,
    response_json LONGTEXT NULL,
    status_code INT NOT NULL DEFAULT 0,
    sucesso TINYINT(1) NOT NULL DEFAULT 0,
    criado_em DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_whatsapp_dispatch (ocorrencia_id, gatilho, referencia),
    CONSTRAINT fk_whatsapp_dispatch_ocorrencia FOREIGN KEY (ocorrencia_id) REFERENCES ocorrencias(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
