"""
Phase 1: Discovery & Inspection
Load data, examine structure, identify data quality issues
"""

import pandas as pd
import numpy as np
from pathlib import Path
import warnings
warnings.filterwarnings('ignore')

# Define paths
DATA_DIR = Path("/app/workspace/user_source_data")
OUTPUT_DIR = Path("/app/workspace/temp_files")

# Load datasets
print("=" * 80)
print("PHASE 1: DISCOVERY & INSPECTION")
print("=" * 80)

print("\n📂 Loading datasets...")
incident_file = DATA_DIR / "SAP Security - ERP Access Management - RemainCo Incident data for 2025.xlsx"
task_file = DATA_DIR / "SAP Security - ERP Access Management - RemainCo SC_TASK data for 2025.xlsx"

incidents_df = pd.read_excel(incident_file)
tasks_df = pd.read_excel(task_file)

print(f"✓ Incidents loaded: {len(incidents_df):,} records, {incidents_df.shape[1]} columns")
print(f"✓ Tasks loaded: {len(tasks_df):,} records, {tasks_df.shape[1]} columns")

# Basic Info
print("\n" + "=" * 80)
print("1. INCIDENTS DATASET OVERVIEW")
print("=" * 80)

print("\n📊 Data Types:")
print(incidents_df.dtypes)

print("\n📋 First 5 Rows:")
print(incidents_df.head(3).to_string())

print("\n📈 Descriptive Statistics:")
print(incidents_df.describe(include='all').to_string())

print("\n❌ Missing Values:")
missing_inc = incidents_df.isnull().sum()
missing_inc_pct = (missing_inc / len(incidents_df) * 100).round(2)
missing_inc_df = pd.DataFrame({
    'Missing Count': missing_inc,
    'Missing %': missing_inc_pct
})
print(missing_inc_df[missing_inc_df['Missing Count'] > 0].to_string())

print("\n🔄 Duplicate Values:")
print(f"  Duplicate Number field: {incidents_df['Number'].duplicated().sum()}")

# Tasks Dataset
print("\n" + "=" * 80)
print("2. TASKS DATASET OVERVIEW")
print("=" * 80)

print("\n📊 Data Types:")
print(tasks_df.dtypes)

print("\n📋 First 5 Rows:")
print(tasks_df.head(3).to_string())

print("\n📈 Descriptive Statistics:")
print(tasks_df.describe(include='all').to_string())

print("\n❌ Missing Values:")
missing_task = tasks_df.isnull().sum()
missing_task_pct = (missing_task / len(tasks_df) * 100).round(2)
missing_task_df = pd.DataFrame({
    'Missing Count': missing_task,
    'Missing %': missing_task_pct
})
print(missing_task_df[missing_task_df['Missing Count'] > 0].to_string())

print("\n🔄 Duplicate Values:")
print(f"  Duplicate Number field: {tasks_df['Number'].duplicated().sum()}")

# Column comparison
print("\n" + "=" * 80)
print("3. SCHEMA COMPARISON")
print("=" * 80)

inc_cols = set(incidents_df.columns)
task_cols = set(tasks_df.columns)

common_cols = inc_cols.intersection(task_cols)
unique_to_inc = inc_cols - task_cols
unique_to_task = task_cols - inc_cols

print(f"\nCommon columns ({len(common_cols)}): {sorted(common_cols)}")
print(f"Unique to Incidents ({len(unique_to_inc)}): {sorted(unique_to_inc)}")
print(f"Unique to Tasks ({len(unique_to_task)}): {sorted(unique_to_task)}")

# Key categorical distributions
print("\n" + "=" * 80)
print("4. KEY CATEGORICAL DISTRIBUTIONS")
print("=" * 80)

print("\n📁 Priority Distribution (Incidents):")
print(incidents_df['Priority'].value_counts(dropna=False))

print("\n📁 State Distribution (Incidents):")
print(incidents_df['State'].value_counts(dropna=False))

print("\n📁 Category Distribution (Incidents):")
print(incidents_df['Category'].value_counts(dropna=False))

