import openpyxl
from datetime import datetime

path = "template_clean.xlsx"
wb = openpyxl.load_workbook(path, data_only=False)

A = wb["Assumptions"]
DB = wb["Dev Budget"]
RC = wb["Rent Comps"]
SC = wb["Sales Comps"]

def setc(ws, cell, value):
    ws[cell] = value

# ---------------- ASSUMPTIONS: scalars ----------------
scalars = {
    "C5": "Marvell Rock Hill \u2014 Multifamily (246 Units)",
    "C6": "2114 Riverchase Boulevard",
    "C7": "Rock Hill, SC 29732",
    "C8": "Multifamily",
    "C9": "Per Unit",
    "C10": 246,
    "C11": 280100,          # GBA (SF) - ai_estimate, ~87% efficiency vs NRA
    "C12": 13.0,            # Site Area (Acres) - MF-only development pad within 42.14-ac master site, ai_estimate
    "C14": 369,             # Parking spaces - MF-only allocation (~1.5/unit) of the 392 site-wide total
    # Land & acquisition
    "C24": "Land",
    # Timing & exit
    "C35": datetime(2026, 10, 1),
    "C36": 3,
    "C37": 22,
    "C38": "Pace",
    "C39": 15,
    "C40": None,
    "C41": "S-Curve",
    "C42": 4,
    "C43": 0.95,
    "C44": "Hold to Year N",
    "C45": 10,
    "C46": 0,
    # Dev cost drivers
    "C59": 0.035,
    "C62": 0.05,
    "C63": 0.04,
    # Exit / disposition
    "C66": 0.054,
    "C67": 0.02,
    "C68": 0.0515,
    # Construction financing - senior
    "C75": 0.60,
    "C76": "SOFR + Spread",
    "C78": None,
    "C79": 0.0374,
    "C80": 300,
    "C81": 0,
    "C83": 0.01,
    "C84": 50000,
    "C85": 0,
    "C86": "Pari Passu",
    # Mezzanine (construction) - disabled
    "C98": "No",
    "C100": None,
    "C101": None,
    "C102": None,
    # Permanent financing
    "C108": "Lesser of LTV & DSCR",
    "C110": 0.65,
    "C111": 1.25,
    "C112": 0.0582,
    "C113": 6,
    "C114": 30,
    "C115": 0.01,
    # Perm mezzanine - disabled
    "C126": "No",
    "C128": None,
    "C129": None,
    "C130": None,
    "C131": None,
    # Waterfall
    "C140": "Yes",
    "C142": 0.10,
    "C146": 0.08,
    "C147": 0.12,
    "C148": 0.20,
    "C149": 0.18,
    "C150": 0.30,
    "C151": 0.40,
    # Growth (col H)
    "H28": "No",
    "H31": 0.03,
    "H32": 0.03,
    "H33": 0.033,
    "H34": 1,
    # Stabilized operations (col H)
    "H37": 0.01,
    "H38": 0.01,
    "H39": 0.03,
    "H40": 0.02,
    "H41": 96,
    "H42": 250,
    "H43": 150,
    # Leasing, turnover & recoveries (col H)
    "H46": "Gross (No Recovery)",
    "H49": None,
    "H52": 0.25,
    "H53": 1500,
    "H55": None, "H56": None, "H57": None, "H58": None, "H59": None, "H60": None, "H61": None,
}
for cell, val in scalars.items():
    setc(A, cell, val)

# ---------------- ASSUMPTIONS: Unit Mix (rows 164-173) ----------------
unit_mix_rows = [
    ("1 Bedroom / 1 Bath", 98, 750, 1450, None, None, None, None),
    ("2 Bedroom / 2 Bath", 111, 1100, 1750, None, None, None, None),
    ("3 Bedroom / 2 Bath", 37, 1300, 2050, None, None, None, None),
]
for i, r in enumerate(range(164, 174)):
    if i < len(unit_mix_rows):
        vals = unit_mix_rows[i]
        A.cell(r, 2, vals[0])   # B Unit Type
        A.cell(r, 3, vals[1])   # C Count
        A.cell(r, 4, vals[2])   # D Size SF
        A.cell(r, 5, vals[3])   # E Rent
        A.cell(r, 6, vals[4])   # F Lease Start
        A.cell(r, 7, vals[5])   # G Term
        A.cell(r, 8, vals[6])   # H Free Rent
        A.cell(r, 9, vals[7])   # I Escalation
    else:
        for c in range(2, 10):
            A.cell(r, c, None)

