Reisacher-Forderung/app/routes.py

import io
import re
from dataclasses import dataclass
from datetime import datetime, timedelta
from pathlib import Path
from urllib.parse import unquote, urlencode

import pandas as pd
from docx import Document
from docxtpl import DocxTemplate
from fastapi import APIRouter, Depends, Form, HTTPException, Request, Response
from fastapi.responses import (
    FileResponse,
    HTMLResponse,
    RedirectResponse,
    StreamingResponse,
)
from sqlalchemy import text
from sqlalchemy.orm import Session

from .db import get_session
from .jinja_templates import templates
from .ldap import ldap_authenticate
from .models import Bemerkung, Forderung
from .schemas import BemerkungIn

router = APIRouter()


@router.get("/", response_class=HTMLResponse)
def index(request: Request):
    return RedirectResponse(url="/login")


@router.get("/login", response_class=HTMLResponse)
def login_get(request: Request):
    return templates.TemplateResponse(request, "base/login.html", {"request": request})


@router.get("/select", response_class=HTMLResponse)
def select_get(request: Request):
    return templates.TemplateResponse(request, "base/select.html", {"request": request})


@router.post("/login")
def login_post(username: str = Form(...), password: str = Form(...)):
    user = ldap_authenticate(username, password)
    if not user:
        raise HTTPException(status_code=401, detail="Invalid credentials")
    # For a simple demo we set a cookie (not secure) -- replace with real session in production
    response = RedirectResponse(url="/forderungen", status_code=302)
    response.set_cookie("user", user["username"])
    return response


@router.get("/forderungen", response_class=HTMLResponse)
def forderungsliste(request: Request, db: Session = Depends(get_session), limit: int = 100):
    q = db.query(Forderung).order_by(Forderung.faelligkeit.asc()).limit(limit).all()
    return templates.TemplateResponse(request, "base/list.html", {"request": request, "forderungen": q})


def single_quote(text: str):
    return "'" + unquote(text) + "'"


@dataclass
class FilterConfig:
    name: str
    text: str
    filter_type: str
    width: str
    key_column: str
    value_column: str
    visible: bool = True
    default_value: str = ""
    options: list[dict[str, str]] | None = None
    current_value: str | None = None


forderung_filter_config = [
    FilterConfig("Hauptbetrieb", "Hauptbetrieb", "select", "2", "Client_DB", "Hauptbetrieb_Name"),
    FilterConfig("Standort", "Standort", "select", "3", "Standort_ID", "Standort_Name"),
    FilterConfig("Bereich", "Bereich", "select", "3", "Bereich", "Bereich"),
    FilterConfig("Verursacher", "Verursacher", "select", "4", "Verursacher", "Verursacher"),
    FilterConfig("Rechnungsnummer", "Rechnungsnummer", "text", "2", "Document_No", "Document_No"),
    FilterConfig(
        "RechnungsdatumVon",
        "Rechnungsdatum von",
        "date",
        "2",
        "Invoice_Date",
        "Invoice_Date",
        default_value="2000-01-01T00:00:00",
    ),
    FilterConfig(
        "RechnungsdatumBis",
        "Rechnungsdatum bis",
        "date",
        "2",
        "Invoice_Date",
        "Invoice_Date",
        default_value="2027-01-01T00:00:00",
    ),
    FilterConfig("Kunde", "Kunde", "text", "4", "Kunde", "Kunde"),
    FilterConfig("Abwarten", "Abwarten", "select", "2", "Abwarten", "Abwarten"),
    FilterConfig(
        "WiedervorlageVon",
        "Wiedervorlage von",
        "date",
        "2",
        "Wiedervorlage",
        "Wiedervorlage",
        default_value="2000-01-01T00:00:00",
    ),
    FilterConfig(
        "WiedervorlageBis",
        "Wiedervorlage bis",
        "date",
        "2",
        "Wiedervorlage",
        "Wiedervorlage",
        default_value="2027-01-01T00:00:00",
    ),
    FilterConfig("Fahrzeug", "Fahrzeug", "select", "2", "VIN", "VIN"),
    FilterConfig("Staffel", "Staffel", "select", "2", "Staffel", "Staffel"),
    FilterConfig("Mahnstufe", "Mahnstufe", "select", "2", "Mahnstufe", "Mahnstufe"),
    FilterConfig("Bearbeitet", "Bearbeitet", "select", "2", "Bearbeitet", "Bearbeitet"),
    FilterConfig("BenutzerSelect", "Benutzer", "hidden", "2", "", "", visible=False, default_value="winter"),
]

