mkdir -p /vercel/sandbox/output && cat << 'EOF' > /vercel/sandbox/build_comps.py
import openpyxl
from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
from openpyxl.utils import get_column_letter
from openpyxl.chart import BarChart, Reference
from openpyxl.formatting.rule import CellIsRule

wb = Workbook()

# ---------- Styles ----------
NAVY = "1F3864"
LIGHT_BLUE = "D9E2F3"
GREY = "F2F2F2"
WHITE = "FFFFFF"
FONT_NAME = "Arial"

title_font = Font(name=FONT_NAME, size=16, bold=True, color=WHITE)
header_font = Font(name=FONT_NAME, size=10, bold=True, color=WHITE)
subheader_font = Font(name=FONT_NAME, size=10, bold=True, color="000000")
body_font = Font(name=FONT_NAME, size=10, color="000000")
input_font = Font(name=FONT_NAME, size=10, color="0000FF")  # blue = hardcoded input
formula_font = Font(name=FONT_NAME, size=10, color="000000")  # black = formula
link_font = Font(name=FONT_NAME, size=10, color="008000")  # green = cross-sheet link
total_font = Font(name=FONT_NAME, size=10, bold=True, color="000000")
subject_font = Font(name=FONT_NAME, size=10, bold=True, color="000000")

header_fill = PatternFill("solid", fgColor=NAVY)
subheader_fill = PatternFill("solid", fgColor=LIGHT_BLUE)
subject_fill = PatternFill("solid", fgColor="FFF2CC")
alt_fill = PatternFill("solid", fgColor=GREY)

thin = Side(style="thin", color="B7B7B7")
border_all = Border(left=thin, right=thin, top=thin, bottom=thin)
top_border = Border(top=Side(style="thin", color="000000"))

def style_header_row(ws, row, col_start, col_end, fill=header_fill, font=header_font, height=32):
    ws.row_dimensions[row].height = height
    for c in range(col_start, col_end+1):
        cell = ws.cell(row=row, column=c)
        cell.fill = fill
        cell.font = font
        cell.alignment = Alignment(horizontal="center", vertical="center", wrap_text=True)
        cell.border = border_all

def set_col_widths(ws, widths):
    for i, w in enumerate(widths, start=1):
        ws.column_dimensions[get_column_letter(i)].width = w

MONEY = '$#,##0;($#,##0);"-"'
MONEY2 = '$#,##0.00;($#,##0.00);"-"'
PSF = '$0.00'
PCT = '0.0%;(0.0%);"-"'
NUM = '#,##0;(#,##0);"-"'
NUM1 = '#,##0.0;(#,##0.0);"-"'

# =========================================================
# SHEET 1: Cover / Assumptions
# =========================================================
ws = wb.active
ws.title = "Assumptions"
set_col_widths(ws, [4, 40, 20, 55])

ws.merge_cells("B2:D2")
ws["B2"] = "Canopy at Baybrook — Rent Comps Adjustment Analysis"
ws["B2"].font = Font(name=FONT_NAME, size=16, bold=True, color=NAVY)
ws.merge_cells("B3:D3")
ws["B3"] = "6609 Reafield Dr, Charlotte, NC 28226  |  Submarket: South Charlotte / Carmel  |  Prepared " + __import__("datetime").date.today().isoformat()
ws["B3"].font = Font(name=FONT_NAME, size=10, italic=True, color="595959")

r = 5
ws.cell(row=r, column=2, value="Subject Property Profile").font = subheader_font
ws.cell(row=r, column=2).fill = subheader_fill
ws.merge_cells(start_row=r, start_column=2, end_row=r, end_column=4)
r += 1

subj_rows = [
    ("Property Name", "Canopy at Baybrook Apartments"),
    ("Address", "6609 Reafield Dr, Charlotte, NC 28226"),
    ("Unit Count", 324),
    ("Avg Unit Size (SF)", 913),
    ("Year Built", 1986),
    ("Year Renovated", 2012),
    ("Effective Vintage Year", "=D11+0"),  # placeholder, will set formula referencing built/reno below
    ("Building Style", "Garden"),
    ("In-Place Rent (avg, $/unit)", 1357.04),
    ("In-Place Rent PSF", 1.51),
    ("Occupancy (latest)", 0.9105),
]
start_r = r
labels = ["Property Name","Address","Unit Count","Avg Unit Size (SF)","Year Built","Year Renovated",
          "Effective Vintage Year","Building Style","In-Place Rent (avg, $/unit)","In-Place Rent PSF","Occupancy (latest)"]
values = ["Canopy at Baybrook Apartments","6609 Reafield Dr, Charlotte, NC 28226",324,913,1986,2012,
          None,"Garden",1357.04,1.51,0.9105]

