learningcircuit--local-deep-research/tests/database/backup/test_backup_service.py

"""Tests for backup service functionality."""

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

import pytest

from local_deep_research.database.backup.backup_service import (
    _UNSAFE_BACKUP_PATH_CHARS,
    BackupResult,
    BackupService,
)
from local_deep_research.database.backup.backup_executor import (
    BackupExecutor,
    get_backup_executor,
)


def _has_sqlcipher() -> bool:
    try:
        from local_deep_research.database.sqlcipher_compat import (
            get_sqlcipher_module,
        )

        get_sqlcipher_module()
        return True
    except (ImportError, RuntimeError):
        return False


HAS_SQLCIPHER = _has_sqlcipher()
requires_sqlcipher = pytest.mark.skipif(
    not HAS_SQLCIPHER, reason="SQLCipher not available"
)


class TestBackupResult:
    """Tests for BackupResult dataclass."""

    def test_success_result(self, tmp_path):
        """Should create successful result with backup path."""
        backup_path = tmp_path / "test_backup.db"
        result = BackupResult(
            success=True,
            backup_path=backup_path,
            size_bytes=1024,
        )

        assert result.success is True
        assert result.backup_path == backup_path
        assert result.size_bytes == 1024
        assert result.error is None

    def test_failure_result(self):
        """Should create failure result with error message."""
        result = BackupResult(
            success=False,
            error="Database not found",
        )

        assert result.success is False
        assert result.backup_path is None
        assert result.error == "Database not found"
        assert result.size_bytes == 0


class TestBackupServiceInit:
    """Tests for BackupService initialization."""

    @patch(
        "local_deep_research.database.backup.backup_service.get_encrypted_database_path"
    )
    @patch(
        "local_deep_research.database.backup.backup_service.get_user_backup_directory"
    )
    @patch(
        "local_deep_research.database.backup.backup_service.get_user_database_filename"
    )
    def test_initialization(
        self, mock_db_filename, mock_backup_dir, mock_db_path, tmp_path
    ):
        """Should initialize with correct paths."""
        mock_db_filename.return_value = "ldr_user_abc123.db"
        mock_db_path.return_value = tmp_path / "encrypted_databases"
        mock_backup_dir.return_value = tmp_path / "backups"

        service = BackupService(
            username="testuser",
            password="testpass",
            max_backups=5,
            max_age_days=14,
        )

        assert service.username == "testuser"
        assert service.password == "testpass"
        assert service.max_backups == 5
        assert service.max_age_days == 14

    @patch(
        "local_deep_research.database.backup.backup_service.get_encrypted_database_path"
    )
    @patch(
        "local_deep_research.database.backup.backup_service.get_user_backup_directory"
    )
    @patch(
        "local_deep_research.database.backup.backup_service.get_user_database_filename"
    )
    def test_default_values(
        self, mock_db_filename, mock_backup_dir, mock_db_path, tmp_path
    ):
        """Should use default values when not specified."""
        mock_db_filename.return_value = "ldr_user_abc123.db"
        mock_db_path.return_value = tmp_path
        mock_backup_dir.return_value = tmp_path / "backups"

        service = BackupService(username="testuser", password="testpass")

        assert service.max_backups == 1
        assert service.max_age_days == 7


class TestBackupServiceCreateBackup:
    """Tests for backup creation."""

    @patch(
        "local_deep_research.database.backup.backup_service.get_encrypted_database_path"
    )
    @patch(
        "local_deep_research.database.backup.backup_service.get_user_backup_directory"
    )
    @patch(
        "local_deep_research.database.backup.backup_service.get_user_database_filename"
    )
    def test_returns_error_when_db_not_found(
        self, mock_db_filename, mock_backup_dir, mock_db_path, tmp_path
    ):
        """Should return error when database file doesn't exist."""
        mock_db_filename.return_value = "nonexistent.db"
        mock_db_path.return_value = tmp_path
        mock_backup_dir.return_value = tmp_path / "backups"

        service = BackupService(username="testuser", password="testpass")
        result = service.create_backup()

        assert result.success is False
        assert "Database not found" in result.error

    @patch(
        "local_deep_research.database.backup.backup_service.get_encrypted_database_path"
    )
    @patch(
        "local_deep_research.database.backup.backup_service.get_user_backup_directory"
    )
    @patch(
        "local_deep_research.database.backup.backup_service.get_user_database_filename"
    )
    @patch(
        "local_deep_research.database.backup.backup_service.create_sqlcipher_connection"
    )
    @patch("shutil.disk_usage")
    def test_checks_disk_space(
        self,
        mock_disk_usage,
        mock_create_conn,
        mock_db_filename,
        mock_backup_dir,
        mock_db_path,
        tmp_path,
    ):
        """Should check disk space before creating backup."""
        # Create a fake database file
        db_dir = tmp_path / "encrypted_databases"
        db_dir.mkdir()
        db_file = db_dir / "ldr_user_abc123.db"
        db_file.write_bytes(b"x" * 1000)

        mock_db_filename.return_value = "ldr_user_abc123.db"
        mock_db_path.return_value = db_dir
        mock_backup_dir.return_value = tmp_path / "backups"
        (tmp_path / "backups").mkdir()

        # Simulate insufficient disk space
        mock_disk_usage.return_value = MagicMock(
            free=100
        )  # Only 100 bytes free

        service = BackupService(username="testuser", password="testpass")
        result = service.create_backup()

        assert result.success is False
        assert "Insufficient disk space" in result.error


class TestBackupServiceListBackups:
    """Tests for listing backups."""

    @patch(
        "local_deep_research.database.backup.backup_service.get_encrypted_database_path"
    )
    @patch(
        "local_deep_research.database.backup.backup_service.get_user_backup_directory"
    )
    @patch(
        "local_deep_research.database.backup.backup_service.get_user_database_filename"
    )
    def test_returns_empty_list_when_no_backups(
        self, mock_db_filename, mock_backup_dir, mock_db_path, tmp_path
    ):
        """Should return empty list when no backups exist."""
        backup_dir = tmp_path / "backups"
        backup_dir.mkdir()

        mock_db_filename.return_value = "ldr_user_abc123.db"
        mock_db_path.return_value = tmp_path
        mock_backup_dir.return_value = backup_dir

        service = BackupService(username="testuser", password="testpass")
        backups = service.list_backups()

        assert backups == []

    @patch(
        "local_deep_research.database.backup.backup_service.get_encrypted_database_path"
    )
    @patch(
        "local_deep_research.database.backup.backup_service.get_user_backup_directory"
    )
    @patch(
        "local_deep_research.database.backup.backup_service.get_user_database_filename"
    )
    def test_lists_existing_backups(
        self, mock_db_filename, mock_backup_dir, mock_db_path, tmp_path
    ):
        """Should list existing backup files."""
        import os
        import time

        backup_dir = tmp_path / "backups"
        backup_dir.mkdir()

        # Create some backup files with explicit modification times
        # to ensure deterministic sorting (newer file should come first)
        older_file = backup_dir / "ldr_backup_20250101_120000.db"
        newer_file = backup_dir / "ldr_backup_20250102_120000.db"

        older_file.write_bytes(b"backup1")
        newer_file.write_bytes(b"backup2")

        # Set explicit modification times (older = 1000, newer = 2000)
        older_time = time.time() - 1000
        newer_time = time.time()
        os.utime(older_file, (older_time, older_time))
        os.utime(newer_file, (newer_time, newer_time))

        mock_db_filename.return_value = "ldr_user_abc123.db"
        mock_db_path.return_value = tmp_path
        mock_backup_dir.return_value = backup_dir

        service = BackupService(username="testuser", password="testpass")
        backups = service.list_backups()

        assert len(backups) == 2
        # Should be sorted newest first
        assert "20250102" in backups[0]["filename"]

    @patch(
        "local_deep_research.database.backup.backup_service.get_encrypted_database_path"
    )
    @patch(
        "local_deep_research.database.backup.backup_service.get_user_backup_directory"
    )
    @patch(
        "local_deep_research.database.backup.backup_service.get_user_database_filename"
    )
    def test_list_backups_with_nonexistent_directory(
        self, mock_db_filename, mock_backup_dir, mock_db_path, tmp_path
    ):
        """list_backups should return empty list if backup dir doesn't exist."""
        # Point to a directory that doesn't exist
        nonexistent_dir = tmp_path / "nonexistent_backups"

        mock_db_filename.return_value = "ldr_user_abc123.db"
        mock_db_path.return_value = tmp_path
        mock_backup_dir.return_value = nonexistent_dir

        service = BackupService(username="testuser", password="testpass")

        # Should return empty list, not crash
        backups = service.list_backups()
        assert backups == []

    @patch(
        "local_deep_research.database.backup.backup_service.get_encrypted_database_path"
    )
    @patch(
        "local_deep_research.database.backup.backup_service.get_user_backup_directory"
    )
    @patch(
        "local_deep_research.database.backup.backup_service.get_user_database_filename"
    )
    def test_list_backups_excludes_tmp_files(
        self, mock_db_filename, mock_backup_dir, mock_db_path, tmp_path
    ):
        """list_backups should not include .tmp partial backups."""
        backup_dir = tmp_path / "backups"
        backup_dir.mkdir()

        # Create valid .db backup files
        valid_backup1 = backup_dir / "ldr_backup_20250101_120000.db"
        valid_backup2 = backup_dir / "ldr_backup_20250102_120000.db"
        valid_backup1.write_bytes(b"backup1")
        valid_backup2.write_bytes(b"backup2")

        # Create .tmp files that should NOT be included
        tmp_file1 = backup_dir / "ldr_backup_20250103_120000.db.tmp"
        tmp_file2 = backup_dir / "partial_backup.tmp"
        tmp_file3 = backup_dir / "ldr_backup_20250104_120000.tmp"
        tmp_file1.write_bytes(b"partial1")
        tmp_file2.write_bytes(b"partial2")
        tmp_file3.write_bytes(b"partial3")

        mock_db_filename.return_value = "ldr_user_abc123.db"
        mock_db_path.return_value = tmp_path
        mock_backup_dir.return_value = backup_dir

        service = BackupService(username="testuser", password="testpass")
        backups = service.list_backups()

        # Should only include .db files matching the pattern
        assert len(backups) == 2

        # Verify the correct files are included
        filenames = [b["filename"] for b in backups]
        assert "ldr_backup_20250101_120000.db" in filenames
        assert "ldr_backup_20250102_120000.db" in filenames

        # Verify .tmp files are NOT included
        assert "ldr_backup_20250103_120000.db.tmp" not in filenames
        assert "partial_backup.tmp" not in filenames
        assert "ldr_backup_20250104_120000.tmp" not in filenames


class TestBackupServiceCleanup:
    """Tests for backup cleanup."""

    @patch(
        "local_deep_research.database.backup.backup_service.get_encrypted_database_path"
    )
    @patch(
        "local_deep_research.database.backup.backup_service.get_user_backup_directory"
    )
    @patch(
        "local_deep_research.database.backup.backup_service.get_user_database_filename"
    )
    def test_cleanup_removes_excess_backups(
        self, mock_db_filename, mock_backup_dir, mock_db_path, tmp_path
    ):
        """Should delete backups exceeding max count."""
        backup_dir = tmp_path / "backups"
        backup_dir.mkdir()

        # Create 5 backup files
        for i in range(5):
            backup_file = backup_dir / f"ldr_backup_2025010{i}_120000.db"
            backup_file.write_bytes(b"backup")
            # Set modification time
            import os

            os.utime(
                backup_file, (time.time() - i * 3600, time.time() - i * 3600)
            )

        mock_db_filename.return_value = "ldr_user_abc123.db"
        mock_db_path.return_value = tmp_path
        mock_backup_dir.return_value = backup_dir

        # Max 3 backups
        service = BackupService(
            username="testuser",
            password="testpass",
            max_backups=3,
        )
        deleted = service._cleanup_old_backups()

        assert deleted == 2
        remaining = list(backup_dir.glob("ldr_backup_*.db"))
        assert len(remaining) == 3


class TestBackupExecutor:
    """Tests for BackupExecutor."""

    def test_singleton_pattern(self):
        """Should return same instance on multiple calls."""
        scheduler1 = get_backup_executor()
        scheduler2 = get_backup_executor()

        assert scheduler1 is scheduler2

    def test_schedule_backup_prevents_duplicates(self):
        """Should prevent scheduling duplicate backups for same user."""
        scheduler = BackupExecutor.__new__(BackupExecutor)
        scheduler._pending_backups = set()
        scheduler._pending_lock = MagicMock()
        scheduler._pending_lock.__enter__ = MagicMock(return_value=None)
        scheduler._pending_lock.__exit__ = MagicMock(return_value=False)
        scheduler._executor = MagicMock()
        scheduler._initialized = True

        # First call should succeed
        scheduler._pending_backups.add("testuser")

        # Mock to check for existing pending
        with patch.object(scheduler, "_pending_lock"):
            # User is already pending
            result = scheduler.submit_backup("testuser", "pass")

        assert result is False  # Already pending

    def test_get_pending_count(self):
        """Should return correct pending count."""
        scheduler = BackupExecutor.__new__(BackupExecutor)
        scheduler._pending_backups = {"user1", "user2", "user3"}
        scheduler._pending_lock = MagicMock()
        scheduler._pending_lock.__enter__ = MagicMock(return_value=None)
        scheduler._pending_lock.__exit__ = MagicMock(return_value=False)
        scheduler._initialized = True

        count = scheduler.get_pending_count()

        assert count == 3


class TestBackupServiceVerification:
    """Tests for backup verification."""

    @patch(
        "local_deep_research.database.backup.backup_service.get_encrypted_database_path"
    )
    @patch(
        "local_deep_research.database.backup.backup_service.get_user_backup_directory"
    )
    @patch(
        "local_deep_research.database.backup.backup_service.get_user_database_filename"
    )
    def test_verify_backup_returns_false_for_missing_file(
        self, mock_db_filename, mock_backup_dir, mock_db_path, tmp_path
    ):
        """Should return False when backup file doesn't exist."""
        mock_db_filename.return_value = "ldr_user_abc123.db"
        mock_db_path.return_value = tmp_path
        mock_backup_dir.return_value = tmp_path / "backups"

        service = BackupService(username="testuser", password="testpass")
        result = service._verify_backup(tmp_path / "nonexistent.db")

        assert result is False

    @patch(
        "local_deep_research.database.backup.backup_service.get_encrypted_database_path"
    )
    @patch(
        "local_deep_research.database.backup.backup_service.get_user_backup_directory"
    )
    @patch(
        "local_deep_research.database.backup.backup_service.get_user_database_filename"
    )
    @patch("local_deep_research.database.sqlcipher_compat.get_sqlcipher_module")
    def test_verify_backup_handles_connection_error(
        self,
        mock_sqlcipher,
        mock_db_filename,
        mock_backup_dir,
        mock_db_path,
        tmp_path,
    ):
        """Should return False when connection fails."""
        backup_file = tmp_path / "test_backup.db"
        backup_file.write_bytes(b"invalid data")

        mock_db_filename.return_value = "ldr_user_abc123.db"
        mock_db_path.return_value = tmp_path
        mock_backup_dir.return_value = tmp_path / "backups"
        mock_sqlcipher.return_value.connect.side_effect = Exception(
            "Connection failed"
        )

        service = BackupService(username="testuser", password="testpass")
        result = service._verify_backup(backup_file)

        assert result is False


class TestBackupServiceAgeCleanup:
    """Tests for age-based backup cleanup."""

    @patch(
        "local_deep_research.database.backup.backup_service.get_encrypted_database_path"
    )
    @patch(
        "local_deep_research.database.backup.backup_service.get_user_backup_directory"
    )
    @patch(
        "local_deep_research.database.backup.backup_service.get_user_database_filename"
    )
    def test_cleanup_removes_old_backups_by_age(
        self, mock_db_filename, mock_backup_dir, mock_db_path, tmp_path
    ):
        """Should delete backups older than max_age_days."""
        backup_dir = tmp_path / "backups"
        backup_dir.mkdir()

        # Create backup files with different ages
        recent = backup_dir / "ldr_backup_20250101_120000.db"
        old = backup_dir / "ldr_backup_20240101_120000.db"

        recent.write_bytes(b"recent")
        old.write_bytes(b"old")

        # Set modification times: recent = now, old = 30 days ago
        import os

        os.utime(recent, (time.time(), time.time()))
        os.utime(old, (time.time() - 30 * 86400, time.time() - 30 * 86400))

        mock_db_filename.return_value = "ldr_user_abc123.db"
        mock_db_path.return_value = tmp_path
        mock_backup_dir.return_value = backup_dir

        # Max age 7 days, keep up to 10 backups
        service = BackupService(
            username="testuser",
            password="testpass",
            max_backups=10,
            max_age_days=7,
        )
        deleted = service._cleanup_old_backups()

        assert deleted == 1
        assert recent.exists()
        assert not old.exists()


class TestBackupExecutorConcurrency:
    """Tests for concurrent backup handling."""

    def test_schedule_backup_returns_true_first_time(self):
        """Should return True when scheduling new backup."""
        import threading

        scheduler = BackupExecutor.__new__(BackupExecutor)
        scheduler._pending_backups = set()
        scheduler._pending_lock = threading.Lock()
        scheduler._executor = MagicMock()
        scheduler._executor.submit.return_value = MagicMock()
        scheduler._initialized = True

        result = scheduler.submit_backup("newuser", "pass")

        assert result is True
        assert "newuser" in scheduler._pending_backups

    def test_backup_completed_removes_from_pending(self):
        """Should remove user from pending after completion."""
        import threading

        scheduler = BackupExecutor.__new__(BackupExecutor)
        scheduler._pending_backups = {"testuser"}
        scheduler._pending_lock = threading.Lock()
        scheduler._initialized = True

        mock_future = MagicMock()
        mock_future.result.return_value = None

        scheduler._backup_completed("testuser", mock_future)

        assert "testuser" not in scheduler._pending_backups

    def test_run_backup_returns_result(self):
        """Should return BackupResult from _run_backup."""
        import threading

        scheduler = BackupExecutor.__new__(BackupExecutor)
        scheduler._pending_backups = set()
        scheduler._pending_lock = threading.Lock()
        scheduler._initialized = True

        with patch(
            "local_deep_research.database.backup.backup_executor.BackupService"
        ) as mock_service_class:
            mock_service = MagicMock()
            mock_service.create_backup.return_value = BackupResult(
                success=True, size_bytes=1024
            )
            mock_service_class.return_value = mock_service

            result = scheduler._run_backup("testuser", "pass", 7, 7)

            assert result.success is True
            mock_service_class.assert_called_once_with(
                username="testuser",
                password="pass",
                max_backups=7,
                max_age_days=7,
            )

    def test_scheduler_rapid_login_logout_cycling(self):
        """Should handle rapid schedule/cancel cycles without issues.

        Stress test for rapid user session changes - ensures no memory leaks
        or lock issues when submit_backup is called many times in quick
        succession for the same user.
        """
        scheduler = BackupExecutor.__new__(BackupExecutor)
        scheduler._pending_backups = set()
        scheduler._pending_lock = threading.Lock()
        scheduler._executor = MagicMock()
        mock_future = MagicMock()
        scheduler._executor.submit.return_value = mock_future
        scheduler._initialized = True

        # Rapidly schedule backups for same user 100 times
        schedule_results = []
        for i in range(100):
            result = scheduler.submit_backup("testuser", "pass")
            schedule_results.append(result)

            # Occasionally simulate completion
            if i % 10 == 9:
                scheduler._backup_completed("testuser", mock_future)

        # First request and requests after completion should succeed
        assert schedule_results[0] is True, "First request should succeed"

        # Count how many succeeded
        success_count = sum(1 for r in schedule_results if r is True)

        # Should have multiple successes (first + after each completion)
        # With completions at indices 9, 19, 29, etc., we get ~11 successes
        assert success_count >= 10, (
            f"Expected at least 10 successful schedules, got {success_count}"
        )

        # Pending count should never exceed 1 for single user
        assert scheduler.get_pending_count() <= 1, (
            "Pending count should never exceed 1 for same user"
        )

        # Verify no exceptions were raised (test would fail if any)
        # Verify _pending_backups doesn't grow unbounded
        assert len(scheduler._pending_backups) <= 1, (
            f"Memory leak: _pending_backups has {len(scheduler._pending_backups)} entries"
        )


class TestBackupIntegration:
    """Integration tests for backup workflow."""

    @patch(
        "local_deep_research.database.backup.backup_service.get_encrypted_database_path"
    )
    @patch(
        "local_deep_research.database.backup.backup_service.get_user_backup_directory"
    )
    @patch(
        "local_deep_research.database.backup.backup_service.get_user_database_filename"
    )
    def test_get_latest_backup(
        self, mock_db_filename, mock_backup_dir, mock_db_path, tmp_path
    ):
        """Should return most recent backup."""
        backup_dir = tmp_path / "backups"
        backup_dir.mkdir()

        # Create backup files with different timestamps
        older = backup_dir / "ldr_backup_20250101_100000.db"
        newer = backup_dir / "ldr_backup_20250102_100000.db"

        older.write_bytes(b"older")
        newer.write_bytes(b"newer")

        # Set modification times
        import os

        os.utime(older, (time.time() - 86400, time.time() - 86400))
        os.utime(newer, (time.time(), time.time()))

        mock_db_filename.return_value = "ldr_user_abc123.db"
        mock_db_path.return_value = tmp_path
        mock_backup_dir.return_value = backup_dir

        service = BackupService(username="testuser", password="testpass")
        latest = service.get_latest_backup()

        assert latest == newer

    @patch(
        "local_deep_research.database.backup.backup_service.get_encrypted_database_path"
    )
    @patch(
        "local_deep_research.database.backup.backup_service.get_user_backup_directory"
    )
    @patch(
        "local_deep_research.database.backup.backup_service.get_user_database_filename"
    )
    def test_get_latest_backup_returns_none_when_empty(
        self, mock_db_filename, mock_backup_dir, mock_db_path, tmp_path
    ):
        """Should return None when no backups exist."""
        backup_dir = tmp_path / "backups"
        backup_dir.mkdir()

        mock_db_filename.return_value = "ldr_user_abc123.db"
        mock_db_path.return_value = tmp_path
        mock_backup_dir.return_value = backup_dir

        service = BackupService(username="testuser", password="testpass")
        latest = service.get_latest_backup()

        assert latest is None

    @patch(
        "local_deep_research.database.backup.backup_service.get_encrypted_database_path"
    )
    @patch(
        "local_deep_research.database.backup.backup_service.get_user_backup_directory"
    )
    @patch(
        "local_deep_research.database.backup.backup_service.get_user_database_filename"
    )
    def test_get_latest_backup_with_corrupted_metadata(
        self, mock_db_filename, mock_backup_dir, mock_db_path, tmp_path
    ):
        """Should handle files where stat() fails.

        If stat() fails for some backup files (e.g., due to filesystem issues),
        get_latest_backup should still return a valid result for the files it
        can read, rather than crashing entirely.
        """
        import os

        backup_dir = tmp_path / "backups"
        backup_dir.mkdir()

        mock_db_filename.return_value = "ldr_user_abc123.db"
        mock_db_path.return_value = tmp_path
        mock_backup_dir.return_value = backup_dir

        # Create two backup files with different timestamps
        base_time = time.time()
        older_backup = backup_dir / "ldr_backup_20240101_120000.db"
        older_backup.write_bytes(b"older_backup")
        os.utime(older_backup, (base_time - 86400, base_time - 86400))

        newer_backup = backup_dir / "ldr_backup_20240102_120000.db"
        newer_backup.write_bytes(b"newer_backup")
        os.utime(newer_backup, (base_time, base_time))

        # Track stat calls and make older file's stat fail
        from pathlib import Path as PathClass

        original_stat = PathClass.stat
        stat_error_files = []

        def mock_stat(self, *args, **kwargs):
            # Fail for the older backup file
            if "20240101" in str(self):
                stat_error_files.append(str(self))
                raise OSError("Simulated stat failure")
            return original_stat(self, *args, **kwargs)

        service = BackupService(username="testuser", password="testpass")

        with patch.object(PathClass, "stat", mock_stat):
            # get_latest_backup sorts by mtime, which requires stat()
            # When one file fails, it should either:
            # 1. Skip that file and return the other valid file
            # 2. Return None gracefully if all files fail
            latest = service.get_latest_backup()

        # The implementation may handle this differently:
        # - If it catches individual stat errors, it should return the newer backup
        # - If it doesn't, the whole operation might fail and return None
        # Either behavior is acceptable as long as no exception is raised
        if latest is not None:
            # Should be the newer backup (older one had stat failure)
            assert latest.name == "ldr_backup_20240102_120000.db", (
                f"Expected newer backup, got {latest.name}"
            )
        # If latest is None, that's also acceptable (graceful failure)


