"""
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()
