"""
Phase 3C: Service Request Fulfillment & OLA Performance
Subtopics:
1. OLA breach prediction using logistic regression
2. Catalog item demand segmentation for automation
3. Reassignment-driven rework detection
4. Transport promotion pipeline lead-time analysis
"""

import pandas as pd
from pathlib import Path
Path("models").mkdir(exist_ok=True)
import numpy as np
import plotly.express as px
import plotly.graph_objects as go
from plotly.subplots import make_subplots
from sklearn.linear_model import LogisticRegression
from sklearn.preprocessing import StandardScaler, LabelEncoder
from sklearn.model_selection import train_test_split
from sklearn.metrics import classification_report, roc_auc_score, roc_curve
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 3C: SERVICE REQUEST FULFILLMENT & OLA PERFORMANCE")
print("=" * 80)

# ============================================================
# 1. OLA BREACH PREDICTION USING LOGISTIC REGRESSION
# ============================================================
print("\n--- 1. OLA Breach Prediction (Logistic Regression) ---")

# Prepare data for modeling
sr_model = sr[sr['ola_hours'].notna() & sr['resolution_hours'].notna()].copy()
sr_model = sr_model[sr_model['resolution_hours'] >= 0]  # Filter invalid

# Features
features = ['reassignment_count', 'sys_mod_count']
le_cat = LabelEncoder()
sr_model['cat_item_enc'] = le_cat.fit_transform(sr_model['cat_item'].astype(str))
features.append('cat_item_enc')

le_env = LabelEncoder()
sr_model['ci_env_enc'] = le_env.fit_transform(sr_model['ci_env'].astype(str))
features.append('ci_env_enc')

# Target
sr_model['breached'] = sr_model['ola_breached'].astype(int)

# Filter to closed tasks only
sr_model = sr_model[sr_model['state'] == 'Closed']

print(f"  Modeling dataset: {len(sr_model)} tasks")
print(f"  Breach rate: {sr_model['breached'].mean()*100:.1f}%")

if len(sr_model) > 100 and sr_model['breached'].sum() > 10:
    X = sr_model[features].fillna(0)
    y = sr_model['breached']
    
    X_train, X_test, y_train, y_test = train_test_split(X, y, test_size=0.3, random_state=42, stratify=y)
    
    scaler = StandardScaler()
    X_train_s = scaler.fit_transform(X_train)
    X_test_s = scaler.transform(X_test)
    
    lr = LogisticRegression(max_iter=1000, class_weight='balanced', random_state=42)
    lr.fit(X_train_s, y_train)
    
    y_pred = lr.predict(X_test_s)
    y_prob = lr.predict_proba(X_test_s)[:, 1]
    
    auc = roc_auc_score(y_test, y_prob)
    print(f"  ROC-AUC: {auc:.3f}")
    
    # Feature importance
    feature_imp = pd.DataFrame({
        'feature': features,
        'coefficient': lr.coef_[0]
    }).sort_values('coefficient', key=abs, ascending=False)
    print(feature_imp)
    
    # Plot ROC curve
    fpr, tpr, _ = roc_curve(y_test, y_prob)
    fig_roc = go.Figure()
    fig_roc.add_trace(go.Scatter(x=fpr, y=tpr, mode='lines', name=f'ROC Curve (AUC={auc:.3f})', line=dict(color='blue')))
    fig_roc.add_trace(go.Scatter(x=[0, 1], y=[0, 1], mode='lines', name='Random', line=dict(color='red', dash='dash')))
    fig_roc.update_layout(
        title='OLA Breach Prediction ROC Curve',
        xaxis_title='False Positive Rate', yaxis_title='True Positive Rate',
        template='plotly_white'
    )
    fig_roc.write_html("assets/images/html/sr_ola_breach_roc.html")
    fig_roc.write_image("assets/images/png/sr_ola_breach_roc.png", width=800, height=600, scale=2)
    
    # Plot feature importance
    fig_fi = px.bar(
        feature_imp, x='coefficient', y='feature', orientation='h',
        title='OLA Breach Prediction: Feature Importance',
        labels={'coefficient': 'Coefficient', 'feature': 'Feature'},
        color='coefficient', color_continuous_scale='RdBu'
    )
    fig_fi.update_layout(template='plotly_white')
    fig_fi.write_html("assets/images/html/sr_ola_feature_importance.html")
    fig_fi.write_image("assets/images/png/sr_ola_feature_importance.png", width=900, height=500, scale=2)
    
    # Save model
    import joblib
    joblib.dump(lr, "models/sr_ola_breach_model.pkl")
    print("  Model saved to models/sr_ola_breach_model.pkl")

