-- migrations/always/ensure_indexes.sql
--
-- Executado em TODO deploy (deploy.sh, passo 4, depois do runner de
-- migrations) — NÃO é uma migration numerada; é a rede de segurança dos
-- índices críticos. Motivo (16/07/2026): o drizzle-kit push DROPA índices
-- que não constam do shared/schema.ts — um deploy apagou 12 índices da
-- produção (atribuição de MO, dedupe do webhook, Alta Vazão). Os índices
-- SIMPLES foram espelhados no schema (o push os mantém/recria); os PARCIAIS
-- (WHERE ...) não podem ser espelhados com segurança (round-trip frágil do
-- kit), então este arquivo os re-garante a cada deploy.
--
-- ⚠ Criou um índice crítico novo numa migration NNN? Adicione-o AQUI também
--   (e, se for simples, espelhe no shared/schema.ts).
--
-- CONCURRENTLY: não bloqueia writes se precisar reconstruir em tabela
-- grande. psql -f roda em autocommit (sem BEGIN), então é permitido.
-- IF NOT EXISTS: no-op quando o índice já existe (caminho normal).

-- 0) Limpa índices INVALID desta lista (um CREATE CONCURRENTLY interrompido
--    deixa o índice inválido e o IF NOT EXISTS o pularia para sempre).
DO $$
DECLARE
  bad record;
BEGIN
  FOR bad IN
    SELECT c.relname AS idxname
    FROM pg_index i
    JOIN pg_class c ON c.oid = i.indexrelid
    WHERE NOT i.indisvalid
      AND c.relname IN (
        'idx_multibm_messages_phone',
        'idx_multibm_messages_campaign_status',
        'idx_multibm_messages_bm_sent',
        'idx_multibm_messages_sender_sent',
        'idx_multibm_messages_status_sent',
        'idx_multibm_messages_sent_at',
        'idx_multibm_messages_nonterminal',
        'uidx_multibm_messages_infobip_id',
        'idx_waba_messages_phone',
        'idx_waba_messages_campaign_id',
        'uidx_waba_messages_infobip_id',
        'idx_bm_senders_number',
        'idx_infobip_senders_number',
        'idx_kb_items_owner',
        'idx_ai_conversations_owner',
        'idx_ai_conversations_lookup',
        'idx_ai_conv_msgs_conversation',
        'uq_ai_conv_msgs_infobip_id',
        'uidx_gupshup_messages_gsid',
        'idx_gupshup_messages_nonterminal',
        'idx_gupshup_messages_wamid',
        'uidx_gupshup_billing_events_gs_id'
      )
  LOOP
    EXECUTE format('DROP INDEX IF EXISTS %I', bad.idxname);
    RAISE NOTICE 'ensure_indexes: índice INVALID % removido para recriação', bad.idxname;
  END LOOP;
END $$;

-- 1) PARCIAIS (fora do schema Drizzle de propósito)
CREATE UNIQUE INDEX CONCURRENTLY IF NOT EXISTS uidx_multibm_messages_infobip_id
  ON multibm_messages (infobip_message_id) WHERE infobip_message_id IS NOT NULL;
CREATE UNIQUE INDEX CONCURRENTLY IF NOT EXISTS uidx_waba_messages_infobip_id
  ON waba_messages (infobip_message_id) WHERE infobip_message_id IS NOT NULL;
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_multibm_messages_nonterminal
  ON multibm_messages (campaign_id) WHERE status NOT IN ('DELIVERED','SEEN','FAILED');

-- 2) SIMPLES (também espelhados no shared/schema.ts — aqui é o cinto extra)
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_multibm_messages_phone ON multibm_messages (phone);
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_multibm_messages_campaign_status ON multibm_messages (campaign_id, status);
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_multibm_messages_bm_sent ON multibm_messages (bm_id, sent_at);
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_multibm_messages_sender_sent ON multibm_messages (sender_id, sent_at);
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_multibm_messages_status_sent ON multibm_messages (status, sent_at);
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_multibm_messages_sent_at ON multibm_messages (sent_at);
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_waba_messages_phone ON waba_messages (phone);
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_waba_messages_campaign_id ON waba_messages (campaign_id);
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_bm_senders_number ON bm_senders (number);
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_infobip_senders_number ON infobip_senders (number);

-- Teto de lotes do link revr fixo (Parte C, 20/08/2026) — countTemplateLotesAtivos
-- (server/storage.ts) filtra campaigns por template_id a cada criação de
-- campanha Multi-BM; sem índice era sequential scan na tabela inteira.
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_campaigns_template_id ON campaigns (template_id);

-- ─── AGENTE DE IA (migration 015) ────────────────────────────────────────────
-- 2026-08-05 (achado no teste vivo da Fase 4a): o dev perdeu TODOS os índices
-- da 015 num db:push — inclusive o dedupe por wamid, sem o qual o
-- onConflictDoNothing do appendAiConversationMessage vira no-op e a MESMA MO
-- retransmitida gera DUAS respostas da IA. Os simples foram espelhados no
-- shared/schema.ts; o único parcial é re-garantido aqui.
--
-- Antes de recriar o único: remove duplicatas exatas de wamid (fruto do
-- período sem índice), preservando a linha mais antiga — sem isso o CREATE
-- UNIQUE falha e derruba o deploy.
DELETE FROM ai_conversation_messages a
  USING ai_conversation_messages b
  WHERE a.infobip_message_id IS NOT NULL
    AND a.infobip_message_id = b.infobip_message_id
    AND (a.created_at, a.id) > (b.created_at, b.id);
