"""
Phase 2: Cleaning & Exploratory Data Analysis (EDA)
Cleans all three ITSM datasets based on Phase 1 findings and produces foundational plots.
"""

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
import re
from pathlib import Path

# Create output dirs
Path("temp_files").mkdir(exist_ok=True)
Path("assets/images/html").mkdir(parents=True, exist_ok=True)
Path("assets/images/png").mkdir(parents=True, exist_ok=True)

FQDN = "https://t83tog5aeh68y65ra0gtpliq.vibex.drake-ltim.com"

# ============================================================
# 1. LOAD
# ============================================================
print("Loading datasets...")
inc = pd.read_excel("user_source_data/incident analysis - Nov 25 - Jun -26 - with category.xlsx")
cr = pd.read_excel("user_source_data/CR Analysis - 01.NOV.25 - 30.APR.2026 - with category.xlsx")
sr = pd.read_excel("user_source_data/SR Analysis - 01.NOV.25 - 30.APR.2026.xlsx")

print(f"INC: {inc.shape}, CR: {cr.shape}, SR: {sr.shape}")

# ============================================================
# 2. CLEAN INCIDENTS
# ============================================================
print("\n--- Cleaning INCIDENTS ---")

# Ensure datetime
inc['opened_at'] = pd.to_datetime(inc['opened_at'], errors='coerce')

# Fill missing Category
inc['Category'] = inc['Category'].fillna('Unknown')

# Clean close_notes carriage returns
inc['close_notes'] = inc['close_notes'].astype(str).str.replace('_x000D_', ' ', regex=False)
inc['close_notes'] = inc['close_notes'].replace('nan', np.nan)

# Parse CMDB CI components: SYSTEM-IS-COMPONENT-ENV-ID
def parse_ci(ci):
    if pd.isna(ci):
        return pd.Series([np.nan, np.nan, np.nan, np.nan])
    parts = str(ci).split('-')
    if len(parts) >= 5:
        return pd.Series([parts[0], parts[2], parts[3], parts[4]])
    return pd.Series([np.nan, np.nan, np.nan, np.nan])

inc[['ci_system', 'ci_component', 'ci_env', 'ci_id']] = inc['cmdb_ci'].apply(parse_ci)

# Extract region from short_description
def extract_region(desc):
    if pd.isna(desc):
        return 'Unknown'
    desc = str(desc).upper()
    if 'SAP-RUN-EMEA' in desc or 'EMEA' in desc:
        return 'EMEA'
    elif 'SAP-RUN-NA' in desc or 'NA-' in desc:
        return 'NA'
    elif '22_NAQ' in desc or 'NAQ' in desc:
        return 'NA'
    return 'Unknown'

inc['region'] = inc['short_description'].apply(extract_region)

# Extract hour, day_of_week, date
inc['hour'] = inc['opened_at'].dt.hour
inc['day_of_week'] = inc['opened_at'].dt.day_name()
inc['date'] = inc['opened_at'].dt.date

# Extract employee ID from assigned_to
inc['assigned_id'] = inc['assigned_to'].str.extract(r'\((\d+)\)')

print(f"  Categories after cleaning: {inc['Category'].nunique()}")
print(f"  Regions: {inc['region'].value_counts().to_dict()}")
print(f"  CI Systems: {inc['ci_system'].value_counts().head(10).to_dict()}")

# ============================================================
# 3. CLEAN CHANGE REQUESTS
# ============================================================
print("\n--- Cleaning CHANGE REQUESTS ---")

# Datetimes
for col in ['start_date', 'end_date', 'work_start', 'work_end']:
    cr[col] = pd.to_datetime(cr[col], errors='coerce')

# Fill missing assigned_to
cr['assigned_to'] = cr['assigned_to'].fillna('Unknown')

# Parse CI
cr[['ci_system', 'ci_component', 'ci_env', 'ci_id']] = cr['cmdb_ci'].apply(parse_ci)

# Extract region from short_description
cr['region'] = cr['short_description'].apply(extract_region)

# Compute schedule adherence metrics
cr['planned_duration_hours'] = (cr['end_date'] - cr['start_date']).dt.total_seconds() / 3600
cr['actual_duration_hours'] = (cr['work_end'] - cr['work_start']).dt.total_seconds() / 3600
cr['schedule_delta_hours'] = cr['actual_duration_hours'] - cr['planned_duration_hours']
cr['start_delay_hours'] = (cr['work_start'] - cr['start_date']).dt.total_seconds() / 3600

