#!/usr/bin/env python3
"""Derive data.csv strictly from raw.json, then compute descriptive findings.

data.csv is the only source used for numbers quoted in NOTE.md.
"""

import csv
import json
from collections import Counter, defaultdict

OUT = "/Volumes/Storage/DeepSeek/workspace/pgx-gap-map/research-notes/faers-adverse-events"
DRUGS = [
    "atorvastatin", "metformin", "amoxicillin", "ibuprofen", "warfarin",
    "insulin glargine", "levothyroxine", "omeprazole", "sertraline", "allopurinol",
]

raw = json.load(open(f"{OUT}/raw.json", encoding="utf-8"))

rows = []
for drug in DRUGS:
    entry = raw.get(drug, {})
    results = entry.get("response_json", {}).get("results", [])
    for i, item in enumerate(results, start=1):
        rows.append(
            {
                "drug": drug,
                "rank": i,
                "reaction_term": item["term"],
                "report_count": int(item["count"]),
            }
        )

with open(f"{OUT}/data.csv", "w", newline="", encoding="utf-8") as f:
    w = csv.DictWriter(f, fieldnames=["drug", "rank", "reaction_term", "report_count"])
    w.writeheader()
    w.writerows(rows)

print(f"wrote {len(rows)} rows to data.csv")

# ---- findings computed only from the CSV ----
term_drugs = defaultdict(list)
for r in rows:
    term_drugs[r["reaction_term"]].append((r["drug"], r["report_count"]))

print("\n== term recurrence across drugs (top-5 membership) ==")
for term, lst in sorted(term_drugs.items(), key=lambda kv: (-len(kv[1]), kv[0])):
    if len(lst) >= 2:
        print(f"{len(lst):2d} drugs  {term}")

print("\n== terms appearing on >=5 drugs ==")
for term, lst in sorted(term_drugs.items(), key=lambda kv: -len(kv[1])):
    if len(lst) >= 5:
        print(f"  {term}: {len(lst)}/{len(DRUGS)} -> {[d for d,_ in lst]}")

print("\n== per-drug top-1 and total of top-5 ==")
tot_all = 0
for drug in DRUGS:
    d = [r for r in rows if r["drug"] == drug]
    d.sort(key=lambda r: r["rank"])
    s = sum(r["report_count"] for r in d)
    tot_all += s
    print(f"{drug:<18} top1={d[0]['reaction_term']:<45} {d[0]['report_count']:>7}  sum5={s:>7}")

print("\ntotal across all 50 cells:", tot_all)
counts = [r["report_count"] for r in rows]
print("min count:", min(counts), "max count:", max(counts))

print("\n== unique-to-one-drug terms ==")
for term, lst in sorted(term_drugs.items(), key=lambda kv: kv[0]):
    if len(lst) == 1:
        print(f"  {term} -> {lst[0][0]} ({lst[0][1]})")

print("\n== rank-1 terms ==")
for r in rows:
    if r["rank"] == 1:
        print(f"  {r['drug']:<18} {r['reaction_term']:<45} {r['report_count']}")