"""Character-related queries.

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

from datetime import datetime

CHARACTER_COLUMNS = (
    "id, novel_id, name, role, description, first_appearance_chapter, "
    "status, created_at, updated_at"
)

ALLOWED_UPDATE_FIELDS = {
    "name",
    "role",
    "description",
    "first_appearance_chapter",
    "status",
}


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


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


def list_characters(db, novel_id):
    """All characters of a novel ordered by id."""
    return _rows(
        db.execute(
            f"SELECT {CHARACTER_COLUMNS} FROM characters WHERE novel_id = ? ORDER BY id",
            (novel_id,),
        )
    )


def get_character(db, character_id):
    """Single character row as dict, or None."""
    row = db.execute(
        f"SELECT {CHARACTER_COLUMNS} FROM characters WHERE id = ?", (character_id,)
    ).fetchone()
    return dict(row) if row is not None else None


def create_character(db, novel_id, name, role=None, description=None, first_appearance_chapter=None):
    """Insert a new character and return its id."""
    now = _now()
    cur = db.execute(
        "INSERT INTO characters (novel_id, name, role, description, first_appearance_chapter, status, created_at, updated_at) "
        "VALUES (?, ?, ?, ?, ?, 'alive', ?, ?)",
        (novel_id, name, role, description, first_appearance_chapter, now, now),
    )
    db.commit()
    return cur.lastrowid


def update_character(db, character_id, **fields):
    """Update allowed character fields."""
    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 characters SET {assignments} WHERE id = ?",
        (*updates.values(), character_id),
    )
    db.commit()


def find_characters_in_text(db, novel_id, text):
    """Characters whose name appears as a substring of text."""
    characters = _rows(
        db.execute(
            f"SELECT {CHARACTER_COLUMNS} FROM characters WHERE novel_id = ?",
            (novel_id,),
        )
    )
    return [c for c in characters if c["name"] and c["name"] in text]