"""
================================================================================
LONG-TERM SUSTAINABILITY ANALYSIS
================================================================================
AstraZeneca Cash Flow Forecasting - Datathon 2025

Purpose: Analyze long-term cash flow sustainability and structural patterns
- Cash flow sustainability metrics
- Operating vs Investing vs Financing activities
- Structural trend analysis
- Cash conversion cycle insights
- Long-term patterns finance leadership should monitor
"""

import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import seaborn as sns
from datetime import datetime
from scipy import stats
import warnings
warnings.filterwarnings('ignore')
import os

# AstraZeneca Color Palette
AZ_COLORS = {
    'mulberry': '#830051',
    'lime_green': '#C4D600',
    'navy': '#003865',
    'graphite': '#3F4444',
    'light_blue': '#68D2DF',
    'magenta': '#D0006F',
    'purple': '#3C1053',
    'gold': '#F0AB00'
}

print("=" * 80)
print("LONG-TERM SUSTAINABILITY ANALYSIS")
print("=" * 80)

# =============================================================================
# LOAD DATA
# =============================================================================
print("\n[1/6] Loading data...")

df = pd.read_csv('data_cleaned_weekly.csv')
df['Week_Start'] = pd.to_datetime(df['Week_Start'])
df = df.sort_values('Week_Start').reset_index(drop=True)

forecast_df = pd.read_csv('FORECAST_OUTPUT_FINAL.csv')
forecast_df['Week_Start'] = pd.to_datetime(forecast_df['Week_Start'])

balance_df = pd.read_csv('data_cash_balance_processed.csv')
total_starting_balance = balance_df['Carryforward Balance (USD)'].sum()

print(f"   Historical: {len(df)} weeks")
print(f"   Forecast: {len(forecast_df)} weeks")

# =============================================================================
# CLASSIFY CASH FLOW ACTIVITIES
# =============================================================================
print("\n[2/6] Classifying cash flow activities...")

# Operating Activities (day-to-day operations)
operating_categories = ['AR', 'AP', 'Netting_AP', 'Netting_AR', 'Payroll', 'Tax_payable', 'Other_receipt']
operating_cols = [f'Net_Cash_{cat}' for cat in operating_categories if f'Net_Cash_{cat}' in df.columns]

# Investing Activities (capital expenditures, investments)
investing_categories = []  # Not clearly identified in data, assume minimal
investing_cols = []

# Financing Activities (loans, equity)
financing_categories = ['Loan_receipt', 'Loan_payment', 'Loan_payment_and_interest_charges']
financing_cols = [f'Net_Cash_{cat}' for cat in financing_categories if f'Net_Cash_{cat}' in df.columns]

# Calculate by activity type
df['Operating_CF'] = df[operating_cols].sum(axis=1) if operating_cols else 0
df['Investing_CF'] = df[investing_cols].sum(axis=1) if investing_cols else 0
df['Financing_CF'] = df[financing_cols].sum(axis=1) if financing_cols else 0

# Historical totals
operating_total = df['Operating_CF'].sum()
financing_total = df['Financing_CF'].sum()
investing_total = df['Investing_CF'].sum() if 'Investing_CF' in df.columns else 0

print(f"   Operating Activities: ${operating_total/1e6:.2f}M")
print(f"   Financing Activities: ${financing_total/1e6:.2f}M")
print(f"   Investing Activities: ${investing_total/1e6:.2f}M")

# =============================================================================
# SUSTAINABILITY METRICS
# =============================================================================
print("\n[3/6] Calculating sustainability metrics...")

# 1. Cash Flow Sustainability Ratio
# = Operating Cash Flow / Total Cash Flow
total_cf = df['Net_Cash_Flow_USD'].sum()
sustainability_ratio = operating_total / total_cf if total_cf != 0 else np.nan

# 2. Operating Cash Flow Trend
weeks = np.arange(len(df))
slope_operating, intercept_operating, r_operating, p_operating, _ = stats.linregress(
    weeks, df['Operating_CF'].values
)

# 3. Cash Flow Volatility (Coefficient of Variation)
cf_mean = df['Net_Cash_Flow_USD'].mean()
cf_std = df['Net_Cash_Flow_USD'].std()
cv = cf_std / abs(cf_mean) if cf_mean != 0 else np.nan

# 4. Positive Cash Flow Weeks Ratio
positive_weeks = (df['Net_Cash_Flow_USD'] > 0).sum()
positive_ratio = positive_weeks / len(df) * 100

