import json, subprocess, math, sys, os
HERE=os.path.dirname(os.path.abspath(__file__))
sys.argv += ["test"]; which = sys.argv[1]
cfg = {"test":"/home/openclaw/eqserver-test/eqemu_config.json","prod":"/home/openclaw/eqserver/eqemu_config.json"}[which]
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]
icols=[c[0] for c in q("select column_name from information_schema.columns where table_schema=database() and table_name='items' order by ordinal_position")]
def item(i):
    r=q(f"select * from items where id={i}"); return dict(zip(icols,r[0])) if r else None
EP=[60332,20076,48147,62649,48136,62880,67742,77640,52348,57405,64067,16576,19839,52962,57054,18609]
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
SP={"clickeffect","focuseffect","worneffect","proceffect"}
bad=0; spmap={}
for k,b in enumerate(EP):
    for t in range(3):
        s=item(b+t*300000); n=item(150161+k+t*300000)
        if not n: print("MISSING",150161+k+t*300000); bad+=1; continue
        for c in icols:
            if c=="id": continue
            if c=="Name": exp="Fabled "+s[c]
            elif c in SC: exp=str(int(math.floor(int(s[c])*SC[c]+0.5)))
            elif c in ("attuneable","nodrop"): exp="1"
            elif c in ("comment","clickname","focusname","wornname","procname"): continue
            elif c in SP:
                if int(s[c])>0:
                    spmap.setdefault(int(s[c]),set()).add(int(n[c])); continue
                exp=s[c]
            else: exp=s[c]
            if n[c]!=exp: print("DIFF",n["id"],c,"src",s[c],"got",n[c],"exp",exp); bad+=1
# every spell slot maps 1:1 and points at a "Fabled <src>" spell
for src,news in spmap.items():
    assert len(news)==1, (src,news); nid=list(news)[0]
    a=q(f"select name from spells_new where id={src}")[0][0]; b=q(f"select name from spells_new where id={nid}")
    if not b or b[0][0]!="Fabled "+a: print("SPELL NAME", src, nid, b); bad+=1
# spells in DB == staged client rows
rows={int(l.split("^",1)[0]):l.rstrip("\n") for l in open(os.path.join(HERE,"spells_rows.txt"))}
for sid,l in rows.items():
    r=q(f"select * from spells_new where id={sid}")
    if not r or "^".join(r[0])!=l: print("SPELL ROW != client row",sid); bad+=1
print("kit",q("select id,Name,bagtype,bagslots,bagsize,itemclass,nodrop,price,loregroup from items where id=150177"))
print("vendor",q("select merchantid,slot,item from merchantlist where item=150177"))
print("recipes",q("select count(*),sum(nofail),min(tradeskill),max(tradeskill) from tradeskill_recipe where id between 31700 and 31715"),
      q("select count(*) from tradeskill_recipe_entries where recipe_id between 31700 and 31715"))
print("pct in new text", q("select count(*) from items where (id between 150161 and 150177 or id between 450161 and 450176 or id between 750161 and 750176) and (Name like '%\\%%' or lore like '%\\%%')"),
      q("select count(*) from spells_new where id between 43100 and 43140 and name like '%\\%%'"))
print("generated fabled untouched", q("select count(*) from items where id in (926819,921448,926741)"))
print("BAD", bad)