"""
Phase 5: Team & Vendor Performance Benchmarking
Assignment group metrics, individual analyst clustering, vendor tracking, escalation analysis
"""

import pandas as pd
import numpy as np
import plotly.express as px
import plotly.graph_objects as go
from plotly.subplots import make_subplots
from pathlib import Path
from sklearn.preprocessing import StandardScaler
from sklearn.cluster import KMeans
import warnings
warnings.filterwarnings('ignore')

# Paths
OUTPUT_DIR = Path("/app/workspace/temp_files")
IMG_DIR = Path("/app/workspace/assets/images")
MODEL_DIR = Path("/app/workspace/models")

print("=" * 80)
print("PHASE 5: TEAM & VENDOR PERFORMANCE BENCHMARKING")
print("=" * 80)

# Load cleaned data
incidents_df = pd.read_pickle(OUTPUT_DIR / "incidents_cleaned.pkl")
tasks_df = pd.read_pickle(OUTPUT_DIR / "tasks_cleaned.pkl")

# Calculate Duration_Ratio if not present
if 'Duration_Ratio' not in tasks_df.columns:
    tasks_df['Duration_Ratio'] = np.where(
        tasks_df['Total_Elapsed_Hours'] > 0,
        tasks_df['Business_Duration_Hours'] / tasks_df['Total_Elapsed_Hours'],
        np.nan
    )

# ============================================================================
# 1. ASSIGNMENT GROUP RESOLUTION EFFICIENCY COMPARISON
# ============================================================================
print("\n📊 ASSIGNMENT GROUP PERFORMANCE COMPARISON")
print("-" * 40)

# Calculate metrics per assignment group (Tasks)
group_metrics = tasks_df.groupby('Assignment group').agg({
    'Number': 'count',
    'Business_Duration_Hours': ['mean', 'median', 'std'],
    'Resolution_Outcome': lambda x: (x == 'Success').mean() * 100,
    'Total_Elapsed_Hours': 'median',
    'Duration_Ratio': 'median'
}).round(2)

# Flatten column names
group_metrics.columns = ['Total_Tasks', 'Mean_Duration', 'Median_Duration', 
                         'Std_Duration', 'Success_Rate', 'Median_Elapsed', 'Efficiency_Ratio']
group_metrics = group_metrics.reset_index()

print("\n📋 TASK ASSIGNMENT GROUP METRICS:")
print(group_metrics.to_string())

# Visual comparison
fig_group_metrics = make_subplots(
    rows=2, cols=2,
    subplot_titles=['Total Task Volume by Group', 'Mean Resolution Time by Group',
                    'Success Rate by Group', 'Efficiency Ratio by Group'],
    specs=[[{'type':'bar'}, {'type':'bar'}],
           [{'type':'bar'}, {'type':'bar'}]]
)

# Volume
fig_group_metrics.add_trace(
    go.Bar(x=group_metrics['Assignment group'], y=group_metrics['Total_Tasks'],
           marker_color='#457B9D', name='Volume'),
    row=1, col=1
)

# Mean Duration
fig_group_metrics.add_trace(
    go.Bar(x=group_metrics['Assignment group'], y=group_metrics['Mean_Duration'],
           marker_color='#E63946', name='Mean Hours'),
    row=1, col=2
)

# Success Rate
fig_group_metrics.add_trace(
    go.Bar(x=group_metrics['Assignment group'], y=group_metrics['Success_Rate'],
           marker_color='#2A9D8F', name='Success %'),
    row=2, col=1
)

# Efficiency
fig_group_metrics.add_trace(
    go.Bar(x=group_metrics['Assignment group'], y=group_metrics['Efficiency_Ratio'] * 100,
           marker_color='#F4A261', name='Efficiency %'),
    row=2, col=2
)

fig_group_metrics.update_layout(
    template='plotly_white', height=700, showlegend=False,
    title_text='Assignment Group Performance Dashboard'
)
fig_group_metrics.write_html(IMG_DIR / "assignment_group_dashboard.html")
fig_group_metrics.write_image(IMG_DIR / "assignment_group_dashboard.png")
print("✓ Saved: assignment_group_dashboard")

