apps/worker/src/services/premiacao-edicoes.ts

import type { Env, IdsScore } from '../types'
import { getSenadorList, LEG57_PERIODO } from './legis'
import type { Periodo } from './legis'
import { computeRanking, getRankingSnapshot, listSnapshots } from './ranking'
import {
  apurarScores,
  REGUA_ANO,
  REGUA_LEGISLATURA,
  NOMES_UF,
} from './premiacao'
import type { CategoriaApurada, EixoId, IndicadorBruto, Premiacao, Regua } from './premiacao'
import { assinarCertificado, sha256Hex } from './assinatura'

/**
 * Edições do Prêmio Observatório do Senado.
 *
 * Duas famílias, ambas com as mesmas 39 categorias:
 *  - `leg57`: a legislatura inteira, sobre um snapshot do ranking semanal;
 *  - `leg57-2025`: um ano legislativo, com o IDS recalculado só naquele ano.
 *
 * Fluxo: rascunho → (apurando) → apurada → aprovada → divulgada.
 * Até aprovar, dá para reapurar quantas vezes for preciso. Aprovar trava o
 * resultado (guardado com hash) e emite um certificado por vencedor. Divulgar
 * torna o resultado público; antes disso, só o admin e quem tem o código de um
 * certificado veem alguma coisa.
 */

export type TipoEdicao = 'legislatura' | 'ano'
export type StatusEdicao = 'rascunho' | 'apurando' | 'apurada' | 'aprovada' | 'divulgada'

/** Legislaturas que a camada de dados sabe medir hoje. A 58 entra na transição. */
const LEGISLATURAS_SUPORTADAS = [57]
const ANOS_LEG57 = [2023, 2024, 2025, 2026]

export interface DefinicaoEdicao {
  id: string
  tipo: TipoEdicao
  legislatura: number
  ano?: number
  /** "57ª Legislatura (2023–2027)", "Ano Legislativo 2025" */
  titulo: string
  /** Linha de apoio do certificado. */
  subtitulo: string
  /** Completa a descrição do Grande Prêmio. */
  recorte: string
  periodo: Periodo
  regua: Regua
  /** Prefixo dos códigos de certificado: "L57", "A25". */
  prefixo: string
}

export function definirEdicao(
  tipo: TipoEdicao,
  legislatura: number,
  ano?: number,
): DefinicaoEdicao {
  if (!LEGISLATURAS_SUPORTADAS.includes(legislatura)) {
    throw new EdicaoInvalida(
      `a ${legislatura}ª Legislatura ainda não é medida pelo IDS; ` +
        'ela entra junto com a transição de legislatura',
    )
  }
  if (tipo === 'legislatura') {
    return {
      id: `leg${legislatura}`,
      tipo,
      legislatura,
      titulo: '57ª Legislatura (2023–2027)',
      subtitulo: 'Edição da Legislatura · 1º de fevereiro de 2023 a 31 de janeiro de 2027',
      recorte: 'da 57ª Legislatura',
      periodo: LEG57_PERIODO,
      regua: REGUA_LEGISLATURA,
      prefixo: `L${legislatura}`,
    }
  }
  if (!ano || !ANOS_LEG57.includes(ano)) {
    throw new EdicaoInvalida(`ano legislativo inválido para a 57ª Legislatura: ${ano}`)
  }
  const ordinal = ano - 2022
  // O ano civil, aparado no início da legislatura: janeiro de 2023 ainda era a 56ª.
  const inicio = `${ano}-01-01` < LEG57_PERIODO.inicio ? LEG57_PERIODO.inicio : `${ano}-01-01`
  return {
    id: `leg${legislatura}-${ano}`,
    tipo,
    legislatura,
    ano,
    titulo: `Ano Legislativo ${ano}`,
    subtitulo: `${ordinal}ª Sessão Legislativa da 57ª Legislatura`,
    recorte: `do ano legislativo de ${ano}`,
    periodo: { inicio, fim: `${ano}-12-31` },
    regua: REGUA_ANO,
    prefixo: `A${String(ano).slice(2)}`,
  }
}

/** Todas as edições que podem ser criadas hoje, para o seletor do admin. */
export function edicoesPossiveis(): DefinicaoEdicao[] {
  return [
    definirEdicao('legislatura', 57),
    ...ANOS_LEG57.map((ano) => definirEdicao('ano', 57, ano)),
  ]
}

