shinkan-jinkendo/backend/routers/auth.py

"""
Authentication Endpoints for Mitai Jinkendo

Handles login, logout, password reset, and profile authentication.
"""
import os
import secrets
import smtplib
import ssl
from urllib.parse import quote
from typing import Optional
from datetime import datetime, timedelta, timezone
from email.mime.text import MIMEText

from fastapi import APIRouter, HTTPException, Header, Depends
from starlette.requests import Request
import ipaddress

from slowapi import Limiter

from db import get_db, get_cursor
from auth import (
    get_limited_session,
    get_session,
    hash_pin,
    make_token,
    require_auth,
    revoke_profile_sessions,
    session_lifetime_days,
    verify_pin,
    verify_pin_for_login,
)
from media_grant import MEDIA_GRANT_TTL_SECONDS, issue_media_grant
from models import (
    ChangePasswordRequest,
    LoginRequest,
    LoginTotpRequest,
    PasswordResetRequest,
    PasswordResetConfirm,
    RegisterRequest,
    ResendVerificationRequest,
    TotpCodeRequest,
    TotpDisableRequest,
)
from totp_auth import (
    generate_recovery_codes,
    generate_totp_secret,
    hash_recovery_code,
    otpauth_uri,
    verify_totp,
)
from password_reset_mail import (
    insert_password_reset_session,
    password_reset_email_body,
    revoke_pending_password_resets_for_profile,
)

router = APIRouter(prefix="/api/auth", tags=["auth"])

LOGIN_LOCK_AFTER = 8
LOGIN_LOCK_MINUTES = 15
_ADMIN_ROLES = ("admin", "superadmin")


def _client_ip(request: Request) -> str:
    """Echte Client-IP nur, wenn der direkte Peer ein vertrauenswürdiger Proxy ist."""
    peer = request.client.host if request.client and request.client.host else ""
    if _is_trusted_proxy(peer):
        real = (request.headers.get("x-real-ip") or "").split(",")[0].strip()
        if real:
            return real[:64]
    return peer or "unknown"


def _is_trusted_proxy(host: str) -> bool:
    if not host:
        return False
    try:
        ip = ipaddress.ip_address(host)
    except ValueError:
        return False
    return ip.is_private or ip.is_loopback or ip.is_link_local


limiter = Limiter(key_func=_client_ip)


def _admin_totp_required() -> bool:
    """Nur wenn REQUIRE_ADMIN_TOTP ausdrücklich an ist. Standard: der Nutzer entscheidet selbst."""
    raw = (os.getenv("REQUIRE_ADMIN_TOTP") or "").strip().lower()
    return raw in ("1", "true", "yes")


def _issue_session(cur, profile_id: int, *, days: int, purpose: str = "full") -> tuple[str, datetime]:
    token = make_token()
    expires = datetime.now(timezone.utc) + timedelta(days=days if purpose == "full" else 0, minutes=0)
    if purpose == "totp_challenge":
        expires = datetime.now(timezone.utc) + timedelta(minutes=5)
    elif purpose == "totp_enroll":
        expires = datetime.now(timezone.utc) + timedelta(minutes=30)
    cur.execute(
        """
        INSERT INTO sessions (token, profile_id, expires_at, created_at, purpose)
        VALUES (%s, %s, %s, CURRENT_TIMESTAMP, %s)
        """,
        (token, profile_id, expires, purpose),
    )
    return token, expires


def _login_payload(prof: dict, token: str, expires: datetime) -> dict:
    return {
        "token": token,
        "profile_id": prof["id"],
        "email": prof.get("email"),
        "name": prof.get("name"),
        "role": prof.get("role"),
        "tier": prof.get("tier"),
        "expires_at": expires.isoformat(),
        "totp_enabled": bool(prof.get("totp_enabled")),
    }


