"""
Phase 3: Workload Distribution & Categorization Analytics
Volume trends, category frequency, priority stratification, CI coverage
"""

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
import warnings
warnings.filterwarnings('ignore')

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

print("=" * 80)
print("PHASE 3: WORKLOAD DISTRIBUTION & CATEGORIZATION ANALYTICS")
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")

# ============================================================================
# 1. INCIDENT & TASK VOLUME TREND ANALYSIS
# ============================================================================
print("\n📈 VOLUME TREND ANALYSIS")
print("-" * 40)

# Daily trends with 7-day rolling average
incidents_df['Opened_Date'] = pd.to_datetime(incidents_df['Opened_Date'])
tasks_df['Opened_Date'] = pd.to_datetime(tasks_df['Opened_Date'])

daily_inc = incidents_df.groupby('Opened_Date').size().reset_index(name='Count')
daily_inc['Rolling_Avg'] = daily_inc['Count'].rolling(window=7, min_periods=1).mean()

daily_task = tasks_df.groupby('Opened_Date').size().reset_index(name='Count')
daily_task['Rolling_Avg'] = daily_task['Count'].rolling(window=7, min_periods=1).mean()

fig_trends = make_subplots(
    rows=2, cols=1,
    subplot_titles=['Daily Incident Volume with 7-Day Rolling Average', 
                    'Daily Service Task Volume with 7-Day Rolling Average'],
    shared_xaxes=True,
    vertical_spacing=0.12
)

# Incidents
fig_trends.add_trace(
    go.Bar(x=daily_inc['Opened_Date'], y=daily_inc['Count'], 
           name='Daily Incidents', marker_color='#E63946', opacity=0.7),
    row=1, col=1
)
fig_trends.add_trace(
    go.Scatter(x=daily_inc['Opened_Date'], y=daily_inc['Rolling_Avg'],
               name='7-Day Avg', line=dict(color='#1D3557', width=3)),
    row=1, col=1
)

# Tasks
fig_trends.add_trace(
    go.Bar(x=daily_task['Opened_Date'], y=daily_task['Count'],
           name='Daily Tasks', marker_color='#457B9D', opacity=0.7),
    row=2, col=1
)
fig_trends.add_trace(
    go.Scatter(x=daily_task['Opened_Date'], y=daily_task['Rolling_Avg'],
               name='7-Day Avg', line=dict(color='#2A9D8F', width=3)),
    row=2, col=1
)

fig_trends.update_layout(
    template='plotly_white', height=600, showlegend=True,
    title_text='Daily Volume Trends with Rolling Average - 2025'
)
fig_trends.write_html(IMG_DIR / "volume_trends_with_rolling_avg.html")
fig_trends.write_image(IMG_DIR / "volume_trends_with_rolling_avg.png")
print("✓ Saved: volume_trends_with_rolling_avg")

# Weekly patterns
incidents_df['DayName'] = incidents_df['Opened'].dt.day_name()
tasks_df['DayName'] = tasks_df['Opened'].dt.day_name()

day_order = ['Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday']

weekly_inc = incidents_df.groupby('DayName').size().reindex(day_order).reset_index(name='Count')
weekly_task = tasks_df.groupby('DayName').size().reindex(day_order).reset_index(name='Count')

fig_weekly = make_subplots(
    rows=1, cols=2,
    subplot_titles=['Incident Volume by Day of Week', 'Task Volume by Day of Week']
)

fig_weekly.add_trace(
    go.Bar(x=weekly_inc['DayName'], y=weekly_inc['Count'],
           marker_color='#E63946', name='Incidents'),
    row=1, col=1
)
fig_weekly.add_trace(
    go.Bar(x=weekly_task['DayName'], y=weekly_task['Count'],
           marker_color='#457B9D', name='Tasks'),
    row=1, col=2
)

fig_weekly.update_layout(template='plotly_white', height=400, showlegend=False)
fig_weekly.write_html(IMG_DIR / "weekly_pattern_analysis.html")
fig_weekly.write_image(IMG_DIR / "weekly_pattern_analysis.png")
print("✓ Saved: weekly_pattern_analysis")

# Monthly heatmap
incidents_df['Month'] = incidents_df['Opened'].dt.month
tasks_df['Month'] = tasks_df['Opened'].dt.month
incidents_df['WeekOfYear'] = incidents_df['Opened'].dt.isocalendar().week
tasks_df['WeekOfYear'] = tasks_df['Opened'].dt.isocalendar().week

