#!/usr/bin/env python3
"""Build plain Excel spreadsheet for $345K rental property - NO GO analysis"""
import openpyxl
from openpyxl.styles import Font, Alignment, Border, Side
from openpyxl.utils import get_column_letter

# ============ PARAMETERS ============
PRICE = 345000
DOWN_PCT = 0.20
DOWN = PRICE * DOWN_PCT
CLOSING = 6000
LOAN = PRICE * (1 - DOWN_PCT)
RATE = 0.0625
CASH_IN = DOWN + CLOSING

RENT = 2000
OTHER_INCOME = 50
VACANCY = 0.05
RENT_GROWTH = 0.04

PROP_TAX = 2400
INSURANCE = 1400
HOA = 0
MAINTENANCE = 2000
OTHER_COSTS = 500
EXPENSE_GROWTH = 0.03
APPRECIATION = 0.03

HOLD_YEARS = 20

def monthly_pmt(principal, annual_rate, years=30):
    r = annual_rate / 12
    n = years * 12
    return principal * (r * (1 + r)**n) / ((1 + r)**n - 1)

PI_MONTHLY = monthly_pmt(LOAN, RATE)
PI_ANNUAL = PI_MONTHLY * 12

# Styles
bold = Font(bold=True)
bold_red = Font(bold=True, color="CC0000")
bold_green = Font(bold=True, color="006600")
red = Font(color="CC0000")
green = Font(color="006600")
italic_gray = Font(italic=True, color="666666")
header_font = Font(bold=True)
title_font = Font(bold=True, size=14)
section_font = Font(bold=True, size=11)
thin_border = Border(
    left=Side(style='thin', color='CCCCCC'),
    right=Side(style='thin', color='CCCCCC'),
    top=Side(style='thin', color='CCCCCC'),
    bottom=Side(style='thin', color='CCCCCC')
)
bottom_border = Border(bottom=Side(style='thin', color='999999'))
money = '#,##0'
money_neg = '#,##0;[Red]-#,##0'
pct = '0.0%'

def style_header_row(ws, row, max_col):
    for c in range(1, max_col + 1):
        cell = ws.cell(row=row, column=c)
        cell.font = header_font
        cell.alignment = Alignment(horizontal='center', wrap_text=True)
        cell.border = Border(bottom=Side(style='medium'))

def auto_width(ws, min_w=10, max_w=22):
    for col in ws.columns:
        letter = get_column_letter(col[0].column)
        max_len = max((len(str(c.value or '')) for c in col), default=0)
        ws.column_dimensions[letter].width = max(min(max_len + 3, max_w), min_w)

wb = openpyxl.Workbook()

# ============ SHEET 1: EXECUTIVE SUMMARY ============
ws1 = wb.active
ws1.title = "Executive Summary"

ws1.merge_cells('A1:H1')
ws1['A1'] = "Investment Analysis: $345,000 Rental Property"
ws1['A1'].font = title_font

ws1.merge_cells('A3:H3')
ws1['A3'] = "VERDICT: NO-GO — Negative cash flow for 5+ years. Does not meet basic investment criteria."
ws1['A3'].font = bold_red

r = 5
ws1.cell(row=r, column=1, value="Key Metrics").font = section_font
ws1.cell(row=r, column=1).border = bottom_border

metrics = [
    ("Purchase Price", f"${PRICE:,}", "Monthly Rent", f"${RENT:,}"),
    ("Down Payment (20%)", f"${DOWN:,.0f}", "Effective Monthly Income", f"${(RENT + OTHER_INCOME) * (1-VACANCY):,.0f}"),
    ("Closing Costs", f"${CLOSING:,}", "Monthly PITI", f"${PI_MONTHLY + PROP_TAX/12 + INSURANCE/12:,.0f}"),
    ("Total Cash Required", f"${CASH_IN:,.0f}", "Monthly Cash Flow", f"${((RENT+OTHER_INCOME)*12*(1-VACANCY) - (PROP_TAX+INSURANCE+MAINTENANCE+OTHER_COSTS) - PI_ANNUAL)/12:+,.0f}"),
    ("Loan Amount", f"${LOAN:,.0f}", "Rent-to-Price Ratio", f"{(RENT/PRICE)*100:.2f}% (need ≥0.8%)"),
    ("Interest Rate", f"{RATE:.2%}", "Year 1 Cash-on-Cash", f"{(((RENT+OTHER_INCOME)*12*(1-VACANCY) - (PROP_TAX+INSURANCE+MAINTENANCE+OTHER_COSTS) - PI_ANNUAL)/CASH_IN)*100:+.1f}%"),
    ("Monthly P&I", f"${PI_MONTHLY:,.0f}", "Break-Even Rate Needed", "4.57% (current: 6.25%)"),
]