print("\n📁 Assignment Group (Incidents) - Top 10:")
print(incidents_df['Assignment group'].value_counts(dropna=False).head(10))

print("\n📁 Priority Distribution (Tasks):")
print(tasks_df['Priority'].value_counts(dropna=False))

print("\n📁 State Distribution (Tasks):")
print(tasks_df['State'].value_counts(dropna=False))

print("\n📁 Assignment Group (Tasks) - Top 10:")
print(tasks_df['Assignment group'].value_counts(dropna=False).head(10))

# Temporal coverage
print("\n" + "=" * 80)
print("5. TEMPORAL COVERAGE")
print("=" * 80)

print("\n📅 Incidents Opened Range:")
incidents_df['Opened_parsed'] = pd.to_datetime(incidents_df['Opened'], errors='coerce')
print(f"  Min: {incidents_df['Opened_parsed'].min()}")
print(f"  Max: {incidents_df['Opened_parsed'].max()}")

print("\n📅 Tasks Opened Range:")
tasks_df['Opened_parsed'] = pd.to_datetime(tasks_df['Opened'], errors='coerce')
print(f"  Min: {tasks_df['Opened_parsed'].min()}")
print(f"  Max: {tasks_df['Opened_parsed'].max()}")

# Linkage analysis
print("\n" + "=" * 80)
print("6. INCIDENT-TO-TASK LINKAGE ANALYSIS")
print("=" * 80)

linked_tasks = tasks_df['Incident to request'].notna().sum()
print(f"\n🔗 Tasks linked to incidents: {linked_tasks:,} ({linked_tasks/len(tasks_df)*100:.1f}%)")
print(f"🔗 Unlinked tasks: {len(tasks_df) - linked_tasks:,}")

# Business duration analysis
print("\n" + "=" * 80)
print("7. BUSINESS DURATION ANALYSIS (TASKS)")
print("=" * 80)

if 'Business duration' in tasks_df.columns:
    valid_duration = tasks_df['Business duration'].dropna()
    print(f"  Valid duration records: {len(valid_duration):,}")
    print(f"  Min: {valid_duration.min():,.0f} seconds ({valid_duration.min()/3600:.1f} hours)")
    print(f"  Max: {valid_duration.max():,.0f} seconds ({valid_duration.max()/86400:.1f} days)")
    print(f"  Mean: {valid_duration.mean():,.0f} seconds ({valid_duration.mean()/3600:.1f} hours)")
    print(f"  Median: {valid_duration.median():,.0f} seconds ({valid_duration.median()/3600:.1f} hours)")

# Save summary
summary = {
    'incidents_count': len(incidents_df),
    'tasks_count': len(tasks_df),
    'incidents_columns': incidents_df.shape[1],
    'tasks_columns': tasks_df.shape[1],
    'incidents_temporal_range': f"{incidents_df['Opened_parsed'].min()} to {incidents_df['Opened_parsed'].max()}",
    'tasks_temporal_range': f"{tasks_df['Opened_parsed'].min()} to {tasks_df['Opened_parsed'].max()}",
    'linked_tasks': linked_tasks,
    'unique_callers': incidents_df['Caller ID'].nunique() if 'Caller ID' in incidents_df.columns else 0,
    'unique_requestors': tasks_df['Requested for'].nunique() if 'Requested for' in tasks_df.columns else 0
}

# Save cleaned dates
incidents_df.to_pickle(OUTPUT_DIR / "incidents_raw.pkl")
tasks_df.to_pickle(OUTPUT_DIR / "tasks_raw.pkl")

print("\n" + "=" * 80)
print("✅ INSPECTION COMPLETE")
print("=" * 80)
print(f"\nRaw data saved to: {OUTPUT_DIR}")
print(f"\nKey Findings:")
print(f"  • Total Incidents: {summary['incidents_count']:,}")
print(f"  • Total Tasks: {summary['tasks_count']:,}")
print(f"  • Linked Tasks: {summary['linked_tasks']:,} ({summary['linked_tasks']/summary['tasks_count']*100:.1f}%)")