#!/usr/bin/env python3
"""
Step 4: Acquirer Financial Profile Analysis
Assesses the financial capacity of potential strategic acquirers
to fund a $10B-$14B acquisition of SentinelOne.

Author: K-Dense M&A Screening Tool
Date: 2026-02-03
"""

import yfinance as yf
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
from datetime import datetime
from pathlib import Path

# Session paths
SESSION_DIR = Path("/app/sandbox/session_20260203_085757_d926b2c7d1fb")
RESULTS_DIR = SESSION_DIR / "results"
FIGURES_DIR = SESSION_DIR / "figures"

# SentinelOne deal price benchmarks from Step 3
DEAL_PRICE_CONSERVATIVE = 8.8e9  # $8.8B
DEAL_PRICE_BASE = 9.6e9          # $9.6B
DEAL_PRICE_OPTIMISTIC = 13.6e9   # $13.6B

# Maximum leverage assumption for debt capacity
MAX_LEVERAGE_EBITDA = 3.0

# Potential acquirers
ACQUIRERS = {
    "PANW": "Palo Alto Networks",
    "CRWD": "CrowdStrike",
    "MSFT": "Microsoft",
    "GOOGL": "Alphabet (Google)",
    "CSCO": "Cisco Systems"
}


def fetch_financial_data(ticker: str) -> dict:
    """
    Fetch financial metrics for an acquirer using yfinance.

    Returns dict with:
    - Cash & Short Term Investments
    - Total Debt
    - Market Cap
    - LTM EBITDA
    - LTM Free Cash Flow
    """
    print(f"  Fetching data for {ticker}...")

    try:
        stock = yf.Ticker(ticker)
        info = stock.info

        # Get balance sheet data
        balance_sheet = stock.balance_sheet

        # Get cash flow statement
        cashflow = stock.cashflow

        # Get income statement for EBITDA
        financials = stock.financials

        # Market Cap
        market_cap = info.get('marketCap', 0)

        # Cash & Short Term Investments
        # Try multiple field names that yfinance might use
        cash = 0
        if not balance_sheet.empty:
            for field in ['Cash And Cash Equivalents', 'Cash', 'Cash And Short Term Investments',
                         'Cash Cash Equivalents And Short Term Investments']:
                if field in balance_sheet.index:
                    val = balance_sheet.loc[field].iloc[0]
                    if pd.notna(val):
                        cash = max(cash, float(val))

            # Also check for short term investments separately
            for field in ['Short Term Investments', 'Other Short Term Investments']:
                if field in balance_sheet.index:
                    val = balance_sheet.loc[field].iloc[0]
                    if pd.notna(val):
                        cash += float(val)

        # Fallback to info dict
        if cash == 0:
            cash = info.get('totalCash', 0) or 0

        # Total Debt
        total_debt = 0
        if not balance_sheet.empty:
            for field in ['Total Debt', 'Long Term Debt', 'Long Term Debt And Capital Lease Obligation']:
                if field in balance_sheet.index:
                    val = balance_sheet.loc[field].iloc[0]
                    if pd.notna(val):
                        total_debt = max(total_debt, float(val))

            # Add short term debt if exists
            for field in ['Short Long Term Debt', 'Current Debt', 'Current Debt And Capital Lease Obligation']:
                if field in balance_sheet.index:
                    val = balance_sheet.loc[field].iloc[0]
                    if pd.notna(val) and total_debt > 0:
                        # Don't double count - only add if not already in total
                        pass

        # Fallback
        if total_debt == 0:
            total_debt = info.get('totalDebt', 0) or 0

        # EBITDA from info (LTM)
        ebitda = info.get('ebitda', 0) or 0

        # If EBITDA not directly available, try to calculate from financials
        if ebitda == 0 and not financials.empty:
            operating_income = 0
            depreciation = 0

            for field in ['Operating Income', 'EBIT']:
                if field in financials.index:
                    val = financials.loc[field].iloc[0]
                    if pd.notna(val):
                        operating_income = float(val)
                        break

            if not cashflow.empty:
                for field in ['Depreciation And Amortization', 'Depreciation',
                             'Depreciation Amortization Depletion']:
                    if field in cashflow.index:
                        val = cashflow.loc[field].iloc[0]
                        if pd.notna(val):
                            depreciation = float(val)
                            break

            if operating_income != 0:
                ebitda = operating_income + depreciation

        # Free Cash Flow (Operating CF - CapEx)
        fcf = info.get('freeCashflow', 0) or 0
        operating_cf = info.get('operatingCashflow', 0) or 0

        if fcf == 0 and not cashflow.empty:
            ocf = 0
            capex = 0

            for field in ['Operating Cash Flow', 'Cash Flow From Continuing Operating Activities']:
                if field in cashflow.index:
                    val = cashflow.loc[field].iloc[0]
                    if pd.notna(val):
                        ocf = float(val)
                        break

            for field in ['Capital Expenditure', 'Capital Expenditures']:
                if field in cashflow.index:
                    val = cashflow.loc[field].iloc[0]
                    if pd.notna(val):
                        capex = abs(float(val))  # CapEx is typically negative
                        break

            if ocf != 0:
                fcf = ocf - capex

        return {
            'Ticker': ticker,
            'Company': ACQUIRERS[ticker],
            'Market_Cap_B': market_cap / 1e9 if market_cap else 0,
            'Cash_B': cash / 1e9 if cash else 0,
            'Total_Debt_B': total_debt / 1e9 if total_debt else 0,
            'EBITDA_B': ebitda / 1e9 if ebitda else 0,
            'FCF_B': fcf / 1e9 if fcf else 0,
            'Operating_CF_B': operating_cf / 1e9 if operating_cf else 0,
        }

    except Exception as e:
        print(f"    Error fetching {ticker}: {e}")
        return {
            'Ticker': ticker,
            'Company': ACQUIRERS[ticker],
            'Market_Cap_B': 0,
            'Cash_B': 0,
            'Total_Debt_B': 0,
            'EBITDA_B': 0,
            'FCF_B': 0,
            'Operating_CF_B': 0,
        }


