-- Tabela de municípios
CREATE TABLE IF NOT EXISTS municipios (
    id INT NOT NULL AUTO_INCREMENT,
    nome VARCHAR(100) COLLATE utf8mb4_unicode_ci NOT NULL,
    estado CHAR(2) COLLATE utf8mb4_unicode_ci NOT NULL,
    codigo_ibge VARCHAR(7) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
    data_criacao DATE DEFAULT NULL,
    data_atualizacao TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    populacao_total INT DEFAULT NULL,
    populacao_urbana INT DEFAULT NULL,
    populacao_rural INT DEFAULT NULL,
    area_km2 DECIMAL(10,2) DEFAULT NULL,
    densidade_demografica DECIMAL(10,2) DEFAULT NULL,
    pib_total DECIMAL(15,2) DEFAULT NULL,
    pib_per_capita DECIMAL(10,2) DEFAULT NULL,
    idhm DECIMAL(4,3) DEFAULT NULL,
    total_eleitores INT DEFAULT NULL,
    total_secoes INT DEFAULT NULL,
    zonas_eleitorais TEXT COLLATE utf8mb4_unicode_ci,
    email_prefeitura VARCHAR(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
    telefone_prefeitura VARCHAR(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
    site_oficial VARCHAR(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
    endereco_prefeitura TEXT COLLATE utf8mb4_unicode_ci,
    cep_prefeitura VARCHAR(9) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
    latitude DECIMAL(10,8) DEFAULT NULL,
    longitude DECIMAL(11,8) DEFAULT NULL,
    observacoes TEXT COLLATE utf8mb4_unicode_ci,
    regiao VARCHAR(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
    microrregiao VARCHAR(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY codigo_ibge (codigo_ibge),
    KEY idx_municipios_nome (nome),
    KEY idx_municipios_estado (estado),
    KEY idx_municipios_codigo_ibge (codigo_ibge)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabela de gestão atual dos municípios
CREATE TABLE IF NOT EXISTS municipios_gestao_atual (
    id INT NOT NULL AUTO_INCREMENT,
    municipio_id INT NOT NULL,
    inicio_mandato DATE NOT NULL,
    fim_mandato DATE NOT NULL,
    nome_prefeito VARCHAR(100) COLLATE utf8mb4_unicode_ci NOT NULL,
    partido_prefeito VARCHAR(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
    contato_id_prefeito INT DEFAULT NULL,
    nome_vice VARCHAR(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
    partido_vice VARCHAR(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
    contato_id_vice INT DEFAULT NULL,
    email_gabinete VARCHAR(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
    telefone_gabinete VARCHAR(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
    endereco_prefeitura TEXT COLLATE utf8mb4_unicode_ci,
    coligacao TEXT COLLATE utf8mb4_unicode_ci,
    observacoes TEXT COLLATE utf8mb4_unicode_ci,
    PRIMARY KEY (id),
    KEY municipio_id (municipio_id),
    KEY contato_id_prefeito (contato_id_prefeito),
    KEY contato_id_vice (contato_id_vice),
    CONSTRAINT municipios_gestao_atual_ibfk_1 FOREIGN KEY (municipio_id) REFERENCES municipios (id),
    CONSTRAINT municipios_gestao_atual_ibfk_2 FOREIGN KEY (contato_id_prefeito) REFERENCES contatos (id),
    CONSTRAINT municipios_gestao_atual_ibfk_3 FOREIGN KEY (contato_id_vice) REFERENCES contatos (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabela de emendas
CREATE TABLE IF NOT EXISTS municipios_emendas (
    id INT NOT NULL AUTO_INCREMENT,
    municipio_id INT NOT NULL,
    numero_emenda VARCHAR(50) COLLATE utf8mb4_unicode_ci NOT NULL,
    valor DECIMAL(15,2) NOT NULL,
    ano_exercicio YEAR NOT NULL,
    area_atuacao VARCHAR(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
    objeto TEXT COLLATE utf8mb4_unicode_ci,
    situacao VARCHAR(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
    data_apresentacao DATE DEFAULT NULL,
    data_liberacao DATE DEFAULT NULL,
    data_pagamento DATE DEFAULT NULL,
    observacoes TEXT COLLATE utf8mb4_unicode_ci,
    PRIMARY KEY (id),
    KEY municipio_id (municipio_id),
    KEY idx_emendas_ano (ano_exercicio),
    CONSTRAINT municipios_emendas_ibfk_1 FOREIGN KEY (municipio_id) REFERENCES municipios (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabela de projetos
CREATE TABLE IF NOT EXISTS municipios_projetos (
    id INT NOT NULL AUTO_INCREMENT,
    municipio_id INT NOT NULL,
    nome_projeto VARCHAR(255) COLLATE utf8mb4_unicode_ci NOT NULL,
    descricao TEXT COLLATE utf8mb4_unicode_ci,
    area_atuacao VARCHAR(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
    data_inicio DATE DEFAULT NULL,
    data_prevista_conclusao DATE DEFAULT NULL,
    data_conclusao DATE DEFAULT NULL,
    valor_estimado DECIMAL(15,2) DEFAULT NULL,
    valor_executado DECIMAL(15,2) DEFAULT NULL,
    situacao VARCHAR(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
    resultados TEXT COLLATE utf8mb4_unicode_ci,
    observacoes TEXT COLLATE utf8mb4_unicode_ci,
    PRIMARY KEY (id),
    KEY municipio_id (municipio_id),
    CONSTRAINT municipios_projetos_ibfk_1 FOREIGN KEY (municipio_id) REFERENCES municipios (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabela de eventos
CREATE TABLE IF NOT EXISTS municipios_eventos (
    id INT NOT NULL AUTO_INCREMENT,
    municipio_id INT NOT NULL,
    tipo_evento VARCHAR(50) COLLATE utf8mb4_unicode_ci NOT NULL,
    titulo VARCHAR(255) COLLATE utf8mb4_unicode_ci NOT NULL,
    descricao TEXT COLLATE utf8mb4_unicode_ci,
    data_inicio DATETIME NOT NULL,
    data_fim DATETIME DEFAULT NULL,
    local TEXT COLLATE utf8mb4_unicode_ci,
    participantes TEXT COLLATE utf8mb4_unicode_ci,
    resultados TEXT COLLATE utf8mb4_unicode_ci,
    observacoes TEXT COLLATE utf8mb4_unicode_ci,
    PRIMARY KEY (id),
    KEY municipio_id (municipio_id),
    KEY idx_eventos_data (data_inicio),
    CONSTRAINT municipios_eventos_ibfk_1 FOREIGN KEY (municipio_id) REFERENCES municipios (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabela de documentos
CREATE TABLE IF NOT EXISTS municipios_documentos (
    id INT NOT NULL AUTO_INCREMENT,
    municipio_id INT NOT NULL,
    tipo_documento VARCHAR(50) COLLATE utf8mb4_unicode_ci NOT NULL,
    titulo VARCHAR(255) COLLATE utf8mb4_unicode_ci NOT NULL,
    descricao TEXT COLLATE utf8mb4_unicode_ci,
    nome_arquivo VARCHAR(255) COLLATE utf8mb4_unicode_ci NOT NULL,
    caminho_arquivo VARCHAR(255) COLLATE utf8mb4_unicode_ci NOT NULL,
    data_upload TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    usuario_id INT DEFAULT NULL,
    tags TEXT COLLATE utf8mb4_unicode_ci,
    PRIMARY KEY (id),
    KEY municipio_id (municipio_id),
    KEY usuario_id (usuario_id),
    CONSTRAINT municipios_documentos_ibfk_1 FOREIGN KEY (municipio_id) REFERENCES municipios (id),
    CONSTRAINT municipios_documentos_ibfk_2 FOREIGN KEY (usuario_id) REFERENCES users (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabela de histórico
CREATE TABLE IF NOT EXISTS municipios_historico (
    id INT NOT NULL AUTO_INCREMENT,
    municipio_id INT NOT NULL,
    tipo_alteracao VARCHAR(50) COLLATE utf8mb4_unicode_ci NOT NULL,
    descricao TEXT COLLATE utf8mb4_unicode_ci NOT NULL,
    data_alteracao TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    usuario_id INT DEFAULT NULL,
    PRIMARY KEY (id),
    KEY municipio_id (municipio_id),
    KEY usuario_id (usuario_id),
    CONSTRAINT municipios_historico_ibfk_1 FOREIGN KEY (municipio_id) REFERENCES municipios (id),
    CONSTRAINT municipios_historico_ibfk_2 FOREIGN KEY (usuario_id) REFERENCES users (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabela de indicadores
CREATE TABLE IF NOT EXISTS municipios_indicadores (
    id INT NOT NULL AUTO_INCREMENT,
    municipio_id INT NOT NULL,
    ano_referencia YEAR NOT NULL,
    taxa_alfabetizacao DECIMAL(5,2) DEFAULT NULL,
    ideb_fundamental DECIMAL(3,1) DEFAULT NULL,
    ideb_medio DECIMAL(3,1) DEFAULT NULL,
    num_escolas_municipais INT DEFAULT NULL,
    num_escolas_estaduais INT DEFAULT NULL,
    num_escolas_particulares INT DEFAULT NULL,
    num_hospitais INT DEFAULT NULL,
    num_ubs INT DEFAULT NULL,
    leitos_sus INT DEFAULT NULL,
    taxa_mortalidade_infantil DECIMAL(5,2) DEFAULT NULL,
    taxa_desemprego DECIMAL(5,2) DEFAULT NULL,
    renda_media_per_capita DECIMAL(10,2) DEFAULT NULL,
    num_empresas INT DEFAULT NULL,
    perc_saneamento_basico DECIMAL(5,2) DEFAULT NULL,
    perc_coleta_lixo DECIMAL(5,2) DEFAULT NULL,
    perc_energia_eletrica DECIMAL(5,2) DEFAULT NULL,
    data_atualizacao TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    fonte_dados TEXT COLLATE utf8mb4_unicode_ci,
    observacoes TEXT COLLATE utf8mb4_unicode_ci,
    PRIMARY KEY (id),
    UNIQUE KEY municipio_id (municipio_id,ano_referencia),
    CONSTRAINT municipios_indicadores_ibfk_1 FOREIGN KEY (municipio_id) REFERENCES municipios (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabela de demandas
CREATE TABLE IF NOT EXISTS municipios_demandas (
    id INT NOT NULL AUTO_INCREMENT,
    municipio_id INT NOT NULL,
    tipo_demanda VARCHAR(50) COLLATE utf8mb4_unicode_ci NOT NULL,
    titulo VARCHAR(255) COLLATE utf8mb4_unicode_ci NOT NULL,
    descricao TEXT COLLATE utf8mb4_unicode_ci,
    solicitante_id INT DEFAULT NULL,
    data_solicitacao DATE NOT NULL,
    prazo_atendimento DATE DEFAULT NULL,
    prioridade ENUM('Baixa','Média','Alta','Urgente') COLLATE utf8mb4_unicode_ci DEFAULT 'Média',
    situacao VARCHAR(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
    resolucao TEXT COLLATE utf8mb4_unicode_ci,
    data_conclusao DATE DEFAULT NULL,
    PRIMARY KEY (id),
    KEY municipio_id (municipio_id),
    KEY solicitante_id (solicitante_id),
    KEY idx_demandas_situacao (situacao),
    CONSTRAINT municipios_demandas_ibfk_1 FOREIGN KEY (municipio_id) REFERENCES municipios (id),
    CONSTRAINT municipios_demandas_ibfk_2 FOREIGN KEY (solicitante_id) REFERENCES contatos (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabela de vereadores
CREATE TABLE IF NOT EXISTS municipios_vereadores (
    id INT NOT NULL AUTO_INCREMENT,
    municipio_id INT NOT NULL,
    contato_id INT NOT NULL,
    partido VARCHAR(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
    cargo_mesa VARCHAR(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
    comissoes TEXT COLLATE utf8mb4_unicode_ci,
    inicio_mandato DATE DEFAULT NULL,
    fim_mandato DATE DEFAULT NULL,
    situacao ENUM('Ativo','Licenciado','Afastado','Cassado') COLLATE utf8mb4_unicode_ci DEFAULT 'Ativo',
    observacoes TEXT COLLATE utf8mb4_unicode_ci,
    PRIMARY KEY (id),
    KEY municipio_id (municipio_id),
    KEY contato_id (contato_id),
    CONSTRAINT municipios_vereadores_ibfk_1 FOREIGN KEY (municipio_id) REFERENCES municipios (id),
    CONSTRAINT municipios_vereadores_ibfk_2 FOREIGN KEY (contato_id) REFERENCES contatos (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabela de resultados eleitorais
CREATE TABLE IF NOT EXISTS municipios_resultados_eleitorais (
    id INT NOT NULL AUTO_INCREMENT,
    municipio_id INT NOT NULL,
    ano_eleicao YEAR NOT NULL,
    turno INT NOT NULL,
    cargo VARCHAR(50) COLLATE utf8mb4_unicode_ci NOT NULL,
    candidato_id INT DEFAULT NULL,
    partido VARCHAR(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
    numero_votos INT NOT NULL,
    percentual_votos DECIMAL(5,2) DEFAULT NULL,
    situacao VARCHAR(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
    PRIMARY KEY (id),
    KEY municipio_id (municipio_id),
    KEY candidato_id (candidato_id),
    KEY idx_resultados_ano_cargo (ano_eleicao,cargo),
    CONSTRAINT municipios_resultados_eleitorais_ibfk_1 FOREIGN KEY (municipio_id) REFERENCES municipios (id),
    CONSTRAINT municipios_resultados_eleitorais_ibfk_2 FOREIGN KEY (candidato_id) REFERENCES contatos (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
