"""Q5b holder-growth SELL overlay (A3 DC3.4 ruler). Based on Q5 overlays on the Second Window wide trades (DC Analyst 1, 7 Oct 2026). Prereg: dc-prereg.md "Q5 overlays".
Low memory: per pool indexed reads from v3hist/v3live (swaps, liq) and /opt/tw/an1/xfer.db (token transfers, A1 fetch). Read only."""
import sqlite3, csv, json, bisect, datetime, random, statistics, collections
BKK = datetime.timezone(datetime.timedelta(hours=7)); TICK = 500
def ts(s): return datetime.datetime.strptime(s, "%Y-%m-%d %H:%M:%S").replace(tzinfo=BKK).timestamp()
PM = "0x8366a39cc670b4001a1121b8f6a443a643e40951"; ZERO = "0x" + "0" * 40
HUB = {PM, ZERO, "0x8876789976decbfcbbbe364623c63652db8c0904", "0x204faca1764b154221e35c0d20abb3c525710498",
       "0x6aa80dbbed9ae5ab45fbf61f9644fada3b29326e", "0x36dc95f1f088e11c0066dc19173f745655002bc2", "0x65050a9b7e5075a2ba5ced7b1b64ee66262c40dc",
       "0x039ec98a76f111092d4751365ff09dd2aec301e8", "0x0da5899dbb36bd741885d4228f04cd9c6304fc56", "0x000000000022d473030f116ddee9f6b43ac78ba3",
       "0x000000000000000000000000000000000000dead", "0xcaf681a66d020601342297493863e78c959e5cb2", "0xf2a277490c6e1d511e236c64895a0fef39aa8098"}
T_TR = "0xddf252ad1be2c89b69c2b068fc378daa952ba7f163c4a11628f55a4df523b3ef"
LQ = sqlite3.connect("file:/opt/pricepredict-data/liqlock.db?mode=ro", uri=True)
BT = LQ.execute("SELECT k, t, spb FROM btime ORDER BY k").fetchall(); BTK = [r[0] * 1000 for r in BT]; BTT = [r[1] for r in BT]
def btime(b):
    i = max(0, bisect.bisect_right(BTK, b) - 1); k, t0, spb = BT[i]; return t0 + (b - k * 1000) * (spb or 0.1013)
def blk(t):
    i = max(0, bisect.bisect_right(BTT, t) - 1); k, t0, spb = BT[i]; return int(k * 1000 + (t - t0) / (spb or 0.1013))
DBS = [sqlite3.connect("file:/opt/sw/%s.db?mode=ro" % n, uri=True, timeout=60) for n in ("v3hist", "v3live")]
RAW = sqlite3.connect("file:/opt/sw/pp3_raw.db?mode=ro", uri=True, timeout=120)
XF = sqlite3.connect("file:/opt/tw/an1/xfer.db?mode=ro", uri=True, timeout=120)   # A1 fetch, launch to entry + 48 h
KIT = {r["token"].lower(): r["kit"] for r in csv.DictReader(open("/root/dca1/sw-a2-24-kits.csv"))}
def Rfix(R, cap):
    if not R or R <= 0: R = 0.1 * cap
    return min(R, 0.5 * cap) if cap > 0 else R
