"""Projekt-Helper fuer den Selbstlern-Loop (Etappe 6).

Kern-Regeln (mit Herb am 2026-05-25 festgehalten):
1. Pro SuS gibt es maximal EIN aktives Projekt (status_lebenszyklus = 'in_arbeit').
2. Ein neues Projekt kann NUR angelegt werden, wenn:
   - kein 'in_arbeit'-Projekt existiert UND
   - das letzte abgeschlossene Projekt ist 'freigegeben' (nicht 'wartet_auf_freigabe'
     und nicht 'rueckfrage').
3. Bei Selbstbewertung 'gelernt' (= Misserfolg) → automatisch 'wartet_auf_freigabe'
   → Lehrer muss freigeben, bevor neues Projekt erlaubt ist.
4. Bei Selbstbewertung 'zufrieden' / 'mittel' → automatisch 'freigegeben'.
   Lehrer kann nachtraeglich 'rueckfrage' setzen, dann blockiert es ebenfalls.
"""
from datetime import datetime


# Status-Werte (zentral, damit nicht ueberall String-Literale)
LEBENSZYKLUS_IN_ARBEIT = "in_arbeit"
LEBENSZYKLUS_ABGESCHLOSSEN = "abgeschlossen"

FREIGABE_IN_ARBEIT = "in_arbeit"
FREIGABE_WARTET = "wartet_auf_freigabe"
FREIGABE_FREIGEGEBEN = "freigegeben"
FREIGABE_RUECKFRAGE = "rueckfrage"

EVAL_GESAMT = ("zufrieden", "mittel", "gelernt")
EVAL_QUALITAET = ("gut", "ok", "schwierig")


def aktives_projekt(conn, profil_id: int):
    """Liefert das aktuelle in_arbeit-Projekt eines SuS (oder None)."""
    return conn.execute(
        "SELECT id, titel, beschreibung_md, status_lebenszyklus, freigabe_status, "
        "       geaendert_am, (cover_blob IS NOT NULL) AS hat_cover "
        "FROM projekt WHERE profil_id = ? AND status_lebenszyklus = ? "
        "ORDER BY id DESC LIMIT 1",
        (profil_id, LEBENSZYKLUS_IN_ARBEIT)
    ).fetchone()


def letztes_projekt(conn, profil_id: int):
    """Liefert das chronologisch letzte Projekt — egal welcher Status."""
    return conn.execute(
        "SELECT id, titel, status_lebenszyklus, freigabe_status, "
        "       selbsteinschaetzung, abgeschlossen_am "
        "FROM projekt WHERE profil_id = ? "
        "ORDER BY COALESCE(abgeschlossen_am, geaendert_am) DESC, id DESC LIMIT 1",
        (profil_id,)
    ).fetchone()


def ist_power_profil(conn, profil_id: int) -> bool:
    """True, wenn das Profil die Rolle 'power' hat."""
    row = conn.execute("SELECT rolle FROM profil WHERE id = ?", (profil_id,)).fetchone()
    return bool(row and row["rolle"] == "power")


def darf_neues_anlegen(conn, profil_id: int):
    """Prueft, ob der Nutzer aktuell ein neues Projekt anlegen darf.

    Power-User duerfen IMMER (mehrere Demo-Projekte parallel). Fuer normale
    SuS gilt die 1-Projekt-Regel + Freigabe-Gate.

    Returns:
        (darf: bool, grund: str | None)
    """
    if ist_power_profil(conn, profil_id):
        return (True, None)
    if aktives_projekt(conn, profil_id):
        return (False, "Du hast schon ein laufendes Projekt — erst abschliessen, dann neu anfangen.")
    letztes = letztes_projekt(conn, profil_id)
    if letztes and letztes["freigabe_status"] == FREIGABE_WARTET:
        return (
            False,
            f"Dein letztes Projekt ('{letztes['titel']}') wartet auf Freigabe vom Lehrer. "
            "Sobald er drueber geschaut hat, kannst du ein neues anfangen."
        )
    if letztes and letztes["freigabe_status"] == FREIGABE_RUECKFRAGE:
        return (
            False,
            f"Der Lehrer hat eine Rueckfrage zu deinem letzten Projekt ('{letztes['titel']}'). "
            "Schau ins Cockpit, beantworte sie — dann kannst du neu anfangen."
        )
    return (True, None)