for i, (lab, val) in enumerate(zip(labels, values)):
    rr = start_r + i
    ws.cell(row=rr, column=2, value=lab).font = body_font
    cell = ws.cell(row=rr, column=3)
    if lab == "Effective Vintage Year":
        cell.value = f"=MAX(C{start_r+4},C{start_r+5})"
        cell.font = formula_font
    else:
        cell.value = val
        cell.font = input_font
    cell.border = border_all
    ws.cell(row=rr, column=2).border = border_all
    if lab in ("Unit Count",):
        cell.number_format = NUM
    if lab == "Avg Unit Size (SF)":
        cell.number_format = NUM
    if lab in ("Year Built","Year Renovated","Effective Vintage Year"):
        cell.number_format = "0"
    if lab == "In-Place Rent (avg, $/unit)":
        cell.number_format = MONEY
    if lab == "In-Place Rent PSF":
        cell.number_format = PSF
    if lab == "Occupancy (latest)":
        cell.number_format = PCT

EFF_VINTAGE_CELL = f"Assumptions!$C${start_r+6}"
UNIT_SIZE_CELL = f"Assumptions!$C${start_r+3}"

r = start_r + len(labels) + 2
ws.cell(row=r, column=2, value="Adjustment Rate Assumptions (editable inputs)").font = subheader_font
ws.cell(row=r, column=2).fill = subheader_fill
ws.merge_cells(start_row=r, start_column=2, end_row=r, end_column=4)
r += 1
ws.cell(row=r, column=2, value="Factor").font = header_font
ws.cell(row=r, column=3, value="Rate").font = header_font
ws.cell(row=r, column=4, value="Basis / Note").font = header_font
for c in (2,3,4):
    ws.cell(row=r, column=c).fill = subheader_fill
    ws.cell(row=r, column=c).border = border_all
r += 1
adj_start = r
adj_rows = [
    ("Age / Vintage ($ per year of effective-vintage difference)", 3.00,
     "Applied to (Subject effective vintage - Comp effective vintage); newer comp = downward adjustment"),
    ("Unit Size ($ per SF difference)", 0.80,
     "Applied to (Subject SF - Comp SF); larger comp unit = downward adjustment"),
    ("Location ($ per mile from subject)", 10.00,
     "Applied to comp's straight-line distance from subject; farther comp = upward adjustment"),
]
for lab, rate, note in adj_rows:
    ws.cell(row=r, column=2, value=lab).font = body_font
    c = ws.cell(row=r, column=3, value=rate)
    c.font = input_font
    c.number_format = MONEY2
    ws.cell(row=r, column=4, value=note).font = Font(name=FONT_NAME, size=9, italic=True, color="595959")
    for cc in (2,3,4):
        ws.cell(row=r, column=cc).border = border_all
    r += 1

AGE_RATE = f"Assumptions!$C${adj_start}"
SIZE_RATE = f"Assumptions!$C${adj_start+1}"
LOC_RATE = f"Assumptions!$C${adj_start+2}"

r += 1
ws.cell(row=r, column=2, value="Methodology").font = subheader_font
ws.cell(row=r, column=2).fill = subheader_fill
ws.merge_cells(start_row=r, start_column=2, end_row=r, end_column=4)
r += 1
method_text = [
 "Adjusted Rent = Raw In-Place Rent + Age Adjustment + Size Adjustment + Location Adjustment.",
 "Each adjustment reflects (Subject characteristic - Comp characteristic) x rate, so a comp that is superior",
 "(newer, larger, closer) is adjusted DOWN and a comp that is inferior is adjusted UP - normalizing every",
 "comp to the subject's physical and locational profile before comparing rent levels.",
 "Effective vintage = greater of year built and year renovated, for both subject and comps.",
 "Location distance is measured straight-line (miles) from the subject; all comps sit in the same submarket.",
]
for line in method_text:
    ws.cell(row=r, column=2, value=line).font = Font(name=FONT_NAME, size=9, italic=True, color="404040")
    ws.merge_cells(start_row=r, start_column=2, end_row=r, end_column=4)
    r += 1

ws.sheet_view.showGridLines = False
ws.freeze_panes = "A5"

EOF
echo "part1 written"

cat << 'EOF' >> /vercel/sandbox/build_comps.py

# =========================================================
# SHEET 3: Summary
# =========================================================
ws3 = wb.create_sheet("Summary")
set_col_widths(ws3, [34, 18, 18, 18, 40])

ws3.merge_cells("A1:E1")
ws3["A1"] = "Summary — Canopy at Baybrook vs. Adjusted Comp Set"
ws3["A1"].font = Font(name=FONT_NAME, size=14, bold=True, color=NAVY)
ws3.row_dimensions[1].height = 22