# Parse employee ID
cr['assigned_id'] = cr['assigned_to'].str.extract(r'\((\d+)\)')

print(f"  Planned duration mean: {cr['planned_duration_hours'].mean():.1f}h")
print(f"  Actual duration mean: {cr['actual_duration_hours'].mean():.1f}h")
print(f"  Schedule delta mean: {cr['schedule_delta_hours'].mean():.1f}h")

# ============================================================
# 4. CLEAN SERVICE REQUESTS
# ============================================================
print("\n--- Cleaning SERVICE REQUESTS ---")

# Drop 100% null columns
null_pct = sr.isna().mean()
cols_to_drop = null_pct[null_pct == 1.0].index.tolist()
sr_clean = sr.drop(columns=cols_to_drop)
print(f"  Dropped {len(cols_to_drop)} fully-null columns. Remaining: {sr_clean.shape[1]}")

# Keep only useful columns for analysis
useful_cols = [
    'number', 'state', 'assigned_to', 'short_description', 'cmdb_ci',
    'assignment_group', 'due_date', 'cat_item', 'parent', 'priority',
    'reassignment_count', 'request', 'request_item', 'u_ola_duration',
    'opened_at', 'work_end', 'u_sla_state', 'u_status', 'sys_updated_on',
    'sys_mod_count', 'wf_activity', 'time_worked', 'expected_start'
]
sr = sr_clean[[c for c in useful_cols if c in sr_clean.columns]].copy()

# Datetimes
for col in ['due_date', 'opened_at', 'work_end', 'sys_updated_on', 'expected_start']:
    if col in sr.columns:
        sr[col] = pd.to_datetime(sr[col], errors='coerce')

# Fill missing cmdb_ci
sr['cmdb_ci'] = sr['cmdb_ci'].fillna('Unknown')

# Parse CI
sr[['ci_system', 'ci_component', 'ci_env', 'ci_id']] = sr['cmdb_ci'].apply(parse_ci)

# Parse OLA duration to numeric hours
def parse_ola(ola_str):
    if pd.isna(ola_str):
        return np.nan
    s = str(ola_str).lower()
    # Extract number
    m = re.search(r'(\d+)', s)
    if not m:
        return np.nan
    num = int(m.group(1))
    if 'business day' in s or 'business days' in s:
        if '24-7' in s or '24-5' in s:
            return num * 24
        elif '8-5' in s:
            return num * 8
        else:
            return num * 8
    return num

sr['ola_hours'] = sr['u_ola_duration'].apply(parse_ola)

# Compute resolution time in hours
sr['resolution_hours'] = (sr['work_end'] - sr['opened_at']).dt.total_seconds() / 3600

# Flag OLA breach (resolution > ola_hours)
sr['ola_breached'] = sr['resolution_hours'] > sr['ola_hours']

# Parse employee ID
sr['assigned_id'] = sr['assigned_to'].str.extract(r'\((\d+)\)')

# Extract transport promotion flag
sr['is_transport_promotion'] = sr['cat_item'].str.contains(
    'Transport', case=False, na=False
)

print(f"  OLA hours parsed: {sr['ola_hours'].notna().sum()}")
print(f"  Resolution hours mean: {sr['resolution_hours'].mean():.1f}h")
print(f"  OLA breached: {sr['ola_breached'].sum()} ({sr['ola_breached'].mean()*100:.1f}%)")
print(f"  Transport promotions: {sr['is_transport_promotion'].sum()}")

# ============================================================
# 5. SAVE CLEANED DATASETS
# ============================================================
print("\n--- Saving cleaned datasets ---")
inc.to_parquet("temp_files/incidents_cleaned.parquet", index=False)
cr.to_parquet("temp_files/cr_cleaned.parquet", index=False)
sr.to_parquet("temp_files/sr_cleaned.parquet", index=False)
inc.to_csv("temp_files/incidents_cleaned.csv", index=False)
cr.to_csv("temp_files/cr_cleaned.csv", index=False)
sr.to_csv("temp_files/sr_cleaned.csv", index=False)
print("Saved to temp_files/")

# ============================================================
# 6. FOUNDATIONAL EDA PLOTS
# ============================================================
print("\n--- Generating EDA plots ---")

# Plot 1: Incident volume by month
fig1 = px.bar(
    inc.groupby('Month').size().reset_index(name='count'),
    x='Month', y='count',
    title='Incident Volume by Month',
    labels={'count': 'Incident Count', 'Month': 'Month'},
    color='count', color_continuous_scale='Blues'
)
fig1.update_layout(template='plotly_white')
fig1.write_html("assets/images/html/eda_incident_volume_monthly.html")
fig1.write_image("assets/images/png/eda_incident_volume_monthly.png", width=1000, height=600, scale=2)