filter_type_option = {
    "text": "text",
    "select": "selectedOptionValue",
    "date": "selectedDate",
    "hidden": "selectedOptionValue",
}


@router.get("/app/forderungen/liste", response_class=HTMLResponse)
def forderungen_liste(request: Request, db: Session = Depends(get_session), limit: int = 100, page: int = 0):
    params = {
        f.name: request.query_params[f.name]
        for f in forderung_filter_config
        if request.query_params.get(f.name, "") != ""
    }
    if page == 0 and len(params) < len(request.query_params):
        return RedirectResponse(url="/app/forderungen/liste?" + urlencode(params))

    context = {}
    for f in forderung_filter_config:
        context[f.name] = {filter_type_option[f.filter_type]: single_quote(params.get(f.name, f.default_value))}

    query = templates.TemplateResponse(request, "forderungen/liste/queries/forderungen_liste.sql", context).body.decode(
        "utf-8"
    )
    # print(query)
    # q = db.execute(text("SELECT * FROM [dbo].[Forderungen]"))
    q_res = db.execute(text(query))
    col_names = list(q_res.keys())
    q = q_res.fetchall()

    filters = {}
    for f in forderung_filter_config:
        if f.key_column == "":
            continue
        filters[f.name] = {row[col_names.index(f.key_column)]: row[col_names.index(f.value_column)] for row in q}
        f.options = filters[f.name]

    summary = {
        "offen": sum([r[col_names.index("offen")] for r in q]),
        "Anzahl": len(q),
    }

    q_limit = q[page * limit : (page + 1) * limit]

    defaults = {}
    for f in forderung_filter_config:
        defaults[f.name] = unquote(request.query_params.get(f.name, f.default_value))
        f.current_value = defaults[f.name]

    if len(q_limit) == 0:
        return None

    template = "base/liste.html"
    if page > 0:
        template = "base/liste_tabelle.html"

    return templates.TemplateResponse(
        request,
        template,
        {
            "request": request,
            "forderungen_liste": q_limit,
            "filter": filters,
            "defaults": defaults,
            "summary": summary,
            "page": page,
            "filter_config": forderung_filter_config,
        },
    )


@router.get("/app/forderungen/details/{client_db}_{document_no}", response_class=HTMLResponse)
def forderungen_details(
    request: Request, client_db: str, document_no: str, db: Session = Depends(get_session), limit: int = 100
):
    context = {
        "appsmith": {
            "URL": {
                "queryParams": {
                    "Client_DB": "'" + client_db + "'",
                    "Document_No": "'" + document_no + "'",
                }
            }
        },
        "BelegeFilter": {"selectedOptionValue": "D"},
        "BenutzerSelect": {"selectedOptionValue": "winter"},
    }

    template_context = {}
    for filename in [
        "auftrag_positionen",
        "forderung_belege",
        "forderung_kommentar",
        "forderung_kopf",
        "forderung_mahnung",
        "versicherungen",
    ]:
        query = templates.TemplateResponse(request, f"forderungen/details/queries/{filename}.sql", context).body.decode(
            "utf-8"
        )
        template_context[filename] = db.execute(text(query)).fetchall()
    template_context["forderung"] = {
        "id": "12345",
        "rechnungsnummer": "WERE123445",
        "kunde": "Burghard",
        "betrag": "120,00",
        "faelligkeit": "23.12.1983",
    }
    fileserver = "C:\\Projekte\\Reisacher-Fileserver"
    files = [f.relative_to(fileserver) for f in Path(f"{fileserver}\\{client_db}\\{document_no}").glob("*")]
    template_context["files"] = files
    template_context["add7days"] = datetime.now() + timedelta(days=7)

    return templates.TemplateResponse(request, "forderungen/details/details.html", template_context)


