db_ops.py

"""
Database operations: authentication, CRUD for all entities, user management,
model preferences, and schema migrations.
"""

import base64
import json
import logging
import os
from datetime import datetime

import bcrypt

from config import PROVIDERS
from crypto_utils import KeyWrapper, crypto
from db_models import (
    Base,
    ChatHistory,
    CustomPrompt,
    DocumentTranslation,
    GeneratedImage,
    SessionLocal,
    Transcription,
    User,
    UserModelPreference,
    UserSettings,
    VisionResult,
    engine,
)

logger = logging.getLogger(__name__)
key_wrapper = KeyWrapper()


def get_db() -> "SessionLocal":  # type: ignore[valid-type]
    """Get database session."""
    return SessionLocal()


def hash_password(password: str) -> str:
    """Hash a password using bcrypt"""
    return bcrypt.hashpw(password.encode("utf-8"), bcrypt.gensalt()).decode("utf-8")


def verify_password(password: str, hashed: str) -> bool:
    """Verify a password against its hash"""
    return bcrypt.checkpw(password.encode("utf-8"), hashed.encode("utf-8"))


# ── Login throttle (per-username, in-memory) ───────────────────────────────
# Sliding window: max 5 failures in 15 minutes locks the account for 15 min.
# In-memory only — clears on app restart. For multi-process deploys, replace
# with Redis. The lockout uses the *username*, not IP, because the request
# layer doesn't surface IP into this function. Add IP gating at the proxy.
import threading as _login_threading
import time as _login_time

_LOGIN_FAILS: dict[str, list[float]] = {}
_LOGIN_LOCKED: dict[str, float] = {}
_LOGIN_LOCK = _login_threading.Lock()
_LOGIN_WINDOW = 15 * 60  # 15 minutes
_LOGIN_MAX_FAILS = 5
_LOGIN_LOCKOUT = 15 * 60  # 15 minutes


def _login_is_locked(username: str) -> bool:
    now = _login_time.time()
    with _LOGIN_LOCK:
        until = _LOGIN_LOCKED.get(username, 0.0)
        if until and until > now:
            return True
        if until and until <= now:
            _LOGIN_LOCKED.pop(username, None)
        return False


def _login_record_failure(username: str) -> None:
    now = _login_time.time()
    cutoff = now - _LOGIN_WINDOW
    with _LOGIN_LOCK:
        fails = [t for t in _LOGIN_FAILS.get(username, []) if t >= cutoff]
        fails.append(now)
        _LOGIN_FAILS[username] = fails
        if len(fails) >= _LOGIN_MAX_FAILS:
            _LOGIN_LOCKED[username] = now + _LOGIN_LOCKOUT
            _LOGIN_FAILS[username] = []
            logger.warning(
                f"[SECURITY] Account '{username}' locked for {_LOGIN_LOCKOUT // 60} min "
                f"after {_LOGIN_MAX_FAILS} failed login attempts"
            )


def _login_record_success(username: str) -> None:
    with _LOGIN_LOCK:
        _LOGIN_FAILS.pop(username, None)
        _LOGIN_LOCKED.pop(username, None)


def get_user_by_username(username: str) -> User | None:
    """Look up a user by username. Returns the User ORM object or None."""
    db = get_db()
    try:
        return db.query(User).filter(User.username == username).first()
    finally:
        db.close()


def get_verified_user_id(user_state: dict | None) -> int | None:
    """Re-read user from DB by session user_id and return the verified ID.

    Use this instead of raw ``user_state["id"]`` in security-sensitive
    operations (delete, decrypt, export, exchange creds) to prevent IDOR
    attacks via tampered session state.

    Returns the user ID if the user exists and is active, else None.
    """
    if not user_state or not user_state.get("id"):
        return None
    db = get_db()
    try:
        user = db.query(User).filter(User.id == user_state["id"]).first()
        if user is None:
            logger.warning(
                "[SECURITY] get_verified_user_id: user_id=%s not found in DB",
                user_state.get("id"),
            )
            return None
        return user.id
    finally:
        db.close()


import threading as _threading

# Server-side UMK cache — populated during gradio_auth_check, consumed
# by demo.load() session init. Keyed by username → umk bytes.
# Thread-safe via lock. Entries persist for the process lifetime
# (acceptable — same as the previous localStorage approach, but
# server-side instead of client-side).
_UMK_CACHE: dict[str, bytes] = {}
_UMK_LOCK = _threading.Lock()


def gradio_auth_check(username: str, password: str) -> bool:
    """Auth callback for Gradio's auth= parameter.

    Returns True if credentials are valid, False otherwise.
    Also caches the derived UMK for use in demo.load() session init.
    """
    user, umk = authenticate_user(username, password)
    if user is not None:
        if umk:
            with _UMK_LOCK:
                _UMK_CACHE[username] = umk
        return True
    return False


def get_cached_umk(username: str) -> bytes | None:
    """Retrieve the UMK cached during login. Returns bytes or None."""
    with _UMK_LOCK:
        return _UMK_CACHE.get(username)


def clear_cached_umk(username: str) -> None:
    """Remove the cached UMK for *username* on logout."""
    with _UMK_LOCK:
        _UMK_CACHE.pop(username, None)


