import io
from datetime import datetime

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 HTMLResponse, RedirectResponse, StreamingResponse
from fastapi.templating import Jinja2Templates
from sqlalchemy import text
from sqlalchemy.orm import Session

from .auth import ldap_authenticate
from .db import get_session
from .models import Bemerkung, Forderung
from .schemas import BemerkungIn

router = APIRouter()
templates = Jinja2Templates(directory="templates")


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


@router.get("/login", response_class=HTMLResponse)
def login_get(request: Request):
    return templates.TemplateResponse("base/login.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 number_format(input: float) -> str:
    return format(input, "0,.2f").replace(".", ":").replace(",", ".").replace(":", ",")


def date_format(input: datetime) -> str:
    return input.strftime("%d.%m.%Y")


templates.env.filters["number_format"] = number_format
templates.env.filters["date_format"] = date_format


@router.get("/forderungen/liste", response_class=HTMLResponse)
def forderungen_liste(request: Request, db: Session = Depends(get_session), limit: int = 100):
    context = {
        "RechnungsdatumVon": {"selectedDate": "'2000-01-01T00:00:00'"},
        "RechnungsdatumBis": {"selectedDate": "'2027-01-01T00:00:00'"},
        "Hauptbetrieb": {"selectedOptionValue": "''"},
        "Standort": {"selectedOptionValue": "''"},
        "Rechnungsnummer": {"text": "''"},
        "Kunde": {"selectedOptionValue": "''"},
        "Verursacher": {"selectedOptionValue": "''"},
        "Fahrzeug": {"selectedOptionValue": "''"},
        "Staffel": {"selectedOptionValue": "''"},
        "Mahnstufe": {"selectedOptionValue": "''"},
        "WiedervorlageVon": {"selectedDate": "'2000-01-01T00:00:00'"},
        "WiedervorlageBis": {"selectedDate": "'2027-01-01T00:00:00'"},
        "BenutzerSelect": {"selectedOptionValue": "winter"},
    }
    query = templates.TemplateResponse(request, "forderungen/queries/forderungen_liste.sql", context).body.decode(
        "utf-8"
    )
    # print(query)
    # q = db.execute(text("SELECT * FROM [dbo].[Forderungen]"))
    q = db.execute(text(query)).fetchall()
    col_names = list(q[0]._asdict().keys())

    config = {
        # "RechnungsdatumVon": (),
        # "RechnungsdatumBis": {"selectedDate": "'2027-01-01T00:00:00'"},
        "Hauptbetrieb": ("Client_DB", "Hauptbetrieb_Name"),
        "Standort": ("Standort_ID", "Standort_Name"),
        # "Rechnungsnummer": {"text": "''"},
        "Kunde": ("Kunde", "Kunde"),
        "Verursacher": ("Verursacher", "Verursacher"),
        "Fahrzeug": ("VIN", "VIN"),
        "Staffel": ("Staffel", "Staffel"),
        "Mahnstufe": ("Mahnstufe", "Mahnstufe"),
        # "WiedervorlageVon": {"selectedDate": "'2000-01-01T00:00:00'"},
        # "WiedervorlageBis": {"selectedDate": "'2027-01-01T00:00:00'"},
        # "BenutzerSelect": {"selectedOptionValue": "winter"},
    }

    filters = {}

    for filter_name, (key, value) in config.items():
        filters[filter_name] = {row[col_names.index(key)]: row[col_names.index(value)] for row in q}

    # print(filters["Standort"])

    return templates.TemplateResponse(
        request, "forderungen/liste.html", {"request": request, "forderungen_liste": q, "filter": filters}
    )


@router.get("/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",
    ]:
        query = templates.TemplateResponse(request, f"forderungen/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",
    }
    return templates.TemplateResponse(request, "forderungen/details.html", template_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("/export/docx")
def export_docx2(id: int, db: Session = Depends(get_session)):
    doc = DocxTemplate("templates\\forderungen\\docs\\Mahnung_AHR.docx")
    context = {
        "Kunde_Name": "Robert Burghard",
        "Kunde_Adresse": "Im Waldhof 14a",
        "Kunde_PLZ": "61476",
        "Kunde_Ort": "Kronberg",
        "Datum_heute": "25.03.2026",
        "Kunde_Nr": "23121983",
        "Fahrzeug_Kennzeichen": "OF-RB 512",
        "Rechnung_Nr": "2600007",
        "Betrag_SB": "250,00",
        "Betrag_USt": "380,00",
    }
    doc.render(context)
    # doc.save("export\\Mahnung_AHR_Burghard.docx")
    buf = io.BytesIO()
    doc.save(buf)
    buf.seek(0)
    return StreamingResponse(
        buf,
        media_type="application/vnd.openxmlformats-officedocument.wordprocessingml.document",
        headers={"Content-Disposition": "attachment; filename=Mahnung_Burghard.docx"},
    )