# Plot: OLA breach by catalog item
breach_by_item = sr.groupby('cat_item')['ola_breached'].agg(['mean', 'count']).reset_index()
breach_by_item.columns = ['cat_item', 'breach_rate', 'count']
breach_by_item = breach_by_item[breach_by_item['count'] >= 20].sort_values('breach_rate', ascending=False).head(15)

fig_breach = px.bar(
    breach_by_item, x='breach_rate', y='cat_item', orientation='h',
    title='OLA Breach Rate by Catalog Item (Min 20 tasks)',
    labels={'breach_rate': 'Breach Rate', 'cat_item': 'Catalog Item'},
    color='breach_rate', color_continuous_scale='Reds'
)
fig_breach.update_layout(template='plotly_white')
fig_breach.write_html("assets/images/html/sr_ola_breach_by_item.html")
fig_breach.write_image("assets/images/png/sr_ola_breach_by_item.png", width=1000, height=700, scale=2)

# ============================================================
# 2. CATALOG ITEM DEMAND SEGMENTATION FOR AUTOMATION
# ============================================================
print("\n--- 2. Catalog Item Demand Segmentation ---")

# Compute frequency and effort metrics
cat_seg = sr.groupby('cat_item').agg({
    'number': 'count',
    'resolution_hours': 'mean',
    'reassignment_count': 'mean',
    'sys_mod_count': 'mean'
}).rename(columns={'number': 'volume', 'resolution_hours': 'avg_resolution'}).reset_index()

cat_seg['automation_score'] = (
    cat_seg['volume'] * 0.4 +
    (1 / (cat_seg['avg_resolution'] + 1)) * 100 * 0.3 +
    (1 / (cat_seg['reassignment_count'] + 1)) * 100 * 0.2 +
    (1 / (cat_seg['sys_mod_count'] + 1)) * 100 * 0.1
)

cat_seg = cat_seg.sort_values('automation_score', ascending=False)
print(f"  Top automation candidates:")
print(cat_seg.head(10)[['cat_item', 'volume', 'avg_resolution', 'automation_score']])

# Plot: Automation quadrant (Volume vs Resolution Time)
fig_auto = px.scatter(
    cat_seg, x='volume', y='avg_resolution', size='automation_score',
    hover_data=['cat_item', 'reassignment_count'],
    title='Automation Candidate Segmentation (Volume vs Resolution Time)',
    labels={'volume': 'Task Volume', 'avg_resolution': 'Avg Resolution (hours)'},
    color='automation_score', color_continuous_scale='Greens'
)
fig_auto.update_layout(template='plotly_white')
fig_auto.write_html("assets/images/html/sr_automation_segmentation.html")
fig_auto.write_image("assets/images/png/sr_automation_segmentation.png", width=1000, height=700, scale=2)

# Plot: Top catalog items by volume
fig_vol = px.bar(
    cat_seg.head(15), x='volume', y='cat_item', orientation='h',
    title='Top 15 Catalog Items by Volume',
    labels={'volume': 'Volume', 'cat_item': 'Catalog Item'},
    color='volume', color_continuous_scale='Blues'
)
fig_vol.update_layout(template='plotly_white')
fig_vol.write_html("assets/images/html/sr_catalog_volume.html")
fig_vol.write_image("assets/images/png/sr_catalog_volume.png", width=1000, height=700, scale=2)

# ============================================================
# 3. REASSIGNMENT-DRIVEN REWORK DETECTION
# ============================================================
print("\n--- 3. Reassignment-Driven Rework Detection ---")

# Flag high-reassignment tasks
sr['rework_flag'] = (sr['reassignment_count'] >= 2).astype(int)
rework_rate = sr['rework_flag'].mean() * 100
print(f"  Overall rework rate (>=2 reassignments): {rework_rate:.1f}%")

# Rework by catalog item
rework_by_item = sr.groupby('cat_item').agg({
    'rework_flag': 'mean',
    'number': 'count',
    'resolution_hours': 'mean'
}).reset_index()
rework_by_item.columns = ['cat_item', 'rework_rate', 'volume', 'avg_resolution']
rework_by_item = rework_by_item[rework_by_item['volume'] >= 20].sort_values('rework_rate', ascending=False)

print(f"  Top rework items:")
print(rework_by_item.head(10))