# Incidents by Assignment Group
inc_group_metrics = incidents_df.groupby('Assignment group').agg({
    'Number': 'count',
    'Resolution_Hours': ['mean', 'median'],
    'Caller': 'nunique'
}).round(2)
inc_group_metrics.columns = ['Total_Incidents', 'Mean_Resolution_Hours', 
                              'Median_Resolution_Hours', 'Unique_Callers']
inc_group_metrics = inc_group_metrics.reset_index()

print("\n📋 INCIDENT ASSIGNMENT GROUP METRICS:")
print(inc_group_metrics.to_string())

fig_inc_group = px.bar(
    inc_group_metrics, x='Assignment group', y='Total_Incidents',
    color='Mean_Resolution_Hours',
    title='Incident Volume and Resolution Time by Assignment Group',
    labels={'Assignment group': 'Team', 'Total_Incidents': 'Incidents'},
    color_continuous_scale='RdYlGn_r'
)
fig_inc_group.update_layout(template='plotly_white', height=400)
fig_inc_group.write_html(IMG_DIR / "incident_group_performance.html")
fig_inc_group.write_image(IMG_DIR / "incident_group_performance.png")
print("✓ Saved: incident_group_performance")

# ============================================================================
# 2. INDIVIDUAL ANALYST PERFORMANCE CLUSTERING
# ============================================================================
print("\n👤 INDIVIDUAL ANALYST PERFORMANCE ANALYSIS")
print("-" * 40)

# Filter for analysts with sufficient volume
min_tasks = 50  # Minimum tasks for meaningful analysis
analyst_metrics = tasks_df[tasks_df['Assigned to'] != 'Unassigned'].groupby('Assigned to').agg({
    'Number': 'count',
    'Business_Duration_Hours': ['mean', 'median'],
    'Resolution_Outcome': lambda x: (x == 'Success').mean() * 100,
    'Total_Elapsed_Hours': 'median'
}).round(2)

analyst_metrics.columns = ['Total_Tasks', 'Mean_Duration', 'Median_Duration', 
                           'Success_Rate', 'Median_Elapsed']
analyst_metrics = analyst_metrics.reset_index()
analyst_metrics = analyst_metrics[analyst_metrics['Total_Tasks'] >= min_tasks]

print(f"\n📋 Analysts with >= {min_tasks} tasks: {len(analyst_metrics)}")
print(f"  Total tasks covered: {analyst_metrics['Total_Tasks'].sum():,}")

# Clustering analysts based on performance
features = ['Total_Tasks', 'Mean_Duration', 'Success_Rate']
X = analyst_metrics[features].values

# Standardize for clustering
scaler = StandardScaler()
X_scaled = scaler.fit_transform(X)

# K-Means clustering (3 clusters: High Performer, Average, Needs Improvement)
kmeans = KMeans(n_clusters=3, random_state=42, n_init=10)
analyst_metrics['Performance_Cluster'] = kmeans.fit_predict(X_scaled)

# Map clusters to performance levels
cluster_means = analyst_metrics.groupby('Performance_Cluster')['Success_Rate'].mean().sort_values(ascending=False)
cluster_mapping = {cluster_means.index[0]: 'High Performer', 
                   cluster_means.index[1]: 'Average', 
                   cluster_means.index[2]: 'Needs Improvement'}
analyst_metrics['Performance_Level'] = analyst_metrics['Performance_Cluster'].map(cluster_mapping)

print("\n📋 PERFORMANCE CLUSTER SUMMARY:")
cluster_summary = analyst_metrics.groupby('Performance_Level').agg({
    'Total_Tasks': ['count', 'sum'],
    'Mean_Duration': 'mean',
    'Success_Rate': 'mean'
}).round(2)
print(cluster_summary.to_string())

# Visualize analyst clusters
fig_cluster = px.scatter(
    analyst_metrics, 
    x='Mean_Duration', 
    y='Success_Rate',
    size='Total_Tasks',
    color='Performance_Level',
    hover_data=['Assigned to'],
    title='Analyst Performance Clustering',
    labels={'Mean_Duration': 'Mean Resolution Hours', 'Success_Rate': 'Success Rate (%)'},
    color_discrete_map={
        'High Performer': '#28A745',
        'Average': '#FFC107',
        'Needs Improvement': '#DC3545'
    }
)
fig_cluster.update_layout(template='plotly_white', height=500)
fig_cluster.write_html(IMG_DIR / "analyst_performance_clusters.html")
fig_cluster.write_image(IMG_DIR / "analyst_performance_clusters.png")
print("✓ Saved: analyst_performance_clusters")

