"""Build delinquency & collections trend workbook for The Alowyn Homes at Centennial (res62701).
Source: Yardi ledger_transaction / unit_status_daily / tenant_balance; RealAI datamart ZIP & peer credit data.
"""
import openpyxl
from openpyxl.styles import Font, Alignment, PatternFill, Border, Side
from openpyxl.utils import get_column_letter

BLUE = Font(name="Arial", size=10, color="0000FF")
BLACK = Font(name="Arial", size=10)
GREEN = Font(name="Arial", size=10, color="008000")
BOLD = Font(name="Arial", size=10, bold=True)
HDR = Font(name="Arial", size=10, bold=True, color="FFFFFF")
HDRFILL = PatternFill("solid", fgColor="1F3864")
TITLE = Font(name="Arial", size=12, bold=True)
TOPB = Border(top=Side(style="thin"))

# mo, tenants_billed_rent, rent_charges, total_charges, concessions, payments, late_fees, late_tenants, atty_tenants, nsf_count, occ_units, open_ar_charges
rows = [
 ("2025-01", 50, 91968.52, 114006.77, 0.00, 115418.30, 449.70, 8, 4, 0, 177),
 ("2025-02", 59, 104055.88, 129486.34, -4000.00, 132692.03, 551.55, 7, 7, 0, 178),
 ("2025-03", 60, 110571.23, 131519.88, -1500.00, 133147.74, 832.85, 9, 8, 0, 178),
 ("2025-04", 61, 112326.13, 135531.81, 0.00, 132205.71, 516.70, 6, 6, 0, 178),
 ("2025-05", 67, 118024.60, 142606.00, -5088.00, 150868.21, 1082.05, 13, 12, 0, 181),
 ("2025-06", 71, 126960.67, 153820.61, -4500.00, 152662.72, 799.65, 10, 7, 1, 180),
 ("2025-07", 73, 135782.00, 162284.66, -2295.00, 153096.48, 722.15, 8, 7, 1, 179),
 ("2025-08", 76, 138819.26, 171637.76, 0.00, 182093.98, 758.25, 11, 10, 0, 177),
 ("2025-09", 82, 146906.77, 179443.04, -7267.00, 174795.79, 842.76, 11, 7, 2, 179),
 ("2025-10", 88, 161140.82, 194633.97, -7786.50, 204237.67, 1300.08, 14, 12, 1, 173),
 ("2025-11", 96, 174148.29, 200310.61, -17345.00, 197104.46, 1403.69, 15, 12, 0, 178),
 ("2025-12", 104, 191575.74, 230172.33, -8998.00, 222503.02, 1443.90, 16, 10, 1, 180),
 ("2026-01", 113, 201091.46, 246049.92, -6997.00, 254855.50, 1315.75, 17, 12, 0, 180),
 ("2026-02", 120, 217312.18, 261571.25, -5454.00, 260733.63, 1186.85, 16, 8, 0, 180),
 ("2026-03", 124, 227076.06, 271202.05, -9031.50, 274138.55, 1443.50, 18, 13, 1, 180),
 ("2026-04", 131, 239612.57, 281588.46, -14503.50, 286863.63, 1420.65, 16, 14, 1, 177),
 ("2026-05", 136, 251397.02, 295163.40, -12536.50, 288230.66, 1587.90, 18, 12, 1, 178),
 ("2026-06", 147, 265879.94, 266155.99, -62353.50, 295193.65, 1858.50, 22, 17, 1, 175),
 ("2026-07", 155, 284055.53, 353344.40, -4898.78, 318807.98, 1560.15, 18, 14, 1, 175),
 ("2026-08", 164, 299468.26, 335589.30, -37438.52, 337230.04, 1720.95, 24, 17, 0, 176),
 ("2026-09", 168, 311013.03, 367885.03, -20512.88, 311615.44, 2182.45, 26, 22, 0, 173),
]

wb = openpyxl.Workbook()

# ---------------- Monthly Detail ----------------
ws = wb.active
ws.title = "Monthly Detail"
ws["A1"] = "The Alowyn Homes at Centennial - Monthly Billings, Collections & Delinquency Signals"
ws["A1"].font = TITLE
ws["A2"] = "Source: Yardi ledger (property res62701). Blue = source data; black = calculated."
ws["A2"].font = Font(name="Arial", size=9, italic=True)