CREATE UNIQUE INDEX CONCURRENTLY IF NOT EXISTS uq_ai_conv_msgs_infobip_id
  ON ai_conversation_messages (infobip_message_id) WHERE infobip_message_id IS NOT NULL;
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_kb_items_owner ON knowledge_base_items (owner_user_id);
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_ai_conversations_owner ON ai_conversations (owner_user_id);
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_ai_conversations_lookup ON ai_conversations (sender_number, contact_phone);
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_ai_conv_msgs_conversation ON ai_conversation_messages (conversation_id);
-- (conversation_id, created_at): sustenta o LATERAL da última mensagem no
-- inbox da Central e o LATERAL da 1ª resposta humana no SLA. Nasceu na
-- migration 066 e ficou órfão — nem no schema, nem aqui. Agora nos dois.
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_ai_conv_msgs_conv_created ON ai_conversation_messages (conversation_id, created_at);

-- ─── WA CLOUD (Meta Tech Provider) ───────────────────────────────────────────
-- Dedupe de webhook da Meta: statuses/redeliveries são resolvidos por wamid.
CREATE UNIQUE INDEX CONCURRENTLY IF NOT EXISTS uidx_cloud_messages_wamid
  ON cloud_messages (wamid) WHERE wamid IS NOT NULL AND wamid <> '';
-- Finalização O(1): existe mensagem não-terminal nesta campanha?
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_cloud_messages_nonterminal
  ON cloud_messages (campaign_id) WHERE status NOT IN ('DELIVERED','SEEN','FAILED');

-- ─── CAMADA DE RECEITA (F0) ──────────────────────────────────────────────────
-- BRIN: contact_events é append-only em ordem temporal — BRIN dá varredura
-- por período a custo mínimo de manutenção (ideal p/ log imutável).
CREATE INDEX IF NOT EXISTS brin_contact_events_occurred
  ON contact_events USING brin (occurred_at);

-- F1: contato só pode estar em UM grupo de controle ativo por conta
CREATE UNIQUE INDEX IF NOT EXISTS uidx_control_group_active
  ON control_group_memberships (owner_user_id, contact_id) WHERE status = 'ativo';

-- F4 — anti-colisão de rotinas: UM contato em NO MÁXIMO UMA rotina ativa
-- por conta (colisão → não entra; decisão do plano).
CREATE UNIQUE INDEX IF NOT EXISTS uidx_routine_exec_active
  ON routine_executions (owner_user_id, contact_id) WHERE status = 'ativa';

-- CHAT-FORM (089) — só UMA sessão de formulário ativa por conversa.
-- Índice parcial: sem isso, duas MOs em rajada abrindo a mesma conversa
-- criariam duas sessões brigando pela mesma pergunta_index.
CREATE UNIQUE INDEX IF NOT EXISTS uidx_chat_form_sessions_active
  ON chat_form_sessions (conversation_id) WHERE status = 'ativa';

-- ─── Usina Gupshup (F11.5, 25/08/2026) ──────────────────────────────────────
-- Achado da revisão: o schema e o storage DECLARAVAM estes parciais como
-- pré-requisito ("índice parcial único de gsId… vive em ensure_indexes.sql"),
-- mas eles nunca foram criados. Consequências reais enquanto faltavam:
-- (a) o onConflictDoNothing de insertGupshupBillingEvent nunca conflitava —
--     reentrega do webhook DUPLICAVA billing (a idempotência era fictícia);
-- (b) getGupshupMessageByGsId/updateGupshupMessageStatus rodam POR EVENTO de
--     DLR sem nenhum índice em gupshup_message_id — seq scan numa tabela
--     desenhada para 1M msgs/dia.

-- Correlação do DLR (gsId do webhook = messageId do envio). ÚNICO: duas linhas
-- com o mesmo id de mensagem seriam corrupção de dado.
CREATE UNIQUE INDEX CONCURRENTLY IF NOT EXISTS uidx_gupshup_messages_gsid
  ON gupshup_messages (gupshup_message_id) WHERE gupshup_message_id IS NOT NULL;

-- EXISTS do guard de finalização (hasNonTerminalGupshupMessages) — roda a cada
-- evento de webhook do lote.
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_gupshup_messages_nonterminal
  ON gupshup_messages (campaign_id) WHERE status NOT IN ('DELIVERED','SEEN','FAILED');

-- Correlação DUPLA: eventos tardios (>1 semana) chegam sem gsId, só com o
-- WhatsApp Message ID gravado no 1º DLR.
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_gupshup_messages_wamid
  ON gupshup_messages (wa_message_id) WHERE wa_message_id IS NOT NULL;

-- Dedupe de reentrega do billing-event (o "2º sinal" que substitui a Logs API
-- que a Gupshup não tem). É ESTE índice que faz o onConflictDoNothing valer.
CREATE UNIQUE INDEX CONCURRENTLY IF NOT EXISTS uidx_gupshup_billing_events_gs_id
  ON gupshup_billing_events (gs_id) WHERE gs_id IS NOT NULL;