-- 097_hubspot_sms.sql — HubSpot → SMS: fila de entrada e correlação (29/08/2026)
--
-- POR QUE EXISTE
-- O SMS que a UNISUAM dispara pelo HubSpot vivia no painel Laravel. A migração
-- traz o fluxo inteiro para cá: webhook → fila → MKOM → crédito → Timeline.
--
-- POR QUE ESTE ARQUIVO, se o db:push já cria estrutura a partir do schema.ts:
-- tudo aqui é ADITIVO e idempotente (CREATE ... IF NOT EXISTS / ADD COLUMN IF
-- NOT EXISTS). Serve para aplicar em DEV sem passar pelo db:push — que é
-- justamente o comando capaz de DROPAR tabela (60+ perdidas em produção, ver
-- o portão da casa). Em produção o db:push chega antes e este arquivo vira
-- no-op; se chegar primeiro, também está correto.

-- ═══ 1. Fila de pedidos de ENVIO vindos do CRM ═══
--
-- Não reaproveita rcs_event_queue de propósito: aquela é de callbacks de SAÍDA
-- (status/resposta de mensagem já enviada). Esta é da direção oposta, com
-- outra consequência quando falha — aqui, falhar significa SMS não entregue.
CREATE TABLE IF NOT EXISTS hubspot_sms_queue (
  id bigserial PRIMARY KEY,
  -- "portal" = workflow novo, batendo direto na URL do portal.
  -- "painel" = workflow antigo, repassado pelo Laravel. É o que mede a
  -- migração dos workflows do cliente sem precisar perguntar a ele.
  entrada varchar(10) NOT NULL DEFAULT 'portal',
  -- Corpo BRUTO do HubSpot. Guardar cru é o que permite reprocessar um evento
  -- antigo com parser novo — no painel, `on_callback_logs` provou isso ao
  -- longo de 800 mil registros.
  payload jsonb NOT NULL,
  status varchar(12) NOT NULL DEFAULT 'pending',   -- pending|processing|done|failed
  attempt_count integer NOT NULL DEFAULT 0,
  last_error text,
  -- Preenchidos depois do parse: diagnóstico sem abrir o JSON.
  portal_id varchar(64),
  contact_object_id varchar(128),
  -- Qual sms_messages nasceu deste item. NULL quando barrado por opt-out,
  -- duplicata, saldo ou payload inválido — o motivo fica em last_error.
  sms_message_id varchar,
  created_at timestamptz DEFAULT now(),
  processed_at timestamptz
);

CREATE INDEX IF NOT EXISTS idx_hubspot_sms_queue_status
  ON hubspot_sms_queue (status, id);
CREATE INDEX IF NOT EXISTS idx_hubspot_sms_queue_portal
  ON hubspot_sms_queue (portal_id, created_at);

-- ═══ 2. Correlação CRM no outbox de SMS ═══
--
-- O envio vindo do HubSpot NÃO tem campanha (é 1-a-1, disparado por workflow).
-- Sem estas três colunas não há como deduplicar o disparo, listá-lo em
-- /envios-crm nem devolver o status para a timeline do contato certo.
-- Espelham o que waba_messages e rcs_messages já carregam.
ALTER TABLE sms_messages ADD COLUMN IF NOT EXISTS portal_id varchar(64);
ALTER TABLE sms_messages ADD COLUMN IF NOT EXISTS source varchar(20);
ALTER TABLE sms_messages ADD COLUMN IF NOT EXISTS contact_object_id varchar(128);

-- Dedupe do disparo: portal + contato + janela de tempo. Mesmo molde do
-- waba_messages_portal_contact_sent_at_idx — a consulta é sempre ancorada nas
-- três colunas, então continua O(log n) com a tabela grande.
CREATE INDEX IF NOT EXISTS idx_sms_messages_portal_contact_sent_at
  ON sms_messages (portal_id, contact_object_id, sent_at);