def liste_eigener(conn, profil_id: int, kommend_nur: bool = False):
    """Liefert alle Projekte eines SuS. Default: ALLE inkl. abgeschlossene."""
    sql = (
        "SELECT id, titel, beschreibung_md, status_lebenszyklus, freigabe_status, "
        "       selbsteinschaetzung, selbsteinschaetzung_planung, selbsteinschaetzung_ausfuehrung, "
        "       abgeschlossen_am, geaendert_am, (cover_blob IS NOT NULL) AS hat_cover "
        "FROM projekt WHERE profil_id = ? "
    )
    if kommend_nur:
        sql += f"AND status_lebenszyklus = '{LEBENSZYKLUS_IN_ARBEIT}' "
    sql += "ORDER BY COALESCE(abgeschlossen_am, geaendert_am) DESC, id DESC"
    return conn.execute(sql, (profil_id,)).fetchall()


def projekt_holen(conn, projekt_id: int):
    """Volle Projekt-Row inkl. hat_cover-Flag + Werkstatt-Felder."""
    return conn.execute(
        "SELECT id, profil_id, paarung_id, titel, beschreibung_md, "
        "       status_lebenszyklus, abgeschlossen_am, freigabe_status, "
        "       selbsteinschaetzung, selbsteinschaetzung_planung, selbsteinschaetzung_ausfuehrung, "
        "       was_gelernt, was_klappte_nicht, "
        "       freigegeben_am, freigegeben_von, sichtbar_in_ag, vorlage_projekt_id, "
        "       modellier_software_slug, drucker_id_used, makerworld_url, "
        "       geaendert_am, (cover_blob IS NOT NULL) AS hat_cover "
        "FROM projekt WHERE id = ?",
        (projekt_id,)
    ).fetchone()


def dateien_eines_projekts(conn, projekt_id: int):
    """Alle Dateien + Links eines Projekts (ohne BLOB-Inhalt — nur Metadaten)."""
    return conn.execute(
        "SELECT id, dateiname, mime_type, groesse_bytes, art, extern_url, hochgeladen_am "
        "FROM projekt_datei WHERE projekt_id = ? "
        "ORDER BY (extern_url IS NOT NULL) DESC, hochgeladen_am ASC",
        (projekt_id,)
    ).fetchall()


def abschluss_voraussetzungen(conn, projekt_id: int):
    """Prueft, ob ein Projekt abgeschlossen werden darf.

    Bedingung: Cover-Bild gesetzt UND mindestens eine Datei hochgeladen.

    Returns:
        (darf: bool, fehler: list[str]) — Liste der noch fehlenden Punkte.
    """
    row = conn.execute(
        "SELECT (cover_blob IS NOT NULL) AS hat_cover, "
        "       (SELECT COUNT(*) FROM projekt_datei WHERE projekt_id = ?) AS files "
        "FROM projekt WHERE id = ?",
        (projekt_id, projekt_id)
    ).fetchone()
    if not row:
        return (False, ["Projekt nicht gefunden."])
    fehler = []
    if not row["hat_cover"]:
        fehler.append("Cover-Bild fehlt — lad ein Foto vom Druck oder Modell hoch.")
    if row["files"] == 0:
        fehler.append("Keine Dateien hochgeladen — mindestens eine STL/3MF oder ein Foto wird gebraucht.")
    return (len(fehler) == 0, fehler)


def freigabe_nach_eval(selbsteinschaetzung_gesamt: str) -> str:
    """Bestimmt den Freigabe-Status anhand der Selbstbewertung.

    - 'zufrieden' / 'mittel' → 'freigegeben' (SuS kann gleich weiter)
    - 'gelernt' (Misserfolg) → 'wartet_auf_freigabe' (Lehrer muss drueber)
    """
    if selbsteinschaetzung_gesamt == "gelernt":
        return FREIGABE_WARTET
    return FREIGABE_FREIGEGEBEN


def warten_auf_freigabe_anzahl(conn) -> int:
    """Zahl fuer das Admin-Dashboard-Badge."""
    row = conn.execute(
        "SELECT COUNT(*) AS n FROM projekt WHERE freigabe_status = ?",
        (FREIGABE_WARTET,)
    ).fetchone()
    return row["n"] if row else 0


