learningcircuit--local-deep-research/tests/database/test_encrypted_db_manager.py

"""
Comprehensive tests for encrypted_db.py DatabaseManager.

Tests cover:
- Database creation and opening
- Password validation and changes
- User existence checks
- Database integrity verification
- Memory usage tracking
- Thread engine management
- Session creation
- Multi-user isolation
- Error scenarios
"""

import threading
import time
from unittest.mock import MagicMock, patch

import pytest


class TestDatabaseCreation:
    """Tests for create_user_database functionality."""

    @patch("local_deep_research.database.encrypted_db.get_data_directory")
    def test_create_database_with_valid_password(self, mock_data_dir, tmp_path):
        """Test database creation with a valid password."""
        from local_deep_research.database.encrypted_db import DatabaseManager

        mock_data_dir.return_value = tmp_path

        with patch.object(
            DatabaseManager, "_check_encryption_available", return_value=False
        ):
            with patch.dict("os.environ", {"LDR_ALLOW_UNENCRYPTED": "true"}):
                manager = DatabaseManager()

                # Mock the internal operations
                with patch.object(manager, "_get_user_db_path") as mock_path:
                    mock_db_path = tmp_path / "test_user.db"
                    mock_path.return_value = mock_db_path

                    with patch(
                        "local_deep_research.database.encrypted_db.create_engine"
                    ) as mock_engine:
                        mock_engine_instance = MagicMock()
                        mock_engine.return_value = mock_engine_instance

                        with patch(
                            "local_deep_research.database.encrypted_db.event"
                        ):
                            with patch(
                                "local_deep_research.database.initialize.initialize_database"
                            ):
                                engine = manager.create_user_database(
                                    "testuser", "validpassword"
                                )

                                assert engine is mock_engine_instance
                                assert "testuser" in manager.connections

    @patch("local_deep_research.database.encrypted_db.get_data_directory")
    def test_create_database_with_empty_password_raises(
        self, mock_data_dir, tmp_path
    ):
        """Test database creation fails with empty password."""
        from local_deep_research.database.encrypted_db import DatabaseManager

        mock_data_dir.return_value = tmp_path

        with patch.object(
            DatabaseManager, "_check_encryption_available", return_value=True
        ):
            manager = DatabaseManager()

            with pytest.raises(ValueError, match="Invalid encryption key"):
                manager.create_user_database("testuser", "")

    @patch("local_deep_research.database.encrypted_db.get_data_directory")
    def test_create_database_with_none_password_raises(
        self, mock_data_dir, tmp_path
    ):
        """Test database creation fails with None password."""
        from local_deep_research.database.encrypted_db import DatabaseManager

        mock_data_dir.return_value = tmp_path

        with patch.object(
            DatabaseManager, "_check_encryption_available", return_value=True
        ):
            manager = DatabaseManager()

            with pytest.raises(ValueError, match="Invalid encryption key"):
                manager.create_user_database("testuser", None)

    @patch("local_deep_research.database.encrypted_db.get_data_directory")
    def test_create_database_existing_user_raises(
        self, mock_data_dir, tmp_path
    ):
        """Test database creation fails if database already exists."""
        from local_deep_research.database.encrypted_db import DatabaseManager

        mock_data_dir.return_value = tmp_path

        # Create the database file
        db_file = tmp_path / "encrypted_databases" / "test_db.db"
        db_file.parent.mkdir(parents=True, exist_ok=True)
        db_file.touch()

        with patch.object(
            DatabaseManager, "_check_encryption_available", return_value=True
        ):
            manager = DatabaseManager()

            with patch.object(
                manager, "_get_user_db_path", return_value=db_file
            ):
                with pytest.raises(ValueError, match="already exists"):
                    manager.create_user_database("existinguser", "password")

    @patch("local_deep_research.database.encrypted_db.get_data_directory")
    def test_create_database_migration_failure_raises_and_cleans_up(
        self, mock_data_dir, tmp_path
    ):
        """A failure inside ``initialize_database`` must propagate, not swallow.

        Regression: the create path used to log the migration exception and
        return the engine anyway, leaving a partial DB on disk (tables but
        no alembic_version stamp). Every subsequent login then re-ran
        alembic, hit the same error, and (post-#3635) 503'd — the user
        could register but never log in. Now the failure surfaces, the
        engine is disposed, the partial DB file is removed, and nothing
        is cached in ``connections``.
        """
        from local_deep_research.database.encrypted_db import DatabaseManager

        mock_data_dir.return_value = tmp_path

        db_file = tmp_path / "encrypted_databases" / "newuser.db"
        db_file.parent.mkdir(parents=True, exist_ok=True)

        with patch.object(
            DatabaseManager, "_check_encryption_available", return_value=False
        ):
            with patch.dict("os.environ", {"LDR_ALLOW_UNENCRYPTED": "true"}):
                manager = DatabaseManager()

                with patch.object(
                    manager, "_get_user_db_path", return_value=db_file
                ):
                    # Simulate the encrypted-path structure-creation step
                    # having materialised the file before migrations run,
                    # so the cleanup branch has something to remove.
                    created_engines = []

                    def fake_engine(*args, **kwargs):
                        db_file.touch()
                        engine = MagicMock()
                        created_engines.append(engine)
                        return engine

                    with patch(
                        "local_deep_research.database.encrypted_db.create_engine",
                        side_effect=fake_engine,
                    ):
                        with patch(
                            "local_deep_research.database.encrypted_db.event"
                        ):
                            with patch(
                                "local_deep_research.database.initialize.initialize_database",
                                side_effect=ValueError(
                                    "Migrations directory has insecure permissions (world-writable)"
                                ),
                            ):
                                with pytest.raises(
                                    ValueError, match="world-writable"
                                ):
                                    manager.create_user_database(
                                        "newuser", "password"
                                    )

                        assert "newuser" not in manager.connections
                        assert len(created_engines) == 1
                        created_engines[0].dispose.assert_called_once()
                        assert not db_file.exists()


