import json
from openpyxl import load_workbook
from openpyxl.styles import Font
from openpyxl.utils import get_column_letter

SRC = "/tmp/Reactivations_current.xlsx"
OUT = "/tmp/Reactivations_out.xlsx"
WINDOW = "2026-04-01 to 2026-06-16 (kept window); future follow-ups checked through 2026-08-15"

rows = json.load(open("/tmp/sheet2_rows_final.json"))

wb = load_workbook(SRC)

# --- Sheet 1: append any newly-qualified pending candidates (11-col schema) ---
import os as _os
s1_appended = 0
if _os.path.exists("/tmp/sheet1_append.json"):
    s1_new = json.load(open("/tmp/sheet1_append.json"))
    if s1_new:
        ws1 = wb[wb.sheetnames[0]]
        existing_pids = {ws1.cell(row=i, column=11).value for i in range(2, ws1.max_row+1)}
        for r in s1_new:
            if r["pid"] in existing_pids:
                continue
            ws1.append([r["name"], r["phone"], r["email"], r["lastdate"], r["lasttype"],
                        r["laststatus"], r["provider"], r["facility"], int(r["days"]), r["dx"], r["pid"]])
            s1_appended += 1
print("Sheet1 rows appended:", s1_appended)

# --- Sheet 2: Active No-Followup -- rebuild contents, keep sheet object/position ---
S2 = "Active No-Followup"
ws = wb[S2]
# clear all existing cells
ws.delete_rows(1, ws.max_row)

HEADERS = ["Patient Name","Phone","Email","Last Kept Appt Date","Provider","Days Since","Patient ID"]
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["provider"], int(r["days"]), r["pid"]])
ws.freeze_panes = "A2"
widths = [24,16,30,18,24,12,12]
for i,w in enumerate(widths,1):
    ws.column_dimensions[get_column_letter(i)].width = w
# clear any leftover wide columns from old 11-col layout
for i in range(8,12):
    ws.column_dimensions[get_column_letter(i)].width = 8.43

# --- README: update Sheet 2 section ---
phone_n = sum(1 for r in rows if r["phone"])
email_n = sum(1 for r in rows if r["email"])
rd = wb["README"]
# locate and update relevant rows by label in column A
updates = {
    "Window": None,  # there are two Window rows (S1 + S2); only update the S2 one (after the S2 header)
}
# Find the "SHEET 2" header row, then update the Rule/Window/Row count/Contact coverage/Sheet 2 status rows after it
s2_start = None
for i in range(1, rd.max_row+1):
    a = rd.cell(row=i, column=1).value or ""
    if isinstance(a,str) and a.startswith("SHEET 2"):
        s2_start = i
        break

if s2_start:
    for i in range(s2_start, min(s2_start+12, rd.max_row+1)):
        a = rd.cell(row=i, column=1).value or ""
        if a == "Window":
            rd.cell(row=i, column=2).value = WINDOW
        elif a == "Row count":
            rd.cell(row=i, column=2).value = str(len(rows))
        elif a == "Contact coverage":
            rd.cell(row=i, column=2).value = f"phone {phone_n}/{len(rows)}, email {email_n}/{len(rows)}"
        elif a == "Sheet 2 status":
            rd.cell(row=i, column=2).value = (
                f"POPULATED {len(rows)} rows. Method: enumerated get_visit_info_by_date for every clinic day "
                f"2026-04-01..2026-08-15 (one call/day; visit universe). Kept = apptstatus in {{3,5,8,9}} dated 4/1-6/16; "
                f"excluded any patient with a later visit apptstatus in {{0,1,2,3,5,7,8,9}} after their last kept visit "
                f"(future scheduled or kept follow-up). Sorted by Days Since desc. Contact from get_updated_patients(2026-04-01) "
                f"with the 2026-06-15 report as fallback."
            )
        elif a == "Rule":
            rd.cell(row=i, column=2).value = (
                "Patient with >=1 KEPT visit (apptstatus in {3,5,8,9}) dated 2026-04-01..2026-06-16 who has NO later visit "
                "with apptstatus in {0,1,2,3,5,7,8,9} dated after that patient's LAST kept visit (no future scheduled or kept follow-up)."
            )

wb.save(OUT)
print("saved", OUT)

# verify
wb2 = load_workbook(OUT)
ws2 = wb2["Active No-Followup"]
print("sheets:", wb2.sheetnames)
print("Sheet2 header:", [c.value for c in ws2[1]])
print("Sheet2 data rows:", ws2.max_row-1)
print("Sheet1 data rows:", wb2[wb2.sheetnames[0]].max_row-1)
print(f"coverage phone {phone_n}/{len(rows)} email {email_n}/{len(rows)}")