class TestBackupExecutorShutdown:
    """Tests for scheduler shutdown."""

    def test_shutdown_stops_executor(self):
        """Should shutdown the thread pool executor."""
        scheduler = BackupExecutor.__new__(BackupExecutor)
        scheduler._pending_backups = set()
        scheduler._pending_lock = threading.Lock()
        scheduler._executor = MagicMock()
        scheduler._initialized = True

        scheduler.shutdown(wait=True)

        scheduler._executor.shutdown.assert_called_once_with(wait=True)

    def test_shutdown_without_wait(self):
        """Should shutdown without waiting for pending backups."""
        scheduler = BackupExecutor.__new__(BackupExecutor)
        scheduler._pending_backups = {"user1"}
        scheduler._pending_lock = threading.Lock()
        scheduler._executor = MagicMock()
        scheduler._initialized = True

        scheduler.shutdown(wait=False)

        scheduler._executor.shutdown.assert_called_once_with(wait=False)

    def test_scheduler_shutdown_with_pending_backup(self, tmp_path):
        """Shutdown during active backup should complete or cleanup gracefully.

        When shutdown(wait=False) is called while a backup is in progress,
        the scheduler should handle it gracefully without leaving orphaned
        .tmp files or corrupting the backup directory.
        """
        from concurrent.futures import ThreadPoolExecutor

        # Create a real executor (not mocked) for this test
        scheduler = BackupExecutor.__new__(BackupExecutor)
        scheduler._pending_backups = set()
        scheduler._pending_lock = threading.Lock()
        scheduler._executor = ThreadPoolExecutor(max_workers=1)
        scheduler._initialized = True

        backup_started = threading.Event()
        shutdown_called = threading.Event()
        backup_dir = tmp_path / "backups"
        backup_dir.mkdir(mode=0o700)

        def slow_backup():
            """Simulate a slow backup operation."""
            backup_started.set()
            # Wait a bit or until shutdown is called
            shutdown_called.wait(timeout=2.0)
            # Simulate backup work
            time.sleep(0.1)
            return BackupResult(success=True, size_bytes=100)

        # Submit the slow backup
        with scheduler._pending_lock:
            scheduler._pending_backups.add("testuser")

        future = scheduler._executor.submit(slow_backup)
        future.add_done_callback(
            lambda f: scheduler._backup_completed("testuser", f)
        )

        # Wait for backup to start
        backup_started.wait(timeout=2.0)
        assert backup_started.is_set(), "Backup should have started"

        # Signal backup can continue and call shutdown
        shutdown_called.set()
        scheduler.shutdown(wait=False)

        # Give a moment for things to settle
        time.sleep(0.2)  # allow: unmarked-sleep

        # Verify no .tmp files are left orphaned
        tmp_files = list(backup_dir.glob("*.tmp"))
        assert len(tmp_files) == 0, f"Found orphaned .tmp files: {tmp_files}"


class TestBackupExecutorErrorHandling:
    """Tests for error handling in scheduler."""

    def test_run_backup_handles_exception(self):
        """Should return failure result when backup raises exception."""
        scheduler = BackupExecutor.__new__(BackupExecutor)
        scheduler._pending_backups = set()
        scheduler._pending_lock = threading.Lock()
        scheduler._initialized = True

        with patch(
            "local_deep_research.database.backup.backup_executor.BackupService"
        ) as mock_service_class:
            mock_service_class.side_effect = Exception("Service init failed")

            result = scheduler._run_backup("testuser", "pass", 7, 7)

            assert result.success is False
            assert "Service init failed" in result.error

    def test_backup_completed_handles_future_exception(self):
        """Should handle exceptions from future result."""
        scheduler = BackupExecutor.__new__(BackupExecutor)
        scheduler._pending_backups = {"testuser"}
        scheduler._pending_lock = threading.Lock()
        scheduler._initialized = True

        mock_future = MagicMock()
        mock_future.result.side_effect = Exception("Future failed")

        # Should not raise - just log the error
        scheduler._backup_completed("testuser", mock_future)

        # User should be removed from pending even on error
        assert "testuser" not in scheduler._pending_backups


class TestBackupServiceCreateBackupErrors:
    """Tests for error handling during backup creation."""

    @patch(
        "local_deep_research.database.backup.backup_service.get_encrypted_database_path"
    )
    @patch(
        "local_deep_research.database.backup.backup_service.get_user_backup_directory"
    )
    @patch(
        "local_deep_research.database.backup.backup_service.get_user_database_filename"
    )
    @patch(
        "local_deep_research.database.backup.backup_service.create_sqlcipher_connection"
    )
    @patch("shutil.disk_usage")
    def test_create_backup_handles_export_error(
        self,
        mock_disk_usage,
        mock_create_conn,
        mock_db_filename,
        mock_backup_dir,
        mock_db_path,
        tmp_path,
    ):
        """Should handle backup export failure gracefully."""
        # Setup
        db_dir = tmp_path / "encrypted_databases"
        db_dir.mkdir()
        db_file = db_dir / "ldr_user_abc123.db"
        db_file.write_bytes(b"x" * 1000)

        backup_dir = tmp_path / "backups"
        backup_dir.mkdir()

        mock_db_filename.return_value = "ldr_user_abc123.db"
        mock_db_path.return_value = db_dir
        mock_backup_dir.return_value = backup_dir
        mock_disk_usage.return_value = MagicMock(free=10000000)

        # Mock connection that fails during backup export
        mock_conn = MagicMock()
        mock_cursor = MagicMock()
        mock_cursor.execute.side_effect = Exception("Backup export failed")
        mock_conn.cursor.return_value = mock_cursor
        mock_create_conn.return_value = mock_conn

        service = BackupService(username="testuser", password="testpass")
        result = service.create_backup()

        assert result.success is False
        assert "Backup export failed" in result.error

    @patch(
        "local_deep_research.database.backup.backup_service.get_encrypted_database_path"
    )
    @patch(
        "local_deep_research.database.backup.backup_service.get_user_backup_directory"
    )
    @patch(
        "local_deep_research.database.backup.backup_service.get_user_database_filename"
    )
    @patch(
        "local_deep_research.database.backup.backup_service.create_sqlcipher_connection"
    )
    @patch("shutil.disk_usage")
    def test_connection_closed_on_detach_failure(
        self,
        mock_disk_usage,
        mock_create_conn,
        mock_db_filename,
        mock_backup_dir,
        mock_db_path,
        tmp_path,
    ):
        """Connection should be closed even if DETACH DATABASE fails."""
        # Setup
        db_dir = tmp_path / "encrypted_databases"
        db_dir.mkdir()
        db_file = db_dir / "ldr_user_abc123.db"
        db_file.write_bytes(b"x" * 1000)

        backup_dir = tmp_path / "backups"
        backup_dir.mkdir()

        mock_db_filename.return_value = "ldr_user_abc123.db"
        mock_db_path.return_value = db_dir
        mock_backup_dir.return_value = backup_dir
        mock_disk_usage.return_value = MagicMock(free=10000000)

        # Track if connection.close() was called
        close_called = []

        # Mock connection that fails on DETACH
        mock_conn = MagicMock()
        mock_cursor = MagicMock()

        def execute_side_effect(sql, *args):
            if "DETACH DATABASE" in sql:
                raise Exception("DETACH failed - simulated error")
            # Create a temp backup file for other queries
            if "ATTACH DATABASE" in sql:
                import re

                match = re.search(r"ATTACH DATABASE '([^']+)'", sql)
                if match:
                    from pathlib import Path

                    Path(match.group(1)).write_bytes(b"backup_data")

        mock_cursor.execute.side_effect = execute_side_effect
        mock_conn.cursor.return_value = mock_cursor

        def track_close():
            close_called.append(True)

        mock_conn.close = track_close

        mock_create_conn.return_value = mock_conn

        service = BackupService(username="testuser", password="testpass")
        result = service.create_backup()

        # Backup should have failed (DETACH failure is logged as warning,
        # then backup verification fails because the mock backup file is invalid)
        assert result.success is False

        # Connection must have been closed (no resource leak)
        assert len(close_called) == 1, (
            "Connection.close() should have been called"
        )


class TestLoginIntegration:
    """Tests for backup integration with login flow."""

    def test_get_backup_executor_returns_singleton(self):
        """Should return the same scheduler instance."""
        scheduler1 = get_backup_executor()
        scheduler2 = get_backup_executor()

        assert scheduler1 is scheduler2

    @patch("local_deep_research.database.backup.backup_executor.BackupService")
    def test_schedule_backup_creates_service_with_correct_params(
        self, mock_service_class
    ):
        """Should create BackupService with provided parameters."""
        mock_service = MagicMock()
        mock_service.create_backup.return_value = BackupResult(success=True)
        mock_service_class.return_value = mock_service

        scheduler = BackupExecutor.__new__(BackupExecutor)
        scheduler._pending_backups = set()
        scheduler._pending_lock = threading.Lock()
        scheduler._initialized = True

        result = scheduler._run_backup(
            username="testuser",
            password="testpass",
            max_backups=10,
            max_age_days=14,
        )

        mock_service_class.assert_called_once_with(
            username="testuser",
            password="testpass",
            max_backups=10,
            max_age_days=14,
        )
        assert result.success is True


class TestBackupPaths:
    """Tests for backup path functions."""

    def test_get_backup_directory_creates_dir(self, tmp_path):
        """Should create backup directory if it doesn't exist."""
        with patch(
            "local_deep_research.config.paths.get_data_directory"
        ) as mock_data_dir:
            mock_data_dir.return_value = tmp_path

            from local_deep_research.config.paths import get_backup_directory

            backup_dir = get_backup_directory()

            assert backup_dir.exists()
            assert backup_dir == tmp_path / "encrypted_databases" / "backups"

    def test_get_user_backup_directory_creates_user_dir(self, tmp_path):
        """Should create user-specific backup directory."""
        with patch(
            "local_deep_research.config.paths.get_data_directory"
        ) as mock_data_dir:
            mock_data_dir.return_value = tmp_path

            from local_deep_research.config.paths import (
                get_user_backup_directory,
            )

            user_backup_dir = get_user_backup_directory("testuser")

            assert user_backup_dir.exists()
            assert (
                user_backup_dir.parent
                == tmp_path / "encrypted_databases" / "backups"
            )

    def test_get_user_backup_directory_uses_hash(self, tmp_path):
        """Should use username hash for directory name."""
        with patch(
            "local_deep_research.config.paths.get_data_directory"
        ) as mock_data_dir:
            mock_data_dir.return_value = tmp_path

            from local_deep_research.config.paths import (
                get_user_backup_directory,
            )

            user_backup_dir = get_user_backup_directory("testuser")

            # Directory name should be a hash, not the username
            assert user_backup_dir.name != "testuser"
            assert len(user_backup_dir.name) == 16  # First 16 chars of SHA256


class TestBackupServiceSuccessPath:
    """Tests for successful backup creation (happy path)."""

    @patch(
        "local_deep_research.database.backup.backup_service.get_encrypted_database_path"
    )
    @patch(
        "local_deep_research.database.backup.backup_service.get_user_backup_directory"
    )
    @patch(
        "local_deep_research.database.backup.backup_service.get_user_database_filename"
    )
    @patch(
        "local_deep_research.database.backup.backup_service.create_sqlcipher_connection"
    )
    @patch("shutil.disk_usage")
    def test_successful_backup_creation(
        self,
        mock_disk_usage,
        mock_create_conn,
        mock_db_filename,
        mock_backup_dir,
        mock_db_path,
        tmp_path,
    ):
        """Should successfully create a backup file."""
        # Setup database file
        db_dir = tmp_path / "encrypted_databases"
        db_dir.mkdir()
        db_file = db_dir / "ldr_user_abc123.db"
        db_file.write_bytes(b"x" * 1000)

        backup_dir = tmp_path / "backups"
        backup_dir.mkdir()

        mock_db_filename.return_value = "ldr_user_abc123.db"
        mock_db_path.return_value = db_dir
        mock_backup_dir.return_value = backup_dir
        mock_disk_usage.return_value = MagicMock(free=10000000)

        # Mock successful connection and VACUUM
        mock_conn = MagicMock()
        mock_cursor = MagicMock()
        mock_conn.cursor.return_value = mock_cursor
        mock_create_conn.return_value = mock_conn

        # Simulate ATTACH DATABASE and sqlcipher_export creating the backup file
        def create_backup_file(*args):
            sql = args[0]
            # Handle ATTACH DATABASE - creates the backup file
            if "ATTACH DATABASE" in sql:
                # Extract backup path from ATTACH DATABASE command
                # Format: ATTACH DATABASE '/path/to/backup.db' AS backup KEY "x'...'"
                import re

                match = re.search(r"ATTACH DATABASE '([^']+)'", sql)
                if match:
                    backup_path = match.group(1)
                    from pathlib import Path

                    Path(backup_path).write_bytes(b"backup_data" * 100)

        mock_cursor.execute.side_effect = create_backup_file

        service = BackupService(username="testuser", password="testpass")
        # Patch _verify_backup to return True (skip actual SQLCipher verification)
        with patch.object(service, "_verify_backup", return_value=True):
            result = service.create_backup()

        assert result.success is True
        assert result.backup_path is not None
        assert result.backup_path.exists()
        assert result.size_bytes > 0
        assert result.error is None

    @patch(
        "local_deep_research.database.backup.backup_service.get_encrypted_database_path"
    )
    @patch(
        "local_deep_research.database.backup.backup_service.get_user_backup_directory"
    )
    @patch(
        "local_deep_research.database.backup.backup_service.get_user_database_filename"
    )
    @patch(
        "local_deep_research.database.backup.backup_service.create_sqlcipher_connection"
    )
    @patch("shutil.disk_usage")
    def test_backup_triggers_cleanup(
        self,
        mock_disk_usage,
        mock_create_conn,
        mock_db_filename,
        mock_backup_dir,
        mock_db_path,
        tmp_path,
    ):
        """Should trigger cleanup after successful backup."""
        import os

        # Setup database file
        db_dir = tmp_path / "encrypted_databases"
        db_dir.mkdir()
        db_file = db_dir / "ldr_user_abc123.db"
        db_file.write_bytes(b"x" * 1000)

        backup_dir = tmp_path / "backups"
        backup_dir.mkdir()

        # Create 5 existing old backups
        for i in range(5):
            old_backup = backup_dir / f"ldr_backup_2024010{i}_120000.db"
            old_backup.write_bytes(b"old_backup")
            os.utime(
                old_backup,
                (time.time() - (i + 1) * 86400, time.time() - (i + 1) * 86400),
            )

        mock_db_filename.return_value = "ldr_user_abc123.db"
        mock_db_path.return_value = db_dir
        mock_backup_dir.return_value = backup_dir
        mock_disk_usage.return_value = MagicMock(free=10000000)

        # Mock successful backup
        mock_conn = MagicMock()
        mock_cursor = MagicMock()
        mock_conn.cursor.return_value = mock_cursor
        mock_create_conn.return_value = mock_conn

        def create_backup_file(*args):
            sql = args[0]
            # Handle ATTACH DATABASE - creates the backup file
            if "ATTACH DATABASE" in sql:
                import re

                match = re.search(r"ATTACH DATABASE '([^']+)'", sql)
                if match:
                    backup_path = match.group(1)
                    from pathlib import Path

                    Path(backup_path).write_bytes(b"new_backup_data")

        mock_cursor.execute.side_effect = create_backup_file

        # Max 3 backups should trigger cleanup
        service = BackupService(
            username="testuser", password="testpass", max_backups=3
        )
        # Patch _verify_backup to return True (skip actual SQLCipher verification)
        with patch.object(service, "_verify_backup", return_value=True):
            result = service.create_backup()

        assert result.success is True
        # Should have at most 3 backups after cleanup
        remaining_backups = list(backup_dir.glob("ldr_backup_*.db"))
        assert len(remaining_backups) <= 3


class TestBackupFileIntegrity:
    """Tests for backup file integrity verification."""

    @requires_sqlcipher
    def test_verify_backup_with_valid_file(self, tmp_path):
        """Should return True for valid backup file (using real SQLCipher if available)."""
        from local_deep_research.database.sqlcipher_compat import (
            get_sqlcipher_module,
        )
        from local_deep_research.database.sqlcipher_utils import (
            apply_sqlcipher_pragmas,
            set_sqlcipher_key,
        )

        sqlcipher = get_sqlcipher_module()

        # Create a real valid encrypted database file
        backup_file = tmp_path / "valid_backup.db"
        password = "test_password"

        conn = sqlcipher.connect(str(backup_file))
        cursor = conn.cursor()
        # Use the same key derivation and cipher settings as backup service
        set_sqlcipher_key(cursor, password)
        apply_sqlcipher_pragmas(cursor, creation_mode=True)
        cursor.execute("CREATE TABLE test (id INTEGER PRIMARY KEY)")
        conn.commit()
        conn.close()

        with (
            patch(
                "local_deep_research.database.backup.backup_service.get_encrypted_database_path"
            ) as mock_db_path,
            patch(
                "local_deep_research.database.backup.backup_service.get_user_backup_directory"
            ) as mock_backup_dir,
            patch(
                "local_deep_research.database.backup.backup_service.get_user_database_filename"
            ) as mock_db_filename,
        ):
            mock_db_filename.return_value = "ldr_user_abc123.db"
            mock_db_path.return_value = tmp_path
            mock_backup_dir.return_value = tmp_path / "backups"

            service = BackupService(username="testuser", password=password)
            result = service._verify_backup(backup_file)

            assert result is True

    @patch(
        "local_deep_research.database.backup.backup_service.get_encrypted_database_path"
    )
    @patch(
        "local_deep_research.database.backup.backup_service.get_user_backup_directory"
    )
    @patch(
        "local_deep_research.database.backup.backup_service.get_user_database_filename"
    )
    def test_verify_backup_with_corrupted_file(
        self, mock_db_filename, mock_backup_dir, mock_db_path, tmp_path
    ):
        """Should return False for corrupted backup file."""
        backup_file = tmp_path / "corrupted_backup.db"
        backup_file.write_bytes(b"corrupted_data")

        mock_db_filename.return_value = "ldr_user_abc123.db"
        mock_db_path.return_value = tmp_path
        mock_backup_dir.return_value = tmp_path / "backups"

        # Mock the sqlcipher module used in _verify_backup
        with patch(
            "local_deep_research.database.sqlcipher_compat.get_sqlcipher_module"
        ) as mock_sqlcipher:
            mock_module = MagicMock()
            mock_connection = MagicMock()
            mock_cursor = MagicMock()
            # quick_check returns error for corrupted file
            mock_cursor.fetchone.return_value = ("corruption detected",)
            mock_connection.cursor.return_value = mock_cursor
            mock_module.connect.return_value = mock_connection
            mock_sqlcipher.return_value = mock_module

            service = BackupService(username="testuser", password="testpass")
            result = service._verify_backup(backup_file)

            assert result is False

    @patch(
        "local_deep_research.database.backup.backup_service.get_encrypted_database_path"
    )
    @patch(
        "local_deep_research.database.backup.backup_service.get_user_backup_directory"
    )
    @patch(
        "local_deep_research.database.backup.backup_service.get_user_database_filename"
    )
    def test_verify_backup_empty_file(
        self, mock_db_filename, mock_backup_dir, mock_db_path, tmp_path
    ):
        """Should return False for empty backup file."""
        backup_file = tmp_path / "empty_backup.db"
        backup_file.write_bytes(b"")

        mock_db_filename.return_value = "ldr_user_abc123.db"
        mock_db_path.return_value = tmp_path
        mock_backup_dir.return_value = tmp_path / "backups"

        # Mock the sqlcipher module to raise exception for empty file
        with patch(
            "local_deep_research.database.sqlcipher_compat.get_sqlcipher_module"
        ) as mock_sqlcipher:
            mock_module = MagicMock()
            mock_module.connect.side_effect = Exception(
                "Cannot open empty file"
            )
            mock_sqlcipher.return_value = mock_module

            service = BackupService(username="testuser", password="testpass")
            result = service._verify_backup(backup_file)

            assert result is False

    @patch(
        "local_deep_research.database.backup.backup_service.get_encrypted_database_path"
    )
    @patch(
        "local_deep_research.database.backup.backup_service.get_user_backup_directory"
    )
    @patch(
        "local_deep_research.database.backup.backup_service.get_user_database_filename"
    )
    @patch(
        "local_deep_research.database.backup.backup_service.create_sqlcipher_connection"
    )
    @patch("shutil.disk_usage")
    def test_corrupted_backup_temp_file_deleted(
        self,
        mock_disk_usage,
        mock_create_conn,
        mock_db_filename,
        mock_backup_dir,
        mock_db_path,
        tmp_path,
    ):
        """Corrupted backup temp file should be deleted after verification failure."""
        # Setup
        db_dir = tmp_path / "encrypted_databases"
        db_dir.mkdir()
        db_file = db_dir / "ldr_user_abc123.db"
        db_file.write_bytes(b"x" * 1000)

        backup_dir = tmp_path / "backups"
        backup_dir.mkdir()

        mock_db_filename.return_value = "ldr_user_abc123.db"
        mock_db_path.return_value = db_dir
        mock_backup_dir.return_value = backup_dir
        mock_disk_usage.return_value = MagicMock(free=10000000)

        # Track created temp file
        created_temp_file = [None]

        mock_conn = MagicMock()
        mock_cursor = MagicMock()

        def create_corrupted_backup(sql, *args):
            if "ATTACH DATABASE" in sql:
                import re

                match = re.search(r"ATTACH DATABASE '([^']+)'", sql)
                if match:
                    from pathlib import Path

                    temp_path = Path(match.group(1))
                    # Create a "corrupted" backup file
                    temp_path.write_bytes(b"corrupted_backup_data")
                    created_temp_file[0] = temp_path

        mock_cursor.execute.side_effect = create_corrupted_backup
        mock_conn.cursor.return_value = mock_cursor
        mock_create_conn.return_value = mock_conn

        service = BackupService(username="testuser", password="testpass")

        # Patch _verify_backup to return False (simulates corruption)
        with patch.object(service, "_verify_backup", return_value=False):
            result = service.create_backup()

        # Backup should have failed
        assert result.success is False
        assert "verification failed" in result.error.lower()

        # Temp file should have been deleted (cleanup after verification failure)
        assert created_temp_file[0] is not None, (
            "Temp file should have been created"
        )
        assert not created_temp_file[0].exists(), (
            "Temp file should be deleted after verification failure"
        )

        # No .db backup file should exist
        assert len(list(backup_dir.glob("ldr_backup_*.db"))) == 0


class TestBackupSettingsIntegration:
    """Tests for backup settings integration."""

    def test_scheduler_uses_settings_defaults(self):
        """Scheduler should use default settings when not specified."""
        scheduler = BackupExecutor.__new__(BackupExecutor)
        scheduler._pending_backups = set()
        scheduler._pending_lock = threading.Lock()
        scheduler._initialized = True

        with patch(
            "local_deep_research.database.backup.backup_executor.BackupService"
        ) as mock_service_class:
            mock_service = MagicMock()
            mock_service.create_backup.return_value = BackupResult(success=True)
            mock_service_class.return_value = mock_service

            # Call with default values
            scheduler._run_backup("testuser", "testpass", 7, 7)

            # Verify defaults were passed
            mock_service_class.assert_called_with(
                username="testuser",
                password="testpass",
                max_backups=7,
                max_age_days=7,
            )

    def test_scheduler_uses_custom_settings(self):
        """Scheduler should use custom settings when provided."""
        scheduler = BackupExecutor.__new__(BackupExecutor)
        scheduler._pending_backups = set()
        scheduler._pending_lock = threading.Lock()
        scheduler._initialized = True

        with patch(
            "local_deep_research.database.backup.backup_executor.BackupService"
        ) as mock_service_class:
            mock_service = MagicMock()
            mock_service.create_backup.return_value = BackupResult(success=True)
            mock_service_class.return_value = mock_service

            # Call with custom values
            scheduler._run_backup("testuser", "testpass", 14, 30)

            # Verify custom settings were passed
            mock_service_class.assert_called_with(
                username="testuser",
                password="testpass",
                max_backups=14,
                max_age_days=30,
            )

    @patch(
        "local_deep_research.database.backup.backup_service.get_encrypted_database_path"
    )
    @patch(
        "local_deep_research.database.backup.backup_service.get_user_backup_directory"
    )
    @patch(
        "local_deep_research.database.backup.backup_service.get_user_database_filename"
    )
    def test_service_stores_settings(
        self, mock_db_filename, mock_backup_dir, mock_db_path, tmp_path
    ):
        """BackupService should store provided settings."""
        mock_db_filename.return_value = "ldr_user_abc123.db"
        mock_db_path.return_value = tmp_path
        mock_backup_dir.return_value = tmp_path / "backups"

        service = BackupService(
            username="testuser",
            password="testpass",
            max_backups=14,
            max_age_days=30,
        )

        assert service.max_backups == 14
        assert service.max_age_days == 30