class TestDatabaseOpening:
    """Tests for open_user_database functionality."""

    @patch("local_deep_research.database.encrypted_db.get_data_directory")
    def test_open_database_with_valid_password(self, mock_data_dir, tmp_path):
        """Test opening database with valid password."""
        from local_deep_research.database.encrypted_db import DatabaseManager

        mock_data_dir.return_value = tmp_path

        # Create db file
        db_file = tmp_path / "encrypted_databases" / "test.db"
        db_file.parent.mkdir(parents=True, exist_ok=True)
        db_file.touch()

        with patch.object(
            DatabaseManager, "_check_encryption_available", return_value=False
        ):
            with patch.dict("os.environ", {"LDR_ALLOW_UNENCRYPTED": "true"}):
                manager = DatabaseManager()

                with patch.object(
                    manager, "_get_user_db_path", return_value=db_file
                ):
                    with patch(
                        "local_deep_research.database.encrypted_db.get_key_from_password",
                        return_value=b"\x00" * 32,
                    ):
                        with patch(
                            "local_deep_research.database.encrypted_db.create_engine"
                        ) as mock_engine:
                            mock_engine_instance = MagicMock()
                            mock_conn = MagicMock()
                            mock_engine_instance.connect.return_value.__enter__ = MagicMock(
                                return_value=mock_conn
                            )
                            mock_engine_instance.connect.return_value.__exit__ = MagicMock(
                                return_value=False
                            )
                            mock_engine.return_value = mock_engine_instance

                            with patch(
                                "local_deep_research.database.encrypted_db.event"
                            ):
                                with patch(
                                    "local_deep_research.database.alembic_runner.needs_migration",
                                    return_value=False,
                                ):
                                    with patch(
                                        "local_deep_research.database.initialize.initialize_database"
                                    ):
                                        engine = manager.open_user_database(
                                            "testuser", "validpassword"
                                        )

                                        assert engine is mock_engine_instance

    @patch("local_deep_research.database.encrypted_db.get_data_directory")
    def test_open_database_with_empty_password_raises(
        self, mock_data_dir, tmp_path
    ):
        """Test opening database fails with empty password."""
        from local_deep_research.database.encrypted_db import DatabaseManager

        mock_data_dir.return_value = tmp_path

        with patch.object(
            DatabaseManager, "_check_encryption_available", return_value=True
        ):
            manager = DatabaseManager()

            with pytest.raises(ValueError, match="Invalid encryption key"):
                manager.open_user_database("testuser", "")

    @patch("local_deep_research.database.encrypted_db.get_data_directory")
    def test_open_database_nonexistent_returns_none(
        self, mock_data_dir, tmp_path
    ):
        """Test opening nonexistent database returns None."""
        from local_deep_research.database.encrypted_db import DatabaseManager

        mock_data_dir.return_value = tmp_path

        with patch.object(
            DatabaseManager, "_check_encryption_available", return_value=True
        ):
            manager = DatabaseManager()

            nonexistent = tmp_path / "nonexistent.db"
            with patch.object(
                manager, "_get_user_db_path", return_value=nonexistent
            ):
                result = manager.open_user_database("nonexistent", "password")

                assert result is None

    @patch("local_deep_research.database.encrypted_db.get_data_directory")
    def test_open_database_already_open_returns_cached(
        self, mock_data_dir, tmp_path
    ):
        """Test opening already-open database returns cached engine."""
        from local_deep_research.database.encrypted_db import DatabaseManager

        mock_data_dir.return_value = tmp_path

        with patch.object(
            DatabaseManager, "_check_encryption_available", return_value=True
        ):
            manager = DatabaseManager()

            mock_engine = MagicMock()
            manager.connections["testuser"] = mock_engine

            result = manager.open_user_database("testuser", "password")

            assert result is mock_engine

    @patch("local_deep_research.database.encrypted_db.get_data_directory")
    def test_open_database_connection_error_returns_none(
        self, mock_data_dir, tmp_path
    ):
        """Test that connection errors return None."""
        from local_deep_research.database.encrypted_db import DatabaseManager

        mock_data_dir.return_value = tmp_path

        db_file = tmp_path / "encrypted_databases" / "test.db"
        db_file.parent.mkdir(parents=True, exist_ok=True)
        db_file.touch()

        with patch.object(
            DatabaseManager, "_check_encryption_available", return_value=False
        ):
            with patch.dict("os.environ", {"LDR_ALLOW_UNENCRYPTED": "true"}):
                manager = DatabaseManager()

                with patch.object(
                    manager, "_get_user_db_path", return_value=db_file
                ):
                    with patch(
                        "local_deep_research.database.encrypted_db.create_engine"
                    ) as mock_engine:
                        mock_engine_instance = MagicMock()
                        # Use context manager that raises on enter
                        mock_context = MagicMock()
                        mock_context.__enter__ = MagicMock(
                            side_effect=Exception("Connection failed")
                        )
                        mock_context.__exit__ = MagicMock(return_value=False)
                        mock_engine_instance.connect.return_value = mock_context
                        mock_engine.return_value = mock_engine_instance

                        with patch(
                            "local_deep_research.database.encrypted_db.event"
                        ):
                            result = manager.open_user_database(
                                "testuser", "password"
                            )

                            assert result is None

    @patch("local_deep_research.database.encrypted_db.get_data_directory")
    def test_open_database_migration_failure_raises_typed_error(
        self, mock_data_dir, tmp_path
    ):
        """Init failures raise DatabaseInitializationError, not return None.

        The login route uses the type distinction to skip the lockout
        counter (credentials are valid; only the schema couldn't come up)
        and to flash a server-error message instead of "Invalid username
        or password". The engine must still be disposed and not cached.
        """
        from local_deep_research.database.encrypted_db import (
            DatabaseInitializationError,
            DatabaseManager,
        )

        mock_data_dir.return_value = tmp_path

        db_file = tmp_path / "encrypted_databases" / "test.db"
        db_file.parent.mkdir(parents=True, exist_ok=True)
        db_file.touch()

        with patch.object(
            DatabaseManager, "_check_encryption_available", return_value=False
        ):
            with patch.dict("os.environ", {"LDR_ALLOW_UNENCRYPTED": "true"}):
                manager = DatabaseManager()

                with patch.object(
                    manager, "_get_user_db_path", return_value=db_file
                ):
                    with patch(
                        "local_deep_research.database.encrypted_db.create_engine"
                    ) as mock_engine_factory:
                        mock_engine = MagicMock()
                        mock_engine_factory.return_value = mock_engine
                        # SELECT 1 connection check passes
                        mock_engine.connect.return_value.__enter__ = MagicMock(
                            return_value=MagicMock()
                        )
                        mock_engine.connect.return_value.__exit__ = MagicMock(
                            return_value=False
                        )

                        with patch(
                            "local_deep_research.database.encrypted_db.event"
                        ):
                            with patch(
                                "local_deep_research.database.alembic_runner.needs_migration",
                                return_value=False,
                            ):
                                with patch(
                                    "local_deep_research.database.initialize.initialize_database",
                                    side_effect=ValueError(
                                        "Migrations directory has insecure permissions (world-writable)"
                                    ),
                                ):
                                    with pytest.raises(
                                        DatabaseInitializationError
                                    ):
                                        manager.open_user_database(
                                            "testuser", "password"
                                        )

                        assert "testuser" not in manager.connections
                        mock_engine.dispose.assert_called_once()


