import sys, json, sqlite3, collections; sys.path.insert(0, "/root/fwa1")
from common import *
cw = json.load(open(W + "/crew_wallets.json"))
crewset = set(); dist = set([MA, MBC])
for r, d in cw.items():
    crewset.add(r)
    for k, v in d.items():
        if isinstance(v, list):
            crewset.update(a.lower() for a in v)
            if k == "distributors": dist.update(a.lower() for a in v)
L, Wt = load_crew(); crewset.update(c.lower() for p, b, tx, c, *_ in L if c)
ws = sqlite3.connect("file:/opt/pricepredict-data/wash.db?mode=ro", uri=True)
rep = set(r[0].lower() for r in ws.execute("select distinct wallet from rep"))
ff = {a: (f or "").lower() for a, f in sqlite3.connect("file:/opt/pricepredict-data/ffund.db?mode=ro", uri=True).execute("select addr, funder from ff")}
coin = {t: [cr, b0, pull, p10] for t, cr, b0, pull, p10 in ws.execute("select token, crew, b0, pull_b, p10 from coin where fee=100 and day>='2026-09-21' and crew like '0x%'")}
pos = {}; firstpx = {}
wcoins = collections.defaultdict(set)
for tok, b, side, eth, px, w in ws.execute("select token, b, side, eth, px, wallet from sw where day>='2026-09-21' order by b"):
    if tok not in coin: continue
    if px and tok not in firstpx: firstpx[tok] = (b, px)
    if not w: continue
    w = w.lower(); wcoins[w].add(tok)
    p = pos.setdefault((tok, w), [None, 0, None, None])
    if side == 1 and p[0] is None: p[0] = b; p[1] = eth; p[2] = px
    if side == -1 and p[0] is not None and p[3] is None: p[3] = b
# generic army: wallets in 5+ coins of one crew with 90%+ share; funders of 10+ such-crew wallets at 80%+
army = set()
wcrew = {}
for w, ts in wcoins.items():
    cc = collections.Counter(coin[t][0] for t in ts)
    cr, n = cc.most_common(1)[0]; wcrew[w] = (cr, n, n / len(ts))
    if n >= 5 and n / len(ts) >= 0.9: army.add(w)
fund_w = collections.defaultdict(list)
for w in wcoins:
    f = ff.get(w)
    if f: fund_w[f].append(w)
for f, ws_ in fund_w.items():
    if len(ws_) >= 10:
        cc = collections.Counter(wcrew[w][0] for w in ws_)
        cr, n = cc.most_common(1)[0]
        if n >= 10 and n / len(ws_) >= 0.8: dist.add(f)
print("army by repeat", len(army), "distributors", len(dist))
R = []
for (tok, w), (fb, fe, px, fs) in pos.items():
    if fb is None or not px: continue
    cr, b0, pull, p10 = coin[tok]
    base = p10 or (firstpx.get(tok, (0, None))[1])
    if not base: continue
    f = ff.get(w, "")
    if w in crewset or w in army or f in dist: cls = "crew"
    elif f in crewset: continue
    else: cls = "out"
    age = (fb - b0) / 10
    if age < 0 or age > 300 or (pull is not None and pull <= fb): continue
    if fs is not None and fs - fb < 100: continue
    mult = px / base
    cell = (0 if age < 20 else 1 if age < 60 else 2 if age < 120 else 3, 0 if mult < 1 else 1 if mult < 2 else 2 if mult < 3 else 3 if mult < 5 else 4)
    R.append((cr, cls, fe, cell, pull is not None and pull - fb <= 600, w, tok))
own = collections.defaultdict(lambda: [0, 0]); pool_ = collections.defaultdict(lambda: [0, 0])
for cr, cls, fe, cell, pu, w, tok in R:
    if cls == "out" and fe < 0.002:
        own[(cr,) + cell][0] += 1; own[(cr,) + cell][1] += pu; pool_[cell][0] += 1; pool_[cell][1] += pu
B = [(0.0055, 0.013, "$20"), (0.013, 0.028, "$50"), (0.028, 0.056, "$100"), (0.013, 0.2, "$50-500")]
out = collections.defaultdict(lambda: [0, 0, 0.0, set(), set()])
for cr, cls, fe, cell, pu, w, tok in R:
    if cls != "out": continue
    for lo, hi, nm in B:
        if lo <= fe < hi:
            c = own.get((cr,) + cell)
            if not c or c[0] < 5: c = pool_[cell]
            if c[0] < 5: continue
            o = out[(cr, nm)]; o[0] += 1; o[1] += pu; o[2] += c[1] / c[0]; o[3].add(w); o[4].add(tok)
crews = sorted(set(k[0] for k in out), key=lambda c: -out[(c, "$50-500")][0])
print("crew | $20 n/pulled/exp | $50 | $100 | $50-500 n/pulled/exp, wallets, coins | lift")
res = {}
for c in crews:
    row = []
    for nm in ("$20", "$50", "$100", "$50-500"):
        n, p, e, wl, tl = out.get((c, nm), [0, 0, 0, set(), set()])
        row.append("%d/%d/%.1f" % (n, p, e) + (" w%d c%d" % (len(wl), len(tl)) if nm == "$50-500" else ""))
        res[c + "|" + nm] = [n, p, e, len(wl), len(tl)]
    n, p, e, *_ = res[c + "|$50-500"]
    print(c, "|", " | ".join(row), "| %.1fx" % (p / e) if e else "| -")
json.dump(res, open(W + "/t11b.json", "w"))