"""
Phase 2: Cleaning & Exploratory Data Analysis
Handle missing values, create derived metrics, produce foundational visualizations
"""

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
DATA_DIR = Path("/app/workspace/user_source_data")
OUTPUT_DIR = Path("/app/workspace/temp_files")
IMG_DIR = Path("/app/workspace/assets/images")

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

# Load raw data
incidents_df = pd.read_pickle(OUTPUT_DIR / "incidents_raw.pkl")
tasks_df = pd.read_pickle(OUTPUT_DIR / "tasks_raw.pkl")

# ============================================================================
# CLEANING OPERATIONS
# ============================================================================
print("\n🧹 DATA CLEANING OPERATIONS")
print("-" * 40)

# Standardize column names for merging
# Tasks have 'Sub Category' vs 'Subcategory' in incidents
tasks_df = tasks_df.rename(columns={'Sub Category': 'Subcategory'})

# Handle missing values - fill where appropriate
incidents_df['Assigned to'] = incidents_df['Assigned to'].fillna('Unassigned')
tasks_df['Assigned to'] = tasks_df['Assigned to'].fillna('Unassigned')

incidents_df['Category'] = incidents_df['Category'].fillna('Unknown')
tasks_df['Category'] = tasks_df['Category'].fillna('Unknown')

incidents_df['Subcategory'] = incidents_df['Subcategory'].fillna('Unknown')
tasks_df['Subcategory'] = tasks_df['Subcategory'].fillna('Unknown')

incidents_df['Configuration item'] = incidents_df['Configuration item'].fillna('Unknown')
tasks_df['Configuration item'] = tasks_df['Configuration item'].fillna('Unknown')

print("✓ Standardized column names (Subcategory)")
print("✓ Handled missing values in key categorical fields")

# ============================================================================
# CREATE DERIVED METRICS
# ============================================================================
print("\n📊 CREATING DERIVED METRICS")
print("-" * 40)

# Incidents: Calculate resolution duration (hours)
incidents_df['Resolution_Hours'] = (
    incidents_df['Resolved'] - incidents_df['Opened']
).dt.total_seconds() / 3600

incidents_df['Total_Elapsed_Hours'] = (
    incidents_df['Closed'] - incidents_df['Opened']
).dt.total_seconds() / 3600

incidents_df['Calendar_Duration_Hours'] = incidents_df['Total_Elapsed_Hours']

# Tasks: Business duration is already in seconds - convert to hours
tasks_df['Business_Duration_Hours'] = tasks_df['Business duration'] / 3600
tasks_df['Business_Duration_Days'] = tasks_df['Business duration'] / 86400

# Calculate total elapsed time for tasks
tasks_df['Total_Elapsed_Hours'] = (
    tasks_df['Closed'] - tasks_df['Opened']
).dt.total_seconds() / 3600

# Calculate after-hours ratio (Business vs Calendar)
tasks_df['After_Hours_Ratio'] = np.where(
    tasks_df['Total_Elapsed_Hours'] > 0,
    tasks_df['Business_Duration_Hours'] / tasks_df['Total_Elapsed_Hours'],
    0
)

# Create derived datetime fields
incidents_df['Opened_Date'] = incidents_df['Opened'].dt.date
incidents_df['Opened_Month'] = incidents_df['Opened'].dt.to_period('M')
incidents_df['Opened_Week'] = incidents_df['Opened'].dt.to_period('W')
incidents_df['DayOfWeek'] = incidents_df['Opened'].dt.dayofweek
incidents_df['HourOfDay'] = incidents_df['Opened'].dt.hour

tasks_df['Opened_Date'] = tasks_df['Opened'].dt.date
tasks_df['Opened_Month'] = tasks_df['Opened'].dt.to_period('M')
tasks_df['Opened_Week'] = tasks_df['Opened'].dt.to_period('W')
tasks_df['DayOfWeek'] = tasks_df['Opened'].dt.dayofweek
tasks_df['HourOfDay'] = tasks_df['Opened'].dt.hour

