"""
Docker-based end-to-end backup crash recovery test.

This test runs in CI (release gate) inside the LDR Docker container
with SQLCipher available. It exercises the full lifecycle in 10 steps:

1. Create encrypted DB with real schema + data
2. Backup via BackupService
3. Verify backup is encrypted (no plaintext SQLite header)
4. Destroy the original DB (simulate crash)
5. Copy backup to replace original
6. Open with same password, verify data intact
7. Simulate password change (rekey)
8. Verify old password fails on rekeyed DB
9. Purge old backups + create fresh one with new key
10. Verify fresh backup works with new password, old backup gone

All files are created in tmp_path — nothing is uploaded or committed.
Requires SQLCipher (skips gracefully if unavailable).
"""

import os
import shutil
from pathlib import Path
from unittest.mock import patch

import pytest

from local_deep_research.database.backup.backup_service import (
    BackupService,
)


def _get_sqlcipher():
    """Import SQLCipher or skip the test."""
    try:
        from local_deep_research.database.sqlcipher_compat import (
            get_sqlcipher_module,
        )

        return get_sqlcipher_module()
    except (ImportError, RuntimeError):
        pytest.skip("SQLCipher not available")


def _create_test_database(db_path: Path, password: str):
    """Create a real encrypted SQLCipher database with LDR-like schema.

    Uses create_sqlcipher_connection() for correct pragma ordering:
    apply_cipher_defaults_before_key → set_sqlcipher_key → apply_sqlcipher_pragmas
    """
    from local_deep_research.database.sqlcipher_utils import (
        create_sqlcipher_connection,
    )

    conn = create_sqlcipher_connection(
        str(db_path), password, creation_mode=True
    )
    cursor = conn.cursor()

    # Create tables mimicking real LDR schema
    cursor.execute(
        """CREATE TABLE research_history (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            title TEXT NOT NULL,
            query TEXT,
            status TEXT DEFAULT 'completed',
            created_at TEXT DEFAULT (datetime('now'))
        )"""
    )
    cursor.execute(
        """CREATE TABLE app_settings (
            key TEXT PRIMARY KEY,
            value TEXT,
            type TEXT DEFAULT 'APP'
        )"""
    )
    cursor.execute(
        """CREATE TABLE documents (
            id TEXT PRIMARY KEY,
            research_id INTEGER,
            file_name TEXT,
            FOREIGN KEY (research_id) REFERENCES research_history(id)
        )"""
    )

    # Insert test data
    for i in range(20):
        cursor.execute(
            "INSERT INTO research_history (title, query) VALUES (?, ?)",
            (f"Research on Topic {i}", f"What is topic {i}?"),
        )
    cursor.execute(
        "INSERT INTO app_settings (key, value) VALUES (?, ?)",
        ("llm.model", "gpt-4o-mini"),
    )
    cursor.execute(
        "INSERT INTO app_settings (key, value) VALUES (?, ?)",
        ("backup.enabled", "true"),
    )
    for i in range(5):
        cursor.execute(
            "INSERT INTO documents (id, research_id, file_name) VALUES (?, ?, ?)",
            (f"doc-{i}", i + 1, f"paper_{i}.pdf"),
        )

    conn.commit()
    cursor.close()
    conn.close()


def _open_and_verify(
    db_path: Path, password: str, expected_research_count: int = 20
):
    """Open a database with create_sqlcipher_connection and verify contents."""
    from local_deep_research.database.sqlcipher_utils import (
        create_sqlcipher_connection,
    )

    conn = create_sqlcipher_connection(str(db_path), password)
    cursor = conn.cursor()

    # Integrity check
    cursor.execute("PRAGMA integrity_check")
    assert cursor.fetchone()[0] == "ok"

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

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

    # Verify documents
    cursor.execute("SELECT COUNT(*) FROM documents")
    doc_count = cursor.fetchone()[0]
    assert doc_count == 5

    cursor.close()
    conn.close()
    return True


