import re, json, html, sys
txt=open('/home/claude/drive/supplements/zouiouich2025_suppl.txt').read()
lines=txt.split('\n')
starts={}
for i,l in enumerate(lines):
    m=re.match(r'\s*Supplementary Table (\d+)\.',l)
    if m: starts[int(m.group(1))]=i
order=sorted(starts.items(), key=lambda x:x[1])
sec={}
for j,(t,i) in enumerate(order):
    end=order[j+1][1] if j+1<len(order) else len(lines)
    sec[t]=lines[i+1:end]
VAL=re.compile(r'^(\d+\.\d+)\s*\(\s*(\d+\.\d+)\s*,\s*(\d+\.\d+)\s*\)$')
def cells(t):
    s='\n'.join(sec[t])
    s=html.unescape(s)
    c=[re.sub(r'\s+',' ',x).strip() for x in s.split('|')]
    return c
COLS=['Red','Red Unprocessed','Red Processed','White','White Unprocessed','White Processed','Fish','Poultry','Processed']
SITE={'Colorectal':'colorectal','Proximal':'proximal','Cecum':'cecum','Ascending Colon':'ascending','Hepatic Flexure':'hepatic','Transverse Colon':'transverse','Distal':'distal','Splenic Flexure':'splenic','Descending Colon':'descending','Sigmoid Colon':'sigmoid','Rectal':'rectal','Rectosigmoid Junction':'rectosigmoid','Rectum':'rectum'}
def site_norm(x):
    x=x.strip()
    for k,v in SITE.items():
        if x.lower()==k.lower(): return v
    return None
recs=[]
def rowtables(t, ncases=False, lag=False):
    c=cells(t)
    # find start after last header col 'Processed'
    i=max(k for k in range(60) if c[k]=='Processed')+1
    site=None; group=None; cases=None
    period='overall'
    while i<len(c):
        x=c[i]
        if lag and x.startswith(('Overall','0-5','5-10','10+','0 -','Follow')) or (lag and 'N =' in x):
            period=x; i+=1; continue
        if VAL.match(x):
            vals=[]
            while i<len(c) and VAL.match(c[i]) and len(vals)<9:
                vals.append(VAL.match(c[i]).groups()); i+=1
            assert len(vals)==9,(t,site,group,vals,c[i])
            for col,v in zip(COLS,vals):
                recs.append(dict(table=t,site=site,group=group if not lag else period,cases=cases,col=col,point=v[0],lo=v[1],hi=v[2],q=None))
            group=None; cases=None
            continue
        if x=='' : i+=1; continue
        sn=site_norm(x)
        if sn: site=sn; i+=1; continue
        if re.fullmatch(r'\d+',x) and ncases: cases=x; i+=1; continue
        if x.startswith('HR (95%') or x.startswith('1') and 'Adjusted' in x: i+=1; continue
        group=x; i+=1
    return
rowtables(1); rowtables(2); rowtables(8,ncases=True); rowtables(9,ncases=True); rowtables(10,lag=True)
# table 3
c=cells(3); i=max(k for k in range(40) if c[k]=='Processed')+1; site=None
while i<len(c):
    x=c[i]
    if VAL.match(x):
        vals=[VAL.match(c[i+k]).groups() for k in range(9)]; i+=9
        for col,v in zip(COLS,vals):
            recs.append(dict(table=3,site=site,group='Overall',cases=None,col=col,point=v[0],lo=v[1],hi=v[2],q=None))
        continue
    sn=site_norm(x)
    if sn: site=sn
    i+=1
# tables 4,5
QS=['colorectal','proximal','cecum','ascending','hepatic','transverse','distal','splenic','descending','sigmoid','rectal','rectosigmoid','rectum']
MT={'Total Red':'Red','Red Unprocessed':'Red Unprocessed','Red Processed':'Red Processed','Total White':'White','White Unprocessed':'White Unprocessed','White Processed':'White Processed','Fish':'Fish','Poultry':'Poultry','Total Processed':'Processed'}
for t in (4,5):
    c=cells(t); i=c.index('Rectum')+1; meat=None
    while i<len(c):
        x=c[i]
        if x in MT: meat=MT[x]; i+=1; continue
        if re.fullmatch(r'Q[1-5]|P trend',x):
            vals=c[i+1:i+14]; i+=14
            for s,v in zip(QS,vals):
                m=VAL.match(v)
                if x=='P trend':
                    recs.append(dict(table=t,site=s,group='Overall',cases=None,col=meat,q='ptrend',point=v,lo=None,hi=None))
                elif m: recs.append(dict(table=t,site=s,group='Overall',cases=None,col=meat,q=x,point=m.group(1),lo=m.group(2),hi=m.group(3)))
                else: recs.append(dict(table=t,site=s,group='Overall',cases=None,col=meat,q=x,point=v,lo=None,hi=None))
            continue
        i+=1
json.dump(recs,open(sys.argv[1],'w'),indent=0)
import collections
print(collections.Counter((r['table'],r['group']) for r in recs))
print(collections.Counter((r['table'],r['site']) for r in recs if r['table'] in (1,9,10)))