def _record_failed_login(cur, profile_id: int) -> None:
    cur.execute(
        """
        UPDATE profiles
        SET failed_login_count = CASE
                WHEN failed_login_count + 1 >= %s THEN 0
                ELSE failed_login_count + 1
            END,
            locked_until = CASE
                WHEN failed_login_count + 1 >= %s
                THEN CURRENT_TIMESTAMP + (%s * INTERVAL '1 minute')
                ELSE locked_until
            END
        WHERE id = %s
        """,
        (LOGIN_LOCK_AFTER, LOGIN_LOCK_AFTER, LOGIN_LOCK_MINUTES, profile_id),
    )


def _clear_login_lock(cur, profile_id: int) -> None:
    cur.execute(
        "UPDATE profiles SET failed_login_count = 0, locked_until = NULL WHERE id = %s",
        (profile_id,),
    )


@router.post("/login")
@limiter.limit("10/minute")
async def login(req: LoginRequest, request: Request):
    """Login with email + password. Bei aktivem TOTP folgt ein zweiter Schritt."""
    email = req.email.lower().strip()
    with get_db() as conn:
        cur = get_cursor(conn)
        cur.execute(
            """
            SELECT *,
                   (locked_until IS NOT NULL AND locked_until > CURRENT_TIMESTAMP) AS is_locked
            FROM profiles WHERE email=%s
            """,
            (email,),
        )
        prof = cur.fetchone()
        password_ok = verify_pin_for_login(req.password, prof["pin_hash"] if prof else None)
        if not prof or not password_ok:
            if prof and not prof.get("is_locked"):
                _record_failed_login(cur, prof["id"])
            outcome = "invalid"
        elif prof.get("is_locked"):
            outcome = "locked"
        else:
            outcome = "ok"
            if prof["pin_hash"] and not str(prof["pin_hash"]).startswith("$2"):
                cur.execute(
                    "UPDATE profiles SET pin_hash=%s WHERE id=%s",
                    (hash_pin(req.password), prof["id"]),
                )
            _clear_login_lock(cur, prof["id"])

    if outcome == "invalid":
        raise HTTPException(401, "Ungültige Zugangsdaten")
    if outcome == "locked":
        raise HTTPException(429, "Zu viele Anmeldeversuche. Bitte später erneut versuchen.")

    with get_db() as conn:
        cur = get_cursor(conn)
        role = (prof.get("role") or "").strip().lower()
        if prof.get("totp_enabled"):
            token, expires = _issue_session(cur, prof["id"], days=1, purpose="totp_challenge")
            return {
                "totp_required": True,
                "challenge_token": token,
                "expires_at": expires.isoformat(),
            }
        if role in _ADMIN_ROLES and _admin_totp_required() and not prof.get("totp_enabled"):
            token, expires = _issue_session(cur, prof["id"], days=1, purpose="totp_enroll")
            return {
                "totp_enrollment_required": True,
                "token": token,
                "email": prof.get("email"),
                "expires_at": expires.isoformat(),
            }

        token, expires = _issue_session(cur, prof["id"], days=session_lifetime_days(prof))

    return _login_payload(prof, token, expires)


@router.post("/logout")
def logout(x_auth_token: Optional[str]=Header(default=None)):
    """Logout (delete session)."""
    if x_auth_token:
        with get_db() as conn:
            cur = get_cursor(conn)
            cur.execute("DELETE FROM sessions WHERE token=%s", (x_auth_token,))
    return {"ok": True}


@router.get("/me")
def get_me(session: dict=Depends(require_auth)):
    """Get current user info."""
    pid = session['profile_id']
    # Import here to avoid circular dependency
    from routers.profiles import profile_document
    return profile_document(pid)


@router.get("/status")
def auth_status():
    """Health check endpoint."""
    return {"status": "ok", "service": "mitai-jinkendo", "version": "v9b"}