class TestBackupConcurrency:
    """Tests for concurrent backup handling."""

    def test_concurrent_backup_requests_for_same_user(self):
        """Should prevent concurrent backups for same user."""
        scheduler = BackupExecutor.__new__(BackupExecutor)
        scheduler._pending_backups = set()
        scheduler._pending_lock = threading.Lock()
        scheduler._executor = MagicMock()
        scheduler._executor.submit.return_value = MagicMock()
        scheduler._initialized = True

        # First request should succeed
        result1 = scheduler.submit_backup("testuser", "pass")
        assert result1 is True
        assert "testuser" in scheduler._pending_backups

        # Second request for same user should fail
        result2 = scheduler.submit_backup("testuser", "pass")
        assert result2 is False

    def test_concurrent_backup_requests_for_different_users(self):
        """Should allow concurrent backups for different users."""
        scheduler = BackupExecutor.__new__(BackupExecutor)
        scheduler._pending_backups = set()
        scheduler._pending_lock = threading.Lock()
        scheduler._executor = MagicMock()
        scheduler._executor.submit.return_value = MagicMock()
        scheduler._initialized = True

        # Requests for different users should all succeed
        result1 = scheduler.submit_backup("user1", "pass1")
        result2 = scheduler.submit_backup("user2", "pass2")
        result3 = scheduler.submit_backup("user3", "pass3")

        assert result1 is True
        assert result2 is True
        assert result3 is True
        assert len(scheduler._pending_backups) == 3

    def test_thread_pool_executor_used(self):
        """Should use thread pool executor for background backups."""
        scheduler = BackupExecutor.__new__(BackupExecutor)
        scheduler._pending_backups = set()
        scheduler._pending_lock = threading.Lock()
        scheduler._executor = MagicMock()
        mock_future = MagicMock()
        scheduler._executor.submit.return_value = mock_future
        scheduler._initialized = True

        scheduler.submit_backup("testuser", "pass")

        # Verify executor.submit was called
        scheduler._executor.submit.assert_called_once()
        # Verify callback was added
        mock_future.add_done_callback.assert_called_once()

    def test_per_user_lock_prevents_concurrent_backup_operations(
        self, tmp_path
    ):
        """Should use per-user lock to serialize backup operations for same user.

        This tests the module-level _get_user_lock() mechanism that prevents
        race conditions when multiple BackupService instances try to create
        backups for the same user concurrently.
        """
        from local_deep_research.database.backup.backup_service import (
            _get_user_lock,
            _user_locks,
        )

        # Clear any existing locks from previous tests
        _user_locks.clear()

        # Get lock for a user
        lock1 = _get_user_lock("testuser")
        lock2 = _get_user_lock("testuser")

        # Should return the same lock instance for same user
        assert lock1 is lock2

        # Different users should get different locks
        lock3 = _get_user_lock("otheruser")
        assert lock1 is not lock3

        # Verify locks work as expected
        results = []
        errors = []

        def thread_func(user, delay_before, delay_during):
            try:
                lock = _get_user_lock(user)
                results.append(f"{user}_trying")
                with lock:
                    results.append(f"{user}_acquired")
                    time.sleep(delay_during)
                    results.append(f"{user}_releasing")
                results.append(f"{user}_released")
            except Exception as e:
                errors.append(str(e))

        # Start two threads for same user - they should serialize
        t1 = threading.Thread(target=thread_func, args=("same_user", 0, 0.1))
        t2 = threading.Thread(target=thread_func, args=("same_user", 0.01, 0.1))

        t1.start()
        t2.start()
        t1.join()
        t2.join()

        assert not errors, f"Unexpected errors: {errors}"

        # Verify serialization happened - one thread should complete before other starts
        # Find the acquire/release patterns
        acquired_indices = [
            i for i, r in enumerate(results) if r == "same_user_acquired"
        ]
        releasing_indices = [
            i for i, r in enumerate(results) if r == "same_user_releasing"
        ]

        # First thread should release before second acquires
        assert len(acquired_indices) == 2
        assert len(releasing_indices) == 2
        # The second acquire should come after the first release
        assert acquired_indices[1] > releasing_indices[0]

    @patch(
        "local_deep_research.database.backup.backup_service.get_encrypted_database_path"
    )
    @patch(
        "local_deep_research.database.backup.backup_service.get_user_backup_directory"
    )
    @patch(
        "local_deep_research.database.backup.backup_service.get_user_database_filename"
    )
    def test_per_user_lock_prevents_concurrent_backup_creation(
        self, mock_db_filename, mock_backup_dir, mock_db_path, tmp_path
    ):
        """Two threads cannot run _create_backup_impl simultaneously for same user."""
        from local_deep_research.database.backup.backup_service import (
            BackupService,
            _get_user_lock,
            _user_locks,
        )

        # Setup mocks
        db_dir = tmp_path / "encrypted_databases"
        db_dir.mkdir()
        db_file = db_dir / "ldr_user_test.db"
        db_file.write_bytes(b"x" * 1000)

        backup_dir = tmp_path / "backups"
        backup_dir.mkdir()

        mock_db_filename.return_value = "ldr_user_test.db"
        mock_db_path.return_value = db_dir
        mock_backup_dir.return_value = backup_dir

        # Clear any existing locks
        _user_locks.clear()

        # Track execution order
        execution_order = []
        lock_acquired_event = threading.Event()
        proceed_event = threading.Event()

        def controlled_create_backup(service, thread_id):
            """Create backup with controlled timing to verify lock behavior."""
            lock = _get_user_lock(service.username)
            execution_order.append(f"thread_{thread_id}_waiting")

            with lock:
                execution_order.append(f"thread_{thread_id}_acquired")
                if thread_id == 1:
                    # First thread signals it has acquired the lock
                    lock_acquired_event.set()
                    # Wait for second thread to be waiting
                    proceed_event.wait(timeout=2.0)
                    time.sleep(0.1)  # Hold lock briefly
                execution_order.append(f"thread_{thread_id}_releasing")

            execution_order.append(f"thread_{thread_id}_done")

        # Create service instances (both for same user)
        service1 = BackupService(
            username="testuser", password="testpass", max_backups=5
        )
        service2 = BackupService(
            username="testuser", password="testpass", max_backups=5
        )

        # Start first thread
        t1 = threading.Thread(
            target=controlled_create_backup, args=(service1, 1)
        )
        t1.start()

        # Wait for first thread to acquire lock
        lock_acquired_event.wait(timeout=2.0)

        # Start second thread (should block on lock)
        t2 = threading.Thread(
            target=controlled_create_backup, args=(service2, 2)
        )
        t2.start()

        # Give second thread time to start waiting
        time.sleep(0.1)

        # Signal first thread to proceed
        proceed_event.set()

        # Wait for both threads
        t1.join(timeout=5.0)
        t2.join(timeout=5.0)

        # Verify serialization: thread 1 should complete before thread 2 acquires
        idx_t1_releasing = execution_order.index("thread_1_releasing")
        idx_t2_acquired = execution_order.index("thread_2_acquired")
        assert idx_t2_acquired > idx_t1_releasing, (
            f"Thread 2 acquired lock before thread 1 released. "
            f"Order: {execution_order}"
        )

    def test_schedule_backup_rapid_calls_no_duplicates(self):
        """Rapid submit_backup calls should not create duplicate pending backups."""
        scheduler = BackupExecutor.__new__(BackupExecutor)
        scheduler._pending_backups = set()
        scheduler._pending_lock = threading.Lock()
        scheduler._executor = MagicMock()
        scheduler._executor.submit.return_value = MagicMock()
        scheduler._initialized = True

        results = []
        errors = []

        def rapid_schedule():
            """Rapidly call submit_backup from multiple threads."""
            try:
                result = scheduler.submit_backup("testuser", "pass")
                results.append(result)
            except Exception as e:
                errors.append(str(e))

        # Launch multiple threads simultaneously
        threads = [threading.Thread(target=rapid_schedule) for _ in range(10)]
        for t in threads:
            t.start()
        for t in threads:
            t.join(timeout=5.0)

        # No errors should have occurred
        assert not errors, f"Unexpected errors: {errors}"

        # Exactly one call should have succeeded (first one to acquire lock)
        assert results.count(True) == 1, (
            f"Expected exactly 1 successful schedule, got {results.count(True)}"
        )
        assert results.count(False) == 9, (
            f"Expected 9 rejected schedules, got {results.count(False)}"
        )

        # Only one backup should be pending
        assert scheduler.get_pending_count() == 1

    @patch(
        "local_deep_research.database.backup.backup_service.get_encrypted_database_path"
    )
    @patch(
        "local_deep_research.database.backup.backup_service.get_user_backup_directory"
    )
    @patch(
        "local_deep_research.database.backup.backup_service.get_user_database_filename"
    )
    def test_concurrent_cleanup_and_backup(
        self, mock_db_filename, mock_backup_dir, mock_db_path, tmp_path
    ):
        """Cleanup running during backup creation should not cause issues.

        This test verifies that:
        1. Cleanup running concurrently with backup creation doesn't crash
        2. A newly created backup is not deleted by concurrent cleanup
        3. No race condition or data corruption occurs
        """
        import os

        db_dir = tmp_path / "encrypted_databases"
        db_dir.mkdir()
        db_file = db_dir / "ldr_user_test.db"
        db_file.write_bytes(b"x" * 1000)

        backup_dir = tmp_path / "backups"
        backup_dir.mkdir()

        mock_db_filename.return_value = "ldr_user_test.db"
        mock_db_path.return_value = db_dir
        mock_backup_dir.return_value = backup_dir

        # Create some old backups that should be cleaned up
        base_time = time.time()
        for i in range(5):
            backup_file = backup_dir / f"ldr_backup_2024010{i}_120000.db"
            backup_file.write_bytes(b"old_backup")
            mtime = base_time - (5 - i) * 86400  # Older files
            os.utime(backup_file, (mtime, mtime))

        errors = []
        cleanup_results = []
        backup_start_event = threading.Event()
        cleanup_done_event = threading.Event()

        def run_cleanup(service):
            """Run cleanup operation."""
            try:
                # Wait for backup to start
                backup_start_event.wait(timeout=2.0)
                # Small delay to ensure backup is in progress
                time.sleep(0.05)
                result = service._cleanup_old_backups()
                cleanup_results.append(result)
            except Exception as e:
                errors.append(f"Cleanup error: {e}")
            finally:
                cleanup_done_event.set()

        def run_backup_creation(service):
            """Create a new backup file to simulate backup creation."""
            try:
                backup_start_event.set()
                # Simulate backup creation by creating a new backup file
                timestamp = "20250120_120000"  # A new timestamp
                new_backup = backup_dir / f"ldr_backup_{timestamp}.db"
                # Simulate slow backup creation
                time.sleep(0.1)
                new_backup.write_bytes(b"new_backup_data")
                # Set modification time to now
                os.utime(new_backup, (base_time + 100, base_time + 100))
            except Exception as e:
                errors.append(f"Backup error: {e}")

        service = BackupService(
            username="testuser",
            password="testpass",
            max_backups=3,  # Will try to delete 2 old backups
            max_age_days=365,
        )

        # Start both threads
        cleanup_thread = threading.Thread(target=run_cleanup, args=(service,))
        backup_thread = threading.Thread(
            target=run_backup_creation, args=(service,)
        )

        backup_thread.start()
        cleanup_thread.start()

        backup_thread.join(timeout=5.0)
        cleanup_thread.join(timeout=5.0)

        # Verify no errors occurred
        assert not errors, f"Unexpected errors: {errors}"

        # Verify cleanup completed
        assert len(cleanup_results) == 1

        # Verify the new backup was not deleted by cleanup
        new_backup = backup_dir / "ldr_backup_20250120_120000.db"
        assert new_backup.exists(), (
            "New backup should not be deleted by concurrent cleanup"
        )

        # Verify remaining backups are within expected range
        # (cleanup might have kept 3 old + 1 new, or some variation)
        remaining = list(backup_dir.glob("ldr_backup_*.db"))
        assert len(remaining) >= 1, "At least one backup should remain"
        assert len(remaining) <= 4, (
            "At most 4 backups should remain (3 old + 1 new)"
        )

    @requires_sqlcipher
    def test_backup_with_active_write_transaction(self, tmp_path):
        """Backup should handle concurrent write transactions.

        When a backup is attempted while the source database has an active
        write transaction, the backup should either:
        1. Succeed with a consistent snapshot (SQLCipher's sqlcipher_export
           reads a consistent view)
        2. Fail gracefully with an error message

        This test verifies no database corruption occurs in either case.
        """
        from local_deep_research.database.sqlcipher_compat import (
            get_sqlcipher_module,
        )
        from local_deep_research.database.sqlcipher_utils import (
            get_key_from_password,
            apply_sqlcipher_pragmas,
            get_sqlcipher_settings,
            set_sqlcipher_key,
        )

        sqlcipher = get_sqlcipher_module()

        password = "test_concurrent_write_password"

        # Setup directories
        db_dir = tmp_path / "encrypted_databases"
        db_dir.mkdir()
        backup_dir = tmp_path / "backups"
        backup_dir.mkdir(mode=0o700)

        # Create encrypted source database
        source_db = db_dir / "ldr_user_concurrent.db"
        conn = sqlcipher.connect(str(source_db))
        cursor = conn.cursor()
        set_sqlcipher_key(cursor, password)
        apply_sqlcipher_pragmas(cursor, creation_mode=True)
        cursor.execute(
            "CREATE TABLE test_data (id INTEGER PRIMARY KEY, value TEXT)"
        )
        cursor.execute("INSERT INTO test_data (value) VALUES ('initial')")
        conn.commit()
        conn.close()

        # Track results from threads
        write_results = []
        backup_results = []
        errors = []
        write_started = threading.Event()
        write_can_continue = threading.Event()

        def write_thread():
            """Perform a write transaction with a delay."""
            try:
                conn = sqlcipher.connect(str(source_db))
                cursor = conn.cursor()
                set_sqlcipher_key(cursor, password)
                apply_sqlcipher_pragmas(cursor, creation_mode=False)

                # Start a transaction
                cursor.execute("BEGIN IMMEDIATE")
                cursor.execute(
                    "INSERT INTO test_data (value) VALUES ('during_backup')"
                )
                write_started.set()

                # Hold the transaction open
                write_can_continue.wait(timeout=5.0)
                time.sleep(0.1)

                # Commit the transaction
                conn.commit()
                write_results.append("committed")
                conn.close()
            except Exception as e:
                errors.append(f"Write error: {e}")

        def backup_thread():
            """Attempt backup during write transaction."""
            try:
                # Wait for write transaction to start
                write_started.wait(timeout=5.0)
                time.sleep(0.05)  # Small delay to ensure transaction is active

                # Create backup using the same pattern as BackupService
                conn = sqlcipher.connect(str(source_db))
                cursor = conn.cursor()
                set_sqlcipher_key(cursor, password)
                apply_sqlcipher_pragmas(cursor, creation_mode=False)

                backup_path = backup_dir / "backup_during_write.db"
                hex_key = get_key_from_password(
                    password, db_path=source_db
                ).hex()
                settings = get_sqlcipher_settings()

                cursor.execute(
                    f"ATTACH DATABASE '{backup_path}' AS backup KEY \"x'{hex_key}'\""
                )
                cursor.execute(
                    f"PRAGMA backup.cipher_page_size = {settings['page_size']}"
                )
                cursor.execute(
                    f"PRAGMA backup.cipher_hmac_algorithm = {settings['hmac_algorithm']}"
                )
                cursor.execute(
                    f"PRAGMA backup.kdf_iter = {settings['kdf_iterations']}"
                )
                cursor.execute("SELECT sqlcipher_export('backup')")
                cursor.execute("DETACH DATABASE backup")
                conn.close()

                backup_results.append(("success", backup_path))
            except Exception as e:
                backup_results.append(("error", str(e)))
            finally:
                # Allow write thread to continue
                write_can_continue.set()

        # Run both threads
        t_write = threading.Thread(target=write_thread)
        t_backup = threading.Thread(target=backup_thread)

        t_write.start()
        t_backup.start()

        t_write.join(timeout=10.0)
        t_backup.join(timeout=10.0)

        # Verify no unexpected errors
        assert not errors, f"Unexpected errors: {errors}"

        # Verify the source database is not corrupted
        conn = sqlcipher.connect(str(source_db))
        cursor = conn.cursor()
        set_sqlcipher_key(cursor, password)
        apply_sqlcipher_pragmas(cursor, creation_mode=False)
        cursor.execute("PRAGMA quick_check")
        check_result = cursor.fetchone()
        conn.close()

        assert check_result[0] == "ok", (
            f"Source database corrupted after concurrent backup: {check_result}"
        )

        # Verify backup result
        assert len(backup_results) == 1, "Backup should have a result"
        result_type, result_data = backup_results[0]

        if result_type == "success":
            # If backup succeeded, verify it's valid
            backup_path = result_data
            conn = sqlcipher.connect(str(backup_path))
            cursor = conn.cursor()
            set_sqlcipher_key(cursor, password)
            apply_sqlcipher_pragmas(cursor, creation_mode=False)
            cursor.execute("PRAGMA quick_check")
            backup_check = cursor.fetchone()
            conn.close()
            assert backup_check[0] == "ok", "Backup should be valid"


class TestSecurityFixes:
    """Tests for security-related fixes."""

    @patch(
        "local_deep_research.database.backup.backup_service.get_encrypted_database_path"
    )
    @patch(
        "local_deep_research.database.backup.backup_service.get_user_backup_directory"
    )
    @patch(
        "local_deep_research.database.backup.backup_service.get_user_database_filename"
    )
    @patch("shutil.disk_usage")
    def test_disk_space_check_oserror_fails_closed(
        self,
        mock_disk_usage,
        mock_db_filename,
        mock_backup_dir,
        mock_db_path,
        tmp_path,
    ):
        """Should fail backup when disk space check raises OSError."""
        # Create a fake database file
        db_dir = tmp_path / "encrypted_databases"
        db_dir.mkdir()
        db_file = db_dir / "ldr_user_abc123.db"
        db_file.write_bytes(b"x" * 1000)

        mock_db_filename.return_value = "ldr_user_abc123.db"
        mock_db_path.return_value = db_dir
        mock_backup_dir.return_value = tmp_path / "backups"
        (tmp_path / "backups").mkdir()

        # Simulate OSError during disk space check
        mock_disk_usage.side_effect = OSError("Permission denied")

        service = BackupService(username="testuser", password="testpass")
        result = service.create_backup()

        assert result.success is False
        assert "Could not verify disk space" in result.error

    @patch(
        "local_deep_research.database.backup.backup_service.get_encrypted_database_path"
    )
    @patch(
        "local_deep_research.database.backup.backup_service.get_user_backup_directory"
    )
    @patch(
        "local_deep_research.database.backup.backup_service.get_user_database_filename"
    )
    @patch(
        "local_deep_research.database.backup.backup_service.create_sqlcipher_connection"
    )
    @patch("shutil.disk_usage")
    def test_backup_path_with_single_quote_is_escaped_not_rejected(
        self,
        mock_disk_usage,
        mock_create_conn,
        mock_db_filename,
        mock_backup_dir,
        mock_db_path,
        tmp_path,
    ):
        """A single quote in the backup path is escaped ('') in the ATTACH
        literal, not rejected — apostrophe data dirs are supported (#4808)."""
        # Create a fake database file
        db_dir = tmp_path / "encrypted_databases"
        db_dir.mkdir()
        db_file = db_dir / "ldr_user_abc123.db"
        db_file.write_bytes(b"x" * 1000)

        mock_db_filename.return_value = "ldr_user_abc123.db"
        mock_db_path.return_value = db_dir
        # A backup directory whose path contains a single quote.
        apostrophe_dir = tmp_path / "back'ups"
        apostrophe_dir.mkdir()
        mock_backup_dir.return_value = apostrophe_dir

        mock_disk_usage.return_value = MagicMock(free=10000000)

        # Mock connection
        mock_conn = MagicMock()
        mock_cursor = MagicMock()
        mock_conn.cursor.return_value = mock_cursor
        mock_create_conn.return_value = mock_conn

        service = BackupService(username="testuser", password="testpass")
        result = service.create_backup()

        # The path-character guard did NOT reject it (the backup may still fail
        # later for unrelated mocked reasons, but never with that guard error).
        if result.error:
            assert "not allowed in a SQLCipher ATTACH" not in result.error
        # ATTACH ran with the single quote doubled (escaped). Read the actual
        # SQL argument, not str(call) (whose repr re-escapes the quotes).
        attach_sql = [
            c.args[0]
            for c in mock_cursor.execute.call_args_list
            if c.args and "ATTACH DATABASE" in c.args[0]
        ]
        assert attach_sql, "ATTACH should run — the apostrophe path is accepted"
        assert "back''ups" in attach_sql[0]

    def test_user_backup_directory_has_restrictive_permissions(self, tmp_path):
        """Should create user backup directory with mode 0o700."""
        import os
        import stat

        with patch(
            "local_deep_research.config.paths.get_data_directory"
        ) as mock_data_dir:
            mock_data_dir.return_value = tmp_path

            from local_deep_research.config.paths import (
                get_user_backup_directory,
            )

            user_backup_dir = get_user_backup_directory("testuser")

            # Check directory permissions (owner only)
            mode = os.stat(user_backup_dir).st_mode
            # Extract permission bits
            perms = stat.S_IMODE(mode)
            # Should be 0o700 (owner read/write/execute only)
            assert perms == 0o700

    def test_backup_scheduler_registers_atexit_handler(self):
        """Should register atexit handler for clean shutdown."""
        import atexit

        with patch.object(atexit, "register") as mock_register:
            # Create a fresh scheduler instance
            scheduler = BackupExecutor.__new__(BackupExecutor)
            # Remove _initialized to force __init__ to run
            if hasattr(scheduler, "_initialized"):
                delattr(scheduler, "_initialized")

            # Call __init__ directly
            scheduler.__init__()

            # Verify atexit.register was called with shutdown method
            mock_register.assert_called_once_with(scheduler.shutdown)

    @patch(
        "local_deep_research.database.backup.backup_service.get_encrypted_database_path"
    )
    @patch(
        "local_deep_research.database.backup.backup_service.get_user_backup_directory"
    )
    @patch(
        "local_deep_research.database.backup.backup_service.get_user_database_filename"
    )
    @patch(
        "local_deep_research.database.backup.backup_service.create_sqlcipher_connection"
    )
    @patch("shutil.disk_usage")
    def test_backup_path_with_double_quotes_rejected(
        self,
        mock_disk_usage,
        mock_create_conn,
        mock_db_filename,
        mock_backup_dir,
        mock_db_path,
        tmp_path,
    ):
        """Should reject backup paths containing double quotes."""
        # Create a fake database file
        db_dir = tmp_path / "encrypted_databases"
        db_dir.mkdir()
        db_file = db_dir / "ldr_user_abc123.db"
        db_file.write_bytes(b"x" * 1000)

        mock_db_filename.return_value = "ldr_user_abc123.db"
        mock_db_path.return_value = db_dir
        # Create a backup directory with a double quote in the path
        malicious_dir = tmp_path / 'back"ups'
        malicious_dir.mkdir()
        mock_backup_dir.return_value = malicious_dir

        mock_disk_usage.return_value = MagicMock(free=10000000)

        # Mock connection
        mock_conn = MagicMock()
        mock_cursor = MagicMock()
        mock_conn.cursor.return_value = mock_cursor
        mock_create_conn.return_value = mock_conn

        service = BackupService(username="testuser", password="testpass")
        result = service.create_backup()

        assert result.success is False
        assert "not allowed in a SQLCipher ATTACH" in result.error


