Datenbanken mit SQLAlchemy: ORM sauber einsetzen

from __future__ import annotations

import json
from datetime import UTC, datetime
from pathlib import Path

from sqlalchemy import (
    create_engine, select, func, ForeignKey,
    String, Integer, Float, DateTime, case
)
from sqlalchemy.orm import (
    DeclarativeBase, Mapped, mapped_column, relationship, sessionmaker
)

# ===== LZ1: Setup (SQLite) =====
Path("daten").mkdir(exist_ok=True)
engine = create_engine("sqlite:///daten/sematrain.db", echo=False)
Session = sessionmaker(bind=engine)

# ===== LZ2: Modelle + Relationships =====
class Base(DeclarativeBase):
    pass

class Kurs(Base):
    __tablename__ = "kurs"

    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    kursname: Mapped[str] = mapped_column(String(120), nullable=False)
    format: Mapped[str] = mapped_column(String(30), nullable=False)     # Online / Praesenz
    standort: Mapped[str] = mapped_column(String(60), nullable=False)
    dauer_tage: Mapped[int] = mapped_column(Integer, nullable=False)
    preis_euro: Mapped[float] = mapped_column(Float, nullable=False)

    buchungen: Mapped[list["Buchung"]] = relationship(
        back_populates="kurs",
        cascade="all, delete-orphan"
    )

class Teilnehmer(Base):
    __tablename__ = "teilnehmer"

    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    name: Mapped[str] = mapped_column(String(120), nullable=False)
    firma: Mapped[str] = mapped_column(String(120), nullable=False)

    buchungen: Mapped[list["Buchung"]] = relationship(
        back_populates="teilnehmer",
        cascade="all, delete-orphan"
    )

def utc_now_naive() -> datetime:
    # UTC-normalisiert; im SQLite-Beispiel bewusst ohne tzinfo gespeichert.
    return datetime.now(UTC).replace(tzinfo=None)


class Buchung(Base):
    __tablename__ = "buchung"

    id: Mapped[int] = mapped_column(Integer, primary_key=True)

    teilnehmer_id: Mapped[int] = mapped_column(ForeignKey("teilnehmer.id"), nullable=False)
    kurs_id: Mapped[int] = mapped_column(ForeignKey("kurs.id"), nullable=False)

    gebucht_am: Mapped[datetime] = mapped_column(DateTime, default=utc_now_naive, nullable=False)
    rabatt_prozent: Mapped[int] = mapped_column(Integer, default=0, nullable=False)

    teilnehmer: Mapped["Teilnehmer"] = relationship(back_populates="buchungen")
    kurs: Mapped["Kurs"] = relationship(back_populates="buchungen")

Base.metadata.create_all(engine)

# ===== Helper: Endpreis als SQL-Expression (LZ4) =====
def endpreis_expr():
    # Endpreis = preis * (1 - rabatt/100)
    return (Kurs.preis_euro * (1 - (Buchung.rabatt_prozent / 100.0)))

# ===== LZ3: Seed nur wenn leer =====
with Session() as session:
    hat_kurse = session.execute(select(func.count(Kurs.id))).scalar_one() > 0
    if not hat_kurse:
        kurs_hh = Kurs(kursname="Python Schulung", format="Praesenz", standort="Hamburg", dauer_tage=3, preis_euro=1490.00)
        kurs_be = Kurs(kursname="Python Schulung", format="Online",   standort="Berlin",  dauer_tage=3, preis_euro=1390.00)
        kurs_m  = Kurs(kursname="Python Schulung", format="Praesenz", standort="Muenchen",dauer_tage=4, preis_euro=1890.00)

        t1 = Teilnehmer(name="Max Beispiel", firma="Beispiel GmbH")
        t2 = Teilnehmer(name="Ada Muster",   firma="Muster AG")
        t3 = Teilnehmer(name="Lena Demo",    firma="Demo KG")

        session.add_all([kurs_hh, kurs_be, kurs_m, t1, t2, t3])
        session.flush()  # IDs verfügbar

        # 4 Buchungen (Beispieldaten)
        session.add_all([
            Buchung(teilnehmer=t1, kurs=kurs_be, rabatt_prozent=10),
            Buchung(teilnehmer=t2, kurs=kurs_be, rabatt_prozent=0),
            Buchung(teilnehmer=t3, kurs=kurs_hh, rabatt_prozent=5),
            Buchung(teilnehmer=t1, kurs=kurs_m,  rabatt_prozent=0),
        ])

        session.commit()
        print("Seed: Daten angelegt.")
    else:
        print("Seed: DB bereits gefüllt – übersprungen.")