def authenticate_user(username: str, password: str) -> tuple:
    """
    Authenticate user AND create keychain if needed.
    Returns a 'SafeUser' object that works after DB close.
    Includes per-username rate-limit / lockout to slow online brute force.
    """
    # Reject obviously invalid usernames before hitting the DB
    ok, _ = validate_username(username)
    if not ok:
        logger.warning("[SECURITY] Login refused — invalid username format: %r", username)
        return None, None
    if _login_is_locked(username):
        logger.warning(f"[SECURITY] Login refused — '{username}' is locked out")
        return None, None
    db = get_db()
    try:
        logger.info(f"🔐 AUTH ATTEMPT: User '{username}'")

        user = db.query(User).filter(User.username == username).first()

        if not user:
            logger.error(f"❌ AUTH FAIL: User '{username}' not found in DB")
            _login_record_failure(username)
            return None, None

        if not verify_password(password, user.password_hash):
            logger.error(f"❌ AUTH FAIL: Password hash mismatch for '{username}'")
            _login_record_failure(username)
            return None, None

        logger.info(f"✅ Password verified for '{username}'. Unlocking keychain...")

        # 1. Migration Logic (User has no keys yet)
        if not user.salt or not user.encrypted_master_key:
            logger.info(f"🔄 Migrating user {username} to per-user encryption...")
            try:
                keychain = key_wrapper.create_user_keychain(password)

                # Wrap the GLOBAL key (legacy support)
                from cryptography.fernet import Fernet

                salt_bytes = base64.b64decode(keychain["salt"])
                wrapper_key = key_wrapper.derive_wrapper_key(password, salt_bytes)
                f = Fernet(wrapper_key)
                encrypted_umk = f.encrypt(crypto.global_key)

                user.salt = keychain["salt"]
                user.encrypted_master_key = encrypted_umk.decode("utf-8")
                db.commit()

                umk = crypto.global_key
            except Exception as e:
                logger.error(f"Migration failed: {e}")
                return None, None
        else:
            # 2. Normal Unlock
            try:
                umk = key_wrapper.unlock_user_keychain(
                    password, user.salt, user.encrypted_master_key
                )
            except Exception as e:
                logger.error(f"❌ KEYCHAIN ERROR: {e}")
                umk = None

        if not umk:
            logger.error(f"❌ AUTH FAIL: Could not decrypt Master Key for '{username}'")
            return None, None

        logger.info(f"🔓 Login successful for '{username}'")
        _login_record_success(username)

        # 3. Create safe object
        class SafeUser:
            pass

        safe_user = SafeUser()
        safe_user.id = user.id
        safe_user.username = user.username
        safe_user.is_admin = user.is_admin
        safe_user.is_media_manager = getattr(user, "is_media_manager", False)
        safe_user.has_sandbox_access = getattr(user, "has_sandbox_access", False)

        return safe_user, umk

    except Exception as e:
        logger.exception(f"🔥 Auth Critical Error: {e}")
        return None, None
    finally:
        db.close()


def create_default_users() -> None:
    """
    Bootstrap a single admin account on a fresh database.

    Security model:
      - No hardcoded credentials in source.
      - Admin username can be supplied via $BOOTSTRAP_ADMIN_USERNAME (default: 'admin').
      - Password is read from $BOOTSTRAP_ADMIN_PASSWORD if set, otherwise a 24-char
        random password is generated and printed to stdout exactly once.
      - The bootstrap user is flagged `must_change_password=True` so the first
        login is forced to rotate. (App must enforce that flag.)
      - Idempotent: if any users already exist, this function does nothing.
    """
    db = SessionLocal()
    try:
        if db.query(User).count() > 0:
            logger.info("Users already exist, skipping bootstrap")
            return

        import secrets as _secrets
        import string as _string

        admin_username = os.environ.get("BOOTSTRAP_ADMIN_USERNAME", "admin").strip()
        admin_password = os.environ.get("BOOTSTRAP_ADMIN_PASSWORD")
        generated = False
        if not admin_password:
            alphabet = _string.ascii_letters + _string.digits + "!@#$%^&*-_=+"
            admin_password = "".join(_secrets.choice(alphabet) for _ in range(24))
            generated = True

        admin = User(
            username=admin_username,
            password_hash=hash_password(admin_password),
            email=os.environ.get("BOOTSTRAP_ADMIN_EMAIL") or None,
            is_admin=True,
        )
        # Force password rotation on first login if the column exists.
        if hasattr(admin, "must_change_password"):
            admin.must_change_password = True
        db.add(admin)
        db.commit()

        if generated:
            # Single-line marker so the operator can grep this once and rotate it.
            print(
                "\n" + "=" * 64 + f"\n[BOOTSTRAP] Created admin user '{admin_username}'."
                f"\n[BOOTSTRAP] Initial password (rotate immediately): {admin_password}"
                "\n[BOOTSTRAP] This password will NOT be shown again."
                "\n" + "=" * 64 + "\n",
                flush=True,
            )
        logger.info(f"✅ Bootstrap admin user '{admin_username}' created")
    except Exception as e:
        db.rollback()
        logger.exception(f"Error creating bootstrap admin: {e!s}")
    finally:
        db.close()


# ── Transition migrations (run idempotently at every startup) ────────────────
# These exist so a deployment that ran the OLD insecure code can be upgraded
# in place without operator surgery. Each step is idempotent and safe to run
# repeatedly.

# Usernames that the OLD `create_default_users` shipped with publicly known
# passwords. ANY user matching one of these gets `must_change_password=True`
# set automatically on startup so the next login forces a rotation.
LEGACY_DEFAULT_USERNAMES = ("admin123", "user123")


def run_security_transition_migrations():
    """
    Idempotent post-deploy hardening for an existing database.

    1. Ensures the `must_change_password` column exists on `users`.
       (`migrate_db.py` also handles this; we duplicate defensively because
       this function may run before the migration script is executed.)
    2. Force-flags any account whose username matches the historic default
       (`admin123`, `user123`) so the operator cannot accidentally keep
       running with the publicly-known credentials.
    3. Logs a warning if `crypto_utils._APP_DIR/.master_key` exists on disk
       and the env var `MASTER_ENCRYPTION_KEY` is *not* set, telling the
       operator how to migrate to the env-var path. The on-disk key keeps
       working until they do so — no service interruption.
    4. Logs a warning if any legacy `*.pickle` token files are still present.
    """
    # 1. Ensure must_change_password column exists.
    try:
        from sqlalchemy import inspect as _inspect
        from sqlalchemy import text as _text

        from db_models import engine as _engine

        _insp = _inspect(_engine)
        if "users" in _insp.get_table_names():
            cols = [c["name"] for c in _insp.get_columns("users")]
            if "must_change_password" not in cols:
                with _engine.begin() as _conn:
                    _conn.execute(
                        _text("ALTER TABLE users ADD COLUMN must_change_password BOOLEAN DEFAULT 0")
                    )
                logger.info("[TRANSITION] added users.must_change_password column")
    except Exception as e:
        logger.warning(f"[TRANSITION] could not ensure must_change_password column: {e}")

    # 2. Flag legacy default users for forced password rotation.
    db = SessionLocal()
    try:
        flagged = 0
        for legacy_name in LEGACY_DEFAULT_USERNAMES:
            u = db.query(User).filter(User.username == legacy_name).first()
            if not u:
                continue
            if not getattr(u, "must_change_password", False):
                u.must_change_password = True
                flagged += 1
                logger.warning(
                    f"[TRANSITION] Legacy default user '{legacy_name}' detected — "
                    f"flagged must_change_password=True. ROTATE THIS PASSWORD NOW."
                )
        if flagged:
            db.commit()
    except Exception as e:
        db.rollback()
        logger.warning(f"[TRANSITION] could not flag legacy users: {e}")
    finally:
        db.close()

    # 3. Master key on disk → recommend env-var migration.
    try:
        from crypto_utils import _APP_DIR as _CRYPTO_DIR

        _key_file = os.path.join(_CRYPTO_DIR, ".master_key")
        if os.path.exists(_key_file) and not os.environ.get("MASTER_ENCRYPTION_KEY"):
            logger.warning(
                "[TRANSITION] .master_key file is present at %s. The new code "
                "still reads it for backward compatibility, but you should "
                "migrate to the MASTER_ENCRYPTION_KEY env var. "
                "Run `python migrate_master_key.py` to print the env-var "
                "value, add it to the systemd EnvironmentFile, then "
                "delete the .master_key file.",
                _key_file,
            )
    except Exception as e:
        logger.debug(f"[TRANSITION] master-key check skipped: {e}")

    # 4. Legacy pickle tokens.
    try:
        import glob as _glob
        from pathlib import Path as _Path

        _here = _Path(__file__).parent
        _stale = list(_glob.glob(str(_here / "yt_token_*.pickle")))
        if _stale:
            logger.warning(
                f"[TRANSITION] {len(_stale)} legacy yt_token_*.pickle file(s) "
                f"found. The new YouTube toolkit will NOT load them (RCE risk "
                f"via pickle). Re-authenticate and delete these files: {_stale}"
            )
    except Exception:  # nosec B110
        pass


