-- Script de criação do banco de dados para Portal Legislativo
-- Execute este script no seu painel cPanel ou phpMyAdmin

CREATE DATABASE IF NOT EXISTS portal_legislativo CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE portal_legislativo;

-- Tabela de usuários administradores
CREATE TABLE usuarios_admin (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    email VARCHAR(100),
    nome VARCHAR(100),
    nivel_permissao ENUM('admin', 'editor', 'autor') DEFAULT 'autor',
    ativo BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Tabela de partidos políticos
CREATE TABLE partidos (
    id INT AUTO_INCREMENT PRIMARY KEY,
    sigla VARCHAR(10) NOT NULL UNIQUE,
    nome VARCHAR(100) NOT NULL,
    numero INT,
    cor VARCHAR(7), -- Cor em hexadecimal
    ativo BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Tabela de legislaturas
CREATE TABLE legislaturas (
    id INT AUTO_INCREMENT PRIMARY KEY,
    numero INT NOT NULL,
    ano_inicio YEAR NOT NULL,
    ano_fim YEAR NOT NULL,
    descricao TEXT,
    ativo BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Tabela de vereadores
CREATE TABLE vereadores (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nome VARCHAR(100) NOT NULL,
    nome_parlamentar VARCHAR(100),
    partido_id INT,
    foto VARCHAR(255),
    biografia TEXT,
    email VARCHAR(100),
    telefone VARCHAR(20),
    facebook VARCHAR(255),
    instagram VARCHAR(255),
    twitter VARCHAR(255),
    whatsapp VARCHAR(20),
    endereco TEXT,
    data_nascimento DATE,
    profissao VARCHAR(100),
    escolaridade VARCHAR(100),
    legislatura_id INT,
    ativo BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (partido_id) REFERENCES partidos(id),
    FOREIGN KEY (legislatura_id) REFERENCES legislaturas(id)
);

-- Tabela da mesa diretora
CREATE TABLE mesa_diretora (
    id INT AUTO_INCREMENT PRIMARY KEY,
    vereador_id INT NOT NULL,
    cargo ENUM('presidente', 'vice_presidente', 'primeiro_secretario', 'segundo_secretario') NOT NULL,
    legislatura_id INT NOT NULL,
    ano_inicio YEAR NOT NULL,
    ano_fim YEAR,
    ativo BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (vereador_id) REFERENCES vereadores(id),
    FOREIGN KEY (legislatura_id) REFERENCES legislaturas(id)
);

-- Tabela de tipos de proposições
CREATE TABLE tipos_proposicoes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nome VARCHAR(50) NOT NULL,
    sigla VARCHAR(10) NOT NULL,
    descricao TEXT,
    ativo BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Tabela de proposições/trabalhos legislativos
CREATE TABLE proposicoes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    numero INT NOT NULL,
    ano YEAR NOT NULL,
    tipo_id INT NOT NULL,
    titulo VARCHAR(500) NOT NULL,
    ementa TEXT NOT NULL,
    autor_id INT,
    data_apresentacao DATE,
    situacao ENUM('tramitando', 'aprovado', 'rejeitado', 'retirado', 'arquivado') DEFAULT 'tramitando',
    arquivo_pdf VARCHAR(255),
    observacoes TEXT,
    ativo BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (tipo_id) REFERENCES tipos_proposicoes(id),
    FOREIGN KEY (autor_id) REFERENCES vereadores(id),
    UNIQUE KEY unique_proposicao (numero, ano, tipo_id)
);

-- Tabela de votações
CREATE TABLE votacoes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    proposicao_id INT NOT NULL,
    data_votacao DATE NOT NULL,
    resultado ENUM('aprovado', 'rejeitado', 'adiado') NOT NULL,
    votos_favoraveis INT DEFAULT 0,
    votos_contrarios INT DEFAULT 0,
    abstencoes INT DEFAULT 0,
    observacoes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (proposicao_id) REFERENCES proposicoes(id)
);

-- Tabela de votos individuais
CREATE TABLE votos_individuais (
    id INT AUTO_INCREMENT PRIMARY KEY,
    votacao_id INT NOT NULL,
    vereador_id INT NOT NULL,
    voto ENUM('favoravel', 'contrario', 'abstencao', 'ausente') NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (votacao_id) REFERENCES votacoes(id),
    FOREIGN KEY (vereador_id) REFERENCES vereadores(id),
    UNIQUE KEY unique_voto (votacao_id, vereador_id)
);

-- Tabela de categorias de notícias
CREATE TABLE categorias_noticias (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nome VARCHAR(50) NOT NULL,
    slug VARCHAR(50) NOT NULL UNIQUE,
    descricao TEXT,
    cor VARCHAR(7), -- Cor em hexadecimal
    ativo BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Tabela de notícias
CREATE TABLE noticias (
    id INT AUTO_INCREMENT PRIMARY KEY,
    titulo VARCHAR(500) NOT NULL,
    slug VARCHAR(500) NOT NULL UNIQUE,
    resumo TEXT,
    conteudo LONGTEXT NOT NULL,
    imagem_destaque VARCHAR(255),
    categoria_id INT,
    autor_id INT,
    data_publicacao DATETIME,
    destaque BOOLEAN DEFAULT FALSE,
    publicado BOOLEAN DEFAULT FALSE,
    visualizacoes INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (categoria_id) REFERENCES categorias_noticias(id),
    FOREIGN KEY (autor_id) REFERENCES usuarios_admin(id)
);

-- Tabela de páginas institucionais
CREATE TABLE paginas (
    id INT AUTO_INCREMENT PRIMARY KEY,
    titulo VARCHAR(200) NOT NULL,
    slug VARCHAR(200) NOT NULL UNIQUE,
    conteudo LONGTEXT NOT NULL,
    meta_description TEXT,
    ordem INT DEFAULT 0,
    menu_principal BOOLEAN DEFAULT FALSE,
    menu_footer BOOLEAN DEFAULT FALSE,
    publicado BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Tabela de documentos de transparência
CREATE TABLE documentos_transparencia (
    id INT AUTO_INCREMENT PRIMARY KEY,
    titulo VARCHAR(200) NOT NULL,
    descricao TEXT,
    categoria ENUM('lai', 'lrf', 'contratos', 'licitacoes', 'folha_pagamento', 'outros') NOT NULL,
    arquivo VARCHAR(255) NOT NULL,
    tamanho_arquivo INT,
    data_documento DATE,
    ano_referencia YEAR,
    mes_referencia TINYINT,
    publicado BOOLEAN DEFAULT TRUE,
    downloads INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Tabela de símbolos municipais
CREATE TABLE simbolos_municipais (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tipo ENUM('brasao', 'bandeira', 'hino') NOT NULL UNIQUE,
    titulo VARCHAR(100) NOT NULL,
    descricao TEXT,
    arquivo VARCHAR(255),
    letra_hino LONGTEXT, -- Para o hino municipal
    historico TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Tabela de formulários personalizados
CREATE TABLE formularios (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nome VARCHAR(100) NOT NULL,
    titulo VARCHAR(200) NOT NULL,
    descricao TEXT,
    email_destino VARCHAR(100) NOT NULL,
    campos JSON NOT NULL, -- Estrutura dos campos em JSON
    ativo BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Tabela de mensagens da ouvidoria/fale conosco
CREATE TABLE mensagens_ouvidoria (
    id INT AUTO_INCREMENT PRIMARY KEY,
    formulario_id INT,
    nome VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL,
    telefone VARCHAR(20),
    assunto VARCHAR(200) NOT NULL,
    mensagem LONGTEXT NOT NULL,
    dados_extras JSON, -- Campos extras do formulário
    ip_origem VARCHAR(45),
    user_agent TEXT,
    respondido BOOLEAN DEFAULT FALSE,
    data_resposta DATETIME,
    resposta LONGTEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (formulario_id) REFERENCES formularios(id)
);

-- Tabela de configurações do sistema
CREATE TABLE configuracoes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    chave VARCHAR(100) NOT NULL UNIQUE,
    valor LONGTEXT,
    tipo ENUM('texto', 'numero', 'boolean', 'json') DEFAULT 'texto',
    descricao TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Inserir usuário administrador padrão (senha: admin123)
INSERT INTO usuarios_admin (username, password, email, nome, nivel_permissao) VALUES 
('admin', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'admin@camara.gov.br', 'Administrador', 'admin');

-- Inserir partidos políticos básicos
INSERT INTO partidos (sigla, nome, numero, cor) VALUES 
('PT', 'Partido dos Trabalhadores', 13, '#FF0000'),
('PSDB', 'Partido da Social Democracia Brasileira', 45, '#0066CC'),
('MDB', 'Movimento Democrático Brasileiro', 15, '#00AA00'),
('PP', 'Progressistas', 11, '#0000FF'),
('PDT', 'Partido Democrático Trabalhista', 12, '#FF6600'),
('PSB', 'Partido Socialista Brasileiro', 40, '#FFFF00'),
('PL', 'Partido Liberal', 22, '#0099FF'),
('REPUBLICANOS', 'Republicanos', 10, '#006600'),
('PSD', 'Partido Social Democrático', 55, '#FF9900'),
('UNIÃO', 'União Brasil', 44, '#9900CC');

-- Inserir tipos de proposições
INSERT INTO tipos_proposicoes (nome, sigla, descricao) VALUES 
('Projeto de Lei', 'PL', 'Proposição destinada a regular matéria de competência do Poder Legislativo'),
('Projeto de Decreto Legislativo', 'PDL', 'Proposição destinada a regular matéria de exclusiva competência do Poder Legislativo'),
('Projeto de Resolução', 'PR', 'Proposição destinada a regular matéria de competência privativa da Câmara'),
('Requerimento', 'REQ', 'Proposição por meio da qual o parlamentar solicita informações ou providências'),
('Indicação', 'IND', 'Proposição por meio da qual o parlamentar sugere medidas de interesse público'),
('Moção', 'MOC', 'Proposição por meio da qual a Câmara manifesta opinião sobre determinado assunto');

-- Inserir categorias de notícias
INSERT INTO categorias_noticias (nome, slug, descricao, cor) VALUES 
('Geral', 'geral', 'Notícias gerais da Câmara Municipal', '#007BFF'),
('Sessões', 'sessoes', 'Notícias sobre sessões plenárias', '#28A745'),
('Comissões', 'comissoes', 'Notícias sobre comissões', '#FFC107'),
('Eventos', 'eventos', 'Eventos e atividades da Câmara', '#17A2B8'),
('Transparência', 'transparencia', 'Notícias sobre transparência e prestação de contas', '#6F42C1');

-- Inserir legislatura atual
INSERT INTO legislaturas (numero, ano_inicio, ano_fim, descricao) VALUES 
(1, 2021, 2024, 'Primeira Legislatura da Câmara Municipal');

-- Inserir páginas institucionais básicas
INSERT INTO paginas (titulo, slug, conteudo, menu_principal, ordem) VALUES 
('Sobre a Câmara', 'sobre', '<h2>História da Câmara Municipal</h2><p>Conteúdo sobre a história e missão da Câmara Municipal...</p>', TRUE, 1),
('Regimento Interno', 'regimento', '<h2>Regimento Interno</h2><p>Conteúdo do regimento interno...</p>', TRUE, 2),
('Estrutura Organizacional', 'estrutura', '<h2>Estrutura Organizacional</h2><p>Organograma e estrutura da Câmara...</p>', TRUE, 3);

-- Inserir símbolos municipais
INSERT INTO simbolos_municipais (tipo, titulo, descricao) VALUES 
('brasao', 'Brasão Municipal', 'Descrição do brasão do município'),
('bandeira', 'Bandeira Municipal', 'Descrição da bandeira do município'),
('hino', 'Hino Municipal', 'Hino oficial do município');

-- Inserir formulário padrão de fale conosco
INSERT INTO formularios (nome, titulo, descricao, email_destino, campos) VALUES 
('fale_conosco', 'Fale Conosco', 'Formulário para contato com a Câmara Municipal', 'contato@camara.gov.br', 
'[{"nome": "nome", "tipo": "text", "label": "Nome completo", "obrigatorio": true}, {"nome": "email", "tipo": "email", "label": "E-mail", "obrigatorio": true}, {"nome": "telefone", "tipo": "tel", "label": "Telefone", "obrigatorio": false}, {"nome": "assunto", "tipo": "text", "label": "Assunto", "obrigatorio": true}, {"nome": "mensagem", "tipo": "textarea", "label": "Mensagem", "obrigatorio": true}]');

-- Inserir configurações básicas
INSERT INTO configuracoes (chave, valor, tipo, descricao) VALUES 
('nome_camara', 'Câmara Municipal', 'texto', 'Nome da Câmara Municipal'),
('endereco', 'Rua Principal, 123 - Centro', 'texto', 'Endereço da Câmara'),
('telefone', '(11) 1234-5678', 'texto', 'Telefone principal'),
('email', 'contato@camara.gov.br', 'texto', 'E-mail principal'),
('horario_funcionamento', 'Segunda a Sexta: 8h às 17h', 'texto', 'Horário de funcionamento'),
('facebook', '', 'texto', 'URL do Facebook'),
('instagram', '', 'texto', 'URL do Instagram'),
('youtube', '', 'texto', 'URL do YouTube'),
('site_manutencao', 'false', 'boolean', 'Site em manutenção');