"""
Phase 2: Data Cleaning & Exploratory Data Analysis
Clean data, handle missing values, calculate MTTR metrics, create EDA visualizations
"""
import pandas as pd
import numpy as np
import plotly.express as px
import plotly.graph_objects as go
from pathlib import Path
from datetime import timedelta
import re
import warnings
warnings.filterwarnings('ignore')

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

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

# SLA targets (hours)
SLA_TARGETS = {
    '1 - Critical': 4,
    '2 - High': 12,
    '3 - Moderate': 24,
    '4 - Low': 36
}

print("=" * 80)
print("PHASE 2: DATA CLEANING & EDA")
print("=" * 80)

# Load data
df = pd.read_excel(DATA_PATH / 'SAP - Non AERO 2025 Incident Dump_OTC.xlsx', sheet_name='OTC')
print(f"\nLoaded {len(df):,} records")

# =============================================================================
# DATA CLEANING
# =============================================================================
print("\n--- DATA CLEANING ---")

# Convert datetime columns
df['Opened'] = pd.to_datetime(df['Opened'], errors='coerce')
df['Resolved'] = pd.to_datetime(df['Resolved'], errors='coerce')
df['Service Restored / Workaround'] = pd.to_datetime(df['Service Restored / Workaround'], errors='coerce')

# Standardize column names (replace spaces with underscores for easier handling)
df.columns = df.columns.str.strip().str.replace(' ', '_')

# Filter for 2025 data only (primary analysis period)
df['Year'] = df['Opened'].dt.year
df_filtered = df[df['Year'] == 2025].copy()
print(f"Filtered to 2025 data: {len(df_filtered):,} records")

# Calculate MTTR in hours
df_filtered['MTTR_hours'] = (df_filtered['Resolved'] - df_filtered['Opened']).dt.total_seconds() / 3600

# Create SLA breach flag based on user-defined targets
def check_sla(row):
    priority = row['Priority']
    mttr = row['MTTR_hours']
    if pd.isna(priority) or pd.isna(mttr):
        return np.nan
    target_hours = SLA_TARGETS.get(priority, 36)  # Default to 36h for unknown
    return 'Breach' if mttr > target_hours else 'Met'

df_filtered['SLA_Status'] = df_filtered.apply(check_sla, axis=1)

# Create resolution notes completeness score
df_filtered['Resolution_Notes_Complete'] = df_filtered['Resolution_notes'].notna().astype(int)
df_filtered['Resolution_Notes_Length'] = df_filtered['Resolution_notes'].fillna('').str.len()

# Extract Priority Number for sorting
df_filtered['Priority_Num'] = df_filtered['Priority'].str.extract(r'(\d)').astype(float)

# =============================================================================
# SAVE CLEANED DATA
# =============================================================================
print("\n--- SAVING CLEANED DATA ---")
df_filtered.to_csv(OUTPUT_PATH / 'cleaned_incidents_2025.csv', index=False)
print(f"Saved cleaned dataset to {OUTPUT_PATH / 'cleaned_incidents_2025.csv'}")

# =============================================================================
# EXPLORATORY DATA ANALYSIS - KEY METRICS
# =============================================================================
print("\n--- EDA: KEY METRICS ---")

# Overall metrics
total_incidents = len(df_filtered)
resolved_incidents = len(df_filtered[df_filtered['State'].isin(['Closed', 'Resolved'])])
avg_mttr = df_filtered['MTTR_hours'].mean()
median_mttr = df_filtered['MTTR_hours'].median()
sla_compliance_rate = (df_filtered['SLA_Status'] == 'Met').sum() / df_filtered['SLA_Status'].notna().sum() * 100

print(f"Total Incidents (2025): {total_incidents:,}")
print(f"Resolved: {resolved_incidents:,} ({resolved_incidents/total_incidents*100:.1f}%)")
print(f"Average MTTR: {avg_mttr:.2f} hours")
print(f"Median MTTR: {median_mttr:.2f} hours")
print(f"SLA Compliance Rate: {sla_compliance_rate:.1f}%")

# By Priority
print("\n--- MTTR by Priority ---")
priority_mttr = df_filtered.groupby('Priority').agg({
    'MTTR_hours': ['count', 'mean', 'median'],
    'SLA_Status': lambda x: (x == 'Met').mean() * 100
}).round(2)
priority_mttr.columns = ['Count', 'Avg_MTTR', 'Median_MTTR', 'SLA_Compliance_%']
print(priority_mttr.to_string())