class TestDatabaseClosure:
    """Tests for close_user_database functionality."""

    @patch("local_deep_research.database.encrypted_db.get_data_directory")
    def test_close_database_disposes_engine(self, mock_data_dir, tmp_path):
        """Test closing database disposes engine and removes from connections."""
        from local_deep_research.database.encrypted_db import DatabaseManager

        mock_data_dir.return_value = tmp_path

        with patch.object(
            DatabaseManager, "_check_encryption_available", return_value=True
        ):
            manager = DatabaseManager()

            mock_engine = MagicMock()
            manager.connections["testuser"] = mock_engine

            manager.close_user_database("testuser")

            mock_engine.dispose.assert_called_once()
            assert "testuser" not in manager.connections

    @patch("local_deep_research.database.encrypted_db.get_data_directory")
    def test_close_nonexistent_database_no_error(self, mock_data_dir, tmp_path):
        """Test closing nonexistent database doesn't raise error."""
        from local_deep_research.database.encrypted_db import DatabaseManager

        mock_data_dir.return_value = tmp_path

        with patch.object(
            DatabaseManager, "_check_encryption_available", return_value=True
        ):
            manager = DatabaseManager()

            # Should not raise
            manager.close_user_database("nonexistent")


class TestCloseAllDatabases:
    """Tests for close_all_databases functionality."""

    @patch("local_deep_research.database.encrypted_db.get_data_directory")
    def test_close_all_databases_continues_after_dispose_failure(
        self, mock_data_dir, tmp_path
    ):
        """Verify one engine's dispose failure doesn't prevent others from closing."""
        from local_deep_research.database.encrypted_db import DatabaseManager

        mock_data_dir.return_value = tmp_path

        with patch.object(
            DatabaseManager, "_check_encryption_available", return_value=True
        ):
            manager = DatabaseManager()

            engine_ok = MagicMock()
            engine_fail = MagicMock()
            engine_fail.dispose.side_effect = RuntimeError("dispose exploded")

            manager.connections["alice"] = engine_fail
            manager.connections["bob"] = engine_ok

            manager.close_all_databases()

            # Both engines attempted dispose
            engine_fail.dispose.assert_called_once()
            engine_ok.dispose.assert_called_once()
            # Connections dict is cleared regardless
            assert len(manager.connections) == 0


class TestPasswordChange:
    """Tests for change_password functionality."""

    @patch("local_deep_research.database.encrypted_db.get_data_directory")
    def test_change_password_no_encryption_returns_false(
        self, mock_data_dir, tmp_path
    ):
        """Test password change returns False when encryption not available."""
        from local_deep_research.database.encrypted_db import DatabaseManager

        mock_data_dir.return_value = tmp_path

        with patch.object(
            DatabaseManager, "_check_encryption_available", return_value=False
        ):
            with patch.dict("os.environ", {"LDR_ALLOW_UNENCRYPTED": "true"}):
                manager = DatabaseManager()

                result = manager.change_password(
                    "testuser", "oldpass", "newpass"
                )

                assert result is False

    @patch("local_deep_research.database.encrypted_db.get_data_directory")
    def test_change_password_nonexistent_db_returns_false(
        self, mock_data_dir, tmp_path
    ):
        """Test password change returns False for nonexistent database."""
        from local_deep_research.database.encrypted_db import DatabaseManager

        mock_data_dir.return_value = tmp_path

        with patch.object(
            DatabaseManager, "_check_encryption_available", return_value=True
        ):
            manager = DatabaseManager()

            nonexistent = tmp_path / "nonexistent.db"
            with patch.object(
                manager, "_get_user_db_path", return_value=nonexistent
            ):
                result = manager.change_password(
                    "testuser", "oldpass", "newpass"
                )

                assert result is False

    @patch("local_deep_research.database.encrypted_db.get_data_directory")
    def test_change_password_wrong_old_password_returns_false(
        self, mock_data_dir, tmp_path
    ):
        """Test password change returns False with wrong old password."""
        from local_deep_research.database.encrypted_db import DatabaseManager

        mock_data_dir.return_value = tmp_path

        db_file = tmp_path / "encrypted_databases" / "test.db"
        db_file.parent.mkdir(parents=True, exist_ok=True)
        db_file.touch()

        with patch.object(
            DatabaseManager, "_check_encryption_available", return_value=True
        ):
            manager = DatabaseManager()

            with patch.object(
                manager, "_get_user_db_path", return_value=db_file
            ):
                # open_user_database fails with wrong password
                with patch.object(
                    manager, "open_user_database", return_value=None
                ):
                    result = manager.change_password(
                        "testuser", "wrongpass", "newpass"
                    )

                    assert result is False


class TestUserExists:
    """Tests for user_exists functionality."""

    @patch("local_deep_research.database.encrypted_db.get_data_directory")
    def test_user_exists_true(self, mock_data_dir, tmp_path):
        """Test user_exists returns True for existing user."""
        from local_deep_research.database.encrypted_db import DatabaseManager

        mock_data_dir.return_value = tmp_path

        with patch.object(
            DatabaseManager, "_check_encryption_available", return_value=True
        ):
            manager = DatabaseManager()

            with patch(
                "local_deep_research.database.auth_db.get_auth_db_session"
            ) as mock_auth:
                mock_session = MagicMock()
                mock_auth.return_value = mock_session

                mock_user = MagicMock()
                mock_session.query.return_value.filter_by.return_value.first.return_value = mock_user

                result = manager.user_exists("existinguser")

                assert result is True
                mock_session.close.assert_called_once()

    @patch("local_deep_research.database.encrypted_db.get_data_directory")
    def test_user_exists_false(self, mock_data_dir, tmp_path):
        """Test user_exists returns False for nonexistent user."""
        from local_deep_research.database.encrypted_db import DatabaseManager

        mock_data_dir.return_value = tmp_path

        with patch.object(
            DatabaseManager, "_check_encryption_available", return_value=True
        ):
            manager = DatabaseManager()

            with patch(
                "local_deep_research.database.auth_db.get_auth_db_session"
            ) as mock_auth:
                mock_session = MagicMock()
                mock_auth.return_value = mock_session

                mock_session.query.return_value.filter_by.return_value.first.return_value = None

                result = manager.user_exists("nonexistent")

                assert result is False


