Reisacher-Forderung/app/cleanup_comments.py

import re
from datetime import date, timedelta

from db import get_session
from sqlalchemy import text


def main():
    db = next(get_session())
    q = db.execute(text("SELECT * FROM dbo.Forderungen_Kommentar_Rohdaten"))

    for row in q.fetchall():
        for i, r in enumerate(row.Kommentar.split("\n"), 1):
            comment_date = row.Timestamp + timedelta(minutes=i)
            comment_user = row.Benutzer_ID
            r = r.replace("'", "")
            d, u = get_date_and_user_from_comment(r)
            db.execute(
                text(
                    "INSERT INTO dbo.Forderungen_Kommentar "
                    f"VALUES ('{row.Client_DB}', '{row.Beleg_Nr}', '{comment_date}', '{comment_user}', '{r}')"
                )
            )

    db.commit()


def get_details_from_comments():
    db = next(get_session())
    q = db.execute(text("SELECT * FROM dbo.Forderungen_Kommentar_Rohdaten"))

    for row in q.fetchall():
        data = {}

        az = re.search(r"^AZ\:?\s(\d+\/\d+)", row.Kommentar)
        if az:
            data["Rechtsanwalt_Aktenzeichen"] = az[1]

        if "HP Schaden" in row.Kommentar:
            data["Versicherung_Typ"] = "Haftpflicht"
        if "Haftpflicht" in row.Kommentar:
            data["Versicherung_Typ"] = "Haftpflicht"
        if "VK Schaden" in row.Kommentar:
            data["Versicherung_Typ"] = "Vollkasko"
        if "VK-Schaden" in row.Kommentar:
            data["Versicherung_Typ"] = "Vollkasko"
        if "TK Schaden" in row.Kommentar:
            data["Versicherung_Typ"] = "Teilkasko"
        if "TK-Schaden" in row.Kommentar:
            data["Versicherung_Typ"] = "Teilkasko"

        s = re.search(r"Schaden (.*) bei (.*) eingereicht", row.Kommentar)
        if s:
            data["Schadennummer"] = s[1]
            data["Vers_Adresse_ID"] = s[2].replace("'", "").lower()[:25]

        if data:
            db.execute(
                text(
                    "UPDATE dbo.Forderungen_Details SET "
                    + ", ".join([f"{k} = '{v}'" for k, v in data.items()])
                    + f" WHERE Client_DB = '{row.Client_DB}' AND Beleg_Nr = '{row.Beleg_Nr}'"
                )
            )
    db.commit()


def get_date_and_user_from_comment(r: str) -> tuple[date, str]:
    m = re.search(r"(\d\d\.\d\d?)\.?[\/\s](\w+)", r)
    if m:
        date_split = m[1].split(".")
        return date(2026, date_split[1], date_split[0]), m[2]

    if r.startswith("EWB"):
        return None, "mde"

    az = re.search(r"^AZ\:?\s(\d+\/\d+)", r)
    if az:
        return None, "mde"
    return None, "mde"


if __name__ == "__main__":
    get_details_from_comments()