from __future__ import annotations

from .db import MysqlCli, sql_quote


PROCESS_STATUS_LABELS = {
    "pending": "Pendente",
    "running": "Processando",
    "done": "Concluído",
    "error": "Erro",
}


def list_sync_processes(db: MysqlCli) -> list[dict[str, str]]:
    rows = db.query_rows(
        """
SELECT
  j.id,
  j.municipio_id,
  m.codigo_siafi,
  m.nome,
  j.ambiente,
  j.perfil,
  j.status,
  j.attempts,
  DATE_FORMAT(j.updated_at, '%d/%m/%Y %H:%i:%s'),
  COALESCE(DATE_FORMAT(j.finished_at, '%d/%m/%Y %H:%i:%s'), ''),
  COALESCE(j.last_error_message, '')
FROM nfse_sync_jobs j
JOIN nfse_municipios m ON m.id = j.municipio_id
ORDER BY j.updated_at DESC, j.id DESC;
"""
    )
    keys = (
        "id",
        "municipio_id",
        "codigo_siafi",
        "municipio_nome",
        "ambiente",
        "perfil",
        "status",
        "attempts",
        "updated_at",
        "finished_at",
        "last_error_message",
    )
    return [dict(zip(keys, row)) for row in rows]


def get_sync_process_detail(db: MysqlCli, job_id: int) -> dict[str, object]:
    rows = db.query_rows(
        f"""
SELECT
  j.id,
  j.municipio_id,
  m.codigo_siafi,
  COALESCE(m.codigo_ibge, ''),
  m.nome,
  m.uf,
  j.ambiente,
  j.perfil,
  j.status,
  j.attempts,
  DATE_FORMAT(j.available_at, '%d/%m/%Y %H:%i:%s'),
  COALESCE(DATE_FORMAT(j.locked_at, '%d/%m/%Y %H:%i:%s'), ''),
  COALESCE(j.locked_by, ''),
  COALESCE(DATE_FORMAT(j.finished_at, '%d/%m/%Y %H:%i:%s'), ''),
  DATE_FORMAT(j.created_at, '%d/%m/%Y %H:%i:%s'),
  DATE_FORMAT(j.updated_at, '%d/%m/%Y %H:%i:%s'),
  COALESCE(j.last_error_message, ''),
  COALESCE(s.status, ''),
  COALESCE(s.last_nsu, 0),
  COALESCE(s.max_nsu, 0),
  COALESCE(DATE_FORMAT(s.last_success_at, '%d/%m/%Y %H:%i:%s'), ''),
  COALESCE(DATE_FORMAT(s.last_error_at, '%d/%m/%Y %H:%i:%s'), ''),
  COALESCE(s.last_error_message, '')
FROM nfse_sync_jobs j
JOIN nfse_municipios m ON m.id = j.municipio_id
LEFT JOIN nfse_sync_state s
  ON s.municipio_id = j.municipio_id
 AND s.ambiente = j.ambiente
 AND s.perfil = j.perfil
WHERE j.id = {int(job_id)}
LIMIT 1;
"""
    )
    if not rows:
        raise ValueError("Processo de sincronização não encontrado.")
    keys = (
        "id",
        "municipio_id",
        "codigo_siafi",
        "codigo_ibge",
        "municipio_nome",
        "uf",
        "ambiente",
        "perfil",
        "status",
        "attempts",
        "available_at",
        "locked_at",
        "locked_by",
        "finished_at",
        "created_at",
        "updated_at",
        "job_error_message",
        "sync_status",
        "last_nsu",
        "max_nsu",
        "last_success_at",
        "last_error_at",
        "sync_error_message",
    )
    detail: dict[str, object] = dict(zip(keys, rows[0]))
    detail["status_label"] = PROCESS_STATUS_LABELS.get(str(detail["status"]), str(detail["status"]))

    log_rows = db.query_rows(
        f"""
SELECT
  nivel,
  evento,
  mensagem,
  COALESCE(contexto, ''),
  DATE_FORMAT(created_at, '%d/%m/%Y %H:%i:%s')
FROM nfse_sync_logs
WHERE municipio_id = {int(detail['municipio_id'])}
  AND ambiente = {sql_quote(str(detail['ambiente']))}
ORDER BY created_at DESC, id DESC
LIMIT 20;
"""
    )
    log_keys = ("nivel", "evento", "mensagem", "contexto", "created_at")
    detail["logs"] = [dict(zip(log_keys, row)) for row in log_rows]
    return detail