export function definicaoPorId(id: string): DefinicaoEdicao {
  const def = edicoesPossiveis().find((d) => d.id === id)
  if (!def) throw new EdicaoInvalida(`edição desconhecida: ${id}`)
  return def
}

export class EdicaoInvalida extends Error {
  constructor(msg: string) {
    super(msg)
    this.name = 'EdicaoInvalida'
  }
}

// ─── Persistência ───────────────────────────────────────────────────────────

interface EdicaoRow {
  id: string
  tipo: TipoEdicao
  legislatura: number
  ano: number | null
  titulo: string
  periodo_inicio: string
  periodo_fim: string
  status: StatusEdicao
  fonte: string | null
  apuracao_json: string | null
  apuracao_sha256: string | null
  scores_json?: string | null
  erro: string | null
  criado_em: string
  apurado_em: string | null
  aprovado_em: string | null
  divulgado_em: string | null
}

export interface Edicao {
  id: string
  tipo: TipoEdicao
  legislatura: number
  ano?: number
  titulo: string
  subtitulo: string
  periodo: Periodo
  /** O período ainda não acabou: o resultado mede um ano (ou legislatura) incompleto. */
  periodoEmCurso: boolean
  status: StatusEdicao
  fonte: string | null
  apuracaoSha256: string | null
  erro: string | null
  criadoEm: string
  apuradoEm: string | null
  aprovadoEm: string | null
  divulgadoEm: string | null
  regua: Regua
}

function hoje(): string {
  return new Date().toISOString().slice(0, 10)
}

function toEdicao(r: EdicaoRow): Edicao {
  const def = definicaoPorId(r.id)
  return {
    id: r.id,
    tipo: r.tipo,
    legislatura: r.legislatura,
    ano: r.ano ?? undefined,
    titulo: r.titulo,
    subtitulo: def.subtitulo,
    periodo: { inicio: r.periodo_inicio, fim: r.periodo_fim },
    periodoEmCurso: hoje() <= r.periodo_fim,
    // Um 'apurando' sem job vivo é resto de reinício do serviço.
    status: r.status === 'apurando' && !emAndamento.has(r.id) ? 'rascunho' : r.status,
    fonte: r.fonte,
    apuracaoSha256: r.apuracao_sha256,
    erro:
      r.status === 'apurando' && !emAndamento.has(r.id)
        ? 'a apuração foi interrompida (serviço reiniciado); apure de novo'
        : r.erro,
    criadoEm: r.criado_em,
    apuradoEm: r.apurado_em,
    aprovadoEm: r.aprovado_em,
    divulgadoEm: r.divulgado_em,
    regua: def.regua,
  }
}

const COLUNAS_EDICAO = `id, tipo, legislatura, ano, titulo, periodo_inicio, periodo_fim,
  status, fonte, apuracao_json, apuracao_sha256, erro, criado_em, apurado_em,
  aprovado_em, divulgado_em`

async function lerEdicao(env: Env, id: string): Promise<EdicaoRow | null> {
  return env.SENADO_DB.prepare(
    `SELECT ${COLUNAS_EDICAO} FROM premiacao_edicoes WHERE id = ?`,
  )
    .bind(id)
    .first<EdicaoRow>()
}

export async function listarEdicoes(env: Env): Promise<Edicao[]> {
  const { results } = await env.SENADO_DB.prepare(
    `SELECT ${COLUNAS_EDICAO} FROM premiacao_edicoes ORDER BY periodo_fim DESC, tipo`,
  ).all<EdicaoRow>()
  return (results ?? []).map(toEdicao)
}

export async function obterEdicao(
  env: Env,
  id: string,
): Promise<{ edicao: Edicao; apuracao: Premiacao | null } | null> {
  const row = await lerEdicao(env, id)
  if (!row) return null
  return {
    edicao: toEdicao(row),
    apuracao: row.apuracao_json ? (JSON.parse(row.apuracao_json) as Premiacao) : null,
  }
}

/** Scores usados na apuração — o ranking completo do período, para auditoria. */
export async function obterScores(env: Env, id: string): Promise<IdsScore[] | null> {
  const row = await env.SENADO_DB.prepare(
    `SELECT scores_json FROM premiacao_edicoes WHERE id = ?`,
  )
    .bind(id)
    .first<{ scores_json: string | null }>()
  return row?.scores_json ? (JSON.parse(row.scores_json) as IdsScore[]) : null
}