hdrs = ["Month", "Tenants Billed Rent (#)", "Occupied Units (#)", "Rent Charges ($)", "Total Charges, Net ($)",
        "Concessions ($)", "Payments Received ($)", "Late Fees ($)", "Tenants w/ Late Fee (#)",
        "Tenants w/ Attorney Fee (#)", "NSF Events (#)", "Collections Ratio (%)",
        "Late-Fee Incidence (%)", "Attorney-Fee Incidence (%)", "Late Fees per $1k Rent Billed ($)"]
for j, h in enumerate(hdrs, start=1):
    c = ws.cell(row=4, column=j, value=h)
    c.font = HDR; c.fill = HDRFILL; c.alignment = Alignment(wrap_text=True, vertical="center", horizontal="center")
ws.row_dimensions[4].height = 46

r0 = 5
for i, d in enumerate(rows):
    r = r0 + i
    mo, tb, rent, tot, conc, pay, lf, lt, at, nsf, occ = d
    ws.cell(row=r, column=1, value=mo).font = BLUE
    for j, v in [(2, tb), (3, occ), (4, rent), (5, tot), (6, conc), (7, pay), (8, lf), (9, lt), (10, at), (11, nsf)]:
        c = ws.cell(row=r, column=j, value=v); c.font = BLUE
    ws.cell(row=r, column=12, value=f"=IF(E{r}=0,\"\",G{r}/E{r})").font = BLACK
    ws.cell(row=r, column=13, value=f"=IF(B{r}=0,\"\",I{r}/B{r})").font = BLACK
    ws.cell(row=r, column=14, value=f"=IF(B{r}=0,\"\",J{r}/B{r})").font = BLACK
    ws.cell(row=r, column=15, value=f"=IF(D{r}=0,\"\",H{r}/D{r}*1000)").font = BLACK

last = r0 + len(rows) - 1
for r in range(r0, last + 1):
    for j in (4, 5, 6, 7, 8):
        ws.cell(row=r, column=j).number_format = '$#,##0;($#,##0);-'
    for j in (2, 3, 9, 10, 11):
        ws.cell(row=r, column=j).number_format = '#,##0;(#,##0);-'
    for j in (12, 13, 14):
        ws.cell(row=r, column=j).number_format = '0.0%'
    ws.cell(row=r, column=15).number_format = '$#,##0.00;($#,##0.00);-'

widths = [10, 13, 12, 14, 15, 13, 15, 12, 13, 14, 11, 13, 13, 14, 15]
for j, w in enumerate(widths, start=1):
    ws.column_dimensions[get_column_letter(j)].width = w
ws.freeze_panes = "B5"

# ---------------- YoY Summary ----------------
w2 = wb.create_sheet("YoY Summary")
w2["A1"] = "Year-over-Year: January - August (2026 vs 2025)"
w2["A1"].font = TITLE
w2["A2"] = "Eight-month like-for-like window; September excluded (partial month, statements still open)."
w2["A2"].font = Font(name="Arial", size=9, italic=True)

# Jan-Aug 2025 = rows 5..12 ; Jan-Aug 2026 = rows 17..24
R25 = "5:12"; R26 = "17:24"
def rng(col, span):
    a, b = span.split(":")
    return f"'Monthly Detail'!{col}{a}:{col}{b}"

for j, h in enumerate(["Metric", "Jan-Aug 2025", "Jan-Aug 2026", "Change", "Change (%)"], start=1):
    c = w2.cell(row=4, column=j, value=h); c.font = HDR; c.fill = HDRFILL
    c.alignment = Alignment(horizontal="center", wrap_text=True)