@pytest.mark.timeout(120)
class TestFullCrashRecoveryCycle:
    """Full end-to-end crash recovery cycle with password change.

    Exercises the complete backup lifecycle in a single sequential flow,
    as it would happen in production. Requires SQLCipher — skips in
    environments without it.
    """

    def test_full_backup_restore_password_change_cycle(self, tmp_path):
        """Complete lifecycle: backup → crash → restore → password change → verify."""
        _get_sqlcipher()  # Skip early if no SQLCipher

        from local_deep_research.database.sqlcipher_utils import (
            create_sqlcipher_connection,
            set_sqlcipher_rekey,
        )

        db_dir = tmp_path / "encrypted_databases"
        db_dir.mkdir()
        db_path = db_dir / "ldr_user_crashtest.db"
        backup_dir = tmp_path / "backups"
        backup_dir.mkdir()
        original_pw = "original_password_789"
        new_pw = "changed_password_456"

        # ── Step 1: Create database with real data ──
        _create_test_database(db_path, original_pw)
        _open_and_verify(db_path, original_pw)

        # ── Step 2: Create backup ──
        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="crashtest",
                password=original_pw,
                max_backups=3,
                max_age_days=7,
            )
            backup_result = svc.create_backup()

        assert backup_result.success, f"Backup failed: {backup_result.error}"
        assert backup_result.backup_path.exists()
        assert backup_result.backup_path.stat().st_size > 0

        # ── Step 3: Verify backup is encrypted (not plaintext SQLite) ──
        header = backup_result.backup_path.read_bytes()[:16]
        assert header != b"SQLite format 3\x00", (
            "Backup has plaintext SQLite header — encryption failed!"
        )

        # ── Step 4: Simulate crash — destroy original database ──
        db_path.unlink()
        for suffix in ["-wal", "-shm"]:
            wal_path = db_path.parent / (db_path.name + suffix)
            if wal_path.exists():
                wal_path.unlink()
        assert not db_path.exists()

        # ── Step 5: Restore from backup — copy to original path ──
        shutil.copy2(str(backup_result.backup_path), str(db_path))
        os.chmod(db_path, 0o600)

        # ── Step 6: Verify restored database works ──
        _open_and_verify(db_path, original_pw)

        # ── Step 7: Simulate password change (rekey) ──
        conn = create_sqlcipher_connection(str(db_path), original_pw)
        set_sqlcipher_rekey(conn, new_pw, db_path=db_path)
        conn.close()

        # Verify new password works
        _open_and_verify(db_path, new_pw)

        # ── Step 8: Verify old password fails on rekeyed DB ──
        with pytest.raises(Exception):
            create_sqlcipher_connection(str(db_path), original_pw)

        # ── Step 9: Purge old backups and create fresh one ──
        old_backup_path = backup_result.backup_path
        assert old_backup_path.exists()

        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,
            ),
        ):
            new_svc = BackupService(
                username="crashtest",
                password=new_pw,
                max_backups=3,
                max_age_days=7,
            )
            refresh_result = new_svc.purge_and_refresh()

        assert refresh_result.success, f"Refresh failed: {refresh_result.error}"
        assert not old_backup_path.exists(), "Old-key backup was not purged"
        assert refresh_result.backup_path.exists()

        # ── Step 10: Verify fresh backup ──
        # Works with new password
        _open_and_verify(refresh_result.backup_path, new_pw)

        # Is encrypted (not plaintext)
        fresh_header = refresh_result.backup_path.read_bytes()[:16]
        assert fresh_header != b"SQLite format 3\x00", (
            "Fresh backup has plaintext SQLite header!"
        )

        # Does NOT work with old password
        with pytest.raises(Exception):
            create_sqlcipher_connection(
                str(refresh_result.backup_path), original_pw
            )

        # Only 1 backup exists (the fresh one)
        backups = list(backup_dir.glob("ldr_backup_*.db"))
        assert len(backups) == 1, f"Expected 1 backup, found {len(backups)}"