export async function criarEdicao(env: Env, id: string): Promise<Edicao> {
  const def = definicaoPorId(id)
  await env.SENADO_DB.prepare(
    `INSERT OR IGNORE INTO premiacao_edicoes
       (id, tipo, legislatura, ano, titulo, periodo_inicio, periodo_fim, status)
     VALUES (?, ?, ?, ?, ?, ?, ?, 'rascunho')`,
  )
    .bind(def.id, def.tipo, def.legislatura, def.ano ?? null, def.titulo,
      def.periodo.inicio, def.periodo.fim)
    .run()
  return toEdicao((await lerEdicao(env, id))!)
}

// ─── Apuração ───────────────────────────────────────────────────────────────

/** Jobs vivos neste processo. A apuração anual leva minutos (cache frio). */
const emAndamento = new Set<string>()

/**
 * Dispara a apuração em segundo plano e volta na hora.
 *
 * O túnel corta requisições longas, e a edição anual recalcula o IDS do zero.
 * O admin acompanha pelo status da edição (`apurando` → `apurada`).
 */
export async function iniciarApuracao(
  env: Env,
  id: string,
  opts: { snapshot?: string } = {},
): Promise<Edicao> {
  const row = await lerEdicao(env, id)
  if (!row) throw new EdicaoInvalida(`edição ${id} não existe`)
  if (row.status === 'aprovada' || row.status === 'divulgada') {
    throw new EdicaoInvalida('edição já aprovada; cancele a aprovação para reapurar')
  }
  if (emAndamento.has(id)) throw new EdicaoInvalida('apuração já em andamento')

  const statusAnterior: StatusEdicao = row.apuracao_json ? 'apurada' : 'rascunho'
  emAndamento.add(id)
  await env.SENADO_DB.prepare(
    `UPDATE premiacao_edicoes SET status = 'apurando', erro = NULL WHERE id = ?`,
  )
    .bind(id)
    .run()

  void (async () => {
    try {
      const { apuracao, scores, fonte } = await apurar(env, definicaoPorId(id), opts)
      const json = JSON.stringify(apuracao)
      await env.SENADO_DB.prepare(
        `UPDATE premiacao_edicoes
            SET status = 'apurada', fonte = ?, apuracao_json = ?, apuracao_sha256 = ?,
                scores_json = ?, erro = NULL, apurado_em = ?
          WHERE id = ?`,
      )
        .bind(fonte, json, await sha256Hex(json), JSON.stringify(scores),
          new Date().toISOString(), id)
        .run()
      console.log(`[premiacao] ${id} apurada (${apuracao.categorias.length} categorias)`)
    } catch (err) {
      console.error(`[premiacao] apuração de ${id} falhou:`, err)
      // Reapuração que falha não apaga a apuração anterior.
      await env.SENADO_DB.prepare(
        `UPDATE premiacao_edicoes SET status = ?, erro = ? WHERE id = ?`,
      )
        .bind(statusAnterior, String((err as Error).message ?? err).slice(0, 500), id)
        .run()
    } finally {
      emAndamento.delete(id)
    }
  })()

  return toEdicao((await lerEdicao(env, id))!)
}

async function apurar(
  env: Env,
  def: DefinicaoEdicao,
  opts: { snapshot?: string },
): Promise<{ apuracao: Premiacao; scores: IdsScore[]; fonte: string }> {
  if (def.tipo === 'legislatura') {
    // Sem snapshot pedido: o mais recente que passe no piso de sanidade.
    let alvo = opts.snapshot
    if (!alvo) {
      const aptos = (await listSnapshots(env)).filter((s) => s.total >= def.regua.minLinhas)
      if (aptos.length === 0) throw new Error('nenhum snapshot do ranking passa no piso de linhas')
      alvo = aptos[0].computedAt
    }
    const snap = await getRankingSnapshot(env, alvo)
    if (!snap) throw new Error(`snapshot ${alvo} não existe`)
    const apuracao = apurarScores(snap.scores, {
      edicao: def.id,
      legislatura: def.legislatura,
      recorte: def.recorte,
      snapshot: snap.computedAt,
      congelado: true,
      regua: def.regua,
    })
    return { apuracao, scores: snap.scores, fonte: `snapshot:${snap.computedAt}` }
  }

  const calculadoEm = new Date().toISOString()
  const scores = await computeRanking(env, {
    ...def.periodo,
    mesesMinReferencia: def.regua.mesesMinReferencia,
  })
  const apuracao = apurarScores(scores, {
    edicao: def.id,
    legislatura: def.legislatura,
    ano: def.ano,
    recorte: def.recorte,
    snapshot: calculadoEm,
    congelado: true,
    regua: def.regua,
  })
  return { apuracao, scores, fonte: `calculo:${calculadoEm}` }
}