class TestDatabaseIntegrity:
    """Tests for check_database_integrity functionality."""

    @patch("local_deep_research.database.encrypted_db.get_data_directory")
    def test_integrity_check_no_connection_returns_false(
        self, mock_data_dir, tmp_path
    ):
        """Test integrity check returns False when no connection exists."""
        from local_deep_research.database.encrypted_db import DatabaseManager

        mock_data_dir.return_value = tmp_path

        with patch.object(
            DatabaseManager, "_check_encryption_available", return_value=True
        ):
            manager = DatabaseManager()

            result = manager.check_database_integrity("nonexistent")

            assert result is False

    @patch("local_deep_research.database.encrypted_db.get_data_directory")
    def test_integrity_check_success(self, mock_data_dir, tmp_path):
        """Test successful integrity check returns True."""
        from local_deep_research.database.encrypted_db import DatabaseManager

        mock_data_dir.return_value = tmp_path

        with patch.object(
            DatabaseManager, "_check_encryption_available", return_value=True
        ):
            manager = DatabaseManager()

            mock_engine = MagicMock()
            mock_conn = MagicMock()
            mock_engine.connect.return_value.__enter__ = MagicMock(
                return_value=mock_conn
            )
            mock_engine.connect.return_value.__exit__ = MagicMock(
                return_value=False
            )

            # Mock successful integrity checks
            mock_conn.execute.side_effect = [
                MagicMock(
                    fetchone=MagicMock(return_value=("ok",))
                ),  # quick_check
                iter([]),  # cipher_integrity_check - no failures
            ]

            manager.connections["testuser"] = mock_engine

            result = manager.check_database_integrity("testuser")

            assert result is True

    @patch("local_deep_research.database.encrypted_db.get_data_directory")
    def test_integrity_check_quick_check_failure(self, mock_data_dir, tmp_path):
        """Test integrity check fails on quick_check failure."""
        from local_deep_research.database.encrypted_db import DatabaseManager

        mock_data_dir.return_value = tmp_path

        with patch.object(
            DatabaseManager, "_check_encryption_available", return_value=True
        ):
            manager = DatabaseManager()

            mock_engine = MagicMock()
            mock_conn = MagicMock()
            mock_engine.connect.return_value.__enter__ = MagicMock(
                return_value=mock_conn
            )
            mock_engine.connect.return_value.__exit__ = MagicMock(
                return_value=False
            )

            # Mock failed quick_check
            mock_conn.execute.return_value.fetchone.return_value = ("corrupt",)

            manager.connections["testuser"] = mock_engine

            result = manager.check_database_integrity("testuser")

            assert result is False

    @patch("local_deep_research.database.encrypted_db.get_data_directory")
    def test_integrity_check_cipher_failure(self, mock_data_dir, tmp_path):
        """Test integrity check fails on cipher_integrity_check failure."""
        from local_deep_research.database.encrypted_db import DatabaseManager

        mock_data_dir.return_value = tmp_path

        with patch.object(
            DatabaseManager, "_check_encryption_available", return_value=True
        ):
            manager = DatabaseManager()

            mock_engine = MagicMock()
            mock_conn = MagicMock()
            mock_engine.connect.return_value.__enter__ = MagicMock(
                return_value=mock_conn
            )
            mock_engine.connect.return_value.__exit__ = MagicMock(
                return_value=False
            )

            # Mock successful quick_check but failed cipher check
            mock_conn.execute.side_effect = [
                MagicMock(
                    fetchone=MagicMock(return_value=("ok",))
                ),  # quick_check
                iter(
                    [("HMAC failure",)]
                ),  # cipher_integrity_check - has failures
            ]

            manager.connections["testuser"] = mock_engine

            result = manager.check_database_integrity("testuser")

            assert result is False

    @patch("local_deep_research.database.encrypted_db.get_data_directory")
    def test_integrity_check_exception_returns_false(
        self, mock_data_dir, tmp_path
    ):
        """Test integrity check returns False on exception."""
        from local_deep_research.database.encrypted_db import DatabaseManager

        mock_data_dir.return_value = tmp_path

        with patch.object(
            DatabaseManager, "_check_encryption_available", return_value=True
        ):
            manager = DatabaseManager()

            mock_engine = MagicMock()
            mock_engine.connect.side_effect = Exception("Connection failed")

            manager.connections["testuser"] = mock_engine

            result = manager.check_database_integrity("testuser")

            assert result is False


class TestMemoryUsage:
    """Tests for get_memory_usage functionality."""

    @patch("local_deep_research.database.encrypted_db.get_data_directory")
    def test_memory_usage_empty(self, mock_data_dir, tmp_path):
        """Test memory usage with no connections."""
        from local_deep_research.database.encrypted_db import DatabaseManager

        mock_data_dir.return_value = tmp_path

        with patch.object(
            DatabaseManager, "_check_encryption_available", return_value=True
        ):
            manager = DatabaseManager()

            usage = manager.get_memory_usage()

            assert usage["active_connections"] == 0
            assert usage["active_sessions"] == 0
            assert usage["estimated_memory_mb"] == 0

    @patch("local_deep_research.database.encrypted_db.get_data_directory")
    def test_memory_usage_with_connections(self, mock_data_dir, tmp_path):
        """Test memory usage with active connections."""
        from local_deep_research.database.encrypted_db import DatabaseManager

        mock_data_dir.return_value = tmp_path

        with patch.object(
            DatabaseManager, "_check_encryption_available", return_value=True
        ):
            manager = DatabaseManager()

            manager.connections["user1"] = MagicMock()
            manager.connections["user2"] = MagicMock()

            usage = manager.get_memory_usage()

            assert usage["active_connections"] == 2
            assert usage["estimated_memory_mb"] == 7.0  # 2 * 3.5

    @patch("local_deep_research.database.encrypted_db.get_data_directory")
    def test_memory_usage_calculation(self, mock_data_dir, tmp_path):
        """Test memory usage calculation formula."""
        from local_deep_research.database.encrypted_db import DatabaseManager

        mock_data_dir.return_value = tmp_path

        with patch.object(
            DatabaseManager, "_check_encryption_available", return_value=True
        ):
            manager = DatabaseManager()

            for i in range(5):
                manager.connections[f"user{i}"] = MagicMock()

            usage = manager.get_memory_usage()

            assert usage["estimated_memory_mb"] == 17.5  # 5 * 3.5


