import json, pickle, os
from datetime import date
TODAY=date(2026,6,16); SCHED_OR_KEPT={0,1,2,3,5,7,8,9}
def pmd(s): m,d,y=s.split('/'); return date(int(y),int(m),int(d))
kept=json.load(open('/tmp/s2_kept.json'))
bypid=pickle.load(open('/tmp/existing_bypid.pkl','rb'))
# build lookup of existing report rows by (pid,date_iso) for type/contact
exist_visits=pickle.load(open('/tmp/existing_visits.pkl','rb'))
exist_lookup={}
for r in exist_visits:
    exist_lookup.setdefault(r['pid'],[]).append(r)
rows_out=[]; missing=[]
for pid,info in kept.items():
    fp=f"/tmp/histories_s2/{pid}.json"
    if not os.path.exists(fp): missing.append(pid); continue
    hist=json.load(open(fp))
    last_kept=pmd(info['date'])
    has_future=any(pmd(h['date'])>TODAY and int(h['apptstatus']) in SCHED_OR_KEPT for h in hist)
    if has_future: continue
    # contact from existing report if present
    name=info['name']; phone=''; email=''; facility=''; lasttype=''
    er=bypid.get(pid)
    if er:
        name=er[0].get('name') or name; phone=er[0].get('phone','') ; email=er[0].get('email','')
        facility=er[0].get('facility','')
    # try type match on last kept date
    for v in exist_lookup.get(pid,[]):
        if str(v['date'])==info['date'].replace('/','-') if False else False:
            pass
    days=(TODAY-last_kept).days
    rows_out.append({"name":name,"phone":phone,"email":email,
        "lastdate":last_kept.strftime("%Y-%m-%d"),"lasttype":lasttype,"laststatus":"Kept",
        "provider":info['provider'],"facility":facility,"days":days,"dx":"","pid":pid})
rows_out.sort(key=lambda r:r['days'],reverse=True)
json.dump(rows_out, open('/tmp/sheet2_rows.json','w'))
cell=sum(1 for r in rows_out if r['phone']); email=sum(1 for r in rows_out if r['email'])
print("Sheet2 rows:",len(rows_out),"missing hist:",len(missing))
print(f"coverage phone {cell}/{len(rows_out)} email {email}/{len(rows_out)}")