# metric rows: label, formula25, formula26, fmt, pct_change?
specs = [
 ("Rent charged ($)", f"=SUM({rng('D',R25)})", f"=SUM({rng('D',R26)})", '$#,##0;($#,##0);-', True),
 ("Total charges, net of concessions ($)", f"=SUM({rng('E',R25)})", f"=SUM({rng('E',R26)})", '$#,##0;($#,##0);-', True),
 ("Payments received ($)", f"=SUM({rng('G',R25)})", f"=SUM({rng('G',R26)})", '$#,##0;($#,##0);-', True),
 ("Collections ratio (payments / net charges)", None, None, '0.0%', False),
 ("Avg tenants billed rent (#)", f"=AVERAGE({rng('B',R25)})", f"=AVERAGE({rng('B',R26)})", '#,##0.0', True),
 ("Late fees charged ($)", f"=SUM({rng('H',R25)})", f"=SUM({rng('H',R26)})", '$#,##0;($#,##0);-', True),
 ("Late fees per $1,000 of rent billed ($)", None, None, '$#,##0.00', True),
 ("Tenant-months with a late fee (#)", f"=SUM({rng('I',R25)})", f"=SUM({rng('I',R26)})", '#,##0;(#,##0);-', True),
 ("Late-fee incidence (share of billed tenants)", None, None, '0.0%', False),
 ("Tenant-months with an attorney/eviction fee (#)", f"=SUM({rng('J',R25)})", f"=SUM({rng('J',R26)})", '#,##0;(#,##0);-', True),
 ("Attorney-fee incidence (share of billed tenants)", None, None, '0.0%', False),
 ("NSF / returned-payment events (#)", f"=SUM({rng('K',R25)})", f"=SUM({rng('K',R26)})", '#,##0;(#,##0);-', True),
 ("Concessions granted ($)", f"=SUM({rng('F',R25)})", f"=SUM({rng('F',R26)})", '$#,##0;($#,##0);-', True),
 ("Concessions as % of rent charged", None, None, '0.0%', False),
]
r = 5
addr = {}
for label, f25, f26, fmt, pct in specs:
    w2.cell(row=r, column=1, value=label).font = BLACK
    addr[label] = r
    if f25:
        w2.cell(row=r, column=2, value=f25).font = GREEN
        w2.cell(row=r, column=3, value=f26).font = GREEN
    r += 1
# derived rows reference the sum rows above
def rowof(l): return addr[l]
cr = rowof("Collections ratio (payments / net charges)")
for col in ("B", "C"):
    w2[f"{col}{cr}"] = f"={col}{rowof('Payments received ($)')}/{col}{rowof('Total charges, net of concessions ($)')}"
    w2[f"{col}{cr}"].font = BLACK
lp = rowof("Late fees per $1,000 of rent billed ($)")
for col in ("B", "C"):
    w2[f"{col}{lp}"] = f"={col}{rowof('Late fees charged ($)')}/{col}{rowof('Rent charged ($)')}*1000"
    w2[f"{col}{lp}"].font = BLACK
li = rowof("Late-fee incidence (share of billed tenants)")
for col in ("B", "C"):
    w2[f"{col}{li}"] = f"={col}{rowof('Tenant-months with a late fee (#)')}/({col}{rowof('Avg tenants billed rent (#)')}*8)"
    w2[f"{col}{li}"].font = BLACK
ai = rowof("Attorney-fee incidence (share of billed tenants)")
for col in ("B", "C"):
    w2[f"{col}{ai}"] = f"={col}{rowof('Tenant-months with an attorney/eviction fee (#)')}/({col}{rowof('Avg tenants billed rent (#)')}*8)"
    w2[f"{col}{ai}"].font = BLACK
ci = rowof("Concessions as % of rent charged")
for col in ("B", "C"):
    w2[f"{col}{ci}"] = f"=-{col}{rowof('Concessions granted ($)')}/{col}{rowof('Rent charged ($)')}"
    w2[f"{col}{ci}"].font = BLACK

lastm = r - 1
for label, f25, f26, fmt, pct in specs:
    rr = rowof(label)
    w2.cell(row=rr, column=2).number_format = fmt
    w2.cell(row=rr, column=3).number_format = fmt
    w2.cell(row=rr, column=4, value=f"=C{rr}-B{rr}").font = BLACK
    w2.cell(row=rr, column=4).number_format = fmt if "%" not in fmt else '0.0%'
    if pct:
        w2.cell(row=rr, column=5, value=f"=IF(B{rr}=0,\"\",C{rr}/B{rr}-1)").font = BLACK
    else:
        w2.cell(row=rr, column=5, value=f"=IF(B{rr}=0,\"\",C{rr}/B{rr}-1)").font = BLACK
    w2.cell(row=rr, column=5).number_format = '0.0%'

w2.column_dimensions["A"].width = 46
for col in ("B", "C", "D", "E"):
    w2.column_dimensions[col].width = 15