# Create success/failure flags based on state
incidents_df['Outcome'] = 'Closed'  # All incidents are closed

# For tasks: success = Closed Complete, failure = Closed Incomplete
tasks_df['Resolution_Outcome'] = np.where(
    tasks_df['State'] == 'Closed Complete', 'Success',
    np.where(tasks_df['State'] == 'Closed Incomplete', 'Failure', 'Other')
)

# Create priority numeric for analysis
priority_map = {'1 - Critical': 1, '2 - High': 2, '3 - Moderate': 3, '4 - Low': 4}
incidents_df['Priority_Num'] = incidents_df['Priority'].map(priority_map)
tasks_df['Priority_Num'] = tasks_df['Priority'].map(priority_map)

# Create time-based periods
incidents_df['Quarter'] = incidents_df['Opened'].dt.quarter
tasks_df['Quarter'] = tasks_df['Opened'].dt.quarter

print("✓ Created resolution duration metrics (hours, days)")
print("✓ Created temporal dimensions (date, week, month, quarter, day of week, hour)")
print("✓ Created resolution outcome flags (Success/Failure)")
print("✓ Created numeric priority mapping")

# ============================================================================
# FOUNDATIONAL VISUALIZATIONS
# ============================================================================
print("\n📈 GENERATING FOUNDATIONAL VISUALIZATIONS")
print("-" * 40)

# 1. Incident Volume Over Time (Daily)
daily_incidents = incidents_df.groupby('Opened_Date').size().reset_index(name='Count')
fig_inc_daily = px.bar(
    daily_incidents, x='Opened_Date', y='Count',
    title='Daily Incident Volume - 2025',
    labels={'Opened_Date': 'Date', 'Count': 'Number of Incidents'},
    color_discrete_sequence=['#2E86AB']
)
fig_inc_daily.update_layout(template='plotly_white', height=350)
fig_inc_daily.write_html(IMG_DIR / "incidents_daily_volume.html")
fig_inc_daily.write_image(IMG_DIR / "incidents_daily_volume.png")
print("✓ Saved: incidents_daily_volume")

# 2. Task Volume Over Time (Daily)
daily_tasks = tasks_df.groupby('Opened_Date').size().reset_index(name='Count')
fig_task_daily = px.bar(
    daily_tasks, x='Opened_Date', y='Count',
    title='Daily Service Task Volume - 2025',
    labels={'Opened_Date': 'Date', 'Count': 'Number of Tasks'},
    color_discrete_sequence=['#28A745']
)
fig_task_daily.update_layout(template='plotly_white', height=350)
fig_task_daily.write_html(IMG_DIR / "tasks_daily_volume.html")
fig_task_daily.write_image(IMG_DIR / "tasks_daily_volume.png")
print("✓ Saved: tasks_daily_volume")

# 3. Monthly Trend Comparison
monthly_data = []
for month in incidents_df['Opened_Month'].unique():
    count = len(incidents_df[incidents_df['Opened_Month'] == month])
    monthly_data.append({'Period': str(month), 'Type': 'Incidents', 'Count': count})
for month in tasks_df['Opened_Month'].unique():
    count = len(tasks_df[tasks_df['Opened_Month'] == month])
    monthly_data.append({'Period': str(month), 'Type': 'Tasks', 'Count': count})
monthly_df = pd.DataFrame(monthly_data)

fig_monthly = px.line(
    monthly_df, x='Period', y='Count', color='Type',
    title='Monthly Volume Trend: Incidents vs Tasks',
    markers=True,
    color_discrete_map={'Incidents': '#E63946', 'Tasks': '#457B9D'}
)
fig_monthly.update_layout(template='plotly_white', height=400)
fig_monthly.write_html(IMG_DIR / "monthly_trend_comparison.html")
fig_monthly.write_image(IMG_DIR / "monthly_trend_comparison.png")
print("✓ Saved: monthly_trend_comparison")