@router.post("/app/forderungen/details/{client_db}_{document_no}", response_class=HTMLResponse)
async def post_forderungen_details(
    request: Request, client_db: str, document_no: str, db: Session = Depends(get_session)
):
    data = {
        "Client_DB": client_db,
        "Beleg_Nr": document_no,
        "Bearbeitet": "J",
        "Versicherung": "N",
        "Rechtsanwalt": "N",
        "Reklamation": "N",
        "Mahnen_aussetzen": "N",
        "Selbstbeteiligung": "",
        "Selbstbeteiligung_Betrag": "0.0",
        "Mwst": "",
        "Mwst_Betrag": "0.0",
        "Wiedervorlage": "",
        "Vers_Adresse_ID": "",
        "Versicherung_Typ": "",
        "Reklamation_Begruendung": "",
        "Rechtsanwalt_Begruendung": "",
        "Rechtsanwalt_Aktenzeichen": "",
        "Rechtsanwalt_Stand": "",
        "Mahnen_Begruendung": "",
    }

    async with request.form() as form:
        for k, v in form.items():
            if k in data:
                if v == "on":
                    v = "J"
                if re.match(r"\d+,?\d*", v):
                    v = v.replace(",", ".")
                data[k] = v
    query = templates.TemplateResponse(
        request, "forderungen/details/queries/post/update_details.sql", context={"data": data}
    ).body.decode("utf-8")
    print(query)
    for subquery in query.split(";"):
        q = db.execute(text(subquery))
        print(q.rowcount)
    db.commit()


@router.get("/app/forderungen/dashboard", response_class=HTMLResponse)
def forderungen_dashboard(request: Request, db: Session = Depends(get_session)):
    context = {"summary": False}
    return templates.TemplateResponse(request, "forderungen/dashboard/dashboard.html", context)


@router.get("/chat", response_class=HTMLResponse)
def chat(request: Request):
    # q = db.query(Forderung).order_by(Forderung.faelligkeit.asc()).limit(limit).all()
    return templates.TemplateResponse(request, "base/chat.html")


@router.get("/export/csv")
def export_csv(db: Session = Depends(get_session)):
    q = db.query(Forderung).all()
    rows = []
    for f in q:
        rows.append(
            {
                "id": f.id,
                "rechnungsnummer": f.rechnungsnummer,
                "kunde": f.kunde.name if f.kunde else "",
                "betrag": float(f.betrag),
                "faelligkeit": f.faelligkeit.isoformat() if f.faelligkeit else "",
            }
        )
    df = pd.DataFrame(rows)
    buf = io.StringIO()
    df.to_csv(buf, index=False)
    buf.seek(0)
    return Response(
        content=buf.getvalue(),
        media_type="text/csv",
        headers={"Content-Disposition": "attachment; filename=forderungen.csv"},
    )


@router.get("/export/xlsx")
def export_xlsx(db: Session = Depends(get_session)):
    q = db.query(Forderung).all()
    rows = []
    for f in q:
        rows.append(
            {
                "id": f.id,
                "rechnungsnummer": f.rechnungsnummer,
                "kunde": f.kunde.name if f.kunde else "",
                "betrag": float(f.betrag),
                "faelligkeit": f.faelligkeit.isoformat() if f.faelligkeit else "",
            }
        )
    df = pd.DataFrame(rows)
    buf = io.BytesIO()
    with pd.ExcelWriter(buf, engine="openpyxl") as writer:
        df.to_excel(writer, index=False, sheet_name="Forderungen")
    buf.seek(0)
    return StreamingResponse(
        buf,
        media_type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
        headers={"Content-Disposition": "attachment; filename=forderungen.xlsx"},
    )