class TestThreadSafeSessionForMetrics:
    """Tests for create_thread_safe_session_for_metrics functionality."""

    @patch("local_deep_research.database.encrypted_db.get_data_directory")
    def test_thread_safe_session_nonexistent_db_raises(
        self, mock_data_dir, tmp_path
    ):
        """Test thread-safe session raises for nonexistent database."""
        from local_deep_research.database.encrypted_db import DatabaseManager

        mock_data_dir.return_value = tmp_path

        with patch.object(
            DatabaseManager, "_check_encryption_available", return_value=True
        ):
            manager = DatabaseManager()

            nonexistent = tmp_path / "nonexistent.db"
            with patch.object(
                manager, "_get_user_db_path", return_value=nonexistent
            ):
                with pytest.raises(ValueError, match="No database found"):
                    manager.create_thread_safe_session_for_metrics(
                        "nonexistent", "password"
                    )

    @patch("local_deep_research.database.encrypted_db.get_data_directory")
    def test_thread_safe_session_binds_to_queuepool_engine(
        self, mock_data_dir, tmp_path
    ):
        """The returned session is bound to the per-user QueuePool engine."""
        from local_deep_research.database.encrypted_db import DatabaseManager

        mock_data_dir.return_value = tmp_path

        db_file = tmp_path / "encrypted_databases" / "test.db"
        db_file.parent.mkdir(parents=True, exist_ok=True)
        db_file.touch()

        with patch.object(
            DatabaseManager, "_check_encryption_available", return_value=True
        ):
            manager = DatabaseManager()

            mock_engine = MagicMock()
            manager.connections["testuser"] = mock_engine

            with patch.object(
                manager, "_get_user_db_path", return_value=db_file
            ):
                session = manager.create_thread_safe_session_for_metrics(
                    "testuser", "password"
                )
                assert session.bind is mock_engine

    @patch("local_deep_research.database.encrypted_db.get_data_directory")
    def test_concurrent_metrics_sessions_via_shared_queuepool(
        self, mock_data_dir, tmp_path
    ):
        """Multiple threads sharing a QueuePool can write concurrently
        without errors, deadlocks, or lost writes.

        Regression test for the #3441 refactor that replaced per-thread
        NullPool engines with a single shared per-user QueuePool.
        """
        from sqlalchemy import text
        from sqlalchemy.pool import QueuePool

        from local_deep_research.database.encrypted_db import DatabaseManager

        mock_data_dir.return_value = tmp_path

        db_file = tmp_path / "encrypted_databases" / "concurrent.db"
        db_file.parent.mkdir(parents=True, exist_ok=True)

        manager = DatabaseManager()
        manager._use_static_pool = False
        manager._pool_class = QueuePool

        # Create a plain SQLite database (no encryption needed for this test)
        from sqlalchemy import create_engine

        engine = create_engine(
            f"sqlite:///{db_file}",
            connect_args={"check_same_thread": False, "timeout": 30},
            poolclass=QueuePool,
            pool_size=5,
            max_overflow=10,
        )
        with engine.connect() as conn:
            conn.execute(
                text(
                    "CREATE TABLE metric_test "
                    "(id INTEGER PRIMARY KEY, thread_id INTEGER, seq INTEGER)"
                )
            )
            conn.commit()

        # Inject the engine so create_thread_safe_session_for_metrics
        # finds it on cache hit.
        manager.connections["testuser"] = engine

        try:
            with patch.object(
                manager, "_get_user_db_path", return_value=db_file
            ):
                num_threads = 10
                writes_per_thread = 10
                errors = []
                write_count = [0]
                lock = threading.Lock()

                def writer(tid):
                    try:
                        session = (
                            manager.create_thread_safe_session_for_metrics(
                                "testuser", "unused"
                            )
                        )
                        for seq in range(writes_per_thread):
                            uid = tid * 1000 + seq
                            session.execute(
                                text(
                                    f"INSERT INTO metric_test VALUES "
                                    f"({uid}, {tid}, {seq})"
                                )
                            )
                            session.commit()
                            with lock:
                                write_count[0] += 1
                        session.close()
                    except Exception as e:
                        errors.append(f"Thread {tid}: {e}")

                threads = [
                    threading.Thread(target=writer, args=(i,))
                    for i in range(num_threads)
                ]
                for t in threads:
                    t.start()
                for t in threads:
                    t.join(timeout=30)

                for t in threads:
                    assert not t.is_alive(), f"{t.name} hung past timeout"

                assert not errors, f"Concurrent write errors: {errors}"
                expected = num_threads * writes_per_thread
                assert write_count[0] == expected

                # Verify all rows persisted
                with engine.connect() as conn:
                    result = conn.execute(
                        text("SELECT COUNT(*) FROM metric_test")
                    )
                    count = result.fetchone()[0]
                    assert count == expected, (
                        f"Expected {expected} rows, got {count}"
                    )
        finally:
            engine.dispose()


