"""
SQLCipher utility functions for consistent database operations.

This module centralizes all SQLCipher-specific operations to ensure
consistent password handling and PRAGMA settings across the codebase.
"""

import os
import secrets
import threading
import time
from hashlib import pbkdf2_hmac
from pathlib import Path
from typing import Any, Dict, Optional, Union

from loguru import logger

from ..settings.env_registry import get_env_setting
from ..utilities.type_utils import to_bool

# Lock to protect cipher_default_* global state during creation
_cipher_default_lock = threading.Lock()

# Salt file constants
SALT_FILE_SUFFIX = ".salt"
SALT_SIZE = 32  # 256 bits


def get_salt_file_path(db_path: Union[str, Path]) -> Path:
    """
    Get the path to the salt file for a database.

    Args:
        db_path: Path to the database file

    Returns:
        Path to the corresponding .salt file
    """
    return Path(db_path).with_suffix(Path(db_path).suffix + SALT_FILE_SUFFIX)


def get_salt_for_database(db_path: Union[str, Path]) -> bytes:
    """
    Get the salt for a database file.

    For new databases (v2+): reads from the .salt file alongside the database.
    For legacy databases (v1): returns LEGACY_PBKDF2_SALT for backwards compatibility.

    Args:
        db_path: Path to the database file

    Returns:
        The salt bytes to use for key derivation
    """
    salt_file = get_salt_file_path(db_path)

    try:
        salt = salt_file.read_bytes()
    except FileNotFoundError:
        # v1: Legacy salt for backwards compatibility
        logger.warning(
            f"Database '{Path(db_path).name}' uses the legacy shared salt "
            f"(deprecated). Consider creating a new database to benefit from "
            f"per-database salt security. See issue #1439 for migration details."
        )
        return LEGACY_PBKDF2_SALT

    # v2: Per-database random salt
    if len(salt) != SALT_SIZE:
        raise ValueError(
            f"Salt file {salt_file} has unexpected size ({len(salt)} bytes), "
            f"expected {SALT_SIZE}. The salt file may be corrupted."
        )
    return salt


def create_database_salt(db_path: Union[str, Path]) -> bytes:
    """
    Create and store a new random salt for a database.

    This should be called when creating a new database.
    The salt is stored in a .salt file alongside the database.

    WARNING: If this salt file is deleted, the associated database becomes
    permanently unreadable. Always back up .salt files alongside their .db files.

    Args:
        db_path: Path to the database file

    Returns:
        The newly generated salt bytes

    Raises:
        FileExistsError: If a salt file already exists for this database
    """
    salt_file = get_salt_file_path(db_path)

    if salt_file.exists():
        raise FileExistsError(
            f"Salt file already exists: {salt_file}. "
            f"Refusing to overwrite to prevent data loss."
        )

    salt = secrets.token_bytes(SALT_SIZE)

    # Ensure parent directory exists
    salt_file.parent.mkdir(parents=True, exist_ok=True)

    # Write salt file with owner-only permissions (0o600)
    fd = os.open(str(salt_file), os.O_WRONLY | os.O_CREAT | os.O_EXCL, 0o600)
    try:
        os.write(fd, salt)
    finally:
        os.close(fd)

    logger.info(f"Created new database salt file: {salt_file}")
    return salt


def has_per_database_salt(db_path: Union[str, Path]) -> bool:
    """
    Check if a database has a per-database salt file (v2).

    Args:
        db_path: Path to the database file

    Returns:
        True if the database has a .salt file, False otherwise
    """
    return get_salt_file_path(db_path).exists()


def _get_key_from_password(
    password: str, salt: bytes, kdf_iterations: int
) -> bytes:
    """
    Generates an encryption key from the user's password and salt.

    Args:
        password: The password.
        salt: The salt bytes to use for key derivation.
        kdf_iterations: Number of PBKDF2 iterations.

    Returns:
        The generated key.
    """
    logger.debug(
        f"Generating DB encryption key with {kdf_iterations} iterations..."
    )

    start = time.perf_counter()
    key = pbkdf2_hmac(
        "sha512",
        password.encode(),
        salt,
        kdf_iterations,
    )
    elapsed_ms = (time.perf_counter() - start) * 1000

    if elapsed_ms > 500:
        logger.info(
            f"PBKDF2 key derivation took {elapsed_ms:.0f}ms "
            f"({kdf_iterations} iterations)"
        )
    else:
        logger.debug(
            f"PBKDF2 key derivation took {elapsed_ms:.0f}ms "
            f"({kdf_iterations} iterations)"
        )
    return key