def get_decrypted_transcription(trans_id: int, user_id: int, user_state: dict | None = None) -> dict | None:
    """Load and decrypt using user session key"""
    db = SessionLocal()
    try:
        trans = (
            db.query(Transcription)
            .filter(Transcription.id == trans_id, Transcription.user_id == user_id)
            .first()
        )

        if not trans:
            return None

        # GET KEY
        umk = user_state.get("umk") if user_state else crypto.global_key

        if trans.is_encrypted:
            try:
                # Try decrypting with Session Key
                decrypted = crypto.decrypt_text(str(trans.original_text), key=umk)

                # Check validity (AES-GCM auth tag will fail if key is wrong)
                if decrypted == "[Decryption Failed]" and umk != crypto.global_key:
                    # Fallback: Data might be old (encrypted with global key)
                    decrypted = crypto.decrypt_text(str(trans.original_text), key=crypto.global_key)

                trans.original_text = decrypted  # type: ignore[assignment]

                # 4. Handle Translation Text (same logic)
                if trans.translated_text:
                    decrypted_trans = crypto.decrypt_text(str(trans.translated_text), key=umk)
                    if decrypted_trans == "[Decryption Failed]" and umk != crypto.global_key:
                        decrypted_trans = crypto.decrypt_text(
                            str(trans.translated_text), key=crypto.global_key
                        )
                    trans.translated_text = decrypted_trans  # type: ignore[assignment]

            except Exception as e:
                logger.error(f"Decryption failed: {e}")
                trans.original_text = "[Fehler: Daten konnten nicht entschlüsselt werden]"  # type: ignore[assignment]

        return trans
    finally:
        db.close()


def get_decrypted_chat(chat_id: int, user_id: int, user_state: dict | None = None) -> dict | None:
    db = SessionLocal()
    try:
        chat = (
            db.query(ChatHistory)
            .filter(ChatHistory.id == chat_id, ChatHistory.user_id == user_id)
            .first()
        )
        if not chat:
            return None

        # GET KEY
        umk = user_state.get("umk") if user_state else None

        if chat.is_encrypted:
            # Try decrypting with Session Key
            try:
                # 2. Try decrypting with User Key first
                decrypted_json = crypto.decrypt_text(str(chat.messages), key=umk)

                # 3. If that failed (returns specific error string), try Global Key
                # This handles legacy data migration automatically
                if decrypted_json == "[Decryption Failed]" and umk != crypto.global_key:
                    # Fallback: Try global key (for data created before migration)
                    decrypted_json = crypto.decrypt_text(str(chat.messages), key=crypto.global_key)

                chat.messages = decrypted_json  # type: ignore[assignment]
            except Exception:
                chat.messages = "[]"  # type: ignore[assignment]

        return chat
    finally:
        db.close()


def change_password_secure(user_id: int, old_password: str, new_password: str) -> tuple[bool, str]:
    """Changes password and re-wraps the Master Key without touching data"""
    db = SessionLocal()
    try:
        user = db.query(User).filter(User.id == user_id).first()
        if not user:
            return False, "User missing"

        # Verify old password hash first
        if not verify_password(old_password, user.password_hash):
            return False, "Altes Passwort falsch"

        # RE-WRAP MAGIC
        if user.salt and user.encrypted_master_key:
            try:
                new_keychain = key_wrapper.rewrap_keychain(
                    old_password, new_password, user.salt, user.encrypted_master_key
                )

                user.salt = new_keychain["salt"]
                user.encrypted_master_key = new_keychain["encrypted_master_key"]
            except ValueError:
                return False, "Kryptographischer Fehler beim Umschlüsseln."
        else:
            # Legacy user migration happens here if they change password!
            # If user has no keychain yet, create one using the Global Key
            # (Assuming existing data is currently encrypted with Global Key)
            pass

        # Standard password update
        user.password_hash = hash_password(new_password)
        # Clear the must_change_password flag after successful rotation
        if hasattr(user, "must_change_password"):
            user.must_change_password = False
        db.commit()
        return True, "✅ Passwort geändert & Key neu verschlüsselt (Daten bleiben sicher)"
    except Exception as e:
        db.rollback()
        return False, f"Fehler: {e}"
    finally:
        db.close()