# 5. Cash Flow Consistency (rolling 4-week average stability)
rolling_4w = df['Net_Cash_Flow_USD'].rolling(window=4).mean()
rolling_volatility = rolling_4w.std()

# 6. Forecast Sustainability
forecast_operating = forecast_df[['Net_Cash_AR', 'Net_Cash_AP', 'Net_Cash_Netting_AP', 
                                   'Net_Cash_Payroll', 'Net_Cash_Tax_payable']].sum(axis=1).sum()
forecast_total = forecast_df['Net_Cash_Flow_Total_USD'].sum()
forecast_sustainability = forecast_operating / forecast_total if forecast_total != 0 else np.nan

print(f"   Sustainability Ratio: {sustainability_ratio:.2f}")
print(f"   Operating CF Trend: ${slope_operating/1e6:.2f}M/week ({'increasing' if slope_operating > 0 else 'decreasing'})")
print(f"   Coefficient of Variation: {cv:.2f}x")
print(f"   Positive Weeks Ratio: {positive_ratio:.1f}%")

# =============================================================================
# STRUCTURAL PATTERN ANALYSIS
# =============================================================================
print("\n[4/6] Analyzing structural patterns...")

# Monthly structural patterns
df['Month'] = df['Week_Start'].dt.month
df['Quarter'] = df['Week_Start'].dt.quarter

monthly_operating = df.groupby('Month')['Operating_CF'].mean()
quarterly_operating = df.groupby('Quarter')['Operating_CF'].mean()

# Long-term trend (using moving average)
df['MA_4W'] = df['Net_Cash_Flow_USD'].rolling(window=4).mean()
df['MA_12W'] = df['Net_Cash_Flow_USD'].rolling(window=12).mean()

# Structural break detection (significant changes in pattern)
ma_4w_diff = df['MA_4W'].diff()
structural_breaks = np.where(np.abs(ma_4w_diff) > 2 * cf_std)[0]

# Cash conversion cycle proxy (AR collection vs AP payment timing)
# Average days between AR and AP (simplified)
ar_weeks = df[df['Net_Cash_AR'] > 0]['Week_Number'].values
ap_weeks = df[df['Net_Cash_AP'] < 0]['Week_Number'].values

print(f"   Structural breaks detected: {len(structural_breaks)} weeks")
print(f"   Quarterly Operating CF: Q1=${quarterly_operating.iloc[0]/1e6:.1f}M, Q2=${quarterly_operating.iloc[1]/1e6:.1f}M, Q3=${quarterly_operating.iloc[2]/1e6:.1f}M")

# =============================================================================
# LONG-TERM MONITORING PATTERNS
# =============================================================================
print("\n[5/6] Identifying patterns for finance leadership...")

monitoring_patterns = []

# Pattern 1: Monthly Seasonality
monthly_avg_cf = df.groupby('Month')['Net_Cash_Flow_USD'].mean()
strongest_month = monthly_avg_cf.idxmax()
weakest_month = monthly_avg_cf.idxmin()

monitoring_patterns.append({
    'Pattern': 'Monthly Seasonality',
    'Description': f'Strongest month: {strongest_month} (${monthly_avg_cf[strongest_month]/1e6:.1f}M avg), Weakest: {weakest_month} (${monthly_avg_cf[weakest_month]/1e6:.1f}M avg)',
    'Action': 'Plan for seasonal variations',
    'Priority': 'High'
})

# Pattern 2: AR Collection Cycle
ar_positive_weeks = df[df['Net_Cash_AR'] > df['Net_Cash_AR'].quantile(0.75)]['Week_Number'].values
ar_cycle = np.diff(ar_positive_weeks).mean() if len(ar_positive_weeks) > 1 else np.nan

monitoring_patterns.append({
    'Pattern': 'AR Collection Cycle',
    'Description': f'Large AR collections occur approximately every {ar_cycle:.1f} weeks',
    'Action': 'Monitor AR collection timing for liquidity planning',
    'Priority': 'High'
})

# Pattern 3: Netting Operations Pattern
netting_large = df[df['Net_Cash_Netting_AP'] < df['Net_Cash_Netting_AP'].quantile(0.25)]['Week_Number'].values
netting_cycle = np.diff(netting_large).mean() if len(netting_large) > 1 else np.nan

monitoring_patterns.append({
    'Pattern': 'Netting Operations Cycle',
    'Description': f'Large netting outflows occur approximately every {netting_cycle:.1f} weeks',
    'Action': 'Coordinate netting timing with AR collections',
    'Priority': 'High'
})