@router.put("/pin")
def change_pin(req: ChangePasswordRequest, session: dict = Depends(require_auth)):
    """Passwort wechseln. Das aktuelle Passwort ist Pflicht; alle Sitzungen werden ersetzt."""
    pid = session["profile_id"]
    with get_db() as conn:
        cur = get_cursor(conn)
        cur.execute("SELECT * FROM profiles WHERE id=%s", (pid,))
        prof = cur.fetchone()
        if not prof or not verify_pin(req.current_password, prof["pin_hash"]):
            raise HTTPException(400, "Aktuelles Passwort ist falsch")
        cur.execute(
            "UPDATE profiles SET pin_hash=%s WHERE id=%s",
            (hash_pin(req.new_password), pid),
        )
        revoke_profile_sessions(cur, int(pid))
        token, expires = _issue_session(cur, int(pid), days=session_lifetime_days(prof))
    return {"ok": True, "token": token, "expires_at": expires.isoformat()}


@router.post("/login/totp")
@limiter.limit("10/minute")
async def login_totp(req: LoginTotpRequest, request: Request):
    """Zweiter Login-Schritt: Authenticator-Code oder Wiederherstellungscode."""
    with get_db() as conn:
        cur = get_cursor(conn)
        cur.execute(
            """
            SELECT s.*, p.role, p.name, p.email
            FROM sessions s JOIN profiles p ON s.profile_id = p.id
            WHERE s.token = %s AND s.expires_at > CURRENT_TIMESTAMP AND s.purpose = 'totp_challenge'
            """,
            (req.challenge_token,),
        )
        challenge = cur.fetchone()
        error = None
        payload = None
        if not challenge:
            error = "Anmeldung abgelaufen. Bitte erneut anmelden."
        else:
            pid = int(challenge["profile_id"])
            cur.execute("SELECT * FROM profiles WHERE id=%s", (pid,))
            prof = cur.fetchone()
            if not prof or not prof.get("totp_enabled"):
                error = "Anmeldung abgelaufen. Bitte erneut anmelden."
            else:
                code_ok = verify_totp(prof.get("totp_secret"), req.code) or _consume_recovery_code(
                    cur, pid, req.code
                )
                if not code_ok:
                    attempts = int(challenge.get("failed_attempts") or 0) + 1
                    if attempts >= 5:
                        cur.execute("DELETE FROM sessions WHERE token=%s", (req.challenge_token,))
                    else:
                        cur.execute(
                            "UPDATE sessions SET failed_attempts=%s WHERE token=%s",
                            (attempts, req.challenge_token),
                        )
                    error = "Ungültiger Code"
                else:
                    revoke_profile_sessions(cur, pid)
                    token, expires = _issue_session(cur, pid, days=session_lifetime_days(prof))
                    payload = _login_payload(prof, token, expires)
    if error:
        raise HTTPException(401, error)
    return payload


def _consume_recovery_code(cur, profile_id: int, code: str) -> bool:
    digest = hash_recovery_code(code)
    cur.execute(
        """
        UPDATE profile_recovery_codes
        SET used_at = CURRENT_TIMESTAMP
        WHERE profile_id = %s AND code_hash = %s AND used_at IS NULL
        RETURNING id
        """,
        (profile_id, digest),
    )
    return cur.fetchone() is not None


def _setup_session_or_401(token: str) -> dict:
    full = None
    if token:
        full = get_session(token)
    if full:
        return full
    enroll = get_limited_session(token, "totp_enroll") if token else None
    if not enroll:
        raise HTTPException(401, "Nicht eingeloggt")
    return enroll


@router.get("/totp/status")
def totp_status(x_auth_token: Optional[str] = Header(default=None)):
    session = _setup_session_or_401(x_auth_token or "")
    with get_db() as conn:
        cur = get_cursor(conn)
        cur.execute(
            "SELECT email, role, totp_enabled FROM profiles WHERE id=%s",
            (session["profile_id"],),
        )
        prof = cur.fetchone()
    if not prof:
        raise HTTPException(401, "Nicht eingeloggt")
    role = (prof.get("role") or session.get("role") or "").strip().lower()
    enrollment = (session.get("purpose") or "full") == "totp_enroll"
    return {
        "totp_enabled": bool(prof.get("totp_enabled")),
        "enrollment_required": enrollment or (
            role in _ADMIN_ROLES and _admin_totp_required() and not prof.get("totp_enabled")
        ),
        "email": prof.get("email"),
        "role": role,
    }