def save_chat_history(
    user_id: int,
    provider: str,
    model: str,
    messages: list,
    title: str | None = None,
    user_state: dict | None = None,
) -> int | None:
    """Save chat conversation to database. Returns chat ID or None."""
    db = SessionLocal()
    try:
        # Grab the key from the session state
        umk = user_state.get("umk") if user_state else crypto.global_key

        messages_json = json.dumps(messages)
        encrypted_messages = crypto.encrypt_text(messages_json, key=umk)

        chat = ChatHistory(
            user_id=user_id,
            provider=provider,
            model=model,
            messages=encrypted_messages,
            title=title or f"Chat {datetime.now().strftime('%Y-%m-%d %H:%M')}",
            is_encrypted=True,
            encryption_metadata=json.dumps({"algorithm": "AES-256-GCM-UserKey", "version": 2}),
        )
        db.add(chat)
        db.commit()
        db.refresh(chat)  # Refresh to get the ID
        chat_id = chat.id  # Get ID BEFORE closing session
        return chat_id
    except Exception as e:
        db.rollback()
        logger.exception(f"Error saving chat history: {e!s}")
        raise
    finally:
        db.close()


def generate_ai_label(provider: str, model: str) -> str:
    """Generate standardized label for AI-generated content"""
    timestamp = datetime.now().strftime("%Y-%m-%d %H:%M")
    compliance = (PROVIDERS.get(provider) or {}).get("badge", "Unbekannt")  # type: ignore[attr-defined]

    return f"""
---
**🤖 KI-Generiert**
- **Provider:** {provider} ({compliance})
- **Modell:** {model}
- **Datum:** {timestamp}

*Dieser Inhalt wurde mit KI-Unterstützung erstellt und durch Menschen geprüft.*
---
"""


def save_transcription(
    user_id: int,
    provider: str,
    model: str,
    original: str,
    translated: str | None = None,
    language: str | None = None,
    filename: str | None = None,
    title: str | None = None,
    user_state: dict | None = None,
):
    db = SessionLocal()
    try:
        # GET KEY FROM SESSION
        umk = user_state.get("umk") if user_state else None
        if not umk:
            # Fallback for system operations if needed, but risky
            logger.warning("No user session key found, falling back to global")
            umk = crypto.global_key

        encrypted_original = crypto.encrypt_text(original, key=umk) if original else None
        encrypted_translated = crypto.encrypt_text(translated, key=umk) if translated else None

        trans = Transcription(
            user_id=user_id,
            provider=provider,
            model=model or "N/A",
            original_text=encrypted_original,
            translated_text=encrypted_translated,
            language=language,
            filename=filename,
            title=title or f"Transkript {datetime.now().strftime('%Y-%m-%d %H:%M')}",
            is_encrypted=True,
            encryption_metadata=json.dumps({"algorithm": "AES-256-GCM", "version": 2}),
        )
        db.add(trans)
        db.commit()
        return trans.id
    except Exception as e:
        db.rollback()
        logger.exception(f"Error saving transcription: {e!s}")
        raise
    finally:
        db.close()


def get_decrypted_vision(vision_id: int, user_id: int, user_state=None):
    """Load and decrypt vision result"""
    db = SessionLocal()
    try:
        vis = (
            db.query(VisionResult)
            .filter(VisionResult.id == vision_id, VisionResult.user_id == user_id)
            .first()
        )

        if not vis:
            return None

        # Get key from session
        umk = user_state.get("umk") if user_state else crypto.global_key

        if vis.is_encrypted:
            try:
                # Decrypt with user key, fallback to global
                orig_result = str(vis.result)
                vis.result = crypto.decrypt_text(orig_result, key=umk)  # type: ignore[assignment]
                if vis.result == "[Decryption Failed]" and umk != crypto.global_key:
                    vis.result = crypto.decrypt_text(orig_result, key=crypto.global_key)  # type: ignore[assignment]

                if vis.prompt:
                    orig_prompt = str(vis.prompt)
                    vis.prompt = crypto.decrypt_text(orig_prompt, key=umk)  # type: ignore[assignment]
                    if vis.prompt == "[Decryption Failed]" and umk != crypto.global_key:
                        vis.prompt = crypto.decrypt_text(orig_prompt, key=crypto.global_key)  # type: ignore[assignment]
            except Exception as e:
                logger.error(f"Vision decryption failed: {e}")
                vis.result = "[Fehler: Entschlüsselung fehlgeschlagen]"  # type: ignore[assignment]

        return vis
    finally:
        db.close()


def save_vision_result(
    user_id: int,
    provider: str,
    model: str,
    prompt: str,
    result: str,
    image_path: str | None = None,
    user_state=None,
):
    """Save vision analysis to database"""
    db = SessionLocal()
    try:
        # Get Key
        umk = user_state.get("umk") if user_state else crypto.global_key

        # Encrypt result and prompt manually before creating object
        enc_result = crypto.encrypt_text(result, key=umk)
        enc_prompt = crypto.encrypt_text(prompt, key=umk)

        vision = VisionResult(
            user_id=user_id,
            provider=provider,
            model=model,
            prompt=enc_prompt,
            result=enc_result,
            is_encrypted=True,
            image_path=image_path,
            encryption_metadata=json.dumps({"algorithm": "AES-256-GCM-UserKey", "version": 2}),
        )
        db.add(vision)
        db.commit()
        db.refresh(vision)
        vision_id = vision.id
        return vision_id
    except Exception as e:
        db.rollback()
        logger.exception(f"Error saving vision result: {e!s}")
        raise
    finally:
        db.close()


def save_generated_image(
    user_id: int, provider: str, model: str, prompt: str, image_path: str, user_state=None
):
    """Save generated image to database"""
    db = SessionLocal()
    try:
        umk = user_state.get("umk") if user_state else crypto.global_key
        enc_prompt = crypto.encrypt_text(prompt, key=umk)
        img = GeneratedImage(
            user_id=user_id,
            provider=provider,
            model=model,
            prompt=enc_prompt,
            is_encrypted=True,
            image_path=image_path,
        )
        db.add(img)
        db.commit()
        db.refresh(img)
        img_id = img.id
        return img_id
    except Exception as e:
        db.rollback()
        logger.exception(f"Error saving generated image: {e!s}")
        raise
    finally:
        db.close()


def get_user_transcriptions(user_id: int, limit: int = 50):
    """Get user's transcription history"""
    db = get_db()
    results = (
        db.query(Transcription)
        .filter(Transcription.user_id == user_id)
        .order_by(Transcription.timestamp.desc())
        .limit(limit)
        .all()
    )
    db.close()
    return results


def get_user_custom_prompts(user_id: int, category: str | None = None):
    """Get user's custom prompts"""
    db = get_db()
    query = db.query(CustomPrompt).filter(CustomPrompt.user_id == user_id)
    if category:
        query = query.filter(CustomPrompt.category == category)
    results = query.order_by(CustomPrompt.timestamp.desc()).all()
    db.close()
    return results


