"""
Phase 3B: Change Risk & Schedule Adherence Analytics
Subtopics:
1. Change window overrun quantification by category
2. Post-change incident correlation via temporal joins (24hr window)
3. Change category specialization clustering
4. Change state transition bottleneck 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 sklearn.cluster import KMeans
from sklearn.preprocessing import StandardScaler, LabelEncoder
import warnings
warnings.filterwarnings('ignore')

# Load cleaned data
inc = pd.read_parquet("temp_files/incidents_cleaned.parquet")
cr = pd.read_parquet("temp_files/cr_cleaned.parquet")
sr = pd.read_parquet("temp_files/sr_cleaned.parquet")

print("=" * 80)
print("PHASE 3B: CHANGE RISK & SCHEDULE ADHERENCE ANALYTICS")
print("=" * 80)

# ============================================================
# 1. CHANGE WINDOW OVERRUN QUANTIFICATION BY CATEGORY
# ============================================================
print("\n--- 1. Change Window Overrun by Category ---")

# Filter valid durations
cr_valid = cr[(cr['planned_duration_hours'] > 0) & (cr['actual_duration_hours'] > 0)].copy()
cr_valid['overrun_pct'] = ((cr_valid['actual_duration_hours'] - cr_valid['planned_duration_hours']) / cr_valid['planned_duration_hours']) * 100
cr_valid['is_overrun'] = cr_valid['actual_duration_hours'] > cr_valid['planned_duration_hours']

# Summary by category
overrun_by_cat = cr_valid.groupby('Category').agg({
    'overrun_pct': 'mean',
    'is_overrun': 'sum',
    'number': 'count',
    'schedule_delta_hours': 'mean'
}).rename(columns={'number': 'total_changes', 'is_overrun': 'overrun_count'})
overrun_by_cat['overrun_rate'] = (overrun_by_cat['overrun_count'] / overrun_by_cat['total_changes']) * 100
overrun_by_cat = overrun_by_cat.sort_values('overrun_rate', ascending=False)

print(f"  Overall overrun rate: {cr_valid['is_overrun'].mean()*100:.1f}%")
print(f"  Categories with highest overrun rates:")
print(overrun_by_cat.head(10))

# Plot: Overrun rate by category
fig_or = px.bar(
    overrun_by_cat.head(15).reset_index(),
    x='Category', y='overrun_rate',
    title='Change Window Overrun Rate by Category (%)',
    labels={'overrun_rate': 'Overrun Rate (%)', 'Category': 'Change Category'},
    color='overrun_rate', color_continuous_scale='Reds'
)
fig_or.update_layout(template='plotly_white', xaxis_tickangle=45)
fig_or.write_html("assets/images/html/cr_overrun_rate_by_category.html")
fig_or.write_image("assets/images/png/cr_overrun_rate_by_category.png", width=1200, height=600, scale=2)

# Plot: Schedule delta distribution
fig_sd = px.box(
    cr_valid, x='Category', y='schedule_delta_hours',
    title='Schedule Delta (Actual - Planned Hours) by Category',
    labels={'schedule_delta_hours': 'Delta (Hours)', 'Category': 'Category'},
    color='Category'
)
fig_sd.update_layout(template='plotly_white', xaxis_tickangle=45, showlegend=False)
fig_sd.write_html("assets/images/html/cr_schedule_delta_boxplot.html")
fig_sd.write_image("assets/images/png/cr_schedule_delta_boxplot.png", width=1400, height=600, scale=2)

# Plot: Planned vs Actual duration scatter (top categories)
top_cr_cats = cr_valid['Category'].value_counts().head(5).index
cr_top = cr_valid[cr_valid['Category'].isin(top_cr_cats)]
fig_pv = px.scatter(
    cr_top, x='planned_duration_hours', y='actual_duration_hours',
    color='Category', opacity=0.6,
    title='Planned vs Actual Duration (Top 5 Categories)',
    labels={'planned_duration_hours': 'Planned (hours)', 'actual_duration_hours': 'Actual (hours)'}
)
# Add 45-degree line
max_val = max(cr_top['planned_duration_hours'].max(), cr_top['actual_duration_hours'].max())
fig_pv.add_trace(go.Scatter(x=[0, max_val], y=[0, max_val], mode='lines', name='Perfect Adherence', line=dict(color='red', dash='dash')))
fig_pv.update_layout(template='plotly_white')
fig_pv.write_html("assets/images/html/cr_planned_vs_actual.html")
fig_pv.write_image("assets/images/png/cr_planned_vs_actual.png", width=1000, height=700, scale=2)

# ============================================================
# 2. POST-CHANGE INCIDENT CORRELATION (24-HOUR WINDOW)
# ============================================================
print("\n--- 2. Post-Change Incident Correlation (24hr Window) ---")

# Merge CR work_end with INC opened_at
cr['work_end'] = pd.to_datetime(cr['work_end'], errors='coerce')
inc['opened_at'] = pd.to_datetime(inc['opened_at'], errors='coerce')

# For each CR, find incidents on same CI within 24 hours after work_end
post_change_incidents = []
for idx, row in cr.iterrows():
    if pd.isna(row['work_end']) or pd.isna(row['cmdb_ci']):
        continue
    window_start = row['work_end']
    window_end = row['work_end'] + pd.Timedelta(hours=24)
    
    mask = (
        (inc['cmdb_ci'] == row['cmdb_ci']) &
        (inc['opened_at'] >= window_start) &
        (inc['opened_at'] <= window_end)
    )
    matched = inc[mask]
    if len(matched) > 0:
        post_change_incidents.append({
            'cr_number': row['number'],
            'cr_category': row['Category'],
            'ci': row['cmdb_ci'],
            'work_end': row['work_end'],
            'inc_count': len(matched),
            'inc_numbers': ';'.join(matched['number'].tolist()),
            'inc_categories': ';'.join(matched['Category'].astype(str).unique())
        })

post_change_df = pd.DataFrame(post_change_incidents)
if len(post_change_df) > 0:
    # Change failure rate by category
    cr_with_incidents = post_change_df.groupby('cr_category').agg({
        'cr_number': 'nunique',
        'inc_count': 'sum'
    }).reset_index()
    cr_total = cr.groupby('Category').size().reset_index(name='total_changes')
    failure_analysis = cr_with_incidents.merge(cr_total, left_on='cr_category', right_on='Category', how='right')
    failure_analysis['cr_number'] = failure_analysis['cr_number'].fillna(0)
    failure_analysis['inc_count'] = failure_analysis['inc_count'].fillna(0)
    failure_analysis['failure_rate'] = (failure_analysis['cr_number'] / failure_analysis['total_changes']) * 100
    failure_analysis['incidents_per_change'] = failure_analysis['inc_count'] / failure_analysis['cr_number'].replace(0, np.nan)
    failure_analysis = failure_analysis.sort_values('failure_rate', ascending=False)
    
    print(f"  Changes causing incidents within 24hrs: {post_change_df['cr_number'].nunique()}")
    print(f"  Total post-change incidents: {post_change_df['inc_count'].sum()}")
    print(f"  Top failure categories:")
    print(failure_analysis.head(10))
    
    # Plot: Change failure rate by category
    fig_cf = px.bar(
        failure_analysis.head(15),
        x='Category', y='failure_rate',
        title='Post-Change Incident Rate by Category (24hr Window)',
        labels={'failure_rate': 'Failure Rate (%)', 'Category': 'Change Category'},
        color='failure_rate', color_continuous_scale='Oranges'
    )
    fig_cf.update_layout(template='plotly_white', xaxis_tickangle=45)
    fig_cf.write_html("assets/images/html/cr_failure_rate_by_category.html")
    fig_cf.write_image("assets/images/png/cr_failure_rate_by_category.png", width=1200, height=600, scale=2)
    
    # Plot: Post-change incident timeline
    post_change_df['hour_after'] = (pd.to_datetime(post_change_df['work_end']) - pd.to_datetime(post_change_df['work_end']).min()).dt.total_seconds() / 3600
    fig_pt = px.histogram(
        post_change_df, x='inc_count', nbins=20,
        title='Distribution of Post-Change Incident Counts per Change',
        labels={'inc_count': 'Incidents within 24hrs', 'count': 'Number of Changes'}
    )
    fig_pt.update_layout(template='plotly_white')
    fig_pt.write_html("assets/images/html/cr_postchange_incident_dist.html")
    fig_pt.write_image("assets/images/png/cr_postchange_incident_dist.png", width=900, height=500, scale=2)
else:
    print("  No post-change incidents found in 24hr window.")

# ============================================================
# 3. CHANGE CATEGORY SPECIALIZATION CLUSTERING
# ============================================================
print("\n--- 3. Change Category Specialization Clustering ---")

# Features: category, ci_component, assigned_to
cr_cluster = cr.copy()
le_cat = LabelEncoder()
cr_cluster['cat_enc'] = le_cat.fit_transform(cr_cluster['Category'].astype(str))
le_comp = LabelEncoder()
cr_cluster['comp_enc'] = le_comp.fit_transform(cr_cluster['ci_component'].astype(str))
le_assign = LabelEncoder()
cr_cluster['assign_enc'] = le_assign.fit_transform(cr_cluster['assigned_to'].astype(str))

features = ['cat_enc', 'comp_enc', 'assign_enc']
X = cr_cluster[features].fillna(0)
scaler = StandardScaler()
X_scaled = scaler.fit_transform(X)

kmeans = KMeans(n_clusters=5, random_state=42, n_init=10)
cr_cluster['cluster'] = kmeans.fit_predict(X_scaled)

print(f"  Cluster distribution: {cr_cluster['cluster'].value_counts().to_dict()}")

# Analyze clusters
cluster_spec = cr_cluster.groupby('cluster').agg({
    'Category': lambda x: x.mode()[0] if not x.mode().empty else 'Mixed',
    'ci_component': lambda x: x.mode()[0] if not x.mode().empty else 'Mixed',
    'assigned_to': lambda x: x.mode()[0] if not x.mode().empty else 'Mixed',
    'number': 'count'
}).rename(columns={'number': 'change_count'})
print(cluster_spec)

# Plot cluster characteristics
fig_cl = px.scatter(
    cr_cluster, x='cat_enc', y='assign_enc', color='cluster',
    hover_data=['Category', 'assigned_to', 'ci_component'],
    title='Change Specialization Clusters (Category vs Assignee)',
    labels={'cat_enc': 'Category (encoded)', 'assign_enc': 'Assignee (encoded)'}
)
fig_cl.update_layout(template='plotly_white')
fig_cl.write_html("assets/images/html/cr_specialization_clusters.html")
fig_cl.write_image("assets/images/png/cr_specialization_clusters.png", width=1000, height=700, scale=2)

# Single point of failure: assignees handling critical categories alone
spof = cr_cluster.groupby(['Category', 'assigned_to']).size().reset_index(name='change_count')
spof_total = cr_cluster.groupby('Category').size().reset_index(name='total')
spof = spof.merge(spof_total, on='Category')
spof['pct_of_category'] = (spof['change_count'] / spof['total']) * 100
spof_high = spof[spof['pct_of_category'] > 50].sort_values('pct_of_category', ascending=False)
print(f"  Single points of failure (assignee >50% of category):")
print(spof_high.head(10))

# Plot SPOF
fig_spof = px.bar(
    spof_high.head(15),
    x='assigned_to', y='pct_of_category', color='Category',
    title='Single Points of Failure: Assignee Concentration by Category',
    labels={'pct_of_category': '% of Category', 'assigned_to': 'Assignee'}
)
fig_spof.update_layout(template='plotly_white', xaxis_tickangle=45)
fig_spof.write_html("assets/images/html/cr_single_point_of_failure.html")
fig_spof.write_image("assets/images/png/cr_single_point_of_failure.png", width=1200, height=600, scale=2)

# ============================================================
# 4. CHANGE STATE TRANSITION BOTTLENECK ANALYSIS
# ============================================================
print("\n--- 4. Change State Transition Bottleneck ---")

# Analyze Review vs Closed states
state_analysis = cr.groupby('state').agg({
    'number': 'count',
    'schedule_delta_hours': 'mean',
    'start_delay_hours': 'mean'
})
print(state_analysis)

# Dwell time in Review state (approximated by start_delay for Review states)
review_cr = cr[cr['state'] == 'Review'].copy()
closed_cr = cr[cr['state'] == 'Closed'].copy()

print(f"  Review state changes: {len(review_cr)}")
print(f"  Closed state changes: {len(closed_cr)}")
print(f"  Avg start delay (Review): {review_cr['start_delay_hours'].mean():.1f}h")
print(f"  Avg start delay (Closed): {closed_cr['start_delay_hours'].mean():.1f}h")

# Plot state distribution
state_counts = cr['state'].value_counts().reset_index()
state_counts.columns = ['state', 'count']
fig_state = px.pie(state_counts, names='state', values='count', title='Change Request State Distribution')
fig_state.update_traces(textinfo='percent+label')
fig_state.update_layout(template='plotly_white')
fig_state.write_html("assets/images/html/cr_state_distribution.html")
fig_state.write_image("assets/images/png/cr_state_distribution.png", width=800, height=600, scale=2)

# Plot bottleneck: start delay by category and state
delay_by_cat_state = cr.groupby(['Category', 'state'])['start_delay_hours'].mean().unstack(fill_value=0)
fig_delay = go.Figure()
for state in delay_by_cat_state.columns:
    fig_delay.add_trace(go.Bar(name=state, x=delay_by_cat_state.index, y=delay_by_cat_state[state]))
fig_delay.update_layout(
    barmode='group', title='Average Start Delay by Category and State',
    xaxis_title='Category', yaxis_title='Start Delay (hours)',
    template='plotly_white', xaxis_tickangle=45
)
fig_delay.write_html("assets/images/html/cr_start_delay_bottleneck.html")
fig_delay.write_image("assets/images/png/cr_start_delay_bottleneck.png", width=1400, height=600, scale=2)

# Save results
change_risk_results = {
    'overall_overrun_rate': cr_valid['is_overrun'].mean() * 100 if len(cr_valid) > 0 else 0,
    'postchange_changes_with_incidents': post_change_df['cr_number'].nunique() if len(post_change_df) > 0 else 0,
    'postchange_total_incidents': post_change_df['inc_count'].sum() if len(post_change_df) > 0 else 0,
    'review_state_count': len(review_cr),
    'spof_count': len(spof_high),
}
pd.Series(change_risk_results).to_json("temp_files/change_risk_results.json")

print("\nPhase 3B complete. All change risk plots saved.")