#!/usr/bin/env python3
"""Fabled 2.0 epics generator (2026-10-26). Reads Biggsvr_test (+ checks Biggsvr), writes:
  out/2026-10-26_fabled_epics.sql, out/spells_rows.txt (new spells_us lines), out/dbstr_rows.txt, out/summary.json"""
import json, subprocess, os, math, sys
OUT = "/tmp/claude-1000/fab/out/"; os.makedirs(OUT, exist_ok=True)
def conn(cfg):
    db = json.load(open(cfg))["server"]["database"]
    def q(sql):
        r = subprocess.run(["mysql","-h",db.get("host","127.0.0.1"),"-u",db["username"],db["db"],"-N","-B","--raw","-e",sql],
                           capture_output=True,text=True,env={"MYSQL_PWD":db["password"],"PATH":"/usr/bin:/bin"})
        if r.returncode: raise SystemExit(r.stderr)
        return [l.split("\t") for l in r.stdout.rstrip("\n").split("\n") if l != ""]
    return q
T = conn("/home/openclaw/eqserver-test/eqemu_config.json"); P = conn("/home/openclaw/eqserver/eqemu_config.json")
STRIDE = 300000
# class, 2.0 base id (from the 2.0 quest rewards / auto_journal final steps), Fabled base id
EPICS = [("Warrior",60332),("Cleric",20076),("Paladin",48147),("Ranger",62649),("Shadow Knight",48136),("Druid",62880),
         ("Monk",67742),("Bard",77640),("Rogue",52348),("Shaman",57405),("Necromancer",64067),("Wizard",16576),
         ("Magician",19839),("Enchanter",52962),("Beastlord",57054),("Berserker",18609)]
FAB0 = 150161; KIT = 150177; SP0 = 43100
RECIPE0 = 31700
def rnd(x): return int(math.floor(abs(x) + 0.5)) * (1 if x >= 0 else -1)

icols = [c[0] for c in T("select column_name from information_schema.columns where table_schema=database() and table_name='items' order by ordinal_position")]
scols = [c[0] for c in T("select column_name from information_schema.columns where table_schema=database() and table_name='spells_new' order by ordinal_position")]
assert len(scols) == 237
def item(q, i):
    r = q(f"select * from items where id={i}")
    return dict(zip(icols, r[0])) if r else None
def spell(q, i):
    r = q(f"select * from spells_new where id={i}")
    return dict(zip(scols, r[0])) if r else None

# ---------- id checks
for k in range(len(EPICS)):
    for t in range(4):
        i = FAB0 + k + t*STRIDE
        assert item(T, i) is None and item(P, i) is None, ("item id used", i)
assert item(T, KIT) is None and item(P, KIT) is None
for q in (T, P):
    assert q(f"select count(*) from tradeskill_recipe where id between {RECIPE0} and {RECIPE0+15}")[0][0] == "0"
    assert q(f"select count(*) from tradeskill_recipe_entries where recipe_id between {RECIPE0} and {RECIPE0+15}")[0][0] == "0"
    assert q(f"select count(*) from merchantlist where merchantid=151932 and (slot=22 or item={KIT})")[0][0] == "0"

# ---------- spells: closure of effect spells + referenced spells
REF_SPA = {85, 323, 333, 419, 340, 374, 360, 361, 365, 406, 383, 339, 386, 387, 481}
LIMIT_SPA = {134,135,136,137,138,139,140,141,142,143,311,348,385,411,414,404,412,413,415,422,423,460,486,487,493,494,495,496,497,498,499}
SCALE = {  # spa -> fields to scale x1.5 ('b' base, 'l' limit, 'm' max)
    0:"bm", 1:"b", 2:"b", 4:"b", 5:"b", 6:"b", 7:"b", 8:"b", 9:"b", 15:"b", 46:"b", 47:"b", 48:"b", 49:"b", 50:"b",
    55:"b", 59:"b", 69:"b", 79:"b", 97:"b", 100:"b", 114:"b", 120:"b", 124:"l", 125:"l", 126:"l", 130:"b", 147:"bm",
    152:"b", 169:"b", 172:"b", 174:"b", 175:"b", 177:"b", 178:"b", 180:"b", 184:"b", 192:"b", 197:"b", 214:"b",
    216:"b", 258:"b", 262:"b", 273:"b", 294:"b", 296:"l", 330:"b"}