# Top performers
top_performers = analyst_metrics[analyst_metrics['Performance_Level'] == 'High Performer'].sort_values(
    'Success_Rate', ascending=False
).head(10)

fig_top = px.bar(
    top_performers, 
    x='Assigned to', 
    y='Success_Rate',
    title='Top 10 High-Performing Analysts',
    labels={'Assigned to': 'Analyst', 'Success_Rate': 'Success Rate (%)'},
    color='Success_Rate',
    color_continuous_scale='Greens'
)
fig_top.update_layout(template='plotly_white', height=400)
fig_top.write_html(IMG_DIR / "top_performing_analysts.html")
fig_top.write_image(IMG_DIR / "top_performing_analysts.png")
print("✓ Saved: top_performing_analysts")

# Save analyst metrics for later use
analyst_metrics.to_pickle(OUTPUT_DIR / "analyst_metrics.pkl")
analyst_metrics.to_csv(OUTPUT_DIR / "analyst_metrics.csv", index=False)

# ============================================================================
# 3. VENDOR TICKET LINKAGE & EXTERNAL PARTNER TRACKING
# ============================================================================
print("\n🏢 VENDOR & EXTERNAL PARTNER ANALYSIS")
print("-" * 40)

# Vendor ticket analysis
tasks_df['Has_Vendor_Ticket'] = tasks_df['Vendor Task Number/Project Code'].notna()
vendor_metrics = tasks_df.groupby('Has_Vendor_Ticket').agg({
    'Number': 'count',
    'Business_Duration_Hours': ['mean', 'median'],
    'Resolution_Outcome': lambda x: (x == 'Success').mean() * 100
}).round(2)
vendor_metrics.columns = ['Total_Tasks', 'Mean_Duration', 'Median_Duration', 'Success_Rate']

print("\n📋 VENDOR TICKET ANALYSIS:")
print(f"  Tasks WITH Vendor Ticket: {vendor_metrics.loc[True, 'Total_Tasks']:,}")
print(f"    - Success Rate: {vendor_metrics.loc[True, 'Success_Rate']:.1f}%")
print(f"    - Mean Duration: {vendor_metrics.loc[True, 'Mean_Duration']:.1f} hours")
print(f"  Tasks WITHOUT Vendor Ticket: {vendor_metrics.loc[False, 'Total_Tasks']:,}")
print(f"    - Success Rate: {vendor_metrics.loc[False, 'Success_Rate']:.1f}%")
print(f"    - Mean Duration: {vendor_metrics.loc[False, 'Mean_Duration']:.1f} hours")

# Vendor code patterns
vendor_codes = tasks_df[tasks_df['Vendor Task Number/Project Code'].notna()]['Vendor Task Number/Project Code']
vendor_codes = vendor_codes[vendor_codes != 'NaN']

# Extract vendor prefixes
vendor_codes_df = pd.DataFrame({'Vendor_Code': vendor_codes})
vendor_codes_df['Vendor_Prefix'] = vendor_codes_df['Vendor_Code'].astype(str).str[:3]
vendor_prefix_counts = vendor_codes_df['Vendor_Prefix'].value_counts().head(10)

fig_vendor_codes = px.bar(
    vendor_prefix_counts, x=vendor_prefix_counts.index, y=vendor_prefix_counts.values,
    title='Top Vendor Code Prefixes',
    labels={'index': 'Vendor Prefix', 'y': 'Count'},
    color_discrete_sequence=['#6C5CE7']
)
fig_vendor_codes.update_layout(template='plotly_white', height=400)
fig_vendor_codes.write_html(IMG_DIR / "vendor_code_analysis.html")
fig_vendor_codes.write_image(IMG_DIR / "vendor_code_analysis.png")
print("✓ Saved: vendor_code_analysis")

# Vendor ticket impact on outcomes
vendor_by_group = tasks_df.groupby(['Assignment group', 'Has_Vendor_Ticket']).agg({
    'Resolution_Outcome': lambda x: (x == 'Success').mean() * 100,
    'Business_Duration_Hours': 'mean'
}).round(2).reset_index()