def load(pool):
    sw = {}; liq = {}; creator = ""
    for c in DBS:
        r = c.execute("SELECT creator FROM pools WHERE pool=?", (pool,)).fetchone()
        if r and r[0]: creator = r[0].lower()
        for t, li, tx, side, usd, eth, cap, pe in c.execute("SELECT t, li, tx, side, usd, eth, mcap_usd, pool_eth_after FROM swaps WHERE pool=? AND mcap_usd IS NOT NULL", (pool,)):
            sw[(tx, li)] = (t, li, tx, side, usd or 0.0, eth or 0.0, cap, pe or 0.0)
        for t, li, tx, kind, eth in c.execute("SELECT t, li, tx, kind, eth FROM liq WHERE pool=? AND eth > 0", (pool,)):
            liq[(tx, li)] = (t, li, eth if kind == "add" else -eth)
    rows = sorted(sw.values()); lq = sorted(liq.values()); k = 0; cur = 0.0; pull = 0.0; ev = []
    for t, li, tx, side, usd, eth, cap, pe in rows:
        while k < len(lq) and (lq[k][0], lq[k][1]) <= (t, li):
            lt, _, de = lq[k]; k += 1
            if de < 0 and cur > 0 and -de >= 0.5 * cur and not pull: pull = float(lt)
            cur = max(0.0, cur + de)
        cur = max(0.0, cur + (eth if side == 1 else -eth)); e = usd / eth if eth > 0 else 2600.0
        ev.append(dict(t=t, tx=tx, cap=cap, buy=side == 1, eth=eth, usd=usd, sell_ok=(side != 1 and usd >= 100), R=(pe if pe else cur) * e))
    return ev, pull, creator
def value(units, x, tick):
    v = units * x["cap"]; R = Rfix(x["R"], x["cap"]); return v * R / (R + v) * 0.99 / tick
FF = sqlite3.connect("file:/opt/pricepredict-data/ffund_snap.db?mode=ro", uri=True)
DISP = {"0x84157d3d24630c297849c84d49ae7f1805c1b60f", "0x03802649e469ed5680c58dff9e65425e22d21cc5"}
ZS = {ZERO, "0x000000000000000000000000000000000000dead"}
ffc = {}
def ff(a):
    if a not in ffc: ffc[a] = FF.execute("select kind, funder from ff where addr=?", (a,)).fetchone()
    return ffc[a]
EDGE, EDGE2, EDGE_H = 528, 69, 494
trades = []
for r in csv.DictReader(open("/root/dca1/sw-a2-16-wide-trades.csv")):
    trades.append(dict(book="SW", pool=r["pool"].lower(), tok=r["token"].lower(), half=r["half"], te=ts(r["entry_time_bkk"]), tx=ts(r["exit_time_bkk"]), exit=r["exit"], tick=500, their_x=float(r["x_500_stop03"])))
for r in csv.DictReader(open("/root/dca1/fwrows.csv")):
    trades.append(dict(book="FW", pool=r["pool"].lower(), tok=r["token"].lower(), half=r["half"], te=int(r["T0"]) + 306, tx=int(r["t300"]), exit=r["e300"], tick=300, their_x=float(r["x300"])))
def growth(T, creator, bc, bp):
    """A3 dc34v2 ruler at block bc: outside holders (loose) and plain holders at bc minus at bp (bp = bc - 50 min)."""
    E = [x for x in T if x[3] <= bc]
    if not E: return 0, 0
    deg = collections.defaultdict(set)
    for f, to, v, b, h in E: deg[f].add(to); deg[to].add(f)
    hubc = {}
    def hub(a):
        if a not in hubc:
            r = ff(a); hubc[a] = a in ZS or len(deg.get(a, ())) >= 50 or (r is not None and r[0] == "contract")
        return hubc[a]
    b0 = E[0][3]; bundle = {creator} if creator else set(); bal = collections.defaultdict(int); snap = None
    for f, to, v, b, h in E:
        if snap is None and b > bp: snap = dict(bal)
        bal[f] -= v; bal[to] += v
        if hub(to): continue
        if b - b0 <= 30: bundle.add(to)
        elif f in bundle and not hub(f): bundle.add(to)
    if snap is None: snap = dict(bal)
    crf = (ff(creator) or (None, None))[1] if creator else None
    bad = DISP | {creator} | ({crf.lower()} if crf else set())
    def count(bb):
        ol = 0; hp = 0
        for a, v in bb.items():
            if v <= 0 or hub(a): continue
            hp += 1
            if a in bundle: continue
            r = ff(a)
            if r is None or (r[1] or "").lower() not in bad: ol += 1
        return ol, hp
    o1, h1 = count(bal); o0, h0 = count(snap)
    return o1 - o0, h1 - h0