# ===== LZ3: CRUD (Update/Delete/Create) + Fehlerbehandlung =====
try:
    with Session() as session:
        # UPDATE: Rabatt für eine Buchung setzen (z.B. erste Buchung von Max)
        b = session.execute(
            select(Buchung).join(Buchung.teilnehmer).where(Teilnehmer.name == "Max Beispiel")
        ).scalars().first()

        if b:
            b.rabatt_prozent = 15  # Beispiel
            session.commit()
            print("UPDATE: Rabatt gesetzt ->", b.rabatt_prozent, "%")

    with Session() as session:
        # DELETE: eine Buchung löschen (z.B. eine von Ada)
        b = session.execute(
            select(Buchung).join(Buchung.teilnehmer).where(Teilnehmer.name == "Ada Muster")
        ).scalars().first()

        if b:
            session.delete(b)
            session.commit()
            print("DELETE: Buchung von Ada gelöscht.")

    with Session() as session:
        # CREATE: neue Buchung anlegen (Lena bucht Berlin Online)
        lena = session.execute(select(Teilnehmer).where(Teilnehmer.name == "Lena Demo")).scalars().first()
        berlin = session.execute(select(Kurs).where(Kurs.standort == "Berlin", Kurs.format == "Online")).scalars().first()

        if lena and berlin:
            session.add(Buchung(teilnehmer=lena, kurs=berlin, rabatt_prozent=10))
            session.commit()
            print("CREATE: Neue Buchung (Lena -> Berlin Online).")

except Exception as e:
    # Wenn du Session außerhalb 'with' nutzt: session.rollback()
    print("FEHLER: Transaktion abgebrochen:", e)

# ===== LZ4: Query (Join) – Buchungsliste mit Endpreis =====
buchungsliste = []
with Session() as session:
    stmt = (
        select(
            Buchung.id,
            Teilnehmer.name,
            Teilnehmer.firma,
            Kurs.kursname,
            Kurs.format,
            Kurs.standort,
            Kurs.preis_euro,
            Buchung.rabatt_prozent,
            endpreis_expr().label("endpreis_euro"),
            Buchung.gebucht_am,
        )
        .join(Buchung.teilnehmer)
        .join(Buchung.kurs)
        .order_by(Kurs.standort.asc(), Teilnehmer.name.asc())
    )

    rows = session.execute(stmt).all()
    print("\nBuchungen:")
    for r in rows:
        print(
            f"#{r.id} | {r.name} ({r.firma}) | {r.kursname} | {r.format} | {r.standort} | "
            f"Preis: {r.preis_euro:.2f} | Rabatt: {r.rabatt_prozent}% | Endpreis: {r.endpreis_euro:.2f}"
        )
        buchungsliste.append({
            "buchung_id": r.id,
            "teilnehmer": r.name,
            "firma": r.firma,
            "kurs": r.kursname,
            "format": r.format,
            "standort": r.standort,
            "preis_euro": round(r.preis_euro, 2),
            "rabatt_prozent": r.rabatt_prozent,
            "endpreis_euro": round(float(r.endpreis_euro), 2),
            "gebucht_am": r.gebucht_am.replace(tzinfo=UTC).isoformat(),
        })

# ===== Bonus (LZ4): Report – Umsatz (Beispiel) pro Standort =====
with Session() as session:
    stmt = (
        select(
            Kurs.standort,
            func.count(Buchung.id).label("buchungen"),
            func.sum(endpreis_expr()).label("umsatz_beispiel")
        )
        .join(Buchung.kurs)
        .group_by(Kurs.standort)
        .order_by(func.sum(endpreis_expr()).desc())
    )
    print("\nReport: Umsatz (Beispiel) je Standort")
    for standort, anzahl, umsatz in session.execute(stmt).all():
        print(standort, "| Buchungen:", anzahl, "| Umsatz:", round(float(umsatz or 0), 2))

# ===== Bonus: JSON Export (passt zu Kapitel 04) =====
export = Path("daten") / "buchungen_export.json"
export.write_text(json.dumps(buchungsliste, ensure_ascii=False, indent=2), encoding="utf-8")
print("\nJSON exportiert:", export)