def calculate_ability_to_pay(df: pd.DataFrame, deal_price_b: float) -> pd.DataFrame:
    """
    Calculate ability to pay metrics for each acquirer.

    Metrics:
    - Net Cash = Cash - Total Debt
    - Potential New Debt = Max Leverage (3.0x) * EBITDA
    - Dry Powder = Cash + Potential New Debt
    - Can Pay Cash = Dry Powder >= Deal Price
    - Stock Required = Deal Price - Dry Powder (if negative, 0)
    """
    print(f"\nCalculating ability to pay for deal price: ${deal_price_b:.1f}B")

    # Net Cash
    df['Net_Cash_B'] = df['Cash_B'] - df['Total_Debt_B']

    # Potential New Debt (3.0x EBITDA leverage capacity)
    df['Potential_New_Debt_B'] = df['EBITDA_B'] * MAX_LEVERAGE_EBITDA

    # Dry Powder = Cash + Potential New Debt (total acquisition capacity)
    df['Dry_Powder_B'] = df['Cash_B'] + df['Potential_New_Debt_B']

    # Compare to deal price
    df['Surplus_vs_Deal_B'] = df['Dry_Powder_B'] - deal_price_b

    # Determine payment method
    df['Can_Pay_100pct_Cash'] = df['Dry_Powder_B'] >= deal_price_b
    df['Stock_Required_B'] = np.maximum(0, deal_price_b - df['Dry_Powder_B'])

    # Categorize
    def categorize_buyer(row):
        if row['Dry_Powder_B'] >= deal_price_b * 1.5:
            return "Cash Rich - Easy Deal"
        elif row['Dry_Powder_B'] >= deal_price_b:
            return "Cash Buyer"
        elif row['Dry_Powder_B'] >= deal_price_b * 0.5:
            return "Needs Stock Component"
        else:
            return "Requires Significant Stock"

    df['Buyer_Category'] = df.apply(categorize_buyer, axis=1)

    # Deal price as % of Market Cap (affordability metric)
    df['Deal_as_Pct_of_MCap'] = (deal_price_b / df['Market_Cap_B']) * 100

    return df


