#!/usr/bin/env python3
"""
Step 2: Precedent Transaction Analysis

Objective: Establish a baseline for valuation multiples ("Takeout Premium")
by analyzing recent relevant cybersecurity and software M&A transactions.

This script:
1. Defines a dataset of precedent M&A transactions
2. Calculates EV/Revenue multiples for each transaction
3. Computes summary statistics (Mean, Median, 75th percentile)
4. Saves detailed data to CSV
5. Generates visualization
6. Appends summary to screening_summary.md
"""

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

# Set up paths
SESSION_DIR = Path('/app/sandbox/session_20260203_085757_d926b2c7d1fb')
RESULTS_DIR = SESSION_DIR / 'results'
FIGURES_DIR = SESSION_DIR / 'figures'

# Ensure directories exist
RESULTS_DIR.mkdir(parents=True, exist_ok=True)
FIGURES_DIR.mkdir(parents=True, exist_ok=True)

print("=" * 60)
print("Step 2: Precedent Transaction Analysis")
print("=" * 60)

# ============================================================================
# 1. Define Precedent Transaction Data
# ============================================================================
print("\n[1/5] Defining precedent transaction dataset...")

# Precedent M&A transactions in cybersecurity/software sector
# Format: Target / Acquirer / EV (Billions) / LTM Revenue (Billions) / Notes
precedent_data = [
    {
        'target': 'Splunk',
        'acquirer': 'Cisco',
        'ev_billions': 28.0,
        'ltm_revenue_billions': 4.2,
        'deal_type': 'Strategic',
        'notes': 'Large-cap observability platform'
    },
    {
        'target': 'Wiz',
        'acquirer': 'Google',
        'ev_billions': 23.0,
        'ltm_revenue_billions': 0.5,
        'deal_type': 'Strategic',
        'notes': 'Rumored - High-growth cloud security outlier'
    },
    {
        'target': 'Mandiant',
        'acquirer': 'Google',
        'ev_billions': 5.4,
        'ltm_revenue_billions': 0.56,
        'deal_type': 'Strategic',
        'notes': 'Threat intelligence leader'
    },
    {
        'target': 'Ping Identity',
        'acquirer': 'Thoma Bravo',
        'ev_billions': 2.8,
        'ltm_revenue_billions': 0.32,
        'deal_type': 'PE Buyout',
        'notes': 'Identity management specialist'
    },
    {
        'target': 'ForgeRock',
        'acquirer': 'Thoma Bravo',
        'ev_billions': 2.3,
        'ltm_revenue_billions': 0.25,
        'deal_type': 'PE Buyout',
        'notes': 'Identity platform'
    },
    {
        'target': 'SailPoint',
        'acquirer': 'Thoma Bravo',
        'ev_billions': 6.9,
        'ltm_revenue_billions': 0.44,
        'deal_type': 'PE Buyout',
        'notes': 'Identity governance leader'
    }
]

# Create DataFrame
df = pd.DataFrame(precedent_data)
print(f"  - Loaded {len(df)} precedent transactions")

# ============================================================================
# 2. Calculate EV/Revenue Multiples
# ============================================================================
print("\n[2/5] Calculating EV/Revenue multiples...")

# Calculate EV/Revenue multiple for each transaction
df['ev_revenue_multiple'] = df['ev_billions'] / df['ltm_revenue_billions']

# Format for display
df['ev_display'] = df['ev_billions'].apply(lambda x: f"${x:.1f}B")
df['revenue_display'] = df['ltm_revenue_billions'].apply(lambda x: f"${x:.2f}B")
df['multiple_display'] = df['ev_revenue_multiple'].apply(lambda x: f"{x:.1f}x")

print("\n  Transaction Details:")
print("-" * 80)
for _, row in df.iterrows():
    print(f"  {row['target']:15s} / {row['acquirer']:15s} | "
          f"EV: {row['ev_display']:>8s} | Rev: {row['revenue_display']:>8s} | "
          f"Multiple: {row['multiple_display']:>6s}")
print("-" * 80)

# ============================================================================
# 3. Compute Summary Statistics
# ============================================================================
print("\n[3/5] Computing summary statistics...")

# All transactions
mean_multiple = df['ev_revenue_multiple'].mean()
median_multiple = df['ev_revenue_multiple'].median()
p75_multiple = df['ev_revenue_multiple'].quantile(0.75)
min_multiple = df['ev_revenue_multiple'].min()
max_multiple = df['ev_revenue_multiple'].max()
std_multiple = df['ev_revenue_multiple'].std()

# Excluding Wiz (outlier) for sensitivity analysis
df_ex_wiz = df[df['target'] != 'Wiz']
mean_ex_wiz = df_ex_wiz['ev_revenue_multiple'].mean()
median_ex_wiz = df_ex_wiz['ev_revenue_multiple'].median()
p75_ex_wiz = df_ex_wiz['ev_revenue_multiple'].quantile(0.75)

