-- Migration to create contatos_votacao table with relationships to contatos, municipios, and municipios_gestao_atual

CREATE TABLE contatos_votacao (
    id INT AUTO_INCREMENT PRIMARY KEY,
    ano YEAR NOT NULL,
    turno TINYINT NOT NULL,
    tipo_eleicao VARCHAR(50) NOT NULL,
    sigla_uf VARCHAR(5) NOT NULL,
    id_municipio INT NOT NULL,
    id_municipio_nome VARCHAR(255) NOT NULL,
    cargo VARCHAR(100) NOT NULL,
    sigla_partido VARCHAR(20) NOT NULL,
    resultado VARCHAR(50) NOT NULL,
    votos INT NOT NULL,
    contato_id INT NOT NULL,
    municipio_id INT NOT NULL,
    municipio_gestao_atual_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (contato_id) REFERENCES contatos(id) ON DELETE CASCADE,
    FOREIGN KEY (municipio_id) REFERENCES municipios(id) ON DELETE CASCADE,
    FOREIGN KEY (municipio_gestao_atual_id) REFERENCES municipios_gestao_atual(id) ON DELETE CASCADE
);