@router.post("/totp/setup")
def totp_setup(x_auth_token: Optional[str] = Header(default=None)):
    """Neues Geheimnis erzeugen. Wirksam erst nach /totp/confirm."""
    session = _setup_session_or_401(x_auth_token or "")
    secret = generate_totp_secret()
    with get_db() as conn:
        cur = get_cursor(conn)
        cur.execute("SELECT email FROM profiles WHERE id=%s", (session["profile_id"],))
        prof = cur.fetchone()
        if not prof:
            raise HTTPException(404, "Profil nicht gefunden")
        cur.execute(
            "UPDATE profiles SET totp_pending_secret=%s WHERE id=%s",
            (secret, session["profile_id"]),
        )
    email = prof.get("email") or "konto"
    return {
        "secret": secret,
        "otpauth_uri": otpauth_uri(secret=secret, account_name=email),
    }


@router.post("/totp/confirm")
def totp_confirm(req: TotpCodeRequest, x_auth_token: Optional[str] = Header(default=None)):
    session = _setup_session_or_401(x_auth_token or "")
    pid = int(session["profile_id"])
    with get_db() as conn:
        cur = get_cursor(conn)
        cur.execute("SELECT * FROM profiles WHERE id=%s", (pid,))
        prof = cur.fetchone()
        if not prof or not prof.get("totp_pending_secret"):
            raise HTTPException(400, "Zuerst ein neues Geheimnis erzeugen")
        if not verify_totp(prof.get("totp_pending_secret"), req.code):
            raise HTTPException(400, "Code stimmt nicht. Uhrzeit des Geräts prüfen.")
        cur.execute(
            """
            UPDATE profiles
            SET totp_secret=%s, totp_pending_secret=NULL, totp_enabled=TRUE
            WHERE id=%s
            """,
            (prof["totp_pending_secret"], pid),
        )
        plain_codes = _replace_recovery_codes(cur, pid)
        prof = dict(prof)
        prof["totp_enabled"] = True
        prof["totp_secret"] = None
        revoke_profile_sessions(cur, pid)
        token, expires = _issue_session(cur, pid, days=session_lifetime_days(prof))
    payload = _login_payload(prof, token, expires)
    payload["recovery_codes"] = plain_codes
    return payload


def _replace_recovery_codes(cur, profile_id: int) -> list[str]:
    cur.execute("DELETE FROM profile_recovery_codes WHERE profile_id=%s", (profile_id,))
    plain_codes = generate_recovery_codes()
    for code in plain_codes:
        cur.execute(
            """
            INSERT INTO profile_recovery_codes (profile_id, code_hash)
            VALUES (%s, %s)
            """,
            (profile_id, hash_recovery_code(code)),
        )
    return plain_codes


@router.post("/totp/recovery-codes")
def regenerate_recovery_codes(req: TotpDisableRequest, session: dict = Depends(require_auth)):
    """Neue Codes ausgeben. Die bisherigen werden ungültig. Passwort und aktueller App-Code sind Pflicht."""
    pid = int(session["profile_id"])
    with get_db() as conn:
        cur = get_cursor(conn)
        cur.execute("SELECT * FROM profiles WHERE id=%s", (pid,))
        prof = cur.fetchone()
        if not prof or not prof.get("totp_enabled"):
            raise HTTPException(400, "Zwei-Faktor ist nicht aktiv")
        if not verify_pin(req.current_password, prof["pin_hash"]):
            raise HTTPException(400, "Aktuelles Passwort ist falsch")
        if not verify_totp(prof.get("totp_secret"), req.code):
            raise HTTPException(400, "Code aus der App stimmt nicht")
        plain_codes = _replace_recovery_codes(cur, pid)
    return {"recovery_codes": plain_codes}