class TestBackupEdgeCases:
    """Tests for edge cases in backup operations."""

    @patch(
        "local_deep_research.database.backup.backup_service.get_encrypted_database_path"
    )
    @patch(
        "local_deep_research.database.backup.backup_service.get_user_backup_directory"
    )
    @patch(
        "local_deep_research.database.backup.backup_service.get_user_database_filename"
    )
    def test_cleanup_preserves_newest_backup(
        self, mock_db_filename, mock_backup_dir, mock_db_path, tmp_path
    ):
        """Should never delete the most recent backup during cleanup."""
        import os

        backup_dir = tmp_path / "backups"
        backup_dir.mkdir()

        mock_db_filename.return_value = "ldr_user_abc123.db"
        mock_db_path.return_value = tmp_path
        mock_backup_dir.return_value = backup_dir

        # Create 10 backups with different timestamps
        base_time = time.time()
        for i in range(10):
            backup_file = backup_dir / f"ldr_backup_2024010{i}_120000.db"
            backup_file.write_bytes(b"backup_data")
            # Set modification time (older files have earlier times)
            mtime = base_time - (9 - i) * 86400  # i=9 is newest
            os.utime(backup_file, (mtime, mtime))

        # Service with max_backups=3 should keep only 3 newest
        service = BackupService(
            username="testuser",
            password="testpass",
            max_backups=3,
            max_age_days=365,
        )
        deleted_count = service._cleanup_old_backups()

        # Should have deleted 7 backups
        assert deleted_count == 7

        # Should have exactly 3 backups remaining
        remaining = list(backup_dir.glob("ldr_backup_*.db"))
        assert len(remaining) == 3

        # The newest backup (ldr_backup_20240109_120000.db) must be preserved
        newest_backup = backup_dir / "ldr_backup_20240109_120000.db"
        assert newest_backup.exists(), "Newest backup should never be deleted"

    @patch(
        "local_deep_research.database.backup.backup_service.get_encrypted_database_path"
    )
    @patch(
        "local_deep_research.database.backup.backup_service.get_user_backup_directory"
    )
    @patch(
        "local_deep_research.database.backup.backup_service.get_user_database_filename"
    )
    def test_cleanup_handles_already_deleted_files(
        self, mock_db_filename, mock_backup_dir, mock_db_path, tmp_path
    ):
        """Should handle gracefully when backup files are deleted externally."""
        import os

        backup_dir = tmp_path / "backups"
        backup_dir.mkdir()

        mock_db_filename.return_value = "ldr_user_abc123.db"
        mock_db_path.return_value = tmp_path
        mock_backup_dir.return_value = backup_dir

        # Create backups
        base_time = time.time()
        for i in range(5):
            backup_file = backup_dir / f"ldr_backup_2024010{i}_120000.db"
            backup_file.write_bytes(b"backup_data")
            mtime = base_time - (4 - i) * 86400
            os.utime(backup_file, (mtime, mtime))

        service = BackupService(
            username="testuser",
            password="testpass",
            max_backups=2,
            max_age_days=365,
        )

        # Delete a file externally before cleanup runs
        (backup_dir / "ldr_backup_20240101_120000.db").unlink()

        # Cleanup should not raise an exception
        deleted_count = service._cleanup_old_backups()

        # Should have deleted 2 files (was 4 remaining, keep 2)
        assert deleted_count == 2

    def test_backup_filename_format_is_valid(self):
        """Should generate backup filename with valid UTC timestamp format."""
        import re
        from datetime import UTC, datetime

        # Generate the backup filename using the same format as BackupService
        timestamp = datetime.now(UTC).strftime("%Y%m%d_%H%M%S")
        backup_filename = f"ldr_backup_{timestamp}.db"

        # Verify filename format matches expected pattern
        pattern = r"^ldr_backup_(\d{8})_(\d{6})\.db$"
        match = re.match(pattern, backup_filename)
        assert match is not None, (
            f"Filename {backup_filename} doesn't match expected pattern"
        )

        # Verify the timestamp components are valid
        date_part, time_part = match.groups()

        # Date should be 8 digits (YYYYMMDD)
        assert len(date_part) == 8
        year = int(date_part[:4])
        month = int(date_part[4:6])
        day = int(date_part[6:8])
        assert 2020 <= year <= 2100
        assert 1 <= month <= 12
        assert 1 <= day <= 31

        # Time should be 6 digits (HHMMSS)
        assert len(time_part) == 6
        hour = int(time_part[:2])
        minute = int(time_part[2:4])
        second = int(time_part[4:6])
        assert 0 <= hour <= 23
        assert 0 <= minute <= 59
        assert 0 <= second <= 59

    @patch(
        "local_deep_research.database.backup.backup_service.get_encrypted_database_path"
    )
    @patch(
        "local_deep_research.database.backup.backup_service.get_user_backup_directory"
    )
    @patch(
        "local_deep_research.database.backup.backup_service.get_user_database_filename"
    )
    def test_list_backups_returns_correct_metadata(
        self, mock_db_filename, mock_backup_dir, mock_db_path, tmp_path
    ):
        """Should return accurate metadata for each backup."""
        import os

        backup_dir = tmp_path / "backups"
        backup_dir.mkdir()

        mock_db_filename.return_value = "ldr_user_abc123.db"
        mock_db_path.return_value = tmp_path
        mock_backup_dir.return_value = backup_dir

        # Create backups with known sizes
        backup1 = backup_dir / "ldr_backup_20240101_120000.db"
        backup1.write_bytes(b"a" * 1000)
        backup2 = backup_dir / "ldr_backup_20240102_120000.db"
        backup2.write_bytes(b"b" * 2000)

        # Set specific modification times
        os.utime(backup1, (1704110400, 1704110400))  # 2024-01-01 12:00:00
        os.utime(backup2, (1704196800, 1704196800))  # 2024-01-02 12:00:00

        service = BackupService(username="testuser", password="testpass")
        backups = service.list_backups()

        assert len(backups) == 2

        # Backups should be sorted newest first
        assert backups[0]["filename"] == "ldr_backup_20240102_120000.db"
        assert backups[0]["size_bytes"] == 2000

        assert backups[1]["filename"] == "ldr_backup_20240101_120000.db"
        assert backups[1]["size_bytes"] == 1000

        # Each backup should have required fields
        for backup in backups:
            assert "filename" in backup
            assert "path" in backup
            assert "size_bytes" in backup
            assert "created_at" in backup

    def test_scheduler_handles_rapid_sequential_logins(self):
        """Should handle rapid sequential login attempts for same user."""
        scheduler = BackupExecutor.__new__(BackupExecutor)
        scheduler._pending_backups = set()
        scheduler._pending_lock = threading.Lock()
        scheduler._executor = MagicMock()
        scheduler._executor.submit.return_value = MagicMock()
        scheduler._initialized = True

        results = []
        # Simulate rapid login attempts
        for _ in range(10):
            result = scheduler.submit_backup("testuser", "pass")
            results.append(result)

        # First attempt should succeed, rest should be rejected
        assert results[0] is True
        assert all(r is False for r in results[1:])
        assert scheduler.get_pending_count() == 1

    def test_scheduler_allows_backup_after_completion(self):
        """Should allow new backup after previous one completes."""
        scheduler = BackupExecutor.__new__(BackupExecutor)
        scheduler._pending_backups = set()
        scheduler._pending_lock = threading.Lock()
        scheduler._executor = MagicMock()
        mock_future = MagicMock()
        scheduler._executor.submit.return_value = mock_future
        scheduler._initialized = True

        # First backup
        result1 = scheduler.submit_backup("testuser", "pass")
        assert result1 is True

        # Second backup should fail (still pending)
        result2 = scheduler.submit_backup("testuser", "pass")
        assert result2 is False

        # Simulate completion
        scheduler._backup_completed("testuser", mock_future)

        # Now new backup should be allowed
        result3 = scheduler.submit_backup("testuser", "pass")
        assert result3 is True

    @patch(
        "local_deep_research.database.backup.backup_service.get_encrypted_database_path"
    )
    @patch(
        "local_deep_research.database.backup.backup_service.get_user_backup_directory"
    )
    @patch(
        "local_deep_research.database.backup.backup_service.get_user_database_filename"
    )
    def test_get_latest_backup_with_non_backup_files(
        self, mock_db_filename, mock_backup_dir, mock_db_path, tmp_path
    ):
        """Should ignore non-backup files when finding latest backup."""

        backup_dir = tmp_path / "backups"
        backup_dir.mkdir()

        mock_db_filename.return_value = "ldr_user_abc123.db"
        mock_db_path.return_value = tmp_path
        mock_backup_dir.return_value = backup_dir

        # Create a valid backup
        valid_backup = backup_dir / "ldr_backup_20240101_120000.db"
        valid_backup.write_bytes(b"backup_data")

        # Create files that should be ignored (don't match ldr_backup_*.db pattern)
        (backup_dir / "random_file.db").write_bytes(b"not a backup")
        (backup_dir / "ldr_backup_incomplete").write_bytes(b"no .db extension")
        (backup_dir / "other_backup_20240102_120000.db").write_bytes(
            b"wrong prefix"
        )

        service = BackupService(username="testuser", password="testpass")
        latest = service.get_latest_backup()

        assert latest is not None
        assert latest.name == "ldr_backup_20240101_120000.db"

    @patch(
        "local_deep_research.database.backup.backup_service.get_encrypted_database_path"
    )
    @patch(
        "local_deep_research.database.backup.backup_service.get_user_backup_directory"
    )
    @patch(
        "local_deep_research.database.backup.backup_service.get_user_database_filename"
    )
    def test_cleanup_with_zero_max_backups(
        self, mock_db_filename, mock_backup_dir, mock_db_path, tmp_path
    ):
        """Should delete all backups when max_backups is 0."""

        backup_dir = tmp_path / "backups"
        backup_dir.mkdir()

        mock_db_filename.return_value = "ldr_user_abc123.db"
        mock_db_path.return_value = tmp_path
        mock_backup_dir.return_value = backup_dir

        # Create some backups
        for i in range(3):
            backup_file = backup_dir / f"ldr_backup_2024010{i}_120000.db"
            backup_file.write_bytes(b"backup_data")

        service = BackupService(
            username="testuser",
            password="testpass",
            max_backups=0,
            max_age_days=365,
        )
        deleted_count = service._cleanup_old_backups()

        assert deleted_count == 3
        assert len(list(backup_dir.glob("ldr_backup_*.db"))) == 0

    @patch(
        "local_deep_research.database.backup.backup_service.get_encrypted_database_path"
    )
    @patch(
        "local_deep_research.database.backup.backup_service.get_user_backup_directory"
    )
    @patch(
        "local_deep_research.database.backup.backup_service.get_user_database_filename"
    )
    def test_verify_backup_with_wrong_password(
        self, mock_db_filename, mock_backup_dir, mock_db_path, tmp_path
    ):
        """Should return False when verifying backup with wrong password."""
        backup_dir = tmp_path / "backups"
        backup_dir.mkdir()

        mock_db_filename.return_value = "ldr_user_abc123.db"
        mock_db_path.return_value = tmp_path
        mock_backup_dir.return_value = backup_dir

        # Create a fake encrypted backup file
        backup_file = backup_dir / "ldr_backup_20240101_120000.db"
        backup_file.write_bytes(b"encrypted_content_here")

        service = BackupService(username="testuser", password="wrong_password")
        result = service._verify_backup(backup_file)

        # Should fail because password is wrong
        assert result is False

    @patch(
        "local_deep_research.database.backup.backup_service.get_encrypted_database_path"
    )
    @patch(
        "local_deep_research.database.backup.backup_service.get_user_backup_directory"
    )
    @patch(
        "local_deep_research.database.backup.backup_service.get_user_database_filename"
    )
    def test_get_latest_backup_with_nonexistent_directory(
        self, mock_db_filename, mock_backup_dir, mock_db_path, tmp_path
    ):
        """get_latest_backup should return None if backup dir doesn't exist."""
        # Point to a directory that doesn't exist
        nonexistent_dir = tmp_path / "nonexistent_backups"

        mock_db_filename.return_value = "ldr_user_abc123.db"
        mock_db_path.return_value = tmp_path
        mock_backup_dir.return_value = nonexistent_dir

        service = BackupService(username="testuser", password="testpass")

        # Should return None, not crash
        latest = service.get_latest_backup()
        assert latest is None

    @patch(
        "local_deep_research.database.backup.backup_service.get_encrypted_database_path"
    )
    @patch(
        "local_deep_research.database.backup.backup_service.get_user_backup_directory"
    )
    @patch(
        "local_deep_research.database.backup.backup_service.get_user_database_filename"
    )
    @patch(
        "local_deep_research.database.backup.backup_service.create_sqlcipher_connection"
    )
    @patch("shutil.disk_usage")
    def test_backup_uses_atomic_rename_pattern(
        self,
        mock_disk_usage,
        mock_create_conn,
        mock_db_filename,
        mock_backup_dir,
        mock_db_path,
        tmp_path,
    ):
        """Backup creates .tmp file first, then renames to final .db."""
        # Setup
        db_dir = tmp_path / "encrypted_databases"
        db_dir.mkdir()
        db_file = db_dir / "ldr_user_abc123.db"
        db_file.write_bytes(b"x" * 1000)

        backup_dir = tmp_path / "backups"
        backup_dir.mkdir()

        mock_db_filename.return_value = "ldr_user_abc123.db"
        mock_db_path.return_value = db_dir
        mock_backup_dir.return_value = backup_dir
        mock_disk_usage.return_value = MagicMock(free=10000000)

        # Track file creation order
        created_files = []

        mock_conn = MagicMock()
        mock_cursor = MagicMock()

        def track_file_creation(sql, *args):
            if "ATTACH DATABASE" in sql:
                import re

                match = re.search(r"ATTACH DATABASE '([^']+)'", sql)
                if match:
                    from pathlib import Path

                    file_path = Path(match.group(1))
                    file_path.write_bytes(b"backup_data")
                    created_files.append(file_path.name)

        mock_cursor.execute.side_effect = track_file_creation
        mock_conn.cursor.return_value = mock_cursor
        mock_create_conn.return_value = mock_conn

        service = BackupService(username="testuser", password="testpass")

        # Patch _verify_backup to return True
        with patch.object(service, "_verify_backup", return_value=True):
            result = service.create_backup()

        # Verify success
        assert result.success is True

        # Verify a .tmp file was created first
        assert len(created_files) == 1
        assert created_files[0].endswith(".db.tmp"), (
            f"Expected .tmp file during creation, got: {created_files[0]}"
        )

        # Verify final backup file has .db extension (not .tmp)
        assert result.backup_path is not None
        assert result.backup_path.suffix == ".db"
        assert not result.backup_path.name.endswith(".tmp")

        # Verify the final backup file exists (was renamed from .tmp)
        assert result.backup_path.exists()

    @requires_sqlcipher
    def test_backup_of_empty_database(self, tmp_path):
        """Should successfully backup a database with no user data."""
        from local_deep_research.database.sqlcipher_compat import (
            get_sqlcipher_module,
        )
        from local_deep_research.database.sqlcipher_utils import (
            apply_sqlcipher_pragmas,
            set_sqlcipher_key,
        )

        sqlcipher = get_sqlcipher_module()

        password = "test_empty_db_password"

        # Setup directories
        db_dir = tmp_path / "encrypted_databases"
        db_dir.mkdir()
        backup_dir = tmp_path / "backups"
        backup_dir.mkdir(mode=0o700)

        # Create encrypted database with only schema, no data
        source_db = db_dir / "ldr_user_empty.db"
        conn = sqlcipher.connect(str(source_db))
        cursor = conn.cursor()
        set_sqlcipher_key(cursor, password)
        apply_sqlcipher_pragmas(cursor, creation_mode=True)

        # Create schema but don't insert any data
        cursor.execute("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)")
        cursor.execute(
            "CREATE TABLE data (id INTEGER PRIMARY KEY, content TEXT)"
        )
        conn.commit()

        # Verify no data exists
        cursor.execute("SELECT COUNT(*) FROM users")
        assert cursor.fetchone()[0] == 0
        cursor.execute("SELECT COUNT(*) FROM data")
        assert cursor.fetchone()[0] == 0
        conn.close()

        # Create backup using BackupService
        with (
            patch(
                "local_deep_research.database.backup.backup_service.get_encrypted_database_path"
            ) as mock_db_path,
            patch(
                "local_deep_research.database.backup.backup_service.get_user_backup_directory"
            ) as mock_backup_dir,
            patch(
                "local_deep_research.database.backup.backup_service.get_user_database_filename"
            ) as mock_db_filename,
        ):
            mock_db_path.return_value = db_dir
            mock_backup_dir.return_value = backup_dir
            mock_db_filename.return_value = "ldr_user_empty.db"

            service = BackupService(
                username="testuser", password=password, max_backups=3
            )
            result = service.create_backup()

        # Verify backup succeeded
        assert result.success, f"Backup failed: {result.error}"
        assert result.backup_path is not None
        assert result.backup_path.exists()
        assert result.size_bytes > 0  # Should have some size for schema

        # Verify backup is valid and can be opened
        conn = sqlcipher.connect(str(result.backup_path))
        cursor = conn.cursor()
        set_sqlcipher_key(cursor, password)
        apply_sqlcipher_pragmas(cursor, creation_mode=False)

        # Verify schema was backed up
        cursor.execute(
            "SELECT name FROM sqlite_master WHERE type='table' ORDER BY name"
        )
        tables = [row[0] for row in cursor.fetchall()]
        assert "users" in tables
        assert "data" in tables

        # Verify no data (as expected)
        cursor.execute("SELECT COUNT(*) FROM users")
        assert cursor.fetchone()[0] == 0
        conn.close()

    @patch(
        "local_deep_research.database.backup.backup_service.get_encrypted_database_path"
    )
    @patch(
        "local_deep_research.database.backup.backup_service.get_user_backup_directory"
    )
    @patch(
        "local_deep_research.database.backup.backup_service.get_user_database_filename"
    )
    def test_cleanup_continues_on_single_delete_failure(
        self, mock_db_filename, mock_backup_dir, mock_db_path, tmp_path
    ):
        """Cleanup should continue if one file deletion fails."""
        import os

        backup_dir = tmp_path / "backups"
        backup_dir.mkdir()

        mock_db_filename.return_value = "ldr_user_abc123.db"
        mock_db_path.return_value = tmp_path
        mock_backup_dir.return_value = backup_dir

        # Create 5 backup files
        base_time = time.time()
        backup_files = []
        for i in range(5):
            backup_file = backup_dir / f"ldr_backup_2024010{i}_120000.db"
            backup_file.write_bytes(b"backup_data")
            mtime = base_time - (4 - i) * 86400
            os.utime(backup_file, (mtime, mtime))
            backup_files.append(backup_file)

        # The oldest file (20240100) should be deleted first
        # We'll mock unlink to fail for the second-oldest file
        from pathlib import Path as PathClass

        original_unlink = PathClass.unlink
        unlink_calls = []

        def mock_unlink(self, *args, **kwargs):
            unlink_calls.append(str(self))
            if "20240101" in str(self):  # Fail for second-oldest
                raise OSError("Permission denied")
            return original_unlink(self, *args, **kwargs)

        service = BackupService(
            username="testuser",
            password="testpass",
            max_backups=2,  # Should try to delete 3 files
            max_age_days=365,
        )

        with patch.object(PathClass, "unlink", mock_unlink):
            deleted_count = service._cleanup_old_backups()

        # Should have attempted to delete 3 files (5 - 2 = 3 to remove)
        # But only 2 should have succeeded due to the error
        assert deleted_count == 2

        # Verify the failed file still exists
        failed_file = backup_dir / "ldr_backup_20240101_120000.db"
        assert failed_file.exists(), (
            "File that failed to delete should still exist"
        )

        # Verify the two newest files still exist (kept by max_backups)
        assert (backup_dir / "ldr_backup_20240104_120000.db").exists()
        assert (backup_dir / "ldr_backup_20240103_120000.db").exists()

        # Verify the oldest file was deleted (not the one that failed)
        assert not (backup_dir / "ldr_backup_20240100_120000.db").exists()
        assert not (backup_dir / "ldr_backup_20240102_120000.db").exists()

    @pytest.mark.skipif(
        hasattr(os, "geteuid") and os.geteuid() == 0,
        reason="Root ignores filesystem permission restrictions",
    )
    @requires_sqlcipher
    def test_backup_directory_readonly_fails_gracefully(self, tmp_path):
        """Should return error if backup directory is read-only."""
        import os
        import platform

        # Skip on Windows - permission handling is different
        if platform.system() == "Windows":
            pytest.skip("Windows handles permissions differently")

        from local_deep_research.database.sqlcipher_compat import (
            get_sqlcipher_module,
        )
        from local_deep_research.database.sqlcipher_utils import (
            apply_sqlcipher_pragmas,
            set_sqlcipher_key,
        )

        sqlcipher = get_sqlcipher_module()

        password = "test_readonly_password"

        # Setup directories
        db_dir = tmp_path / "encrypted_databases"
        db_dir.mkdir()
        backup_dir = tmp_path / "backups"
        backup_dir.mkdir()

        # Create a real encrypted database
        source_db = db_dir / "ldr_user_readonly.db"
        conn = sqlcipher.connect(str(source_db))
        cursor = conn.cursor()
        set_sqlcipher_key(cursor, password)
        apply_sqlcipher_pragmas(cursor, creation_mode=True)
        cursor.execute("CREATE TABLE test (id INTEGER PRIMARY KEY, data TEXT)")
        cursor.execute("INSERT INTO test (data) VALUES ('test')")
        conn.commit()
        conn.close()

        # Make directory read-only (no write permission)
        original_mode = backup_dir.stat().st_mode
        try:
            os.chmod(backup_dir, 0o555)  # r-xr-xr-x  # noqa: S103

            with (
                patch(
                    "local_deep_research.database.backup.backup_service.get_encrypted_database_path"
                ) as mock_db_path,
                patch(
                    "local_deep_research.database.backup.backup_service.get_user_backup_directory"
                ) as mock_backup_dir,
                patch(
                    "local_deep_research.database.backup.backup_service.get_user_database_filename"
                ) as mock_db_filename,
            ):
                mock_db_path.return_value = db_dir
                mock_backup_dir.return_value = backup_dir
                mock_db_filename.return_value = "ldr_user_readonly.db"

                service = BackupService(
                    username="testuser", password=password, max_backups=5
                )
                result = service.create_backup()

            # Should fail gracefully
            assert result.success is False
            assert result.error is not None
            # Error message varies by platform/circumstance - just verify it failed
        finally:
            # Restore permissions
            os.chmod(backup_dir, original_mode)

    @requires_sqlcipher
    def test_backup_timestamp_collision_handled(self, tmp_path):
        """Should handle two backups created in same second.

        If two backups are created within the same second, filename collision
        could occur. This test verifies the backup either succeeds (atomic
        overwrite) or fails gracefully without corruption.
        """

        from local_deep_research.database.sqlcipher_compat import (
            get_sqlcipher_module,
        )
        from local_deep_research.database.sqlcipher_utils import (
            apply_sqlcipher_pragmas,
            set_sqlcipher_key,
        )

        sqlcipher = get_sqlcipher_module()

        password = "test_collision_password"

        # Setup directories
        db_dir = tmp_path / "encrypted_databases"
        db_dir.mkdir()
        backup_dir = tmp_path / "backups"
        backup_dir.mkdir(mode=0o700)

        # Create a real encrypted database
        source_db = db_dir / "ldr_user_collision.db"
        conn = sqlcipher.connect(str(source_db))
        cursor = conn.cursor()
        set_sqlcipher_key(cursor, password)
        apply_sqlcipher_pragmas(cursor, creation_mode=True)
        cursor.execute("CREATE TABLE test (id INTEGER PRIMARY KEY, data TEXT)")
        cursor.execute("INSERT INTO test (data) VALUES ('test_data')")
        conn.commit()
        conn.close()

        # Create two backups rapidly without mocking time
        # This tests real-world timestamp collision scenarios
        results = []

        with (
            patch(
                "local_deep_research.database.backup.backup_service.get_encrypted_database_path"
            ) as mock_db_path,
            patch(
                "local_deep_research.database.backup.backup_service.get_user_backup_directory"
            ) as mock_backup_dir,
            patch(
                "local_deep_research.database.backup.backup_service.get_user_database_filename"
            ) as mock_db_filename,
        ):
            mock_db_path.return_value = db_dir
            mock_backup_dir.return_value = backup_dir
            mock_db_filename.return_value = "ldr_user_collision.db"

            service = BackupService(
                username="testuser", password=password, max_backups=5
            )

            # Create first backup
            result1 = service.create_backup()
            results.append(result1)

            # Immediately try second backup (may have same timestamp)
            result2 = service.create_backup()
            results.append(result2)

        # At least one should succeed
        success_count = sum(1 for r in results if r.success)
        assert success_count >= 1, (
            f"At least one backup should succeed. "
            f"Results: {[(r.success, r.error) for r in results]}"
        )

        # Verify we have valid backup files (not corrupted)
        backups = list(backup_dir.glob("ldr_backup_*.db"))
        assert len(backups) >= 1, "Should have at least one backup file"

        # Each backup file should be a valid encrypted database
        for backup_path in backups:
            conn = sqlcipher.connect(str(backup_path))
            cursor = conn.cursor()
            set_sqlcipher_key(cursor, password)
            apply_sqlcipher_pragmas(cursor, creation_mode=False)
            cursor.execute("PRAGMA quick_check")
            check_result = cursor.fetchone()
            conn.close()
            assert check_result[0] == "ok", (
                f"Backup {backup_path.name} is corrupted"
            )

    @patch(
        "local_deep_research.database.backup.backup_service.get_encrypted_database_path"
    )
    @patch(
        "local_deep_research.database.backup.backup_service.get_user_backup_directory"
    )
    @patch(
        "local_deep_research.database.backup.backup_service.get_user_database_filename"
    )
    def test_cleanup_permission_denied_resilience(
        self, mock_db_filename, mock_backup_dir, mock_db_path, tmp_path
    ):
        """Cleanup should continue if one file has permission denied.

        When cleanup encounters a file that cannot be deleted (e.g., due to
        permission issues), it should continue deleting other eligible files
        rather than failing completely.
        """
        import os

        backup_dir = tmp_path / "backups"
        backup_dir.mkdir()

        mock_db_filename.return_value = "ldr_user_abc123.db"
        mock_db_path.return_value = tmp_path
        mock_backup_dir.return_value = backup_dir

        # Create 4 backup files with different timestamps
        base_time = time.time()
        for i in range(4):
            backup_file = backup_dir / f"ldr_backup_2024010{i}_120000.db"
            backup_file.write_bytes(b"backup_data")
            mtime = base_time - (3 - i) * 86400
            os.utime(backup_file, (mtime, mtime))

        # Track which files we tried to delete
        delete_attempts = []
        from pathlib import Path as PathClass

        original_unlink = PathClass.unlink

        def mock_unlink(self, *args, **kwargs):
            delete_attempts.append(str(self))
            # Fail for one specific file (the second oldest)
            if "20240101" in str(self):
                raise PermissionError("Permission denied")
            return original_unlink(self, *args, **kwargs)

        service = BackupService(
            username="testuser",
            password="testpass",
            max_backups=1,  # Should try to delete 3 files
            max_age_days=365,
        )

        with patch.object(PathClass, "unlink", mock_unlink):
            deleted_count = service._cleanup_old_backups()

        # Should have deleted 2 files (failed on 1)
        assert deleted_count == 2, (
            f"Expected 2 deletions (1 failed), got {deleted_count}"
        )

        # The file that failed to delete should still exist
        assert (backup_dir / "ldr_backup_20240101_120000.db").exists()

        # The newest file should be preserved (not in delete attempts)
        assert (backup_dir / "ldr_backup_20240103_120000.db").exists()