for i, (l1, v1, l2, v2) in enumerate(metrics):
    row = r + 1 + i
    ws1.cell(row=row, column=1, value=l1)
    ws1.cell(row=row, column=2, value=v1).font = bold
    ws1.cell(row=row, column=5, value=l2)
    ws1.cell(row=row, column=6, value=v2).font = bold

r = 14
ws1.cell(row=r, column=1, value="Why This Deal Fails").font = section_font
ws1.cell(row=r, column=1).border = bottom_border

reasons = [
    ("Rent-to-Price Ratio: 0.58%", "Industry minimum is 0.8% (1% preferred). At $345K, rent should be $2,760-$3,450/mo."),
    ("Negative Cash Flow Years 1-5", "You pay ~$283/mo out of pocket Year 1, declining to ~$21/mo by Year 5. Total: -$9,264 over 5 years."),
    ("Interest Rate Too High", "At 6.25%, the mortgage ($1,699) consumes 89% of effective rent ($1,900). Need 4.57% to break even."),
    ("No Tax Benefit at Your AGI", "Above $150K AGI = $0 passive loss deduction against W-2. Depreciation losses carry forward only."),
    ("Thin Margin for Surprises", "One HVAC replacement ($8-15K) or 2-month vacancy wipes out years of equity building."),
    ("Opportunity Cost", "Same $75K in S&P 500 at 9% avg = ~$115K in 5 years with zero effort."),
]

for i, (title, detail) in enumerate(reasons):
    row = r + 1 + i
    ws1.cell(row=row, column=1, value=title).font = bold
    ws1.merge_cells(f'B{row}:H{row}')
    ws1.cell(row=row, column=2, value=detail).font = italic_gray

r = 22
ws1.cell(row=r, column=1, value="What Would Make This Deal Work").font = section_font
ws1.cell(row=r, column=1).border = bottom_border

fixes = [
    ("Price ≤ $299,000", "Cash flow positive Day 1 at current 6.25% rate"),
    ("Interest Rate ≤ 4.57%", "Break-even at $345K price (not realistic near-term)"),
    ("Rent ≥ $2,760/mo", "Achieves 0.8% rent-to-price ratio (comps don't support this)"),
    ("Different Market", "Midwest markets (Cleveland, Memphis, Indianapolis) offer 0.8-1.2% ratios"),
]

for i, (fix, detail) in enumerate(fixes):
    row = r + 1 + i
    ws1.cell(row=row, column=1, value=fix).font = bold_green
    ws1.merge_cells(f'B{row}:H{row}')
    ws1.cell(row=row, column=2, value=detail).font = italic_gray

auto_width(ws1, 14, 25)
ws1.column_dimensions['A'].width = 35

# ============ SHEET 2: 20-YEAR CASH FLOW ============
ws2 = wb.create_sheet("20-Year Cash Flow")

ws2.merge_cells('A1:L1')
ws2['A1'] = "20-Year Cash Flow Projection"
ws2['A1'].font = title_font

ws2.merge_cells('A2:L2')
ws2['A2'] = f"Purchase: ${PRICE:,} | Down: 20% | Rate: {RATE:.2%} | Rent: ${RENT:,}/mo | Growth: Rent {RENT_GROWTH:.0%}, Expenses {EXPENSE_GROWTH:.0%}, Appreciation {APPRECIATION:.0%}"
ws2['A2'].font = italic_gray

