"""
Phase 1: Discovery & Inspection
Load and explore the application portfolio data to understand structure and quality.
"""

import pandas as pd
import numpy as np
import os

# Define paths
DATA_PATH = "user_source_data/Schedule B App Catalogue Extract March 2026.xlsx"
OUTPUT_DIR = "temp_files"

# Ensure output directory exists
os.makedirs(OUTPUT_DIR, exist_ok=True)

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

print("\n[1] Loading data...")
df = pd.read_excel(DATA_PATH)
print(f"    Dataset shape: {df.shape[0]} rows × {df.shape[1]} columns")

# Display column names
print("\n[2] Column structure:")
for i, col in enumerate(df.columns):
    print(f"    {i+1}. {col}")

# Data types
print("\n[3] Data types:")
print(df.dtypes)

# Head preview
print("\n[4] First 5 rows (preview):")
print(df.head().to_string())

# Info
print("\n[5] DataFrame info:")
df.info()

# Check for numeric columns
numeric_cols = df.select_dtypes(include=[np.number]).columns.tolist()
print(f"\n[6] Numeric columns found: {len(numeric_cols)}")
if len(numeric_cols) > 0:
    print(df.describe(include=[np.number]))
else:
    print("    (No numeric columns in dataset)")

# Describe (string columns)
print("\n[7] Statistical summary (string columns):")
print(df.describe(include=['object']))

# Missing values analysis
print("\n[8] Missing values analysis:")
missing = df.isnull().sum()
missing_pct = (df.isnull().sum() / len(df) * 100).round(2)
missing_df = pd.DataFrame({
    'Column': missing.index,
    'Missing Count': missing.values,
    'Missing %': missing_pct.values
}).sort_values('Missing %', ascending=False)
print(missing_df.to_string(index=False))

# Duplicate analysis
print("\n[9] Duplicate analysis:")
print(f"    Total rows: {len(df)}")
print(f"    Duplicate rows (exact): {df.duplicated().sum()}")
print(f"    Unique Titles: {df['Title'].nunique()}")
print(f"    Unique Alternative Application Names: {df['Alternative Application Name'].nunique()}")

# Title duplicates
title_dupes = df[df.duplicated(subset=['Title'], keep=False)].sort_values('Title')
print(f"\n    Duplicate Titles ({len(title_dupes)} rows):")
if len(title_dupes) > 0:
    print(title_dupes[['Title', 'Alternative Application Name', 'Domain', 'Supplier']].head(10).to_string())

# Cardinality analysis
print("\n[10] Cardinality analysis (unique values per column):")
for col in df.columns:
    print(f"    {col}: {df[col].nunique()} unique values")

# Value distribution for key categorical columns
print("\n[11] Value distribution - Application Type:")
print(df['Application Type'].value_counts(dropna=False))

print("\n[12] Value distribution - Domain:")
print(df['Domain'].value_counts(dropna=False))

print("\n[13] Value distribution - Supplier (top 20):")
print(df['Supplier'].value_counts(dropna=False).head(20))

# Save summary to file
summary_path = os.path.join(OUTPUT_DIR, "inspection_summary.txt")
with open(summary_path, 'w') as f:
    f.write("=" * 80 + "\n")
    f.write("PHASE 1: DISCOVERY & INSPECTION SUMMARY\n")
    f.write("=" * 80 + "\n\n")
    f.write(f"Dataset Shape: {df.shape[0]} rows × {df.shape[1]} columns\n\n")
    f.write("Columns:\n")
    for col in df.columns:
        f.write(f"  - {col}\n")
    f.write(f"\nMissing Values:\n{missing_df.to_string(index=False)}\n")
    f.write(f"\nUnique Titles: {df['Title'].nunique()}\n")
    f.write(f"Unique Suppliers: {df['Supplier'].nunique()}\n")
    f.write(f"Unique Domains: {df['Domain'].nunique()}\n")
    f.write(f"Unique Sub Domains: {df['Sub Domain'].nunique()}\n")
    f.write(f"Unique Functional Areas: {df['Functional Area'].nunique()}\n")

print(f"\n[✓] Inspection summary saved to: {summary_path}")
print("\n" + "=" * 80)
print("Phase 1 Complete - Ready for Phase 2: Cleaning & EDA")
print("=" * 80)