-- ===================================================================== -- Sistema Cristo é Vida — Recebimento e Acompanhamento de Mensagens -- Banco de dados MySQL -- ===================================================================== CREATE DATABASE IF NOT EXISTS cristoevida_sistema CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; USE cristoevida_sistema; -- --------------------------------------------------------------------- -- Usuários do painel (multi-usuário, com papéis) -- --------------------------------------------------------------------- CREATE TABLE usuarios ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, nome VARCHAR(150) NOT NULL, email VARCHAR(190) NOT NULL UNIQUE, senha_hash VARCHAR(255) NOT NULL, papel ENUM('admin','responsavel','leitor') NOT NULL DEFAULT 'responsavel', foto VARCHAR(255) NULL, ativo TINYINT(1) NOT NULL DEFAULT 1, recebe_email_notificacao TINYINT(1) NOT NULL DEFAULT 1, -- recebe e-mail quando chega mensagem nova ultimo_acesso DATETIME NULL, criado_em DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, atualizado_em DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ENGINE=InnoDB; -- --------------------------------------------------------------------- -- Mensagens recebidas pelo site (formulário principal ou popup) -- --------------------------------------------------------------------- CREATE TABLE mensagens ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, nome VARCHAR(150) NOT NULL, idade TINYINT UNSIGNED NULL, cidade VARCHAR(120) NULL, estado CHAR(2) NULL, whatsapp VARCHAR(30) NULL, email VARCHAR(190) NULL, tipo_decisao ENUM('entender','estudos','conversar','oracao','entreguei','duvidas') NOT NULL, mensagem_texto TEXT NULL, origem ENUM('formulario_principal','popup') NOT NULL DEFAULT 'formulario_principal', status ENUM('novo','em_acompanhamento','concluido') NOT NULL DEFAULT 'novo', responsavel_id INT UNSIGNED NULL, ip_origem VARCHAR(45) NULL, criado_em DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, atualizado_em DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, CONSTRAINT fk_mensagens_responsavel FOREIGN KEY (responsavel_id) REFERENCES usuarios(id) ON DELETE SET NULL, INDEX idx_status (status), INDEX idx_responsavel (responsavel_id), INDEX idx_criado_em (criado_em) ) ENGINE=InnoDB; -- --------------------------------------------------------------------- -- Histórico/linha do tempo de cada caso (acompanhamento, mudanças de -- status, trocas de responsável, anotações do responsável) -- --------------------------------------------------------------------- CREATE TABLE mensagem_historico ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, mensagem_id INT UNSIGNED NOT NULL, usuario_id INT UNSIGNED NULL, -- quem gerou o evento (NULL = sistema) tipo ENUM('nota','mudanca_status','atribuicao','sistema') NOT NULL DEFAULT 'nota', texto TEXT NOT NULL, criado_em DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT fk_historico_mensagem FOREIGN KEY (mensagem_id) REFERENCES mensagens(id) ON DELETE CASCADE, CONSTRAINT fk_historico_usuario FOREIGN KEY (usuario_id) REFERENCES usuarios(id) ON DELETE SET NULL, INDEX idx_mensagem (mensagem_id) ) ENGINE=InnoDB; -- --------------------------------------------------------------------- -- Chat interno vinculado a um caso específico (todos os responsáveis -- envolvidos no caso podem conversar sobre ele) -- --------------------------------------------------------------------- CREATE TABLE chat_caso_mensagens ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, mensagem_id INT UNSIGNED NOT NULL, usuario_id INT UNSIGNED NOT NULL, texto TEXT NOT NULL, criado_em DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT fk_chatcaso_mensagem FOREIGN KEY (mensagem_id) REFERENCES mensagens(id) ON DELETE CASCADE, CONSTRAINT fk_chatcaso_usuario FOREIGN KEY (usuario_id) REFERENCES usuarios(id) ON DELETE CASCADE, INDEX idx_mensagem (mensagem_id) ) ENGINE=InnoDB; -- controla quem já leu o chat de cada caso (para o contador do sino) CREATE TABLE chat_caso_leituras ( mensagem_id INT UNSIGNED NOT NULL, usuario_id INT UNSIGNED NOT NULL, lido_ate DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (mensagem_id, usuario_id), CONSTRAINT fk_leitura_mensagem FOREIGN KEY (mensagem_id) REFERENCES mensagens(id) ON DELETE CASCADE, CONSTRAINT fk_leitura_usuario FOREIGN KEY (usuario_id) REFERENCES usuarios(id) ON DELETE CASCADE ) ENGINE=InnoDB; -- --------------------------------------------------------------------- -- Mensagens diretas 1:1 entre usuários do painel -- --------------------------------------------------------------------- CREATE TABLE mensagens_diretas ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, remetente_id INT UNSIGNED NOT NULL, destinatario_id INT UNSIGNED NOT NULL, texto TEXT NOT NULL, lida_em DATETIME NULL, criado_em DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT fk_dm_remetente FOREIGN KEY (remetente_id) REFERENCES usuarios(id) ON DELETE CASCADE, CONSTRAINT fk_dm_destinatario FOREIGN KEY (destinatario_id) REFERENCES usuarios(id) ON DELETE CASCADE, INDEX idx_destinatario (destinatario_id, lida_em) ) ENGINE=InnoDB; -- --------------------------------------------------------------------- -- Notificações do sino no header (uma linha por evento por usuário) -- --------------------------------------------------------------------- CREATE TABLE notificacoes ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, usuario_id INT UNSIGNED NOT NULL, tipo ENUM('mensagem_nova','atribuicao','chat_caso','mensagem_direta') NOT NULL, referencia_id INT UNSIGNED NULL, -- id da mensagem, do dm, etc. conforme o tipo texto VARCHAR(255) NOT NULL, link VARCHAR(255) NULL, lida TINYINT(1) NOT NULL DEFAULT 0, criado_em DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT fk_notif_usuario FOREIGN KEY (usuario_id) REFERENCES usuarios(id) ON DELETE CASCADE, INDEX idx_usuario_lida (usuario_id, lida) ) ENGINE=InnoDB; -- --------------------------------------------------------------------- -- Configurações gerais do sistema (chave/valor), editáveis no painel -- Ex: texto do e-mail de conforto, assunto, assinatura, nome do sistema -- --------------------------------------------------------------------- CREATE TABLE configuracoes ( chave VARCHAR(100) PRIMARY KEY, valor TEXT NULL, atualizado_em DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ENGINE=InnoDB; INSERT INTO configuracoes (chave, valor) VALUES ('nome_sistema', 'Cristo é Vida — Painel de Acompanhamento'), ('email_conforto_assunto', 'Recebemos sua mensagem 💛'), ('email_conforto_corpo', 'Olá {{nome}},\n\nQue alegria saber do seu contato! Sua mensagem já chegou até nossa equipe e em breve alguém entrará em contato com você.\n\n"Vinde a mim, todos os que estais cansados e oprimidos, e eu vos aliviarei." — Mateus 11:28\n\nEstamos orando por você.\n\nCom carinho,\nEquipe Cristo é Vida'), ('email_assinatura', 'Igreja Batista Fundamentalista Cristo é Vida'), ('email_notificacao_assunto', 'Nova mensagem recebida pelo site — {{nome}}'); -- --------------------------------------------------------------------- -- Usuário administrador inicial de exemplo -- (troque a senha assim que possível pelo painel) -- senha inicial sugerida: "trocaresta123" (hash gerado com password_hash) -- --------------------------------------------------------------------- -- INSERT INTO usuarios (nome, email, senha_hash, papel) VALUES -- ('Administrador', 'admin@cristoevida.com', '', 'admin');