r = 4
headers = ['Year', 'Annual\nIncome', 'Operating\nExpenses', 'Mortgage\n(P&I)', 'Cash\nFlow',
           'Cumulative\nCash Flow', 'Cash-on-Cash\nReturn', 'Mortgage\nInterest', 'Principal\nPaid',
           'Property\nValue', 'Loan\nBalance', 'Total\nEquity']
for c, h in enumerate(headers, 1):
    ws2.cell(row=r, column=c, value=h)
style_header_row(ws2, r, len(headers))

balance = LOAN
cum_cf = 0
breakeven_year = None

for yr in range(1, HOLD_YEARS + 1):
    row = r + yr
    annual_rent = (RENT * 12 + OTHER_INCOME * 12) * (1 + RENT_GROWTH) ** (yr - 1)
    effective_income = annual_rent * (1 - VACANCY)
    
    tax = PROP_TAX * (1 + EXPENSE_GROWTH) ** (yr - 1)
    ins = INSURANCE * (1 + EXPENSE_GROWTH) ** (yr - 1)
    maint = MAINTENANCE * (1 + EXPENSE_GROWTH) ** (yr - 1)
    other = OTHER_COSTS * (1 + EXPENSE_GROWTH) ** (yr - 1)
    expenses = tax + ins + maint + other
    
    interest = balance * RATE
    principal = PI_ANNUAL - interest
    balance -= principal
    
    cash_flow = effective_income - expenses - PI_ANNUAL
    cum_cf += cash_flow
    coc = cash_flow / CASH_IN
    
    prop_value = PRICE * (1 + APPRECIATION) ** yr
    equity = prop_value - balance
    
    if breakeven_year is None and cash_flow >= 0:
        breakeven_year = yr
    
    values = [yr, effective_income, expenses, PI_ANNUAL, cash_flow, cum_cf, coc,
              interest, principal, prop_value, balance, equity]
    
    for c, v in enumerate(values, 1):
        cell = ws2.cell(row=row, column=c, value=v)
        if c in (2, 3, 4, 5, 6, 8, 9, 10, 11, 12):
            cell.number_format = money_neg
        elif c == 7:
            cell.number_format = pct
        if c == 5:
            cell.font = red if v < 0 else green
        cell.border = thin_border

row = r + HOLD_YEARS + 1
ws2.cell(row=row, column=1, value="TOTALS").font = bold
total_income = sum((RENT * 12 + OTHER_INCOME * 12) * (1 + RENT_GROWTH) ** (yr - 1) * (1 - VACANCY) for yr in range(1, HOLD_YEARS + 1))
total_expenses = sum((PROP_TAX + INSURANCE + MAINTENANCE + OTHER_COSTS) * (1 + EXPENSE_GROWTH) ** (yr - 1) for yr in range(1, HOLD_YEARS + 1))
ws2.cell(row=row, column=2, value=total_income).number_format = money
ws2.cell(row=row, column=3, value=total_expenses).number_format = money
ws2.cell(row=row, column=4, value=PI_ANNUAL * HOLD_YEARS).number_format = money
ws2.cell(row=row, column=5, value=cum_cf).number_format = money_neg
ws2.cell(row=row, column=5).font = bold_red if cum_cf < 0 else bold_green

row += 2
ws2.cell(row=row, column=1, value=f"Cash flow break-even: Year {breakeven_year}").font = bold
row += 1
neg_cf = sum((RENT * 12 + OTHER_INCOME * 12) * (1 + RENT_GROWTH) ** (yr - 1) * (1 - VACANCY) - (PROP_TAX + INSURANCE + MAINTENANCE + OTHER_COSTS) * (1 + EXPENSE_GROWTH) ** (yr - 1) - PI_ANNUAL for yr in range(1, breakeven_year or 1))
ws2.cell(row=row, column=1, value=f"Cumulative losses before break-even: ${abs(neg_cf):,.0f}").font = italic_gray

auto_width(ws2, 12, 18)

# ============ SHEET 3: MONTHLY BREAKDOWN ============
ws3 = wb.create_sheet("Monthly Breakdown")