def get_key_from_password(
    password: str, db_path: Optional[Union[str, Path]] = None
) -> bytes:
    """
    Wrapper that gets salt and settings, then calls the key derivation.

    Args:
        password: The password.
        db_path: Optional path to the database file. If provided, uses
                 per-database salt. If not provided, uses legacy salt.

    Returns:
        The derived encryption key bytes.
    """
    if db_path is not None:
        salt = get_salt_for_database(db_path)
    else:
        salt = LEGACY_PBKDF2_SALT

    settings = get_sqlcipher_settings()
    return _get_key_from_password(password, salt, settings["kdf_iterations"])


def set_sqlcipher_key(
    cursor_or_conn: Any,
    password: str,
    db_path: Optional[Union[str, Path]] = None,
) -> None:
    """
    Set the SQLCipher encryption key using hexadecimal encoding.

    This avoids SQL injection and escaping issues with special characters.

    Args:
        cursor_or_conn: SQLCipher cursor or connection object
        password: The password to use for encryption
        db_path: Optional path to the database file. If provided, uses
                 per-database salt. If not provided, uses legacy salt.
    """
    key = get_key_from_password(password, db_path=db_path)  # gitleaks:allow
    cursor_or_conn.execute(f"PRAGMA key = \"x'{key.hex()}'\"")


def set_sqlcipher_key_from_hex(cursor_or_conn: Any, hex_key: str) -> None:
    """
    Set the SQLCipher encryption key from a pre-derived hex key string.

    Used by connection closures to avoid capturing plaintext passwords.

    Args:
        cursor_or_conn: SQLCipher cursor or connection object
        hex_key: Pre-derived hex key string (from get_key_from_password().hex())
    """
    cursor_or_conn.execute(f"PRAGMA key = \"x'{hex_key}'\"")  # gitleaks:allow


def set_sqlcipher_rekey(
    cursor_or_conn: Any,
    new_password: str,
    db_path: Optional[Union[str, Path]] = None,
) -> None:
    """
    Change the SQLCipher encryption key using hexadecimal encoding.

    Uses the same PBKDF2 key derivation as set_sqlcipher_key() to ensure
    consistency when re-opening databases after password change.

    Args:
        cursor_or_conn: SQLCipher cursor or connection object
        new_password: The new password to use for encryption
        db_path: Optional path to the database file. If provided, uses
                 per-database salt. If not provided, uses legacy salt.
    """
    # Use the same key derivation as set_sqlcipher_key for consistency
    key = get_key_from_password(new_password, db_path=db_path)  # gitleaks:allow

    # The hex encoding already prevents injection since it only contains [0-9a-f]
    safe_sql = f"PRAGMA rekey = \"x'{key.hex()}'\""

    try:
        # Try SQLAlchemy connection (needs text() wrapper)
        from sqlalchemy import text

        cursor_or_conn.execute(text(safe_sql))
    except TypeError:
        # Raw SQLCipher connection - use string directly
        cursor_or_conn.execute(safe_sql)


# Default SQLCipher configuration (can be overridden by settings)
DEFAULT_KDF_ITERATIONS = 256000
DEFAULT_PAGE_SIZE = 16384  # 16KB pages for maximum performance with caching
DEFAULT_HMAC_ALGORITHM = "HMAC_SHA512"
DEFAULT_KDF_ALGORITHM = "PBKDF2_HMAC_SHA512"

# Valid page sizes (powers of 2 within the SQLite range).
# IntegerSetting validates min/max but not that the value is a power of 2,
# so we check against this set as an additional safeguard.
VALID_PAGE_SIZES = frozenset({512, 1024, 2048, 4096, 8192, 16384, 32768, 65536})
MAX_KDF_ITERATIONS = 1_000_000