print("\n  Summary Statistics (All Transactions):")
print(f"    - Mean Multiple:           {mean_multiple:.1f}x")
print(f"    - Median Multiple:         {median_multiple:.1f}x")
print(f"    - 75th Percentile:         {p75_multiple:.1f}x")
print(f"    - Min:                     {min_multiple:.1f}x")
print(f"    - Max:                     {max_multiple:.1f}x")
print(f"    - Std Dev:                 {std_multiple:.1f}x")

print("\n  Summary Statistics (Excluding Wiz - High-Growth Outlier):")
print(f"    - Mean Multiple:           {mean_ex_wiz:.1f}x")
print(f"    - Median Multiple:         {median_ex_wiz:.1f}x")
print(f"    - 75th Percentile:         {p75_ex_wiz:.1f}x")

# ============================================================================
# 4. Save Transaction Data to CSV
# ============================================================================
print("\n[4/5] Saving transaction data to CSV...")

# Prepare export DataFrame
export_df = df[['target', 'acquirer', 'deal_type', 'ev_billions',
                'ltm_revenue_billions', 'ev_revenue_multiple', 'notes']].copy()
export_df.columns = ['Target', 'Acquirer', 'Deal_Type', 'EV_Billions',
                     'LTM_Revenue_Billions', 'EV_Revenue_Multiple', 'Notes']

# Add summary statistics row
summary_row = pd.DataFrame([{
    'Target': '--- SUMMARY ---',
    'Acquirer': '',
    'Deal_Type': '',
    'EV_Billions': '',
    'LTM_Revenue_Billions': '',
    'EV_Revenue_Multiple': f'Mean: {mean_multiple:.1f}x | Median: {median_multiple:.1f}x | 75th: {p75_multiple:.1f}x',
    'Notes': ''
}])

export_df_with_summary = pd.concat([export_df, summary_row], ignore_index=True)

csv_path = RESULTS_DIR / 'precedent_transactions.csv'
export_df_with_summary.to_csv(csv_path, index=False)
print(f"  - Saved: {csv_path}")

# ============================================================================
# 5. Generate Visualization
# ============================================================================
print("\n[5/5] Generating visualization...")

# Set up publication-quality figure
plt.rcParams['font.family'] = 'sans-serif'
plt.rcParams['font.size'] = 10
plt.rcParams['axes.linewidth'] = 0.8

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

# Prepare data for bar chart
targets = df['target'].tolist()
multiples = df['ev_revenue_multiple'].tolist()
deal_types = df['deal_type'].tolist()

# Color based on deal type
colors = ['#1f77b4' if dt == 'Strategic' else '#2ca02c' for dt in deal_types]

# Create bar chart
bars = ax.bar(range(len(targets)), multiples, color=colors, edgecolor='black', linewidth=0.5)

# Add value labels on bars
for i, (bar, mult) in enumerate(zip(bars, multiples)):
    ax.text(bar.get_x() + bar.get_width()/2, bar.get_height() + 0.5,
            f'{mult:.1f}x', ha='center', va='bottom', fontsize=11, fontweight='bold')

# Add horizontal lines for statistics
ax.axhline(y=median_multiple, color='red', linestyle='--', linewidth=2,
           label=f'Median: {median_multiple:.1f}x')
ax.axhline(y=mean_multiple, color='orange', linestyle=':', linewidth=2,
           label=f'Mean: {mean_multiple:.1f}x')
ax.axhline(y=median_ex_wiz, color='purple', linestyle='-.', linewidth=1.5,
           label=f'Median (ex-Wiz): {median_ex_wiz:.1f}x')

# Customize x-axis
ax.set_xticks(range(len(targets)))
ax.set_xticklabels([f'{t}\n({a})' for t, a in zip(targets, df['acquirer'].tolist())],
                   rotation=0, ha='center', fontsize=9)

# Labels and title
ax.set_xlabel('Target Company (Acquirer)', fontsize=12, fontweight='bold')
ax.set_ylabel('EV/Revenue Multiple (x)', fontsize=12, fontweight='bold')
ax.set_title('Precedent M&A Transaction Multiples\nCybersecurity & Software Sector',
             fontsize=14, fontweight='bold', pad=15)

# Add legend with deal type explanation
legend_elements = [
    plt.Rectangle((0,0),1,1, facecolor='#1f77b4', edgecolor='black', label='Strategic Acquisition'),
    plt.Rectangle((0,0),1,1, facecolor='#2ca02c', edgecolor='black', label='PE Buyout'),
    plt.Line2D([0], [0], color='red', linestyle='--', linewidth=2, label=f'Median: {median_multiple:.1f}x'),
    plt.Line2D([0], [0], color='orange', linestyle=':', linewidth=2, label=f'Mean: {mean_multiple:.1f}x'),
    plt.Line2D([0], [0], color='purple', linestyle='-.', linewidth=1.5, label=f'Median (ex-Wiz): {median_ex_wiz:.1f}x'),
]
ax.legend(handles=legend_elements, loc='upper right', fontsize=9)

# Set y-axis limits with padding
ax.set_ylim(0, max(multiples) * 1.15)

# Add grid
ax.yaxis.grid(True, linestyle='--', alpha=0.3)
ax.set_axisbelow(True)