KEEP = {10, 21, 64, 153, 154, 310, 254}   # placeholder / stun duration / balance / dispel / reuse reduction: unchanged
F = {"b": "effect_base_value%d", "l": "effect_limit_value%d", "m": "max%d"}

epic_rows = {}
for cls, b in EPICS:
    rows = [item(T, b + t*STRIDE) for t in range(3)]
    assert all(rows), (cls, b)
    for t in range(3):
        assert rows[t]["Name"] == rows[0]["Name"] + ("", " (Enchanted)", " (Legendary)")[t], rows[t]["Name"]
        for f in ("clickeffect","focuseffect","worneffect","proceffect"):
            assert rows[t][f] == rows[0][f], (cls, t, f)
        pr = item(P, b + t*STRIDE); dif = [c for c in icols if pr[c] != rows[t][c]]
        if dif: print("prod differs", b + t*STRIDE, [(c, rows[t][c], pr[c]) for c in dif])
    epic_rows[b] = rows

order = []; seen = set()
def visit(sid):
    if sid in seen or sid <= 0: return
    seen.add(sid); order.append(sid)
    s = spell(T, sid); assert s, sid
    for i in range(1, 13):
        if int(s[f"effectid{i}"]) in REF_SPA: visit(int(s[f"effect_base_value{i}"]))
    if int(s["RecourseLink"]) > 0: visit(int(s["RecourseLink"]))
for cls, b in EPICS:
    for f in ("clickeffect","focuseffect","worneffect","proceffect"):
        v = int(epic_rows[b][0][f])
        if v > 0: visit(v)
newid = {sid: SP0 + n for n, sid in enumerate(order)}
for n in newid.values():
    for q in (T, P):
        assert spell(q, n) is None, ("spell id used", n)
        assert q(f"select count(*) from spells_new where descnum={n}")[0][0] == "0", ("descnum used", n)

main_spells = set()
for cls, b in EPICS:
    for f in ("clickeffect","focuseffect","worneffect"):
        v = int(epic_rows[b][0][f])
        if v > 0: main_spells.add(v)

sql = []; spell_rows = {}; spell_changes = {}; unknown = []
def sq(v): return "'" + v.replace("\\", "\\\\").replace("'", "''") + "'"
for sid in order:
    s = spell(T, sid); assert spell(P, sid) == s, ("prod spell differs", sid)
    n = dict(s); sets = {}; ch = []
    n["id"] = str(newid[sid]); n["name"] = "Fabled " + s["name"]; sets["name"] = sq(n["name"])
    assert len(n["name"]) <= 64 and "%" not in n["name"] and "^" not in n["name"]
    for i in range(1, 13):
        spa = int(s[f"effectid{i}"])
        if spa in REF_SPA:
            old = int(s[f"effect_base_value{i}"]); k = f"effect_base_value{i}"
            n[k] = str(newid[old]); sets[k] = n[k]; ch.append(f"slot{i} spa{spa} spell {old}->{newid[old]}")
        elif spa in SCALE:
            for fl in SCALE[spa]:
                k = F[fl] % i; old = int(s[k])
                if old == 0: continue
                new = rnd(old * 1.5)
                if spa == 152 and fl == "b": new = max(old + 1, new)
                n[k] = str(new); sets[k] = n[k]; ch.append(f"slot{i} spa{spa} {fl} {old}->{new}")
        elif spa == 310:
            # reuse-timer reduction of one discipline (spa139 limit): x1.5 but leave at least 1 minute of reuse
            lim = [int(s[f"effect_base_value{j}"]) for j in range(1, 13) if s[f"effectid{j}"] == "139"]
            assert len(lim) == 1 and lim[0] > 0
            rec = int(spell(T, lim[0])["recast_time"]); old = int(s[f"effect_base_value{i}"])
            new = min(rnd(old * 1.5), rec - 60000); assert new > old
            k = f"effect_base_value{i}"; n[k] = str(new); ch.append(f"slot{i} spa310 b {old}->{new} (disc {lim[0]} recast {rec})")
        elif spa in KEEP or spa in LIMIT_SPA:
            pass
        else:
            unknown.append((sid, s["name"], i, spa))
    if int(s["RecourseLink"]) > 0:
        n["RecourseLink"] = str(newid[int(s["RecourseLink"])]); sets["RecourseLink"] = n["RecourseLink"]
        ch.append(f"recourse {s['RecourseLink']}->{n['RecourseLink']}")
    if sid in main_spells:
        n["descnum"] = str(newid[sid]); sets["descnum"] = n["descnum"]
    spell_rows[newid[sid]] = n; spell_changes[newid[sid]] = (sid, s["name"], n["name"], ch)
