"""Background-job-related queries.

Functions take an explicit sqlite3 connection with row_factory=sqlite3.Row
and return plain dicts. No Flask imports.
"""

from datetime import datetime

JOB_COLUMNS = (
    "id, novel_id, job_type, status, input_json, output_json, error, "
    "created_at, completed_at"
)

ALLOWED_UPDATE_FIELDS = {
    "status",
    "output_json",
    "error",
    "completed_at",
}


def _rows(cur):
    return [dict(row) for row in cur.fetchall()]


def _now():
    return datetime.now().isoformat(timespec="seconds")


def create_job(db, novel_id, job_type, input_json=None):
    """Insert a new job with status 'pending' and return its id."""
    cur = db.execute(
        "INSERT INTO jobs (novel_id, job_type, status, input_json, created_at) "
        "VALUES (?, ?, 'pending', ?, ?)",
        (novel_id, job_type, input_json, _now()),
    )
    db.commit()
    return cur.lastrowid


def update_job(db, job_id, **fields):
    """Update allowed job fields."""
    updates = {k: v for k, v in fields.items() if k in ALLOWED_UPDATE_FIELDS}
    if not updates:
        return
    assignments = ", ".join(f"{key} = ?" for key in updates)
    db.execute(
        f"UPDATE jobs SET {assignments} WHERE id = ?",
        (*updates.values(), job_id),
    )
    db.commit()


def get_job(db, job_id):
    """Single job row as dict, or None."""
    row = db.execute(
        f"SELECT {JOB_COLUMNS} FROM jobs WHERE id = ?", (job_id,)
    ).fetchone()
    return dict(row) if row is not None else None


def list_jobs(db, novel_id, limit=20):
    """Most recent jobs for a novel, newest first."""
    return _rows(
        db.execute(
            f"SELECT {JOB_COLUMNS} FROM jobs WHERE novel_id = ? ORDER BY id DESC LIMIT ?",
            (novel_id, limit),
        )
    )