@router.post("/totp/disable")
def totp_disable(req: TotpDisableRequest, session: dict = Depends(require_auth)):
    pid = int(session["profile_id"])
    role = (session.get("role") or "").strip().lower()
    if role in _ADMIN_ROLES and _admin_totp_required():
        raise HTTPException(400, "Für Plattform-Admins bleibt die Zwei-Faktor-Anmeldung aktiv.")
    with get_db() as conn:
        cur = get_cursor(conn)
        cur.execute("SELECT * FROM profiles WHERE id=%s", (pid,))
        prof = cur.fetchone()
        if not prof or not verify_pin(req.current_password, prof["pin_hash"]):
            raise HTTPException(400, "Aktuelles Passwort ist falsch")
        if not (
            verify_totp(prof.get("totp_secret"), req.code)
            or _consume_recovery_code(cur, pid, req.code)
        ):
            raise HTTPException(400, "Code stimmt nicht")
        cur.execute(
            """
            UPDATE profiles
            SET totp_enabled=FALSE, totp_secret=NULL, totp_pending_secret=NULL
            WHERE id=%s
            """,
            (pid,),
        )
        cur.execute("DELETE FROM profile_recovery_codes WHERE profile_id=%s", (pid,))
    return {"ok": True}


@router.post("/media-grant")
def media_grant(session: dict = Depends(require_auth)):
    """Kurzlebiges Token nur für Medien-Dateien. Ersetzt das Sitzungstoken in Bild-URLs."""
    token, exp = issue_media_grant(int(session["profile_id"]), ttl_seconds=MEDIA_GRANT_TTL_SECONDS)
    return {
        "token": token,
        "expires_at": datetime.fromtimestamp(exp, tz=timezone.utc).isoformat(),
    }


@router.post("/forgot-password")
@limiter.limit("3/minute")
async def password_reset_request(req: PasswordResetRequest, request: Request):
    """Request password reset email."""
    email = req.email.lower().strip()
    with get_db() as conn:
        cur = get_cursor(conn)
        cur.execute("SELECT id, name FROM profiles WHERE email=%s", (email,))
        prof = cur.fetchone()
        if not prof:
            # Don't reveal if email exists
            return {"ok": True, "message": "Falls die E-Mail existiert, wurde ein Reset-Link gesendet."}
        revoke_pending_password_resets_for_profile(cur, prof["id"])
        raw_token = insert_password_reset_session(cur, prof["id"])

    reset_body = password_reset_email_body(
        recipient_name=prof.get("name"),
        token=raw_token,
        intro="Du hast einen Passwort-Reset angefordert.",
    )
    if not send_email(email, "Passwort zurücksetzen – Shinkan Jinkendo", reset_body):
        print("[SMTP] Passwort-Reset konnte nicht gesendet werden (SMTP prüfen).")

    return {"ok": True, "message": "Falls die E-Mail existiert, wurde ein Reset-Link gesendet."}


@router.post("/reset-password")
def password_reset_confirm(req: PasswordResetConfirm):
    """Confirm password reset with token."""
    with get_db() as conn:
        cur = get_cursor(conn)
        cur.execute("SELECT profile_id FROM sessions WHERE token=%s AND expires_at > CURRENT_TIMESTAMP",
            (f"reset_{req.token}",))
        sess = cur.fetchone()
        if not sess:
            raise HTTPException(400, "Ungültiger oder abgelaufener Reset-Link")

        pid = sess["profile_id"]
        new_hash = hash_pin(req.new_password)
        cur.execute(
            """
            UPDATE profiles
            SET pin_hash=%s, failed_login_count=0, locked_until=NULL
            WHERE id=%s
            """,
            (new_hash, pid),
        )
        revoke_profile_sessions(cur, int(pid))

    return {"ok": True, "message": "Passwort erfolgreich zurückgesetzt"}