# Pattern 4: Entity Concentration Risk
entity_cols = [col for col in df.columns if col.startswith('Net_Cash_') and 
               col.split('_')[-1] in ['TW10', 'MY10', 'ID10', 'KR10', 'PH10', 'SS10', 'TH10', 'VN20']]
entity_totals = {col.split('_')[-1]: df[col].sum() for col in entity_cols}
top_entity = max(entity_totals.items(), key=lambda x: abs(x[1]))
top_entity_pct = abs(top_entity[1]) / abs(total_cf) * 100 if total_cf != 0 else 0

monitoring_patterns.append({
    'Pattern': 'Entity Concentration',
    'Description': f'{top_entity[0]} represents {top_entity_pct:.1f}% of total cash flow',
    'Action': 'Monitor entity-specific risks',
    'Priority': 'Medium'
})

# Pattern 5: Volatility Trend
early_volatility = df.iloc[:22]['Net_Cash_Flow_USD'].std()
late_volatility = df.iloc[22:]['Net_Cash_Flow_USD'].std()
volatility_change = ((late_volatility - early_volatility) / early_volatility) * 100 if early_volatility > 0 else 0

monitoring_patterns.append({
    'Pattern': 'Volatility Trend',
    'Description': f'Volatility {"increased" if volatility_change > 0 else "decreased"} by {abs(volatility_change):.1f}% over time',
    'Action': 'Adjust forecast uncertainty accordingly',
    'Priority': 'Medium'
})

patterns_df = pd.DataFrame(monitoring_patterns)

# =============================================================================
# CREATE VISUALIZATION
# =============================================================================
print("\n[6/6] Creating sustainability visualization...")

os.makedirs('dashboard', exist_ok=True)

fig = plt.figure(figsize=(22, 16))
fig.suptitle('LONG-TERM SUSTAINABILITY ANALYSIS\nAstraZeneca Cash Flow Forecasting', 
             fontsize=18, fontweight='bold', color=AZ_COLORS['navy'], y=0.98)

# Plot 1: Operating vs Financing Activities
ax1 = fig.add_subplot(2, 3, 1)

weeks = df['Week_Number'].values
ax1.plot(weeks, df['Operating_CF']/1e6, 'o-', color=AZ_COLORS['navy'], linewidth=2, 
         markersize=4, label='Operating CF', alpha=0.8)
ax1.plot(weeks, df['Financing_CF']/1e6, 's-', color=AZ_COLORS['purple'], linewidth=2, 
         markersize=4, label='Financing CF', alpha=0.8)
ax1.axhline(y=0, color=AZ_COLORS['graphite'], linewidth=1, linestyle='--')

ax1.set_xlabel('Week Number', fontweight='bold')
ax1.set_ylabel('Cash Flow (Millions USD)', fontweight='bold')
ax1.set_title('Operating vs Financing Activities', fontweight='bold', fontsize=11)
ax1.legend()
ax1.grid(True, alpha=0.3)

# Plot 2: Long-Term Trend (Moving Averages)
ax2 = fig.add_subplot(2, 3, 2)

ax2.plot(weeks, df['Net_Cash_Flow_USD']/1e6, 'o', color=AZ_COLORS['graphite'], 
         markersize=3, alpha=0.5, label='Weekly CF')
ax2.plot(weeks, df['MA_4W']/1e6, '-', color=AZ_COLORS['navy'], linewidth=2, 
         label='4-Week MA', alpha=0.8)
ax2.plot(weeks, df['MA_12W']/1e6, '-', color=AZ_COLORS['lime_green'], linewidth=3, 
         label='12-Week MA', alpha=0.8)

# Trend line
z = np.polyfit(weeks, df['Net_Cash_Flow_USD'], 1)
p = np.poly1d(z)
ax2.plot(weeks, p(weeks)/1e6, '--', color=AZ_COLORS['magenta'], linewidth=2, 
         label=f'Trend: ${z[0]/1e6:.2f}M/week')

ax2.axhline(y=0, color=AZ_COLORS['graphite'], linewidth=1, linestyle='--')
ax2.set_xlabel('Week Number', fontweight='bold')
ax2.set_ylabel('Cash Flow (Millions USD)', fontweight='bold')
ax2.set_title('Long-Term Trend Analysis', fontweight='bold', fontsize=11)
ax2.legend(loc='best')
ax2.grid(True, alpha=0.3)