def create_visualization(df: pd.DataFrame, deal_price_b: float) -> None:
    """
    Create a stacked bar chart showing acquirer capacity vs deal price.
    """
    print("\nGenerating visualization...")

    fig, ax = plt.subplots(figsize=(12, 8))

    companies = df['Company'].tolist()
    x = np.arange(len(companies))
    width = 0.6

    # Stacked bar: Cash + Potential New Debt = Dry Powder
    bars_cash = ax.bar(x, df['Cash_B'], width, label='Cash & Investments',
                       color='#2E86AB', edgecolor='white', linewidth=0.5)
    bars_debt = ax.bar(x, df['Potential_New_Debt_B'], width, bottom=df['Cash_B'],
                       label='Debt Capacity (3x EBITDA)', color='#A23B72',
                       edgecolor='white', linewidth=0.5)

    # Deal price line
    ax.axhline(y=deal_price_b, color='#F18F01', linestyle='--', linewidth=2.5,
               label=f'Optimistic Deal Price (${deal_price_b:.1f}B)')

    # Base case deal price line
    base_deal = DEAL_PRICE_BASE / 1e9
    ax.axhline(y=base_deal, color='#C73E1D', linestyle=':', linewidth=2,
               label=f'Base Case Deal Price (${base_deal:.1f}B)')

    # Annotations
    for i, (idx, row) in enumerate(df.iterrows()):
        total = row['Dry_Powder_B']
        ax.annotate(f'${total:.1f}B', xy=(i, total + 2), ha='center',
                    fontsize=10, fontweight='bold')

        # Category label below
        category = row['Buyer_Category']
        color = '#2E86AB' if 'Cash' in category else '#C73E1D'
        ax.annotate(category, xy=(i, -12), ha='center', fontsize=8,
                    color=color, style='italic')

    ax.set_xlabel('Potential Acquirer', fontsize=12)
    ax.set_ylabel('Acquisition Capacity ($B)', fontsize=12)
    ax.set_title('Acquirer Financial Capacity vs. SentinelOne Deal Price\n' +
                 '(Cash + Debt Capacity @ 3.0x EBITDA Leverage)', fontsize=14, fontweight='bold')
    ax.set_xticks(x)
    ax.set_xticklabels(companies, rotation=15, ha='right')
    ax.legend(loc='upper right', fontsize=10)

    # Y-axis formatting
    ax.set_ylim(0, max(df['Dry_Powder_B'].max() * 1.2, deal_price_b * 1.5))
    ax.yaxis.set_major_formatter(plt.FuncFormatter(lambda x, p: f'${x:.0f}B'))

    # Grid
    ax.grid(axis='y', alpha=0.3, linestyle='-')
    ax.set_axisbelow(True)

    plt.tight_layout()
    plt.savefig(FIGURES_DIR / 'acquirer_capacity.png', dpi=300, bbox_inches='tight',
                facecolor='white', edgecolor='none')
    plt.close()
    print(f"  Saved: {FIGURES_DIR / 'acquirer_capacity.png'}")


def create_detailed_comparison(df: pd.DataFrame) -> None:
    """
    Create a more detailed comparison chart.
    """
    fig, axes = plt.subplots(2, 2, figsize=(14, 10))

    companies = df['Company'].tolist()
    x = np.arange(len(companies))

    # 1. Market Cap comparison
    ax1 = axes[0, 0]
    bars1 = ax1.bar(x, df['Market_Cap_B'], color='#2E86AB', alpha=0.8)
    ax1.set_title('Market Capitalization', fontweight='bold')
    ax1.set_ylabel('$B')
    ax1.set_xticks(x)
    ax1.set_xticklabels([c.split()[0] for c in companies], rotation=45, ha='right')
    ax1.bar_label(bars1, fmt='$%.0fB', fontsize=8)

    # 2. Net Cash Position
    ax2 = axes[0, 1]
    colors = ['#2E86AB' if v >= 0 else '#C73E1D' for v in df['Net_Cash_B']]
    bars2 = ax2.bar(x, df['Net_Cash_B'], color=colors, alpha=0.8)
    ax2.set_title('Net Cash Position (Cash - Debt)', fontweight='bold')
    ax2.set_ylabel('$B')
    ax2.axhline(y=0, color='black', linestyle='-', linewidth=0.5)
    ax2.set_xticks(x)
    ax2.set_xticklabels([c.split()[0] for c in companies], rotation=45, ha='right')
    ax2.bar_label(bars2, fmt='$%.1fB', fontsize=8)

    # 3. EBITDA and FCF
    ax3 = axes[1, 0]
    width = 0.35
    bars3a = ax3.bar(x - width/2, df['EBITDA_B'], width, label='LTM EBITDA', color='#2E86AB')
    bars3b = ax3.bar(x + width/2, df['FCF_B'], width, label='LTM Free Cash Flow', color='#A23B72')
    ax3.set_title('Profitability Metrics', fontweight='bold')
    ax3.set_ylabel('$B')
    ax3.set_xticks(x)
    ax3.set_xticklabels([c.split()[0] for c in companies], rotation=45, ha='right')
    ax3.legend()

    # 4. Deal as % of Market Cap
    ax4 = axes[1, 1]
    deal_pct = df['Deal_as_Pct_of_MCap'].tolist()
    colors4 = ['#2E86AB' if p < 10 else '#F18F01' if p < 50 else '#C73E1D' for p in deal_pct]
    bars4 = ax4.bar(x, deal_pct, color=colors4, alpha=0.8)
    ax4.set_title(f'Deal Size as % of Acquirer Market Cap\n(${DEAL_PRICE_OPTIMISTIC/1e9:.1f}B Optimistic Deal)', fontweight='bold')
    ax4.set_ylabel('% of Market Cap')
    ax4.set_xticks(x)
    ax4.set_xticklabels([c.split()[0] for c in companies], rotation=45, ha='right')
    ax4.axhline(y=10, color='gray', linestyle='--', alpha=0.5, label='10% Threshold')
    ax4.bar_label(bars4, fmt='%.1f%%', fontsize=8)

    plt.suptitle('Acquirer Financial Profile Comparison', fontsize=16, fontweight='bold', y=1.02)
    plt.tight_layout()
    plt.savefig(FIGURES_DIR / 'acquirer_detailed_comparison.png', dpi=300, bbox_inches='tight',
                facecolor='white', edgecolor='none')
    plt.close()
    print(f"  Saved: {FIGURES_DIR / 'acquirer_detailed_comparison.png'}")