# ── Helpers (Registrierung / E-Mail-Links) ─────────────────────────────────────

def _public_app_base() -> str:
    return (os.getenv("APP_URL") or "https://shinkan.jinkendo.de").rstrip("/")


def _verification_link_expired(expires_at) -> bool:
    """
    True, wenn Ablaufzeit in der Vergangenheit liegt.
    PG liefert TIMESTAMP oft als naive datetime — Vergleich mit timezone-aware UTC
    würde sonst TypeError → 500.
    """
    if expires_at is None:
        return False
    deadline = expires_at.replace(tzinfo=timezone.utc) if expires_at.tzinfo is None else expires_at.astimezone(timezone.utc)
    return datetime.now(timezone.utc) > deadline


def verification_link(token: str) -> str:
    """Link zur Web-App (`/verify?token=`); die SPA ruft wie bei Mitai die API auf."""
    return f"{_public_app_base()}/verify?token={quote(token, safe='')}"


def registration_role(cur, email_lower: str) -> str:
    """
    bootstrap: erste Registrierung in leerer DB → superadmin,
    oder E-Mail ∈ ADMIN_BOOTSTRAP_EMAILS (kommasepariert, Groß/Klein egal).

    superadmin deckt alle Portal-Rechte ab (inkl. löschen / andere Super-Admins).
    Um alle Self-Regs als Trainer zu haben: AUTO_ADMIN_FIRST_USER=false und keine ADMIN_BOOTSTRAP_EMAILS.
    """
    bootstrap = {
        x.strip().lower()
        for x in os.getenv("ADMIN_BOOTSTRAP_EMAILS", "").split(",")
        if x.strip()
    }
    if email_lower in bootstrap:
        return "superadmin"
    if os.getenv("AUTO_ADMIN_FIRST_USER", "true").strip().lower() not in ("1", "true", "yes"):
        return "trainer"
    cur.execute("SELECT COUNT(*) AS n FROM profiles")
    row = cur.fetchone()
    try:
        n = int(row["n"]) if row is not None else 0
    except (KeyError, TypeError, ValueError):
        n = 0
    return "superadmin" if n == 0 else "trainer"


# ── Helper: Send Email ────────────────────────────────────────────────────────
def send_email(to_email: str, subject: str, body: str):
    """Send mail via SMTP. Port 465 → SSL; sonst SMTP + optional STARTTLS (587)."""
    try:
        smtp_host = (os.getenv("SMTP_HOST") or "").strip()
        smtp_port_raw = os.getenv("SMTP_PORT") or "587"
        try:
            smtp_port = int(str(smtp_port_raw).strip())
        except ValueError:
            smtp_port = 587
        smtp_user = (os.getenv("SMTP_USER") or "").strip()
        smtp_pass = os.getenv("SMTP_PASS")
        smtp_from = os.getenv("SMTP_FROM", "noreply@jinkendo.de")

        if not smtp_host or not smtp_user or smtp_pass is None or smtp_pass == "":
            print("[SMTP] Nicht konfiguriert — setze SMTP_HOST, SMTP_USER, SMTP_PASS (und ggf. SMTP_PORT)")
            return False

        msg = MIMEText(body)
        msg["Subject"] = subject
        msg["From"] = smtp_from
        msg["To"] = to_email

        use_tls_env = (os.getenv("SMTP_STARTTLS", "true").strip().lower() not in ("0", "false", "no"))
        force_ssl = (os.getenv("SMTP_SSL", "").strip().lower() in ("1", "true", "yes")) or smtp_port == 465

        ctx = ssl.create_default_context()

        if force_ssl:
            print(f"[SMTP] Versand (SSL) an …@{to_email.split('@')[-1]} über {smtp_host}:{smtp_port}")
            with smtplib.SMTP_SSL(smtp_host, smtp_port, context=ctx, timeout=30) as server:
                server.login(smtp_user, smtp_pass)
                server.send_message(msg)
        else:
            print(f"[SMTP] Versand (STARTTLS={use_tls_env}) an …@{to_email.split('@')[-1]} über {smtp_host}:{smtp_port}")
            with smtplib.SMTP(smtp_host, smtp_port, timeout=30) as server:
                server.ehlo()
                if use_tls_env:
                    server.starttls(context=ctx)
                    server.ehlo()
                server.login(smtp_user, smtp_pass)
                server.send_message(msg)

        print("[SMTP] Nachricht erfolgreich akzeptiert (Server-Queue)")
        return True
    except Exception as e:
        print(f"[SMTP] Fehler: {e}")
        return False