def save_custom_prompt(
    user_id: int, name: str, prompt_text: str, category: str = "general", is_shared: bool = False
):
    """Save a custom prompt template"""
    db = SessionLocal()
    try:
        prompt = CustomPrompt(
            user_id=user_id,
            name=name,
            category=category,
            prompt_text=prompt_text,
            is_shared=is_shared,
        )
        db.add(prompt)
        db.commit()
        db.refresh(prompt)
        prompt_id = prompt.id
        return prompt_id
    except Exception as e:
        db.rollback()
        logger.exception(f"Error saving custom prompt: {e!s}")
        raise
    finally:
        db.close()


# Add after existing database functions


def delete_transcription(trans_id: int, user_id: int):
    """Delete a transcription safely"""
    db = get_db()
    try:
        trans = (
            db.query(Transcription)
            .filter(Transcription.id == trans_id, Transcription.user_id == user_id)
            .first()
        )
        if trans:
            db.delete(trans)
            db.commit()
            return True
        return False
    except Exception as e:
        db.rollback()
        logger.exception(f"DB Error: {e}")
        return False
    finally:
        db.close()


def delete_chat_history(chat_id: int, user_id: int):
    """Delete a chat history safely"""
    db = get_db()
    try:
        chat = (
            db.query(ChatHistory)
            .filter(ChatHistory.id == chat_id, ChatHistory.user_id == user_id)
            .first()
        )
        if chat:
            db.delete(chat)
            db.commit()
            return True
        return False
    except Exception as e:
        db.rollback()
        logger.exception(f"DB Error: {e}")
        return False
    finally:
        db.close()


def delete_vision_result(vision_id: int, user_id: int):
    """Delete a vision result safely"""
    db = get_db()
    try:
        vision = (
            db.query(VisionResult)
            .filter(VisionResult.id == vision_id, VisionResult.user_id == user_id)
            .first()
        )
        if vision:
            db.delete(vision)
            db.commit()
            return True
        return False
    except Exception as e:
        db.rollback()
        logger.exception(f"DB Error: {e}")
        return False
    finally:
        db.close()


def get_user_settings(user_id: int):
    """Get or create user settings"""
    db = get_db()
    try:
        settings = db.query(UserSettings).filter(UserSettings.user_id == user_id).first()

        if not settings:
            # Create default settings
            settings = UserSettings(user_id=user_id)
            db.add(settings)
            db.commit()
            db.refresh(settings)

        return settings
    finally:
        db.close()


def update_user_settings(user_id: int, **kwargs):
    """Update user settings"""
    db = SessionLocal()
    try:
        settings = db.query(UserSettings).filter(UserSettings.user_id == user_id).first()

        if not settings:
            settings = UserSettings(user_id=user_id)
            db.add(settings)

        # Update provided fields
        for key, value in kwargs.items():
            if hasattr(settings, key):
                setattr(settings, key, value)

        settings.updated_at = datetime.utcnow()  # type: ignore[assignment]
        db.commit()

        return True, "✅ Einstellungen gespeichert"
    except Exception as e:
        db.rollback()
        logger.exception(f"Error updating settings: {e}")
        return False, f"🔥 Fehler: {e!s}"
    finally:
        db.close()


def get_tool_preferences(user_id: int) -> dict:
    """Load tool preferences for a user. Returns defaults if not set."""
    from tools import DEFAULT_TOOLS_STANDARD

    _defaults = {
        "enabled_tools": DEFAULT_TOOLS_STANDARD,
        "search_backend": "tavily",
        "image_provider": "auto",  # "auto", "Nebius", "BFL"
        "num_results": 5,
        "max_result_chars": 6000,
    }
    try:
        settings = get_user_settings(user_id)
        raw = getattr(settings, "tool_preferences_json", None)
        if raw:
            stored = json.loads(raw)
            _defaults.update(stored)
    except Exception as e:
        logger.warning(f"get_tool_preferences failed for user {user_id}: {e}")
    return _defaults


def save_tool_preferences(user_id: int, prefs: dict) -> tuple[bool, str]:
    """Persist tool preferences for a user."""
    try:
        return update_user_settings(user_id, tool_preferences_json=json.dumps(prefs))
    except Exception as e:
        return False, f"🔥 Fehler: {e}"


def delete_generated_image(img_id: int, user_id: int):
    """Delete a generated image safely"""
    db = get_db()
    try:
        img = (
            db.query(GeneratedImage)
            .filter(GeneratedImage.id == img_id, GeneratedImage.user_id == user_id)
            .first()
        )
        if img:
            if img.image_path and os.path.exists(img.image_path):
                try:
                    os.remove(img.image_path)
                except Exception:
                    pass  # nosec
            db.delete(img)
            db.commit()
            return True
        return False
    except Exception as e:
        db.rollback()
        logger.exception(f"DB Error: {e}")
        return False
    finally:
        db.close()


def delete_custom_prompt(prompt_id: int, user_id: int):
    """Delete a custom prompt"""
    db = get_db()
    prompt = (
        db.query(CustomPrompt)
        .filter(CustomPrompt.id == prompt_id, CustomPrompt.user_id == user_id)
        .first()
    )
    if prompt:
        db.delete(prompt)
        db.commit()
        db.close()
        return True
    db.close()
    return False


def get_user_chat_history(user_id: int, limit: int = 50):
    """Get user's chat history"""
    db = get_db()
    results = (
        db.query(ChatHistory)
        .filter(ChatHistory.user_id == user_id)
        .order_by(ChatHistory.timestamp.desc())
        .limit(limit)
        .all()
    )
    db.close()
    return results


def get_user_vision_results(user_id: int, limit: int = 50):
    """Get user's vision results"""
    db = get_db()
    results = (
        db.query(VisionResult)
        .filter(VisionResult.user_id == user_id)
        .order_by(VisionResult.timestamp.desc())
        .limit(limit)
        .all()
    )
    db.close()
    return results


def get_user_generated_images(user_id: int, limit: int = 50):
    """Get user's generated images"""
    db = get_db()
    results = (
        db.query(GeneratedImage)
        .filter(GeneratedImage.user_id == user_id)
        .order_by(GeneratedImage.timestamp.desc())
        .limit(limit)
        .all()
    )
    db.close()
    return results


