526 lines
21 KiB
Python
Executable File
526 lines
21 KiB
Python
Executable File
#!/usr/bin/env python3
|
|
"""Audit ClientFlow task requests and reply suggestions.
|
|
|
|
This script is intentionally read-only by default:
|
|
- it does not send Chatwoot messages;
|
|
- it does not complete tasks;
|
|
- it does not persist message_drafts unless --persist-drafts is used;
|
|
- it disables the optional OpenRouter reply LLM unless --use-llm is used.
|
|
|
|
Typical usage:
|
|
python scripts/audit_task_reply_suggestions.py --status pending --format markdown --out /tmp/task_reply_audit.md
|
|
python scripts/audit_task_reply_suggestions.py --status all --limit 500 --format csv --out /tmp/task_reply_audit.csv
|
|
python scripts/audit_task_reply_suggestions.py --status pending --use-llm --format json
|
|
"""
|
|
from __future__ import annotations
|
|
|
|
import argparse
|
|
import csv
|
|
import json
|
|
import os
|
|
import re
|
|
import sys
|
|
from datetime import datetime, timezone
|
|
from pathlib import Path
|
|
from typing import Any, Dict, Iterable, List, Optional
|
|
|
|
|
|
STATUS_CHOICES = ["all", "pending", "done", "failed", "skipped"]
|
|
FORMAT_CHOICES = ["markdown", "json", "csv"]
|
|
|
|
|
|
def bootstrap_project_root() -> None:
|
|
"""Ensure `app` imports work when this file is executed as scripts/*.py.
|
|
|
|
When Python runs a script by path, sys.path[0] points to the
|
|
script directory (`scripts/`) rather than the project root. The
|
|
backend package lives in `<project_root>/app`, so add the parent
|
|
directory explicitly.
|
|
"""
|
|
project_root = Path(__file__).resolve().parents[1]
|
|
root_str = str(project_root)
|
|
if root_str not in sys.path:
|
|
sys.path.insert(0, root_str)
|
|
|
|
|
|
bootstrap_project_root()
|
|
|
|
|
|
def parse_args(argv: Optional[List[str]] = None) -> argparse.Namespace:
|
|
parser = argparse.ArgumentParser(
|
|
description="Audita tarefas ClientFlow e gera a resposta sugerida pelo Reply Assistant.",
|
|
)
|
|
parser.add_argument(
|
|
"--status",
|
|
default="all",
|
|
choices=STATUS_CHOICES,
|
|
help="Estado das tarefas a consultar. Por defeito consulta todos os estados.",
|
|
)
|
|
parser.add_argument("--route", default="", help="Filtrar por fila/route, ex.: comercial, financeiro, operacoes.")
|
|
parser.add_argument("--q", default="", help="Filtro textual simples sobre tarefa/mensagem/cliente.")
|
|
parser.add_argument("--limit", type=int, default=0, help="Número máximo de tarefas. 0 = sem limite.")
|
|
parser.add_argument("--format", default="markdown", choices=FORMAT_CHOICES, help="Formato de saída.")
|
|
parser.add_argument("--out", default="", help="Ficheiro de saída. Se omitido, imprime no terminal.")
|
|
parser.add_argument(
|
|
"--use-llm",
|
|
action="store_true",
|
|
help="Ativa CLIENTFLOW_REPLY_LLM_ENABLED para testar OpenRouter. Por defeito fica desligado para evitar custos.",
|
|
)
|
|
parser.add_argument(
|
|
"--persist-drafts",
|
|
action="store_true",
|
|
help="Guarda message_drafts na base de dados. Por defeito é read-only e não persiste rascunhos.",
|
|
)
|
|
parser.add_argument(
|
|
"--include-body",
|
|
action="store_true",
|
|
help="Inclui corpo completo do pedido e resposta. Sem esta opção, o CSV/JSON mantém tudo; Markdown truncará textos longos.",
|
|
)
|
|
parser.add_argument(
|
|
"--max-text-chars",
|
|
type=int,
|
|
default=900,
|
|
help="Limite de caracteres por texto no Markdown quando --include-body não é usado.",
|
|
)
|
|
return parser.parse_args(argv)
|
|
|
|
|
|
def configure_environment(args: argparse.Namespace) -> None:
|
|
# app.config loads `.env` via pydantic-settings. Do not inject a dummy
|
|
# OPENROUTER_API_KEY when --use-llm is active, because environment
|
|
# variables take precedence over `.env` and the dummy value would shadow
|
|
# the real OpenRouter key.
|
|
if args.use_llm:
|
|
if os.environ.get("OPENROUTER_API_KEY") == "not-used-by-task-reply-audit":
|
|
os.environ.pop("OPENROUTER_API_KEY", None)
|
|
os.environ["CLIENTFLOW_REPLY_LLM_ENABLED"] = "true"
|
|
else:
|
|
# OPENROUTER_API_KEY is required by app.config even when LLM is disabled.
|
|
# In read-only/no-LLM audit mode a harmless placeholder avoids forcing
|
|
# operators to export a real key.
|
|
os.environ.setdefault("OPENROUTER_API_KEY", "not-used-by-task-reply-audit")
|
|
os.environ["CLIENTFLOW_REPLY_LLM_ENABLED"] = "false"
|
|
|
|
if not os.environ.get("DATABASE_URL"):
|
|
env_path = Path(".env")
|
|
if not env_path.exists():
|
|
raise SystemExit(
|
|
"DATABASE_URL não está definido e não existe .env no diretório atual. "
|
|
"Execute dentro do backend ClientFlow ou exporte DATABASE_URL."
|
|
)
|
|
|
|
|
|
def normalize_space(value: Any) -> str:
|
|
return re.sub(r"\s+", " ", str(value or "")).strip()
|
|
|
|
|
|
def compact_text(value: Any, *, max_chars: int) -> str:
|
|
text = str(value or "").strip()
|
|
if len(text) <= max_chars:
|
|
return text
|
|
return text[: max_chars - 1].rstrip() + "…"
|
|
|
|
|
|
def task_customer_message(task: Dict[str, Any]) -> str:
|
|
metadata = task.get("metadata") if isinstance(task.get("metadata"), dict) else {}
|
|
message_metadata = task.get("message_metadata") if isinstance(task.get("message_metadata"), dict) else {}
|
|
candidates = [
|
|
task.get("request_text"),
|
|
task.get("clean_body"),
|
|
task.get("raw_body"),
|
|
metadata.get("request_text"),
|
|
metadata.get("customer_message"),
|
|
message_metadata.get("clean_body"),
|
|
message_metadata.get("raw_body"),
|
|
task.get("message_subject"),
|
|
task.get("note"),
|
|
]
|
|
for candidate in candidates:
|
|
text = str(candidate or "").strip()
|
|
if text:
|
|
return text
|
|
return ""
|
|
|
|
|
|
def query_task_ids(*, status: str, route: str, q: str, limit: int) -> List[str]:
|
|
from sqlalchemy import text
|
|
|
|
from app.db import engine
|
|
|
|
where: List[str] = []
|
|
params: Dict[str, Any] = {}
|
|
if status and status != "all":
|
|
where.append("t.status = :status")
|
|
params["status"] = status
|
|
if route:
|
|
where.append("t.route = :route")
|
|
params["route"] = route
|
|
if q:
|
|
params["q"] = f"%{q.strip().lower()}%"
|
|
where.append(
|
|
"""
|
|
(
|
|
lower(coalesce(t.conversation_id, '')) LIKE :q
|
|
OR lower(coalesce(t.contact_id, '')) LIKE :q
|
|
OR lower(coalesce(t.customer_id, '')) LIKE :q
|
|
OR lower(coalesce(t.action_code, '')) LIKE :q
|
|
OR lower(coalesce(t.route, '')) LIKE :q
|
|
OR lower(coalesce(t.action, '')) LIKE :q
|
|
OR lower(coalesce(t.note, '')) LIKE :q
|
|
OR lower(coalesce(m.clean_body, '')) LIKE :q
|
|
OR lower(coalesce(m.raw_body, '')) LIKE :q
|
|
OR lower(coalesce(re.payload->'sender'->>'name', '')) LIKE :q
|
|
OR lower(coalesce(re.payload->'sender'->>'email', '')) LIKE :q
|
|
OR lower(coalesce(re.payload->>'content', '')) LIKE :q
|
|
)
|
|
"""
|
|
)
|
|
|
|
where_sql = "WHERE " + " AND ".join(where) if where else ""
|
|
limit_sql = "LIMIT :limit" if limit and limit > 0 else ""
|
|
if limit and limit > 0:
|
|
params["limit"] = limit
|
|
|
|
sql = text(
|
|
f"""
|
|
SELECT t.id::text AS id
|
|
FROM tasks t
|
|
LEFT JOIN messages m ON m.id = t.message_id
|
|
LEFT JOIN raw_events re ON re.id = t.raw_event_id
|
|
{where_sql}
|
|
ORDER BY
|
|
CASE t.status
|
|
WHEN 'pending' THEN 1
|
|
WHEN 'failed' THEN 2
|
|
WHEN 'skipped' THEN 3
|
|
WHEN 'done' THEN 4
|
|
ELSE 5
|
|
END,
|
|
CASE COALESCE(t.priority, 'normal')
|
|
WHEN 'alta' THEN 1
|
|
WHEN 'normal' THEN 2
|
|
WHEN 'baixa' THEN 3
|
|
ELSE 4
|
|
END,
|
|
t.due_at NULLS LAST,
|
|
t.created_at DESC
|
|
{limit_sql}
|
|
"""
|
|
)
|
|
|
|
with engine.begin() as conn:
|
|
rows = conn.execute(sql, params).mappings().all()
|
|
return [str(row["id"]) for row in rows]
|
|
|
|
|
|
def latest_preparation_for_task(task_id: str) -> Dict[str, Any]:
|
|
from sqlalchemy import text
|
|
|
|
from app.db import engine
|
|
|
|
try:
|
|
with engine.begin() as conn:
|
|
row = conn.execute(
|
|
text(
|
|
"""
|
|
SELECT prep_type, status, suggested_reply, confidence, created_at
|
|
FROM task_preparations
|
|
WHERE task_id = CAST(:task_id AS UUID)
|
|
ORDER BY created_at DESC
|
|
LIMIT 1
|
|
"""
|
|
),
|
|
{"task_id": task_id},
|
|
).mappings().first()
|
|
return dict(row) if row else {}
|
|
except Exception:
|
|
return {}
|
|
|
|
|
|
def audit_one_task(task_id: str, *, persist_drafts: bool) -> Dict[str, Any]:
|
|
from app.reply_assistant_service import ReplyAssistantError, generate_reply_draft
|
|
|
|
try:
|
|
draft = generate_reply_draft(task_id, persist=persist_drafts)
|
|
task = dict(draft.get("task") or {})
|
|
template = dict(draft.get("template") or {})
|
|
knowledge = dict(draft.get("business_knowledge") or {})
|
|
intent_gate = dict(draft.get("intent_gate") or {})
|
|
topics = knowledge.get("topics") or []
|
|
preparation = latest_preparation_for_task(task_id)
|
|
selected_docs = [str(doc.get("label") or doc.get("document_number") or doc.get("id")) for doc in draft.get("selected_documents") or []]
|
|
|
|
return {
|
|
"ok": True,
|
|
"task_id": task_id,
|
|
"status": task.get("status"),
|
|
"route": task.get("route"),
|
|
"action_code": task.get("action_code"),
|
|
"action": task.get("action"),
|
|
"priority": task.get("priority"),
|
|
"created_at": str(task.get("created_at") or ""),
|
|
"conversation_id": task.get("conversation_id"),
|
|
"customer_name": task.get("linked_customer_name") or task.get("customer_name"),
|
|
"customer_email": task.get("linked_customer_email") or task.get("customer_email"),
|
|
"subject": task.get("message_subject") or "",
|
|
"request_text": task_customer_message(task),
|
|
"previous_suggested_reply": preparation.get("suggested_reply") or "",
|
|
"intent_category": intent_gate.get("category"),
|
|
"intent_label": intent_gate.get("label"),
|
|
"intent_confidence": intent_gate.get("confidence"),
|
|
"intent_reasons": intent_gate.get("reasons") or [],
|
|
"intent_matched_terms": intent_gate.get("matched_terms") or [],
|
|
"commercial_reply_allowed": intent_gate.get("commercial_reply_allowed"),
|
|
"template_code": template.get("code"),
|
|
"template_name": template.get("name"),
|
|
"reply_type": template.get("reply_type") or knowledge.get("reply_type") or intent_gate.get("reply_type"),
|
|
"requires_attachment": bool(template.get("expected_document_kinds")),
|
|
"selected_documents": selected_docs,
|
|
"knowledge_topics": [topic.get("id") for topic in topics],
|
|
"knowledge_titles": [topic.get("title") for topic in topics],
|
|
"llm": draft.get("llm") or {},
|
|
"llm_intent": (draft.get("llm") or {}).get("intent"),
|
|
"llm_reply_type": (draft.get("llm") or {}).get("reply_type"),
|
|
"llm_confidence": (draft.get("llm") or {}).get("confidence"),
|
|
"llm_customer_need": (draft.get("llm") or {}).get("customer_need"),
|
|
"suggested_reply": draft.get("message_body") or "",
|
|
"blockers": draft.get("blockers") or [],
|
|
"warnings": draft.get("warnings") or [],
|
|
"draft_id": draft.get("draft_id") or "",
|
|
}
|
|
except ReplyAssistantError as exc:
|
|
return {"ok": False, "task_id": task_id, "error": str(exc)}
|
|
except Exception as exc:
|
|
return {"ok": False, "task_id": task_id, "error": f"{type(exc).__name__}: {exc}"}
|
|
|
|
|
|
def audit_tasks(args: argparse.Namespace) -> Dict[str, Any]:
|
|
task_ids = query_task_ids(status=args.status, route=args.route, q=args.q, limit=args.limit)
|
|
results = [audit_one_task(task_id, persist_drafts=args.persist_drafts) for task_id in task_ids]
|
|
ok_count = sum(1 for row in results if row.get("ok"))
|
|
blocked_count = sum(1 for row in results if row.get("blockers"))
|
|
warning_count = sum(1 for row in results if row.get("warnings"))
|
|
intent_counts: Dict[str, int] = {}
|
|
for row in results:
|
|
key = str(row.get("intent_category") or "ERROR")
|
|
intent_counts[key] = intent_counts.get(key, 0) + 1
|
|
return {
|
|
"generated_at": datetime.now(timezone.utc).isoformat(),
|
|
"filters": {"status": args.status, "route": args.route, "q": args.q, "limit": args.limit},
|
|
"options": {"use_llm": args.use_llm, "persist_drafts": args.persist_drafts},
|
|
"summary": {
|
|
"tasks_found": len(task_ids),
|
|
"tasks_ok": ok_count,
|
|
"tasks_failed": len(results) - ok_count,
|
|
"tasks_with_blockers": blocked_count,
|
|
"tasks_with_warnings": warning_count,
|
|
"intent_counts": intent_counts,
|
|
},
|
|
"results": results,
|
|
}
|
|
|
|
|
|
def render_markdown(report: Dict[str, Any], *, include_body: bool, max_text_chars: int) -> str:
|
|
summary = report["summary"]
|
|
filters = report["filters"]
|
|
lines = [
|
|
"# Auditoria de respostas sugeridas por tarefa",
|
|
"",
|
|
f"Gerado em: `{report['generated_at']}`",
|
|
"",
|
|
"## Filtros",
|
|
"",
|
|
f"- Estado: `{filters.get('status')}`",
|
|
f"- Fila/route: `{filters.get('route') or 'todas'}`",
|
|
f"- Pesquisa: `{filters.get('q') or '—'}`",
|
|
f"- Limite: `{filters.get('limit') or 'sem limite'}`",
|
|
f"- LLM: `{'ativo' if report['options'].get('use_llm') else 'inativo'}`",
|
|
f"- Persistir rascunhos: `{'sim' if report['options'].get('persist_drafts') else 'não'}`",
|
|
"",
|
|
"## Resumo",
|
|
"",
|
|
f"- Tarefas encontradas: **{summary['tasks_found']}**",
|
|
f"- Tarefas auditadas com sucesso: **{summary['tasks_ok']}**",
|
|
f"- Falhas: **{summary['tasks_failed']}**",
|
|
f"- Com bloqueios: **{summary['tasks_with_blockers']}**",
|
|
f"- Com avisos: **{summary['tasks_with_warnings']}**",
|
|
"",
|
|
"## Triagem por intenção",
|
|
"",
|
|
*[f"- `{key}`: **{value}**" for key, value in sorted((summary.get("intent_counts") or {}).items())],
|
|
"",
|
|
"## Tarefas",
|
|
"",
|
|
]
|
|
for idx, row in enumerate(report["results"], 1):
|
|
lines.append(f"### {idx}. {row.get('action') or row.get('task_id')}")
|
|
lines.append("")
|
|
if not row.get("ok"):
|
|
lines.append(f"- Task ID: `{row.get('task_id')}`")
|
|
lines.append(f"- Erro: `{row.get('error')}`")
|
|
lines.append("")
|
|
continue
|
|
request_text = row.get("request_text") or ""
|
|
suggested = row.get("suggested_reply") or ""
|
|
if not include_body:
|
|
request_text = compact_text(request_text, max_chars=max_text_chars)
|
|
suggested = compact_text(suggested, max_chars=max_text_chars)
|
|
topics = ", ".join(row.get("knowledge_titles") or row.get("knowledge_topics") or []) or "—"
|
|
docs = ", ".join(row.get("selected_documents") or []) or "—"
|
|
blockers = row.get("blockers") or []
|
|
warnings = row.get("warnings") or []
|
|
|
|
lines.extend(
|
|
[
|
|
f"- Task ID: `{row.get('task_id')}`",
|
|
f"- Estado/fila: `{row.get('status')}` / `{row.get('route')}`",
|
|
f"- Cliente: **{row.get('customer_name') or '—'}** `{row.get('customer_email') or ''}`",
|
|
f"- Conversa: `{row.get('conversation_id') or '—'}`",
|
|
f"- Action code: `{row.get('action_code')}`",
|
|
f"- Intent gate: `{row.get('intent_category') or '—'}` — {row.get('intent_label') or ''}",
|
|
f"- Template: `{row.get('template_code')}` — {row.get('template_name') or ''}",
|
|
f"- Tipo de resposta: `{row.get('reply_type') or '—'}`",
|
|
f"- Conhecimento usado: {topics}",
|
|
f"- Anexos selecionados: {docs}",
|
|
f"- Motivo da triagem: {'; '.join(row.get('intent_reasons') or []) or '—'}",
|
|
f"- LLM: `{(row.get('llm') or {}).get('status') or '—'}` · intent `{row.get('llm_intent') or '—'}` · confiança `{row.get('llm_confidence') if row.get('llm_confidence') is not None else '—'}`",
|
|
f"- Necessidade detetada pelo LLM: {row.get('llm_customer_need') or '—'}",
|
|
"",
|
|
"**Pedido do cliente**",
|
|
"",
|
|
"```text",
|
|
request_text or "—",
|
|
"```",
|
|
"",
|
|
"**Resposta sugerida**",
|
|
"",
|
|
"```text",
|
|
suggested or "—",
|
|
"```",
|
|
]
|
|
)
|
|
if row.get("previous_suggested_reply"):
|
|
previous = row.get("previous_suggested_reply")
|
|
if not include_body:
|
|
previous = compact_text(previous, max_chars=max_text_chars)
|
|
lines.extend(["", "**Resposta sugerida anterior/preparação**", "", "```text", previous, "```"])
|
|
if blockers:
|
|
lines.append("")
|
|
lines.append("**Bloqueios**")
|
|
lines.extend([f"- {item}" for item in blockers])
|
|
if warnings:
|
|
lines.append("")
|
|
lines.append("**Avisos**")
|
|
lines.extend([f"- {item}" for item in warnings])
|
|
lines.append("")
|
|
return "\n".join(lines).rstrip() + "\n"
|
|
|
|
|
|
def render_json(report: Dict[str, Any]) -> str:
|
|
return json.dumps(report, ensure_ascii=False, indent=2, default=str) + "\n"
|
|
|
|
|
|
def render_csv(report: Dict[str, Any]) -> str:
|
|
import io
|
|
|
|
output = io.StringIO()
|
|
fieldnames = [
|
|
"task_id",
|
|
"status",
|
|
"route",
|
|
"customer_name",
|
|
"customer_email",
|
|
"conversation_id",
|
|
"action_code",
|
|
"action",
|
|
"intent_category",
|
|
"intent_label",
|
|
"intent_reasons",
|
|
"commercial_reply_allowed",
|
|
"template_code",
|
|
"template_name",
|
|
"reply_type",
|
|
"llm_intent",
|
|
"llm_reply_type",
|
|
"llm_confidence",
|
|
"llm_customer_need",
|
|
"knowledge_topics",
|
|
"selected_documents",
|
|
"request_text",
|
|
"suggested_reply",
|
|
"previous_suggested_reply",
|
|
"blockers",
|
|
"warnings",
|
|
"error",
|
|
]
|
|
writer = csv.DictWriter(output, fieldnames=fieldnames)
|
|
writer.writeheader()
|
|
for row in report["results"]:
|
|
writer.writerow(
|
|
{
|
|
"task_id": row.get("task_id"),
|
|
"status": row.get("status"),
|
|
"route": row.get("route"),
|
|
"customer_name": row.get("customer_name"),
|
|
"customer_email": row.get("customer_email"),
|
|
"conversation_id": row.get("conversation_id"),
|
|
"action_code": row.get("action_code"),
|
|
"action": row.get("action"),
|
|
"intent_category": row.get("intent_category"),
|
|
"intent_label": row.get("intent_label"),
|
|
"intent_reasons": "; ".join(row.get("intent_reasons") or []),
|
|
"commercial_reply_allowed": row.get("commercial_reply_allowed"),
|
|
"template_code": row.get("template_code"),
|
|
"template_name": row.get("template_name"),
|
|
"reply_type": row.get("reply_type"),
|
|
"llm_intent": row.get("llm_intent"),
|
|
"llm_reply_type": row.get("llm_reply_type"),
|
|
"llm_confidence": row.get("llm_confidence"),
|
|
"llm_customer_need": row.get("llm_customer_need"),
|
|
"knowledge_topics": "; ".join(row.get("knowledge_topics") or []),
|
|
"selected_documents": "; ".join(row.get("selected_documents") or []),
|
|
"request_text": row.get("request_text"),
|
|
"suggested_reply": row.get("suggested_reply"),
|
|
"previous_suggested_reply": row.get("previous_suggested_reply"),
|
|
"blockers": "; ".join(row.get("blockers") or []),
|
|
"warnings": "; ".join(row.get("warnings") or []),
|
|
"error": row.get("error"),
|
|
}
|
|
)
|
|
return output.getvalue()
|
|
|
|
|
|
def write_output(content: str, out_path: str) -> None:
|
|
if not out_path:
|
|
print(content, end="")
|
|
return
|
|
path = Path(out_path)
|
|
path.parent.mkdir(parents=True, exist_ok=True)
|
|
path.write_text(content, encoding="utf-8")
|
|
print(f"Relatório escrito em: {path}")
|
|
|
|
|
|
def main(argv: Optional[List[str]] = None) -> int:
|
|
args = parse_args(argv)
|
|
configure_environment(args)
|
|
|
|
try:
|
|
report = audit_tasks(args)
|
|
except Exception as exc:
|
|
print(f"Erro ao auditar tarefas: {type(exc).__name__}: {exc}", file=sys.stderr)
|
|
return 2
|
|
|
|
if args.format == "json":
|
|
content = render_json(report)
|
|
elif args.format == "csv":
|
|
content = render_csv(report)
|
|
else:
|
|
content = render_markdown(report, include_body=args.include_body, max_text_chars=args.max_text_chars)
|
|
|
|
write_output(content, args.out)
|
|
return 0 if not report["summary"].get("tasks_failed") else 1
|
|
|
|
|
|
if __name__ == "__main__":
|
|
raise SystemExit(main())
|