"""
Phase 1: Discovery & Inspection
Loads all three ITSM datasets and performs comprehensive data quality assessment.
"""

import pandas as pd
import numpy as np
import sys
from pathlib import Path

# Paths
INC_PATH = "user_source_data/incident analysis - Nov 25 - Jun -26 - with category.xlsx"
CR_PATH = "user_source_data/CR Analysis - 01.NOV.25 - 30.APR.2026 - with category.xlsx"
SR_PATH = "user_source_data/SR Analysis - 01.NOV.25 - 30.APR.2026.xlsx"

def inspect_dataset(name, path):
    print(f"\n{'='*80}")
    print(f"DATASET: {name}")
    print(f"{'='*80}")
    
    try:
        df = pd.read_excel(path)
    except Exception as e:
        print(f"ERROR loading {name}: {e}")
        return None
    
    print(f"\n--- Shape: {df.shape[0]} rows x {df.shape[1]} columns ---")
    
    print(f"\n--- Columns ---")
    for i, col in enumerate(df.columns):
        print(f"  {i}: {col}")
    
    print(f"\n--- Head (5 rows) ---")
    print(df.head().to_string())
    
    print(f"\n--- Info ---")
    buf = []
    for col in df.columns:
        non_null = df[col].notna().sum()
        dtype = df[col].dtype
        unique = df[col].nunique(dropna=False)
        buf.append(f"  {col:40s} | non-null: {non_null:6d} | dtype: {str(dtype):12s} | unique: {unique:6d}")
    print("\n".join(buf))
    
    print(f"\n--- Describe (numeric) ---")
    numeric_df = df.select_dtypes(include=[np.number])
    if not numeric_df.empty:
        print(numeric_df.describe().to_string())
    else:
        print("  No numeric columns found.")
    
    print(f"\n--- Describe (datetime-like) ---")
    for col in df.columns:
        if 'date' in col.lower() or 'time' in col.lower() or 'opened' in col.lower() or 'closed' in col.lower() or 'start' in col.lower() or 'end' in col.lower() or 'work_' in col.lower():
            try:
                converted = pd.to_datetime(df[col], errors='coerce')
                if converted.notna().sum() > 0:
                    print(f"  {col}: min={converted.min()}, max={converted.max()}, nulls={converted.isna().sum()}")
            except:
                pass
    
    print(f"\n--- Missing Values (top 20) ---")
    missing = df.isna().sum().sort_values(ascending=False)
    missing = missing[missing > 0].head(20)
    if not missing.empty:
        for col, count in missing.items():
            pct = count / len(df) * 100
            print(f"  {col:40s} | {count:6d} missing ({pct:5.1f}%)")
    else:
        print("  No missing values.")
    
    print(f"\n--- Duplicate Rows ---")
    dups = df.duplicated().sum()
    print(f"  Exact duplicate rows: {dups}")
    
    print(f"\n--- Sample Values for Key Categorical Columns ---")
    for col in df.columns:
        if df[col].dtype == 'object' and df[col].nunique(dropna=False) <= 50 and df[col].nunique(dropna=False) > 1:
            print(f"  {col}: {df[col].value_counts(dropna=False).head(10).to_dict()}")
    
    print(f"\n--- End of {name} ---")
    return df

if __name__ == "__main__":
    print("PHASE 1: DATA INSPECTION")
    print(f"Current time: 2026-07-15")
    
    inc_df = inspect_dataset("INCIDENTS", INC_PATH)
    cr_df = inspect_dataset("CHANGE_REQUESTS", CR_PATH)
    sr_df = inspect_dataset("SERVICE_REQUESTS", SR_PATH)
    
    # Save column lists for reference
    if inc_df is not None:
        with open("temp_files/inc_columns.txt", "w") as f:
            f.write("\n".join(inc_df.columns))
    if cr_df is not None:
        with open("temp_files/cr_columns.txt", "w") as f:
            f.write("\n".join(cr_df.columns))
    if sr_df is not None:
        with open("temp_files/sr_columns.txt", "w") as f:
            f.write("\n".join(sr_df.columns))
    
    print("\nInspection complete. Column lists saved to temp_files/")