# By Region
print("\n--- MTTR by Region ---")
region_mttr = df_filtered.groupby('Region').agg({
    'MTTR_hours': ['count', 'mean', 'median'],
    'SLA_Status': lambda x: (x == 'Met').mean() * 100
}).round(2)
region_mttr.columns = ['Count', 'Avg_MTTR', 'Median_MTTR', 'SLA_Compliance_%']
print(region_mttr.to_string())

# By Assignment Group
print("\n--- MTTR by Assignment Group ---")
group_mttr = df_filtered.groupby('Assignment_group').agg({
    'MTTR_hours': ['count', 'mean', 'median'],
    'SLA_Status': lambda x: (x == 'Met').mean() * 100
}).round(2)
group_mttr.columns = ['Count', 'Avg_MTTR', 'Median_MTTR', 'SLA_Compliance_%']
print(group_mttr.to_string())

# By Configuration Item
print("\n--- MTTR by Configuration Item (Top 5) ---")
config_mttr = df_filtered.groupby('Configuration_item').agg({
    'MTTR_hours': ['count', 'mean', 'median'],
    'SLA_Status': lambda x: (x == 'Met').mean() * 100
}).round(2)
config_mttr.columns = ['Count', 'Avg_MTTR', 'Median_MTTR', 'SLA_Compliance_%']
config_mttr = config_mttr.sort_values('Count', ascending=False).head(5)
print(config_mttr.to_string())

# =============================================================================
# CREATE EDA VISUALIZATIONS
# =============================================================================
print("\n--- GENERATING VISUALIZATIONS ---")

# 1. Priority Distribution
fig1 = px.histogram(df_filtered, x='Priority', color='Priority',
                    title='Incident Distribution by Priority Level',
                    labels={'Priority': 'Priority Level', 'count': 'Number of Incidents'},
                    color_discrete_sequence=px.colors.sequential.RdBu)
fig1.update_layout(showlegend=False)
fig1.write_html(IMAGES_PATH / 'html/01_priority_distribution.html')
fig1.write_image(IMAGES_PATH / 'png/01_priority_distribution.png', scale=2)
print("Saved: 01_priority_distribution")

# 2. Regional Distribution
fig2 = px.histogram(df_filtered, x='Region', color='Region',
                    title='Incident Distribution by Region',
                    labels={'Region': 'Region', 'count': 'Number of Incidents'},
                    color_discrete_sequence=px.colors.qualitative.Set2)
fig2.update_layout(showlegend=False)
fig2.write_html(IMAGES_PATH / 'html/02_region_distribution.html')
fig2.write_image(IMAGES_PATH / 'png/02_region_distribution.png', scale=2)
print("Saved: 02_region_distribution")

# 3. Monthly Trend
df_filtered['YearMonth'] = df_filtered['Opened'].dt.to_period('M').astype(str)
monthly_trend = df_filtered.groupby('YearMonth').size().reset_index(name='Incidents')
fig3 = px.line(monthly_trend, x='YearMonth', y='Incidents', markers=True,
               title='Monthly Incident Volume Trend (2025)',
               labels={'YearMonth': 'Month', 'Incidents': 'Number of Incidents'})
fig3.update_traces(line_color='#2E86AB', line_width=3)
fig3.write_html(IMAGES_PATH / 'html/03_monthly_trend.html')
fig3.write_image(IMAGES_PATH / 'png/03_monthly_trend.png', scale=2)
print("Saved: 03_monthly_trend")

# 4. Closure Code Pareto
closure_counts = df_filtered['Closure_Code'].value_counts().head(10)
fig4 = px.bar(x=closure_counts.values, y=closure_counts.index, orientation='h',
              title='Top 10 Closure Codes (Pareto View)',
              labels={'x': 'Count', 'y': 'Closure Code'},
              color=closure_counts.values,
              color_continuous_scale='Viridis')
fig4.update_layout(yaxis={'categoryorder': 'total ascending'})
fig4.write_html(IMAGES_PATH / 'html/04_closure_code_pareto.html')
fig4.write_image(IMAGES_PATH / 'png/04_closure_code_pareto.png', scale=2)
print("Saved: 04_closure_code_pareto")

# 5. MTTR by Priority
fig5 = px.box(df_filtered, x='Priority', y='MTTR_hours', color='Priority',
              title='MTTR Distribution by Priority Level',
              labels={'Priority': 'Priority Level', 'MTTR_hours': 'MTTR (Hours)'})
