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

-- Legislaturas
CREATE TABLE legislaturas (
  id INT AUTO_INCREMENT PRIMARY KEY,
  ano_inicio INT NOT NULL,
  ano_fim INT NOT NULL,
  presidente_id INT DEFAULT NULL,
  resumo TEXT NULL,
  criado_em TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Vereadores
CREATE TABLE vereadores (
  id INT AUTO_INCREMENT PRIMARY KEY,
  nome VARCHAR(150) NOT NULL,
  partido VARCHAR(50) NULL,
  bio TEXT NULL,
  foto VARCHAR(255) NULL,
  criado_em TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Relação vereador x legislatura
CREATE TABLE legislatura_membros (
  id INT AUTO_INCREMENT PRIMARY KEY,
  legislatura_id INT NOT NULL,
  vereador_id INT NOT NULL,
  funcao VARCHAR(80) NULL,
  periodo_inicio DATE NULL,
  periodo_fim DATE NULL,
  FOREIGN KEY (legislatura_id) REFERENCES legislaturas(id) ON DELETE CASCADE,
  FOREIGN KEY (vereador_id) REFERENCES vereadores(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Leis (destaques)
CREATE TABLE leis (
  id INT AUTO_INCREMENT PRIMARY KEY,
  legislatura_id INT NOT NULL,
  titulo VARCHAR(255) NOT NULL,
  descricao TEXT NULL,
  numero VARCHAR(60) NULL,
  data_publicacao DATE NULL,
  pdf_arquivo VARCHAR(255) NULL,
  criado_em TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (legislatura_id) REFERENCES legislaturas(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Fotos históricas
CREATE TABLE fotos (
  id INT AUTO_INCREMENT PRIMARY KEY,
  legislatura_id INT NULL,
  arquivo VARCHAR(255) NOT NULL,
  legenda VARCHAR(255) NULL,
  credito VARCHAR(255) NULL,
  criado_em TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (legislatura_id) REFERENCES legislaturas(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- Ex-presidentes
CREATE TABLE presidentes (
  id INT AUTO_INCREMENT PRIMARY KEY,
  vereador_id INT NOT NULL,
  periodo VARCHAR(80) NOT NULL,
  foto VARCHAR(255) NULL,
  biografia TEXT NULL,
  FOREIGN KEY (vereador_id) REFERENCES vereadores(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Seeds de exemplo
INSERT INTO vereadores (nome, partido) VALUES
('João Nunes Ferreira','MDB'),
('Erisvaldo Bandeira Rios','ARENA');

INSERT INTO legislaturas (ano_inicio, ano_fim, resumo) VALUES
(1972, 1977, 'Oitava Legislatura. Período marcado por...');

INSERT INTO legislatura_membros (legislatura_id, vereador_id, funcao)
VALUES (1, 1, 'Vereador Titular'), (1, 2, 'Suplente');

INSERT INTO leis (legislatura_id, titulo, descricao, numero, data_publicacao)
VALUES (1, 'Lei de Criação do Bairro X','Institui o bairro X no município', 'Lei 123/1973','1973-04-15');
