#!/usr/bin/env python3
"""Extract a tabular users census from the jsonplaceholder API into Markdown,
CSV and JSON. Reads the already-downloaded /tmp/{users,todos}.json and writes
the committed crew under /root/troupe-standup/wizard-work/ledger/."""
import csv
import json
import os
import sys
from collections import Counter

USERS = "/tmp/users.json"
TODOS = "/tmp/todos.json"
OUT_DIR = "/root/troupe-standup/wizard-work/ledger"

COLUMNS = ["id", "name", "username", "email", "city", "todo_count", "completed_count"]


def load(name):
    with open(name, encoding="utf-8") as fh:
        return json.load(fh)


def main():
    users = load(USERS)
    todos = load(TODOS)

    todos_by_owner = {}
    for t in todos:
        todos_by_owner.setdefault(t["userId"], []).append(t)

    rows = []
    for u in sorted(users, key=lambda x: x["id"]):
        owner_todos = todos_by_owner.get(u["id"], [])
        rows.append({
            "id": u["id"],
            "name": u["name"],
            "username": u["username"],
            "email": u["email"],
            "city": u["address"]["city"],
            "todo_count": len(owner_todos),
            "completed_count": sum(1 for t in owner_todos if t["completed"]),
        })

    os.makedirs(OUT_DIR, exist_ok=True)

    md_path = os.path.join(OUT_DIR, "users-census.md")
    with open(md_path, "w", encoding="utf-8") as fh:
        fh.write("# Users Census (jsonplaceholder)\n\n")
        fh.write("Table of all users with per-user TODO counts (10 users, 200 todos).\n\n")
        fh.write("| id | name | username | email | city | todo_count | completed_count |\n")
        fh.write("|---:|---|---:|---:|---|---:|---:|\n")
        for r in rows:
            fh.write(f"| {r['id']} | {r['name']} | {r['username']} | {r['email']} | {r['city']} | {r['todo_count']} | {r['completed_count']} |\n")

    csv_path = os.path.join(OUT_DIR, "users-census.csv")
    with open(csv_path, "w", encoding="utf-8", newline="") as fh:
        w = csv.DictWriter(fh, fieldnames=COLUMNS)
        w.writeheader()
        w.writerows(rows)

    completed_total = sum(r["completed_count"] for r in rows)
    activity = {
        "source": "https://jsonplaceholder.typicode.com/users",
        "source_todos": "https://jsonplaceholder.typicode.com/todos",
        "n_users": len(rows),
        "n_todos": len(todos),
        "users": rows,
        "totals": {
            "users": len(rows),
            "todos": len(todos),
            "completed": completed_total,
        },
    }
    json_path = os.path.join(OUT_DIR, "users-activity.json")
    with open(json_path, "w", encoding="utf-8") as fh:
        json.dump(activity, fh, indent=2)

    print(f"wrote {md_path}")
    print(f"wrote {csv_path}")
    print(f"wrote {json_path}")
    print(f"users={len(rows)} todos={len(todos)} completed={completed_total}")


if __name__ == "__main__":
    sys.exit(main())