if unknown: print("UNHANDLED SPAs (left unchanged):", unknown)

# ---------- items
SC = {"damage":1.75, "ac":2.18}
for c in ("astr","asta","aagi","adex","awis","aint","acha","mr","fr","cr","dr","pr","svcorruption"): SC[c] = 1.5
for c in ("heroic_str","heroic_sta","heroic_agi","heroic_dex","heroic_wis","heroic_int","heroic_cha",
          "heroic_mr","heroic_fr","heroic_cr","heroic_dr","heroic_pr","heroic_svcorrup"): SC[c] = 1.44
for c in ("attack","accuracy","spelldmg","healamt"): SC[c] = 1.88
for c in ("hp","mana","endur"): SC[c] = 2.0
fab_items = {}
for k, (cls, b) in enumerate(EPICS):
    for t in range(3):
        src = epic_rows[b][t]; fid = FAB0 + k + t*STRIDE; n = dict(src); sets = {}
        n["id"] = str(fid); n["Name"] = "Fabled " + src["Name"]; sets["Name"] = sq(n["Name"])
        assert len(n["Name"]) <= 64
        for c, m in SC.items():
            v = int(src[c]); nv = rnd(v * m)
            if nv != v: n[c] = str(nv); sets[c] = str(nv)
        for f in ("clickeffect","focuseffect","worneffect","proceffect"):
            v = int(src[f])
            if v > 0: n[f] = str(newid[v]); sets[f] = n[f]
        for f in ("clickname","focusname","wornname","procname"):
            if src[f]: n[f] = ""; sets[f] = "''"
        n["attuneable"] = "1"; sets["attuneable"] = "1"
        n["nodrop"] = "1"; sets["nodrop"] = "1"
        n["comment"] = f"Fabled 2.0 epic ({cls}) from {src['id']} x4 in Epic Reforging Kit {KIT}; 2026-10-26"; sets["comment"] = sq(n["comment"])
        fab_items[fid] = (src, n, sets)