// ─── Aprovação e certificados ───────────────────────────────────────────────

/** Conteúdo impresso no certificado. Imutável depois de emitido. */
export interface DadosCertificado {
  codigo: string
  edicao: {
    id: string
    tipo: TipoEdicao
    titulo: string
    subtitulo: string
    periodo: Periodo
    apuracaoSha256: string
    metodologiaVersao?: string
  }
  categoria: {
    id: string
    eixo: EixoId
    titulo: string
    descricao: string
    /** "Brasil", "Região Nordeste", "Bahia", "Tema: CEAP". */
    ambito: string
    concorrentes: number
  }
  senador: {
    codigo: string
    nome: string
    /** Forma oficial do Senado: "Senador", "Senadora". */
    tratamento: string
    partido: string
    uf: string
  }
  idsTotal: number
  valor: number
  indicador: IndicadorBruto
  houveDesempate: boolean
  /** YYYY-MM-DD, data da aprovação. */
  emitidoEm: string
}

export interface Certificado {
  codigo: string
  edicaoId: string
  categoriaId: string
  senadorCod: string
  dados: DadosCertificado
  pdfSha256: string | null
  pdfGerado: boolean
  emitidoEm: string
  email: string | null
  emailOficial: string | null
  enviadoEm: string | null
  envios: number
  envioErro: string | null
  revogadoEm: string | null
  motivoRevogacao: string | null
}

interface CertRow {
  codigo: string
  edicao_id: string
  categoria_id: string
  senador_cod: string
  dados_json: string
  assinatura: string
  pdf_sha256: string | null
  tem_pdf: number
  emitido_em: string
  email: string | null
  email_oficial: string | null
  enviado_em: string | null
  envios: number
  envio_erro: string | null
  revogado_em: string | null
  motivo_revogacao: string | null
}

const COLUNAS_CERT = `codigo, edicao_id, categoria_id, senador_cod, dados_json, assinatura,
  pdf_sha256, (pdf_base64 IS NOT NULL) AS tem_pdf, emitido_em, email, email_oficial,
  enviado_em, envios, envio_erro, revogado_em, motivo_revogacao`

function toCert(r: CertRow): Certificado {
  return {
    codigo: r.codigo,
    edicaoId: r.edicao_id,
    categoriaId: r.categoria_id,
    senadorCod: r.senador_cod,
    dados: JSON.parse(r.dados_json) as DadosCertificado,
    pdfSha256: r.pdf_sha256,
    pdfGerado: Boolean(r.tem_pdf),
    emitidoEm: r.emitido_em,
    email: r.email,
    emailOficial: r.email_oficial,
    enviadoEm: r.enviado_em,
    envios: r.envios ?? 0,
    envioErro: r.envio_erro,
    revogadoEm: r.revogado_em,
    motivoRevogacao: r.motivo_revogacao,
  }
}

/** Crockford base32 sem I, L, O, U: legível em voz alta e no papel. */
const ALFABETO = '0123456789ABCDEFGHJKMNPQRSTVWXYZ'

function blocoAleatorio(n: number): string {
  const bytes = new Uint8Array(n)
  crypto.getRandomValues(bytes)
  return Array.from(bytes, (b) => ALFABETO[b & 31]).join('')
}

/** Ex.: OS-L57-7K3M-Q9XA. 40 bits aleatórios: inviável de adivinhar. */
function novoCodigo(prefixo: string): string {
  return `OS-${prefixo}-${blocoAleatorio(4)}-${blocoAleatorio(4)}`
}

