"""
Phase 1: Data Discovery & Inspection
Load data, inspect schema, types, nulls, duplicates
"""
import pandas as pd
import numpy as np
from pathlib import Path

# Set display options
pd.set_option('display.max_columns', 50)
pd.set_option('display.width', 200)

# Define paths
DATA_PATH = Path('/app/workspace/user_source_data')
OUTPUT_PATH = Path('/app/workspace/temp_files')

# Load the Excel file - OTC sheet (primary incident log)
print("=" * 80)
print("LOADING DATA: SAP Non-AERO 2025 Incident Dump - OTC Sheet")
print("=" * 80)

df = pd.read_excel(DATA_PATH / 'SAP - Non AERO 2025 Incident Dump_OTC.xlsx', sheet_name='OTC')
print(f"\nDataset Shape: {df.shape[0]:,} rows × {df.shape[1]} columns")

# Also load Sheet5 for closure code categorization
print("\n" + "=" * 80)
print("LOADING DATA: Sheet5 (Closure Code Categories)")
print("=" * 80)

try:
    df_sheet5 = pd.read_excel(DATA_PATH / 'SAP - Non AERO 2025 Incident Dump_OTC.xlsx', sheet_name='Sheet5')
    print(f"\nSheet5 Shape: {df_sheet5.shape[0]:,} rows × {df_sheet5.shape[1]} columns")
    print(f"Sheet5 Columns: {df_sheet5.columns.tolist()}")
except Exception as e:
    print(f"Sheet5 load error: {e}")
    df_sheet5 = None

# Display basic info
print("\n" + "=" * 80)
print("DATAFRAME INFO")
print("=" * 80)
print(df.info())

# Display column names
print("\n" + "=" * 80)
print("COLUMN NAMES")
print("=" * 80)
for i, col in enumerate(df.columns):
    print(f"  {i:2d}. {col}")

# Show first 5 rows
print("\n" + "=" * 80)
print("FIRST 5 ROWS")
print("=" * 80)
print(df.head().to_string())

# Statistical summary
print("\n" + "=" * 80)
print("STATISTICAL SUMMARY (Numeric Columns)")
print("=" * 80)
print(df.describe().to_string())

# Missing values analysis
print("\n" + "=" * 80)
print("MISSING VALUES ANALYSIS")
print("=" * 80)
missing = df.isnull().sum()
missing_pct = (df.isnull().sum() / len(df) * 100).round(2)
missing_df = pd.DataFrame({'Missing Count': missing, 'Missing %': missing_pct})
missing_df = missing_df[missing_df['Missing Count'] > 0].sort_values('Missing %', ascending=False)
print(missing_df.to_string())

# Duplicate check
print("\n" + "=" * 80)
print("DUPLICATE ANALYSIS")
print("=" * 80)
print(f"Total duplicate rows: {df.duplicated().sum()}")
print(f"Duplicate 'Number' (Incident ID) values: {df['Number'].duplicated().sum()}")

# Key field analysis
print("\n" + "=" * 80)
print("KEY FIELD ANALYSIS")
print("=" * 80)

# State distribution
print("\n--- State Distribution ---")
print(df['State'].value_counts().to_string())

# Priority distribution
print("\n--- Priority Distribution ---")
print(df['Priority'].value_counts().to_string())

# Region distribution
print("\n--- Region Distribution ---")
print(df['Region'].value_counts().to_string())

# Closure Code distribution
print("\n--- Closure Code Distribution ---")
print(df['Closure Code'].value_counts().head(20).to_string())

# Assignment Group distribution
print("\n--- Top 15 Assignment Groups ---")
# Try to find assignment group column dynamically
assign_group_col = [col for col in df.columns if 'assignment' in col.lower() and 'group' in col.lower()]
if assign_group_col:
    print(f"\n--- Assignment Group (found: {assign_group_col[0]}) ---")
    print(df[assign_group_col[0]].value_counts().head(15).to_string())
else:
    print("\n--- Assignment Group columns found ---")
    print([c for c in df.columns if 'group' in c.lower()])

# Configuration Item distribution
config_col = [col for col in df.columns if 'config' in col.lower() and 'item' in col.lower()]
if config_col:
    print(f"\n--- Top 15 Configuration Items (found: {config_col[0]}) ---")
    print(df[config_col[0]].value_counts().head(15).to_string())
else:
    print("\n--- Configuration Item columns found ---")
    print([c for c in df.columns if 'config' in c.lower()])

# Channel distribution
print("\n--- Channel Distribution ---")
print(df['Channel'].value_counts().to_string())

# Date range analysis
print("\n" + "=" * 80)
print("TEMPORAL ANALYSIS")
print("=" * 80)
df['Opened_dt'] = pd.to_datetime(df['Opened'], errors='coerce')
df['Resolved_dt'] = pd.to_datetime(df['Resolved'], errors='coerce')
print(f"Date range: {df['Opened_dt'].min()} to {df['Opened_dt'].max()}")
print(f"Incidents by Year-Month:")
df['YearMonth'] = df['Opened_dt'].dt.to_period('M')
print(df['YearMonth'].value_counts().sort_index().to_string())

# Save inspection summary
inspection_summary = {
    'total_records': len(df),
    'total_columns': len(df.columns),
    'date_range': f"{df['Opened_dt'].min()} to {df['Opened_dt'].max()}",
    'missing_values': missing_df.to_dict(),
    'duplicates': int(df.duplicated().sum()),
    'priority_distribution': df['Priority'].value_counts().to_dict(),
    'region_distribution': df['Region'].value_counts().to_dict()
}

print("\n" + "=" * 80)
print("INSPECTION COMPLETE")
print("=" * 80)
print(f"Total records: {len(df):,}")
print(f"Total columns: {len(df.columns)}")
print(f"Missing values in key fields: Opened ({df['Opened'].isna().sum()}), Resolved ({df['Resolved'].isna().sum()})")