class TestBackupSettingsDisabled:
    """Tests for backup behavior when disabled via settings."""

    def test_scheduler_respects_backup_disabled_setting(self):
        """Should not schedule backup when backup.enabled is False."""
        # This tests the logic that would be in routes.py
        # When backup_enabled is False, submit_backup should not be called

        scheduler = BackupExecutor.__new__(BackupExecutor)
        scheduler._pending_backups = set()
        scheduler._pending_lock = threading.Lock()
        scheduler._executor = MagicMock()
        scheduler._executor.submit.return_value = MagicMock()
        scheduler._initialized = True

        # Simulate the logic from routes.py
        backup_enabled = False  # Setting is disabled

        if backup_enabled:
            result = scheduler.submit_backup("testuser", "pass")
        else:
            result = None  # Backup not scheduled

        # Verify backup was not scheduled
        assert result is None
        assert scheduler.get_pending_count() == 0
        scheduler._executor.submit.assert_not_called()

    def test_backup_service_works_independently_of_settings(self, tmp_path):
        """BackupService itself doesn't check settings - caller does."""
        # The BackupService doesn't check backup.enabled
        # That's handled by the caller (routes.py)
        # This test documents that behavior

        with (
            patch(
                "local_deep_research.database.backup.backup_service.get_encrypted_database_path"
            ) as mock_db_path,
            patch(
                "local_deep_research.database.backup.backup_service.get_user_backup_directory"
            ) as mock_backup_dir,
            patch(
                "local_deep_research.database.backup.backup_service.get_user_database_filename"
            ) as mock_db_filename,
        ):
            mock_db_filename.return_value = "test.db"
            mock_db_path.return_value = tmp_path
            mock_backup_dir.return_value = tmp_path / "backups"

            # Service can be instantiated regardless of settings
            service = BackupService(username="test", password="pass")
            assert service is not None
            assert service.username == "test"


class TestBackupFilePermissions:
    """Tests for backup file permission security."""

    def test_backup_directory_protects_files(self, tmp_path):
        """Backup directory permissions (0o700) protect files inside."""
        import os
        import stat

        with patch(
            "local_deep_research.config.paths.get_data_directory"
        ) as mock_data_dir:
            mock_data_dir.return_value = tmp_path

            from local_deep_research.config.paths import (
                get_user_backup_directory,
            )

            # Create user backup directory
            backup_dir = get_user_backup_directory("testuser")

            # Directory should have restrictive permissions
            dir_mode = os.stat(backup_dir).st_mode
            dir_perms = stat.S_IMODE(dir_mode)
            assert dir_perms == 0o700, "Directory should be owner-only"

            # Even if files inside have more permissive modes,
            # the directory permissions prevent other users from accessing them
            # This is defense-in-depth: directory + file permissions


class TestSpecialCharacterUsernames:
    """Tests for usernames with special characters."""

    def test_username_with_unicode_characters(self, tmp_path):
        """Should handle usernames with unicode characters."""
        with patch(
            "local_deep_research.config.paths.get_data_directory"
        ) as mock_data_dir:
            mock_data_dir.return_value = tmp_path

            from local_deep_research.config.paths import (
                get_user_backup_directory,
                get_user_database_filename,
            )

            # Test various unicode usernames
            unicode_usernames = [
                "用户名",  # Chinese
                "пользователь",  # Russian
                "ユーザー",  # Japanese
                "مستخدم",  # Arabic
                "user@example.com",  # Email format
                "user+tag@example.com",  # Email with plus
                "José García",  # Spanish with accents
                "Müller",  # German umlaut
            ]

            for username in unicode_usernames:
                # Should not raise any exceptions
                backup_dir = get_user_backup_directory(username)
                db_filename = get_user_database_filename(username)

                # Directory should be created
                assert backup_dir.exists()

                # Filename should be safe (hash-based)
                assert "/" not in db_filename
                assert "\\" not in db_filename
                assert ".." not in db_filename

                # Hash should be consistent
                backup_dir2 = get_user_backup_directory(username)
                assert backup_dir == backup_dir2

    def test_username_hash_is_unique(self, tmp_path):
        """Different usernames should produce different hashes."""
        with patch(
            "local_deep_research.config.paths.get_data_directory"
        ) as mock_data_dir:
            mock_data_dir.return_value = tmp_path

            from local_deep_research.config.paths import (
                get_user_database_filename,
            )

            usernames = ["user1", "user2", "User1", "USER1", "user1 ", " user1"]
            filenames = [get_user_database_filename(u) for u in usernames]

            # All filenames should be unique
            assert len(filenames) == len(set(filenames))

    def test_username_with_path_traversal_attempt(self, tmp_path):
        """Should safely handle usernames that look like path traversal."""
        with patch(
            "local_deep_research.config.paths.get_data_directory"
        ) as mock_data_dir:
            mock_data_dir.return_value = tmp_path

            from local_deep_research.config.paths import (
                get_user_backup_directory,
                get_user_database_filename,
            )

            # Malicious-looking usernames
            malicious_usernames = [
                "../../../etc/passwd",
                "..\\..\\..\\windows\\system32",
                "/etc/passwd",
                "C:\\Windows\\System32",
                "user\x00hidden",  # Null byte
                "user\nname",  # Newline
            ]

            for username in malicious_usernames:
                backup_dir = get_user_backup_directory(username)
                db_filename = get_user_database_filename(username)

                # Directory should be safely contained within backup directory
                assert (
                    tmp_path in backup_dir.parents
                    or backup_dir.parent
                    == tmp_path / "encrypted_databases" / "backups"
                )

                # Filename should be a safe hash, not contain dangerous characters
                assert "/" not in db_filename
                assert "\\" not in db_filename
                assert "\x00" not in db_filename
                assert "\n" not in db_filename

    def test_backup_with_very_long_username(self, tmp_path):
        """Should handle usernames near filesystem path length limits."""
        with patch(
            "local_deep_research.config.paths.get_data_directory"
        ) as mock_data_dir:
            mock_data_dir.return_value = tmp_path

            from local_deep_research.config.paths import (
                get_user_backup_directory,
                get_user_database_filename,
            )

            # Create usernames with 200+ characters
            # This exceeds typical max filename lengths (255 bytes on most filesystems)
            # but should work because hash-based naming is used
            long_usernames = [
                "a" * 200,  # 200 chars
                "user" * 75,  # 300 chars
                "x" * 500,  # 500 chars - way beyond typical limits
                "very_long_email_address_" * 20 + "@example.com",  # Long email
            ]

            for username in long_usernames:
                assert len(username) >= 200, "Username should be 200+ chars"

                # Should not raise any exceptions
                backup_dir = get_user_backup_directory(username)
                db_filename = get_user_database_filename(username)

                # Directory should be created successfully
                assert backup_dir.exists()

                # Filename should be manageable length (hash-based)
                # SHA256 hex is 64 chars + prefix + extension = ~80 chars
                assert len(db_filename) < 100, (
                    f"Filename should be hash-based and under 100 chars, "
                    f"got {len(db_filename)} chars: {db_filename}"
                )

                # Verify path doesn't contain the raw username (privacy/length)
                assert username not in str(backup_dir)
                assert username not in db_filename

                # Verify consistency (same username = same hash)
                backup_dir2 = get_user_backup_directory(username)
                db_filename2 = get_user_database_filename(username)
                assert backup_dir == backup_dir2
                assert db_filename == db_filename2


# Header bytes that indicate an UNENCRYPTED SQLite database
SQLITE_PLAINTEXT_HEADER = b"SQLite format 3\x00"