note = lastm + 2
w2.cell(row=note, column=1, value="Note: the ledger's billed-tenant count rose from 50 (Jan-2025) to 164 (Aug-2026) while occupied units held at 173-181 of 185, so ledger coverage - not lease-up - drives dollar growth. Rate metrics (incidence, ratios, per-$1k) are the comparable basis; dollar totals are not.").font = Font(name="Arial", size=9, italic=True)
w2.cell(row=note, column=1).alignment = Alignment(wrap_text=True)
w2.row_dimensions[note].height = 44

# ---------------- AR Aging ----------------
w3 = wb.create_sheet("AR Aging")
w3["A1"] = "Open Receivables Aging - as of 24-Sep-2026"
w3["A1"].font = TITLE
w3["A2"] = "Open (unapplied) charges in the Yardi ledger, by age of charge. Blue = source data."
w3["A2"].font = Font(name="Arial", size=9, italic=True)
for j, h in enumerate(["Aging Bucket", "Rent ($)", "Other Charges ($)", "Late/Attorney Fees ($)", "Total ($)", "% of Total"], start=1):
    c = w3.cell(row=4, column=j, value=h); c.font = HDR; c.fill = HDRFILL
    c.alignment = Alignment(horizontal="center", wrap_text=True)
# bucket, rent, other, fees  (other = all non-rent, non-fee open charges incl. term fee and credits)
ag = [
 ("0-30 days", 21773.00, 4630.11, 1397.90),
 ("31-60 days", 5545.00, 1091.31, 302.25),
 ("61-90 days", 4190.00, 776.82, 259.50),
 ("90+ days", 0.00, 75.00, 119.75),
]
for i, (b, rent, oth, fee) in enumerate(ag):
    r = 5 + i
    w3.cell(row=r, column=1, value=b).font = BLUE
    for j, v in [(2, rent), (3, oth), (4, fee)]:
        w3.cell(row=r, column=j, value=v).font = BLUE
    w3.cell(row=r, column=5, value=f"=SUM(B{r}:D{r})").font = BLACK
tr = 9
w3.cell(row=tr, column=1, value="Total open AR").font = BOLD
for j in range(2, 6):
    c = w3.cell(row=tr, column=j, value=f"=SUM({get_column_letter(j)}5:{get_column_letter(j)}8)")
    c.font = BOLD; c.border = TOPB
for r in range(5, 9):
    w3.cell(row=r, column=6, value=f"=E{r}/$E${tr}").font = BLACK
w3.cell(row=tr, column=6, value=f"=E{tr}/$E${tr}").font = BOLD
w3.cell(row=tr, column=6).border = TOPB
for r in range(5, tr + 1):
    for j in range(2, 6):
        w3.cell(row=r, column=j).number_format = '$#,##0;($#,##0);-'
    w3.cell(row=r, column=6).number_format = '0.0%'

w3.cell(row=11, column=1, value="Tenant balance check (Yardi tenant_balance, 24-Sep-2026)").font = BOLD
chk = [("Residents with a debit balance (#)", 64, '#,##0'),
       ("Residents on the ledger (#)", 176, '#,##0'),
       ("Share of residents with a balance owing", None, '0.0%'),
       ("Gross debit balances ($)", 48187.99, '$#,##0'),
       ("Largest single resident balance ($)", 13331.52, '$#,##0'),
       ("Gross AR as % of one month's rent billed (Sep-26)", None, '0.0%')]
r = 12
for label, v, fmt in chk:
    w3.cell(row=r, column=1, value=label).font = BLACK
    if v is not None:
        w3.cell(row=r, column=2, value=v).font = BLUE
    w3.cell(row=r, column=2).number_format = fmt
    r += 1
w3["B14"] = "=B12/B13"; w3["B14"].font = BLACK
w3["B17"] = "=B15/'Monthly Detail'!D25"; w3["B17"].font = GREEN
w3.column_dimensions["A"].width = 48
for col in ("B", "C", "D", "E", "F"):
    w3.column_dimensions[col].width = 15

# ---------------- Comp Benchmark ----------------
w4 = wb.create_sheet("Comp Benchmark")
w4["A1"] = "Comp-Set Benchmark - Tenant Credit Stress (no peer rent ledgers available)"
w4["A1"].font = TITLE
w4["A2"] = "Peer AR ledgers are not available, so the comp read uses consumer-credit past-due rates for the subject ZIP and for peer rentals in the submarket (RealAI SuperCensus, credit data as of 30-Jun-2026)."
w4["A2"].font = Font(name="Arial", size=9, italic=True)
w4["A2"].alignment = Alignment(wrap_text=True)
w4.row_dimensions[2].height = 28