res = []; random.seed(7)
for tr in trades:
    pool, tok, te, tx_, tick = tr["pool"], tr["tok"], tr["te"], tr["tx"], tr["tick"]
    ev, pull, creator = load(pool)
    if not ev: print("no swaps", tr["book"], pool, flush=True); continue
    TS = [x["t"] for x in ev]; j = bisect.bisect_left(TS, te)
    if j >= len(ev): continue
    e0 = ev[j]; R0 = Rfix(e0["R"], e0["cap"]); units = tick * 0.99 * R0 / (R0 + tick) / e0["cap"]
    pullt = pull or 9e18
    def sell_at(t):
        k = bisect.bisect_left(TS, t)
        while k < len(ev) and ev[k]["t"] < pullt:
            if ev[k]["sell_ok"]: return value(units, ev[k], tick)
            k += 1
        return 0.0
    base = 0.0 if tr["exit"] == "pull" else sell_at(tx_)
    T = [(a, b_, int(d), b, txh) for a, b_, d, b, txh in
         XF.execute("SELECT frm, too, val, b, tx FROM tr WHERE token=? AND b<=? ORDER BY b, li", (tok, blk(te + 4 * 3600 + 60)))]
    r = dict(book=tr["book"], token=tok, pool=pool, half=tr["half"], exit=tr["exit"], their_x=tr["their_x"], base=round(base, 4), transfers=len(T), checks=[])
    for name, dt in (("15m", 900), ("1h", 3600), ("4h", 14400)):
        c = te + dt
        if c >= tx_ or c >= pullt: continue
        g, gh = growth(T, creator, blk(c), blk(c - 3000))
        r["checks"].append(dict(check=name, growth=g, hgrowth=gh, sell_value=round(sell_at(c), 4)))
    res.append(r); print(tr["book"], tok, tr["exit"], len(T), [(c["check"], c["growth"]) for c in r["checks"]], flush=True)
json.dump(res, open("/root/dca1/q5b.json", "w"), indent=1)
TICKS = {"SW": 500, "FW": 300}
def score(sel, key, edge, book=None):
    out = {}
    for h in ("A", "B"):
        R = [r for r in res if r["half"] == h and (book is None or r["book"] == book)]
        add = []; slots = {}
        for r in R:
            t = TICKS[r["book"]]
            for c in r["checks"]: slots.setdefault(r["token"] + r["book"] + str(r["base"]), []).append(t * (c["sell_value"] - r["base"]))
            f = next((c for c in r["checks"] if c[key] >= edge), None)
            if f: add.append(t * (f["sell_value"] - r["base"]))
        beat = None
        if add and len(slots) >= len(add):
            m = statistics.mean(add); ks = list(slots); w = 0
            for _ in range(10000):
                pick = random.sample(ks, len(add)); w += m > statistics.mean([random.choice(slots[k]) for k in pick])
            beat = round(w / 10000, 3)
        out[h] = dict(trades=len(R), with_checks=sum(1 for r in R if r["checks"]), fired=len(add), usd_added=round(sum(add)), mean_added=round(statistics.mean(add), 1) if add else None, beats_random=beat)
    return out
summ = {"bar_rule_pooled": score(None, "growth", EDGE), "bar_rule_SW": score(None, "growth", EDGE, "SW"), "bar_rule_FW": score(None, "growth", EDGE, "FW"),
        "sens_top_half_pooled": score(None, "growth", EDGE2), "sens_plain_holders_pooled": score(None, "hgrowth", EDGE_H),
        "growth_dist": sorted(c["growth"] for r in res for c in r["checks"])[::max(1, len([1 for r in res for c in r["checks"]]) // 20)]}
json.dump(summ, open("/root/dca1/q5b_summary.json", "w"), indent=1); print(json.dumps(summ, indent=1))