omnichannel-deploy/database/migrations/019_bot_flow_builder.sql

-- ============================================================
-- Migration 019: Flow Builder visual do bot
-- Tabelas:
--   bot_flow_versions
--   bot_flow_nodes
--   whatsapp_chat_atribuicoes
-- ============================================================

CREATE TABLE IF NOT EXISTS bot_flow_versions (
  id SERIAL PRIMARY KEY,
  name VARCHAR(160) NOT NULL DEFAULT 'Fluxo RH Sothis',
  status VARCHAR(30) NOT NULL DEFAULT 'draft',
  version_number INTEGER NOT NULL DEFAULT 0,
  root_node_id INTEGER,
  published_at TIMESTAMP WITH TIME ZONE,
  snapshot JSONB,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS bot_flow_nodes (
  id SERIAL PRIMARY KEY,
  version_id INTEGER NOT NULL REFERENCES bot_flow_versions(id) ON DELETE CASCADE,
  parent_id INTEGER REFERENCES bot_flow_nodes(id) ON DELETE CASCADE,
  node_type VARCHAR(30) NOT NULL CHECK (node_type IN ('greeting', 'question', 'agent', 'close')),
  title VARCHAR(160) NOT NULL,
  message_text TEXT,
  keywords TEXT,
  fallback_message TEXT,
  fallback_attempts INTEGER NOT NULL DEFAULT 2,
  fallback_area_id INTEGER REFERENCES areas(id) ON DELETE SET NULL,
  area_id INTEGER REFERENCES areas(id) ON DELETE SET NULL,
  sort_order INTEGER NOT NULL DEFAULT 1,
  position_x INTEGER NOT NULL DEFAULT 0,
  position_y INTEGER NOT NULL DEFAULT 0,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

DO $$
BEGIN
  IF NOT EXISTS (
    SELECT 1
    FROM pg_constraint
    WHERE conname = 'fk_bot_flow_versions_root'
  ) THEN
    ALTER TABLE bot_flow_versions
      ADD CONSTRAINT fk_bot_flow_versions_root
      FOREIGN KEY (root_node_id) REFERENCES bot_flow_nodes(id) ON DELETE SET NULL;
  END IF;
END $$;

ALTER TABLE whatsapp_chat_atribuicoes
  ADD COLUMN IF NOT EXISTS triage_builder_version_id INTEGER REFERENCES bot_flow_versions(id) ON DELETE SET NULL,
  ADD COLUMN IF NOT EXISTS triage_builder_node_id INTEGER REFERENCES bot_flow_nodes(id) ON DELETE SET NULL;

CREATE INDEX IF NOT EXISTS idx_bot_flow_versions_status
  ON bot_flow_versions (status, published_at DESC, id DESC);

CREATE INDEX IF NOT EXISTS idx_bot_flow_nodes_version_parent
  ON bot_flow_nodes (version_id, parent_id, sort_order, id);

CREATE INDEX IF NOT EXISTS idx_whatsapp_atribuicoes_builder_node
  ON whatsapp_chat_atribuicoes (triage_builder_version_id, triage_builder_node_id);

WITH inserted_version AS (
  INSERT INTO bot_flow_versions (name, status, version_number, updated_at)
  SELECT 'Fluxo RH Sothis', 'draft', 0, CURRENT_TIMESTAMP
  WHERE NOT EXISTS (SELECT 1 FROM bot_flow_versions WHERE status = 'draft')
  RETURNING id
),
draft_version AS (
  SELECT id FROM inserted_version
  UNION ALL
  SELECT id
  FROM (
    SELECT id
    FROM bot_flow_versions
    WHERE status = 'draft'
      AND NOT EXISTS (SELECT 1 FROM inserted_version)
    ORDER BY id ASC
    LIMIT 1
  ) existing_draft
),
inserted_root AS (
  INSERT INTO bot_flow_nodes (
    version_id,
    parent_id,
    node_type,
    title,
    message_text,
    keywords,
    fallback_message,
    fallback_attempts,
    sort_order,
    position_x,
    position_y,
    updated_at
  )
  SELECT
    draft_version.id,
    NULL,
    'greeting',
    'Saudacao inicial',
    'Ola! Sou o Agente Virtual Sothis. Vou te direcionar para o atendimento correto de RH. Digite o numero da opcao que melhor descreve voce:\n\n1 - Sou colaborador ativo\n2 - Sou ex-colaborador\n3 - Sou candidato a uma vaga',
    NULL,
    'Nao consegui identificar seu perfil. Digite 1 para colaborador ativo, 2 para ex-colaborador ou 3 para candidato.',
    2,
    1,
    0,
    0,
    CURRENT_TIMESTAMP
  FROM draft_version
  WHERE NOT EXISTS (
    SELECT 1
    FROM bot_flow_nodes node
    WHERE node.version_id = draft_version.id
      AND node.node_type = 'greeting'
  )
  RETURNING id, version_id
)
UPDATE bot_flow_versions version
SET root_node_id = inserted_root.id,
    updated_at = CURRENT_TIMESTAMP
FROM inserted_root
WHERE version.id = inserted_root.version_id
  AND version.root_node_id IS NULL;