fig_vendor_group = px.bar(
    vendor_by_group, 
    x='Assignment group', 
    y='Resolution_Outcome',
    color='Has_Vendor_Ticket',
    barmode='group',
    title='Vendor Ticket Impact on Resolution Success by Team',
    labels={'Assignment group': 'Team', 'Resolution_Outcome': 'Success Rate (%)', 'Has_Vendor_Ticket': 'Has Vendor Ticket'},
    color_discrete_map={True: '#28A745', False: '#DC3545'}
)
fig_vendor_group.update_layout(template='plotly_white', height=400)
fig_vendor_group.write_html(IMG_DIR / "vendor_impact_by_team.html")
fig_vendor_group.write_image(IMG_DIR / "vendor_impact_by_team.png")
print("✓ Saved: vendor_impact_by_team")

# ============================================================================
# 4. ESCALATION PATTERN ANALYSIS BY TEAM
# ============================================================================
print("\n⬆️ ESCALATION PATTERN ANALYSIS")
print("-" * 40)

# Escalation field analysis
tasks_df['Is_Escalated'] = tasks_df['Escalation'] == 'Escalated'
incidents_df['Is_Escalated'] = incidents_df['Last Escalation'].notna()

# Escalation by assignment group
esc_group_task = tasks_df.groupby(['Assignment group', 'Is_Escalated']).size().unstack(fill_value=0)
esc_cols = esc_group_task.columns.tolist()
if len(esc_cols) >= 2:
    esc_group_task['Escalation_Rate'] = esc_group_task[esc_cols[1]] / (esc_group_task[esc_cols[0]] + esc_group_task[esc_cols[1]]) * 100
elif len(esc_cols) == 1:
    esc_group_task['Escalation_Rate'] = 0.0
else:
    esc_group_task['Escalation_Rate'] = 0.0

print("\n📋 TASK ESCALATION RATE BY TEAM:")
for group in esc_group_task.index:
    rate = esc_group_task.loc[group, 'Escalation_Rate']
    print(f"  {group}: {rate:.1f}%")

fig_esc_group = px.bar(
    esc_group_task.reset_index(), 
    x='Assignment group', 
    y='Escalation_Rate',
    title='Escalation Rate by Assignment Group',
    labels={'Assignment group': 'Team', 'Escalation_Rate': 'Escalation Rate (%)'},
    color='Escalation_Rate',
    color_continuous_scale='Reds'
)
fig_esc_group.update_layout(template='plotly_white', height=400)
fig_esc_group.write_html(IMG_DIR / "escalation_rate_by_team.html")
fig_esc_group.write_image(IMG_DIR / "escalation_rate_by_team.png")
print("✓ Saved: escalation_rate_by_team")

# Escalation by category
esc_cat = tasks_df.groupby(['Subcategory', 'Is_Escalated']).size().unstack(fill_value=0)
esc_cat_cols = esc_cat.columns.tolist()
if len(esc_cat_cols) >= 2:
    esc_cat['Escalation_Rate'] = esc_cat[esc_cat_cols[1]] / (esc_cat[esc_cat_cols[0]] + esc_cat[esc_cat_cols[1]]) * 100
elif len(esc_cat_cols) == 1:
    esc_cat['Escalation_Rate'] = 0.0
else:
    esc_cat['Escalation_Rate'] = 0.0
esc_cat_sorted = esc_cat.sort_values('Escalation_Rate', ascending=False).head(10)

fig_esc_cat = px.bar(
    esc_cat_sorted.reset_index(), 
    x='Subcategory', 
    y='Escalation_Rate',
    title='Top 10 Subcategories by Escalation Rate',
    labels={'Subcategory': 'Subcategory', 'Escalation_Rate': 'Escalation Rate (%)'},
    color='Escalation_Rate',
    color_continuous_scale='Oranges'
)
fig_esc_cat.update_layout(template='plotly_white', height=400, xaxis={'tickangle': 45})
fig_esc_cat.write_html(IMG_DIR / "escalation_rate_by_subcategory.html")
fig_esc_cat.write_image(IMG_DIR / "escalation_rate_by_subcategory.png")
print("✓ Saved: escalation_rate_by_subcategory")