# ---------------- ASSUMPTIONS: OpEx table (rows 180-189) ----------------
opex_rows = [
    ("Real Estate Taxes", "$ / Unit / Yr", 2900, "No"),
    ("Property Insurance", "% of EGI", 0.0334, "No"),
    ("Payroll & Benefits", "% of EGI", 0.0940, "No"),
    ("Repairs & Maintenance", "% of EGI", 0.0538, "No"),
    ("Utilities (Owner-Paid)", "$ / Unit / Yr", 500, "No"),
    ("Marketing & Advertising", "% of EGI", 0.0109, "No"),
    ("General & Administrative", "% of EGI", 0.0263, "No"),
    ("Management Fee", "% of EGI", 0.0319, "No"),
    ("Other Expenses", "% of EGI", 0.0000, "No"),
]
for i, r in enumerate(range(180, 190)):
    if i < len(opex_rows):
        label, basis, val, recov = opex_rows[i]
        A.cell(r, 2, label)   # B Expense Line
        A.cell(r, 3, basis)   # C Basis
        A.cell(r, 4, val)     # D Input
        A.cell(r, 9, recov)   # I Recoverable?
    else:
        A.cell(r, 2, None); A.cell(r, 3, None); A.cell(r, 4, None); A.cell(r, 9, None)

wb.save(path)
print("Assumptions sheet written.")

import openpyxl
from datetime import datetime

path = "template_clean.xlsx"
wb = openpyxl.load_workbook(path, data_only=False)
DB = wb["Dev Budget"]
RC = wb["Rent Comps"]
SC = wb["Sales Comps"]

# ---------------- DEV BUDGET ----------------
# Clear all item rows first (B,C,D,H,I,J,K,L,M) across all 7 categories, preserve header/subtotal rows
item_rows = list(range(7,13)) + list(range(14,20)) + list(range(21,27)) + list(range(28,34)) + list(range(35,41)) + list(range(42,48)) + list(range(49,55))
for r in item_rows:
    for c in [2,3,4,8,9,10,11,12]:  # B,C,D,H,I,J,K,L
        DB.cell(r, c, None)

# Now write the specific line items (keeping existing phase/timing defaults where sensible)
def set_item(row, item, basis, input_val, escalate, phase, start_mo, length_mo, method="Straight-Line", steep=5):
    DB.cell(row, 2, item)
    DB.cell(row, 3, basis)
    DB.cell(row, 4, input_val)
    DB.cell(row, 8, escalate)
    DB.cell(row, 9, phase)
    DB.cell(row, 10, start_mo)
    DB.cell(row, 11, length_mo)
    DB.cell(row, 12, method)
    DB.cell(row, 13, steep)

# 1. Construction Costs
set_item(7, "Vertical Construction \u2014 Hard Costs", "$ per Unit", 190000, "Yes", "Construction", 1, 22, "S-Curve", 5)

# 2. Architecture & Engineering
set_item(14, "Architecture & Engineering Fees", "% of Hard Cost", 0.05, "No", "Pre-Development", 1, 3, "Straight-Line", 5)

# 3. Site Improvements (NOTE: Blythe site-work bid excludes the MF parcel - "multi-family will be bid at a later date" -
# this is a market-calibrated estimate, not the contractor bid)
set_item(21, "Sitework, Utilities & Paving (MF Pod)", "$ per Unit", 18000, "Yes", "Construction", 1, 22, "S-Curve", 5)

# 4. Soft Costs
set_item(28, "Permits, Fees & Impact Fees", "$ Total", 1200000, "No", "Pre-Development", 1, 3, "Straight-Line", 5)
set_item(29, "Legal & Closing Costs", "$ Total", 300000, "No", "Pre-Development", 1, 3, "Straight-Line", 5)
set_item(30, "Builder's Risk & Liability Insurance", "$ Total", 400000, "No", "Construction", 1, 22, "Straight-Line", 5)
set_item(31, "Property Taxes During Construction", "$ Total", 350000, "No", "Construction", 1, 22, "Straight-Line", 5)
set_item(32, "Developer Fee", "% of Total Cost", "=Assumptions!$C$63", "No", "Construction", 1, 22, "Straight-Line", 5)
set_item(33, "Marketing & Lease-Up Costs", "$ Total", 750000, "No", "Construction", 15, 8, "Straight-Line", 5)

# 5. FF&E
set_item(35, "Unit Appliances, Fixtures & Finishes", "$ Total", 400000, "No", "Construction", 15, 8, "Straight-Line", 5)
set_item(36, "Amenity & Clubhouse FF&E", "$ Total", 200000, "No", "Construction", 18, 5, "Straight-Line", 5)
set_item(37, "Leasing Office / Model Unit FF&E", "$ Total", 138000, "No", "Construction", 18, 5, "Straight-Line", 5)