@router.get("/detail/{id}", response_class=HTMLResponse)
def detail(request: Request, id: int, db: Session = Depends(get_session)):
    f = db.query(Forderung).filter(Forderung.id == id).first()
    if not f:
        raise HTTPException(status_code=404)
    return templates.TemplateResponse(request, "base/detail.html", {"request": request, "forderung": f})


@router.post("/detail/{id}/bemerkung")
def save_bemerkung(id: int, data: BemerkungIn, request: Request, db: Session = Depends(get_session)):
    user = request.cookies.get("user") or "anonymous"
    b = Bemerkung(
        forderung_id=id, benutzer=user, bemerkung=data.bemerkung, wiedervorlage_datum=data.wiedervorlage_datum
    )
    db.add(b)
    db.commit()
    return RedirectResponse(url=f"/detail/{id}", status_code=302)


@router.get("/detail/{id}/export/docx")
def export_docx(id: int, db: Session = Depends(get_session)):
    f = db.query(Forderung).filter(Forderung.id == id).first()
    if not f:
        raise HTTPException(status_code=404)
    doc = Document()
    doc.add_heading(f"Rechnung {f.rechnungsnummer}", level=1)
    doc.add_paragraph(f'Kunde: {f.kunde.name if f.kunde else ""}')
    doc.add_paragraph(f"Betrag: {float(f.betrag)}")
    doc.add_paragraph(f"Fälligkeit: {f.faelligkeit}")
    buf = io.BytesIO()
    doc.save(buf)
    buf.seek(0)
    return StreamingResponse(
        buf,
        media_type="application/vnd.openxmlformats-officedocument.wordprocessingml.document",
        headers={"Content-Disposition": f"attachment; filename=rechnung_{f.rechnungsnummer}.docx"},
    )


@router.get("/app/forderungen/export/{client_db}_{document_no}/")
def export_docx2(request: Request, client_db: str, document_no: str, db: Session = Depends(get_session)):
    context = {
        "appsmith": {
            "URL": {
                "queryParams": {
                    "Client_DB": "'" + client_db + "'",
                    "Document_No": "'" + document_no + "'",
                }
            }
        },
    }
    query = templates.TemplateResponse(request, "forderungen/details/queries/forderung_kopf.sql", context).body.decode(
        "utf-8"
    )
    q = db.execute(text(query)).fetchone()

    doc = DocxTemplate("templates\\forderungen\\docs\\Mahnung_AHR.docx")
    filename = Path(
        f"C:\\Projekte\\Reisacher-Fileserver\\{client_db}\\{document_no}\\Mahnung_{client_db}_{document_no}.docx"
    )
    filename.parent.mkdir(parents=True, exist_ok=True)

    context = {
        "Kunde_Name": q.Kunde,
        "Kunde_Adresse": "Im Waldhof 14a",
        "Kunde_PLZ": "61476",
        "Kunde_Ort": "Kronberg",
        "Datum_heute": datetime.now().strftime("%d.%m.%Y"),
        "Kunde_Nr": q.Kunde.split("-")[-1].strip(),
        "Fahrzeug_Kennzeichen": "OF-RB 512",
        "Rechnung_Nr": q.Document_No,
        "Betrag_SB": "250,00",
        "Betrag_USt": "380,00",
    }
    doc.render(context)
    doc.save(filename)

    return FileResponse(filename, media_type="application/octet-stream", filename=filename.name)


@router.get("/files/")
def get_files(request: Request):
    name = request.query_params.get("name")
    filename = Path(f"C:\\Projekte\\Reisacher-Fileserver\\{unquote(name)}")

    return FileResponse(filename, media_type="application/octet-stream", filename=filename.name)