-- =====================================================================
-- SISTEMA DE PESQUISA / ENQUETE ELEITORAL  —  ESTRUTURA DO BANCO
-- MySQL 8.0+ / MariaDB 10.5+   (InnoDB, utf8mb4)
-- =====================================================================

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

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 1;

-- ---------------------------------------------------------------------
-- 1. USUÁRIOS DO PAINEL
-- ---------------------------------------------------------------------
CREATE TABLE `usuarios` (
  `id`             INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `nome`           VARCHAR(120) NOT NULL,
  `email`          VARCHAR(160) NOT NULL,
  `senha_hash`     VARCHAR(255) NOT NULL,
  `papel`          ENUM('admin','editor','auditor') NOT NULL DEFAULT 'editor',
  `ativo`          TINYINT(1) NOT NULL DEFAULT 1,
  `tentativas`     TINYINT UNSIGNED NOT NULL DEFAULT 0,
  `bloqueado_ate`  DATETIME NULL,
  `ultimo_login`   DATETIME NULL,
  `criado_em`      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_usuarios_email` (`email`)
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- 2. REGIÕES (espelham os códigos oficiais do IBGE)
--    tipo = estado (nível N3) ou municipio (nível N6)
-- ---------------------------------------------------------------------
CREATE TABLE `regioes` (
  `id`              INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `nome`            VARCHAR(120) NOT NULL,
  `uf`              CHAR(2) NOT NULL,
  `tipo`            ENUM('estado','municipio') NOT NULL,
  `ibge_codigo`     VARCHAR(10) NOT NULL COMMENT 'Código IBGE do estado (2 díg.) ou município (7 díg.)',
  `nivel_ibge`      ENUM('N3','N6') NOT NULL DEFAULT 'N3',
  `populacao`       BIGINT UNSIGNED NULL COMMENT 'Cache da última leitura do IBGE',
  `populacao_ano`   SMALLINT NULL,
  `ativo`           TINYINT(1) NOT NULL DEFAULT 1,
  `criado_em`       DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_regioes_ibge` (`ibge_codigo`),
  KEY `ix_regioes_uf` (`uf`, `tipo`)
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- 3. ENQUETES
-- ---------------------------------------------------------------------
CREATE TABLE `enquetes` (
  `id`                INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `titulo`            VARCHAR(180) NOT NULL,
  `slug`              VARCHAR(180) NOT NULL,
  `pergunta`          VARCHAR(255) NOT NULL,
  `descricao`         TEXT NULL,
  `cargo`             ENUM('presidente','governador','senador','deputado','prefeito','tema') NOT NULL DEFAULT 'presidente',
  `imagem_capa`       VARCHAR(255) NULL,
  `abertura`          DATETIME NOT NULL,
  `encerramento`      DATETIME NULL,
  `status`            ENUM('rascunho','ativa','pausada','encerrada') NOT NULL DEFAULT 'rascunho',
  `exige_localidade`  TINYINT(1) NOT NULL DEFAULT 1,
  `exige_token`       TINYINT(1) NOT NULL DEFAULT 0 COMMENT '1 = só vota com link único (grupos de WhatsApp)',
  `resultado_publico` TINYINT(1) NOT NULL DEFAULT 1,
  `criado_por`        INT UNSIGNED NULL,
  `criado_em`         DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `atualizado_em`     DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_enquetes_slug` (`slug`),
  KEY `ix_enquetes_status` (`status`, `abertura`),
  CONSTRAINT `fk_enquetes_usuario` FOREIGN KEY (`criado_por`)
    REFERENCES `usuarios` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB;

-- Escopo do lançamento: BA, SP, ES/Vitória...
CREATE TABLE `enquete_regioes` (
  `enquete_id` INT UNSIGNED NOT NULL,
  `regiao_id`  INT UNSIGNED NOT NULL,
  PRIMARY KEY (`enquete_id`, `regiao_id`),
  CONSTRAINT `fk_er_enquete` FOREIGN KEY (`enquete_id`) REFERENCES `enquetes` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_er_regiao`  FOREIGN KEY (`regiao_id`)  REFERENCES `regioes`  (`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- 4. CANDIDATOS
-- ---------------------------------------------------------------------
CREATE TABLE `candidatos` (
  `id`            INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `nome`          VARCHAR(160) NOT NULL,
  `nome_urna`     VARCHAR(80) NOT NULL,
  `partido`       VARCHAR(40) NULL,
  `numero`        VARCHAR(10) NULL,
  `cargo`         VARCHAR(40) NULL,
  `foto`          VARCHAR(255) NULL COMMENT 'Caminho relativo em /public/uploads/candidatos',
  `foto_url_ext`  VARCHAR(500) NULL COMMENT 'Foto vinda do site oficial (migração/CDN)',
  `cor_hex`       CHAR(7) NOT NULL DEFAULT '#0d6efd',
  `bio`           TEXT NULL,
  `site_oficial`  VARCHAR(255) NULL,
  `ativo`         TINYINT(1) NOT NULL DEFAULT 1,
  `criado_em`     DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `ix_candidatos_nome` (`nome_urna`)
) ENGINE=InnoDB;

CREATE TABLE `enquete_candidatos` (
  `id`           INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `enquete_id`   INT UNSIGNED NOT NULL,
  `candidato_id` INT UNSIGNED NOT NULL,
  `ordem`        SMALLINT NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_ec` (`enquete_id`, `candidato_id`),
  CONSTRAINT `fk_ec_enquete`   FOREIGN KEY (`enquete_id`)   REFERENCES `enquetes`   (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_ec_candidato` FOREIGN KEY (`candidato_id`) REFERENCES `candidatos` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Realizações / histórico — SEMPRE com fonte verificável.
-- Este conteúdo é curado no painel; o IBGE não fornece "obras por político".
CREATE TABLE `candidato_realizacoes` (
  `id`           INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `candidato_id` INT UNSIGNED NOT NULL,
  `titulo`       VARCHAR(200) NOT NULL,
  `descricao`    TEXT NULL,
  `ano_inicio`   SMALLINT NULL,
  `ano_fim`      SMALLINT NULL,
  `categoria`    VARCHAR(60) NULL COMMENT 'saúde, educação, economia, infraestrutura...',
  `fonte_nome`   VARCHAR(160) NOT NULL,
  `fonte_url`    VARCHAR(500) NOT NULL,
  `verificado`   TINYINT(1) NOT NULL DEFAULT 0,
  `ordem`        SMALLINT NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `ix_real_candidato` (`candidato_id`, `ordem`),
  CONSTRAINT `fk_real_candidato` FOREIGN KEY (`candidato_id`) REFERENCES `candidatos` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- 5. VOTOS  (1 voto por eleitor_hash por enquete — garantido por índice)
-- ---------------------------------------------------------------------
CREATE TABLE `votos` (
  `id`            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `enquete_id`    INT UNSIGNED NOT NULL,
  `candidato_id`  INT UNSIGNED NOT NULL,
  `regiao_id`     INT UNSIGNED NULL,
  `eleitor_hash`  CHAR(64) NOT NULL COMMENT 'HMAC-SHA256 (IP + UA + fingerprint + pepper)',
  `ip_hash`       CHAR(64) NOT NULL,
  `dispositivo`   ENUM('mobile','tablet','desktop','outro') NOT NULL DEFAULT 'outro',
  `origem`        VARCHAR(40) NOT NULL DEFAULT 'direto' COMMENT 'whatsapp, site_oficial, instagram, direto',
  `utm_campanha`  VARCHAR(80) NULL,
  `faixa_idade`   ENUM('16-24','25-34','35-44','45-59','60+') NULL,
  `sexo`          ENUM('F','M','outro','nao_informado') NULL DEFAULT 'nao_informado',
  `criado_em`     DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_votos_unico` (`enquete_id`, `eleitor_hash`),
  KEY `ix_votos_apuracao` (`enquete_id`, `candidato_id`, `regiao_id`),
  KEY `ix_votos_data` (`enquete_id`, `criado_em`),
  CONSTRAINT `fk_votos_enquete`   FOREIGN KEY (`enquete_id`)   REFERENCES `enquetes`   (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_votos_candidato` FOREIGN KEY (`candidato_id`) REFERENCES `candidatos` (`id`),
  CONSTRAINT `fk_votos_regiao`    FOREIGN KEY (`regiao_id`)    REFERENCES `regioes`    (`id`) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- 6. TOKENS DE VOTO (link único distribuído nos grupos de WhatsApp)
-- ---------------------------------------------------------------------
CREATE TABLE `tokens_voto` (
  `id`            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `enquete_id`    INT UNSIGNED NOT NULL,
  `token`         CHAR(32) NOT NULL,
  `canal`         VARCHAR(60) NOT NULL DEFAULT 'whatsapp',
  `lote`          VARCHAR(60) NULL COMMENT 'Nome do grupo/lote de distribuição',
  `telefone_hash` CHAR(64) NULL,
  `usado_em`      DATETIME NULL,
  `expira_em`     DATETIME NULL,
  `criado_em`     DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_token` (`token`),
  KEY `ix_token_enquete` (`enquete_id`, `usado_em`),
  CONSTRAINT `fk_token_enquete` FOREIGN KEY (`enquete_id`) REFERENCES `enquetes` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- 7. CACHE DA API DO IBGE
-- ---------------------------------------------------------------------
CREATE TABLE `ibge_cache` (
  `chave`         VARCHAR(190) NOT NULL,
  `payload`       LONGTEXT NOT NULL,
  `atualizado_em` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `expira_em`     DATETIME NOT NULL,
  PRIMARY KEY (`chave`),
  KEY `ix_cache_exp` (`expira_em`)
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- 8. RATE LIMIT + AUDITORIA (LGPD)
-- ---------------------------------------------------------------------
CREATE TABLE `rate_limit` (
  `chave`     VARCHAR(190) NOT NULL,
  `janela`    INT UNSIGNED NOT NULL,
  `contador`  INT UNSIGNED NOT NULL DEFAULT 1,
  PRIMARY KEY (`chave`, `janela`)
) ENGINE=InnoDB;

CREATE TABLE `auditoria` (
  `id`         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `usuario_id` INT UNSIGNED NULL,
  `acao`       VARCHAR(80) NOT NULL,
  `entidade`   VARCHAR(60) NULL,
  `entidade_id` VARCHAR(40) NULL,
  `detalhe`    JSON NULL,
  `ip_hash`    CHAR(64) NULL,
  `criado_em`  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `ix_auditoria_data` (`criado_em`),
  CONSTRAINT `fk_auditoria_usuario` FOREIGN KEY (`usuario_id`) REFERENCES `usuarios` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- 9. VIEW DE APURAÇÃO
-- ---------------------------------------------------------------------
CREATE OR REPLACE VIEW `vw_apuracao` AS
SELECT
  v.enquete_id,
  c.id            AS candidato_id,
  c.nome_urna,
  c.partido,
  c.cor_hex,
  c.foto,
  r.uf,
  r.nome          AS regiao_nome,
  COUNT(*)        AS votos
FROM votos v
JOIN candidatos c ON c.id = v.candidato_id
LEFT JOIN regioes r ON r.id = v.regiao_id
GROUP BY v.enquete_id, c.id, c.nome_urna, c.partido, c.cor_hex, c.foto, r.uf, r.nome;

-- =====================================================================
-- SEEDS
-- =====================================================================

-- ATENCAO: o hash abaixo e um PLACEHOLDER e nao corresponde a nenhuma senha.
-- Gere o seu com:  php tools/gerar-hash.php "SuaSenhaForte@2026"
-- e rode:  UPDATE usuarios SET senha_hash = '<hash>' WHERE email = 'admin@pesquisa.local';
INSERT INTO `usuarios` (`nome`, `email`, `senha_hash`, `papel`) VALUES
('Administrador', 'admin@pesquisa.local',
 '$2y$12$Q9Zq0Yy8wKQ1yZ2m0m8N7uJt3wYcQb5eYyKcE4hZQXqWxq7WcS9Ru', 'admin');

-- Regiões do lançamento (códigos oficiais IBGE)
INSERT INTO `regioes` (`nome`, `uf`, `tipo`, `ibge_codigo`, `nivel_ibge`) VALUES
('Bahia',           'BA', 'estado',    '29',      'N3'),
('São Paulo',       'SP', 'estado',    '35',      'N3'),
('Espírito Santo',  'ES', 'estado',    '32',      'N3'),
('Vitória',         'ES', 'municipio', '3205309', 'N6'),
('Salvador',        'BA', 'municipio', '2927408', 'N6'),
('São Paulo',       'SP', 'municipio', '3550308', 'N6');

INSERT INTO `enquetes`
 (`titulo`, `slug`, `pergunta`, `descricao`, `cargo`, `abertura`, `status`, `exige_localidade`, `criado_por`)
VALUES
('Pesquisa Presidencial 2026', 'presidente-2026',
 'Se a eleição para Presidente fosse hoje, em quem você votaria?',
 'Pesquisa de opinião espontânea, aberta ao público. Não possui registro no TSE e não substitui pesquisa contratada.',
 'presidente', NOW(), 'ativa', 1, 1);

INSERT INTO `candidatos` (`nome`, `nome_urna`, `partido`, `numero`, `cargo`, `cor_hex`, `ativo`) VALUES
('Luiz Inácio Lula da Silva', 'Lula', 'PT', '13', 'Presidente', '#c62828', 1),
('Candidato B', 'Candidato B', '—', '00', 'Presidente', '#1565c0', 1),
('Candidato C', 'Candidato C', '—', '00', 'Presidente', '#2e7d32', 1),
('Branco / Nulo', 'Branco/Nulo', NULL, NULL, 'Presidente', '#616161', 1),
('Não sei / Não decidi', 'Indeciso', NULL, NULL, 'Presidente', '#9e9e9e', 1);

INSERT INTO `enquete_candidatos` (`enquete_id`, `candidato_id`, `ordem`)
SELECT 1, id, id FROM `candidatos`;

INSERT INTO `enquete_regioes` (`enquete_id`, `regiao_id`)
SELECT 1, id FROM `regioes`;