"""Tests for SQLite job state persistence."""

from __future__ import annotations

import sqlite3
import tempfile
from datetime import datetime
from pathlib import Path

import pytest

from src.orchestration.models import JobStatus
from src.orchestration.state import JobStateStore


@pytest.fixture
def temp_db() -> Path:
    """Create a temporary database path."""
    with tempfile.TemporaryDirectory() as tmpdir:
        yield Path(tmpdir) / "jobs" / "test.db"


@pytest.fixture
def state_store(temp_db: Path) -> JobStateStore:
    """Create a JobStateStore with a temporary database."""
    return JobStateStore(temp_db)


class TestJobStateStoreInit:
    """Tests for JobStateStore initialization."""

    def test_creates_directory(self, temp_db: Path) -> None:
        """Test that state store creates parent directories."""
        store = JobStateStore(temp_db)
        assert temp_db.parent.exists()

    def test_creates_tables(self, temp_db: Path) -> None:
        """Test that state store creates required tables."""
        store = JobStateStore(temp_db)

        # Check tables exist
        conn = sqlite3.connect(str(temp_db))
        cursor = conn.execute(
            "SELECT name FROM sqlite_master WHERE type='table'"
        )
        tables = {row[0] for row in cursor.fetchall()}
        conn.close()

        assert "bible_jobs" in tables
        assert "book_jobs" in tables
        assert "verse_jobs" in tables

    def test_idempotent_init(self, temp_db: Path) -> None:
        """Test that multiple initializations don't fail."""
        store1 = JobStateStore(temp_db)
        store2 = JobStateStore(temp_db)  # Should not raise


class TestBibleJobManagement:
    """Tests for Bible job management."""

    def test_create_bible_job(self, state_store: JobStateStore) -> None:
        """Test creating a Bible job."""
        job = state_store.create_bible_job(
            books=["john", "matthew"],
            config={"model": "test"},
        )

        assert job.job_id is not None
        assert job.books == ["john", "matthew"]
        assert job.status == JobStatus.PENDING
        assert job.config_snapshot == {"model": "test"}

    def test_create_job_with_specific_id(self, state_store: JobStateStore) -> None:
        """Test creating a job with a specific ID."""
        job = state_store.create_bible_job(
            books=["john"],
            config={},
            job_id="my-custom-id",
        )
        assert job.job_id == "my-custom-id"

    def test_get_bible_job(self, state_store: JobStateStore) -> None:
        """Test getting a Bible job by ID."""
        created = state_store.create_bible_job(books=["john"], config={})
        retrieved = state_store.get_bible_job(created.job_id)

        assert retrieved is not None
        assert retrieved.job_id == created.job_id
        assert retrieved.books == ["john"]

    def test_get_nonexistent_job(self, state_store: JobStateStore) -> None:
        """Test getting a non-existent job returns None."""
        result = state_store.get_bible_job("nonexistent-id")
        assert result is None

    def test_get_latest_incomplete_job(self, state_store: JobStateStore) -> None:
        """Test getting the latest incomplete job."""
        # Create a completed job
        job1 = state_store.create_bible_job(books=["john"], config={})
        state_store.update_job_status(job1.job_id, JobStatus.COMPLETED)

        # Create an incomplete job
        job2 = state_store.create_bible_job(books=["matthew"], config={})

        latest = state_store.get_latest_incomplete_job()
        assert latest is not None
        assert latest.job_id == job2.job_id

    def test_get_latest_incomplete_no_jobs(self, state_store: JobStateStore) -> None:
        """Test getting latest incomplete when no jobs exist."""
        result = state_store.get_latest_incomplete_job()
        assert result is None

    def test_update_job_status(self, state_store: JobStateStore) -> None:
        """Test updating job status."""
        job = state_store.create_bible_job(books=["john"], config={})

        started = datetime.utcnow()
        state_store.update_job_status(
            job.job_id,
            JobStatus.IN_PROGRESS,
            started_at=started,
        )

        updated = state_store.get_bible_job(job.job_id)
        assert updated is not None
        assert updated.status == JobStatus.IN_PROGRESS
        assert updated.started_at is not None

    def test_update_job_verse_counts(self, state_store: JobStateStore) -> None:
        """Test updating job verse counts."""
        job = state_store.create_bible_job(books=["john"], config={})

        state_store.update_job_verse_counts(
            job.job_id,
            total=100,
            completed=50,
            failed=5,
        )

        updated = state_store.get_bible_job(job.job_id)
        assert updated is not None
        assert updated.total_verses == 100
        assert updated.completed_verses == 50
        assert updated.failed_verses == 5


