#!/usr/bin/env python3
"""Build the per-user post census from JSONPlaceholder /users + /posts.

Extract lane (data extraction into a table). Produces:
  2026-11-01-posts-census.csv  (tab-separated)
  2026-11-01-posts-census.md   (markdown table)
  2026-11-01-posts-census.json (machine-readable)

Canned run:  python3 build_posts_census.py
"""
import csv
import json
import urllib.request
import urllib.error

BASE = "https://jsonplaceholder.typicode.com"
OUT_DIR = "/root/troupe-standup/wizard-work/ledger"


def fetch(path):
    with urllib.request.urlopen(BASE + path, timeout=20) as r:
        return json.loads(r.read().decode("utf-8"))


def build(users, posts):
    by_id = {u["id"]: u for u in users}
    post_counts = {}
    for p in posts:
        post_counts[p["userId"]] = post_counts.get(p["userId"], 0) + 1

    rows = []
    for uid in sorted(by_id):
        u = by_id[uid]
        rows.append({
            "id": uid,
            "name": u["name"],
            "username": u["username"],
            "email": u["email"],
            "city": u["address"]["city"],
            "post_count": post_counts.get(uid, 0),
        })
    return rows


def write_artifacts(rows):
    # CSV (comma-separated, the CSV convention)
    csv_path = f"{OUT_DIR}/2026-11-01-posts-census.csv"
    with open(csv_path, "w", encoding="utf-8", newline="") as f:
        w = csv.writer(f, lineterminator="\n")  # comma-separated (CSV convention)
        w.writerow(["id", "name", "username", "email", "city", "post_count"])
        for r in rows:
            w.writerow([r["id"], r["name"], r["username"], r["email"],
                        r["city"], r["post_count"]])

    # Markdown table
    md_path = f"{OUT_DIR}/2026-11-01-posts-census.md"
    with open(md_path, "w", encoding="utf-8") as f:
        f.write("# Per-user Post Census (JSONPlaceholder)\n\n")
        f.write("Table of each user with their post count "
                f"({len(rows)} users, {sum(r['post_count'] for r in rows)} posts total).\n\n")
        f.write("| id | name | username | email | city | post_count |\n")
        f.write("|---:|---|---:|---:|---:|---:|\n")
        for r in rows:
            f.write(f"| {r['id']} | {r['name']} | {r['username']} | "
                    f"{r['email']} | {r['city']} | {r['post_count']} |\n")

    # JSON
    json_path = f"{OUT_DIR}/2026-11-01-posts-census.json"
    payload = {
        "source": "https://jsonplaceholder.typicode.com/users + /posts",
        "n_users": len(rows),
        "total_posts": sum(r["post_count"] for r in rows),
        "rows": rows,
    }
    with open(json_path, "w", encoding="utf-8") as f:
        json.dump(payload, f, indent=2)

    print(f"wrote {csv_path} ({len(rows)} data rows)")
    print(f"wrote {md_path}")
    print(f"wrote {json_path}")


if __name__ == "__main__":
    users = fetch("/users")
    posts = fetch("/posts")
    rows = build(users, posts)
    write_artifacts(rows)