def alle_projekte(conn, filter_status: str | None = None):
    """Alle Projekte aller SuS — fuer Admin-Uebersicht.

    filter_status:
        None / 'alle'       — alles
        'wartet'            — nur freigabe_status = wartet_auf_freigabe
        'in_arbeit'         — nur status_lebenszyklus = in_arbeit
        'abgeschlossen'     — nur status_lebenszyklus = abgeschlossen
        'rueckfrage'        — nur freigabe_status = rueckfrage
    """
    sql = (
        "SELECT p.id, p.titel, p.status_lebenszyklus, p.freigabe_status, "
        "       p.selbsteinschaetzung, p.selbsteinschaetzung_planung, p.selbsteinschaetzung_ausfuehrung, "
        "       p.abgeschlossen_am, p.geaendert_am, p.profil_id, "
        "       pr.vorname, pr.tier, (p.cover_blob IS NOT NULL) AS hat_cover "
        "FROM projekt p LEFT JOIN profil pr ON pr.id = p.profil_id "
    )
    where = []
    params: list = []
    if filter_status == "wartet":
        where.append("p.freigabe_status = ?")
        params.append(FREIGABE_WARTET)
    elif filter_status == "in_arbeit":
        where.append("p.status_lebenszyklus = ?")
        params.append(LEBENSZYKLUS_IN_ARBEIT)
    elif filter_status == "abgeschlossen":
        where.append("p.status_lebenszyklus = ?")
        params.append(LEBENSZYKLUS_ABGESCHLOSSEN)
    elif filter_status == "rueckfrage":
        where.append("p.freigabe_status = ?")
        params.append(FREIGABE_RUECKFRAGE)
    if where:
        sql += "WHERE " + " AND ".join(where) + " "
    # Sortierung: wartend zuerst, dann in_arbeit, dann abgeschlossen
    sql += (
        "ORDER BY "
        "CASE p.freigabe_status WHEN 'wartet_auf_freigabe' THEN 0 WHEN 'rueckfrage' THEN 1 ELSE 2 END, "
        "CASE p.status_lebenszyklus WHEN 'in_arbeit' THEN 0 ELSE 1 END, "
        "COALESCE(p.abgeschlossen_am, p.geaendert_am) DESC"
    )
    return conn.execute(sql, params).fetchall()


def status_zaehler(conn) -> dict:
    """Liefert dict mit Anzahl pro Filter-Status — fuer Filter-Buttons."""
    row = conn.execute(
        "SELECT "
        "  COUNT(*) AS alle, "
        "  SUM(CASE WHEN freigabe_status = ? THEN 1 ELSE 0 END) AS wartet, "
        "  SUM(CASE WHEN status_lebenszyklus = ? THEN 1 ELSE 0 END) AS in_arbeit, "
        "  SUM(CASE WHEN status_lebenszyklus = ? THEN 1 ELSE 0 END) AS abgeschlossen, "
        "  SUM(CASE WHEN freigabe_status = ? THEN 1 ELSE 0 END) AS rueckfrage "
        "FROM projekt",
        (FREIGABE_WARTET, LEBENSZYKLUS_IN_ARBEIT, LEBENSZYKLUS_ABGESCHLOSSEN, FREIGABE_RUECKFRAGE)
    ).fetchone()
    return {
        "alle": row["alle"] or 0,
        "wartet": row["wartet"] or 0,
        "in_arbeit": row["in_arbeit"] or 0,
        "abgeschlossen": row["abgeschlossen"] or 0,
        "rueckfrage": row["rueckfrage"] or 0,
    }


def katalog_projekte(conn):
    """Oeffentlicher Katalog (fuer SuS):
       - alle in_arbeit-Projekte (man sieht, woran andere gerade arbeiten)
       - alle abgeschlossenen mit freigabe_status='freigegeben'

       NICHT: wartet_auf_freigabe oder rueckfrage — die sind noch nicht
       'oeffentlich' und sollen nicht im Katalog auftauchen.
    """
    return conn.execute(
        "SELECT p.id, p.titel, p.beschreibung_md, p.status_lebenszyklus, "
        "       p.selbsteinschaetzung, p.abgeschlossen_am, p.geaendert_am, "
        "       p.makerworld_url, "
        "       p.modellier_software_slug, sw.titel AS software_titel, "
        "       p.drucker_id_used, d.name AS drucker_name, "
        "       p.profil_id, pr.vorname, pr.tier, "
        "       (p.cover_blob IS NOT NULL) AS hat_cover, "
        "       (SELECT COUNT(*) FROM projekt_datei WHERE projekt_id = p.id) AS datei_anzahl "
        "FROM projekt p "
        "LEFT JOIN profil pr ON pr.id = p.profil_id "
        "LEFT JOIN software_seite sw ON sw.slug = p.modellier_software_slug "
        "LEFT JOIN drucker d ON d.id = p.drucker_id_used "
        "WHERE p.sichtbar_in_ag = 1 "
        "AND ( "
        "  p.status_lebenszyklus = 'in_arbeit' "
        "  OR (p.status_lebenszyklus = 'abgeschlossen' AND p.freigabe_status = 'freigegeben') "
        ") "
        "ORDER BY COALESCE(p.abgeschlossen_am, p.geaendert_am) DESC"
    ).fetchall()


