# stress_test.py
import sqlite3
from alm_schema import DB_PATH

def run_stress_test(cash_reserves=4_500_000):
    conn = sqlite3.connect(DB_PATH)
    c = conn.cursor()

    # Current portfolio value
    c.execute("""
        SELECT SUM(h.shares * p.close_price)
        FROM holdings h
        JOIN prices p ON h.ticker = p.ticker
        WHERE p.price_date = (SELECT MAX(price_date) FROM prices)
    """)
    portfolio_value = c.fetchone()[0]

    # Margin loan details
    c.execute("""
        SELECT balance, freeze_pct, margin_call_pct, forced_liq_pct
        FROM loans WHERE collateral_type = 'portfolio'
    """)
    loan = c.fetchone()
    if not loan:
        print("No margin loan found.")
        return

    loan_balance, freeze_pct, call_pct, liq_pct = loan

    # Unsecured credit line
    c.execute("SELECT balance FROM loans WHERE collateral_type IS NULL")
    credit_row = c.fetchone()
    credit_available = credit_row[0] if credit_row else 0

    conn.close()

    total_defense = cash_reserves + credit_available

    print(f"Portfolio Value:    ¥{portfolio_value:>14,.0f}")
    print(f"Loan Balance:      ¥{loan_balance:>14,.0f}")
    print(f"Current Ratio:       {loan_balance/portfolio_value:>12.1%}")
    print(f"Cash Reserves:     ¥{cash_reserves:>14,.0f}")
    print(f"Credit Line:       ¥{credit_available:>14,.0f}")
    print(f"Total Defense:     ¥{total_defense:>14,.0f}")
    print()
    print("=" * 90)
    print(f"{'Drop':>6} {'Collateral':>14} {'Ratio':>8} {'Status':>16} "
          f"{'Repay to 65%':>14} {'Repay to 85%':>14} {'Survive?':>10}")
    print("=" * 90)

    for drop in range(5, 75, 5):
        pct = drop / 100
        collateral = portfolio_value * (1 - pct)
        ratio = loan_balance / collateral if collateral > 0 else float('inf')

        # Determine status
        if ratio > liq_pct:
            status = "🔴 FORCED LIQ"
        elif ratio > call_pct:
            status = "🟠 MARGIN CALL"
        elif ratio > freeze_pct:
            status = "🟡 FROZEN"
        else:
            status = "🟢 OK"

        # Cash needed to bring ratio back to safe levels
        repay_to_65 = max(0, loan_balance - collateral * 0.65)
        repay_to_85 = max(0, loan_balance - collateral * 0.85)

        # Can we survive with available cash?
        can_survive = "✅ YES" if repay_to_85 <= total_defense else "❌ NO"

        print(f"{drop:>5}% ¥{collateral:>13,.0f} {ratio:>7.1%} {status:>16} "
              f"¥{repay_to_65:>13,.0f} ¥{repay_to_85:>13,.0f} {can_survive:>10}")

    # Find maximum survivable drawdown
    print()
    print("─" * 90)
    for drop in range(1, 100):
        collateral = portfolio_value * (1 - drop/100)
        if collateral <= 0:
            break
        ratio = loan_balance / collateral
        repay_to_85 = max(0, loan_balance - collateral * 0.85)
        if repay_to_85 > total_defense:
            print(f"⚡ Maximum survivable drawdown: -{drop-1}%")
            print(f"   At -{drop}%, you need ¥{repay_to_85:,.0f} but only have ¥{total_defense:,.0f}")
            break

if __name__ == "__main__":
    run_stress_test()

Portfolio Value:    ¥  125,135,000
Loan Balance:      ¥   50,000,000
Current Ratio:              39.9%
Cash Reserves:     ¥    4,500,000
Credit Line:       ¥    8,000,000
Total Defense:     ¥   12,500,000

==========================================================================================
  Drop     Collateral    Ratio           Status   Repay to 65%   Repay to 85%   Survive?