/** Normaliza o que a pessoa digitou: caixa, espaços, O→0, I/L→1. */
export function normalizarCodigo(bruto: string): string {
  const partes = bruto.toUpperCase().replace(/\s+/g, '').split('-')
  if (partes.length !== 4) return partes.join('-')
  // Só os blocos aleatórios: o prefixo (L57, A25) tem letras de verdade.
  for (const i of [2, 3]) {
    partes[i] = partes[i].replace(/O/g, '0').replace(/[IL]/g, '1')
  }
  return partes.join('-')
}

function ambito(cat: CategoriaApurada): string {
  if (cat.eixo === 'grande_premio') return 'Nacional'
  if (cat.eixo === 'regional') return `Região ${cat.titulo.replace('Destaque ', '')}`
  if (cat.eixo === 'estadual') return cat.uf ? NOMES_UF[cat.uf] ?? cat.uf : cat.titulo
  return 'Temático'
}

/**
 * Aprova a apuração: trava o resultado e emite um certificado por vencedor.
 *
 * O e-mail pré-preenchido é o institucional publicado pelo Senado; o admin
 * pode trocá-lo antes de enviar. O tratamento ("Senador"/"Senadora") também
 * vem do cadastro oficial, nunca do nome.
 */
export async function aprovarEdicao(env: Env, id: string): Promise<Edicao> {
  const atual = await obterEdicao(env, id)
  if (!atual) throw new EdicaoInvalida(`edição ${id} não existe`)
  if (atual.edicao.status !== 'apurada' || !atual.apuracao) {
    throw new EdicaoInvalida(`só uma edição apurada pode ser aprovada (status: ${atual.edicao.status})`)
  }
  const def = definicaoPorId(id)
  const apuracao = atual.apuracao
  const sha = atual.edicao.apuracaoSha256!

  // Cadastro oficial: e-mail e forma de tratamento. A lista do período não
  // importa aqui; quem venceu está na coorte da legislatura.
  const cadastro = new Map((await getSenadorList(env)).map((s) => [s.codigo, s]))

  const emitidoEm = hoje()
  const stmts = []
  for (const cat of apuracao.categorias) {
    const v = cat.vencedor
    const oficial = cadastro.get(v.senadorCod)
    const codigo = novoCodigo(def.prefixo)
    const dados: DadosCertificado = {
      codigo,
      edicao: {
        id,
        tipo: def.tipo,
        titulo: def.titulo,
        subtitulo: def.subtitulo,
        periodo: def.periodo,
        apuracaoSha256: sha,
        metodologiaVersao: apuracao.metodologiaVersao,
      },
      categoria: {
        id: cat.id,
        eixo: cat.eixo,
        titulo: cat.titulo,
        descricao: cat.descricao,
        ambito: ambito(cat),
        concorrentes: cat.elegiveis,
      },
      senador: {
        codigo: v.senadorCod,
        nome: v.nome,
        tratamento: oficial?.formaTratamento ?? 'Senador(a)',
        partido: v.partido,
        uf: v.uf,
      },
      idsTotal: v.idsTotal,
      valor: v.valor,
      indicador: v.indicador,
      houveDesempate: cat.houveDesempate,
      emitidoEm,
    }
    const json = JSON.stringify(dados)
    const email = oficial?.email || null
    stmts.push(
      env.SENADO_DB.prepare(
        `INSERT INTO premiacao_certificados
           (codigo, edicao_id, categoria_id, senador_cod, dados_json, assinatura,
            emitido_em, email, email_oficial)
         VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)`,
      ).bind(codigo, id, cat.id, v.senadorCod, json,
        await assinarCertificado(env.ADMIN_SECRET, codigo, json), emitidoEm, email, email),
    )
  }
  stmts.push(
    env.SENADO_DB.prepare(
      `UPDATE premiacao_edicoes SET status = 'aprovada', aprovado_em = ? WHERE id = ?`,
    ).bind(new Date().toISOString(), id),
  )
  for (const s of stmts) await s.run()
  return toEdicao((await lerEdicao(env, id))!)
}

/**
 * Desfaz a aprovação, apagando os certificados. Só enquanto nenhum saiu por
 * e-mail: certificado entregue não some, se revoga.
 */