# ── Registration Endpoints ────────────────────────────────────────────────────
@router.post("/register")
@limiter.limit("3/hour")
async def register(req: RegisterRequest, request: Request):
    """Self-registration with email verification."""
    email = req.email.lower().strip()
    name = req.name.strip()
    password = req.password

    # Validation
    if not email or '@' not in email:
        raise HTTPException(400, "Ungültige E-Mail-Adresse")
    if len(password) < 8:
        raise HTTPException(400, "Passwort muss mindestens 8 Zeichen lang sein")
    if not name or len(name) < 2:
        raise HTTPException(400, "Name muss mindestens 2 Zeichen lang sein")

    with get_db() as conn:
        cur = get_cursor(conn)

        cur.execute("SELECT id, name FROM profiles WHERE email=%s", (email,))
        existing = cur.fetchone()
        if existing:
            existing_name = existing.get("name")
        else:
            existing_name = None

        if existing is None:
            # Generate verification token
            verification_token = secrets.token_urlsafe(32)
            verification_expires = datetime.now(timezone.utc) + timedelta(hours=24)

            # Rolle: erster Nutzer oder ADMIN_BOOTSTRAP_EMAILS → superadmin
            role = registration_role(cur, email)

            # Create profile (inactive until verified) — profiles.id ist SERIAL (INT), keine String-IDs einfügen.
            pin_hash = hash_pin(password)
            trial_ends = datetime.now(timezone.utc) + timedelta(days=14)  # 14-day trial

            cur.execute("""
                INSERT INTO profiles (
                    name, email, pin_hash, auth_type, role, tier,
                    email_verified, verification_token, verification_expires,
                    trial_ends_at, created_at
                ) VALUES (%s, %s, %s, 'email', %s, 'free', FALSE, %s, %s, %s, CURRENT_TIMESTAMP)
                RETURNING id
            """, (name, email, pin_hash, role, verification_token, verification_expires, trial_ends))
            new_profile_id = cur.fetchone()["id"]

            req_club = req.requested_club_id
            if req_club is not None:
                cur.execute(
                    "SELECT id FROM clubs WHERE id = %s AND status = 'active'",
                    (int(req_club),),
                )
                if cur.fetchone():
                    cur.execute(
                        """
                        SELECT id FROM club_membership_requests
                        WHERE profile_id = %s AND club_id = %s AND status = 'pending'
                        LIMIT 1
                        """,
                        (new_profile_id, int(req_club)),
                    )
                    if not cur.fetchone():
                        cur.execute(
                            """
                            INSERT INTO club_membership_requests (profile_id, club_id, status, message)
                            VALUES (%s, %s, 'pending', NULL)
                            """,
                            (new_profile_id, int(req_club)),
                        )

    generic = {
        "ok": True,
        "message": "Registrierung erfolgreich! Bitte prüfe dein E-Mail-Postfach und bestätige deine E-Mail-Adresse.",
    }
    if existing is not None:
        send_email(
            email,
            "Shinkan Jinkendo – Registrierung",
            f"""Hallo {existing_name or name},

für diese E-Mail-Adresse gibt es bereits ein Konto. Es wurde kein neues Passwort gesetzt.
Wenn du den Zugang brauchst, melde dich an oder fordere ein neues Passwort an.

Dein Shinkan Jinkendo Team
""",
        )
        return generic

    verify_url = verification_link(verification_token)

    email_body = f"""Hallo {name},

willkommen bei Shinkan Jinkendo!

Bitte bestätige deine E-Mail-Adresse, um die Registrierung abzuschließen:

{verify_url}

Der Link ist 24 Stunden gültig.

Dein Shinkan Jinkendo Team
"""

    if not send_email(email, "Shinkan Jinkendo – E-Mail bestätigen", email_body):
        print("[SMTP] Verifizierungs-Mail konnte nicht gesendet werden — Logs prüfen, SMTP_* in der Laufzeitumgebung.")

    return generic