class TestBookJobManagement:
    """Tests for book job management."""

    def test_book_jobs_created_with_bible_job(
        self, state_store: JobStateStore
    ) -> None:
        """Test that book jobs are created when Bible job is created."""
        job = state_store.create_bible_job(
            books=["john", "matthew", "luke"],
            config={},
        )

        book_jobs = state_store.get_book_jobs(job.job_id)
        assert len(book_jobs) == 3
        book_names = [bj.book for bj in book_jobs]
        assert "john" in book_names
        assert "matthew" in book_names
        assert "luke" in book_names

    def test_get_book_job(self, state_store: JobStateStore) -> None:
        """Test getting a specific book job."""
        job = state_store.create_bible_job(books=["john"], config={})
        book_job = state_store.get_book_job(job.job_id, "john")

        assert book_job is not None
        assert book_job.book == "john"
        assert book_job.status == JobStatus.PENDING

    def test_get_nonexistent_book_job(self, state_store: JobStateStore) -> None:
        """Test getting a non-existent book job."""
        job = state_store.create_bible_job(books=["john"], config={})
        result = state_store.get_book_job(job.job_id, "matthew")
        assert result is None

    def test_update_book_status(self, state_store: JobStateStore) -> None:
        """Test updating book status."""
        job = state_store.create_bible_job(books=["john"], config={})

        state_store.update_book_status(
            job.job_id,
            "john",
            JobStatus.IN_PROGRESS,
            worker_pid=12345,
            started_at=datetime.utcnow(),
        )

        book_job = state_store.get_book_job(job.job_id, "john")
        assert book_job is not None
        assert book_job.status == JobStatus.IN_PROGRESS
        assert book_job.worker_pid == 12345

    def test_update_book_verse_counts(self, state_store: JobStateStore) -> None:
        """Test updating book verse counts."""
        job = state_store.create_bible_job(books=["john"], config={})

        state_store.update_book_verse_counts(
            job.job_id,
            "john",
            total=879,
            completed=100,
            failed=2,
        )

        book_job = state_store.get_book_job(job.job_id, "john")
        assert book_job is not None
        assert book_job.total_verses == 879
        assert book_job.completed_verses == 100
        assert book_job.failed_verses == 2