class TestSessionManagement:
    """Tests for get_session functionality."""

    @patch("local_deep_research.database.encrypted_db.get_data_directory")
    def test_get_session_no_connection_returns_none(
        self, mock_data_dir, tmp_path
    ):
        """Test get_session returns None when no connection exists."""
        from local_deep_research.database.encrypted_db import DatabaseManager

        mock_data_dir.return_value = tmp_path

        with patch.object(
            DatabaseManager, "_check_encryption_available", return_value=True
        ):
            manager = DatabaseManager()

            result = manager.get_session("nonexistent")

            assert result is None

    @patch("local_deep_research.database.encrypted_db.get_data_directory")
    def test_get_session_creates_new_session(self, mock_data_dir, tmp_path):
        """Test get_session creates a new session from existing connection."""
        from local_deep_research.database.encrypted_db import DatabaseManager

        mock_data_dir.return_value = tmp_path

        with patch.object(
            DatabaseManager, "_check_encryption_available", return_value=True
        ):
            manager = DatabaseManager()

            mock_engine = MagicMock()
            manager.connections["testuser"] = mock_engine

            with patch(
                "local_deep_research.database.encrypted_db.sessionmaker"
            ) as mock_sm:
                mock_session = MagicMock()
                mock_sm.return_value = MagicMock(return_value=mock_session)

                result = manager.get_session("testuser")

                assert result is mock_session
                mock_sm.assert_called_once_with(bind=mock_engine)


class TestMultiUserIsolation:
    """Tests for multi-user database isolation."""

    @patch("local_deep_research.database.encrypted_db.get_data_directory")
    def test_multiple_users_separate_connections(self, mock_data_dir, tmp_path):
        """Test multiple users have separate connections."""
        from local_deep_research.database.encrypted_db import DatabaseManager

        mock_data_dir.return_value = tmp_path

        with patch.object(
            DatabaseManager, "_check_encryption_available", return_value=True
        ):
            manager = DatabaseManager()

            mock_engine1 = MagicMock()
            mock_engine2 = MagicMock()

            manager.connections["user1"] = mock_engine1
            manager.connections["user2"] = mock_engine2

            assert (
                manager.connections["user1"] is not manager.connections["user2"]
            )
            assert len(manager.connections) == 2

    @patch("local_deep_research.database.encrypted_db.get_data_directory")
    def test_closing_one_user_doesnt_affect_others(
        self, mock_data_dir, tmp_path
    ):
        """Test closing one user's database doesn't affect others."""
        from local_deep_research.database.encrypted_db import DatabaseManager

        mock_data_dir.return_value = tmp_path

        with patch.object(
            DatabaseManager, "_check_encryption_available", return_value=True
        ):
            manager = DatabaseManager()

            mock_engine1 = MagicMock()
            mock_engine2 = MagicMock()

            manager.connections["user1"] = mock_engine1
            manager.connections["user2"] = mock_engine2

            manager.close_user_database("user1")

            assert "user1" not in manager.connections
            assert "user2" in manager.connections
            assert manager.connections["user2"] is mock_engine2


class TestConcurrentAccess:
    """Tests for concurrent access handling."""

    @patch("local_deep_research.database.encrypted_db.get_data_directory")
    def test_concurrent_connection_storage(self, mock_data_dir, tmp_path):
        """Test concurrent connection storage operations."""
        from local_deep_research.database.encrypted_db import DatabaseManager

        mock_data_dir.return_value = tmp_path

        with patch.object(
            DatabaseManager, "_check_encryption_available", return_value=True
        ):
            manager = DatabaseManager()

            results = {}

            def add_connection(user_id):
                mock_engine = MagicMock()
                manager.connections[f"user{user_id}"] = mock_engine
                time.sleep(0.001)
                results[user_id] = manager.connections.get(f"user{user_id}")

            threads = [
                threading.Thread(target=add_connection, args=(i,))
                for i in range(20)
            ]

            for t in threads:
                t.start()
            for t in threads:
                t.join()

            # All connections should be stored
            assert len(manager.connections) == 20


class TestEncryptionAvailability:
    """Tests for encryption availability checking."""

    @patch("local_deep_research.database.encrypted_db.get_data_directory")
    def test_encryption_available_true(self, mock_data_dir, tmp_path):
        """Test has_encryption is True when SQLCipher available."""
        from local_deep_research.database.encrypted_db import DatabaseManager

        mock_data_dir.return_value = tmp_path

        with patch.object(
            DatabaseManager, "_check_encryption_available", return_value=True
        ):
            manager = DatabaseManager()

            assert manager.has_encryption is True

    @patch("local_deep_research.database.encrypted_db.get_data_directory")
    def test_encryption_available_false(self, mock_data_dir, tmp_path):
        """Test has_encryption is False when SQLCipher not available."""
        from local_deep_research.database.encrypted_db import DatabaseManager

        mock_data_dir.return_value = tmp_path

        with patch.dict("os.environ", {"LDR_ALLOW_UNENCRYPTED": "true"}):
            with patch.object(
                DatabaseManager,
                "_check_encryption_available",
                return_value=False,
            ):
                manager = DatabaseManager()

                assert manager.has_encryption is False


class TestPoolConfiguration:
    """Tests for connection pool configuration."""

    @patch("local_deep_research.database.encrypted_db.get_data_directory")
    def test_static_pool_in_testing_mode(self, mock_data_dir, tmp_path):
        """Test StaticPool is used in testing mode."""
        from local_deep_research.database.encrypted_db import DatabaseManager

        mock_data_dir.return_value = tmp_path

        with patch.dict("os.environ", {"TESTING": "true"}):
            with patch.object(
                DatabaseManager,
                "_check_encryption_available",
                return_value=True,
            ):
                manager = DatabaseManager()

                assert manager._use_static_pool is True
                assert manager._get_pool_kwargs() == {}

    @patch("local_deep_research.database.encrypted_db.get_data_directory")
    def test_queue_pool_in_production_mode(self, mock_data_dir, tmp_path):
        """Test QueuePool is used in production mode."""
        from local_deep_research.database.encrypted_db import DatabaseManager

        mock_data_dir.return_value = tmp_path

        with patch.dict("os.environ", {}, clear=True):
            with patch.object(
                DatabaseManager,
                "_check_encryption_available",
                return_value=True,
            ):
                manager = DatabaseManager()
                manager._use_static_pool = False

                kwargs = manager._get_pool_kwargs()

                assert "pool_size" in kwargs
                assert kwargs["pool_size"] == 20
                assert kwargs["max_overflow"] == 40
                assert kwargs["pool_timeout"] == 10