r = 3
ws3.cell(row=r, column=1, value="Metric").font = header_font
ws3.cell(row=r, column=2, value="Subject").font = header_font
ws3.cell(row=r, column=3, value="Comp Set Avg").font = header_font
ws3.cell(row=r, column=4, value="Comp Set Median").font = header_font
ws3.cell(row=r, column=5, value="Note").font = header_font
style_header_row(ws3, r, 1, 5, height=26)
r += 1

metric_rows = [
 ("Raw In-Place Rent ($/unit)", f"='Comps Adjustment Grid'!J{sr}", f"='Comps Adjustment Grid'!J{AVG_ROW}", f"='Comps Adjustment Grid'!J{MED_ROW}", "Unadjusted, as-reported", MONEY),
 ("Adjusted Rent ($/unit)", f"='Comps Adjustment Grid'!P{sr}", f"='Comps Adjustment Grid'!P{AVG_ROW}", f"='Comps Adjustment Grid'!P{MED_ROW}", "Age/size/location-normalized", MONEY),
 ("Adjusted Rent PSF ($)", f"='Comps Adjustment Grid'!Q{sr}", f"='Comps Adjustment Grid'!Q{AVG_ROW}", f"='Comps Adjustment Grid'!Q{MED_ROW}", "Adjusted rent / unit size", PSF),
 ("Unit Size (SF)", f"='Comps Adjustment Grid'!D{sr}", f"='Comps Adjustment Grid'!D{AVG_ROW}", "n/a", "Avg unit size", NUM),
]
first_metric_row = r
for lab, subj_f, avg_f, med_f, note, fmt in metric_rows:
    ws3.cell(row=r, column=1, value=lab).font = body_font
    c2 = ws3.cell(row=r, column=2, value=subj_f); c2.font = link_font; c2.number_format = fmt
    c3 = ws3.cell(row=r, column=3, value=avg_f); c3.font = link_font; c3.number_format = fmt
    if med_f != "n/a":
        c4 = ws3.cell(row=r, column=4, value=med_f); c4.font = link_font; c4.number_format = fmt
    else:
        c4 = ws3.cell(row=r, column=4, value="n/a"); c4.font = body_font
    ws3.cell(row=r, column=5, value=note).font = Font(name=FONT_NAME, size=9, italic=True, color="595959")
    for c in range(1,6):
        ws3.cell(row=r, column=c).border = border_all
        if r % 2 == 0:
            ws3.cell(row=r, column=c).fill = alt_fill
    r += 1

r += 1
ws3.cell(row=r, column=1, value="Canopy at Baybrook's Position vs. the Adjusted Set").font = subheader_font
ws3.cell(row=r, column=1).fill = subheader_fill
ws3.merge_cells(start_row=r, start_column=1, end_row=r, end_column=5)
r += 1

pos_row_start = r
adj_rent_subj_cell = f"'Comps Adjustment Grid'!P{sr}"
adj_rent_avg_cell = f"'Comps Adjustment Grid'!P{AVG_ROW}"

pos_metrics = [
 ("$ Discount / Premium to Adjusted Comp Set Avg", f"={adj_rent_subj_cell}-{adj_rent_avg_cell}", MONEY),
 ("% Discount / Premium to Adjusted Comp Set Avg", f"=({adj_rent_subj_cell}-{adj_rent_avg_cell})/{adj_rent_avg_cell}", PCT),
 ("Rank Among Comp Set (1 = highest adjusted rent)", f"=1+COUNTIF('Comps Adjustment Grid'!P{FIRST_COMP_ROW}:P{LAST_COMP_ROW},\">\"&{adj_rent_subj_cell})", NUM),
 ("Comps in Set", f"={LAST_COMP_ROW}-{FIRST_COMP_ROW}+1", NUM),
]
for lab, f, fmt in pos_metrics:
    ws3.cell(row=r, column=1, value=lab).font = body_font
    c = ws3.cell(row=r, column=2, value=f); c.font = link_font if "Comps" in f else formula_font; c.number_format = fmt
    for c_ in range(1,6):
        ws3.cell(row=r, column=c_).border = border_all
        if r % 2 == 0:
            ws3.cell(row=r, column=c_).fill = alt_fill
    r += 1

r += 1
ws3.cell(row=r, column=1, value="Takeaway").font = subheader_font
ws3.cell(row=r, column=1).fill = subheader_fill
ws3.merge_cells(start_row=r, start_column=1, end_row=r, end_column=5)
r += 1
ws3.cell(row=r, column=1,
    value="Canopy at Baybrook's raw in-place rent sits below every comp's raw figure in this set; the adjustment grid tests how much of that gap is explained by the property's older effective vintage (2012) and smaller average unit (913 SF) versus the comp set, rather than by rent itself.")