# Production minimum KDF iterations. Relaxed automatically in test/CI environments.
MIN_KDF_ITERATIONS_PRODUCTION = 100_000
MIN_KDF_ITERATIONS_TESTING = 1


def _get_min_kdf_iterations() -> int:
    """Get minimum KDF iterations, relaxed for test/CI environments.

    Only relaxes when PYTEST_CURRENT_TEST (set automatically by pytest) or
    LDR_TEST_MODE (project-specific) is set. Generic env vars like CI or
    TESTING are NOT checked to avoid accidentally weakening production
    encryption in Docker/CD pipelines that set CI=true.

    PYTEST_CURRENT_TEST is presence-based: pytest sets it to a descriptive
    non-boolean string (e.g. "tests/foo.py::test_bar (call)"), so its mere
    presence signals a test run. LDR_TEST_MODE is parsed as a proper boolean
    so an explicit LDR_TEST_MODE=0 / =false does NOT relax the floor — a bare
    truthiness check would treat any non-empty string (including "false") as
    enabled and silently weaken encryption for someone trying to disable it.
    """
    is_testing = bool(os.environ.get("PYTEST_CURRENT_TEST")) or to_bool(
        os.environ.get("LDR_TEST_MODE")
    )
    return (
        MIN_KDF_ITERATIONS_TESTING
        if is_testing
        else MIN_KDF_ITERATIONS_PRODUCTION
    )


# Legacy salt for backwards compatibility with databases created before v2.
# New databases use per-database random salts stored in .salt files.
# WARNING: Do NOT change this value - it would break all existing legacy databases!
LEGACY_PBKDF2_SALT = b"no salt"

# Alias for backwards compatibility with code that references the old name
PBKDF2_PLACEHOLDER_SALT = LEGACY_PBKDF2_SALT


def get_sqlcipher_settings() -> dict:
    """
    Get SQLCipher settings from environment variables or use defaults.

    These settings cannot be changed after database creation, so they
    must be configured via environment variables only.

    Settings are read via the env settings registry, which handles
    canonical env var names (LDR_DB_CONFIG_*) with automatic fallback
    to deprecated names (LDR_DB_*) and deprecation warnings.

    Returns:
        Dictionary with SQLCipher configuration
    """
    # HMAC algorithm - registry validates against allowed values
    hmac_algorithm = get_env_setting(
        "db_config.hmac_algorithm", DEFAULT_HMAC_ALGORITHM
    )

    # KDF algorithm - registry validates against allowed values
    kdf_algorithm = get_env_setting(
        "db_config.kdf_algorithm", DEFAULT_KDF_ALGORITHM
    )

    # Page size - registry validates range, we also check power-of-2
    page_size = get_env_setting("db_config.page_size", DEFAULT_PAGE_SIZE)
    if page_size not in VALID_PAGE_SIZES:
        logger.warning(
            f"Invalid page_size value '{page_size}', using default "
            f"'{DEFAULT_PAGE_SIZE}'. Valid values: {sorted(VALID_PAGE_SIZES)}"
        )
        page_size = DEFAULT_PAGE_SIZE

    # KDF iterations - registry validates basic range, then apply CI-aware minimum
    kdf_iterations = get_env_setting(
        "db_config.kdf_iterations", DEFAULT_KDF_ITERATIONS
    )
    min_kdf = _get_min_kdf_iterations()
    if not (min_kdf <= kdf_iterations <= MAX_KDF_ITERATIONS):
        logger.warning(
            f"KDF iterations value '{kdf_iterations}' outside safe range "
            f"[{min_kdf}, {MAX_KDF_ITERATIONS}], using default "
            f"'{DEFAULT_KDF_ITERATIONS}'."
        )
        kdf_iterations = DEFAULT_KDF_ITERATIONS

    return {
        "kdf_iterations": kdf_iterations,
        "page_size": page_size,
        "hmac_algorithm": hmac_algorithm,
        "kdf_algorithm": kdf_algorithm,
    }