# Escalation impact on resolution time
esc_time = tasks_df.groupby('Is_Escalated').agg({
    'Business_Duration_Hours': 'mean',
    'Total_Elapsed_Hours': 'mean',
    'Resolution_Outcome': lambda x: (x == 'Success').mean() * 100
}).round(2)

print("\n📋 ESCALATION IMPACT ON OUTCOMES:")
# Handle case where only one category exists
esc_time_index = esc_time.index.tolist()
if len(esc_time_index) >= 2:
    esc_val = esc_time.loc[True, 'Resolution_Outcome'] if True in esc_time_index else 0
    non_esc_val = esc_time.loc[False, 'Resolution_Outcome'] if False in esc_time_index else 0
    esc_dur = esc_time.loc[True, 'Business_Duration_Hours'] if True in esc_time_index else 0
    non_esc_dur = esc_time.loc[False, 'Business_Duration_Hours'] if False in esc_time_index else 0
else:
    esc_val = esc_time.iloc[0]['Resolution_Outcome']
    non_esc_val = esc_time.iloc[0]['Resolution_Outcome']
    esc_dur = esc_time.iloc[0]['Business_Duration_Hours']
    non_esc_dur = esc_time.iloc[0]['Business_Duration_Hours']

print(f"  Escalated Tasks: {esc_val:.1f}% success rate")
print(f"  Non-Escalated Tasks: {non_esc_val:.1f}% success rate")
print(f"  Mean Duration (Escalated): {esc_dur:.1f} hours")
print(f"  Mean Duration (Non-Escalated): {non_esc_dur:.1f} hours")

fig_esc_impact = go.Figure()
fig_esc_impact.add_trace(go.Bar(
    x=['Escalated', 'Non-Escalated'],
    y=[esc_val, non_esc_val],
    name='Success Rate (%)',
    marker_color=['#DC3545', '#28A745']
))
fig_esc_impact.update_layout(
    title='Resolution Success Rate: Escalated vs Non-Escalated',
    template='plotly_white', height=400
)
fig_esc_impact.write_html(IMG_DIR / "escalation_impact_on_success.html")
fig_esc_impact.write_image(IMG_DIR / "escalation_impact_on_success.png")
print("✓ Saved: escalation_impact_on_success")

# ============================================================================
# BENCHMARK SUMMARY
# ============================================================================
print("\n📊 TEAM & VENDOR PERFORMANCE BENCHMARKS")
print("-" * 40)

print("\n🏆 TOP PERFORMING TEAMS (Tasks):")
for _, row in group_metrics.sort_values('Success_Rate', ascending=False).iterrows():
    print(f"  {row['Assignment group']}: {row['Success_Rate']:.1f}% success, {row['Mean_Duration']:.1f}h avg duration")

print("\n👥 ANALYST COVERAGE:")
print(f"  Total unique analysts: {tasks_df['Assigned to'].nunique() - 1:,}")
print(f"  Analysts in clustering: {len(analyst_metrics)}")
print(f"  High performers: {len(analyst_metrics[analyst_metrics['Performance_Level']=='High Performer'])}")
print(f"  Needs improvement: {len(analyst_metrics[analyst_metrics['Performance_Level']=='Needs Improvement'])}")

print("\n🏢 VENDOR IMPACT:")
print(f"  Tasks with vendor involvement: {tasks_df['Has_Vendor_Ticket'].sum():,} ({tasks_df['Has_Vendor_Ticket'].mean()*100:.1f}%)")
print(f"  Vendor tasks success rate: {vendor_metrics.loc[True, 'Success_Rate']:.1f}%")
print(f"  Non-vendor tasks success rate: {vendor_metrics.loc[False, 'Success_Rate']:.1f}%")

print("\n⬆️ ESCALATION METRICS:")
print(f"  Overall escalation rate: {tasks_df['Is_Escalated'].mean()*100:.2f}%")
# Use the calculated values from earlier
if 'esc_val' in locals() and 'non_esc_val' in locals():
    print(f"  Escalated task success rate: {esc_val:.1f}%")
    print(f"  Non-escalated success rate: {non_esc_val:.1f}%")
else:
    print(f"  Escalated task success rate: N/A (no escalated cases)")
    print(f"  Non-escalated success rate: N/A")

print("\n✅ TEAM & VENDOR PERFORMANCE ANALYSIS COMPLETE")