# 6. Land / Acquisition
set_item(42, "Land Acquisition \u2014 246-Unit MF Parcel", "$ Total", 2500000, "No", "Pre-Development", 1, 1, "Straight-Line", 5)
set_item(43, "Closing Costs & Title", "$ Total", 75000, "No", "Pre-Development", 1, 1, "Straight-Line", 5)

# 7. Other & Contingency
set_item(49, "Contingency", "% of Hard Cost", "=Assumptions!$C$62", "No", "Construction", 1, 22, "Straight-Line", 5)

wb.save(path)
print("Dev Budget written.")

# ---------------- RENT COMPS ----------------
rent_comps = [
    # name, address, city_state_zip, dist, units, sf, yrbuilt, yrrenov, avgrent
    ("The Heights at Rock Hill", "1914 Paces Landing Ave", "Rock Hill, SC 29732", 0.14, 230, 199870, 1989, 2025, 1233.15),
    ("Paces River Apartments", "1817 Paces River Ave", "Rock Hill, SC 29732", 0.17, 470, 409840, 1988, 2018, 1255.61),
    ("The Boulders Apartment Homes", "1304 Stoneypointe Dr", "Rock Hill, SC 29732", 0.92, 264, 251064, 1996, 2008, 1279.43),
    ("Riverwalk Apartments", "517 Pink Moon Dr", "Rock Hill, SC 29730", 0.94, 307, 314675, 2016, None, 1556.71),
    ("Gable Oaks Apartment Homes", "752 Patriot Pkwy", "Rock Hill, SC 29730", 1.25, 252, 195048, 1996, 2010, 1320.87),
    ("The Alexander Apartment Homes", "2361 Eden Ter", "Rock Hill, SC 29730", 1.45, 360, 384480, 1998, 2024, 1451.71),
]
cols = ["D","E","F","G","H","I"]
for i, comp in enumerate(rent_comps):
    col = cols[i]
    RC[f"{col}6"] = comp[0]
    RC[f"{col}7"] = comp[1]
    RC[f"{col}8"] = comp[2]
    RC[f"{col}9"] = comp[3]
    RC[f"{col}10"] = comp[4]
    RC[f"{col}11"] = comp[5]
    RC[f"{col}12"] = comp[6]
    RC[f"{col}13"] = comp[7]
    RC[f"{col}14"] = comp[8]
RC["C19"] = 0.15  # Adjustment for New Construction %

wb.save(path)
print("Rent Comps written.")

# ---------------- SALES COMPS ----------------
sales_comps = [
    # name, address, city_state_zip, dist, units, sf(or None), yrbuilt, yrrenov, saledate, saleprice, adj_time, adj_size, adj_year, adj_loc, adj_mkt, weight
    ("Paces River Apartments", "1817 Paces River Ave", "Rock Hill, SC 29732", 0.23, 470, 409840, 1988, 2018, datetime(2025,4,2), 77888547, 0.01, -0.03, 0.18, 0.00, 0.01, 0.30),
    ("The Boulders Apartment Homes", "1304 Stoneypointe Dr", "Rock Hill, SC 29732", 0.95, 264, 251064, 1996, 2008, datetime(2021,6,24), 34175000, 0.03, 0.00, 0.20, -0.01, 0.03, 0.20),
    ("Patriots Crossing", "793 Patriot Pkwy", "Rock Hill, SC 29730", 1.32, 160, None, 1998, None, datetime(2022,8,19), 28623000, 0.02, 0.03, 0.22, -0.01, 0.02, 0.10),
    ("The Alexander Apartment Homes", "2361 Eden Ter", "Rock Hill, SC 29730", 1.52, 360, 384480, 1998, 2024, datetime(2024,4,12), 38150000, 0.01, -0.02, 0.12, -0.01, 0.01, 0.20),
    ("The Linden Apartment Homes", "2051 McGee Rd", "Rock Hill, SC 29732", 2.65, 144, 144000, 1990, None, datetime(2024,4,12), 13100000, 0.01, 0.05, 0.22, -0.02, 0.01, 0.10),
    ("Elements at Rock Hill", "708 Glamorgan Way", "Rock Hill, SC 29730", 3.64, 168, None, 2016, None, datetime(2022,11,2), 38100000, 0.02, 0.03, 0.10, -0.02, 0.02, 0.10),
]
for i, comp in enumerate(sales_comps):
    col = cols[i]
    (name, addr, csz, dist, units, sf, yrb, yrr, saledate, saleprice,
     adj_time, adj_size, adj_year, adj_loc, adj_mkt, weight) = comp
    SC[f"{col}6"] = name
    SC[f"{col}7"] = addr
    SC[f"{col}8"] = csz
    SC[f"{col}9"] = dist
    SC[f"{col}10"] = units
    SC[f"{col}11"] = sf
    SC[f"{col}12"] = yrb
    SC[f"{col}13"] = yrr
    SC[f"{col}16"] = saledate
    SC[f"{col}17"] = saleprice
    SC[f"{col}21"] = adj_time
    SC[f"{col}22"] = adj_size
    SC[f"{col}23"] = adj_year
    SC[f"{col}24"] = adj_loc
    SC[f"{col}25"] = adj_mkt
    SC[f"{col}26"] = f"=SUM({col}21:{col}25)"
    SC[f"{col}27"] = weight