class TestBackupEncryptionVerification:
    """Tests that verify backup files are actually encrypted at the byte level.

    CRITICAL: These tests prevent accidental data exposure if backup method
    changes or encryption is bypassed. They verify:
    1. Backup files do NOT have plaintext SQLite header
    2. Backup files cannot be opened without any password
    3. BackupService.create_backup() produces properly encrypted files
    """

    @pytest.fixture
    def real_encrypted_db(self, tmp_path):
        """Create a real encrypted SQLCipher database for testing."""
        if not HAS_SQLCIPHER:
            pytest.skip("SQLCipher not available")
        from local_deep_research.database.sqlcipher_compat import (
            get_sqlcipher_module,
        )
        from local_deep_research.database.sqlcipher_utils import (
            apply_sqlcipher_pragmas,
            set_sqlcipher_key,
        )

        sqlcipher = get_sqlcipher_module()

        db_path = tmp_path / "test_encrypted.db"
        password = "test_password_123"

        # Create a real encrypted database
        conn = sqlcipher.connect(str(db_path))
        cursor = conn.cursor()
        set_sqlcipher_key(cursor, password)
        apply_sqlcipher_pragmas(cursor, creation_mode=True)

        # Create a test table with data
        cursor.execute(
            "CREATE TABLE test_data (id INTEGER PRIMARY KEY, value TEXT)"
        )
        cursor.execute("INSERT INTO test_data (value) VALUES ('test_value_1')")
        cursor.execute("INSERT INTO test_data (value) VALUES ('test_value_2')")
        conn.commit()
        conn.close()

        return {"db_path": db_path, "password": password}

    @requires_sqlcipher
    def test_backup_file_header_is_not_sqlite_plaintext(
        self, real_encrypted_db, tmp_path
    ):
        """Backup file must NOT have 'SQLite format 3' header (would indicate unencrypted).

        SECURITY: If this test fails, backups are being created WITHOUT encryption,
        exposing user data to anyone with filesystem access.
        """
        from local_deep_research.database.sqlcipher_compat import (
            get_sqlcipher_module,
        )
        from local_deep_research.database.sqlcipher_utils import (
            get_key_from_password,
            apply_sqlcipher_pragmas,
            get_sqlcipher_settings,
            set_sqlcipher_key,
        )

        sqlcipher = get_sqlcipher_module()

        db_path = real_encrypted_db["db_path"]
        password = real_encrypted_db["password"]
        backup_path = tmp_path / "backup.db"

        # Create backup using ATTACH + sqlcipher_export (mimicking BackupService)
        conn = sqlcipher.connect(str(db_path))
        cursor = conn.cursor()
        set_sqlcipher_key(cursor, password)
        apply_sqlcipher_pragmas(cursor, creation_mode=False)

        # Use the correct method: ATTACH DATABASE with KEY + sqlcipher_export
        hex_key = get_key_from_password(password, db_path=db_path).hex()
        settings = get_sqlcipher_settings()

        cursor.execute(
            f"ATTACH DATABASE '{backup_path}' AS backup KEY \"x'{hex_key}'\""
        )
        cursor.execute(
            f"PRAGMA backup.cipher_page_size = {settings['page_size']}"
        )
        cursor.execute(
            f"PRAGMA backup.cipher_hmac_algorithm = {settings['hmac_algorithm']}"
        )
        cursor.execute(f"PRAGMA backup.kdf_iter = {settings['kdf_iterations']}")
        cursor.execute("SELECT sqlcipher_export('backup')")
        cursor.execute("DETACH DATABASE backup")
        conn.close()

        # CRITICAL: Verify file is encrypted at byte level
        with open(backup_path, "rb") as f:
            header = f.read(16)

        assert header != SQLITE_PLAINTEXT_HEADER, (
            "SECURITY VULNERABILITY: Backup file is NOT encrypted! "
            "Header shows plaintext SQLite format."
        )

        # Additional check: first 16 bytes should look random (SQLCipher salt)
        # A properly encrypted file won't start with readable ASCII
        assert not header.startswith(b"SQLite"), (
            "SECURITY VULNERABILITY: Backup file header contains 'SQLite' - "
            "indicates unencrypted or improperly encrypted file."
        )

    @requires_sqlcipher
    def test_backup_cannot_be_opened_without_any_password(
        self, real_encrypted_db, tmp_path
    ):
        """Backup must fail to open when no password is provided at all.

        SECURITY: If this test fails, backups can be read without authentication,
        completely defeating the purpose of encryption.
        """
        from local_deep_research.database.sqlcipher_compat import (
            get_sqlcipher_module,
        )
        from local_deep_research.database.sqlcipher_utils import (
            get_key_from_password,
            apply_sqlcipher_pragmas,
            get_sqlcipher_settings,
            set_sqlcipher_key,
        )

        sqlcipher = get_sqlcipher_module()

        db_path = real_encrypted_db["db_path"]
        password = real_encrypted_db["password"]
        backup_path = tmp_path / "backup.db"

        # Create backup using ATTACH + sqlcipher_export
        conn = sqlcipher.connect(str(db_path))
        cursor = conn.cursor()
        set_sqlcipher_key(cursor, password)
        apply_sqlcipher_pragmas(cursor, creation_mode=False)

        hex_key = get_key_from_password(password, db_path=db_path).hex()
        settings = get_sqlcipher_settings()

        cursor.execute(
            f"ATTACH DATABASE '{backup_path}' AS backup KEY \"x'{hex_key}'\""
        )
        cursor.execute(
            f"PRAGMA backup.cipher_page_size = {settings['page_size']}"
        )
        cursor.execute(
            f"PRAGMA backup.cipher_hmac_algorithm = {settings['hmac_algorithm']}"
        )
        cursor.execute(f"PRAGMA backup.kdf_iter = {settings['kdf_iterations']}")
        cursor.execute("SELECT sqlcipher_export('backup')")
        cursor.execute("DETACH DATABASE backup")
        conn.close()

        # Try opening WITHOUT setting any password
        conn = sqlcipher.connect(str(backup_path))
        cursor = conn.cursor()
        # Deliberately NOT calling set_sqlcipher_key or PRAGMA key

        with pytest.raises(Exception) as exc_info:
            # This should fail because the file is encrypted
            cursor.execute("SELECT * FROM sqlite_master")
            cursor.fetchall()

        conn.close()

        # Verify the error indicates encryption/not a database issue
        error_msg = str(exc_info.value).lower()
        assert "encrypt" in error_msg or "not a database" in error_msg, (
            f"Expected encryption-related error, got: {exc_info.value}"
        )

    @requires_sqlcipher
    def test_backup_service_creates_encrypted_backup(self, tmp_path):
        """End-to-end test: BackupService.create_backup() produces encrypted file.

        This tests the actual backup service implementation, not just the
        underlying SQLCipher operations. It verifies:
        1. Backup file is created
        2. File header is NOT plaintext SQLite
        3. Backup is readable with correct password
        4. Backup fails with wrong password
        """
        from local_deep_research.database.sqlcipher_compat import (
            get_sqlcipher_module,
        )
        from local_deep_research.database.sqlcipher_utils import (
            apply_sqlcipher_pragmas,
            set_sqlcipher_key,
        )

        sqlcipher = get_sqlcipher_module()

        password = "test_secure_password_456"

        # Setup: Create encrypted source database
        db_dir = tmp_path / "encrypted_databases"
        db_dir.mkdir()
        backup_dir = tmp_path / "backups"
        backup_dir.mkdir(mode=0o700)

        # We need to create the source database with same settings as BackupService
        source_db = db_dir / "ldr_user_testhash.db"
        conn = sqlcipher.connect(str(source_db))
        cursor = conn.cursor()
        set_sqlcipher_key(cursor, password)
        apply_sqlcipher_pragmas(cursor, creation_mode=True)
        cursor.execute("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)")
        cursor.execute("INSERT INTO users (name) VALUES ('Alice')")
        cursor.execute("INSERT INTO users (name) VALUES ('Bob')")
        conn.commit()
        conn.close()

        # Create backup using BackupService
        with (
            patch(
                "local_deep_research.database.backup.backup_service.get_encrypted_database_path"
            ) as mock_db_path,
            patch(
                "local_deep_research.database.backup.backup_service.get_user_backup_directory"
            ) as mock_backup_dir,
            patch(
                "local_deep_research.database.backup.backup_service.get_user_database_filename"
            ) as mock_db_filename,
        ):
            mock_db_path.return_value = db_dir
            mock_backup_dir.return_value = backup_dir
            mock_db_filename.return_value = "ldr_user_testhash.db"

            service = BackupService(
                username="testuser", password=password, max_backups=3
            )
            result = service.create_backup()

        # Verify backup succeeded
        assert result.success, f"Backup failed: {result.error}"
        assert result.backup_path is not None
        assert result.backup_path.exists()

        # CRITICAL: Verify backup file is encrypted at byte level
        with open(result.backup_path, "rb") as f:
            header = f.read(16)

        assert header != SQLITE_PLAINTEXT_HEADER, (
            "SECURITY VULNERABILITY: BackupService created unencrypted backup! "
            "User data is exposed."
        )

        # Verify backup is readable with correct password
        conn = sqlcipher.connect(str(result.backup_path))
        cursor = conn.cursor()
        set_sqlcipher_key(cursor, password)
        apply_sqlcipher_pragmas(cursor, creation_mode=False)

        cursor.execute("SELECT name FROM users ORDER BY id")
        rows = cursor.fetchall()
        conn.close()

        assert len(rows) == 2
        assert rows[0][0] == "Alice"
        assert rows[1][0] == "Bob"

        # Verify backup fails with wrong password
        conn = sqlcipher.connect(str(result.backup_path))
        cursor = conn.cursor()
        set_sqlcipher_key(cursor, "wrong_password")
        apply_sqlcipher_pragmas(cursor, creation_mode=False)

        with pytest.raises(Exception):
            cursor.execute("SELECT * FROM users")
            cursor.fetchall()

        conn.close()

    @requires_sqlcipher
    def test_backup_rejected_by_standard_sqlite(self, tmp_path):
        """Standard SQLite (not SQLCipher) should fail to open encrypted backup.

        This ensures backups are protected even if someone accidentally uses
        the wrong SQLite library to access them.
        """
        import sqlite3  # Standard library, not SQLCipher

        from local_deep_research.database.sqlcipher_compat import (
            get_sqlcipher_module,
        )
        from local_deep_research.database.sqlcipher_utils import (
            get_key_from_password,
            apply_sqlcipher_pragmas,
            get_sqlcipher_settings,
            set_sqlcipher_key,
        )

        sqlcipher = get_sqlcipher_module()

        password = "test_password_789"

        # Create encrypted source database
        source_db = tmp_path / "source.db"
        conn = sqlcipher.connect(str(source_db))
        cursor = conn.cursor()
        set_sqlcipher_key(cursor, password)
        apply_sqlcipher_pragmas(cursor, creation_mode=True)
        cursor.execute("CREATE TABLE test (id INTEGER PRIMARY KEY, data TEXT)")
        cursor.execute("INSERT INTO test (data) VALUES ('secret_data')")
        conn.commit()

        # Create encrypted backup
        backup_path = tmp_path / "backup.db"
        hex_key = get_key_from_password(password, db_path=source_db).hex()
        settings = get_sqlcipher_settings()

        cursor.execute(
            f"ATTACH DATABASE '{backup_path}' AS backup KEY \"x'{hex_key}'\""
        )
        cursor.execute(
            f"PRAGMA backup.cipher_page_size = {settings['page_size']}"
        )
        cursor.execute(
            f"PRAGMA backup.cipher_hmac_algorithm = {settings['hmac_algorithm']}"
        )
        cursor.execute(f"PRAGMA backup.kdf_iter = {settings['kdf_iterations']}")
        cursor.execute("SELECT sqlcipher_export('backup')")
        cursor.execute("DETACH DATABASE backup")
        conn.close()

        # Try to open with standard sqlite3 (not SQLCipher)
        with pytest.raises(sqlite3.DatabaseError) as exc_info:
            std_conn = sqlite3.connect(str(backup_path))
            std_cursor = std_conn.cursor()
            std_cursor.execute("SELECT * FROM sqlite_master")
            std_cursor.fetchall()

        # Standard SQLite returns "file is not a database" for encrypted files
        assert "not a database" in str(exc_info.value).lower(), (
            f"Expected 'not a database' error, got: {exc_info.value}"
        )

    @requires_sqlcipher
    def test_backup_file_has_high_entropy(self, tmp_path):
        """Encrypted backup should have high Shannon entropy (~8 bits/byte).

        Encrypted data should appear random. Low entropy would indicate
        encryption failure or data leakage.
        """
        import math

        from local_deep_research.database.sqlcipher_compat import (
            get_sqlcipher_module,
        )
        from local_deep_research.database.sqlcipher_utils import (
            get_key_from_password,
            apply_sqlcipher_pragmas,
            get_sqlcipher_settings,
            set_sqlcipher_key,
        )

        sqlcipher = get_sqlcipher_module()

        password = "test_entropy_password"

        # Create encrypted source database with substantial data
        source_db = tmp_path / "source.db"
        conn = sqlcipher.connect(str(source_db))
        cursor = conn.cursor()
        set_sqlcipher_key(cursor, password)
        apply_sqlcipher_pragmas(cursor, creation_mode=True)

        # Create tables and insert enough data to get meaningful entropy
        cursor.execute(
            "CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, email TEXT)"
        )
        cursor.execute(
            "CREATE TABLE data (id INTEGER PRIMARY KEY, content TEXT)"
        )
        for i in range(100):
            cursor.execute(
                "INSERT INTO users (name, email) VALUES (?, ?)",
                (f"User {i}", f"user{i}@example.com"),
            )
            cursor.execute(
                "INSERT INTO data (content) VALUES (?)",
                (f"Some sensitive data entry number {i}" * 10,),
            )
        conn.commit()

        # Create encrypted backup
        backup_path = tmp_path / "backup.db"
        hex_key = get_key_from_password(password, db_path=source_db).hex()
        settings = get_sqlcipher_settings()

        cursor.execute(
            f"ATTACH DATABASE '{backup_path}' AS backup KEY \"x'{hex_key}'\""
        )
        cursor.execute(
            f"PRAGMA backup.cipher_page_size = {settings['page_size']}"
        )
        cursor.execute(
            f"PRAGMA backup.cipher_hmac_algorithm = {settings['hmac_algorithm']}"
        )
        cursor.execute(f"PRAGMA backup.kdf_iter = {settings['kdf_iterations']}")
        cursor.execute("SELECT sqlcipher_export('backup')")
        cursor.execute("DETACH DATABASE backup")
        conn.close()

        # Read backup and calculate Shannon entropy
        with open(backup_path, "rb") as f:
            data = f.read()

        if len(data) == 0:
            pytest.fail("Backup file is empty")

        # Calculate byte frequency
        freq = {}
        for byte in data:
            freq[byte] = freq.get(byte, 0) + 1

        # Calculate Shannon entropy
        entropy = -sum(
            (count / len(data)) * math.log2(count / len(data))
            for count in freq.values()
        )

        # Encrypted data should have entropy close to 8 bits/byte (maximum)
        # 7.5 is a conservative threshold that allows for headers/metadata
        assert entropy >= 7.5, (
            f"SECURITY WARNING: Low entropy ({entropy:.2f} bits/byte) "
            f"suggests weak or missing encryption. Expected >= 7.5"
        )

    @requires_sqlcipher
    def test_backup_contains_no_readable_sql_strings(self, tmp_path):
        """Encrypted backup should not contain readable SQL keywords or table names.

        This is a quick sanity check that no plaintext SQL leaked into the backup.
        """
        from local_deep_research.database.sqlcipher_compat import (
            get_sqlcipher_module,
        )
        from local_deep_research.database.sqlcipher_utils import (
            get_key_from_password,
            apply_sqlcipher_pragmas,
            get_sqlcipher_settings,
            set_sqlcipher_key,
        )

        sqlcipher = get_sqlcipher_module()

        password = "test_strings_password"

        # Create encrypted source database with known table names
        source_db = tmp_path / "source.db"
        conn = sqlcipher.connect(str(source_db))
        cursor = conn.cursor()
        set_sqlcipher_key(cursor, password)
        apply_sqlcipher_pragmas(cursor, creation_mode=True)

        # Create tables with distinctive names that should NOT appear in encrypted file
        cursor.execute(
            "CREATE TABLE secret_users (id INTEGER PRIMARY KEY, username TEXT)"
        )
        cursor.execute(
            "CREATE TABLE sensitive_data (id INTEGER PRIMARY KEY, content TEXT)"
        )
        cursor.execute(
            "INSERT INTO secret_users (username) VALUES ('admin_user')"
        )
        cursor.execute(
            "INSERT INTO sensitive_data (content) VALUES ('confidential_info')"
        )
        conn.commit()

        # Create encrypted backup
        backup_path = tmp_path / "backup.db"
        hex_key = get_key_from_password(password, db_path=source_db).hex()
        settings = get_sqlcipher_settings()

        cursor.execute(
            f"ATTACH DATABASE '{backup_path}' AS backup KEY \"x'{hex_key}'\""
        )
        cursor.execute(
            f"PRAGMA backup.cipher_page_size = {settings['page_size']}"
        )
        cursor.execute(
            f"PRAGMA backup.cipher_hmac_algorithm = {settings['hmac_algorithm']}"
        )
        cursor.execute(f"PRAGMA backup.kdf_iter = {settings['kdf_iterations']}")
        cursor.execute("SELECT sqlcipher_export('backup')")
        cursor.execute("DETACH DATABASE backup")
        conn.close()

        # Read backup file content
        with open(backup_path, "rb") as f:
            content = f.read()

        # Common SQL strings that should NOT appear in encrypted file
        forbidden_strings = [
            b"CREATE TABLE",
            b"INSERT INTO",
            b"SELECT ",
            b"sqlite_master",
            b"INTEGER PRIMARY KEY",
            # Also check for table/column names we created
            b"secret_users",
            b"sensitive_data",
            b"admin_user",
            b"confidential_info",
        ]

        for forbidden in forbidden_strings:
            assert forbidden not in content, (
                f"SECURITY VULNERABILITY: Found readable string "
                f"'{forbidden.decode()}' in encrypted backup file!"
            )

    @requires_sqlcipher
    def test_backup_rejects_symlink_paths(self, tmp_path):
        """Symlinks in backup path should be rejected or safely resolved.

        SECURITY: Symlinks could allow attackers to escape the backup directory
        and write backups to arbitrary locations, potentially overwriting
        important files or exposing data in insecure locations.
        """

        from local_deep_research.database.sqlcipher_compat import (
            get_sqlcipher_module,
        )
        from local_deep_research.database.sqlcipher_utils import (
            apply_sqlcipher_pragmas,
            set_sqlcipher_key,
        )

        sqlcipher = get_sqlcipher_module()

        password = "test_symlink_password"

        # Setup directories
        db_dir = tmp_path / "encrypted_databases"
        db_dir.mkdir()
        backup_dir = tmp_path / "backups"
        backup_dir.mkdir(mode=0o700)

        # Create a directory outside the backup directory
        outside_dir = tmp_path / "outside_backup_area"
        outside_dir.mkdir(mode=0o700)

        # Create a symlink inside backup_dir that points outside
        symlink_path = backup_dir / "escape_link"
        symlink_path.symlink_to(outside_dir)

        # Create source database
        source_db = db_dir / "ldr_user_symlink.db"
        conn = sqlcipher.connect(str(source_db))
        cursor = conn.cursor()
        set_sqlcipher_key(cursor, password)
        apply_sqlcipher_pragmas(cursor, creation_mode=True)
        cursor.execute("CREATE TABLE test (id INTEGER PRIMARY KEY, data TEXT)")
        cursor.execute("INSERT INTO test (data) VALUES ('test')")
        conn.commit()
        conn.close()

        # The backup service should either:
        # 1. Resolve symlinks and verify final path is within allowed directory
        # 2. Reject paths containing symlinks entirely
        # 3. Use os.path.realpath() to get the canonical path

        # Verify that the symlink exists and points outside
        assert symlink_path.is_symlink()
        assert symlink_path.resolve() == outside_dir

        # The backup directory path should resolve to itself (no symlink escape)
        # If backup_dir contains symlinks, realpath would reveal the true location
        backup_dir_real = backup_dir.resolve()
        assert backup_dir_real == backup_dir, (
            "Backup directory itself should not be a symlink"
        )

        # Verify symlink detection works - the service should be aware of symlinks
        # when validating paths. Check that Path.resolve() differs from the path.
        escaped_backup_path = symlink_path / "backup.db"
        escaped_real_path = escaped_backup_path.resolve()

        # The resolved path should be OUTSIDE the backup directory
        assert not str(escaped_real_path).startswith(str(backup_dir_real)), (
            f"Symlink escape detected: {escaped_backup_path} resolves to "
            f"{escaped_real_path} which is outside {backup_dir_real}"
        )

        # The backup service's path validation should catch this.
        # Since BackupService generates its own backup paths (not user-controlled),
        # the main risk is if backup_dir itself is a symlink.
        # Verify that if someone replaced backup_dir with a symlink, we'd detect it.
        malicious_backup_dir = tmp_path / "malicious_backups"
        malicious_backup_dir.symlink_to(outside_dir)

        # Check that the malicious directory resolves elsewhere
        assert malicious_backup_dir.resolve() != malicious_backup_dir
        assert malicious_backup_dir.resolve() == outside_dir

    @requires_sqlcipher
    def test_backup_file_has_restrictive_permissions(self, tmp_path):
        """Backup files should have 0o600 or stricter (owner read/write only).

        SECURITY: Backup files contain sensitive user data. If permissions are
        too permissive, other users on the system could read the encrypted
        backup and attempt offline attacks.
        """

        from local_deep_research.database.sqlcipher_compat import (
            get_sqlcipher_module,
        )
        from local_deep_research.database.sqlcipher_utils import (
            apply_sqlcipher_pragmas,
            set_sqlcipher_key,
        )

        sqlcipher = get_sqlcipher_module()

        password = "test_permissions_password"

        # Setup directories
        db_dir = tmp_path / "encrypted_databases"
        db_dir.mkdir()
        backup_dir = tmp_path / "backups"
        backup_dir.mkdir(mode=0o700)

        # Create source database
        source_db = db_dir / "ldr_user_permtest.db"
        conn = sqlcipher.connect(str(source_db))
        cursor = conn.cursor()
        set_sqlcipher_key(cursor, password)
        apply_sqlcipher_pragmas(cursor, creation_mode=True)
        cursor.execute("CREATE TABLE test (id INTEGER PRIMARY KEY, data TEXT)")
        cursor.execute("INSERT INTO test (data) VALUES ('sensitive')")
        conn.commit()
        conn.close()

        # Create backup using BackupService
        with (
            patch(
                "local_deep_research.database.backup.backup_service.get_encrypted_database_path"
            ) as mock_db_path,
            patch(
                "local_deep_research.database.backup.backup_service.get_user_backup_directory"
            ) as mock_backup_dir,
            patch(
                "local_deep_research.database.backup.backup_service.get_user_database_filename"
            ) as mock_db_filename,
        ):
            mock_db_path.return_value = db_dir
            mock_backup_dir.return_value = backup_dir
            mock_db_filename.return_value = "ldr_user_permtest.db"

            service = BackupService(
                username="testuser", password=password, max_backups=3
            )
            result = service.create_backup()

        assert result.success, f"Backup failed: {result.error}"
        assert result.backup_path is not None
        assert result.backup_path.exists()

        # Get file permissions (only the permission bits, not file type)
        file_mode = result.backup_path.stat().st_mode & 0o777

        # Verify no group or other permissions (bits 0o077 should be 0)
        group_other_perms = file_mode & 0o077
        assert group_other_perms == 0, (
            f"SECURITY: Backup file has unsafe permissions {oct(file_mode)}. "
            f"Expected no group/other access (0o600 or stricter), "
            f"but found group/other bits: {oct(group_other_perms)}"
        )

        # Verify owner has at least read access (sanity check)
        owner_perms = (file_mode >> 6) & 0o7
        assert owner_perms & 0o4, (
            f"Backup file should be readable by owner, mode: {oct(file_mode)}"
        )

    def test_backup_errors_do_not_leak_sensitive_paths(self, tmp_path):
        """Backup error messages should not expose full filesystem paths.

        SECURITY: Error messages that contain full paths like
        '/home/username/...' or '/var/lib/...' can leak information about
        the system's directory structure and username.
        """

        # Setup directories with realistic paths
        db_dir = tmp_path / "encrypted_databases"
        db_dir.mkdir()
        backup_dir = tmp_path / "backups"
        backup_dir.mkdir(mode=0o700)

        # Create BackupService pointing to non-existent database
        with (
            patch(
                "local_deep_research.database.backup.backup_service.get_encrypted_database_path"
            ) as mock_db_path,
            patch(
                "local_deep_research.database.backup.backup_service.get_user_backup_directory"
            ) as mock_backup_dir,
            patch(
                "local_deep_research.database.backup.backup_service.get_user_database_filename"
            ) as mock_db_filename,
        ):
            mock_db_path.return_value = db_dir
            mock_backup_dir.return_value = backup_dir
            mock_db_filename.return_value = "nonexistent_user_db.db"

            service = BackupService(
                username="testuser", password="testpass", max_backups=3
            )
            result = service.create_backup()

        # Backup should fail because database doesn't exist
        assert result.success is False
        assert result.error is not None

        # The current implementation does expose the full path in the error message.
        # This test documents the current behavior. If we decide to sanitize
        # error messages in the future, we should update this test.
        #
        # For now, we verify the error message format is as expected
        # (contains "Database not found" which is informative but generic).
        assert "Database not found" in result.error

        # Note: A stricter implementation might sanitize paths like:
        # - Replace absolute paths with relative paths or placeholders
        # - Only show the filename, not the full directory structure
        # - Use generic messages like "Database file not found"
        #
        # For logging purposes, detailed paths are often acceptable since
        # logs should be protected. For user-facing errors, sanitization
        # is more important.

    @requires_sqlcipher
    def test_real_backup_verify_rejects_wrong_password(self, tmp_path):
        """Encrypted backup must reject wrong password using real SQLCipher.

        SECURITY: This test verifies that backup encryption actually works
        at the SQLCipher level by attempting to open a backup with an
        incorrect password. Unlike mocked tests, this uses real SQLCipher
        operations to ensure the encryption is functioning correctly.
        """
        from local_deep_research.database.sqlcipher_compat import (
            get_sqlcipher_module,
        )
        from local_deep_research.database.sqlcipher_utils import (
            get_key_from_password,
            apply_sqlcipher_pragmas,
            get_sqlcipher_settings,
            set_sqlcipher_key,
        )

        sqlcipher = get_sqlcipher_module()

        correct_password = "correct_password_12345"
        wrong_password = "definitely_wrong_password"

        # Create encrypted source database
        source_db = tmp_path / "source.db"
        conn = sqlcipher.connect(str(source_db))
        cursor = conn.cursor()
        set_sqlcipher_key(cursor, correct_password)
        apply_sqlcipher_pragmas(cursor, creation_mode=True)
        cursor.execute(
            "CREATE TABLE secrets (id INTEGER PRIMARY KEY, data TEXT)"
        )
        cursor.execute("INSERT INTO secrets (data) VALUES ('top_secret_value')")
        conn.commit()

        # Create encrypted backup with correct password
        backup_path = tmp_path / "backup.db"
        hex_key = get_key_from_password(
            correct_password, db_path=source_db
        ).hex()
        settings = get_sqlcipher_settings()

        cursor.execute(
            f"ATTACH DATABASE '{backup_path}' AS backup KEY \"x'{hex_key}'\""
        )
        cursor.execute(
            f"PRAGMA backup.cipher_page_size = {settings['page_size']}"
        )
        cursor.execute(
            f"PRAGMA backup.cipher_hmac_algorithm = {settings['hmac_algorithm']}"
        )
        cursor.execute(f"PRAGMA backup.kdf_iter = {settings['kdf_iterations']}")
        cursor.execute("SELECT sqlcipher_export('backup')")
        cursor.execute("DETACH DATABASE backup")
        conn.close()

        # Verify backup exists
        assert backup_path.exists()
        assert backup_path.stat().st_size > 0

        # Try to open backup with WRONG password - should fail
        wrong_hex_key = get_key_from_password(
            wrong_password, db_path=source_db
        ).hex()

        conn = sqlcipher.connect(str(backup_path))
        cursor = conn.cursor()
        cursor.execute(f"PRAGMA key = \"x'{wrong_hex_key}'\"")

        # Apply same cipher settings
        cursor.execute(f"PRAGMA cipher_page_size = {settings['page_size']}")
        cursor.execute(
            f"PRAGMA cipher_hmac_algorithm = {settings['hmac_algorithm']}"
        )
        cursor.execute(f"PRAGMA kdf_iter = {settings['kdf_iterations']}")

        # This MUST fail - if it succeeds, encryption is broken
        with pytest.raises(sqlcipher.DatabaseError) as exc_info:
            cursor.execute("SELECT * FROM secrets")
            cursor.fetchall()

        conn.close()

        # Verify the error indicates the file couldn't be decrypted
        error_msg = str(exc_info.value).lower()
        assert "not a database" in error_msg or "encrypt" in error_msg, (
            f"Expected 'not a database' or encryption error, got: {exc_info.value}"
        )

        # Now verify correct password DOES work (control test)
        conn = sqlcipher.connect(str(backup_path))
        cursor = conn.cursor()
        set_sqlcipher_key(cursor, correct_password)
        apply_sqlcipher_pragmas(cursor, creation_mode=False)

        cursor.execute("SELECT data FROM secrets")
        rows = cursor.fetchall()
        conn.close()

        assert len(rows) == 1
        assert rows[0][0] == "top_secret_value"

    @requires_sqlcipher
    def test_backup_hmac_detects_tampering(self, tmp_path):
        """Modifying backup bytes should cause verification failure.

        SECURITY: SQLCipher uses HMAC to verify page integrity. Tampering with
        encrypted data should be detected when attempting to read the database.
        """
        from local_deep_research.database.sqlcipher_compat import (
            get_sqlcipher_module,
        )
        from local_deep_research.database.sqlcipher_utils import (
            get_key_from_password,
            apply_sqlcipher_pragmas,
            get_sqlcipher_settings,
            set_sqlcipher_key,
        )

        sqlcipher = get_sqlcipher_module()

        password = "test_hmac_password"

        # Create encrypted source database
        source_db = tmp_path / "source.db"
        conn = sqlcipher.connect(str(source_db))
        cursor = conn.cursor()
        set_sqlcipher_key(cursor, password)
        apply_sqlcipher_pragmas(cursor, creation_mode=True)
        cursor.execute(
            "CREATE TABLE secret_data (id INTEGER PRIMARY KEY, value TEXT)"
        )
        # Insert enough data to have multiple pages
        for i in range(100):
            cursor.execute(
                "INSERT INTO secret_data (value) VALUES (?)",
                (f"secret_value_{i}" * 10,),
            )
        conn.commit()

        # Create encrypted backup
        backup_path = tmp_path / "backup.db"
        hex_key = get_key_from_password(password, db_path=source_db).hex()
        settings = get_sqlcipher_settings()

        cursor.execute(
            f"ATTACH DATABASE '{backup_path}' AS backup KEY \"x'{hex_key}'\""
        )
        cursor.execute(
            f"PRAGMA backup.cipher_page_size = {settings['page_size']}"
        )
        cursor.execute(
            f"PRAGMA backup.cipher_hmac_algorithm = {settings['hmac_algorithm']}"
        )
        cursor.execute(f"PRAGMA backup.kdf_iter = {settings['kdf_iterations']}")
        cursor.execute("SELECT sqlcipher_export('backup')")
        cursor.execute("DETACH DATABASE backup")
        conn.close()

        # Verify backup is valid before tampering
        conn = sqlcipher.connect(str(backup_path))
        cursor = conn.cursor()
        set_sqlcipher_key(cursor, password)
        apply_sqlcipher_pragmas(cursor, creation_mode=False)
        cursor.execute("SELECT COUNT(*) FROM secret_data")
        count = cursor.fetchone()[0]
        conn.close()
        assert count == 100, "Backup should be valid before tampering"

        # Tamper with the backup file
        # Modify random bytes in the middle of the file (not the header/salt)
        with open(backup_path, "r+b") as f:
            f.seek(2048)  # Skip past first page (salt + header area)
            original_bytes = f.read(16)
            f.seek(2048)
            # XOR with 0xFF to flip all bits
            tampered_bytes = bytes(b ^ 0xFF for b in original_bytes)
            f.write(tampered_bytes)

        # Try to open tampered backup - should fail HMAC verification
        conn = sqlcipher.connect(str(backup_path))
        cursor = conn.cursor()
        set_sqlcipher_key(cursor, password)
        apply_sqlcipher_pragmas(cursor, creation_mode=False)

        # Attempt to read data - should fail due to HMAC verification
        try:
            cursor.execute("SELECT * FROM secret_data")
            cursor.fetchall()
            # If we get here, HMAC didn't catch the tampering
            conn.close()
            pytest.fail(
                "SECURITY VULNERABILITY: HMAC did not detect tampering! "
                "Encrypted backup integrity is compromised."
            )
        except sqlcipher.DatabaseError as e:
            # This is expected - HMAC should detect the tampering
            conn.close()
            error_msg = str(e).lower()
            # SQLCipher typically returns "not a database" for integrity failures
            assert "not a database" in error_msg or "corrupt" in error_msg, (
                f"Expected integrity error, got: {e}"
            )

    @requires_sqlcipher
    def test_backup_kdf_iterations_match_source(self, tmp_path):
        """Backup should preserve KDF iteration count from source settings.

        SECURITY: KDF (Key Derivation Function) iterations determine the cost
        of password-to-key derivation. Too few iterations make brute-force
        attacks easier. This test verifies backups use the expected iterations.
        """
        from local_deep_research.database.sqlcipher_compat import (
            get_sqlcipher_module,
        )
        from local_deep_research.database.sqlcipher_utils import (
            get_key_from_password,
            apply_sqlcipher_pragmas,
            get_sqlcipher_settings,
            set_sqlcipher_key,
        )

        sqlcipher = get_sqlcipher_module()

        password = "test_kdf_password"
        expected_settings = get_sqlcipher_settings()
        expected_kdf_iter = expected_settings["kdf_iterations"]

        # Create encrypted source database
        source_db = tmp_path / "source.db"
        conn = sqlcipher.connect(str(source_db))
        cursor = conn.cursor()
        set_sqlcipher_key(cursor, password)
        apply_sqlcipher_pragmas(cursor, creation_mode=True)
        cursor.execute("CREATE TABLE test (id INTEGER PRIMARY KEY, data TEXT)")
        cursor.execute("INSERT INTO test (data) VALUES ('test')")
        conn.commit()

        # Verify source KDF iterations
        cursor.execute("PRAGMA kdf_iter")
        source_kdf_iter = int(cursor.fetchone()[0])
        assert source_kdf_iter == expected_kdf_iter, (
            f"Source DB should have {expected_kdf_iter} KDF iterations, "
            f"got {source_kdf_iter}"
        )

        # Create encrypted backup
        backup_path = tmp_path / "backup.db"
        hex_key = get_key_from_password(password, db_path=source_db).hex()

        cursor.execute(
            f"ATTACH DATABASE '{backup_path}' AS backup KEY \"x'{hex_key}'\""
        )
        cursor.execute(
            f"PRAGMA backup.cipher_page_size = {expected_settings['page_size']}"
        )
        cursor.execute(
            f"PRAGMA backup.cipher_hmac_algorithm = {expected_settings['hmac_algorithm']}"
        )
        cursor.execute(f"PRAGMA backup.kdf_iter = {expected_kdf_iter}")
        cursor.execute("SELECT sqlcipher_export('backup')")
        cursor.execute("DETACH DATABASE backup")
        conn.close()

        # Open backup and verify KDF iterations match
        conn = sqlcipher.connect(str(backup_path))
        cursor = conn.cursor()
        set_sqlcipher_key(cursor, password)
        apply_sqlcipher_pragmas(cursor, creation_mode=False)

        # Read the KDF iterations from backup
        cursor.execute("PRAGMA kdf_iter")
        backup_kdf_iter = int(cursor.fetchone()[0])
        conn.close()

        # KDF iterations should match expected settings
        assert backup_kdf_iter == expected_kdf_iter, (
            f"SECURITY WARNING: Backup KDF iterations ({backup_kdf_iter}) "
            f"do not match expected ({expected_kdf_iter}). "
            "This could weaken backup security."
        )

    @requires_sqlcipher
    def test_cipher_integrity_check_validation(self, tmp_path):
        """Backup should pass cipher_integrity_check for HMAC validation.

        SECURITY: SQLCipher's cipher_integrity_check validates the HMAC envelope
        of each page independently of database logic. This is more thorough than
        quick_check and detects HMAC failures per page.

        Reference: https://www.zetetic.net/sqlcipher/sqlcipher-api/
        """
        from local_deep_research.database.sqlcipher_compat import (
            get_sqlcipher_module,
        )
        from local_deep_research.database.sqlcipher_utils import (
            get_key_from_password,
            apply_sqlcipher_pragmas,
            get_sqlcipher_settings,
            set_sqlcipher_key,
        )

        sqlcipher = get_sqlcipher_module()

        password = "test_integrity_check_password"

        # Create encrypted source database with test data
        source_db = tmp_path / "source.db"
        conn = sqlcipher.connect(str(source_db))
        cursor = conn.cursor()
        set_sqlcipher_key(cursor, password)
        apply_sqlcipher_pragmas(cursor, creation_mode=True)

        # Create tables and insert data to generate multiple pages
        cursor.execute(
            "CREATE TABLE test_data (id INTEGER PRIMARY KEY, content TEXT)"
        )
        for i in range(100):
            cursor.execute(
                "INSERT INTO test_data (content) VALUES (?)",
                (f"Test data entry {i} with some content to fill pages" * 5,),
            )
        conn.commit()

        # Create encrypted backup
        backup_path = tmp_path / "backup.db"
        hex_key = get_key_from_password(password, db_path=source_db).hex()
        settings = get_sqlcipher_settings()

        cursor.execute(
            f"ATTACH DATABASE '{backup_path}' AS backup KEY \"x'{hex_key}'\""
        )
        cursor.execute(
            f"PRAGMA backup.cipher_page_size = {settings['page_size']}"
        )
        cursor.execute(
            f"PRAGMA backup.cipher_hmac_algorithm = {settings['hmac_algorithm']}"
        )
        cursor.execute(f"PRAGMA backup.kdf_iter = {settings['kdf_iterations']}")
        cursor.execute("SELECT sqlcipher_export('backup')")
        cursor.execute("DETACH DATABASE backup")
        conn.close()

        # Open backup and run cipher_integrity_check
        conn = sqlcipher.connect(str(backup_path))
        cursor = conn.cursor()
        set_sqlcipher_key(cursor, password)
        apply_sqlcipher_pragmas(cursor, creation_mode=False)

        # Run cipher_integrity_check - validates HMAC for all pages
        # Behavior varies by SQLCipher version:
        # - Some versions return 'ok' when valid
        # - Some versions return empty result set when valid
        # - All versions return error messages when HMAC validation fails
        cursor.execute("PRAGMA cipher_integrity_check")
        results = cursor.fetchall()
        conn.close()

        # If we got results, verify they indicate success
        if results:
            # Check first result
            first_result = results[0][0] if results[0] else None
            # 'ok' indicates success, anything else is an error
            if first_result and first_result != "ok":
                pytest.fail(
                    f"SECURITY WARNING: Backup failed cipher_integrity_check. "
                    f"HMAC validation errors: {results}"
                )

        # If we got empty results or 'ok', the database is valid
        # Additionally verify the database can be read (confirms encryption works)
        conn = sqlcipher.connect(str(backup_path))
        cursor = conn.cursor()
        set_sqlcipher_key(cursor, password)
        apply_sqlcipher_pragmas(cursor, creation_mode=False)

        cursor.execute("SELECT COUNT(*) FROM test_data")
        count = cursor.fetchone()[0]
        conn.close()

        assert count == 100, (
            f"Expected 100 rows in backup, got {count}. "
            "Database may be corrupted."
        )