w4["A4"] = "ZIP 89084 quarterly credit trend"; w4["A4"].font = BOLD
for j, h in enumerate(["Quarter End", "Past-Due Rate (%)", "Avg Past-Due Balance ($)", "Avg Accounts Past Due (#)", "Avg FICO", "Sample (adults)"], start=1):
    c = w4.cell(row=5, column=j, value=h); c.font = HDR; c.fill = HDRFILL
    c.alignment = Alignment(horizontal="center", wrap_text=True)
zt = [("2024-09-30", 0.0475, 163.46, 0.1687, 699.25, 21274),
      ("2025-03-31", 0.0439, 173.58, 0.1582, 700.24, 22890),
      ("2025-06-30", 0.0486, 179.81, 0.1690, 700.67, 22975),
      ("2025-09-30", 0.0619, 228.22, 0.2214, 700.72, 23370),
      ("2025-12-31", 0.0640, 220.38, 0.2291, 699.54, 23388),
      ("2026-03-31", 0.0651, 244.18, 0.2338, 698.98, 23532),
      ("2026-06-30", 0.0710, 263.70, 0.2619, 697.89, 23615)]
for i, row in enumerate(zt):
    r = 6 + i
    for j, v in enumerate(row, start=1):
        w4.cell(row=r, column=j, value=v).font = BLUE
    w4.cell(row=r, column=2).number_format = '0.0%'
    w4.cell(row=r, column=3).number_format = '$#,##0'
    w4.cell(row=r, column=4).number_format = '0.00'
    w4.cell(row=r, column=5).number_format = '#,##0'
    w4.cell(row=r, column=6).number_format = '#,##0'
r = 13
w4.cell(row=14, column=1, value="Change, Jun-2025 to Jun-2026 (past-due rate)").font = BLACK
w4.cell(row=14, column=2, value="=B12-B8").font = BLACK
w4.cell(row=14, column=2).number_format = '0.0%'
w4.cell(row=15, column=1, value="Relative increase in past-due rate").font = BLACK
w4.cell(row=15, column=2, value="=B12/B8-1").font = BLACK
w4.cell(row=15, column=2).number_format = '0.0%'

w4["A17"] = "Nearest large peer rentals (submarket), tenant past-due rate"; w4["A17"].font = BOLD
for j, h in enumerate(["Property", "ZIP", "Units (#)", "Year Built", "Tenant Past-Due Rate (%)", "Avg FICO", "Median HHI ($)"], start=1):
    c = w4.cell(row=18, column=j, value=h); c.font = HDR; c.fill = HDRFILL
    c.alignment = Alignment(horizontal="center", wrap_text=True)
peers = [
 ("Centennial at 5th Apartments", "89084", 428, "2009", 0.1216, 611.10, 78784),
 ("The Presidio by Picerne", "89084", 580, "2007", 0.1294, 621.92, 83449),
 ("The Preserve by Picerne", "89086", 455, "2007", 0.1400, 604.26, 40966),
 ("MAA Desert Vista", "89086", 380, "2008", 0.1100, 625.11, 40436),
 ("Destinations Alexander", "89032", 472, "2002", 0.0691, 676.93, 69888),
 ("Tribeca North Apartment Homes", "89032", 312, "2009", 0.2002, 575.53, 55456),
]
for i, row in enumerate(peers):
    r = 19 + i
    for j, v in enumerate(row, start=1):
        w4.cell(row=r, column=j, value=v).font = BLUE
    w4.cell(row=r, column=3).number_format = '#,##0'
    w4.cell(row=r, column=5).number_format = '0.0%'
    w4.cell(row=r, column=6).number_format = '#,##0'
    w4.cell(row=r, column=7).number_format = '$#,##0'
mr = 25
w4.cell(row=mr, column=1, value="Peer median").font = BOLD
for j, fmt in [(3, '#,##0'), (5, '0.0%'), (6, '#,##0'), (7, '$#,##0')]:
    c = w4.cell(row=mr, column=j, value=f"=MEDIAN({get_column_letter(j)}19:{get_column_letter(j)}24)")
    c.font = BOLD; c.number_format = fmt; c.border = TOPB