# ---------- SQL text
L = []
L.append(f"""-- Fabled 2.0 epics (2026-10-26). Generated by /tmp/claude-1000/fab/gen.py from Biggsvr_test (source rows verified
-- identical in Biggsvr). Safe to re-run: every row is deleted by id and re-inserted.
-- Contents: {len(order)} spells {SP0}-{SP0+len(order)-1} (Fabled copies of the 2.0 click/focus/worn spells and the procs/recourses
-- they trigger), 48 items (Fabled base {FAB0}-{FAB0+15}, Enchanted +300000, Legendary +600000), the Epic Reforging Kit {KIT},
-- 16 fallback recipes {RECIPE0}-{RECIPE0+15} (4 x base epic; the Lua handler lua_modules/fabled_epics.lua normally takes
-- every kit combine first and also accepts any tier mix), and the kit on Bag_Merchant (151932, Bazaar) slot 22.
-- Items/spells load at boot (shared memory): restart the world after applying.
-- BACKUP before applying on prod:  mysqldump Biggsvr items spells_new tradeskill_recipe tradeskill_recipe_entries merchantlist
--   --where="1" is not needed; the rollback below only removes rows this file added (all ids were free on 2026-10-25).
-- ROLLBACK:
--   DELETE FROM items WHERE id IN ({KIT}) OR (id BETWEEN {FAB0} AND {FAB0+15}) OR (id BETWEEN {FAB0+STRIDE} AND {FAB0+15+STRIDE}) OR (id BETWEEN {FAB0+2*STRIDE} AND {FAB0+15+2*STRIDE});
--   DELETE FROM spells_new WHERE id BETWEEN {SP0} AND {SP0+len(order)-1};
--   DELETE FROM tradeskill_recipe_entries WHERE recipe_id BETWEEN {RECIPE0} AND {RECIPE0+15};
--   DELETE FROM tradeskill_recipe WHERE id BETWEEN {RECIPE0} AND {RECIPE0+15};
--   DELETE FROM merchantlist WHERE merchantid=151932 AND item={KIT};
-- No raw percent signs in any text.
""")
L.append("-- ===== spells")
for nid, n in spell_rows.items():
    sid, oname, nname, ch = spell_changes[nid]
    s = spell(T, sid)
    sets = {k: (sq(v) if k == "name" else v) for k, v in n.items() if v != s[k] and k != "id"}
    L.append(f"-- {nid} {nname} (copy of {sid}): " + ("; ".join(ch) or "no magnitude change"))
    L.append(f"DROP TEMPORARY TABLE IF EXISTS fe_tmp; CREATE TEMPORARY TABLE fe_tmp AS SELECT * FROM spells_new WHERE id={sid};")
    L.append(f"UPDATE fe_tmp SET id={nid}, " + ", ".join(f"`{k}`={v}" for k, v in sets.items()) + ";")
    L.append(f"DELETE FROM spells_new WHERE id={nid}; INSERT INTO spells_new SELECT * FROM fe_tmp;")
L.append("\n-- ===== items")
for fid, (src, n, sets) in fab_items.items():
    L.append(f"-- {fid} {n['Name']} (from {src['id']})")
    L.append(f"DROP TEMPORARY TABLE IF EXISTS fe_tmp; CREATE TEMPORARY TABLE fe_tmp AS SELECT * FROM items WHERE id={src['id']};")
    L.append(f"UPDATE fe_tmp SET id={fid}, " + ", ".join(f"`{k}`={v}" for k, v in sets.items()) + ";")
    L.append(f"DELETE FROM items WHERE id={fid}; INSERT INTO items SELECT * FROM fe_tmp;")
L.append(f"""
-- ===== Epic Reforging Kit (copy of Taromani's Insignia Case 52414: itemclass 1 bag, bagtype 13, itemtype 11)
DROP TEMPORARY TABLE IF EXISTS fe_tmp; CREATE TEMPORARY TABLE fe_tmp AS SELECT * FROM items WHERE id=52414;
UPDATE fe_tmp SET id={KIT}, Name='Epic Reforging Kit', lore='Four echoes of one legend', loregroup=0, bagslots=4, bagsize=4,
  bagwr=0, bagtype=13, nodrop=1, norent=1, price=10000, sellrate=1, weight=10, size=2, classes=65535, races=65535,
  comment='Fabled 2.0 epics: 4 copies of one class 2.0 epic (any tier) -> Fabled base; Lua fabled_epics.lua; 2026-10-26';
DELETE FROM items WHERE id={KIT}; INSERT INTO items SELECT * FROM fe_tmp; DROP TEMPORARY TABLE IF EXISTS fe_tmp;

-- ===== fallback recipes (4 x base epic + kit -> Fabled base; never fails; kit is kept)""")
for k, (cls, b) in enumerate(EPICS):
    rid = RECIPE0 + k; fid = FAB0 + k
    nm = f"Fabled {epic_rows[b][0]['Name']}"
    L.append(f"DELETE FROM tradeskill_recipe_entries WHERE recipe_id={rid}; DELETE FROM tradeskill_recipe WHERE id={rid};")
    L.append(f"INSERT INTO tradeskill_recipe (id,name,tradeskill,skillneeded,trivial,nofail,replace_container,notes,must_learn,quest,enabled) "
             f"VALUES ({rid},{sq(nm)},75,0,0,1,0,'Fabled 2.0 epics fallback (Lua fabled_epics.lua handles tier mixes)',0,0,1);")
    L.append(f"INSERT INTO tradeskill_recipe_entries (recipe_id,item_id,successcount,failcount,componentcount,iscontainer) VALUES "
             f"({rid},{b},0,0,4,0),({rid},{fid},1,0,0,0),({rid},{KIT},0,0,0,1);")