# Plot 3: Monthly Structural Patterns
ax3 = fig.add_subplot(2, 3, 3)

month_names = ['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct']
monthly_data = monthly_avg_cf.values[:10] if len(monthly_avg_cf) >= 10 else monthly_avg_cf.values

colors_month = [AZ_COLORS['lime_green'] if v > 0 else AZ_COLORS['magenta'] for v in monthly_data]
bars = ax3.bar(month_names[:len(monthly_data)], monthly_data/1e6, color=colors_month, 
               edgecolor=AZ_COLORS['navy'], linewidth=1.5)
ax3.axhline(y=0, color=AZ_COLORS['graphite'], linewidth=2)
ax3.set_ylabel('Average Cash Flow (Millions USD)', fontweight='bold')
ax3.set_title('Monthly Structural Patterns', fontweight='bold', fontsize=11)
ax3.grid(True, alpha=0.3, axis='y')

for bar, val in zip(bars, monthly_data):
    height = bar.get_height()
    ax3.text(bar.get_x() + bar.get_width()/2., height + (0.05 if height > 0 else -0.1),
             f'${val/1e6:.1f}M', ha='center', va='bottom' if height > 0 else 'top', 
             fontweight='bold', fontsize=8)

# Plot 4: Sustainability Metrics Summary
ax4 = fig.add_subplot(2, 3, 4)
ax4.axis('off')

sustainability_text = f"""
SUSTAINABILITY METRICS
=====================

CASH FLOW SUSTAINABILITY
------------------------
Operating CF Ratio:    {sustainability_ratio:.2f}
  (Operating / Total)

Operating CF Trend:    ${slope_operating/1e6:.2f}M/week
  ({'Increasing' if slope_operating > 0 else 'Decreasing'})

Forecast Sustainability: {forecast_sustainability:.2f}
  (Operating / Total in forecast)

VOLATILITY ANALYSIS
-------------------
Coefficient of Var:    {cv:.2f}x
  ({'High volatility' if cv > 1.0 else 'Moderate' if cv > 0.5 else 'Low volatility'})

Positive Weeks:        {positive_ratio:.1f}%
  ({positive_weeks} out of {len(df)} weeks)

Rolling 4W Volatility: ${rolling_volatility/1e6:.2f}M

INTERPRETATION
--------------
{'✅ Sustainable' if sustainability_ratio > 0.8 and slope_operating > 0 else '⚠️ Monitor' if sustainability_ratio > 0.5 else '❌ Unsustainable'}
Operating activities drive cash flow.
{'Stable' if cv < 0.5 else 'Moderate' if cv < 1.0 else 'Volatile'} cash flow pattern.
"""

ax4.text(0.05, 0.95, sustainability_text, transform=ax4.transAxes, fontsize=9,
         verticalalignment='top', fontfamily='monospace',
         bbox=dict(boxstyle='round', facecolor=AZ_COLORS['light_blue'], alpha=0.2))

# Plot 5: Activity Type Breakdown
ax5 = fig.add_subplot(2, 3, 5)

activities = ['Operating', 'Financing', 'Investing']
activity_totals = [operating_total/1e6, financing_total/1e6, investing_total/1e6]
colors_act = [AZ_COLORS['navy'], AZ_COLORS['purple'], AZ_COLORS['gold']]

bars = ax5.bar(activities, activity_totals, color=colors_act, edgecolor=AZ_COLORS['navy'], linewidth=2)
ax5.axhline(y=0, color=AZ_COLORS['graphite'], linewidth=2)
ax5.set_ylabel('Total Cash Flow (Millions USD)', fontweight='bold')
ax5.set_title('Activity Type Breakdown (44 Weeks)', fontweight='bold', fontsize=11)

for bar, val in zip(bars, activity_totals):
    height = bar.get_height()
    ax5.text(bar.get_x() + bar.get_width()/2., height + (0.2 if height > 0 else -0.3),
             f'${val:.1f}M', ha='center', va='bottom' if height > 0 else 'top', 
             fontweight='bold', fontsize=10)

ax5.grid(True, alpha=0.3, axis='y')

# Plot 6: Monitoring Patterns for Leadership
ax6 = fig.add_subplot(2, 3, 6)
ax6.axis('off')

