"""
Phase 2: Cleaning & Exploratory Data Analysis
Address data quality issues, generate foundational visualizations, and save cleaned dataset.
"""

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 os
import warnings
warnings.filterwarnings('ignore')

# Define paths
DATA_PATH = "user_source_data/Schedule B App Catalogue Extract March 2026.xlsx"
OUTPUT_DIR = "temp_files"
IMAGES_DIR = "assets/images"
IMAGES_HTML_DIR = "assets/images/html"
IMAGES_PNG_DIR = "assets/images/png"

# Ensure directories exist
for d in [OUTPUT_DIR, IMAGES_DIR, IMAGES_HTML_DIR, IMAGES_PNG_DIR]:
    os.makedirs(d, exist_ok=True)

# Load data
print("=" * 80)
print("PHASE 2: CLEANING & EXPLORATORY DATA ANALYSIS")
print("=" * 80)

print("\n[1] Loading data...")
df = pd.read_excel(DATA_PATH)

# Clean column names - replace newlines with spaces
df.columns = df.columns.str.replace('\n', ' ').str.strip()
print(f"    Cleaned column names: {list(df.columns)}")

# =====================================
# DATA CLEANING
# =====================================
print("\n[2] Data Cleaning Operations:")

# 2.1 Handle duplicate Titles - create unique identifier
print("    a. Handling duplicate Titles...")
df['_original_title'] = df['Title']
dupe_titles = df[df.duplicated(subset=['Title'], keep=False)]['Title'].unique()
for title in dupe_titles:
    mask = df['Title'] == title
    df.loc[mask, 'Title'] = df.loc[mask, 'Title'] + ' (' + df.loc[mask, 'Sub Domain'].fillna('Unknown') + ')'

print(f"       - Processed {len(dupe_titles)} duplicate Title(s)")

# 2.2 Standardize Application Type values
print("    b. Standardizing Application Type...")
app_type_map = {
    'Infastructure': 'Infrastructure',
    'Pure Infastructure': 'Infrastructure',
    'Shared Infrastructure': 'Infrastructure',
    'Shared Business Infrastructure': 'Business Application',
    'Tool': 'Business Application'
}
df['Application Type'] = df['Application Type'].replace(app_type_map)

# 2.3 Fill missing suppliers with 'Unknown'
print("    c. Filling missing Suppliers...")
df['Supplier'] = df['Supplier'].fillna('Unknown')

# 2.4 Create derived features
print("    d. Creating derived features...")
df['Is_SaaS'] = df['Application Category'].str.lower().str.contains('saas', na=False)
df['Is_Bespoke'] = df['Supplier'].str.lower().str.contains('bespoke', na=False)
df['Is_Infrastructure'] = df['Application Type'].str.lower().str.contains('infrastructure', na=False)

# =====================================
# COMPLETENESS ANALYSIS
# =====================================
print("\n[3] Data Completeness Analysis:")
completeness = (1 - df.isnull().sum() / len(df)) * 100
completeness_df = completeness.sort_values(ascending=True).reset_index()
completeness_df.columns = ['Column', 'Completeness %']

# Create completeness heatmap
fig_completeness = go.Figure(data=go.Heatmap(
    z=[completeness_df['Completeness %'].values],
    x=completeness_df['Column'].values,
    colorscale='RdYlGn',
    zmin=0,
    zmax=100,
    text=[[f"{v:.1f}%" for v in completeness_df['Completeness %'].values]],
    texttemplate="%{text}",
    textfont={"size": 10},
    hovertemplate='Column: %{x}<br>Completeness: %{text}<extra></extra>'
))

fig_completeness.update_layout(
    title='Data Completeness Heatmap Across All Attributes',
    xaxis_title='Attributes',
    yaxis_title='Completeness',
    height=300,
    width=1200,
    xaxis=dict(tickangle=45, tickfont=dict(size=9)),
    yaxis=dict(showticklabels=False)
)

# Save as HTML
completeness_path = os.path.join(IMAGES_HTML_DIR, "completeness_heatmap.html")
fig_completeness.write_html(completeness_path)
print(f"    - Saved: {completeness_path}")