def ensure_database_schema():
    """Ensure database schema is up to date"""
    try:
        # This creates tables that don't exist
        Base.metadata.create_all(bind=engine)

        # Check for UserSettings table specifically
        from sqlalchemy import inspect as _inspect

        _inspector = _inspect(engine)
        if "user_settings" not in _inspector.get_table_names():
            UserSettings.__table__.create(bind=engine, checkfirst=True)
            logger.info("✅ Created UserSettings table")
        else:
            logger.info("✅ Database schema is up to date")

    except Exception as e:
        logger.exception(f"Database schema check failed: {e}")


def migrate_add_media_manager_column():
    """Add is_media_manager column to existing users table"""
    try:
        from sqlalchemy import inspect, text

        inspector = inspect(engine)
        columns = [col["name"] for col in inspector.get_columns("users")]

        if "is_media_manager" not in columns:
            logger.info("Adding is_media_manager column to users table...")
            with engine.connect() as conn:
                conn.execute(
                    text("ALTER TABLE users ADD COLUMN is_media_manager BOOLEAN DEFAULT 0")
                )
                conn.commit()
            logger.info("✅ Migration complete")
        else:
            logger.info("is_media_manager column already exists")
    except Exception as e:
        logger.error(f"Migration failed: {e}")


def migrate_add_tool_preferences():
    """Add tool_preferences_json column to user_settings table (idempotent)."""
    try:
        from sqlalchemy import inspect, text

        inspector = inspect(engine)
        columns = [col["name"] for col in inspector.get_columns("user_settings")]
        if "tool_preferences_json" not in columns:
            logger.info("Adding tool_preferences_json to user_settings…")
            with engine.connect() as conn:
                conn.execute(
                    text("ALTER TABLE user_settings ADD COLUMN tool_preferences_json TEXT")
                )
                conn.commit()
            logger.info("✅ tool_preferences_json migration complete")
    except Exception as e:
        logger.error(f"tool_preferences_json migration failed: {e}")


def migrate_add_sandbox_access_column():
    """Add has_sandbox_access column to users table (idempotent)."""
    try:
        from sqlalchemy import inspect, text

        inspector = inspect(engine)
        columns = [col["name"] for col in inspector.get_columns("users")]
        if "has_sandbox_access" not in columns:
            logger.info("Adding has_sandbox_access to users table…")
            with engine.connect() as conn:
                conn.execute(
                    text("ALTER TABLE users ADD COLUMN has_sandbox_access BOOLEAN DEFAULT FALSE")
                )
                conn.commit()
            logger.info("✅ has_sandbox_access migration complete")
    except Exception as e:
        logger.error(f"has_sandbox_access migration failed: {e}")


def migrate_add_exchange_credentials_column():
    """Add exchange_credentials_json column to user_settings table (idempotent)."""
    try:
        from sqlalchemy import inspect, text

        inspector = inspect(engine)
        columns = [col["name"] for col in inspector.get_columns("user_settings")]
        if "exchange_credentials_json" not in columns:
            logger.info("Adding exchange_credentials_json to user_settings table…")
            with engine.connect() as conn:
                conn.execute(
                    text("ALTER TABLE user_settings ADD COLUMN exchange_credentials_json TEXT")
                )
                conn.commit()
            logger.info("✅ exchange_credentials_json migration complete")
    except Exception as e:
        logger.error(f"exchange_credentials_json migration failed: {e}")


def migrate_add_max_model_size():
    """Add max_model_size_b column to user_settings table (idempotent)."""
    try:
        from sqlalchemy import inspect, text

        inspector = inspect(engine)
        columns = [col["name"] for col in inspector.get_columns("user_settings")]
        if "max_model_size_b" not in columns:
            logger.info("Adding max_model_size_b to user_settings…")
            with engine.connect() as conn:
                conn.execute(
                    text("ALTER TABLE user_settings ADD COLUMN max_model_size_b REAL DEFAULT 2.0")
                )
                conn.commit()
            logger.info("✅ max_model_size_b migration complete")
    except Exception as e:
        logger.error(f"max_model_size_b migration failed: {e}")


# ---------------------------------------------------------------------------
# Exchange Credentials Helpers
# ---------------------------------------------------------------------------


def save_exchange_credentials(user_id: int, credentials: dict, umk: bytes):
    """Encrypt and save exchange credentials for a user."""
    db = SessionLocal()
    try:
        settings = db.query(UserSettings).filter(UserSettings.user_id == user_id).first()
        if not settings:
            settings = UserSettings(user_id=user_id)
            db.add(settings)

        # Serialize and encrypt
        json_data = json.dumps(credentials)
        encrypted_data = crypto.encrypt_text(json_data, key=umk)

        settings.exchange_credentials_json = encrypted_data  # type: ignore[assignment]
        settings.updated_at = datetime.utcnow()  # type: ignore[assignment]
        db.commit()
        return True, "✅ Exchange-Zugangsdaten sicher gespeichert"
    except Exception as e:
        db.rollback()
        logger.exception(f"Error saving exchange credentials: {e}")
        return False, f"🔥 Fehler beim Speichern: {e!s}"
    finally:
        db.close()


def get_exchange_credentials(user_id: int, umk: bytes) -> dict | None:
    """Retrieve and decrypt exchange credentials for a user."""
    db = SessionLocal()
    try:
        settings = db.query(UserSettings).filter(UserSettings.user_id == user_id).first()
        if not settings or not settings.exchange_credentials_json:
            return None

        # Decrypt and deserialize
        ex_creds_json_val = settings.exchange_credentials_json
        decrypted_json = crypto.decrypt_text(ex_creds_json_val, key=umk)  # type: ignore[arg-type]
        if decrypted_json == "[Decryption Failed]":
            logger.error(f"Decryption failed for user {user_id} exchange credentials")
            return None

        return json.loads(decrypted_json)
    except Exception as e:
        logger.exception(f"Error retrieving exchange credentials: {e}")
        return None
    finally:
        db.close()


# Auto-Migration
ensure_database_schema()
migrate_add_media_manager_column()
migrate_add_tool_preferences()
migrate_add_sandbox_access_column()
migrate_add_exchange_credentials_column()
migrate_add_max_model_size()
# Must run before create_default_users so the must_change_password column
# exists when SQLAlchemy queries the User model (which includes all columns).
run_security_transition_migrations()

