# pip install duckdb==1.5.5
# Place observed-rows.json beside this script. No API call or API key needed.
import json
from pathlib import Path
import duckdb

fixture = json.loads(Path(__file__).with_name("observed-rows.json").read_text())
rows = [(r["id"], r["text"]) for r in fixture["sourceRows"]]
answers = [(r["id"], r["choice"]) for r in fixture["decisions"]]
if any(label not in fixture["choices"] for _, label in answers):
    raise ValueError("Unexpected label")
with duckdb.connect(":memory:") as db:
    db.execute("CREATE TABLE source_rows(id VARCHAR PRIMARY KEY, text VARCHAR)")
    db.execute("CREATE TABLE decisions(id VARCHAR PRIMARY KEY, label VARCHAR)")
    db.executemany("INSERT INTO source_rows VALUES (?, ?)", rows)
    db.executemany("INSERT INTO decisions VALUES (?, ?)", answers)
    missing = db.execute("""
        SELECT s.id FROM source_rows s
        LEFT JOIN decisions d USING(id) WHERE d.id IS NULL
    """).fetchall()
    if missing:
        raise ValueError(f"Unclassified rows: {missing}")
    joined = db.execute("""
        SELECT s.id, s.text, d.label FROM source_rows s
        JOIN decisions d USING(id) ORDER BY s.id
    """).fetchall()
    summary = db.execute("""
        SELECT label, count(*) AS rows FROM decisions
        GROUP BY label ORDER BY label
    """).fetchall()
    print(json.dumps({"joined": joined, "summary": summary}, indent=2))