def update_screening_summary(df: pd.DataFrame, deal_price_b: float) -> None:
    """
    Append acquirer analysis section to screening_summary.md
    """
    print("\nUpdating screening summary...")

    summary_path = RESULTS_DIR / 'screening_summary.md'

    acquirer_section = f"""
---

## Step 4: Acquirer Financial Profile Analysis

### Objective
Assess the financial capacity of potential strategic acquirers to fund a $10B-$14B acquisition of SentinelOne.

### Potential Acquirers Analyzed

| Company | Ticker | Market Cap | Cash & Investments | Total Debt | Net Cash | EBITDA | FCF |
|---------|--------|------------|-------------------|------------|----------|--------|-----|
"""

    for _, row in df.iterrows():
        acquirer_section += f"| {row['Company']} | {row['Ticker']} | ${row['Market_Cap_B']:.1f}B | ${row['Cash_B']:.1f}B | ${row['Total_Debt_B']:.1f}B | ${row['Net_Cash_B']:.1f}B | ${row['EBITDA_B']:.1f}B | ${row['FCF_B']:.1f}B |\n"

    acquirer_section += f"""
### Ability to Pay Analysis

**Assumptions:**
- Maximum Leverage: 3.0x EBITDA
- Target Deal Price (Optimistic): ${deal_price_b:.1f}B
- Dry Powder = Cash + (3.0x EBITDA debt capacity)

| Company | Dry Powder | vs Deal Price | Buyer Category | Stock Required |
|---------|------------|---------------|----------------|----------------|
"""

    for _, row in df.iterrows():
        surplus = row['Surplus_vs_Deal_B']
        surplus_str = f"+${surplus:.1f}B" if surplus >= 0 else f"-${abs(surplus):.1f}B"
        stock_req = f"${row['Stock_Required_B']:.1f}B" if row['Stock_Required_B'] > 0 else "None"
        acquirer_section += f"| {row['Company']} | ${row['Dry_Powder_B']:.1f}B | {surplus_str} | {row['Buyer_Category']} | {stock_req} |\n"

    acquirer_section += f"""
### Key Findings

"""

    # Generate key findings based on analysis
    cash_buyers = df[df['Can_Pay_100pct_Cash'] == True]
    stock_buyers = df[df['Can_Pay_100pct_Cash'] == False]

    acquirer_section += f"1. **Cash-Rich Acquirers ({len(cash_buyers)} of {len(df)})**: "
    if len(cash_buyers) > 0:
        names = ', '.join(cash_buyers['Company'].tolist())
        acquirer_section += f"{names} have sufficient financial capacity to fund an all-cash deal.\n\n"
    else:
        acquirer_section += "No acquirers can fund 100% cash at the optimistic deal price.\n\n"

    acquirer_section += f"2. **Stock-Dependent Acquirers ({len(stock_buyers)} of {len(df)})**: "
    if len(stock_buyers) > 0:
        names = ', '.join(stock_buyers['Company'].tolist())
        acquirer_section += f"{names} would need to include a stock component or pursue a lower valuation.\n\n"
    else:
        acquirer_section += "All acquirers have sufficient cash capacity.\n\n"

    # Most capable acquirer
    best = df.loc[df['Dry_Powder_B'].idxmax()]
    acquirer_section += f"3. **Most Capable Acquirer**: {best['Company']} with ${best['Dry_Powder_B']:.1f}B in dry powder, representing a ${best['Surplus_vs_Deal_B']:.1f}B surplus over the optimistic deal price.\n\n"

    # Deal impact analysis
    small_deal_acquirers = df[df['Deal_as_Pct_of_MCap'] < 10]
    acquirer_section += f"4. **Deal Size Relativity**: For "
    if len(small_deal_acquirers) > 0:
        names = ', '.join(small_deal_acquirers['Company'].tolist())
        acquirer_section += f"{names}, the deal represents <10% of market cap, making it highly digestible from a strategic perspective.\n\n"
    else:
        acquirer_section += "all pure-play cybersecurity acquirers, this would be a transformative deal (>10% of market cap).\n\n"

    acquirer_section += """### Visual Summary
![Acquirer Capacity Chart](../figures/acquirer_capacity.png)

![Detailed Comparison](../figures/acquirer_detailed_comparison.png)

### Outputs Generated
- `results/acquirer_financials.csv` - Complete financial metrics for all acquirers
- `figures/acquirer_capacity.png` - Capacity vs. deal price visualization
- `figures/acquirer_detailed_comparison.png` - Multi-metric comparison

### Next Steps
- **Step 5**: Strategic Fit Analysis - Evaluate synergies and strategic rationale for each acquirer
- **Step 6**: Accretion/Dilution Modeling - Model EPS impact for potential transactions

"""

    # Append to existing summary
    with open(summary_path, 'a') as f:
        f.write(acquirer_section)

    print(f"  Updated: {summary_path}")


