"""
Phase 5-6: Organizational Benchmarking & System Stability Analysis
Heatmaps, MTTR comparisons, Channel effectiveness, Configuration mapping, Change ID impact
"""
import pandas as pd
import numpy as np
import plotly.express as px
import plotly.graph_objects as go
from pathlib import Path
import warnings
warnings.filterwarnings('ignore')

# Set display options
pd.set_option('display.max_columns', 50)
pd.set_option('display.width', 200)

# Define paths
OUTPUT_PATH = Path('/app/workspace/temp_files')
IMAGES_PATH = Path('/app/workspace/assets/images')

print("=" * 80)
print("PHASE 5-6: ORGANIZATIONAL BENCHMARKING & SYSTEM STABILITY")
print("=" * 80)

# Load cleaned data
df = pd.read_csv(OUTPUT_PATH / 'cleaned_incidents_2025.csv')
print(f"\nLoaded {len(df):,} records from cleaned dataset")

# Load Sheet5 for configuration issue mapping
try:
    df_sheet5 = pd.read_excel(Path('/app/workspace/user_source_data') / 'SAP - Non AERO 2025 Incident Dump_OTC.xlsx', 
                              sheet_name='Sheet5')
    sheet5_closure_codes = set(df_sheet5['Closure Code'].unique())
    print(f"Sheet5 loaded: {len(sheet5_closure_codes)} closure codes with configuration flags")
except Exception as e:
    sheet5_closure_codes = set()
    print(f"Sheet5 not available: {e}")

# =============================================================================
# PHASE 5: ORGANIZATIONAL BENCHMARKING
# =============================================================================

# ============================================================================
# INCIDENT VOLUME HEATMAP BY REGION AND SBU
# ============================================================================
print("\n--- INCIDENT VOLUME HEATMAP: REGION × SBU ---")

# Filter for valid SBU values
df_valid = df[df['SBU'].notna() & df['Region'].notna()]

# Create pivot table for heatmap
region_sbu_heatmap = df_valid.pivot_table(
    values='Number',
    index='SBU',
    columns='Region',
    aggfunc='count',
    fill_value=0
)

fig1 = px.imshow(region_sbu_heatmap,
                 labels=dict(x="Region", y="SBU", color="Incident Count"),
                 title='Incident Density Heatmap: Region × Strategic Business Unit (SBU)',
                 color_continuous_scale='YlOrRd')
fig1.write_html(IMAGES_PATH / 'html/25_region_sbu_heatmap.html')
fig1.write_image(IMAGES_PATH / 'png/25_region_sbu_heatmap.png', scale=2)
print("Saved: 25_region_sbu_heatmap")

print("\nTop SBUs by Incident Volume:")
sbu_counts = df_valid['SBU'].value_counts().head(10)
print(sbu_counts.to_string())

# ============================================================================
# MTTR COMPARISON ACROSS SBUs
# ============================================================================
print("\n--- MTTR COMPARISON BY SBU ---")

sbu_mttr = df_valid.groupby('SBU').agg({
    'MTTR_hours': ['count', 'mean', 'median'],
    'SLA_Status': lambda x: (x == 'Met').mean() * 100
}).round(2)
sbu_mttr.columns = ['Incident_Count', 'Avg_MTTR', 'Median_MTTR', 'SLA_Compliance_%']
sbu_mttr = sbu_mttr[sbu_mttr['Incident_Count'] >= 100]  # Filter for statistical significance
sbu_mttr = sbu_mttr.sort_values('Avg_MTTR')

print("\nSBU MTTR Comparison (min 100 incidents):")
print(sbu_mttr.to_string())

# ============================================================================
# VISUALIZATION 2: SBU MTTR Comparison
# ============================================================================
fig2 = px.bar(sbu_mttr.reset_index(), 
              x='SBU', 
              y='Avg_MTTR',
              color='SLA_Compliance_%',
              title='Average MTTR by Strategic Business Unit (SBU)',
              labels={'SBU': 'SBU', 'Avg_MTTR': 'Average MTTR (Hours)', 'SLA_Compliance_%': 'SLA Compliance (%)'},
              color_continuous_scale='RdYlGn')
fig2.update_layout(xaxis_title='Strategic Business Unit',
                   yaxis_title='Average MTTR (Hours)')
fig2.write_html(IMAGES_PATH / 'html/26_sbu_mttr_comparison.html')
fig2.write_image(IMAGES_PATH / 'png/26_sbu_mttr_comparison.png', scale=2)
print("Saved: 26_sbu_mttr_comparison")