def warn_if_weak_kdf_with_existing_databases(
    data_dir: Union[str, Path],
) -> bool:
    """Warn loudly when the effective SQLCipher KDF is below the production
    floor *and* user databases already exist in ``data_dir``.

    The KDF iteration count is derived from the environment at open time
    (see :func:`get_sqlcipher_settings`) and is NOT stored with the
    database. So if the floor is relaxed (test mode) on a deployment that
    already holds real data, newly created databases get a weak at-rest work
    factor, and — separately — any existing database that was created at a
    *higher* KDF can no longer be opened: its on-disk key is unchanged, but
    the server now derives a different, weaker key, so decryption fails and
    login returns a generic "Invalid username or password" 401 that is
    indistinguishable from a wrong password (the KDF-mismatch symptom class
    behind PR #4775).

    Scope: this only catches the "server is now weak" direction, and only a
    *sub-floor* effective KDF. It cannot detect the inverse (a production-KDF
    server opening databases created weak) or general in-range KDF drift,
    because the creation-time KDF is not persisted to compare against — only
    a sub-floor effective KDF is observable from the environment alone.

    Intentionally silent on a fresh deployment (no pre-existing databases →
    nothing to mismatch) and when the effective KDF is at/above the floor
    (e.g. the 256000 default) — that is harmless.

    Returns ``True`` if a warning was emitted (used by tests).
    """
    effective_kdf = get_sqlcipher_settings()["kdf_iterations"]
    if effective_kdf >= MIN_KDF_ITERATIONS_PRODUCTION:
        return False

    # Only count + truthiness are used, so no need to sort the glob result.
    existing = list(Path(data_dir).glob("ldr_user_*.db"))
    if not existing:
        return False

    logger.warning(
        "SQLCipher KDF is configured to {} iterations — below the production "
        "floor of {} (reached only in test mode, e.g. LDR_TEST_MODE) — while "
        "{} user database(s) already exist in {}. New databases created now "
        "get this weak at-rest work factor. Separately, any of those existing "
        "databases that was created at a higher KDF can no longer be opened: "
        "its on-disk key is unchanged, but the server now derives a different, "
        "weaker key, so decryption fails and login returns a generic 'Invalid "
        "username or password' 401 for every affected user (the KDF-mismatch "
        "symptom class behind PR #4775). If this is a production deployment, "
        "unset LDR_TEST_MODE (and any low LDR_DB_CONFIG_KDF_ITERATIONS).",
        effective_kdf,
        MIN_KDF_ITERATIONS_PRODUCTION,
        len(existing),
        data_dir,
    )
    return True


def apply_cipher_defaults_before_key(
    cursor_or_conn: Any,
) -> None:
    """
    Apply cipher_default_* pragmas BEFORE PRAGMA key for new database creation.

    Per SQLCipher 4.x docs, cipher_default_* pragmas set the defaults that
    apply when a key is set on a NEW database. These MUST be called before
    PRAGMA key.

    For EXISTING databases, cipher_page_size/cipher_hmac_algorithm/
    cipher_kdf_algorithm are set AFTER the key via apply_sqlcipher_pragmas().

    Args:
        cursor_or_conn: SQLCipher cursor or connection object
    """
    settings = get_sqlcipher_settings()

    logger.debug(
        f"Applying cipher_default_* pragmas for new DB: settings={settings}"
    )

    cursor_or_conn.execute(
        f"PRAGMA cipher_default_page_size = {settings['page_size']}"
    )
    cursor_or_conn.execute(
        f"PRAGMA cipher_default_hmac_algorithm = {settings['hmac_algorithm']}"
    )
    cursor_or_conn.execute(
        f"PRAGMA cipher_default_kdf_algorithm = {settings['kdf_algorithm']}"
    )