# =====================================
# DOMAIN DISTRIBUTION
# =====================================
print("\n[4] Domain Distribution Analysis:")

domain_counts = df['Domain'].value_counts().reset_index()
domain_counts.columns = ['Domain', 'Count']

fig_domain = px.bar(
    domain_counts, 
    x='Domain', 
    y='Count',
    color='Count',
    color_continuous_scale='Viridis',
    title='Application Distribution by Domain',
    text='Count'
)
fig_domain.update_layout(
    xaxis_title='Business Domain',
    yaxis_title='Number of Applications',
    height=500,
    width=900
)
fig_domain.update_traces(textposition='outside')

domain_path = os.path.join(IMAGES_HTML_DIR, "domain_distribution.html")
fig_domain.write_html(domain_path)
print(f"    - Saved: {domain_path}")

# =====================================
# APPLICATION TYPE DISTRIBUTION
# =====================================
print("\n[5] Application Type Distribution:")

app_type_counts = df['Application Type'].value_counts(dropna=False).reset_index()
app_type_counts.columns = ['Application Type', 'Count']

fig_apptype = px.pie(
    app_type_counts,
    values='Count',
    names='Application Type',
    title='Application Type Breakdown',
    hole=0.4,
    color_discrete_sequence=px.colors.qualitative.Set2
)
fig_apptype.update_layout(height=500, width=700)

apptype_path = os.path.join(IMAGES_HTML_DIR, "application_type_distribution.html")
fig_apptype.write_html(apptype_path)
print(f"    - Saved: {apptype_path}")

# =====================================
# SUPPLIER DISTRIBUTION (Top 15)
# =====================================
print("\n[6] Supplier Distribution (Top 15):")

supplier_counts = df['Supplier'].value_counts().head(15).reset_index()
supplier_counts.columns = ['Supplier', 'Count']

fig_supplier = px.bar(
    supplier_counts,
    x='Count',
    y='Supplier',
    orientation='h',
    color='Count',
    color_continuous_scale='Blues',
    title='Top 15 Suppliers by Application Count',
    text='Count'
)
fig_supplier.update_layout(
    xaxis_title='Number of Applications',
    yaxis_title='Supplier',
    height=600,
    width=900,
    yaxis=dict(categoryorder='total ascending')
)

supplier_path = os.path.join(IMAGES_HTML_DIR, "supplier_distribution.html")
fig_supplier.write_html(supplier_path)
print(f"    - Saved: {supplier_path}")

# =====================================
# SUB-DOMAIN COVERAGE
# =====================================
print("\n[7] Sub-Domain Coverage Analysis:")

subdomain_counts = df.groupby(['Domain', 'Sub Domain']).size().reset_index(name='Count')
fig_subdomain = px.treemap(
    subdomain_counts,
    path=['Domain', 'Sub Domain'],
    values='Count',
    title='Application Coverage: Domain - Sub-Domain Hierarchy',
    color='Count',
    color_continuous_scale='Tealrose'
)
fig_subdomain.update_layout(height=600, width=1000)

subdomain_path = os.path.join(IMAGES_HTML_DIR, "subdomain_treemap.html")
fig_subdomain.write_html(subdomain_path)
print(f"    - Saved: {subdomain_path}")

# =====================================
# FUNCTIONAL AREA ANALYSIS
# =====================================
print("\n[8] Functional Area Analysis (Top 20):")

func_area_counts = df['Functional Area'].value_counts().head(20).reset_index()
func_area_counts.columns = ['Functional Area', 'Count']

fig_func = px.bar(
    func_area_counts,
    x='Count',
    y='Functional Area',
    orientation='h',
    color='Count',
    color_continuous_scale='Plasma',
    title='Top 20 Functional Areas by Application Count',
    text='Count'
)
fig_func.update_layout(
    xaxis_title='Number of Applications',
    yaxis_title='Functional Area',
    height=700,
    width=900,
    yaxis=dict(categoryorder='total ascending')
)

