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))
def piso(s): p=s.split('-'); return date(int(p[0]),int(p[1]),int(p[2]))
cands=json.load(open('/tmp/sheet1_candidate_newvisits.json'))
bypid=pickle.load(open('/tmp/existing_bypid.pkl','rb'))
STATUSNAME={10:"Cancelled",11:"No-Show"}
keep_rows=[]
missing=[]
for pid in cands:
    fp=f"/tmp/histories/{pid}.json"
    if not os.path.exists(fp):
        missing.append(pid); continue
    hist=json.load(open(fp))
    # anchor = earliest NEW no-show/cancel visit
    crows=sorted(cands[pid], key=lambda r:piso(r['date']))
    nv=crows[0]
    anchor=piso(nv['date'])
    has_fu=False
    for h in hist:
        d=pmd(h['date']); st=int(h['apptstatus'])
        if d>anchor and st in SCHED_OR_KEPT:
            has_fu=True; break
    if has_fu: continue
    # build row from existing report row (match pid + date + type)
    er=None
    for row in bypid.get(pid,[]):
        if str(row['date'])==nv['date'] and (row['type'] or '')==nv['type']:
            er=row; break
    if er is None:
        # fallback: any existing row for pid
        er=bypid.get(pid,[{}])[0] if bypid.get(pid) else {}
    days=(TODAY-anchor).days
    keep_rows.append({
        "name":er.get('name',''),"phone":er.get('phone',''),"email":er.get('email',''),
        "lastdate":anchor.strftime("%Y-%m-%d"),"lasttype":nv['type'],
        "laststatus":STATUSNAME.get(nv['statuscode'],str(nv['statuscode'])),
        "provider":nv.get('provider') or er.get('provider',''),"facility":nv.get('facility') or er.get('facility',''),
        "days":days,"dx":"","pid":pid})
keep_rows.sort(key=lambda r:r['days'], reverse=True)
json.dump(keep_rows, open('/tmp/sheet1_rows.json','w'))
# coverage
cell=sum(1 for r in keep_rows if r['phone'])
email=sum(1 for r in keep_rows if r['email'])
print("Sheet1 kept rows:",len(keep_rows),"| missing hist pids:",len(missing),missing)
print(f"coverage: phone {cell}/{len(keep_rows)}  email {email}/{len(keep_rows)}")