wb.save(path)
print("Sales Comps written.")

cd /vercel/sandbox && python3 -c "
import openpyxl
wb = openpyxl.load_workbook('template_clean.xlsx', data_only=True)
A = wb['Assumptions']
checks = {
 'H64 Sources&Uses Balance': A['H64'].value,
 'H65 Budget Spread Tie-Out': A['H65'].value,
 'H66 Loan Sizing Convergence': A['H66'].value,
 'H67 LTC on Target': A['H67'].value,
 'H68 Unlevered CF Monthly=Annual': A['H68'].value,
 'H69 Levered CF Monthly=Annual': A['H69'].value,
 'H70 Per-Year Monthly=Annual': A['H70'].value,
 'H71 Waterfall LP+GP Tie-Out': A['H71'].value,
 'H72 Roster Total=NRA': A['H72'].value,
 'H73 OpEx Table Ties': A['H73'].value,
 'H74 Lease-Up Pace Plausible': A['H74'].value,
 'H75 Total LTC >= Senior LTC': A['H75'].value,
}
for k,v in checks.items():
    print(k, '->', v)
print()
print('NRA C13', A['C13'].value)
print('GBA C11', A['C11'].value)
print('Total Dev Cost H17', A['H17'].value)
print('Cost/Unit H18', A['H18'].value)
print('Cost/SF H19', A['H19'].value)
print('Peak Equity H16', A['H16'].value)
print('Unlevered IRR H10', A['H10'].value)
print('Unlevered EM H12', A['H12'].value)
print('Levered IRR H13', A['H13'].value)
print('Levered EM H15', A['H15'].value)
print('Dev Yield Trended H5', A['H5'].value)
print('Dev Yield Untrended H6', A['H6'].value)
print('Market Cap H7', A['H7'].value)
print('Dev Spread Trended (bps) H8', A['H8'].value)
print('Dev Spread Untrended (bps) H9', A['H9'].value)
print('Stab DSCR Senior H20', A['H20'].value)
print('Stab DSCR Combined H21', A['H21'].value)
print('Stab Debt Yield H22', A['H22'].value)
"

cd /vercel/sandbox && python3 -c "
import openpyxl
wb = openpyxl.load_workbook('template_clean.xlsx', data_only=True)
DB = wb['Dev Budget']
for r in [6,13,20,27,34,41,48,56,57,58]:
    print(r, DB.cell(r,2).value, '| Amount', DB.cell(r,5).value, '| Tie-out', DB.cell(r,16).value)
print()
RS = wb['Returns Summary']
for r in range(35,70):
    b = RS.cell(r,2).value
    c = RS.cell(r,3).value
    if b:
        print(r, b, '->', c)
"

cd /vercel/sandbox && python3 -c "
import openpyxl
wb = openpyxl.load_workbook('template_clean.xlsx', data_only=True)
BE = wb['Break-Even']
for r in [11,12,20,21,29,35,36,37,38,39]:
    print(r, BE.cell(r,2).value, '->', BE.cell(r,3).value)
print()
WF = wb['Waterfall']
for r in range(66,82):
    row=[WF.cell(r,c).value for c in range(2,6)]
    if any(v is not None for v in row):
        print(r,row)
"

cd /vercel/sandbox && python3 -c "
import openpyxl
wb = openpyxl.load_workbook('template_clean.xlsx', data_only=True)
A = wb['Assumptions']
print('LTC Senior actual C92', A['C92'].value)
print('LTC Total Debt actual C93', A['C93'].value)
print('Senior Loan Amount C88', A['C88'].value)
print('Permanent Loan Amount C120', A['C120'].value)
print('Peak Equity Requirement C91/H16', A['C91'].value, A['H16'].value)
print('Cost/Unit', A['H18'].value, 'Cost/SF', A['H19'].value)
"
python3 skills/xlsx/scripts/workbook_integrity_scan.py template_clean.xlsx --json integrity.json
echo "exit code: $?"
cat integrity.json | python3 -c "
import json,sys
d=json.load(sys.stdin)
print('total findings', len(d.get('findings',[])))
highs=[f for f in d.get('findings',[]) if f.get('severity')=='high']
print('HIGH count', len(highs))
for f in highs[:20]:
    print(f.get('sheet'), f.get('cell'), f.get('type'), f.get('message',f.get('description','')))
"