w4.cell(row=mr, column=1).border = TOPB
w4.cell(row=26, column=1, value="Subject ZIP 89084 (latest)").font = BLACK
w4.cell(row=26, column=5, value="=B12").font = GREEN
w4.cell(row=26, column=5).number_format = '0.0%'
w4.cell(row=26, column=7, value=118872).font = BLUE
w4.cell(row=26, column=7).number_format = '$#,##0'
w4.cell(row=27, column=1, value="Subject median household income is well above every peer, and the subject's own property-level credit sample is not published; the ZIP series is the closest available benchmark.").font = Font(name="Arial", size=9, italic=True)
w4.cell(row=27, column=1).alignment = Alignment(wrap_text=True)
w4.row_dimensions[27].height = 28
w4.column_dimensions["A"].width = 44
for col in ("B", "C", "D", "E", "F", "G"):
    w4.column_dimensions[col].width = 16

for sheet in wb.worksheets:
    for row in sheet.iter_rows():
        for c in row:
            if c.font is None or c.font.name is None:
                c.font = BLACK

wb.save("output/alowyn_delinquency_collections.xlsx")
print("saved")

cd /vercel/sandbox && python3 -c "
b={'0-30':{'rnta':21773.00,'fees':1097.90+300},'31-60':{'rnta':5545.00,'fees':277.25+25},'61-90':{'rnta':4190.00,'fees':209.50+50},'90+':{'rnta':0,'fees':94.75+25}}
o={'0-30':[4590,1483.68,1270.75,898.84,790.29,698.66,632.66,625,470.27,464.50,335,280,218.33,141.25,50,45,25.75,-1000,-2588.65],
'31-60':[265.00,262.25,160.00,130.00,106.15,103.57,75.00,75.00,54.93,42.75,40.00,16.95,15.00,4.71],
'61-90':[225.00,80.00,75.19,75.00,73.11,70.00,65.00,50.00,40.80,40.00,27.98,11.30,3.44],
'90+':[40.00,35.00]}
tot=0
for k in b:
  s=sum(o[k]); print(k, round(b[k]['rnta'],2), round(s,2), round(b[k]['fees'],2), round(b[k]['rnta']+s+b[k]['fees'],2)); tot+=b[k]['rnta']+s+b[k]['fees']
print('total',round(tot,2))
"

cd /vercel/sandbox && mkdir -p output && python3 - <<'EOF'
import re
p='build_delinquency_workbook.py'
s=open(p).read()
s=s.replace(""" ("0-30 days", 21773.00, 4630.11, 1397.90),
 ("31-60 days", 5545.00, 1091.31, 302.25),
 ("61-90 days", 4190.00, 776.82, 259.50),
 ("90+ days", 0.00, 75.00, 119.75),""",
""" ("0-30 days", 21773.00, 9431.33, 1397.90),
 ("31-60 days", 5545.00, 1351.31, 302.25),
 ("61-90 days", 4190.00, 836.82, 259.50),
 ("90+ days", 0.00, 75.00, 119.75),""")
open(p,'w').write(s)
EOF
python3 build_delinquency_workbook.py && python3 skills/xlsx/scripts/workbook_integrity_scan.py output/alowyn_delinquency_collections.xlsx --json output/integrity.json; echo "EXIT:$?"; python3 -c "
import json;d=json.load(open('output/integrity.json'))
for f in d.get('findings',[]):
    print(f.get('severity'),f.get('category'),f.get('sheet'),str(f.get('cells'))[:160],str(f.get('message'))[:180])
"

cd /vercel/sandbox && python3 - <<'EOF'
import openpyxl
from openpyxl.styles import Font
f='output/alowyn_delinquency_collections.xlsx'
wb=openpyxl.load_workbook(f)
w2=wb['YoY Summary']
for c in ('A6','B6','C6','D6','E6'):
    w2[c].font=Font(name='Arial',size=10,bold=True, color=w2[c].font.color)
w2.row_dimensions[20].height=92
w4=wb['Comp Benchmark']
w4.row_dimensions[2].height=44
w4.row_dimensions[27].height=44
wb.save(f)
EOF
python3 skills/xlsx/scripts/recalc.py output/alowyn_delinquency_collections.xlsx

cd /vercel/sandbox && python3 -c "
import openpyxl
wb=openpyxl.load_workbook('output/alowyn_delinquency_collections.xlsx',data_only=True)
w=wb['YoY Summary']
for r in range(5,19):
    print(r,[w.cell(row=r,column=c).value for c in range(1,6)])