@router.get("/verify/{token}")
async def verify_email(token: str):
    """Verify email address and activate account."""
    with get_db() as conn:
        cur = get_cursor(conn)

        # Find profile with this verification token
        cur.execute("""
            SELECT id, name, email, email_verified, verification_expires
            FROM profiles
            WHERE verification_token=%s
        """, (token,))

        prof = cur.fetchone()

        if not prof:
            # Token not found - might be already used/verified
            # Check if there's a verified profile (token was deleted after verification)
            raise HTTPException(400, "Verifikations-Link ungültig oder bereits verwendet. Falls du bereits verifiziert bist, melde dich einfach an.")

        if prof['email_verified']:
            raise HTTPException(400, "E-Mail-Adresse bereits bestätigt")

        # Check if token expired
        if _verification_link_expired(prof["verification_expires"]):
            raise HTTPException(400, "Verifikations-Link abgelaufen. Bitte registriere dich erneut.")

        # Mark as verified and clear token
        cur.execute("""
            UPDATE profiles
            SET email_verified=TRUE, verification_token=NULL, verification_expires=NULL
            WHERE id=%s
        """, (prof['id'],))

        # Create session (auto-login after verification)
        session_token = make_token()
        expires = datetime.now(timezone.utc) + timedelta(days=30)
        cur.execute("""
            INSERT INTO sessions (token, profile_id, expires_at, created_at)
            VALUES (%s, %s, %s, CURRENT_TIMESTAMP)
        """, (session_token, prof['id'], expires))

    return {
        "ok": True,
        "message": "E-Mail-Adresse erfolgreich bestätigt!",
        "token": session_token,
        "profile": {
            "id": prof['id'],
            "name": prof['name'],
            "email": prof['email']
        }
    }


@router.post("/resend-verification")
@limiter.limit("3/hour")
async def resend_verification(req: ResendVerificationRequest, request: Request):
    """Bestätigung erneut senden. Bekannte und unbekannte Adressen antworten gleich."""
    email = str(req.email).lower().strip()
    generic = {
        "ok": True,
        "message": "Falls ein Account mit dieser E-Mail existiert, wurde eine Bestätigungs-E-Mail versendet.",
    }

    with get_db() as conn:
        cur = get_cursor(conn)
        cur.execute("""
            SELECT id, name, email, email_verified, verification_token, verification_expires
            FROM profiles
            WHERE email=%s
        """, (email,))
        prof = cur.fetchone()
        if not prof or prof["email_verified"]:
            return generic

        verification_token = secrets.token_urlsafe(32)
        verification_expires = datetime.now(timezone.utc) + timedelta(hours=24)
        cur.execute("""
            UPDATE profiles
            SET verification_token=%s, verification_expires=%s
            WHERE id=%s
        """, (verification_token, verification_expires, prof["id"]))

    verify_url = verification_link(verification_token)
    email_body = f"""Hallo {prof['name']},

du hast eine neue Bestätigungs-E-Mail angefordert.

Bitte bestätige deine E-Mail-Adresse mit diesem Link:

{verify_url}

Dieser Link ist 24 Stunden gültig.

Dein Shinkan Jinkendo Team
"""
    send_email(email, "Shinkan Jinkendo – E-Mail bestätigen", email_body)
    return generic