3cx-telefonie-middleware/tools/review_zammad_unknown.py

#!/usr/bin/env python3

import json
import sqlite3
import subprocess

DB = "data/zammad_analysis.sqlite3"
MODEL = "qwen3:8b"

SYSTEM = """Du analysierst historische Zammad-Artikel eines Kundenservice-Systems.

Entscheide ausschließlich zwischen:
AGENT_RESPONSE = individuell formulierte fachliche Antwort eines Mitarbeiters
AUTOMATION = automatisch erzeugte System-/Ticketnachricht

Eine fachliche Antwort kann auch von Zammad als System/email geführt werden.

Antworte ausschließlich als JSON:
{
  "role": "AGENT_RESPONSE|AUTOMATION",
  "confidence": 0.0,
  "reason": "kurze Begründung"
}
"""

db = sqlite3.connect(DB)

rows = db.execute("""
    SELECT id, ticket_id, subject, clean_body
    FROM articles
    WHERE content_role = 'UNKNOWN_REVIEW'
    ORDER BY id
""").fetchall()

print("=" * 72)
print("QWEN – UNKNOWN REVIEW")
print("=" * 72)
print(f"Fälle: {len(rows)}")
print(f"Modell: {MODEL}")
print()

for article_id, ticket_id, subject, body in rows:

    prompt = f"""{SYSTEM}

Betreff:
{subject or ""}

Artikel:
{body or ""}
"""

    result = subprocess.run(
        [
            "ollama",
            "run",
            MODEL,
            prompt,
        ],
        capture_output=True,
        text=True,
        timeout=300,
    )

    output = result.stdout.strip()

    # JSON aus möglichem Markdown-Codeblock extrahieren
    if "{" in output and "}" in output:
        output = output[
            output.find("{"):
            output.rfind("}") + 1
        ]

    try:
        data = json.loads(output)

        role = data.get("role")
        confidence = float(
            data.get("confidence", 0)
        )
        reason = data.get("reason", "")

        if role not in (
            "AGENT_RESPONSE",
            "AUTOMATION",
        ):
            raise ValueError(
                f"Ungültige Rolle: {role}"
            )

    except Exception as exc:
        print(
            f"Ticket #{ticket_id}: "
            f"JSON-Fehler: {exc}"
        )
        print(output[:500])
        continue

    db.execute("""
        UPDATE articles
        SET content_role = ?,
            classification_score = ?,
            classification_reason = ?
        WHERE id = ?
    """, (
        role,
        confidence,
        "QWEN: " + reason,
        article_id,
    ))

    db.commit()

    print(
        f"Ticket #{ticket_id}: "
        f"{role:16} "
        f"{confidence:.2f} "
        f"{reason}"
    )

db.close()