L.append(f"""
-- ===== vendor: Bag_Merchant (151932, The Bazaar), next to the Utility Kit
DELETE FROM merchantlist WHERE merchantid=151932 AND item={KIT};
INSERT INTO merchantlist (merchantid,slot,item,faction_required,level_required,min_status,max_status,alt_currency_cost,classes_required,probability)
  VALUES (151932,22,{KIT},-100,0,0,255,0,65535,100);
""")
sqltxt = "\n".join(L) + "\n"
assert "%" not in sqltxt
open(OUT + "2026-10-26_fabled_epics.sql", "w").write(sqltxt)

# ---------- client rows
with open(OUT + "spells_rows.txt", "w") as f:
    for nid, n in spell_rows.items():
        line = "^".join(n[c] for c in scols); assert "%" not in line and "\n" not in line and line.count("^") == 236
        f.write(line + "\n")
src_desc = {}
for l in open("/home/openclaw/heroes_journey_workspace/patcher/staged_sympathetic/dbstr_us.txt", "rb").read().split(b"\n"):
    p = l.split(b"^", 3)
    if len(p) >= 3 and p[1] == b"6": src_desc[int(p[0])] = p[2].decode("latin-1")
with open(OUT + "dbstr_rows.txt", "w") as f:
    for nid, n in spell_rows.items():
        sid = spell_changes[nid][0]
        if n["descnum"] != str(nid): continue
        assert nid not in src_desc, ("dbstr type 6 id used", nid)
        t = src_desc.get(int(spell(T, sid)["descnum"]), "").strip()
        tail = ("Fabled: cuts even more time from that reuse timer." if "reuse timer" in t
                else "Fabled: about half again as strong as the original.")
        t = (t + "  " if t else "") + tail
        assert "%" not in t and "^" not in t
        f.write(f"{nid}^6^{t}^0\n")
summary = {"epics": [], "spells": []}
for k, (cls, b) in enumerate(EPICS):
    leg = fab_items[FAB0 + k + 2*STRIDE][1]; srcl = epic_rows[b][2]
    summary["epics"].append({"class": cls, "epic_id": b, "epic": epic_rows[b][0]["Name"], "fabled_ids": [FAB0+k, FAB0+k+STRIDE, FAB0+k+2*STRIDE],
        "leg_src": {c: srcl[c] for c in ("damage","delay","ac","hp","mana","endur","attack","spelldmg","healamt","heroic_str","astr")},
        "leg_new": {c: leg[c] for c in ("damage","delay","ac","hp","mana","endur","attack","spelldmg","healamt","heroic_str","astr")},
        "spells": {f: (srcl[f], spell(T, int(srcl[f]))["name"], leg[f], spell_rows[int(leg[f])]["name"]) for f in ("clickeffect","focuseffect","worneffect","proceffect") if int(srcl[f]) > 0}})
for nid, (sid, on, nn, ch) in spell_changes.items(): summary["spells"].append({"id": nid, "from": sid, "name": nn, "changes": ch})
json.dump(summary, open(OUT + "summary.json", "w"), indent=1)
print("spells", len(order), SP0, SP0 + len(order) - 1, "items", len(fab_items), "unknown", len(unknown))