ws3.merge_cells(start_row=r, start_column=1, end_row=r, end_column=5)
ws3.cell(row=r, column=1).font = Font(name=FONT_NAME, size=10, italic=True, color="404040")
ws3.cell(row=r, column=1).alignment = Alignment(wrap_text=True, vertical="top")
ws3.row_dimensions[r].height = 40

ws3.sheet_view.showGridLines = False
ws3.freeze_panes = "A4"

# ---- Chart: raw vs adjusted rent per comp (incl subject) ----
chart_ws = ws2
chart = BarChart()
chart.type = "col"
chart.title = "Raw vs. Adjusted In-Place Rent by Property"
chart.y_axis.title = "Monthly Rent ($/unit)"
chart.x_axis.title = "Property"
chart.style = 10
chart.height = 9
chart.width = 24

cats = Reference(chart_ws, min_col=1, min_row=sr, max_row=LAST_COMP_ROW)
data_raw = Reference(chart_ws, min_col=10, min_row=hr, max_row=LAST_COMP_ROW)
data_adj = Reference(chart_ws, min_col=16, min_row=hr, max_row=LAST_COMP_ROW)
chart.add_data(data_raw, titles_from_data=True)
chart.add_data(data_adj, titles_from_data=True)
chart.set_categories(cats)
chart.series[0].graphicalProperties.solidFill = "8FAADC"
chart.series[1].graphicalProperties.solidFill = "1F3864"
chart_ws.add_chart(chart, f"B{tr2+3}")

wb.save("output/Canopy_at_Baybrook_Rent_Comps.xlsx")
print("saved")
EOF
cd /vercel/sandbox && python3 build_comps.py

cd /vercel/sandbox && sed -i \
  -e 's/Font(name=FONT_NAME, size=9, italic=True, color="595959")/Font(name=FONT_NAME, size=10, italic=True, color="595959")/g' \
  -e 's/Font(name=FONT_NAME, size=9, italic=True, color="404040")/Font(name=FONT_NAME, size=10, italic=True, color="404040")/g' \
  build_comps.py
grep -n 'size=9' build_comps.py

cd /vercel/sandbox && python3 - << 'EOF'
with open('build_comps.py') as f:
    c = f.read()

c = c.replace(
'''    else:
        cell.font = formula_font
    cell.alignment = Alignment(horizontal="center", vertical="center")
ws2.cell(row=sr, column=1).alignment = Alignment(horizontal="left", vertical="center")
ws2.cell(row=sr, column=2).alignment = Alignment(horizontal="left", vertical="center")''',
'''    else:
        cell.font = formula_font
    cell.alignment = Alignment(horizontal="right", vertical="center")
ws2.cell(row=sr, column=1).alignment = Alignment(horizontal="left", vertical="center")
ws2.cell(row=sr, column=2).alignment = Alignment(horizontal="left", vertical="center")''')

c = c.replace(
'''    for c in range(1, 18):
        cell = ws2.cell(row=row, column=c)
        cell.border = border_all
        if c not in (1,2):
            cell.alignment = Alignment(horizontal="center", vertical="center")
        if row % 2 == 0:
            cell.fill = alt_fill''',
'''    for c in range(1, 18):
        cell = ws2.cell(row=row, column=c)
        cell.border = border_all
        if c not in (1,2):
            cell.alignment = Alignment(horizontal="right", vertical="center")
        if row % 2 == 0:
            cell.fill = alt_fill''')

with open('build_comps.py','w') as f:
    f.write(c)
print("done")
EOF

cd /vercel/sandbox && python3 build_comps.py && python3 skills/xlsx/scripts/workbook_integrity_scan.py output/Canopy_at_Baybrook_Rent_Comps.xlsx --json /tmp/scan2.json; echo EXIT:$?
python3 -c "
import json
d=json.load(open('/tmp/scan2.json'))
from collections import Counter
print(Counter([f['severity'] for f in d.get('findings',[])]))
for f in d['findings']:
    if f['severity']=='high':
        print(f['category'], f.get('sheet'), f.get('cell'))
"

cd /vercel/sandbox && python3 skills/xlsx/scripts/recalc.py output/Canopy_at_Baybrook_Rent_Comps.xlsx

cd /vercel/sandbox && python3 -c "
import openpyxl
wb = openpyxl.load_workbook('output/Canopy_at_Baybrook_Rent_Comps.xlsx', data_only=True)
ws2 = wb['Comps Adjustment Grid']
for row in ws2.iter_rows(min_row=3, max_row=16, min_col=1, max_col=17):
    vals = [c.value for c in row]
    print(vals)
print('---SUMMARY---')
ws3 = wb['Summary']
for row in ws3.iter_rows(min_row=1, max_row=13, min_col=1, max_col=5):
    print([c.value for c in row])
"