-- Gabinete Digital Verci - Schema MySQL -- PHP 8.4 | Cadastro Único orientado a demandas CREATE DATABASE IF NOT EXISTS verci CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; USE verci; -- --------------------------------------------------------------------------- -- Usuários do sistema (responsáveis pelas demandas) -- --------------------------------------------------------------------------- CREATE TABLE usuarios ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, nome VARCHAR(150) NOT NULL, email VARCHAR(150) NOT NULL UNIQUE, senha_hash VARCHAR(255) NOT NULL, ativo TINYINT(1) NOT NULL DEFAULT 1, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ENGINE=InnoDB; -- --------------------------------------------------------------------------- -- Origens de indicação (lista configurável) -- --------------------------------------------------------------------------- CREATE TABLE origens_indicacao ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, nome VARCHAR(100) NOT NULL UNIQUE, exige_indicador TINYINT(1) NOT NULL DEFAULT 0, ativo TINYINT(1) NOT NULL DEFAULT 1, ordem SMALLINT UNSIGNED NOT NULL DEFAULT 0 ) ENGINE=InnoDB; INSERT INTO origens_indicacao (nome, exige_indicador, ordem) VALUES ('Atendimento Presencial', 0, 1), ('WhatsApp', 0, 2), ('Telefone', 0, 3), ('Evento', 0, 4), ('Rede Social', 0, 5), ('Site', 0, 6), ('Visita', 0, 7), ('Indicação de Apoiador', 1, 8), ('Outra', 0, 9); -- --------------------------------------------------------------------------- -- Cadastro Único do Cidadão -- --------------------------------------------------------------------------- CREATE TABLE cidadaos ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, -- Dados pessoais nome_completo VARCHAR(200) NOT NULL, cpf CHAR(11) NULL UNIQUE, rg VARCHAR(20) NULL, data_nascimento DATE NULL, sexo ENUM('M', 'F', 'O', 'N') NULL COMMENT 'M=Masculino, F=Feminino, O=Outro, N=Não informado', -- Contato telefone VARCHAR(20) NULL, whatsapp VARCHAR(20) NULL, email VARCHAR(150) NULL, -- Endereço cep CHAR(8) NULL, logradouro VARCHAR(200) NULL, numero VARCHAR(20) NULL, complemento VARCHAR(100) NULL, bairro VARCHAR(100) NULL, cidade VARCHAR(100) NULL, estado CHAR(2) NULL, endereco_completo VARCHAR(400) NULL, -- Georreferenciamento latitude DECIMAL(10, 8) NULL, longitude DECIMAL(11, 8) NULL, geocod_status ENUM('pendente', 'sucesso', 'falha', 'parcial') NOT NULL DEFAULT 'pendente', geocodificado_em DATETIME NULL, -- Dados eleitorais zona_eleitoral VARCHAR(10) NULL, secao_eleitoral VARCHAR(10) NULL, -- Redes sociais instagram VARCHAR(100) NULL, facebook VARCHAR(150) NULL, tiktok VARCHAR(100) NULL, -- Relacionamento origem_indicacao_id INT UNSIGNED NULL, indicado_por_cidadao_id INT UNSIGNED NULL, eh_apoiador TINYINT(1) NOT NULL DEFAULT 0, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, CONSTRAINT fk_cidadao_origem FOREIGN KEY (origem_indicacao_id) REFERENCES origens_indicacao(id), CONSTRAINT fk_cidadao_indicador FOREIGN KEY (indicado_por_cidadao_id) REFERENCES cidadaos(id), INDEX idx_cidadao_nome (nome_completo), INDEX idx_cidadao_telefone (telefone), INDEX idx_cidadao_whatsapp (whatsapp), INDEX idx_cidadao_bairro (bairro), INDEX idx_cidadao_zona (zona_eleitoral), INDEX idx_cidadao_apoiador (eh_apoiador), INDEX idx_cidadao_geo (latitude, longitude) ) ENGINE=InnoDB; -- --------------------------------------------------------------------------- -- Categorias e subcategorias de demanda -- --------------------------------------------------------------------------- CREATE TABLE categorias_demanda ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, nome VARCHAR(100) NOT NULL UNIQUE, ativo TINYINT(1) NOT NULL DEFAULT 1, ordem SMALLINT UNSIGNED NOT NULL DEFAULT 0 ) ENGINE=InnoDB; CREATE TABLE subcategorias_demanda ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, categoria_id INT UNSIGNED NOT NULL, nome VARCHAR(100) NOT NULL, ativo TINYINT(1) NOT NULL DEFAULT 1, ordem SMALLINT UNSIGNED NOT NULL DEFAULT 0, CONSTRAINT fk_subcat_categoria FOREIGN KEY (categoria_id) REFERENCES categorias_demanda(id), UNIQUE KEY uk_subcat (categoria_id, nome) ) ENGINE=InnoDB; INSERT INTO categorias_demanda (nome, ordem) VALUES ('Infraestrutura', 1), ('Saúde', 2), ('Educação', 3), ('Assistência Social', 4), ('Outros', 99); INSERT INTO subcategorias_demanda (categoria_id, nome, ordem) SELECT id, 'Geral', 1 FROM categorias_demanda; -- --------------------------------------------------------------------------- -- Demandas -- --------------------------------------------------------------------------- CREATE TABLE demandas ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, cidadao_id INT UNSIGNED NOT NULL, categoria_id INT UNSIGNED NOT NULL, subcategoria_id INT UNSIGNED NULL, assunto VARCHAR(200) NOT NULL, descricao TEXT NOT NULL, prioridade ENUM('baixa', 'media', 'alta', 'urgente') NOT NULL DEFAULT 'media', responsavel_id INT UNSIGNED NULL, prazo DATE NULL, status ENUM('pendente', 'em_andamento', 'concluida') NOT NULL DEFAULT 'pendente', observacoes TEXT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, CONSTRAINT fk_demanda_cidadao FOREIGN KEY (cidadao_id) REFERENCES cidadaos(id), CONSTRAINT fk_demanda_categoria FOREIGN KEY (categoria_id) REFERENCES categorias_demanda(id), CONSTRAINT fk_demanda_subcategoria FOREIGN KEY (subcategoria_id) REFERENCES subcategorias_demanda(id), CONSTRAINT fk_demanda_responsavel FOREIGN KEY (responsavel_id) REFERENCES usuarios(id), INDEX idx_demanda_status (status), INDEX idx_demanda_cidadao (cidadao_id), INDEX idx_demanda_prazo (prazo) ) ENGINE=InnoDB; -- --------------------------------------------------------------------------- -- Anexos de demanda -- --------------------------------------------------------------------------- CREATE TABLE demanda_anexos ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, demanda_id INT UNSIGNED NOT NULL, nome_original VARCHAR(255) NOT NULL, caminho VARCHAR(500) NOT NULL, mime_type VARCHAR(100) NULL, tamanho_bytes INT UNSIGNED NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT fk_anexo_demanda FOREIGN KEY (demanda_id) REFERENCES demandas(id) ON DELETE CASCADE ) ENGINE=InnoDB; -- --------------------------------------------------------------------------- -- Histórico de movimentações da demanda -- --------------------------------------------------------------------------- CREATE TABLE demanda_historico ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, demanda_id INT UNSIGNED NOT NULL, usuario_id INT UNSIGNED NULL, status_anterior VARCHAR(30) NULL, status_novo VARCHAR(30) NOT NULL, descricao TEXT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT fk_hist_demanda FOREIGN KEY (demanda_id) REFERENCES demandas(id) ON DELETE CASCADE, CONSTRAINT fk_hist_usuario FOREIGN KEY (usuario_id) REFERENCES usuarios(id) ) ENGINE=InnoDB; -- --------------------------------------------------------------------------- -- Linha do tempo do cidadão -- --------------------------------------------------------------------------- CREATE TABLE cidadao_timeline ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, cidadao_id INT UNSIGNED NOT NULL, tipo ENUM( 'cadastro', 'demanda', 'alteracao_cadastral', 'atendimento', 'visita', 'comentario', 'arquivo', 'encaminhamento', 'conclusao' ) NOT NULL, titulo VARCHAR(200) NOT NULL, descricao TEXT NULL, referencia_tipo VARCHAR(50) NULL COMMENT 'demanda, anexo, etc.', referencia_id INT UNSIGNED NULL, usuario_id INT UNSIGNED NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT fk_timeline_cidadao FOREIGN KEY (cidadao_id) REFERENCES cidadaos(id) ON DELETE CASCADE, CONSTRAINT fk_timeline_usuario FOREIGN KEY (usuario_id) REFERENCES usuarios(id), INDEX idx_timeline_cidadao (cidadao_id, created_at) ) ENGINE=InnoDB; -- --------------------------------------------------------------------------- -- Auditoria de alterações cadastrais -- --------------------------------------------------------------------------- CREATE TABLE cidadao_auditoria ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, cidadao_id INT UNSIGNED NOT NULL, usuario_id INT UNSIGNED NULL, campo VARCHAR(100) NOT NULL, valor_anterior TEXT NULL, valor_novo TEXT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT fk_audit_cidadao FOREIGN KEY (cidadao_id) REFERENCES cidadaos(id) ON DELETE CASCADE, CONSTRAINT fk_audit_usuario FOREIGN KEY (usuario_id) REFERENCES usuarios(id), INDEX idx_audit_cidadao (cidadao_id, created_at) ) ENGINE=InnoDB; -- --------------------------------------------------------------------------- -- Colaboradores (equipe de campo / visitas) -- --------------------------------------------------------------------------- CREATE TABLE colaboradores ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, nome VARCHAR(150) NOT NULL, cpf CHAR(11) NULL, telefone VARCHAR(20) NULL, email VARCHAR(150) NOT NULL UNIQUE, senha_hash VARCHAR(255) NOT NULL, funcao VARCHAR(100) NULL, ativo TINYINT(1) NOT NULL DEFAULT 1, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ENGINE=InnoDB; -- --------------------------------------------------------------------------- -- Rotas de visita -- --------------------------------------------------------------------------- CREATE TABLE rotas ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, titulo VARCHAR(200) NOT NULL, descricao TEXT NULL, responsavel_id INT UNSIGNED NULL, colaborador_id INT UNSIGNED NULL, data_planejada DATE NULL, status ENUM('rascunho', 'planejada', 'em_andamento', 'concluida', 'cancelada') NOT NULL DEFAULT 'rascunho', created_by INT UNSIGNED NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, CONSTRAINT fk_rota_responsavel FOREIGN KEY (responsavel_id) REFERENCES usuarios(id), CONSTRAINT fk_rota_colaborador FOREIGN KEY (colaborador_id) REFERENCES colaboradores(id), CONSTRAINT fk_rota_criador FOREIGN KEY (created_by) REFERENCES usuarios(id) ) ENGINE=InnoDB; CREATE TABLE rota_paradas ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, rota_id INT UNSIGNED NOT NULL, cidadao_id INT UNSIGNED NOT NULL, ordem SMALLINT UNSIGNED NOT NULL DEFAULT 1, observacao TEXT NULL, status ENUM('pendente', 'visitado', 'nao_localizado', 'cancelado') NOT NULL DEFAULT 'pendente', visitado_em DATETIME NULL, CONSTRAINT fk_parada_rota FOREIGN KEY (rota_id) REFERENCES rotas(id) ON DELETE CASCADE, CONSTRAINT fk_parada_cidadao FOREIGN KEY (cidadao_id) REFERENCES cidadaos(id), UNIQUE KEY uk_rota_cidadao (rota_id, cidadao_id) ) ENGINE=InnoDB; -- --------------------------------------------------------------------------- -- Usuário admin padrão (senha: admin123 — alterar em produção) -- --------------------------------------------------------------------------- INSERT INTO usuarios (nome, email, senha_hash) VALUES ('Administrador', 'admin@verci.local', '$2y$12$SvXTRhCr.bjYAuGVNHriv.yUUwg4S9isXh9Px1DKi3O1vWrl.NaBK');