"""
GLAMWalk -- Volume 5: From Data to Product
05_collection_to_book_pipeline.py

Turning a saved collection of image_pool rows into a book skeleton, and mechanically generating a correct attribution page by checking one field per image.

Source: Volume Five, Sections 3.1-3.2
Targets Python 3.11.4 -- see Volume One, Chapter 1.5/1.6.
"""

def create_collection(conn, name, image_pool_ids):
    conn.execute(
        "INSERT INTO collections (name, created_at) VALUES (?, datetime('now'))",
        (name,),
    )
    collection_id = conn.execute("SELECT last_insert_rowid()").fetchone()[0]

    for pool_id in image_pool_ids:
        conn.execute(
            "INSERT INTO collection_items (collection_id, image_pool_id) VALUES (?, ?)",
            (collection_id, pool_id),
        )
    conn.commit()
    return collection_id

def build_book_skeleton_from_collection(conn, collection_id):
    rows = conn.execute("""
        SELECT ip.* FROM image_pool ip
        JOIN collection_items ci ON ci.image_pool_id = ip.id
        WHERE ci.collection_id = ?
    """, (collection_id,)).fetchall()

    chapters = []
    for i, row in enumerate(rows, start=1):
        chapters.append({
            "chapter_num": i,
            "title": row["title"] or f"Plate {i}",
            "image_source_type": row["source_type"],
            "image_reference": row["source_reference"],
            "requires_attribution": row["requires_attribution"],
        })
    return chapters


def generate_attribution_page(chapters, image_pool_lookup):
    lines = ["## Image Credits\n"]
    for ch in chapters:
        pool_row = image_pool_lookup[ch["image_reference"]]
        if pool_row["requires_attribution"]:
            lines.append(f"- {ch['title']}: {pool_row['attribution_text']}")
    return "\n".join(lines) if len(lines) > 1 else None