# 4. Priority Distribution (Both Datasets)
fig_pri = make_subplots(
    rows=1, cols=2,
    subplot_titles=['Incidents by Priority', 'Tasks by Priority'],
    specs=[[{'type':'pie'}, {'type':'pie'}]]
)

pri_counts_inc = incidents_df['Priority'].value_counts()
fig_pri.add_trace(
    go.Pie(labels=pri_counts_inc.index, values=pri_counts_inc.values, 
           marker=dict(colors=['#28A745', '#FFC107', '#DC3545']), hole=0.4),
    row=1, col=1
)

pri_counts_task = tasks_df['Priority'].value_counts()
fig_pri.add_trace(
    go.Pie(labels=pri_counts_task.index, values=pri_counts_task.values,
           marker=dict(colors=['#28A745', '#FFC107', '#DC3545']), hole=0.4),
    row=1, col=2
)
fig_pri.update_layout(template='plotly_white', height=400, showlegend=True)
fig_pri.write_html(IMG_DIR / "priority_distribution.html")
fig_pri.write_image(IMG_DIR / "priority_distribution.png")
print("✓ Saved: priority_distribution")

# 5. Category Distribution (Incidents)
cat_inc = incidents_df['Category'].value_counts().head(10)
fig_cat = px.bar(
    cat_inc, x=cat_inc.values, y=cat_inc.index, orientation='h',
    title='Incident Categories (Top 10)',
    labels={'index': 'Category', 'value': 'Count'},
    color_discrete_sequence=['#6C5CE7']
)
fig_cat.update_layout(template='plotly_white', height=400)
fig_cat.write_html(IMG_DIR / "incident_categories.html")
fig_cat.write_image(IMG_DIR / "incident_categories.png")
print("✓ Saved: incident_categories")

# 6. Assignment Group Distribution
fig_assign = make_subplots(
    rows=1, cols=2,
    subplot_titles=['Incidents by Assignment Group', 'Tasks by Assignment Group']
)

assign_inc = incidents_df['Assignment group'].value_counts()
fig_assign.add_trace(
    go.Bar(x=assign_inc.values, y=assign_inc.index, orientation='h',
           marker=dict(color='#E76F51'), name='Incidents'),
    row=1, col=1
)

assign_task = tasks_df['Assignment group'].value_counts().head(6)
fig_assign.add_trace(
    go.Bar(x=assign_task.values, y=assign_task.index, orientation='h',
           marker=dict(color='#2A9D8F'), name='Tasks'),
    row=1, col=2
)
fig_assign.update_layout(template='plotly_white', height=400, showlegend=False)
fig_assign.write_html(IMG_DIR / "assignment_group_distribution.html")
fig_assign.write_image(IMG_DIR / "assignment_group_distribution.png")
print("✓ Saved: assignment_group_distribution")

# 7. State Distribution (Tasks)
state_counts = tasks_df['State'].value_counts()
fig_state = px.bar(
    state_counts, x=state_counts.index, y=state_counts.values,
    title='Task State Distribution',
    labels={'index': 'State', 'value': 'Count'},
    color_discrete_sequence=['#06D6A0']
)
fig_state.update_layout(template='plotly_white', height=350)
fig_state.write_html(IMG_DIR / "task_state_distribution.html")
fig_state.write_image(IMG_DIR / "task_state_distribution.png")
print("✓ Saved: task_state_distribution")

# 8. Channel Distribution (Incidents)
channel_counts = incidents_df['Channel'].value_counts()
fig_channel = px.pie(
    channel_counts, values=channel_counts.values, names=channel_counts.index,
    title='Incident Channel Distribution',
    color_discrete_sequence=px.colors.qualitative.Set3
)
fig_channel.update_layout(template='plotly_white', height=400)
fig_channel.write_html(IMG_DIR / "channel_distribution.html")
fig_channel.write_image(IMG_DIR / "channel_distribution.png")
print("✓ Saved: channel_distribution")