class TestVerseJobManagement:
    """Tests for verse job management."""

    def test_initialize_verse_jobs(self, state_store: JobStateStore) -> None:
        """Test initializing verse jobs for a book."""
        job = state_store.create_bible_job(books=["john"], config={})

        verses = [(1, 1), (1, 2), (1, 3), (2, 1)]
        state_store.initialize_verse_jobs(job.job_id, "john", verses)

        pending = state_store.get_pending_verses(job.job_id, "john")
        assert len(pending) == 4

    def test_atomic_claim_verse_success(self, state_store: JobStateStore) -> None:
        """Test successfully claiming a verse."""
        job = state_store.create_bible_job(books=["john"], config={})
        state_store.initialize_verse_jobs(job.job_id, "john", [(1, 1)])

        claimed = state_store.atomic_claim_verse(
            job.job_id, "john", 1, 1, worker_pid=12345
        )
        assert claimed is True

        # Verify verse is now in_progress
        pending = state_store.get_pending_verses(job.job_id, "john")
        assert len(pending) == 0

    def test_atomic_claim_verse_already_claimed(
        self, state_store: JobStateStore
    ) -> None:
        """Test that claiming an already claimed verse fails."""
        job = state_store.create_bible_job(books=["john"], config={})
        state_store.initialize_verse_jobs(job.job_id, "john", [(1, 1)])

        # First claim succeeds
        claim1 = state_store.atomic_claim_verse(
            job.job_id, "john", 1, 1, worker_pid=12345
        )
        assert claim1 is True

        # Second claim fails
        claim2 = state_store.atomic_claim_verse(
            job.job_id, "john", 1, 1, worker_pid=54321
        )
        assert claim2 is False

    def test_update_verse_status_completed(
        self, state_store: JobStateStore
    ) -> None:
        """Test marking a verse as completed."""
        job = state_store.create_bible_job(books=["john"], config={})
        state_store.initialize_verse_jobs(job.job_id, "john", [(1, 1)])
        state_store.atomic_claim_verse(job.job_id, "john", 1, 1, 12345)

        state_store.update_verse_status(
            job.job_id, "john", 1, 1, JobStatus.COMPLETED
        )

        # Verify book counts updated
        book_job = state_store.get_book_job(job.job_id, "john")
        assert book_job is not None
        assert book_job.completed_verses == 1

    def test_update_verse_status_failed(self, state_store: JobStateStore) -> None:
        """Test marking a verse as failed."""
        job = state_store.create_bible_job(books=["john"], config={})
        state_store.initialize_verse_jobs(job.job_id, "john", [(1, 1)])
        state_store.atomic_claim_verse(job.job_id, "john", 1, 1, 12345)

        state_store.update_verse_status(
            job.job_id,
            "john",
            1,
            1,
            JobStatus.FAILED,
            error="Test error",
        )

        # Verify book counts updated
        book_job = state_store.get_book_job(job.job_id, "john")
        assert book_job is not None
        assert book_job.failed_verses == 1

        # Verify failed verses can be retrieved
        failed = state_store.get_failed_verses(job.job_id, "john")
        assert len(failed) == 1
        assert failed[0].last_error == "Test error"

    def test_get_book_progress(self, state_store: JobStateStore) -> None:
        """Test getting book progress."""
        job = state_store.create_bible_job(books=["john"], config={})
        state_store.initialize_verse_jobs(
            job.job_id, "john", [(1, 1), (1, 2), (1, 3), (1, 4)]
        )

        # Complete 2 verses, fail 1
        for i in range(3):
            state_store.atomic_claim_verse(job.job_id, "john", 1, i + 1, 12345)

        state_store.update_verse_status(
            job.job_id, "john", 1, 1, JobStatus.COMPLETED
        )
        state_store.update_verse_status(
            job.job_id, "john", 1, 2, JobStatus.COMPLETED
        )
        state_store.update_verse_status(
            job.job_id, "john", 1, 3, JobStatus.FAILED
        )

        progress = state_store.get_book_progress(job.job_id, "john")
        assert progress["total_verses"] == 4
        assert progress["completed_verses"] == 2
        assert progress["failed_verses"] == 1
        assert progress["pending_verses"] == 1
        assert progress["progress_percentage"] == 50.0

    def test_reset_orphaned_verses(self, state_store: JobStateStore) -> None:
        """Test resetting orphaned (in_progress) verses."""
        job = state_store.create_bible_job(books=["john"], config={})
        state_store.initialize_verse_jobs(job.job_id, "john", [(1, 1), (1, 2)])

        # Claim both verses
        state_store.atomic_claim_verse(job.job_id, "john", 1, 1, 12345)
        state_store.atomic_claim_verse(job.job_id, "john", 1, 2, 12345)

        # Reset orphaned
        reset_count = state_store.reset_orphaned_verses(job.job_id)
        assert reset_count == 2

        # Verify verses are pending again
        pending = state_store.get_pending_verses(job.job_id, "john")
        assert len(pending) == 2

    def test_reset_failed_verses(self, state_store: JobStateStore) -> None:
        """Test resetting failed verses for retry."""
        job = state_store.create_bible_job(books=["john"], config={})
        state_store.initialize_verse_jobs(job.job_id, "john", [(1, 1)])
        state_store.atomic_claim_verse(job.job_id, "john", 1, 1, 12345)
        state_store.update_verse_status(
            job.job_id, "john", 1, 1, JobStatus.FAILED, error="Error"
        )

        reset_count = state_store.reset_failed_verses(job.job_id, "john")
        assert reset_count == 1

        # Verify verse is pending again
        pending = state_store.get_pending_verses(job.job_id, "john")
        assert len(pending) == 1

        # Verify book counts updated
        book_job = state_store.get_book_job(job.job_id, "john")
        assert book_job is not None
        assert book_job.failed_verses == 0


