AyronSantos/chatc2
1
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
Files
dev
chatc2/scripts/migracoes.sql
T

581 lines
26 KiB
SQL
Raw Permalink Blame History

-- ============================================================
-- CHATC2 - MIGRAÇÕES DE BANCO DE DADOS (Firebird)
-- ============================================================
-- Execute este arquivo no seu banco .FDB usando:
-- IBExpert: Tools > Execute SQL Script > External terminator: ^
-- isql (linha de comando):
-- isql -user SYSDBA -pass masterkey localhost:SEU.FDB
-- SET TERM ^ ;
-- INPUT migracoes.sql;
-- SET TERM ; ^
-- EXIT;
--
-- Cada bloco é idempotente: se a tabela/coluna já existe, o erro é ignorado.
-- ============================================================
-- ============================================================
-- TABELA DE CONTROLE (deve ser a primeira)
-- ============================================================
EXECUTE BLOCK AS BEGIN
IF (NOT EXISTS(SELECT 1 FROM RDB$RELATIONS WHERE RDB$RELATION_NAME = 'CHATC2_CONTROLE_MIGRACOES')) THEN
EXECUTE STATEMENT 'CREATE TABLE CHATC2_CONTROLE_MIGRACOES (
MIG_ID INTEGER NOT NULL PRIMARY KEY,
MIG_DESCRICAO VARCHAR(200),
MIG_DATA_EXECUCAO TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
MIG_STATUS CHAR(1) DEFAULT ''A''
)';
END;
^
COMMIT;
^
-- ============================================================
-- MIGRAÇÃO 1: Campo USU_TIPO_CHAT em USUARIOS
-- ============================================================
EXECUTE BLOCK AS BEGIN
IF (NOT EXISTS(SELECT 1 FROM RDB$RELATION_FIELDS WHERE RDB$RELATION_NAME = 'USUARIOS' AND RDB$FIELD_NAME = 'USU_TIPO_CHAT')) THEN
EXECUTE STATEMENT 'ALTER TABLE USUARIOS ADD USU_TIPO_CHAT CHAR(1) DEFAULT ''A''';
END;
^
COMMIT;
^
-- ============================================================
-- MIGRAÇÃO 2: Tabela CHATC2_INSTANCIAS (WhatsApp / Evolution API)
-- ============================================================
EXECUTE BLOCK AS BEGIN
IF (NOT EXISTS(SELECT 1 FROM RDB$RELATIONS WHERE RDB$RELATION_NAME = 'CHATC2_INSTANCIAS')) THEN
BEGIN
EXECUTE STATEMENT 'CREATE TABLE CHATC2_INSTANCIAS (
INS_CODIGO_ID INTEGER NOT NULL PRIMARY KEY,
INS_EMPRESA_ID INTEGER NOT NULL,
INS_NOME VARCHAR(100),
INS_URL VARCHAR(500),
INS_API_KEY VARCHAR(500),
INS_INSTANCE_NAME VARCHAR(100),
INS_STATUS CHAR(1) DEFAULT ''D'',
INS_QR_CODE BLOB SUB_TYPE 0,
INS_DT_CONEXAO TIMESTAMP,
INS_DT_CADASTRO TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
INS_SITUACAO CHAR(1) DEFAULT ''A''
)';
EXECUTE STATEMENT 'CREATE SEQUENCE GEN_INSTANCIAS';
END
END;
^
COMMIT;
^
-- ============================================================
-- MIGRAÇÃO 3: Tabela CHATC2_EQUIPES
-- ============================================================
EXECUTE BLOCK AS BEGIN
IF (NOT EXISTS(SELECT 1 FROM RDB$RELATIONS WHERE RDB$RELATION_NAME = 'CHATC2_EQUIPES')) THEN
BEGIN
EXECUTE STATEMENT 'CREATE TABLE CHATC2_EQUIPES (
EQU_CODIGO_ID INTEGER NOT NULL PRIMARY KEY,
EQU_EMPRESA_ID INTEGER NOT NULL,
EQU_NOME VARCHAR(100),
EQU_ORDEM INTEGER DEFAULT 0,
EQU_SITUACAO CHAR(1) DEFAULT ''A''
)';
EXECUTE STATEMENT 'CREATE SEQUENCE GEN_EQUIPES';
END
END;
^
COMMIT;
^
-- ============================================================
-- MIGRAÇÃO 4: Tabela CHATC2_USU_EQUIPES (usuários x equipes)
-- ============================================================
EXECUTE BLOCK AS BEGIN
IF (NOT EXISTS(SELECT 1 FROM RDB$RELATIONS WHERE RDB$RELATION_NAME = 'CHATC2_USU_EQUIPES')) THEN
EXECUTE STATEMENT 'CREATE TABLE CHATC2_USU_EQUIPES (
EQU_EQUIPE_ID INTEGER NOT NULL,
EQU_USUARIO_ID INTEGER NOT NULL,
PRIMARY KEY (EQU_EQUIPE_ID, EQU_USUARIO_ID)
)';
END;
^
COMMIT;
^
-- ============================================================
-- MIGRAÇÃO 5: Tabela CHATC2_ETIQUETAS
-- ============================================================
EXECUTE BLOCK AS BEGIN
IF (NOT EXISTS(SELECT 1 FROM RDB$RELATIONS WHERE RDB$RELATION_NAME = 'CHATC2_ETIQUETAS')) THEN
BEGIN
EXECUTE STATEMENT 'CREATE TABLE CHATC2_ETIQUETAS (
ETI_CODIGO_ID INTEGER NOT NULL PRIMARY KEY,
ETI_EMPRESA_ID INTEGER NOT NULL,
ETI_NOME VARCHAR(50),
ETI_COR VARCHAR(7) DEFAULT ''#667eea'',
ETI_SITUACAO CHAR(1) DEFAULT ''A''
)';
EXECUTE STATEMENT 'CREATE SEQUENCE GEN_ETIQUETAS';
END
END;
^
COMMIT;
^
-- ============================================================
-- MIGRAÇÃO 6: Tabela CHATC2_CONVERSAS
-- ============================================================
EXECUTE BLOCK AS BEGIN
IF (NOT EXISTS(SELECT 1 FROM RDB$RELATIONS WHERE RDB$RELATION_NAME = 'CHATC2_CONVERSAS')) THEN
BEGIN
EXECUTE STATEMENT 'CREATE TABLE CHATC2_CONVERSAS (
CON_CODIGO_ID INTEGER NOT NULL PRIMARY KEY,
CON_EMPRESA_ID INTEGER NOT NULL,
CON_INSTANCIA_ID INTEGER,
CON_CLIENTE_ID INTEGER,
CON_NUMERO VARCHAR(20),
CON_NOME_CONTATO VARCHAR(100),
CON_STATUS CHAR(1) DEFAULT ''E'',
CON_USUARIO_ID INTEGER,
CON_EQUIPE_ID INTEGER,
CON_DT_INICIO TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
CON_DT_FINAL TIMESTAMP,
CON_DT_ULTIMA_MSG TIMESTAMP,
CON_SAUDACAO_ENVIADA CHAR(1) DEFAULT ''N'',
CON_CSAT_ENVIADO CHAR(1) DEFAULT ''N'',
CON_PRIMEIRA_MSG TIMESTAMP,
CON_ORIGEM VARCHAR(20) DEFAULT ''whatsapp'',
CON_SITUACAO CHAR(1) DEFAULT ''A''
)';
EXECUTE STATEMENT 'CREATE SEQUENCE GEN_CONVERSAS';
END
END;
^
COMMIT;
^
-- ============================================================
-- MIGRAÇÃO 7: Tabela CHATC2_CONVERSAS_MENSAGENS
-- ============================================================
EXECUTE BLOCK AS BEGIN
IF (NOT EXISTS(SELECT 1 FROM RDB$RELATIONS WHERE RDB$RELATION_NAME = 'CHATC2_CONVERSAS_MENSAGENS')) THEN
BEGIN
EXECUTE STATEMENT 'CREATE TABLE CHATC2_CONVERSAS_MENSAGENS (
CME_CODIGO_ID INTEGER NOT NULL PRIMARY KEY,
CME_CONVERSA_ID INTEGER NOT NULL,
CME_REMETENTE CHAR(1) DEFAULT ''C'',
CME_USUARIO_ID INTEGER,
CME_MENSAGEM BLOB SUB_TYPE 1,
CME_TEXTO VARCHAR(4000),
CME_TIPO VARCHAR(20) DEFAULT ''text'',
CME_DT_ENVIO TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
CME_PRIVADA CHAR(1) DEFAULT ''N'',
CME_LIDA CHAR(1) DEFAULT ''N'',
CME_MIDIA_ID INTEGER,
CME_SITUACAO CHAR(1) DEFAULT ''A''
)';
EXECUTE STATEMENT 'CREATE SEQUENCE GEN_CONVERSAS_MENSAGENS';
END
END;
^
COMMIT;
^
-- ============================================================
-- MIGRAÇÃO 8: Tabela CHATC2_MENSAGENS_ATENDIMENTOS (mídias)
-- ============================================================
EXECUTE BLOCK AS BEGIN
IF (NOT EXISTS(SELECT 1 FROM RDB$RELATIONS WHERE RDB$RELATION_NAME = 'CHATC2_MENSAGENS_ATENDIMENTOS')) THEN
BEGIN
EXECUTE STATEMENT 'CREATE TABLE CHATC2_MENSAGENS_ATENDIMENTOS (
MAT_CODIGO_ID INTEGER NOT NULL PRIMARY KEY,
MAT_CONVERSA_ID INTEGER,
MAT_MENSAGEM_ID INTEGER,
MAT_NOME_ARQUIVO VARCHAR(255),
MAT_TIPO_ARQUIVO VARCHAR(50),
MAT_ARQUIVO BLOB SUB_TYPE 0,
MAT_DT_CADASTRO TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
MAT_SITUACAO CHAR(1) DEFAULT ''A''
)';
EXECUTE STATEMENT 'CREATE SEQUENCE GEN_MENSAGENS_ATENDIMENTO';
END
END;
^
COMMIT;
^
-- ============================================================
-- MIGRAÇÃO 9: Tabela CHATC2_CONVERSAS_ETIQUETAS
-- ============================================================
EXECUTE BLOCK AS BEGIN
IF (NOT EXISTS(SELECT 1 FROM RDB$RELATIONS WHERE RDB$RELATION_NAME = 'CHATC2_CONVERSAS_ETIQUETAS')) THEN
EXECUTE STATEMENT 'CREATE TABLE CHATC2_CONVERSAS_ETIQUETAS (
CET_CONVERSA_ID INTEGER NOT NULL,
CET_ETIQUETA_ID INTEGER NOT NULL,
PRIMARY KEY (CET_CONVERSA_ID, CET_ETIQUETA_ID)
)';
END;
^
COMMIT;
^
-- ============================================================
-- MIGRAÇÃO 10: Tabela CHATC2_CSAT_AVALIACOES
-- ============================================================
EXECUTE BLOCK AS BEGIN
IF (NOT EXISTS(SELECT 1 FROM RDB$RELATIONS WHERE RDB$RELATION_NAME = 'CHATC2_CSAT_AVALIACOES')) THEN
BEGIN
EXECUTE STATEMENT 'CREATE TABLE CHATC2_CSAT_AVALIACOES (
CSA_CODIGO_ID INTEGER NOT NULL PRIMARY KEY,
CSA_CONVERSA_ID INTEGER,
CSA_EMPRESA_ID INTEGER,
CSA_CLIENTE_ID INTEGER,
CSA_NOTA INTEGER,
CSA_COMENTARIO VARCHAR(500),
CSA_DT_AVALIACAO TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)';
EXECUTE STATEMENT 'CREATE SEQUENCE GEN_CSAT_AVALIACOES';
END
END;
^
COMMIT;
^
-- ============================================================
-- MIGRAÇÃO 11: Tabela CHATC2_CONFIGURACOES_EMPRESA
-- ============================================================
EXECUTE BLOCK AS BEGIN
IF (NOT EXISTS(SELECT 1 FROM RDB$RELATIONS WHERE RDB$RELATION_NAME = 'CHATC2_CONFIGURACOES_EMPRESA')) THEN
EXECUTE STATEMENT 'CREATE TABLE CHATC2_CONFIGURACOES_EMPRESA (
CFE_EMPRESA_ID INTEGER NOT NULL PRIMARY KEY,
CFE_INSTANCIA_PADRAO_ID INTEGER,
CFE_FOTO_CELULAR CHAR(1) DEFAULT ''N'',
CFE_SAUDACAO_ATIVA CHAR(1) DEFAULT ''S'',
CFE_SAUDACAO_MENSAGEM VARCHAR(500),
CFE_CSAT_ATIVO CHAR(1) DEFAULT ''N'',
CFE_CSAT_MENSAGEM VARCHAR(500) DEFAULT ''Avalie seu atendimento de 1 a 5 estrelas''
)';
END;
^
COMMIT;
^
-- ============================================================
-- MIGRAÇÃO 12: EQU_ORDEM em CHATC2_EQUIPES
-- ============================================================
EXECUTE BLOCK AS BEGIN
IF (NOT EXISTS(SELECT 1 FROM RDB$RELATION_FIELDS WHERE RDB$RELATION_NAME = 'CHATC2_EQUIPES' AND RDB$FIELD_NAME = 'EQU_ORDEM')) THEN
EXECUTE STATEMENT 'ALTER TABLE CHATC2_EQUIPES ADD EQU_ORDEM INTEGER DEFAULT 0';
END;
^
COMMIT;
^
-- ============================================================
-- MIGRAÇÃO 13: Campos de triagem em CHATC2_CONFIGURACOES_EMPRESA
-- ============================================================
EXECUTE BLOCK AS BEGIN
IF (NOT EXISTS(SELECT 1 FROM RDB$RELATION_FIELDS WHERE RDB$RELATION_NAME = 'CHATC2_CONFIGURACOES_EMPRESA' AND RDB$FIELD_NAME = 'CFE_TRIAGEM_ATIVA')) THEN
BEGIN
EXECUTE STATEMENT 'ALTER TABLE CHATC2_CONFIGURACOES_EMPRESA ADD CFE_ENVIAR_NOME_USUARIO CHAR(1) DEFAULT ''N''';
EXECUTE STATEMENT 'ALTER TABLE CHATC2_CONFIGURACOES_EMPRESA ADD CFE_TRIAGEM_ATIVA CHAR(1) DEFAULT ''N''';
EXECUTE STATEMENT 'ALTER TABLE CHATC2_CONFIGURACOES_EMPRESA ADD CFE_TRIAGEM_MSG_WELCOME VARCHAR(500)';
EXECUTE STATEMENT 'ALTER TABLE CHATC2_CONFIGURACOES_EMPRESA ADD CFE_TRIAGEM_MSG_AFTER VARCHAR(500)';
EXECUTE STATEMENT 'ALTER TABLE CHATC2_CONFIGURACOES_EMPRESA ADD CFE_TRIAGEM_BOLETO_NUMERO VARCHAR(10) DEFAULT ''0''';
END
END;
^
COMMIT;
^
-- ============================================================
-- MIGRAÇÃO 14: CON_MENU_ESTADO em CHATC2_CONVERSAS
-- ============================================================
EXECUTE BLOCK AS BEGIN
IF (NOT EXISTS(SELECT 1 FROM RDB$RELATION_FIELDS WHERE RDB$RELATION_NAME = 'CHATC2_CONVERSAS' AND RDB$FIELD_NAME = 'CON_MENU_ESTADO')) THEN
EXECUTE STATEMENT 'ALTER TABLE CHATC2_CONVERSAS ADD CON_MENU_ESTADO VARCHAR(100)';
END;
^
COMMIT;
^
-- ============================================================
-- MIGRAÇÃO 15: CON_ETIQUETAS_DESC em CHATC2_CONVERSAS
-- ============================================================
EXECUTE BLOCK AS BEGIN
IF (NOT EXISTS(SELECT 1 FROM RDB$RELATION_FIELDS WHERE RDB$RELATION_NAME = 'CHATC2_CONVERSAS' AND RDB$FIELD_NAME = 'CON_ETIQUETAS_DESC')) THEN
EXECUTE STATEMENT 'ALTER TABLE CHATC2_CONVERSAS ADD CON_ETIQUETAS_DESC VARCHAR(500)';
END;
^
COMMIT;
^
-- ============================================================
-- MIGRAÇÃO 16: Tabela CHATC2_MENUS_EMPRESA (fluxo de atendimento)
-- ============================================================
EXECUTE BLOCK AS BEGIN
IF (NOT EXISTS(SELECT 1 FROM RDB$RELATIONS WHERE RDB$RELATION_NAME = 'CHATC2_MENUS_EMPRESA')) THEN
EXECUTE STATEMENT 'CREATE TABLE CHATC2_MENUS_EMPRESA (
MNE_CODIGO_ID INTEGER NOT NULL PRIMARY KEY,
MNE_EMPRESA_ID INTEGER,
MNE_EQUIPE_ID INTEGER,
MNE_MENU_PAI_ID INTEGER,
MNE_ORDEM INTEGER DEFAULT 0,
MNE_TITULO VARCHAR(100),
MNE_TIPO CHAR(1) DEFAULT ''M'',
MNE_TEXTO BLOB SUB_TYPE TEXT,
MNE_ACAO_ROTA VARCHAR(100),
MNE_ACAO_METODO VARCHAR(10),
MNE_ACAO_PROMPT VARCHAR(300),
MNE_SITUACAO CHAR(1) DEFAULT ''A''
)';
END;
^
COMMIT;
^
-- ============================================================
-- MIGRAÇÃO 17: Novos campos em CHATC2_CONVERSAS
-- ============================================================
EXECUTE BLOCK AS BEGIN
IF (NOT EXISTS(SELECT 1 FROM RDB$RELATION_FIELDS WHERE RDB$RELATION_NAME = 'CHATC2_CONVERSAS' AND RDB$FIELD_NAME = 'CON_USUARIO_NOME')) THEN
BEGIN
EXECUTE STATEMENT 'ALTER TABLE CHATC2_CONVERSAS ADD CON_USUARIO_NOME VARCHAR(100)';
EXECUTE STATEMENT 'ALTER TABLE CHATC2_CONVERSAS ADD CON_EQUIPE_NOME VARCHAR(100)';
END
END;
^
COMMIT;
^
-- ============================================================
-- MIGRAÇÃO 18: Etiquetas nos menus de fluxo
-- ============================================================
EXECUTE BLOCK AS BEGIN
IF (NOT EXISTS(SELECT 1 FROM RDB$RELATION_FIELDS WHERE RDB$RELATION_NAME = 'CHATC2_MENUS_EMPRESA' AND RDB$FIELD_NAME = 'MNE_ETIQUETA_IDS')) THEN
EXECUTE STATEMENT 'ALTER TABLE CHATC2_MENUS_EMPRESA ADD MNE_ETIQUETA_IDS VARCHAR(200)';
END;
^
COMMIT;
^
-- ============================================================
-- MIGRAÇÃO 19: Transcrição de áudio
-- ============================================================
EXECUTE BLOCK AS BEGIN
IF (NOT EXISTS(SELECT 1 FROM RDB$RELATION_FIELDS WHERE RDB$RELATION_NAME = 'CHATC2_MENSAGENS_ATENDIMENTOS' AND RDB$FIELD_NAME = 'MAT_TRANSCRICAO')) THEN
EXECUTE STATEMENT 'ALTER TABLE CHATC2_MENSAGENS_ATENDIMENTOS ADD MAT_TRANSCRICAO BLOB SUB_TYPE TEXT';
END;
^
COMMIT;
^
-- ============================================================
-- MIGRAÇÃO 20: Generators thread-safe para todas as tabelas Chatc2
-- Cria sequences faltantes e inicializa todas com MAX atual + 1
-- ============================================================
EXECUTE BLOCK AS
DECLARE max_val INTEGER;
BEGIN
-- Tabelas que já tinham sequence (criadas nas migrações 2-8, 10).
-- Nomes podem variar - este bloco garante que existam com o nome
-- usado pelo código (nextId). Se a sequence com outro nome já
-- existir, mantém ambas (a antiga fica dormente).
-- CHATC2_CONVERSAS: já tinha GEN_CONVERSAS (migração 6)
IF (NOT EXISTS(SELECT 1 FROM RDB$GENERATORS WHERE RDB$GENERATOR_NAME = 'GEN_CHATC2_CONVERSAS')) THEN
EXECUTE STATEMENT 'CREATE SEQUENCE GEN_CHATC2_CONVERSAS';
SELECT COALESCE(MAX(CON_CODIGO_ID), 0) + 1 FROM CHATC2_CONVERSAS INTO :max_val;
EXECUTE STATEMENT 'SET GENERATOR GEN_CHATC2_CONVERSAS TO ' || :max_val;
-- CHATC2_CONVERSAS_MENSAGENS: já tinha GEN_CONVERSAS_MENSAGENS (migração 7)
IF (NOT EXISTS(SELECT 1 FROM RDB$GENERATORS WHERE RDB$GENERATOR_NAME = 'GEN_CONVERSAS_MENSAGENS')) THEN
EXECUTE STATEMENT 'CREATE SEQUENCE GEN_CONVERSAS_MENSAGENS';
SELECT COALESCE(MAX(CME_CODIGO_ID), 0) + 1 FROM CHATC2_CONVERSAS_MENSAGENS INTO :max_val;
EXECUTE STATEMENT 'SET GENERATOR GEN_CONVERSAS_MENSAGENS TO ' || :max_val;
-- CHATC2_MENSAGENS_ATENDIMENTOS: já tinha GEN_MENSAGENS_ATENDIMENTO (migração 8)
-- Criamos com nome corrigido (plural ATENDIMENTOS)
IF (NOT EXISTS(SELECT 1 FROM RDB$GENERATORS WHERE RDB$GENERATOR_NAME = 'GEN_MENSAGENS_ATENDIMENTOS')) THEN
EXECUTE STATEMENT 'CREATE SEQUENCE GEN_MENSAGENS_ATENDIMENTOS';
SELECT COALESCE(MAX(MAT_CODIGO_ID), 0) + 1 FROM CHATC2_MENSAGENS_ATENDIMENTOS INTO :max_val;
EXECUTE STATEMENT 'SET GENERATOR GEN_MENSAGENS_ATENDIMENTOS TO ' || :max_val;
-- CHATC2_EQUIPES: já tinha GEN_EQUIPES (migração 3)
IF (NOT EXISTS(SELECT 1 FROM RDB$GENERATORS WHERE RDB$GENERATOR_NAME = 'GEN_CHATC2_EQUIPES')) THEN
EXECUTE STATEMENT 'CREATE SEQUENCE GEN_CHATC2_EQUIPES';
SELECT COALESCE(MAX(EQU_CODIGO_ID), 0) + 1 FROM CHATC2_EQUIPES INTO :max_val;
EXECUTE STATEMENT 'SET GENERATOR GEN_CHATC2_EQUIPES TO ' || :max_val;
-- CHATC2_ETIQUETAS: já tinha GEN_ETIQUETAS (migração 5)
IF (NOT EXISTS(SELECT 1 FROM RDB$GENERATORS WHERE RDB$GENERATOR_NAME = 'GEN_CHATC2_ETIQUETAS')) THEN
EXECUTE STATEMENT 'CREATE SEQUENCE GEN_CHATC2_ETIQUETAS';
SELECT COALESCE(MAX(ETI_CODIGO_ID), 0) + 1 FROM CHATC2_ETIQUETAS INTO :max_val;
EXECUTE STATEMENT 'SET GENERATOR GEN_CHATC2_ETIQUETAS TO ' || :max_val;
-- CHATC2_INSTANCIAS: já tinha GEN_INSTANCIAS (migração 2)
IF (NOT EXISTS(SELECT 1 FROM RDB$GENERATORS WHERE RDB$GENERATOR_NAME = 'GEN_CHATC2_INSTANCIAS')) THEN
EXECUTE STATEMENT 'CREATE SEQUENCE GEN_CHATC2_INSTANCIAS';
SELECT COALESCE(MAX(INS_CODIGO_ID), 0) + 1 FROM CHATC2_INSTANCIAS INTO :max_val;
EXECUTE STATEMENT 'SET GENERATOR GEN_CHATC2_INSTANCIAS TO ' || :max_val;
-- CHATC2_CSAT_AVALIACOES: já tinha GEN_CSAT_AVALIACOES (migração 10)
IF (NOT EXISTS(SELECT 1 FROM RDB$GENERATORS WHERE RDB$GENERATOR_NAME = 'GEN_CHATC2_CSAT_AVALIACOES')) THEN
EXECUTE STATEMENT 'CREATE SEQUENCE GEN_CHATC2_CSAT_AVALIACOES';
SELECT COALESCE(MAX(CSA_CODIGO_ID), 0) + 1 FROM CHATC2_CSAT_AVALIACOES INTO :max_val;
EXECUTE STATEMENT 'SET GENERATOR GEN_CHATC2_CSAT_AVALIACOES TO ' || :max_val;
-- CHATC2_MENUS_EMPRESA: NÃO tinha sequence (criada na migração 16)
IF (NOT EXISTS(SELECT 1 FROM RDB$GENERATORS WHERE RDB$GENERATOR_NAME = 'GEN_CHATC2_MENUS_EMPRESA')) THEN
EXECUTE STATEMENT 'CREATE SEQUENCE GEN_CHATC2_MENUS_EMPRESA';
SELECT COALESCE(MAX(MNE_CODIGO_ID), 0) + 1 FROM CHATC2_MENUS_EMPRESA INTO :max_val;
EXECUTE STATEMENT 'SET GENERATOR GEN_CHATC2_MENUS_EMPRESA TO ' || :max_val;
-- CHATC2_MOTIVOS_ATENDIMENTO: tabela criada por resolucaoSetup.js (sem sequence)
IF (NOT EXISTS(SELECT 1 FROM RDB$GENERATORS WHERE RDB$GENERATOR_NAME = 'GEN_CHATC2_MOTIVOS_ATENDIMENTO')) THEN
EXECUTE STATEMENT 'CREATE SEQUENCE GEN_CHATC2_MOTIVOS_ATENDIMENTO';
SELECT COALESCE(MAX(MOT_CODIGO_ID), 0) + 1 FROM "CHATC2_MOTIVOS_ATENDIMENTO" INTO :max_val;
EXECUTE STATEMENT 'SET GENERATOR GEN_CHATC2_MOTIVOS_ATENDIMENTO TO ' || :max_val;
END;
^
COMMIT;
^
-- ============================================================
-- MIGRAÇÃO 21: Índices para busca de telefone
-- ============================================================
EXECUTE BLOCK AS BEGIN
IF (NOT EXISTS(SELECT 1 FROM RDB$INDICES WHERE RDB$INDEX_NAME = 'IDX_CLIENTES_CELULAR')) THEN
EXECUTE STATEMENT 'CREATE INDEX IDX_CLIENTES_CELULAR ON CLIENTES (CLI_CELULAR)';
IF (NOT EXISTS(SELECT 1 FROM RDB$INDICES WHERE RDB$INDEX_NAME = 'IDX_CLIENTES_FONE1')) THEN
EXECUTE STATEMENT 'CREATE INDEX IDX_CLIENTES_FONE1 ON CLIENTES (CLI_FONE1)';
IF (NOT EXISTS(SELECT 1 FROM RDB$INDICES WHERE RDB$INDEX_NAME = 'IDX_CLIENTES_FONE2')) THEN
EXECUTE STATEMENT 'CREATE INDEX IDX_CLIENTES_FONE2 ON CLIENTES (CLI_FONE2)';
IF (NOT EXISTS(SELECT 1 FROM RDB$INDICES WHERE RDB$INDEX_NAME = 'IDX_CLIENTES_EMPRESA')) THEN
EXECUTE STATEMENT 'CREATE INDEX IDX_CLIENTES_EMPRESA ON CLIENTES (CLI_EMPRESA_ID)';
IF (NOT EXISTS(SELECT 1 FROM RDB$INDICES WHERE RDB$INDEX_NAME = 'IDX_CONV_NUMERO_EMPRESA')) THEN
EXECUTE STATEMENT 'CREATE INDEX IDX_CONV_NUMERO_EMPRESA ON CHATC2_CONVERSAS (CON_NUMERO, CON_EMPRESA_ID)';
IF (NOT EXISTS(SELECT 1 FROM RDB$INDICES WHERE RDB$INDEX_NAME = 'IDX_CONV_STATUS')) THEN
EXECUTE STATEMENT 'CREATE INDEX IDX_CONV_STATUS ON CHATC2_CONVERSAS (CON_STATUS)';
END;
^
COMMIT;
^
-- ============================================================
-- MIGRAÇÃO 22: Mensagem e etiqueta por equipe (triagem)
-- ============================================================
EXECUTE BLOCK AS BEGIN
IF (NOT EXISTS(SELECT 1 FROM RDB$RELATION_FIELDS WHERE RDB$RELATION_NAME = 'CHATC2_EQUIPES' AND RDB$FIELD_NAME = 'EQU_MENSAGEM')) THEN
EXECUTE STATEMENT 'ALTER TABLE CHATC2_EQUIPES ADD EQU_MENSAGEM VARCHAR(500)';
IF (NOT EXISTS(SELECT 1 FROM RDB$RELATION_FIELDS WHERE RDB$RELATION_NAME = 'CHATC2_EQUIPES' AND RDB$FIELD_NAME = 'EQU_ETIQUETA_ID')) THEN
EXECUTE STATEMENT 'ALTER TABLE CHATC2_EQUIPES ADD EQU_ETIQUETA_ID INTEGER';
END;
^
COMMIT;
^
-- ============================================================
-- MIGRAÇÃO 23: Agendamento de conversas
-- ============================================================
EXECUTE BLOCK AS BEGIN
IF (NOT EXISTS(SELECT 1 FROM RDB$RELATIONS WHERE RDB$RELATION_NAME = 'CHATC2_CONVERSAS_AGENDAMENTOS')) THEN
EXECUTE STATEMENT 'CREATE TABLE CHATC2_CONVERSAS_AGENDAMENTOS (
CAG_CODIGO_ID INTEGER NOT NULL PRIMARY KEY,
CAG_CONVERSA_ID INTEGER NOT NULL,
CAG_EMPRESA_ID INTEGER,
CAG_DATA DATE NOT NULL,
CAG_HORA TIME,
CAG_NOTA VARCHAR(300),
CAG_STATUS CHAR(1) DEFAULT ''P'',
CAG_USUARIO_ID INTEGER,
CAG_DT_CRIACAO TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)';
END;
^
COMMIT;
^
-- ============================================================
-- MIGRAÇÃO 24: DDL do fluxo de resolução (runtime -> migration)
-- ============================================================
EXECUTE BLOCK AS BEGIN
IF (NOT EXISTS(SELECT 1 FROM RDB$RELATIONS WHERE RDB$RELATION_NAME = 'CHATC2_MOTIVOS_ATENDIMENTO')) THEN
EXECUTE STATEMENT 'CREATE TABLE ''CHATC2_MOTIVOS_ATENDIMENTO'' (
MOT_CODIGO_ID INTEGER NOT NULL PRIMARY KEY,
MOT_EMPRESA_ID INTEGER,
MOT_DESCRICAO VARCHAR(150),
MOT_SITUACAO CHAR(1) DEFAULT ''A''
)';
IF (NOT EXISTS(SELECT 1 FROM RDB$RELATION_FIELDS WHERE RDB$RELATION_NAME = 'CHATC2_CONFIGURACOES_EMPRESA' AND RDB$FIELD_NAME = 'CFE_MOTIVO_VISUALIZAR')) THEN
EXECUTE STATEMENT 'ALTER TABLE CHATC2_CONFIGURACOES_EMPRESA ADD CFE_MOTIVO_VISUALIZAR CHAR(1) DEFAULT ''N''';
IF (NOT EXISTS(SELECT 1 FROM RDB$RELATION_FIELDS WHERE RDB$RELATION_NAME = 'CHATC2_CONFIGURACOES_EMPRESA' AND RDB$FIELD_NAME = 'CFE_MOTIVO_OBRIGATORIO')) THEN
EXECUTE STATEMENT 'ALTER TABLE CHATC2_CONFIGURACOES_EMPRESA ADD CFE_MOTIVO_OBRIGATORIO CHAR(1) DEFAULT ''N''';
IF (NOT EXISTS(SELECT 1 FROM RDB$RELATION_FIELDS WHERE RDB$RELATION_NAME = 'CHATC2_CONFIGURACOES_EMPRESA' AND RDB$FIELD_NAME = 'CFE_RESOLUCAO_VISUALIZAR')) THEN
EXECUTE STATEMENT 'ALTER TABLE CHATC2_CONFIGURACOES_EMPRESA ADD CFE_RESOLUCAO_VISUALIZAR CHAR(1) DEFAULT ''N''';
IF (NOT EXISTS(SELECT 1 FROM RDB$RELATION_FIELDS WHERE RDB$RELATION_NAME = 'CHATC2_CONFIGURACOES_EMPRESA' AND RDB$FIELD_NAME = 'CFE_RESOLUCAO_OBRIGATORIO')) THEN
EXECUTE STATEMENT 'ALTER TABLE CHATC2_CONFIGURACOES_EMPRESA ADD CFE_RESOLUCAO_OBRIGATORIO CHAR(1) DEFAULT ''N''';
IF (NOT EXISTS(SELECT 1 FROM RDB$RELATION_FIELDS WHERE RDB$RELATION_NAME = 'CHATC2_CONFIGURACOES_EMPRESA' AND RDB$FIELD_NAME = 'CFE_ENVIAR_BOLETO')) THEN
EXECUTE STATEMENT 'ALTER TABLE CHATC2_CONFIGURACOES_EMPRESA ADD CFE_ENVIAR_BOLETO CHAR(1) DEFAULT ''N''';
IF (NOT EXISTS(SELECT 1 FROM RDB$RELATION_FIELDS WHERE RDB$RELATION_NAME = 'CHATC2_CONFIGURACOES_EMPRESA' AND RDB$FIELD_NAME = 'CFE_RESOLUCAO_SALVAR_MSC')) THEN
EXECUTE STATEMENT 'ALTER TABLE CHATC2_CONFIGURACOES_EMPRESA ADD CFE_RESOLUCAO_SALVAR_MSC CHAR(1) DEFAULT ''N''';
IF (NOT EXISTS(SELECT 1 FROM RDB$RELATION_FIELDS WHERE RDB$RELATION_NAME = 'CHATC2_CONVERSAS' AND RDB$FIELD_NAME = 'CON_MOTIVO_ID')) THEN
EXECUTE STATEMENT 'ALTER TABLE CHATC2_CONVERSAS ADD CON_MOTIVO_ID INTEGER';
IF (NOT EXISTS(SELECT 1 FROM RDB$RELATION_FIELDS WHERE RDB$RELATION_NAME = 'CHATC2_CONVERSAS' AND RDB$FIELD_NAME = 'CON_RESOLUCAO')) THEN
EXECUTE STATEMENT 'ALTER TABLE CHATC2_CONVERSAS ADD CON_RESOLUCAO VARCHAR(4000)';
END;
^
COMMIT;
^
-- ============================================================
-- REGISTRO DAS MIGRAÇÕES (marca como executadas)
-- ============================================================
EXECUTE BLOCK AS
DECLARE mig_id INTEGER;
DECLARE mig_desc VARCHAR(200);
BEGIN
-- Só insere se não existir (idempotente)
FOR SELECT MIG_ID, MIG_DESCRICAO FROM (
SELECT 1 AS MIG_ID, 'Campo USU_TIPO_CHAT em USUARIOS' AS MIG_DESCRICAO FROM RDB$DATABASE UNION ALL
SELECT 2, 'Tabela CHATC2_INSTANCIAS' FROM RDB$DATABASE UNION ALL
SELECT 3, 'Tabela CHATC2_EQUIPES' FROM RDB$DATABASE UNION ALL
SELECT 4, 'Tabela CHATC2_USU_EQUIPES' FROM RDB$DATABASE UNION ALL
SELECT 5, 'Tabela CHATC2_ETIQUETAS' FROM RDB$DATABASE UNION ALL
SELECT 6, 'Tabela CHATC2_CONVERSAS' FROM RDB$DATABASE UNION ALL
SELECT 7, 'Tabela CHATC2_CONVERSAS_MENSAGENS' FROM RDB$DATABASE UNION ALL
SELECT 8, 'Tabela CHATC2_MENSAGENS_ATENDIMENTOS' FROM RDB$DATABASE UNION ALL
SELECT 9, 'Tabela CHATC2_CONVERSAS_ETIQUETAS' FROM RDB$DATABASE UNION ALL
SELECT 10, 'Tabela CHATC2_CSAT_AVALIACOES' FROM RDB$DATABASE UNION ALL
SELECT 11, 'Tabela CHATC2_CONFIGURACOES_EMPRESA' FROM RDB$DATABASE UNION ALL
SELECT 12, 'EQU_ORDEM em CHATC2_EQUIPES' FROM RDB$DATABASE UNION ALL
SELECT 13, 'Campos de triagem em config' FROM RDB$DATABASE UNION ALL
SELECT 14, 'CON_MENU_ESTADO em conversas' FROM RDB$DATABASE UNION ALL
SELECT 15, 'CON_ETIQUETAS_DESC em conversas' FROM RDB$DATABASE UNION ALL
SELECT 16, 'Tabela CHATC2_MENUS_EMPRESA' FROM RDB$DATABASE UNION ALL
SELECT 17, 'CON_USUARIO_NOME e CON_EQUIPE_NOME' FROM RDB$DATABASE UNION ALL
SELECT 18, 'MNE_ETIQUETA_IDS nos menus' FROM RDB$DATABASE UNION ALL
SELECT 19, 'MAT_TRANSCRICAO nas mídias' FROM RDB$DATABASE UNION ALL
SELECT 20, 'Generators thread-safe para todas as tabelas' FROM RDB$DATABASE UNION ALL
SELECT 21, 'Índices para busca de telefone' FROM RDB$DATABASE UNION ALL
SELECT 22, 'Mensagem e etiqueta por equipe' FROM RDB$DATABASE UNION ALL
SELECT 23, 'Tabela de agendamentos' FROM RDB$DATABASE UNION ALL
SELECT 24, 'DDL do fluxo de resolução' FROM RDB$DATABASE
) AS migracoes
INTO :mig_id, :mig_desc
DO BEGIN
IF (NOT EXISTS(SELECT 1 FROM CHATC2_CONTROLE_MIGRACOES WHERE MIG_ID = :mig_id)) THEN
INSERT INTO CHATC2_CONTROLE_MIGRACOES (MIG_ID, MIG_DESCRICAO) VALUES (:mig_id, :mig_desc);
END
END;
^
COMMIT;
^
-- ============================================================
-- FIM
-- ============================================================
-- Após executar, verifique com:
-- SELECT * FROM CHATC2_CONTROLE_MIGRACOES ORDER BY MIG_ID;
-- Deve mostrar 24 migrações registradas.
Reference in New Issue View Git Blame Copy Permalink