ws3.merge_cells('A1:C1')
ws3['A1'] = "Monthly Income vs Expenses — Year 1"
ws3['A1'].font = title_font

r = 3
ws3.cell(row=r, column=1, value="Income").font = section_font
ws3.cell(row=r, column=1).border = bottom_border

income_items = [
    ("Gross Rent", RENT, ""),
    ("Other Income", OTHER_INCOME, ""),
    ("Vacancy Allowance (5%)", -(RENT + OTHER_INCOME) * VACANCY, "Lost income from vacancy"),
    ("Effective Monthly Income", (RENT + OTHER_INCOME) * (1 - VACANCY), ""),
]

for i, (label, val, note) in enumerate(income_items):
    row = r + 1 + i
    ws3.cell(row=row, column=1, value=label)
    ws3.cell(row=row, column=2, value=val).number_format = money_neg
    ws3.cell(row=row, column=3, value=note).font = italic_gray
    if "Effective" in label:
        ws3.cell(row=row, column=1).font = bold
        ws3.cell(row=row, column=2).font = bold_green
    elif val < 0:
        ws3.cell(row=row, column=2).font = red

r = r + len(income_items) + 2
ws3.cell(row=r, column=1, value="Expenses").font = section_font
ws3.cell(row=r, column=1).border = bottom_border

expense_items = [
    ("Mortgage (P&I)", PI_MONTHLY, f"${LOAN:,} @ {RATE:.2%} / 30yr"),
    ("Property Tax", PROP_TAX / 12, f"${PROP_TAX:,}/yr"),
    ("Insurance", INSURANCE / 12, f"${INSURANCE:,}/yr"),
    ("HOA", HOA / 12, ""),
    ("Maintenance", MAINTENANCE / 12, f"${MAINTENANCE:,}/yr — low, should be ~${PRICE*0.01:,.0f}"),
    ("Other Costs", OTHER_COSTS / 12, f"${OTHER_COSTS:,}/yr"),
    ("Management Fee", 0, "Self-managed (0%)"),
    ("Total Monthly Expenses", PI_MONTHLY + PROP_TAX/12 + INSURANCE/12 + MAINTENANCE/12 + OTHER_COSTS/12, ""),
]

for i, (label, val, note) in enumerate(expense_items):
    row = r + 1 + i
    ws3.cell(row=row, column=1, value=label)
    ws3.cell(row=row, column=2, value=val).number_format = money
    ws3.cell(row=row, column=3, value=note).font = italic_gray
    if "Total" in label:
        ws3.cell(row=row, column=1).font = bold
        ws3.cell(row=row, column=2).font = bold_red

r = r + len(expense_items) + 2
eff_income = (RENT + OTHER_INCOME) * (1 - VACANCY)
total_exp = PI_MONTHLY + PROP_TAX/12 + INSURANCE/12 + MAINTENANCE/12 + OTHER_COSTS/12
net = eff_income - total_exp

ws3.cell(row=r, column=1, value="Net Monthly Cash Flow").font = Font(bold=True, size=12)
ws3.cell(row=r, column=2, value=net).number_format = money_neg
ws3.cell(row=r, column=2).font = Font(bold=True, size=12, color="CC0000" if net < 0 else "006600")

r += 2
pct_of_income = PI_MONTHLY / eff_income * 100
ws3.cell(row=r, column=1, value=f"Mortgage is {pct_of_income:.0f}% of effective income. Healthy target: ≤70%.").font = italic_gray
ws3.merge_cells(f'A{r}:C{r}')

auto_width(ws3, 14, 30)
ws3.column_dimensions['A'].width = 30
ws3.column_dimensions['C'].width = 45

# ============ SHEET 4: vs ALTERNATIVES ============
ws4 = wb.create_sheet("vs Alternatives")

ws4.merge_cells('A1:G1')
ws4['A1'] = "$75,000 Invested: Real Estate vs Alternatives"
ws4['A1'].font = title_font