class TestConcurrency:
    """Tests for concurrent access safety."""

    def test_wal_mode_enabled(self, temp_db: Path) -> None:
        """Test that WAL mode is enabled for better concurrency."""
        store = JobStateStore(temp_db)

        conn = sqlite3.connect(str(temp_db))
        cursor = conn.execute("PRAGMA journal_mode")
        mode = cursor.fetchone()[0]
        conn.close()

        assert mode.lower() == "wal"


class TestCleanupMethods:
    """Tests for job cleanup methods."""

    def test_delete_job(self, state_store: JobStateStore) -> None:
        """Test deleting a specific job."""
        job = state_store.create_bible_job(books=["john"], config={})
        state_store.initialize_verse_jobs(job.job_id, "john", [(1, 1), (1, 2)])

        result = state_store.delete_job(job.job_id)
        assert result is True

        # Verify job is gone
        assert state_store.get_bible_job(job.job_id) is None
        assert state_store.get_book_job(job.job_id, "john") is None
        assert state_store.get_pending_verses(job.job_id, "john") == []

    def test_delete_nonexistent_job(self, state_store: JobStateStore) -> None:
        """Test deleting a job that doesn't exist."""
        result = state_store.delete_job("nonexistent-id")
        assert result is False

    def test_delete_completed_jobs_keeps_latest(
        self, state_store: JobStateStore
    ) -> None:
        """Test deleting completed jobs while keeping latest."""
        # Create 3 completed jobs
        job1 = state_store.create_bible_job(books=["john"], config={})
        state_store.update_job_status(
            job1.job_id,
            JobStatus.COMPLETED,
            completed_at=datetime(2024, 1, 1),
        )

        job2 = state_store.create_bible_job(books=["matthew"], config={})
        state_store.update_job_status(
            job2.job_id,
            JobStatus.COMPLETED,
            completed_at=datetime(2024, 1, 2),
        )

        job3 = state_store.create_bible_job(books=["luke"], config={})
        state_store.update_job_status(
            job3.job_id,
            JobStatus.COMPLETED,
            completed_at=datetime(2024, 1, 3),
        )

        # Delete all but 1 latest
        deleted = state_store.delete_completed_jobs(keep_latest=1)
        assert deleted == 2

        # Only most recent should remain
        assert state_store.get_bible_job(job1.job_id) is None
        assert state_store.get_bible_job(job2.job_id) is None
        assert state_store.get_bible_job(job3.job_id) is not None

    def test_delete_completed_jobs_keeps_multiple(
        self, state_store: JobStateStore
    ) -> None:
        """Test keeping multiple recent completed jobs."""
        jobs = []
        for i in range(5):
            job = state_store.create_bible_job(books=["john"], config={})
            state_store.update_job_status(
                job.job_id,
                JobStatus.COMPLETED,
                completed_at=datetime(2024, 1, i + 1),
            )
            jobs.append(job)

        deleted = state_store.delete_completed_jobs(keep_latest=2)
        assert deleted == 3

        # Verify only last 2 remain
        remaining = state_store.list_jobs()
        assert len(remaining) == 2

    def test_delete_completed_jobs_no_completed(
        self, state_store: JobStateStore
    ) -> None:
        """Test deleting completed jobs when none are completed."""
        job = state_store.create_bible_job(books=["john"], config={})
        # Job stays pending

        deleted = state_store.delete_completed_jobs(keep_latest=1)
        assert deleted == 0
        assert state_store.get_bible_job(job.job_id) is not None

    def test_delete_jobs_older_than(self, state_store: JobStateStore) -> None:
        """Test deleting jobs older than N days."""
        from datetime import timedelta

        # Create an old job (manually set created_at)
        job1 = state_store.create_bible_job(books=["john"], config={})

        # Manually update the created_at to be old
        old_date = (datetime.utcnow() - timedelta(days=10)).isoformat()
        with state_store._connection() as conn:
            conn.execute(
                "UPDATE bible_jobs SET created_at = ? WHERE job_id = ?",
                (old_date, job1.job_id),
            )

        # Create a recent job
        job2 = state_store.create_bible_job(books=["matthew"], config={})

        # Delete jobs older than 5 days
        deleted = state_store.delete_jobs_older_than(5)
        assert deleted == 1

        assert state_store.get_bible_job(job1.job_id) is None
        assert state_store.get_bible_job(job2.job_id) is not None

    def test_delete_jobs_older_than_none_old(
        self, state_store: JobStateStore
    ) -> None:
        """Test deleting old jobs when none are old enough."""
        job = state_store.create_bible_job(books=["john"], config={})

        deleted = state_store.delete_jobs_older_than(30)
        assert deleted == 0
        assert state_store.get_bible_job(job.job_id) is not None

    def test_list_jobs(self, state_store: JobStateStore) -> None:
        """Test listing all jobs."""
        job1 = state_store.create_bible_job(books=["john"], config={})
        job2 = state_store.create_bible_job(books=["matthew"], config={})
        job3 = state_store.create_bible_job(books=["luke"], config={})

        jobs = state_store.list_jobs()
        assert len(jobs) == 3

        # Should be ordered by created_at desc
        job_ids = [j.job_id for j in jobs]
        assert job3.job_id == job_ids[0]
        assert job2.job_id == job_ids[1]
        assert job1.job_id == job_ids[2]

    def test_list_jobs_with_limit(self, state_store: JobStateStore) -> None:
        """Test listing jobs with a limit."""
        for i in range(10):
            state_store.create_bible_job(books=["john"], config={})

        jobs = state_store.list_jobs(limit=5)
        assert len(jobs) == 5

    def test_get_database_stats(self, state_store: JobStateStore) -> None:
        """Test getting database statistics."""
        # Create jobs in various states
        job1 = state_store.create_bible_job(books=["john"], config={})
        state_store.initialize_verse_jobs(job1.job_id, "john", [(1, 1), (1, 2)])

        job2 = state_store.create_bible_job(books=["matthew"], config={})
        state_store.update_job_status(job2.job_id, JobStatus.IN_PROGRESS)

        job3 = state_store.create_bible_job(books=["luke"], config={})
        state_store.update_job_status(job3.job_id, JobStatus.COMPLETED)

        job4 = state_store.create_bible_job(books=["mark"], config={})
        state_store.update_job_status(job4.job_id, JobStatus.FAILED)

        stats = state_store.get_database_stats()

        assert stats["total_jobs"] == 4
        assert stats["pending_jobs"] == 1
        assert stats["in_progress_jobs"] == 1
        assert stats["completed_jobs"] == 1
        assert stats["failed_jobs"] == 1
        assert stats["total_verse_records"] == 2
        assert stats["database_size_mb"] >= 0