def apply_sqlcipher_pragmas(
    cursor_or_conn: Any,
    creation_mode: bool = False,
) -> None:
    """
    Apply SQLCipher PRAGMA settings that are set AFTER the key.

    For SQLCipher 4.x:
    - New databases: cipher_default_* are set before key via
      apply_cipher_defaults_before_key(). This function only sets kdf_iter.
    - Existing databases: cipher_page_size, cipher_hmac_algorithm,
      cipher_kdf_algorithm MUST be set AFTER the key (not before).
      This function handles that.

    Args:
        cursor_or_conn: SQLCipher cursor or connection object
        creation_mode: If True, only sets kdf_iter (defaults already applied).
                      If False, sets cipher_* settings + kdf_iter for existing DB.
    """
    settings = get_sqlcipher_settings()

    if not creation_mode:
        # For existing databases: cipher_* pragmas go AFTER the key
        cursor_or_conn.execute(
            f"PRAGMA cipher_page_size = {settings['page_size']}"
        )
        cursor_or_conn.execute(
            f"PRAGMA cipher_hmac_algorithm = {settings['hmac_algorithm']}"
        )
        cursor_or_conn.execute(
            f"PRAGMA cipher_kdf_algorithm = {settings['kdf_algorithm']}"
        )

    # kdf_iter can be set after the key (applies to future derivation)
    cursor_or_conn.execute(f"PRAGMA kdf_iter = {settings['kdf_iterations']}")

    # cipher_memory_security is a runtime PRAGMA. ON zeroes SQLCipher buffers
    # and calls mlock() to prevent swap; OFF skips this. Defaulting to OFF
    # because the password already sits unprotected in Flask session, db_manager,
    # and thread-local storage — mlock on SQLCipher's buffers alone doesn't help.
    # Users can opt in with LDR_DB_CONFIG_CIPHER_MEMORY_SECURITY=ON + IPC_LOCK.
    # Applied on every connection (not just creation) so env var overrides work.
    mem_security = get_env_setting("db_config.cipher_memory_security", "OFF")
    cursor_or_conn.execute(f"PRAGMA cipher_memory_security = {mem_security}")


def apply_performance_pragmas(cursor_or_conn: Any) -> None:
    """
    Apply performance-related PRAGMA settings from environment variables.

    Settings are read via the env settings registry, which handles
    canonical env var names (LDR_DB_CONFIG_*) with automatic fallback
    to deprecated names (LDR_DB_*) and deprecation warnings.

    Args:
        cursor_or_conn: SQLCipher cursor or connection object
    """
    # Default values that are always applied
    cursor_or_conn.execute("PRAGMA temp_store = MEMORY")
    cursor_or_conn.execute("PRAGMA busy_timeout = 10000")  # 10 second timeout

    # SQLite defaults foreign_keys to OFF — without this every
    # ondelete="CASCADE"/"SET NULL" declared on an FK is inert,
    # including for raw-SQL DELETEs issued by Query.delete().
    cursor_or_conn.execute("PRAGMA foreign_keys = ON")

    # Cache size - registry validates min/max range
    cache_mb = get_env_setting("db_config.cache_size_mb", 64)
    cache_pages = -(cache_mb * 1024)  # Negative for KB cache size
    cursor_or_conn.execute(f"PRAGMA cache_size = {cache_pages}")

    # Journal mode - registry validates against allowed values
    journal_mode = get_env_setting("db_config.journal_mode", "WAL")
    cursor_or_conn.execute(f"PRAGMA journal_mode = {journal_mode}")

    # Synchronous mode - registry validates against allowed values
    sync_mode = get_env_setting("db_config.synchronous", "NORMAL")
    cursor_or_conn.execute(f"PRAGMA synchronous = {sync_mode}")

    # WAL autocheckpoint frame threshold. SQLite's default of 1000 frames
    # paired with our 16 KB page size means the WAL can grow to ~16 MB
    # before SQLite triggers a PASSIVE checkpoint at commit. Lowering the
    # threshold bounds the WAL high-water-mark on disk for users who never
    # log out (the explicit TRUNCATE checkpoint runs on dispose, not here).
    wal_autocheckpoint = get_env_setting("db_config.wal_autocheckpoint", 250)
    cursor_or_conn.execute(f"PRAGMA wal_autocheckpoint = {wal_autocheckpoint}")