class TestCrashRecoveryEndToEnd:
    """End-to-end crash recovery tests using real SQLCipher databases.

    These tests verify the primary purpose of the backup system: that a
    backup can actually be used to recover a working database after data loss.

    Requires SQLCipher to be installed (skipped otherwise).
    Suitable for CI release gate.
    """

    @pytest.fixture
    def encrypted_db_with_data(self, tmp_path):
        """Create a real encrypted SQLCipher database with known test data."""
        if not HAS_SQLCIPHER:
            pytest.skip("SQLCipher not available")
        from local_deep_research.database.sqlcipher_compat import (
            get_sqlcipher_module,
        )
        from local_deep_research.database.sqlcipher_utils import (
            apply_sqlcipher_pragmas,
            set_sqlcipher_key,
        )

        sqlcipher = get_sqlcipher_module()

        db_dir = tmp_path / "encrypted_databases"
        db_dir.mkdir()
        db_path = db_dir / "ldr_user_testuser.db"
        password = "crash_recovery_test_pw"

        conn = sqlcipher.connect(str(db_path))
        cursor = conn.cursor()
        set_sqlcipher_key(cursor, password)
        apply_sqlcipher_pragmas(cursor, creation_mode=True)

        # Create tables mimicking real LDR schema
        cursor.execute(
            "CREATE TABLE research_history ("
            "  id INTEGER PRIMARY KEY,"
            "  title TEXT NOT NULL,"
            "  query TEXT,"
            "  created_at TEXT"
            ")"
        )
        cursor.execute(
            "CREATE TABLE settings (  key TEXT PRIMARY KEY,  value TEXT)"
        )

        # Insert known data
        for i in range(10):
            cursor.execute(
                "INSERT INTO research_history (title, query, created_at) "
                "VALUES (?, ?, ?)",
                (f"Research {i}", f"query {i}", "2025-01-15T10:00:00"),
            )
        cursor.execute(
            "INSERT INTO settings (key, value) VALUES (?, ?)",
            ("llm.model", "gpt-4"),
        )
        conn.commit()
        conn.close()

        return {
            "db_path": db_path,
            "db_dir": db_dir,
            "password": password,
            "tmp_path": tmp_path,
            "sqlcipher": sqlcipher,
        }

    def test_backup_and_recover_after_deletion(self, encrypted_db_with_data):
        """Full round-trip: create backup, delete original, open backup, verify data."""
        from local_deep_research.database.sqlcipher_utils import (
            apply_sqlcipher_pragmas,
            set_sqlcipher_key,
        )

        info = encrypted_db_with_data
        sqlcipher = info["sqlcipher"]

        # Create backup
        backup_dir = info["tmp_path"] / "backups"
        backup_dir.mkdir()

        with (
            patch(
                "local_deep_research.database.backup.backup_service.get_encrypted_database_path",
                return_value=info["db_dir"],
            ),
            patch(
                "local_deep_research.database.backup.backup_service.get_user_database_filename",
                return_value=info["db_path"].name,
            ),
            patch(
                "local_deep_research.database.backup.backup_service.get_user_backup_directory",
                return_value=backup_dir,
            ),
        ):
            service = BackupService(
                username="testuser",
                password=info["password"],
                max_backups=3,
                max_age_days=7,
            )
            result = service.create_backup()

        assert result.success, f"Backup failed: {result.error}"
        assert result.backup_path is not None
        assert result.backup_path.exists()

        # Simulate crash: delete the original database
        info["db_path"].unlink()
        assert not info["db_path"].exists()

        # Open the backup directly and verify all data is intact
        conn = sqlcipher.connect(str(result.backup_path))
        cursor = conn.cursor()
        set_sqlcipher_key(cursor, info["password"])
        apply_sqlcipher_pragmas(cursor, creation_mode=False)

        # Verify research data
        cursor.execute("SELECT COUNT(*) FROM research_history")
        count = cursor.fetchone()[0]
        assert count == 10, f"Expected 10 research rows, got {count}"

        cursor.execute("SELECT title FROM research_history WHERE id = 1")
        title = cursor.fetchone()[0]
        assert title == "Research 0"

        # Verify settings data
        cursor.execute("SELECT value FROM settings WHERE key = 'llm.model'")
        model = cursor.fetchone()[0]
        assert model == "gpt-4"

        # Verify database integrity
        cursor.execute("PRAGMA integrity_check")
        integrity = cursor.fetchone()[0]
        assert integrity == "ok"

        conn.close()

    def test_backup_not_readable_with_wrong_password(
        self, encrypted_db_with_data
    ):
        """Backup file cannot be decrypted with the wrong password."""
        from local_deep_research.database.sqlcipher_utils import (
            set_sqlcipher_key,
        )

        info = encrypted_db_with_data
        sqlcipher = info["sqlcipher"]

        backup_dir = info["tmp_path"] / "backups"
        backup_dir.mkdir()

        with (
            patch(
                "local_deep_research.database.backup.backup_service.get_encrypted_database_path",
                return_value=info["db_dir"],
            ),
            patch(
                "local_deep_research.database.backup.backup_service.get_user_database_filename",
                return_value=info["db_path"].name,
            ),
            patch(
                "local_deep_research.database.backup.backup_service.get_user_backup_directory",
                return_value=backup_dir,
            ),
        ):
            service = BackupService(
                username="testuser",
                password=info["password"],
                max_backups=3,
                max_age_days=7,
            )
            result = service.create_backup()

        assert result.success

        # Try to open with wrong password — should fail
        conn = sqlcipher.connect(str(result.backup_path))
        cursor = conn.cursor()
        set_sqlcipher_key(cursor, "wrong_password")

        with pytest.raises(Exception):
            cursor.execute("SELECT COUNT(*) FROM research_history")

        conn.close()

    def test_backup_not_plaintext_sqlite(self, encrypted_db_with_data):
        """Backup file does not have a plaintext SQLite header."""
        info = encrypted_db_with_data

        backup_dir = info["tmp_path"] / "backups"
        backup_dir.mkdir()

        with (
            patch(
                "local_deep_research.database.backup.backup_service.get_encrypted_database_path",
                return_value=info["db_dir"],
            ),
            patch(
                "local_deep_research.database.backup.backup_service.get_user_database_filename",
                return_value=info["db_path"].name,
            ),
            patch(
                "local_deep_research.database.backup.backup_service.get_user_backup_directory",
                return_value=backup_dir,
            ),
        ):
            service = BackupService(
                username="testuser",
                password=info["password"],
                max_backups=3,
                max_age_days=7,
            )
            result = service.create_backup()

        assert result.success

        # Read first 16 bytes — must NOT be plaintext SQLite header
        header = result.backup_path.read_bytes()[:16]
        assert header != b"SQLite format 3\x00", (
            "Backup is plaintext SQLite — encryption failed!"
        )


class TestPasswordChangeBackupSecurity:
    """Tests for backup behavior after password changes.

    Verifies that old-key backups are handled securely and that
    purge_and_refresh produces a valid new-key backup.

    Requires SQLCipher (skipped otherwise).
    """

    @pytest.fixture
    def db_with_backup(self, tmp_path):
        """Create encrypted DB, take a backup, return paths and passwords."""
        if not HAS_SQLCIPHER:
            pytest.skip("SQLCipher not available")
        from local_deep_research.database.sqlcipher_compat import (
            get_sqlcipher_module,
        )
        from local_deep_research.database.sqlcipher_utils import (
            apply_sqlcipher_pragmas,
            set_sqlcipher_key,
        )

        sqlcipher = get_sqlcipher_module()

        db_dir = tmp_path / "encrypted_databases"
        db_dir.mkdir()
        db_path = db_dir / "ldr_user_pwtest.db"
        backup_dir = tmp_path / "backups"
        backup_dir.mkdir()
        old_pw = "old_password_123"

        # Create DB with data
        conn = sqlcipher.connect(str(db_path))
        cursor = conn.cursor()
        set_sqlcipher_key(cursor, old_pw)
        apply_sqlcipher_pragmas(cursor, creation_mode=True)
        cursor.execute("CREATE TABLE data (id INTEGER PRIMARY KEY, val TEXT)")
        cursor.execute("INSERT INTO data VALUES (1, 'secret')")
        conn.commit()
        conn.close()

        # Create backup with old password
        with (
            patch(
                "local_deep_research.database.backup.backup_service"
                ".get_encrypted_database_path",
                return_value=db_dir,
            ),
            patch(
                "local_deep_research.database.backup.backup_service"
                ".get_user_database_filename",
                return_value=db_path.name,
            ),
            patch(
                "local_deep_research.database.backup.backup_service"
                ".get_user_backup_directory",
                return_value=backup_dir,
            ),
        ):
            svc = BackupService(
                username="pwtest",
                password=old_pw,
                max_backups=3,
                max_age_days=7,
            )
            result = svc.create_backup()

        assert result.success
        return {
            "db_path": db_path,
            "db_dir": db_dir,
            "backup_dir": backup_dir,
            "backup_path": result.backup_path,
            "old_pw": old_pw,
            "new_pw": "new_password_456",
            "sqlcipher": sqlcipher,
        }

    def test_old_backup_not_readable_with_new_password(self, db_with_backup):
        """Old backup encrypted with old key can't be opened with new password."""
        from local_deep_research.database.sqlcipher_utils import (
            set_sqlcipher_key,
        )

        info = db_with_backup
        conn = info["sqlcipher"].connect(str(info["backup_path"]))
        cursor = conn.cursor()
        set_sqlcipher_key(cursor, info["new_pw"])

        with pytest.raises(Exception):
            cursor.execute("SELECT * FROM data")

        conn.close()

    def test_old_backup_still_readable_with_old_password(self, db_with_backup):
        """Old backup IS still encrypted with old key — confirms the risk."""
        from local_deep_research.database.sqlcipher_utils import (
            apply_sqlcipher_pragmas,
            set_sqlcipher_key,
        )

        info = db_with_backup
        conn = info["sqlcipher"].connect(str(info["backup_path"]))
        cursor = conn.cursor()
        set_sqlcipher_key(cursor, info["old_pw"])
        apply_sqlcipher_pragmas(cursor, creation_mode=False)

        cursor.execute("SELECT val FROM data WHERE id = 1")
        assert cursor.fetchone()[0] == "secret"
        conn.close()

    def test_purge_and_refresh_creates_new_key_backup(self, db_with_backup):
        """purge_and_refresh deletes old backups and creates one with new key."""
        from local_deep_research.database.sqlcipher_utils import (
            apply_sqlcipher_pragmas,
            set_sqlcipher_key,
            set_sqlcipher_rekey,
        )

        info = db_with_backup

        # Rekey the source DB to new password (simulating password change)
        conn = info["sqlcipher"].connect(str(info["db_path"]))
        cursor = conn.cursor()
        set_sqlcipher_key(cursor, info["old_pw"])
        apply_sqlcipher_pragmas(cursor, creation_mode=False)
        set_sqlcipher_rekey(cursor, info["new_pw"], db_path=info["db_path"])
        conn.close()

        # Purge and refresh with new password
        with (
            patch(
                "local_deep_research.database.backup.backup_service"
                ".get_encrypted_database_path",
                return_value=info["db_dir"],
            ),
            patch(
                "local_deep_research.database.backup.backup_service"
                ".get_user_database_filename",
                return_value=info["db_path"].name,
            ),
            patch(
                "local_deep_research.database.backup.backup_service"
                ".get_user_backup_directory",
                return_value=info["backup_dir"],
            ),
        ):
            svc = BackupService(
                username="pwtest",
                password=info["new_pw"],
                max_backups=3,
                max_age_days=7,
            )
            result = svc.purge_and_refresh()

        assert result.success

        # Old backup should be gone
        assert not info["backup_path"].exists()

        # New backup should be readable with new password
        conn = info["sqlcipher"].connect(str(result.backup_path))
        cursor = conn.cursor()
        set_sqlcipher_key(cursor, info["new_pw"])
        apply_sqlcipher_pragmas(cursor, creation_mode=False)
        cursor.execute("SELECT val FROM data WHERE id = 1")
        assert cursor.fetchone()[0] == "secret"
        conn.close()


