-- Banco de Dados: Sistema de Portarias v3
-- Criado em: 2025-10-28
-- Estrutura NORMALIZADA com tabelas auxiliares

CREATE DATABASE IF NOT EXISTS sistema_portarias 
CHARACTER SET utf8mb4 
COLLATE utf8mb4_unicode_ci;

USE sistema_portarias;

-- ====================================
-- TABELAS AUXILIARES (Cadastros Base)
-- ====================================

-- Tabela de servidores (cadastro único)
CREATE TABLE IF NOT EXISTS servidores (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nome VARCHAR(255) NOT NULL,
    nome_normalizado VARCHAR(255) NOT NULL,
    data_cadastro TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY idx_nome_normalizado (nome_normalizado),
    INDEX idx_nome (nome)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabela de cargos (lista padronizada)
CREATE TABLE IF NOT EXISTS cargos (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nome VARCHAR(100) NOT NULL,
    sigla VARCHAR(20) NULL,
    data_cadastro TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY idx_nome (nome),
    INDEX idx_sigla (sigla)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabela de funções na portaria (lista padronizada)
CREATE TABLE IF NOT EXISTS funcoes_portaria (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nome VARCHAR(100) NOT NULL,
    descricao TEXT NULL,
    data_cadastro TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY idx_nome (nome)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ====================================
-- TABELAS PRINCIPAIS
-- ====================================

-- Tabela principal de portarias
CREATE TABLE IF NOT EXISTS portarias (
    id INT AUTO_INCREMENT PRIMARY KEY,
    numero_portaria VARCHAR(50) NOT NULL,
    emissao DATE NOT NULL,
    finalidade TEXT NOT NULL,
    tipo VARCHAR(50) NULL,
    vigencia VARCHAR(255) NULL,
    status VARCHAR(50) NULL,
    nova_portaria VARCHAR(50) NULL,
    data_importacao TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    data_atualizacao TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY idx_portaria_emissao (numero_portaria, emissao),
    INDEX idx_numero_portaria (numero_portaria),
    INDEX idx_emissao (emissao),
    INDEX idx_status (status),
    INDEX idx_tipo (tipo)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabela de membros designados (NORMALIZADA)
CREATE TABLE IF NOT EXISTS membros (
    id INT AUTO_INCREMENT PRIMARY KEY,
    portaria_id INT NOT NULL,
    servidor_id INT NOT NULL,
    cargo_id INT NULL,
    funcao_id INT NULL,
    data_importacao TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_portaria_id (portaria_id),
    INDEX idx_servidor_id (servidor_id),
    INDEX idx_cargo_id (cargo_id),
    INDEX idx_funcao_id (funcao_id),
    UNIQUE KEY idx_unique_membro (portaria_id, servidor_id, funcao_id),
    FOREIGN KEY (portaria_id) REFERENCES portarias(id) ON DELETE CASCADE,
    FOREIGN KEY (servidor_id) REFERENCES servidores(id) ON DELETE RESTRICT,
    FOREIGN KEY (cargo_id) REFERENCES cargos(id) ON DELETE SET NULL,
    FOREIGN KEY (funcao_id) REFERENCES funcoes_portaria(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ====================================
-- VIEWS PARA CONSULTAS
-- ====================================

-- View completa com todos os dados desnormalizados (facilita consultas)
CREATE OR REPLACE VIEW vw_portarias_completas AS
SELECT 
    p.id as portaria_id,
    p.numero_portaria,
    p.emissao,
    p.finalidade,
    p.tipo,
    p.vigencia,
    p.status,
    p.nova_portaria,
    m.id as membro_id,
    s.nome as servidor_nome,
    c.nome as cargo_nome,
    c.sigla as cargo_sigla,
    f.nome as funcao_nome,
    f.descricao as funcao_descricao
FROM portarias p
LEFT JOIN membros m ON p.id = m.portaria_id
LEFT JOIN servidores s ON m.servidor_id = s.id
LEFT JOIN cargos c ON m.cargo_id = c.id
LEFT JOIN funcoes_portaria f ON m.funcao_id = f.id
ORDER BY p.emissao DESC, p.numero_portaria, s.nome;

-- View de servidores com contagem de portarias
CREATE OR REPLACE VIEW vw_servidores_resumo AS
SELECT 
    s.id,
    s.nome,
    COUNT(DISTINCT m.portaria_id) as total_portarias,
    GROUP_CONCAT(DISTINCT f.nome ORDER BY f.nome SEPARATOR ', ') as funcoes,
    GROUP_CONCAT(DISTINCT c.nome ORDER BY c.nome SEPARATOR ', ') as cargos
FROM servidores s
LEFT JOIN membros m ON s.id = m.servidor_id
LEFT JOIN funcoes_portaria f ON m.funcao_id = f.id
LEFT JOIN cargos c ON m.cargo_id = c.id
GROUP BY s.id, s.nome
ORDER BY total_portarias DESC, s.nome;

-- View de portarias com contagem de membros
CREATE OR REPLACE VIEW vw_portarias_resumo AS
SELECT 
    p.id,
    p.numero_portaria,
    p.emissao,
    p.finalidade,
    p.tipo,
    p.status,
    COUNT(m.id) as total_membros
FROM portarias p
LEFT JOIN membros m ON p.id = m.portaria_id
GROUP BY p.id
ORDER BY p.emissao DESC;

-- ====================================
-- DADOS INICIAIS (Funções comuns)
-- ====================================

-- Inserir funções comuns de portarias
INSERT INTO funcoes_portaria (nome, descricao) VALUES
('Presidente', 'Presidente da comissão'),
('Vice-Presidente', 'Vice-presidente da comissão'),
('Membro', 'Membro da comissão'),
('Suplente', 'Membro suplente da comissão'),
('Secretário', 'Secretário da comissão'),
('Coordenador', 'Coordenador'),
('Fiscal', 'Fiscal do contrato'),
('Gestor', 'Gestor do contrato'),
('Requisitante', 'Requisitante')
ON DUPLICATE KEY UPDATE nome=nome;

-- ====================================
-- ÍNDICES ADICIONAIS PARA PERFORMANCE
-- ====================================

-- Índice fulltext para busca de texto em finalidade
ALTER TABLE portarias ADD FULLTEXT INDEX idx_finalidade_fulltext (finalidade);

-- Índice composto para consultas comuns
CREATE INDEX idx_servidor_cargo ON membros(servidor_id, cargo_id);
CREATE INDEX idx_portaria_status_emissao ON portarias(status, emissao);
