"""Novel-related queries.

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

from datetime import datetime

NOVEL_COLUMNS = (
    "id, title, genre, style, target_words, current_words, status, "
    "created_at, updated_at"
)

ALLOWED_UPDATE_FIELDS = {
    "title",
    "genre",
    "style",
    "target_words",
    "current_words",
    "status",
}


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


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


def list_novels(db):
    """All novels ordered by updated_at desc."""
    return _rows(
        db.execute(f"SELECT {NOVEL_COLUMNS} FROM novels ORDER BY updated_at DESC")
    )


def get_novel(db, novel_id):
    """Single novel row as dict, or None."""
    row = db.execute(
        f"SELECT {NOVEL_COLUMNS} FROM novels WHERE id = ?", (novel_id,)
    ).fetchone()
    return dict(row) if row is not None else None


def create_novel(db, title, genre=None, style=None, target_words=None, status="planning"):
    """Insert a new novel and return its id."""
    now = _now()
    cur = db.execute(
        "INSERT INTO novels (title, genre, style, target_words, status, created_at, updated_at) "
        "VALUES (?, ?, ?, ?, ?, ?, ?)",
        (title, genre, style, target_words, status, now, now),
    )
    db.commit()
    return cur.lastrowid


def update_novel(db, novel_id, **fields):
    """Update allowed novel fields and refresh updated_at."""
    updates = {k: v for k, v in fields.items() if k in ALLOWED_UPDATE_FIELDS}
    if not updates:
        return
    updates["updated_at"] = _now()
    assignments = ", ".join(f"{key} = ?" for key in updates)
    db.execute(
        f"UPDATE novels SET {assignments} WHERE id = ?",
        (*updates.values(), novel_id),
    )
    db.commit()


def get_stats(db, novel_id):
    """Aggregate stats for one novel."""
    chapter_count = db.execute(
        "SELECT COUNT(*) FROM chapters WHERE novel_id = ?", (novel_id,)
    ).fetchone()[0]
    done_chapters = db.execute(
        "SELECT COUNT(*) FROM chapters WHERE novel_id = ? AND status = 'done'",
        (novel_id,),
    ).fetchone()[0]
    total_words = db.execute(
        "SELECT COALESCE(SUM(word_count), 0) FROM chapters WHERE novel_id = ?",
        (novel_id,),
    ).fetchone()[0]
    character_count = db.execute(
        "SELECT COUNT(*) FROM characters WHERE novel_id = ?", (novel_id,)
    ).fetchone()[0]
    open_foreshadowing = db.execute(
        "SELECT COUNT(*) FROM foreshadowing WHERE novel_id = ? AND status IN ('pending', 'planted')",
        (novel_id,),
    ).fetchone()[0]
    return {
        "chapter_count": chapter_count,
        "done_chapters": done_chapters,
        "total_words": total_words,
        "character_count": character_count,
        "open_foreshadowing": open_foreshadowing,
    }