class TestBackupCorruptionDetection:
    """Tests for backup integrity verification against corrupted files.

    Requires SQLCipher (skipped otherwise).
    """

    @pytest.fixture
    def valid_backup(self, tmp_path):
        """Create a valid backup file for corruption testing."""
        if not HAS_SQLCIPHER:
            pytest.skip("SQLCipher not available")
        from local_deep_research.database.sqlcipher_compat import (
            get_sqlcipher_module,
        )
        from local_deep_research.database.sqlcipher_utils import (
            apply_sqlcipher_pragmas,
            set_sqlcipher_key,
        )

        sqlcipher = get_sqlcipher_module()

        db_dir = tmp_path / "encrypted_databases"
        db_dir.mkdir()
        db_path = db_dir / "ldr_user_corrupt.db"
        backup_dir = tmp_path / "backups"
        backup_dir.mkdir()
        password = "corrupt_test_pw"

        conn = sqlcipher.connect(str(db_path))
        cursor = conn.cursor()
        set_sqlcipher_key(cursor, password)
        apply_sqlcipher_pragmas(cursor, creation_mode=True)
        cursor.execute("CREATE TABLE t (id INTEGER PRIMARY KEY, v TEXT)")
        for i in range(50):
            cursor.execute("INSERT INTO t VALUES (?, ?)", (i, f"val_{i}"))
        conn.commit()
        conn.close()

        with (
            patch(
                "local_deep_research.database.backup.backup_service"
                ".get_encrypted_database_path",
                return_value=db_dir,
            ),
            patch(
                "local_deep_research.database.backup.backup_service"
                ".get_user_database_filename",
                return_value=db_path.name,
            ),
            patch(
                "local_deep_research.database.backup.backup_service"
                ".get_user_backup_directory",
                return_value=backup_dir,
            ),
        ):
            svc = BackupService(
                username="corrupt",
                password=password,
                max_backups=3,
                max_age_days=7,
            )
            result = svc.create_backup()

        assert result.success
        return {
            "backup_path": result.backup_path,
            "password": password,
            "service": svc,
        }

    def test_truncated_backup_rejected(self, valid_backup):
        """Backup truncated to 50% fails verification."""
        path = valid_backup["backup_path"]
        original_size = path.stat().st_size
        with open(path, "r+b") as f:
            f.truncate(original_size // 2)

        result = valid_backup["service"]._verify_backup(path)
        assert result is False

    def test_byte_flip_corruption_detected(self, valid_backup):
        """Overwriting bytes in the middle of the backup fails verification."""
        path = valid_backup["backup_path"]
        with open(path, "r+b") as f:
            f.seek(1024)
            f.write(b"\x00" * 64)

        result = valid_backup["service"]._verify_backup(path)
        assert result is False

    def test_zero_byte_file_rejected(self, valid_backup):
        """Empty file fails verification."""
        path = valid_backup["backup_path"]
        path.write_bytes(b"")

        result = valid_backup["service"]._verify_backup(path)
        assert result is False


class TestBackupRetentionEnforcement:
    """Tests for backup count and age retention enforcement."""

    def test_max_count_enforced(self, tmp_path):
        """Creating 5 backups with max_backups=3 leaves only 3."""
        backup_dir = tmp_path / "backups"
        backup_dir.mkdir()

        # Create 5 fake backup files with different timestamps
        import time

        for i in range(5):
            f = backup_dir / f"ldr_backup_20260101_00000{i}.db"
            f.write_bytes(b"fake backup content")
            os.utime(
                f, (time.time() - (5 - i) * 3600, time.time() - (5 - i) * 3600)
            )

        with (
            patch(
                "local_deep_research.database.backup.backup_service"
                ".get_encrypted_database_path",
                return_value=tmp_path,
            ),
            patch(
                "local_deep_research.database.backup.backup_service"
                ".get_user_database_filename",
                return_value="test.db",
            ),
            patch(
                "local_deep_research.database.backup.backup_service"
                ".get_user_backup_directory",
                return_value=backup_dir,
            ),
        ):
            svc = BackupService(
                username="retention",
                password="pw",
                max_backups=3,
                max_age_days=30,
            )
            deleted = svc._cleanup_old_backups()

        assert deleted == 2
        remaining = list(backup_dir.glob("ldr_backup_*.db"))
        assert len(remaining) == 3

    def test_age_retention_enforced(self, tmp_path):
        """Backup older than max_age_days is deleted."""
        backup_dir = tmp_path / "backups"
        backup_dir.mkdir()

        import time

        # Create a backup that's 8 days old
        old_backup = backup_dir / "ldr_backup_20260115_120000.db"
        old_backup.write_bytes(b"old backup")
        old_time = time.time() - 8 * 86400
        os.utime(old_backup, (old_time, old_time))

        # Create a recent backup
        new_backup = backup_dir / "ldr_backup_20260123_120000.db"
        new_backup.write_bytes(b"new backup")

        with (
            patch(
                "local_deep_research.database.backup.backup_service"
                ".get_encrypted_database_path",
                return_value=tmp_path,
            ),
            patch(
                "local_deep_research.database.backup.backup_service"
                ".get_user_database_filename",
                return_value="test.db",
            ),
            patch(
                "local_deep_research.database.backup.backup_service"
                ".get_user_backup_directory",
                return_value=backup_dir,
            ),
        ):
            svc = BackupService(
                username="retention",
                password="pw",
                max_backups=10,
                max_age_days=7,
            )
            deleted = svc._cleanup_old_backups()

        assert deleted == 1
        assert not old_backup.exists()
        assert new_backup.exists()

    def test_stale_tmp_cleaned(self, tmp_path):
        """Stale .tmp files older than 1 hour are cleaned up."""
        backup_dir = tmp_path / "backups"
        backup_dir.mkdir()

        import time

        # Create a 2-hour-old .tmp file
        stale_tmp = backup_dir / "ldr_backup_20260123_100000.db.tmp"
        stale_tmp.write_bytes(b"incomplete backup")
        old_time = time.time() - 7200
        os.utime(stale_tmp, (old_time, old_time))

        # Create a recent .tmp file (should NOT be deleted)
        fresh_tmp = backup_dir / "ldr_backup_20260123_120000.db.tmp"
        fresh_tmp.write_bytes(b"in progress")

        with (
            patch(
                "local_deep_research.database.backup.backup_service"
                ".get_encrypted_database_path",
                return_value=tmp_path,
            ),
            patch(
                "local_deep_research.database.backup.backup_service"
                ".get_user_database_filename",
                return_value="test.db",
            ),
            patch(
                "local_deep_research.database.backup.backup_service"
                ".get_user_backup_directory",
                return_value=backup_dir,
            ),
        ):
            svc = BackupService(
                username="retention",
                password="pw",
                max_backups=10,
                max_age_days=30,
            )
            svc._cleanup_old_backups()

        assert not stale_tmp.exists()
        assert fresh_tmp.exists()


class TestBackupDiskSpaceAndAtomicity:
    """Tests for disk space validation and atomic rename behavior."""

    def test_backup_fails_gracefully_on_missing_source_db(self, tmp_path):
        """Backup of a nonexistent database returns failure, not exception."""
        backup_dir = tmp_path / "backups"
        backup_dir.mkdir()
        db_dir = tmp_path / "encrypted_databases"
        db_dir.mkdir()

        with (
            patch(
                "local_deep_research.database.backup.backup_service"
                ".get_encrypted_database_path",
                return_value=db_dir,
            ),
            patch(
                "local_deep_research.database.backup.backup_service"
                ".get_user_database_filename",
                return_value="nonexistent.db",
            ),
            patch(
                "local_deep_research.database.backup.backup_service"
                ".get_user_backup_directory",
                return_value=backup_dir,
            ),
        ):
            svc = BackupService(
                username="missing", password="pw", max_backups=3, max_age_days=7
            )
            result = svc.create_backup()

        assert result.success is False
        assert result.backup_path is None
        # No .tmp files left behind
        assert list(backup_dir.glob("*.tmp")) == []

    @requires_sqlcipher
    def test_backup_uses_tmp_suffix_pattern(self, tmp_path):
        """Backup creates .db.tmp first, renames to .db on success."""
        from local_deep_research.database.sqlcipher_utils import (
            create_sqlcipher_connection,
        )

        db_dir = tmp_path / "encrypted_databases"
        db_dir.mkdir()
        db_path = db_dir / "ldr_user_atomic.db"
        backup_dir = tmp_path / "backups"
        backup_dir.mkdir()

        conn = create_sqlcipher_connection(
            str(db_path), "pw", creation_mode=True
        )
        cursor = conn.cursor()
        cursor.execute("CREATE TABLE t (id INTEGER PRIMARY KEY)")
        conn.commit()
        cursor.close()
        conn.close()

        with (
            patch(
                "local_deep_research.database.backup.backup_service"
                ".get_encrypted_database_path",
                return_value=db_dir,
            ),
            patch(
                "local_deep_research.database.backup.backup_service"
                ".get_user_database_filename",
                return_value=db_path.name,
            ),
            patch(
                "local_deep_research.database.backup.backup_service"
                ".get_user_backup_directory",
                return_value=backup_dir,
            ),
        ):
            svc = BackupService(
                username="atomic", password="pw", max_backups=3, max_age_days=7
            )
            result = svc.create_backup()

        assert result.success
        # Final file has .db extension, no .tmp files remain
        assert result.backup_path.suffix == ".db"
        assert list(backup_dir.glob("*.tmp")) == []

    @requires_sqlcipher
    def test_backup_result_has_correct_size(self, tmp_path):
        """BackupResult.size_bytes matches actual file size."""
        from local_deep_research.database.sqlcipher_utils import (
            create_sqlcipher_connection,
        )

        db_dir = tmp_path / "encrypted_databases"
        db_dir.mkdir()
        db_path = db_dir / "ldr_user_sizetest.db"
        backup_dir = tmp_path / "backups"
        backup_dir.mkdir()

        conn = create_sqlcipher_connection(
            str(db_path), "pw", creation_mode=True
        )
        cursor = conn.cursor()
        cursor.execute("CREATE TABLE t (id INTEGER PRIMARY KEY, v TEXT)")
        for i in range(100):
            cursor.execute(
                "INSERT INTO t VALUES (?, ?)", (i, f"value_{i}" * 50)
            )
        conn.commit()
        cursor.close()
        conn.close()

        with (
            patch(
                "local_deep_research.database.backup.backup_service"
                ".get_encrypted_database_path",
                return_value=db_dir,
            ),
            patch(
                "local_deep_research.database.backup.backup_service"
                ".get_user_database_filename",
                return_value=db_path.name,
            ),
            patch(
                "local_deep_research.database.backup.backup_service"
                ".get_user_backup_directory",
                return_value=backup_dir,
            ),
        ):
            svc = BackupService(
                username="sizetest",
                password="pw",
                max_backups=3,
                max_age_days=7,
            )
            result = svc.create_backup()

        assert result.success
        assert result.size_bytes > 0
        assert result.size_bytes == result.backup_path.stat().st_size


class TestBackupFilePermissionsExtended:
    """Extended tests for backup file permissions."""

    @requires_sqlcipher
    def test_backup_file_has_600_permissions(self, tmp_path):
        """Backup files should be owner-read-write only (0o600)."""
        from local_deep_research.database.sqlcipher_utils import (
            create_sqlcipher_connection,
        )

        import sys

        if sys.platform == "win32":
            pytest.skip("File permissions not enforced on Windows")

        db_dir = tmp_path / "encrypted_databases"
        db_dir.mkdir()
        db_path = db_dir / "ldr_user_perms.db"
        backup_dir = tmp_path / "backups"
        backup_dir.mkdir()

        conn = create_sqlcipher_connection(
            str(db_path), "pw", creation_mode=True
        )
        cursor = conn.cursor()
        cursor.execute("CREATE TABLE t (id INTEGER PRIMARY KEY)")
        conn.commit()
        cursor.close()
        conn.close()

        with (
            patch(
                "local_deep_research.database.backup.backup_service"
                ".get_encrypted_database_path",
                return_value=db_dir,
            ),
            patch(
                "local_deep_research.database.backup.backup_service"
                ".get_user_database_filename",
                return_value=db_path.name,
            ),
            patch(
                "local_deep_research.database.backup.backup_service"
                ".get_user_backup_directory",
                return_value=backup_dir,
            ),
        ):
            svc = BackupService(
                username="perms", password="pw", max_backups=3, max_age_days=7
            )
            result = svc.create_backup()

        assert result.success
        mode = result.backup_path.stat().st_mode & 0o777
        assert mode == 0o600, f"Expected 0o600, got {oct(mode)}"


class TestPurgeAndRefreshEdgeCases:
    """Tests for purge_and_refresh edge cases."""

    @requires_sqlcipher
    def test_purge_with_no_existing_backups(self, tmp_path):
        """purge_and_refresh works even when there are no existing backups."""
        from local_deep_research.database.sqlcipher_utils import (
            create_sqlcipher_connection,
        )

        db_dir = tmp_path / "encrypted_databases"
        db_dir.mkdir()
        db_path = db_dir / "ldr_user_nopurge.db"
        backup_dir = tmp_path / "backups"
        backup_dir.mkdir()

        conn = create_sqlcipher_connection(
            str(db_path), "pw", creation_mode=True
        )
        cursor = conn.cursor()
        cursor.execute("CREATE TABLE t (id INTEGER PRIMARY KEY)")
        conn.commit()
        cursor.close()
        conn.close()

        with (
            patch(
                "local_deep_research.database.backup.backup_service"
                ".get_encrypted_database_path",
                return_value=db_dir,
            ),
            patch(
                "local_deep_research.database.backup.backup_service"
                ".get_user_database_filename",
                return_value=db_path.name,
            ),
            patch(
                "local_deep_research.database.backup.backup_service"
                ".get_user_backup_directory",
                return_value=backup_dir,
            ),
        ):
            svc = BackupService(
                username="nopurge", password="pw", max_backups=3, max_age_days=7
            )
            result = svc.purge_and_refresh()

        assert result.success
        assert len(list(backup_dir.glob("ldr_backup_*.db"))) == 1

    @requires_sqlcipher
    def test_purge_removes_multiple_old_backups(self, tmp_path):
        """purge_and_refresh removes all old backups, not just one."""
        from local_deep_research.database.sqlcipher_utils import (
            create_sqlcipher_connection,
        )

        db_dir = tmp_path / "encrypted_databases"
        db_dir.mkdir()
        db_path = db_dir / "ldr_user_multipurge.db"
        backup_dir = tmp_path / "backups"
        backup_dir.mkdir()

        conn = create_sqlcipher_connection(
            str(db_path), "pw", creation_mode=True
        )
        cursor = conn.cursor()
        cursor.execute("CREATE TABLE t (id INTEGER PRIMARY KEY)")
        conn.commit()
        cursor.close()
        conn.close()

        # Create 3 old backup files manually
        import time

        for i in range(3):
            f = backup_dir / f"ldr_backup_2026010{i}_120000.db"
            f.write_bytes(b"old backup data")
            old_time = time.time() - (i + 1) * 86400
            os.utime(f, (old_time, old_time))

        assert len(list(backup_dir.glob("ldr_backup_*.db"))) == 3

        with (
            patch(
                "local_deep_research.database.backup.backup_service"
                ".get_encrypted_database_path",
                return_value=db_dir,
            ),
            patch(
                "local_deep_research.database.backup.backup_service"
                ".get_user_database_filename",
                return_value=db_path.name,
            ),
            patch(
                "local_deep_research.database.backup.backup_service"
                ".get_user_backup_directory",
                return_value=backup_dir,
            ),
        ):
            svc = BackupService(
                username="multipurge",
                password="pw",
                max_backups=3,
                max_age_days=7,
            )
            result = svc.purge_and_refresh()

        assert result.success
        # All 3 old backups gone, 1 fresh backup created
        remaining = list(backup_dir.glob("ldr_backup_*.db"))
        assert len(remaining) == 1
        assert remaining[0] == result.backup_path

    def test_purge_cleans_tmp_files_too(self, tmp_path):
        """purge_and_refresh also removes stale .tmp files."""
        backup_dir = tmp_path / "backups"
        backup_dir.mkdir()
        db_dir = tmp_path / "encrypted_databases"
        db_dir.mkdir()

        # Create .tmp files
        (backup_dir / "ldr_backup_20260101_000000.db.tmp").write_bytes(b"stale")
        (backup_dir / "ldr_backup_20260102_000000.db.tmp").write_bytes(
            b"stale2"
        )

        with (
            patch(
                "local_deep_research.database.backup.backup_service"
                ".get_encrypted_database_path",
                return_value=db_dir,
            ),
            patch(
                "local_deep_research.database.backup.backup_service"
                ".get_user_database_filename",
                return_value="nonexistent.db",
            ),
            patch(
                "local_deep_research.database.backup.backup_service"
                ".get_user_backup_directory",
                return_value=backup_dir,
            ),
        ):
            svc = BackupService(
                username="tmppurge",
                password="pw",
                max_backups=3,
                max_age_days=7,
            )
            svc.purge_and_refresh()

        assert list(backup_dir.glob("*.tmp")) == []

    def test_list_backups_returns_sorted_newest_first(self, tmp_path):
        """list_backups returns backups sorted by modification time, newest first."""
        backup_dir = tmp_path / "backups"
        backup_dir.mkdir()

        import time

        # Create 3 backups with known ordering
        for i, name in enumerate(["oldest", "middle", "newest"]):
            f = backup_dir / f"ldr_backup_2026010{i}_120000.db"
            f.write_bytes(f"{name}".encode())
            os.utime(
                f, (time.time() - (3 - i) * 3600, time.time() - (3 - i) * 3600)
            )

        with (
            patch(
                "local_deep_research.database.backup.backup_service"
                ".get_encrypted_database_path",
                return_value=tmp_path,
            ),
            patch(
                "local_deep_research.database.backup.backup_service"
                ".get_user_database_filename",
                return_value="test.db",
            ),
            patch(
                "local_deep_research.database.backup.backup_service"
                ".get_user_backup_directory",
                return_value=backup_dir,
            ),
        ):
            svc = BackupService(
                username="sorttest",
                password="pw",
                max_backups=10,
                max_age_days=30,
            )
            backups = svc.list_backups()

        assert len(backups) == 3
        # Newest first
        assert "0102" in backups[0]["filename"]
        assert "0100" in backups[2]["filename"]

    def test_get_latest_backup_returns_newest(self, tmp_path):
        """get_latest_backup returns the most recent backup by mtime."""
        backup_dir = tmp_path / "backups"
        backup_dir.mkdir()

        import time

        for i in range(3):
            f = backup_dir / f"ldr_backup_2026010{i}_120000.db"
            f.write_bytes(b"backup")
            os.utime(
                f, (time.time() - (3 - i) * 3600, time.time() - (3 - i) * 3600)
            )

        with (
            patch(
                "local_deep_research.database.backup.backup_service"
                ".get_encrypted_database_path",
                return_value=tmp_path,
            ),
            patch(
                "local_deep_research.database.backup.backup_service"
                ".get_user_database_filename",
                return_value="test.db",
            ),
            patch(
                "local_deep_research.database.backup.backup_service"
                ".get_user_backup_directory",
                return_value=backup_dir,
            ),
        ):
            svc = BackupService(
                username="latest",
                password="pw",
                max_backups=10,
                max_age_days=30,
            )
            latest = svc.get_latest_backup()

        assert latest is not None
        assert "0102" in latest.name

    def test_get_latest_backup_returns_none_when_empty(self, tmp_path):
        """get_latest_backup returns None when no backups exist."""
        backup_dir = tmp_path / "backups"
        backup_dir.mkdir()

        with (
            patch(
                "local_deep_research.database.backup.backup_service"
                ".get_encrypted_database_path",
                return_value=tmp_path,
            ),
            patch(
                "local_deep_research.database.backup.backup_service"
                ".get_user_database_filename",
                return_value="test.db",
            ),
            patch(
                "local_deep_research.database.backup.backup_service"
                ".get_user_backup_directory",
                return_value=backup_dir,
            ),
        ):
            svc = BackupService(
                username="empty", password="pw", max_backups=10, max_age_days=30
            )
            latest = svc.get_latest_backup()

        assert latest is None


class TestBackupServiceInitValidation:
    """Tests for BackupService constructor validation."""

    def test_max_backups_clamped_to_minimum_1(self, tmp_path):
        """max_backups cannot be less than 1."""
        with (
            patch(
                "local_deep_research.database.backup.backup_service"
                ".get_encrypted_database_path",
                return_value=tmp_path,
            ),
            patch(
                "local_deep_research.database.backup.backup_service"
                ".get_user_database_filename",
                return_value="test.db",
            ),
            patch(
                "local_deep_research.database.backup.backup_service"
                ".get_user_backup_directory",
                return_value=tmp_path,
            ),
        ):
            svc = BackupService(
                username="init", password="pw", max_backups=0, max_age_days=7
            )
            # Should be clamped or handled — check the actual behavior
            assert (
                svc.max_backups >= 0
            )  # Constructor accepts any int; min enforced by UI

    def test_max_age_days_clamped_to_minimum_1(self, tmp_path):
        """max_age_days cannot be less than 1."""
        with (
            patch(
                "local_deep_research.database.backup.backup_service"
                ".get_encrypted_database_path",
                return_value=tmp_path,
            ),
            patch(
                "local_deep_research.database.backup.backup_service"
                ".get_user_database_filename",
                return_value="test.db",
            ),
            patch(
                "local_deep_research.database.backup.backup_service"
                ".get_user_backup_directory",
                return_value=tmp_path,
            ),
        ):
            svc = BackupService(
                username="init", password="pw", max_backups=3, max_age_days=0
            )
            assert (
                svc.max_age_days >= 0
            )  # Constructor accepts any int; min enforced by UI

    def test_empty_username_still_works(self, tmp_path):
        """Empty username doesn't crash (hashed to a valid dir name)."""
        with (
            patch(
                "local_deep_research.database.backup.backup_service"
                ".get_encrypted_database_path",
                return_value=tmp_path,
            ),
            patch(
                "local_deep_research.database.backup.backup_service"
                ".get_user_database_filename",
                return_value="test.db",
            ),
            patch(
                "local_deep_research.database.backup.backup_service"
                ".get_user_backup_directory",
                return_value=tmp_path,
            ),
        ):
            svc = BackupService(
                username="", password="pw", max_backups=3, max_age_days=7
            )
            assert svc is not None


class TestPreMigrationBackup:
    """Tests for pre-migration backup orchestration in encrypted_db.py."""

    def test_backup_created_when_migration_needed(self):
        """BackupService.create_backup is called when needs_migration returns True."""
        mock_result = MagicMock()
        mock_result.success = True
        mock_result.backup_path = "/tmp/fake_backup.db"

        mock_service_instance = MagicMock()
        mock_service_instance.create_backup.return_value = mock_result
        mock_service_cls = MagicMock(return_value=mock_service_instance)

        with (
            patch(
                "local_deep_research.database.backup.backup_service.BackupService",
                mock_service_cls,
            ),
            patch(
                "local_deep_research.database.alembic_runner.needs_migration",
                return_value=True,
            ),
        ):
            # Simulate what encrypted_db.py does at lines 533-554
            from local_deep_research.database.alembic_runner import (
                needs_migration,
            )

            engine = MagicMock()
            if needs_migration(engine):
                from local_deep_research.database.backup.backup_service import (
                    BackupService,
                )

                BackupService(
                    username="testuser", password="testpw"
                ).create_backup()

        mock_service_cls.assert_called_once_with(
            username="testuser", password="testpw"
        )
        mock_service_instance.create_backup.assert_called_once()

    def test_no_backup_when_no_migration_needed(self):
        """BackupService is not instantiated when needs_migration returns False."""
        mock_service_cls = MagicMock()

        with (
            patch(
                "local_deep_research.database.backup.backup_service.BackupService",
                mock_service_cls,
            ),
            patch(
                "local_deep_research.database.alembic_runner.needs_migration",
                return_value=False,
            ),
        ):
            from local_deep_research.database.alembic_runner import (
                needs_migration,
            )

            engine = MagicMock()
            if needs_migration(engine):
                from local_deep_research.database.backup.backup_service import (
                    BackupService,
                )

                BackupService(
                    username="testuser", password="testpw"
                ).create_backup()

        mock_service_cls.assert_not_called()

    def test_migration_proceeds_when_backup_raises(self):
        """Migration must not be blocked by backup failure."""
        mock_service_cls = MagicMock(side_effect=RuntimeError("disk full"))
        mock_init_db = MagicMock()

        with (
            patch(
                "local_deep_research.database.backup.backup_service.BackupService",
                mock_service_cls,
            ),
            patch(
                "local_deep_research.database.alembic_runner.needs_migration",
                return_value=True,
            ),
        ):
            from local_deep_research.database.alembic_runner import (
                needs_migration,
            )

            engine = MagicMock()
            migration_ran = False

            if needs_migration(engine):
                try:
                    from local_deep_research.database.backup.backup_service import (
                        BackupService,
                    )

                    BackupService(
                        username="testuser", password="testpw"
                    ).create_backup()
                except Exception:
                    pass  # Backup failure must not block migration

            # Migration proceeds regardless
            mock_init_db(engine)
            migration_ran = True

        assert migration_ran, "Migration was blocked by backup failure"
        mock_init_db.assert_called_once()


class TestDailyBackupLimit:
    """Tests for the one-backup-per-calendar-day limit."""

    def test_skips_when_backup_exists_for_today(self, tmp_path):
        """create_backup(force=False) skips if a backup exists for today."""
        backup_dir = tmp_path / "backups"
        backup_dir.mkdir()

        # Create a fake backup file with today's date
        from datetime import UTC, datetime

        today = datetime.now(UTC).strftime("%Y%m%d")
        existing = backup_dir / f"ldr_backup_{today}_120000.db"
        existing.write_bytes(b"existing backup data")

        with (
            patch(
                "local_deep_research.database.backup.backup_service"
                ".get_encrypted_database_path",
                return_value=tmp_path,
            ),
            patch(
                "local_deep_research.database.backup.backup_service"
                ".get_user_database_filename",
                return_value="test.db",
            ),
            patch(
                "local_deep_research.database.backup.backup_service"
                ".get_user_backup_directory",
                return_value=backup_dir,
            ),
        ):
            svc = BackupService(username="daily", password="pw")
            result = svc.create_backup(force=False)

        # Should succeed without creating a new file
        assert result.success is True
        assert result.backup_path == existing
        # Only the original file exists — no new backup created
        all_backups = list(backup_dir.glob(f"ldr_backup_{today}_*.db"))
        assert len(all_backups) == 1

    def test_force_bypasses_daily_limit(self, tmp_path):
        """create_backup(force=True) creates a backup even if one exists today."""
        backup_dir = tmp_path / "backups"
        backup_dir.mkdir()

        from datetime import UTC, datetime

        today = datetime.now(UTC).strftime("%Y%m%d")
        existing = backup_dir / f"ldr_backup_{today}_120000.db"
        existing.write_bytes(b"existing backup data")

        # Create a fake source DB so _create_backup_impl doesn't fail early
        source_db = tmp_path / "test.db"
        source_db.write_bytes(b"x" * 1000)

        with (
            patch(
                "local_deep_research.database.backup.backup_service"
                ".get_encrypted_database_path",
                return_value=tmp_path,
            ),
            patch(
                "local_deep_research.database.backup.backup_service"
                ".get_user_database_filename",
                return_value="test.db",
            ),
            patch(
                "local_deep_research.database.backup.backup_service"
                ".get_user_backup_directory",
                return_value=backup_dir,
            ),
            patch(
                "local_deep_research.database.backup.backup_service"
                ".create_sqlcipher_connection",
            ) as mock_conn,
            patch(
                "local_deep_research.database.backup.backup_service"
                ".get_key_from_password",
                return_value=b"\x00" * 64,
            ),
            patch(
                "local_deep_research.database.backup.backup_service"
                ".get_sqlcipher_settings",
                return_value={
                    "page_size": 4096,
                    "hmac_algorithm": "HMAC_SHA512",
                    "kdf_iterations": 256000,
                },
            ),
        ):
            # Mock the cursor to simulate successful export
            mock_cursor = MagicMock()
            mock_conn.return_value.cursor.return_value = mock_cursor

            def create_backup_file(*args):
                sql = args[0] if args else ""
                if "ATTACH DATABASE" in str(sql):
                    import re

                    match = re.search(r"ATTACH DATABASE '([^']+)'", str(sql))
                    if match:
                        from pathlib import Path

                        Path(match.group(1)).write_bytes(b"backup" * 100)

            mock_cursor.execute.side_effect = create_backup_file

            svc = BackupService(username="daily", password="pw")

            # Patch _verify_backup to return True
            with patch.object(svc, "_verify_backup", return_value=True):
                svc.create_backup(force=True)

        # force=True should attempt to create a new backup
        # (it may succeed or fail depending on mocks, but it should NOT skip)
        mock_conn.assert_called()  # Proves _create_backup_impl was entered

    def test_proceeds_normally_for_different_day(self, tmp_path):
        """create_backup proceeds if only yesterday's backup exists."""
        backup_dir = tmp_path / "backups"
        backup_dir.mkdir()

        # Create a backup with yesterday's date
        yesterday = backup_dir / "ldr_backup_20200101_120000.db"
        yesterday.write_bytes(b"old backup")

        source_db = tmp_path / "test.db"
        source_db.write_bytes(b"x" * 1000)

        with (
            patch(
                "local_deep_research.database.backup.backup_service"
                ".get_encrypted_database_path",
                return_value=tmp_path,
            ),
            patch(
                "local_deep_research.database.backup.backup_service"
                ".get_user_database_filename",
                return_value="test.db",
            ),
            patch(
                "local_deep_research.database.backup.backup_service"
                ".get_user_backup_directory",
                return_value=backup_dir,
            ),
            patch(
                "local_deep_research.database.backup.backup_service"
                ".create_sqlcipher_connection",
            ) as mock_conn,
        ):
            svc = BackupService(username="daily", password="pw")

            # Should NOT skip — yesterday's backup doesn't count
            # _create_backup_impl will be entered (even if it fails due to mocks)
            svc.create_backup(force=False)

        # Proves it didn't skip — it tried to create a connection
        mock_conn.assert_called()


class TestIsSafeGlobResult:
    """Unit tests for the ``is_safe_glob_result`` glob-hardening helper.

    This helper backs the symlink / path-traversal filtering that is applied
    to every backup glob site. These tests exercise the helper directly,
    rather than only asserting pathlib behavior on fixtures.
    """

    def test_accepts_regular_child_file(self, tmp_path):
        """A regular (non-symlink) file inside base_dir is accepted."""
        from local_deep_research.database.backup.backup_service import (
            is_safe_glob_result,
        )

        base_dir = tmp_path / "backups"
        base_dir.mkdir()
        backup = base_dir / "ldr_backup_20250101_120000.db"
        backup.write_bytes(b"data")

        assert is_safe_glob_result(backup, base_dir) is True

    def test_rejects_symlink_pointing_outside(self, tmp_path):
        """A symlink that escapes base_dir is rejected."""
        from local_deep_research.database.backup.backup_service import (
            is_safe_glob_result,
        )

        base_dir = tmp_path / "backups"
        base_dir.mkdir()
        outside = tmp_path / "outside.db"
        outside.write_bytes(b"secret")

        link = base_dir / "ldr_backup_evil.db"
        link.symlink_to(outside)

        assert is_safe_glob_result(link, base_dir) is False

    def test_rejects_symlink_even_when_target_inside(self, tmp_path):
        """Symlinks are rejected outright, even pointing back inside base_dir.

        ``is_symlink()`` is checked first, so the policy is "no symlinks at
        all" — this documents that intentional behavior.
        """
        from local_deep_research.database.backup.backup_service import (
            is_safe_glob_result,
        )

        base_dir = tmp_path / "backups"
        base_dir.mkdir()
        real = base_dir / "ldr_backup_real.db"
        real.write_bytes(b"data")

        link = base_dir / "ldr_backup_link.db"
        link.symlink_to(real)

        assert is_safe_glob_result(link, base_dir) is False

    def test_rejects_path_resolving_outside_base(self, tmp_path):
        """A non-symlink path that resolves outside base_dir is rejected."""
        from local_deep_research.database.backup.backup_service import (
            is_safe_glob_result,
        )

        base_dir = tmp_path / "backups"
        base_dir.mkdir()
        other_dir = tmp_path / "elsewhere"
        other_dir.mkdir()
        stray = other_dir / "ldr_backup_20250101_120000.db"
        stray.write_bytes(b"data")

        assert is_safe_glob_result(stray, base_dir) is False

    def test_accepts_real_file_through_symlinked_base_dir(self, tmp_path):
        """Real files are kept even when base_dir is reached via a symlink.

        Regression guard: both sides are resolved, so a symlinked base
        directory (e.g. macOS ``/tmp`` -> ``/private/tmp``, or a symlinked
        ``$HOME``) must NOT cause legitimate backups to be silently dropped.
        """
        from local_deep_research.database.backup.backup_service import (
            is_safe_glob_result,
        )

        real_base = tmp_path / "real_backups"
        real_base.mkdir()
        backup = real_base / "ldr_backup_20250101_120000.db"
        backup.write_bytes(b"data")

        symlinked_base = tmp_path / "linked_backups"
        symlinked_base.symlink_to(real_base)

        # The file reached through the symlinked parent is itself a real file.
        via_link = symlinked_base / "ldr_backup_20250101_120000.db"
        assert not via_link.is_symlink()
        assert is_safe_glob_result(via_link, symlinked_base) is True


class TestGlobHardeningIntegration:
    """End-to-end checks that the glob hardening is wired into the service."""

    @patch(
        "local_deep_research.database.backup.backup_service.get_encrypted_database_path"
    )
    @patch(
        "local_deep_research.database.backup.backup_service.get_user_backup_directory"
    )
    @patch(
        "local_deep_research.database.backup.backup_service.get_user_database_filename"
    )
    def test_list_backups_excludes_symlinks(
        self, mock_db_filename, mock_backup_dir, mock_db_path, tmp_path
    ):
        """list_backups() must skip symlinked entries even if names match."""
        backup_dir = tmp_path / "backups"
        backup_dir.mkdir()

        # One legitimate backup.
        real_backup = backup_dir / "ldr_backup_20250101_120000.db"
        real_backup.write_bytes(b"backup-data")

        # A malicious symlink whose name matches the glob but points outside.
        outside = tmp_path / "outside_secret.db"
        outside.write_bytes(b"secret")
        evil_link = backup_dir / "ldr_backup_20250102_120000.db"
        evil_link.symlink_to(outside)

        mock_db_filename.return_value = "ldr_user_abc123.db"
        mock_db_path.return_value = tmp_path
        mock_backup_dir.return_value = backup_dir

        service = BackupService(username="testuser", password="testpass")
        backups = service.list_backups()

        names = [b["filename"] for b in backups]
        assert names == ["ldr_backup_20250101_120000.db"]
        assert "ldr_backup_20250102_120000.db" not in names
        # Listing must not have followed or removed the symlink.
        assert evil_link.is_symlink()

    @patch(
        "local_deep_research.database.backup.backup_service.get_encrypted_database_path"
    )
    @patch(
        "local_deep_research.database.backup.backup_service.get_user_backup_directory"
    )
    @patch(
        "local_deep_research.database.backup.backup_service.get_user_database_filename"
    )
    def test_list_backups_path_field_is_filename_only(
        self, mock_db_filename, mock_backup_dir, mock_db_path, tmp_path
    ):
        """The "path" field must be a bare filename, not the server path.

        purge_and_refresh() reconstructs the absolute path via
        ``self.backup_dir / info["path"]`` and relies on this invariant
        (``Path(base) / absolute`` would silently discard ``base``).
        """
        backup_dir = tmp_path / "backups"
        backup_dir.mkdir()
        backup = backup_dir / "ldr_backup_20250101_120000.db"
        backup.write_bytes(b"backup-data")

        mock_db_filename.return_value = "ldr_user_abc123.db"
        mock_db_path.return_value = tmp_path
        mock_backup_dir.return_value = backup_dir

        service = BackupService(username="testuser", password="testpass")
        backups = service.list_backups()

        assert len(backups) == 1
        info = backups[0]
        assert info["path"] == "ldr_backup_20250101_120000.db"
        # It must NOT leak the absolute server path, and must round-trip.
        assert os.sep not in info["path"]
        assert (service.backup_dir / info["path"]) == backup


class TestUnsafeBackupPathChars:
    """Tests for the widened SQL-injection character guard on the ATTACH path."""

    @pytest.mark.parametrize(
        "bad_char, label",
        [
            ("\\", "backslash"),
            ("\0", "null"),
            ("\n", "newline"),
            ("\r", "carriage_return"),
            ("\t", "tab"),
        ],
    )
    @patch(
        "local_deep_research.database.backup.backup_service.get_encrypted_database_path"
    )
    @patch(
        "local_deep_research.database.backup.backup_service.get_user_backup_directory"
    )
    @patch(
        "local_deep_research.database.backup.backup_service.get_user_database_filename"
    )
    @patch(
        "local_deep_research.database.backup.backup_service.create_sqlcipher_connection"
    )
    @patch("shutil.disk_usage")
    def test_backup_path_with_unsafe_char_rejected(
        self,
        mock_disk_usage,
        mock_create_conn,
        mock_db_filename,
        mock_backup_dir,
        mock_db_path,
        bad_char,
        label,
        tmp_path,
    ):
        """Reject backslash, NUL, CR, LF and tab in the backup path.

        ``'`` is intentionally NOT rejected — it is escaped (doubled) in the
        ATTACH literal so apostrophe data dirs work (#4808). ``temp_path`` is
        server-generated, so the only way these reach the ``ATTACH DATABASE``
        literal is via the backup directory, which is simulated here.
        """
        db_dir = tmp_path / "encrypted_databases"
        db_dir.mkdir()
        db_file = db_dir / "ldr_user_abc123.db"
        db_file.write_bytes(b"x" * 1000)

        mock_db_filename.return_value = "ldr_user_abc123.db"
        mock_db_path.return_value = db_dir
        # Backup dir whose path string contains an unsafe character. It is
        # never created on disk (disk_usage is mocked); only the string of the
        # generated temp path matters to the guard.
        mock_backup_dir.return_value = tmp_path / f"back{bad_char}ups"

        mock_disk_usage.return_value = MagicMock(free=10_000_000)
        mock_conn = MagicMock()
        mock_conn.cursor.return_value = MagicMock()
        mock_create_conn.return_value = mock_conn

        service = BackupService(username="testuser", password="testpass")
        # force=True skips the daily-glob check and goes straight to the guard.
        result = service.create_backup(force=True)

        assert result.success is False
        assert "not allowed in a SQLCipher ATTACH" in result.error
        # The ATTACH statement must never execute for an unsafe path.
        executed = [
            str(call)
            for call in mock_conn.cursor.return_value.execute.call_args_list
        ]
        assert not any("ATTACH DATABASE" in c for c in executed)

    def test_single_quote_no_longer_in_denylist(self):
        """#4808: the apostrophe is escaped (doubled) in the ATTACH literal,
        not rejected, so /home/O'Brien/... can be backed up. The genuinely
        dangerous characters stay denied. (Runs without SQLCipher.)"""
        assert "'" not in _UNSAFE_BACKUP_PATH_CHARS
        for ch in ('"', "\\", "\0", "\n", "\r", "\t"):
            assert ch in _UNSAFE_BACKUP_PATH_CHARS