# ============================================================================
# CHANNEL EFFECTIVENESS ANALYSIS
# ============================================================================
print("\n--- CHANNEL EFFECTIVENESS ANALYSIS ---")

channel_metrics = df.groupby('Channel').agg({
    'MTTR_hours': ['count', 'mean', 'median'],
    'SLA_Status': lambda x: (x == 'Met').mean() * 100,
    'Reopen_count': lambda x: (x > 0).mean() * 100,
    'Resolution_Notes_Complete': 'mean'
}).round(2)
channel_metrics.columns = ['Count', 'Avg_MTTR', 'Median_MTTR', 'SLA_Compliance_%', 'Reopen_Rate_%', 'Resolution_Doc_%']
channel_metrics = channel_metrics.sort_values('Count', ascending=False)

print("\nChannel Performance Metrics:")
print(channel_metrics.to_string())

# ============================================================================
# VISUALIZATION 3: Channel Effectiveness Matrix
# ============================================================================
fig3 = px.scatter(channel_metrics.reset_index(), 
                  x='Avg_MTTR', 
                  y='SLA_Compliance_%',
                  size='Count',
                  color='Channel',
                  title='Channel Effectiveness: MTTR vs SLA Compliance',
                  labels={'Avg_MTTR': 'Average MTTR (Hours)', 
                          'SLA_Compliance_%': 'SLA Compliance (%)',
                          'Count': 'Incident Volume'},
                  color_discrete_sequence=px.colors.qualitative.Set2)
fig3.add_hline(y=90, line_dash="dash", line_color="green", annotation_text="90% SLA Target")
fig3.add_vline(x=50, line_dash="dash", line_color="orange", annotation_text="50h MTTR Target")
fig3.write_html(IMAGES_PATH / 'html/27_channel_effectiveness_matrix.html')
fig3.write_image(IMAGES_PATH / 'png/27_channel_effectiveness_matrix.png', scale=2)
print("Saved: 27_channel_effectiveness_matrix")

# ============================================================================
# VISUALIZATION 4: Channel Comparison Bar Chart
# ============================================================================
channel_melted = channel_metrics.reset_index().melt(
    id_vars=['Channel'], 
    value_vars=['Avg_MTTR', 'SLA_Compliance_%', 'Reopen_Rate_%'],
    var_name='Metric',
    value_name='Value'
)

fig4 = px.bar(channel_melted, 
              x='Channel', 
              y='Value',
              color='Metric',
              barmode='group',
              title='Channel Comparison: MTTR, SLA Compliance, and Reopen Rate',
              labels={'Channel': 'Incident Channel', 'Value': 'Value', 'Metric': 'Metric'},
              color_discrete_sequence=['#2E86AB', '#4ECDC4', '#FF6B6B'])
fig4.write_html(IMAGES_PATH / 'html/28_channel_comparison.html')
fig4.write_image(IMAGES_PATH / 'png/28_channel_comparison.png', scale=2)
print("Saved: 28_channel_comparison")

# ============================================================================
# CHANNEL → CLOSURE CODE FLOW ANALYSIS
# ============================================================================
print("\n--- CHANNEL TO CLOSURE CODE FLOW ---")

channel_closure = pd.crosstab(df['Channel'], df['Closure_Code'], normalize='index') * 100
print("\nTop Closure Codes by Channel:")
for channel in df['Channel'].unique()[:4]:
    if channel in channel_closure.index:
        top_codes = channel_closure.loc[channel].nlargest(3)
        print(f"  {channel}: {dict(top_codes.round(1))}")

# =============================================================================
# PHASE 6: SYSTEM STABILITY ANALYSIS
# =============================================================================

# ============================================================================
# INCIDENT CONCENTRATION BY CONFIGURATION ITEM
# ============================================================================
print("\n--- INCIDENT CONCENTRATION BY CONFIGURATION ITEM (SAP SYSTEMS) ---")

config_counts = df['Configuration_item'].value_counts()
config_counts_top = config_counts.head(15)

print("\nTop 15 SAP Systems by Incident Count:")
print(config_counts_top.to_string())

# ============================================================================
# VISUALIZATION 5: Configuration Item Incident Distribution
# ============================================================================
fig5 = px.bar(x=config_counts_top.values, 
              y=config_counts_top.index, 
              orientation='h',
              color=config_counts_top.values,
              color_continuous_scale='Viridis',
              title='Top 15 SAP Systems by Incident Volume',
              labels={'x': 'Number of Incidents', 'y': 'SAP System'},
              log_x=True)
