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

# ============ 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
MGMT_FEE_PCT = 0.0

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

HOLD_YEARS = 20
COST_TO_SELL = 0.08

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
def make_styles():
    styles = {}
    styles['title_font'] = Font(bold=True, color="FFFFFF", size=16)
    styles['title_fill'] = PatternFill(start_color="1a1a2e", end_color="1a1a2e", fill_type="solid")
    styles['header_font'] = Font(bold=True, color="FFFFFF", size=10)
    styles['header_fill'] = PatternFill(start_color="16213e", end_color="16213e", fill_type="solid")
    styles['section_font'] = Font(bold=True, color="00BFFF", size=11)
    styles['section_fill'] = PatternFill(start_color="0f3460", end_color="0f3460", fill_type="solid")
    styles['red_font'] = Font(color="FF4444", bold=True)
    styles['red_fill'] = PatternFill(start_color="3d1111", end_color="3d1111", fill_type="solid")
    styles['green_font'] = Font(color="44CC44", bold=True)
    styles['green_fill'] = PatternFill(start_color="113d11", end_color="113d11", fill_type="solid")
    styles['warn_font'] = Font(color="FFaa00", bold=True, size=11)
    styles['warn_fill'] = PatternFill(start_color="3d3011", end_color="3d3011", fill_type="solid")
    styles['label_font'] = Font(color="CCCCCC", size=10)
    styles['value_font'] = Font(color="FFFFFF", size=10, bold=True)
    styles['dim_font'] = Font(color="888888", size=9, italic=True)
    styles['bg_dark'] = PatternFill(start_color="121212", end_color="121212", fill_type="solid")
    styles['bg_row1'] = PatternFill(start_color="1a1a2e", end_color="1a1a2e", fill_type="solid")
    styles['bg_row2'] = PatternFill(start_color="16213e", end_color="16213e", fill_type="solid")
    styles['border'] = Border(
        left=Side(style='thin', color='333333'),
        right=Side(style='thin', color='333333'),
        top=Side(style='thin', color='333333'),
        bottom=Side(style='thin', color='333333')
    )
    styles['money_fmt'] = '#,##0'
    styles['money_neg'] = '#,##0;[Red]-#,##0'
    styles['pct_fmt'] = '0.0%'
    return styles

S = make_styles()

wb = openpyxl.Workbook()

def apply_dark_bg(ws, max_row, max_col):
    for r in range(1, max_row + 1):
        for c in range(1, max_col + 1):
            cell = ws.cell(row=r, column=c)
            if not cell.fill or cell.fill.start_color.index == '00000000':
                cell.fill = S['bg_dark']
            cell.border = S['border']

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 = S['header_font']
        cell.fill = S['header_fill']
        cell.alignment = Alignment(horizontal='center', wrap_text=True)

def auto_width(ws, min_width=10, max_width=20):
    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_width), min_width)

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

# Title
ws1.merge_cells('A1:H1')
ws1['A1'] = "INVESTMENT ANALYSIS: $345,000 RENTAL PROPERTY"
ws1['A1'].font = S['title_font']
ws1['A1'].fill = S['title_fill']

# Verdict banner
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 = Font(bold=True, color="FF4444", size=13)
ws1['A3'].fill = S['red_fill']

# Key metrics
r = 5
ws1.cell(row=r, column=1, value="KEY METRICS").font = S['section_font']
ws1.cell(row=r, column=1).fill = S['section_fill']
ws1.merge_cells(f'A{r}:H{r}')

metrics = [
    ("Purchase Price", f"${PRICE:,}", "Monthly Rent", f"${RENT:,}"),
    ("Down Payment (20%)", f"${DOWN:,.0f}", "Effective Monthly Income", f"${RENT * (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*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*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).font = S['label_font']
    ws1.cell(row=row, column=2, value=v1).font = S['value_font']
    ws1.cell(row=row, column=5, value=l2).font = S['label_font']
    ws1.cell(row=row, column=6, value=v2).font = S['value_font']

# Why it fails
r = 14
ws1.cell(row=r, column=1, value="WHY THIS DEAL FAILS").font = S['section_font']
ws1.cell(row=r, column=1).fill = S['section_fill']
ws1.merge_cells(f'A{r}:H{r}')

reasons = [
    ("❌ Rent-to-Price Ratio: 0.58%", "Industry minimum is 0.8% (1% preferred). At $345K, rent should be $2,760-$3,450/mo to work."),
    ("❌ 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 alone ($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 just carry forward uselessly."),
    ("❌ 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. This property returns less with far more risk."),
]

for i, (title, detail) in enumerate(reasons):
    row = r + 1 + i
    ws1.cell(row=row, column=1, value=title).font = S['red_font']
    ws1.merge_cells(f'B{row}:H{row}')
    ws1.cell(row=row, column=2, value=detail).font = S['dim_font']
    ws1.cell(row=row, column=2).alignment = Alignment(wrap_text=True)

# What would make it work
r = 22
ws1.cell(row=r, column=1, value="WHAT WOULD MAKE THIS DEAL WORK").font = S['section_font']
ws1.cell(row=r, column=1).fill = S['section_fill']
ws1.merge_cells(f'A{r}:H{r}')

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 at lower price points"),
]

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

apply_dark_bg(ws1, 28, 8)
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.sheet_properties.tabColor = "FF8800"

ws2.merge_cells('A1:L1')
ws2['A1'] = "20-YEAR CASH FLOW PROJECTION"
ws2['A1'].font = S['title_font']
ws2['A1'].fill = S['title_fill']

# Assumptions row
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 = S['dim_font']

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)
    mgmt = effective_income * MGMT_FEE_PCT
    expenses = tax + ins + HOA + maint + other + mgmt
    
    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 = S['money_neg']
        elif c == 7:
            cell.number_format = S['pct_fmt']
        
        # Color negative cash flow red
        if c == 5 and v < 0:
            cell.font = S['red_font']
        elif c == 5 and v >= 0:
            cell.font = S['green_font']
        
        # Alternating row colors
        cell.fill = S['bg_row1'] if yr % 2 == 0 else S['bg_row2']
    
    ws2.cell(row=row, column=1).font = S['value_font']

