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

import asyncio
import json
import sqlite3
import subprocess
import sys
import urllib.request
from pathlib import Path

DB = Path("data/telephony.sqlite3")
MODEL = "qwen3:8b"
OLLAMA = "http://127.0.0.1:11434/api/generate"


def schema():
    return {
        "type": "object",
        "properties": {
            "customer": {
                "type": "object",
                "properties": {
                    "name": {"type": ["string", "null"]},
                    "email": {"type": ["string", "null"]},
                    "phone": {"type": ["string", "null"]},
                    "address": {"type": ["string", "null"]},
                    "customer_number": {"type": ["string", "null"]},
                    "order_number": {"type": ["string", "null"]}
                },
                "required": [
                    "name", "email", "phone", "address",
                    "customer_number", "order_number"
                ]
            },
            "products": {
                "type": "array",
                "items": {
                    "type": "object",
                    "properties": {
                        "raw_text": {"type": "string"},
                        "normalized": {"type": ["string", "null"]},
                        "quantity": {"type": ["number", "null"]},
                        "uncertain": {"type": "boolean"}
                    },
                    "required": [
                        "raw_text", "normalized",
                        "quantity", "uncertain"
                    ]
                }
            },
            "vouchers": {
                "type": "array",
                "items": {
                    "type": "object",
                    "properties": {
                        "raw_text": {"type": "string"},
                        "amount": {"type": ["number", "null"]},
                        "code": {"type": ["string", "null"]},
                        "uncertain": {"type": "boolean"}
                    },
                    "required": [
                        "raw_text", "amount",
                        "code", "uncertain"
                    ]
                }
            },
            "analysis": {
                "type": "object",
                "properties": {
                    "intent": {"type": ["string", "null"]},
                    "sentiment": {
                        "type": ["string", "null"],
                        "enum": ["positive", "neutral", "negative", "mixed", None]
                    },
                    "summary": {"type": ["string", "null"]},
                    "advice": {
                        "type": "array",
                        "items": {"type": "string"}
                    },
                    "follow_up": {
                        "type": "array",
                        "items": {"type": "string"}
                    }
                },
                "required": [
                    "intent", "sentiment", "summary",
                    "advice", "follow_up"
                ]
            },
            "uncertainties": {
                "type": "array",
                "items": {"type": "string"}
            }
        },
        "required": [
            "customer",
            "products",
            "vouchers",
            "analysis",
            "uncertainties"
        ]
    }


def transcript_text(data):
    texts = []

    for item in data.get("transcripts", []):
        for segment in item.get("segments", []):
            text = segment.get("text", "").strip()
            if text:
                texts.append(text)

    return "\n".join(texts)


def analyze(text):
    prompt = f"""
Analysiere dieses deutschsprachige Kundentelefonat.

Extrahiere personenbezogene Daten, sofern sie im Gespräch genannt werden:
Name, E-Mail, Telefonnummer, Adresse, Kundennummer, Bestellnummer
und Gutscheincode.

Diese Daten dienen der späteren Kunden- und Auftragszuordnung.

Verwende ausschließlich Informationen aus dem Transkript.
Erfinde niemals Daten.

Whisper kann Wörter falsch erkennen.
Korrigiere einen Fehler nur, wenn der Kontext die Korrektur eindeutig macht.

Bei unsicheren Produktnamen, Nummern, Codes oder Namen:
raw_text = tatsächlich erkannter Wortlaut
normalized = null
uncertain = true

Keine Sprecherzuordnung erfinden.
Keine neuen Zeitstempel erzeugen.

TRANSKRIPT:

{text}
"""

    payload = {
        "model": MODEL,
        "prompt": prompt,
        "stream": False,
        "format": schema(),
        "think": False,
        "options": {"temperature": 0}
    }

    request = urllib.request.Request(
        OLLAMA,
        data=json.dumps(payload).encode(),
        headers={"Content-Type": "application/json"},
        method="POST"
    )

    with urllib.request.urlopen(request, timeout=300) as response:
        return json.loads(json.load(response)["response"])


async def main():
    con = sqlite3.connect(DB)
    con.row_factory = sqlite3.Row

    t = con.execute("""
        SELECT *
        FROM transcripts
        WHERE rec_id = 4618
        ORDER BY id DESC
        LIMIT 1
    """).fetchone()

    if not t:
        raise RuntimeError("Transcript 4618 nicht gefunden.")

    print(f"Vorhandenes Transcript: {t['id']}")

    data = json.loads(t["transcript_json"])
    text = transcript_text(data)

    print(f"Transkript: {len(text)} Zeichen")
    print("Qwen3:8b analysiert vorhandenes Transkript ...")

    analysis = await asyncio.to_thread(analyze, text)

    con.execute("""
        INSERT INTO analyses (
            call_id,
            cdr_row_id,
            transcript_id,
            model,
            schema_version,
            analysis_json,
            status
        )
        VALUES (
            NULL,
            ?,
            ?,
            ?,
            '1.0',
            ?,
            'completed'
        )
    """, (
        t["cdr_row_id"],
        t["id"],
        MODEL,
        json.dumps(analysis, ensure_ascii=False)
    ))

    con.commit()

    print()
    print("Analyse gespeichert.")
    print(json.dumps(analysis, ensure_ascii=False, indent=2))

    con.close()


asyncio.run(main())