# ============================================================================
# 2. CATEGORY/SUBCATEGORY FREQUENCY PROFILING
# ============================================================================
print("\n📊 CATEGORY & SUBCATEGORY ANALYSIS")
print("-" * 40)

# Incidents by Category
cat_dist = incidents_df['Category'].value_counts()
fig_cat_full = px.bar(
    cat_dist, x=cat_dist.values, y=cat_dist.index, orientation='h',
    title='Complete Incident Category Distribution',
    labels={'index': 'Category', 'value': 'Count'},
    color=cat_dist.values, color_continuous_scale='Viridis'
)
fig_cat_full.update_layout(template='plotly_white', height=400)
fig_cat_full.write_html(IMG_DIR / "incident_category_distribution.html")
fig_cat_full.write_image(IMG_DIR / "incident_category_distribution.png")
print("✓ Saved: incident_category_distribution")

# Incidents by Subcategory (Top 15)
subcat_inc = incidents_df['Subcategory'].value_counts().head(15)
fig_subcat = px.bar(
    subcat_inc, x=subcat_inc.values, y=subcat_inc.index, orientation='h',
    title='Top 15 Incident Subcategories',
    labels={'index': 'Subcategory', 'value': 'Count'},
    color=subcat_inc.values, color_continuous_scale='Plasma'
)
fig_subcat.update_layout(template='plotly_white', height=500)
fig_subcat.write_html(IMG_DIR / "incident_subcategory_top15.html")
fig_subcat.write_image(IMG_DIR / "incident_subcategory_top15.png")
print("✓ Saved: incident_subcategory_top15")

# Tasks by Subcategory
subcat_task = tasks_df['Subcategory'].value_counts()
fig_task_subcat = px.bar(
    subcat_task, x=subcat_task.values, y=subcat_task.index, orientation='h',
    title='Task Subcategory Distribution',
    labels={'index': 'Subcategory', 'value': 'Count'},
    color=subcat_task.values, color_continuous_scale='Magma'
)
fig_task_subcat.update_layout(template='plotly_white', height=500)
fig_task_subcat.write_html(IMG_DIR / "task_subcategory_distribution.html")
fig_task_subcat.write_image(IMG_DIR / "task_subcategory_distribution.png")
print("✓ Saved: task_subcategory_distribution")

# Category × Priority cross-tabulation
cat_pri = pd.crosstab(incidents_df['Category'], incidents_df['Priority'])
fig_heat = px.imshow(
    cat_pri, text_auto=True, aspect='auto',
    title='Category × Priority Heatmap (Incidents)',
    color_continuous_scale='Blues'
)
fig_heat.update_layout(template='plotly_white', height=500)
fig_heat.write_html(IMG_DIR / "category_priority_heatmap.html")
fig_heat.write_image(IMG_DIR / "category_priority_heatmap.png")
print("✓ Saved: category_priority_heatmap")

# ============================================================================
# 3. PRIORITY STRATIFICATION ASSESSMENT
# ============================================================================
print("\n🎯 PRIORITY STRATIFICATION ANALYSIS")
print("-" * 40)

# Priority distribution with percentages
pri_inc = incidents_df['Priority'].value_counts().reset_index()
pri_inc['Percentage'] = (pri_inc['count'] / pri_inc['count'].sum() * 100).round(1)

fig_pri_analysis = px.bar(
    pri_inc, x='Priority', y='count',
    title='Incident Priority Distribution',
    labels={'count': 'Count', 'Priority': 'Priority Level'},
    text='Percentage',
    color='Priority',
    color_discrete_map={
        '4 - Low': '#28A745',
        '3 - Moderate': '#FFC107',
        '2 - High': '#DC3545',
        '1 - Critical': '#7B1FA2'
    }
)
fig_pri_analysis.update_layout(template='plotly_white', height=400)
fig_pri_analysis.write_html(IMG_DIR / "priority_stratification.html")
fig_pri_analysis.write_image(IMG_DIR / "priority_stratification.png")
print("✓ Saved: priority_stratification")