# Initialize default users
create_default_users()


# ---------------------------------------------------------------------------
# User management
# ---------------------------------------------------------------------------

import re as _re

# Strict username rules: prevents path-traversal (../admin), NUL injection,
# and special-chars that could break filesystem paths or shell interpolation.
# 3–40 chars, lowercase letters, digits, dot, dash, underscore. No leading dot.
_USERNAME_RE = _re.compile(r"^[a-z0-9][a-z0-9._-]{2,39}$")


def validate_username(username: str) -> tuple[bool, str]:
    """Return (ok, error_msg). Apply at registration AND rename."""
    if not username:
        return False, "Username must not be empty"
    if not _USERNAME_RE.match(username):
        return False, (
            "Username must be 3–40 characters, start with a letter or digit, "
            "and contain only lowercase letters, digits, '.', '-', or '_'."
        )
    if ".." in username:
        return False, "Username must not contain '..'"
    return True, ""


def create_user(username: str, password: str, email: str, is_admin: bool = False) -> tuple[bool, str]:
    """Create a new user with their own Encryption Keychain"""
    ok, err = validate_username(username)
    if not ok:
        return False, f"❌ {err}"
    db = SessionLocal()
    try:
        if db.query(User).filter(User.username == username).first():
            return False, "❌ Benutzername existiert bereits"

        # 1. Generate Keychain (Returns: salt, encrypted_master_key, decrypted_master_key)
        keychain = key_wrapper.create_user_keychain(password)

        new_user = User(
            username=username,
            password_hash=hash_password(password),
            email=email,
            is_admin=is_admin,
            # Store the Lockbox
            salt=keychain["salt"],
            encrypted_master_key=keychain["encrypted_master_key"],
        )
        db.add(new_user)
        db.commit()
        return True, f"✅ Benutzer '{username}' erstellt (Secure Keychain Active)"
    except Exception as e:
        db.rollback()
        logger.exception(f"Error creating user: {e!s}")
        return False, f"🔥 Fehler: {e!s}"
    finally:
        db.close()


def delete_user(user_id: int, current_user_id: int) -> tuple[bool, str]:
    """Delete a user (cannot delete self)"""
    db = SessionLocal()
    try:
        if user_id == current_user_id:
            return False, "❌ Du kannst dich nicht selbst löschen"

        user = db.query(User).filter(User.id == user_id).first()
        if not user:
            return False, "❌ Benutzer nicht gefunden"

        username = user.username
        db.delete(user)
        db.commit()

        logger.info(f"User deleted: {username} (ID: {user_id})")
        return True, f"✅ Benutzer '{username}' gelöscht"
    except Exception as e:
        db.rollback()
        logger.exception(f"Error deleting user: {e!s}")
        return False, f"🔥 Fehler: {e!s}"
    finally:
        db.close()


def rename_user(user_id: int, new_username: str) -> tuple[bool, str]:
    """Rename a user"""
    ok, err = validate_username(new_username)
    if not ok:
        return False, f"❌ {err}"
    db = SessionLocal()
    try:
        # Check if new username already exists
        existing = db.query(User).filter(User.username == new_username).first()
        if existing and existing.id != user_id:
            return False, "❌ Benutzername existiert bereits"

        user = db.query(User).filter(User.id == user_id).first()
        if not user:
            return False, "❌ Benutzer nicht gefunden"

        old_username = user.username
        user.username = new_username
        db.commit()

        logger.info(f"User renamed: {old_username} → {new_username}")
        return True, f"✅ Benutzer umbenannt: '{old_username}' → '{new_username}'"

    except Exception as e:
        db.rollback()
        logger.exception(f"Error renaming user: {e!s}")
        return False, f"🔥 Fehler: {e!s}"
    finally:
        db.close()


def reset_user_password(user_id: int, new_password: str, admin_aware: bool = False) -> tuple[bool, str]:
    """Admin password reset - WARNING: User data will become inaccessible!"""
    if not admin_aware:
        return (
            False,
            "⚠️ WARNUNG: Passwort-Reset macht verschlüsselte Daten unzugänglich. Nutzer muss Daten neu erstellen.",
        )

    db = SessionLocal()
    try:
        user = db.query(User).filter(User.id == user_id).first()
        if not user:
            return False, "❌ Benutzer nicht gefunden"

        # Generate NEW keychain (old data becomes inaccessible)
        new_keychain = key_wrapper.create_user_keychain(new_password)

        user.password_hash = hash_password(new_password)
        user.salt = new_keychain["salt"]
        user.encrypted_master_key = new_keychain["encrypted_master_key"]

        db.commit()

        return True, "✅ Passwort zurückgesetzt. WICHTIG: Alte Daten sind nicht mehr zugänglich!"
    except Exception as e:
        db.rollback()
        logger.exception(f"Error resetting password: {e!s}")
        return False, f"🔥 Fehler: {e!s}"
    finally:
        db.close()


def toggle_admin_status(user_id: int, current_user_id: int) -> tuple[bool, str]:
    """Toggle admin status for a user (cannot change own status)"""
    db = SessionLocal()
    try:
        # Prevent changing own admin status
        if user_id == current_user_id:
            return False, "❌ Du kannst deinen eigenen Admin-Status nicht ändern"

        user = db.query(User).filter(User.id == user_id).first()
        if not user:
            return False, "❌ Benutzer nicht gefunden"

        # Toggle admin status
        user.is_admin = not user.is_admin
        db.commit()

        status = "Admin" if user.is_admin else "Normaler Benutzer"
        logger.info(f"Admin status changed for {user.username}: {status}")
        return True, f"✅ '{user.username}' ist jetzt: {status}"

    except Exception as e:
        db.rollback()
        logger.exception(f"Error toggling admin status: {e!s}")
        return False, f"🔥 Fehler: {e!s}"
    finally:
        db.close()


