"""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):
    v = units * x["cap"]; R = Rfix(x["R"], x["cap"]); return v * R / (R + v) * 0.99 / TICK
res = []; random.seed(5)
for tr in csv.DictReader(open("/root/dca1/sw-a2-16-wide-trades.csv")):
    pool = tr["pool"].lower(); tok = tr["token"].lower(); te = ts(tr["entry_time_bkk"]); tx_ = ts(tr["exit_time_bkk"])
    ev, pull, creator = load(pool)
    if not ev: 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]), ev[k]["t"]
            k += 1
        return 0.0, pullt
    base = 0.0 if tr["exit"] == "pull" else sell_at(tx_)[0]
    # transfers up to entry + 48 h
    b48 = blk(te + 172800)
    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.lower(), b48))]
    MK = HUB | {pool}
    MKT = MK - {ZERO, "0x000000000000000000000000000000000000dead"}   # the mint is not a market transfer (v1 fix)
    bfirst = next((b for f, to, v, b, h in T if f in MKT and to not in MK), None)
    ins = {creator} if creator else set()
    txmap = collections.defaultdict(list)
    for f, to, v, b, h in T: txmap[h].append((f, to))
    def state(t):
        """bundle share of wallet-held supply at t, bundle net tokens to market since entry (fraction of bundle holdings at entry), outside net buy ETH in the hour before t."""
        bt = blk(t); be = blk(te); bal = collections.defaultdict(int); I = set(ins); net_out = 0; Bentry = None
        for f, to, v, b, h in T:
            if b > bt: break
            if Bentry is None and b > be: Bentry = sum(bal[w] for w in I if bal[w] > 0)
            if bfirst is not None and b <= bfirst + 30 and to not in MK: I.add(to)
            elif f in I and to not in MK and f not in MK: I.add(to)
            if b > be:
                if f in I and to in MK: net_out += v
                if f in MK and to in I: net_out -= v
            bal[f] -= v; bal[to] += v
        if Bentry is None: Bentry = sum(bal[w] for w in I if bal[w] > 0)
        hold = {w: v for w, v in bal.items() if w not in MK and v > 0}; tot = sum(hold.values()) or 1
        share = sum(v for w, v in hold.items() if w in I) / tot
        out_eth = 0.0
        for x in ev:
            if t - 3600 < x["t"] <= t:
                trs = txmap.get(x["tx"], []); who = None
                if x["buy"]:
                    frm = {f for f, _ in trs}; fin = [to for f, to in trs if to not in frm and to not in MK]; who = fin[-1] if fin else None
                else:
                    src = [f for f, to in trs if to in MK and f not in MK]; who = src[0] if src else None
                if who is not None and who not in I: out_eth += x["eth"] if x["buy"] else -x["eth"]
        return share, (net_out / Bentry if Bentry else 0.0), out_eth
    r = dict(token=tok, pool=pool, half=tr["half"], exit=tr["exit"], kit=KIT.get(tok, "?"), sw_x=float(tr["x_500_stop03"]), base=round(base, 4),
             pulled=int(pull > 0), transfers=len(T))
    # ceilings (context, perfect foresight)
    r["sell_ceiling"] = round(max([value(units, x) for x in ev[j:] if x["sell_ok"] and x["t"] <= min(tx_, pullt - 1)] or [0]), 4)
    r["hold_ceiling"] = round(max([value(units, x) for x in ev[j:] if x["sell_ok"] and tx_ < x["t"] < min(pullt, te + 172800)] or [0]), 4)
    # (a) sell-now checks
    r["checks"] = []
    for name, dt in (("15m", 900), ("1h", 3600), ("4h", 14400)):
        c = te + dt
        if c >= tx_ or c >= pullt: continue
        sh, ns, oe = state(c); v, tsold = sell_at(c)
        r["checks"].append(dict(check=name, share=round(sh, 3), netsell=round(ns, 3), out_eth=round(oe, 3), sell_value=round(v, 4),
                                fire=int(ns >= 0.10 and sh >= 0.5)))
    # (b) hold past 5x (non-kit, target exits)
    r["hold"] = None
    if tr["exit"] == "target" and r["kit"] not in ("A", "B"):
        sh, ns, oe = state(tx_)
        k = bisect.bisect_left(TS, tx_); peak = 0.0; hv = base; why = "48h"
        while k < len(ev):
            x = ev[k]
            if x["t"] >= pullt: hv, why = 0.0, "pull"; break
            if x["t"] > te + 172800: break
            if x["sell_ok"]:
                v = value(units, x); peak = max(peak, v); hv = v
                if v <= 0.5 * peak: why = "trail"; break
            k += 1
        r["hold"] = dict(share=round(sh, 3), netsell=round(ns, 3), out_eth=round(oe, 3), fire=int(ns <= 0 and oe > 0), hold_value=round(hv, 4), why=why)
    res.append(r); print(tok, r["exit"], r["kit"], len(r["checks"]), flush=True)
json.dump(res, open("/root/dca1/q5.json", "w"), indent=1)
# scoring
def usd(x): return x * TICK
out = {"trades": len(res)}
slots = [(r, c) for r in res for c in r["checks"]]
for h in ("A", "B"):
    S = [(r, c) for r, c in slots if r["half"] == h]; F = [(r, c) for r, c in S if c["fire"]]
    # one sell per trade: first firing check
    first = {}
    for r, c in F: first.setdefault(r["token"], (r, c))
    add = [usd(c["sell_value"] - r["base"]) for r, c in first.values()]
    pool_add = {}
    for r, c in S: pool_add.setdefault(r["token"], []).append(usd(c["sell_value"] - r["base"]))
    beat = None
    if add:
        m = statistics.mean(add); toks = list(pool_add); w = 0
        for _ in range(10000):
            pick = random.sample(toks, min(len(add), len(toks))); w += m > statistics.mean([random.choice(pool_add[t]) for t in pick])
        beat = round(w / 10000, 3)
    out["a_" + h] = dict(open_slots=len(S), fired_trades=len(first), usd_added=round(sum(add)), mean_added=round(statistics.mean(add), 1) if add else None, beats_random=beat)
    Hs = [r for r in res if r["half"] == h and r["hold"]]; HF = [r for r in Hs if r["hold"]["fire"]]
    addh = [usd(r["hold"]["hold_value"] - r["base"]) for r in HF]; allh = [usd(r["hold"]["hold_value"] - r["base"]) for r in Hs]
    beat = None
    if addh:
        m = statistics.mean(addh); w = 0
        for _ in range(10000): w += m > statistics.mean(random.sample(allh, len(addh)))
        beat = round(w / 10000, 3)
    out["b_" + h] = dict(candidates=len(Hs), fired=len(HF), usd_added=round(sum(addh)), hold_all_usd=round(sum(allh)), beats_random=beat)
    out["ceiling_" + h] = dict(sell_now=round(sum(max(0, usd(r["sell_ceiling"] - r["base"])) for r in res if r["half"] == h and r["exit"] != "target")),
                               hold_past=round(sum(max(0, usd(r["hold_ceiling"] - r["base"])) for r in res if r["half"] == h and r["exit"] == "target")),
                               base_usd=round(sum(usd(r["base"] - 1) for r in res if r["half"] == h)), sw_usd=round(sum(usd(r["sw_x"] - 1) for r in res if r["half"] == h)))
json.dump(out, open("/root/dca1/q5_summary.json", "w"), indent=1); print(json.dumps(out, indent=1))