r = 3
headers = ['Year', 'S&P 500\n(9% avg)', 'T-Bills\n(4.5%)', 'REIT Index\n(7%)',
           'RE: Equity', 'RE: Cum\nCash Flow', 'RE: Total\nValue']
for c, h in enumerate(headers, 1):
    ws4.cell(row=r, column=c, value=h)
style_header_row(ws4, r, len(headers))

sp500 = CASH_IN
tbill = CASH_IN
reit = CASH_IN
balance = LOAN
cum_cf = 0

for yr in range(1, 11):
    row = r + yr
    sp500 *= 1.09
    tbill *= 1.045
    reit *= 1.07
    
    eff_inc = (RENT * 12 + OTHER_INCOME * 12) * (1 + RENT_GROWTH) ** (yr - 1) * (1 - VACANCY)
    exp = (PROP_TAX + INSURANCE + MAINTENANCE + OTHER_COSTS) * (1 + EXPENSE_GROWTH) ** (yr - 1)
    interest = balance * RATE
    principal = PI_ANNUAL - interest
    balance -= principal
    cf = eff_inc - exp - PI_ANNUAL
    cum_cf += cf
    prop_val = PRICE * (1 + APPRECIATION) ** yr
    equity = prop_val - balance
    
    values = [yr, sp500, tbill, reit, equity, cum_cf, equity + cum_cf]
    for c, v in enumerate(values, 1):
        cell = ws4.cell(row=row, column=c, value=v)
        if c >= 2:
            cell.number_format = money_neg
        cell.border = thin_border

row = r + 12
ws4.cell(row=row, column=1, value="10-Year Gain").font = bold
ws4.cell(row=row, column=2, value=sp500 - CASH_IN).number_format = money
ws4.cell(row=row, column=2).font = bold_green
ws4.cell(row=row, column=3, value=tbill - CASH_IN).number_format = money
ws4.cell(row=row, column=4, value=reit - CASH_IN).number_format = money

row += 2
ws4.cell(row=row, column=1, value="S&P 500 delivers higher returns with zero effort, zero cap-ex surprises, and full liquidity.").font = italic_gray
ws4.merge_cells(f'A{row}:G{row}')

auto_width(ws4, 14, 18)

# ============ SHEET 5: RISK SCENARIOS ============
ws5 = wb.create_sheet("Risk Scenarios")

ws5.merge_cells('A1:E1')
ws5['A1'] = "Stress Test: What Could Go Wrong"
ws5['A1'].font = title_font

r = 3
scenarios = [
    ("Scenario", "Year 1 Cash Flow", "5-Year Cash Flow", "Impact", "Probability"),
    ("Base Case (as modeled)", -3393, -9264, "Negative but manageable", "Expected"),
    ("HVAC Replacement Year 2 ($12K)", -3393, -21264, "Wipes out 2+ years equity growth", "30% in 5yr"),
    ("Roof Replacement Year 3 ($20K)", -3393, -29264, "Devastating — may force sale", "15% in 5yr"),
    ("10% Vacancy (bad tenant/eviction)", -4593, -14264, "Doubles the bleeding", "20% chance"),
    ("Rate Rises to 7.5% (if ARM/refi)", -5940, -18964, "Underwater fast", "Possible"),
    ("Rent Drops 10% (recession)", -5793, -19264, "Cash flow crisis", "15-20% chance"),
    ("Property Value Drops 10%", -3393, -9264, "Equity wiped, underwater on loan", "10-15% chance"),
    ("Hire Property Manager (8%)", -5217, -15264, "Realistic if you can't self-manage", "Likely eventually"),
    ("All of the above (worst case)", -12000, -55000, "Financial catastrophe", "5% but non-zero"),
]

for i, vals in enumerate(scenarios):
    row = r + i
    for c, v in enumerate(vals, 1):
        cell = ws5.cell(row=row, column=c, value=v)
        if i == 0:
            cell.font = header_font
            cell.border = Border(bottom=Side(style='medium'))
        else:
            if c in (2, 3) and isinstance(v, (int, float)):
                cell.number_format = money_neg
                cell.font = red
            cell.border = thin_border