# Add annotation for Wiz outlier
wiz_idx = targets.index('Wiz')
ax.annotate('High-growth\noutlier', xy=(wiz_idx, multiples[wiz_idx]),
            xytext=(wiz_idx + 0.8, multiples[wiz_idx] + 3),
            fontsize=8, ha='center', color='gray',
            arrowprops=dict(arrowstyle='->', color='gray', lw=0.8))

plt.tight_layout()

# Save figure
fig_path = FIGURES_DIR / 'precedent_multiples_chart.png'
plt.savefig(fig_path, dpi=300, bbox_inches='tight', facecolor='white')
plt.close()
print(f"  - Saved: {fig_path}")

# ============================================================================
# 6. Append Summary to screening_summary.md
# ============================================================================
print("\n[6/6] Appending summary to screening_summary.md...")

summary_path = RESULTS_DIR / 'screening_summary.md'

# Read existing content
existing_content = ""
if summary_path.exists():
    with open(summary_path, 'r') as f:
        existing_content = f.read()

# Prepare new section
new_section = f"""

---

## Step 2: Precedent Transaction Analysis

### Objective
Establish a baseline for valuation multiples ("Takeout Premium") by analyzing recent relevant cybersecurity and software M&A transactions.

### Precedent Transactions Analyzed

| Target | Acquirer | Deal Type | EV | LTM Revenue | EV/Rev Multiple |
|--------|----------|-----------|-----|-------------|-----------------|
| Splunk | Cisco | Strategic | $28.0B | $4.2B | 6.7x |
| Wiz | Google | Strategic | $23.0B | $0.5B | 46.0x |
| Mandiant | Google | Strategic | $5.4B | $0.56B | 9.6x |
| Ping Identity | Thoma Bravo | PE Buyout | $2.8B | $0.32B | 8.8x |
| ForgeRock | Thoma Bravo | PE Buyout | $2.3B | $0.25B | 9.2x |
| SailPoint | Thoma Bravo | PE Buyout | $6.9B | $0.44B | 15.7x |

### Summary Statistics

**All Transactions:**
| Metric | Value |
|--------|-------|
| Mean Multiple | {mean_multiple:.1f}x |
| Median Multiple | {median_multiple:.1f}x |
| 75th Percentile | {p75_multiple:.1f}x |
| Min | {min_multiple:.1f}x |
| Max | {max_multiple:.1f}x |
| Std Dev | {std_multiple:.1f}x |

**Excluding Wiz (High-Growth Outlier):**
| Metric | Value |
|--------|-------|
| Mean Multiple | {mean_ex_wiz:.1f}x |
| Median Multiple | {median_ex_wiz:.1f}x |
| 75th Percentile | {p75_ex_wiz:.1f}x |

### Key Observations

1. **High Variance in Multiples**: The EV/Revenue multiples range from {min_multiple:.1f}x to {max_multiple:.1f}x, reflecting significant variability based on growth profiles and strategic value.

2. **Wiz as an Outlier**: Wiz's {multiples[wiz_idx]:.0f}x multiple is an extreme outlier driven by exceptional growth (~500% YoY) and cloud-native security positioning. This should be treated as a high-growth premium case, not the norm.

3. **PE Buyout Range**: Private equity transactions (Thoma Bravo) cluster between {df_ex_wiz[df_ex_wiz['deal_type'] == 'PE Buyout']['ev_revenue_multiple'].min():.1f}x - {df_ex_wiz[df_ex_wiz['deal_type'] == 'PE Buyout']['ev_revenue_multiple'].max():.1f}x, representing a more realistic floor for take-private valuations.

4. **Recommended Baseline for SentinelOne Valuation**:
   - **Conservative (Median ex-Wiz)**: {median_ex_wiz:.1f}x
   - **Base Case (Mean ex-Wiz)**: {mean_ex_wiz:.1f}x
   - **Optimistic (75th Percentile)**: {p75_multiple:.1f}x

### Outputs Generated
- `results/precedent_transactions.csv` - Full transaction data with calculated multiples
- `figures/precedent_multiples_chart.png` - Visual comparison of multiples

"""

# Write combined content
with open(summary_path, 'w') as f:
    f.write(existing_content + new_section)

print(f"  - Updated: {summary_path}")

# ============================================================================
# Final Summary
# ============================================================================
print("\n" + "=" * 60)
print("Step 2: Precedent Transaction Analysis - COMPLETE")
print("=" * 60)
print("\nKey Results:")
print(f"  - Transactions Analyzed: {len(df)}")
print(f"  - Median Multiple (All): {median_multiple:.1f}x")
print(f"  - Median Multiple (ex-Wiz): {median_ex_wiz:.1f}x")
print(f"  - 75th Percentile: {p75_multiple:.1f}x")
print("\nOutputs:")
print(f"  1. {csv_path}")
print(f"  2. {fig_path}")
print(f"  3. {summary_path} (updated)")
print("\nRecommended Valuation Multiples for SentinelOne:")
print(f"  - Conservative: {median_ex_wiz:.1f}x")
print(f"  - Base Case: {mean_ex_wiz:.1f}x")
print(f"  - Optimistic: {p75_multiple:.1f}x")