# Plot 2: Priority distribution
prio_counts = inc['priority'].value_counts().reset_index()
prio_counts.columns = ['priority', 'count']
fig2 = px.pie(prio_counts, names='priority', values='count', title='Incident Priority Distribution')
fig2.update_traces(textinfo='percent+label')
fig2.update_layout(template='plotly_white')
fig2.write_html("assets/images/html/eda_priority_distribution.html")
fig2.write_image("assets/images/png/eda_priority_distribution.png", width=800, height=600, scale=2)

# Plot 3: Top 15 incident categories
cat_counts = inc['Category'].value_counts().head(15).reset_index()
cat_counts.columns = ['category', 'count']
fig3 = px.bar(cat_counts, x='count', y='category', orientation='h',
              title='Top 15 Incident Categories',
              labels={'count': 'Count', 'category': 'Category'},
              color='count', color_continuous_scale='Reds')
fig3.update_layout(template='plotly_white', yaxis=dict(categoryorder='total ascending'))
fig3.write_html("assets/images/html/eda_top_categories.html")
fig3.write_image("assets/images/png/eda_top_categories.png", width=1000, height=700, scale=2)

# Plot 4: Incident volume by hour of day
hour_counts = inc.groupby('hour').size().reset_index(name='count')
fig4 = px.line(hour_counts, x='hour', y='count', markers=True,
               title='Incident Arrival Pattern by Hour of Day',
               labels={'hour': 'Hour (UTC)', 'count': 'Incident Count'})
fig4.update_layout(template='plotly_white')
fig4.write_html("assets/images/html/eda_incident_hourly.html")
fig4.write_image("assets/images/png/eda_incident_hourly.png", width=1000, height=500, scale=2)

# Plot 5: Change request categories
cr_cat = cr['Category'].value_counts().head(15).reset_index()
cr_cat.columns = ['category', 'count']
fig5 = px.bar(cr_cat, x='count', y='category', orientation='h',
              title='Top 15 Change Request Categories',
              labels={'count': 'Count', 'category': 'Category'},
              color='count', color_continuous_scale='Greens')
fig5.update_layout(template='plotly_white', yaxis=dict(categoryorder='total ascending'))
fig5.write_html("assets/images/html/eda_cr_categories.html")
fig5.write_image("assets/images/png/eda_cr_categories.png", width=1000, height=700, scale=2)

# Plot 6: SR catalog items
sr_cat = sr['cat_item'].value_counts().head(15).reset_index()
sr_cat.columns = ['cat_item', 'count']
fig6 = px.bar(sr_cat, x='count', y='cat_item', orientation='h',
              title='Top 15 Service Request Catalog Items',
              labels={'count': 'Count', 'cat_item': 'Catalog Item'},
              color='count', color_continuous_scale='Purples')
fig6.update_layout(template='plotly_white', yaxis=dict(categoryorder='total ascending'))
fig6.write_html("assets/images/html/eda_sr_catalog_items.html")
fig6.write_image("assets/images/png/eda_sr_catalog_items.png", width=1000, height=700, scale=2)

# Plot 7: SR SLA state distribution
sla_counts = sr['u_sla_state'].value_counts().reset_index()
sla_counts.columns = ['sla_state', 'count']
fig7 = px.pie(sla_counts, names='sla_state', values='count', title='Service Request SLA State Distribution')
fig7.update_traces(textinfo='percent+label')
fig7.update_layout(template='plotly_white')
fig7.write_html("assets/images/html/eda_sr_sla_state.html")
fig7.write_image("assets/images/png/eda_sr_sla_state.png", width=800, height=600, scale=2)

# Plot 8: Regional distribution for incidents
reg_counts = inc['region'].value_counts().reset_index()
reg_counts.columns = ['region', 'count']
fig8 = px.bar(reg_counts, x='region', y='count', title='Incident Volume by Region',
              labels={'region': 'Region', 'count': 'Count'},
              color='count', color_continuous_scale='Oranges')
fig8.update_layout(template='plotly_white')
fig8.write_html("assets/images/html/eda_incident_region.html")
fig8.write_image("assets/images/png/eda_incident_region.png", width=800, height=500, scale=2)

print("\nPhase 2 complete. All cleaned datasets and EDA plots saved.")
print(f"  HTML plots: assets/images/html/")
print(f"  PNG plots:  assets/images/png/")