# Priority vs Resolution Time
fig_pri_time = px.box(
    incidents_df, x='Priority', y='Resolution_Hours',
    title='Resolution Time Distribution by Priority Level',
    labels={'Resolution_Hours': 'Resolution Hours', 'Priority': 'Priority'},
    color='Priority',
    color_discrete_map={
        '4 - Low': '#28A745',
        '3 - Moderate': '#FFC107',
        '2 - High': '#DC3545'
    }
)
fig_pri_time.update_layout(template='plotly_white', height=400)
fig_pri_time.write_html(IMG_DIR / "priority_vs_resolution_time.html")
fig_pri_time.write_image(IMG_DIR / "priority_vs_resolution_time.png")
print("✓ Saved: priority_vs_resolution_time")

# High-priority incidents deep dive
high_pri_inc = incidents_df[incidents_df['Priority'].isin(['2 - High', '1 - Critical'])]
if len(high_pri_inc) > 0:
    fig_high_pri = px.scatter(
        high_pri_inc, x='Opened', y='Resolution_Hours',
        color='Category', size='Resolution_Hours',
        title='High Priority Incidents: Resolution Time Analysis',
        hover_data=['Subcategory', 'Assignment group']
    )
    fig_high_pri.update_layout(template='plotly_white', height=450)
    fig_high_pri.write_html(IMG_DIR / "high_priority_incidents.html")
    fig_high_pri.write_image(IMG_DIR / "high_priority_incidents.png")
    print("✓ Saved: high_priority_incidents")

# ============================================================================
# 4. CONFIGURATION ITEM COVERAGE MAPPING
# ============================================================================
print("\n🔧 CONFIGURATION ITEM COVERAGE ANALYSIS")
print("-" * 40)

# Top Configuration Items for Incidents
ci_inc = incidents_df['Configuration item'].value_counts().head(15)
fig_ci_inc = px.bar(
    ci_inc, x=ci_inc.values, y=ci_inc.index, orientation='h',
    title='Top 15 Configuration Items (Incidents)',
    labels={'index': 'Configuration Item', 'value': 'Incident Count'},
    color=ci_inc.values, color_continuous_scale='Reds'
)
fig_ci_inc.update_layout(template='plotly_white', height=500)
fig_ci_inc.write_html(IMG_DIR / "ci_incident_coverage.html")
fig_ci_inc.write_image(IMG_DIR / "ci_incident_coverage.png")
print("✓ Saved: ci_incident_coverage")

# Top Configuration Items for Tasks
ci_task = tasks_df['Configuration item'].value_counts().head(15)
fig_ci_task = px.bar(
    ci_task, x=ci_task.values, y=ci_task.index, orientation='h',
    title='Top 15 Configuration Items (Tasks)',
    labels={'index': 'Configuration Item', 'value': 'Task Count'},
    color=ci_task.values, color_continuous_scale='Greens'
)
fig_ci_task.update_layout(template='plotly_white', height=500)
fig_ci_task.write_html(IMG_DIR / "ci_task_coverage.html")
fig_ci_task.write_image(IMG_DIR / "ci_task_coverage.png")
print("✓ Saved: ci_task_coverage")

# CI × Assignment Group for Tasks
ci_group = pd.crosstab(tasks_df['Configuration item'], tasks_df['Assignment group'])
ci_group_top = ci_group.loc[ci_group.sum(axis=1).nlargest(10).index]

fig_ci_group = px.bar(
    ci_group_top, barmode='group',
    title='Top 10 CIs by Assignment Group (Tasks)',
    color_discrete_sequence=px.colors.qualitative.Set2
)
fig_ci_group.update_layout(template='plotly_white', height=500)
fig_ci_group.write_html(IMG_DIR / "ci_assignment_group_matrix.html")
fig_ci_group.write_image(IMG_DIR / "ci_assignment_group_matrix.png")
print("✓ Saved: ci_assignment_group_matrix")

# Platform summary
print("\n📋 CI PLATFORM SUMMARY:")
platform_mapping = {
    'SAP': 'SAP ECC/CIP',
    'ORACLE': 'Oracle INTELLIGRATED',
    'GRC': 'SAP GRC'
}

for df_name, df in [('Incidents', incidents_df), ('Tasks', tasks_df)]:
    print(f"\n  {df_name}:")
    for ci in df['Configuration item'].unique()[:5]:
        print(f"    - {ci}")

# ============================================================================
# 5. SEASONALITY ANALYSIS
# ============================================================================
print("\n📅 SEASONALITY ANALYSIS")
print("-" * 40)