export async function cancelarAprovacao(env: Env, id: string): Promise<Edicao> {
  const row = await lerEdicao(env, id)
  if (!row || row.status !== 'aprovada') {
    throw new EdicaoInvalida('só uma edição aprovada e não divulgada pode ter a aprovação cancelada')
  }
  const enviados = await env.SENADO_DB.prepare(
    `SELECT COUNT(*) AS n FROM premiacao_certificados WHERE edicao_id = ? AND enviado_em IS NOT NULL`,
  )
    .bind(id)
    .first<{ n: number }>()
  if ((enviados?.n ?? 0) > 0) {
    throw new EdicaoInvalida(
      `${enviados!.n} certificado(s) já enviados; revogue-os individualmente em vez de cancelar`,
    )
  }
  await env.SENADO_DB.prepare(`DELETE FROM premiacao_certificados WHERE edicao_id = ?`).bind(id).run()
  await env.SENADO_DB.prepare(
    `UPDATE premiacao_edicoes SET status = 'apurada', aprovado_em = NULL WHERE id = ?`,
  )
    .bind(id)
    .run()
  return toEdicao((await lerEdicao(env, id))!)
}

/** Divulga (ou recolhe) o resultado público de uma edição aprovada. */
export async function definirDivulgacao(env: Env, id: string, divulgar: boolean): Promise<Edicao> {
  const row = await lerEdicao(env, id)
  if (!row) throw new EdicaoInvalida(`edição ${id} não existe`)
  const esperado: StatusEdicao = divulgar ? 'aprovada' : 'divulgada'
  if (row.status !== esperado) {
    throw new EdicaoInvalida(
      divulgar ? 'só uma edição aprovada pode ser divulgada' : 'a edição não está divulgada',
    )
  }
  await env.SENADO_DB.prepare(
    `UPDATE premiacao_edicoes SET status = ?, divulgado_em = ? WHERE id = ?`,
  )
    .bind(divulgar ? 'divulgada' : 'aprovada', divulgar ? new Date().toISOString() : null, id)
    .run()
  return toEdicao((await lerEdicao(env, id))!)
}

export async function listarCertificados(env: Env, edicaoId: string): Promise<Certificado[]> {
  const { results } = await env.SENADO_DB.prepare(
    `SELECT ${COLUNAS_CERT} FROM premiacao_certificados WHERE edicao_id = ? ORDER BY rowid`,
  )
    .bind(edicaoId)
    .all<CertRow>()
  return (results ?? []).map(toCert)
}

export async function obterCertificado(env: Env, codigo: string): Promise<Certificado | null> {
  const row = await env.SENADO_DB.prepare(
    `SELECT ${COLUNAS_CERT} FROM premiacao_certificados WHERE codigo = ?`,
  )
    .bind(codigo)
    .first<CertRow>()
  return row ? toCert(row) : null
}

const EMAIL_VALIDO = /^[^\s@]+@[^\s@]+\.[^\s@]{2,}$/

/** Define o destinatário de todos os certificados do senador na edição. */
export async function definirEmail(
  env: Env,
  edicaoId: string,
  senadorCod: string,
  email: string,
): Promise<number> {
  const limpo = email.trim()
  if (limpo && (!EMAIL_VALIDO.test(limpo) || limpo.length > 200)) {
    throw new EdicaoInvalida(`e-mail inválido: ${limpo}`)
  }
  const r = await env.SENADO_DB.prepare(
    `UPDATE premiacao_certificados SET email = ? WHERE edicao_id = ? AND senador_cod = ?`,
  )
    .bind(limpo || null, edicaoId, senadorCod)
    .run()
  return r.meta?.changes ?? 0
}

export async function revogarCertificado(env: Env, codigo: string, motivo: string): Promise<void> {
  if (!motivo.trim()) throw new EdicaoInvalida('informe o motivo da revogação')
  const r = await env.SENADO_DB.prepare(
    `UPDATE premiacao_certificados SET revogado_em = ?, motivo_revogacao = ?
      WHERE codigo = ? AND revogado_em IS NULL`,
  )
    .bind(new Date().toISOString(), motivo.trim().slice(0, 300), codigo)
    .run()
  if (!r.meta?.changes) throw new EdicaoInvalida('certificado inexistente ou já revogado')
}