def aus_vorlage_anlegen(conn, profil_id: int, vorlage_id: int):
    """Legt ein neues Projekt mit Verknuepfung zu vorlage_id an.

    Verknuepfung-Modus (kein Klon, kein Copy): nur vorlage_projekt_id wird
    gesetzt. Titel kommt mit "Wiederholt: ..." als Vorschlag — SuS aendert
    in der Werkstatt. Beschreibung, Software, Drucker, Files bleiben leer.

    Pruefungen:
    - SuS darf neues Projekt anlegen (darf_neues_anlegen).
    - Vorlage existiert + ist sichtbar (in_arbeit oder freigegeben).

    Returns:
        (neue_projekt_id, None) bei Erfolg
        (None, fehler_text) bei Fehler
    """
    darf, grund = darf_neues_anlegen(conn, profil_id)
    if not darf:
        return (None, grund)

    vorlage = conn.execute(
        "SELECT id, titel, status_lebenszyklus, freigabe_status, sichtbar_in_ag "
        "FROM projekt WHERE id = ?",
        (vorlage_id,)
    ).fetchone()
    if not vorlage or not vorlage["sichtbar_in_ag"]:
        return (None, "Vorlage nicht gefunden oder nicht sichtbar.")
    # Wartet-auf-Freigabe und Rueckfrage-Projekte koennen NICHT als Vorlage dienen
    if vorlage["status_lebenszyklus"] == LEBENSZYKLUS_ABGESCHLOSSEN and vorlage["freigabe_status"] != FREIGABE_FREIGEGEBEN:
        return (None, "Diese Vorlage ist noch nicht freigegeben.")

    titel_vorschlag = f"Wiederholt: {vorlage['titel']}"
    cur = conn.execute(
        "INSERT INTO projekt (profil_id, titel, vorlage_projekt_id, status_lebenszyklus, freigabe_status) "
        "VALUES (?, ?, ?, 'in_arbeit', 'in_arbeit')",
        (profil_id, titel_vorschlag, vorlage_id)
    )
    conn.commit()
    return (cur.lastrowid, None)


def wiederholungen_von(conn, projekt_id: int):
    """Liefert alle Projekte, die dieses als vorlage_projekt_id haben.

    Mit Person-Info fuer die Anzeige.
    """
    return conn.execute(
        "SELECT p.id, p.titel, p.status_lebenszyklus, p.freigabe_status, "
        "       p.profil_id, pr.vorname, pr.tier "
        "FROM projekt p LEFT JOIN profil pr ON pr.id = p.profil_id "
        "WHERE p.vorlage_projekt_id = ? "
        "ORDER BY p.id",
        (projekt_id,)
    ).fetchall()


def vorlage_info(conn, vorlage_id: int):
    """Daten der Vorlage fuer das Banner im Detail."""
    return conn.execute(
        "SELECT p.id, p.titel, p.profil_id, pr.vorname, pr.tier "
        "FROM projekt p LEFT JOIN profil pr ON pr.id = p.profil_id "
        "WHERE p.id = ?",
        (vorlage_id,)
    ).fetchone()


def projekt_dateien_fuer_katalog(conn, projekt_id: int):
    """Nur die Metadaten fuer die Datei-Liste im Katalog-Detail (keine BLOBs).

    Links (extern_url) zuerst, dann Uploads.
    """
    return conn.execute(
        "SELECT id, dateiname, mime_type, groesse_bytes, art, extern_url "
        "FROM projekt_datei WHERE projekt_id = ? "
        "ORDER BY (extern_url IS NOT NULL) DESC, art, dateiname",
        (projekt_id,)
    ).fetchall()


def review_queue(conn):
    """Alle Projekte, die auf Lehrer-Freigabe warten."""
    return conn.execute(
        "SELECT p.id, p.titel, p.beschreibung_md, p.abgeschlossen_am, "
        "       p.selbsteinschaetzung, p.selbsteinschaetzung_planung, "
        "       p.selbsteinschaetzung_ausfuehrung, p.was_gelernt, p.was_klappte_nicht, "
        "       p.profil_id, pr.vorname, pr.tier, "
        "       (p.cover_blob IS NOT NULL) AS hat_cover "
        "FROM projekt p LEFT JOIN profil pr ON pr.id = p.profil_id "
        "WHERE p.freigabe_status = ? "
        "ORDER BY p.abgeschlossen_am ASC",
        (FREIGABE_WARTET,)
    ).fetchall()