class TestValidEncryptionKey:
    """Tests for _is_valid_encryption_key functionality."""

    @patch("local_deep_research.database.encrypted_db.get_data_directory")
    def test_valid_keys(self, mock_data_dir, tmp_path):
        """Test valid encryption keys are accepted."""
        from local_deep_research.database.encrypted_db import DatabaseManager

        mock_data_dir.return_value = tmp_path

        with patch.object(
            DatabaseManager, "_check_encryption_available", return_value=True
        ):
            manager = DatabaseManager()

            assert manager._is_valid_encryption_key("password") is True
            assert manager._is_valid_encryption_key("a") is True
            assert manager._is_valid_encryption_key("complex!@#$%^&*()") is True
            assert manager._is_valid_encryption_key("123456") is True
            assert manager._is_valid_encryption_key("   spaces   ") is True

    @patch("local_deep_research.database.encrypted_db.get_data_directory")
    def test_invalid_keys(self, mock_data_dir, tmp_path):
        """Test invalid encryption keys are rejected."""
        from local_deep_research.database.encrypted_db import DatabaseManager

        mock_data_dir.return_value = tmp_path

        with patch.object(
            DatabaseManager, "_check_encryption_available", return_value=True
        ):
            manager = DatabaseManager()

            assert manager._is_valid_encryption_key(None) is False
            assert manager._is_valid_encryption_key("") is False


class TestMakeSqlcipherConnectionCursorCloseLogging:
    """Tests for cursor.close() failure logging in _make_sqlcipher_connection (PR #2145)."""

    @patch("local_deep_research.database.encrypted_db.get_data_directory")
    def test_cursor_close_failure_logs_warning(self, mock_data_dir, tmp_path):
        """When cursor.close() fails during cleanup, logger.warning is called."""
        from local_deep_research.database.encrypted_db import DatabaseManager

        mock_data_dir.return_value = tmp_path

        with patch.object(
            DatabaseManager, "_check_encryption_available", return_value=True
        ):
            DatabaseManager()

        # Mock sqlcipher3 module
        mock_conn = MagicMock()
        mock_cursor = MagicMock()
        mock_conn.cursor.return_value = mock_cursor

        mock_sqlcipher3 = MagicMock()
        mock_sqlcipher3.connect.return_value = mock_conn

        # Make set_sqlcipher_key raise to trigger cleanup path
        with patch(
            "local_deep_research.database.encrypted_db.get_sqlcipher_module",
            return_value=mock_sqlcipher3,
        ):
            with patch(
                "local_deep_research.database.encrypted_db.set_sqlcipher_key",
                side_effect=ValueError("Bad key"),
            ):
                # Make cursor.close() also fail
                mock_cursor.close.side_effect = Exception(
                    "Cursor already closed"
                )

                with patch(
                    "local_deep_research.database.encrypted_db.logger"
                ) as mock_logger:
                    with pytest.raises(ValueError, match="Bad key"):
                        DatabaseManager._make_sqlcipher_connection(
                            tmp_path / "test.db", "password"
                        )

                    # Verify logger.warning was called about cursor close failure
                    mock_logger.warning.assert_called_once()
                    call_args = mock_logger.warning.call_args
                    assert "Failed to close cursor" in call_args[0][0]

    @patch("local_deep_research.database.encrypted_db.get_data_directory")
    def test_original_exception_propagates_despite_cursor_close_failure(
        self, mock_data_dir, tmp_path
    ):
        """Original exception still propagates even when cursor.close() fails."""
        from local_deep_research.database.encrypted_db import DatabaseManager

        mock_data_dir.return_value = tmp_path

        mock_conn = MagicMock()
        mock_cursor = MagicMock()
        mock_conn.cursor.return_value = mock_cursor

        mock_sqlcipher3 = MagicMock()
        mock_sqlcipher3.connect.return_value = mock_conn

        with patch(
            "local_deep_research.database.encrypted_db.get_sqlcipher_module",
            return_value=mock_sqlcipher3,
        ):
            with patch(
                "local_deep_research.database.encrypted_db.set_sqlcipher_key",
                side_effect=ValueError("Verification failed"),
            ):
                mock_cursor.close.side_effect = RuntimeError("Cannot close")

                # The original ValueError should propagate, not the RuntimeError
                with pytest.raises(ValueError, match="Verification failed"):
                    DatabaseManager._make_sqlcipher_connection(
                        tmp_path / "test.db", "password"
                    )

                # conn.close() should still be called
                mock_conn.close.assert_called_once()

    @patch("local_deep_research.database.encrypted_db.get_data_directory")
    def test_cursor_close_success_no_warning(self, mock_data_dir, tmp_path):
        """No warning logged when cursor.close() succeeds during cleanup."""
        from local_deep_research.database.encrypted_db import DatabaseManager

        mock_data_dir.return_value = tmp_path

        mock_conn = MagicMock()
        mock_cursor = MagicMock()
        mock_conn.cursor.return_value = mock_cursor

        mock_sqlcipher3 = MagicMock()
        mock_sqlcipher3.connect.return_value = mock_conn

        with patch(
            "local_deep_research.database.encrypted_db.get_sqlcipher_module",
            return_value=mock_sqlcipher3,
        ):
            with patch(
                "local_deep_research.database.encrypted_db.set_sqlcipher_key",
                side_effect=ValueError("Bad key"),
            ):
                # cursor.close() succeeds (no side_effect)
                with patch(
                    "local_deep_research.database.encrypted_db.logger"
                ) as mock_logger:
                    with pytest.raises(ValueError, match="Bad key"):
                        DatabaseManager._make_sqlcipher_connection(
                            tmp_path / "test.db", "password"
                        )

                    # No warning should be logged when cursor.close() succeeds
                    mock_logger.warning.assert_not_called()