# Totals
row = r + HOLD_YEARS + 1
ws2.cell(row=row, column=1, value="TOTALS").font = Font(bold=True, color="00BFFF")
# Calculate totals from the data
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 = S['money_fmt']
ws2.cell(row=row, column=3, value=total_expenses).number_format = S['money_fmt']
ws2.cell(row=row, column=4, value=PI_ANNUAL * HOLD_YEARS).number_format = S['money_fmt']
ws2.cell(row=row, column=5, value=cum_cf).number_format = S['money_neg']
ws2.cell(row=row, column=5).font = S['red_font'] if cum_cf < 0 else S['green_font']

# Break-even note
row += 2
ws2.cell(row=row, column=1, value=f"Cash flow break-even: Year {breakeven_year}" if breakeven_year else "Never breaks even").font = S['warn_font']
ws2.cell(row=row, column=1).fill = S['warn_fill']
ws2.merge_cells(f'A{row}:D{row}')

row += 1
ws2.cell(row=row, column=1, value=f"Cumulative losses before break-even: ${abs(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)))):.0f}").font = S['warn_font']
ws2.merge_cells(f'A{row}:D{row}')

apply_dark_bg(ws2, row + 2, len(headers))
auto_width(ws2, 12, 18)

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

ws3.merge_cells('A1:F1')
ws3['A1'] = "MONTHLY INCOME vs EXPENSES — YEAR 1"
ws3['A1'].font = S['title_font']
ws3['A1'].fill = S['title_fill']

r = 3
ws3.cell(row=r, column=1, value="INCOME").font = S['section_font']
ws3.cell(row=r, column=1).fill = S['section_fill']
ws3.merge_cells(f'A{r}:C{r}')

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).font = S['label_font']
    ws3.cell(row=row, column=2, value=val).number_format = S['money_neg']
    ws3.cell(row=row, column=2).font = S['green_font'] if val > 0 else S['red_font']
    ws3.cell(row=row, column=3, value=note).font = S['dim_font']
    if "EFFECTIVE" in label:
        ws3.cell(row=row, column=1).font = S['value_font']
        ws3.cell(row=row, column=2).font = Font(bold=True, color="44CC44", size=11)

r = r + len(income_items) + 2
ws3.cell(row=r, column=1, value="EXPENSES").font = S['section_font']
ws3.cell(row=r, column=1).fill = S['section_fill']
ws3.merge_cells(f'A{r}:C{r}')

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).font = S['label_font']
    ws3.cell(row=row, column=2, value=val).number_format = S['money_fmt']
    ws3.cell(row=row, column=2).font = S['red_font']
    ws3.cell(row=row, column=3, value=note).font = S['dim_font']
    if "TOTAL" in label:
        ws3.cell(row=row, column=1).font = S['value_font']
        ws3.cell(row=row, column=2).font = Font(bold=True, color="FF4444", size=11)

r = r + len(expense_items) + 2
ws3.cell(row=r, column=1, value="NET MONTHLY CASH FLOW").font = Font(bold=True, color="FFFFFF", size=12)
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=2, value=net).number_format = S['money_neg']
ws3.cell(row=r, column=2).font = Font(bold=True, color="FF4444" if net < 0 else "44CC44", size=14)

r += 2
ws3.cell(row=r, column=1, value="MORTGAGE EATS EVERYTHING").font = S['warn_font']
ws3.cell(row=r, column=1).fill = S['warn_fill']
ws3.merge_cells(f'A{r}:C{r}')
r += 1
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 = S['dim_font']
ws3.merge_cells(f'A{r}:C{r}')

apply_dark_bg(ws3, r + 2, 6)
auto_width(ws3, 14, 30)
ws3.column_dimensions['A'].width = 30
ws3.column_dimensions['C'].width = 45