def main():
    """Main execution function."""
    print("=" * 60)
    print("Step 4: Acquirer Financial Profile Analysis")
    print("=" * 60)
    print(f"Analysis Date: {datetime.now().strftime('%Y-%m-%d %H:%M:%S')}")
    print(f"Target Deal Price (Optimistic): ${DEAL_PRICE_OPTIMISTIC/1e9:.1f}B")
    print(f"Max Leverage Assumption: {MAX_LEVERAGE_EBITDA}x EBITDA")
    print()

    # Fetch financial data for all acquirers
    print("Fetching financial data for potential acquirers...")
    results = []
    for i, ticker in enumerate(ACQUIRERS.keys()):
        print(f"  [{i+1}/{len(ACQUIRERS)}] Processing {ticker}...")
        data = fetch_financial_data(ticker)
        results.append(data)

    # Create DataFrame
    df = pd.DataFrame(results)
    print(f"\nData collected for {len(df)} acquirers")

    # Calculate ability to pay metrics
    deal_price_b = DEAL_PRICE_OPTIMISTIC / 1e9
    df = calculate_ability_to_pay(df, deal_price_b)

    # Display summary
    print("\n" + "=" * 60)
    print("Acquirer Financial Summary")
    print("=" * 60)
    for _, row in df.iterrows():
        print(f"\n{row['Company']} ({row['Ticker']})")
        print(f"  Market Cap: ${row['Market_Cap_B']:.1f}B")
        print(f"  Cash: ${row['Cash_B']:.1f}B | Debt: ${row['Total_Debt_B']:.1f}B | Net Cash: ${row['Net_Cash_B']:.1f}B")
        print(f"  EBITDA: ${row['EBITDA_B']:.1f}B | FCF: ${row['FCF_B']:.1f}B")
        print(f"  Dry Powder: ${row['Dry_Powder_B']:.1f}B")
        print(f"  Category: {row['Buyer_Category']}")
        print(f"  Deal as % of MCap: {row['Deal_as_Pct_of_MCap']:.1f}%")

    # Save to CSV
    csv_path = RESULTS_DIR / 'acquirer_financials.csv'
    df.to_csv(csv_path, index=False)
    print(f"\nSaved: {csv_path}")

    # Create visualizations
    create_visualization(df, deal_price_b)
    create_detailed_comparison(df)

    # Update screening summary
    update_screening_summary(df, deal_price_b)

    print("\n" + "=" * 60)
    print("Step 4 Complete!")
    print("=" * 60)
    print("\nOutputs:")
    print(f"  - {csv_path}")
    print(f"  - {FIGURES_DIR / 'acquirer_capacity.png'}")
    print(f"  - {FIGURES_DIR / 'acquirer_detailed_comparison.png'}")
    print(f"  - {RESULTS_DIR / 'screening_summary.md'} (updated)")

    return df


if __name__ == "__main__":
    df = main()