# 9. Resolution Duration Distribution (Tasks)
valid_durations = tasks_df[tasks_df['Business_Duration_Hours'] <= 2000]  # Cap outliers
fig_dur_hist = px.histogram(
    valid_durations, x='Business_Duration_Hours', nbins=50,
    title='Task Resolution Duration Distribution (Hours)',
    labels={'Business_Duration_Hours': 'Business Hours'},
    color_discrete_sequence=['#9B5DE5']
)
fig_dur_hist.update_layout(template='plotly_white', height=400, bargap=0.1)
fig_dur_hist.write_html(IMG_DIR / "resolution_duration_histogram.html")
fig_dur_hist.write_image(IMG_DIR / "resolution_duration_histogram.png")
print("✓ Saved: resolution_duration_histogram")

# 10. Resolution Outcome by Assignment Group
outcome_by_group = tasks_df.groupby(['Assignment group', 'Resolution_Outcome']).size().unstack(fill_value=0)
fig_outcome = px.bar(
    outcome_by_group, barmode='group',
    title='Resolution Outcome by Assignment Group',
    color_discrete_map={'Success': '#2ECC71', 'Failure': '#E74C3C', 'Other': '#95A5A6'}
)
fig_outcome.update_layout(template='plotly_white', height=400)
fig_outcome.write_html(IMG_DIR / "outcome_by_assignment_group.html")
fig_outcome.write_image(IMG_DIR / "outcome_by_assignment_group.png")
print("✓ Saved: outcome_by_assignment_group")

# ============================================================================
# SUMMARY STATISTICS
# ============================================================================
print("\n📋 SUMMARY STATISTICS")
print("-" * 40)

print("\n📊 INCIDENTS SUMMARY:")
print(f"  Total Records: {len(incidents_df):,}")
print(f"  Unique Callers: {incidents_df['Caller'].nunique():,}")
print(f"  Resolution Rate: 100% (all closed)")
print(f"  Mean Resolution Time: {incidents_df['Resolution_Hours'].mean():.1f} hours")
print(f"  Median Resolution Time: {incidents_df['Resolution_Hours'].median():.1f} hours")

print("\n📋 TASKS SUMMARY:")
print(f"  Total Records: {len(tasks_df):,}")
print(f"  Success Rate: {(tasks_df['Resolution_Outcome']=='Success').mean()*100:.1f}%")
print(f"  Failure Rate: {(tasks_df['Resolution_Outcome']=='Failure').mean()*100:.1f}%")
print(f"  Mean Business Duration: {tasks_df['Business_Duration_Hours'].mean():.1f} hours")
print(f"  Median Business Duration: {tasks_df['Business_Duration_Hours'].median():.1f} hours")
print(f"  Linked to Incidents: {tasks_df['Incident to request'].notna().sum():,} ({tasks_df['Incident to request'].notna().mean()*100:.1f}%)")

# ============================================================================
# SAVE CLEANED DATASET
# ============================================================================
incidents_df.to_pickle(OUTPUT_DIR / "incidents_cleaned.pkl")
tasks_df.to_pickle(OUTPUT_DIR / "tasks_cleaned.pkl")

# Also save as CSV for easy inspection
incidents_df.to_csv(OUTPUT_DIR / "incidents_cleaned.csv", index=False)
tasks_df.to_csv(OUTPUT_DIR / "tasks_cleaned.csv", index=False)

print("\n✅ CLEANING & EDA COMPLETE")
print(f"✓ Cleaned datasets saved to: {OUTPUT_DIR}")
print(f"✓ {len(list(IMG_DIR.glob('*.html')))} HTML visualizations saved")
print(f"✓ {len(list(IMG_DIR.glob('*.png')))} PNG visualizations saved")