# ============ SHEET 4: COMPARISON ============
ws4 = wb.create_sheet("vs Alternatives")
ws4.sheet_properties.tabColor = "44CC44"

ws4.merge_cells('A1:G1')
ws4['A1'] = "$75,000 INVESTED: REAL ESTATE vs ALTERNATIVES"
ws4['A1'].font = S['title_font']
ws4['A1'].fill = S['title_fill']

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
    
    # RE calcs
    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
    re_total = equity + cum_cf - CASH_IN  # net gain
    
    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 = S['money_neg']
        cell.fill = S['bg_row1'] if yr % 2 == 0 else S['bg_row2']
    
    # Highlight winner
    gains = {'S&P': sp500 - CASH_IN, 'T-Bill': tbill - CASH_IN, 'REIT': reit - CASH_IN, 'RE': equity + cum_cf - CASH_IN}
    winner = max(gains, key=gains.get)

# Summary
row = r + 11 + 1
ws4.cell(row=row, column=1, value="10-YEAR GAIN").font = Font(bold=True, color="00BFFF")
ws4.cell(row=row, column=2, value=sp500 - CASH_IN).number_format = S['money_fmt']
ws4.cell(row=row, column=2).font = S['green_font']
ws4.cell(row=row, column=3, value=tbill - CASH_IN).number_format = S['money_fmt']
ws4.cell(row=row, column=4, value=reit - CASH_IN).number_format = S['money_fmt']

row += 2
ws4.cell(row=row, column=1, value="S&P 500 delivers higher returns with zero effort, zero risk of midnight toilet calls,").font = S['dim_font']
ws4.merge_cells(f'A{row}:G{row}')
row += 1
ws4.cell(row=row, column=1, value="zero cap-ex surprises, and zero chance of losing your investment to a bad tenant.").font = S['dim_font']
ws4.merge_cells(f'A{row}:G{row}')

apply_dark_bg(ws4, row + 2, 7)
auto_width(ws4, 14, 18)

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

ws5.merge_cells('A1:E1')
ws5['A1'] = "STRESS TEST: WHAT COULD GO WRONG"
ws5['A1'].font = S['title_font']
ws5['A1'].fill = S['title_fill']

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 = S['header_font']
            cell.fill = S['header_fill']
        else:
            if c in (2, 3) and isinstance(v, (int, float)):
                cell.number_format = S['money_neg']
                cell.font = S['red_font']
            cell.fill = S['bg_row1'] if i % 2 == 0 else S['bg_row2']

apply_dark_bg(ws5, r + len(scenarios) + 2, 5)
auto_width(ws5, 15, 40)
ws5.column_dimensions['A'].width = 40
ws5.column_dimensions['D'].width = 35

# ============ SHEET 6: BREAK-EVEN TABLE ============
ws6 = wb.create_sheet("Break-Even Analysis")
ws6.sheet_properties.tabColor = "4488FF"

ws6.merge_cells('A1:D1')
ws6['A1'] = "WHAT PRICE + RATE = DAY 1 CASH FLOW POSITIVE?"
ws6['A1'].font = S['title_font']
ws6['A1'].fill = S['title_fill']

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

# Break-even rate by price
r = 4
ws6.cell(row=r, column=1, value="BREAK-EVEN RATE BY PRICE").font = S['section_font']
ws6.cell(row=r, column=1).fill = S['section_fill']
ws6.merge_cells(f'A{r}:D{r}')

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
    # Find break-even rate
    be_rate = None
    for rate_bps in range(100, 1200, 1):
        rate = rate_bps / 10000
        loan = price * 0.80
        pi = monthly_pmt(loan, rate)
        tax_ins_maint = (price * 0.007 + price * 0.004 + price * 0.006 + OTHER_COSTS/12*12/price*price) / 12
        total = pi + tax_ins_maint + OTHER_COSTS/12
        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 = S['green_font'] if "YES" in viable else S['red_font']
    ws6.cell(row=row, column=4, value=f"{(RENT/price)*100:.2f}%")
    ws6.cell(row=row, column=4).font = S['green_font'] if RENT/price >= 0.008 else S['red_font']
    
    # Highlight current deal
    if price == 345000:
        for c in range(1, 5):
            ws6.cell(row=row, column=c).fill = S['red_fill']

# Break-even price by rate
r = row + 3
ws6.cell(row=r, column=1, value="BREAK-EVEN PRICE BY RATE").font = S['section_font']
ws6.cell(row=r, column=1).fill = S['section_fill']
ws6.merge_cells(f'A{r}:D{r}')

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 = rate_pct / 100
    max_price = None
    for price in range(400000, 100000, -1000):
        loan = price * 0.80
        pi = monthly_pmt(loan, rate)
        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:
        for c in range(1, 5):
            ws6.cell(row=row, column=c).fill = S['warn_fill']

apply_dark_bg(ws6, row + 2, 4)
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}")