export async function registrarEnvio(
  env: Env,
  codigos: string[],
  resultado: { ok: boolean; erro?: string },
): Promise<void> {
  for (const codigo of codigos) {
    await env.SENADO_DB.prepare(
      resultado.ok
        ? `UPDATE premiacao_certificados
              SET enviado_em = ?, envios = envios + 1, envio_erro = NULL WHERE codigo = ?`
        : `UPDATE premiacao_certificados SET envio_erro = ? WHERE codigo = ?`,
    )
      .bind(resultado.ok ? new Date().toISOString() : (resultado.erro ?? 'falha'), codigo)
      .run()
  }
}

// ─── PDF guardado ───────────────────────────────────────────────────────────

/** PDF já emitido, se houver. O hash publicado na verificação é o deste arquivo. */
export async function lerPdf(env: Env, codigo: string): Promise<Uint8Array | null> {
  const row = await env.SENADO_DB.prepare(
    `SELECT pdf_base64 FROM premiacao_certificados WHERE codigo = ?`,
  )
    .bind(codigo)
    .first<{ pdf_base64: string | null }>()
  return row?.pdf_base64 ? new Uint8Array(Buffer.from(row.pdf_base64, 'base64')) : null
}

export async function guardarPdf(env: Env, codigo: string, pdf: Uint8Array): Promise<string> {
  const sha = await sha256Hex(pdf)
  await env.SENADO_DB.prepare(
    `UPDATE premiacao_certificados SET pdf_base64 = ?, pdf_sha256 = ?
      WHERE codigo = ? AND pdf_base64 IS NULL`,
  )
    .bind(Buffer.from(pdf).toString('base64'), sha, codigo)
    .run()
  return sha
}

// ─── Verificação pública ────────────────────────────────────────────────────

export interface Verificacao {
  codigo: string
  situacao: 'valido' | 'revogado' | 'adulterado'
  motivoRevogacao?: string
  revogadoEm?: string
  emitidoEm: string
  edicao: DadosCertificado['edicao'] & { divulgada: boolean }
  categoria: DadosCertificado['categoria']
  senador: Omit<DadosCertificado['senador'], 'codigo'>
  idsTotal: number
  indicador: IndicadorBruto
  pdfSha256: string | null
}

/**
 * O que qualquer pessoa vê ao conferir um código. Só os dados impressos no
 * próprio certificado: nada de e-mail, pódio ou outros vencedores.
 */
export async function verificarCertificado(env: Env, bruto: string): Promise<Verificacao | null> {
  const codigo = normalizarCodigo(bruto)
  const row = await env.SENADO_DB.prepare(
    `SELECT c.codigo, c.dados_json, c.assinatura, c.pdf_sha256, c.emitido_em,
            c.revogado_em, c.motivo_revogacao, e.status AS status_edicao
       FROM premiacao_certificados c
       JOIN premiacao_edicoes e ON e.id = c.edicao_id
      WHERE c.codigo = ?`,
  )
    .bind(codigo)
    .first<{
      codigo: string
      dados_json: string
      assinatura: string
      pdf_sha256: string | null
      emitido_em: string
      revogado_em: string | null
      motivo_revogacao: string | null
      status_edicao: StatusEdicao
    }>()
  if (!row) return null

  const integro =
    (await assinarCertificado(env.ADMIN_SECRET, row.codigo, row.dados_json)) === row.assinatura
  const d = JSON.parse(row.dados_json) as DadosCertificado
  return {
    codigo: row.codigo,
    situacao: !integro ? 'adulterado' : row.revogado_em ? 'revogado' : 'valido',
    motivoRevogacao: row.motivo_revogacao ?? undefined,
    revogadoEm: row.revogado_em ?? undefined,
    emitidoEm: row.emitido_em,
    edicao: { ...d.edicao, divulgada: row.status_edicao === 'divulgada' },
    categoria: d.categoria,
    senador: {
      nome: d.senador.nome,
      tratamento: d.senador.tratamento,
      partido: d.senador.partido,
      uf: d.senador.uf,
    },
    idsTotal: d.idsTotal,
    indicador: d.indicador,
    pdfSha256: row.pdf_sha256,
  }
}

/** Resultado público: só de edições divulgadas. */
export async function edicaoDivulgada(
  env: Env,
  id: string,
): Promise<{ edicao: Edicao; apuracao: Premiacao } | null> {
  const r = await obterEdicao(env, id)
  if (!r || r.edicao.status !== 'divulgada' || !r.apuracao) return null
  return { edicao: r.edicao, apuracao: r.apuracao }
}