cat << 'EOF' > /vercel/sandbox/tenant_analysis.py
# North Harbor Tower tenant quality vs Loop submarket benchmark

# --- Income ---
subj_hhi_median = 134146
subj_hhi_avg = 137384
bench_hhi_median = 128991
bench_hhi_avg = 173474
income_var_pct = (subj_hhi_median - bench_hhi_median) / bench_hhi_median * 100

# Bottom quartile proxy: subject has 0% below $75k; bench has ~4.4% below $75k (sum of buckets under 75k)
subj_below_75k = 0.0
bench_below_75k = 0.000601 + 0.014435 + 0.028168  # 10-15k,35-50k,50-75k (all others 0)
bench_below_75k_pct = bench_below_75k * 100

# --- Wealth ---
subj_net_worth_tier_avg = 7.159834
bench_net_worth_tier_avg = 6.496357
subj_net_worth_median_range = "$750,000 - $999,999"
bench_net_worth_median_range = "$500,000 - $749,999"

subj_wealth_tier_avg = 6.776058
bench_wealth_tier_avg = 6.494666

# --- Credit ---
subj_fico = 751.40
bench_fico = 737.64
fico_gap = subj_fico - bench_fico

# Credit tier mix (prime+ = FICO 670+) - subject sample only reports avg FICO, not full tier mix (mf_tenant_profile_detail lacks tier %). Use submarket tier mix for context only.
bench_prime_plus = 0.2283 + 0.5328 + 0.1963  # 670-739 + 740-799 + 800+
bench_subprime_near = 0.0032 + 0.0394  # below579 + 580-669

# Short-term liability (debt burden) tier - lower is better
subj_stl_tier = 2.8765808983863934
bench_stl_tier = 3.495036591551337

# Past due rate
subj_past_due = 0.0592
bench_past_due = 0.0593

# --- Rent to income ---
subj_rti = 0.2395
# submarket RTI field is null; compute manually: submarket in-place median rent * 12 / submarket median income
bench_in_place_rent_median = 2599.00
bench_rti_calc = bench_in_place_rent_median*12/bench_hhi_median

# --- Retention & rent ---
subj_retention = 0.7117
bench_retention = 0.665
subj_in_place_rent_median = 2677.00
bench_in_place_rent_median2 = 2599.00
rent_premium_pct = (subj_in_place_rent_median - bench_in_place_rent_median2)/bench_in_place_rent_median2*100

subj_tradeout = None  # not provided at property level in this pull
bench_tradeout_pct = 0.1020

subj_occ = 0.9567
bench_occ = 0.9695

subj_dom = 117
bench_dom = 58

# NOI margin
subj_noi_margin = 0.5863

print("Income variance (median) vs submarket: {:.1f}%".format(income_var_pct))
print("Subject % HH <$75k income: {:.1f}%  | Submarket: {:.1f}%".format(subj_below_75k*100, bench_below_75k_pct))
print("Net worth tier avg - Subject: {:.2f} | Submarket: {:.2f}".format(subj_net_worth_tier_avg, bench_net_worth_tier_avg))
print("Wealth resources tier avg - Subject: {:.2f} | Submarket: {:.2f}".format(subj_wealth_tier_avg, bench_wealth_tier_avg))
print("FICO - Subject: {:.0f} | Submarket: {:.0f} | Gap: {:.0f} pts".format(subj_fico, bench_fico, fico_gap))
print("Submarket prime+ (670+) share: {:.1f}% | subprime/near-prime (<670): {:.1f}%".format(bench_prime_plus*100, bench_subprime_near*100))
print("Short-term liability tier (lower=better) - Subject: {:.2f} | Submarket: {:.2f}".format(subj_stl_tier, bench_stl_tier))
print("Past due rate - Subject: {:.2f}% | Submarket: {:.2f}%".format(subj_past_due*100, bench_past_due*100))
print("Rent-to-income - Subject: {:.1f}% | Submarket (calc): {:.1f}%".format(subj_rti*100, bench_rti_calc*100))
print("Retention - Subject: {:.1f}% | Submarket: {:.1f}% | Delta: {:.1f} pts".format(subj_retention*100, bench_retention*100, (subj_retention-bench_retention)*100))
print("In-place median rent - Subject: ${:.0f} | Submarket: ${:.0f} | Premium: {:.1f}%".format(subj_in_place_rent_median, bench_in_place_rent_median2, rent_premium_pct))
print("Occupancy - Subject: {:.1f}% | Submarket: {:.1f}%".format(subj_occ*100, bench_occ*100))
print("Days on market (new leases) - Subject: {:.0f} days | Submarket: {:.0f} days".format(subj_dom, bench_dom))
print("Subject NOI margin (NOI/EGI): {:.1f}%".format(subj_noi_margin*100))
print("Submarket tradeout on new leases: {:.1f}%".format(bench_tradeout_pct*100))

# Household age / mobility
subj_age = 55.55
bench_age = 35.8
print("Household head avg age - Subject: {:.1f} | Submarket: {:.1f}".format(subj_age, bench_age))
EOF
python3 /vercel/sandbox/tenant_analysis.py