# Plot: Rework rate by catalog item
fig_rw = px.bar(
    rework_by_item.head(15), x='rework_rate', y='cat_item', orientation='h',
    title='Rework Rate by Catalog Item (>=2 Reassignments, Min 20 tasks)',
    labels={'rework_rate': 'Rework Rate', 'cat_item': 'Catalog Item'},
    color='rework_rate', color_continuous_scale='Oranges'
)
fig_rw.update_layout(template='plotly_white')
fig_rw.write_html("assets/images/html/sr_rework_rate_by_item.html")
fig_rw.write_image("assets/images/png/sr_rework_rate_by_item.png", width=1000, height=700, scale=2)

# Plot: Resolution time vs reassignment count
fig_rt = px.box(
    sr[sr['reassignment_count'] <= 5], x='reassignment_count', y='resolution_hours',
    title='Resolution Time vs Reassignment Count',
    labels={'reassignment_count': 'Reassignment Count', 'resolution_hours': 'Resolution (hours)'}
)
fig_rt.update_layout(template='plotly_white')
fig_rt.write_html("assets/images/html/sr_resolution_vs_reassignment.html")
fig_rt.write_image("assets/images/png/sr_resolution_vs_reassignment.png", width=900, height=500, scale=2)

# ============================================================
# 4. TRANSPORT PROMOTION PIPELINE LEAD-TIME ANALYSIS
# ============================================================
print("\n--- 4. Transport Promotion Lead-Time Analysis ---")

transport = sr[sr['is_transport_promotion'] == True].copy()
print(f"  Transport promotion tasks: {len(transport)}")

if len(transport) > 0:
    transport = transport[transport['resolution_hours'].notna() & (transport['resolution_hours'] >= 0)]
    
    # Lead time distribution
    fig_lt = px.histogram(
        transport, x='resolution_hours', nbins=50,
        title='Transport Promotion Lead-Time Distribution',
        labels={'resolution_hours': 'Resolution Time (hours)', 'count': 'Count'},
        color_discrete_sequence=['teal']
    )
    fig_lt.update_layout(template='plotly_white')
    fig_lt.write_html("assets/images/html/sr_transport_leadtime_dist.html")
    fig_lt.write_image("assets/images/png/sr_transport_leadtime_dist.png", width=900, height=500, scale=2)
    
    # Lead time by environment
    lt_by_env = transport.groupby('ci_env')['resolution_hours'].agg(['mean', 'median', 'count']).reset_index()
    lt_by_env = lt_by_env[lt_by_env['count'] >= 5]
    fig_lt_env = px.bar(
        lt_by_env, x='ci_env', y='mean',
        title='Transport Promotion: Mean Lead Time by Environment',
        labels={'ci_env': 'Environment', 'mean': 'Mean Resolution (hours)'},
        color='mean', color_continuous_scale='Purples'
    )
    fig_lt_env.update_layout(template='plotly_white')
    fig_lt_env.write_html("assets/images/html/sr_transport_leadtime_by_env.html")
    fig_lt_env.write_image("assets/images/png/sr_transport_leadtime_by_env.png", width=900, height=500, scale=2)
    
    # Lead time trend over time
    transport['month'] = transport['opened_at'].dt.to_period('M').astype(str)
    lt_trend = transport.groupby('month')['resolution_hours'].mean().reset_index()
    fig_lt_trend = px.line(
        lt_trend, x='month', y='resolution_hours', markers=True,
        title='Transport Promotion Lead Time Trend',
        labels={'month': 'Month', 'resolution_hours': 'Mean Resolution (hours)'}
    )
    fig_lt_trend.update_layout(template='plotly_white')
    fig_lt_trend.write_html("assets/images/html/sr_transport_leadtime_trend.html")
    fig_lt_trend.write_image("assets/images/png/sr_transport_leadtime_trend.png", width=1000, height=500, scale=2)
    
    print(f"  Mean transport lead time: {transport['resolution_hours'].mean():.1f}h")
    print(f"  Median transport lead time: {transport['resolution_hours'].median():.1f}h")
    print(f"  By environment:\n{lt_by_env}")

# Save SR results
sr_results = {
    'total_sr_tasks': len(sr),
    'ola_breach_rate': sr['ola_breached'].mean() * 100 if 'ola_breached' in sr.columns else 0,
    'rework_rate': rework_rate,
    'transport_tasks': len(transport) if 'transport' in locals() else 0,
    'transport_mean_leadtime': transport['resolution_hours'].mean() if 'transport' in locals() and len(transport) > 0 else 0,
}
pd.Series(sr_results).to_json("temp_files/sr_results.json")

print("\nPhase 3C complete. All service request plots saved.")