-- =====================================================================
-- 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', '<gerar hash com password_hash()>', 'admin');