print('---AR')
a=wb['AR Aging']
for r in range(5,18):
    print(r,[a.cell(row=r,column=c).value for c in range(1,7)])
print('---comp')
c=wb['Comp Benchmark']
for r in [14,15,25,26]:
    print(r,[c.cell(row=r,column=i).value for i in range(1,8)])
print('---monthly last')
m=wb['Monthly Detail']
for r in [5,12,17,24,25]:
    print(r,[m.cell(row=r,column=i).value for i in (1,12,13,14,15)])
"

cd /vercel/sandbox && python3 - <<'EOF'
import openpyxl
from openpyxl.styles import Font, PatternFill, Alignment
f='output/alowyn_delinquency_collections.xlsx'
wb=openpyxl.load_workbook(f)
w=wb['YoY Summary']
B=Font(name='Arial',size=10,bold=True); K=Font(name='Arial',size=10); G=Font(name='Arial',size=10,color='008000')
HDR=Font(name='Arial',size=10,bold=True,color='FFFFFF'); F=PatternFill('solid',fgColor='1F3864')
w['A23']='Most recent quarter (Jul-Sep) - is the trend worsening inside 2026?'; w['A23'].font=B
for j,h in enumerate(['Metric','Jul-Sep 2025','Jul-Sep 2026','Change'],start=1):
    c=w.cell(row=24,column=j,value=h); c.font=HDR; c.fill=F; c.alignment=Alignment(horizontal='center',wrap_text=True)
rows=[('Tenants billed rent (tenant-months)', "=SUM('Monthly Detail'!B11:B13)", "=SUM('Monthly Detail'!B23:B25)", '#,##0'),
      ('Tenant-months with a late fee (#)', "=SUM('Monthly Detail'!I11:I13)", "=SUM('Monthly Detail'!I23:I25)", '#,##0'),
      ('Late-fee incidence (%)', None, None, '0.0%'),
      ('Tenant-months with an attorney/eviction fee (#)', "=SUM('Monthly Detail'!J11:J13)", "=SUM('Monthly Detail'!J23:J25)", '#,##0'),
      ('Attorney-fee incidence (%)', None, None, '0.0%'),
      ('Late fees per $1,000 of rent billed ($)', None, None, '$#,##0.00')]
r=25
for label,f25,f26,fmt in rows:
    w.cell(row=r,column=1,value=label).font=K
    if f25:
        w.cell(row=r,column=2,value=f25).font=G; w.cell(row=r,column=3,value=f26).font=G
    r+=1
for col in ('B','C'):
    w[f'{col}27']=f'={col}26/{col}25'; w[f'{col}27'].font=K
    w[f'{col}29']=f'={col}28/{col}25'; w[f'{col}29'].font=K
w['B30']="=SUM('Monthly Detail'!H11:H13)/SUM('Monthly Detail'!D11:D13)*1000"; w['B30'].font=G
w['C30']="=SUM('Monthly Detail'!H23:H25)/SUM('Monthly Detail'!D23:D25)*1000"; w['C30'].font=G
for i,(label,f25,f26,fmt) in enumerate(rows):
    rr=25+i
    w.cell(row=rr,column=2).number_format=fmt; w.cell(row=rr,column=3).number_format=fmt
    w.cell(row=rr,column=4,value=f'=C{rr}-B{rr}').font=K
    w.cell(row=rr,column=4).number_format=fmt
w.cell(row=31,column=1,value='Sep-2026 is billed through 23-Sep; payment posting for the month is incomplete, so the September collections ratio understates.').font=Font(name='Arial',size=9,italic=True)
w.cell(row=31,column=1).alignment=Alignment(wrap_text=True); w.row_dimensions[31].height=30
wb.save(f)
EOF
python3 skills/xlsx/scripts/workbook_integrity_scan.py output/alowyn_delinquency_collections.xlsx >/dev/null; echo "scan:$?"; python3 skills/xlsx/scripts/recalc.py output/alowyn_delinquency_collections.xlsx && python3 -c "
import openpyxl
w=openpyxl.load_workbook('output/alowyn_delinquency_collections.xlsx',data_only=True)['YoY Summary']
for r in range(25,31): print(r,[w.cell(row=r,column=c).value for c in range(1,5)])
"