class TestTimingAttackPrevention:
    """Tests for timing attack prevention in open_user_database and
    create_thread_safe_session_for_metrics (PR #2168).

    Key derivation (get_key_from_password) must happen BEFORE the
    db_path.exists() check so that both existing and non-existing users
    take the same amount of time, preventing username enumeration.
    """

    @patch("local_deep_research.database.encrypted_db.get_data_directory")
    def test_open_user_database_derives_key_before_file_check(
        self, mock_data_dir, tmp_path
    ):
        """open_user_database derives key before checking if user db exists."""
        from local_deep_research.database.encrypted_db import DatabaseManager

        mock_data_dir.return_value = tmp_path

        with patch.object(
            DatabaseManager, "_check_encryption_available", return_value=True
        ):
            manager = DatabaseManager()

        with patch(
            "local_deep_research.database.encrypted_db.get_key_from_password"
        ) as mock_derive:
            mock_derive.return_value = b"\x00" * 32

            # Non-existent user — no database file exists
            result = manager.open_user_database(
                "nonexistent_user", "password123"
            )

            # Should return None
            assert result is None
            # Key derivation must have been called
            mock_derive.assert_called_once()

    @patch("local_deep_research.database.encrypted_db.get_data_directory")
    def test_open_user_database_derives_key_for_existing_user(
        self, mock_data_dir, tmp_path
    ):
        """Key derivation called for existing users too (timing consistency)."""
        from local_deep_research.config.paths import get_user_database_filename
        from local_deep_research.database.encrypted_db import DatabaseManager

        mock_data_dir.return_value = tmp_path

        with patch.object(
            DatabaseManager, "_check_encryption_available", return_value=False
        ):
            manager = DatabaseManager()

        # Create database at the correct path
        username = "test_user"
        db_path = tmp_path / get_user_database_filename(username)
        db_path.parent.mkdir(parents=True, exist_ok=True)
        db_path.touch()

        with patch.object(manager, "_get_user_db_path", return_value=db_path):
            with patch(
                "local_deep_research.database.encrypted_db.get_key_from_password"
            ) as mock_derive:
                mock_derive.return_value = b"\x00" * 32

                try:
                    manager.open_user_database(username, "password123")
                except Exception:
                    pass  # May fail later; we verify key derivation happened

                # Verify key derivation was called with the expected arguments.
                # Use assert_any_call instead of assert_called_once because
                # concurrent xdist workers sharing the same process may trigger
                # additional calls from other tests (e.g. auth route tests).
                mock_derive.assert_any_call("password123", db_path=db_path)

    @patch("local_deep_research.database.encrypted_db.get_data_directory")
    def test_metrics_session_skips_key_derivation_for_nonexistent_db(
        self, mock_data_dir, tmp_path
    ):
        """create_thread_safe_session_for_metrics skips key derivation when db missing.

        Unlike open_user_database (the login path), this method does not need
        timing-attack resistance because it is only called for already-authenticated
        users in background threads.  It should fail fast without wasting ~0.5s on
        PBKDF2 when the database file does not exist.
        """
        from local_deep_research.database.encrypted_db import DatabaseManager

        mock_data_dir.return_value = tmp_path

        with patch.object(
            DatabaseManager, "_check_encryption_available", return_value=True
        ):
            manager = DatabaseManager()

        with patch(
            "local_deep_research.database.encrypted_db.get_key_from_password"
        ) as mock_derive:
            mock_derive.return_value = b"\x00" * 32

            with pytest.raises(ValueError, match="No database found"):
                manager.create_thread_safe_session_for_metrics(
                    "nonexistent_user", "password123"
                )

            mock_derive.assert_not_called()

    @patch("local_deep_research.database.encrypted_db.get_data_directory")
    def test_salt_warning_not_called_for_nonexistent_db(
        self, mock_data_dir, tmp_path
    ):
        """has_per_database_salt not called when db doesn't exist."""
        from local_deep_research.database.encrypted_db import DatabaseManager

        mock_data_dir.return_value = tmp_path

        with patch.object(
            DatabaseManager, "_check_encryption_available", return_value=True
        ):
            manager = DatabaseManager()

        with patch(
            "local_deep_research.database.encrypted_db.has_per_database_salt"
        ) as mock_salt:
            with pytest.raises(ValueError, match="No database found"):
                manager.create_thread_safe_session_for_metrics(
                    "nonexistent_user", "password123"
                )

            # has_per_database_salt must NOT be called for non-existent db
            mock_salt.assert_not_called()


class TestRemovePartialUserDbFiles:
    """Unit tests for _remove_partial_user_db_files (create-failure cleanup)."""

    def test_removes_db_salt_and_all_sidecars(self, tmp_path):
        """Every artifact a half-created DB can leave — the .db, its .salt,
        and the -wal/-shm/-journal sidecars — must be removed, so a failed
        create leaves nothing that blocks a retry.

        Guards each unlink line independently of journal_mode: a typo in any
        one of them leaves its file behind and fails this test (the
        registration-flow tests only ever create/assert .db and .salt, so a
        broken sidecar line ships silently without this).
        """
        from local_deep_research.database.encrypted_db import (
            _remove_partial_user_db_files,
        )
        from local_deep_research.database.sqlcipher_utils import (
            get_salt_file_path,
        )

        db_path = tmp_path / "ldr_user_deadbeef.db"
        artifacts = [
            db_path,
            get_salt_file_path(db_path),
            db_path.with_name(db_path.name + "-wal"),
            db_path.with_name(db_path.name + "-shm"),
            db_path.with_name(db_path.name + "-journal"),
        ]
        for path in artifacts:
            path.write_bytes(b"x")
            assert path.exists()

        _remove_partial_user_db_files(db_path)

        for path in artifacts:
            assert not path.exists(), f"{path.name} was not removed"

    def test_missing_files_are_a_noop_not_an_error(self, tmp_path):
        """Never raises when artifacts are already absent (missing_ok)."""
        from local_deep_research.database.encrypted_db import (
            _remove_partial_user_db_files,
        )

        # No files created — must not raise.
        _remove_partial_user_db_files(tmp_path / "ldr_user_absent.db")

    def test_never_raises_when_paths_cannot_be_derived(self):
        """The 'Never raises' contract holds even when a sidecar path cannot
        be derived — the failure is logged and swallowed, not propagated, so
        it can never mask the original creation error it's cleaning up after.
        """
        from pathlib import Path
        from unittest.mock import patch

        from local_deep_research.database.encrypted_db import (
            _remove_partial_user_db_files,
        )

        # Path("/") has an empty name, so get_salt_file_path's with_suffix()
        # raises ValueError during path derivation. That must be swallowed
        # (the contract) AND the function must return before the unlink loop,
        # so it never attempts to unlink "/" or any real path. Patch unlink and
        # assert it's never called to *prove* the early-return — a plain
        # no-raise check couldn't (a regression reaching the loop would call
        # unlink("/") → IsADirectoryError, which the inner except OSError would
        # silently swallow, and the test would still pass).
        with patch("pathlib.Path.unlink") as mock_unlink:
            _remove_partial_user_db_files(Path("/"))  # must not raise
        mock_unlink.assert_not_called()