#!/usr/bin/env python3

import re
import sqlite3

DB = "data/zammad_analysis.sqlite3"

db = sqlite3.connect(DB)
db.row_factory = sqlite3.Row

cols = {r[1] for r in db.execute("PRAGMA table_info(articles)")}

if "analysis_body" not in cols:
    db.execute("""
        ALTER TABLE articles
        ADD COLUMN analysis_body TEXT
    """)

db.commit()


def clean_quotes(text):
    if not text:
        return ""

    text = text.replace("\r\n", "\n").replace("\r", "\n")

    # Klassische Antwortmarker.
    markers = [
        r"^\s*Am\s+.+?\bschrieb\s+.+?:\s*$",
        r"^\s*On\s+.+?\bwrote:\s*$",
        r"^\s*-----Original[- ]Nachricht-----\s*$",
        r"^\s*-----Ursprüngliche Nachricht-----\s*$",
        r"^\s*-----Original Message-----\s*$",
        r"^\s*Von:\s+.+$",
        r"^\s*From:\s+.+$",
        r"^\s*Gesendet:\s+.+$",
        r"^\s*Sent:\s+.+$",
        r"^\s*Datum:\s+.+$",
        r"^\s*Date:\s+.+$",
    ]

    lines = text.splitlines()

    cut = None

    for i, line in enumerate(lines):
        stripped = line.strip()

        for pattern in markers:
            if re.match(pattern, stripped, re.I):
                cut = i
                break

        if cut is not None:
            break

    if cut is not None:
        lines = lines[:cut]

    # Klassische >-Zitatblöcke entfernen.
    result = []

    for line in lines:
        if re.match(r"^\s*>", line):
            continue

        result.append(line)

    text = "\n".join(result)

    # Automatisch zitierte Bestellformulare entfernen.
    # Diese enthalten keinen neuen Kunden-/Agenteninhalt.
    order_markers = [
        r"\bwir haben nachfolgende bestellung soeben erhalten\b",
        r"\bguten tag\s*,?\s*wir haben nachfolgende bestellung",
    ]

    for marker in order_markers:
        m = re.search(marker, text, re.I)
        if m:
            text = text[:m.start()].rstrip()
            break

    # Signaturen nur am Ende.
    text = re.split(
        r"\n\s*(Mit freundlichen Grüßen|"
        r"Viele Grüße|"
        r"Beste Grüße|"
        r"Freundliche Grüße|"
        r"Kind regards|"
        r"Best regards)\b",
        text,
        maxsplit=1,
        flags=re.I,
    )[0]

    text = re.sub(r"[ \t]+", " ", text)
    text = re.sub(r"\n{3,}", "\n\n", text)

    return text.strip()


rows = db.execute("""
    SELECT id, ticket_id, role, clean_body
    FROM articles
    WHERE clean_body IS NOT NULL
""").fetchall()

changed = 0
empty = 0

for row in rows:

    original = row["clean_body"] or ""
    cleaned = clean_quotes(original)

    db.execute("""
        UPDATE articles
        SET analysis_body = ?
        WHERE id = ?
    """, (
        cleaned,
        row["id"],
    ))

    if cleaned != original:
        changed += 1

    if not cleaned:
        empty += 1

db.commit()

print("=" * 72)
print("ZAMMAD ZITATBEREINIGUNG")
print("=" * 72)
print(f"Artikel:             {len(rows)}")
print(f"Verändert:           {changed}")
print(f"Leer nach Bereinigung: {empty}")

print()
print("BEISPIELE")
print("-" * 72)

examples = db.execute("""
    SELECT
        ticket_id,
        role,
        clean_body,
        analysis_body
    FROM articles
    WHERE clean_body != analysis_body
    ORDER BY id
    LIMIT 20
""").fetchall()

for row in examples:
    print(f"\nTicket #{row['ticket_id']} [{row['role']}]")
    print("VORHER:")
    print((row["clean_body"] or "")[:500])
    print("NACHHER:")
    print((row["analysis_body"] or "")[:500])

db.close()