func_path = os.path.join(IMAGES_HTML_DIR, "functional_area_distribution.html")
fig_func.write_html(func_path)
print(f"    - Saved: {func_path}")

# =====================================
# BUSINESS CAPABILITY MAPPING
# =====================================
print("\n[9] Business Capability Coverage:")

cap_l0_counts = df['Business Capabilities Level 0'].value_counts(dropna=False).reset_index()
cap_l0_counts.columns = ['Capability Level 0', 'Count']

fig_cap0 = px.bar(
    cap_l0_counts,
    x='Capability Level 0',
    y='Count',
    color='Count',
    color_continuous_scale='Twilight',
    title='Application Coverage by Business Capability (Level 0)',
    text='Count'
)
fig_cap0.update_layout(
    xaxis_title='Business Capability Level 0',
    yaxis_title='Number of Applications',
    height=500,
    width=900,
    xaxis=dict(tickangle=30)
)
fig_cap0.update_traces(textposition='outside')

cap0_path = os.path.join(IMAGES_HTML_DIR, "capability_level0_distribution.html")
fig_cap0.write_html(cap0_path)
print(f"    - Saved: {cap0_path}")

# =====================================
# SAVE CLEANED DATASET
# =====================================
print("\n[10] Saving cleaned dataset...")

# Select core columns for cleaned dataset
core_columns = [
    'Title', 'Description', 'Alternative Application Name', 'Application Type',
    'Application Category', 'Business Capabilities Level 0', 'Business Capabilities Level 1',
    'Domain', 'Sub Domain', 'Functional Area', 'Functional Sub Area', 
    'Supplier', 'Comments', 'Is_SaaS', 'Is_Bespoke', 'Is_Infrastructure'
]
df_clean = df[core_columns]
cleaned_path = os.path.join(OUTPUT_DIR, "cleaned_portfolio.csv")
df_clean.to_csv(cleaned_path, index=False)
print(f"    - Saved: {cleaned_path}")

# =====================================
# SUMMARY STATISTICS
# =====================================
print("\n[11] Summary Statistics:")
print(f"    - Total Applications: {len(df)}")
print(f"    - Unique Domains: {df['Domain'].nunique()}")
print(f"    - Unique Sub Domains: {df['Sub Domain'].nunique()}")
print(f"    - Unique Functional Areas: {df['Functional Area'].nunique()}")
print(f"    - Unique Suppliers: {df['Supplier'].nunique()}")
print(f"    - Unique Business Capabilities (L0): {df['Business Capabilities Level 0'].nunique()}")
print(f"    - Unique Business Capabilities (L1): {df['Business Capabilities Level 1'].nunique()}")
print(f"    - SaaS Applications: {df['Is_SaaS'].sum()}")
print(f"    - Bespoke Applications: {df['Is_Bespoke'].sum()}")
print(f"    - Infrastructure Applications: {df['Is_Infrastructure'].sum()}")

# Save summary to file
summary_path = os.path.join(OUTPUT_DIR, "eda_summary.txt")
with open(summary_path, 'w') as f:
    f.write("=" * 80 + "\n")
    f.write("PHASE 2: CLEANING & EDA SUMMARY\n")
    f.write("=" * 80 + "\n\n")
    f.write(f"Total Applications: {len(df)}\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")
    f.write(f"Unique Suppliers: {df['Supplier'].nunique()}\n")
    f.write(f"Unique Business Capabilities (L0): {df['Business Capabilities Level 0'].nunique()}\n")
    f.write(f"Unique Business Capabilities (L1): {df['Business Capabilities Level 1'].nunique()}\n")
    f.write(f"\nSaaS Applications: {df['Is_SaaS'].sum()}\n")
    f.write(f"Bespoke Applications: {df['Is_Bespoke'].sum()}\n")
    f.write(f"Infrastructure Applications: {df['Is_Infrastructure'].sum()}\n")
    f.write(f"\nCleaned dataset saved to: {cleaned_path}\n")

print(f"\n- EDA summary saved to: {summary_path}")
print("\n" + "=" * 80)
print("Phase 2 Complete - Ready for Deep Analysis")
print("=" * 80)