"""SQLite-based job state persistence for fault-tolerant translation.

Provides ACID-compliant storage for job state, enabling crash recovery
and resume functionality. Uses SQLite for reliable atomic operations.
"""

from __future__ import annotations

import json
import sqlite3
from contextlib import contextmanager
from datetime import datetime
from pathlib import Path
from typing import Any, Generator

from src.orchestration.models import BibleJob, BookJob, JobStatus, VerseJob


class JobStateStore:
    """SQLite-based storage for Bible translation job state.

    Provides atomic operations for:
    - Creating and managing Bible translation jobs
    - Tracking verse-level progress
    - Claiming verses for worker processes
    - Recovering from crashes

    All operations use SQLite transactions for ACID compliance.
    """

    def __init__(self, db_path: Path | str) -> None:
        """Initialize the job state store.

        Args:
            db_path: Path to the SQLite database file.
        """
        self.db_path = Path(db_path)
        self.db_path.parent.mkdir(parents=True, exist_ok=True)
        self._init_schema()

    def _init_schema(self) -> None:
        """Initialize the database schema."""
        with self._connection() as conn:
            conn.executescript("""
                CREATE TABLE IF NOT EXISTS bible_jobs (
                    job_id TEXT PRIMARY KEY,
                    books TEXT NOT NULL,
                    status TEXT NOT NULL DEFAULT 'pending',
                    config_snapshot TEXT NOT NULL DEFAULT '{}',
                    created_at TEXT NOT NULL,
                    started_at TEXT,
                    completed_at TEXT,
                    total_verses INTEGER DEFAULT 0,
                    completed_verses INTEGER DEFAULT 0,
                    failed_verses INTEGER DEFAULT 0
                );

                CREATE TABLE IF NOT EXISTS book_jobs (
                    job_id TEXT NOT NULL,
                    book TEXT NOT NULL,
                    status TEXT NOT NULL DEFAULT 'pending',
                    total_verses INTEGER DEFAULT 0,
                    completed_verses INTEGER DEFAULT 0,
                    failed_verses INTEGER DEFAULT 0,
                    worker_pid INTEGER,
                    started_at TEXT,
                    completed_at TEXT,
                    error_message TEXT,
                    PRIMARY KEY (job_id, book),
                    FOREIGN KEY (job_id) REFERENCES bible_jobs(job_id)
                );

                CREATE TABLE IF NOT EXISTS verse_jobs (
                    job_id TEXT NOT NULL,
                    book TEXT NOT NULL,
                    chapter INTEGER NOT NULL,
                    verse INTEGER NOT NULL,
                    status TEXT NOT NULL DEFAULT 'pending',
                    attempts INTEGER DEFAULT 0,
                    last_error TEXT,
                    worker_pid INTEGER,
                    started_at TEXT,
                    completed_at TEXT,
                    PRIMARY KEY (job_id, book, chapter, verse),
                    FOREIGN KEY (job_id) REFERENCES bible_jobs(job_id)
                );

                CREATE INDEX IF NOT EXISTS idx_verse_pending
                ON verse_jobs(job_id, book, status)
                WHERE status = 'pending';

                CREATE INDEX IF NOT EXISTS idx_bible_jobs_status
                ON bible_jobs(status);

                CREATE INDEX IF NOT EXISTS idx_book_jobs_status
                ON book_jobs(job_id, status);
            """)

    @contextmanager
    def _connection(self) -> Generator[sqlite3.Connection, None, None]:
        """Create a database connection with proper settings.

        Yields:
            SQLite connection with row factory configured.
        """
        conn = sqlite3.connect(str(self.db_path), timeout=30.0)
        conn.row_factory = sqlite3.Row
        conn.execute("PRAGMA journal_mode=WAL")
        conn.execute("PRAGMA foreign_keys=ON")
        try:
            yield conn
            conn.commit()
        except Exception:
            conn.rollback()
            raise
        finally:
            conn.close()

    # -------------------------------------------------------------------------
    # Bible Job Management
    # -------------------------------------------------------------------------

    def create_bible_job(
        self,
        books: list[str],
        config: dict[str, Any],
        job_id: str | None = None,
    ) -> BibleJob:
        """Create a new Bible translation job.

        Args:
            books: List of book names to translate.
            config: Configuration snapshot for this job.
            job_id: Optional specific job ID (generates UUID if not provided).

        Returns:
            Created BibleJob instance.
        """
        import uuid

        job = BibleJob(
            job_id=job_id or str(uuid.uuid4()),
            books=books,
            status=JobStatus.PENDING,
            config_snapshot=config,
            created_at=datetime.utcnow(),
        )

        with self._connection() as conn:
            conn.execute(
                """
                INSERT INTO bible_jobs
                (job_id, books, status, config_snapshot, created_at,
                 total_verses, completed_verses, failed_verses)
                VALUES (?, ?, ?, ?, ?, ?, ?, ?)
                """,
                (
                    job.job_id,
                    json.dumps(job.books),
                    job.status.value,
                    json.dumps(job.config_snapshot),
                    job.created_at.isoformat(),
                    job.total_verses,
                    job.completed_verses,
                    job.failed_verses,
                ),
            )

            # Create book job entries
            for book in books:
                conn.execute(
                    """
                    INSERT INTO book_jobs (job_id, book, status)
                    VALUES (?, ?, ?)
                    """,
                    (job.job_id, book, JobStatus.PENDING.value),
                )

        return job

    def get_bible_job(self, job_id: str) -> BibleJob | None:
        """Get a Bible job by ID.

        Args:
            job_id: Job identifier.

        Returns:
            BibleJob if found, None otherwise.
        """
        with self._connection() as conn:
            row = conn.execute(
                "SELECT * FROM bible_jobs WHERE job_id = ?",
                (job_id,),
            ).fetchone()

            if not row:
                return None

            return self._row_to_bible_job(row)

    def get_latest_incomplete_job(self) -> BibleJob | None:
        """Get the most recent incomplete job.

        Returns:
            Most recent BibleJob with status pending or in_progress.
        """
        with self._connection() as conn:
            row = conn.execute(
                """
                SELECT * FROM bible_jobs
                WHERE status IN (?, ?)
                ORDER BY created_at DESC
                LIMIT 1
                """,
                (JobStatus.PENDING.value, JobStatus.IN_PROGRESS.value),
            ).fetchone()

            if not row:
                return None

            return self._row_to_bible_job(row)

    def update_job_status(
        self,
        job_id: str,
        status: JobStatus,
        started_at: datetime | None = None,
        completed_at: datetime | None = None,
    ) -> None:
        """Update the status of a Bible job.

        Args:
            job_id: Job identifier.
            status: New status.
            started_at: When job started (if transitioning to in_progress).
            completed_at: When job completed (if transitioning to completed/failed).
        """
        with self._connection() as conn:
            updates = ["status = ?"]
            params: list[Any] = [status.value]

            if started_at:
                updates.append("started_at = ?")
                params.append(started_at.isoformat())

            if completed_at:
                updates.append("completed_at = ?")
                params.append(completed_at.isoformat())

            params.append(job_id)

            conn.execute(
                f"UPDATE bible_jobs SET {', '.join(updates)} WHERE job_id = ?",
                params,
            )

    def update_job_verse_counts(
        self,
        job_id: str,
        total: int | None = None,
        completed: int | None = None,
        failed: int | None = None,
    ) -> None:
        """Update verse counts for a Bible job.

        Args:
            job_id: Job identifier.
            total: New total verse count.
            completed: New completed verse count.
            failed: New failed verse count.
        """
        with self._connection() as conn:
            updates = []
            params: list[Any] = []

            if total is not None:
                updates.append("total_verses = ?")
                params.append(total)

            if completed is not None:
                updates.append("completed_verses = ?")
                params.append(completed)

            if failed is not None:
                updates.append("failed_verses = ?")
                params.append(failed)

            if not updates:
                return

            params.append(job_id)

            conn.execute(
                f"UPDATE bible_jobs SET {', '.join(updates)} WHERE job_id = ?",
                params,
            )

    # -------------------------------------------------------------------------
    # Book Job Management
    # -------------------------------------------------------------------------

    def get_book_job(self, job_id: str, book: str) -> BookJob | None:
        """Get a book job by job ID and book name.

        Args:
            job_id: Parent job identifier.
            book: Book name.

        Returns:
            BookJob if found, None otherwise.
        """
        with self._connection() as conn:
            row = conn.execute(
                "SELECT * FROM book_jobs WHERE job_id = ? AND book = ?",
                (job_id, book),
            ).fetchone()

            if not row:
                return None

            return self._row_to_book_job(row)

    def get_book_jobs(self, job_id: str) -> list[BookJob]:
        """Get all book jobs for a Bible job.

        Args:
            job_id: Parent job identifier.

        Returns:
            List of BookJob instances.
        """
        with self._connection() as conn:
            rows = conn.execute(
                "SELECT * FROM book_jobs WHERE job_id = ? ORDER BY book",
                (job_id,),
            ).fetchall()

            return [self._row_to_book_job(row) for row in rows]

    def update_book_status(
        self,
        job_id: str,
        book: str,
        status: JobStatus,
        worker_pid: int | None = None,
        started_at: datetime | None = None,
        completed_at: datetime | None = None,
        error_message: str | None = None,
    ) -> None:
        """Update the status of a book job.

        Args:
            job_id: Parent job identifier.
            book: Book name.
            status: New status.
            worker_pid: Worker process PID.
            started_at: When book started.
            completed_at: When book completed.
            error_message: Error message if failed.
        """
        with self._connection() as conn:
            updates = ["status = ?"]
            params: list[Any] = [status.value]

            if worker_pid is not None:
                updates.append("worker_pid = ?")
                params.append(worker_pid)

            if started_at:
                updates.append("started_at = ?")
                params.append(started_at.isoformat())

            if completed_at:
                updates.append("completed_at = ?")
                params.append(completed_at.isoformat())

            if error_message is not None:
                updates.append("error_message = ?")
                params.append(error_message)

            params.extend([job_id, book])

            conn.execute(
                f"UPDATE book_jobs SET {', '.join(updates)} "
                "WHERE job_id = ? AND book = ?",
                params,
            )

    def update_book_verse_counts(
        self,
        job_id: str,
        book: str,
        total: int | None = None,
        completed: int | None = None,
        failed: int | None = None,
    ) -> None:
        """Update verse counts for a book job.

        Args:
            job_id: Parent job identifier.
            book: Book name.
            total: New total verse count.
            completed: New completed verse count.
            failed: New failed verse count.
        """
        with self._connection() as conn:
            updates = []
            params: list[Any] = []

            if total is not None:
                updates.append("total_verses = ?")
                params.append(total)

            if completed is not None:
                updates.append("completed_verses = ?")
                params.append(completed)

            if failed is not None:
                updates.append("failed_verses = ?")
                params.append(failed)

            if not updates:
                return

            params.extend([job_id, book])

            conn.execute(
                f"UPDATE book_jobs SET {', '.join(updates)} "
                "WHERE job_id = ? AND book = ?",
                params,
            )

    # -------------------------------------------------------------------------
    # Verse Job Management
    # -------------------------------------------------------------------------

    def initialize_verse_jobs(
        self,
        job_id: str,
        book: str,
        verses: list[tuple[int, int]],
        completed_verses: list[tuple[int, int]] | None = None,
    ) -> None:
        """Initialize verse jobs for a book.

        Args:
            job_id: Parent job identifier.
            book: Book name.
            verses: List of (chapter, verse) tuples to mark as pending.
            completed_verses: List of (chapter, verse) tuples already
                translated on disk, marked COMPLETED immediately.
        """
        completed_verses = completed_verses or []
        total = len(verses) + len(completed_verses)

        with self._connection() as conn:
            # Delete any existing verse jobs for this book
            conn.execute(
                "DELETE FROM verse_jobs WHERE job_id = ? AND book = ?",
                (job_id, book),
            )

            # Insert pending verse jobs
            if verses:
                conn.executemany(
                    """
                    INSERT INTO verse_jobs (job_id, book, chapter, verse, status)
                    VALUES (?, ?, ?, ?, ?)
                    """,
                    [
                        (job_id, book, chapter, verse, JobStatus.PENDING.value)
                        for chapter, verse in verses
                    ],
                )

            # Insert already-completed verse jobs
            if completed_verses:
                now = datetime.utcnow().isoformat()
                conn.executemany(
                    """
                    INSERT INTO verse_jobs
                        (job_id, book, chapter, verse, status, completed_at)
                    VALUES (?, ?, ?, ?, ?, ?)
                    """,
                    [
                        (job_id, book, chapter, verse,
                         JobStatus.COMPLETED.value, now)
                        for chapter, verse in completed_verses
                    ],
                )

            # Update book totals
            conn.execute(
                """
                UPDATE book_jobs
                SET total_verses = ?, completed_verses = ?
                WHERE job_id = ? AND book = ?
                """,
                (total, len(completed_verses), job_id, book),
            )

    def atomic_claim_verse(
        self,
        job_id: str,
        book: str,
        chapter: int,
        verse: int,
        worker_pid: int,
    ) -> bool:
        """Atomically claim a verse for translation.

        Uses a conditional update to ensure only one worker can claim a verse.

        Args:
            job_id: Parent job identifier.
            book: Book name.
            chapter: Chapter number.
            verse: Verse number.
            worker_pid: Worker process PID claiming the verse.

        Returns:
            True if claim succeeded, False if verse was already claimed.
        """
        with self._connection() as conn:
            cursor = conn.execute(
                """
                UPDATE verse_jobs
                SET status = ?, worker_pid = ?, started_at = ?, attempts = attempts + 1
                WHERE job_id = ? AND book = ? AND chapter = ? AND verse = ?
                AND status = ?
                """,
                (
                    JobStatus.IN_PROGRESS.value,
                    worker_pid,
                    datetime.utcnow().isoformat(),
                    job_id,
                    book,
                    chapter,
                    verse,
                    JobStatus.PENDING.value,
                ),
            )
            return cursor.rowcount > 0

    def update_verse_status(
        self,
        job_id: str,
        book: str,
        chapter: int,
        verse: int,
        status: JobStatus,
        error: str | None = None,
    ) -> None:
        """Update the status of a verse job.

        Args:
            job_id: Parent job identifier.
            book: Book name.
            chapter: Chapter number.
            verse: Verse number.
            status: New status.
            error: Error message if failed.
        """
        with self._connection() as conn:
            updates = ["status = ?"]
            params: list[Any] = [status.value]

            if status in (JobStatus.COMPLETED, JobStatus.FAILED):
                updates.append("completed_at = ?")
                params.append(datetime.utcnow().isoformat())

            if error is not None:
                updates.append("last_error = ?")
                params.append(error)

            params.extend([job_id, book, chapter, verse])

            conn.execute(
                f"UPDATE verse_jobs SET {', '.join(updates)} "
                "WHERE job_id = ? AND book = ? AND chapter = ? AND verse = ?",
                params,
            )

            # Update book counts
            if status == JobStatus.COMPLETED:
                conn.execute(
                    """
                    UPDATE book_jobs
                    SET completed_verses = completed_verses + 1
                    WHERE job_id = ? AND book = ?
                    """,
                    (job_id, book),
                )
            elif status == JobStatus.FAILED:
                conn.execute(
                    """
                    UPDATE book_jobs
                    SET failed_verses = failed_verses + 1
                    WHERE job_id = ? AND book = ?
                    """,
                    (job_id, book),
                )

    def get_pending_verses(self, job_id: str, book: str) -> list[VerseJob]:
        """Get all pending verses for a book.

        Args:
            job_id: Parent job identifier.
            book: Book name.

        Returns:
            List of pending VerseJob instances.
        """
        with self._connection() as conn:
            rows = conn.execute(
                """
                SELECT * FROM verse_jobs
                WHERE job_id = ? AND book = ? AND status = ?
                ORDER BY chapter, verse
                """,
                (job_id, book, JobStatus.PENDING.value),
            ).fetchall()

            return [self._row_to_verse_job(row) for row in rows]

    def get_failed_verses(self, job_id: str, book: str) -> list[VerseJob]:
        """Get all failed verses for a book.

        Args:
            job_id: Parent job identifier.
            book: Book name.

        Returns:
            List of failed VerseJob instances.
        """
        with self._connection() as conn:
            rows = conn.execute(
                """
                SELECT * FROM verse_jobs
                WHERE job_id = ? AND book = ? AND status = ?
                ORDER BY chapter, verse
                """,
                (job_id, book, JobStatus.FAILED.value),
            ).fetchall()

            return [self._row_to_verse_job(row) for row in rows]

    def get_book_progress(self, job_id: str, book: str) -> dict[str, Any]:
        """Get progress statistics for a book.

        Args:
            job_id: Parent job identifier.
            book: Book name.

        Returns:
            Dictionary with progress statistics.
        """
        with self._connection() as conn:
            row = conn.execute(
                """
                SELECT
                    total_verses,
                    completed_verses,
                    failed_verses,
                    status
                FROM book_jobs
                WHERE job_id = ? AND book = ?
                """,
                (job_id, book),
            ).fetchone()

            if not row:
                return {}

            total = row["total_verses"]
            completed = row["completed_verses"]
            failed = row["failed_verses"]
            pending = total - completed - failed

            return {
                "book": book,
                "status": row["status"],
                "total_verses": total,
                "completed_verses": completed,
                "failed_verses": failed,
                "pending_verses": pending,
                "progress_percentage": (completed / total * 100) if total > 0 else 0,
            }

    def reset_orphaned_verses(self, job_id: str) -> int:
        """Reset verses that were in_progress but not completed (orphaned by crash).

        Args:
            job_id: Parent job identifier.

        Returns:
            Number of verses reset.
        """
        with self._connection() as conn:
            cursor = conn.execute(
                """
                UPDATE verse_jobs
                SET status = ?, worker_pid = NULL
                WHERE job_id = ? AND status = ?
                """,
                (JobStatus.PENDING.value, job_id, JobStatus.IN_PROGRESS.value),
            )
            return cursor.rowcount

    def reset_failed_verses(self, job_id: str, book: str) -> int:
        """Reset failed verses to pending for retry.

        Args:
            job_id: Parent job identifier.
            book: Book name.

        Returns:
            Number of verses reset.
        """
        with self._connection() as conn:
            cursor = conn.execute(
                """
                UPDATE verse_jobs
                SET status = ?, worker_pid = NULL, last_error = NULL
                WHERE job_id = ? AND book = ? AND status = ?
                """,
                (JobStatus.PENDING.value, job_id, book, JobStatus.FAILED.value),
            )

            # Update book counts
            reset_count = cursor.rowcount
            if reset_count > 0:
                conn.execute(
                    """
                    UPDATE book_jobs
                    SET failed_verses = failed_verses - ?
                    WHERE job_id = ? AND book = ?
                    """,
                    (reset_count, job_id, book),
                )

            return reset_count

    # -------------------------------------------------------------------------
    # Helper Methods
    # -------------------------------------------------------------------------

    def _row_to_bible_job(self, row: sqlite3.Row) -> BibleJob:
        """Convert a database row to a BibleJob."""
        return BibleJob(
            job_id=row["job_id"],
            books=json.loads(row["books"]),
            status=JobStatus(row["status"]),
            config_snapshot=json.loads(row["config_snapshot"]),
            created_at=datetime.fromisoformat(row["created_at"]),
            started_at=(
                datetime.fromisoformat(row["started_at"])
                if row["started_at"]
                else None
            ),
            completed_at=(
                datetime.fromisoformat(row["completed_at"])
                if row["completed_at"]
                else None
            ),
            total_verses=row["total_verses"],
            completed_verses=row["completed_verses"],
            failed_verses=row["failed_verses"],
        )

    def _row_to_book_job(self, row: sqlite3.Row) -> BookJob:
        """Convert a database row to a BookJob."""
        return BookJob(
            book=row["book"],
            status=JobStatus(row["status"]),
            total_verses=row["total_verses"],
            completed_verses=row["completed_verses"],
            failed_verses=row["failed_verses"],
            worker_pid=row["worker_pid"],
            started_at=(
                datetime.fromisoformat(row["started_at"])
                if row["started_at"]
                else None
            ),
            completed_at=(
                datetime.fromisoformat(row["completed_at"])
                if row["completed_at"]
                else None
            ),
            error_message=row["error_message"],
        )

    def _row_to_verse_job(self, row: sqlite3.Row) -> VerseJob:
        """Convert a database row to a VerseJob."""
        return VerseJob(
            book=row["book"],
            chapter=row["chapter"],
            verse=row["verse"],
            status=JobStatus(row["status"]),
            attempts=row["attempts"],
            last_error=row["last_error"],
            worker_pid=row["worker_pid"],
            started_at=(
                datetime.fromisoformat(row["started_at"])
                if row["started_at"]
                else None
            ),
            completed_at=(
                datetime.fromisoformat(row["completed_at"])
                if row["completed_at"]
                else None
            ),
        )

    # -------------------------------------------------------------------------
    # Cleanup Methods
    # -------------------------------------------------------------------------

    def delete_job(self, job_id: str) -> bool:
        """Delete a job and all its associated data.

        Args:
            job_id: Job identifier to delete.

        Returns:
            True if job was deleted, False if job not found.
        """
        with self._connection() as conn:
            # Check if job exists
            existing = conn.execute(
                "SELECT 1 FROM bible_jobs WHERE job_id = ?",
                (job_id,),
            ).fetchone()

            if not existing:
                return False

            # Delete in order: verse_jobs -> book_jobs -> bible_jobs
            conn.execute("DELETE FROM verse_jobs WHERE job_id = ?", (job_id,))
            conn.execute("DELETE FROM book_jobs WHERE job_id = ?", (job_id,))
            conn.execute("DELETE FROM bible_jobs WHERE job_id = ?", (job_id,))

            return True

    def delete_completed_jobs(self, keep_latest: int = 1) -> int:
        """Delete completed jobs, optionally keeping the most recent ones.

        Args:
            keep_latest: Number of most recent completed jobs to keep.

        Returns:
            Number of jobs deleted.
        """
        with self._connection() as conn:
            # Get completed job IDs ordered by completion time
            rows = conn.execute(
                """
                SELECT job_id FROM bible_jobs
                WHERE status = ?
                ORDER BY completed_at DESC
                """,
                (JobStatus.COMPLETED.value,),
            ).fetchall()

            # Skip the ones we want to keep
            jobs_to_delete = [row["job_id"] for row in rows[keep_latest:]]

            if not jobs_to_delete:
                return 0

            # Delete each job
            placeholders = ",".join("?" * len(jobs_to_delete))
            conn.execute(
                f"DELETE FROM verse_jobs WHERE job_id IN ({placeholders})",
                jobs_to_delete,
            )
            conn.execute(
                f"DELETE FROM book_jobs WHERE job_id IN ({placeholders})",
                jobs_to_delete,
            )
            conn.execute(
                f"DELETE FROM bible_jobs WHERE job_id IN ({placeholders})",
                jobs_to_delete,
            )

            return len(jobs_to_delete)

    def delete_jobs_older_than(self, days: int) -> int:
        """Delete jobs older than a specified number of days.

        Args:
            days: Delete jobs created more than this many days ago.

        Returns:
            Number of jobs deleted.
        """
        from datetime import timedelta

        cutoff = (datetime.utcnow() - timedelta(days=days)).isoformat()

        with self._connection() as conn:
            # Get job IDs older than cutoff
            rows = conn.execute(
                """
                SELECT job_id FROM bible_jobs
                WHERE created_at < ?
                """,
                (cutoff,),
            ).fetchall()

            jobs_to_delete = [row["job_id"] for row in rows]

            if not jobs_to_delete:
                return 0

            # Delete each job
            placeholders = ",".join("?" * len(jobs_to_delete))
            conn.execute(
                f"DELETE FROM verse_jobs WHERE job_id IN ({placeholders})",
                jobs_to_delete,
            )
            conn.execute(
                f"DELETE FROM book_jobs WHERE job_id IN ({placeholders})",
                jobs_to_delete,
            )
            conn.execute(
                f"DELETE FROM bible_jobs WHERE job_id IN ({placeholders})",
                jobs_to_delete,
            )

            return len(jobs_to_delete)

    def list_jobs(self, limit: int = 20) -> list[BibleJob]:
        """List all jobs, most recent first.

        Args:
            limit: Maximum number of jobs to return.

        Returns:
            List of BibleJob instances.
        """
        with self._connection() as conn:
            rows = conn.execute(
                """
                SELECT * FROM bible_jobs
                ORDER BY created_at DESC
                LIMIT ?
                """,
                (limit,),
            ).fetchall()

            return [self._row_to_bible_job(row) for row in rows]

    def get_database_stats(self) -> dict[str, Any]:
        """Get database statistics.

        Returns:
            Dictionary with job counts and database size.
        """
        with self._connection() as conn:
            total_jobs = conn.execute(
                "SELECT COUNT(*) as count FROM bible_jobs"
            ).fetchone()["count"]

            pending_jobs = conn.execute(
                "SELECT COUNT(*) as count FROM bible_jobs WHERE status = ?",
                (JobStatus.PENDING.value,),
            ).fetchone()["count"]

            in_progress_jobs = conn.execute(
                "SELECT COUNT(*) as count FROM bible_jobs WHERE status = ?",
                (JobStatus.IN_PROGRESS.value,),
            ).fetchone()["count"]

            completed_jobs = conn.execute(
                "SELECT COUNT(*) as count FROM bible_jobs WHERE status = ?",
                (JobStatus.COMPLETED.value,),
            ).fetchone()["count"]

            failed_jobs = conn.execute(
                "SELECT COUNT(*) as count FROM bible_jobs WHERE status = ?",
                (JobStatus.FAILED.value,),
            ).fetchone()["count"]

            total_verses = conn.execute(
                "SELECT COUNT(*) as count FROM verse_jobs"
            ).fetchone()["count"]

        # Get file size
        db_size_bytes = self.db_path.stat().st_size if self.db_path.exists() else 0
        db_size_mb = db_size_bytes / (1024 * 1024)

        return {
            "total_jobs": total_jobs,
            "pending_jobs": pending_jobs,
            "in_progress_jobs": in_progress_jobs,
            "completed_jobs": completed_jobs,
            "failed_jobs": failed_jobs,
            "total_verse_records": total_verses,
            "database_size_mb": round(db_size_mb, 2),
        }