==========================================================================================
    5% ¥ 118,878,250    42.1%           🟢 OK  ¥            0  ¥            0     ✅ YES
   10% ¥ 112,621,500    44.4%           🟢 OK  ¥            0  ¥            0     ✅ YES
   15% ¥ 106,364,750    47.0%           🟢 OK  ¥            0  ¥            0     ✅ YES
   20% ¥ 100,108,000    49.9%           🟢 OK  ¥            0  ¥            0     ✅ YES
   25% ¥  93,851,250    53.3%           🟢 OK  ¥            0  ¥            0     ✅ YES
   30% ¥  87,594,500    57.1%           🟢 OK  ¥            0  ¥            0     ✅ YES
   35% ¥  81,337,750    61.5%        🟡 FROZEN  ¥    1,130,463  ¥            0     ✅ YES
   40% ¥  75,081,000    66.6%        🟡 FROZEN  ¥    1,197,350  ¥            0     ✅ YES
   45% ¥  68,824,250    72.6%     🟠 MARGIN CALL ¥    5,264,238  ¥            0     ✅ YES
   50% ¥  62,567,500    79.9%     🟠 MARGIN CALL ¥    9,331,125  ¥            0     ✅ YES
   55% ¥  56,310,750    88.8%     🔴 FORCED LIQ  ¥   13,397,963  ¥    2,135,863     ✅ YES
   60% ¥  50,054,000    99.9%     🔴 FORCED LIQ  ¥   17,464,900  ¥    7,454,100     ✅ YES
   65% ¥  43,797,250   114.2%     🔴 FORCED LIQ  ¥   21,531,788  ¥   12,772,338     ❌ NO
   70% ¥  37,540,500   133.2%     🔴 FORCED LIQ  ¥   25,598,675  ¥   18,090,575     ❌ NO

──────────────────────────────────────────────────────────────────────────────────────────
⚡ Maximum survivable drawdown: -62%
   At -63%, you need ¥13,101,398 but only have ¥12,500,000

# stress_chart.py
import matplotlib.pyplot as plt
import matplotlib.patches as mpatches
import sqlite3
from alm_schema import DB_PATH

def plot_stress_test():
    conn = sqlite3.connect(DB_PATH)
    c = conn.cursor()

    c.execute("""
        SELECT SUM(h.shares * p.close_price)
        FROM holdings h
        JOIN prices p ON h.ticker = p.ticker
        WHERE p.price_date = (SELECT MAX(price_date) FROM prices)
    """)
    portfolio = c.fetchone()[0]

    c.execute("SELECT balance, freeze_pct, margin_call_pct, forced_liq_pct FROM loans WHERE collateral_type = 'portfolio'")
    balance, freeze, call, liq = c.fetchone()
    conn.close()

    drops = list(range(0, 75))
    ratios = []
    for d in drops:
        collateral = portfolio * (1 - d/100)
        ratios.append(balance / collateral * 100 if collateral > 0 else 100)

    fig, ax = plt.subplots(figsize=(12, 6))
    fig.patch.set_facecolor('#0a0a0a')
    ax.set_facecolor('#0a0a0a')

    # Danger zones
    ax.axhspan(0, freeze*100, alpha=0.15, color='#00ff88', label=f'Safe (<{freeze:.0%})')
    ax.axhspan(freeze*100, call*100, alpha=0.15, color='#ffaa00', label=f'Frozen ({freeze:.0%}-{call:.0%})')
    ax.axhspan(call*100, liq*100, alpha=0.15, color='#ff6600', label=f'Margin Call ({call:.0%}-{liq:.0%})')
    ax.axhspan(liq*100, 120, alpha=0.15, color='#ff0000', label=f'Forced Liquidation (>{liq:.0%})')

    # Ratio line
    ax.plot(drops, ratios, color='#00ff88', linewidth=2.5, zorder=5)

    # Threshold lines
    for pct, color, style in [(freeze*100, '#ffaa00', '--'), (call*100, '#ff6600', '--'), (liq*100, '#ff0000', '-')]:
        ax.axhline(y=pct, color=color, linestyle=style, alpha=0.7, linewidth=1)

    ax.set_xlabel('Portfolio Drawdown (%)', color='#888', fontsize=12)
    ax.set_ylabel('Margin Ratio (%)', color='#888', fontsize=12)
    ax.set_title('Stress Test: Margin Ratio vs. Drawdown', color='white', fontsize=14, pad=20)
    ax.tick_params(colors='#888')
    ax.spines['bottom'].set_color('#333')
    ax.spines['left'].set_color('#333')
    ax.spines['top'].set_visible(False)
    ax.spines['right'].set_visible(False)
    ax.legend(loc='upper left', facecolor='#111', edgecolor='#333', labelcolor='#ccc')
    ax.set_xlim(0, 74)
    ax.set_ylim(30, 120)

    plt.tight_layout()
    plt.savefig('stress_test.png', dpi=150, facecolor='#0a0a0a')
    print("Chart saved: stress_test.png")

if __name__ == "__main__":
    plot_stress_test()