def verify_sqlcipher_connection(cursor_or_conn: Any) -> bool:
    """
    Verify that the SQLCipher connection is working correctly.

    Args:
        cursor_or_conn: SQLCipher cursor or connection object

    Returns:
        True if the connection is valid, False otherwise
    """
    try:
        cursor_or_conn.execute("SELECT 1")
        result = (
            cursor_or_conn.fetchone()
            if hasattr(cursor_or_conn, "fetchone")
            else cursor_or_conn.execute("SELECT 1").fetchone()
        )
        is_valid = result == (1,)
        if not is_valid:
            logger.error(
                f"SQLCipher verification failed: result {result} != (1,)"
            )
        return is_valid
    except Exception:
        logger.exception("SQLCipher verification failed")
        return False


def get_sqlcipher_version(cursor_or_conn: Any) -> Optional[str]:
    """
    Get the SQLCipher version string.

    Args:
        cursor_or_conn: SQLCipher cursor or connection object

    Returns:
        Version string (e.g. "4.6.1 community") or None if unavailable
    """
    try:
        cursor_or_conn.execute("PRAGMA cipher_version")
        result = cursor_or_conn.fetchone()
        return result[0] if result else None
    except Exception:
        logger.debug("Could not query SQLCipher version", exc_info=True)
        return None


def create_sqlcipher_connection(
    db_path: Union[str, Path],
    password: Optional[str] = None,
    creation_mode: bool = False,
    connect_kwargs: Optional[Dict[str, Any]] = None,
    hex_key: Optional[str] = None,
) -> Any:
    """
    Create a properly configured SQLCipher connection.

    Implements the full PRAGMA sequence with proper error cleanup:
    - Creation: cipher_default_* -> key -> kdf_iter -> performance -> verify
    - Existing: key -> cipher_* + kdf_iter -> performance -> verify

    Uses per-database salt if a .salt file exists alongside the database,
    otherwise falls back to legacy salt for backwards compatibility.

    Args:
        db_path: Path to the database file
        password: The password for encryption (mutually exclusive with hex_key)
        creation_mode: If True, set cipher_default_* before key (new DB)
        connect_kwargs: Extra kwargs passed to sqlcipher3.connect()
        hex_key: Pre-derived hex key (skips PBKDF2 derivation)

    Returns:
        SQLCipher connection object

    Raises:
        ImportError: If sqlcipher3 is not available
        ValueError: If the connection cannot be established
    """
    from .sqlcipher_compat import get_sqlcipher_module

    try:
        sqlcipher3 = get_sqlcipher_module()
    except ImportError:
        raise ImportError(
            "sqlcipher3 is not available for encrypted databases. "
            "Ensure SQLCipher system library is installed, then run: pdm install"
        )

    conn = sqlcipher3.connect(str(db_path), **(connect_kwargs or {}))
    try:
        cursor = conn.cursor()

        if creation_mode:
            with _cipher_default_lock:
                apply_cipher_defaults_before_key(cursor)

        # Set encryption key (uses per-database salt when password + db_path)
        if hex_key:
            set_sqlcipher_key_from_hex(cursor, hex_key)
        elif password:
            set_sqlcipher_key(cursor, password, db_path=db_path)
        else:
            raise ValueError("Either password or hex_key must be provided")  # noqa: TRY301 — except does connection cleanup before re-raise

        # Apply post-key pragmas (cipher_* for existing, kdf_iter for both)
        apply_sqlcipher_pragmas(cursor, creation_mode=creation_mode)

        # Apply performance settings
        apply_performance_pragmas(cursor)

        # Verify connection works
        if not verify_sqlcipher_connection(cursor):
            raise ValueError(  # noqa: TRY301 — except does connection cleanup before re-raise
                "Failed to establish encrypted database connection"
            )

        cursor.close()
        return conn
    except Exception:
        from ..utilities.resource_utils import safe_close

        safe_close(conn, "SQLCipher connection")
        raise


# Backwards compatibility alias — old name still importable
apply_cipher_settings_before_key = apply_cipher_defaults_before_key