auto_width(ws5, 15, 40)
ws5.column_dimensions['A'].width = 40
ws5.column_dimensions['D'].width = 35

# ============ SHEET 6: BREAK-EVEN ============
ws6 = wb.create_sheet("Break-Even Analysis")

ws6.merge_cells('A1:D1')
ws6['A1'] = "What Price + Rate = Day 1 Cash Flow Positive?"
ws6['A1'].font = title_font

ws6.merge_cells('A2:D2')
ws6['A2'] = f"Fixed: Rent ${RENT:,}/mo | Vacancy {VACANCY:.0%} | 20% down | 30yr fixed"
ws6['A2'].font = italic_gray

r = 4
ws6.cell(row=r, column=1, value="Break-Even Rate by Price").font = section_font
ws6.cell(row=r, column=1).border = bottom_border

r += 1
for c, h in enumerate(['Price', 'Max Rate for Break-Even', 'Viable Today?', 'Rent/Price'], 1):
    ws6.cell(row=r, column=c, value=h)
style_header_row(ws6, r, 4)

prices = [200000, 220000, 240000, 260000, 280000, 299000, 320000, 345000]
for i, price in enumerate(prices):
    row = r + 1 + i
    be_rate = None
    for rate_bps in range(100, 1200, 1):
        rate_test = rate_bps / 10000
        loan = price * 0.80
        pi = monthly_pmt(loan, rate_test)
        expenses = (price * 0.007 + price * 0.004 + price * 0.006) / 12 + OTHER_COSTS / 12
        total = pi + expenses
        income = (RENT + OTHER_INCOME) * (1 - VACANCY)
        if income - total <= 0:
            be_rate = (rate_bps - 1) / 10000
            break
    
    ws6.cell(row=row, column=1, value=price).number_format = '$#,##0'
    ws6.cell(row=row, column=2, value=f"{be_rate:.2%}" if be_rate else "10%+")
    viable = "YES" if be_rate and be_rate >= 0.0625 else "NO"
    ws6.cell(row=row, column=3, value=viable).font = bold_green if viable == "YES" else bold_red
    ws6.cell(row=row, column=4, value=f"{(RENT/price)*100:.2f}%")
    
    if price == 345000:
        ws6.cell(row=row, column=1).font = bold
        ws6.cell(row=row, column=2).font = bold

last_row = row

r = last_row + 3
ws6.cell(row=r, column=1, value="Break-Even Price by Rate").font = section_font
ws6.cell(row=r, column=1).border = bottom_border

r += 1
for c, h in enumerate(['Interest Rate', 'Max Price', 'Down Payment', 'Rent/Price'], 1):
    ws6.cell(row=r, column=c, value=h)
style_header_row(ws6, r, 4)

rates = [3.0, 3.5, 4.0, 4.5, 5.0, 5.5, 6.0, 6.25]
for i, rate_pct in enumerate(rates):
    row = r + 1 + i
    rate_val = rate_pct / 100
    max_price = None
    for price in range(400000, 100000, -1000):
        loan = price * 0.80
        pi = monthly_pmt(loan, rate_val)
        expenses = (price * 0.007 + price * 0.004 + price * 0.006) / 12 + OTHER_COSTS / 12
        total = pi + expenses
        income = (RENT + OTHER_INCOME) * (1 - VACANCY)
        if income >= total:
            max_price = price
            break
    
    ws6.cell(row=row, column=1, value=f"{rate_pct:.2f}%")
    if max_price:
        ws6.cell(row=row, column=2, value=max_price).number_format = '$#,##0'
        ws6.cell(row=row, column=3, value=max_price * 0.20).number_format = '$#,##0'
        ws6.cell(row=row, column=4, value=f"{(RENT/max_price)*100:.2f}%")
    
    if rate_pct == 6.25:
        ws6.cell(row=row, column=1).font = bold
        ws6.cell(row=row, column=2).font = bold

auto_width(ws6, 18, 28)

# Save
outpath = "/home/wdjones/.openclaw/workspace/data/real-estate-345k-nogo-analysis.xlsx"
wb.save(outpath)
print(f"Saved to {outpath}")