fig5.update_layout(yaxis={'categoryorder': 'total ascending'})
fig5.write_html(IMAGES_PATH / 'html/29_config_item_distribution.html')
fig5.write_image(IMAGES_PATH / 'png/29_config_item_distribution.png', scale=2)
print("Saved: 29_config_item_distribution")

# ============================================================================
# CONFIGURATION ITEM PERFORMANCE METRICS
# ============================================================================
print("\n--- CONFIGURATION ITEM PERFORMANCE ---")

config_metrics = df.groupby('Configuration_item').agg({
    'MTTR_hours': ['count', 'mean', 'median'],
    'SLA_Status': lambda x: (x == 'Met').mean() * 100,
    'Reopen_count': lambda x: (x > 0).mean() * 100
}).round(2)
config_metrics.columns = ['Incident_Count', 'Avg_MTTR', 'Median_MTTR', 'SLA_Compliance_%', 'Reopen_Rate_%']

# Filter for top 10 systems
config_metrics_top = config_metrics[config_metrics['Incident_Count'] >= 100].nlargest(10, 'Incident_Count')

print("\nTop 10 SAP Systems Performance Metrics:")
print(config_metrics_top.to_string())

# ============================================================================
# VISUALIZATION 6: Configuration Item Performance Comparison
# ============================================================================
fig6 = go.Figure()

x = config_metrics_top.index
fig6.add_trace(go.Bar(x=x, y=config_metrics_top['Avg_MTTR'], name='Avg MTTR (hrs)', 
                      marker_color='#2E86AB', yaxis='y1'))
fig6.add_trace(go.Scatter(x=x, y=config_metrics_top['SLA_Compliance_%'], name='SLA %', 
                          line=dict(color='#4ECDC4', width=3), yaxis='y2'))

fig6.update_layout(title='SAP System Performance: MTTR vs SLA Compliance',
                   xaxis_title='SAP System',
                   yaxis=dict(title='Avg MTTR (Hours)', side='left'),
                   yaxis2=dict(title='SLA Compliance (%)', side='right', overlaying='y', range=[0, 100]),
                   legend=dict(x=0.5, y=1.1, orientation='h', xanchor='center'))
fig6.write_html(IMAGES_PATH / 'html/30_config_performance.html')
fig6.write_image(IMAGES_PATH / 'png/30_config_performance.png', scale=2)
print("Saved: 30_config_performance")

# ============================================================================
# IMPACT ANALYSIS OF EXTERNAL CHANGE IDs
# ============================================================================
print("\n--- EXTERNAL CHANGE ID IMPACT ANALYSIS ---")

# Incidents linked to changes
df_with_changes = df[df['Caused_by_External_Change_ID'].notna()].copy()
print(f"\nIncidents linked to External Changes: {len(df_with_changes):,} ({len(df_with_changes)/len(df)*100:.1f}%)")

# Group incidents by Change ID
change_impact = df_with_changes.groupby('Caused_by_External_Change_ID').agg({
    'Number': 'count',
    'MTTR_hours': 'mean',
    'SLA_Status': lambda x: (x == 'Breach').mean() * 100,
    'Priority': lambda x: (x.str.contains('1|2', regex=True)).mean() * 100
}).round(2)
change_impact.columns = ['Incident_Count', 'Avg_MTTR', 'SLA_Breach_%', 'High_Priority_%']
change_impact = change_impact.sort_values('Incident_Count', ascending=False)

print("\nTop 10 Changes by Incident Count:")
print(change_impact.head(10).to_string())

# ============================================================================
# VISUALIZATION 7: Change Impact Distribution
# ============================================================================
fig7 = px.histogram(change_impact, x='Incident_Count', 
                    title='Distribution of Incidents per External Change ID',
                    labels={'Incident_Count': 'Incidents per Change', 'count': 'Number of Changes'},
                    color_discrete_sequence=['#FF6B6B'],
                    nbins=20)
fig7.update_layout(showlegend=False)
fig7.write_html(IMAGES_PATH / 'html/31_change_impact_distribution.html')
fig7.write_image(IMAGES_PATH / 'png/31_change_impact_distribution.png', scale=2)
print("Saved: 31_change_impact_distribution")

# ============================================================================
# CHANGES WITH HIGH INCIDENT VOLUME (Risk Assessment)
# ============================================================================
high_impact_changes = change_impact[change_impact['Incident_Count'] >= 5]
print(f"\nHigh-Impact Changes (≥5 incidents): {len(high_impact_changes)}")

if len(high_impact_changes) > 0:
    print("\nHigh-Impact Changes Details:")
    print(high_impact_changes.head(10).to_string())

