# 续写同一文件：catalog_project/src/cataloghub/storage.py
def query(connection, q="", finished="all", tag="", page=1, size=3):
    conditions, values = ["instr(lower(title),lower(?))>0"], [q]
    if finished != "all":
        conditions.append("finished=?")
        values.append(int(finished))
    if tag:
        # json_each 将这本书的标签数组展开，value 是其中一项标签。
        conditions.append(
            "EXISTS(SELECT 1 FROM json_each(books.tags_json) WHERE value=?)"
        )
        values.append(tag)
    where = " AND ".join(conditions)
    count = connection.execute(
        "SELECT count(*) FROM books WHERE " + where, values
    ).fetchone()[0]
    rows = connection.execute(
        "SELECT * FROM books WHERE " + where + " ORDER BY source_id LIMIT ? OFFSET ?",
        [*values, size, (page - 1) * size],
    ).fetchall()
    return {
        "total": count,
        "page": page,
        "size": size,
        "pages": max(1, (count + size - 1) // size),
        "items": [public(row) for row in rows],
    }


def public(row):
    return {
        "source_id": row["source_id"],
        "title": row["title"],
        "finished": bool(row["finished"]),
        "tags": json.loads(row["tags_json"]),
        "updated_run": row["updated_run"],
    }