# Monthly volumes
month_names = ['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 
               'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec']

monthly_inc = incidents_df.groupby('Month').size().reindex(range(1,13), fill_value=0)
monthly_task = tasks_df.groupby('Month').size().reindex(range(1,13), fill_value=0)

fig_season = go.Figure()
fig_season.add_trace(go.Bar(
    x=month_names, y=monthly_inc.values,
    name='Incidents', marker_color='#E63946', opacity=0.8
))
fig_season.add_trace(go.Bar(
    x=month_names, y=monthly_task.values / 10,  # Scaled for visibility
    name='Tasks (÷10)', marker_color='#457B9D', opacity=0.8
))
fig_season.update_layout(
    title='Monthly Volume Seasonality - 2025',
    barmode='group', template='plotly_white', height=400
)
fig_season.write_html(IMG_DIR / "monthly_seasonality.html")
fig_season.write_image(IMG_DIR / "monthly_seasonality.png")
print("✓ Saved: monthly_seasonality")

# Quarter comparison
quarter_inc = incidents_df.groupby('Quarter').size()
quarter_task = tasks_df.groupby('Quarter').size()

fig_quarter = make_subplots(
    rows=1, cols=2,
    subplot_titles=['Incidents by Quarter', 'Tasks by Quarter'],
    specs=[[{'type':'pie'}, {'type':'pie'}]]
)

fig_quarter.add_trace(
    go.Pie(labels=[f'Q{q}' for q in quarter_inc.index], 
           values=quarter_inc.values, hole=0.4),
    row=1, col=1
)
fig_quarter.add_trace(
    go.Pie(labels=[f'Q{q}' for q in quarter_task.index], 
           values=quarter_task.values, hole=0.4),
    row=1, col=2
)
fig_quarter.update_layout(template='plotly_white', height=400)
fig_quarter.write_html(IMG_DIR / "quarterly_distribution.html")
fig_quarter.write_image(IMG_DIR / "quarterly_distribution.png")
print("✓ Saved: quarterly_distribution")

# ============================================================================
# KEY METRICS SUMMARY
# ============================================================================
print("\n📋 WORKLOAD DISTRIBUTION KEY METRICS")
print("-" * 40)

print("\n📊 INCIDENT VOLUME METRICS:")
print(f"  Total Volume: {len(incidents_df):,}")
print(f"  Daily Average: {len(incidents_df)/incidents_df['Opened_Date'].nunique():.1f}")
print(f"  Peak Day: {daily_inc.loc[daily_inc['Count'].idxmax(), 'Opened_Date'].strftime('%Y-%m-%d')} ({daily_inc['Count'].max()} incidents)")
print(f"  Lowest Day: {daily_inc.loc[daily_inc['Count'].idxmin(), 'Opened_Date'].strftime('%Y-%m-%d')} ({daily_inc['Count'].min()} incidents)")

print("\n📊 TASK VOLUME METRICS:")
print(f"  Total Volume: {len(tasks_df):,}")
print(f"  Daily Average: {len(tasks_df)/tasks_df['Opened_Date'].nunique():.1f}")
print(f"  Peak Day: {daily_task.loc[daily_task['Count'].idxmax(), 'Opened_Date'].strftime('%Y-%m-%d')} ({daily_task['Count'].max()} tasks)")
print(f"  Lowest Day: {daily_task.loc[daily_task['Count'].idxmin(), 'Opened_Date'].strftime('%Y-%m-%d')} ({daily_task['Count'].min()} tasks)")

print("\n📊 CATEGORY CONCENTRATION:")
print(f"  Top Category: {cat_dist.index[0]} ({cat_dist.values[0]:,} - {cat_dist.values[0]/len(incidents_df)*100:.1f}%)")
print(f"  Top 3 Categories: {cat_dist.head(3).index.tolist()}")

print("\n📊 PRIORITY DISTRIBUTION:")
for pri in ['4 - Low', '3 - Moderate', '2 - High', '1 - Critical']:
    count = len(incidents_df[incidents_df['Priority'] == pri])
    print(f"  {pri}: {count} ({count/len(incidents_df)*100:.1f}%)")

print("\n✅ WORKLOAD DISTRIBUTION ANALYSIS COMPLETE")
print(f"✓ {len(list(IMG_DIR.glob('*workload*')))} additional visualizations saved")