@pytest.mark.timeout(120)
class TestBackupDataIntegrity:
    """Tests that verify backup data integrity properties.

    These tests require real SQLCipher and validate that sqlcipher_export()
    preserves schema, foreign keys, and produces a fully functional database.
    """

    def test_backup_preserves_all_schema_objects(self, tmp_path):
        """Backup must contain all tables, indexes, and triggers from source."""
        _get_sqlcipher()
        from local_deep_research.database.sqlcipher_utils import (
            create_sqlcipher_connection,
        )

        password = "schema-test-pw"
        db_dir = tmp_path / "db"
        db_dir.mkdir()
        db_path = db_dir / "ldr_user_schematest.db"
        backup_dir = tmp_path / "backups"
        backup_dir.mkdir()

        # Create DB with tables, explicit index, and trigger
        conn = create_sqlcipher_connection(
            str(db_path), password, creation_mode=True
        )
        cursor = conn.cursor()
        cursor.execute(
            """CREATE TABLE research_history (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                title TEXT NOT NULL,
                query TEXT
            )"""
        )
        cursor.execute(
            """CREATE TABLE documents (
                id TEXT PRIMARY KEY,
                research_id INTEGER,
                file_name TEXT,
                FOREIGN KEY (research_id) REFERENCES research_history(id)
            )"""
        )
        cursor.execute(
            "CREATE INDEX idx_docs_research ON documents(research_id)"
        )
        cursor.execute("INSERT INTO research_history (title) VALUES ('Test')")
        conn.commit()
        cursor.close()
        conn.close()

        # Get source schema
        conn = create_sqlcipher_connection(str(db_path), password)
        cursor = conn.cursor()
        cursor.execute(
            "SELECT type, name, sql FROM sqlite_master "
            "WHERE sql IS NOT NULL ORDER BY type, name"
        )
        source_schema = cursor.fetchall()
        cursor.close()
        conn.close()

        # Create backup
        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="schematest", password=password, max_backups=3
            )
            result = svc.create_backup()

        assert result.success, f"Backup failed: {result.error}"

        # Compare schemas
        conn = create_sqlcipher_connection(str(result.backup_path), password)
        cursor = conn.cursor()
        cursor.execute(
            "SELECT type, name, sql FROM sqlite_master "
            "WHERE sql IS NOT NULL ORDER BY type, name"
        )
        backup_schema = cursor.fetchall()
        cursor.close()
        conn.close()

        assert source_schema == backup_schema, (
            f"Schema mismatch!\nSource: {source_schema}\nBackup: {backup_schema}"
        )

    def test_backup_passes_foreign_key_check(self, tmp_path):
        """Backup must have zero foreign key violations."""
        _get_sqlcipher()
        from local_deep_research.database.sqlcipher_utils import (
            create_sqlcipher_connection,
        )

        password = "fk-test-pw"
        db_dir = tmp_path / "db"
        db_dir.mkdir()
        db_path = db_dir / "ldr_user_fktest.db"
        backup_dir = tmp_path / "backups"
        backup_dir.mkdir()

        _create_test_database(db_path, 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="fktest", password=password, max_backups=3
            )
            result = svc.create_backup()

        assert result.success, f"Backup failed: {result.error}"

        conn = create_sqlcipher_connection(str(result.backup_path), password)
        cursor = conn.cursor()
        cursor.execute("PRAGMA foreign_key_check")
        violations = cursor.fetchall()
        cursor.close()
        conn.close()

        assert violations == [], f"FK violations in backup: {violations}"

    def test_restored_backup_accepts_new_writes(self, tmp_path):
        """A restored backup must be a fully writable, functional database."""
        _get_sqlcipher()
        from local_deep_research.database.sqlcipher_utils import (
            create_sqlcipher_connection,
        )

        password = "write-test-pw"
        db_dir = tmp_path / "db"
        db_dir.mkdir()
        db_path = db_dir / "ldr_user_writetest.db"
        backup_dir = tmp_path / "backups"
        backup_dir.mkdir()

        _create_test_database(db_path, 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="writetest", password=password, max_backups=3
            )
            result = svc.create_backup()

        assert result.success, f"Backup failed: {result.error}"

        # Restore: copy backup over original
        restored_path = tmp_path / "restored.db"
        shutil.copy2(str(result.backup_path), str(restored_path))

        # Copy salt file too (needed for key derivation)
        salt_src = Path(str(db_path) + ".salt")
        if salt_src.exists():
            shutil.copy2(str(salt_src), str(restored_path) + ".salt")

        # Open restored DB and write new data
        conn = create_sqlcipher_connection(str(restored_path), password)
        cursor = conn.cursor()

        cursor.execute(
            "INSERT INTO research_history (title, query) VALUES (?, ?)",
            ("New after restore", "Does restore work?"),
        )
        cursor.execute(
            "UPDATE research_history SET title = ? WHERE id = 1",
            ("Modified after restore",),
        )
        conn.commit()

        # Verify writes persisted
        cursor.execute("SELECT COUNT(*) FROM research_history")
        assert cursor.fetchone()[0] == 21  # 20 original + 1 new

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

        cursor.close()
        conn.close()

        # Reopen to verify durability
        conn = create_sqlcipher_connection(str(restored_path), password)
        cursor = conn.cursor()
        cursor.execute("SELECT COUNT(*) FROM research_history")
        assert cursor.fetchone()[0] == 21
        cursor.close()
        conn.close()


class TestBackupPathWithApostrophe:
    """Regression for #4808: a backup/data-dir path containing an apostrophe
    (e.g. /home/O'Brien) must back up successfully. ATTACH DATABASE can't be
    parameterized, so the path literal escapes the single quote (doubling it)
    instead of the denylist rejecting the path."""

    def test_backup_succeeds_with_apostrophe_in_path(self, tmp_path):
        _get_sqlcipher()  # skip early if SQLCipher is unavailable

        db_dir = tmp_path / "encrypted_databases"
        db_dir.mkdir()
        db_path = db_dir / "ldr_user_apos.db"
        # The offending component: a directory whose name has a single quote.
        backup_dir = tmp_path / "O'Brien" / "backups"
        backup_dir.mkdir(parents=True)
        password = "apostrophe_password_123"

        _create_test_database(db_path, 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="apostuser",
                password=password,
                max_backups=3,
                max_age_days=7,
            )
            result = svc.create_backup()

        assert result.success, f"Backup failed: {result.error}"
        assert "'" in str(result.backup_path)  # the apostrophe path was used
        assert result.backup_path.exists()
        assert result.backup_path.stat().st_size > 0
        # Encrypted, not a plaintext SQLite file
        assert result.backup_path.read_bytes()[:16] != b"SQLite format 3\x00"