def toggle_media_manager_status(user_id: int, current_user_id: int) -> tuple[bool, str]:
    """Toggle media manager status for a user (only admins can do this)"""
    db = SessionLocal()
    try:
        # Prevent changing own media manager status
        if user_id == current_user_id:
            return False, "❌ Du kannst deinen eigenen Medienverwalter-Status nicht ändern"

        user = db.query(User).filter(User.id == user_id).first()
        if not user:
            return False, "❌ Benutzer nicht gefunden"

        # Toggle status
        user.is_media_manager = not user.is_media_manager
        db.commit()

        status = "Medienverwalter" if user.is_media_manager else "Normaler Benutzer"
        logger.info(f"Media manager status changed for {user.username}: {status}")
        return True, f"✅ '{user.username}' ist jetzt: {status}"

    except Exception as e:
        db.rollback()
        logger.exception(f"Error toggling media manager status: {e!s}")
        return False, f"🔥 Fehler: {e!s}"
    finally:
        db.close()


def toggle_sandbox_access_status(user_id: int, current_user_id: int) -> tuple[bool, str]:
    """Toggle sandbox access for a user (only admins can do this)."""
    db = SessionLocal()
    try:
        user = db.query(User).filter(User.id == user_id).first()
        if not user:
            return False, "❌ Benutzer nicht gefunden"
        user.has_sandbox_access = not getattr(user, "has_sandbox_access", False)
        db.commit()
        status = "Sandbox-Zugang" if user.has_sandbox_access else "Kein Sandbox-Zugang"
        logger.info(f"Sandbox access changed for {user.username}: {status}")
        return True, f"✅ '{user.username}' ist jetzt: {status}"
    except Exception as e:
        db.rollback()
        logger.exception(f"Error toggling sandbox access: {e!s}")
        return False, f"🔥 Fehler: {e!s}"
    finally:
        db.close()


def get_all_users() -> list[dict]:
    """Get all users for admin panel (updated for media manager column)"""
    db = SessionLocal()
    try:
        users = db.query(User).order_by(User.created_at.desc()).all()
        data = []
        for u in users:
            # 1. GENERATE ROLES STRING
            roles = []
            if u.is_admin:
                roles.append("👑 Admin")
            if u.is_media_manager:
                roles.append("🎬 Medienv.")
            if getattr(u, "has_sandbox_access", False):
                roles.append("🐍 Sandbox")
            if not roles:
                roles.append("👤 User")

            role_str = ", ".join(roles)

            data.append(
                [
                    u.id,
                    u.username,
                    u.email or "—",
                    role_str,  # UPDATED ROLE DISPLAY
                    u.created_at.strftime("%Y-%m-%d %H:%M"),
                ]
            )
        return data
    except Exception as e:
        logger.exception(f"Error getting users: {e!s}")
        return []
    finally:
        db.close()


def update_user_email(user_id: int, new_email: str) -> tuple[bool, str]:
    """Update user's email address"""
    db = SessionLocal()
    try:
        # Check if email already exists
        if new_email:
            existing = db.query(User).filter(User.email == new_email).first()
            if existing and existing.id != user_id:
                return False, "❌ E-Mail wird bereits verwendet"

        user = db.query(User).filter(User.id == user_id).first()
        if not user:
            return False, "❌ Benutzer nicht gefunden"

        user.email = new_email
        db.commit()

        logger.info(f"Email updated for user: {user.username}")
        return True, f"✅ E-Mail für '{user.username}' aktualisiert"

    except Exception as e:
        db.rollback()
        logger.exception(f"Error updating email: {e!s}")
        return False, f"🔥 Fehler: {e!s}"
    finally:
        db.close()


# ---------------------------------------------------------------------------
# Model preferences
# ---------------------------------------------------------------------------


def get_user_model_preferences(user_id: int, provider: str) -> list[dict]:
    """Get user's model preferences for a provider"""
    db = SessionLocal()
    try:
        prefs = (
            db.query(UserModelPreference)
            .filter(
                UserModelPreference.user_id == user_id, UserModelPreference.provider == provider
            )
            .order_by(UserModelPreference.display_order)
            .all()
        )

        return prefs
    except Exception as e:
        logger.exception(f"Error getting user model preferences: {e!s}")
        return []
    finally:
        db.close()


def save_user_model_preferences(user_id: int, provider: str, model_configs: list[dict]) -> tuple[bool, str]:
    """
    Save user's model preferences
    model_configs: list of dicts with keys: model_id, display_name, is_visible, display_order
    """
    db = SessionLocal()
    try:
        # Delete existing preferences for this provider
        db.query(UserModelPreference).filter(
            UserModelPreference.user_id == user_id, UserModelPreference.provider == provider
        ).delete()

        # Add new preferences
        for config in model_configs:
            pref = UserModelPreference(
                user_id=user_id,
                provider=provider,
                model_id=config["model_id"],
                display_name=config.get("display_name", config["model_id"]),
                display_order=config.get("display_order", 0),
                is_visible=config.get("is_visible", True),
            )
            db.add(pref)

        db.commit()
        logger.info(
            f"Saved {len(model_configs)} model preferences for user {user_id}, provider {provider}"
        )
        return True, "✅ Einstellungen gespeichert"

    except Exception as e:
        db.rollback()
        logger.exception(f"Error saving model preferences: {e!s}")
        return False, f"🔥 Fehler: {e!s}"
    finally:
        db.close()


def get_user_visible_models(user_id: int, provider: str) -> list[str]:
    """Get list of visible models for user, in order"""
    prefs = get_user_model_preferences(user_id, provider)

    if not prefs:
        # No preferences set, return all models from provider config
        provider_config = PROVIDERS.get(provider, {})
        return provider_config.get("chat_models", [])

    # Return visible models in order
    visible_models = [p.model_id for p in prefs if p.is_visible]
    return visible_models if visible_models else [prefs[0].model_id] if prefs else []


def get_default_model_for_user(user_id: int, provider: str) -> str | None:
    """Get user's default model (first in order)"""
    visible = get_user_visible_models(user_id, provider)
    return visible[0] if visible else None


def save_document_translation(
    user_id: int,
    source_file: str,
    target_file: str,
    src_lang: str,
    tgt_lang: str,
    mode: str,
    provider: str,
):
    """Save document translation record to database"""
    import os as _os

    db = SessionLocal()
    try:
        trans = DocumentTranslation(
            user_id=user_id,
            source_filename=_os.path.basename(source_file),
            target_filename=_os.path.basename(target_file),
            source_lang=src_lang,
            target_lang=tgt_lang,
            mode=mode,
            provider=provider,
        )
        db.add(trans)
        db.commit()
        db.refresh(trans)
        return trans.id
    except Exception as e:
        db.rollback()
        logger.exception(f"Error saving translation: {e}")
        raise
    finally:
        db.close()