fig5.update_layout(showlegend=False)
fig5.add_hline(y=24, line_dash="dash", line_color="red", annotation_text="24h Target (P3)")
fig5.add_hline(y=12, line_dash="dash", line_color="orange", annotation_text="12h Target (P2)")
fig5.write_html(IMAGES_PATH / 'html/05_mttr_by_priority.html')
fig5.write_image(IMAGES_PATH / 'png/05_mttr_by_priority.png', scale=2)
print("Saved: 05_mttr_by_priority")

# 6. Assignment Group Workload
fig6 = px.bar(x=group_mttr.index, y=group_mttr['Count'], color=group_mttr.index,
              title='Workload Distribution by Assignment Group',
              labels={'index': 'Assignment Group', 'y': 'Number of Incidents'})
fig6.update_layout(showlegend=False)
fig6.write_html(IMAGES_PATH / 'html/06_assignment_group_workload.html')
fig6.write_image(IMAGES_PATH / 'png/06_assignment_group_workload.png', scale=2)
print("Saved: 06_assignment_group_workload")

# 7. Configuration Item Distribution
fig7 = px.bar(x=config_mttr.index[:5], y=config_mttr['Count'][:5], color=config_mttr.index[:5],
              title='Top 5 Configuration Items (SAP Systems)',
              labels={'x': 'Configuration Item', 'y': 'Number of Incidents'})
fig7.update_layout(showlegend=False, xaxis_title='SAP System')
fig7.write_html(IMAGES_PATH / 'html/07_config_items.html')
fig7.write_image(IMAGES_PATH / 'png/07_config_items.png', scale=2)
print("Saved: 07_config_items")

# 8. Channel Effectiveness
channel_metrics = df_filtered.groupby('Channel').agg({
    'MTTR_hours': 'mean',
    'SLA_Status': lambda x: (x == 'Met').mean() * 100,
    'Resolution_Notes_Complete': 'mean'
}).round(2)
channel_metrics.columns = ['Avg_MTTR', 'SLA_Compliance_%', 'Resolution_Doc_%']
fig8 = px.scatter(channel_metrics, x='Avg_MTTR', y='SLA_Compliance_%',
                  size='Resolution_Doc_%', color=channel_metrics.index,
                  title='Channel Effectiveness Analysis',
                  labels={'index': 'Channel', 'Avg_MTTR': 'Avg MTTR (Hours)',
                          'SLA_Compliance_%': 'SLA Compliance %', 'Resolution_Doc_%': 'Documentation %'})
fig8.write_html(IMAGES_PATH / 'html/08_channel_effectiveness.html')
fig8.write_image(IMAGES_PATH / 'png/08_channel_effectiveness.png', scale=2)
print("Saved: 08_channel_effectiveness")

# 9. State Distribution
state_counts = df_filtered['State'].value_counts()
fig9 = px.pie(values=state_counts.values, names=state_counts.index,
              title='Incident State Distribution',
              color_discrete_sequence=px.colors.qualitative.Pastel)
fig9.write_html(IMAGES_PATH / 'html/09_state_distribution.html')
fig9.write_image(IMAGES_PATH / 'png/09_state_distribution.png', scale=2)
print("Saved: 09_state_distribution")

# 10. Escalation Analysis
escalation_metrics = df_filtered.groupby('Escalation').agg({
    'MTTR_hours': 'mean',
    'Reopen_count': 'mean',
    'SLA_Status': lambda x: (x == 'Met').mean() * 100
}).round(2)
escalation_metrics.columns = ['Avg_MTTR', 'Avg_Reopens', 'SLA_Compliance_%']
fig10 = px.bar(escalation_metrics, x=escalation_metrics.index, y='Avg_MTTR',
               color=escalation_metrics.index,
               title='Escalation Impact on MTTR',
               labels={'index': 'Escalation Level', 'Avg_MTTR': 'Average MTTR (Hours)'})
fig10.update_layout(showlegend=False)
fig10.write_html(IMAGES_PATH / 'html/10_escalation_analysis.html')
fig10.write_image(IMAGES_PATH / 'png/10_escalation_analysis.png', scale=2)
print("Saved: 10_escalation_analysis")

print("\n" + "=" * 80)
print("PHASE 2 COMPLETE: Cleaning & EDA visualizations saved")
print("=" * 80)
print(f"\nKey Findings:")
print(f"  - Total 2025 incidents: {total_incidents:,}")
print(f"  - SLA compliance rate: {sla_compliance_rate:.1f}%")
print(f"  - Average MTTR: {avg_mttr:.2f} hours")
print(f"  - Data Issue closure rate: {(df_filtered['Closure_Code'] == 'Data Issue').mean()*100:.1f}%")