# ============================================================================
# CONFIGURATION ISSUE FLAG CORRELATION (Sheet5)
# ============================================================================
print("\n--- CONFIGURATION ISSUE FLAG CORRELATION ---")

# Mark incidents with configuration issues from Sheet5
df['Is_Configuration_Issue'] = df['Closure_Code'].isin(sheet5_closure_codes) | \
                                (df['Closure_Code'].str.contains('Configuration', case=False, na=False))

config_issue_metrics = df.groupby('Is_Configuration_Issue').agg({
    'MTTR_hours': ['count', 'mean'],
    'SLA_Status': lambda x: (x == 'Met').mean() * 100,
    'Reopen_count': lambda x: (x > 0).mean() * 100
}).round(2)
config_issue_metrics.columns = ['Count', 'Avg_MTTR', 'SLA_Compliance_%', 'Reopen_Rate_%']

print("\nConfiguration Issue vs Non-Configuration Issue:")
print(config_issue_metrics.to_string())

# ============================================================================
# VISUALIZATION 8: Configuration Issue Impact
# ============================================================================
fig8 = go.Figure()

labels = ['Non-Configuration Issues', 'Configuration Issues']
fig8.add_trace(go.Bar(x=labels, y=config_issue_metrics['Avg_MTTR'], name='Avg MTTR',
                      marker_color=['#4ECDC4', '#FF6B6B']))
fig8.add_trace(go.Bar(x=labels, y=config_issue_metrics['SLA_Compliance_%'], name='SLA %',
                      marker_color=['#2E86AB', '#E8E8E8'], yaxis='y2'))

fig8.update_layout(title='Impact of Configuration Issues on Resolution',
                   xaxis_title='Issue Type',
                   yaxis=dict(title='Avg MTTR (Hours)'),
                   yaxis2=dict(title='SLA Compliance (%)', overlaying='y', side='right', range=[0, 100]),
                   legend=dict(x=0.5, y=1.1, orientation='h', xanchor='center'))
fig8.write_html(IMAGES_PATH / 'html/32_config_issue_impact.html')
fig8.write_image(IMAGES_PATH / 'png/32_config_issue_impact.png', scale=2)
print("Saved: 32_config_issue_impact")

# ============================================================================
# SYSTEM STABILITY SCORECARD
# ============================================================================
print("\n--- SYSTEM STABILITY SCORECARD ---")

stability_scorecard = pd.DataFrame({
    'SAP System': config_counts_top.index[:10],
    'Incident_Count': config_counts_top.values[:10],
    'Avg_MTTR': [config_metrics.loc[sys, 'Avg_MTTR'] if sys in config_metrics.index else np.nan 
                 for sys in config_counts_top.index[:10]],
    'SLA_Compliance': [config_metrics.loc[sys, 'SLA_Compliance_%'] if sys in config_metrics.index else np.nan 
                       for sys in config_counts_top.index[:10]]
}).round(1)

print("\nSystem Stability Scorecard (Top 10 SAP Systems):")
print(stability_scorecard.to_string(index=False))

# Save scorecard
stability_scorecard.to_csv(OUTPUT_PATH / 'system_stability_scorecard.csv', index=False)

# =============================================================================
# SUMMARY METRICS
# =============================================================================
print("\n" + "=" * 80)
print("PHASE 5-6 COMPLETE: Organizational Benchmarking & System Stability")
print("=" * 80)

print(f"\nKey Findings:")
print(f"  - Highest Incident Region: {df['Region'].value_counts().idxmax()} ({df['Region'].value_counts().max():,} incidents)")
print(f"  - Best Performing Channel: {channel_metrics['SLA_Compliance_%'].idxmax()} ({channel_metrics['SLA_Compliance_%'].max():.1f}% SLA)")
print(f"  - Worst Performing Channel: {channel_metrics['SLA_Compliance_%'].idxmin()} ({channel_metrics['SLA_Compliance_%'].min():.1f}% SLA)")
print(f"  - Most Incident-Prone System: {config_counts.idxmax()} ({config_counts.max():,} incidents)")
print(f"  - Changes with ≥5 incidents: {len(high_impact_changes)}")
print(f"  - Configuration Issues Avg MTTR: {config_issue_metrics.loc[True, 'Avg_MTTR']:.1f} hrs")
print(f"  - Non-Configuration Issues Avg MTTR: {config_issue_metrics.loc[False, 'Avg_MTTR']:.1f} hrs")

print(f"\nAll visualizations saved to {IMAGES_PATH / 'html'} and {IMAGES_PATH / 'png'}")