"""AN4.3: funder root for every FW mc5 / SW live book coin; count those on the frozen good-root list. Read only."""
import json, csv, sqlite3
GOOD = {"0x28bd039474d6942d8ad4ec07d57a0e98a1c58e45", "0x92a0849f9b1e9e02363a4bd8d74a317fa6ddae92", "0x4757fc41278bd0f1b028c6dae71d381dc4558d98",
        "0x2c2572da570646b402b9b9e625aa102803904a49", "0x37e51b3694e7cdc9a4f0c6ad4031e1b55eb94cf4", "0x847091a657ff98a37213246d343def3e3988945c"}
IDX = json.load(open("/opt/sw/harness/gold/idx.json"))
CR = sqlite3.connect("file:/opt/pricepredict-data/crew.db?mode=ro", uri=True)
FF = sqlite3.connect("file:/opt/pricepredict-data/ffund_snap.db?mode=ro", uri=True)
def chain(a, depth=6):
    seen = [a]
    for _ in range(depth):
        r = CR.execute("SELECT funder FROM wallet WHERE addr=?", (a,)).fetchone() or FF.execute("SELECT funder FROM ff WHERE addr=?", (a,)).fetchone()
        if not r or not r[0]: break
        a = r[0].lower(); seen.append(a)
    return seen
out = []
for book, fn in (("sw_live", "/root/fwa3/lab/out/sw_accept/live_book_trades.csv"), ("fw_mc5", "/root/fwa3/lab/out/sw/fw_mc5_trades.csv")):
    for r in csv.DictReader(open(fn)):
        pool = r["pool"].lower(); cr = (IDX.get(pool, {}).get("creator") or "").lower()
        lr = CR.execute("SELECT root FROM launch WHERE creator=? AND root IS NOT NULL LIMIT 1", (cr,)).fetchone()
        ch = chain(cr) if cr else []
        roots = set(ch) | ({lr[0].lower()} if lr and lr[0] else set())
        out.append(dict(book=book, tok=r["tok"], half=r["half"], creator=cr, launch_root=lr[0] if lr else "", chain="|".join(ch), good=int(bool(roots & GOOD))))
with open("/opt/tw/an4/an43_roots.csv", "w") as f:
    w = csv.DictWriter(f, fieldnames=list(out[0])); w.writeheader(); w.writerows(out)
for b in ("sw_live", "fw_mc5"):
    s = [o for o in out if o["book"] == b]
    print(b, "trades", len(s), "creator known", sum(1 for o in s if o["creator"]), "with launch root", sum(1 for o in s if o["launch_root"]),
          "funder chain >1", sum(1 for o in s if "|" in o["chain"]), "GOOD ROOT", sum(o["good"] for o in s))