import json
from datetime import date
from openpyxl import Workbook
from openpyxl.styles import Font
from openpyxl.utils import get_column_letter
HEADERS=["Patient Name","Patient Phone","Patient Email","Last Appt Date","Last Appt Type","Last Appt Status","Provider","Facility","Days Since Last Appt","Primary Dx","Patient ID"]
def add_sheet(wb,title,rows):
    ws=wb.create_sheet(title)
    ws.append(HEADERS)
    for c in ws[1]: c.font=Font(bold=True)
    for r in rows:
        ws.append([r['name'],r['phone'],r['email'],r['lastdate'],r['lasttype'],r['laststatus'],r['provider'],r['facility'],int(r['days']),r['dx'],r['pid']])
    if not rows and title=="Active No-Followup":
        ws.append(["(NOT POPULATED - blocked by AdvancedMD API rate-limit. See README 'Sheet 2 status' + CAVEATS. Requires a scheduled throttled backfill.)"])
    ws.freeze_panes="A2"
    widths=[22,16,28,14,20,16,24,12,12,16,12]
    for i,w in enumerate(widths,1): ws.column_dimensions[get_column_letter(i)].width=w
    return ws
s1=json.load(open('/tmp/sheet1_rows.json'))
try: s2=json.load(open('/tmp/sheet2_rows.json'))
except FileNotFoundError: s2=[]
wb=Workbook(); wb.remove(wb.active)
add_sheet(wb,"New No-Show-Cancel No-Followup",s1)
add_sheet(wb,"Active No-Followup",s2)
# README
rd=wb.create_sheet("README")
import os
s1cell=sum(1 for r in s1 if r['phone']); s1em=sum(1 for r in s1 if r['email'])
s2cell=sum(1 for r in s2 if r['phone']); s2em=sum(1 for r in s2 if r['email'])
s2note=open('/tmp/s2_status.txt').read().strip() if os.path.exists('/tmp/s2_status.txt') else ""
lines=[
("Reactivations Workbook — Exult Healthcare (AdvancedMD office 161112)",""),
("Generated","2026-06-16"),
("Purpose","CEO patient-reactivation targets. PHI — handle per HIPAA."),
("",""),
("SHEET 1 — New No-Show-Cancel No-Followup",""),
("Rule","NEW-patient appt that was No-Showed (apptstatus 11) or Cancelled (10), and the patient was NEVER scheduled for any follow-up (no later visit with apptstatus in {0,1,2,3,5,7,8,9} dated strictly after the NEW visit). Bumped(12)/Cancelled(10)/No-Show(11) do NOT count as a follow-up."),
("NEW appt types","IP-MED MGMT NEW, IP-THERAPY NEW PT, TH-MED MGMT NEW, TH-THERAPY NEW PT, IP-TMS MAPPING, IP-TMS INIT EVAL, TH-TMS INIT EVAL"),
("Window","2026-03-16 to 2026-06-15 (see caveat below)"),
("Row count",str(len(s1))),
("Contact coverage",f"phone {s1cell}/{len(s1)}, email {s1em}/{len(s1)}"),
("",""),
("SHEET 2 — Active No-Followup",""),
("Rule","Patient with >=1 KEPT visit (apptstatus in {3,5,8,9}) dated 2026-04-01..2026-06-16 who currently has NO future follow-up (no visit with apptstatus in {0,1,2,3,5,7,8,9} dated after 2026-06-16)."),
("Window","2026-04-01 to 2026-06-16"),
("Row count",str(len(s2))),
("Contact coverage",f"phone {s2cell}/{len(s2)}, email {s2em}/{len(s2)}"),
("Sheet 2 status", s2note),
("",""),
("apptstatus codes","0=Scheduled, 1=Confirmed, 3/5/8/9=Kept/Seen, 10=Cancelled, 11=No-Show, 12=Bumped"),
("",""),
("DATA SOURCES",""),
("src1","Existing report AMD-followup-report-2026-06-15.xlsx (No-Shows + Cancellations sheets, window 2026-03-16..2026-06-15) used to identify NEW-type no-show/cancel visits + patient contact info."),
("src2","AdvancedMD get_appointment_history (per candidate) used for follow-up determination."),
("src3","AdvancedMD get_updated_visits (lastmodifieddate=2026-04-01) used to enumerate kept-in-window visits for Sheet 2."),
("",""),
("CAVEATS",""),
("c1","Sheet 1 window reduced to 2026-03-16..2026-06-15 (not the full 2025-12-16..2026-06-16). Reason: the AdvancedMD MCP tools available (get_appointment_history, get_visit_info_by_date, get_updated_visits) do NOT return the appointment TYPE name, so NEW-type visits can only be identified from the existing report, which covers 3/16-6/15. The 12/16-3/15 NEW-patient backfill could not be performed."),
("c2","Primary Dx is unavailable via the permitted AMD tools and is left blank."),
("c3","Last Appt Type on Sheet 2 is blank — appt type is not exposed by the enumeration tools."),
("c4", open('/tmp/s1_caveat.txt').read().strip() if os.path.exists('/tmp/s1_caveat.txt') else ""),
]
for a,b in lines: rd.append([a,b])
for c in rd[1]: c.font=Font(bold=True)
rd.column_dimensions['A'].width=26; rd.column_dimensions['B'].width=110
wb.save('/tmp/Reactivations.xlsx')
print("workbook saved. S1",len(s1),"S2",len(s2))