patterns_text = f"""
PATTERNS FOR FINANCE LEADERSHIP
================================

1. MONTHLY SEASONALITY
   Strongest: Month {strongest_month} (${monthly_avg_cf[strongest_month]/1e6:.1f}M)
   Weakest: Month {weakest_month} (${monthly_avg_cf[weakest_month]/1e6:.1f}M)
   → Plan for seasonal variations

2. AR COLLECTION CYCLE
   Large collections every {ar_cycle:.1f} weeks
   → Monitor AR timing for liquidity

3. NETTING OPERATIONS
   Large outflows every {netting_cycle:.1f} weeks
   → Coordinate with AR collections

4. ENTITY CONCENTRATION
   {top_entity[0]} = {top_entity_pct:.1f}% of total CF
   → Monitor entity-specific risks

5. VOLATILITY TREND
   {'Increased' if volatility_change > 0 else 'Decreased'} by {abs(volatility_change):.1f}%
   → Adjust forecast uncertainty

LONG-TERM MONITORING
--------------------
• Operating activities: {sustainability_ratio*100:.0f}% of cash flow
• Trend: {'Positive' if slope_operating > 0 else 'Negative'} (${slope_operating/1e6:.2f}M/week)
• Sustainability: {'GOOD' if sustainability_ratio > 0.8 else 'MODERATE' if sustainability_ratio > 0.5 else 'POOR'}
"""

ax6.text(0.05, 0.95, patterns_text, transform=ax6.transAxes, fontsize=9,
         verticalalignment='top', fontfamily='monospace',
         bbox=dict(boxstyle='round', facecolor=AZ_COLORS['gold'], alpha=0.2))

plt.tight_layout()
plt.savefig('dashboard/10_sustainability_analysis.png', dpi=300, bbox_inches='tight', facecolor='white')
plt.close()

print("   Saved: dashboard/10_sustainability_analysis.png")

# =============================================================================
# SAVE RESULTS
# =============================================================================
sustainability_metrics = pd.DataFrame({
    'Metric': ['Sustainability_Ratio', 'Operating_CF_Trend_M_per_week', 'Coefficient_of_Variation',
               'Positive_Weeks_Pct', 'Rolling_4W_Volatility_M', 'Forecast_Sustainability_Ratio'],
    'Value': [sustainability_ratio, slope_operating/1e6, cv, positive_ratio, 
              rolling_volatility/1e6, forecast_sustainability]
})
sustainability_metrics.to_csv('sustainability_metrics.csv', index=False)
print("   Saved: sustainability_metrics.csv")

patterns_df.to_csv('structural_patterns_monitoring.csv', index=False)
print("   Saved: structural_patterns_monitoring.csv")

# =============================================================================
# FINAL SUMMARY
# =============================================================================
print("\n" + "=" * 80)
print("SUSTAINABILITY ANALYSIS COMPLETE")
print("=" * 80)

print(f"""
SUSTAINABILITY SUMMARY
======================

CASH FLOW SUSTAINABILITY:
   Operating CF Ratio:    {sustainability_ratio:.2f}
   Operating CF Trend:    ${slope_operating/1e6:.2f}M/week ({'Increasing' if slope_operating > 0 else 'Decreasing'})
   Forecast Sustainability: {forecast_sustainability:.2f}

VOLATILITY:
   Coefficient of Variation: {cv:.2f}x ({'High' if cv > 1.0 else 'Moderate' if cv > 0.5 else 'Low'})
   Positive Weeks:        {positive_ratio:.1f}% ({positive_weeks}/{len(df)})

ACTIVITY BREAKDOWN:
   Operating Activities:  ${operating_total/1e6:.2f}M
   Financing Activities:  ${financing_total/1e6:.2f}M
   Investing Activities:  ${investing_total/1e6:.2f}M

STRUCTURAL PATTERNS IDENTIFIED: {len(monitoring_patterns)}
   1. Monthly Seasonality
   2. AR Collection Cycle
   3. Netting Operations Pattern
   4. Entity Concentration
   5. Volatility Trend

INTERPRETATION:
   Cash flow is {'SUSTAINABLE' if sustainability_ratio > 0.8 and slope_operating > 0 else 'MODERATELY SUSTAINABLE' if sustainability_ratio > 0.5 else 'NEEDS ATTENTION'}
   Operating activities drive {sustainability_ratio*100:.0f}% of cash flow.
   {'Positive' if slope_operating > 0 else 'Negative'} trend indicates {'improving' if slope_operating > 0 else 'declining'} sustainability.

FILES CREATED:
   - dashboard/10_sustainability_analysis.png
   - sustainability_metrics.csv
   - structural_patterns_monitoring.csv

==> Long-term sustainability analysis complete!
""")