Reisacher-Forderung/app/comments.py

"""Transactional comment writes shared by chat and follow-up editing."""

from sqlalchemy import text


def lock_comment_invoice(db, pkey):
    """Serialize comment writers before touching details or current flags."""
    row = db.execute(
        text("""
            SELECT Beleg_Nr FROM dbo.Forderungen_Kopf WITH (UPDLOCK, HOLDLOCK)
            WHERE Client_DB = :Client_DB AND Beleg_Nr = :Beleg_Nr
        """),
        pkey,
    ).first()
    if row is None:
        raise ValueError("Forderung nicht gefunden")


def save_comment(db, pkey, *, username, timestamp, comment):
    """Write a comment and its current flags; the caller owns the transaction."""
    lock_comment_invoice(db, pkey)
    fibu = db.execute(
        text("""
            SELECT TOP 1 1 FROM dbo.Benutzer_Rolle
            WHERE Benutzer_ID = :Benutzer_ID AND Rolle_ID = 'FIBU'
        """),
        {"Benutzer_ID": username},
    ).first() is not None
    # The identifier comes exclusively from these two constants, never from input.
    column = "Aktuell_Fibu" if fibu else "Aktuell_Abteilung"
    db.execute(
        text(f"""
            UPDATE dbo.Forderungen_Kommentar SET {column} = 0
            WHERE Client_DB = :Client_DB AND Beleg_Nr = :Beleg_Nr
        """),
        pkey,
    )
    db.execute(
        text("""
            INSERT INTO dbo.Forderungen_Kommentar
                (Client_DB, Beleg_Nr, [Timestamp], Benutzer_ID, Kommentar, Aktuell_Fibu, Aktuell_Abteilung)
            VALUES (:Client_DB, :Beleg_Nr, :Timestamp, :Benutzer_ID, :Kommentar, :Aktuell_Fibu, :Aktuell_Abteilung)
        """),
        dict(pkey, Benutzer_ID=username, Timestamp=timestamp, Kommentar=comment,
             Aktuell_Fibu=int(fibu), Aktuell_Abteilung=int(not fibu)),
    )